让查询飞起来 —— 查询优化与Explain

你写了一条SQL，功能上没问题，但执行要5秒。老板说：“能不能快点？“你一脸茫然，不知道慢在哪儿。今天我们来学习MySQL最实用的性能诊断工具——EXPLAIN，以及查询优化的核心思路。

📋 开篇自测：你已经知道多少？

EXPLAIN输出中的type列有哪些值？它们从好到差的排列顺序是什么？

MySQL的查询优化器是基于什么原则来选择执行计划的？

什么是”索引条件下推（ICP）“？它能带来什么好处？

一、查询优化器——MySQL的”导航仪”

当你写下一条SQL时，MySQL并不是”你怎么说它怎么做”。在执行之前，查询优化器会分析这条SQL，尝试找到执行成本最低的方案。

就像你打开导航软件输入目的地，导航不会直接让你走直线，而是综合考虑距离、拥堵、红绿灯等因素，找一条综合成本最低的路线。

优化器做了哪些事情

规则优化（逻辑优化）：基于等价变换规则改写SQL
成本优化（物理优化）：估算不同执行计划的成本，选择最优的

规则优化的例子

-- 你写的
SELECT * FROM users WHERE 1=1 AND age > 18;
-- 优化器改写后（移除恒为真的条件）
SELECT * FROM users WHERE age > 18;

-- 你写的
SELECT * FROM users WHERE age + 0 > 18;
-- 优化器可能不会改写这个，因为对列做了运算，索引失效了！

-- 常量传播
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 100;
-- 优化器推导出：o.user_id = 100，可以直接用索引查orders

成本优化的原理

MySQL为每种可能的执行计划估算一个”成本值”。成本主要由两部分组成：

I/O成本：从磁盘读取数据页的代价。一个页的I/O成本默认为1.0
CPU成本：检查记录是否满足条件的代价。一条记录的CPU成本默认为0.2

这些默认值可以通过 mysql.server_cost 和 mysql.engine_cost 系统表进行调整，以匹配你的实际硬件环境（比如SSD的随机I/O成本远低于机械硬盘）。

-- 查看MySQL优化器为某个查询估算的成本
EXPLAIN FORMAT=JSON SELECT * FROM employees WHERE department = '技术部';

在JSON格式的EXPLAIN输出中，你可以看到cost_info字段，里面有详细的成本估算。

-- 查看优化器可能考虑过的执行方案（MySQL 8.0+）
-- 使用optimizer_trace
SET optimizer_trace = 'enabled=on';
SELECT * FROM employees WHERE department = '技术部' AND salary > 15000;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';

🤔 想一想 如果一个表有100万行，有一个city列的索引，执行SELECT * FROM users WHERE city = '上海'。假设上海的用户有50万人，你觉得优化器会选择用索引还是全表扫描？为什么？

二、EXPLAIN——查询的X光片

EXPLAIN是MySQL最重要的性能分析工具。在任何SELECT语句前面加上EXPLAIN关键字，MySQL就会展示这条查询的执行计划，而不是真正执行它。

EXPLAIN SELECT * FROM employees WHERE department = '技术部';

EXPLAIN输出的每一列都有特定含义，我们逐个解析。

id列

表示查询中SELECT的序号。id相同的行在同一个查询层级中，从上到下执行；id不同的，id大的先执行。

-- 简单查询：只有1个id
EXPLAIN SELECT * FROM employees WHERE id = 1;

-- 子查询：有2个id
EXPLAIN SELECT * FROM employees
WHERE department IN (SELECT dept_name FROM departments WHERE location = '3楼');

select_type列

查询的类型：

值	含义
SIMPLE	简单查询（不包含子查询或UNION）
PRIMARY	最外层的查询
SUBQUERY	WHERE中的子查询
DERIVED	FROM中的子查询（派生表）
UNION	UNION中第二个及之后的查询

table列

这一行的执行针对哪张表。

