在開發(fā)系統(tǒng)的時候，你可能經(jīng)常需要計算一個表的行數(shù)，比如一個交易系統(tǒng)的所有變更記錄總數(shù)。這時候你可能會想，一條 select count(*) from t 語句不就解決了嗎？

但是，你會發(fā)現(xiàn)隨著系統(tǒng)中記錄數(shù)越來越多，這條語句執(zhí)行得也會越來越慢。然后你可能就想了，MySQL 怎么這么笨啊，記個總數(shù)，每次要查的時候直接讀出來，不就好了嗎。

本文，我們就來聊聊 count(*) 語句到底是怎樣實現(xiàn)的，以及 MySQL 為什么會這么實現(xiàn)。

count(*) 的實現(xiàn)方式

• 你首先要明確的是，在不同的 MySQL 引擎中，count() 有不同的實現(xiàn)方式。
  MyISAM 引擎把一個表的總行數(shù)存在了磁盤上，因此執(zhí)行 count() 的時候會直接返回這個數(shù)，效率很高；
• 而 InnoDB 引擎就麻煩了，它執(zhí)行 count(*) 的時候，需要把數(shù)據(jù)一行一行地從引擎里面讀出來，然后累積計數(shù)。
• 這里需要注意的是，我們在這篇文章里討論的是沒有過濾條件的 count(*)，如果加了 where 條件的話，MyISAM 表也是不能返回得這么快的。
• 在前面的文章中，我們一起分析了為什么要使用 InnoDB，因為不論是在事務支持、并發(fā)能力還是在數(shù)據(jù)安全方面，InnoDB 都優(yōu)于 MyISAM。我猜你的表也一定是用了 InnoDB 引擎。這就是當你的記錄數(shù)越來越多的時候，計算一個表的總行數(shù)會越來越慢的原因。
• 那為什么 InnoDB 不跟 MyISAM 一樣，也把數(shù)字存起來呢？
  這是因為即使是在同一個時刻的多個查詢，由于多版本并發(fā)控制（MVCC）的原因，InnoDB 表“應該返回多少行”也是不確定的。這里，用一個算 count(*) 的例子來為你解釋一下。
• 假設表 t 中現(xiàn)在有 10000 條記錄，我們設計了三個用戶并行的會話。
  • 會話 A 先啟動事務并查詢一次表的總行數(shù)；
  • 會話 B 啟動事務，插入一行后記錄后，查詢表的總行數(shù)；
  • 會話 C 先啟動一個單獨的語句，插入一行記錄后，查詢表的總行數(shù)。
• 我們假設從上到下是按照時間順序執(zhí)行的，同一行語句是在同一時刻執(zhí)行的。

會話A,B,C執(zhí)行流程

• 你會看到，在最后一個時刻，三個會話 A、B、C 會同時查詢表 t 的總行數(shù)，但拿到的結果卻不同。
• 這和 InnoDB 的事務設計有關系，可重復讀是它默認的隔離級別，在代碼上就是通過多版本并發(fā)控制，也就是 MVCC 來實現(xiàn)的。每一行記錄都要判斷自己是否對這個會話可見，因此對于 count(*) 請求來說，InnoDB 只好把數(shù)據(jù)一行一行地讀出依次判斷，可見的行才能夠用于計算“基于這個查詢”的表的總行數(shù)。
• 當然，現(xiàn)在這個看上去笨笨的 MySQL，在執(zhí)行 count(*) 操作的時候還是做了優(yōu)化的。
• 你知道的，InnoDB 是索引組織表，主鍵索引樹的葉子節(jié)點是數(shù)據(jù)，而普通索引樹的葉子節(jié)點是主鍵值。所以，普通索引樹比主鍵索引樹小很多。對于 count(*) 這樣的操作，遍歷哪個索引樹得到的結果邏輯上都是一樣的。因此，MySQL 優(yōu)化器會找到最小的那棵樹來遍歷。在保證邏輯正確的前提下，盡量減少掃描的數(shù)據(jù)量，是數(shù)據(jù)庫系統(tǒng)設計的通用法則之一。
• 如果你用過 show table status 命令的話，就會發(fā)現(xiàn)這個命令的輸出結果里面也有一個 TABLE_ROWS 用于顯示這個表當前有多少行，這個命令執(zhí)行挺快的，那這個 TABLE_ROWS 能代替 count(*) 嗎？
• 索引統(tǒng)計的值是通過采樣來估算的。實際上，TABLE_ROWS 就是從這個采樣估算得來的，因此它也很不準。有多不準呢，官方文檔說誤差可能達到 40% 到 50%。所以，show table status 命令顯示的行數(shù)也不能直接使用。
  到這里我們小結一下：
  • MyISAM 表雖然 count(*) 很快，但是不支持事務；
  • show table status 命令雖然返回很快，但是不準確；
  • InnoDB 表直接 count(*) 會遍歷全表，雖然結果準確，但會導致性能問題。
