COUNT(列名)、COUNT(常量)和COUNT(*)之間的區(qū)別

COUNT( * )和COUNT(1) ：在InnoDB中COUNT(*)和COUNT(1)實(shí)現(xiàn)上沒(méi)有區(qū)別，而且效率一樣，統(tǒng)計(jì)結(jié)果中，會(huì)包含值為NULL的行數(shù)COUNT(字段)： 需要進(jìn)行字段的非NULL判斷，所以效率會(huì)低一些。

因?yàn)镃OUNT( * )是SQL92定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)法，并且效率高，所以沒(méi)有某個(gè)字段不為null 的統(tǒng)計(jì)行數(shù)需求請(qǐng)直接使用COUNT( * )查詢表的行數(shù)！

COUNT(id)和COUNT()以外，還可以使用COUNT(常量)（如COUNT(1)）來(lái)統(tǒng)計(jì)行數(shù)，那么這三條SQL語(yǔ)句有什么區(qū)別呢？

到底哪種效率更高呢？

為什么《阿里巴巴Java開(kāi)發(fā)手冊(cè)》中強(qiáng)制要求不讓使用 COUNT(列名)或 COUNT(常量)來(lái)替代 COUNT()呢？

1、COUNT(列名) ，返回SELECT語(yǔ)句檢索的行中expr的值不為NULL的數(shù)量。結(jié)果是一個(gè)BIGINT值。

2、但是，值得注意的是，COUNT( * ) 和count(1)的統(tǒng)計(jì)結(jié)果中，會(huì)包含值為NULL的行數(shù)。

那么列名、 常量 和 *這三個(gè)條件中，常量 是一個(gè)固定值，肯定不為NULL。*可以理解為查詢整行，所以肯定也不為NULL，那么就只有列名的查詢結(jié)果有可能是NULL了。

所以， COUNT(常量) 和 COUNT( * )表示的是直接查詢符合條件的數(shù)據(jù)庫(kù)表的行數(shù)。而COUNT(列名)表示的是查詢符合條件的列的值不為NULL的行數(shù)。

COUNT(*)是SQL92定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)法，因?yàn)樗菢?biāo)準(zhǔn)語(yǔ)法，所以MySQL數(shù)據(jù)庫(kù)對(duì)他進(jìn)行過(guò)很多優(yōu)化。

COUNT(*)和COUNT(1)

COUNT(*)和COUNT(1)這二者到底有沒(méi)有區(qū)別，網(wǎng)上的說(shuō)法眾說(shuō)紛紜。

有的說(shuō)COUNT(*)執(zhí)行時(shí)會(huì)轉(zhuǎn)換成COUNT(1)，所以COUNT(1)少了轉(zhuǎn)換步驟，所以更快。

還有的說(shuō)，因?yàn)镸ySQL針對(duì)COUNT()做了特殊優(yōu)化，所以COUNT()更快。

那么，到底哪種說(shuō)法是對(duì)的呢？

看下MySQL官方文檔是怎么說(shuō)的：

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

畫(huà)重點(diǎn)：

same way , no performance difference。

所以，對(duì)于COUNT(1)和COUNT(*)，MySQL的優(yōu)化是完全一樣的，根本不存在誰(shuí)比誰(shuí)快！

COUNT( * )和COUNT(字段)

COUNT(字段)，他的查詢就比較簡(jiǎn)單粗暴了，就是進(jìn)行全表掃描，然后判斷指定字段的值是不是為NULL，不為NULL則累加。

相比COUNT()，COUNT(字段)多了一個(gè)步驟就是判斷所查詢的字段是否為NULL，所以他的性能要比COUNT()慢。

COUNT(*)的優(yōu)化

MySQL中比較常用的執(zhí)行引擎就是InnoDB和MyISAM。

MyISAM

不包含WHERE或GROUP BY等條件

MyISAM不支持事務(wù)，MyISAM中的鎖是表級(jí)鎖；而InnoDB支持事務(wù)，并且支持行級(jí)鎖。

