MySql大表分页(附独门秘技)

问题背景

MySql(InnoDB)中的订单表需要按时间顺序分页查询,且主键不是时间维度递增,订单表在百万以上规模,此时如何高性能地实现该需求?

注:本文并非主要讲解如何建立索引,以下的分析均建立在有合适的索引的前提下

初步方案1

众所周知,MySql中,有一个limit offset, pageSize的用法,可以实现分页查询

select * from order where user_id = xxx and 【其它业务条件】 order by created_time, id limit offset, pageSize

因为created_time可能重复,所以order by时应加上id,保证顺序的确定性

点评

该方案在表规模较小的时候,不会暴露出问题,当order表增长到十万级,并且查询后面几页的时候,执行速度明显变慢,可能降到100ms的量级,如果数据量增长到百万级,则耗时达到秒级,如果增长到千万级,那耗时就变得完全不可接受了(曾排查过这样的线上慢SQL)


 深入分析

方案1为啥在大表中表现这么差呢?我们可以来揣测一下MySql是怎么执行这个查询的

假设我们在user_id,created_time,以及【其它业务条件】建立了联合索引,当我要查找第100000条到100049条的记录时,因为MySql的索引是b+ tree结构,不像数组可以随机定位到第N条记录,它需要花不小的成本去找到N的位置,N越大,成本越大

抛开b+ tree的细节不讲,我们还可以借助统计表记录总数的SQL来理解

select count(1) from order

如果能非常高效地定位第N条记录,那么上述统计也能非常高效的执行,但实际上,在大表中统计记录总条数,也是非常慢的(本文是在InnoDB的场景下)

方案1低效的根本原因在于:定位到offset的成本过高,未能充分利用索引的有序性


方案2

索引(b+ tree)的特点在于,数据是有序的,虽然找到第N条记录的效率比较低,但找到某一条数据在索引中的位置,其效率是很高的(索引本来就是解决这个问题的)

我们换一种思路,每次取50条记录,第一次取的时候,指定从上次结束的位置继续往后取50条,这样,我们便可以利用上索引的有序性了

我们先看一个以id为序,进行分页查询的例子

select * from order where id > 'pre max id' order by id limit 50

第一次查询不用带条件,后续查询则传入前一次查询的最大id,简单分析可知,MySql在执行时,先定位到pre max id的位置(id是有序的,定位非常快),然后从这往后取50条记录即可,整个过程非常高效

我们回到最开始的问题,“按时间顺序分页查询,且主键不是时间维度递增”,此时我们不能用id作为分页的条件,因为按它去分页,便不是按时间顺序了,但也不能直接把id换成时间,因为时间可能会重复,我们来分析一下

idusernamecreated_time
xxxzhangsan2019-01-01
dddzhangsan2019-02-03
yyyzhangsan2019-02-03
abczhangsan2019-02-05
aaazhangsan2020-08-01

假如前一次分页的最后一条记录为id=ddd的这条(created_time为2019-02-03),下一次查询使用created_time>2019-02-03作为条件时,则会把id=yyy的这条记录漏掉,如果换成created_time>=2019-02-03也不行,id=ddd的这条记录就又被查出来了

对于这个数据遗漏或重复的问题,我看到一种解决方案是这样的:

分三种情况进行查询

  1. 首次查询,created_time>=’xxxx-xx-xx’,如果不要求以某时间开始,则无条件
    select * from order where user_id = xxx and 【其它业务条件】 and created_time >= 'xxxx-xx-xx' order by created_time, id limit pageSize
  2. 如果上次查询的记录条数等于pageSize,则用created_time和id的组合条件来查询,为了防止created_time在边界位置发生重复时漏掉数据
    select * from order where user_id = xxx and 【其它业务条件】 and created_time = 'created_time of latest recored' and id > 'id of latest recored' order by created_time, id limit pageSize
  3. 如果上次查询的记录数小于pageSize,并且上次查询是第二种查询,则仅用created_time来查询,
    select * from order where user_id = xxx and 【其它业务条件】 and created_time > 'created_time of latest recored' order by created_time, id limit pageSize 
注意:

created_time不能为null,否=和>会返回null,导致对应结果查不出来,如果存在为null的情况,则需要对部分查询把=和>分别改为is null和is not null来查询

点评

上述方法确实可以解决漏掉数据或重复的问题,并且也有着不错的性能,但缺点也比较明显,查询过于复杂,得分情况执行不同的SQL,并且分页不稳定,中间查询出来的记录数可能小于pageSize(如果没有重复项,那会多出一倍的结果为空的查询),实际上后面还有数据


进一步深入分析

我尝试在网上找过资料,只找到了以id为分页顺序,然后用id>’pre max id’这种方式来查,而我们要以可重复的created_time为分页顺序,如何写出简洁高效的SQL呢?

如果要成为一个优秀的程序员,我觉得分析&解决新问题的能力,是必不可少的,即使在网上能找到解决方案,优秀的分析能力也有助于借鉴并结合自己的场景,优化出更好的个性化方案。

我们在(user_id,created_time)建立了索引,并且我们知道InnoDB的辅助索引是包含了主键的,且主键一定不会重复,这意味着在索引上,每条记录的顺序是完全确定的,不存在重复的情况

我们要分页的顺序跟此索引的顺序是吻合的,只需要沿着索引,一批一批地取数据就可以了,这是一个对索引很直接的利用,为什么现在我没办法做到?

如果我是MySql的设计人员,针对这种很常见很直接的需求,我怎么去提供支持?还是说不支持?

