在我們平時查詢數(shù)據(jù)庫表記錄行數(shù)的時候，經(jīng)常會使用到count()函數(shù)，比如使用count(*)、count(1)或者count(某個主鍵或索引列)，今天我們來對比下這些用法中哪個性能最優(yōu)秀！

創(chuàng)建短信表

比如說，你有一張短信表(sms) ，里面放了各種需要發(fā)送的短信信息。

需要注意的是state字段，為0的時候說明這時候短信還未發(fā)送。

此時還會有一個異步線程 不斷的撈起未發(fā)送（state=0）** 的短信數(shù)據(jù)，執(zhí)行發(fā)短信操作，發(fā)送成功之后state字段會被*置為1（已發(fā)送） 。也就是說未發(fā)送的數(shù)據(jù)會不斷變少* 。

假設(shè)由于某些原因，你現(xiàn)在需要做一些監(jiān)控，比如監(jiān)控的內(nèi)容是，你的sms數(shù)據(jù)表里還有沒有state=0（未發(fā)送）的短信，方便判斷一下堆積的未發(fā)送短信大概在什么樣的一個量級。

為了獲取滿足某些條件的行數(shù)是多少 ，我們一般會使用count()方法 。

這時候為了獲取未發(fā)送的短信數(shù)據(jù)，我們很自然就想到了使用下面的sql語句進(jìn)行查詢。

selectcount(*)fromsmswherestate=0;

然后再把獲得數(shù)據(jù)作為打點發(fā)給監(jiān)控服務(wù)。

當(dāng)數(shù)據(jù)表小的時候，這是沒問題的，但當(dāng)數(shù)據(jù)量大的時候，比如未發(fā)送的短信到了百萬量級 的時候，你就會發(fā)現(xiàn)，上面的sql查詢時間會變得很長，最后timeout報錯，查不出結(jié)果了 。

為什么？

我們先從count()方法的原理 聊起。

基于 Spring Boot + MyBatis Plus + Vue & Element 實現(xiàn)的后臺管理系統(tǒng) + 用戶小程序，支持 RBAC 動態(tài)權(quán)限、多租戶、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

• 項目地址：https://github.com/YunaiV/ruoyi-vue-pro
• 視頻教程：https://doc.iocoder.cn/video/

count（）的原理

count()方法的目的是計算當(dāng)前sql語句查詢得到的非NULL的行數(shù) 。

我們知道m(xù)ysql是分為server層和存儲引擎層的 。

存儲引擎層里可以選擇各種引擎進(jìn)行存儲，最常見的是innodb、myisam。具體使用哪個存儲引擎，可以通過建表sql里的ENGINE字段進(jìn)行指定。比如這篇文章開頭的建表sql里用了ENGINE=InnoDB，那這張表用的就是innodb引擎。

雖然在server層都叫count()方法，但在不同的存儲引擎下，它們的實現(xiàn)方式是有區(qū)別的。

比如同樣是讀全表數(shù)據(jù) select count(*) from sms；語句。

使用 myisam引擎 的數(shù)據(jù)表里有個記錄當(dāng)前表里有幾行數(shù)據(jù)的字段，直接讀這個字段返回就好了，因此速度快得飛起。

而使用innodb引擎 的數(shù)據(jù)表，則會選擇體積最小的索引樹 ，然后通過遍歷葉子節(jié)點的個數(shù)挨個加起來，這樣也能得到全表數(shù)據(jù)。

因此回到文章開頭的問題里，當(dāng)數(shù)據(jù)表行數(shù)變大后，單次count就需要掃描大量的數(shù)據(jù) ，因此很可能就會出現(xiàn)超時報錯。

那么問題就來了。

為什么innodb不能像myisam那樣實現(xiàn)count()方法

myisam和innodb這兩個引擎，有幾個比較明顯的區(qū)別，這個是八股文?？剂?。

其中最大的區(qū)別在于myisam不支持事務(wù)，而innodb支持事務(wù)。

而事務(wù)，有四層隔離級別，其中默認(rèn)隔離級別就是可重復(fù)讀隔離級別（RR） 。

innodb引擎通過MVCC實現(xiàn)了可重復(fù)隔離級別 ，事務(wù)開啟后，多次執(zhí)行同樣的select快照讀 ，要能讀到同樣的數(shù)據(jù)。

于是我們看個例子。

