背景
上一篇博文(链接)介绍了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的时候,已经验证过条件合法,其实在合并的时候,可以直接做去重操作即可。
相关推荐
在MySQL数据库中,优化`DISTINCT`操作是一个关键的性能提升策略,特别是在处理大量数据时。上述场景中,用户遇到了一个问题:对一个10G以上的单表`user_access_xx_xx`执行`SELECT COUNT(DISTINCT nick)`以统计唯一...
在MongoDB中,你可以直接调用`db.collection.count()`来获取一个集合中的所有文档数量,或者提供一个查询对象作为参数来统计满足该条件的文档数。例如,以下代码展示了如何使用`count`: ```bash # 查询空集合的...
当`DISTINCT`与`ORDER BY`一起使用时,MySQL通常需要创建一个临时表来存储中间结果。这个过程可能会消耗大量的内存资源,并且可能导致查询执行时间变长。为了提高效率,MySQL会尽可能地避免创建临时表,特别是在以下...
对于 `GROUP_CONCAT` 函数,它在 MySQL 4.1 及更高版本中可用,可以将多列的不重复值组合为一个字符串,但请注意,这将不再返回单个记录,而是组合后的结果。 其次,`COUNT(*)` 用于统计表中的行数。这个函数对全表...
下面是一个简单的示例: ``` SELECT DISTINCT name FROM A; ``` 这条语句将返回表 A 中 name 列的所有唯一值。如果表 A 中有重复的 name 值,这条语句将忽略这些重复值,返回的结果将是 name 列的所有唯一值。 ...
本文涉及一个不能利用索引完成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,...
可以看到,虽然`name`列中有重复值,但通过`COUNT(DISTINCT name)`,我们可以准确地知道每个名字的出现次数为1,这是`DISTINCT`与`GROUP BY`完美配合的一个实例。 ### 结合`GROUP_CONCAT`处理多个字段 在复杂的...
完美解决distinct中使用多个字段的方法,完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法完美解决distinct中使用多个字段的方法
在SQL查询中,`DISTINCT`关键字是一个非常重要的部分,它允许我们从查询结果中去除重复的记录,只返回唯一的、不同的值。在MySQL中,`DISTINCT`的使用方式非常灵活,可以从单个列到多个列进行操作,也可以与聚合函数...
其原因是distinct只能返回它的目标字段,而无法返回其它字段,这个问题让我困扰了很久,用distinct不能解决的话,我只有用二重循环查询来解决,而这样对于一个数据量非常大的站来说,无疑是会直接影响到效率的。...
但需要注意的是,`COUNT(DISTINCT)`仅能用于一个字段,不是所有数据库系统都支持对多个字段进行统计,如Access。如果需要对多列进行统计,可以使用嵌套查询,如下: ```sql SELECT COUNT(*) FROM (SELECT ...
总计COUNT_DISTINCT 此扩展提供了COUNT(DISTINCT ...)的替代方法,该方法对于大量数据通常会以排序和不良性能而告终。 职能 有两个多态聚合函数,用于处理按值传递的固定长度数据类型(即,在64位计算机上最多为...
例如,当查询只包含DISTINCT关键字和一个被索引的列时,MySQL会遍历索引,对每个不同值形成一个组,然后返回每个组的第一个记录。在EXPLAIN计划中,`Extra`字段会显示"Using index for group-by",这意味着MySQL利用...
解决MaxCompute SQL count distinct多个字段的方法按照惯性思维,统计一个字段去重后的条数我们的sql写起来如下:Distinct的作用
`DISTINCT`是SQL语言中的一个关键字,用于从查询结果中消除重复的行。当你执行一个`SELECT DISTINCT`语句时,数据库将返回所有不重复的唯一值。例如,假设我们有一个包含员工信息的`employees`表,其中`name`字段...
1. 关于MySQL `count(distinct)` 的逻辑bug 在执行`count(distinct)`查询时,MySQL可能会遇到性能问题,尤其是在处理大量数据时。这是因为MySQL在计算唯一值时没有利用索引,导致全表扫描。为优化此操作,可以尝试...
例如,如果你有一个包含员工姓名的表,`SELECT DISTINCT name FROM employees` 将返回所有不同的员工姓名,忽略重复。对于多列去重,`DISTINCT` 同样有效,如 `SELECT DISTINCT name, department FROM employees` 将...
例如,如果你有一个包含员工姓名的表,而你想找出所有不重复的员工姓名,你可以这样写: ```sql SELECT DISTINCT name FROM employees; ``` 上述语句会返回表`employees`中所有唯一的`name`值,重复的名字只会...