mysql深度分页优化

1. 背景

Mysql使用select * from table limit offset, rows分页在深度分页的情况下, 性能急剧下降。

• 例如：select * 的情况下直接⽤limit 600000，10 扫描的是约60万条数据，并且是需要回表 60W次，也就是说⼤部分性能都耗在随机访问上，到头来只⽤到10条数据(总共取600010条数据只留10条记录).

2. 如何优化

2.1 业务层面限制跨度比较大的跳页

• 限制最大跨度是 3到12

• 这样的话用户需要点很多次才能访问到页码比较大的数据，通过提高用户操作成本，降低了深分页请求数量。

  
  image.png

2.2 记录每次取出的最大id， 然后where id > 最大id

• 这种方法适用于主键ID自增的情况，分布式ID则不行。需要考虑连续型字段datetime，sequence 等。
• 实现步骤：每一次查询把本批数据的最大ID传给前端，查询下一页的时候再带到后台

select * from table_name where id > 最大id limit 10000, 10

2.3 通过in获取id

select * from table_name where id in (select id from table_name where userId = xxx  limit 10000, 10)

子查询先根据where条件分页查出一页id, 然后外层查询只需要回表查询返回这一页的数据行。大大降低了无效的数据回表次数。