MySQL 查询优化与 EXPLAIN 执行计划分析

一条 SQL 语句执行慢，往往不是因为数据库本身的性能瓶颈，而是因为这条 SQL 没有走对索引、扫描了过多数据行。MySQL 提供的 EXPLAIN 命令是诊断这类问题的利器——它能告诉你优化器为这条语句选择了怎样的执行计划。

本文将系统讲解 EXPLAIN 各字段的含义、慢查询日志的配置方法，以及常见场景下的优化技巧，帮助你建立一套完整的 SQL 优化方法论。

一、EXPLAIN 执行计划

在 SQL 语句前加上 EXPLAIN 关键字即可查看执行计划：

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

输出是一个表格，每行代表一个查询步骤（多表 JOIN 会有多行）。核心字段如下：

字段	含义
id	查询标识符，相同 id 表示同一组，从上往下执行；id 越大越先执行
select_type	查询类型（SIMPLE / PRIMARY / SUBQUERY / DERIVED 等）
table	当前步骤访问的表
type	访问类型，反映扫描方式的好坏
possible_keys	优化器可能使用的索引
key	实际选择的索引
key_len	使用的索引长度（字节）
ref	索引比较的来源（const、字段名等）
rows	估算需要扫描的行数
filtered	过滤后剩余的百分比
Extra	附加信息，揭示是否用了文件排序、临时表等

下面重点讲解最关键的几个字段。

1.1 type：访问类型

type 字段从好到坏依次为：

级别	含义	性能
system	表只有一行（系统表）	最优
const	通过主键或唯一索引等值查询，最多匹配一行	极快
eq_ref	JOIN 时使用主键或唯一索引等值匹配，每次精确一行	极快
ref	通过普通索引等值匹配，可能多行	较快
range	索引范围扫描（BETWEEN、>、<、IN）	较快
index	扫描整个索引树，不回表	一般
ALL	全表扫描	最差

生产环境一般要求至少达到 range 级别，ALL 是需要重点优化的信号。

-- const：主键等值查询
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type = const

-- ref：普通索引等值查询
EXPLAIN SELECT * FROM users WHERE email = 'a@b.com';
-- type = ref

-- ALL：无索引的全表扫描
EXPLAIN SELECT * FROM users WHERE age > 20;
-- type = ALL（假设 age 无索引）

1.2 key 与 possible_keys

• possible_keys：优化器认为可能使用的索引列表，为空说明没有可用索引。
• key：优化器实际选择的索引，为 NULL 说明没走索引。

有时会出现 possible_keys 有值但 key 为 NULL 的情况——优化器认为全表扫描比走索引更快（比如表很小，或者索引区分度太低）。如果需要强制使用某个索引：

SELECT * FROM users USE INDEX (idx_email) WHERE email = 'a@b.com';
SELECT * FROM users FORCE INDEX (idx_email) WHERE email = 'a@b.com';

USE INDEX 是建议，FORCE INDEX 是强制。强制索引要谨慎使用，因为数据分布变化后可能不再最优。

1.3 key_len：索引使用长度

key_len 表示实际使用了索引的多少字节，可以据此判断联合索引用到了几列。

计算规则（UTF-8 编码下）：

数据类型	长度
CHAR(n)	3n 字节
VARCHAR(n)	3n + 2 字节
INT	4 字节
BIGINT	8 字节
DATETIME	5 字节
允许 NULL	额外 +1 字节

假设有联合索引 idx(a, b, c)，其中 a 是 INT，b 是 VARCHAR(20)，c 是 INT：

-- 只用到 a：key_len = 4
EXPLAIN SELECT * FROM t WHERE a = 1;

-- 用到 a 和 b：key_len = 4 + (20*3+2) = 66
EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 'x';

-- 用到 a、b、c：key_len = 66 + 4 = 70
EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 'x' AND c = 2;

如果 key_len 比预期小，说明联合索引的某些列没有被利用，往往是因为违反了最左前缀原则。

1.4 rows 与 filtered

• rows：优化器估算的”需要扫描的行数”（不是结果行数）。值越小越好。
• filtered：经过条件过滤后剩余的百分比。rows × filtered / 100 才是真正传给下一步的行数。

例如 rows=1000, filtered=10，意味着扫描 1000 行后只有约 100 行满足条件。如果 filtered 很低，说明扫描了大量无用数据，可能需要加索引或改写查询。

1.5 Extra：附加信息

Extra 字段是优化的重点观察对象，常见值：

值	含义	是否需要优化
Using index	覆盖索引，无需回表	理想
Using where	用 WHERE 过滤	一般
Using index condition	索引下推 ICP	较好
Using filesort	额外排序	需优化
Using temporary	使用临时表	需优化
Using join buffer	JOIN 无索引走 Block Nested Loop	需优化

出现 Using filesort 或 Using temporary 通常意味着查询有优化空间。

二、慢查询日志

2.1 开启慢查询日志

