1背景大多数开发人员和DBA同事都非常熟悉逐页查询。看帖子翻页需要分页查询，搜索商品需要分页查询。那么问题来了，遇到几千万、上亿的数据如何快速全量拉取，比如某大商户拉取月千万的订单量给自己独立的ISV做财务统计；或者拥有百万粉丝的公众号，给所有粉丝发消息。这篇文章说说我在优化分页查询方面的个人经验，抛砖引玉。2、分析在说如何优化之前，我们先来看一个写法比较常见的错误SELECT*FROMtablewherekid=1342andtype=1orderidaslimit149420,20;这条SQL是一个非常典型的排序+分页查询：orderbycollimitN，MMySQL需要执行这类SQL先扫描到N行，然后取M行。对于这种操作，获取前几行数据会很快，但是随着扫描记录数的增加，SQL的性能会变差，因为N的值越大，MySQL需要扫描的数据越多定位到具体的N条线，会消耗大量的IO成本和时间成本。一张图片胜过千言万语。我们用一张简单的图来解释为什么上面的sql扫描数据慢。t表是索引组织表，keyidxkidtype(kid,type)。匹配kid=3和type=1的记录中有很多行，我们取第9行和第10行。选择*fromtwherekid=3andtype=1orderbyiddesc8,2;MySQL如何执行上面的sql呢？对于Innodb表，系统根据idxkidtype二级索引中包含的主键查找对应的行。对于数百万条记录，索引的大小可能几乎与数据的大小相同。内存中缓存的索引数量有限，二级索引和数据叶子节点不在同一个物理块上。主键相对无序的映射关系也会带来大量的随机IO请求。N值越大，越需要遍历大量的索引页和数据叶，耗时也越长。针对上面大页面查询耗时较长的原因，我们思考一个问题，是否需要对“无效数据”进行彻底遍历？如果我们需要limit8,2;我们跳过前8行的无关数据页遍历，可以直接通过索引定位到第9行和第10行。这个操作更快吗？还是一图胜千言，这其实就是延迟关联的核心思想：通过覆盖索引查询返回需要的主键，然后将原表与主键关联起来，得到需要的数据，而不是通过二级索引获取主键，然后通过主键遍历数据页。通过以上原理的分析，我们知道了常规方式下大页查询慢的原因，也知道了改善大页查询的具体方法。下面就在线业务系统中常用的解决方案进行探讨。3.真正的知识来自实践。limit的优化有多种方式：1、在前端增加缓存和搜索，减少落入数据库的查询操作。例如，可以在搜索中放置大量的产品，以瀑布的方式展示数据，这是很多电子商务网站所采用的。2优化SQL访问数据的方式，直接快速定位到要访问的数据行。3用书签的方法记录上次查询的最新/最大的id值，回溯M行记录。对于第二种方法，我们推荐使用“延迟关联”的方法来优化排序操作。什么是“延迟关联”：通过覆盖索引查询返回需要的主键，然后将原表与主键关联起来，得到需要的数据。3.1delaycorrelation优化前的执行时间：优化后：执行时间：优化后执行时间是原来的1/3。3.2使用书签的方法是先复合条件获取记录的最大id和最小id（默认id为主键）selectmax(id)asmaxid,min(id)asminidfromtwherekid=2333andtype=1;其次，根据id大于最小值或者小于最大值来遍历。selectxx,xxfromtwherekid=2333andtype=1andid>=min_idorderbyidasclimit100;selectxx,xxfromtwherekid=2333andtype=1andid<=max_idorderbyiddesclimit100;caseSELECTa.idasid,clientid,adminid,kdtid,type,token,createdtime,updatetime,isvalid,versionFROMt1a,(SELECTidFROMt1WHERE1andclient_id='xxx'andis_valid='1'orderbykdt_idasclimit267100,100)bWHEREa.id=b.id;使用延迟关联查询数据510ms，与使用基于书签的方案将其降低到10ms以内是质的飞跃。SELECT*FROMt1whereclientid='xxxxx'andisvalid=1andid<47399727orderbyiddescLIMIT100;四小结从我们的优化经验和案例来看，直接根据主键定位数据定位主键起点，然后过滤需要的数据相对延迟关联速度更快，二级索引更少查找数据时扫描。但是优化方法没有灵丹妙药，也没有一劳永逸的方法。比如下例中的orderbyiddesc和orderbyasc相差70ms。在生产的情况下，limit100之间有1.3s的差异，这是为什么呢？留给大家自己思考。最后，其实我相信还有其他的优化方法，比如在使用不到复合索引的所有索引列的时候使用ICP进行覆盖索引扫描，这样也可以加快大页查询的速度。以上是我在优化分页查询方面的经验总结。有兴趣的可以多交流，分享你们的优化经验案例。
