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

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

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

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

• 你首先要明確的是，在不同的 MySQL 引擎中香到，count() 有不同的實(shí)現(xiàn)方式。
  MyISAM 引擎把一個(gè)表的總行數(shù)存在了磁盤上报破，因此執(zhí)行 count() 的時(shí)候會(huì)直接返回這個(gè)數(shù)悠就，效率很高；
• 而 InnoDB 引擎就麻煩了充易，它執(zhí)行 count(*) 的時(shí)候梗脾，需要把數(shù)據(jù)一行一行地從引擎里面讀出來，然后累積計(jì)數(shù)盹靴。
• 這里需要注意的是炸茧，我們?cè)谶@篇文章里討論的是沒有過濾條件的 count(*)，如果加了 where 條件的話稿静，MyISAM 表也是不能返回得這么快的梭冠。
• 在前面的文章中，我們一起分析了為什么要使用 InnoDB改备，因?yàn)椴徽撌窃谑聞?wù)支持控漠、并發(fā)能力還是在數(shù)據(jù)安全方面，InnoDB 都優(yōu)于 MyISAM悬钳。我猜你的表也一定是用了 InnoDB 引擎盐捷。這就是當(dāng)你的記錄數(shù)越來越多的時(shí)候，計(jì)算一個(gè)表的總行數(shù)會(huì)越來越慢的原因默勾。
• 那為什么 InnoDB 不跟 MyISAM 一樣碉渡，也把數(shù)字存起來呢？
  這是因?yàn)榧词故窃谕粋€(gè)時(shí)刻的多個(gè)查詢母剥，由于多版本并發(fā)控制（MVCC）的原因滞诺，InnoDB 表“應(yīng)該返回多少行”也是不確定的。這里环疼，用一個(gè)算 count(*) 的例子來為你解釋一下铭段。
• 假設(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ú)的語句芬膝，插入一行記錄后，查詢表的總行數(shù)形娇。
• 我們假設(shè)從上到下是按照時(shí)間順序執(zhí)行的锰霜，同一行語句是在同一時(shí)刻執(zhí)行的。

會(huì)話A,B,C執(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 來實(shí)現(xiàn)的搀军。每一行記錄都要判斷自己是否對(duì)這個(gè)會(huì)話可見膨俐，因此對(duì)于 count(*) 請(qǐng)求來說，InnoDB 只好把數(shù)據(jù)一行一行地讀出依次判斷罩句，可見的行才能夠用于計(jì)算“基于這個(gè)查詢”的表的總行數(shù)焚刺。
• 當(dāng)然，現(xiàn)在這個(gè)看上去笨笨的 MySQL门烂，在執(zhí)行 count(*) 操作的時(shí)候還是做了優(yōu)化的檩坚。
• 你知道的，InnoDB 是索引組織表诅福，主鍵索引樹的葉子節(jié)點(diǎn)是數(shù)據(jù)匾委，而普通索引樹的葉子節(jié)點(diǎn)是主鍵值。所以氓润，普通索引樹比主鍵索引樹小很多赂乐。對(duì)于 count(*) 這樣的操作，遍歷哪個(gè)索引樹得到的結(jié)果邏輯上都是一樣的咖气。因此挨措，MySQL 優(yōu)化器會(huì)找到最小的那棵樹來遍歷。在保證邏輯正確的前提下崩溪，盡量減少掃描的數(shù)據(jù)量浅役，是數(shù)據(jù)庫(kù)系統(tǒng)設(shè)計(jì)的通用法則之一。
• 如果你用過 show table status 命令的話伶唯，就會(huì)發(fā)現(xiàn)這個(gè)命令的輸出結(jié)果里面也有一個(gè) TABLE_ROWS 用于顯示這個(gè)表當(dāng)前有多少行觉既，這個(gè)命令執(zhí)行挺快的，那這個(gè) TABLE_ROWS 能代替 count(*) 嗎？
• 索引統(tǒng)計(jì)的值是通過采樣來估算的瞪讼。實(shí)際上钧椰，TABLE_ROWS 就是從這個(gè)采樣估算得來的，因此它也很不準(zhǔn)符欠。有多不準(zhǔn)呢嫡霞，官方文檔說誤差可能達(dá)到 40% 到 50%。所以希柿，show table status 命令顯示的行數(shù)也不能直接使用诊沪。
  到這里我們小結(jié)一下：
  • MyISAM 表雖然 count(*) 很快，但是不支持事務(wù)曾撤；
  • show table status 命令雖然返回很快端姚，但是不準(zhǔn)確；
  • InnoDB 表直接 count(*) 會(huì)遍歷全表盾戴，雖然結(jié)果準(zhǔn)確，但會(huì)導(dǎo)致性能問題兵多。
• 那么尖啡，如果你現(xiàn)在有一個(gè)頁(yè)面經(jīng)常要顯示交易系統(tǒng)的操作記錄總數(shù)，到底應(yīng)該怎么辦呢剩膘？答案是衅斩，我們只能自己計(jì)數(shù)。
• 接下來怠褐，我們討論一下畏梆，看看自己計(jì)數(shù)有哪些方法，以及每種方法的優(yōu)缺點(diǎn)有哪些奈懒。
• 這里奠涌，我先和你說一下這些方法的基本思路：你需要自己找一個(gè)地方，把操作記錄表的行數(shù)存起來磷杏。

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

• 對(duì)于更新很頻繁的庫(kù)來說溜畅，你可能會(huì)第一時(shí)間想到，用緩存系統(tǒng)來支持极祸。
  你可以用一個(gè) Redis 服務(wù)來保存這個(gè)表的總行數(shù)慈格。這個(gè)表每被插入一行 Redis 計(jì)數(shù)就加 1，每被刪除一行 Redis 計(jì)數(shù)就減 1遥金。這種方式下浴捆，讀和更新操作都很快，但你再想一下這種方式存在什么問題嗎稿械？
• 沒錯(cuò)选泻，緩存系統(tǒng)可能會(huì)丟失更新。
• Redis 的數(shù)據(jù)不能永久地留在內(nèi)存里，所以你會(huì)找一個(gè)地方把這個(gè)值定期地持久化存儲(chǔ)起來滔金。但即使這樣色解，仍然可能丟失更新。試想如果剛剛在數(shù)據(jù)表中插入了一行餐茵，Redis 中保存的值也加了 1科阎，然后 Redis 異常重啟了，重啟后你要從存儲(chǔ) redis 數(shù)據(jù)的地方把這個(gè)值讀回來忿族，而剛剛加 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ù)記錄惨撇。
• 我們是這么定義不精確的：
  • 一種是剿吻，查到的 100 行結(jié)果里面有最新插入記錄，而 Redis 的計(jì)數(shù)里還沒加 1串纺；
  • 另一種是丽旅，查到的 100 行結(jié)果里沒有最新插入的記錄，而 Redis 的計(jì)數(shù)里已經(jīng)加了 1纺棺。
