前言

在实际开发中分页查询是最常用的场景之一，但也通常也是最容易出问题的地方。DBA的同事给出的解决方法就是加“索引/组合索引”，例如在name,create_time,status等字段上加组合索引，这样根据这些条件查询的时候能够有效的利用索引，性能迅速的就提高了。

以上做法对 select * from table limit 0,10 这个没有问题，但是当 limit 1000000,10 的时候数据读取就很慢了。我只想要10条数据但是每次查询都扫描了100多万行，在高频访问下堵的死死的。那么有什么办法解决这个问题呢？

方案1—最大ID

前提：数据库主键ID为自增

正常分页SQL语句为：

select id,name,content,create_time from users order by id asc limit 1000000,10

优化后SQL语句为：

select id,name,content,create_time from users where id > 1000000 order by id asc limit 10

看到这里大家应该明白了吧？进行查询的时候可以将上一页的最大值ID当成参数作为查询条件的，那么当次查询只需要扫描最大ID开始的20行数据即可。

方案2—入库时间

那么有的同学会问如果设计的表主键不是自增又该怎么办？这里还有一种方法根据数据的入库时间，具体方法和上面雷同。

select id,name,content,create_time from users where id > '2020-01-01 12:29:23' order by id asc limit 10

这样查询的时间基本固定，并且也不会随着数据量的增长而发生变化。

测评

如下图所示，小编准备了300w+的数据，这张表共有28个字段。

正常分页所需时长：

从200w开始获取10条数据共花费12s多。

根据入库时间进行分页：

这里的查询时间是上面SQL查询出来结果的create_time，可以看到花费了2s多。性能提升了约6倍！

备注：create_time 字段没有增加索引。

总结

当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。
如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。
如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。

SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000, 10);

如果limit语句的offset较大，你可以通过传递pk键值来减小offset = 0，这个主键最好是int类型并且auto_increment

SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;

这条语句，大意如下:

SELECT * FROM users WHERE uid >= (SELECT uid FROM users ORDER BY uid limit 895682, 1) limit 0, 10; 如果limit的offset值过大，用户也会翻页疲劳，你可以设置一个offset最大的，超过了可以另行处理，一般连续翻页过大，用户体验很差，则应该提供更优的用户体验给用户。