前言：

在開發(fā)工作中，經(jīng)常需要計算一個表的行數(shù)，比如一個內(nèi)容系統(tǒng)審核記錄總數(shù)。這時候我們最先想到是一條 select count(*) from my_table;語句。但是，隨著系統(tǒng)（表）中記錄數(shù)越來越多，這條查詢語句執(zhí)行的也會越來越慢。難道MySQL不會單獨記個總數(shù)，我們每次要查的時候直接讀出來？

那么就通過這一篇來聊聊count(*)語句到底是怎樣實現(xiàn)的，以及 MySQL為什么會這么實現(xiàn)。如果在開發(fā)應(yīng)用中有這樣的需求，業(yè)務(wù)設(shè)計上可以怎么去做。

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

1、實現(xiàn)方式比較

在不同的MySQL引擎中，count(*)有著不同的實現(xiàn)方式。

• MyISAM 引擎把一個表的總行數(shù)存在了磁盤上，因此執(zhí)行 count(*) 的時候會直接返回這個數(shù)，效率很高；
• 而 InnoDB 引擎執(zhí)行 count(*) 的時候，需要把數(shù)據(jù)一行一行地從引擎里面讀出來，然后累積計數(shù)。這樣就比較麻煩了。

【注意】在這篇學(xué)習(xí)文章里，是沒有where過濾條件的count(*)，如果加了where條件，使用MyISAM引擎的表返回結(jié)果也不會變的很快。

2、為什么InnoDB不像MyISAM一樣，也把數(shù)字存起來

因為InnoDB引擎即使是在同一個時刻的多個查詢，由于多版本并發(fā)控制（MVCC）的原因，InnoDB引擎表“應(yīng)該返回多少行”也是不確定的。

在InnoDB引擎四個隔離級別（讀未提交RU；讀已提交RC；可重復(fù)讀RR；可串行化Serializable）中，而MVCC只適用于RC和RR。
MVCC：Multi-Version Concurrency Control，就是在事務(wù)執(zhí)行過程中加入“版本”這個概念、MVCC的實現(xiàn)：通過保存數(shù)據(jù)在某個時間點的快照來實現(xiàn)的。

假設(shè)表 test 中現(xiàn)在有 10000 條記錄，我們設(shè)計了三個用戶并行的會話。

• 會話 A 先啟動事務(wù)并查詢一次表的總行數(shù)；
• 會話 B 啟動事務(wù)，插入一行后記錄后，查詢表的總行數(shù)；
• 會話 C 先啟動一個單獨的語句，插入一行記錄后，查詢表的總行數(shù)。

如果從上到下是按照時間順序執(zhí)行的，同一行語句是在同一時刻執(zhí)行的。下圖表為會話 A、B、C 的執(zhí)行流程。

從上圖表中可以看到，在最后一個時刻，三個會話會同時查詢表 test 的總行數(shù)，但結(jié)果卻不同。

這和 InnoDB 的事務(wù)設(shè)計有關(guān)，可重復(fù)讀是它默認的隔離級別，在代碼上就是通過多版本并發(fā)控制(MVCC)來實現(xiàn)的。每一行記錄都要判斷自己是否對這個會話可見，因此對于count(*) 請求來說，InnoDB 只好把數(shù)據(jù)一行一行地讀出依次判斷，可見的行才能夠用于計算“基于這個查詢”的表的總行數(shù)。

InnoDB 是索引組織表，主鍵索引樹的葉子節(jié)點是數(shù)據(jù)，而普通索引樹的葉子節(jié)點是主鍵值。所以，普通索引樹比主鍵索引樹小很多。對于 count(*) 這樣的操作，遍歷哪個索引樹得到的結(jié)果邏輯上都是一樣的。因此，MySQL 優(yōu)化器會找到最小的那棵樹來遍歷。在保證邏輯正確的前提下，盡量減少掃描的數(shù)據(jù)量，是數(shù)據(jù)庫系統(tǒng)設(shè)計的通用法則之一。

如果使用 show table status 命令，輸出結(jié)果里面也有一個TABLE_ROWS 用于顯示這個表當前有多少行，這個命令執(zhí)行挺快的，那 TABLE_ROWS 能代替 count(*)么？

索引統(tǒng)計的值是通過采樣來估算的。實際上，TABLE_ROWS 就是從這個采樣估算得來的，因此它很不準。官方文檔說誤差可能達到 40% 到 50%。所以，show table status 命令顯示的行數(shù)也不能直接使用。

3、小結(jié)

• MyISAM 引擎表雖然 count(*) 很快，但是不支持事務(wù)；
• show table status 命令雖然返回很快，但是不準確；
• InnoDB 引擎表直接 count(*)會遍歷全表，雖然結(jié)果準確，但會導(dǎo)致性能問題。

那么，回到本文章開頭的問題，如果在一個頁面經(jīng)常要顯示內(nèi)容系統(tǒng)的操作記錄總數(shù)，這樣的話，我們只能自己計數(shù)。下面我們來看看有哪些計數(shù)方法，以及每種方法的優(yōu)缺點有哪些。基本思路：自己找一個地方，把操作記錄表的行數(shù)存起來。

二、計數(shù)方法

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

對于更新很頻繁的庫來說，我們第一時間想到的是用緩存系統(tǒng)來保存。比如用 Redis 服務(wù)來保存這個表的總行數(shù)。這個表插入一行時， Redis 計數(shù)就加 1，刪除一行時， Redis 計數(shù)就減 1。這種方式下，讀和更新操作都很快，但緩存系統(tǒng)可能會丟失更新，導(dǎo)致數(shù)據(jù)錯誤。

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)的情況，這一次全表掃描的成本，還是可以接受的。但在應(yīng)用中，將計數(shù)保存在緩存系統(tǒng)中，還不只是丟失更新的問題。這個值在邏輯上也是不精確的。，

