文章
文章用户商铺文档快讯圈子网址导航

[数据库优化] 10个MySQL/MariaDB索引优化技巧：大幅提升查询性能

[数据库优化] 10个MySQL/MariaDB索引优化技巧：大幅提升查询性能

你的网站或应用程序是不是经常因为数据库查询缓慢而让用户等到“地老天荒”？CPU 占用率不高，内存也足够，服务器看起来一点都不忙，但页面就是出奇地慢？如果你遇到了这种情况，那么恭喜你（也可能是不幸），你很可能遇到了数据库性能优化中最常见也最关键的一环——索引问题！

很多开发者和初级 DBA（数据库管理员）可能会觉得索引这东西“玄之又玄”，或者简单粗暴地给每个列都加上索引，以为这样就能“一劳永逸”。大错特错！索引就像是一把锋利的“双刃剑”，用好了能让你的查询速度快如闪电，用不好（或者不用）则可能让数据库背上沉重的负担，甚至拖慢整体性能。它更像是一门“精确制导”的艺术，需要我们理解其原理，并根据实际的查询模式进行精心设计和维护。

别担心，这门“艺术”并没有你想象的那么高不可攀。这篇指南，我就将我多年“与慢查询作斗争”的经验，浓缩成 10 个（实际上会超过一点点，给你加量不加价！）实用且易于上手的 MySQL/MariaDB 索引优化技巧，并结合一些“接地气”的比喻和实战思路，帮助你理解索引的奥秘，让你也能成为一名“查询性能调优师”，为你的数据库大幅提速！准备好了吗？让我们开始给你的数据库“开刃”吧！

索引基础回顾：为什么它是查询性能的“超级加速器”？

在深入技巧之前，我们先花一分钟快速重温一下索引到底是个啥，以及它为什么能让查询飞起来。

简单来说，数据库索引是一种特殊的数据结构（在 MySQL InnoDB 和 MyISAM 存储引擎中，最常用的是 B-Tree 或其变种 B+Tree 结构），它存储了表中一个或多个列（我们称之为“索引键”）的排序副本，并且每个索引键都带有一个指向原始数据行物理位置的“指针”（就像书的目录页码指向正文章节一样）。

它是如何加速查询的？

  • 当你执行一个带 WHERE 条件的查询（比如 SELECT * FROM users WHERE username = 'hostol';）时，如果 username 列上建有索引，数据库引擎就可以直接通过这个有序的索引结构，快速定位到 username 等于 ‘hostol’ 的那几条记录的“指针”，然后根据指针直接去表中取出数据，而无需从头到尾扫描整张表的每一行。这就像查字典时，你先通过偏旁部首或拼音索引找到字的页码，而不是一页页翻。
  • 对于 JOIN 操作，如果连接条件中的字段（如 ON t1.id = t2.user_id）建有索引，数据库也能更高效地匹配两张表中的相关记录。
  • 对于 ORDER BY (排序) 和 GROUP BY (分组) 操作，如果排序或分组的列与索引的顺序一致，数据库可能可以直接利用索引的有序性，避免昂贵的额外排序操作（所谓的“文件排序 filesort”）。

索引并非“免费的午餐”：

虽然索引能大大提升查询（主要是 SELECT）性能，但它也不是没有代价的：

  • 占用磁盘空间： 索引本身也是要存储的，索引越多、越复杂，占用的磁盘空间就越大。
  • 降低写操作（INSERT, UPDATE, DELETE）的性能： 当你对表中的数据进行增、删、改时，数据库不仅要修改表数据本身，还需要同时更新相关的索引结构以保持其有序性和准确性。索引越多，这个更新维护的开销就越大，从而可能降低写操作的速度。

所以，创建索引是一个权衡利弊的过程。我们需要为那些能从索引中获得巨大查询性能提升的列和查询模式创建索引，同时避免创建过多不必要或低效的索引。这正是我们接下来要讨论的优化技巧的核心。

10+ 个MySQL/MariaDB索引优化技巧，让你的查询飞起来！

好了，基础知识回顾完毕，上干货！

技巧1：为WHERE子句中的常用查询条件列创建索引 (最基础也最重要)

这是索引应用最广泛、最直接有效的场景。如果你的查询语句中，WHERE 子句里经常用来作为筛选条件的那些列（比如用户表中的 email, user_id, status；订单表中的 order_status, customer_id），那么为这些列创建索引，几乎总能带来显著的性能提升。

