【应用篇】聊聊MySQL EXPLAIN 从原理到实战优化

上一篇 我写了一篇

一、EXPLAIN 命令：SQL 查询的透视镜

相信看到此篇文章的大多数同学都应该已经用过这个命令了，EXPLAIN 是 MySQL 提供的一个强大工具，用于分析 SQL 查询的执行计划。当你在 SQL 前加上 EXPLAIN 关键字时，MySQL 不会真正执行查询，而是返回查询执行的详细信息，包括表的读取顺序、索引使用情况、数据过滤方式等。

基本语法：

EXPLAIN SELECT * FROM users WHERE age > 30;

2. EXPLAIN 输出字段详解

二、何时需要使用 EXPLAIN？

1. 慢查询场景

当 SQL 查询执行时间过长时，首先应该使用 EXPLAIN 分析执行计划。例如：

-- 执行时间超过 1 秒的查询
SELECT * FROM orders WHERE create_time > '2024-01-01' AND status = 'paid';

使用 EXPLAIN 分析是否存在全表扫描或索引未命中的情况。

2. 优化索引时

在创建或修改索引前，使用 EXPLAIN 评估索引的有效性。例如：

-- 计划为 user_id 和 create_time 添加复合索引
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND create_time > '2024-01-01';

通过分析执行计划，确定索引的顺序和必要性。

3. 复杂查询优化

对于包含子查询、JOIN、GROUP BY、ORDER BY 的复杂查询，EXPLAIN 能揭示潜在的性能问题。例如：

EXPLAIN SELECT u.name, COUNT(o.order_id) 
FROM users u 
JOIN orders o ON u.id = o.user_id 
GROUP BY u.id 
ORDER BY COUNT(o.order_id) DESC;

检查是否存在临时表或文件排序操作。

4. 表结构设计验证

在设计表结构时，使用 EXPLAIN 验证查询模式是否与索引设计匹配。例如：

-- 验证按日期范围查询的索引效果
EXPLAIN SELECT * FROM logs WHERE log_time BETWEEN '2024-01-01' AND '2024-01-31';

三、EXPLAIN 实战优化案例

案例 1：全表扫描优化

业务场景：查询某用户近 30 天的订单 初始 SQL：

EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 AND create_time > CURDATE() - INTERVAL 30 DAY;

EXPLAIN 输出：

优化步骤：

1. 为 user_id 和 create_time 创建复合索引：

   ALTER TABLE orders ADD INDEX idx_user_time (user_id, create_time);
   

2. 再次执行 EXPLAIN：

   table	type	possible_keys	key	rows	Extra
   orders	ref	idx_user_time	idx_user_time	500	Using index condition

优化效果：type 从 ALL 变为 ref，rows 从 100 万降至 500，查询速度提升 1000 倍。

案例 2：消除临时表和文件排序

业务场景：统计各分类的商品数量并按销量排序 初始 SQL：

EXPLAIN SELECT category_id, COUNT(*) as cnt 
FROM products 
GROUP BY category_id 
ORDER BY cnt DESC;

EXPLAIN 输出：

优化步骤：

1. 创建覆盖索引：

   ALTER TABLE products ADD INDEX idx_category (category_id);
   

2. 调整查询顺序，利用索引排序：

   EXPLAIN SELECT category_id, COUNT(*) as cnt 
   FROM products 
   GROUP BY category_id 
   ORDER BY category_id; -- 按索引列排序
   

优化效果：Extra 中的 Using temporary 和 Using filesort 消失，查询时间从 2 秒降至 0.1 秒。

案例 3：子查询优化

业务场景：查询未完成订单的用户信息 初始 SQL：

EXPLAIN SELECT * FROM users 
WHERE id NOT IN (SELECT user_id FROM orders WHERE status = 'completed');

EXPLAIN 输出：

• 子查询 type=ALL，扫描 100 万行

• 主查询 type=ALL，扫描 10 万行

优化步骤：

1. 改写为 LEFT JOIN：

   EXPLAIN SELECT u.* FROM users u 
   LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed' 
   WHERE o.order_id IS NULL;
   

2. 为 orders(user_id, status) 添加复合索引：

   ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
   

优化效果：子查询消失，主查询 type 变为 ref，总扫描行数从 10 亿降至 100 万，性能提升 1000 倍。

四、EXPLAIN 进阶技巧

1. 覆盖索引（Using index）

当查询的所有字段都在索引中时，MySQL 无需回表查询，直接从索引中获取数据，效率极高。例如：

-- 原查询（需回表）
EXPLAIN SELECT id, name FROM users WHERE age > 30;

-- 创建覆盖索引
ALTER TABLE users ADD INDEX idx_age_name (age, name);

-- 优化后查询（覆盖索引）
EXPLAIN SELECT name FROM users WHERE age > 30;
-- Extra 显示 Using index

2. 索引下推（ICP）

MySQL 5.6+ 支持将部分过滤条件下推到存储引擎层，减少回表次数。例如：

-- 原查询
EXPLAIN SELECT * FROM products WHERE category_id = 5 AND price > 100;

-- 创建复合索引
ALTER TABLE products ADD INDEX idx_cat_price (category_id, price);

-- 优化后执行计划显示 Using index condition

3. 强制索引（FORCE INDEX）

当优化器选择的索引不是最优时，可以使用 FORCE INDEX 强制使用指定索引

EXPLAIN SELECT * FROM orders FORCE INDEX (idx_create_time) 
WHERE create_time > '2024-01-01';

4. 分析多表连接顺序

EXPLAIN 会显示表的连接顺序，可通过 STRAIGHT_JOIN 强制调整

EXPLAIN SELECT * FROM A STRAIGHT_JOIN B ON A.id = B.a_id WHERE A.age > 20;

五、EXPLAIN 常见问题与解决方案

1. 索引未命中原因

• 查询条件未使用索引：如对字段进行函数运算 WHERE YEAR(create_time) = 2024

• 类型不匹配：如字符串类型字段未加引号 WHERE user_id = '100'（字段为 INT）

• 索引被截断：复合索引未遵循最左前缀原则

2. 如何优化 Using filesort

• 在 ORDER BY 列上创建索引

• 确保 ORDER BY 条件与索引顺序一致

• 避免 ORDER BY 和 GROUP BY 使用不同的列

3. 处理 Using temporary

• 优化 GROUP BY 和 ORDER BY 条件，使其使用相同的索引

• 对结果集进行预过滤，减少临时表的数据量

• 考虑使用覆盖索引避免回表