当前位置: 首页 > 科技观察

MySQL如何优化大型分页查询?

时间:2023-03-13 23:24:21 科技观察

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进行覆盖索引扫描,这样也可以加快大页查询的速度。以上是我在优化分页查询方面的经验总结。有兴趣的可以多交流,分享你们的优化经验案例。