示例：

假设你经常执行这样的查询：

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


SELECT * FROM users WHERE email = 'test@example.com';
SELECT * FROM orders WHERE customer_id = 123 AND order_status = 'completed';

那么，你就应该考虑在 users 表的 email 列上创建一个索引，并在 orders 表的 customer_id 列和 order_status 列上创建索引（可能是单独创建，也可能是联合索引，见后文）。

如何创建？

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_customer_status ON orders (customer_id, order_status); -- 这是一个组合索引

如何验证效果？ 使用 EXPLAIN 命令分析你的查询。如果索引生效，EXPLAIN 输出的 type 列通常会是 ref, eq_ref, range, index 等（都比 ALL 全表扫描好得多），并且 key 列会显示实际用到的索引名称。

技巧2：善用“覆盖索引” (Covering Index) 减少“回表”查询，一步到位！

什么是“覆盖索引”？听起来是不是很高大上？其实原理很简单：如果一个索引中已经包含了查询语句所需检索的所有列（即 SELECT 后面跟着的列、WHERE 条件中的列、以及可能涉及的 ORDER BY 或 GROUP BY 列），那么 MySQL/MariaDB 就可以仅仅通过读取这个索引就能得到所有需要的数据，而完全不需要再去访问原始的数据表（这个访问原始数据表的额外操作，我们称之为“回表查询”，是很耗时的）。这就叫覆盖索引。

示例：

假设你有这样一个查询，它只需要用户的 ID 和用户名：

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


SELECT user_id, username FROM users WHERE status = 'active' AND age > 30;

如果你只在 status 和 age 上创建了索引，比如 INDEX(status, age)，那么数据库引擎通过这个索引找到符合条件的记录后，还需要根据索引中的主键指针“回表”到 users 表中去捞取 user_id 和 username 这两列数据。

但如果你创建的是一个包含了所有查询所需列的“覆盖索引”，比如：

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


CREATE INDEX idx_status_age_id_name ON users (status, age, user_id, username);

那么，当执行上面的查询时，数据库引擎只需要扫描这个索引，就能拿到所有需要的信息，完全不用再碰数据表了！这时，你用 EXPLAIN 分析查询，通常会在 Extra 列看到 Using index 的提示，这就是覆盖索引生效的标志。

注意： 创建覆盖索引时，也需要权衡索引的大小。如果包含的列太多，索引本身也会变得很大。通常用于那些查询非常频繁、且能显著减少回表开销的核心查询。

技巧3：理解并用好“组合索引”（联合索引），顺序很重要！

当你的查询条件中经常同时涉及到多个列时（比如 WHERE col1 = 'A' AND col2 = 'B' AND col3 = 'C'），为这些列创建一个组合索引 (Composite Index)，通常比为它们分别创建多个单列索引要高效得多。

创建组合索引：

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


CREATE INDEX idx_col1_col2_col3 ON mytable (col1, col2, col3);

“最左前缀”原则 (Leftmost Prefix Principle)： 这是使用组合索引时必须理解的核心原则！

一个定义在 (col1, col2, col3) 上的组合索引，它可以有效地支持以下这些查询条件的索引查找：

  • WHERE col1 = 'value'
  • WHERE col1 = 'value' AND col2 = 'value'
  • WHERE col1 = 'value' AND col2 = 'value' AND col3 = 'value'

也就是说，查询条件必须从索引定义的最左边的列开始，并且是连续的，才能充分利用这个组合索引。如果你的查询条件是：

  • WHERE col2 = 'value' (跳过了最左边的 col1) → 索引通常无法高效使用。
  • WHERE col1 = 'value' AND col3 = 'value' (跳过了中间的 col2) → 索引在 col1 上可能生效，但 col3 部分通常无法有效利用索引进行快速定位（可能需要索引扫描）。

如何决定组合索引中列的顺序？ 通常的经验是：

  1. 将选择性最高（基数最大，即不同值的数量最多）的列放在最前面，这样能更快地筛选掉大部分不符合条件的记录。
  2. 考虑查询中这些列被用作等值查询 (= 或 IN) 的频率，常用的等值查询列也适合放在前面。
  3. 如果查询中有范围查询 (>, <, BETWEEN, LIKE 'prefix%')，尽量把等值查询的列放在范围查询的列前面，因为一旦遇到范围查询，它右边的索引列通常就无法再用于精确的索引定位了（只能用于索引扫描）。