對于兩個事務(wù)A和B，一開始sms表假設(shè)就2條 數(shù)據(jù)，那事務(wù)A一開始確實是讀到2條數(shù)據(jù)。事務(wù)B在這期間插入了1條數(shù)據(jù)，按道理數(shù)據(jù)庫其實有3條數(shù)據(jù)了，但由于可重復(fù)讀的隔離級別，事務(wù)A依然還是只能讀到2條數(shù)據(jù)。

因此由于事務(wù)隔離級別的存在，不同的事務(wù)在同一時間下，看到的表內(nèi)數(shù)據(jù)行數(shù)是不一致的 ，因此innodb，沒辦法，也沒必要像myisam那樣單純的加個count字段信息在數(shù)據(jù)表上。

那如果不可避免要使用count()，有沒有辦法讓它快一點？

基于 Spring Cloud Alibaba + Gateway + Nacos + RocketMQ + Vue & Element 實現(xiàn)的后臺管理系統(tǒng) + 用戶小程序，支持 RBAC 動態(tài)權(quán)限、多租戶、數(shù)據(jù)權(quán)限、工作流、三方登錄、支付、短信、商城等功能

• 項目地址：https://github.com/YunaiV/yudao-cloud
• 視頻教程：https://doc.iocoder.cn/video/

各種count（）方法的原理

count()的括號里，可以放各種奇奇怪怪的東西，想必大家應(yīng)該看過，比如放個星號*，放個1，放個索引列啥的。

我們來分析下他們的執(zhí)行流程。

count方法的大原則是server層會從innodb存儲引擎里讀來一行行數(shù)據(jù)，并且只累計非null的值 。但這個過程，根據(jù)count()方法括號內(nèi)的傳參，有略有不同。

count（*）

server層拿到innodb返回的行數(shù)據(jù)，不對里面的行數(shù)據(jù)做任何解析和判斷 ，默認(rèn)取出的值肯定都不是null，直接行數(shù)+1。

count（1）

server層拿到innodb返回的行數(shù)據(jù)，每行放個1進(jìn)去，默認(rèn)不可能為null，直接行數(shù)+1.

count（某個列字段）

由于指明了要count某個字段，innodb在取數(shù)據(jù)的時候，會把這個字段解析出來 返回給server層，所以會比count(1)和count(*)多了個解析字段出來的流程。

• 如果這個列字段是主鍵id ，主鍵是不可能為null的，所以server層也不用判斷是否為null，innodb每返回一行，行數(shù)結(jié)果就+1.
• 如果這個列是普通索引字段 ，innodb一般會走普通索引 ，每返回一行數(shù)據(jù)，server層就會判斷這個字段是否為null，不是null的情況下+1。當(dāng)然如果建表sql里字段定義為not null的話，那就不用做這一步判斷直接+1。
• 如果這個列沒有加過索引 ，那innodb可能會全表掃描，返回的每一行數(shù)據(jù)，server層都會判斷這個字段是否為null，不是null的情況下+1。同上面的情況一樣，字段加了not null也就省下這一步判斷了。

理解了原理后我們大概可以知道他們的性能排序是

count(*)≈count(1)>count(主鍵id)>count(普通索引列)>count(未加索引列)

所以說count(*)，已經(jīng)是最快的了。

知道真相的我眼淚掉下來。

那有沒有其他更好的辦法？

允許粗略估計行數(shù)的場景

我們回過頭來細(xì)品下文章開頭的需求，我們只是希望知道數(shù)據(jù)庫里還有多少短信是堆積在那沒發(fā)的，具體是1k還是2k其實都是差不多量級，等到了百萬以上，具體數(shù)值已經(jīng)不重要了，我們知道它現(xiàn)在堆積得很離譜，就夠了。因此這個場景，其實是允許使用比較粗略 的估計的。

那怎么樣才能獲得粗略的數(shù)值呢？

還記得我們平時為了查看sql執(zhí)行計劃用的explain命令 不。

其中有個rows ，會用來估計 接下來執(zhí)行這條sql需要掃描和檢查多少行。它是通過采樣的方式計算出來的，雖然會有一定的偏差，但它能反映一定的數(shù)量級。

有些語言的orm里可能沒有專門的explain語法，但是肯定有執(zhí)行raw sql的功能，你可以把explain語句當(dāng)做raw sql傳入，從返回的結(jié)果里將rows那一列讀出來使用。

