MySQL 查询优化——EXPLAIN 解读、慢查询、优化器、JOIN 策略

更新时间：2026-09-02。本文是数据库域第三篇，接 MySQL 锁与事务。索引解决了"怎么建"的问题，锁与事务解决了"怎么并发"的问题，查询优化解决的是"怎么查得快"的问题——三个文档构成 MySQL 核心知识三角。

本文要回答的问题

EXPLAIN 输出的每一列代表什么？type、rows、Extra 怎么判断性能？
慢查询日志怎么配置？怎么分析慢 SQL？
优化器怎么选择索引？什么时候走错索引？
JOIN 的三种实现方式（NLJ/BNL/Hash Join）有什么区别？
哪些情况索引会失效？
常见 SQL 怎么改写才快？

一、EXPLAIN 详解

EXPLAIN 是 MySQL 查询优化的第一工具，输出 SQL 的执行计划。

1. 基本用法

sql

EXPLAIN SELECT * FROM users WHERE id = 1;
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;  -- 更详细
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;      -- 实际执行，MySQL 8.0.18+

2. 输出列解读

列名	含义	关键点
id	查询序号	越大越先执行，相同则从上到下
select_type	查询类型	SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION
table	表名	实际表名或别名
partitions	分区	命中的分区
type	访问类型	最重要的列，从好到差见下表
possible_keys	可能使用的索引	候选索引列表
key	实际使用的索引	实际选中的索引
key_len	索引长度	越长说明用了越多索引列
ref	索引等值匹配	和什么值比较
rows	扫描行数估计	越小越好，估计值
filtered	过滤比例	100% 最好，越低越好
Extra	额外信息	关键信息列

3. type 访问类型（从好到差）

性能从好到差：
system → const → eq_ref → ref → range → index → ALL

system：表只有一行（系统表）
const：主键或唯一索引等值查询
  SELECT * FROM users WHERE id = 1;  -- const
  → 最多返回一行，MySQL 把该行当常量处理

eq_ref：JOIN 时，被驱动表用主键/唯一索引等值匹配
  SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
  → orders 表用 user_id 索引，每个用户只匹配一次

ref：普通索引等值查询
  SELECT * FROM users WHERE status = 'active';  -- ref（status 有索引）
  → 返回多行，但走索引

range：索引范围扫描
  SELECT * FROM users WHERE id > 100;           -- range
  SELECT * FROM users WHERE name LIKE '张%';    -- range（前缀匹配）
  → 比 ref 差，但比 index 好

index：索引全扫描（遍历整个索引树）
  SELECT COUNT(*) FROM users;                   -- index（辅助索引比主键小）
  → 遍历索引树，比 ALL 好一点（索引比数据小）

ALL：全表扫描（最差）
  SELECT * FROM users WHERE email = 'a@b.com';  -- ALL（email 无索引）
  → 遍历整个表，应该避免

4. Extra 关键信息

Extra 信息	含义	建议
Using index	覆盖索引，不回表	✅ 好
Using where	回表后过滤	还可以
Using index condition	索引条件下推（ICP）	✅ 好，减少回表
Using filesort	文件排序（无法用索引排序）	❌ 尽量优化
Using temporary	使用了临时表	❌ 尽量优化
Using join buffer (Block Nested Loop)	JOIN 未用索引，用了 BNL	❌ 加索引
Using where; Using index	覆盖索引且有过滤条件	✅ 好
Impossible WHERE	WHERE 条件永远为假	检查 SQL

sql

-- Using filesort 示例（需要优化）
EXPLAIN SELECT * FROM users ORDER BY create_time;
-- Extra: Using filesort

-- 优化：加索引
ALTER TABLE users ADD INDEX idx_create_time(create_time);
-- 再次 EXPLAIN，Extra 不再有 filesort

-- Using temporary 示例
EXPLAIN SELECT status, COUNT(*) FROM users GROUP BY status;
-- Extra: Using temporary; Using filesort

-- 优化：加索引
ALTER TABLE users ADD INDEX idx_status(status);
-- 再次 EXPLAIN，Extra 变为 Using index

二、慢查询日志

1. 配置慢查询

sql

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志（当前会话）
SET GLOBAL slow_query_log = 1;

-- 设置慢查询阈值（秒）
SET GLOBAL long_query_time = 1;  -- 超过 1 秒记录