慢查询日志会记录所有执行时间超过阈值的 SQL，是发现性能问题的第一道防线。

-- 查看配置
SELECT @@slow_query_log, @@long_query_time, @@slow_query_log_file;

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 设置阈值（秒），超过此值的查询被记录
SET GLOBAL long_query_time = 1;
-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = ON;

这些是会话级动态设置。持久化需要修改 my.cnf：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

2.2 分析慢查询日志

用 mysqldumpslow 工具汇总分析：

# 按总耗时排序，取 Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

-s 排序方式：t 按总时间、c 按次数、r 按返回行数。更强大的工具是 Percona 的 pt-query-digest：

pt-query-digest /var/log/mysql/slow.log > report.txt

它会按”指纹”（把具体参数替换成 ? 后的 SQL 模板）聚合，输出每类查询的执行次数、总耗时、95% 耗时分布等，便于定位最值得优化的 SQL。

三、常见优化场景

3.1 JOIN 优化

JOIN 的核心原则是被驱动表的关联字段要有索引。

-- ❌ orders.user_id 无索引：被驱动表全表扫描
SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = 1;

-- ✅ 为 orders.user_id 建索引
CREATE INDEX idx_user_id ON orders(user_id);

优化器会自动选择”小结果集”作为驱动表（即”小表驱动大表”）。用 EXPLAIN 确认驱动表是 rows 较小的那一行。

BNL 与 NLJ 算法：

算法	触发条件	性能
Nested Loop Join（NLJ）	被驱动表关联字段有索引	快
Block Nested Loop（BNL）	被驱动表无索引	慢，出现 Using join buffer
Hash Join	MySQL 8.0+ 无索引时	比 BNL 快

MySQL 8.0.18 起引入了 Hash Join 替代 BNL，无索引的 JOIN 性能有所改善，但仍建议通过加索引走 NLJ。

3.2 子查询改写

某些子查询会被优化器执行得很低效，常见改写：

IN 子查询改 JOIN：

-- ❌ 子查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);

-- ✅ 改写为 JOIN
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.vip = 1;

NOT IN 改 LEFT JOIN：

-- ❌ NOT IN
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE created_at > '2025-01-01');

-- ✅ LEFT JOIN ... WHERE ... IS NULL
SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.created_at > '2025-01-01'
WHERE o.user_id IS NULL;

MySQL 8.0 对子查询的优化已大幅改进，很多场景下子查询会被自动转换为半连接（Semi-Join），但 EXPLAIN 发现效率不佳时仍可手动改写。

3.3 分页优化

LIMIT offset, size 在 offset 很大时性能极差，因为 MySQL 要扫描 offset+size 行后丢弃前 offset 行。

-- ❌ 深度分页，offset=100000 扫描 10 万行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

方案一：子查询 + 覆盖索引

-- ✅ 子查询先通过覆盖索引拿到 id，再关联
SELECT * FROM orders
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 20;

方案二：游标分页（记住上一页最后一个 id）

-- ✅ 记住上一页最后一条的 id，直接定位
SELECT * FROM orders WHERE id > 100020 ORDER BY id LIMIT 20;

游标分页性能最优，但要求排序字段连续且唯一，且不支持跳页。

3.4 ORDER BY 优化

ORDER BY 在以下情况下可以利用索引已排序的特性，避免 Using filesort：

1. ORDER BY 的字段是索引的最左前缀。
2. ORDER BY 的方向与索引一致（索引默认 ASC，ORDER BY ... DESC 需要索引也是 DESC 或用反向扫描）。

-- 索引 idx(a, b, c)
-- ✅ Using index
SELECT * FROM t WHERE a = 1 ORDER BY b, c;

-- ✅ Using index
SELECT * FROM t WHERE a = 1 ORDER BY b;

-- ❌ Using filesort（跳过了 b）
SELECT * FROM t WHERE a = 1 ORDER BY c;

-- ❌ Using filesort（方向不一致）
SELECT * FROM t WHERE a = 1 ORDER BY b DESC, c ASC;

四、覆盖索引

当查询所需的所有字段都能从索引中获取时，就称为覆盖索引（Covering Index），此时 Extra 会显示 Using index，无需回表查询聚簇索引。

-- 假设有索引 idx_name_email(name, email)
-- ❌ 需要回表：SELECT * 要拿所有字段
SELECT * FROM users WHERE name = 'Tom';

-- ✅ 覆盖索引：只查 name 和 email
SELECT name, email FROM users WHERE name = 'Tom';

设计索引时，可以按”查询字段”组织联合索引，把高频查询变成覆盖索引，能显著减少 IO。但要注意联合索引列数不宜过多（一般不超过 5 列），索引维护成本也要考虑。

一个典型的”查询+排序”覆盖索引设计：

-- 业务：查某用户的订单列表，按时间倒序
-- 索引 idx(user_id, created_at DESC)
SELECT user_id, order_no, created_at FROM orders
WHERE user_id = 123 ORDER BY created_at DESC LIMIT 20;

