Mysql深分页

一、引言

        在工作中，经常出现深分页的场景，例如某个工单管理系统有几百万工单，某个供应链系统货品模块有上百万品，每页10个品的话就有数十万页，此时如果想点击第9万页，sql语句大致会"limit 900000,10"，此时效率显然非常低，如果不做优化接口响应时间会达到5秒以上，用户体验感太差。

二、优化

1、业务限制法

        看待一个需求首先要判断一下业务上是否可以优化，如果业务上能放宽要求，技术上有时候可以大大减轻压力，例如业务上要求最大只能查询100页，从根本上预防了深分页带来的影响。

        例如百度的查询，一次最多往后查询10页：

2、子查询法

        假设有张user表，有id（主键），name，age字段，表中有1000万条数据，现在要查询第50万页，每页10条，那么也就是需要limit 5000000,10。

        优化前：

SQL1:select * from user limit 5000000, 10;

        优化后：

SQL2:select a.* from user a 
inner join(
    select id
    from user
    limit 5000000, 10
) b ON a.id = b.id;

        优化原理：第1条sql需要msql从第1页开始把每一页的数据都读取，直到读取到第50万页，性能较低；第2条sql的inner join中只需要在主键索引树中定位到符合条件的10个主键，由于主键是按从小到大顺序排列，所以查找数据很快，再在外层根据主键id进行关联，从而取出所有的字段信息，速度可以提升1倍以上；同时覆盖索引原理相同，都是索引树中直接遍历，减少扫描行数。

3、滚动查询法

        滚动查询法，就是想滚轮一样查询，每次查询最新的1页；

        设置一个滚动标识符roll_id，每次查询完一页后，将本页最大的id赋值给roll_id，在sql中拼接id>roll_id，例如SQL1中查询第一页后，将10赋值给roll_id，此时SQL就演变成：

select * from user where id > 10 limit 10

        同理如果是偏移量为5000000万条，那么SQL演变成：

select * from user where id > 5000000 limit 10

        这样就可以避免limit太多偏移量，而id>x的写法，由于id是主键，走主键索引，同时主键索引树中id按照从小到大排列，所以想查找大于5000000后的10个id是非常快速的。

        缺点：假设删除第1条数据，（limit 5000000,1）和（id>5000000）得到的结果就不一样了；并且要保证两者结果一样，除了数据不能删除外，还要保证分页的id是呈顺序排列的，比如页面上按照id从小到大排列或者从大到小排列；

        适用场景：这种分页方法非常适用于瀑布流分页，即下一页附带在当页尾部，滚动刷新页码，例如淘宝或者京东的首页商品，往下滑实时加载下一页商品数据。

        还有一种场景也非常适用，我在实际生产环境中遇到过使用滚动查询的情况，不过不是因为需要分页展示数据。当时有张表有5万条数据，需要将5万条数据查询出来做处理，而项目中限制了单次最大查询条数为1万（单次查询条数过多对数据库造成的压力较大，并发情况下服务器撑不住，多次数据库cpu打满），此时就可以用滚动查询优雅的将所有数据查询出来。

4、反向查找法

        大家思考一下，假设10万页数据，点击最后一页，每页10条，偏移量为999990条，若是逆向思维，最后一页其实就是将id反向排序后的第1页，此时相当于偏移量就是0条，那么速度非常快。

        反向查找法存在一个比较致命的缺陷，就是需要对表进行count的操作：要想确定反向offset参数，必须先获得总数量。对于行数量比较稳定的表，可以直接使用定时刷新的缓存值；对于不需要进行事务操作的表，可以考虑采用MyISAM引擎；如果数据量大，count的查询耗时也会随之变长，也需要对count操作进行优化，可以使用缓存和MyISAM引擎。