MySQL慢查询优化与EXPLAIN神探 🔍

一、开篇故事：寻找迷路的快递员 📦

想象你是一个物流中心的管理员，有个快递员送货特别慢：

场景1：找到慢快递员

你："为什么送得这么慢？"
快递员："我不知道路线，每次都乱走。"
你："好，我给你装个GPS，记录你的路线。"
  → 开启慢查询日志 📝

你："原来你绕了10公里远路！"
  → 发现问题SQL

场景2：分析路线问题

你："让我看看你的送货计划。"
快递员："我计划先去A区，再去B区..."
你："但实际上你先去了C区，还绕路了！"
  → EXPLAIN执行计划 🗺️

你："问题找到了：
  1. 没用导航（索引）
  2. 路线不对（全表扫描）
  3. 地图过时（统计信息）"

场景3：优化方案

你："给你装个导航，优化路线。"
快递员："现在10分钟就送完了！"
  → 优化后，性能提升10倍！✨

这就是MySQL慢查询优化的完整流程！

---

二、慢查询日志：抓住慢SQL 🎯

2.1 什么是慢查询日志？

慢查询日志（Slow Query Log） 记录执行时间超过阈值的SQL语句。

2.2 开启慢查询日志

-- 查看是否开启
SHOW VARIABLES LIKE 'slow_query_log';

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

-- 设置阈值（单位：秒）
SET GLOBAL long_query_time = 2; -- 超过2秒记录

-- 设置日志文件路径
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 记录没有使用索引的查询
SET GLOBAL log_queries_not_using_indexes = 'ON';

2.3 配置文件设置

# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

2.4 慢查询日志内容

# Time: 2025-01-15T10:30:00.123456Z
# User@Host: root[root] @ localhost []
# Query_time: 5.123456  Lock_time: 0.000123  Rows_sent: 1000  Rows_examined: 1000000
SET timestamp=1736936400;
SELECT * FROM users WHERE name = '张三';

字段说明：

• Query_time：查询耗时
• Lock_time：锁等待时间
• Rows_sent：返回行数
• Rows_examined：扫描行数（重点！）

---

三、EXPLAIN：SQL的X光片 📸

3.1 什么是EXPLAIN？

EXPLAIN 可以查看MySQL执行SQL的计划，不实际执行SQL。

3.2 基本用法

EXPLAIN SELECT * FROM users WHERE age = 25;

3.3 EXPLAIN输出字段详解

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

---

四、EXPLAIN各字段详解 🔍

4.1 id（执行顺序）

含义： SELECT的序号，表示执行顺序。

EXPLAIN 
SELECT * FROM users u
WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 1000
);

结果：

id=1: users表（外层查询）
id=2: orders表（子查询）

执行顺序：id越大越先执行
  → 先执行id=2（子查询）
  → 再执行id=1（外层查询）

规则：

• id相同：从上到下顺序执行
• id不同：id越大越先执行
• id为NULL：结果集（如UNION的结果）

---

4.2 select_type（查询类型）

常见类型：

select_type	含义	示例
SIMPLE	简单查询，不包含子查询或UNION	SELECT * FROM users
PRIMARY	最外层查询	子查询的外层
SUBQUERY	子查询	WHERE id IN (SELECT...)
DERIVED	派生表（FROM子句中的子查询）	FROM (SELECT...) t
UNION	UNION中的第二个或后面的SELECT	SELECT ... UNION SELECT...
UNION RESULT	UNION的结果	UNION后的合并结果

示例：

-- SIMPLE
EXPLAIN SELECT * FROM users WHERE id = 1;
-- select_type: SIMPLE

-- PRIMARY + SUBQUERY
EXPLAIN SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);
-- select_type: PRIMARY (users)
-- select_type: SUBQUERY (orders)

-- DERIVED
EXPLAIN SELECT * FROM (SELECT * FROM users WHERE age > 20) t;
-- select_type: PRIMARY (外层)
-- select_type: DERIVED (子查询)

---

4.3 type（访问类型）⭐⭐⭐⭐⭐

