SQL Server on Linux列存储索引

2021-11-18 11:07:45

问题引入

“鸟儿啊，听说微软至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 tempdb
GO
IF OBJECT_ID('dbo.Table_with_5M_rows','U') IS NOT NULL
    DROP TABLE dbo.Table_with_5M_rows
GO

CREATE 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_rows
SELECT 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 index
CREATE NONCLUSTERED INDEX IX_OrderId_@price
ON dbo.Table_with_5M_rows(OrderId)
INCLUDE(price) WITH(ONLINE =ON)
GO

--create columnstore index
CREATE CLUSTERED COLUMNSTORE INDEX CSIX_Table_with_5M_rows ON dbo.Table_with_5M_rows;
GO

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

执行测试查询

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

--clear data cache
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE 
GO

--open statistics
SET STATISTICS IO ON 
SET STATISTICS TIME ON 
GO

--Testing using B-tree index
SELECT 
    OrderId
    ,totalAmount = sum(price)
    ,avgPrice = avg(price)
FROM Table_with_5M_rows WITH(NOLOCK, INDEX=IX_OrderId_@price)
GROUP BY OrderId
GO

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

--clear data cache
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE 
GO

--Testing using Column store index
SELECT 
    OrderId
    ,totalAmount = sum(price)
    ,avgPrice = avg(price)
FROM Table_with_5M_rows WITH(NOLOCK, INDEX=CSIX_Table_with_5M_rows)
GROUP BY OrderId
GO

对比测试结果

两组查询测试语句执行完毕，以下我通过统计信息和执行计划两个方面来对比测试结果。
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的提升相当明显。

码农公寓