type列（最重要！）

访问类型，表示MySQL用什么方式访问这张表。这是判断查询性能最直观的指标。从好到差排列：

system > const > eq_ref > ref > range > index > ALL

system：表中只有一行数据。极少见
const：通过主键或唯一索引等值查询。最多返回一行，速度极快
```
EXPLAIN SELECT * FROM employees WHERE id = 1;  -- type: const
```

eq_ref：连接查询中，被驱动表通过主键或唯一索引等值匹配。每次只匹配一行

EXPLAIN SELECT * FROM orders o
JOIN users u ON o.user_id = u.id;  -- users表的type: eq_ref

ref：通过普通索引等值查询。可能匹配多行

EXPLAIN SELECT * FROM employees WHERE department = '技术部';  -- type: ref（如有索引）

range：索引范围扫描（>、<、BETWEEN、IN等）

EXPLAIN SELECT * FROM employees WHERE salary > 15000;  -- type: range（如有索引）

index：全索引扫描。遍历整棵索引树，但不需要回表

EXPLAIN SELECT id FROM employees;  -- type: index（只需扫描主键索引）

ALL：全表扫描。最慢，需要优化

EXPLAIN SELECT * FROM employees WHERE name LIKE '%伟';  -- type: ALL

实际开发中，至少要达到range级别。如果看到ALL，必须考虑优化。

possible_keys 和 key列

possible_keys：优化器认为可能用到的索引
key：实际选择的索引

如果key为NULL，说明没有使用任何索引。

key_len列

使用的索引的长度（字节数）。对于联合索引，可以通过key_len判断用了联合索引的几个列。

-- 联合索引 idx_dept_salary(department, salary)
-- department是VARCHAR(30) utf8mb4，允许NULL时 key_len = 30*4+2+1 = 123（变长+NULL标记）
-- 如果该列定义为NOT NULL，则不需要NULL标记的1字节，key_len = 30*4+2 = 122
-- salary是DECIMAL(10,2) NOT NULL，key_len再加5
EXPLAIN SELECT * FROM employees WHERE department = '技术部' AND salary > 15000;
-- key_len = 128 → 用了两个列

rows列

优化器估算需要扫描的行数。注意这是估算值，不一定准确。

Extra列（信息量很大）

常见的Extra值：

值	含义	好坏
Using index	覆盖索引，无需回表	好
Using where	需要在Server层做额外过滤	中性
Using index condition	索引条件下推（ICP）	好
Using temporary	需要创建临时表	需优化
Using filesort	需要额外排序（非索引排序）	需优化
Using join buffer	连接时使用了Join Buffer	中性偏差

⚠️ 常见误区 误区一：EXPLAIN显示用了索引就一定快。 索引也分好坏。如果type是index（全索引扫描），虽然走了索引，但效率不一定比全表扫描好多少。关键是看type是否是const、eq_ref、ref或range。

误区二：rows越小查询一定越快。 rows只是估算值，而且不考虑回表的成本。一个查询可能rows很小但因为大量回表而很慢。

三、索引条件下推（ICP）

这是MySQL 5.6引入的一个重要优化，理解它需要先知道MySQL的两层架构：

Server层：负责SQL解析、优化、调用存储引擎
存储引擎层（InnoDB）：负责数据的读写

没有ICP时的流程：

1. Server层告诉InnoDB："给我满足某个索引条件的记录"
2. InnoDB通过索引找到记录，回表取出完整行数据，返回给Server层
3. Server层再检查其他WHERE条件，过滤不满足的行

有ICP时的流程：

1. Server层告诉InnoDB："给我满足某个索引条件的记录，顺便帮我检查这些额外条件"
2. InnoDB通过索引找到记录，先检查索引中已有的列是否满足额外条件
3. 只有满足条件的记录才回表取完整数据
4. 减少了不必要的回表次数

