SQL优化 | 大数据量分页查询优化策略

分页查询是一个很经典的sql应用场景，不夸张的说大部分互联网应用都支持分页查询。分页查询的好处在于可以将大量数据分成多页展示，提高查询效率和用户体验。我们先回顾一下正常情况下我们是怎么实现分页查询的？
其实实现分页很简单，只需要传入偏移量offset和记录行数目size即可，如果偏移量offset为1000，记录数量size为10，那么其实就是查询满足条件的第1000行记录和第1009行记录，这些数据被称为一页。

select * from <表名> limit <从第几条开始查询>,<查询多少条数据>

那么这么做会有问题吗？

深分页问题

正常情况下分页查询会有很多查询条件，一般我们都会设置一个组合索引来加快条件筛选。sql格式如下所示：

select * from <表名> where <查询条件> limit <从第几条开始查询 offset>,<查询多少条数据 size>

假设offset是100001，size是10，我们先来看下这个SQL的执行流程：

1. 通过二级索引树过滤查询条件，找到满足条件的记录ID。
2. 通过ID，回到主键索引树，找到满足记录的行，然后取出展示的列（回表）
3. 扫描满足条件的100010行，然后扔掉前100000行，返回结果。

所谓深分页其实就是，当偏移量很大时，查询效率会变得很低。sql查询变慢的原因有两个：

1. limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。
2. limit 100000,10 扫描更多的行数，也意味着回表更多的次数。

如果满足条件的数据量很大，app用户还喜欢不停的往下滑动阅览更多的数据或者web端用户就喜欢检索最后一页数据，那么这条sql就肯定会超时！我们该怎么解决这种问题嘞？

优化方案

方案一：根据id查

每次分页查询携带上页最后一行数据的id，然后根据id查询，大于上一页最后一条的id。

-- id > 上页最后一行id
select * from 表名 where id > 8000000 limit 100
测试耗时：0.025s

优点：根据上页最后一条记录id查，效果佳。
缺点：只能连续分页查询，不能跳页。

方案二：子查询id

查询当前id>(查询当前页第一行id) limit 分页大小

-- 子查询查出当前分页的首行id
select * from 表名 where id >= (select id from 表名 limit 8000000,1 ) limit 100
耗时：1.966s

优点：比常规分页写法能只快几倍。中等水平数据量，配合其他优化，一般查询能到5秒内。
缺点：优点不是很突出，并且子查询结果的id要有正序，否则id>=就不准了。

参考文献：

• https://www.cnblogs.com/huaweiyun/p/15938831.html
• https://juejin.cn/post/7012016858379321358