MySQL 查询优化实战：慢 SQL 排查与深度分页优化

前言

慢 SQL 和深度分页是后端开发中最常见也最头疼的两个性能问题。一条慢查询可能拖垮整个数据库连接池，一个深度翻页可能把 CPU 打满。本文从排查方法讲起，到优化策略，再到面试常考点，争取一次说透。

一、慢 SQL 排查：从发现问题到定位原因

1.1 开启慢查询日志

首先得能"看见"慢 SQL。MySQL 提供了慢查询日志机制：

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

-- 开启慢查询日志（需要 SUPER 权限）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;           -- 超过 1 秒就记录
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未走索引的 SQL
SET GLOBAL log_slow_admin_statements = ON;      -- 记录 DDL

生产环境建议用配置文件永久生效：

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

1.2 分析慢查询日志

原始日志可读性差，用 mysqldump 自带的工具分析：

# 按查询时间排序，取 top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

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

# 按扫描行数排序
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

更好的方式是使用 pt-query-digest（Percona Toolkit）：

# 分析慢日志，生成详细报告
pt-query-digest /var/log/mysql/slow.log > slow_report.log

# 实时分析（抓取线上请求）
pt-query-digest --processlist h=localhost,u=root,p=xxx --interval=0.5

这个工具能帮你：

按 query fingerprint 聚合（忽略参数值，归类相同 SQL）
按总耗时、平均耗时、执行次数排序
给出每个查询的 EXPLAIN 信息

1.3 定位正在执行的慢查询

线上出问题时不能等日志，要实时查看：

-- 查看正在执行的所有查询，按耗时排序
SELECT 
    id,
    user,
    host,
    db,
    command,
    time AS elapsed_seconds,
    state,
    info AS sql_text
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC;

-- 找出执行超过 10 秒的查询
SELECT * FROM information_schema.PROCESSLIST 
WHERE time > 10 AND command != 'Sleep';

-- 8.0 版本可以用 sys schema（更友好）
SELECT * FROM sys.processlist WHERE time > 10 ORDER BY time DESC;

1.4 用 EXPLAIN 分析执行计划

拿到慢 SQL 之后，第一步就是用 EXPLAIN 分析：

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 20;

核心看这几个字段：

字段	含义	关注点
type	访问类型	system > const > eq_ref > ref > range > index > ALL（全表扫描是红灯）
key	实际使用的索引	为空说明没走索引
rows	预估扫描行数	越大越慢，和实际返回行数差距大说明索引差
Extra	额外信息	`Using filesort`（额外排序）、`Using temporary`（临时表）、`Using where`（回表过滤）都需要关注
key_len	索引使用长度	判断联合索引用了几列
filtered	过滤后剩余百分比	配合 rows 判断实际有效扫描比例

EXPLAIN 速判口诀：

type 是 ALL → 没走索引，红灯
Extra 有 Using filesort → 排序没走索引
Extra 有 Using temporary → 用了临时表，通常出现在 GROUP BY 和 DISTINCT
rows 巨大但返回很少 → 扫描了大量无效数据

1.5 使用 EXPLAIN ANALYZE（MySQL 8.0.18+）

传统 EXPLAIN 只看预估，EXPLAIN ANALYZE 能看到实际执行数据：

EXPLAIN ANALYZE 
SELECT * FROM orders WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 20;

输出会包含：

每个步骤的实际执行时间
实际返回行数 vs 循环次数
是否触发了磁盘排序

这比 EXPLAIN 准确得多，定位瓶颈非常直观。

二、慢 SQL 优化策略

2.1 索引优化（最高收益）

基本原则：

-- 1. WHERE 条件列建索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 2. 联合索引要遵循最左前缀原则
-- 查询：WHERE a = 1 AND b = 2 ORDER BY c
-- 正确索引：(a, b, c)  ← 等值在前，范围在后，排序在最后
CREATE INDEX idx_a_b_c ON orders(a, b, c);

-- 3. 覆盖索引避免回表
-- 查询只需要 user_id, create_time, status
CREATE INDEX idx_cover ON orders(user_id, create_time, status);
-- 这样 Extra 显示 Using index，不用回表