• 這兩種情況榄笙，都是邏輯不一致的。我們一起來看看這個(gè)時(shí)序圖祷蝌。

執(zhí)行時(shí)序圖

• 會(huì)話 A 是一個(gè)插入交易記錄的邏輯茅撞，往數(shù)據(jù)表里插入一行 R，然后 Redis 計(jì)數(shù)加 1；會(huì)話 B 就是查詢頁(yè)面顯示時(shí)需要的數(shù)據(jù)米丘。
• 在圖的這個(gè)時(shí)序里剑令，在 T3 時(shí)刻會(huì)話 B 來查詢的時(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é)果匠抗。

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

• 你會(huì)發(fā)現(xiàn)故源，這時(shí)候反過來了污抬，會(huì)話 B 在 T3 時(shí)刻查詢的時(shí)候汞贸，Redis 計(jì)數(shù)加了 1 了，但還查不到新插入的 R 這一行印机，也是數(shù)據(jù)不一致的情況矢腻。
• 在并發(fā)系統(tǒng)里面，我們是無法精確控制不同線程的執(zhí)行時(shí)刻的射赛，因?yàn)榇嬖趫D中的這種操作序列多柑，所以，我們說即使 Redis 正常工作楣责，這個(gè)計(jì)數(shù)值還是邏輯上不精確的竣灌。

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

• 根據(jù)上面的分析，用緩存系統(tǒng)保存計(jì)數(shù)有丟失數(shù)據(jù)和計(jì)數(shù)不精確的問題秆麸。那么初嘹，如果我們把這個(gè)計(jì)數(shù)直接放到數(shù)據(jù)庫(kù)里單獨(dú)的一張計(jì)數(shù)表 C 中，又會(huì)怎么樣呢沮趣？首先屯烦，這解決了崩潰丟失的問題，InnoDB 是支持崩潰恢復(fù)不丟數(shù)據(jù)的。
• 然后驻龟，我們?cè)倏纯茨懿荒芙鉀Q計(jì)數(shù)不精確的問題温眉。
  你會(huì)說，這不一樣嗎翁狐？無非就是把圖中對(duì) Redis 的操作类溢，改成了對(duì)計(jì)數(shù)表 C 的操作。只要出現(xiàn)圖的這種執(zhí)行序列谴蔑，這個(gè)問題還是無解的吧豌骏？
