MYSQL深度分页的常见优化方案

1、分页查询：一般写法

一个比较常见的写法

SELECT * FROM tableName where kid=1342 and type=1 order id asc limit 149420, 20;

该SQL是一个经典的排序+分页查询

order by col limit N,M

MySQL 执行此类SQL时需要先扫描到N行，然后再去取M行。对于此类操作，获取前面少数几行数据会很快，但是随着扫描的记录数越多，SQL的性能就会越差，因为N的值越大，MySQL需要扫描越多的数据来定位到具体的N行，这样耗费大量的 IO 成本和时间成本。

举例说明

-- 表名：user
-- 主键：id
-- 索引：key idx_uid_type(uid, type)
select * from user where uid =3 and type=1 order by id desc 8,2;

MySQL 是如何执行上面的sql 的？ 对于Innodb表，系统是根据 idx_uid_type二级索引里面包含的主键去查找对应的行，也就是会通过主键在进行一次回表查询。

对于百万千万级别的数据而言，索引大小可能和数据大小相差无几，cache在内存中的索引数量有限，而且二级索引和数据叶子节点不在同一个物理块儿上存储，二级索引与主键的相对无序映射关系，也会带来大量的随机IO请求，N值越大越需要遍历大量索引页和数据叶，需要耗费的时间就越久。

我们来思考一个问题，是否需要完全遍历“无效的数据”？如果我们需要limit 8，2；我们跳过前面8行无关的数据页遍历，可以直接通过索引定位到第9，第10行，这样操作是不是更快了？

2、分页查询：延迟关联查询

核心思思：通过使用覆盖索引查询返回需要的主键，再根据主键关联原表获得需要的数据，而不是通过二级索引获取主键，再通过主键去遍历数据页（回表）。

SELECT a.id as id, clientid, adminid, kdtid, type, token, createdtime, updatetime, isvalid, version FROM t1 a, (SELECT id FROM t1 WHERE 1 and client_id = 'xxx' and is_valid= '1' order by kdt_id asc limit 267100,100 ) b WHERE a.id = b.id;

上述SQL中的子查询只取主键id，可避免通过二级索引中的主键去回表查询，这样性能会快一些。

像上述案例，当offset变得超级大时，会引发一个问题，它有一个专门的术语，叫深度分页。

深度分页问题，是个很恶心的问题，其恶心之处，在于这个问题，它其实无解。目前不管是mysql还是es都没有很好的方法去解决这个问题。只能通过限制查询数量或分批获取的方式进行规避。

3、分页查询：基于上次最新主键查询

核心思想：通过主键索引，每次定位到id在哪，然后往后遍历N个数据，这样不管是多少数据，查询性能都很稳定。将所有数据根据主键id进行排序，然后分批次取，将当前批次的最大id作为下次筛选的条件进行查询。

针对上面的延迟关联查询案例，我们使用基于上次最新主键查询来进行优化，如下：

SELECT * FROM t1 where clientid='xxxxx' and isvalid=1 and id<47399727 order by id desc LIMIT 100;

一般情况下该方式有如下两步：
首先，要获取复合条件的记录的最大id和最小id(默认id是主键)

select max(id) as maxid ,min(id) as minid from t where kid=2333 and type=1;

其次，根据id大于最小值或者小于最大值 进行遍历。

select xx,xx from t where kid=2333 and type=1 and id >=min_id order by id asc limit 100;
select xx,xx from t where kid=2333 and type=1 and id <=max_id order by id desc limit 100;

4、小结

从上面的案例来讲，基于上次最新主键查询的方式直接定位到主键起始位点，然后过滤所需要的数据， 相对比延迟关联查询的方式，其查询速度更快些，查找数据的时候少了二级索引扫描。

当遇到深度分页的问题，多思考其原始需求，大部分时候是不应该出现深度分页的场景的，必要时多去影响产品经理或需求方。