前言

當(dāng)我們對(duì)一張數(shù)據(jù)表中的記錄進(jìn)行統(tǒng)計(jì)的時(shí)候，習(xí)慣都會(huì)使用 count 函數(shù)來(lái)統(tǒng)計(jì)，但是 count 函數(shù)傳入的參數(shù)有很多種，比如 count(1)、count(*)、count(字段) 等。

到底哪種效率是最好的呢？是不是 count(*) 效率最差？

我曾經(jīng)以為 count(*) 是效率最差的，因?yàn)檎J(rèn)知上 selete * from t 會(huì)讀取所有表中的字段，所以凡事帶有 * 字符的就覺(jué)得會(huì)讀取表中所有的字段，當(dāng)時(shí)網(wǎng)上有很多博客也這么說(shuō)。

但是，當(dāng)我深入 count 函數(shù)的原理后，被啪啪啪的打臉了！

不多說(shuō)， 發(fā)車(chē)！

哪種 count 性能最好？

哪種 count 性能最好？

我先直接說(shuō)結(jié)論：

要弄明白這個(gè)，我們得要深入 count 的原理，以下內(nèi)容基于常用的 innodb 存儲(chǔ)引擎來(lái)說(shuō)明。

count() 是什么？

count() 是一個(gè)聚合函數(shù)，函數(shù)的參數(shù)不僅可以是字段名，也可以是其他任意表達(dá)式，該函數(shù)作用是統(tǒng)計(jì)符合查詢條件的記錄中，函數(shù)指定的參數(shù)不為 NULL 的記錄有多少個(gè)。

假設(shè) count() 函數(shù)的參數(shù)是字段名，如下：

select count(name) from t_order;

這條語(yǔ)句是統(tǒng)計(jì)「 t_order 表中，name 字段不為 NULL 的記錄」有多少個(gè)。也就是說(shuō)，如果某一條記錄中的 name 字段的值為 NULL，則就不會(huì)被統(tǒng)計(jì)進(jìn)去。

再來(lái)假設(shè) count() 函數(shù)的參數(shù)是數(shù)字 1 這個(gè)表達(dá)式，如下：

select count(1) from t_order;

這條語(yǔ)句是統(tǒng)計(jì)「 t_order 表中，1 這個(gè)表達(dá)式不為 NULL 的記錄」有多少個(gè)。

1 這個(gè)表達(dá)式就是單純數(shù)字，它永遠(yuǎn)都不是 NULL，所以上面這條語(yǔ)句，其實(shí)是在統(tǒng)計(jì) t_order 表中有多少個(gè)記錄。

count(主鍵字段) 執(zhí)行過(guò)程是怎樣的？

在通過(guò) count 函數(shù)統(tǒng)計(jì)有多少個(gè)記錄時(shí)，MySQL 的 server 層會(huì)維護(hù)一個(gè)名叫 count 的變量。

server 層會(huì)循環(huán)向 InnoDB 讀取一條記錄，如果 count 函數(shù)指定的參數(shù)不為 NULL，那么就會(huì)將變量 count 加 1，直到符合查詢的全部記錄被讀完，就退出循環(huán)。最后將 count 變量的值發(fā)送給客戶端。

InnoDB 是通過(guò) B+ 樹(shù)來(lái)保持記錄的，根據(jù)索引的類(lèi)型又分為聚簇索引和二級(jí)索引，它們區(qū)別在于，聚簇索引的葉子節(jié)點(diǎn)存放的是實(shí)際數(shù)據(jù)，而二級(jí)索引的葉子節(jié)點(diǎn)存放的是主鍵值，而不是實(shí)際數(shù)據(jù)。

用下面這條語(yǔ)句作為例子：

//id 為主鍵值
select count(id) from t_order;

如果表里只有主鍵索引，沒(méi)有二級(jí)索引時(shí)，那么，InnoDB 循環(huán)遍歷聚簇索引，將讀取到的記錄返回給 server 層，然后讀取記錄中的 id 值，就會(huì) id 值判斷是否為 NULL，如果不為 NULL，就將 count 變量加 1。

但是，如果表里有二級(jí)索引時(shí)，InnoDB 循環(huán)遍歷的對(duì)象就不是聚簇索引，而是二級(jí)索引。

