MySQL大表分页越查越慢，优化方案

前言

大表分页查询，第一页很快，第十页就慢，第一百页直接卡死。这是MySQL分页的经典问题，本文讲怎么优化。

为什么越往后越慢

普通分页：

SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

MySQL 会先扫描前100010条，然后丢掉前100000条，只返回最后10条。

越往后翻，扫描的行越多，当然越慢。

优化方案一：子查询优化

先查出起始位置的id，再用id做条件：

SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 100000, 1)
LIMIT 10;

原理： 子查询走索引，只查id列，很快。然后外层用id直接定位，不用扫那么多行。

优化方案二：游标分页（推荐）

记住上一页最后一条的id，下次从这个id之后查：

-- 第一页
SELECT * FROM orders ORDER BY id LIMIT 10;

-- 第二页（假设上一页最后id是100）
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;

-- 第三页（假设上一页最后id是110）
SELECT * FROM orders WHERE id > 110 ORDER BY id LIMIT 10;

优点： 不管翻到第几页，都是直接定位，速度一样快。

缺点： 不能跳页，只能上一页下一页。

优化方案三：覆盖索引

只查需要的列，不要 SELECT *：

SELECT id, title, create_time FROM orders ORDER BY id LIMIT 100000, 10;

如果查询的列都在索引里，MySQL直接从索引返回数据，不用回表，快很多。

优化方案四：业务上限制深分页

根本解决： 不让用户翻到那么深。

1. 搜索结果只显示前100页
2. 用条件过滤，不要全表翻
3. 用搜索引擎（ES）代替深分页

常见坑

坑1：用了offset就一定慢

其实offset小的时候（比如前10页），差别不大。

结论： 数据量不大的时候，普通分页就行，不用过度优化。

坑2：游标分页不能用排序

游标分页只能按主键或唯一索引排序。

解决： 如果按其他字段排序，先查出对应的id列表，再用id定位。

坑3：子查询效率也不高

子查询在MySQL 5.6以前有优化器问题。

解决： MySQL 5.6以后子查询优化得很好了，放心用。

坑4：没加where条件

深分页没加where，全表扫描。

解决： 尽量加上时间范围、状态等过滤条件。

性能对比

方案	第1页	第100页	第1000页
普通分页	快	慢	很慢
子查询优化	快	中	慢
游标分页	快	快	快
搜索引擎	快	快	快

总结

大表分页优化三板斧：

1. 小数据量：普通分页就行
2. 中等数据量：子查询优化
3. 大数据量：游标分页 + 搜索引擎

记住： 深分页不是MySQL擅长的事，数据量大了就上ES。

遇到问题加QQ23979811 协助处理