mysql踩坑之count distinct多列問題

更新時(shí)間：2023年03月23日 08:50:12 作者：山木枝

這篇文章主要介紹了mysql踩坑之count distinct多列問題，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

背景

有個(gè)小伙伴在用mysql做統(tǒng)計(jì)分析的時(shí)候發(fā)現(xiàn)有行數(shù)據(jù)憑空消失了。

最近我剛好在學(xué)習(xí)相關(guān)內(nèi)容，所以對(duì)這個(gè)問題比較感興趣，就研究了一下。

復(fù)現(xiàn)的測(cè)試數(shù)據(jù)庫如下所示：

CREATE TABLE `test_distinct` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  `b` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

表內(nèi)測(cè)試數(shù)據(jù)如下，現(xiàn)在我們需要統(tǒng)計(jì)這三列去重后的列的數(shù)量。

問題分析

小伙伴給了我四條用來定位問題的查詢語句

SELECT COUNT(*) AS cnt FROM test_distinct;
SELECT COUNT(DISTINCT id, a, b) as cnt FROM test_distinct;
SELECT id, a, b, COUNT(*) AS cnt FROM test_distinct GROUP BY id, a, b HAVING cnt > 1;
SELECT 
	l.id AS l_id,
	l.a AS l_a,
	l.b AS l_b,
	r.id AS r_id,
	r.a AS r_a,
	r.b AS r_b
FROM test_distinct l LEFT JOIN test_distinct r
ON l.id = r.id AND l.a = r.a AND l.b = r.b
WHERE r.id is NULL or r.id = 'null';

查詢結(jié)果，如下所示：

注意?。?！從測(cè)試數(shù)據(jù)很快就能大概猜出問題在哪，但是原來表中數(shù)據(jù)是有3萬多條，無法用肉眼查看數(shù)據(jù)。

上面查詢結(jié)果違反直覺的點(diǎn)有兩個(gè)：

第二條去重統(tǒng)計(jì)后數(shù)據(jù)少了一條，但是，第三條數(shù)據(jù)的結(jié)果顯示并沒有相同的數(shù)據(jù)。
用同一張表做左外連接出現(xiàn)了驅(qū)動(dòng)表有數(shù)據(jù)，而被驅(qū)動(dòng)表為空的情況。

先看第二個(gè)問題，官方文檔上有如下解釋：

與ON一起使用的search_condition和WHERE子句中使用的條件表達(dá)式一樣。通常，ON子句用于指定如何連接表的條件，WHERE子句限制要包含在結(jié)果集中的行。
如果對(duì)于LEFT JOIN中ON或USING部分中的條件，右表沒有匹配的行，則右表使用所有列設(shè)置為NULL。
不能使用算術(shù)比較運(yùn)算符（如=，<或<>）來比較NULL。

SELECT NULL = NULL;
SELECT NULL IS NULL;

所以問題二在于NULL=NULL的結(jié)果永遠(yuǎn)為False，也就導(dǎo)致兩行原本相等的數(shù)據(jù)結(jié)果卻不相等。

可是這并沒有解決第一個(gè)問題：為什么去重后有一條數(shù)據(jù)消失了。但是，我們可以猜測(cè)消失的數(shù)據(jù)很有可能和NULL值有關(guān)系。

我們將count和distinct兩個(gè)操作分開：

SELECT COUNT(*) as cnt FROM (SELECT  DISTINCT id, a, b FROM test_distinct) as tmp;

嗯？結(jié)果是正確的，那就說明count(distinct expr)生成的查詢計(jì)劃可能和我們想象的不一樣，并不是先去重再統(tǒng)計(jì)，使用explain分析一下兩條語句的查詢計(jì)劃，如下所示：

從表中可以看到，mysql執(zhí)行引擎直接將count(distinct expr)作為一個(gè)查詢，查看官方文檔：

解決辦法

至此問題才終于弄清楚了。解決這個(gè)問題的辦法有兩種，第一種就是上述的先去重后統(tǒng)計(jì)，第二種可以利用IFNULL()函數(shù):

SELECT COUNT(DISTINCT id, a, IFNULL(b, '0')) as cnt FROM test_distinct;

另外補(bǔ)充一點(diǎn)，count()嘚瑟使用：

SELECT id, a, b, COUNT(*) FROM test_distinct GROUP BY id, a, b;
SELECT id, a, b, COUNT(b) FROM test_distinct GROUP BY id, a, b;

知識(shí)點(diǎn)

不能使用算術(shù)比較運(yùn)算符（如=，<或<>）來比較空值；
count(distinct expr)返回expr列中不同的且非空的行數(shù)；
COUNT()有兩個(gè)非常不同的作用：它可以統(tǒng)計(jì)某個(gè)列值的數(shù)量，也可以統(tǒng)計(jì)行數(shù)。在統(tǒng)計(jì)列值時(shí)要求列值是非空的（不統(tǒng)計(jì)NULL）。如果在COUNT()的括號(hào)中定了列或者列表達(dá)式，則統(tǒng)計(jì)的就是這個(gè)表達(dá)式有值的結(jié)果數(shù)。COUNT()的另一個(gè)作用是統(tǒng)計(jì)結(jié)果集的行數(shù)。當(dāng)MySQL確認(rèn)括號(hào)內(nèi)的表達(dá)式值不可能為空時(shí)，實(shí)際上就是在統(tǒng)計(jì)行數(shù)。最簡(jiǎn)單的就是當(dāng)我們使用COUNT()的時(shí)候，這種情況下通配符并不像我們猜想的那樣擴(kuò)展成所有的列，實(shí)際上，他會(huì)忽略所有列而直接統(tǒng)計(jì)所有的行數(shù)——《高性能MySQL》；
在InnoDB中，SELECT COUNT(*)和SELECT COUNT(1)處理方式一樣，沒有性能差異。