面試官：請說一下Mysql中count(1)、count(*)以及count(列)的區別？

本文深度解析MySQL的COUNT(1), COUNT(*),COUNT(列)計數方式，強調COUNT(*)的廣泛應用與InnoDB存儲引擎的優化。通過性能比較，揭示COUNT(id)在索引下的性能，通過技術細節揭示MySQL查詢優化器的工作原理，最終總結適用場景，為讀者提供計數方式選擇的指導。 ...

近期在Review項目代碼時，發現同事們在查詢MySQL行數時存在多樣的方式，有的使用COUNT(1), 有的用COUNT(id), 還有人選擇了COUNT(*)。這混雜的選擇引發了我的思考。當然這三種count的方式也是眾說紛紜，其中最大的分歧點就是COUNT(*)和COUNT(1)查詢性能上，有人覺得COUNT(*)需要轉換為COUNT(1)，所以COUNT(1)得速度更快。究竟這三種計數方式之間有何區別，它們的背後原理是怎樣的呢？

COUNT()含義

在《高性能Mysql》一書第236頁中是這麼解釋COUNT的作用的：

COUNT()是一個特殊的函數，有兩種非常不同的作用:它可以統計某個列值的數量，也可以統計行數。在統計列值時要求列值是非空的(不統計NULL)。如果在COUNT()的括弧中指定了列或者列的表達式，則統計的就是這個表達式有值的結果數”。因為很多人對 NULL理解有問題，所以這裡很容易產生誤解。
COUNT()的另一個作用是統計結果集的行數。當MySOL確認括弧內的表達式值不可能為空時，實際上就是在統計行數。最簡單的就是當我們使用COUNT()的時候，這種情況下通配符並不會像我們猜想的那樣擴展成所有的列，實際上，它會忽略所有的列而直接統計所有的行數。
我們發現一個最常見的錯誤就是，在括弧內指定了一個列卻希望統計結果集的行數。如果希望知道的是結果集的行數，最好使用COUNT(*)，這樣寫意義清晰，性能也會很好

由此我們也可以大概總結COUNT函數的種方式如下：

COUNT(1): 此查詢返回的是結果集中的行數，不關心具體的列內容，因此使用常數1。
在很多資料庫系統中，這種方式被優化為與 SELECT COUNT(*) 相同的性能水平，因為資料庫引擎通常忽略括弧內的內容。
COUNT(*)：統計整個表的行數，不考慮是否有NULL值。
通常優於 COUNT(id)，因為它不需要關心具體的列，且現代資料庫引擎會對其進行特殊優化。
COUNT(列) ：統計指定列非空值的數量。需要考慮是否有NULL值
此種方式取決於列是否有索引。如果列有索引，資料庫引擎可能會利用索引進行快速計數。如果沒有索引，或者有大量NULL值，性能可能較差，因為需要掃描整個表。

區別

1、Mysql5.7

在MySql 5.7官方文檔中是這麼介紹COUNT(expr)函數的

COUNT(expr)
Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.
If there are no matching rows, COUNT() returns 0.
mysql> SELECT student.student_name,COUNT(*)
FROM student,course
WHERE student.student_id=course.student_id
GROUP BY student_name;
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

For transactional storage engines such as InnoDB, storing an exact row count is problematic. Multiple transactions may be occurring at the same time, each of which may affect the count.

InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.

Prior to MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index. As of MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, the clustered index is scanned.

Processing SELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient, use SHOW TABLE STATUS.

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

For MyISAM tables, COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:
mysql> SELECT COUNT(*) FROM student;
This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.

從官方文檔中我們可以看出mysql官方對COUNT函數的解釋：

COUNT(expr) 返回由 SELECT 語句檢索的行中 expr 的非 NULL 值的數量，結果為 BIGINT 值。如果沒有匹配的行，COUNT() 返回 0。
COUNT(*) 有所不同，它返回所檢索的行數的計數，無論它們是否包含 NULL 值。
對於事務性存儲引擎（如 InnoDB），存儲準確的行數是有問題的。因為多個事務可能同時影響計數，InnoDB 不會保留表中行的內部計數。SELECT COUNT(*) 只會計算當前事務可見的行。
在 MySQL 5.7.18 之前，InnoDB 通過掃描聚集索引處理 SELECT COUNT(*) 語句。從 MySQL 5.7.18 開始，除非索引或優化器提示指示使用其他索引，InnoDB 會通過遍歷最小的可用二級索引來處理 SELECT COUNT(*) 語句。如果沒有二級索引，則將掃描聚集索引。
處理 SELECT COUNT(*) 語句可能花費一些時間，如果索引記錄沒有完全在緩衝池中。為了更快的計數，可以創建一個計數器表，並根據插入和刪除操作進行更新。然而，在成千上萬的併發事務更新同一計數器表的情況下，該方法可能無法很好地擴展。如果粗略的行數足夠，可以使用SHOW TABLE STATUS。
InnoDB 處理 SELECT COUNT(*) 和 SELECT COUNT(1) 操作的方式相同，沒有性能差異。
對於 MyISAM 表，COUNT(\*) 在從一個表中檢索、沒有檢索其他列、沒有 WHERE 子句的情況下可以快速返回，因為 MyISAM 存儲了準確的行數。COUNT(1) 只有在第一列被定義為 NOT NULL 時才能進行相同的優化。

2、Mysql 8.0

在Mysql8.0的文檔中對COUNT(expr)的解釋是這樣

COUNT(expr) [over_clause]

Returns a count of the number of non-NULL values of expr in the rows retrieved by a SELECT statement. The result is a BIGINT value.

If there are no matching rows, COUNT() returns 0.COUNT(NULL)returns 0.