-- 设置慢查询日志路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 记录未使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 1;

2. 使用 mysqldumpslow 分析

bash

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

# 按平均查询时间排序
mysqldumpslow -s t /var/log/mysql/slow.log

# 按锁定时间排序
mysqldumpslow -s l /var/log/mysql/slow.log

# 只显示前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 输出带时间戳
mysqldumpslow -s t -t 10 -a /var/log/mysql/slow.log

3. 使用 pt-query-digest 分析

bash

# 安装 Percona Toolkit
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

# 分析实时 MySQL 查询
pt-query-digest --processlist h=localhost

# 生成报告到文件
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

4. performance_schema 查询慢 SQL

sql

-- 使用 performance_schema 查询慢 SQL（MySQL 5.7+）
SELECT 
  DIGEST_TEXT,
  COUNT_STAR,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
  SUM_TIMER_WAIT / 1000000000 AS total_time_ms,
  AVG_TIMER_WAIT / 1000000000 AS avg_time_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_TIMER_WAIT > 1000000000000  -- 总时间 > 1 秒
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

三、优化器工作原理

1. 优化器做什么

MySQL 优化器（Optimizer）的决策流程：

SQL 语句
  ↓
解析器 → 语法树
  ↓
预处理器 → 语义检查
  ↓
优化器 ← 统计信息（cardinality、索引分布）
  │
  ├── 逻辑优化：子查询优化、谓词下推、等价变换
  └── 物理优化：选择索引、选择 JOIN 顺序、选择 JOIN 算法
  ↓
执行计划
  ↓
执行器

2. 统计信息

sql

-- 查看表的统计信息
SHOW TABLE STATUS LIKE 'users';

-- 查看索引统计信息
SHOW INDEX FROM users;

-- 查看索引基数（Cardinality）
-- Cardinality 越大，索引选择性越好
SELECT 
  INDEX_NAME,
  CARDINALITY,
  TABLE_ROWS,
  CARDINALITY / TABLE_ROWS AS selectivity
FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'users';

-- 更新统计信息
ANALYZE TABLE users;

3. 优化器选错索引怎么办

sql

-- 场景：优化器选了错误的索引

-- 方法 1：使用 FORCE INDEX 强制指定索引
SELECT * FROM users FORCE INDEX(idx_create_time)
WHERE create_time > '2026-01-01' AND status = 'active';

-- 方法 2：使用 USE INDEX 建议索引
SELECT * FROM users USE INDEX(idx_status)
WHERE create_time > '2026-01-01' AND status = 'active';

-- 方法 3：使用 IGNORE INDEX 忽略索引
SELECT * FROM users IGNORE INDEX(idx_status)
WHERE create_time > '2026-01-01' AND status = 'active';

-- 方法 4：更新统计信息
ANALYZE TABLE users;

-- 方法 5：重建索引
ALTER TABLE users DROP INDEX idx_status, ADD INDEX idx_status(status);

4. 优化器跟踪

sql

-- 开启优化器跟踪（MySQL 5.6+）
SET optimizer_trace = "enabled=on";
SET optimizer_trace_max_mem_size = 1000000;

-- 执行查询
SELECT * FROM users WHERE id > 100 AND status = 'active';

-- 查看优化器决策过程
SELECT * FROM information_schema.OPTIMIZER_TRACE\G
-- 输出包含：
--   - 索引选择原因
--   - 成本估算
--   - 为什么选这个索引而不是那个

-- 关闭优化器跟踪
SET optimizer_trace = "enabled=off";

四、JOIN 策略

1. JOIN 的三种实现

JOIN 算法	原理	条件	特点
Nested Loop Join（NLJ）	外层循环逐行匹配内层	被驱动表有索引	小表驱动大表，适合小结果集
Block Nested Loop（BNL）	外层批量读入 join buffer，和内层全表扫描匹配	被驱动表无索引	比 NLJ 好，但还是要扫描全表
Hash Join（MySQL 8.0.18+）	外层建哈希表，内层逐行探测	无索引或不适合 NLJ	大表 JOIN 大表的最优解

2. Nested Loop Join（NLJ）

NLJ 执行流程（被驱动表有索引）：

外层表（驱动表）  →  内层表（被驱动表）
  rows=100            rows=10000（有索引）
  
