我們?cè)跇I(yè)務(wù)中經(jīng)常遇到的一個(gè)場(chǎng)景就是統(tǒng)計(jì)當(dāng)前已有的業(yè)務(wù)數(shù)據(jù)，比如說(shuō)商品庫(kù)內(nèi)商品的數(shù)量、每天的用戶(hù)訂單數(shù)量等等。

這時(shí)候，我們一般就需要MySQL的統(tǒng)計(jì)功能實(shí)現(xiàn)。

1 count(*)實(shí)現(xiàn)方式

不同的引擎，count(*)實(shí)現(xiàn)邏輯也不一致：

• MyISAM引擎將一個(gè)表的總數(shù)存在磁盤(pán)上，當(dāng)執(zhí)行count(*)沒(méi)有where條件時(shí)，直接從磁盤(pán)讀取數(shù)據(jù)返回即可，效率比較高；如果是有where條件，則和InnoDB實(shí)現(xiàn)邏輯類(lèi)似；
• InnoDB執(zhí)行count(*)需要將一行行數(shù)據(jù)從引擎中讀取出來(lái)后累積計(jì)數(shù)；

InnoDB利用多版本控制機(jī)制支持事務(wù)，一行記錄會(huì)記錄多個(gè)MVCC，統(tǒng)計(jì)行數(shù)這一行為和隔離級(jí)別直接相關(guān)。在RR級(jí)別下，每一行記錄都要判斷自己是否對(duì)這個(gè)會(huì)話(huà)可見(jiàn)，每個(gè)會(huì)話(huà)也會(huì)執(zhí)行增刪改操作，導(dǎo)致每個(gè)事務(wù)統(tǒng)計(jì)的行數(shù)不一致，因此，對(duì)于count(*)來(lái)說(shuō)，InnoDB只好把數(shù)據(jù)一行行讀出來(lái)，對(duì)可見(jiàn)的行進(jìn)行統(tǒng)計(jì)。因此，InnoDB不能像MyISAM引擎一樣在磁盤(pán)保存數(shù)據(jù)行樹(shù)。

表中，會(huì)話(huà)C沒(méi)有顯示開(kāi)始事務(wù)，因此每條語(yǔ)句都是獨(dú)立事務(wù)，由于AB會(huì)話(huà)都沒(méi)有提交事務(wù)，因此，AB的修改對(duì)C不可見(jiàn)。

事實(shí)上，InnoDB對(duì)count(*)做了一定優(yōu)化，由于InnoDB是索引組織表，主鍵索引樹(shù)的葉子節(jié)點(diǎn)是數(shù)據(jù)，普通索引樹(shù)的葉子階段是主鍵值，因此，普通索引樹(shù)比主鍵索引樹(shù)小很多。執(zhí)行count(*)的邏輯就是遍歷，因此，MySQL優(yōu)化器會(huì)選擇最小的索引樹(shù)用于遍歷，相對(duì)于每次都讀取所有的數(shù)據(jù)行，只是遍歷主鍵，自然IO開(kāi)銷(xiāo)要小的多。

因此說(shuō)：在保證邏輯正確的前提下，盡量減少掃描的數(shù)據(jù)量，是數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)的通用法則之一。

另外，還有一個(gè)顯示行數(shù)的命令為：

show table status;

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

2 如何實(shí)現(xiàn)計(jì)數(shù)邏輯

2.1 用緩存系統(tǒng)統(tǒng)計(jì)計(jì)數(shù)

如果是一般場(chǎng)景，使用緩存系統(tǒng)執(zhí)行計(jì)數(shù)是滿(mǎn)足需求的，即使說(shuō)，由于redis服務(wù)集群異常重啟導(dǎo)致數(shù)據(jù)丟失，但是可以再次掃描一次表獲取表的總數(shù)。

但是如果是非常嚴(yán)謹(jǐn)?shù)膱?chǎng)景（銀行統(tǒng)計(jì)實(shí)際支付的訂單數(shù)據(jù)等），那可能有如下的問(wèn)題。

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

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

數(shù)據(jù)一致性問(wèn)題目前可分為三類(lèi)：

1.主從不一。解決辦法:半同步復(fù)制可以保證實(shí)時(shí)的一致性，因?yàn)閷?xiě)時(shí)寫(xiě)主和從之后才響應(yīng)，只不顧這樣寫(xiě)的并發(fā)上不去；其他訪問(wèn)有強(qiáng)制讀主、消息中間件路由讀主和緩存是否失效讀主；

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

3.一個(gè)事務(wù)跨多個(gè)節(jié)點(diǎn)或者多種數(shù)據(jù)庫(kù)(分庫(kù)分表和銀行轉(zhuǎn)賬這種例子) 。目前好像都是通過(guò)2pc,3pc來(lái)保證的。 

