原文作者：杨奇龙
大部分开拓和DBA同行都对分页查询非常非常理解，看帖子翻页须要分页查询，搜索商品也须要分页查询。那么问题来了,碰着上千万或者上亿的数据量怎么快速的拉取全量，比如大商家拉取每月千万级别的订单数量到自己独立的ISV做财务统计;或者拥有百千万万粉丝的公众年夜众大号,给全部粉丝推送的场景。本文讲讲个人的优化分页查询的履历，抛砖引玉。
剖析
在讲如何优化之前我们先来看看一个比较常见缺点的写法
SELECT FROM tablewhere 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 本钱和韶光本钱。一图胜千言，我们利用大略的图来阐明为什么 上面的sql 的写法扫描数据会慢。
t 表是一个索引组织表，key idxkidtype(kid,type) 。
符合kid=3 and type=1 的记录有很多行,我们取第 9，10行。
select from t where kid =3 and type=1 order by id desc 8,2;
MySQL 是如何实行上面的sql 的？对付Innodb表，系统是根据 idxkidtype 二级索引里面包含的主键去查找对应的行。对付百千万万级别的记录而言，索引大小可能和数据大小相差无几，cache在内存中的索引数量有限，而且二级索引和数据叶子节点不在同一个物理块儿上存储，二级索引与主键的相对无序映射关系，也会带来大量的随机IO要求，N值越大越须要遍积年夜量索引页和数据叶，须要耗费的韶光就越久。
鉴于上面的大分页查询耗费韶光长的缘故原由，我们思考一个问题，是否须要完备遍历“无效的数据”？如果我们须要limit 8，2；我们跳过前面8行无关的数据页遍历，可以直接通过索引定位到第9，第10行，这样操作是不是更快了？依然是一图胜千言，通过这实在也是 延迟关联的 核心思思:
通过利用覆盖索引查询返回须要的主键,再根据主键关联原表得到须要的数据,而不是通过二级索引获取主键再通过主键去遍历数据页。
通过上面的事理剖析，我们知道通过常规办法进行大分页查询慢的缘故原由，也知道了提高大分页查询的详细方法 ，下面我们谈论一下在线上业务系统中常用的办理方法。
实践出真知
针对limit 优化有很多种办法: 1 前端加缓存、搜索，减少落到库的查询操作。比如海量商品可以放到搜索里面，利用瀑布流的办法展现数据，很多电商网站采取了这种办法。
2 优化SQL 访问数据的办法，直接快速定位到要访问的数据行。
3 利用书签办法 ，记录上次查询最新/大的id值，向后追溯 M行记录。对付第二种办法 我们推举利用"延迟关联"的方法来优化排序操作，何谓"延迟关联" ：通过利用覆盖索引查询返回须要的主键,再根据主键关联原表得到须要的数据。
延迟关联
优化前
其实行韶光：
优化后:
实行韶光：
优化后 实行韶光 为原来的1/3 。
利用书签的办法
首先要获取复合条件的记录的最大 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;
案例
当碰着延迟关联也不能知足查询速率的哀求时
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;
利用延迟关联查询数据510ms ，利用基于书签模式的办理方法减少到10ms以内 绝对是一个质的飞跃。
SELECT FROM t1 where clientid='xxxxx' and isvalid=1 and id<47399727 order by id desc LIMIT 100;
小结
从我们的优化履历和案例上来讲，根据主键定位数据的办法直接定位到主键起始位点，然后过滤所须要的数据 相比拟延迟关联的速率更快些，查找数据的时候少了二级索引扫描。但是 优化方法没有银弹，没有一劳永逸的方法。比如下面的例子
order by id desc 和 order by asc 的结果相差70ms ，生产上的案例有limit 100 相差1.3s ，这是为什么呢？留给大家去思考吧。
末了，实在我相信还有其他优化办法，比如在利用不到组合索引的全部索引列进行覆盖索引扫描的时候利用 ICP 的办法 也能够加快大分页查询。以上是我在优化分页查询方面的履历总结，抛砖引玉，有兴趣的朋友可以多互换，分享你们的优化履历案例。
我自己是一名从事了多年开拓的Java老程序员，辞职目前在做自己的Java私人定制课程，今年年初我花了一个月整理了一份最适宜2019年学习的Java学习干货，从最根本的JavaSE到Spring各种框架都有整理，送给每一位Java小伙伴，想要获取的可以关注我的头条号并在后台私信我：Java，即可免费获取。
