有一张财务流水表，数据量接近千万级，未做分库分表。
原始分页查询使用了 LIMIT，优化前耗时接近 17 秒：
16 s 938 ms
调整 SQL 后，耗时降到 300ms 左右：
347 ms
优化方式并不复杂：
先在子查询中只查主键 ID，再通过主键关联回原表查询完整字段。
也就是把原来的查询：
SELECT 各种字段
FROM table_name
WHERE 各种条件
LIMIT 0, 10;改成类似：
SELECT main_table.各种字段
FROM table_name main_table
JOIN (
SELECT id
FROM table_name
WHERE 各种条件
LIMIT 0, 10
) temp_table ON temp_table.id = main_table.id;这类优化的核心目的，是减少不必要的回表。
本文基于一个示例，记录一下这个优化思路背后的原因。
一、测试环境与表结构
示例 MySQL 版本：
SELECT VERSION();结果：
5.7.17测试表结构如下：
DESC test;结果：
+--------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| val | int(10) unsigned | NO | MUL | 0 | |
| source | int(10) unsigned | NO | | 0 | |
+--------+---------------------+------+-----+---------+----------------+其中：
id是自增主键；val是普通非唯一索引；source是普通字段。
测试数据量：
SELECT COUNT(*) FROM test;结果：
5242882也就是 500 多万条数据。
二、问题 SQL：深分页 LIMIT
原始 SQL 如下：
SELECT *
FROM test
WHERE val = 4
LIMIT 300000, 5;执行结果类似：
5 rows in set (15.98 sec)这类 SQL 的问题在于：
LIMIT 300000, 5它不是直接跳到第 300001 条数据开始取 5 条。
MySQL 需要先找到满足条件的前 300005 条记录，然后丢弃前 300000 条，只返回最后 5 条。
也就是说，虽然最终只返回 5 条数据，但中间需要扫描和处理大量无用数据。
三、优化 SQL：子查询先查主键
优化后的写法：
SELECT *
FROM test a
INNER JOIN (
SELECT id
FROM test
WHERE val = 4
LIMIT 300000, 5
) b ON a.id = b.id;执行耗时明显下降：
5 rows in set (0.38 sec)这条 SQL 的核心变化是：
子查询只查询主键
id，外层再根据主键查完整数据。
子查询部分：
SELECT id
FROM test
WHERE val = 4
LIMIT 300000, 5因为只查询 id，而 val 是索引字段，InnoDB 的二级索引叶子节点中本身就包含主键值。
所以这一步可以尽量只在索引层完成扫描，不需要对前面被丢弃的大量记录做完整回表。
外层再通过主键：
ON a.id = b.id只对最终需要的 5 条记录进行回表查询。
四、为什么原 SQL 慢
原 SQL：
SELECT *
FROM test
WHERE val = 4
LIMIT 300000, 5;查询过程可以简化理解为：
1. 通过 val 索引找到满足 val = 4 的索引记录
2. 根据索引记录中的主键 id 回表查询完整行数据
3. 重复处理前 300005 条记录
4. 丢弃前 300000 条
5. 返回最后 5 条这里最浪费的地方是：
前 300000 条记录最终不会返回，但仍然做了回表查询。
因为查询的是：
SELECT *需要返回所有字段。
普通二级索引 val 中并不包含 source 等完整字段，所以 MySQL 需要根据主键回到聚簇索引中读取完整行。
这就是回表。
当偏移量很大时，大量回表操作都是无效的。
五、为什么优化 SQL 快
优化 SQL：
SELECT *
FROM test a
INNER JOIN (
SELECT id
FROM test
WHERE val = 4
LIMIT 300000, 5
) b ON a.id = b.id;可以理解为两步。
第一步：
SELECT id
FROM test
WHERE val = 4
LIMIT 300000, 5;这一步只需要 val 和 id。
由于二级索引 val 的叶子节点中包含主键 id，所以可以通过索引扫描拿到需要的主键。
第二步：
SELECT *
FROM test a
JOIN temp_table b ON a.id = b.id;这一步只对最终 5 个主键做回表。
所以回表次数从原来的大量无效回表，变成只对最终结果回表。
这也是性能差距明显的主要原因。
六、用 Buffer Pool 观察回表差异
为了验证上面的推论，原文通过观察 InnoDB Buffer Pool 中的数据页变化来做对比。
思路是：
如果原 SQL 做了大量回表，那么它会把更多聚簇索引数据页加载到 Buffer Pool。
如果优化 SQL 只对最终少量记录回表，那么加载的数据页应该明显更少。
1. 执行原 SQL 前
先查询 Buffer Pool 中是否已有测试表相关页：
SELECT index_name, COUNT(*)
FROM information_schema.INNODB_BUFFER_PAGE
WHERE INDEX_NAME IN ('val', 'PRIMARY')
AND TABLE_NAME LIKE '%test%'
GROUP BY index_name;结果为空：
Empty set说明当前 Buffer Pool 中没有测试表相关页。
2. 执行原 SQL
SELECT *
FROM test
WHERE val = 4
LIMIT 300000, 5;执行耗时：
26.19 sec再次查看 Buffer Pool：
SELECT index_name, COUNT(*)
FROM information_schema.INNODB_BUFFER_PAGE
WHERE INDEX_NAME IN ('val', 'PRIMARY')
AND TABLE_NAME LIKE '%test%'
GROUP BY index_name;结果类似：
+------------+----------+
| index_name | count(*) |
+------------+----------+
| PRIMARY | 4098 |
| val | 208 |
+------------+----------+可以看到，PRIMARY 聚簇索引数据页加载了很多。
这说明原 SQL 做了大量回表。
七、优化 SQL 的 Buffer Pool 表现
为了避免上一次测试影响，需要重启 MySQL 或清空 Buffer Pool。
然后再次确认测试表相关页为空：
Empty set执行优化 SQL：
SELECT *
FROM test a
INNER JOIN (
SELECT id
FROM test
WHERE val = 4
LIMIT 300000, 5
) b ON a.id = b.id;执行耗时：
0.09 sec再次查看 Buffer Pool：
+------------+----------+
| index_name | count(*) |
+------------+----------+
| PRIMARY | 5 |
| val | 390 |
+------------+----------+可以看到：
原 SQL 加载了大量
PRIMARY数据页；优化 SQL 只加载了少量
PRIMARY数据页。
这和前面的分析一致。
优化 SQL 的回表次数明显更少。
八、这个优化的本质
这个优化的本质不是 JOIN 本身更快。
真正的关键是：
子查询只查索引中已有的数据，尽量避免对被丢弃的记录回表。
原 SQL 的问题是：
SELECT *导致每扫描一条候选记录，都可能需要回表读取完整行。
而优化后的子查询：
SELECT id只拿主键，能够尽量利用二级索引完成扫描。
等确定最终需要的少量主键后，再回表查询完整字段。
所以性能提升来自：
减少无效回表
减少随机 I/O
减少 Buffer Pool 污染九、Buffer Pool 污染问题
深分页原 SQL 除了慢，还有一个隐藏问题：
它会把大量不需要返回的数据页加载到 Buffer Pool。
这些数据页可能只是为了跳过前面 300000 条记录而被读取。
但它们进入 Buffer Pool 后，会占用缓存空间，挤掉原本更热的数据页。
这就是 Buffer Pool 污染。
对于高并发系统来说，这可能影响其他正常查询的缓存命中率。
所以深分页慢 SQL 不只是影响当前查询，也可能影响整个数据库实例的缓存表现。
十、什么时候适合这种优化
这种优化方式适合以下场景：
1. 深分页
例如：
LIMIT 300000, 10
LIMIT 1000000, 20偏移量越大，原 SQL 需要丢弃的数据越多，优化收益越明显。
2. 查询字段较多
如果原 SQL 是：
SELECT *或者查询了很多非索引字段，就容易产生大量回表。
3. WHERE 条件能走索引
例如：
WHERE val = 4并且 val 上有索引。
子查询只查索引字段和主键时，效果更明显。
4. 最终返回行数较少
例如：
LIMIT 300000, 5最终只需要少量记录。
这种情况下，先定位主键再回表比较合适。
十一、需要注意的问题
1. 子查询要尽量只查主键
优化的关键是：
SELECT id不要在子查询里继续写：
SELECT *否则又会回到原来的问题。
2. WHERE 和 ORDER BY 要匹配索引
分页查询通常会带排序。
例如：
WHERE status = 1
ORDER BY create_time DESC
LIMIT 300000, 10这种场景需要结合索引设计。
常见索引可能是：
(status, create_time, id)否则即使用子查询，也可能因为排序或过滤不走索引，导致效果不理想。
3. JOIN 类型要写清楚
原文示例中有 RIGHT JOIN 写法。
实际写 SQL 时，更常见也更直观的是：
SELECT main_table.*
FROM (
SELECT id
FROM table_name
WHERE ...
LIMIT ...
) temp_table
JOIN table_name main_table ON main_table.id = temp_table.id;或者：
SELECT main_table.*
FROM table_name main_table
JOIN (
SELECT id
FROM table_name
WHERE ...
LIMIT ...
) temp_table ON temp_table.id = main_table.id;只要语义清晰即可。
4. 深分页本身要尽量避免
如果业务允许，深分页最好从产品和接口设计上规避。
例如使用基于游标的翻页：
SELECT *
FROM test
WHERE val = 4
AND id > #{lastId}
ORDER BY id
LIMIT 10;这种方式不依赖大 offset，性能通常更稳定。
适合“下一页”这种连续翻页场景。
5. 需要结合 EXPLAIN 分析
不同表结构、索引、数据分布下，优化效果可能不同。
实际改 SQL 前，建议先使用：
EXPLAIN查看执行计划。
重点关注：
是否走索引；
扫描行数；
是否 Using filesort；
是否 Using temporary；
是否覆盖索引；
是否回表。
十二、优化前后对比
优化前：
SELECT *
FROM test
WHERE val = 4
LIMIT 300000, 5;优化后：
SELECT *
FROM test a
INNER JOIN (
SELECT id
FROM test
WHERE val = 4
LIMIT 300000, 5
) b ON a.id = b.id;结论
MySQL 深分页慢的一个重要原因是：
LIMIT offset, size在 offset 很大时，需要先扫描并丢弃大量记录。
如果查询字段不在索引中，MySQL 还可能对这些最终会被丢弃的记录执行大量回表操作。
通过子查询先只查询主键，再关联原表查询完整字段，可以减少无效回表。
核心思路是：
先用索引定位最终需要的主键
再根据主键回表查询完整数据这种方式可以把大量无意义的回表操作，减少到只对最终结果集回表。
深分页查询不要一上来就 SELECT *。
能先用覆盖索引查出主键，再回表查完整字段，通常会更稳定，也更容易避免大量无效 I/O。