mysql解决深度分页的问题

limit深分页为什么会变慢？

例如：一条sql：

select id,name,age, from user where age >10 limit (10000,10);

首先这条sql肯定是比较慢的，因为它经过了很多次的回表。

sql执行流程：

1：先通过普通索引age，过滤掉age条件，找到符合条件的这10010条记录的id

2：通过这10010条记录的id找到id的叶子结点，取出对应的值（回表）

3：丢弃前10000条数据，留下最后10条，返回。

sql变慢的原因：

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

解决方案一：通过子查询优化

因为上面的sql，回表了100010次，但其实回表10次就够了，所以说只需要减少回表次数就够了。

到这个时候有些同学可能对于回表这个概念并不是很清楚，简单来说：从二级索引的叶子结点上只能查询到当前索引字段和id字段，然后根据查询到的id字段再去主键索引中查询（因为主键索引的叶子结点中存放的是整行数据）

那么看到这可能很多人就明白了，只需要将条件转移到主键索引树上就能够减少回表次数了。

所以我们只需要写一个子查询将id查询出来，然后将这个id作为主查询的where条件就ok了。

注意：子查询也不能过多的回表。

select id,sales_order_code,lift_code from `or_lift_item` where id > (select id FROM or_lift_item WHERE  create_time> '2023-06-03 10:25:51' limit 50000,1) LIMIT 10

这个查询效果是一样的，但是回表次数却减少到了10条，查询效率大大提高。

解决方案二： INNER JOIN 延迟关联

延迟关联的优化思路，跟子查询的优化思路其实是一样的：都是把条件转移到主键索引树，然后减少回表。不同点是，延迟关联使用了inner join代替子查询。

优化后的SQL如下：

select abc.id,abc.sales_order_code,abc.lift_code from `or_lift_item` abc JOIN (select a.id FROM or_lift_item a WHERE  a.create_time> '2023-06-03 10:25:51' ORDER BY a.create_time limit 40000,10) tbs ON abc.id = tbs.id

解决方案三： 标签记录法

limit 深分页问题的本质原因就是：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉。这样就导致查询性能的下降。

其实我们可以采用标签记录法，就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。

假设上一次记录到100000，则SQL可以修改为：

select id,sales_order_code,lift_code from `or_lift_item` where id > 100000 ORDER BY id LIMIT 10