我刚工作那会儿，线上有个接口平均响应时间 5 秒，用户投诉电话被打爆。DBA 帮我一看慢查询日志，发现一条 SQL 扫描了 2000 万行数据，还没走索引。

从那以后，我每次上线前都会检查慢查询日志，确保没有性能隐患。

今天咱们就来聊聊 MySQL 慢查询优化，看完这篇，你就能自己定位和优化慢 SQL 了。

什么是慢查询？

慢查询（Slow Query） 指的是执行时间超过某个阈值的 SQL 语句。MySQL 可以记录这些慢查询，方便我们后续分析。

开启慢查询日志

-- 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';

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

-- 设置慢查询阈值（单位：秒，默认 10 秒）
SET GLOBAL long_query_time = 1;

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

注意：修改全局变量需要 SUPER 权限，并且重启后会失效。如果想永久生效，得修改配置文件 my.cnf：

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

模拟慢查询

-- 故意让查询变慢（睡眠 5 秒）
SELECT SLEEP(5);

-- 或者全表扫描大表
SELECT * FROM users;

执行后，慢查询会被记录到日志文件里。

分析慢查询日志

慢查询日志是纯文本文件，直接看会很痛苦。MySQL 提供了 mysqldumpslow 工具来分析。

使用 mysqldumpslow

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

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

# 按锁时间排序，查看最慢的 10 条
mysqldumpslow -s l -t 10 /var/lib/mysql/slow.log

参数说明：

• -s：排序方式（t=查询时间，c=执行次数，l=锁时间）
• • -t：显示前 N 条
• • -a：不把数字和字符串抽象成 N 和 S（默认会抽象，方便归类）

慢查询日志示例

# Time: 2024-01-15T10:30:00.123456Z
# User@Host: root[root] @ localhost []  Id:    10
# Query_time: 5.123456  Lock_time: 0.000123  Rows_sent: 100  Rows_examined: 20000000
SET timestamp=1705312200;
SELECT * FROM users WHERE age = 25;

关键信息：

• Query_time：查询执行时间（5.12 秒）
• • Lock_time：锁等待时间（0.000123 秒）
• • Rows_sent：返回给客户端的行数（100 行）
• • Rows_examined：扫描的行数（2000 万行！）
    问题很明显：扫描了 2000 万行，但只返回 100 行，说明没走索引。

优化慢查询的实战步骤

咱们来个实际案例：假设有个订单表，查询很慢。

第 1 步：复现慢查询

-- 执行慢查询
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 10;

第 2 步：看执行计划（EXPLAIN）

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 10;

输出：

+----+-------------+--------+------+---------------+------+---------+------+----------+----------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows     | Extra                            |
+----+-------------+--------+------+---------------+------+---------+------+----------+----------------------------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 20000000 | Using where; Using filesort      |
+----+-------------+--------+------+---------------+------+---------+------+----------+----------------------------------+

问题：

1. type = ALL：全表扫描（最差）
2. 2. rows = 20000000：扫描 2000 万行
3. 3. Extra = Using filesort：文件排序（性能差）

第 3 步：加索引

-- 给 user_id 和 status 加联合索引
CREATE INDEX idx_user_status ON orders(user_id, status);

再加索引后，看看执行计划：

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 10;

输出：

+----+-------------+--------+------+-----------------+-----------------+---------+-------------+------+----------+----------------------------------+
| id | select_type | table  | type | possible_keys   | key             | key_len | ref         | rows | Extra                            |
+----+-------------+--------+------+---------------+------+---------+-------------+------+----------+----------------------------------+
|  1 | SIMPLE      | orders | ref  | idx_user_status | idx_user_status | 10      | const,const | 100  | Using where; Using filesort      |
+----+-------------+--------+------+---------------+------+---------+-------------+------+----------+----------------------------------+

优化效果：

1. type = ref：用了索引（从 ALL 到 ref，质的飞跃）
2. 2. rows = 100：只扫描 100 行（从 2000 万到 100，减少了 99.9995%）
3. 3. Extra 里还有 Using filesort（因为 ORDER BY created_at 没走索引）

第 4 步：优化 ORDER BY

问题是 ORDER BY created_at 没走索引，导致 filesort。

方案：把 created_at 也加入联合索引：

-- 删除旧索引，创建新的联合索引
DROP INDEX idx_user_status ON orders;
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at DESC);

再看执行计划：

EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'paid' ORDER BY created_at DESC LIMIT 10;

输出：

+----+-------------+--------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | type | possible_keys            | key                      | key_len | ref         | rows | Extra                            |
+----+-------------+--------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | orders | ref  | idx_user_status_created  | idx_user_status_created  | 10      | const,const | 100  | Using where                      |
+----+-------------+--------+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+

优化效果：

1. Extra 里没有 Using filesort 了（因为索引是有序的，直接读就行）
2. 2. 查询时间从 5 秒降到 0.01 秒（500 倍提升！）

常见慢查询场景及优化

1. 全表扫描（type = ALL）

