这篇要讲个跟排序相关的知识点。
先来构造一下这个bug,我用的版本是MySQL 8.0.39。
构造数据过程如下:
这个表上有id,a,b三个字段,只有一个主键id,没有其他索引,插入32768行。
然后执行
select count(*) from (
(select id,a from t1 order by a limit 10080)
union
(select id,a from t1 order by a limit 10080, 10)
)a;
这个语句的语义上看, 第一个子查询返回前10080行,第二个子查询返回第10081到第10090行。
返回字段里面包含主键字段id,因此这两个子查询的数据本应该是没有重复的,也就是说整个语句的返回结果是10090.
我们来看一下实际的执行结果。
可以看到,少了9行,也就说两个子查询里出现了重复的行。这个对应到业务场景里,就是有些静态数据表,按顺序分页查询出来,得到的不是逻辑上完整的数据。
以下是排查过程和原因分析。
由于a上没有索引,这两个子查询都使用了排序。
我们用optimizer_trace 看执行过程信息里,看看有没有信息提供线索。
set optimizer_trace=1;
select count(*) ...
select * from information_schema.optimizer_trace\G
可以看到第一个子查询用了优先队列排序,而第二个没有。
MySQL里的优先队列排序,就是堆排序,语句里是order by a,因此用的是最大堆排序。我们简单说下这个过程。
先用前10080行构造了一个大顶堆,这个堆确保每个父节点的值,都不小于子节点的值。这样,堆订的值就是整个堆最大的,假设为M。
之后继续读入下一个值,假设为N。
如果N>M, 则表示N比堆里所有的值都大,肯定不会出现在查询结果里,直接丢掉;
如果N<=M, 那么当前堆里的最大值,也就是堆订的N,就不会出现在结果里,用M替代N。
当然这时候堆顶M可能不是堆里最大的值了, 再通过堆调整,让这个堆恢复最大堆的特征。
可以看到,完成这个替换和调整动作后,就又确保了,当前堆里的10080个数据,是所有遍历过的数据里,最小的那一批。
等到所有满足查询条件的数据行依次经过上面的流程后, 最终堆里留下的10080行数据,就是满足语义的,可以用来返回。
维护这个堆是需要内存的, 内存的大小总有个限制,就是sort_buffer_size定义的大小,在这个版本里,默认值是32k。
就是说,如果这个堆能够完全放入内存,MySQL就会选择堆排序。
如果超过呢?就放弃堆排序,使用时间复杂度是O(nlogn)的排序算法。
你一定猜到了为什么我要构造10080这个数字。
第二个子查询的逻辑,需要先排序得到前10090行,而就是这多出来的10行,导致sort_buffer_size不够用。
也就是说,这两个子查询使用了不同的排序算法,导致第二个子查询的那10行里,有9行是在第一个子查询的结果里。
因为union是要去重的,所以最终count(*)的结果就是10081。
小结:
了解了这个原因,我们就知道如果要通过多次分页来完整遍历一个表数据,就需要在order by里加上能够严格排序的字段。
然后再回顾这个一眼看上去就是个bug的结果,好像又能接受了。
==EOF==
还没有加入技术交流群的小伙伴,可以扫下面这个码