背景
上一篇博文(链接)介绍了count distinct的一个bug。解决完以后发现客户的SQL语句仍然返回错误结果(0), 再查原因,发现了另外一个bug。也就是说,这个SQL语句触发了两个bug -_-
这里只说第二个,将问题简化后复现如下,影响已知的所有版本 。
drop table if exists tb; set tmp_table_size=1024; create table tb(id int auto_increment primary key, v varchar(32))engine=myisam charset=gbk; insert into tb(v) values("aaa"); insert into tb(v) (select v from tb); insert into tb(v) (select v from tb); insert into tb(v) (select v from tb); insert into tb(v) (select v from tb); insert into tb(v) (select v from tb); insert into tb(v) (select v from tb);
update tb set v=concat(v, id); select count(distinct case when id<=64 then id end) from tb; 返回64,正确 select count(distinct case when id<=63 then id end) from tb; 返回0 |
上述中update语句的目的是将所有的v值设为各不相同。
与上个bug类似,5.5+的版本直接复现;5.1版本需要修改的是max_heap_table_size参数,而由于max_heap_table_size的最小值限制不能设置为1024,需要的测试数据量大些,但原理类似。
原因分析
Count(distinct case when xxx then f end)的语义就是计算字段f的去重总数,计算流程细节参看前一篇。这里直接给出tmp_table_size不够大时的流程,便于说明此问题。
流程:
1、 构造一个unique 集合A1, 将满足条件的结果插入A1中(计算了case when之后的值)
2、 插入item过程中若大小超过tmp_table_size,则将A1暂时写到文件中,再构造集合A2
3、 重复步骤2直到所有的item插入完成
因此若item很多则可能重复生成多个集合A1~An。
4、 对A1~An作合并操作。由于只是每个集合A保证unique,因此需要做类似归并排序的操作(实际上不需要排序,只是扫一遍)
5、 合并加和操作本来只需要去重和去掉NULL值即可,但为了复用代码,对于每个item,重新计算了一次结果的合法性,也就是,再判断一次case when是否正确。
6、 不幸的是,计算结果合法性的这些case when,其实是共同的一个:最后一行。
因此最后的结果是正确值还是0,就取决于最后一行的case when的结果。
案例分析
以上面这个case为例。由于使用主键,最后的一行必然是id=64的那一行。这样在合并的时候,若条件是id<=64 这些值都被认为符合条件可以合并。而最后一个语句的情况,最后一行id<=64不成立
作为验证可以看一下这个case
CREATE TABLE `tb2` ( `id` int(11) NOT NULL , `v` varchar(32) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=gbk; insert into tb2 (select * from tb order by id desc); select count(distinct case when id<=63 then id end) from tb2; 返回63,正确 |
可以看到,其实tb2和tb1的数据内容是一样的,只是tb2没有索引且数据倒置插入,因此查询的最后一行的id是1,满足id<=63, 结果记入就正确了。
解决方法
调高tmp_table_size也是一种直接的方法,但是不治本,因为只要满足条件的行数足够多,就会出现这个问题。
当然本质上这是一个bug。
代码上,对于已经走到合并操作的这个逻辑,其实前面在构造各个集合A1~An的时候,已经验证过条件合法,其实在合并的时候,可以直接做去重操作即可。
相关推荐
有这样的一个需求:select count(distinct nick) from user_access_xx_xx; 这条sql用于统计用户访问的uv,由于单表的数据量在10G以上,即使在user_access_xx_xx上加上nick的索引, 通过查看执行计划,也为全索引扫描...
1.在count 不重复的记录的时候能用到,比如SELECT COUNT( DISTINCT id ) FROM tablename;就是计算talbebname表中id不同的记录有多少条。 2,在需要返回记录不同的id的具体值的时候可以用,比如SELECT DISTINCT ...
主要介绍了MongoDB教程之聚合,MongoDB除了基本的查询功能之外,还提供了强大的聚合功能,这里主要介绍count、distinct和group,需要的朋友可以参考下
本文涉及一个不能利用索引完成DISTINCT操作的实例. 实例1 使用索引优化DISTINCT操作 create table m11 (a int, b int, c int, d int, primary key(a)) engine=INNODB; insert into m11 values (1,1,1,1),(2,2,2,...
【DISTINCT】优化之MySQL官方文档翻译
其原因是distinct只能返回它的目标字段,而无法返回其它字段,这个问题让我困扰了很久,用distinct不能解决的话,我只有用二重循环查询来解决,而这样对于一个数据量非常大的站来说,无疑是会直接影响到效率的。...
mysql中distinct用法【SQL中distinct的用法】.docx
总计COUNT_DISTINCT 此扩展提供了COUNT(DISTINCT ...)的替代方法,该方法对于大量数据通常会以排序和不良性能而告终。 职能 有两个多态聚合函数,用于处理按值传递的固定长度数据类型(即,在64位计算机上最多为...
解决MaxCompute SQL count distinct多个字段的方法按照惯性思维,统计一个字段去重后的条数我们的sql写起来如下:Distinct的作用
用Distinct在MySQL中查询多条不重复记录值,绝对的物有所值
完美解决distinct中使用多个字段的方法,完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法
mysql count详解 count函数是用来统计表中或数组...COUNT(DISTINCT 字段)这个优化仅适用于 MyISAM表, 原因是这些表类型会储存一个函数返回记录的精确数量,而且非常容易访问。 对于事务型的存储引擎(InnoDB, BDB), 存
mysql distinct 语句的应用详解...
主要介绍了MySQL关键字Distinct的详细介绍的相关资料,需要的朋友可以参考下
主要介绍了MySQL DISTINCT 的基本实现原理详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
distinct简单来说就是用来去重的,而group by的设计目的则是用来聚合统计的,两者在能够实现的功能上有些相同之处,但应该仔细区分。 单纯的去重操作使用distinct,速度是快于group by的。 distinct支持单列、多列的...
主要介绍了MySQL中Distinct和Group By语句的基本使用教程,这里主要是针对查询结果去重的用法,需要的朋友可以参考下