MySQL中count(*)函式原理詳解

曉呆同學發表於2020-12-18
MySql函式

COUNT() 函式，是我們在平常的開發工作中，經常會用到的一個函式，它可以用來統計某個列值的數量，也可以用來統計行數。

拿 count(*) 為例，我們先介紹一下它在 MySQL 中的實現方式。

MyISAM 引擎 和 InnoDB 引擎中 count(*) 的實現方式

以 select count(*) from t 這條語句為例（注意這裡不帶任何的 where 條件）。

  • 在 MyISAM 引擎 中，每個表的總行數都會在記憶體和磁碟檔案中進行儲存，記憶體中的 count 變數值通過讀取檔案中的 count 值來進行初始化。當執行 count(*) 語句的時候，會直接將記憶體中儲存的數值返回，所以執行非常快。
  • 而在InnoDB 引擎中，當執行 count(*) 的時候，它需要一行一行的進行統計和計數，並將最終的統計結果返回。

也就是說，MyISAM 引擎中 count(*) 的時間複雜度是 O(1)，InnoDB 引擎中 count(*) 的時間複雜度是O(N)。

所以隨著表中資料越來越多，使用InnoDB 引擎的表，這條語句執行得也會越來越慢。

那為什麼 InnoDB 引擎就不能像 MyISAM 引擎一樣，也把總行數儲存到記憶體和磁碟檔案中呢？

這是因為 InnoDB 引擎實現了多版本併發控制（MVCC）的原因：對同一個表，不同事物在同一時刻，看到的資料可能是不一樣的。

我們拿個例子來說明下。

假設我們用的 InnoDB 引擎中事物隔離級別為預設的可重複讀（repeatable-read）。表 t 中現有10條資料，這時又來了三個併發請求。

會話A會話B會話C
begin;
select count(*) from t;
返回 10
insert into t…
begin;
insert into t…
select count(*) from t;
返回 10		select count(*) from t;
返回 12		select count(*) from t;
返回 11

可以看到，在最後一個時刻，會話 A、B、C 同時查詢表 t 的總行數，但拿到的結果卻不同。這也就是為什麼 InnoDB 引擎，無法像MyISAM 引擎那樣，將表的總行數進行儲存，因為它無法提供一個統一的值。

剛上邊介紹的時候，我們著重強調了一下，不能帶任何的 where 條件，如果加了 where 條件的話，MyISAM 引擎和其它引擎一樣，也是不能返回的這麼快的。

InnoDB 引擎對 count(*) 的優化

InnoDB 針對 count(*) 語句是做了些優化的，count(*)的目的只是為了統計總行數，它根本不關心自己查到的具體值，所以，InnoDB引擎在統計計數的時候，它會選擇一個成本較低的索引樹進行掃描，儘量避免掃描行資料，從而大大節省時間。

使用匯總表統計計數

既然 InnoDB 引擎中無法給我們記錄總條數，那我們可以自己定義一張彙總表，當插入一條新記錄時，我們就將彙總表中記錄的值加1。

假設表 t 中還是有10條記錄，此外有一張彙總表表 c，表 c 中記錄值為10。這時來了如下幾個併發請求。

會話A會話B會話C會話D
begin;
表c中記錄值加1
begin;
讀表c中記錄的值
(返回10)
讀表t中資料(有10條)
commit;		begin;
讀表c中記錄的值
(返回10)
表 t 中插入一行資料
commit;
讀表t中資料(有10條)
commit;		begin;
讀表c中記錄的值
(返回11)
讀表t中資料(有11條)
commit;

這樣，我們就可以快速的獲取總條數了。還有，細心的同學可能已經發現，上邊的幾個會話都開啟了事務。這裡也是為了保證我們查出來的表c中的數值，和表t中的條數始終一致。

如果都不用事務的話，會話B中查出來的表c中的值將會是11，而表t中卻只有10條資料。而如果只有會話A裡的更新和插入操作加事務的話，會話C中查出來的表c中的值將會是10，而表t中卻有11條資料。

count(*)、count(1)、count(列名)之間的區別

count()可以統計行數，也可以統計某個列值的數量。如果指定了列，則統計時要求列值是非空的（不為NULL），也就是隻會統計有值的結果數。

count(*)： 用於統計行數。這裡的萬用字元 "*" 並不會擴充套件成所有的列，實際上它會忽略所有的列，直接統計所有的行數。

count(1)： 用於統計行數。按照MySQL官方文件的介紹，count(1) 跟 count(*) 沒有區別。

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

count(列名)： MySQL會將列值取出來判斷是否為NULL，如果不為NULL，則進行累加，最終統計的是指定列有值的結果數。

如果我們需要統計行數，建議使用 count(*) ，count(*) 是SQL92定義的標準統計行數的語法，跟資料庫無關，跟NULL和非NULL無關。

相關文章