MySQL 的分页查询优化策略解析

写在文章开头

千万级别的MySQL
单表查询算是近几年面试时碰到的一道比较棘手的问题，因为很多开发没有这方面的经验，所以最终回答都不是很好，所以笔者就以MySQL8
作为实验数据库为读者演示一下笔者日常的处理思路和技巧。

Hi，我是 sharkChili ，是个不断在硬核技术上作死的技术人，是 CSDN的博客专家 ，也是开源项目 Java Guide 的维护者之一，熟悉 Java 也会一点 Go ，偶尔也会在 C源码 边缘徘徊。写过很多有意思的技术博客，也还在研究并输出技术的路上，希望我的文章对你有帮助，非常欢迎你关注我的公众号： 写代码的SharkChili 。

同时也非常欢迎你star我的开源项目mini-redis:https://github.com/shark-ctrl/mini-redis

因为近期收到很多读者的私信，所以也专门创建了一个交流群，感兴趣的读者可以通过上方的公众号获取笔者的联系方式完成好友添加，点击备注  “加群”  即可和笔者和笔者的朋友们进行深入交流。

百万级别数据查询实践

前置准备

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

CREATE TABLE `batch_insert_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `fileid_1` varchar(100) DEFAULT NULL,
  `fileid_2` varchar(100) DEFAULT NULL,
  `fileid_3` varchar(100) DEFAULT NULL,
  `fileid_4` varchar(100) DEFAULT NULL,
  `fileid_5` varchar(100) DEFAULT NULL,
  `fileid_6` varchar(100) DEFAULT NULL,
  `fileid_7` varchar(100) DEFAULT NULL,
  `fileid_8` varchar(100) DEFAULT NULL,
  `fileid_9` varchar(100) DEFAULT NULL,
  `fileid_10` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25414 DEFAULT CHARSET=utf8mb3 COMMENT='测试批量插入，一行数据1k左右';

如何limit

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

select * from batch_insert_test bit2 limit 10,10;
select * from batch_insert_test bit2 limit 100,10;
select * from batch_insert_test bit2 limit 1000,10;
select * from batch_insert_test bit2 limit 10000,10;
select * from batch_insert_test bit2 limit 100000,10;
select * from batch_insert_test bit2 limit 1000000,10;
select * from batch_insert_test bit2 limit 5000000,10;

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

select * from batch_insert_test limit 5000000,10;

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

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

id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows   |filtered|Extra|
--+-----------+-----+----------+----+-------------+---+-------+---+-------+--------+-----+
 1|SIMPLE     |batch_insert_test |          |ALL |             |   |       |   |9004073|   100.0|     |

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

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

select
 *
from
 batch_insert_test 
where
 id >=(select id from batch_insert_test bit2 limit 5000000,1)
 limit 10;

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

id|select_type|table|partitions|type |possible_keys|key    |key_len|ref|rows |filtered|Extra                         |
--+-----------+-----+----------+-----+-------------+-------+-------+---+-----+--------+------------------------------+
 1|PRIMARY    |     |          |     |             |       |       |   |     |        |no matching row in const table|
 2|SUBQUERY   |bit2 |          |index|             |PRIMARY|4      |   |38677|   100.0|Using index                   |

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

select
 b1.*
from
 batch_insert_test b1
inner join (
 select
  id
 from
  batch_insert_test
 limit 5000000,
 10) as b2 on
 b1.id = b2.id;

limit多少

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

select * from batch_insert_test bit2 limit 1000000,10;
select * from batch_insert_test bit2 limit 1000000,100;
select * from batch_insert_test bit2 limit 1000000,1000;
select * from batch_insert_test bit2 limit 1000000,10000;
select * from batch_insert_test bit2 limit 1000000,100000;
select * from batch_insert_test bit2 limit 1000000,1000000;
select * from batch_insert_test bit2 limit 1000000,10000000;

其他注意事项

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

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

select
 id,fileid_1 ,fileid_4 
from
 batch_insert_test bit2
where
 id >(select id from batch_insert_test bit2 limit 5000000,1)
 limit 10;

小结

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

1. 尽可能利用索引，确保用最小的开销得到索引。
2. 结合业务场景和服务器性能压测出最合适的limit数据量。
3. 尽量不要查询没必要的列。

我是 sharkchili ，CSDN Java 领域博客专家，mini-redis的作者，我想写一些有意思的东西，希望对你有帮助，如果你想实时收到我写的硬核的文章也欢迎你关注我的公众号： 写代码的SharkChili 。

同时也非常欢迎你star我的开源项目mini-redis:https://github.com/shark-ctrl/mini-redis

因为近期收到很多读者的私信，所以也专门创建了一个交流群，感兴趣的读者可以通过上方的公众号获取笔者的联系方式完成好友添加，点击备注  “加群”  即可和笔者和笔者的朋友们进行深入交流。

参考

面试官：一千万的数据，你是怎么查询的？ :https://mp.weixin.qq.com/s/zhmVw1C5BgPbEYbI37721Q

【MySQL·8.0·源码】MySQL 表的扫描方式:https://zhuanlan.zhihu.com/p/675876280

MySQL中的全表扫描和索引树扫描:https://www.cnblogs.com/GarrettWale/p/16271622.html

MySQL：limit分页公式、总页数公式 :https://blog.csdn.net/houfengfei668/article/details/80058195