MySQL：SELECT COUNT 小結

翁智華發表於2020-10-17
MySql

MySQL：SELECT COUNT 小結

背景

今天團隊在做線下程式碼評審的時候，發現同學們在程式碼中出現了select count(1) 、 select count(*)，和具體的select count(欄位)的不同寫法，本著分析的目的在會議室討論了起來，那這幾種寫法究竟孰優孰劣呢，我們一起來看一下。

討論歸納

先來看看MySQL官方對SELECT COUNT的定義：

傳送門：https://dev.mysql.com/doc/refman/5.6/en/aggregate-functions.html#function_count

 大概可以分下面這幾個步驟討論。

COUNT(expr)的分析

COUNT(expr)函式返回的值是由SELECT語句檢索的行中expr表示式非null的計數值，一個BIGINT的值。 如果沒有匹配到資料，COUNT(expr)將返回0，通常有下面這三種用法：

1、COUNT(欄位) 會統計該欄位在表中出現的次數，忽略欄位為null 的情況。即不統計欄位為null 的記錄。 

2、COUNT(*) 則不同，它執行時返回檢索到的行數的計數，不管這些行是否包含null值，

3、COUNT(1)跟COUNT(*)類似，不將任何列是否null列入統計標準，僅用1代表程式碼行，所以在統計結果的時候，不會忽略列值為NULL的行。

所以執行以下資料會出現這樣的結果（這邊是故意給component欄位設定了幾個null值）：

1 select COUNT(*),COUNT(1),COUNT(component) from worklog;

 

歸納如下： 

count(*) 包括了所有的列，相當於行數，在統計結果的時候，不會忽略列值為NULL  
count(1) 包括了忽略所有列，用1代表程式碼行，在統計結果的時候，不會忽略列值為NULL  
count(欄位) 只包括欄位那一列，在統計結果的時候，會忽略列值為null的計數，即某個欄位值為NULL時，不統計。

 

關於 COUNT(*) 和 COUNT(1)

先看看COUNT(*)，MyISAM 引擎會把一個表的總行數記錄了下來，所以在執行 COUNT(*) 的時候會直接返回數量，執行效率很高。對於InnoDB這樣的事務性儲存引擎， 因為增加了版本控制(MVCC)的原因，同時有多個事務訪問資料並且有更新操作的時候，每個事務需要維護自己的可見性，那麼每個事務查詢到的行數也是不同的，所以不能快取具體的行數，他每次都需要 count 計算一下所有的行數。

至於 COUNT(1) 和 COUNT(*)有什麼區別呢，根據官網的內容（即上述截圖倒數第二段），兩種實現上其實一樣：

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

因為COUNT(*) 不care返回值是否為空都會將改行納入計算，所以他count了所有行數，而 COUNT(1) 中的 1 ，則是遇到了行的時候為恆真表示式，所以 COUNT(*) 還是 COUNT(1) 都是對所有的結果集進行 count，他們本質上沒有什麼區別。姑且認為 COUNT(*) ≈ COUNT(1)。 

 

關於COUNT(欄位)

我們再來看看的COUNT(欄位)，他的查詢就簡單粗暴了，就是進行全表掃描，然後判斷拿到的欄位的值是不是為NULL，不為NULL則累加。

相比COUNT(*)，COUNT(欄位)多了一個步驟就是判斷所查詢的欄位是否為NULL，所以他的效能要比COUNT(*)和COUNT(1)慢。 

 

總結 

綜上，COUNT(1)和 COUNT(*)表示的是直接查詢符合條件的資料庫表的行數。而COUNT(欄位)表示的是查詢符合條件的列的值，並判斷不為NULL的行數的累計，效率自然會低一點,

除了查詢得到結果集有區別之外，相比COUNT(1) 和 COUNT(欄位)來講，COUNT(*)是SQL92定義的標準統計數的語法，是官方提供的標準方案，基於此，MySQL資料庫對他進行過很多優化。

注：SQL92，是資料庫的一個ANSI/ISO標準。它定義了一種語言（SQL）以及資料庫的行為（事務、隔離級別等）。

下面是對一張具有3400W資料的表的統計過程，comid是整型，可以對比下執行效率差異： 

 

  

使用建議

根據總結的內容，從效率層面說，COUNT(*) ≈ COUNT(1) > COUNT(欄位)，又因為 COUNT(*)是SQL92定義的標準統計數的語法，我們建議使用 COUNT(*)。

我們再來看看MySQL資料庫做了哪些優化：以MySQL中比較常用的執行引擎InnoDB和MyISAM為例子。

1、MyISAM不支援事務，MyISAM中的鎖是表級鎖；

因為MyISAM的鎖是表級鎖，所以同一張表上面的操作是序列執行的，MyISAM把表的總行數單獨記錄下來，如果只是使用COUNT(*)對錶進行查詢的時候，可以直接返回這個記錄的數值就可以了。

這樣表中總行數記錄即可提供給COUNT(*)查詢使用，又因MyISAM資料庫是表級鎖，資料庫行數不會被並行修改，所以行數是準確無誤的。

2、InnoDB支援事務，其中大部分操作都是行級鎖。

這樣就不能愉快的做這種快取操作了，因為表的行數可能會被併發修改，快取記錄下來的總行數就不準確了。

在InnoDB中，使用COUNT(*)查詢行數的時候，不需要進行掃表，只要獲取記錄行數而已。所以官方在針對InnoDB的 SELECT COUNT(*) FROM 語句執行過程，會自動選擇一個成本較低的索引進行的話，這樣就可以大大節省時間。

InnoDB中索引分為聚簇索引（主鍵索引）和非聚簇索引（非主鍵索引），聚簇索引的葉子節點中儲存的是整行記錄，而非聚簇索引的葉子節點中儲存的是該行記錄的主鍵的值，非聚簇索引要比聚簇索引小很多，MySQL會優先選擇最小的非聚簇索引來掃表，這樣可以保證COUNT(*)的最優效率。

當查詢語句中包含WHERE以及GROUP BY條件，會有一些其他的因素影響，所以要綜合考慮。

 

判斷資料在否,COUNT怎麼用？

上面那種很獲取COUNT數的場景多用於資料分頁，資料統計的場景，有很多的情況則是直接判斷資料是否存在，這種情況下，其實是不關心有多少資料。但是我們CoreReview的時候還是會很經常看到這種做法：

1 select COUNT(*) from test_ucsyncdetail where comid>520;
2 
3 int count = testDao.CountByComId(comId);
4 if(count>0){
5    //存在，則執行存在分支的程式碼
6 }
7 else{
8   //不存在，則執行存在分支的程式碼
9 }

更好的寫法應該是這樣：

1 select 1 from test_ucsyncdetail where comid>520 limit 1;
2  
3 Object tda= testDao.checkExit(comId);
4 if(tda != null){
5     //存在，則執行存在分支的程式碼
6 }
7  else{
8    //不存在，則執行存在分支的程式碼
9 }


規避了SQL使用COUNT表示式掃表的操作，而是改用SELECT 1 ... LIMIT 1，資料庫查詢時遇到一條就返回，不會再繼續查詢和執行，如果存在傳輸回一條結果為1的資料 ，否則為null，業務程式碼中直接判斷是否非空即可

 

後記

細節把握的好不好，真的影響很大，接下來準備重新擼一下 《高效能MySQL》和《MySql筆記》。

相關文章