MySQL-深度分页如何优化

场景举例

1. 查询文章列表，一直滑动翻页，不用跳转到指定页数
2. 从数据库查询百万客户数据写入到redis
3. 访问某小程序的积分商城查看商品，一直滑动翻页，不用跳转到指定页数

问题分析

深度分页SQL

SELECT
*
FROM 表名
WHERE 条件
LIMIT #{offset}，#{pageSize}

深度分页造成的结果，offset越来越大，回表的记录越来越多，SQL查询性能急剧下降，会出现大量的慢SQL

即使在二级索引中已经知道前10000条数据要丢掉，但是MySQL也会去聚集索引中去回表查询一下所以效率是很低的，同时这个也是一种随机IO所以来说更加慢

解决办法

方法一：产品上绕过

根据业务实际需求，看能否替换为上一页，下一页的功能，这样子就可以通过和上次数据进行比较，搭上树分支过滤的快车

select * from t where id > last_id limit 10

就是将上一页中的数据的最后一个id传输过来然后在聚集索引中快速查找

优点

1. 能利用树的分支结构，过滤掉第n个数之前的数据
2. 直接通过主键索引查找，省略了二级索引查找过程，性能更高

缺点

1. 使用场景受限。比如针对非主键索引判断再分页那么使用主键id查找不满足需求
2. 把主键id暴露出去了，这个本身不应该是业务层面关心的字段

方法二：子查询

先查询出所需要的数据的主键id，因为在非聚集索引中每个叶子节点记录的数据为其id，这样就不需要回表查询，然后再去主键索引中查询

select * from t where id in (select id from t where age > 10 offset 10000 limit 10)

优点

1. 维持了分页需求，适用于所有的limit offset场景，大大减少了随机IO，提高了性能
2. 二级索引上只查询id，传输数据包变小

缺点

1. 二级索引还是会走下面的链表来遍历，这部分时间复杂度还是O（n）

方法三：INNER JOIN 延迟查询

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

select * from t inner join (select id from t where age > 10 offset 10000 limit 10) as d where t.id = d.id

方法四：使用between … and …

将limit查询转换为已知位置的查询，但是这种方法具有很大的局限性所以很少使用