索引失效的常见场景：

-- ❌ 对索引列使用函数
WHERE DATE(create_time) = '2026-01-01'   -- 函数导致索引失效
-- ✅ 改为范围查询
WHERE create_time >= '2026-01-01' AND create_time < '2026-01-02'

-- ❌ 隐式类型转换
WHERE phone = 13800138000                 -- phone 是 varchar，传入数字
-- ✅ 保持类型一致
WHERE phone = '13800138000'

-- ❌ LIKE 以 % 开头
WHERE name LIKE '%小明'                   -- 左模糊索引失效
-- ✅ 只右模糊
WHERE name LIKE '小明%'                   -- 走索引

-- ❌ OR 连接不同列
WHERE user_id = 1 OR status = 2           -- 可能全表扫描
-- ✅ 改为 UNION
SELECT * FROM orders WHERE user_id = 1
UNION
SELECT * FROM orders WHERE status = 2

-- ❌ 负向查询（!=, NOT IN, NOT EXISTS）
WHERE status != 3                         -- 通常不走索引
-- ✅ 用 IN 替代
WHERE status IN (1, 2, 4, 5)

2.2 查询重写

有时候换个写法效果天差地别：

-- ❌ 子查询
SELECT * FROM orders WHERE user_id IN (
    SELECT id FROM users WHERE age > 20
);
-- ✅ 改为 JOIN
SELECT o.* FROM orders o 
INNER JOIN users u ON o.user_id = u.id 
WHERE u.age > 20;

-- ❌ NOT IN
SELECT * FROM orders WHERE user_id NOT IN (
    SELECT id FROM blacklist
);
-- ✅ 改为 LEFT JOIN + IS NULL
SELECT o.* FROM orders o 
LEFT JOIN blacklist b ON o.user_id = b.id 
WHERE b.id IS NULL;

-- ❌ 无意义的排序（业务代码不需要顺序）
SELECT * FROM orders ORDER BY id;
-- ✅ 去掉 ORDER BY
SELECT * FROM orders;

2.3 避免 SELECT *

-- ❌ 返回所有列，无法使用覆盖索引
SELECT * FROM orders WHERE user_id = 1;

-- ✅ 只查需要的列
SELECT id, order_no, amount, status FROM orders WHERE user_id = 1;

好处三重：

减少网络传输
减少从磁盘读取的数据量
更容易利用覆盖索引（Using index）

2.4 大表加字段与 ONLINE DDL

-- 大表加索引不锁表（MySQL 5.6+，InnoDB）
ALTER TABLE orders ADD INDEX idx_create_time(create_time), ALGORITHM=INPLACE, LOCK=NONE;

记住：ALGORITHM=INPLACE, LOCK=NONE 对线上大表操作是救命参数。

2.5 JOIN 优化

-- 小表驱动大表
SELECT * FROM small_table s 
INNER JOIN large_table l ON s.id = l.s_id;

-- JOIN 的列要建索引
CREATE INDEX idx_l_s_id ON large_table(s_id);

-- 控制 JOIN 表的数量（不超过 3 个）
-- 多表 JOIN 考虑拆成多次查询，在应用层组装

2.6 合理使用 LIMIT

-- ❌ 不加 LIMIT，可能返回百万行
SELECT * FROM orders WHERE status = 1;

-- ✅ 分批处理
SELECT * FROM orders WHERE status = 1 LIMIT 1000;

三、深度分页优化（面试高频）

3.1 问题演示

SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

执行过程：MySQL 需要扫描前 1000020 行，然后丢弃前 1000000 行，只返回最后 20 行。offset 越大越慢，因为需要扫描然后丢弃的行数越来越多。

3.2 方案一：基于索引的延迟关联

-- ❌ 原始写法：先排序再丢弃
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- ✅ 先查主键，再关联
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;

子查询的 SELECT id 走覆盖索引（Using index），非常快。拿到 20 个 ID 后再回表取完整数据。核心思路是让丢弃操作发生在索引上而非数据行上。

3.3 方案二：基于游标的分页（推荐）