合理设计组合索引的列顺序，是用好它的关键！

技巧4：关注索引的“选择性”（基数 Cardinality）– 别给“性别”列建索引！

索引的“选择性”指的是索引列中不同值的数量（我们称之为基数，Cardinality）与表中总行数的比率。一个具有高选择性（高基数）的列，意味着该列有很多不同的值，比如用户表中的 user_id (每个都唯一) 或 email (大部分唯一)。为这种列创建索引，数据库引擎通过索引值能非常快速地筛选到极少数符合条件的行，效率非常高。

相反，如果一个列的选择性很低（低基数），意味着该列只有很少几种不同的值，比如用户表中的 gender (可能只有 ‘男’, ‘女’, ‘未知’ 三种) 或者 is_active (可能只有 0 和 1 两种)。为这种列创建索引，效果通常很差，甚至可能起反作用！因为即使通过索引找到了某个值（比如 gender = '男'），它可能对应了表中一半的记录，数据库引擎可能觉得还不如直接全表扫描来得快，从而放弃使用这个索引。

如何判断选择性？

  • 执行 SHOW INDEX FROM your_table_name;，查看输出结果中的 Cardinality 列。这个值是数据库估算的索引中唯一值的数量。将它与表的总行数 (SELECT COUNT(*) FROM your_table_name;) 对比一下。如果 Cardinality 远小于总行数，说明选择性可能不高。
  • 更精确的方法是自己计算：SELECT COUNT(DISTINCT(column_name)) / COUNT(*) AS selectivity FROM your_table_name; 结果越接近 1，选择性越高。

经验法则： 通常，当一个列的选择性低于某个阈值（比如低于 5%-10%，即一个索引值平均对应超过 10-20% 的数据行）时，为它单独创建 B-Tree 索引的意义就不大了。对于这种低基数列，如果确实需要作为查询条件，可以考虑将它与其他高选择性的列一起放入组合索引中（并且通常放在组合索引的后面位置），或者考虑其他优化方式（如应用层逻辑、位图索引（MySQL InnoDB不直接支持）等）。

技巧5：避免在索引列上“动手动脚” – 不要在 WHERE 子句的索引列上使用函数或运算

这是一个非常常见也极其容易让索引“报废”的坏习惯！如果你在 WHERE 子句中，对已经建立了索引的列使用了函数（如 YEAR(date_column), LOWER(username), SUBSTRING(phone, 1, 3)）或者进行了算术运算（如 age + 1 = 30, price * 1.1 > 100），那么 MySQL/MariaDB 的优化器通常就**无法有效地使用该列上的索引**了，它很可能会退化成全表扫描。

为什么会这样？ 因为 B-Tree 索引中存储的是原始列值的排序。当你对列值进行了转换（通过函数或运算）之后，优化器就不知道转换后的值在索引中对应哪个位置了。

示例（错误的做法）：

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


-- 假设 `created_at` 列上有索引
SELECT * FROM orders WHERE YEAR(created_at) = 2025; -- 索引可能失效！

-- 假设 `username` 列上有索引
SELECT * FROM users WHERE LOWER(username) = 'admin'; -- 索引可能失效！

-- 假设 `price` 列上有索引
SELECT * FROM products WHERE price * 1.1 > 100; -- 索引可能失效！

如何修正（正确的做法）：

关键在于，将函数或运算移到查询条件的右边（常量值那边），保持索引列的“纯洁”。

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


-- 修正 YEAR() 的例子:
SELECT * FROM orders WHERE created_at &gt;= '2025-01-01 00:00:00' AND created_at &lt; '2026-01-01 00:00:00';

-- 修正 LOWER() 的例子 (如果数据库和列的字符集/排序规则不区分大小写，通常可以直接比较；否则可能需要在应用层处理或使用支持大小写不敏感的排序规则)
-- SELECT * FROM users WHERE username = 'admin'; (假设排序规则不区分大小写)

-- 修正 price * 1.1 &gt; 100 的例子:
SELECT * FROM products WHERE price &gt; 100 / 1.1; -- (即 price &gt; 90.909...)

保持索引列“干净”，是让优化器能愉快地使用索引的前提。

技巧6：优化JOIN操作 – 为关联字段创建索引，并确保类型匹配

