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