MySQL中count(*)執行慢的解決方案

2022-06-02 14:06:34

在開發工作中，經常需要計算一個表的行數，比如一個內容系統稽核記錄總數。這時候我們最先想到是一條 select count(*) from my_table;語句。但是，隨著系統（表）中記錄數越來越多，這條查詢語句執行的也會越來越慢。難道MySQL不會單獨記個總數，我們每次要查的時候直接讀出來？

那麼就通過這一篇來聊聊count(*)語句到底是怎樣實現的，以及 MySQL為什麼會這麼實現。如果在開發應用中有這樣的需求，業務設計上可以怎麼去做。

一、 count(*) 的實現方式

1、實現方式比較

在不同的MySQL引擎中，count(*)有著不同的實現方式。

MyISAM 引擎把一個表的總行數存在了磁碟上，因此執行 count(*) 的時候會直接返回這個數，效率很高；
而 InnoDB 引擎執行 count(*) 的時候，需要把資料一行一行地從引擎裡面讀出來，然後累積計數。這樣就比較麻煩了。

【注意】在這篇學習文章裡，是沒有where過濾條件的count(*)，如果加了where條件，使用MyISAM引擎的表返回結果也不會變的很快。

2、為什麼InnoDB不像MyISAM一樣，也把數位存起來

因為InnoDB引擎即使是在同一個時刻的多個查詢，由於多版本並行控制（MVCC）的原因，InnoDB引擎表“應該返回多少行”也是不確定的。

在InnoDB引擎四個隔離級別（讀未提交RU；讀已提交RC；可重複讀RR；可序列化Serializable）中，而MVCC只適用於RC和RR。
MVCC：Multi-Version Concurrency Control，就是在事務執行過程中加入“版本”這個概念、MVCC的實現：通過儲存資料在某個時間點的快照來實現的。

假設表 test 中現在有 10000 條記錄，我們設計了三個使用者並行的對談。

對談 A 先啟動事務並查詢一次表的總行數；
對談 B 啟動事務，插入一行後記錄後，查詢表的總行數；
對談 C 先啟動一個單獨的語句，插入一行記錄後，查詢表的總行數。

如果從上到下是按照時間順序執行的，同一行語句是在同一時刻執行的。下圖表為對談 A、B、C 的執行流程。

對談A	對談B	對談C
BEGIN;
SELECT COUNT(*) FROM `test`;
		INSERT INTO `test`(寫入一行資料);
	BEGIN;
	INSERT INTO `test`( 寫入一行資料);
SELECT COUNT(*) FROM `test`; （返回1000）	SELECT COUNT(*) FROM `test`; （（返回1002）	SELECT COUNT(*) FROM `test`; （（返回1001）

從上圖表中可以看到，在最後一個時刻，三個對談會同時查詢表 test 的總行數，但結果卻不同。

這和 InnoDB 的事務設計有關，可重複讀是它預設的隔離級別，在程式碼上就是通過多版本並行控制(MVCC)來實現的。每一行記錄都要判斷自己是否對這個對談可見，因此對於count(*) 請求來說，InnoDB 只好把資料一行一行地讀出依次判斷，可見的行才能夠用於計算“基於這個查詢”的表的總行數。

InnoDB 是索引組織表，主鍵索引樹的葉子節點是資料，而普通索引樹的葉子節點是主鍵值。所以，普通索引樹比主鍵索引樹小很多。對於 count(*) 這樣的操作，遍歷哪個索引樹得到的結果邏輯上都是一樣的。因此，MySQL 優化器會找到最小的那棵樹來遍歷。在保證邏輯正確的前提下，儘量減少掃描的資料量，是資料庫系統設計的通用法則之一。

如果使用 show table status 命令，輸出結果裡面也有一個TABLE_ROWS 用於顯示這個表當前有多少行，這個命令執行挺快的，那 TABLE_ROWS 能代替 count(*)麼？

索引統計的值是通過取樣來估算的。實際上，TABLE_ROWS 就是從這個取樣估算得來的，因此它很不準。官方檔案說誤差可能達到 40% 到 50%。所以，show table status 命令顯示的行數也不能直接使用。

3、小結

MyISAM 引擎表雖然 count(*) 很快，但是不支援事務；
show table status 命令雖然返回很快，但是不準確；
InnoDB 引擎表直接 count(*)會遍歷全表，雖然結果準確，但會導致效能問題。

那麼，回到本文章開頭的問題，如果在一個頁面經常要顯示內容系統的操作記錄總數，這樣的話，我們只能自己計數。下面我們來看看有哪些計數方法，以及每種方法的優缺點有哪些。基本思路：自己找一個地方，把操作記錄表的行數存起來。

二、計數方法

1、用快取系統儲存計數

對於更新很頻繁的庫來說，我們第一時間想到的是用快取系統來儲存。比如用 Redis 服務來儲存這個表的總行數。這個表插入一行時， Redis 計數就加 1，刪除一行時， Redis 計數就減 1。這種方式下，讀和更新操作都很快，但快取系統可能會丟失更新，導致資料錯誤。