This function executes as a window function if over_clause is present. over_clause is as described in Section 12.20.2, “Window Function Concepts and Syntax”.

mysql> SELECT student.student_name,COUNT(*) FROM student,course WHERE student.student_id=course.student_id GROUP BY student_name;
COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether or not they contain NULL values.

For transactional storage engines such as InnoDB, storing an exact row count is problematic. Multiple transactions may be occurring at the same time, each of which may affect the count.

InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.

As of MySQL 8.0.13, SELECT COUNT(*) FROM tbl_name query performance for InnoDB tables is optimized for single-threaded workloads if there are no extra clauses such as WHERE or GROUP BY.

InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index.

Processing SELECT COUNT(*) statements takes some time if index records are not entirely in the buffer pool. For a faster count, create a counter table and let your application update it according to the inserts and deletes it does. However, this method may not scale well in situations where thousands of concurrent transactions are initiating updates to the same counter table. If an approximate row count is sufficient, use SHOW TABLE STATUS.

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

For MyISAM tables, COUNT(*)is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student;
This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.

從mysql8.0的文檔中我們可以看出mysql8.0對COUNT的解釋

COUNT(expr) 返回在由 SELECT 語句檢索的行中 _expr_ 的非 NULL 值的數量，結果為 BIGINT 值。如果沒有匹配的行，COUNT() 返回 0。COUNT(NULL) 也返回 0。
COUNT(*) 有所不同，它返回所檢索的行數的計數，無論它們是否包含 NULL 值。
對於事務性存儲引擎（如 InnoDB），存儲準確的行數是有問題的，因為多個事務可能同時影響計數。InnoDB 不會保留表中行的內部計數。SELECT COUNT(*) 只計算當前事務可見的行。
在 MySQL 8.0.13 及以後版本，對於 InnoDB 表，執行 SELECT COUNT(*) FROM tbl_name 查詢性能在沒有額外子句（如 WHERE 或 GROUP BY）的情況下進行了優化，特別適用於單線程工作負載。
InnoDB 處理 SELECT COUNT(*) 語句的方式：
- 通過遍歷最小可用二級索引，除非指示使用其他索引。
- 如果沒有二級索引，InnoDB 通過掃描聚集索引來處理 SELECT COUNT(*) 語句。
處理 SELECT COUNT(*) 語句可能花費一些時間，如果索引記錄沒有完全在緩衝池中。為了更快的計數，可以創建一個計數器表，讓應用程式根據插入和刪除操作進行更新。但是，這種方法在數千個併發事務同時對同一計數器表進行更新的情況下可能不會很好地擴展。如果粗略的行數足夠，可以使用 SHOW TABLE STATUS。
InnoDB 對待 SELECT COUNT(*) 和 SELECT COUNT(1) 操作的方式相同，沒有性能差異。
對於 MyISAM 表，COUNT(*) 在從一個表中檢索、沒有檢索其他列、沒有 WHERE 子句的情況下可以快速返回，因為 MyISAM 存儲了準確的行數。COUNT(1) 只有在第一列被定義為 NOT NULL 時才能進行相同的優化。

結合Mysql5.7與Mysql8.0的文檔我們可以看出兩個版本對COUNT的支持的差異：

MySQL 8.0 優化了 InnoDB 表的 SELECT COUNT(*) 查詢性能： 在 MySQL 8.0.13 及以後版本，對於 InnoDB 表，執行 SELECT COUNT(*) 查詢的性能進行了優化，特別適用於單線程工作負載。這是 MySQL 5.7 文檔中未包含的新特性。
MyISAM 表的優化說明：
MySQL 8.0 文檔中強調了 MyISAM 表在執行 COUNT(*) 時的優化情況，即在從一個表中檢索、沒有檢索其他列、沒有 WHERE 子句的情況下可以快速返回。MySQL 5.7 文檔中也提到了 MyISAM 表的優化，但對於 COUNT(*) 的具體優化情況沒有細節。

MySQL 8.0 在性能優化方面對於 InnoDB 表的 SELECT COUNT(*) 查詢進行了特別的關註，而且在 MyISAM 表的優化方面進行了詳細的說明。其他方面，兩個版本在COUNT()函數的解釋和使用上基本保持一致。

基於此我們明白，其實COUNT(*)和COUNT(1)其實是一樣的，在性能上並沒有差異。

那這兩種方式與COUNT(列)的差異呢？從以上《高性能Mysql》以及Mysql官方文檔中我們知道，COUNT(列)是統計非空列的行數，它也會遍歷整張表，然後會對列對應的值做非空判斷，非空的欄位進行個數累加。當然這是列為主鍵索引時的操作。如果列不為主鍵索引時，那麼查詢時還需要進行回表操作，再根據主鍵獲取數據，此時無疑是增加了一次IO，在性能上其實是不如COUNT(*)和COUNT(1)的。那麼我們就可以知道，按照效率來看，count(*) = count(1) > count(主鍵) > count(非主鍵列)。

使用建議

如果你想知道一張表的大概行數，我們可以直接使用show table status命令或者咱們使用的一些mysql客戶端Navicat或者datagrip都可以辦到。
如果你想獲取一張表的確切行數時，我可以是優先使用使用 COUNT(*) 獲取行數，這樣寫法清晰，性能較好，尤其對於 InnoDB 表的優化更為明顯。我們要避免使用 COUNT(列) 統計行數，除非你真的需要統計該列非空值的數量，否則容易產生誤解。

篇幅有限，深入驗證將在後續文章中介紹。

本文已收錄於我的個人博客：碼農Academy的博客，專註分享Java技術乾貨，包括Java基礎、Spring Boot、Spring Cloud、Mysql、Redis、Elasticsearch、中間件、架構設計、面試題、程式員攻略等