如何讓SQL中的COUNT(*)飛起來
本文轉(zhuǎn)載自微信公眾號「有關(guān)SQL」，作者一只小小鳥鳥。轉(zhuǎn)載本文請聯(lián)系有關(guān)SQL公眾號。
COUNT(*)是每個初學(xué)者的最愛，但凡漂亮的按下回車時，看著轉(zhuǎn)啊轉(zhuǎn)的進度條，總是有種莫名的喜感。平時總被老板催著干這干那，現(xiàn)在我也能指揮下電腦幫我跑跑數(shù)據(jù)!
雖說平時面試官總愛問 COUNT(*) 有什么壞處啊，為什么要避免使用 COUNT(*) 這類怪問題。真要說起來，他們也是一臉懵圈，因為面試題都有可能是網(wǎng)上隨便摘的。
至于原理，多少人真正懂呢，真正在乎呢?
那么，COUNT(*)的性能真那么差嗎?怎么才能提高性能呢!今天就盤它
已知 SQL Server 中有這樣張表 (其他數(shù)據(jù)庫也適用):
- CREATE TABLE [dbo].[MobileLink](
- [user_id] [varchar](50) NULL,
- [item_id] [varchar](50) NULL,
- [behavior_type] [varchar](50) NULL,
- [user_geohash] [varchar](50) NULL,
- [item_category] [varchar](50) NULL,
- [time] [varchar](50) NULL
- )
笨拙的堆表(Heap Table)
這張表沒有索引，是張堆表(Heap Table). 總共有4000多萬條數(shù)據(jù)。
第一次，運行 count(*)
- SELECT COUNT(*) AS CNT
- FROM dbo.MobileLink
可以看到運行大約花了 3 秒時間 執(zhí)行計劃也簡單，走了全表掃描
萬能的性能殺-索引
我之前也分享過，數(shù)據(jù)是存在數(shù)據(jù)頁上的。這個數(shù)據(jù)頁可以看做是一頁紙。在紙上把字寫得越緊湊，得到的信息越多。反之，如果你把字寫得夠大，行與行之間又很松散，每頁紙能容納的信息量也就少了。
于是，像這樣全表掃描的效率就很低，理論上，只要把每頁上，每一行的第一個字段統(tǒng)計下，就能知道有多少行了。于是索引就排上用場了。
第一個提高性能的方案就出來了，建一個索引
- CREATE INDEX IDX_USR_ITEM ON dbo.MobileLink(user_id,item_id) ;
執(zhí)行計劃如我所料，肯定走索引
總耗時2.036s 比剛才 3s 好上一丟丟。
經(jīng)?？吹骄W(wǎng)上有貼發(fā)表，count 單列(如 count(user_id) )會比 count(*) 有優(yōu)勢，果真如此嗎?
- SELECT COUNT(user_id) AS CNT
- FROM dbo.MobileLink
2.813s 對 2.036s , 并無優(yōu)勢。
快上加快-壓縮
那么按照剛才的思路，現(xiàn)在已經(jīng)取 user_id , item_id 作為統(tǒng)計基數(shù)了，那么是不是還有辦法可以更小?對，那就是壓縮
- ALTER INDEX IDX_USR_ITEM ON dbo.MobileLink REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
執(zhí)行上面壓縮語句，再運行 count(*). 對比結(jié)果與執(zhí)行計劃
耗時已經(jīng)進入1s級，又進一步。
再反觀，使用單列( COUNT(user_id) )來統(tǒng)計行數(shù)：
依舊在2s級徘徊!
可見， COUNT(USER_ID) 并無優(yōu)勢!
SQL Server: 我還可以更快
還有更快的方法，列式索引。它的優(yōu)點除了節(jié)省空間外，還外加壓縮，雙重優(yōu)化。
- CREATE NONCLUSTERED COLUMNSTORE INDEX COL_IDX ON dbo.MobileLink(user_id,item_id) ;
已經(jīng)破1s 級。在列式索引面前，其他索引都得讓道!
列式索引的結(jié)構(gòu)比較復(fù)雜，詳細可見這篇(SQL Server Storage)。在這里提到列式索引，旨在分享，列式索引的存儲和壓縮優(yōu)勢。
對數(shù)據(jù)庫各項特性了解越多，對待同一問題可用的方法也就越多。所以，我找不到理由，不去通讀數(shù)據(jù)庫體系類的書。
