MySQL：如何才能實現高效數據統計

作者：果凍布丁兔 2023-12-08 07:55:37

對于count(*)來說，InnoDB只好把數據一行行讀出來，對可見的行進行統計。因此，InnoDB不能像MyISAM引擎一樣在磁盤保存數據行樹。

我們在業務中經常遇到的一個場景就是統計當前已有的業務數據，比如說商品庫內商品的數量、每天的用戶訂單數量等等。

這時候，我們一般就需要MySQL的統計功能實現。

1 count(*)實現方式

不同的引擎，count(*)實現邏輯也不一致：

MyISAM引擎將一個表的總數存在磁盤上，當執行count(*)沒有where條件時，直接從磁盤讀取數據返回即可，效率比較高；如果是有where條件，則和InnoDB實現邏輯類似；
InnoDB執行count(*)需要將一行行數據從引擎中讀取出來后累積計數；

InnoDB利用多版本控制機制支持事務，一行記錄會記錄多個MVCC，統計行數這一行為和隔離級別直接相關。在RR級別下，每一行記錄都要判斷自己是否對這個會話可見，每個會話也會執行增刪改操作，導致每個事務統計的行數不一致，因此，對于count(*)來說，InnoDB只好把數據一行行讀出來，對可見的行進行統計。因此，InnoDB不能像MyISAM引擎一樣在磁盤保存數據行樹。

表中，會話C沒有顯示開始事務，因此每條語句都是獨立事務，由于AB會話都沒有提交事務，因此，AB的修改對C不可見。

事實上，InnoDB對count(*)做了一定優化，由于InnoDB是索引組織表，主鍵索引樹的葉子節點是數據，普通索引樹的葉子階段是主鍵值，因此，普通索引樹比主鍵索引樹小很多。執行count(*)的邏輯就是遍歷，因此，MySQL優化器會選擇最小的索引樹用于遍歷，相對于每次都讀取所有的數據行，只是遍歷主鍵，自然IO開銷要小的多。

因此說：在保證邏輯正確的前提下，盡量減少掃描的數據量，是數據庫系統設計的通用法則之一。

另外，還有一個顯示行數的命令為：

show table status;

也會顯示表的行數，但是這里的rows是預估值，這個預估值是根據隨機采集計算出來的，MySQL隨機取N頁數據，計算出每頁中不同記錄數，求取平均值后乘以總頁數得到的就是預估值。這個預估值是否接近真實值，取決于索引字段區分度、索引數據頁緊湊程度、是否存在頁分裂、索引空洞等元素。這個預估值也是造成MySQL選錯索引的原因。

2 如何實現計數邏輯

2.1 用緩存系統統計計數

如果是一般場景，使用緩存系統執行計數是滿足需求的，即使說，由于redis服務集群異常重啟導致數據丟失，但是可以再次掃描一次表獲取表的總數。

但是如果是非常嚴謹的場景（銀行統計實際支付的訂單數據等），那可能有如下的問題。

第一個是緩存可能會丟失數據，即使是開啟持久化，還是存在丟失數據可能性。redis持久化有RDB和AOF兩種方式；RDB按照備份策略，比如60秒1000個k-v被修改，備份過程中宕機，那么這個階段的所有更新都會丟失；AOF按照備份策略，比如 appendfsync always 策略，同步記錄所執行的指令到日志文件，但是它的日志和mysql的WAL不同，它是寫后日志，可能指令執行后寫日之前宕機，那這個數據就丟失了，雖然丟失數據較少且概率較低，但依然存在這個可能。

第二個是數據一致性保證問題，Redis和MySQL是兩個數據源，可以看成是一種分布式一致性的問題，而分布式一致性由于不能保證原子性，因此一般只能保證最終一致性，而不能保證實時一致性。

數據一致性問題目前可分為三類：

1.主從不一。解決辦法:半同步復制可以保證實時的一致性，因為寫時寫主和從之后才響應，只不顧這樣寫的并發上不去；其他訪問有強制讀主、消息中間件路由讀主和緩存是否失效讀主；

