MySQL效能之count* count1 count列對比範例

正文

最近的工作中，我聽到組內兩名研發同學在交流資料統計效能的時候，聊到了以下內容：

資料統計你怎麼能用 count(*) 統計資料呢，count(*) 太慢了，要是把資料庫搞垮了那不就完了麼，趕緊改用 count(1)，這樣比較快......

有點兒好奇，難道 count(1) 的效能真的就比 count(*) 要好嗎？

印象中網上有很多的文章都有過類似問題的討論，那 MySQL 統計資料總數 count(*) 、count(1)和count(列名) 哪個效能更優呢？今天我們就來聊一聊這個問題。

count() 效能與啥相關？

在討論問題之前，我們需要先搞明白一件事：MySQL 中 count() 的效能到底與什麼相關呢？

一件東西，我們知道如何取，必定需要提前知道如何存放才行，那我們可以初步判定，count（） 效能應該與儲存引擎相關！

我們都知道，MySQL 常見的儲存引擎有兩種：MyISAM 和 InnoDB。

在這兩種儲存引擎下，MySQL 對於使用 count()  返回結果的流程是不一樣的：

• **MyISAM引擎：**每張表的總行數是儲存在磁碟上，所以當執行 count() 時，是直接從磁碟拿到這個值返回，能夠快速返回。

但要是在後面加了where查詢條件時，統計總數也沒有像想象中那麼快了。

• **InnoDB 引擎：**執行 count()，需要將資料一行一行地讀，再統計總數。

看到這裡，可能你會有這樣的疑問：

Q：為什麼 InnoDB 引擎不像 MyISAM 引擎一樣，把表總記錄儲存起來呢？

這個問題非常好，在回答這個問題之前，我們先來了解一下 MVCC 是個什麼東東。

MVCC 簡介

所謂MVCC，全稱：Multi-Version Concurrency Control，即多版本並行控制。

MVCC 是一種並行控制的方法，一般在資料庫管理系統中，實現對資料庫的並行存取，在程式語言中實現事務記憶體。

MVCC 在 MySQL InnoDB 中的實現主要是為了提高資料庫並行效能，用更好的方式去處理讀-寫衝突，做到即使有讀寫衝突時，也能做到不加鎖，非阻塞並行讀。

就是因為要實現多版本並行控制，所以才導致 InnoDB 引擎不能直接儲存表總記錄數。因為每個事務獲取到的一致性檢視都是不一樣的，所以返回的資料總記錄也是不一致的。

到這裡，相信你已經知道 InnoDB 引擎為什麼不像 MyISAM 引擎一樣把表總記錄儲存起來了，簡單理解原因就是：InnoDB 支援事務，MyISAM 不支援事務。

MySQL 對 count() 的優化

我們知道了count() 效能與儲存引擎相關，那 MySQL 在執行 count() 操作的時候有沒有對其效能做些優化呢？

答案是肯定有的！

InnoDB 是索引組織表，主鍵索引樹的葉子節點是資料，而普通索引樹的葉子節點是主鍵值。因此，普通索引樹比主鍵索引樹小很多。對於count(*)這樣的操作，遍歷哪個索引樹得到的結果邏輯上都是一樣的。因此，MySQL優化器會找到最小的那棵樹來遍歷。

如果你使用過 show table status 命令的話，就會發現這個命令的輸出結果裡面也有一個 rows 值用於顯示這個表當前有多少行。

相信有人肯定會問，是不是這個 rows 值就能代替 count() 了嗎？

其實不能，rows 這個是從從取樣估算得來的，因此它也是不是準確。

官方檔案說是在40%到50%，所以此行數 rows 是不能直接使用的，如下所示：

查詢效能 PK 大起底

基於 MySQL 的 Innodb 儲存引擎，統計表的總記錄數下面這幾種做法，到底哪種效率最高？

count(主鍵id)

InnoDB引擎會遍歷整張表，把每一行的 id 值都取出來，返回給 server 層。server 層拿到 id 後，判斷是不可能為空的，就按行累加。

count(1)

會統計表中的所有的記錄數，包含欄位為 null 的記錄。

同樣遍歷整張表，但不取值，server 層對返回的每一行，放一個數位1進去，判斷是不可能為空的，按行累加。

count(欄位)

分為兩種情況，欄位定義為 not null 和 null：

1）為 not null 時：逐行從記錄裡面讀出這個欄位，判斷不為 null，累加；

2）為 null 時：執行時，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。

count(*)

需要注意的是，並不是帶了 * 就把所有值取出來，而是 MySQL 做了專門的優化，count(*) 肯定不是null，按行累加。

count(1) 和 count(*) 對比

當表的資料量大些時，對錶作分析之後，使用 count(1)還要比使用 count(*)用時多了！

從執行計劃來看， count(1) 和 count(*)的效果是一樣的。但是在表做過分析之後， count(1) 會比 count(*)的用時少些（1w以內資料量），不過差不了多少。

如果 count(1)是聚索引，那肯定是 count(1)快，但是差的很小。因為 count(*)自動會優化指定到那一個欄位，所以沒必要去 count(1)，用 count(*) sql會幫你完成優化的，因此：count(1) 和 count(*)基本沒有差別！

總結

基於 MySQL 的 InnoDB 儲存引擎，統計表的總記錄數按照效率排序：

count(欄位) < count(主鍵id) < count(1)≈count(*)

效率最高是 count(*)，並不是count(1)，所以建議儘量使用 count(*)。

執行效果上：

count(*)包括了所有的列，相當於行數，在統計結果的時候，不會忽略列值為null

count(1)包括了忽略所有列，用1代表程式碼行，在統計結果的時候，不會忽略列值為null

count(列名)只包括列名那一列，在統計結果的時候，會忽略列值為空（這裡的空不是隻空字串或者0，而是表示null 的計數，即某個欄位值為null 時，不統計。

執行效率上：

• 列名為主鍵， count(列名) 會比 count(1)快
• 列名不為主鍵， count(1) 會比 count(列名)快
• 如果表多個列並且沒有主鍵，則 count(1) 的執行效率優於 count(*)
• 如果有主鍵，則 select count(主鍵) 的執行效率是最優的
• 如果表只有一個欄位，則 select count（*）最優。

希望今天的講解對大家有所幫助，謝謝！

更多關於MySQL count效能對比的資料請關注it145.com其它相關文章！