`
丁林.tb
  • 浏览: 797183 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

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

阅读更多

背景

         上一篇博文(链接)介绍了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很多则可能重复生成多个集合A1An

4、  A1An作合并操作。由于只是每个集合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,正确

   可以看到,其实tb2tb1的数据内容是一样的,只是tb2没有索引且数据倒置插入,因此查询的最后一行的id1,满足id<=63, 结果记入就正确了。

 

解决方法

         调高tmp_table_size也是一种直接的方法,但是不治本,因为只要满足条件的行数足够多,就会出现这个问题。

 

         当然本质上这是一个bug

         代码上,对于已经走到合并操作的这个逻辑,其实前面在构造各个集合A1~An的时候,已经验证过条件合法,其实在合并的时候,可以直接做去重操作即可。

1
0
分享到:
评论
2 楼 丁林.tb 2013-11-28  
babaoqi 写道
版本5.5.29

如果在case when中加上else,则结果没有出错

测试sql:

SET @@session.tmp_table_size=1024;
DROP TABLE IF EXISTS tb;
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 'no else:64' AS ty,COUNT(DISTINCT (CASE WHEN id<=64 THEN id END)) AS cnt FROM tb
UNION ALL
SELECT 'no else:63' AS ty,COUNT(DISTINCT (CASE WHEN id<=63 THEN id END)) AS cnt FROM tb
UNION ALL
SELECT 'else:64' AS ty,COUNT(DISTINCT (CASE WHEN id<=64 THEN id ELSE 0 END)) AS cnt FROM tb
UNION ALL
SELECT 'else:63' AS ty,COUNT(DISTINCT (CASE WHEN id<=63 THEN id ELSE 0 END)) AS cnt FROM tb;

# 输出结果
ty	       cnt
no else:64	64
no else:63	0
else:64	        64
else:63	        64




是的,与分析一致
1 楼 babaoqi 2013-11-28  
版本5.5.29

如果在case when中加上else,则结果没有出错

测试sql:

SET @@session.tmp_table_size=1024;
DROP TABLE IF EXISTS tb;
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 'no else:64' AS ty,COUNT(DISTINCT (CASE WHEN id<=64 THEN id END)) AS cnt FROM tb
UNION ALL
SELECT 'no else:63' AS ty,COUNT(DISTINCT (CASE WHEN id<=63 THEN id END)) AS cnt FROM tb
UNION ALL
SELECT 'else:64' AS ty,COUNT(DISTINCT (CASE WHEN id<=64 THEN id ELSE 0 END)) AS cnt FROM tb
UNION ALL
SELECT 'else:63' AS ty,COUNT(DISTINCT (CASE WHEN id<=63 THEN id ELSE 0 END)) AS cnt FROM tb;

# 输出结果
ty	       cnt
no else:64	64
no else:63	0
else:64	        64
else:63	        64



相关推荐

    分析MySQL中优化distinct的技巧

    在MySQL数据库中,优化`DISTINCT`操作是一个关键的性能提升策略,特别是在处理大量数据时。上述场景中,用户遇到了一个问题:对一个10G以上的单表`user_access_xx_xx`执行`SELECT COUNT(DISTINCT nick)`以统计唯一...

    MongoDB教程之聚合(count、distinct和group)

    在MongoDB中,你可以直接调用`db.collection.count()`来获取一个集合中的所有文档数量,或者提供一个查询对象作为参数来统计满足该条件的文档数。例如,以下代码展示了如何使用`count`: ```bash # 查询空集合的...

    【DISTINCT】优化之MySQL官方文档翻译

    当`DISTINCT`与`ORDER BY`一起使用时,MySQL通常需要创建一个临时表来存储中间结果。这个过程可能会消耗大量的内存资源,并且可能导致查询执行时间变长。为了提高效率,MySQL会尽可能地避免创建临时表,特别是在以下...

    MySQL中distinct和count(*)的使用方法比较

    对于 `GROUP_CONCAT` 函数,它在 MySQL 4.1 及更高版本中可用,可以将多列的不重复值组合为一个字符串,但请注意,这将不再返回单个记录,而是组合后的结果。 其次,`COUNT(*)` 用于统计表中的行数。这个函数对全表...

    mysql中distinct用法【SQL中distinct的用法】.docx

    下面是一个简单的示例: ``` SELECT DISTINCT name FROM A; ``` 这条语句将返回表 A 中 name 列的所有唯一值。如果表 A 中有重复的 name 值,这条语句将忽略这些重复值,返回的结果将是 name 列的所有唯一值。 ...

    MySQL中索引优化distinct语句及distinct的多字段操作

    本文涉及一个不能利用索引完成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中查询多条不重复记录值,绝对的物有所值

    可以看到,虽然`name`列中有重复值,但通过`COUNT(DISTINCT name)`,我们可以准确地知道每个名字的出现次数为1,这是`DISTINCT`与`GROUP BY`完美配合的一个实例。 ### 结合`GROUP_CONCAT`处理多个字段 在复杂的...

    完美解决distinct中使用多个字段的方法

    完美解决distinct中使用多个字段的方法,完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法

    小度写范文【SQL中distinct的用法】mysql中distinct用法模板.pdf

    在SQL查询中,`DISTINCT`关键字是一个非常重要的部分,它允许我们从查询结果中去除重复的记录,只返回唯一的、不同的值。在MySQL中,`DISTINCT`的使用方式非常灵活,可以从单个列到多个列进行操作,也可以与聚合函数...

    解析mysql不重复字段值求和

    其原因是distinct只能返回它的目标字段,而无法返回其它字段,这个问题让我困扰了很久,用distinct不能解决的话,我只有用二重循环查询来解决,而这样对于一个数据量非常大的站来说,无疑是会直接影响到效率的。...

    小度写范文【SQL中distinct的用法】mysql中distinct用法模板.docx

    但需要注意的是,`COUNT(DISTINCT)`仅能用于一个字段,不是所有数据库系统都支持对多个字段进行统计,如Access。如果需要对多列进行统计,可以使用嵌套查询,如下: ```sql SELECT COUNT(*) FROM (SELECT ...

    count_distinct:在PostgreSQL中扩展以COUNT(DISTINCT ...)聚合的替代方法

    总计COUNT_DISTINCT 此扩展提供了COUNT(DISTINCT ...)的替代方法,该方法对于大量数据通常会以排序和不良性能而告终。 职能 有两个多态聚合函数,用于处理按值传递的固定长度数据类型(即,在64位计算机上最多为...

    MySQL DISTINCT 的基本实现原理详解

    例如,当查询只包含DISTINCT关键字和一个被索引的列时,MySQL会遍历索引,对每个不同值形成一个组,然后返回每个组的第一个记录。在EXPLAIN计划中,`Extra`字段会显示"Using index for group-by",这意味着MySQL利用...

    alibaba-developer#MaxCompute#解决MaxCompute SQL count distinct多个字段

    解决MaxCompute SQL count distinct多个字段的方法按照惯性思维,统计一个字段去重后的条数我们的sql写起来如下:Distinct的作用

    vc 小技巧 mysql distinct 语句

    `DISTINCT`是SQL语言中的一个关键字,用于从查询结果中消除重复的行。当你执行一个`SELECT DISTINCT`语句时,数据库将返回所有不重复的唯一值。例如,假设我们有一个包含员工信息的`employees`表,其中`name`字段...

    mysql-常见问题,索引优化

    1. 关于MySQL `count(distinct)` 的逻辑bug 在执行`count(distinct)`查询时,MySQL可能会遇到性能问题,尤其是在处理大量数据时。这是因为MySQL在计算唯一值时没有利用索引,导致全表扫描。为优化此操作,可以尝试...

    Mysql中distinct与group by的去重方面的区别

    例如,如果你有一个包含员工姓名的表,`SELECT DISTINCT name FROM employees` 将返回所有不同的员工姓名,忽略重复。对于多列去重,`DISTINCT` 同样有效,如 `SELECT DISTINCT name, department FROM employees` 将...

    MySQL关键字Distinct的详细介绍

    例如,如果你有一个包含员工姓名的表,而你想找出所有不重复的员工姓名,你可以这样写: ```sql SELECT DISTINCT name FROM employees; ``` 上述语句会返回表`employees`中所有唯一的`name`值,重复的名字只会...

Global site tag (gtag.js) - Google Analytics