问题引入

“鸟儿啊，听说微软至SQL Server 2012以来，推出了一种全新的基于列式存储的索引，你去研究看看SQL Server on Linux对这个功能的支持度如何，效率有多大的提升？”。老鸟又迫不及待的开始给菜鸟分配任务。

分析问题

的确如老鸟所说，从SQL Server 2012开始推出了列存储索引，这个版本限制颇多，但是它对统计查询的效率提升又是实实在在的。所以，让我们来看看SQL Server on Linux列存储索引对统计查询的效率提升情况如何。

这里也顺便提一下SQL Server 2012 列存储索引的限制，比如：

非聚集列存储索引是只读的，换句话说，基表会变成Read-Only

仅支持非聚集列存储索引

只能通过删除及创建索引的方式重建索引，而不可使用ALTER INDEX命令

对应的表不可包含唯一性约束、主键约束或外键约束

......

解决问题

这一小节，我们以一组对比测试来看看列存储索引相对于B-Tree索引对统计查询的效率提升，真正是强大到没有敌人。

创建测试对象

测试之前，我们需要创建测试表对象，B-Tree索引和列存储索引，并且初始化500万条记录数据，做为测试的基础数据。

use tempdbGOIF OBJECT_ID('dbo.Table_with_5M_rows','U') IS NOT NULL    DROP TABLE dbo.Table_with_5M_rowsGOCREATE TABLE [dbo].[Table_with_5M_rows](    [OrderItemId] [bigint] NULL,    [OrderId] [int] NULL,    [Price] [int] NULL,    [ProductName] [varchar](240) NULL) ON [PRIMARY]GO;WITH a AS (    SELECT *     FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS a(a))INSERT INTO Table_with_5M_rowsSELECT TOP(5000000)    OrderItemId = ROW_NUMBER() OVER (ORDER BY a.a)    ,OrderId = a.a + b.a + c.a + d.a + e.a + f.a + g.a + h.a    ,Price = a.a * 10    ,ProductName = cast(a.a as varchar)  + cast(b.a as varchar)  + cast(c.a as varchar)  + cast(d.a as varchar)  + cast(e.a as varchar)  + cast(f.a as varchar)  + cast(g.a as varchar)  + cast(h.a as varchar)FROM a, a AS b, a AS c, a AS d, a AS e, a AS f, a AS g, a AS h;GO--Create regular indexCREATE NONCLUSTERED INDEX IX_OrderId_@priceON dbo.Table_with_5M_rows(OrderId)INCLUDE(price) WITH(ONLINE =ON)GO--create columnstore indexCREATE CLUSTERED COLUMNSTORE INDEX CSIX_Table_with_5M_rows ON dbo.Table_with_5M_rows;GO

对象创建完毕后，截图如下：

执行测试查询

首先，我们来测试使用B-Tree常规索引的查询效率，业务场景是统计每一个订单的消费总额和平均每单价格。这里，我们强制查询语句使用索引IX_OrderId_@price，需要注意的地方是，在执行查询语句之前，我们需要清空缓存来避免缓存对执行结果的影响。查询语句如下：

--clear data cacheDBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO--open statisticsSET STATISTICS IO ON SET STATISTICS TIME ON GO--Testing using B-tree indexSELECT     OrderId    ,totalAmount = sum(price)    ,avgPrice = avg(price)FROM Table_with_5M_rows WITH(NOLOCK, INDEX=IX_OrderId_@price)GROUP BY OrderIdGO

同样的道理，在对比组查询测试最开始，我们需要清空SQL Server缓存，然后强制使用列存储索引CSIX_Table_with_5M_rows，语句如下：

--clear data cacheDBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE GO--Testing using Column store indexSELECT     OrderId    ,totalAmount = sum(price)    ,avgPrice = avg(price)FROM Table_with_5M_rows WITH(NOLOCK, INDEX=CSIX_Table_with_5M_rows)GROUP BY OrderIdGO

对比测试结果

两组查询测试语句执行完毕，以下我通过统计信息和执行计划两个方面来对比测试结果。

B-Tree索引查询统计信息：

Table 'Table_with_5M_rows'. Scan count 1, logical reads 16136, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:   CPU time = 1295 ms,  elapsed time = 1313 ms.

列存储索引查询统计信息：

Table 'Table_with_5M_rows'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 73, lob physical reads 7, lob read-ahead reads 0.Table 'Table_with_5M_rows'. Segment reads 6, segment skipped 0. SQL Server Execution Times:   CPU time = 5 ms,  elapsed time = 15 ms.

从查询执行的统计信息输出来看，基于B-Tree索引的查询逻辑读IO为16136，CPU消耗为1295毫秒，执行时间为1313毫秒，而基于列存储索引的查询逻辑读IO为0，CPU消耗为5毫秒，执行时间15毫秒。CPU和执行时间上有259倍和87倍的性能提升。

B-Tree索引查询执行计划截图：

列存储索引查询执行计划截图：

从实际的执行计划对比来看，IO消耗从11.912降低到0.003125，大大节约了IO的性能开销，这也是为什么性能提升非常显著的原因。

写在最后

SQL Server on Linux对列存储索引的支持这点非常强大，对于统计查询效率的提升尤其是IO的提升相当明显。