当你的查询涉及到多表连接 (JOIN) 时，索引的正确使用对性能至关重要。数据库需要根据连接条件（通常在 ON 或 USING 子句中指定）来匹配两张表中的相关记录。如果这些连接字段上没有索引，数据库可能需要进行嵌套循环连接（Nested Loop Join）的低效操作，即对一张表的每一行都去扫描另一张表的全部内容，数据量大时性能会非常糟糕。

优化要点：

  • 在所有参与 JOIN 条件的列上，都应该创建索引。例如，对于查询 SELECT ... FROM tableA a JOIN tableB b ON a.columnX = b.columnY;，你应该在 tableA 的 columnX 列和 tableB 的 columnY 列上都创建索引（如果它们不是主键或已有的唯一键的话）。
  • 更重要的是，确保参与连接的这些字段，在两张表中具有**完全相同的数据类型、字符集和排序规则 (Collation)**！如果类型不匹配（比如一个是 INT，另一个是 VARCHAR；或者一个是 utf8mb4_general_ci，另一个是 utf8mb4_unicode_ci），即使它们都建立了索引，数据库在连接时也可能无法有效利用索引，或者需要进行隐式类型转换，从而大大降低性能。

示例：

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


-- 假设有 orders 表和 customers 表
-- orders.customer_id (INT) 需要连接 customers.id (INT, 主键)
-- 确保 orders.customer_id 上有索引
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
-- customers.id 作为主键，通常自动有索引

正确索引连接字段，是优化多表查询性能的关键一步。

技巧7：合理使用前缀索引缩短长字符串索引长度

如果你的表中有些列是很长的字符串类型（比如 VARCHAR(255), TEXT, BLOB），你可能仍然希望为它们创建索引以加速查询（比如基于文章标题的搜索）。但是，直接为整个长字符串列创建索引，会导致索引本身非常庞大，占用大量磁盘空间，并且在比较和查找时效率也可能不高。

这时，可以考虑使用**前缀索引 (Prefix Index)**。即，只对该字符串列的**前面一部分字符**（比如前 10 个、前 50 个或前 100 个字符）创建索引。

创建语法：

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


-- 为 article_title 列的前 50 个字符创建索引
CREATE INDEX idx_articles_title_prefix ON articles (article_title(50));

优点：

  • 显著减小索引大小，节省磁盘空间。
  • 提高索引的查找和比较效率（因为比较的字符串更短了）。

缺点与权衡：

  • 降低了索引的**选择性**。因为只索引了前缀，不同的原始字符串可能拥有相同的前缀，导致通过前缀索引筛选出来的结果集比完整索引要大，可能需要更多地回表确认。
  • 前缀索引**不能用于覆盖索引**（因为它没有包含完整的列值）。
  • 前缀索引**不能用于 ORDER BY 或 GROUP BY** 操作。

如何选择合适的前缀长度？ 这需要你分析你的数据。目标是找到一个尽可能短、但又能提供足够选择性（即不同前缀的数量尽可能多）的长度。你可以通过类似这样的查询来估算不同前缀长度的选择性：

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


SELECT 
    COUNT(DISTINCT LEFT(your_column, 10)) / COUNT(*) AS selectivity_10_chars,
    COUNT(DISTINCT LEFT(your_column, 20)) / COUNT(*) AS selectivity_20_chars,
    COUNT(DISTINCT LEFT(your_column, 50)) / COUNT(*) AS selectivity_50_chars
FROM your_table;

选择一个让选择性接近完整列选择性的、最短的前缀长度。

技巧8：小心 LIKE 查询的“左模糊” – 它可能让索引失效

在进行字符串模糊匹配时，我们经常使用 LIKE 操作符。但你需要知道，LIKE 的使用方式直接影响索引能否被利用：

  • WHERE column LIKE 'keyword%' (右模糊/后缀匹配): 这种情况，如果 column 上有 B-Tree 索引，是可以有效利用索引进行范围扫描的，性能通常不错。
  • WHERE column LIKE '%keyword' (左模糊/前缀匹配): 这种情况，B-Tree 索引通常**无法有效使用**，因为索引是按从左到右的顺序排列的，你不知道开头的字符是什么，就没法快速定位。
  • WHERE column LIKE '%keyword%' (全模糊/包含匹配): 这种情况，B-Tree 索引同样**无法有效使用**。