這是因?yàn)橄嗤瑪?shù)量的二級(jí)索引記錄可以比聚簇索引記錄占用更少的存儲(chǔ)空間，所以二級(jí)索引樹(shù)比聚簇索引樹(shù)小，這樣遍歷二級(jí)索引的 I/O 成本比遍歷聚簇索引的 I/O 成本小，因此「優(yōu)化器」優(yōu)先選擇的是二級(jí)索引。

count(1) 執(zhí)行過(guò)程是怎樣的？

用下面這條語(yǔ)句作為例子：

select count(1) from t_order;

如果表里只有主鍵索引，沒(méi)有二級(jí)索引時(shí)。

那么，InnoDB 循環(huán)遍歷聚簇索引（主鍵索引），將讀取到的記錄返回給 server 層，但是不會(huì)讀取記錄中的任何字段的值，因?yàn)?count 函數(shù)的參數(shù)是 1，不是字段，所以不需要讀取記錄中的字段值。參數(shù) 1 很明顯并不是 NULL，因此 server 層每從 InnoDB 讀取到一條記錄，就將 count 變量加 1。

可以看到，count(1) 相比 count(主鍵字段) 少一個(gè)步驟，就是不需要讀取記錄中的字段值，所以通常會(huì)說(shuō) count(1) 執(zhí)行效率會(huì)比 count(主鍵字段) 高一點(diǎn)。

但是，如果表里有二級(jí)索引時(shí)，InnoDB 循環(huán)遍歷的對(duì)象就二級(jí)索引了。

count(*) 執(zhí)行過(guò)程是怎樣的？

看到 * 這個(gè)字符的時(shí)候，是不是大家覺(jué)得是讀取記錄中的所有字段值？

對(duì)于 selete * 這條語(yǔ)句來(lái)說(shuō)是這個(gè)意思，但是在 count(*) 中并不是這個(gè)意思。

count(*) 其實(shí)等于 count(0)，也就是說(shuō)，當(dāng)你使用 count(*) 時(shí)，MySQL 會(huì)將 * 參數(shù)轉(zhuǎn)化為參數(shù) 0 來(lái)處理。

所以，count(*) 執(zhí)行過(guò)程跟 count(1) 執(zhí)行過(guò)程基本一樣的，性能沒(méi)有什么差異。

在 MySQL 5.7 的官方手冊(cè)中有這么一句話：

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

翻譯：InnoDB以相同的方式處理SELECT COUNT（*）和SELECT COUNT（1）操作，沒(méi)有性能差異。

而且 MySQL 會(huì)對(duì) count(*) 和 count(1) 有個(gè)優(yōu)化，如果有多個(gè)二級(jí)索引的時(shí)候，優(yōu)化器會(huì)使用key_len 最小的二級(jí)索引進(jìn)行掃描。

只有當(dāng)沒(méi)有二級(jí)索引的時(shí)候，才會(huì)采用主鍵索引來(lái)進(jìn)行統(tǒng)計(jì)。

count(字段) 執(zhí)行過(guò)程是怎樣的？

count(字段) 的執(zhí)行效率相比前面的 count(1)、 count(*)、 count(主鍵字段) 執(zhí)行效率是最差的。

用下面這條語(yǔ)句作為例子：

//name不是索引，普通字段
select count(name) from t_order;

對(duì)于這個(gè)查詢來(lái)說(shuō)，會(huì)采用全表掃描的方式來(lái)計(jì)數(shù)，所以它的執(zhí)行效率是比較差的。

小結(jié)

count(1)、 count(*)、 count(主鍵字段)在執(zhí)行的時(shí)候，如果表里存在二級(jí)索引，優(yōu)化器就會(huì)選擇二級(jí)索引進(jìn)行掃描。

所以，如果要執(zhí)行 count(1)、 count(*)、 count(主鍵字段) 時(shí)，盡量在數(shù)據(jù)表上建立二級(jí)索引，這樣優(yōu)化器會(huì)自動(dòng)采用 key_len 最小的二級(jí)索引進(jìn)行掃描，相比于掃描主鍵索引效率會(huì)高一些。

再來(lái)，就是不要使用 count(字段) 來(lái)統(tǒng)計(jì)記錄個(gè)數(shù)，因?yàn)樗男适亲畈畹?，?huì)采用全表掃描的方式來(lái)統(tǒng)計(jì)。如果你非要統(tǒng)計(jì)表中該字段不為 NULL 的記錄個(gè)數(shù)，建議給這個(gè)字段建立一個(gè)二級(jí)索引。