• 那么，如果你現(xiàn)在有一個頁面經(jīng)常要顯示交易系統(tǒng)的操作記錄總數(shù)，到底應該怎么辦呢？答案是，我們只能自己計數(shù)。
• 接下來，我們討論一下，看看自己計數(shù)有哪些方法，以及每種方法的優(yōu)缺點有哪些。
• 這里，我先和你說一下這些方法的基本思路：你需要自己找一個地方，把操作記錄表的行數(shù)存起來。

用緩存系統(tǒng)保存計數(shù)

• 對于更新很頻繁的庫來說，你可能會第一時間想到，用緩存系統(tǒng)來支持。
  你可以用一個 Redis 服務來保存這個表的總行數(shù)。這個表每被插入一行 Redis 計數(shù)就加 1，每被刪除一行 Redis 計數(shù)就減 1。這種方式下，讀和更新操作都很快，但你再想一下這種方式存在什么問題嗎？
• 沒錯，緩存系統(tǒng)可能會丟失更新。
• Redis 的數(shù)據(jù)不能永久地留在內(nèi)存里，所以你會找一個地方把這個值定期地持久化存儲起來。但即使這樣，仍然可能丟失更新。試想如果剛剛在數(shù)據(jù)表中插入了一行，Redis 中保存的值也加了 1，然后 Redis 異常重啟了，重啟后你要從存儲 redis 數(shù)據(jù)的地方把這個值讀回來，而剛剛加 1 的這個計數(shù)操作卻丟失了。
• 當然了，這還是有解的。比如，Redis 異常重啟以后，到數(shù)據(jù)庫里面單獨執(zhí)行一次 count(*) 獲取真實的行數(shù)，再把這個值寫回到 Redis 里就可以了。異常重啟畢竟不是經(jīng)常出現(xiàn)的情況，這一次全表掃描的成本，還是可以接受的。
• 但實際上，將計數(shù)保存在緩存系統(tǒng)中的方式，還不只是丟失更新的問題。即使 Redis 正常工作，這個值還是邏輯上不精確的。
• 你可以設想一下有這么一個頁面，要顯示操作記錄的總數(shù)，同時還要顯示最近操作的 100 條記錄。那么，這個頁面的邏輯就需要先到 Redis 里面取出計數(shù)，再到數(shù)據(jù)表里面取數(shù)據(jù)記錄。
• 我們是這么定義不精確的：
  • 一種是，查到的 100 行結果里面有最新插入記錄，而 Redis 的計數(shù)里還沒加 1；
  • 另一種是，查到的 100 行結果里沒有最新插入的記錄，而 Redis 的計數(shù)里已經(jīng)加了 1。
• 這兩種情況，都是邏輯不一致的。我們一起來看看這個時序圖。

執(zhí)行時序圖

• 會話 A 是一個插入交易記錄的邏輯，往數(shù)據(jù)表里插入一行 R，然后 Redis 計數(shù)加 1；會話 B 就是查詢頁面顯示時需要的數(shù)據(jù)。
• 在圖的這個時序里，在 T3 時刻會話 B 來查詢的時候，會顯示出新插入的 R 這個記錄，但是 Redis 的計數(shù)還沒加 1。這時候，就會出現(xiàn)我們說的數(shù)據(jù)不一致。
• 你一定會說，這是因為我們執(zhí)行新增記錄邏輯時候，是先寫數(shù)據(jù)表，再改 Redis 計數(shù)。而讀的時候是先讀 Redis，再讀數(shù)據(jù)表，這個順序是相反的。那么，如果保持順序一樣的話，是不是就沒問題了？我們現(xiàn)在把會話 A 的更新順序換一下，再看看執(zhí)行結果。

調(diào)整順序執(zhí)行圖

• 你會發(fā)現(xiàn)，這時候反過來了，會話 B 在 T3 時刻查詢的時候，Redis 計數(shù)加了 1 了，但還查不到新插入的 R 這一行，也是數(shù)據(jù)不一致的情況。
• 在并發(fā)系統(tǒng)里面，我們是無法精確控制不同線程的執(zhí)行時刻的，因為存在圖中的這種操作序列，所以，我們說即使 Redis 正常工作，這個計數(shù)值還是邏輯上不精確的。

在數(shù)據(jù)庫保存計數(shù)

• 根據(jù)上面的分析，用緩存系統(tǒng)保存計數(shù)有丟失數(shù)據(jù)和計數(shù)不精確的問題。那么，如果我們把這個計數(shù)直接放到數(shù)據(jù)庫里單獨的一張計數(shù)表 C 中，又會怎么樣呢？首先，這解決了崩潰丟失的問題，InnoDB 是支持崩潰恢復不丟數(shù)據(jù)的。
• 然后，我們再看看能不能解決計數(shù)不精確的問題。
  你會說，這不一樣嗎？無非就是把圖中對 Redis 的操作，改成了對計數(shù)表 C 的操作。只要出現(xiàn)圖的這種執(zhí)行序列，這個問題還是無解的吧？