2.2 用數(shù)據(jù)庫(kù)保存計(jì)數(shù)

在數(shù)據(jù)庫(kù)中設(shè)計(jì)單獨(dú)的計(jì)數(shù)表，將插入數(shù)據(jù)、刪除數(shù)據(jù)的SQL和更新計(jì)數(shù)表的SQL語(yǔ)句作為同一個(gè)事務(wù)執(zhí)行。

圖片

在統(tǒng)計(jì)時(shí)，將讀取計(jì)數(shù)器和查詢(xún)最近數(shù)據(jù)也作為一個(gè)事務(wù)執(zhí)行，這樣拿到的就是理論上的實(shí)際值。

但是實(shí)際上，在高并發(fā)場(chǎng)景以及一般場(chǎng)景，這種統(tǒng)計(jì)的意義可能并不是很大，因?yàn)楫?dāng)你剛剛統(tǒng)計(jì)的數(shù)據(jù)，可能在返回的期間已經(jīng)有變化。

這時(shí)候再次有個(gè)問(wèn)題：在并發(fā)系統(tǒng)性能的角度考慮，在事務(wù)序列里，是先插入操作記錄，還是應(yīng)該先更新計(jì)數(shù)表？

答案是：先插入新紀(jì)錄。

• 因?yàn)椴迦胄录o(jì)錄只會(huì)影響到行鎖和間隙鎖，但是更新計(jì)數(shù)表會(huì)占用計(jì)數(shù)表的寫(xiě)鎖，而很多其他事務(wù)的插入操作就必須阻塞等待，即：并發(fā)度高的操作放在后面執(zhí)行，可以減少鎖等待；
• 計(jì)數(shù)表是公用表，根據(jù)鎖的二階段協(xié)議，在需要的時(shí)候獲取，在事務(wù)提交的時(shí)候釋放，晚獲取可以減少并發(fā)，提高吞吐量；

3 不同的count方法

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

• InnoDB存儲(chǔ)引擎查詢(xún)數(shù)據(jù)結(jié)果集；
• Server層根據(jù)結(jié)果集進(jìn)行遍歷統(tǒng)計(jì)；

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

MySQL執(zhí)行統(tǒng)計(jì)的原則是：

• server層要什么就給什么；
• InnoDB只給必要的值；
• 現(xiàn)在的優(yōu)化器只優(yōu)化了count(*)的語(yǔ)義為取行數(shù)，其他的語(yǔ)句沒(méi)有做優(yōu)化。

count(主鍵)：InnoDB引擎會(huì)遍歷整張表，把每一行的id取出來(lái)（存在數(shù)據(jù)行數(shù)據(jù)解析），把主鍵返回給server層（存在字段值拷貝）。判定id是否為空，不為空直接按行累加即可（這里應(yīng)該是可以?xún)?yōu)化的，因?yàn)橹麈I一定不允許為空）；同時(shí)，count(id)可能會(huì)走最小的索引來(lái)遍歷，并不一定非要走主鍵索引；

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

count(column)：

• 如果字段不允許為空，一行行從記錄里面讀取這個(gè)字段，按行累加；
• 如果字段允許為空，一行行從記錄讀取字段后，先判定是否為null，如果為null，則過(guò)濾掉，不為Null，則累加；
• 如果字段為索引，那么這里的統(tǒng)計(jì)就會(huì)走該索引；

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

MySQL文檔中有如下說(shuō)明：

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

因此，按照效率排序?yàn)椋篶ount(字段)<count(主鍵 id)<count(1)=count(*)，所以我建議你，盡量使用 count(*)。

從獲取的數(shù)值來(lái)看，count(字段)也一定是最小的，因?yàn)榱凶侄蔚闹悼赡転閚ull。

4 本章回顧問(wèn)題

把該講內(nèi)容總結(jié)為幾個(gè)問(wèn)題, 大家復(fù)習(xí)的時(shí)候可以先嘗試回答這些問(wèn)題檢查自己的掌握程度:

1. count(*)的實(shí)現(xiàn)方式在MySAM引擎和InnoDB引擎的實(shí)現(xiàn)方式各是怎么樣的? 為什么會(huì)有這種不同?
2. 使用緩存保存count總數(shù)存在什么問(wèn)題?
3. 如果使用一場(chǎng)單獨(dú)的表來(lái)記錄其他各張表的記錄數(shù)的話(huà),是怎么解決統(tǒng)計(jì)結(jié)果不精確的問(wèn)題的?
4. count(字段),count(id),count(1), count(*)各自是怎么樣的執(zhí)行機(jī)制, 效率排序是怎么樣的?