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

SQL Server 列存儲系列：

• SQL Server 列存儲索引 第一篇：概述
• SQL Server 列存儲索引 第二篇：設計
• SQL Server 列存儲索引 第三篇：維護
• SQL Server 列存儲索引 第四篇：實時營運資料分析

列存儲索引可以是聚集的，也可以是非聚集的，使用者可以在表上建立聚集的列存儲索引（Clustered Columnstore Index）或非聚集的列存儲索引（Nonclustered Columnstore Index）。由于聚集索引實際上是表的實體存儲，是以，表上隻能建立一個聚集索引，該聚集索引要麼是聚集的列存儲索引，要麼是聚集的行存儲索引。由于非聚集的索引（列存儲索引和行存儲索引），是在表的實體存儲空間之外額外建立的資料結構，是以一個表可以建立多個非聚集的索引。

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

一，索引的設計思路

在建立索引時，對于一個表：

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

列存儲索引特别适合進行大量資料的分析查詢，而行存儲索引适合用于少量資料值的查找。

聚集的列存儲索引是整個表的實體存儲，通常把聚集的列存儲索引稱作列存儲表，而非聚集的列存儲索引是在表的實體存儲之外額外建立的資料結構，非聚集的索引包含基礎表中部分或全部的資料行，也可以隻包含部分列，即，列存儲索引被定義為表的一列或多列，并具有過濾行的可選條件。

推薦的設計思路：

• 使用聚集的列存儲索引（把表轉換為列存儲）來存儲事實表和大的次元表，用于提高查詢性能和資料壓縮性能，提高的性能大概在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

作者

：悅光陰

出處

：http://www.cnblogs.com/ljhdo/

本文版權歸作者和部落格園所有，歡迎轉載，但未經作者同意，必須保留此段聲明，且在文章頁面醒目位置顯示原文連接配接，否則保留追究法律責任的權利。