MySQL的explain执行计划分析与索引优化

当查询变慢时，第一个工具就是 EXPLAIN。它像一张X光片，能清晰地展示MySQL如何执行你的SQL语句。学会阅读它，是优化查询、合理使用索引的必经之路。

第一阶段：运行并解读 EXPLAIN 输出

执行 EXPLAIN 命令是分析的起点。在你的SQL语句前加上 EXPLAIN 关键字即可。

EXPLAIN SELECT * FROM users WHERE age > 25;

执行后，你会得到一个包含多列的表格。重点关注以下列：

1. 检查 type 列的值。它描述了MySQL查找数据的方式，从好到坏的顺序是：

   • system > const > eq_ref > ref > range > index > ALL
   • 核心目标是避免出现 ALL（全表扫描）。看到 ALL，意味着数据库不得不从头到尾把整张表都扫一遍，性能最差。index 表示全索引扫描，稍好一点，但也不理想。range 及以上级别通常是比较可接受的。

2. 查看 key 列的值。这一列显示了MySQL实际决定使用的索引。如果该列为 NULL，则表示没有使用任何索引，这是需要优化的重要信号。

3. 评估 rows 列的值。这个数字是MySQL预估需要扫描的行数。这个数字越小越好。它不是精确值，但能很好地反映查询的复杂度。

4. 分析 Extra 列的值。这里会给出一些额外的、非常重要的信息。

   • Using index：这是一个非常好的信号，意味着查询所需的所有数据都能直接从索引中获得，无需回表读取数据行。这被称为“覆盖索引”。
   • Using where：表示MySQL在存储引擎层返回数据后，还需要在服务器层进行额外的过滤。
   • Using temporary：表示MySQL需要创建一个临时表来处理查询，常见于 GROUP BY 或 ORDER BY 子句，通常需要优化。
   • Using filesort：表示MySQL需要对结果进行额外的排序操作，无法利用索引完成排序。当数据量大时，性能影响显著。

第二阶段：识别导致索引失效的常见场景

即使创建了索引，在某些写法下，优化器也可能选择不使用它。避免这些写法是优化的前提。

1. 对索引列使用函数或进行计算。

   -- 索引失效
   SELECT * FROM orders WHERE YEAR(create_time) = 2023;
   -- 优化写法
   SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

2. 在 WHERE 子句中对索引列使用 != 或 <> 操作符。在大多数情况下，优化器会认为全表扫描比走索引更快。

3. 在 WHERE 子句中使用 OR，且 OR 的两侧条件并非都走索引。如果其中一个条件没有索引，整个查询可能退化为全表扫描。

   -- 如果 `name` 有索引，`age` 无索引，这个查询可能不走索引
   SELECT * FROM users WHERE name = 'Tom' OR age = 25;

4. 在 WHERE 子句中使用 LIKE 且以通配符开头。

   -- 索引失效
   SELECT * FROM users WHERE name LIKE '%om';
   -- 索引可用
   SELECT * FROM users WHERE name LIKE 'Tom%';

5. 字符串类型字段在查询条件中未加引号。这会导致MySQL进行隐式的类型转换，相当于对索引列进行了函数操作。

   -- `phone` 是字符串类型，索引可能失效
   SELECT * FROM users WHERE phone = 13800138000;
   -- 优化写法
   SELECT * FROM users WHERE phone = '13800138000';

第三阶段：针对性的索引优化策略

创建索引并非越多越好。索引会占用存储空间，并降低写操作（INSERT, UPDATE, DELETE）的速度。因此，必须精确地创建“有用”的索引。

1. 分析高频查询和慢查询。优化应从最影响系统性能的地方开始。使用慢查询日志（slow_query_log）来捕获这些SQL。

2. 遵循最左前缀原则来设计复合索引。假设你有一个索引 idx_a_b_c (a, b, c)。

   -- 可以使用该索引的查询
   WHERE a = 1
   WHERE a = 1 AND b = 2
   WHERE a = 1 AND b = 2 AND c = 3
   WHERE a = 1 AND c = 3 (只用到 a 部分)
   WHERE b = 2 AND c = 3 (无法使用该索引)
   WHERE a = 1 AND b > 10 AND c = 3 (a 和 b 可用，c 不可用)

   思考查询条件，将区分度最高（即该列不同值最多）的列放在复合索引的最左边。

3. 利用覆盖索引减少回表。如果查询只需要索引中的字段，就在 SELECT 中明确指定这些字段，而非使用 SELECT *。

   -- 假设 `idx_user_name_age` 在 `(name, age)` 列上
   -- 这个查询可以仅通过索引完成，性能极高
   SELECT name, age FROM users WHERE name = 'Tom';

4. 优化 ORDER BY 和 GROUP BY。确保排序或分组的字段与索引顺序一致，可以避免额外的排序操作（Using filesort）和临时表（Using temporary）。

   -- 索引 `idx_category_time (category, create_time)`
   -- 这个查询既能用索引快速过滤，也能利用索引的有序性进行排序
   SELECT * FROM products
   WHERE category = '电子产品'
   ORDER BY create_time DESC;

5. 定期审查和维护索引。使用 SHOW INDEX FROM table_name; 查看表的索引信息。删除未被使用、冗余或重复的索引。

第四阶段：完整的优化流程实践

1. 定位问题SQL。通过监控或慢日志，找到执行缓慢、调用频繁的SQL语句。

2. 执行 EXPLAIN。复制这条SQL，在前面加上 EXPLAIN 并运行。审视输出的 type、key、rows 和 Extra 列。

3. 判断索引使用情况。key 为 NULL 或 type 为 ALL/index 通常意味着需要优化。

4. 检查索引设计。查看 WHERE、ORDER BY、GROUP BY 涉及的字段。判断是否已有合适的索引？索引的列顺序是否合理？

5. 应用优化策略。根据第二、三阶段的知识，修改SQL写法以避免索引失效，或创建/调整索引。

6. 验证优化效果。再次执行 EXPLAIN，对比优化前后的 type、key、rows。观察 Extra 列是否消除了 Using filesort 或 Using temporary。确认性能是否得到提升。

7. 重复以上过程，优化下一条问题SQL。