这是最重要的字段！性能从好到差：

system > const > eq_ref > ref > range > index > ALL
  ↑                                              ↑
 最好                                          最差

4.3.1 system（最优）

含义： 表只有一行记录（系统表）

EXPLAIN SELECT * FROM mysql.proxies_priv;
-- type: system

4.3.2 const（极优）

含义： 通过主键或唯一索引查询，最多返回1行

EXPLAIN SELECT * FROM users WHERE id = 1; -- 主键
-- type: const ✅✅✅

EXPLAIN SELECT * FROM users WHERE email = 'test@qq.com'; -- 唯一索引
-- type: const ✅✅✅

4.3.3 eq_ref（优）

含义： 多表连接时，通过主键或唯一索引关联

EXPLAIN 
SELECT * FROM orders o
LEFT JOIN users u ON o.user_id = u.id; -- u.id是主键
-- type: eq_ref (users表) ✅✅

4.3.4 ref（良好）

含义： 通过非唯一索引查询

EXPLAIN SELECT * FROM users WHERE age = 25; -- age有索引
-- type: ref ✅

EXPLAIN SELECT * FROM orders WHERE user_id = 100; -- user_id有索引
-- type: ref ✅

4.3.5 range（可接受）

含义： 范围查询（>、<、BETWEEN、IN）

EXPLAIN SELECT * FROM users WHERE id > 100 AND id < 1000;
-- type: range ⚠️

EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- type: range ⚠️

EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3, 4, 5);
-- type: range ⚠️

4.3.6 index（较差）

含义： 全索引扫描（扫描整个索引树）

EXPLAIN SELECT id FROM users; -- 只查主键
-- type: index ❌

-- 虽然用了索引，但扫描了所有索引记录

4.3.7 ALL（最差）

含义： 全表扫描

EXPLAIN SELECT * FROM users WHERE name = '张三'; -- name没索引
-- type: ALL 💀💀💀

-- 最慢！必须优化！

性能对比：

假设100万数据：

const:      查询1条，扫描1条    → 0.001秒 ✅
ref:        查询100条，扫描100条 → 0.01秒 ✅
range:      查询1000条，扫描1000条 → 0.05秒 ⚠️
index:      查询1条，扫描100万条 → 1秒 ❌
ALL:        查询1条，扫描100万条 → 5秒 💀

---

4.4 possible_keys（可能用到的索引）

含义： MySQL认为可能用到的索引列表

EXPLAIN SELECT * FROM users WHERE age = 25 OR city = '北京';
-- possible_keys: idx_age,idx_city（两个索引都可能用到）

---

4.5 key（实际使用的索引）⭐⭐⭐⭐

含义： MySQL实际选择的索引

EXPLAIN SELECT * FROM users WHERE age = 25;
-- key: idx_age（实际使用idx_age索引）✅

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- key: NULL（没有使用索引）❌

关键：

• key = NULL：没用索引，性能差！
• key != NULL：用了索引，性能好！

---

4.6 key_len（索引长度）

含义： 使用的索引字节数（越短越好）

-- 索引：idx_age_city (age, city)
EXPLAIN SELECT * FROM users WHERE age = 25;
-- key_len: 5（只用了age，INT=4字节+1字节NULL标志）

EXPLAIN SELECT * FROM users WHERE age = 25 AND city = '北京';
-- key_len: 155（用了age+city，INT=4 + VARCHAR(50)=150 + NULL标志）

计算规则：

INT:        4字节
BIGINT:     8字节
VARCHAR(N): N*3（UTF8）+ 2（长度）+ 1（NULL）
CHAR(N):    N*3（UTF8）+ 1（NULL）
DATE:       3字节
DATETIME:   8字节

---

4.7 ref（索引比较的列）

含义： 显示索引的哪一列被使用了

EXPLAIN SELECT * FROM users WHERE age = 25;
-- ref: const（常量25）

EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id;
-- ref: test.o.user_id（orders表的user_id列）

---

