前言：

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

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

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

1、實(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ù)。這樣就比較麻煩了。

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

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

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

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

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

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

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

從上圖表中可以看到，在最后一個(gè)時(shí)刻，三個(gè)會(huì)話會(huì)同時(shí)查詢表 test 的總行數(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ù)。

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

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

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

3、小結(jié)

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

那么，回到本文章開頭的問題，如果在一個(gè)頁面經(jīng)常要顯示內(nèi)容系統(tǒng)的操作記錄總數(shù)，這樣的話，我們只能自己計(jì)數(shù)。下面我們來看看有哪些計(jì)數(shù)方法，以及每種方法的優(yōu)缺點(diǎn)有哪些?；舅悸罚鹤约赫乙粋€(gè)地方，把操作記錄表的行數(shù)存起來。

二、計(jì)數(shù)方法

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

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

Redis 的數(shù)據(jù)不能永久地留在內(nèi)存里，所以你會(huì)找一個(gè)地方把這個(gè)值定期地持久化存儲(chǔ)起來。但即使這樣，仍然可能丟失更新。如果剛剛在數(shù)據(jù)表中插入了一行，Redis 中保存的值也加了 1，然后 Redis 異常重啟了，重啟后你要從存儲(chǔ) redis 數(shù)據(jù)的地方把這個(gè)值讀回來，而剛剛加 1 的這個(gè)計(jì)數(shù)操作卻丟失了。

這種情況也可以解決，如果Redis 異常重啟以后，到數(shù)據(jù)庫里面單獨(dú)執(zhí)行一次 count(*) 獲取真實(shí)的行數(shù)，再把這個(gè)值寫回到 Redis 里就可以了。異常重啟畢竟不是經(jīng)常出現(xiàn)的情況，這一次全表掃描的成本，還是可以接受的。但在應(yīng)用中，將計(jì)數(shù)保存在緩存系統(tǒng)中，還不只是丟失更新的問題。這個(gè)值在邏輯上也是不精確的。，

注意是邏輯不準(zhǔn)確。

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

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

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

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

我們一起來看看這個(gè)時(shí)序圖：

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

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

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

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

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

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

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

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

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

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

三、不同的 count 用法

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

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

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)化并沒有做。

1、 count(主鍵 id)

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

2、count(1)

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

只看這上面這兩個(gè)用法，count(1) 執(zhí)行得要比 count(主鍵 id) 快。因?yàn)閺囊娣祷?id 會(huì)涉及到解析數(shù)據(jù)行，以及拷貝字段值的操作。

3、count(字段)

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

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

4、count(*)

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

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

最新評(píng)論