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

曉呆同學發表於2020-12-18

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無關。

相關文章