流程：
1. 读驱动表 1 行
2. 根据关联字段，走索引查被驱动表
3. 重复 1-2，直到驱动表取完

成本：100 + 100 × log₂(10000) ≈ 100 + 100 × 14 = 1500

sql

EXPLAIN SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- users 表访问类型：ALL（全表扫描，驱动表）
-- orders 表访问类型：ref（通过 user_id 索引连接）
-- Extra: 无 "Using join buffer"

3. Block Nested Loop（BNL）

BNL 执行流程（被驱动表无索引）：

外层表（驱动表）  →  内层表（被驱动表）
  rows=100            rows=10000（无索引）
  
流程：
1. 把驱动表 100 行读入 join_buffer
2. 全表扫描被驱动表，每行和 join_buffer 匹配
3. 只扫描一次被驱动表

成本：100 + 10000 = 10100（比 NLJ 的 100 + 100×10000 好很多）

sql

EXPLAIN SELECT * FROM users u JOIN orders o ON u.name = o.order_no;
-- users 表访问类型：ALL
-- orders 表访问类型：ALL
-- Extra: Using where; Using join buffer (Block Nested Loop)

4. Hash Join（MySQL 8.0.18+）

Hash Join 执行流程（MySQL 8.0.18+，被驱动表无索引且大表）：

外层表（驱动表）  →  内层表（被驱动表）
  rows=10000          rows=100000（无索引）
  
流程：
1. 把驱动表 10000 行的关联字段建哈希表
2. 全表扫描被驱动表，每行在哈希表探测
3. 只扫描一次被驱动表

成本：10000 + 100000 = 110000
比 BNL 好，因为哈希查找是 O(1)，而 BNL 是 O(n²)

sql

EXPLAIN FORMAT=JSON SELECT * FROM users u JOIN orders o ON u.name = o.order_no;
-- 在 MySQL 8.0.18+ 中，会显示使用 "hash join"

-- 优化器提示：强制使用 Hash Join
SELECT /*+ HASH_JOIN(users orders) */ *
FROM users u JOIN orders o ON u.name = o.order_no;

5. JOIN 优化原则

JOIN 优化四条原则：

1. 小表驱动大表
   ✅ 对：SELECT * FROM small s JOIN big b ON s.id = b.id;
   ❌ 错：SELECT * FROM big b JOIN small s ON b.id = s.id;
   → 优化器通常会自动选择，但复杂查询可能选错

2. 被驱动表关联字段加索引
   ✅ 对：orders.user_id 建索引
   → 让 JOIN 走 NLJ，不走 BNL/Hash Join

3. 避免 SELECT *，只取需要的列
   → join_buffer 能放更多行，减少扫描次数

4. 大表 JOIN 大表考虑分拆
   → 先过滤再 JOIN，或走应用层合并

五、索引失效场景

1. 最常见的索引失效

sql

-- 假设有索引：idx_name_age(name, age)

-- 场景 1：违反最左前缀
-- ✅ 走索引
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 25;
-- ❌ 不走索引（跳过了 name）
SELECT * FROM users WHERE age = 25;

-- 场景 2：范围查询右边的列失效
-- ✅ 走索引（name 等值 + age 范围，但 age 之后失效）
SELECT * FROM users WHERE name = '张三' AND age > 25 AND status = 1;
-- name 走索引，age 走 range，status 不走索引

-- 场景 3：对索引列做运算
-- ❌ 不走索引
SELECT * FROM users WHERE id + 1 = 100;
SELECT * FROM users WHERE YEAR(create_time) = 2026;
-- ✅ 改写
SELECT * FROM users WHERE id = 99;
SELECT * FROM users WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';

-- 场景 4：隐式类型转换
-- 假设 name 是 varchar
-- ❌ 不走索引
SELECT * FROM users WHERE name = 123;  -- 类型转换，索引失效
-- ✅ 走索引
SELECT * FROM users WHERE name = '123';

-- 场景 5：LIKE 以通配符开头
-- ❌ 不走索引
SELECT * FROM users WHERE name LIKE '%张%';
-- ✅ 走索引（前缀匹配）
SELECT * FROM users WHERE name LIKE '张%';