-- 假设有联合索引 idx_dept_salary(department, salary)
EXPLAIN SELECT * FROM employees
WHERE department LIKE '技%' AND salary > 15000;
-- Extra: Using index condition  ← ICP生效

在这个例子中，InnoDB在索引层面就能判断salary > 15000，不满足的记录直接跳过，不用回表。

把ICP比作网购：没有ICP时，仓库把所有”技X部”的货物都打包发给你，你再自己挑出价格合适的。有了ICP，仓库帮你先筛一遍价格，只发给你符合条件的——快递费（I/O）省了很多。

🤔 想一想 ICP只在什么类型的索引场景下有意义？如果查询条件完全可以通过索引满足（覆盖索引），还需要ICP吗？

四、常见查询优化技巧

1. 避免SELECT *

-- 差：取所有列，可能无法利用覆盖索引
SELECT * FROM employees WHERE department = '技术部';

-- 好：只取需要的列，可能走覆盖索引
SELECT name, salary FROM employees WHERE department = '技术部';

2. 利用索引排序

-- 假设有索引 idx_dept_salary(department, salary)

-- 好：ORDER BY的列在索引中，不需要filesort
SELECT name, salary FROM employees
WHERE department = '技术部'
ORDER BY salary;

-- 差：ORDER BY的列不在索引中，需要filesort
SELECT name, salary FROM employees
WHERE department = '技术部'
ORDER BY hire_date;

3. 用UNION ALL替代UNION

-- UNION会去重（需要排序），更慢
SELECT name FROM employees WHERE department = '技术部'
UNION
SELECT name FROM employees WHERE department = '市场部';

-- UNION ALL不去重（不需要排序），更快
-- 如果你确定结果不会重复，或者不需要去重
SELECT name FROM employees WHERE department = '技术部'
UNION ALL
SELECT name FROM employees WHERE department = '市场部';

4. 优化GROUP BY

-- 利用索引避免临时表和排序
-- 如果有索引 idx_department(department)
SELECT department, COUNT(*) FROM employees GROUP BY department;
-- Extra中不会出现 Using temporary 和 Using filesort

-- 如果GROUP BY的列没有索引，MySQL需要创建临时表来分组
-- 考虑添加索引或优化查询

5. 小表驱动大表

-- 当需要过滤的条件在小表中时
-- 用EXISTS（大表驱动小表时效率低）和IN（小表驱动大表时更好）

-- 假设departments表很小，employees表很大
-- 好：IN从小表中取出值，驱动大表查询
SELECT * FROM employees
WHERE department IN (SELECT dept_name FROM departments WHERE location = '3楼');

-- 在某些情况下EXISTS更快（外表小、内表大且内表有索引时）
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department = d.dept_name);

6. 延迟关联优化深度分页

-- 慢：LIMIT偏移量大时，需要扫描大量数据
SELECT * FROM employees ORDER BY hire_date LIMIT 100000, 10;

-- 快：先用覆盖索引定位id，再回表取数据
SELECT e.*
FROM employees e
INNER JOIN (
    SELECT id FROM employees ORDER BY hire_date LIMIT 100000, 10
) AS tmp ON e.id = tmp.id;

五、慢查询日志——找到性能瓶颈

在生产环境中，不可能对每条SQL都手动EXPLAIN。慢查询日志可以自动帮你捕获执行慢的SQL。

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;

-- 设置阈值：超过1秒的查询会被记录
SET GLOBAL long_query_time = 1;

-- 记录没有使用索引的查询（即使它们执行很快）
SET GLOBAL log_queries_not_using_indexes = ON;

-- 查看慢查询日志文件位置
SHOW VARIABLES LIKE 'slow_query_log_file';

分析慢查询日志

MySQL自带了mysqldumpslow工具来分析慢查询日志：

# 查看最慢的10条SQL
mysqldumpslow -s t -t 10 /var/lib/mysql/slow_query.log

# 查看出现次数最多的10条慢SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow_query.log