-- ❌ 传统 offset 分页
SELECT * FROM orders ORDER BY id LIMIT 10000, 20;   -- 第 1 页
SELECT * FROM orders ORDER BY id LIMIT 10020, 20;   -- 第 2 页（越来越慢）

-- ✅ 游标分页（记录上一页最后一条的 ID）
SELECT * FROM orders WHERE id > 10000 ORDER BY id LIMIT 20;   -- 第 1 页
SELECT * FROM orders WHERE id > 10020 ORDER BY id LIMIT 20;   -- 第 2 页（始终很快）

原理：直接定位到起始位置，不再需要扫描和丢弃。

优缺点对比：

对比维度	Offset 分页	游标分页
性能	offset 越大越慢	始终稳定
跳页	支持	不支持（只能上一页/下一页）
适用场景	管理系统、数据导出	C 端列表、无限滚动
对数据变化敏感度	可能重复/遗漏	排序字段不变更时稳定

3.4 方案三：标签记录 + 多线程并行

适用于数据导出、定时任务等离线场景：

// 按 ID 范围分段，多线程并行拉取
public void exportData() {
    Long maxId = getMaxId(); // SELECT MAX(id) FROM orders
    long batchSize = 10000;

    for (long minId = 0; minId <= maxId; minId += batchSize) {
        long maxIdInBatch = minId + batchSize;
        List<Order> batch = queryByIdRange(minId, maxIdInBatch); // WHERE id > minId AND id <= maxIdInBatch
        process(batch);
    }
}

3.5 方案四：覆盖索引 + 子查询

-- 组合拳：覆盖索引 + 延迟关联
-- 前提：有联合索引 idx_status_id (status, id)
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    WHERE status = 1 
    ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;

子查询只用到了 idx_status_id 覆盖索引（status + id 都在索引中），不用回表，扫描行数虽多但全是索引页，效率高。

3.6 方案五：ES / 搜索引擎

当数据量达到亿级，MySQL 深度分页力不从心：

ES 的 search_after 天然支持游标分页
ES 的 from + size 默认上限 10000，但要控制深度
MySQL 与 ES 互补：MySQL 做主存储，ES 做检索

3.7 方案选择决策

你的场景是什么？
├── C 端 App/小程序列表 → 游标分页（方案二）⭐
├── 管理后台翻页 → 延迟关联（方案一）+ 限制最大页数
├── 数据导出/离线任务 → 标签记录分段（方案三）⭐
├── 复杂条件搜索 + 分页 → ES（方案五）
└── 并发高 + 数据量千万级 → 游标分页 + Redis 缓存热点数据

四、完整排查流程总结

1. 发现慢 SQL
   ├── 线上实时：SHOW PROCESSLIST / sys.processlist
   ├── 事后分析：slow_query_log + pt-query-digest
   └── 监控告警：Prometheus + MySQL Exporter

2. 定位执行计划
   ├── EXPLAIN（预估）
   └── EXPLAIN ANALYZE（实际，8.0.18+）

3. 分析问题类型
   ├── 没走索引 → 建索引 / 改写 SQL 避免索引失效
   ├── 走了索引但 rows 很大 → 索引区分度不够 / 考虑覆盖索引
   ├── Using filesort → ORDER BY 没走索引
   ├── Using temporary → GROUP BY/DISTINCT 优化
   └── 深度分页 → 游标分页 / 延迟关联

4. 验证优化效果
   ├── EXPLAIN 对比
   ├── 实际执行时间对比
   └── 压测验证

五、面试高频问题

Q1：如何发现和排查慢 SQL？

回答要点：

慢查询日志 + mysqldumpslow / pt-query-digest
线上实时：SHOW PROCESSLIST / sys.processlist
拿到慢 SQL 后用 EXPLAIN 分析执行计划
8.0 可以用 EXPLAIN ANALYZE 看实际执行数据

Q2：索引失效的常见场景？

回答要点：

对索引列使用函数或计算
隐式类型转换（varchar 传入数字）
LIKE 左模糊 %xxx
OR 条件中有的列没索引
负向查询 !=、NOT IN
联合索引不满足最左前缀

Q3：深度分页的性能问题及解决方案？

回答要点：