如何应对左模糊或全模糊搜索需求？

  • 如果数据量不大，或者对性能要求不高，全表扫描可能是可以接受的（但通常不是好主意）。
  • 考虑使用 **MySQL/MariaDB 内置的全文索引 (Full-Text Search)** 功能。为你的文本列创建全文索引，然后使用 MATCH(...) AGAINST(...) 语法进行搜索，它专门为文本内容搜索优化，支持更复杂的匹配模式，并且比 LIKE '%...%' 在大数据量下性能好得多。
  • 对于更高级、更专业的全文搜索需求，可以考虑引入外部的专业搜索引擎，如 **Elasticsearch** 或 **OpenSearch**，并将需要搜索的数据同步到这些引擎中。

避免在对性能要求高的查询中使用无法利用索引的左模糊或全模糊 LIKE。

技巧9：定期分析与优化现有索引 – “体检”与“瘦身”

索引不是“创建完就万事大吉”的。随着你的应用数据不断增长、查询模式发生变化，你之前创建的索引可能变得不再高效，甚至可能有些索引从来就没被用上，白白占着空间、拖慢写操作。因此，定期对现有索引进行“体检”和“瘦身”非常重要。

你需要做什么？

  • 更新索引统计信息： 数据库优化器依赖准确的统计信息（如列的基数、数据分布等）来决定是否使用某个索引以及如何使用。定期（或在数据发生大量变更后）执行 ANALYZE TABLE your_table_name; 命令，可以帮助优化器更新这些统计信息，做出更优的查询计划。
  • 识别并删除未使用的索引 (Unused Indexes)： 有些索引可能在你创建后，因为查询条件变化、代码重构等原因，再也没有被任何查询使用过。它们就成了“僵尸索引”，只带来负面影响。
    • MySQL 5.6 及以后版本，如果开启了 performance_schema，可以通过查询 sys 库中的 schema_unused_indexes 视图或 performance_schema.table_io_waits_summary_by_index_usage 表来找到那些 I/O 操作为 0 的索引。（需要确保 performance_schema 相关消费者已开启）。
    • 可以使用 Percona Toolkit 中的 pt-index-usage 工具来分析查询日志，找出未被使用的索引。
    • 找到后，大胆地用 DROP INDEX index_name ON table_name; 把它们删掉！
  • 识别并合并冗余索引 (Redundant Indexes)： 有时你可能会创建一些功能上重复或被其他组合索引覆盖的索引。例如：
    • 如果你已经有了组合索引 INDEX(colA, colB)，那么再单独为 colA 创建一个索引通常是冗余的（因为组合索引的最左前缀已经能覆盖对 colA 的查询）。
    • 如果你有一个主键 PRIMARY KEY(id)，又创建了一个唯一索引 UNIQUE INDEX(id)，后者也是冗余的。
    识别并删除这些冗余索引，可以减少索引维护开销和存储空间。
  • 识别缺失的索引 (Missing Indexes)： 通过分析慢查询日志（结合 EXPLAIN），或者使用像 Percona Toolkit 的 pt-query-digest（它可以分析查询日志并给出索引建议）、或者一些商业的数据库优化工具（如 EverSQL, SolarWinds DPA），可以帮助你发现那些因为缺少合适索引而导致性能低下的查询，并给出创建新索引的建议。

定期审视你的索引策略，就像定期给你的汽车做保养一样，能让它始终保持在最佳状态。

技巧10：理解 ORDER BY 和 GROUP BY 如何利用索引避免“文件排序”

索引不仅仅能加速 WHERE 子句的查找，它也能帮助优化器高效地处理 ORDER BY (排序) 和 GROUP BY (分组) 操作，避免进行昂贵的额外“文件排序 (filesort)”或“临时表 (temporary table)”操作。

如何让 ORDER BY / GROUP BY 用上索引？

