Mysql - 深度分页问题的解决方案

    深度分页问题是面试的高频，高级开发往上问到的概率都非常大？最容易得到的答案就是，不论 where ... order by ... limit m,n 用到了哪些查询条件，都尽量让其在合适的索引或复合索引中完成；基于分表的方式解决；使用子查询主键索引的方式进行优化等。怎么都能回答上来一些但是如何需要深入的全面的理解这部分知识和常用的方案，还是比较麻烦的。

    Mysql深度分页本质问题？

1、limit 100000 , 20 // 偏移量十万，就一定要查询前十万零20条数据，然后丢弃前十万条，返回20条数据，听着都消耗很大

2、count，统计分页的总条数。在InnoDB内部基于MVCC多版本并发实现，需要实时计算。

 

    深度分页问题需要注意的点：

1、如果数据量大到一定从程度，那么可以使用分库表的方式，数据使用多CPU和内存等处理；

2、深度分页尽量禁止跳页访问，这个需要与产品进沟通，最好能妥协；

3、其实很大时候可能并不是所有数据都会执行分库表，那样会引入更多的问题。 当偏移量达到万、十万级别，查询的性能已经非常低了；

4、当数据量非常庞大时，一般这种需要基于搜索引擎、MongoDB等，此时解决性能问题可以使用 游标 处理。

5、Mysql的分页limit经常执行的sql为： where xx = ？order by yy limit 100000,10 。此时如果执行计划中包含了 Using filesort 那么基本都发生了文件排序，其优化点就是尽量不要执行真正的排序，就是建立一个 xx，yy的复合索引（可以参见：Mysql - order by执行原理）。

 

    深度分页的问题，如果能从业务层面妥协。尽量在这一步处理，深度分页主要包含两个方面：1、满足条件的总条数 2、深度分页数据查询

1、满足条件的总页数

    Mysql - count(字段)＜count(主键 id)＜count(1)≈count(*)中我们分析过，如果一定要执行查询总条数时，尽量使用count(*)、count(1)；

    如果可以不统计总条数，可以每次查询的时候多添加一条（比如每条20，就查询21条），然后返回是否还有下一页数据；

    如果查询非常频繁，并行需要的高性能。此时可以单独维度查询条件的总条数，只是当发生数据修改操作时，需要根据条件维护总条数。 如果一致性要求不是非常高，可以基于redis进行维护，如果一致性要求非常高，可以在另一张表中维护总条数，修改操作时将两者放到统一事务中，详细可以参考（Mysql - count(字段)＜count(主键 id)＜count(1)≈count(*)）。

2、需要在未分库分表的Mysql中执行深度分页时

    1）、如果允许不跳页，可以让客户端调用分页时，传入上一次分页的最后一条数据的最后一条

          info：我们在项目上使用过该方式，项目场景是开放平台系统需要定时全量将他们的数据全部调用一遍，此时就会涉及深度分页的问题。 此时就在分页请求参数中传入上一次分页数据的最大一个 主键id，那么执行sql如下：

select * from t where id > maxId limit 20； 此时查询的条件就只需要查询一遍B+树

    2）、基于主键索引的子查询方式优化：

select * from t where id > (select id from t limit 100000,1) limit 20;

    3）、使用 inner join方式，与第二种方法基本一致

select * from t inner join （select id from t limit 100000,20）t2 on t2.id = t.id