問題引入

“鳥兒啊，聽說微軟至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的提升相當明顯。