為什么要通過(guò)遍歷的方式來(lái)計(jì)數(shù)？

你可以會(huì)好奇，為什么 count 函數(shù)需要通過(guò)遍歷的方式來(lái)統(tǒng)計(jì)記錄個(gè)數(shù)？

我前面將的案例都是基于 Innodb 存儲(chǔ)引擎來(lái)說(shuō)明的，但是在 MyISAM 存儲(chǔ)引擎里，執(zhí)行 count 函數(shù)的方式是不一樣的，通常在沒(méi)有任何查詢條件下的 count(*)，MyISAM 的查詢速度要明顯快于 InnoDB。

使用 MyISAM 引擎時(shí)，執(zhí)行 count 函數(shù)只需要 O(1 )復(fù)雜度，這是因?yàn)槊繌?MyISAM 的數(shù)據(jù)表都有一個(gè) meta 信息有存儲(chǔ)了row_count值，由表級(jí)鎖保證一致性，所以直接讀取 row_count 值就是 count 函數(shù)的執(zhí)行結(jié)果。

而 InnoDB 存儲(chǔ)引擎是支持事務(wù)的，同一個(gè)時(shí)刻的多個(gè)查詢，由于多版本并發(fā)控制（MVCC）的原因，InnoDB 表“應(yīng)該返回多少行”也是不確定的，所以無(wú)法像 MyISAM一樣，只維護(hù)一個(gè) row_count 變量。

舉個(gè)例子，假設(shè)表 t_order 有 100 條記錄，現(xiàn)在有兩個(gè)會(huì)話并行以下語(yǔ)句：

在會(huì)話 A 和會(huì)話 B的最后一個(gè)時(shí)刻，同時(shí)查表 t_order 的記錄總個(gè)數(shù)，可以發(fā)現(xiàn)，顯示的結(jié)果是不一樣的。所以，在使用 InnoDB 存儲(chǔ)引擎時(shí)，就需要掃描表來(lái)統(tǒng)計(jì)具體的記錄。

而當(dāng)帶上 where 條件語(yǔ)句之后，MyISAM 跟 InnoDB 就沒(méi)有區(qū)別了，它們都需要掃描表來(lái)進(jìn)行記錄個(gè)數(shù)的統(tǒng)計(jì)。

如何優(yōu)化 count(*)？

如果對(duì)一張大表經(jīng)常用 count(*) 來(lái)做統(tǒng)計(jì)，其實(shí)是很不好的。

比如下面我這個(gè)案例，表 t_order 共有 1200+ 萬(wàn)條記錄，我也創(chuàng)建了二級(jí)索引，但是執(zhí)行一次 select count(*) from t_order 要花費(fèi)差不多 5 秒！

面對(duì)大表的記錄統(tǒng)計(jì)，我們有沒(méi)有什么其他更好的辦法呢？

*第一種，近似值*

如果你的業(yè)務(wù)對(duì)于統(tǒng)計(jì)個(gè)數(shù)不需要很精確，比如搜索引擎在搜索關(guān)鍵詞的時(shí)候，給出的搜索結(jié)果條數(shù)是一個(gè)大概值。

這時(shí)，我們就可以使用 show table status 或者 explain 命令來(lái)表進(jìn)行估算。

執(zhí)行 explain 命令效率是很高的，因?yàn)樗⒉粫?huì)真正的去查詢，下圖中的 rows 字段值就是 explain 命令對(duì)表 t_order 記錄的估算值。

第二種，額外表保存計(jì)數(shù)值

如果是想精確的獲取表的記錄總數(shù)，我們可以將這個(gè)計(jì)數(shù)值保存到單獨(dú)的一張計(jì)數(shù)表中。

當(dāng)我們?cè)跀?shù)據(jù)表插入一條記錄的同時(shí)，將計(jì)數(shù)表中的計(jì)數(shù)字段 + 1。也就是說(shuō)，在新增和刪除操作時(shí)，我們需要額外維護(hù)這個(gè)計(jì)數(shù)表。

總結(jié)

到此這篇關(guān)于MySQL中count()和count(1)有何區(qū)別以及哪個(gè)性能最好的文章就介紹到這了,更多相關(guān)MySQL中count()和count(1)區(qū)別對(duì)比內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論