-- 场景 6：OR 条件中有非索引列
-- 假设 name 有索引，status 无索引
-- ❌ 不走索引
SELECT * FROM users WHERE name = '张三' OR status = 1;
-- ✅ 改写为 UNION
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE status = 1;
-- 或在 status 上加索引

-- 场景 7：NOT IN / NOT EXISTS / <>
-- ❌ 通常不走索引
SELECT * FROM users WHERE id <> 100;
SELECT * FROM users WHERE id NOT IN (1, 2, 3);
-- ✅ 改写成范围
SELECT * FROM users WHERE id < 100 OR id > 100;

2. 索引失效检查方法

sql

-- 用 EXPLAIN 检查是否走索引
EXPLAIN SELECT * FROM users WHERE YEAR(create_time) = 2026;
-- 看 key 列是否 NULL，type 是否 ALL

-- 用 FORMAT=JSON 看成本
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE YEAR(create_time) = 2026;
-- 看 "access_type" 是否是 "ALL"

六、SQL 改写技巧

1. 分页优化

sql

-- ❌ 深分页：LIMIT 100000, 20 需要扫描 100020 行
SELECT * FROM users ORDER BY id LIMIT 100000, 20;

-- ✅ 优化 1：子查询先拿 ID，再 JOIN
SELECT * FROM users
WHERE id > (SELECT id FROM users ORDER BY id LIMIT 100000, 1)
ORDER BY id
LIMIT 20;

-- ✅ 优化 2：记录上次位置（推荐）
SELECT * FROM users WHERE id > 100000 ORDER BY id LIMIT 20;
-- 适合"加载更多"场景，不适合随机跳页

2. COUNT 优化

sql

-- ❌ COUNT 非索引列
SELECT COUNT(*) FROM users;  -- 会走最小索引（MyISAM 有缓存，InnoDB 需要扫索引）

-- InnoDB 下 COUNT(*) 会走最小索引树
-- 如果辅助索引比主键小，走辅助索引更快

-- 优化：建一个非常小的索引
ALTER TABLE users ADD INDEX idx_count(id);  -- 但主键本来就是索引
-- 更好的做法：用二级索引
ALTER TABLE users ADD INDEX idx_status(status);
-- COUNT(*) 会走这个索引，因为二级索引树比主键索引树小

-- 近似值优化
-- 如果不需要精确值，用 SHOW TABLE STATUS
SHOW TABLE STATUS LIKE 'users';
-- 返回 rows 列是估计值（不精确，但很快）

3. 子查询优化

sql

-- ❌ 子查询（MySQL 5.7 前可能性能差）
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- ✅ 改写为 JOIN（MySQL 5.7+ 优化器会自动优化，但复杂的还是手动改）
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

-- ❌ EXISTS 子查询（有些版本优化不好）
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);

-- ✅ JOIN 改写同上

4. UNION 优化

sql

-- ❌ UNION（默认去重，有额外开销）
SELECT * FROM users WHERE status = 1
UNION
SELECT * FROM users WHERE status = 2;

-- ✅ UNION ALL（不需要去重时）
SELECT * FROM users WHERE status = 1
UNION ALL
SELECT * FROM users WHERE status = 2;
-- UNION ALL 不会去重，少一次排序

-- ✅ 或者用 IN
SELECT * FROM users WHERE status IN (1, 2);

5. INSERT 优化

sql

-- ❌ 逐条插入
INSERT INTO users VALUES (1, 'a');
INSERT INTO users VALUES (2, 'b');
INSERT INTO users VALUES (3, 'c');

-- ✅ 批量插入
INSERT INTO users VALUES (1, 'a'), (2, 'b'), (3, 'c');
-- 批量提交减少事务和日志开销

-- ✅ 大批量数据用 LOAD DATA
LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
-- LOAD DATA 比 INSERT 快 10-20 倍

七、常见坑对照

坑	现象	原因	对策
索引没走	查询慢，EXPLAIN 显示 ALL	索引失效或选择性差	检查索引失效场景，加 FORCE INDEX
深分页慢	第 100 页比第 1 页慢很多	LIMIT 100000, 20 要扫 100020 行	用子查询或游标分页
JOIN 慢	查询卡住	被驱动表无索引，用 BNL	加索引，让 JOIN 走 NLJ
统计信息旧	优化器选错索引	表的统计信息没更新	ANALYZE TABLE
隐式转换	索引没走	字符串列查数字	检查类型，统一用引号
大事务	锁等待超时	一次处理太多数据	分批处理，缩小事务