• 這個(gè)問題還真不是無解的。
• 我們這篇文章要解決的問題隐锭，都是由于 InnoDB 要支持事務(wù)窃躲，從而導(dǎo)致 InnoDB 表不能把 count(*) 直接存起來，然后查詢的時(shí)候直接返回形成的∏账現(xiàn)在我們就利用“事務(wù)”這個(gè)特性蒂窒，把問題解決掉。

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

• 我們來看下現(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 用法

• 在 select count(?) from t 這樣的查詢語句里面，count()啦撮、count(主鍵 id)谭网、count(字段) 和 count(1) 等不同用法的性能，有哪些差別赃春。今天談到了 count() 的性能問題愉择，借此機(jī)會(huì)和你詳細(xì)說明一下這幾種用法的性能差別。
• 需要注意的是织中，下面的討論還是基于 InnoDB 引擎的锥涕。
• 這里，首先你要弄清楚 count() 的語義狭吼。count() 是一個(gè)聚合函數(shù)层坠，對(duì)于返回的結(jié)果集，一行行地判斷搏嗡，如果 count 函數(shù)的參數(shù)不是 NULL窿春，累計(jì)值就加 1拉一，否則不加。最后返回累計(jì)值旧乞。
• 所以蔚润，count(*)、count(主鍵 id) 和 count(1) 都表示返回滿足條件的結(jié)果集的總行數(shù)尺栖；而 count(字段）嫡纠，則表示返回滿足條件的數(shù)據(jù)行里面，參數(shù)“字段”不為 NULL 的總個(gè)數(shù)延赌。
• 至于分析性能差別的時(shí)候除盏，你可以記住這么幾個(gè)原則：
  • server 層要什么就給什么；
  • InnoDB 只給必要的值挫以；
  • 現(xiàn)在的優(yōu)化器只優(yōu)化了 count(*) 的語義為“取行數(shù)”者蠕，其他“顯而易見”的優(yōu)化并沒有做。
• 對(duì)于 count(主鍵 id) 來說掐松，InnoDB 引擎會(huì)遍歷整張表踱侣，把每一行的 id 值都取出來，返回給 server 層大磺。server 層拿到 id 后抡句，判斷是不可能為空的，就按行累加杠愧。
• 對(duì)于 count(1) 來說待榔，InnoDB 引擎遍歷整張表，但不取值流济。server 層對(duì)于返回的每一行锐锣，放一個(gè)數(shù)字“1”進(jìn)去，判斷是不可能為空的袭灯，按行累加刺下。
• 單看這兩個(gè)用法的差別的話绑嘹，你能對(duì)比出來稽荧，count(1) 執(zhí)行得要比 count(主鍵 id) 快。因?yàn)閺囊娣祷?id 會(huì)涉及到解析數(shù)據(jù)行工腋，以及拷貝字段值的操作姨丈。
• 對(duì)于 count(字段) 來說：
  • 如果這個(gè)“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個(gè)字段擅腰，判斷不能為 null蟋恬，按行累加；
  • 如果這個(gè)“字段”定義允許為 null趁冈，那么執(zhí)行的時(shí)候歼争，判斷到有可能是 null拜马，還要把值取出來再判斷一下，不是 null 才累加沐绒。
• 也就是前面的第一條原則俩莽，server 層要什么字段，InnoDB 就返回什么字段乔遮。
  但是 count() 是例外扮超，并不會(huì)把全部字段取出來，而是專門做了優(yōu)化蹋肮，不取值出刷。count() 肯定不是 null，按行累加坯辩。
• 看到這里馁龟，你一定會(huì)說，優(yōu)化器就不能自己判斷一下嗎漆魔，主鍵 id 肯定非空啊屁柏，為什么不能按照 count(*) 來處理，多么簡(jiǎn)單的優(yōu)化啊有送。
• 當(dāng)然淌喻，MySQL 專門針對(duì)這個(gè)語句進(jìn)行優(yōu)化，也不是不可以雀摘。但是這種需要專門優(yōu)化的情況太多了裸删，而且 MySQL 已經(jīng)優(yōu)化過 count(*) 了，你直接使用這種用法就可以了阵赠。
• 所以結(jié)論是：按照效率排序的話涯塔，count(字段)<count(主鍵 id)<count(1)≈count()，所以我建議你清蚀，盡量使用 count()匕荸。

小結(jié)

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