更强大的工具是pt-query-digest（来自Percona Toolkit）：

# 生成慢查询分析报告
pt-query-digest /var/lib/mysql/slow_query.log

优化流程总结

1. 开启慢查询日志 → 发现慢SQL
2. 用EXPLAIN分析执行计划 → 定位问题
3. 检查索引设计 → 是否缺少索引/索引不合理
4. 改写SQL → 避免全表扫描/减少回表
5. 验证优化效果 → 再次EXPLAIN确认改善

⚠️ 常见误区 误区：只需要优化慢查询日志里的SQL。 慢查询日志只捕获超过阈值的SQL。但有些SQL单次执行很快（比如0.01秒），却在高并发下每秒被调用10000次，同样会造成严重的性能问题。这种情况需要通过performance_schema或应用层监控来发现。

六、Profile——查看SQL的时间花在哪里

EXPLAIN告诉你MySQL”打算怎么做”，Profile告诉你”实际做了多久”。

⚠️ 注意：SHOW PROFILE / SHOW PROFILES 自 MySQL 5.6 起已被标记为废弃（deprecated），未来版本可能移除。官方推荐使用 Performance Schema 的 events_stages_history 表替代（见本节末尾）。不过在当前版本中它仍然可用，且使用起来更简单直观。

-- 开启profiling
SET profiling = 1;

-- 执行你的查询
SELECT * FROM employees WHERE department = '技术部' AND salary > 15000;

-- 查看profile结果
SHOW PROFILES;

-- 查看最近一条查询的详细时间分布
SHOW PROFILE FOR QUERY 1;

输出类似：

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000015 |
| checking permissions           | 0.000004 |
| Opening tables                 | 0.000020 |
| init                           | 0.000018 |
| System lock                    | 0.000005 |
| optimizing                     | 0.000008 |
| statistics                     | 0.000050 |
| preparing                      | 0.000010 |
| executing                      | 0.000150 |
| Sending data                   | 0.002500 |  ← 主要时间花在这里
| end                            | 0.000005 |
| query end                      | 0.000003 |
| closing tables                 | 0.000005 |
| freeing items                  | 0.000010 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

Sending data阶段通常占大头——它包含了实际的数据读取和传输。需要注意的是，上述阶段名称可能因 MySQL 版本而异。例如，MySQL 8.0.17+ 将原来的 Sending data 拆分为更细粒度的阶段（如 executing），因此你在不同版本上看到的阶段列表可能有所不同。

Performance Schema 替代方案

如果你使用的是较新版本的 MySQL，可以通过 Performance Schema 获取同样的分阶段耗时信息：

-- 开启 stage 事件收集（如未开启）
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'stage/%';

UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME LIKE 'events_stages%';

-- 执行你的查询后，查看各阶段耗时
SELECT EVENT_NAME, TRUNCATE(TIMER_WAIT/1000000000000, 6) AS Duration_sec
FROM performance_schema.events_stages_history_long
ORDER BY EVENT_ID DESC LIMIT 20;

🤔 想一想 如果Profile显示一条查询在”Sending data”阶段花了很长时间，可能的原因有哪些？你应该怎样进一步诊断？

📝 掌握度自测

EXPLAIN输出中，type列的值从好到差排列是什么？达到什么级别以上才算可以接受？
Extra列中的Using index和Using index condition分别表示什么？
什么是索引条件下推（ICP）？它是如何减少I/O的？
为什么LIMIT 100000, 10很慢？请描述”延迟关联”优化的原理。
在生产环境中，你会使用哪些工具和手段来发现和诊断慢查询？

💡 自我评估

答对5题：恭喜，你已经具备了实际项目中SQL性能调优的能力

答对3-4题：核心工具掌握了，建议在真实项目中多实践EXPLAIN分析

答对0-2题：这章非常实用，建议反复阅读并在自己的数据库上动手练习每一个EXPLAIN示例