一句话总结

MySQL 查询优化：EXPLAIN 看 type（const → eq_ref → ref → range → index → ALL，ALL 要避免）和 Extra（filesort/temporary 要优化）；慢查询用 mysqldumpslow 或 pt-query-digest 分析；JOIN 优化优先让被驱动表有索引走 NLJ，大表无索引用 MySQL 8.0 的 Hash Join；索引失效六大场景（最左前缀、运算、类型转换、LIKE 前缀、OR、NOT IN）用 EXPLAIN 验证；深分页用子查询优化，批量 INSERT 用多值或 LOAD DATA。

数据库索引

关系型（MySQL/PG）

MySQL 查询优化——EXPLAIN 解读、慢查询、优化器、JOIN 策略

本文要回答的问题

一、EXPLAIN 详解

1. 基本用法

2. 输出列解读

3. type 访问类型（从好到差）

4. Extra 关键信息

二、慢查询日志

1. 配置慢查询

2. 使用 mysqldumpslow 分析

3. 使用 pt-query-digest 分析

4. performance_schema 查询慢 SQL

三、优化器工作原理

1. 优化器做什么

2. 统计信息

3. 优化器选错索引怎么办

4. 优化器跟踪

四、JOIN 策略

1. JOIN 的三种实现

2. Nested Loop Join（NLJ）

3. Block Nested Loop（BNL）

4. Hash Join（MySQL 8.0.18+）

5. JOIN 优化原则

五、索引失效场景

1. 最常见的索引失效

2. 索引失效检查方法

六、SQL 改写技巧

1. 分页优化

2. COUNT 优化

3. 子查询优化

4. UNION 优化

5. INSERT 优化

七、常见坑对照

相关与延伸

一句话总结

关系型（MySQL/PG）

MySQL 查询优化——EXPLAIN 解读、慢查询、优化器、JOIN 策略 ​

本文要回答的问题 ​

一、EXPLAIN 详解 ​

1. 基本用法 ​

2. 输出列解读 ​

3. type 访问类型（从好到差） ​

4. Extra 关键信息 ​

二、慢查询日志 ​

1. 配置慢查询 ​

2. 使用 mysqldumpslow 分析 ​

3. 使用 pt-query-digest 分析 ​

4. performance_schema 查询慢 SQL ​

三、优化器工作原理 ​

1. 优化器做什么 ​

2. 统计信息 ​

3. 优化器选错索引怎么办 ​

4. 优化器跟踪 ​

四、JOIN 策略 ​

1. JOIN 的三种实现 ​

2. Nested Loop Join（NLJ） ​

3. Block Nested Loop（BNL） ​

4. Hash Join（MySQL 8.0.18+） ​

5. JOIN 优化原则 ​

五、索引失效场景 ​

1. 最常见的索引失效 ​

2. 索引失效检查方法 ​

六、SQL 改写技巧 ​

1. 分页优化 ​

2. COUNT 优化 ​

3. 子查询优化 ​

4. UNION 优化 ​

5. INSERT 优化 ​

七、常见坑对照 ​

相关与延伸 ​

一句话总结 ​

MySQL 查询优化——EXPLAIN 解读、慢查询、优化器、JOIN 策略

本文要回答的问题

一、EXPLAIN 详解

1. 基本用法

2. 输出列解读

3. type 访问类型（从好到差）

4. Extra 关键信息

二、慢查询日志

1. 配置慢查询

2. 使用 mysqldumpslow 分析

3. 使用 pt-query-digest 分析

4. performance_schema 查询慢 SQL

三、优化器工作原理

1. 优化器做什么

2. 统计信息

3. 优化器选错索引怎么办

4. 优化器跟踪

四、JOIN 策略

1. JOIN 的三种实现

2. Nested Loop Join（NLJ）

3. Block Nested Loop（BNL）

4. Hash Join（MySQL 8.0.18+）

5. JOIN 优化原则

五、索引失效场景

1. 最常见的索引失效

2. 索引失效检查方法

六、SQL 改写技巧

1. 分页优化

2. COUNT 优化

3. 子查询优化

4. UNION 优化

5. INSERT 优化

七、常见坑对照

相关与延伸

一句话总结