4.8 rows（扫描行数）⭐⭐⭐⭐⭐

含义： 预计扫描的行数（越少越好！）

EXPLAIN SELECT * FROM users WHERE id = 1;
-- rows: 1 ✅✅✅

EXPLAIN SELECT * FROM users WHERE age = 25;
-- rows: 1000 ⚠️

EXPLAIN SELECT * FROM users WHERE name = '张三';
-- rows: 1000000 💀（全表扫描）

关键指标：

rows < 100:     性能很好 ✅
rows < 10000:   性能尚可 ⚠️
rows > 100000:  性能很差 ❌
rows = 全表:    必须优化 💀

---

4.9 Extra（额外信息）⭐⭐⭐⭐⭐

这是重要的性能提示！

4.9.1 Using index（最好）

含义： 索引覆盖，不需要回表

-- 索引：idx_age (age)
EXPLAIN SELECT age FROM users WHERE age = 25;
-- Extra: Using index ✅✅✅

-- 只查age，索引中就有，不需要回表

4.9.2 Using where

含义： 使用WHERE过滤

EXPLAIN SELECT * FROM users WHERE age > 20;
-- Extra: Using where

4.9.3 Using index condition（好）

含义： 索引下推（Index Condition Pushdown）

-- 索引：idx_age_name (age, name)
EXPLAIN SELECT * FROM users WHERE age > 20 AND name LIKE '张%';
-- Extra: Using index condition ✅

-- 在索引层面就过滤掉不符合的数据

4.9.4 Using filesort（差）

含义： 需要额外排序，性能差

EXPLAIN SELECT * FROM users WHERE age = 25 ORDER BY name;
-- Extra: Using filesort ❌

-- name没有索引，需要额外排序

优化： 在name上建索引

4.9.5 Using temporary（很差）

含义： 需要创建临时表

EXPLAIN SELECT DISTINCT name FROM users WHERE age = 25;
-- Extra: Using temporary ❌❌

EXPLAIN SELECT name, COUNT(*) FROM users GROUP BY name;
-- Extra: Using temporary ❌❌

优化： 在分组/去重字段上建索引

4.9.6 Using join buffer（差）

含义： 连接时使用了缓冲区（没用索引）

EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_name = u.name;
-- Extra: Using join buffer ❌

-- user_name没有索引，无法有效JOIN

优化： 在JOIN字段上建索引

---

五、慢查询优化思路 💡

5.1 优化流程图

发现慢查询
    ↓
EXPLAIN分析
    ↓
识别问题
    ↓
┌─────────────────────┐
│ type=ALL?           │ → 是 → 加索引
├─────────────────────┤
│ key=NULL?           │ → 是 → 加索引
├─────────────────────┤
│ rows很大?           │ → 是 → 优化WHERE条件
├─────────────────────┤
│ Extra有filesort?    │ → 是 → 加排序索引
├─────────────────────┤
│ Extra有temporary?   │ → 是 → 加分组索引
└─────────────────────┘
    ↓
实施优化
    ↓
再次EXPLAIN验证

---

5.2 优化技巧汇总

技巧1：避免全表扫描

-- ❌ 不好：全表扫描
SELECT * FROM users WHERE name = '张三';
-- type: ALL, rows: 1000000

-- ✅ 好：加索引
CREATE INDEX idx_name ON users(name);
-- type: ref, rows: 100

技巧2：避免索引失效

-- ❌ 不好：函数导致索引失效
SELECT * FROM users WHERE YEAR(birthday) = 1990;
-- type: ALL

-- ✅ 好：改写SQL
SELECT * FROM users WHERE birthday BETWEEN '1990-01-01' AND '1990-12-31';
-- type: range


-- ❌ 不好：隐式转换
SELECT * FROM users WHERE phone = 13800138000; -- phone是VARCHAR
-- type: ALL

-- ✅ 好：加引号
SELECT * FROM users WHERE phone = '13800138000';
-- type: ref


-- ❌ 不好：左模糊
SELECT * FROM users WHERE name LIKE '%张三';
-- type: ALL

