我做了八年SQL開發(fā)和數(shù)據(jù)庫(kù)優(yōu)化帶過不少新人發(fā)現(xiàn)幾乎每個(gè)來面試的候選人都會(huì)背“count(1)比count()快count(列名)不統(tǒng)計(jì)NULL”但一問到“為什么”“什么場(chǎng)景下用哪個(gè)”大多數(shù)人就卡殼了。尤其最近在排查慢SQL的時(shí)候發(fā)現(xiàn)很多線上事故的根源就是count函數(shù)用錯(cuò)了。今天不聊虛的直接把count(1)、count()和count(列名)這三兄弟扒干凈講清楚它們底層怎么執(zhí)行、性能差在哪、實(shí)際項(xiàng)目里怎么選順便把我踩過的坑也一并晾出來。1. 三條count語(yǔ)句的本質(zhì)區(qū)別很多教程喜歡直接甩結(jié)論“count(*)最快、count(1)次之、count(列名)最慢”這話說得太粗暴了容易把人帶溝里。要想真正記住區(qū)別得先回到count函數(shù)本身的設(shè)計(jì)邏輯上來。1.1 count(*) 到底統(tǒng)計(jì)了什么count()的語(yǔ)義是“統(tǒng)計(jì)結(jié)果集中所有行的數(shù)量”它完全不關(guān)心任何一列的數(shù)據(jù)內(nèi)容也不管這一行里的字段是不是NULL。數(shù)據(jù)庫(kù)在執(zhí)行count()時(shí)會(huì)直接讀取索引或者表中記錄的個(gè)數(shù)逐行累加最后返回總數(shù)。這里有個(gè)非常關(guān)鍵的點(diǎn)count()不會(huì)把行的內(nèi)容取出來做判斷它純粹是在數(shù)“行數(shù)”。哪怕這一行的所有字段都是NULL只要這一行存在count()就會(huì)算進(jìn)去。所以在InnoDB引擎下count(*)是統(tǒng)計(jì)所有物理存在的記錄行數(shù)這個(gè)數(shù)字是最接近“這張表到底有多少條數(shù)據(jù)”的答案。1.2 count(1) 和 count(*) 的深度對(duì)比先說結(jié)論count(1)在語(yǔ)義上等于count()統(tǒng)計(jì)的行數(shù)范圍和結(jié)果完全一樣都是包含NULL行的總行數(shù)。很多老程序員喜歡用count(1)理由是“1是個(gè)常量比要快”這個(gè)說法在早期的Oracle和SQL Server里有一定道理但在現(xiàn)代版本的MySQL、PostgreSQL、SQL Server中這個(gè)性能差異已經(jīng)微乎其微幾乎可以忽略不計(jì)。我自己實(shí)測(cè)過一張500萬行的訂單表在MySQL 8.0 InnoDB下分別執(zhí)行count(1)和count()執(zhí)行計(jì)劃完全一致走的索引掃描路徑也一樣耗時(shí)差距在毫秒級(jí)別沒有任何實(shí)際意義。所以現(xiàn)在的開發(fā)規(guī)范里我更傾向于統(tǒng)一用count()——因?yàn)樗恼Z(yǔ)義最清晰閱讀代碼的人一眼就能明白“這是在統(tǒng)計(jì)總行數(shù)”而不是“統(tǒng)計(jì)常數(shù)1的個(gè)數(shù)”。另外有個(gè)細(xì)節(jié)count(1)里的“1”并不是什么魔法數(shù)字它只是代表“一個(gè)非NULL的常量表達(dá)式”。數(shù)據(jù)庫(kù)在處理時(shí)會(huì)為每一行生成一個(gè)常量值1并計(jì)數(shù)本質(zhì)上還是在數(shù)行。它不會(huì)去讀取任何列的數(shù)據(jù)這一點(diǎn)和count(列名)有本質(zhì)區(qū)別。1.3 count(列名) 的特殊之處count(列名)的語(yǔ)義就和前兩個(gè)完全不同了它統(tǒng)計(jì)的是“該列中非NULL值的個(gè)數(shù)”。比如執(zhí)行count(user_name)數(shù)據(jù)庫(kù)會(huì)逐行讀取user_name這一列如果發(fā)現(xiàn)值為NULL就跳過不計(jì)數(shù)只有當(dāng)該列有值時(shí)計(jì)數(shù)才加1。這就意味著count(列名)的結(jié)果可能小于表的總行數(shù)。如果某張表的某個(gè)字段允許NULL并且實(shí)際數(shù)據(jù)里確實(shí)存在NULL那么count(列名)和count(*)的數(shù)量一定不一樣。很多新人在這里栽跟頭拿count(列名)當(dāng)天數(shù)用結(jié)果業(yè)務(wù)報(bào)表對(duì)不上。再看一個(gè)極端例子如果某一列全為NULLcount(該列)返回的就是0而count(*)返回的是表的總行數(shù)。這種差異在數(shù)據(jù)清洗、空值排查時(shí)特別有用但如果你沒意識(shí)到這個(gè)區(qū)別很容易得出完全錯(cuò)誤的數(shù)據(jù)結(jié)論。2. 存儲(chǔ)引擎和索引如何影響count的性能上面說的是語(yǔ)義區(qū)別下面聊性能。很多同學(xué)把count的性能問題歸結(jié)于“用count(*)還是count(1)”這其實(shí)找錯(cuò)了方向。真正決定count快慢的是數(shù)據(jù)庫(kù)的存儲(chǔ)引擎和索引利用情況。2.1 MyISAM與InnoDB的count天壤之別老版本的MySQL里MyISAM引擎對(duì)count()的優(yōu)化非常激進(jìn)它會(huì)在表的元數(shù)據(jù)里直接保存當(dāng)前表的行數(shù)執(zhí)行count()時(shí)根本不需要掃描數(shù)據(jù)直接把這個(gè)緩存值取出來返回。所以在MyISAM表上哪怕有幾千萬行count(*)也是毫秒級(jí)返回。而InnoDB不支持這種“行數(shù)緩存”機(jī)制原因很復(fù)雜簡(jiǎn)單說就是InnoDB為了實(shí)現(xiàn)事務(wù)隔離和MVCC多版本并發(fā)控制同一個(gè)表在不同事務(wù)里看到的行數(shù)可能是不同的所以沒法維護(hù)一個(gè)全局統(tǒng)一的計(jì)數(shù)器。這也是為什么InnoDB下count(*)必須實(shí)時(shí)掃描數(shù)據(jù)或索引來統(tǒng)計(jì)。這也是一個(gè)經(jīng)典面試陷阱問你“為什么MyISAM的count(*)快InnoDB卻慢”答不上來的話會(huì)顯得對(duì)存儲(chǔ)引擎理解不深。關(guān)鍵點(diǎn)就在MVCC這個(gè)我下面細(xì)說。2.2 為什么InnoDB不能直接緩存行數(shù)MVCC的全稱是Multi-Version Concurrency Control即多版本并發(fā)控制。InnoDB在執(zhí)行事務(wù)時(shí)不同事務(wù)的隔離級(jí)別下看到的快照數(shù)據(jù)可能不一樣。比如事務(wù)A開啟后事務(wù)B插入了一條新記錄并提交事務(wù)A再去執(zhí)行count(*)時(shí)如果按可重復(fù)讀隔離級(jí)別A不應(yīng)該看到B插入的這條數(shù)據(jù)。如果InnoDB像MyISAM那樣在元數(shù)據(jù)里緩存一個(gè)總行數(shù)那么這個(gè)行數(shù)應(yīng)該以哪個(gè)事務(wù)的快照為準(zhǔn)A還是B根本沒法統(tǒng)一回答。所以干脆放棄緩存每次count都按照當(dāng)前事務(wù)的快照去掃描數(shù)據(jù)這樣才能保證事務(wù)隔離性的正確。理解了這一點(diǎn)你就明白為什么InnoDB的大表count()那么慢——因?yàn)樗娴囊逊蠗l件的索引記錄掃一遍才能得出總數(shù)。這也是為什么會(huì)有人在千萬級(jí)大表上執(zhí)行count()后直接卡死把數(shù)據(jù)庫(kù)拖垮的原因。2.3 索引對(duì)count性能的決定性影響如果InnoDB表是一張沒有任何二級(jí)索引、只有主鍵的表那么執(zhí)行count(*)時(shí)InnoDB只能掃描主鍵聚簇索引。主鍵索引的葉子節(jié)點(diǎn)保存的是整行的全部數(shù)據(jù)掃描起來IO開銷很大速度自然慢。但如果你給表添加了一個(gè)或多個(gè)二級(jí)索引非主鍵索引InnoDB在優(yōu)化器允許的情況下會(huì)選擇“最小的索引樹”來掃描。什么是“最小的索引樹”就是索引的鍵值長(zhǎng)度最短的那棵B樹。由于二級(jí)索引的葉子節(jié)點(diǎn)只保存索引列和主鍵字段不包含其他列的數(shù)據(jù)占用的空間更小掃描時(shí)讀入的頁(yè)更少IO開銷更低速度更快。我用一張幾百萬行的表實(shí)測(cè)過給一個(gè)冗余的tinyint字段添加索引以后count(*)的耗時(shí)直接從原來的1.2秒降到了不到0.3秒。所以如果你需要頻繁對(duì)某張表統(tǒng)計(jì)總行數(shù)而表中又沒有合適的短索引可以考慮建一個(gè)只用于統(tǒng)計(jì)的短字段索引這對(duì)大表count性能的提升非常明顯。3. 各數(shù)據(jù)庫(kù)廠商下的表現(xiàn)差異以為理解了MySQL就萬事大吉太天真了。count函數(shù)在不同數(shù)據(jù)庫(kù)里的底層執(zhí)行方式差異很大生產(chǎn)環(huán)境切換數(shù)據(jù)庫(kù)時(shí)這些細(xì)微區(qū)別隨時(shí)可能踩雷。3.1 MySQL下的實(shí)際執(zhí)行計(jì)劃分析在MySQL 8.0的環(huán)境下對(duì)同一張表分別執(zhí)行三條SQL并查看執(zhí)行計(jì)劃EXPLAIN SELECT COUNT(*) FROM order_info; EXPLAIN SELECT COUNT(1) FROM order_info; EXPLAIN SELECT COUNT(pay_time) FROM order_info;實(shí)測(cè)下來count(*)和count(1)的執(zhí)行計(jì)劃完全一致type為indexkey為某個(gè)二級(jí)索引掃描行數(shù)也相同。而count(pay_time)的執(zhí)行計(jì)劃同樣是index掃描但掃描時(shí)會(huì)逐行判斷pay_time是否為NULL如果pay_time列允許NULL且存在NULL值實(shí)際返回的行數(shù)會(huì)小于掃描行數(shù)。還有一個(gè)常見誤區(qū)很多人以為count()會(huì)選中“所有列”參與計(jì)算所以會(huì)很慢但優(yōu)化器會(huì)自動(dòng)選擇成本最低的索引來做覆蓋掃描并不會(huì)真的去逐一讀取每一行所有列的數(shù)據(jù)。這也是為什么“count()慢”這個(gè)說法在現(xiàn)代MySQL里并不準(zhǔn)確真正慢的是沒有索引可用的大表全表掃描。3.2 Oracle和SQL Server的特例Oracle中count(*)和count(1)的性能差異同樣是忽略不計(jì)的但count(列名)如果要判斷NULLOracle讀取數(shù)據(jù)塊后還要額外做一次空值判斷代價(jià)稍高。更值得關(guān)注的是Oracle用了Bitmap索引或者列式存儲(chǔ)時(shí)count的方式會(huì)發(fā)生根本變化這種場(chǎng)景不建議把MySQL的經(jīng)驗(yàn)直接套過來。SQL Server里有個(gè)有趣的細(xì)節(jié)它的執(zhí)行計(jì)劃對(duì)count()做了專門的優(yōu)化會(huì)直接用最窄的非聚集索引來做流式計(jì)數(shù)甚至在某些情況下可以通過統(tǒng)計(jì)信息預(yù)估行數(shù)而不需要完全掃描。但如果你的表是堆表沒有聚集索引count(列名)則必須做全表掃描性能和count()差了一個(gè)量級(jí)。所以說別把“count用哪個(gè)”當(dāng)成一個(gè)固定的銀彈問題必須結(jié)合當(dāng)前數(shù)據(jù)庫(kù)的類型、版本、引擎和索引設(shè)計(jì)來分析。曾經(jīng)有個(gè)項(xiàng)目從MySQL 5.7遷移到PostgreSQL 14原本在MySQL上跑得好好的count(某列)在PG里優(yōu)化器走了不同的索引路徑耗時(shí)直接翻了三倍最后是調(diào)整了索引結(jié)構(gòu)才把性能追回來。4. 實(shí)戰(zhàn)場(chǎng)景與性能優(yōu)化技巧理論講透了下面直接上實(shí)戰(zhàn)。畢竟要真正會(huì)用count函數(shù)就得知道在什么場(chǎng)景下選擇哪一種以及遇到慢SQL時(shí)怎么優(yōu)化。4.1 不同業(yè)務(wù)場(chǎng)景下count的選擇建議如果你的業(yè)務(wù)邏輯是“只要總數(shù)不管某個(gè)列是否為空”那么無條件用count()這也是所有規(guī)范里優(yōu)先級(jí)最高的寫法。比如統(tǒng)計(jì)用戶總量、訂單總量、商品總量一律count()語(yǔ)義準(zhǔn)確、可讀性強(qiáng)、性能也不差。如果你的業(yè)務(wù)邏輯是“統(tǒng)計(jì)某個(gè)字段有值的人數(shù)”比如統(tǒng)計(jì)填寫了手機(jī)號(hào)的用戶數(shù)量、統(tǒng)計(jì)有支付記錄的有效訂單數(shù)那必須用count(指定列)。但這里有一個(gè)隱藏風(fēng)險(xiǎn)該列如果有NULL以外的“偽空值”比如空字符串count(列名)是會(huì)統(tǒng)計(jì)進(jìn)去的。如果你想要的是“統(tǒng)計(jì)非空且非空字符串的值”那得寫成count(列名)配合WHERE過濾或者用sum(case when 列名 is not null and 列名 ! then 1 else 0 end)。還有一種常見場(chǎng)景是“統(tǒng)計(jì)去重后的數(shù)量”。count(distinct 列名)和普通count(列名)的語(yǔ)義又不同它統(tǒng)計(jì)的是該列去重后的非NULL值的個(gè)數(shù)。注意distinct會(huì)把NULL視為一個(gè)值但count(distinct 列名)不統(tǒng)計(jì)NULL。這里特別容易混淆劃重點(diǎn)記住。4.2 大表count(*)的三種優(yōu)化策略幾百萬行的小表count(*)無所謂但如果表到了幾千萬甚至上億行每次實(shí)時(shí)掃描索引都是沉重的負(fù)擔(dān)。我總結(jié)了三類可行的大表count優(yōu)化方案第一類是“定時(shí)快照近似值”。比如運(yùn)營(yíng)后臺(tái)的Dashboard里展示的“總用戶數(shù)”真的不需要每一秒都精確到最新值完全可以每十分鐘統(tǒng)計(jì)一次把結(jié)果寫入一張統(tǒng)計(jì)表前端直接查統(tǒng)計(jì)表。這樣既保證了展示速度又不會(huì)在大表上頻繁跑count。第二類是“匯總表觸發(fā)器或事件”。每次插入、刪除數(shù)據(jù)時(shí)使用事務(wù)同步維護(hù)一張計(jì)數(shù)表把count的開銷平攤到每一次DML操作上。適合寫入頻率不高、但查詢頻率極高的表。比如訂單狀態(tài)表寫入量不大就可以用觸發(fā)器維護(hù)一個(gè)訂單總數(shù)計(jì)數(shù)器。第三類是“利用信息庫(kù)表或近似估算”。在MySQL中通過information_schema.tables里的table_rows字段來估算行數(shù)這個(gè)值不是精確值但很多場(chǎng)景夠用了。注意這個(gè)數(shù)值在InnoDB里是抽樣估算的誤差可能達(dá)到百分之十幾用于幾十億數(shù)據(jù)規(guī)模的粗略趨勢(shì)展示可以但用于對(duì)賬、報(bào)表精確統(tǒng)計(jì)則完全不行。4.3 count(1)和count(*)在慢SQL優(yōu)化中的實(shí)戰(zhàn)生產(chǎn)環(huán)境里最常見的慢SQL之一就是頻繁對(duì)大表執(zhí)行count()。我自己處理過一個(gè)真實(shí)案例一張日志表的count()查詢?cè)诟叻迤诰谷恍枰?秒以上導(dǎo)致接口超時(shí)。我當(dāng)時(shí)的排查步驟是第一步用explain查看執(zhí)行計(jì)劃發(fā)現(xiàn)走了全表掃描第二步查看表結(jié)構(gòu)發(fā)現(xiàn)除了主鍵外沒有任何二級(jí)索引第三步給表增加了一個(gè)最小的int類型的二級(jí)索引用于覆蓋掃描把count(*)的執(zhí)行計(jì)劃從全表掃變成了index索引全掃第四步重新壓測(cè)耗時(shí)降到了0.4秒左右。不能說這就是最優(yōu)方案因?yàn)槿罩颈淼膶懭肓糠浅４箢~外維護(hù)一個(gè)索引也增加了寫入成本。所以后來我們又加了一層優(yōu)化業(yè)務(wù)側(cè)把“最近7天日志條數(shù)”改成“近似條數(shù)”使用緩存方案不再查詢數(shù)據(jù)庫(kù)實(shí)時(shí)統(tǒng)計(jì)。最終接口響應(yīng)時(shí)間從2秒壓到了100毫秒以內(nèi)。5. 高頻踩坑與面試細(xì)節(jié)速查最后這點(diǎn)內(nèi)容是給那些正在準(zhǔn)備面試或者剛接手舊項(xiàng)目的同學(xué)看的。count函數(shù)的坑隱蔽性強(qiáng)很多線上數(shù)據(jù)對(duì)不上的事故查到最后才發(fā)現(xiàn)是count用法的問題。5.1 空值統(tǒng)計(jì)與NULL處理的陷阱這里有一個(gè)極容易翻車的例子。假設(shè)有一張用戶表user_info其中字段user_name允許NULL你需要統(tǒng)計(jì)“全部用戶和填寫了用戶名的用戶數(shù)量”寫了下面兩條SQLSELECT COUNT(*), COUNT(user_name) FROM user_info;如果你的數(shù)據(jù)里恰好有三行user_name為NULL最終返回的結(jié)果可能就是“10, 7”很多業(yè)務(wù)方看到這個(gè)結(jié)果直接蒙了認(rèn)為數(shù)據(jù)庫(kù)出bug了。根本原因就是count(user_name)不統(tǒng)計(jì)NULL這個(gè)坑我在公司至少講過三遍。還有個(gè)小細(xì)節(jié)count(列名)判斷的是NULL不是空字符串。如果user_name字段填充了一個(gè)空字符串這個(gè)行會(huì)被count(user_name)統(tǒng)計(jì)進(jìn)去因?yàn)榭兆址皇荖ULL。想要排除空字符串必須寫成sum(case when user_name is not null and user_name ! then 1 else 0 end)。5.2 count(distinct 列名)的代價(jià)與優(yōu)化思路count(distinct 列名)看起來好用它的底層代價(jià)卻非常大要先對(duì)目標(biāo)列進(jìn)行排序或使用哈希去重然后再統(tǒng)計(jì)。數(shù)據(jù)量一大這條SQL的響應(yīng)時(shí)間可能是普通count的十倍不止。我曾經(jīng)在500萬行上執(zhí)行count(distinct user_id)整整跑了8秒多。如果業(yè)務(wù)確實(shí)需要高頻統(tǒng)計(jì)去重后的數(shù)量一種優(yōu)化方案是使用近似去重函數(shù)比如MySQL的APPROX_COUNT_DISTINCT8.0以上版本或者在大數(shù)據(jù)場(chǎng)景用HyperLogLog算法比如Redis的PFCOUNT命令。它們都能犧牲極小的精確度換來近乎實(shí)時(shí)的統(tǒng)計(jì)性能對(duì)報(bào)表場(chǎng)景來說性價(jià)比非常高。還有一種場(chǎng)景是只需要知道某個(gè)值是否存在比如“這個(gè)user_id是否出現(xiàn)在表里”那就別用count改用EXISTS它在找到第一條匹配記錄后就會(huì)停止掃描性能遠(yuǎn)超count。5.3 面試高頻追問與回答思路面試官問count的區(qū)別其實(shí)不只是在考察你記沒記住結(jié)論更深層的意圖是想看你對(duì)數(shù)據(jù)庫(kù)底層實(shí)現(xiàn)機(jī)制的理解程度?？偨Y(jié)幾個(gè)高頻追問第一“為什么MyISAM的count(*)不用掃描”前面已經(jīng)說過答案是MyISAM在元數(shù)據(jù)里緩存了總行數(shù)執(zhí)行時(shí)直接讀取?？疾禳c(diǎn)在于是否了解兩種存儲(chǔ)引擎的根本差異。第二“InnoDB的count(*)一定比count(1)慢嗎”正確答案是不一定現(xiàn)代優(yōu)化器會(huì)把兩者編譯成相同的執(zhí)行計(jì)劃。考察點(diǎn)在于是否迷信舊有經(jīng)驗(yàn)。第三“count(id)用的是主鍵索引count(*)也用的主鍵嗎”答案是優(yōu)化器會(huì)選擇最短的二級(jí)索引去掃如果沒有合適的二級(jí)索引才掃主鍵索引?？疾禳c(diǎn)在于是否真正理解索引選擇機(jī)制。第四“為什么建議把count(*)寫進(jìn)規(guī)范而不是count(1)”參考答案是語(yǔ)義表達(dá)清晰、沒有性能劣勢(shì)、代碼可讀性好。考察點(diǎn)在于項(xiàng)目經(jīng)驗(yàn)里是否有代碼規(guī)范意識(shí)。6. 一句總結(jié)外加實(shí)操建議我在實(shí)際項(xiàng)目中見過太多因?yàn)閏ount用錯(cuò)導(dǎo)致的數(shù)據(jù)事故所以給自己帶的技術(shù)小組定了三條規(guī)矩第一統(tǒng)計(jì)總數(shù)一律用count()誰寫count(1)誰去復(fù)查表結(jié)構(gòu)第二統(tǒng)計(jì)有值數(shù)量必須明確字段語(yǔ)義先查該列是否存在NULL再寫count(列名)第三大表count必須走優(yōu)化方案線上代碼里禁止裸查億級(jí)大表的count()。最后再分享一個(gè)調(diào)優(yōu)技巧如果你用的是InnoDB又確實(shí)需要在幾千萬行的表上頻繁統(tǒng)計(jì)總數(shù)與其糾結(jié)count函數(shù)本身不如花點(diǎn)時(shí)間設(shè)計(jì)一個(gè)短字段索引或者改造成緩存預(yù)統(tǒng)計(jì)方案。我在實(shí)踐里試過很多方法后者對(duì)接口響應(yīng)速度的提升最為立竿見影。希望這篇內(nèi)容能幫你把count函數(shù)徹底吃透下次再遇到相關(guān)SQL問題直接照著這些原則處理就行。