建站
咨詢
售后
建站咨詢
新聞中心
兄弟們。
成都創(chuàng)新互聯(lián)公司專業(yè)為企業(yè)提供寧鄉(xiāng)網(wǎng)站建設(shè)、寧鄉(xiāng)做網(wǎng)站、寧鄉(xiāng)網(wǎng)站設(shè)計(jì)、寧鄉(xiāng)網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、寧鄉(xiāng)企業(yè)網(wǎng)站模板建站服務(wù)，10年寧鄉(xiāng)做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。
淺淺的炫個(gè)富吧。
說(shuō)出來(lái)你們可能不信。
手機(jī)你們有嗎？我有。
短信，知道吧？一條一毛錢，我天天發(fā)。
你敢想嗎？
所以說(shuō)，年輕人，有錢是真的好。
今天，我們就以短信為話題聊起。
短信，它又叫SMS。
比如說(shuō)，你有一張短信表(sms)，里面放了各種需要發(fā)送的短信信息。
sms建表sql
sms表
需要注意的是state字段，為0的時(shí)候說(shuō)明這時(shí)候短信還未發(fā)送。
此時(shí)還會(huì)有一個(gè)異步線程不斷的撈起未發(fā)送（state=0）的短信數(shù)據(jù)，執(zhí)行發(fā)短信操作，發(fā)送成功之后state字段會(huì)被置為1（已發(fā)送）。也就是說(shuō)未發(fā)送的數(shù)據(jù)會(huì)不斷變少。
異步線程發(fā)送短信
假設(shè)由于某些原因，你現(xiàn)在需要做一些監(jiān)控，比如監(jiān)控的內(nèi)容是，你的sms數(shù)據(jù)表里還有沒(méi)有state=0（未發(fā)送）的短信，方便判斷一下堆積的未發(fā)送短信大概在什么樣的一個(gè)量級(jí)。
為了獲取滿足某些條件的行數(shù)是多少，我們一般會(huì)使用count()方法。
這時(shí)候?yàn)榱双@取未發(fā)送的短信數(shù)據(jù)，我們很自然就想到了使用下面的sql語(yǔ)句進(jìn)行查詢。
select count(*) from sms where state = 0;
然后再把獲得數(shù)據(jù)作為打點(diǎn)發(fā)給監(jiān)控服務(wù)。
當(dāng)數(shù)據(jù)表小的時(shí)候，這是沒(méi)問(wèn)題的，但當(dāng)數(shù)據(jù)量大的時(shí)候，比如未發(fā)送的短信到了百萬(wàn)量級(jí)的時(shí)候，你就會(huì)發(fā)現(xiàn)，上面的sql查詢時(shí)間會(huì)變得很長(zhǎng)，最后timeout報(bào)錯(cuò)，查不出結(jié)果了。
為什么？
我們先從count()方法的原理聊起。
count()的原理
count()方法的目的是計(jì)算當(dāng)前sql語(yǔ)句查詢得到的非NULL的行數(shù)。
我們知道m(xù)ysql是分為server層和存儲(chǔ)引擎層的。
Mysql架構(gòu)
存儲(chǔ)引擎層里可以選擇各種引擎進(jìn)行存儲(chǔ)，最常見的是innodb、myisam。具體使用哪個(gè)存儲(chǔ)引擎，可以通過(guò)建表sql里的ENGINE?字段進(jìn)行指定。比如這篇文章開頭的建表sql里用了ENGINE=InnoDB，那這張表用的就是innodb引擎。
雖然在server層都叫count()方法，但在不同的存儲(chǔ)引擎下，它們的實(shí)現(xiàn)方式是有區(qū)別的。
比如同樣是讀全表數(shù)據(jù) select count(*) from sms；語(yǔ)句。
使用 myisam引擎的數(shù)據(jù)表里有個(gè)記錄當(dāng)前表里有幾行數(shù)據(jù)的字段，直接讀這個(gè)字段返回就好了，因此速度快得飛起。
而使用innodb引擎的數(shù)據(jù)表，則會(huì)選擇體積最小的索引樹，然后通過(guò)遍歷葉子節(jié)點(diǎn)的個(gè)數(shù)挨個(gè)加起來(lái)，這樣也能得到全表數(shù)據(jù)。
因此回到文章開頭的問(wèn)題里，當(dāng)數(shù)據(jù)表行數(shù)變大后，單次count就需要掃描大量的數(shù)據(jù)，因此很可能就會(huì)出現(xiàn)超時(shí)報(bào)錯(cuò)。
那么問(wèn)題就來(lái)了。
為什么innodb不能像myisam那樣實(shí)現(xiàn)count()方法
myisam和innodb這兩個(gè)引擎，有幾個(gè)比較明顯的區(qū)別，這個(gè)是八股文?？剂恕?/p>
其中最大的區(qū)別在于myisam不支持事務(wù)，而innodb支持事務(wù)。
而事務(wù)，有四層隔離級(jí)別，其中默認(rèn)隔離級(jí)別就是可重復(fù)讀隔離級(jí)別（RR）。
四層隔離級(jí)別
innodb引擎通過(guò)MVCC實(shí)現(xiàn)了可重復(fù)隔離級(jí)別，事務(wù)開啟后，多次執(zhí)行同樣的select快照讀，要能讀到同樣的數(shù)據(jù)。
于是我們看個(gè)例子。
為什么innodb不單獨(dú)記錄表行數(shù)
對(duì)于兩個(gè)事務(wù)A和B，一開始sms表假設(shè)就2條數(shù)據(jù)，那事務(wù)A一開始確實(shí)是讀到2條數(shù)據(jù)。事務(wù)B在這期間插入了1條數(shù)據(jù)，按道理數(shù)據(jù)庫(kù)其實(shí)有3條數(shù)據(jù)了，但由于可重復(fù)讀的隔離級(jí)別，事務(wù)A依然還是只能讀到2條數(shù)據(jù)。
因此由于事務(wù)隔離級(jí)別的存在，不同的事務(wù)在同一時(shí)間下，看到的表內(nèi)數(shù)據(jù)行數(shù)是不一致的，因此innodb，沒(méi)辦法，也沒(méi)必要像myisam那樣單純的加個(gè)count字段信息在數(shù)據(jù)表上。
那如果不可避免要使用count()，有沒(méi)有辦法讓它快一點(diǎn)？
各種count()方法的原理
count()的括號(hào)里，可以放各種奇奇怪怪的東西，想必大家應(yīng)該看過(guò)，比如放個(gè)星號(hào)*，放個(gè)1，放個(gè)索引列啥的。
我們來(lái)分析下他們的執(zhí)行流程。
count方法的大原則是server層會(huì)從innodb存儲(chǔ)引擎里讀來(lái)一行行數(shù)據(jù)，并且只累計(jì)非null的值。但這個(gè)過(guò)程，根據(jù)count()方法括號(hào)內(nèi)的傳參，有略有不同。
count(*)
server層拿到innodb返回的行數(shù)據(jù)，不對(duì)里面的行數(shù)據(jù)做任何解析和判斷，默認(rèn)取出的值肯定都不是null，直接行數(shù)+1。
count(1)
server層拿到innodb返回的行數(shù)據(jù)，每行放個(gè)1進(jìn)去，默認(rèn)不可能為null，直接行數(shù)+1.
count(某個(gè)列字段)
由于指明了要count某個(gè)字段，innodb在取數(shù)據(jù)的時(shí)候，會(huì)把這個(gè)字段解析出來(lái)返回給server層，所以會(huì)比count(1)和count(*)多了個(gè)解析字段出來(lái)的流程。
如果這個(gè)列字段是主鍵id，主鍵是不可能為null的，所以server層也不用判斷是否為null，innodb每返回一行，行數(shù)結(jié)果就+1.
如果這個(gè)列是普通索引字段，innodb一般會(huì)走普通索引，每返回一行數(shù)據(jù)，server層就會(huì)判斷這個(gè)字段是否為null，不是null的情況下+1。當(dāng)然如果建表sql里字段定義為not null的話，那就不用做這一步判斷直接+1。
如果這個(gè)列沒(méi)有加過(guò)索引，那innodb可能會(huì)全表掃描，返回的每一行數(shù)據(jù)，server層都會(huì)判斷這個(gè)字段是否為null，不是null的情況下+1。同上面的情況一樣，字段加了not null也就省下這一步判斷了。
理解了原理后我們大概可以知道他們的性能排序是
count(*) ≈ count(1) > count(主鍵id) > count(普通索引列) > count(未加索引列)
所以說(shuō)count(*)，已經(jīng)是最快的了。
知道真相的我眼淚掉下來(lái)。?
那有沒(méi)有其他更好的辦法？
允許粗略估計(jì)行數(shù)的場(chǎng)景
我們回過(guò)頭來(lái)細(xì)品下文章開頭的需求，我們只是希望知道數(shù)據(jù)庫(kù)里還有多少短信是堆積在那沒(méi)發(fā)的，具體是1k還是2k其實(shí)都是差不多量級(jí)，等到了百萬(wàn)以上，具體數(shù)值已經(jīng)不重要了，我們知道它現(xiàn)在堆積得很離譜，就夠了。因此這個(gè)場(chǎng)景，其實(shí)是允許使用比較粗略的估計(jì)的。
那怎么樣才能獲得粗略的數(shù)值呢？
還記得我們平時(shí)為了查看sql執(zhí)行計(jì)劃用的explain命令不。
其中有個(gè)rows，會(huì)用來(lái)估計(jì)接下來(lái)執(zhí)行這條sql需要掃描和檢查多少行。它是通過(guò)采樣的方式計(jì)算出來(lái)的，雖然會(huì)有一定的偏差，但它能反映一定的數(shù)量級(jí)。
explain里的rows
有些語(yǔ)言的orm里可能沒(méi)有專門的explain語(yǔ)法，但是肯定有執(zhí)行raw sql的功能，你可以把explain語(yǔ)句當(dāng)做raw sql傳入，從返回的結(jié)果里將rows那一列讀出來(lái)使用。
一般情況下，explain的sql如果能走索引，那會(huì)比不走索引的情況更準(zhǔn) 。單個(gè)字段的索引會(huì)比多個(gè)字段組成的復(fù)合索引要準(zhǔn)。索引區(qū)分度越高，rows的值也會(huì)越準(zhǔn)。
這種情況幾乎滿足大部分的監(jiān)控場(chǎng)景。但總有一些場(chǎng)景，它要求必須得到精確的行數(shù)，這種情況該怎么辦呢？
必須精確估計(jì)行數(shù)的場(chǎng)景
這種場(chǎng)景就比較頭疼了，但也不是不能做。
我們可以單獨(dú)拉一張新的數(shù)據(jù)庫(kù)表，只為保存各種場(chǎng)景下的count。
CREATE TABLE `count_table` (
`id` int NOT NULL AUTO_INCREMENT COMMENT '主鍵',
`cnt_what` char(20) NOT NULL DEFAULT '' COMMENT '各種需要計(jì)算的指標(biāo)',
`cnt` tinyint NOT NULL COMMENT 'cnt指標(biāo)值',
PRIMARY KEY (`id`),
KEY `idx_cnt_what` (`cnt_what`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
count_table表保存各種場(chǎng)景下的count
當(dāng)需要獲取某個(gè)場(chǎng)景下的cout值時(shí)，可以使用下面的sql進(jìn)行直接讀取，快得飛起。
select cnt from count_table where cnt_what = "未發(fā)送的短信數(shù)量";
那這些count的結(jié)果值從哪來(lái)呢？
這里分成兩種情況。
實(shí)時(shí)性要求較高的場(chǎng)景
如果你對(duì)這個(gè)cnt計(jì)算結(jié)果的實(shí)時(shí)性要求很高，那你需要將更新cnt的sql加入到對(duì)應(yīng)變更行數(shù)的事務(wù)中。
比如我們有兩個(gè)事務(wù)A和B，分別是增加未發(fā)送短信和減少未發(fā)送短信。
將更改表行數(shù)的操作放入到事務(wù)里
這樣做的好處是事務(wù)內(nèi)的cnt行數(shù)依然符合隔離級(jí)別，事務(wù)回滾的時(shí)候，cnt的值也會(huì)跟著回滾。
壞處也比較明顯，多個(gè)線程對(duì)同一個(gè)cnt進(jìn)行寫操作，會(huì)觸發(fā)悲觀鎖，多個(gè)線程之間需要互相等待。對(duì)于高頻寫的場(chǎng)景，性能會(huì)有折損。
實(shí)時(shí)性沒(méi)那么高的場(chǎng)景
如果實(shí)時(shí)性要求不高的話，比如可以一天一次，那你可以通過(guò)全表掃描后做計(jì)算。
舉個(gè)例子，比如上面的短信表，可以按id排序，每次取出1w條數(shù)據(jù)，記下這一批里最大的id，然后下次從最大id開始再拿1w條數(shù)據(jù)出來(lái)，不斷循環(huán)。
對(duì)于未發(fā)送的短信，就只需要在撈出的那1w條數(shù)據(jù)里，篩選出state=0的條數(shù)。
batch分批獲取短信表
當(dāng)然如果有條件，這種場(chǎng)景最好的方式還是消費(fèi)binlog將數(shù)據(jù)導(dǎo)入到hive里，然后在hive里做查詢，不少公司也已經(jīng)有現(xiàn)成的組件可以做這種事情，不用自己寫腳本，豈不美哉。
mysql同步hive
總結(jié)
mysql用count方法查全表數(shù)據(jù)，在不同的存儲(chǔ)引擎里實(shí)現(xiàn)不同，myisam有專門字段記錄全表的行數(shù)，直接讀這個(gè)字段就好了。而innodb則需要一行行去算。
性能方面 count(*) ≈ count(1) > count(主鍵id) > count(普通索引列) > count(未加索引列)，但哪怕是性能最好的count(*)，由于實(shí)現(xiàn)上就需要一行行去算，所以數(shù)據(jù)量大的時(shí)候就是不給力。
如果確實(shí)需要獲取行數(shù)，且可以接受不那么精確的行數(shù)（只需要判斷大概的量級(jí)）的話，那可以用explain里的rows，這可以滿足大部分的監(jiān)控場(chǎng)景，實(shí)現(xiàn)簡(jiǎn)單。
如果要求行數(shù)準(zhǔn)確，可以建個(gè)新表，里面專門放表行數(shù)的信息。
如果對(duì)實(shí)時(shí)性要求比較高的話，可以將更新行數(shù)的sql放入到對(duì)應(yīng)事務(wù)里，這樣既能滿足事務(wù)隔離性，還能快速讀取到行數(shù)信息。
如果對(duì)實(shí)時(shí)性要求不高，接受一小時(shí)或者一天的更新頻率，那既可以自己寫腳本遍歷全表后更新行數(shù)信息。也可以將通過(guò)監(jiān)聽binlog將數(shù)據(jù)導(dǎo)入hive，需要數(shù)據(jù)時(shí)直接通過(guò)hive計(jì)算得出。
參考資料
《丁奇mysql45講》
當(dāng)前標(biāo)題：為什么MySQL的count()方法這么慢？
轉(zhuǎn)載注明：http://www.dlmjj.cn/article/cdhsgpj.html