关键在于，你排序或分组的列，必须与某个现有索引的**列顺序和排序方向**相匹配：

  • 列顺序： 如果你有一个组合索引 INDEX(colA, colB, colC)，那么 ORDER BY colA, ORDER BY colA, colB, ORDER BY colA, colB, colC 都有可能直接利用这个索引的有序性。但 ORDER BY colB 或者 ORDER BY colA, colC (跳过了 colB) 通常就无法完全利用这个索引来避免排序了。
  • 排序方向： 如果索引是按升序 (ASC) 创建的（默认），那么 ORDER BY colA ASC 就能用上。如果查询是 ORDER BY colA DESC，而索引是 ASC，优化器有时也能反向扫描索引来避免排序（但并非总是高效）。如果组合索引中的所有列都以相同的方向排序（要么全 ASC，要么全 DESC），并且你的查询也使用相同的排序方向，那么利用索引的效率最高。
  • WHERE 条件与排序/分组的配合： 如果 WHERE 子句中的条件（特别是等值条件）已经将结果集缩小到了索引的某个连续范围，那么后续的 ORDER BY 或 GROUP BY 在这个范围内的索引列上，就更容易利用索引的有序性。

示例：

假设有索引 INDEX idx_cat_date ON posts (category_id ASC, publish_date DESC);

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


-- 可能利用索引避免排序 (Using index for order by)
SELECT * FROM posts WHERE category_id = 10 ORDER BY publish_date DESC;

-- category_id 是等值，publish_date 排序方向与索引一致

-- 可能无法完全利用索引避免排序 (可能出现 Using filesort)
SELECT * FROM posts ORDER BY publish_date DESC; -- 没有使用最左前缀 category_id
SELECT * FROM posts WHERE category_id = 10 ORDER BY publish_date ASC; -- publish_date 排序方向与索引不一致

当你用 EXPLAIN 分析这类查询时，如果 Extra 列没有出现 Using filesort 或 Using temporary，而是可能出现 Using index for group-by 或直接是 Using index (如果是覆盖索引)，那就说明排序或分组操作很可能高效地利用了索引。

(额外技巧) 技巧11：了解并选择合适的索引类型（不止 B-Tree）

虽然 B-Tree (或 B+Tree) 是 MySQL/MariaDB (特别是 InnoDB 和 MyISAM 存储引擎) 最常用、也是默认的索引类型，并且它对各种查询条件（等值、范围、排序）都有较好的普适性。但了解一下还有其他类型的索引，以及它们各自的特长，有时也能帮你解决特定问题：

  • 哈希索引 (Hash Index): 只支持精确的等值比较 (= 或 IN)，不支持范围查询或排序。它的查找速度理论上是 O(1)，非常快。MySQL 的 Memory 存储引擎支持显式的哈希索引。InnoDB 有一个“自适应哈希索引 (Adaptive Hash Index)”特性，它会在内部根据访问模式自动为某些热点 B-Tree 索引页创建哈希索引，你通常无法直接控制。
  • 全文索引 (Full-Text Index): 专门用于对大段文本内容（如文章正文、产品描述）进行关键词搜索。它使用不同于 B-Tree 的算法来索引单词或词组，并支持更自然的语言搜索（如 MATCH(...) AGAINST(...) 语法）。MyISAM 和 InnoDB (MySQL 5.6+ / MariaDB 10.0.5+) 都支持全文索引。
  • 空间索引 (Spatial Index / R-Tree Index): 用于索引地理空间数据类型（如点、线、多边形），以便高效地执行地理位置相关的查询（如“查找我附近的餐馆”）。MyISAM 和 InnoDB (MySQL 5.7.5+) 都支持空间索引。

虽然 B-Tree 索引能解决 90% 以上的问题，但了解这些特殊类型的索引，能在特定场景下为你提供更优的解决方案。

如何知道我的查询是否用上了索引？– EXPLAIN 命令是你的“透视眼镜”

说了这么多技巧，你怎么知道你的优化是否真的生效了呢？或者，当一个查询很慢时，你怎么判断它是不是因为没用上索引呢？答案就是使用 **EXPLAIN 命令**！

EXPLAIN 是 MySQL/MariaDB 提供的一个非常强大的工具，它可以分析并显示数据库将如何执行一条 SELECT 语句（对于 UPDATE, DELETE, INSERT 也可以用，但通常用在 SELECT 上来分析其内部的 SELECT 部分）。你可以把它想象成，在你真正让图书管理员去找书之前，先问问他：“嘿，这本书你打算怎么找？是直接去对应的书架（用索引），还是准备把整个图书馆翻个底朝天（全表扫描）？”

如何使用？ 非常简单，在你的 SELECT 语句前加上 EXPLAIN 关键字即可：

[提示：请将以下代码片段复制并粘贴到 WordPress 的“代码”区块中]


