SQL Server 列存儲索引第二篇：設計

列存儲索引可以是聚集的，也可以是非聚集的，用戶可以在表上創建聚集的列存儲索引（Clustered Columnstore Index）或非聚集的列存儲索引（Nonclustered Columnstore Index）。由於聚集索引實際上是表的物理存儲，因此，表上只能創建一個聚集索引，該聚集索引要麼是聚集的列存儲索引，要麼是聚集的行存儲索引。由於非聚集的索引（列存儲索引和行存儲索引），是在表的物理存儲空間之外額外創建的數據結構，因此一個表可以創建多個非聚集的索引。

由於列存儲索引相比普通的B-Tree索引，提高約10被的壓縮率和查詢性能，因此，對於數據倉庫的大型數據表，都可以創建列存儲索引。而列存儲索引實際上是由兩部分構成的：列存儲區（columnstore）和增量存儲區（deltastore），並且會產生索引的碎片，在創建列存儲索引時，需要根據表的更新頻率和查詢的需求（是值查找，還是分析查詢）來為表設計合適的索引。

一，索引的設計思路

在創建索引時，對於一個表：

如果已經創建了聚集的列存儲索引，那麼該表上不能再創建非聚集的列存儲索引，但是可以創建非聚集的行存儲索引。
如果已經創建的聚集的行存儲索引，那麼該表上可以創建一個或多個非聚集的列存儲索引，也可以創建一個或多個非聚集的行存儲索引。

列存儲索引特別適合進行大量數據的分析查詢，而行存儲索引適合用於少量數據值的查找。

聚集的列存儲索引是整個表的物理存儲，通常把聚集的列存儲索引稱作列存儲表，而非聚集的列存儲索引是在表的物理存儲之外額外創建的數據結構，非聚集的索引包含基礎表中部分或全部的數據行，也可以只包含部分列，即，列存儲索引被定義為表的一列或多列，並具有過濾行的可選條件。

推薦的設計思路：

使用聚集的列存儲索引（把錶轉換為列存儲）來存儲事實表和大的維度表，用於提高查詢性能和數據壓縮性能，提高的性能大概在10倍左右。
在行存儲表上，使用非聚集的列存儲索引對數據進行分析查詢。

二，把列存儲和行存儲結合到一起

從SQL Server 2016 (13.x)版本開始，列存儲索引和行存儲索引可以結合在一起，利用這兩種類型的索引的優點，提高查詢性能、並減低存儲消耗。

用戶可以在rowstore表上創建一個或多個可更新的非聚集列存儲索引（updatable nonclustered columnstore index），該索引存儲所選列的副本，因此需要額外的空間來存儲此數據，但是所選數據平均被壓縮10倍。用戶可以在列存儲索引上運行分析，同時在行存儲索引上運行事務。當行存儲表中的數據更改時，列存儲將更新，因此兩個索引都針對相同的數據工作。

用戶可以在列存儲表上創建一個或多個非聚集的行存儲索引，並在基礎列存儲上執行有效的表查找。

三，設計方案

方案1：創建聚集的列存儲索引

表通常是行存儲的，為表創建一個列存儲索引，就把錶轉換為列存儲格式。聚集的列存儲索引不僅僅是一個索引，實際上，聚集的列存儲索引就是數據表的物理存儲，能夠提高10倍的壓縮率和數據查詢性能。

當表滿足以下條件，考慮創建聚集的列存儲索引：

對於分區表來說，每個分區至少100萬行數據，列存儲索引在每個分區中都有行組，如果表太小而無法在每個分區中填充行組，則無法獲得列存儲壓縮和查詢性能的好處。
查詢主要對值範圍執行分析，例如，要查找列的平均值，查詢需要掃描所有列的值，然後，通過將它們求和以確定平均值來匯總這些值。
大多數插入的數據量是海量的，而更新和刪除操作最少。

相反，如果每個分區少於100萬行數據，或者表上的更新和刪除操作非常多（更新操作會導致碎片），或者含有LOB欄位，即包含 varchar(max), nvarchar(max) 和 varbinary(max)數據類型，那麼不要創建聚集的列存儲索引。

方案2：在聚集的列存儲索引上創建非聚集的行存儲索引，用於少量值得查找

從SQL Server 2016（13.x）開始，用戶可以在聚集得列存儲索引上創建非聚集得B-Tree索引，當列存儲索引發生更改時，非聚集得B-Tree索引也會更新。通過使用輔助的B樹索引，用戶可以有效地搜索特定行，而無需掃描所有行。

方案3：使用非聚集的列存儲索引進行實時分析

從SQL Server 2016（13.x）開始，用戶可以在行存儲表（Disk-Based表或記憶體記憶體優化表）上創建非聚集的列存儲索引，使得用戶可以在事務表上進行實時分析。在基礎表上進行事務處理時，數據會更新到列存儲索引上，用戶可以在列存儲索引上進行分析性的查詢。由於一個表同時管理兩種類型的索引，因此，行存儲索引和列存儲索引都可以實時進行更新。由於列存儲索引的數據壓縮性能比行存儲索引高約10倍，因此只需要少量的額外存儲。例如，如果壓縮的行存儲表佔用20 GB，則列存儲索引可能需要額外的2 GB。所需的額外空間還取決於非聚集列存儲索引中的列數。

四，分區對列存儲的影響

可以對分區表創建列存儲索引，對於每一個分區，都有一個或多個行組，可以認為對每個分區單獨創建列存儲索引。由於列存儲索引對數據量有一個顯式的要求，100萬行，如果每個分區沒有一百萬行，那麼大多數數據行可能會轉到增量存儲，而在增量存儲中它們將無法獲得列存儲壓縮的性能優勢。除非你有足夠大的數據量，否則，為列存儲索引使用更少的分區。

舉個例子：

將100萬行載入到一個分區或未分區的表中，您將獲得一個包含100萬行的壓縮行組，這對於高數據壓縮和快速查詢性能非常有用。
將100萬行平均載入到10個分區中，每個分區獲得10萬行，這比列存儲壓縮的最低閾值還小，這導致列存儲索引可能有10個增量行組，每個組有10萬行。

雖然有一些方法可以把增量行組強制進入列存儲，但是，如果這些是columnstore索引中僅有的行，則壓縮的行組將太小而無法獲得最佳的壓縮和查詢性能。

五，選擇合適的數據壓縮演算法

列存儲索引為提供了兩種數據壓縮的演算法：列存儲壓縮（columnstore compression）和存檔壓縮（archive compression）。用戶可以在創建索引時選擇壓縮選項，稍後使用ALTER INDEX … REBUILD對其進行更改。

1，使用列存儲壓縮以獲得最佳查詢性能
與行存儲索引相比，列存儲壓縮通常可實現10倍更好的壓縮率。它是列存儲索引的標準壓縮方法，可實現快速查詢性能。

2，使用存檔壓縮以獲得最佳數據壓縮
當查詢性能不太重要時，歸檔壓縮旨在最大程度地壓縮數據，與列存儲壓縮相比，它實現了更高的數據壓縮率，但代價不菲。壓縮和解壓縮數據需要更長的時間，因此不適合快速查詢性能。

參考文檔：

Columnstore indexes – Design guidance

Tags: SQL Server SQL Server 管理列存儲索引