Limit分页遇到百万级数据该何去何从

一、Limit分页基础

mysql使用查询语句的时候，经常要返回前几条或者中间某几行数据，也就是我们说的分页，语法如下：

SELECT * FROM table LIMIT offset,length
1

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。具体详情可参考： 理解SpringBoot中的分页竟然如何简单

二、性能分析

1. 案例分析

select * from student limit offset,length
1

当起始页较小时，查询没有性能问题，我们分别看下从10， 100， 1000，10000，400000，800000开始分页的执行时间（每页取20条）

select * from student limit 10, 20   0.016秒
select * from student limit 100, 20   0.016秒
select * from student limit 1000, 20   0.047秒
select * from student limit 10000, 20   0.094秒
select * from student limit 400000, 20   3.229秒
select * from student  limit 866613, 20   37.44秒
1
2
3
4
5
6

可以看出随着起始记录的增加，时间也随着增大， 这说明分页语句limit跟记录行的**偏移量（起始页码）**是有很大关系的。

LIMIT 100000, 20 的意思是扫描满足条件的 100001 行，然后扔掉前 100000 行。

MySQL 耗费了 大量随机 I/O 在回表查询聚簇索引的数据上，而这 100000 次随机 I/O 查询数据不会出现在结果集中。

像这种分页最大的页码页显然这种时间是无法忍受的。从中我们也能总结出两件事情：

• limit语句的查询时间与起始记录的位置成正比
• mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

2. 优化方法

利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。在上述例子中，我们知道id字段是主键，自然就包含了默认的主键索引。利用覆盖索引的对查询效果进行测试。

查询最后一页的数据（利用覆盖索引，只包含id列），如下

select id from table limit 866613, 20    0.2秒
1

相对于查询了所有列的37.44秒，提升了大概100多倍的速度

那么如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，看下实际情况：

//使用id> =
SELECT * FROM table WHERE ID > = 866613 limit 20;
1
2

类似于查询 SELECT * FROM table WHERE id > 866613 LIMIT 20; 这样的效率非常快,因为主键上是有索引的,但是这样有个缺点,就是id必须是连续的,并且查询不能有WHERE语句,因为WHERE语句会造成过滤数据。

//使用JOIN的方式
SELECT * FROM table a JOIN (select id from table limit 866613, 20) b ON a.id = b.id
1
2

MySQL的查询完全命中索引的时候，称为覆盖索引,是非常快的，因为查询只需要在索引上进行查找，之后可以直接返回，而不用再回数据表拿数据。因此我们可以先查出索引的 id，然后根据 id 拿数据。

这2种写法的查询时间都很短，大概0.2s左右


参考文章：https://blog.csdn.net/weixin_37598682/article/details/94547518