-- ✅ 好：右模糊
SELECT * FROM users WHERE name LIKE '张三%';
-- type: range

技巧3：使用索引覆盖

-- ❌ 不好：需要回表
SELECT * FROM users WHERE age = 25;
-- Extra: NULL

-- ✅ 好：索引覆盖
SELECT id, age FROM users WHERE age = 25;
-- Extra: Using index

技巧4：优化排序

-- ❌ 不好：filesort
SELECT * FROM users WHERE age = 25 ORDER BY create_time;
-- Extra: Using filesort

-- ✅ 好：加索引
CREATE INDEX idx_age_time ON users(age, create_time);
-- Extra: Using index

技巧5：优化分页

-- ❌ 不好：深分页，回表很多
SELECT * FROM users ORDER BY id LIMIT 100000, 10;
-- 需要回表100010次

-- ✅ 好：子查询优化
SELECT * FROM users WHERE id >= (
    SELECT id FROM users ORDER BY id LIMIT 100000, 1
) LIMIT 10;
-- 子查询索引覆盖，只回表10次

技巧6：优化JOIN

-- ❌ 不好：没有索引
SELECT * FROM orders o 
LEFT JOIN users u ON o.user_name = u.name;
-- Extra: Using join buffer

-- ✅ 好：在JOIN字段加索引
CREATE INDEX idx_name ON users(name);
-- Extra: NULL（用了索引）

---

六、实战案例：电商订单查询优化 💼

案例背景

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50),
    user_id BIGINT,
    product_id BIGINT,
    amount DECIMAL(10,2),
    status TINYINT,
    create_time DATETIME,
    update_time DATETIME
);

-- 1000万订单数据

问题SQL

SELECT * FROM orders 
WHERE create_time >= '2024-01-01' 
  AND status = 1
ORDER BY create_time DESC
LIMIT 10;

步骤1：EXPLAIN分析

EXPLAIN SELECT * FROM orders ...;

结果：
id: 1
type: ALL 💀
key: NULL 💀
rows: 10000000 💀
Extra: Using where; Using filesort 💀

问题：
1. type=ALL：全表扫描
2. key=NULL：没用索引
3. rows=10000000：扫描全表
4. Using filesort：额外排序

步骤2：优化方案

-- 创建联合索引
CREATE INDEX idx_status_time ON orders(status, create_time);

步骤3：再次EXPLAIN

EXPLAIN SELECT * FROM orders ...;

结果：
id: 1
type: range ✅
key: idx_status_time ✅
rows: 1000 ✅
Extra: Using index condition ✅

改进：
1. type=range：范围查询
2. key=idx_status_time：用了索引
3. rows=1000：只扫描1000行
4. 没有filesort：索引已排序

性能提升：
  查询时间：5秒 → 0.05秒
  提升：100倍！🚀

---

七、慢查询分析工具 🛠️

7.1 mysqldumpslow

MySQL自带的慢查询分析工具

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

# 查看访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 查看返回记录最多的10条SQL
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

7.2 pt-query-digest

Percona Toolkit工具，更强大

# 安装
yum install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 输出报告
# 1. 总体统计
# 2. 最慢的SQL
# 3. 执行次数最多的SQL
# 4. 详细的EXPLAIN分析

7.3 SHOW PROFILE

查看SQL执行的详细过程

-- 开启profiling
SET profiling = 1;

-- 执行SQL
SELECT * FROM users WHERE age = 25;

-- 查看所有profile
SHOW PROFILES;

-- 查看具体某个SQL的详细信息
SHOW PROFILE FOR QUERY 1;

-- 查看CPU、IO等信息
SHOW PROFILE CPU, BLOCK IO FOR QUERY 1;

---

八、常见慢查询场景与优化 🎯

场景1：深分页

-- ❌ 慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;
-- 需要扫描1000010行，回表1000010次

-- ✅ 快
SELECT * FROM orders 
WHERE id > (SELECT id FROM orders ORDER BY id LIMIT 1000000, 1)
LIMIT 10;
-- 子查询索引覆盖，只回表10次

