【MySQL Tips】偏移量大的分页查询LIMIT子句的优化方法

独上西楼影三人

已于 2022-08-25 17:24:12 修改

阅读量1.5k

点赞数

分类专栏：数据库原理 MySQL 8 笔记文章标签： mysql 数据库 dba sql

于 2022-08-25 17:21:52 首次发布

本文链接：https://blog.csdn.net/wudi53433927/article/details/126528532

版权

MySQL 8 笔记同时被 2 个专栏收录

23 篇文章 1 订阅

订阅专栏

数据库原理

4 篇文章 0 订阅

订阅专栏

SQL优化是要看执行计划分析，并做基准测试的。

前言

MySQL官方关于LIMIT子句的优化建议在之前的文章中写过，链接如下：

8.2.19 LIMIT查询优化.note

【MySQL 8翻译】8.2.19 LIMIT查询优化_影三人的博客-CSDN博客

对网上经常讨论的对分页查询时LIMIT偏移量过大的优化，思路大体上没什么问题，但额外增加了很多先决条件，比如需要提供上页最后一条记录的主键（或其他唯一键）值，且该值单调递增（不必连续）。这些文章的源头大多是《高性能MySQL》这本书。

正文

“需求”驱动“应用”，“应用”驱动“设计”。这种问题在常见应用中应该很少遇到，如果是前端用户行为，谁会去翻看一万多页，一般看10页以上就觉得烦了；除了用户行为，那我猜测就只能是爬虫或者是OLAP分析类应用了。

总体上，偏移量非常大的分页查询的优化方案分两类：

1、页面中限制分页数量

前端只限制返回一定量的分页，超过则不予展示

2、优化偏移量的性能，又分为：

2.1 覆盖索引 + JOIN

先利用分页查询找到所需记录的主键（比如ID）生成派生表，再通过主键作为连接条件与原表连接（嵌套循环连接）。

2.2 预先准备某些东西

2.2.1 预先准备“位置”字段，转换为已知位置的查询

需要一个存储位置信息的字段，该字段单调连续递增且建有索引，该值通常需要预先计算得到。MySQL通过索引范围扫描获得相应结果。

mysql> select * from employee where position between 10050 and 10099;

2.2.2 预先准备上页的最后一条记录的唯一键的值作为“书签”，使用“书签”

假设上页最后一条记录的主键ID（或其他唯一键）的值为10049，则查询本页数据记录可以执行：

mysql> select * from employee where id > 10049 limit 50;

同时，记录新的“书签”为10099（即10049+50）。

注意，某些情况下所需数据的ID并非连续的，此时不能简单进行加减运算，需要读取最后一条记录的“书签”值。

2.2.3 《高性能MySQL》中的其他优化方法

包括使用预先计算的汇总表，或者关联到一个只包含需要做排序的数据列和主键列的冗余表，

2.3 使用Sphinx优化一些搜索操作

该方案对于MySQL 8等新版本MySQL已不具优势，某些情况下对于旧版本MySQL具备一定优势：

使用max_matches选项，以每个服务器和每个查询为基础，可控制类似“sort_buffer"的大小。MySQL 8中sort_buffer_size表示其最大阈值，每个会话会获得一个很小的初始大小sort_buffer，然后MySQL会根据实际使用情况动态改变。
顺序I/O读取磁盘，而不是旧版本MySQL中的半随机方式。MySQL 8中使用 Disk-Sweep Multi-Range Read (MRR)技术来优化。

当表很大且没有存储在存储引擎缓存内时，使用次级索引上的范围扫描读取行会导致对基表的大量随机磁盘访问。通过MRR可以先缓存次级索引记录再按行ID进行排序，最终将大量随机磁盘访问转化为一次或多次顺序磁盘访问。该设计思路类似于Change Buffer的设计思路。详见MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.11 Multi-Range Read Optimization

测试

详细内容因格式与篇幅问题无法展示，烦请移步至我分享的有道云笔记偏移量大的分页查询LIMIT子句的优化方法查看。

虽从上面代码块可以看出，使用"书签"并使用ID排序的SQL-B2优于不使用“书签”的SQL-JOIN，使用"书签"并使用ID排序的SQL-B2很可能是最优解，但算上应用程序获取“书签”的时间，两者孰优孰劣并不清晰，需做基准测试并依据具体情况具体分析。

我认为这种比较就是不公平的，没有可比性。前者额外增加了已知条件，如果允许这种“作弊”，那为什么只缓存一页，而不是执行一次SQL一次缓存100页，前端显示的分页由应用程序的缓存提供，在查101页时再触发一次对数据库的查询？这样显然更优，同样，类似的额外增加的优化手段还有很多，每增加新的条件，总会比没增加前更优，但同样也会使系统变得越来越复杂臃肿。

结论

1、使用“书签”可能是最优的优化方案，但需要额外成本和限制。

2、在获取“书签”的成本较高时，使用JOIN派生表的方案也可能是最优的优化方案，没有额外成本和限制，适用几乎所有场景。

后记

做这个测试、写这篇文章的最初目的是为了怼该UP主，当看到他把倒序排序的“书签”条件写成“ID > XXX”（正确的应该用小于"<"）,而且不用ID排序反用CREATE_TIME排序时我就想怼了，后面又看到IN形式的半连接查询中包含LIMIT，我就更想怼了。而后与他评论区battle的过程中，我说应用缓存（后端或前端），他硬是理解成Redis缓存，后面发现他的认知中只知道Redis能缓存。。。还说MongoDB现在多垃圾，垃圾人家DB-Engines这两年评分排名疯狂上涨？真服了这些一瓶子不满半瓶子晃荡的老六了！成天搁这误人子弟！他最多是个给大厂做外包的程序员，要真是大厂的，除非哪个大厂眼瞎！搞Java的不好好搞Java，成天好为人师给人家讲解数据库，你说好不好笑！