筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》 （本篇內圖片均來自丁奇老師的講解，如有侵權，請聯繫我刪除） 14） --count(*)這麼慢，我該怎麼辦？ 有時你會發現，隨著系統中記錄數越來越多，select count(*) from t執行得也越來越慢。那麼今天，我們就來聊聊count ...
筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》
（本篇內圖片均來自丁奇老師的講解，如有侵權，請聯繫我刪除）
14） --count(*)這麼慢，我該怎麼辦？
有時你會發現，隨著系統中記錄數越來越多，select count(*) from t執行得也越來越慢。那麼今天，我們就來聊聊count(*)語句到底是怎樣實現的，以及MySQL為什麼會這麼實現。
count(*)的實現方式:
前面我們提到過，MySQL的引擎是插件式的，這裡要明確一點，在不同的MySQL引擎中，count(*)有不同的實現方式。
- MyISAM引擎把一個表的總行數存在了磁碟上，因此執行count(*)的時候會直接返回這個數，效率很高；
- InnoDB引擎就麻煩了，它執行count(*)的時候，需要把數據一行一行地從引擎裡面讀出來，然後累計計數。
這裡要說明的是，我們這裡的count(*)，是沒有加任何where的過濾條件的，如果添加了過濾條件，MyISAM引擎也是不能返回這麼快的。之前的文章我們分享了為什麼要使用InnoDB引擎，因為不論是在事務支持，併發能力還是數據安全方面，InnoDB都優於MyISAM。那麼，為什麼InnoDB不跟MyISAM一樣，也把數字存下來呢？
這是因為即使是在同一個時刻的多個查詢，優於MVCC（多版本併發控制）的原因，InnoDB表“應該返回多少行”也是不確定的。這裡，我用一個算count（*）的例子來解釋：假設表t中現在有10000條記錄，我們設計了三個用戶並行的會話。
圖1 會話A,B,C的執行流程
- 會話A先啟動事務並查詢一次表的總行數；
- 會話B啟動事務，插入一行記錄後，查詢表的總行數；
- 會話C先啟動一個單獨的語句，插入一行記錄後，查詢表的總行數。
我們假設從上到下是按照時間順序執行的，同一行語句是在同一時刻執行的。你會看到，三個會話A,B,C會同時查詢表t的總行數，但拿到的結果卻不同。這和InnoDB的事務設計有關係，可重覆讀是它預設的隔離級別，在代碼上是通過MVCC，也就是多版本併發控制來實現的。每一行記錄都要判斷自己是否對這個會話可見，因此對於count(*)請求來說，InnoDB只好把數據一行一行地讀出依次判斷，可見的行才能夠用於計算“基於這個查詢”的表的總行數。
當然，MySQL在執行count(*)過程時還是做了優化的。InnoDB是索引組織表，主鍵索引的葉子節點是數據，而普通索引的葉子節點是主鍵值。所以，普通索引樹比主鍵索引樹小很多。對於count(*)這樣的操作，遍歷哪個索引樹得到的結果邏輯上都是一樣的。因此，MySQL優化器會找到最小的那棵樹來遍歷。在保證邏輯正確的前提下，儘量減少掃描的數據量，是資料庫系統設計的通用法則之一。
另外，如果你用過show table status命令的話，就會發現這個命令的輸出結果裡面也有一個TABLE_ROWS用於顯示這個表當前有多少行，這個命令執行挺快的，那這個欄位能代替count(*)嗎?實際上是不能的，我們之前提到過，索引統計的值是通過採樣來估算的。實際上，這個欄位正是通過採樣估算二來的，因此它也很不准。官方文檔說這個欄位的誤差可能達到40%~50%.
現在我們回到文章開頭的問題，如果你現在有一個頁面要經常顯示交易操作記錄的總數，到底應該怎麼辦呢？答案是，我們只能自己計數。基本思路是我們自己找一個地方，把操作記錄表的行數存起來。
用緩存系統保存計數：
你可能會很自然的想到使用Redis這樣的服務來保存這個表的總行數。這個表每次插入一行Redis記錄就加1.每次刪除一行，Redis記錄就減1。但你再想一下，這會有什麼問題嘛？沒錯，緩存系統有可能會丟失更新。如Redis異常重啟，這時redis中保存的記錄都不會有了。當然，這個問題還是有解決辦法的，比如每次Redis重啟之後我們都去DB中單獨執行一次count(*)來求得記錄，畢竟redis並不會常常重啟，這個成本也不會很高。但實際上，即使你使用這種方式，Redis的記錄還是有可能是不精確的。我們來舉兩個例子：
- 如果我們先插入記錄，再更新Redis時可能會存在這一情況。T1時刻表t插入數據R。T2時刻讀取redis記錄數，並且從DB中查詢最近的100條記錄。T3時刻，更新redis記錄，計數加1。這種情況下，T2時刻讀取最近100條記錄是包括數據R的，而同時Redis的記錄數是不包含R的。
- 反過來，如果我們先更新Redis記錄，再插入數據呢？T1時刻我們更新Redis計數加1。T2時刻，讀取Redis記錄，從DB中查詢最近的100條記錄。T3時刻，向表t插入數據R。這種情況下，T2時刻讀取最近100條記錄是不包括R的，而Redis的記錄中卻是包含R這個記錄的。
在資料庫保存計數：
使用緩存可能會有記錄不精確的問題，如果我們把這個計數直接放到資料庫里單獨的一張計數表C中，又會怎麼樣呢？首先是異常重啟的問題，資料庫崩潰了，異常重啟。MySQL是支持崩潰恢復的，所以這其實不是個問題。我們再來看看剛剛在緩存系統中出現的記錄不精確的問題：
相信你已經發行，記錄不精確的問題的關鍵在於，讀取記錄和記錄總數時數據還沒有完整的更新完。即Redis與DB中記錄並不完全一致。換句話說，比如上面兩個例子，如果我們在T4時刻再去執行T2中的操作就不會出現這類問題了。
圖2，會話A,B的執行時序圖
如圖2所示，由於MySQL預設的隔離級別是可重覆讀，所以在會話B中，T3時刻並不會讀到T2時刻改變的數據。因為T3時刻，會話A的改動還未提交，它對會話B來說是不可見的。這樣，就保證了數據的精確結果。
不同Count的用法：
你可能看到過各種寫法來求總行數，如count(*), count(1)，count(主鍵id)等等。這裡我們首先要弄清楚count()語義。count()是一個聚合函數，對於返回的結果集，一行行地判斷，如果count函數的參數是NULL，累計值就加1，否則就不加。最後返回累計值。
所以count(*), count(1)，count(主鍵id)返回的就是滿足條件的結果集的總行數；而count(欄位)，返回的就是滿足條件的數據里，參數“欄位”不為Null的總個數。MySQL有這麼幾個原則：
- server層要什麼就給什麼
- InnoDB只給必要的值
- 現在優化器只優化了count(*)的語義為“取行數”，其他“顯而易見”的優化並沒有做。
因此，對於count(主鍵id),InnoDB先是會遍歷整張表，把每一行的id都取出來，返回給server層。server層拿到id後，判斷是不可能為空的，就按行累計。對於count(1)，InnoDB先是會遍歷整張表，server層對返回的每一行，放一個“1”進去，判斷不為空，按行累計。由於少了返回id，解析數據行等操作，count（1）會更快一些。對於count(欄位)，不僅要判斷數據行，還要判斷是否為null。而對於count(*)，由於做了優化，直接按行累計。所以結論是，按效率排序的話 count(欄位)<count(主鍵id)<count(1)約等於count(*)。
上期問題：
如果有一個情況是這樣的：想要收縮表空間，結果適得其反，看上去是：
- 一個表t文件大小為1TB；
- 對這個表執行alter table t engine = InnoDB；
- 執行完成後，空間不僅沒有變小，還稍微大了一點，變為了1.01TB
請問這是什麼原因導致的呢？
答：在DDL期間，如果剛好有外部的DML在執行，這期間可能會引入一些新的空洞。另外，還有一個更深層的機制在文章中沒有提到。在重建表的時候，InnoDB不會把整張表占滿，每個頁會留下1/16的空間給後續的更新用。也就是說，其實重建後的表不是“最”緊湊的。所以導致上述情況的可能是這麼一個流程：
- 將表t重建一次。
- 插入一部分數據，但是插入的這些數據，用掉了一部分預留空間。
- 再重建一次表t，就可能會出現上面的情況了。
問題：
在上面關於count計數的討論中，我們用了事務來確保數據的精確性。由於事務可以保證中間結果不被別的事務讀到，因此修改計數值和插入新的記錄的順序是不影響邏輯結果的。但是，從併發系統性能的角度考慮，你覺得在這個事務序列里，應該先插入操作記錄，還是先更新計數表呢？