• 這個問題還真不是無解的。
• 我們這篇文章要解決的問題，都是由于 InnoDB 要支持事務，從而導致 InnoDB 表不能把 count(*) 直接存起來，然后查詢的時候直接返回形成的?，F(xiàn)在我們就利用“事務”這個特性，把問題解決掉。

會話A,B執(zhí)行時序圖

• 我們來看下現(xiàn)在的執(zhí)行結果。雖然會話 B 的讀操作仍然是在 T3 執(zhí)行的，但是因為這時候更新事務還沒有提交，所以計數(shù)值加 1 這個操作對會話 B 還不可見。因此，會話 B 看到的結果里， 查計數(shù)值和“最近 100 條記錄”看到的結果，邏輯上就是一致的。

不同的 count 用法

• 在 select count(?) from t 這樣的查詢語句里面，count()、count(主鍵 id)、count(字段) 和 count(1) 等不同用法的性能，有哪些差別。今天談到了 count() 的性能問題，借此機會和你詳細說明一下這幾種用法的性能差別。
• 需要注意的是，下面的討論還是基于 InnoDB 引擎的。
• 這里，首先你要弄清楚 count() 的語義。count() 是一個聚合函數(shù)，對于返回的結果集，一行行地判斷，如果 count 函數(shù)的參數(shù)不是 NULL，累計值就加 1，否則不加。最后返回累計值。
• 所以，count(*)、count(主鍵 id) 和 count(1) 都表示返回滿足條件的結果集的總行數(shù)；而 count(字段），則表示返回滿足條件的數(shù)據(jù)行里面，參數(shù)“字段”不為 NULL 的總個數(shù)。
• 至于分析性能差別的時候，你可以記住這么幾個原則：
  • server 層要什么就給什么；
  • InnoDB 只給必要的值；
  • 現(xiàn)在的優(yōu)化器只優(yōu)化了 count(*) 的語義為“取行數(shù)”，其他“顯而易見”的優(yōu)化并沒有做。
• 對于 count(主鍵 id) 來說，InnoDB 引擎會遍歷整張表，把每一行的 id 值都取出來，返回給 server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加。
• 對于 count(1) 來說，InnoDB 引擎遍歷整張表，但不取值。server 層對于返回的每一行，放一個數(shù)字“1”進去，判斷是不可能為空的，按行累加。
• 單看這兩個用法的差別的話，你能對比出來，count(1) 執(zhí)行得要比 count(主鍵 id) 快。因為從引擎返回 id 會涉及到解析數(shù)據(jù)行，以及拷貝字段值的操作。
• 對于 count(字段) 來說：
  • 如果這個“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個字段，判斷不能為 null，按行累加；
  • 如果這個“字段”定義允許為 null，那么執(zhí)行的時候，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。
• 也就是前面的第一條原則，server 層要什么字段，InnoDB 就返回什么字段。
  但是 count() 是例外，并不會把全部字段取出來，而是專門做了優(yōu)化，不取值。count() 肯定不是 null，按行累加。
• 看到這里，你一定會說，優(yōu)化器就不能自己判斷一下嗎，主鍵 id 肯定非空啊，為什么不能按照 count(*) 來處理，多么簡單的優(yōu)化啊。
• 當然，MySQL 專門針對這個語句進行優(yōu)化，也不是不可以。但是這種需要專門優(yōu)化的情況太多了，而且 MySQL 已經(jīng)優(yōu)化過 count(*) 了，你直接使用這種用法就可以了。
• 所以結論是：按照效率排序的話，count(字段)<count(主鍵 id)<count(1)≈count()，所以我建議你，盡量使用 count()。

小結

• 本文我們聊了MySQL 中獲得表行數(shù)的兩種方法。我們提到了在不同引擎中 count(*) 的實現(xiàn)方式是不一樣的，也分析了用緩存系統(tǒng)來存儲計數(shù)值存在的問題。
• 其實，把計數(shù)放在 Redis 里面，不能夠保證計數(shù)和 MySQL 表里的數(shù)據(jù)精確一致的原因，是這兩個不同的存儲構成的系統(tǒng)，不支持分布式事務，無法拿到精確一致的視圖。而把計數(shù)值也放在 MySQL 中，就解決了一致性視圖的問題。
  InnoDB 引擎支持事務，我們利用好事務的原子性和隔離性，就可以簡化在業(yè)務開發(fā)時的邏輯。這也是 InnoDB 引擎?zhèn)涫芮嗖A的原因之一。