如何让SQL中的COUNT(*)飞起来

如何让SQL中的COUNT(*)飞起来

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 好上一丢丢。

经常看到网上有贴发表，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)。在这里提到列式索引，旨在分享，列式索引的存储和压缩优势。

对数据库各项特性了解越多，对待同一问题可用的方法也就越多。所以，我找不到理由，不去通读数据库体系类的书。