客户报告了一个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很多则可能重复生成多个集合A1~An。
4、 对A1~An作合并操作。由于只是每个集合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字节。单个集合只能放11个item (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用第一种思路完成。后面看看社区有没有别的方案。
相关推荐
有这样的一个需求: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官方文档翻译
mysql中distinct用法【SQL中distinct的用法】.docx
总计COUNT_DISTINCT 此扩展提供了COUNT(DISTINCT ...)的替代方法,该方法对于大量数据通常会以排序和不良性能而告终。 职能 有两个多态聚合函数,用于处理按值传递的固定长度数据类型(即,在64位计算机上最多为...
解决MaxCompute SQL count distinct多个字段的方法按照惯性思维,统计一个字段去重后的条数我们的sql写起来如下:Distinct的作用
其原因是distinct只能返回它的目标字段,而无法返回其它字段,这个问题让我困扰了很久,用distinct不能解决的话,我只有用二重循环查询来解决,而这样对于一个数据量非常大的站来说,无疑是会直接影响到效率的。...
用Distinct在MySQL中查询多条不重复记录值,绝对的物有所值
mysql count详解 count函数是用来统计表中或数组...COUNT(DISTINCT 字段)这个优化仅适用于 MyISAM表, 原因是这些表类型会储存一个函数返回记录的精确数量,而且非常容易访问。 对于事务型的存储引擎(InnoDB, BDB), 存
完美解决distinct中使用多个字段的方法,完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法
主要介绍了MySQL关键字Distinct的详细介绍的相关资料,需要的朋友可以参考下
mysql distinct 语句的应用详解...
主要介绍了MySQL DISTINCT 的基本实现原理详解,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下
distinct简单来说就是用来去重的,而group by的设计目的则是用来聚合统计的,两者在能够实现的功能上有些相同之处,但应该仔细区分。 单纯的去重操作使用distinct,速度是快于group by的。 distinct支持单列、多列的...
主要介绍了MySQL中Distinct和Group By语句的基本使用教程,这里主要是针对查询结果去重的用法,需要的朋友可以参考下