一般情況下，explain的sql如果能走索引，那會比不走索引的情況更準(zhǔn) 。單個字段的索引會比多個字段組成的復(fù)合索引要準(zhǔn)。索引區(qū)分度越高，rows的值也會越準(zhǔn)。

這種情況幾乎滿足大部分的監(jiān)控場景。但總有一些場景，它要求必須得到精確的行數(shù)，這種情況該怎么辦呢？

必須精確估計行數(shù)的場景

這種場景就比較頭疼了，但也不是不能做。

我們可以單獨拉一張新的數(shù)據(jù)庫表，只為保存各種場景下的count。

CREATETABLE`count_table`(
`id`intNOTNULLAUTO_INCREMENTCOMMENT'主鍵',
`cnt_what`char(20)NOTNULLDEFAULT''COMMENT'各種需要計算的指標(biāo)',
`cnt`tinyintNOTNULLCOMMENT'cnt指標(biāo)值',
PRIMARYKEY(`id`),
KEY`idx_cnt_what`(`cnt_what`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

當(dāng)需要獲取某個場景下的cout值時，可以使用下面的sql進(jìn)行直接讀取，快得飛起 。

selectcntfromcount_tablewherecnt_what="未發(fā)送的短信數(shù)量";

那這些count的結(jié)果值從哪來呢？

這里分成兩種情況。

實時性要求較高的場景

如果你對這個cnt計算結(jié)果的實時性要求很高，那你需要將更新cnt的sql加入到對應(yīng)變更行數(shù)的事務(wù)中 。

比如我們有兩個事務(wù)A和B，分別是增加未發(fā)送短信和減少未發(fā)送短信。

這樣做的好處 是事務(wù)內(nèi)的cnt行數(shù)依然符合隔離級別，事務(wù)回滾的時候，cnt的值也會跟著回滾。

壞處 也比較明顯，多個線程對同一個cnt進(jìn)行寫操作，會觸發(fā)悲觀鎖，多個線程之間需要互相等待。對于高頻寫的場景 ，性能會有折損。

實時性沒那么高的場景

如果實時性要求不高的話，比如可以一天一次，那你可以通過全表掃描后做計算。

舉個例子，比如上面的短信表，可以按id排序 ，每次取出1w條數(shù)據(jù)，記下這一批里最大的id，然后下次從最大id開始再拿1w條數(shù)據(jù)出來，不斷循環(huán)。

對于未發(fā)送的短信，就只需要在撈出的那1w條數(shù)據(jù)里，篩選出state=0的條數(shù)。

當(dāng)然如果有條件，這種場景最好的方式還是消費binlog將數(shù)據(jù)導(dǎo)入到hive里 ，然后在hive里做查詢，不少公司也已經(jīng)有現(xiàn)成的組件可以做這種事情，不用自己寫腳本，豈不美哉。

總結(jié)

• mysql用count方法查全表數(shù)據(jù) ，在不同的存儲引擎里實現(xiàn)不同，myisam有專門字段記錄全表的行數(shù)，直接讀這個字段就好了。而innodb則需要一行行去算。
• 性能方面 count(*) ≈ count(1) > count(主鍵id) > count(普通索引列) > count(未加索引列)，但哪怕是性能最好的count(*)，由于實現(xiàn)上就需要一行行去算，所以數(shù)據(jù)量大的時候就是不給力。
• 如果確實需要獲取行數(shù)，且可以接受不那么精確的行數(shù)（只需要判斷大概的量級） 的話，那可以用explain里的rows，這可以滿足大部分的監(jiān)控場景，實現(xiàn)簡單。
• 如果要求行數(shù)準(zhǔn)確 ，可以建個新表，里面專門放表行數(shù)的信息。
• 如果對實時性要求比較高 的話，可以將更新行數(shù)的sql放入到對應(yīng)事務(wù)里，這樣既能滿足事務(wù)隔離性，還能快速讀取到行數(shù)信息。
• 如果對實時性要求不高 ，接受一小時或者一天的更新頻率，那既可以自己寫腳本遍歷全表后更新行數(shù)信息。也可以將通過監(jiān)聽binlog將數(shù)據(jù)導(dǎo)入hive，需要數(shù)據(jù)時直接通過hive計算得出。

審核編輯 ：李倩