MySQL 分页查询优化指南

发布时间：2025-11-04 06:50:22 来源：技术快报 作者：人工智能

本文以MySQL8为例演示一下分页查询技巧和常见优化思路，页查希望对你有帮助。询优

一、页查业务妥协向的询优非跳页查询

该问题实际上有两种比较常见的方案，一种是页查search_after上下翻页查询，如方案名所说，询优当用户进行上下翻页的页查时候，永远都是询优基于本次分页的结果的区间定位上一页和上一页，这也就意味着该查询必须要求用户的页查数据必须具备有序的字段，例如我们当前查询到id为20~30的询优数据，基于该方案我们获取下一页的页查数据就是找到大于30的前10条数据：

对应的我们也给出这条SQL示例：

而查询上一页也是同理，通过当前页码的询优最小值，定位到上一页的页查最大值，从而获取上一页的询优结果区间：

图片

对应的我们也给出这段SQL示例：

二、支持跳页的高防服务器页查分页查询SQL

为了方便演示笔者，这里拿出一张曾经作为批量插入的数据表，该表差不多有200w左右的数据:

对应的我们也给出批量插入模拟数据的脚本：

按照分页查询公式，查询第N页的sql就是limit (page-1)*size, size，所以笔者对如下几个分页查询进行实验，不难看出，随着分页深度的增加，查询也变得十分耗时：

查看第500w页的数据10条，花费了将近10s：

因为查询时没有使用任何索引，所以查询时直接进行完整的table scan即针对整颗聚簇索引树的非空data域进行扫描检索：

查看其执行计划，可以发现本次查询走了全表扫描，性能表现非常差劲:

所以我们需要对这些SQL进行改造，因为笔者这张表是以有序自增id作为主键的，所以我们可以很好的利用这一点，通过定位当前页的第一个id，然后通过这个id筛选对应页的WordPress模板数据：

对应SQL如下所示，经过笔者的实验耗时大约在500ms左右：

查看这条sql的执行计划可以发现，这条sql是直接通过索引直接定位id，避免走向叶子节点直接返回，再通过走索引的方式进行范围查询性能提升了不少。

当然，我们也可以通过子查询的方式先定位到索引区间，然后再和查询的表进行关联完成检索，性能表现也差不多，这里不多做赘述了：

接下来就是limit数据量的选择了，有些读者可能为了方便直接在业务上进行改造，一次性查询大几十万数据给用户。 可以看到随着数据量的增加，查询耗时主键增大，所以读者在进行这方面考虑的时候务必要结合压测，根据自己业务上所能容忍的延迟涉及最大的pageSize，以笔者为例大约10w条以内的数据查询性能差异是不大的(上下相差200ms左右)：

还有一点细节上的云服务器提供商优化，MySQL的基本单位是页，所以每次查询都是以页为单位进行查询，所以高效的查询也要求我们用尽可能少的块查到存储尽可能多的数据，所以查询时我们建议没有用到的列就不要查询来了。

以笔者为例，只需用到3个字段，则直接将*改为了id,fileid_1 ,fileid_4

延迟关联查询法在若带有通过其它字段进行分页查询或者排序时，我们务必针对该字段创建一个索引，假设我们要查询19001页的数据，对应的SQL如下所示：

假设分页查询有一个limit_count记录分页偏移量，如果file_8没有创建索引，这条查询的执行过程为：

因为涉及文件排序和全表扫描，所以这条SQL的查询表现比较差劲，查询耗时为1m39s ，对应的我们也给出相应的执行计划印证：

所以在此基础上，我们会考虑在fileid_8 上增加一个索引，为后续的优化做铺垫：

有了索引之后，使用二级索引进行排序，查询耗时变为400ms，但这还不够，原因很简单，通过下述的SQL很好的利用二级索引完成排序，但是检索数据时整体过程还是：

因为二级索引b+树记录的是索引和主键的映射，若需要投影其它字段，还需要经过回表这一步：

对应的我们也给出执行计划：

因为我们基于fileid_8创建了二级索引，所以我们可以借助MySQL中索引覆盖的特性，在排序时通过扫描二级索引定位到主键索引区间，并基于这个主键区间一次性到聚簇索引树上获取所有数据，避免多次回表的开销。

对应的我们给出下面这条SQL，需要注意的是MySQL默认语法不允许in直接和子查询的select id 子句一起使用，若使用该语句则会抛出This version of MySQL doesnt yet support LIMIT & IN/ALL/ANY/SOME subquery;异常，所以笔者对应的SQL上增加了将select id这个子查询结果构建成一张只有id的虚表t1和外部关联：

对应查询结果一下子优化至20ms，从执行结果上可以看出：

三、小结

来简单小结一下，本文通过一张大表结合一个分页查询的场景为读者演示的大表分页查询的技巧，整体来说，针对大表查询时，我们的SQL优化要遵循以下几点：

相关文章