当前位置:首页 > 开发 > 数据库 > 正文

关于MySQL count(distinct) 逻辑的一个bug

发表于: 2013-11-16   作者:丁林.tb   来源:转载   浏览次数:
摘要:   背景            客户报告了一个count(distinct)语句返回结果错误,实际结果存在值,但是用count(distinct)统计后返回的是0。          将问题简化后复现如下,影响已
  背景

 

         客户报告了一个count(distinct)语句返回结果错误,实际结果存在值,但是用count(distinct)统计后返回的是0

         将问题简化后复现如下,影响已知的所有版本。

 

drop table if exists tb;

set tmp_table_size=1024;

create table tb(id int auto_increment primary key, v varchar(32)) 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);

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 v) from tb;

   返回0

 

上述中update语句的目的是将所有的v值设为各不相同。

 

 

 

原因分析

 

         Count(distinct f)的语义就是计算字段f的去重总数,计算流程大致如下:

 

      流程一:

 

1、  构造一个unique集合A1(用tree实现)

2、  对每个值都试图插入集合A1

3、  若和A1中现有item重复则直接跳过,不重复则插入并+1

4、  完成后计算集合中元素个数。

 

   细心的同学会看到上面的语句中有一个set tmp_table_size的过程,集合A1并不能无限扩大,大小上限为tmp_table_size。若超过则上述流程变为

 

           流程二:

 

1、  构造一个unique 集合A1

2、  插入item过程中若大小超过tmp_table_size,则将A1暂时写到文件中,再构造集合A2

3、  重复步骤2直到所有的item插入完成

因此若item很多则可能重复生成多个集合A1An

4、  A1An作合并操作。由于只是每个集合A保证unique,因此需要做类似归并排序的操作(实际上不需要排序,只是扫一遍)

5、  因此合并操作需要一个临时内存,长度为n,单元大小为key_length key大小)。这个临时内存,用的也是tmp_table_size定义的大小。实际上在合并过程中还需要长为key_length的预留空间作临时内存保存。因此需要的空间为 (n+1)*key_length

6、  在进行合并前会判断tmp_table_size >=(n+1)*key_length 不满足则直接放弃合并。其结果就是返回为0

 

案例分析

 

以上面这个case为例。字段v的单key大小为65  (65 = 32*2+1) 加上tree节点字占空间24字节共89字节。单个集合只能放11item 1024/89), 因此n 24 24>=256/11, 在合并时需要 (24+1)*65= 1625字节的临时空间,大于1024,放弃合并。

 

Sql_big_tables

         实际上在最初处理这个问题时,俊达同学发现社区也有人讨论这个bug,并且指出在set sql_big_tables=on的时候,执行count(distinct)就能正确返回结果。原因就是在sql_big_tables=on的情况下,构造集合的方式是直接生成一个临时表,全部插入后直接计算临时表的大小作为结果,整个过程与tmp_table_size无关。

 

解决方法

         运维上,set sql_big_tables是一个方法,不过会影响性能。调高tmp_table_size算是正招。

         当然本质上这是一个bug

         代码上,对于已经走到合并操作的这个逻辑,如果tmp_table_size不够,应该直接申请新的临时空间用于合并,完成后释放。虽然会造成临时征用内存,不过以现有的逻辑来看,临时征用的内存已经不少了-_-

 

         另外一种时间换空间的方法,就是作多次合并。

 

         相比之下第一种改造比较简单安全,提交的patch用第一种思路完成。后面看看社区有没有别的方案。

关于MySQL count(distinct) 逻辑的一个bug

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
1 select column_2,count(column_2) as 'count(column_2)' ,count(column_1) as 'count(column_1)'
先说遇到的问题: java.lang.Thread.State: RUNNABLE at java.net.SocketInputStream.socketRead0(Na
关于Thold插件的安装、配置这里就不详细介绍了,这里以磁盘、内存使用情况报警为例子详细说明,阀值
遇到一个问题:(面试的问题) 题目: ========================= 用户名 商品 数量 A 甲 1 B 乙 2 C 丙
刚用到count中用distinct多个字段,找到了下面这条语句,记录一下 查询出全部记录数 SELECT COUNT(*
注:这些技术是通用的,只不过我们选择使用Postgres的语法。使用独特的pgAdminIII生成解释图形。  
1:COUNT DISTINCT SELECT COUNT(DISTINCT [QS_QuestionStem].Id) AS ReqCount1, [QS_QuestionStem]
今天在群里面遇到一个人问的问题 他只问了一个问题:为何查询了主键,却没有使用主键索引,因为我对
这是今天在微博的一个人发的,有人@我了,我就做了一个,看一下吧, 两次结果相差如此直大。。。。
前天在微薄上发了个优化器的问题,从评论来看,还是需要简单说明一下。 现象说明 其实这里主要要说
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号