Redis 的資料不能永久地留在記憶體裡，所以你會找一個地方把這個值定期地持久化儲存起來。但即使這樣，仍然可能丟失更新。如果剛剛在資料表中插入了一行，Redis 中儲存的值也加了 1，然後 Redis 異常重啟了，重啟後你要從儲存 redis 資料的地方把這個值讀回來，而剛剛加 1 的這個計數操作卻丟失了。

這種情況也可以解決，如果Redis 異常重啟以後，到資料庫裡面單獨執行一次 count(*) 獲取真實的行數，再把這個值寫回到 Redis 裡就可以了。異常重啟畢竟不是經常出現的情況，這一次全表掃描的成本，還是可以接受的。但在應用中，將計數儲存在快取系統中，還不只是丟失更新的問題。這個值在邏輯上也是不精確的。，

注意是邏輯不準確。

你可以設想一下有這麼一個頁面，要顯示操作記錄的總數，同時還要顯示最近操作的 50 條記錄。那麼，這個頁面的邏輯就需要先到 Redis 裡面取出計數，再到資料表裡面取資料記錄。

我們是這麼定義不精確的：

一種是，查到的 50 行結果裡面有最新插入記錄，而 Redis 的計數裡還沒加 1；
另一種是，查到的 50 行結果裡沒有最新插入的記錄，而 Redis 的計數裡已經加了 1。

這兩種情況，都是邏輯不一致的。

我們一起來看看這個時序圖：

上圖中，對談 A 是一個插入記錄的邏輯，往資料表裡插入一行，然後 Redis 計數加 1；對談 B 就是查詢頁面顯示時需要的資料。在這個時序裡，在 T3 時刻對談 B 來查詢的時候，會顯示出新插入的這個記錄，但是 Redis 的計數還沒加 1。這時候，就會出現我們說的資料不一致。

即使把對談 A 的更新順序換一下，再看看執行結果。

即使反過來，對談 B 在 T3 時刻查詢的時候，Redis 計數加了 1 了，但還查不到新插入的 R 這一行，也是資料不一致的情況。

在並行系統裡面，我們是無法精確控制不同執行緒的執行時刻的，因為存在圖中的這種操作序列，所以，我們說即使 Redis 正常工作，這個計數值還是邏輯上不精確的。

2、在資料庫儲存計數

用快取系統儲存計數有丟失資料和計數不精確的問題。那麼，如果我們把這個計數直接放到資料庫裡單獨的一張計數表 A 中，會怎麼樣呢？

首先，解決了崩潰丟失的問題，InnoDB 是支援崩潰恢復不丟資料的。然後，再看看能不能解決計數不精確的問題。這也是我們要解決的問題，由於 InnoDB 要支援事務，從而導致 InnoDB 表不能把 count(*) 直接存起來，然後查詢的時候直接返回形成的。

現在我們就利用“事務”這個特性，把問題解決掉。

執行結果：雖然對談 B 的讀操作仍然是在 T3 執行的，但是因為這時候更新事務還沒有提交，所以計數值加 1 這個操作對對談 B 還不可見。

因此，在對談 B 看到的結果裡，查計數值和“最近 50 條記錄”看到的結果，邏輯上就是一致的。

三、不同的 count 用法

在 select count(?) from test 這樣的查詢語句裡面，count(*)、count(主鍵 id)、count(欄位) 和 count(1) 等不同用法的效能，這幾種用法有啥效能差別呢？

【注意】下面學習的也是基於 InnoDB 引擎的。

count() 是一個聚合函數，對於返回的結果集，一行行地判斷，如果 count 函數的引數不是 NULL，累計值就加 1，否則不加。最後返回累計值。

所以，count(*)、count(主鍵 id)和 count(1)都表示返回滿足條件的結果集的總行數；而 count(欄位），則表示返回滿足條件的資料行裡面，引數“欄位”不為 NULL 的總個數。

在分析效能差別時，我們需要記住這麼幾個原則：

server 層要什麼就給什麼；
InnoDB 只給必要的值；
現在的優化器只優化了 count(*) 的語意為“取行數”，其他“顯而易見”的優化並沒有做。

1、 count(主鍵 id)

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

2、count(1)

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

只看這上面這兩個用法，count(1) 執行得要比 count(主鍵 id) 快。因為從引擎返回 id 會涉及到解析資料行，以及拷貝欄位值的操作。

3、count(欄位)

如果這個“欄位”是定義為 not null 的話，一行行地從記錄裡面讀出這個欄位，判斷不能為 null，按行累加；

如果這個“欄位”定義允許為 null，那麼執行的時候，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。

4、count(*)

count(*)是例外，並不會把全部欄位取出來，而是專門做了優化，不取值。count(*)肯定不是 null，按行累加。按照效率排序的話，count(*) = count(1) > count(主鍵 id) > count(欄位)，所以建議儘量使用 count(*)。

到此這篇關於MySQL中count(*)執行慢的解決方案的文章就介紹到這了,更多相關MySQL執行count(*)內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！