MySQL分页查询优化

引言

后台列表、文章分页是最常见的SQL场景。数据量小的时候LIMIT偏移量查询很快，数据到几十万、上百万行，深分页会明显变慢，甚至拖垮数据库。

为什么深分页慢

LIMIT 100000,20 会先扫描前100000行再丢弃，只返回20行，扫描行数越多IO开销越大。

优化方案一：子查询定位主键

先查出起始主键，再用主键关联取数据，避免回表扫描：

SELECT a.* FROM article a
INNER JOIN (
  SELECT id FROM article ORDER BY id LIMIT 100000,20
) b ON a.id=b.id;

子查询走覆盖索引，速度明显提升。

优化方案二：游标/记住上一页最大ID

SELECT * FROM article
WHERE id > 100000
ORDER BY id LIMIT 20;

每次只查比上一页最后一条ID大的记录，不走大偏移，适合无限滚动。

其他优化点

• 分页排序字段必须建索引，避免filesort；
• 只查询需要的字段，不用SELECT *；
• 禁止前端跳转到任意深页，后台限制最大页码；
• 超大表考虑分表、搜索引擎承接列表。

总结

深分页慢的根源是大偏移扫描。优先用主键游标翻页，配合覆盖索引；传统offset分页用子查询优化。列表数据量极大时，交给ES等搜索方案更合理。

前言

大表分页查询，第一页很快，第十页就慢，第一百页直接卡死。这是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. 小数据量：普通分页就行
2. 中等数据量：子查询优化
3. 大数据量：游标分页 + 搜索引擎

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

遇到问题加QQ23979811 协助处理