如果 order_no 也在索引里，就完全不用回表了。

五、索引下推 ICP

Index Condition Pushdown（ICP） 是 MySQL 5.6 引入的优化，核心思想是把 WHERE 条件下推到存储引擎层，在索引扫描时就过滤，减少回表次数。

没有 ICP 时：

1. 存储引擎按索引找到所有满足”最左前缀”的记录。
2. 逐行回表取完整数据。
3. Server 层用其他 WHERE 条件过滤。

有 ICP 时：

1. 存储引擎按索引找到满足”最左前缀”的记录。
2. 在索引上直接应用能用到索引列的 WHERE 条件过滤。
3. 只有通过过滤的行才回表。

看一个例子。假设有联合索引 idx(name, age)：

SELECT * FROM users WHERE name LIKE '张%' AND age > 18;

• name LIKE '张%' 可以用索引范围扫描。
• age > 18 由于不是等值匹配的后续列，在旧版本中不能用索引过滤，只能回表后再判断。

开启 ICP 后（Extra 显示 Using index condition）：

-- 无 ICP：扫描所有 name 以"张"开头的记录（可能 1000 行），全部回表
-- 有 ICP：在索引上先用 age > 18 过滤（可能剩 100 行），只回表这 100 行

查看与控制 ICP：

SELECT @@optimizer_switch\G
-- 默认包含 index_condition_pushdown=on

-- 关闭 ICP（仅用于测试对比）
SET SESSION optimizer_switch='index_condition_pushdown=off';

六、查询重写技巧

6.1 避免对索引列使用函数

-- ❌ 函数作用在索引列上，索引失效
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

-- ✅ 改为范围查询
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

6.2 避免隐式类型转换

-- 字段 phone 是 VARCHAR，传入数字会隐式转换，索引失效
-- ❌
SELECT * FROM users WHERE phone = 13800138000;
-- ✅
SELECT * FROM users WHERE phone = '13800138000';

6.3 LIKE 前导通配符失效

-- ❌ 前导 % 导致索引失效
SELECT * FROM users WHERE name LIKE '%Tom';

-- ✅ 后导 % 可以用索引
SELECT * FROM users WHERE name LIKE 'Tom%';

如果必须做前导模糊匹配，考虑使用全文索引或外部搜索引擎（如 Elasticsearch）。

6.4 OR 条件拆分

-- ❌ OR 两侧只要有一侧无索引，整个查询退化为全表扫描
SELECT * FROM users WHERE id = 1 OR name = 'Tom';

-- ✅ 拆为 UNION ALL（两侧都走索引）
SELECT * FROM users WHERE id = 1
UNION ALL
SELECT * FROM users WHERE name = 'Tom' AND id <> 1;

MySQL 5.0+ 的 Index Merge 优化能自动对 OR 使用多个索引合并，但 UNION 写法往往更稳定可控。

6.5 COUNT 优化

-- ❌ 全表扫描
SELECT COUNT(*) FROM orders WHERE status = 'pending';
-- ✅ 为 status 建索引

如果对精确度要求不高，可以用估算值：

-- 快速估算（返回近似值）
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

七、优化方法论总结

把前面的内容汇总成一套可执行的优化流程：

1. 开启慢查询日志，用 pt-query-digest 找出 Top SQL
        ↓
2. 对目标 SQL 执行 EXPLAIN，观察：
   - type 是否为 ALL / index？（需走更高效的访问类型）
   - key 是否为 NULL？（未走索引）
   - rows 是否过大？（扫描行数过多）
   - Extra 是否有 filesort / temporary？（需改写或加索引）
        ↓
3. 针对性优化：
   - 无索引 → 建合适的索引（联合索引遵循最左前缀）
   - 索引未命中 → 检查函数、类型转换、前导通配符
   - 深度分页 → 子查询或游标分页
   - JOIN 慢 → 被驱动表关联字段加索引
   - filesort → 排序字段纳入索引
        ↓
4. 优化后再次 EXPLAIN 验证，对比 rows 与执行时间
        ↓
5. 上线后持续监控慢查询日志，形成闭环

结语

SQL 优化不是玄学，而是一套基于执行计划的系统工程。EXPLAIN 是诊断工具，索引是主要手段，而理解优化器的工作方式是判断依据。掌握本文介绍的方法论后，面对慢查询时你可以：

• 读懂 EXPLAIN 每个字段的含义，快速定位问题；
• 针对不同场景（JOIN、分页、排序）选择对应优化策略；
• 通过覆盖索引与 ICP 进一步压榨索引性能；
• 建立慢查询监控闭环，持续优化系统。

最后强调一点：优化要基于真实数据和业务场景。同样一条 SQL，在不同数据量、不同分布下，执行计划可能完全不同。养成”改完就 EXPLAIN 验证”的习惯，比记住任何技巧都重要。