EXPLAIN SELECT * FROM users WHERE username = 'hostol' AND status = 'active';

执行后，它不会真的返回查询结果，而是会输出一张包含很多列的“执行计划表”。你需要重点关注以下几列：

  • id: 查询的标识符。
  • select_type: 查询的类型（如 SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION 等）。
  • table: 当前正在访问的表名。
  • partitions: 匹配的分区（如果使用了表分区）。
  • type: 极其重要！ 这是访问类型，直接反映了查询效率。理想情况下，我们希望看到：
    • system / const: 表中只有一行匹配（或通过主键/唯一索引直接定位到一行），速度最快。
    • eq_ref: 通常出现在多表连接中，对于前一张表的每一行，在当前表中只通过唯一性索引（主键或唯一索引）精确匹配到一行。
    • ref: 通过非唯一性索引查找到匹配的行。
    • range: 使用索引进行范围查找（如 WHERE id > 100, WHERE date BETWEEN ...）。
    • index: 扫描了整个索引树来查找数据。比全表扫描快，因为索引通常比表数据小，但如果索引很大，仍然可能很慢。如果 Extra 列显示 Using index，表示是覆盖索引，性能不错。
    • ALL: 全表扫描！ 这是最坏的情况，意味着数据库需要读取表中的每一行来找到匹配的数据。如果你的表很大，这会非常非常慢！看到 ALL，通常就意味着你需要检查索引了。
  • possible_keys: 显示了优化器认为可能可以用于这个查询的索引列表。
  • key: 极其重要！ 显示了优化器最终实际选择使用的索引。如果是 NULL，表示没有使用任何索引。
  • key_len: 使用的索引的长度（字节）。越短通常越好（在保证选择性的前提下）。
  • ref: 显示了哪些列或常量被用于与 key 列中的索引进行比较。
  • rows: 优化器估算的为了找到结果，需要扫描的行数。这个值越小越好。
  • filtered: 表示符合查询条件（WHERE 子句）的行数占扫描行数的百分比。
  • Extra: 极其重要！ 包含了很多额外的、非常有价值的信息，比如：
    • Using index: 表明查询使用了“覆盖索引”，无需回表，性能极佳！
    • Using where: 表明在通过索引找到行之后，还需要使用 WHERE 子句中的其他条件进行过滤。
    • Using temporary: 表明查询需要创建一个临时表来存储中间结果（通常在 ORDER BY 或 GROUP BY 无法利用索引时出现），性能较差。
    • Using filesort: 表明查询需要进行一次额外的排序操作（通常在 ORDER BY 无法利用索引时出现），性能较差。
    • Using index condition (ICP – Index Condition Pushdown): 索引条件下推，优化器可以将部分 WHERE 条件下推到存储引擎层面，在读取索引时就进行过滤，减少回表次数。

通过仔细分析 EXPLAIN 的输出，特别是 type, key, rows, Extra 这几列，你就能准确地判断出你的查询是否用上了索引、用上了哪个索引、以及索引的使用效率如何，从而有针对性地进行优化。

结论：索引是“双刃剑”，善用方能为你的查询“提速增效”！

怎么样，通过今天的“索引优化秘籍”分享，你是不是对如何让你的 MySQL/MariaDB 查询“飞”起来，有了更清晰的思路和更足的信心了？

记住，索引是提升数据库查询性能最直接、最有效的手段之一，它就像是给你的数据检索过程安装了“涡轮增压器”。但是，它也是一把“双刃剑”：恰到好处的索引能带来数量级的性能提升，而不当或过多的索引则可能拖慢写操作、占用过多磁盘空间，甚至误导查询优化器。

所以，优化索引的关键在于：

  • 理解你的查询： 你的应用最常执行哪些查询？它们的瓶颈在哪里？
  • 精准创建索引： 为真正需要加速的查询条件、连接字段、排序和分组字段创建合适的索引（单列或组合，考虑选择性和最左前缀）。
  • 避免索引失效的“坑”： 保持索引列的“纯洁”，小心使用函数和左模糊 LIKE。
  • 善用 EXPLAIN： 它是你诊断和验证索引效果的“火眼金睛”。
  • 定期审视和维护： 索引不是一劳永逸的，随着数据和查询的变化，你需要定期分析、清理无用索引、添加新的必要索引。

