CSDN知識庫 - 為什麼mysql的count()方法這麼慢？－鑽石舞台

Jul 25 Mon 2022 16:32
CSDN知識庫 - 為什麼mysql的count()方法這麼慢？

轉自|小白debug

作者|小白

短信，它又叫SMS。

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

sms建表sql

sms表

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

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

異步線程發送短信

假設由於某些原因，你現在需要做一些監控，比如監控的內容是，你的sms數據表里還有沒有state=0（未發送）的短信，方便判斷一下堆積的未發送短信大概在什麼樣的一個量級。

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

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

selectcount(*)fromsmswherestate=0;

然後再把獲得數據作為打點發給監控服務。

當數據表小的時候，這是沒問題的，但當數據量大的時候，比如未發送的短信到了百萬量級的時候，你就會發現，上面的sql查詢時間會變得很長，最後timeout報錯，查不出結果了。

為什麼？

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

01count（）的原理

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

我們知道mysql是分為server層和存儲引擎層的。

Mysql架構

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

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

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

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

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

因此回到文章開頭的問題里，當數據表行數變大後，單次count就需要掃描大量的數據，因此很可能就會出現超時報錯。

那麼問題就來了。

為什麼innodb不能像myisam那樣實現count()方法

myisam和innodb這兩個引擎，有幾個比較明顯的區別，這個是八股文常考了。

其中最大的區別在於myisam不支持事務，而innodb支持事務。

而事務，有四層隔離級別，其中默認隔離級別就是可重複讀隔離級別（RR）。

四層隔離級別

innodb引擎通過MVCC實現了可重複隔離級別，事務開啟後，多次執行同樣的select快照讀，要能讀到同樣的數據。

於是我們看個例子。

為什麼innodb不單獨記錄表行數

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

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

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

02各種count（）方法的原理

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

我們來分析下他們的執行流程。

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

count(*)

server層拿到innodb返回的行數據，不對裡面的行數據做任何解析和判斷，默認取出的值肯定都不是null，直接行數+1。

count(1)

server層拿到innodb返回的行數據，每行放個1進去，默認不可能為null，直接行數+1.

count(某個列字段)

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

如果這個列字段是主鍵id，主鍵是不可能為null的，所以server層也不用判斷是否為null，innodb每返回一行，行數結果就+1.

如果這個列是普通索引字段，innodb一般會走普通索引，每返回一行數據，server層就會判斷這個字段是否為null，不是null的情況下+1。當然如果建表sql里字段定義為not null的話，那就不用做這一步判斷直接+1。

如果這個列沒有加過索引，那innodb可能會全表掃描，返回的每一行數據，server層都會判斷這個字段是否為null，不是null的情況下+1。同上面的情況一樣，字段加了not null也就省下這一步判斷了。

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

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

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

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

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

03允許粗略估計行數的場景

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

那怎麼樣才能獲得粗略的數值呢？

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

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

explain里的rows

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

一般情況下，explain的sql如果能走索引，那會比不走索引的情況更准。單個字段的索引會比多個字段組成的複合索引要准。索引區分度越高，rows的值也會越准。

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

04必須精確估計行數的場景

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

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

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