count(*)那么慢能用嗎，該怎么辦呢？
比較喜歡的一段話：不經(jīng)一番寒徹骨，怎得梅花撲鼻香 閱讀這篇文章大概需要20分鐘！
麻煩各位來(lái)個(gè)三連：點(diǎn)贊+在看+分享！
大家好前面我們大概了解了為什么delete from表名，表的大小還是沒有變??！以及數(shù)據(jù)刪除流程，數(shù)據(jù)頁(yè)空洞，online和inplace。重建表的兩種實(shí)現(xiàn)方式。今天介紹一下為什么count(*)那么慢。
count(*)實(shí)現(xiàn)方式
今天介紹的是MySQL的兩種常用的引擎方式。第一種是最早期的MySQL默認(rèn)引擎：myisam。第二種就是當(dāng)下最流行的MySQL默認(rèn)引擎innodb。兩種引擎的實(shí)現(xiàn)方式各不相同。下面我們來(lái)分析一下。
myisam
myisam引擎在處理countI(*)時(shí)，速度是非常快的。因?yàn)閙yisam的設(shè)計(jì)思路就是，會(huì)把一個(gè)表的統(tǒng)計(jì)總數(shù)存在一個(gè)磁盤上。當(dāng)用戶count這個(gè)語(yǔ)句的時(shí)候。不需要把這個(gè)表中的數(shù)據(jù)全部查一遍，只需要把這個(gè)數(shù)據(jù)頁(yè)上的統(tǒng)計(jì)數(shù)據(jù)取出來(lái)就可以了。所以myisam在這一點(diǎn)效率是非常高的。
但是在有一點(diǎn)myisam是的性能是非常差的。就是當(dāng)這個(gè)count語(yǔ)句的時(shí)候后面加了一個(gè)where條件的時(shí)候，myisam與innodb一樣。會(huì)采用從磁盤讀取數(shù)據(jù)累計(jì)的手法。這樣性能也就慢了下來(lái)。
innodb
innodb相比myisam就弱了一些。innodb獨(dú)特的存儲(chǔ)方式并沒有采用這種方式。因?yàn)閕nnodb兼顧著事務(wù)的特性，與mvcc多并發(fā)版本的實(shí)現(xiàn)，無(wú)法確保應(yīng)該返回多少行，會(huì)有一個(gè)一致性的問題。所以innodb沒有采用這種方式。innodb的處理方式是把當(dāng)前數(shù)據(jù)表中的所有數(shù)據(jù)全部撈取到內(nèi)存上。然后進(jìn)行統(tǒng)計(jì)計(jì)數(shù)。
舉例說明
假設(shè)表 t 中現(xiàn)在有 10000 條記錄，我們?cè)O(shè)計(jì)了三個(gè)用戶并行的會(huì)話。
會(huì)話 A 先啟動(dòng)事務(wù)并查詢一次表的總行數(shù)；
會(huì)話 B 啟動(dòng)事務(wù)，插入一行后記錄后，查詢表的總行數(shù)；
會(huì)話 C 先啟動(dòng)一個(gè)單獨(dú)的語(yǔ)句，插入一行記錄后，查詢表的總行數(shù)。
我們假設(shè)從上到下是按照時(shí)間順序執(zhí)行的，同一行語(yǔ)句是在同一時(shí)刻執(zhí)行的。
你會(huì)看到，在最后一個(gè)時(shí)刻，三個(gè)會(huì)話 A、B、C 會(huì)同時(shí)查詢表 t 的總行數(shù)，但拿到的結(jié)果卻不同。
這和 InnoDB 的事務(wù)設(shè)計(jì)有關(guān)系，可重復(fù)讀是它默認(rèn)的隔離級(jí)別，在代碼上就是通過多版本并發(fā)控制，也就是 MVCC 來(lái)實(shí)現(xiàn)的。每一行記錄都要判斷自己是否對(duì)這個(gè)會(huì)話可見，因此對(duì)于 count(*) 請(qǐng)求來(lái)說，InnoDB 只好把數(shù)據(jù)一行一行地讀出依次判斷，可見的行才能夠用于計(jì)算“基于這個(gè)查詢”的表的總行數(shù)。
count(*)優(yōu)化
性能那么慢不需要優(yōu)化嗎？肯定是需要優(yōu)化的。那么是如何優(yōu)化的呢？
InnoDB 是索引組織表，主鍵索引樹的葉子節(jié)點(diǎn)是數(shù)據(jù)，而普通索引樹的葉子節(jié)點(diǎn)是主鍵值。所以，普通索引樹比主鍵索引樹小很多。對(duì)于 count(*) 這樣的操作，遍歷哪個(gè)索引樹得到的結(jié)果邏輯上都是一樣的。因此，MySQL 優(yōu)化器會(huì)找到最小的那棵樹來(lái)遍歷。在保證邏輯正確的前提下，盡量減少掃描的數(shù)據(jù)量，是數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)的通用法則之一。
擴(kuò)展一個(gè)語(yǔ)法：跟統(tǒng)計(jì)行數(shù)相同的還有一個(gè)SQL是 show table status 這個(gè)語(yǔ)句也會(huì)返回相應(yīng)的行數(shù)。但是這個(gè)行數(shù)是極其不準(zhǔn)的，他是通過一種采樣來(lái)估算的。table_rowsu是從這種估算得來(lái)的。據(jù)官方介紹誤差大概在40-50%。
小結(jié)
MyISAM 表雖然 count(*) 很快，但是不支持事務(wù)；
show table status 命令雖然返回很快，但是不準(zhǔn)確；
InnoDB 表直接 count(*) 會(huì)遍歷全表，雖然結(jié)果準(zhǔn)確，但會(huì)導(dǎo)致性能問題。
用緩存系統(tǒng)保存計(jì)數(shù)
上面介紹了innodb與myisam對(duì)count的計(jì)數(shù)的性能都是非常不友好的，那么我們可不可以基礎(chǔ)count計(jì)數(shù)設(shè)計(jì)一個(gè)系統(tǒng)用來(lái)專門計(jì)數(shù)呢。
你可以第一時(shí)間會(huì)想到緩存系統(tǒng)，說到緩存系統(tǒng)肯定第一時(shí)間也會(huì)想到Redis。Redis的性能是非常好的，但是有一個(gè)通病就是。當(dāng)MySQL跟Redis協(xié)同完全緩存計(jì)數(shù)的時(shí)候。就會(huì)存在丟失更新的狀況。
舉例說明
試想如果剛剛在數(shù)據(jù)表中插入了一行，Redis 中保存的值也加了 1，然后 Redis 異常重啟了，重啟后你要從存儲(chǔ) redis 數(shù)據(jù)的地方把這個(gè)值讀回來(lái)，而剛剛加 1 的這個(gè)計(jì)數(shù)操作卻丟失了。
當(dāng)然了，這還是有解的。比如，Redis 異常重啟以后，到數(shù)據(jù)庫(kù)里面單獨(dú)執(zhí)行一次 count(*) 獲取真實(shí)的行數(shù)，再把這個(gè)值寫回到 Redis 里就可以了。異常重啟畢竟不是經(jīng)常出現(xiàn)的情況，這一次全表掃描的成本，還是可以接受的。
但實(shí)際上，將計(jì)數(shù)保存在緩存系統(tǒng)中的方式，還不只是丟失更新的問題。即使 Redis 正常工作，這個(gè)值還是邏輯上不精確的。
你可以設(shè)想一下有這么一個(gè)頁(yè)面，要顯示操作記錄的總數(shù)，同時(shí)還要顯示最近操作的 100 條記錄。那么，這個(gè)頁(yè)面的邏輯就需要先到 Redis 里面取出計(jì)數(shù)，再到數(shù)據(jù)表里面取數(shù)據(jù)記錄。
下列兩種情況都是不對(duì)的
查到的 100 行結(jié)果里面有最新插入記錄，而 Redis 的計(jì)數(shù)里還沒加 1；
查到的 100 行結(jié)果里沒有最新插入的記錄，而 Redis 的計(jì)數(shù)里已經(jīng)加了 1。
看圖我們繼續(xù)分析一下
如上圖會(huì)話 A 是一個(gè)插入交易記錄的邏輯，往數(shù)據(jù)表里插入一行 R，然后 Redis 計(jì)數(shù)加 1；會(huì)話 B 就是查詢頁(yè)面顯示時(shí)需要的數(shù)據(jù)。
在這個(gè)時(shí)序里，在 T3 時(shí)刻會(huì)話 B 來(lái)查詢的時(shí)候，會(huì)顯示出新插入的 R 這個(gè)記錄，但是 Redis 的計(jì)數(shù)還沒加 1。這時(shí)候，就會(huì)出現(xiàn)我們說的數(shù)據(jù)不一致。
你一定會(huì)說，這是因?yàn)槲覀儓?zhí)行新增記錄邏輯時(shí)候，是先寫數(shù)據(jù)表，再改 Redis 計(jì)數(shù)。而讀的時(shí)候是先讀 Redis，再讀數(shù)據(jù)表，這個(gè)順序是相反的。那么，如果保持順序一樣的話，是不是就沒問題了？我們現(xiàn)在把會(huì)話 A 的更新順序換一下，再看看執(zhí)行結(jié)果。
你會(huì)發(fā)現(xiàn)，這時(shí)候反過來(lái)了，會(huì)話 B 在 T3 時(shí)刻查詢的時(shí)候，Redis 計(jì)數(shù)加了 1 了，但還查不到新插入的 R 這一行，也是數(shù)據(jù)不一致的情況。在并發(fā)系統(tǒng)里面，我們是無(wú)法精確控制不同線程的執(zhí)行時(shí)刻的，因?yàn)榇嬖趫D中的這種操作序列，所以，我們說即使 Redis 正常工作，這個(gè)計(jì)數(shù)值還是邏輯上不精確的。
在數(shù)據(jù)庫(kù)保存計(jì)數(shù)呢
在文章一開始的時(shí)候，說過不能在數(shù)據(jù)表里保存計(jì)數(shù)，這里又繞了回來(lái)。我相信很多小伙伴都糊涂了。先帶著糊涂看完再一一介紹好吧，
我們直接統(tǒng)一放在一個(gè)表中。首先就要解決數(shù)據(jù)崩潰的問題。而innodb是支持?jǐn)?shù)據(jù)崩潰不丟數(shù)據(jù)的。
然后我們?cè)俳鉀Q數(shù)據(jù)準(zhǔn)確性問題。
我們這篇文章要解決的問題，都是由于 InnoDB 要支持事務(wù)，從而導(dǎo)致 InnoDB 表不能把 count(*) 直接存起來(lái)，然后查詢的時(shí)候直接返回形成的。所謂以子之矛攻子之盾，現(xiàn)在我們就利用“事務(wù)”這個(gè)特性，把問題解決掉。如上圖
我們來(lái)看下現(xiàn)在的執(zhí)行結(jié)果。雖然會(huì)話 B 的讀操作仍然是在 T3 執(zhí)行的，但是因?yàn)檫@時(shí)候更新事務(wù)還沒有提交，所以計(jì)數(shù)值加 1 這個(gè)操作對(duì)會(huì)話 B 還不可見。因此，會(huì)話 B 看到的結(jié)果里， 查計(jì)數(shù)值和“最近 100 條記錄”看到的結(jié)果，邏輯上就是一致的。
不同count()語(yǔ)句的流程
首先你要弄清楚 count() 的語(yǔ)義。count() 是一個(gè)聚合函數(shù)，對(duì)于返回的結(jié)果集，一行行地判斷，如果 count 函數(shù)的參數(shù)不是 NULL，累計(jì)值就加 1，否則不加。最后返回累計(jì)值。
count(*)
表示所有數(shù)據(jù)中，不管是不是null，都算在內(nèi)。具體的流程是從磁盤IO上讀取數(shù)據(jù)，放到內(nèi)存中然后一個(gè)一個(gè)累計(jì)計(jì)數(shù)。
因?yàn)镸ySQL內(nèi)部?jī)?yōu)化問題，不會(huì)取值
count(字段)
表示所有的數(shù)據(jù)中，不為null的所有數(shù)據(jù)的累計(jì)。具體流程是從從磁盤IO上讀取相應(yīng)的字段數(shù)據(jù)，放到內(nèi)存中然后判斷當(dāng)前數(shù)值是否為null。只有不為null的值才進(jìn)行累計(jì)計(jì)數(shù)。
count(1)
InnoDB 引擎遍歷整張表，但不取值。server 層對(duì)于返回的每一行，放一個(gè)數(shù)字“1”進(jìn)去，判斷是不可能為空的，按行累加
綜上所述：按照效率排序的話，count(字段) < count(主鍵 id) < count(1) ≈ count( )。所以還是建議你盡量使用count( )