因?yàn)镸yISAM的鎖是表級(jí)鎖，所以同一張表上面的操作需要串行進(jìn)行，所以，MyISAM做了一個(gè)簡(jiǎn)單的優(yōu)化，那就是它可以把表的總行數(shù)單獨(dú)記錄下來(lái)，如果從一張表中使用COUNT(*)進(jìn)行查詢的時(shí)候，可以直接返回這個(gè)記錄下來(lái)的數(shù)值就可以了，當(dāng)然，前提是不能有where條件。

MyISAM之所以可以把表中的總行數(shù)記錄下來(lái)供COUNT(*)查詢使用，那是因?yàn)镸yISAM數(shù)據(jù)庫(kù)是表級(jí)鎖，不會(huì)有并發(fā)的數(shù)據(jù)庫(kù)行數(shù)修改，所以查詢得到的行數(shù)是準(zhǔn)確的。

InnoDB

不包含WHERE或GROUP BY等條件

InnoDB來(lái)說(shuō)，就不能做這種緩存操作了，因?yàn)镮nnoDB支持事務(wù)，其中大部分操作都是行級(jí)鎖，所以可能表的行數(shù)可能會(huì)被并發(fā)修改，那么緩存記錄下來(lái)的總行數(shù)就不準(zhǔn)確了。

InnoDB中索引分為主鍵索引和非主鍵索引主鍵引的葉子節(jié)點(diǎn)中保存的是整行記錄，而非主鍵索引的葉子節(jié)點(diǎn)中保存的是該行記錄的主鍵的值。

所以，相比之下，非主鍵索引要比主鍵索引小很多，所以MySQL會(huì)優(yōu)先選擇最小的非主鍵索引來(lái)掃表。所以，當(dāng)我們建表的時(shí)候，除了主鍵索引以外，創(chuàng)建一個(gè)非主鍵索引還是有必要的。

count(*)、count(列名)、count(常量)的理解

內(nèi)容來(lái)之阿里巴巴的開(kāi)發(fā)手冊(cè)：

1.  【強(qiáng)制】不要使用 count(列名)或 count(常量)來(lái)替代 count(*)，count(*)是 SQL92 定義的

標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)法，跟數(shù)據(jù)庫(kù)無(wú)關(guān)，跟 NULL 和非 NULL 無(wú)關(guān)。

說(shuō)明：count(*)會(huì)統(tǒng)計(jì)值為 NULL 的行，而 count(列名)不會(huì)統(tǒng)計(jì)此列為 NULL 值的行。

2.  【強(qiáng)制】count(distinct col) 計(jì)算該列除 NULL 之外的不重復(fù)行數(shù)，注意 count(distinct col1, col2) 如果其中一列全為 NULL，那么即使另一列有不同的值，也返回為 0。

3.  【強(qiáng)制】當(dāng)某一列的值全是 NULL 時(shí)，count(col)的返回結(jié)果為 0，但 sum(col)的返回結(jié)果 為 NULL，因此使用 sum()時(shí)需注意 NPE 問(wèn)題。

正例：使用如下方式來(lái)避免 sum 的 NPE 問(wèn)題：SELECT IFNULL(SUM(column), 0) FROM table;

4.  【強(qiáng)制】使用 ISNULL()來(lái)判斷是否為 NULL 值。

說(shuō)明：NULL 與任何值的直接比較都為 NULL。

1） NULL<>NULL 的返回結(jié)果是 NULL，而不是 false。

2） NULL=NULL 的返回結(jié)果是 NULL，而不是 true。

3） NULL<>1 的返回結(jié)果是 NULL，而不是 true。

如何在同一個(gè)查詢中統(tǒng)計(jì)同，一個(gè)列的不同值的數(shù)量，以減少查詢的語(yǔ)句量。

例如：

通過(guò)一個(gè)查詢返回各種不同顏色的商品數(shù)量 。

select count(color='blue' or color='red') from items; (不滿足條件，因?yàn)闊o(wú)法區(qū)分不同顏色的商品數(shù)量)
select count(*) from items where color = 'blue' and color = 'red';(不滿足條件，顏色的條件是互斥的)

下面為滿足條件的例子：

select sum(if(color = 'blue', 1, 0)) as blue, sum(if(color = 'red', 1, 0)) as red from items;
select count(color = 'blue' or NULL) as blue, count(color = 'red' or NULL) as red from items;

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論