2.數據庫與緩存的不一。解決辦法:讀操作直接讀緩存,寫操作先更新到數據庫,淘汰緩存(程序需要保證兩個操作的原子性，如果淘汰失敗，則發一條小實現異步淘汰).由于該key的緩存已經清理掉,那么下次讀的時候需要先讀數據庫,在重建緩存. 由于redis是單線程,保證了一個操作的原子性.可以通過設置appendfsync always來保證每次操作都把該操作記錄并落盤到aof文件里(不過一般redis該值為everysec),畢竟使用redis的目的不是為了保證acid.還是要根據業務來選擇。

3.一個事務跨多個節點或者多種數據庫(分庫分表和銀行轉賬這種例子) 。目前好像都是通過2pc,3pc來保證的。

2.2 用數據庫保存計數

在數據庫中設計單獨的計數表，將插入數據、刪除數據的SQL和更新計數表的SQL語句作為同一個事務執行。

圖片

在統計時，將讀取計數器和查詢最近數據也作為一個事務執行，這樣拿到的就是理論上的實際值。

但是實際上，在高并發場景以及一般場景，這種統計的意義可能并不是很大，因為當你剛剛統計的數據，可能在返回的期間已經有變化。

這時候再次有個問題：在并發系統性能的角度考慮，在事務序列里，是先插入操作記錄，還是應該先更新計數表？

答案是：先插入新紀錄。

因為插入新紀錄只會影響到行鎖和間隙鎖，但是更新計數表會占用計數表的寫鎖，而很多其他事務的插入操作就必須阻塞等待，即：并發度高的操作放在后面執行，可以減少鎖等待；
計數表是公用表，根據鎖的二階段協議，在需要的時候獲取，在事務提交的時候釋放，晚獲取可以減少并發，提高吞吐量；

3 不同的count方法

count()方法是一個聚合函數，對于返回的結果集，一行行判斷，如果count函數參數不是null，累計值+1，否則不加。最后返回累計值。

InnoDB存儲引擎查詢數據結果集；
Server層根據結果集進行遍歷統計；

所以，count(*)、count(1)、count(主鍵)都表示返回滿足條件的結果集的總行數；count(列）表示返回滿足條件的數據行里面，參數“字段”不為Null的總個數。

MySQL執行統計的原則是：

server層要什么就給什么；
InnoDB只給必要的值；
現在的優化器只優化了count(*)的語義為取行數，其他的語句沒有做優化。

count(主鍵)：InnoDB引擎會遍歷整張表，把每一行的id取出來（存在數據行數據解析），把主鍵返回給server層（存在字段值拷貝）。判定id是否為空，不為空直接按行累加即可（這里應該是可以優化的，因為主鍵一定不允許為空）；同時，count(id)可能會走最小的索引來遍歷，并不一定非要走主鍵索引；

count(1)：InnoDB引擎遍歷整張表，但是無需取值。server層對返回的每一行放一個數字1，按行累加；

count(column)：

如果字段不允許為空，一行行從記錄里面讀取這個字段，按行累加；
如果字段允許為空，一行行從記錄讀取字段后，先判定是否為null，如果為null，則過濾掉，不為Null，則累加；
如果字段為索引，那么這里的統計就會走該索引；

count(*)：count(*)不取值，按行累加即可；

MySQL文檔中有如下說明：

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

因此，按照效率排序為：count(字段)<count(主鍵 id)<count(1)=count(*)，所以我建議你，盡量使用 count(*)。

從獲取的數值來看，count(字段)也一定是最小的，因為列字段的值可能為null。

4 本章回顧問題

把該講內容總結為幾個問題, 大家復習的時候可以先嘗試回答這些問題檢查自己的掌握程度:

count(*)的實現方式在MySAM引擎和InnoDB引擎的實現方式各是怎么樣的? 為什么會有這種不同?
使用緩存保存count總數存在什么問題?
如果使用一場單獨的表來記錄其他各張表的記錄數的話,是怎么解決統計結果不精確的問題的?
count(字段),count(id),count(1), count(*)各自是怎么樣的執行機制, 效率排序是怎么樣的?

責任編輯：武曉燕來源：陸隊長