MySQL 的分页查询优化策略解析-51CTO.COM

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

百万级别数据查询实践

前置准备

为了方便演示笔者，这里拿出一张曾经作为批量插入的数据表，该表差不多有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优化要遵循以下几点：

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