原因：没建索引，或者索引失效。

优化：

• 给 WHERE 条件加索引
• • 避免索引失效（函数、类型转换、模糊查询前缀通配符）

-- 优化前：全表扫描
SELECT * FROM users WHERE age = 25;

-- 优化后：加索引
CREATE INDEX idx_age ON users(age);

2. 索引失效

常见原因：

-- 1. 对索引字段用函数
WHERE LEFT(name, 5) = 'Alice'  -- 失效

-- 2. 类型转换
WHERE age = '25'  -- 如果 age 是 INT，失效

-- 3. 模糊查询前缀通配符
WHERE name LIKE '%Alice'  -- 失效
WHERE name LIKE 'Alice%'  -- 有效

-- 4. OR 连接的条件中有字段没索引
WHERE age = 25 OR name = 'Alice'  -- 如果 name 没索引，失效

-- 5. 联合索引没遵循最左前缀
-- 索引：(age, name)
WHERE name = 'Alice'  -- 失效（没用到最左前缀 age）

3. 文件排序（Using filesort）

原因：ORDER BY 没走索引。

优化：

• 给 ORDER BY 字段加索引
• • 或者让 ORDER BY 用上联合索引的后缀

-- 优化前：filesort
SELECT * FROM users ORDER BY age LIMIT 10;

-- 优化后：加索引
CREATE INDEX idx_age ON users(age);

4. 临时表（Using temporary）

原因：GROUP BY、DISTINCT、UNION 需要创建临时表。

优化：

• 给 GROUP BY、DISTINCT 字段加索引

-- 优化前：Using temporary
SELECT DISTINCT age FROM users;

-- 优化后：加索引
CREATE INDEX idx_age ON users(age);

5. 大分页（LIMIT offset, size）

问题：LIMIT 1000000, 10 会扫描 1000010 行，然后丢弃前 1000000 行，性能极差。

优化方案 1：用延迟关联（先查 ID，再关联）

-- 优化前：扫描 1000010 行
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;

-- 优化后：先查 ID（覆盖索引），再关联
SELECT * FROM users a
JOIN (SELECT id FROM users ORDER BY id LIMIT 1000000, 10) b
ON a.id = b.id;

优化方案 2：用游标分页（记住上一页的最后一条记录的 ID）

-- 优化前：大分页
SELECT * FROM users ORDER BY id LIMIT 1000000, 10;

-- 优化后：游标分页
SELECT * FROM users WHERE id > 1000000 ORDER BY id LIMIT 10;

6. 深分页（LIMIT 1000000, 10）

参考上面的优化方案。

7. 没用到覆盖索引（回表次数多）

问题：二级索引查询需要回表（拿着主键去聚集索引查完整数据），如果回表次数多，性能差。

优化：用覆盖索引（查询的字段都在索引里，不需要回表）

-- 优化前：需要回表
SELECT * FROM users WHERE age = 25;

-- 优化后：覆盖索引
CREATE INDEX idx_age_name ON users(age, name);
SELECT age, name FROM users WHERE age = 25;  -- 不需要回表

实战建议

1. 开启慢查询日志，定期分析

建议：

• 设置 long_query_time = 1（记录超过 1 秒的查询）
• • 每周用 mysqldumpslow 分析一次，找出慢查询并优化

2. 上线前用 EXPLAIN 检查 SQL

建议：每次写完 SQL，都用 EXPLAIN 看看执行计划，确保走了索引。

3. 给 WHERE、ORDER BY、GROUP BY 加索引

建议：

• WHERE 条件的字段加索引
• • ORDER BY、GROUP BY 的字段加索引（或者让联合索引覆盖）

4. 避免 SELECT *

问题：SELECT * 会查所有字段，增加网络传输和内存消耗，而且无法用覆盖索引。

建议：只查需要的字段。

-- 优化前
SELECT * FROM users WHERE age = 25;

-- 优化后
SELECT id, name, age FROM users WHERE age = 25;

5. 用连接（JOIN）代替子查询

问题：子查询会创建临时表，性能差。

建议：用 JOIN 代替子查询。

-- 优化前：子查询
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- 优化后：JOIN
SELECT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

总结

• 慢查询是指执行时间超过 long_query_time 的 SQL 语句
• • 开启慢查询日志：SET GLOBAL slow_query_log = 'ON';
• • 分析慢查询日志：用 mysqldumpslow 工具
• • 优化慢查询的步骤：复现 → EXPLAIN → 加索引 → 再 EXPLAIN 验证
• • 常见慢查询场景：全表扫描、索引失效、文件排序、临时表、大分页
• • 实战建议：开启慢查询日志、上线前用 EXPLAIN 检查、给 WHERE/ORDER BY/GROUP BY 加索引、避免 SELECT *、用 JOIN 代替子查询
    如果你能把慢查询优化的步骤和常见场景讲清楚，面试官绝对觉得你有实战经验。

---

实战代码都在我本地跑过，你可以放心复制。 如果有问题，欢迎评论区交流！