希望这些技巧能帮助你彻底告别“慢查询”的烦恼，让你的数据库和应用都能如丝般顺滑地运行！

还有疑问？常见问题解答 (FAQs)

  1. 问: 表的主键 (Primary Key) 会自动创建索引吗？唯一键 (Unique Key) 呢？ 答: 是的！当你为一个表定义主键时，MySQL/MariaDB 会自动为该主键列（或列组）创建一个唯一的、通常是聚集的（对于 InnoDB）索引。同样，当你为一个列（或列组）添加唯一约束 (UNIQUE KEY) 时，数据库也会自动为它创建一个唯一索引。所以，主键列和唯一键列通常不需要你再手动为它们单独创建普通索引了，它们本身就具备了索引的快速查找能力。
  2. 问: 是不是给表创建的索引越多越好？ 答: 绝对不是！这是一个常见的误区。虽然索引能加速查询，但每个索引都会：1) 占用额外的磁盘空间。2) 在进行数据写入操作（INSERT, UPDATE, DELETE）时，数据库需要花费额外的时间去维护这些索引的结构（比如更新 B-Tree），从而降低写操作的性能。索引越多，写操作的负担就越重。因此，你应该只为你真正需要通过索引来加速的查询创建索引，并定期清理那些很少被使用或完全不被使用的“僵尸索引”。目标是在查询性能和写性能/存储空间之间找到一个最佳平衡点。
  3. 问: 索引对 INSERT, UPDATE, DELETE 这些写操作有帮助吗？ 答: 对于写操作本身（比如直接 INSERT 一条新记录），索引通常是负面影响，因为数据库在写入数据的同时还需要更新所有相关的索引。但是，对于带 WHERE 条件的 UPDATE 和 DELETE 语句，索引则非常重要！因为数据库需要先通过 WHERE 条件快速定位到要修改或删除的那些行，这个定位过程如果能用上索引，就能大大提高 UPDATE/DELETE 的效率。如果没有索引，数据库可能需要全表扫描来找到目标行，然后再进行修改或删除，那就会非常慢了。
  4. 问: 我听说 InnoDB 表的索引有“聚集索引”和“非聚集索引”（或“二级索引”）的区别，这是什么意思？ 答: 这是 InnoDB 存储引擎的一个重要特性。简单来说：聚集索引 (Clustered Index) 的叶子节点直接存储了完整的数据行。InnoDB 表通常使用主键作为聚集索引。这意味着表中的数据行本身就是按照主键的顺序物理存储的（或者逻辑上紧密排列）。因此，通过主键查找数据非常快。而非聚集索引 (Non-clustered Index)，也叫二级索引或辅助索引，它的叶子节点存储的不是完整数据行，而是索引键的值以及对应数据行的主键值。当你通过二级索引查找数据时，数据库引擎会先在二级索引中找到对应的主键值，然后再根据这个主键值去聚集索引（也就是数据表本身）中查找完整的数据行——这个过程就叫做“回表查询”。这也是为什么覆盖索引（如果二级索引包含了所有查询所需列）能提升性能，因为它避免了回表。
  5. 问: 我为一个组合索引 INDEX(colA, colB) 创建了索引。如果我的查询条件是 WHERE colB = 'value' 或者 WHERE colA = 'valA' OR colB = 'valB'，这个索引能用上吗？ 答: 1) 对于 WHERE colB = 'value'：由于它没有使用组合索引的最左前缀 (colA)，所以这个组合索引通常无法被用来直接定位数据，查询很可能会退化成全表扫描或索引扫描（如果优化器认为扫描整个索引比扫描表快的话）。2) 对于 WHERE colA = 'valA' OR colB = 'valB'：OR 条件通常也会让组合索引难以有效利用。优化器可能会尝试对 colA = 'valA' 使用索引，对 colB = 'valB' 使用索引（如果 colB 单独也有索引的话），然后合并结果；或者干脆放弃索引进行全表扫描。组合索引对 OR 条件和非最左前缀条件的优化能力是有限的。

实操指南

[MySQL实战] 主从复制(Replication)搭建教程：实现读写分离与高可用基础

2025-5-16 11:29:32

实操指南

[服务器备份教程] Rclone实战：自动备份数据到阿里云OSS/腾讯云COS等对象存储

2025-5-19 10:53:50

0 条回复 A文章作者 M管理员
    暂无讨论，说说你的看法吧