SQL 深分页场景如何优化？

问题

当我们使用limit进行分页查询的时候，在数据量小的时候没有性能问题，但是当我们使用limit 100000,20的时候便会出现性能问题，查询速度极其慢，如下sql

select * from goods limit 100000,20;

这是因为MySQL会扫描从0记录开始扫描到100020条记录，时间复杂度为O(n)，然后只返回20条给客户端，前面100000条数据将被抛弃。

如果是使用了二级索引，这种场景的性能会加剧，因为对前100000条数据都会进行回表查询，就导致了很多次的随机IO。

优化过程

• 减少扫描次数：可以记录上一页的最后一个数据的id，在当前页查询的时候，利用上一页最后一个数据的id，进行条件查询，这样id会走聚簇索引，找到20条数据。

-- 1.记录上一页的最后一个数据的id为pre_id
-- 2.本页查询
select * from goods where id > pre_id limit 20;

• 减少回表：还是使用limit语句，但需要利用覆盖索引来减少回表产生的随机IO，无论是聚簇索引还是二级索引，id主键都是存在各个索引的叶子节点中的，因此使用limit 100000,20查询需要的20条数据的id，这就不用回表了，然后通过这20条数据进行回表查询具体需要的字段。这20条数据回表的时间消耗一般固定，因此可以大大降低回表时间。

select * from goods where id in (
  select id from goods limit 100000,20
);

测试

通过测试4000w数据场景下，针对二级索引分页的场景，如果是使用limit n,m分页方式，查询速度是245秒，如果采用优化SQL语句来查询，查询速度只需要0.4秒。