我举一个例子,像java中的基于排序的TreeSet,我猜它一定有floor和ceiling这样的方法(返回Set中,大于或小于指定元素的第一个元素),这是基于排序的数据结构该有的东西,如果它没有,那早被人喷了然后加上去了

回到索引的话题,这种直接的需求,它应该支持,否则说不过去,现在的问题变成了:用什么语法来,来实现在组合索引上,基于组合(user_id,created_time,id的组合)顺序的遍历?

此时脑海里便回想起以前用过的(a,b) in ((1,2),(3,4),(7,4))这样的组合写法,然后猜测它也支持大于小于这类比较,跑去MySql中验证一下:

select (3,7)>(3,7),    (3,6)>(3,7),    (3,8)>(3,7),    (4,7)>(3,7),    (4,2)>(3,7);
返回:
0    0    1    1    1

如此一来,这问题就变得和id>’pre max id’这种一样简单了。

这种写法在官方文档中也找到了对应的资料,官方称这类运算为“行比较”(row comparisons)

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#operator_greater-than

看到这里,也许你跟我当时一样,即开心又兴奋,一个完美的方案就在眼前,然而MySql优化器没有我们想像的聪明,在“行比较”面前,就变成了二傻子,不能很好地使用索引了

此时我又回过头去试验了一下“行比较”对应的等价写法

(a,b)>(x,y)
等价于
a>x or (a=x and b>y)

发现这种看似很复杂且还有or的写法,竟然能很好地使用索引,效率非常高,即使像(a,b,c)>(x,y,z),改成很复杂的等价写法:

a>x or (a=x and (b>y or (b=y and c>z)))

也能很好地使用索引,此时真不知道该夸它还是骂它,唉

关于“行比较”的索引选择,在官网找到这样一份资料,文中说索引覆盖不到时,建议拆开成普通写法,这样看来,也许人家是有什么苦衷吧

https://dev.mysql.com/doc/refman/5.7/en/row-constructor-optimization.html

 方案3

由于有了a>x or (a=x and b>y)这种等价于组合比较的语法,且能正确地使用索引,所以可以写出高效且还算简洁的SQL

select * from order 

where user_id = xxx and 【其它业务条件】 and (created_time > 'created_time of latest recode' or (created_time = 'created_time of latest recode' and id > 'id of latest recode'))

order by created_time, id limit pageSize

此方式跟以id为序的分页查询是一样的,首次查询去掉组合条件即可,代码略显复杂,好在可以利用上组合索引,十分高效,耗时稳定,不会因为遍历到末尾而性能降低

遗憾地是,最优雅的方式却撞见个二傻子优化器,按理说用他们支持的特定语法(变化范围更小,模式更固定)去精确地表达查询需求,应该更容易被优化器识别出来并用最优方案去执行才说得通,结果却不如人意

希望以后能MySql更好地支持“行比较”吧(8.0.19仍存在问题)

 注意:

这里也不允许created_time为null,因为null值参与>和=运算,结果一律为null,即条件不成立,相应结果查不出来。

如果存在为null的情况,则要作一些调整,如果前一批数据的最后一条记录的created_time为null(null在索引中被视作极小值),则可以这样改:

(created_time is not null or (created_time is null and id > 'id of latest recode'))

仍旧可以走索引,实现高效分页查询


总结

方案1在小表的情况下,简单方便,只用传页码和页大小即可,还可以随机跳到指定页,具有一定优势

方案2和方案3在大表的情况下,有着优异的性能,以及稳定性,缺点是不能随机地跳转页面,需要传入上一页的排序字段。这个弊端在一定程度上可以规避,比如现在很多分页都是一页一页地往下翻,比如微博、朋友圈动态等,或者是分批处理全表数据,不需要随机跳转

细心的同学可能发现,where条件里还有【其它业务条件】,这样还能正常走索引吗?是否会发生全表扫描?这个问题其实是可以规避的,有空再写一篇执行计划并不完全可靠的案例。

注:执行计划有时不能正确地反映实际执行效果,所以我没有贴执行计划;我使用的MySql版本为5.7.23和8.0.19

题外话

方案3的写法是我自己琢磨出来的,在网上也没找到类似的资料,算独门秘技吧,除此之外,我觉得同样很有价值的是【进一步深入分析】中的思考过程,如果养成这种思考习惯,有利于创新,去解决别人没遇到过的问题,在未知的领域,知道该从哪个方向去寻找答案;或者找到新的方法更好地去解决旧问题。

如果本文有帮助到你,或者觉得有价值,麻烦点个赞,这样我会更有动力去更多地分享自己的经验


转载请注明出处及作者

原创文章,转载请注明: 转载自并发编程网 – ifeve.com本文链接地址: MySql大表分页(附独门秘技)

  • Trackback 关闭
  • 评论 (2)
    • xyt666
    • 2020/10/19 5:27下午

    如果上次查询的记录数小于pageSize,并且上次查询是第二种查询,则仅用created_time来查询,

    这里是什么意思?查询记录数小于pageSize不都是已经查到最后一页了吗?怎么还继续查什么?

    • 注意看下第二种查询的条件,是created_time = ‘created_time of latest recored’,是等于
      如果记录数等于pageSize,则意味着可能后面还有等于该时间的记录,所以要以该条件继续查
      如果记录数小于pageSize,则意味着后面没有等于该时间的记录了,应该把条件换成created_time > ‘created_time of latest recored’,继续查后面的大于该时间的记录

return top