场景2：COUNT(*)统计

-- ❌ 慢
SELECT COUNT(*) FROM orders WHERE status = 1;
-- 全表扫描

-- ✅ 快
-- 在status上建索引
CREATE INDEX idx_status ON orders(status);
-- 扫描索引（比扫描数据快）

场景3：大表JOIN

-- ❌ 慢
SELECT * FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.create_time > '2024-01-01';
-- orders表很大，全表扫描

-- ✅ 快
-- 先过滤再JOIN
SELECT * FROM (
    SELECT * FROM orders WHERE create_time > '2024-01-01'
) o
LEFT JOIN users u ON o.user_id = u.id;
-- 先过滤orders，减少JOIN数据量

场景4：IN子查询

-- ❌ 慢
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 1000
);
-- 子查询可能全表扫描

-- ✅ 快
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;
-- 用JOIN代替IN

---

九、面试高频问题 🎤

Q1: EXPLAIN的type字段哪些值性能最好？

答： 性能从好到差：

1. system / const：最优
2. eq_ref：优
3. ref：良好
4. range：可接受
5. index：较差
6. ALL：最差（必须优化）

Q2: Extra字段出现哪些值需要优化？

答： 需要优化的：

• Using filesort：需要额外排序，加排序索引
• Using temporary：需要临时表，加分组索引
• Using join buffer：JOIN没用索引，加索引

好的：

• Using index：索引覆盖，性能最好

Q3: 如何优化ORDER BY？

答：

1. 在排序字段上建索引
2. 使用联合索引（WHERE字段+ORDER BY字段）
3. 利用索引的有序性，避免filesort

Q4: rows很大但查询还是快，为什么？

答： 可能是：

1. 索引覆盖（不需要回表）
2. 只查索引列（Using index）
3. 数据在内存中（Buffer Pool命中）

Q5: 如何优化深分页？

答：

1. 子查询先查ID，再回表
2. 使用游标分页（记录上次的最大ID）
3. 使用ElasticSearch等搜索引擎

---

十、最佳实践 💡

1. 定期分析慢查询日志

# 每天分析一次
crontab -e
0 2 * * * pt-query-digest /var/log/mysql/slow.log > /tmp/slow_$(date +%Y%m%d).txt

2. 监控关键指标

-- 查询执行最慢的SQL
SELECT * FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- 查询没用索引的SQL
SELECT * FROM mysql.slow_log WHERE sql_text NOT LIKE '%INDEX%' LIMIT 10;

3. 强制索引提示

-- 建议MySQL使用某个索引
SELECT * FROM users USE INDEX(idx_age) WHERE age = 25;

-- 强制使用某个索引
SELECT * FROM users FORCE INDEX(idx_age) WHERE age = 25;

-- 忽略某个索引
SELECT * FROM users IGNORE INDEX(idx_age) WHERE age = 25;

4. 定期更新统计信息

-- 分析表，更新统计信息
ANALYZE TABLE users;

-- 优化表，整理碎片
OPTIMIZE TABLE users;

---

十一、总结口诀 📝

慢查询优化有妙招，
EXPLAIN分析很重要。
type字段看类型，
ALL最差要避免。

key字段看索引，
NULL就是没用到。
rows字段看扫描，
越小越好记得牢。

Extra信息要关注，
filesort要优化掉。
temporary也不好，
索引建好都搞定。

索引覆盖最优秀，
Using index是目标。
深分页要优化，
子查询来帮忙。

定期分析慢日志，
问题SQL早发现。
索引建好维护好，
性能优化不用愁！

---

参考资料 📚

• MySQL官方文档 - EXPLAIN
• MySQL官方文档 - Slow Query Log
• 高性能MySQL（第3版）

---

下期预告： 142-为什么MySQL单表建议不超过2000万行？大表如何优化？📊

---

编写时间：2025年
作者：技术文档小助手 ✍️
版本：v1.0

愿你的SQL永远飞快！ ⚡🚀