问题本质：offset 大时需要扫描大量行再丢弃，IO 浪费严重
游标分页：WHERE id > lastId LIMIT N，性能稳定
延迟关联：子查询先取主键（覆盖索引），再回表
标签记录分段：离线场景按 ID 范围分段拉取
ES 等外部引擎分担检索压力

Q4：覆盖索引是什么？有什么好处？

回答要点：索引中包含了查询需要的所有列，不需要回表（Extra 显示 Using index）。好处：减少一次 IO 寻址，查询效率提升明显。注意：覆盖索引要平衡索引大小和维护成本。

Q5：`SELECT *` 和 `SELECT 具体列` 的区别？

回答要点：

SELECT * 无法利用覆盖索引（必须回表）
传输更多数据，网络开销更大
表结构变更时可能引起解析错误
只查需要的列是最佳实践

Q6：JOIN 的优化原则有哪些？

回答要点：

小表驱动大表
JOIN 条件的列要建索引
控制 JOIN 表的数量，不超过 3 个
大表 JOIN 查看 Buffer Pool 是否够用，避免磁盘 JOIN

Q7：MySQL 的 type 字段有哪些取值？怎么判断好坏？

回答要点：

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

- const: 主键或唯一索引等值查询，1 行
- eq_ref: 关联查询时用主键/唯一索引，每行
- ref: 普通索引等值查询
- range: 索引范围扫描
- index: 全索引扫描（比全表好一点）
- ALL: 全表扫描（最差，必须优化）

Q8：联合索引的字段顺序有什么讲究？

回答要点：最左前缀原则。等值查询的列放前面，范围查询的列放后面，排序的列按 ORDER BY 顺序排。区分度高的列放前面效果更好。

总结

慢 SQL 优化的核心思路就三条：

减少扫描的数据量 —— 该走索引走索引，该覆盖索引就覆盖
减少返回的数据量 —— 别 SELECT *，加合理的 LIMIT
减少磁盘 IO —— 覆盖索引 > 回表，内存排序 > 磁盘排序

深度分页的本质是"让 MySQL 做了太多无用功"——扫描了 100 万行然后丢了 99.99 万行。优化的核心是要么不让它扫那么多（游标分页），要么让它在索引上扫而不是数据行上扫（延迟关联）。

慢 SQL 优化是后端开发的基本功，也是面试的高频考点。希望本文能帮你建立一套从发现到分析的完整排查框架。

posted @ 2026-06-04 09:35 松鼠航阅读(22) 评论(0) 收藏举报

刷新页面返回顶部

松鼠航

MySQL 查询优化实战：慢 SQL 排查与深度分页优化

MySQL 查询优化实战：慢 SQL 排查与深度分页优化

前言

一、慢 SQL 排查：从发现问题到定位原因

1.1 开启慢查询日志

1.2 分析慢查询日志

1.3 定位正在执行的慢查询

1.4 用 EXPLAIN 分析执行计划

1.5 使用 EXPLAIN ANALYZE（MySQL 8.0.18+）

二、慢 SQL 优化策略

2.1 索引优化（最高收益）

2.2 查询重写

2.3 避免 SELECT *

2.4 大表加字段与 ONLINE DDL

2.5 JOIN 优化

2.6 合理使用 LIMIT

三、深度分页优化（面试高频）

3.1 问题演示

3.2 方案一：基于索引的延迟关联

3.3 方案二：基于游标的分页（推荐）

3.4 方案三：标签记录 + 多线程并行

3.5 方案四：覆盖索引 + 子查询

3.6 方案五：ES / 搜索引擎

3.7 方案选择决策

四、完整排查流程总结

五、面试高频问题

Q1：如何发现和排查慢 SQL？

Q2：索引失效的常见场景？

Q3：深度分页的性能问题及解决方案？

Q4：覆盖索引是什么？有什么好处？

Q5：SELECT * 和 SELECT 具体列 的区别？

Q6：JOIN 的优化原则有哪些？

Q7：MySQL 的 type 字段有哪些取值？怎么判断好坏？

Q8：联合索引的字段顺序有什么讲究？

总结

公告

Q5：`SELECT *` 和 `SELECT 具体列` 的区别？