注意是邏輯不準確。

你可以設(shè)想一下有這么一個頁面，要顯示操作記錄的總數(shù)，同時還要顯示最近操作的 50 條記錄。那么，這個頁面的邏輯就需要先到 Redis 里面取出計數(shù)，再到數(shù)據(jù)表里面取數(shù)據(jù)記錄。

我們是這么定義不精確的：

• 一種是，查到的 50 行結(jié)果里面有最新插入記錄，而 Redis 的計數(shù)里還沒加 1；
• 另一種是，查到的 50 行結(jié)果里沒有最新插入的記錄，而 Redis 的計數(shù)里已經(jīng)加了 1。

這兩種情況，都是邏輯不一致的。

我們一起來看看這個時序圖：

上圖中，會話 A 是一個插入記錄的邏輯，往數(shù)據(jù)表里插入一行，然后 Redis 計數(shù)加 1；會話 B 就是查詢頁面顯示時需要的數(shù)據(jù)。在這個時序里，在 T3 時刻會話 B 來查詢的時候，會顯示出新插入的這個記錄，但是 Redis 的計數(shù)還沒加 1。這時候，就會出現(xiàn)我們說的數(shù)據(jù)不一致。

即使把會話 A 的更新順序換一下，再看看執(zhí)行結(jié)果。

即使反過來，會話 B 在 T3 時刻查詢的時候，Redis 計數(shù)加了 1 了，但還查不到新插入的 R 這一行，也是數(shù)據(jù)不一致的情況。

在并發(fā)系統(tǒng)里面，我們是無法精確控制不同線程的執(zhí)行時刻的，因為存在圖中的這種操作序列，所以，我們說即使 Redis 正常工作，這個計數(shù)值還是邏輯上不精確的。

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

用緩存系統(tǒng)保存計數(shù)有丟失數(shù)據(jù)和計數(shù)不精確的問題。那么，如果我們把這個計數(shù)直接放到數(shù)據(jù)庫里單獨的一張計數(shù)表 A 中，會怎么樣呢？

首先，解決了崩潰丟失的問題，InnoDB 是支持崩潰恢復(fù)不丟數(shù)據(jù)的。然后，再看看能不能解決計數(shù)不精確的問題。這也是我們要解決的問題，由于 InnoDB 要支持事務(wù)，從而導(dǎo)致 InnoDB 表不能把 count(*) 直接存起來，然后查詢的時候直接返回形成的。

現(xiàn)在我們就利用“事務(wù)”這個特性，把問題解決掉。

執(zhí)行結(jié)果：雖然會話 B 的讀操作仍然是在 T3 執(zhí)行的，但是因為這時候更新事務(wù)還沒有提交，所以計數(shù)值加 1 這個操作對會話 B 還不可見。

因此，在會話 B 看到的結(jié)果里， 查計數(shù)值和“最近 50 條記錄”看到的結(jié)果，邏輯上就是一致的。

三、不同的 count 用法

在 select count(?) from test 這樣的查詢語句里面，count(*)、count(主鍵 id)、count(字段) 和 count(1) 等不同用法的性能，這幾種用法有啥性能差別呢？

【注意】下面學(xué)習(xí)的也是基于 InnoDB 引擎的。

count() 是一個聚合函數(shù)，對于返回的結(jié)果集，一行行地判斷，如果 count 函數(shù)的參數(shù)不是 NULL，累計值就加 1，否則不加。最后返回累計值。

所以，count(*)、count(主鍵 id)和 count(1)都表示返回滿足條件的結(jié)果集的總行數(shù)；而 count(字段），則表示返回滿足條件的數(shù)據(jù)行里面，參數(shù)“字段”不為 NULL 的總個數(shù)。

在分析性能差別時，我們需要記住這么幾個原則：

• server 層要什么就給什么；
• InnoDB 只給必要的值；
• 現(xiàn)在的優(yōu)化器只優(yōu)化了 count(*) 的語義為“取行數(shù)”，其他“顯而易見”的優(yōu)化并沒有做。

1、 count(主鍵 id)

InnoDB 引擎會遍歷整張表，把每一行的 id 值都取出來，返回給 server 層。server 層拿到 id 后，判斷是不可能為空的，就按行累加。

2、count(1)

InnoDB 引擎遍歷整張表，但不取值。server 層對于返回的每一行，放一個數(shù)字“1”進去，判斷是不可能為空的，按行累加。

只看這上面這兩個用法，count(1) 執(zhí)行得要比 count(主鍵 id) 快。因為從引擎返回 id 會涉及到解析數(shù)據(jù)行，以及拷貝字段值的操作。

3、count(字段)

如果這個“字段”是定義為 not null 的話，一行行地從記錄里面讀出這個字段，判斷不能為 null，按行累加；

如果這個“字段”定義允許為 null，那么執(zhí)行的時候，判斷到有可能是 null，還要把值取出來再判斷一下，不是 null 才累加。

4、count(*)

count(*)是例外，并不會把全部字段取出來，而是專門做了優(yōu)化，不取值。count(*)肯定不是 null，按行累加。按照效率排序的話，count(*) = count(1) > count(主鍵 id) > count(字段)，所以建議盡量使用 count(*)。

到此這篇關(guān)于MySQL中count(*)執(zhí)行慢的解決方案的文章就介紹到這了,更多相關(guān)MySQL執(zhí)行count(*)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論