資料行存放區索引 - 查詢效能
適用於：
SQL ServerAzure SQL 資料庫Azure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric 中的 SQL 資料庫
本文包含使用數據行存放區索引達成快速查詢效能的建議。
數據行存放區索引在分析和數據倉儲工作負載上可達到最高 100 倍的效能，而且數據壓縮比傳統數據列存放區索引高出 10 倍。 這些建議可協助您的查詢達到數據行存放區索引所設計的快速查詢效能。
改善查詢效能的建議
以下是一些建議，用於達成資料行存放區索引所提供的高效能。
1. 組織資料以在全資料表掃描中排除更多資料列群組
仔細選擇插入順序。 在一般的傳統資料倉儲案例中，資料確實是按照時間順序插入且分析是在時間維度中進行。 例如，按照季來分析銷售量。 針對這類型的工作負載，會自動進行列組排除。 在 SQL Server 2016 (13.x) 中，您會發現查詢程序略過數字資料列群組。
使用數據列存放區叢集索引。 如果通用查詢述詞位於與插入順序無關的欄位（例如，
C1），請在欄位C1上建立行存放區叢集索引。 然後，卸除數據列存放區叢集索引，並建立叢集數據行存放區索引。 如果您明確地使用MAXDOP = 1建立叢集列存儲索引，則產生的叢集列存儲索引會在C1上完全排序。 如果您指定MAXDOP = 8，則您會看到八個數據列群組的值重疊。 如果是非叢集列存放區索引（NCCI），如果數據表有行存放區叢集索引，則數據列已經由叢集索引鍵排序。 在此情況下，非叢集數據行存放區索引也會自動排序。 數據行存放區索引原本不會維護數據列的順序。 當插入新資料列或更新較舊的數據列時，您可能需要重複此程式，因為分析查詢效能可能會惡化。實作資料表分區。 您可以分割列存儲索引，然後使用分割淘汰來減少需要掃描的列組。 例如，事實資料表會儲存客戶的購買記錄。 常見的查詢模式是尋找每季度的購買
customer。 在此情況下，請將插入順序欄位與customer欄位的分區結合。 每個分割區都包含每個customer的資料列，並按照插入時的順序排序。 此外，如果需要從數據行存放區移除較舊的數據，請考慮使用數據表數據分割。 確換掉和修剪不需要的分區是刪除數據而確保沒有碎片形成的有效策略。避免刪除大量資料。 從數據列群組中移除壓縮的數據列不是同步作業。 解壓縮資料列群組、刪除資料列然後將其重新壓縮，會十分昂貴。 因此，當您從壓縮的數據列群組中刪除數據時，這些數據列群組仍會掃描，即使它們傳回較少的數據列也一樣。 如果數個數據列群組的已刪除數據列數目足以合併成較少的數據列群組，重新組織數據行存放區會增加索引的品質，並改善查詢效能。 如果您的數據刪除程式通常會清空整個數據列群組，請考慮使用數據表分割。 替換不再需要的分區，並截斷它們，而不是刪除資料列。
注意
從 SQL Server 2019 （15.x）開始，Tuple-mover 由背景合併任務所協助。 此工作會自動壓縮已存在一段時間的較小 OPEN 異動數據列群組，根據內部臨界值決定，或合併大量數據列已被刪除的 COMPRESSED 數據列群組。 這會隨著時間的推移，逐步提高資料行存放區索引的品質。 如果需要從數據行存放區索引刪除大量數據，請考慮將作業分割成一段時間內較小的刪除批次。 批處理可讓背景合併工作處理合併較小數據列群組的工作，並改善索引品質。 因此，數據刪除後不需要排程索引重組維護窗口。 如需資料行存放區詞彙與概念的詳細資訊，請參閱資料行存放區索引：概觀。
2.規劃足夠的記憶體以平行建立資料行存放區索引
除非記憶體受到限制，資料行存放區索引的建立預設都是平行作業。 平行建立索引比循序建立索引需要更多的記憶體。 在記憶體很寬裕的情況下，建立資料行存放區索引花費的時間大約是根據相同的資料行建構 B 型樹狀目錄的 1.5 倍。
建立資料行存放區索引所需的記憶體取決於資料行數目、字串資料行的數目、平行處理原則的程度 (DOP) 和資料的特性。 例如，如果您的數據表的數據列少於一百萬個，SQL Server 只會使用一個線程來建立數據行存放區索引。
如果您的數據表有一百多萬個數據列，但 SQL Server 無法取得足夠的記憶體授與，以使用 MAXDOP 建立索引，SQL Server 會視需要自動減少 MAXDOP。 在某些情況下，必須將 DOP 降至1，才能在受限制的可用記憶體分配中建置索引。
由於 SQL Server 2016 （13.x），查詢一律會以批次模式運作。 在舊版中，只有當 DOP 大於 1 時才會使用批次執行。
欄儲效能說明
列存儲索引透過結合高速記憶體內的批次模式處理和大幅降低 I/O 操作需求的技術，來實現高效能的查詢。 由於分析查詢會掃描大量數據列，所以通常是 I/O 系結，因此在查詢執行期間減少 I/O 對於數據行存放區索引的設計至關重要。 將數據讀入記憶體後，請務必減少記憶體中的操作的數目。
資料行存放區索引會透過高資料壓縮、資料行存放區刪除、資料列群組刪除和批次處理來減少 I/O 並最佳化記憶體內作業。
資料壓縮
數據行存放區索引的數據壓縮比數據列存放區索引高出 10 倍。 這樣可以大幅減少執行分析查詢的 I/O 要求，因此可改善查詢效能。
列存儲索引會從磁碟讀取壓縮資料，這意味著需要讀入記憶體的位元組更少。
列儲存索引會將資料以壓縮格式存放在記憶體中，藉由避免將相同的資料讀入記憶體來減少 I/O 操作。 例如，使用 10 倍的壓縮，資料行索引在記憶體中可以保存 10 倍的資料，相較於以未壓縮的形式儲存資料。 在記憶體中有更多數據時，數據行存放區索引更有可能在記憶體中尋找所需的數據，而不會從磁碟產生不必要的讀取。
資料行存放區索引是根據資料行來壓縮資料，而不是根據資料列，這樣可以達到高壓縮率並減少磁碟機上儲存的資料大小。 每個資料行都已壓縮並獨立儲存。 數據行中的數據一律具有相同的數據類型，而且通常會有類似的值。 當值相似時，列存儲數據壓縮技術在實現較高壓縮率方面表現出色。
例如，事實數據表會儲存客戶位址，並有一個 country-region 列。 可能值的總數小於 200。 其中一些值重複多次。 如果事實數據表有 1 億個數據列，數據 country-region 行會輕鬆壓縮，而且需要很少的記憶體。 逐行壓縮無法以這種方式利用列數值的相似性，因此必須使用更多位元組來壓縮 country-region 列中的數值。
欄位刪除
列存儲索引會略過讀取查詢結果中不需要的列。 數據行刪除可進一步減少查詢執行的 I/O，因此可改善查詢效能。
- 能夠進行行刪除的原因是資料以資料行的形式來組織和壓縮。 相反地，當數據逐列儲存時，每個數據列中的數據行值會實際儲存在一起，而且不容易分隔。 查詢處理器必須讀取整個數據列以擷取特定的數據行值，因為額外的數據不必要地讀取到記憶體中，因此會增加I/O。
例如，如果數據表有50個數據行，而查詢只會使用其中五個數據行，則數據行存放區索引只會從磁碟擷取五個數據行。 它會略過其他 45 個數據行的讀取，假設所有數據行的大小都類似，因此將 I/O 減少 90%。 如果相同的數據儲存在數據列存放區中，查詢處理器必須讀取其餘 45 個數據行。
資料列群組刪除
對於完整數據表掃描，大部分的數據通常不符合查詢述詞準則。 藉由使用元數據，數據行存放區索引就能夠略過數據列群組中不包含查詢結果所需數據的數據讀取，而不需要實際 I/O。 這項功能稱為列群消除，能夠減少全表掃描的 I/O，因而改善查詢效能。
資料行存放區索引何時需要執行完整資料表掃描？
從 SQL Server 2016 （13.x）開始，您可以在叢集列存儲索引上建立一個或多個一般的非叢集行存儲或 B-樹索引。 非叢集 B 樹索引可以加快具有等值述詞或是值範圍較小的述詞的查詢。 對於更複雜的述詞，查詢優化器可能會選擇完整資料表掃描。 若無法略過數據列群組，完整數據表掃描可能會很耗時，尤其是大型數據表。
什麼時候分析查詢在進行全資料表掃描時會因為排除資料列群組而受益？
例如，零售業務會使用具有叢集數據行存放區索引的事實數據表來建立銷售數據的模型。 每筆新銷售都會儲存交易的各種屬性，包括產品的銷售日期。 有趣的是，即使數據行存放區索引不保證排序順序，此數據表中的數據列會以日期排序的順序載入。 隨著時間推移，此數據表會成長。 雖然該零售商可能會保留近 10 年的銷售資料，但分析查詢應該只需要計算最近一季的彙總。 資料行存放區索引只要查看日期資料行的中繼資料，即可刪除對前 39 季資料的存取。 這是讀取到記憶體並處理的數據量減少97%。
完整資料表掃描會略過哪些資料列群組？
資料行存放區索引會使用中繼資料，來針對每個資料列群組排序各資料行區段的最大值和最小值，以決定要刪除的資料列群組。 當沒有任何數據行區段範圍符合查詢述詞準則時，會略過整個數據列群組，而不會執行任何實際的 I/O。 這可運作，因為數據通常會以排序順序載入。 雖然不保證數據列排序，但類似的數據值通常位於相同的數據列群組或鄰近數據列群組內。
如需資料列群組的詳細資訊，請參閱資料行存放區索引設計指導方針。
批次模式執行
批次模式執行 會處理群組中的數據列，通常一次最多900個，以提高效率。 例如，查詢 SELECT SUM(Sales) FROM SalesData 會計算 SalesData 資料表中的總銷售額。 在批次模式中，查詢引擎會處理900個數據列群組中的數據。 這種方法可藉由將元數據存取成本和其他類型的額外負荷分散到批次中的所有數據列，而不是產生每個數據列的額外負荷，藉此減少元數據存取成本。 此外，批次模式盡可能搭配壓縮數據運作，並移除數據列模式中使用的一些交換運算元，大幅加快分析查詢的速度。
並非所有查詢執行操作子都能在批次模式中執行。 例如，資料操作語言（DML）作業，如插入、刪除或更新，是逐行執行的。 批次模式運算符，例如掃描、聯結、匯總、排序等，可以改善查詢效能。 因為資料行存放區索引是在 SQL Server 2012 (11.x) 引進，所以仍需要一些努力來擴增可在批次模式中執行的運算子。 下表顯示根據產品版本以批次模式執行的運算符。
| 批次模式運算子 | 使用時 | SQL Server 2012 (11.x) | SQL Server 2014 (12.x) | SQL Server 2016 (13.x) 和 SQL Database 1 | 註解 |
|---|---|---|---|---|---|
| DML 操作 (insert、delete、update、merge) | 否 | 否 | 否 | DML 不是批次模式作業，因為它不是平行作業。 即使我們啟用序列模式批次處理，仍看不出允許 DML 以批次模式處理之後能有顯著改善。 | |
| 列存儲索引掃描 | SCAN | 尚未提供 | 是 | 是 | 對於列存索引，我們可以將條件推送到 SCAN 節點。 |
| 列存儲索引掃描 (非叢集) | SCAN | 是 | 是 | 是 | 是 |
| 索引搜尋 | 尚未提供 | 尚未提供 | 否 | 我們以資料列模式透過非叢集 B 型樹狀結構索引執行搜尋作業。 | |
| 計算純量 | 評估純量值的運算式 | 是 | 是 | 是 | 和所有批次模式運算符一樣，數據類型也有一些限制。 |
| 串連 | UNION 和 UNION ALL | 否 | 是 | 是 | |
| filter | 套用述詞 | 是 | 是 | 是 | |
| 雜湊比對 | 雜湊型彙總函數、外部雜湊聯接、右雜湊聯接、左雜湊聯接、右內部聯接、左內部聯接 | 是 | 是 | 是 | 彙總限制︰對於字串沒有最小值/最大值。 可用的彙總函式有 sum/count/avg/min/max。 連結的限制：在非整數類型上不相符類型不能互相聯結。 |
| 合併連結 | 否 | 否 | 否 | ||
| 多執行緒查詢 | 是 | 是 | 是 | ||
| 巢狀迴圈 | 否 | 否 | 否 | ||
| 在 MAXDOP 1 下執行的單一執行緒查詢 | 否 | 否 | 是 | ||
| 具有序列查詢計畫的單一執行序查詢 | 否 | 否 | 是 | ||
| 排序 | 在 SCAN 上搭配資料行存放區索引由子句排序。 | 否 | 否 | 是 | |
| 頂端排序 | 否 | 否 | 是 | ||
| 視窗彙總 | 尚未提供 | 尚未提供 | 是 | SQL Server 2016 (13.x) 中新的運算子。 |
1 適用於 SQL Server 2016 （13.x）、SQL 資料庫 進階層、標準層 - S3 和更新版本，以及所有虛擬核心層，以及分析平台系統 （PDW）
如需詳細資訊，請參閱查詢處理架構指南。
彙總下推
在「批次模式」中，從 SCAN 節點擷取符合條件的資料列並彙總其值的一般執行途徑。 雖然這會提供良好的效能，但從 SQL Server 2016 （13.x） 開始，匯總作業可以推送至 SCAN 節點。 匯總下推可以在批次模式執行的基礎上大幅度提高匯總計算的效能，前提是符合下列條件：
- 彙總是
MIN、MAX、SUM、COUNT、和COUNT(*)。 - 彙總運算子必須位於 SCAN 節點或包含
GROUP BY的 SCAN 節點之上。 - 此匯總不是獨特的匯總。
- 匯總數據行不是字串數據行。
- 匯總數據行不是虛擬數據行。
- 輸入和輸出資料類型必須是下列其中一項，且必須符合 64 位：
- tinyint、int、bigint、smallint、bit
- smallmoney、money、decimal 和 numeric，精度 <= 18
- smalldate， date， datetime， datetime2， time
例如，匯總下推會在下列兩個查詢中完成：
SELECT productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
SELECT SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;
字串述詞下推
在設計資料倉儲結構描述時，建議的結構描述模型是使用一或多個事實資料表及多個維度資料表的星型結構描述或雪花結構描述。
提示
事實資料表 會儲存商務測量或交易，而 維度資料表 會儲存要分析之事實間的維度。 如需維度模型化的詳細資訊，請參閱 Microsoft Fabric 中的維度模型化。
例如，事實可以是代表特定產品在特定地區之銷售的記錄，而維度則代表地區、產品等集合。 事實表和維度表透過主鍵/外鍵關係來連接。 最常使用的分析查詢會聯結一或多個維度資料表和事實資料表。
讓我們考慮維度資料表 Products。 典型的主鍵是 ProductCode，通常表示為字串。 針對查詢效能，最佳做法是建立代理鍵，通常是一個 整數 欄位，以便從事實表中參照維度表的列。
數據行存放區索引會以有效率的方式執行包含數值或整數索引鍵的聯結和述詞的分析查詢。 SQL Server 2016 （13.x） 藉由將字串數據行的述詞向下推送至 SCAN 節點，大幅改善了使用字串型數據行的分析查詢效能。
字串述詞下推會利用針對數據行建立的主要/次要字典來改善查詢效能。 例如，請考慮數據列群組內包含100個相異字串值的字串資料行區段。 假設有一百萬個資料列，每個相異字串值平均被參考 10,000 次。 使用字串述詞下推功能，查詢操作會根據字典中的值計算出述詞。 如果謂詞符合，參考字典值的所有資料列都會自動符合。 這藉由兩種方法改善效能：
- 只會傳回限定的數據列數目，減少需要流出掃描節點的數據列數目。
- 字串比較的數目會減少。 在此範例中，只需要進行 100 次字串比較，而不是 100 萬次比較。 有一些限制：
- 差異資料列群組沒有字串述詞推送。 增量資料列群組中的資料行沒有字典。
- 如果字典超過 64 KB 個項目則沒有字串述詞下推。
- 不支援評估 Null 的運算式。
區段刪除
資料類型的選擇可能會對基於常見篩選述詞的資料行存放區索引查詢效能產生重大影響。
在資料行存放區資料中，資料列群組是由資料行區段所組成。 每個區段都有中繼資料，可讓您快速刪除區段，而不需讀取它們。 此段落刪除適用於數值、日期和時間資料類型，以及 小數位數小於或等於二的 datetimeoffset 資料類型。 從 SQL Server 2022 （16.x）開始，區段消除功能會延伸至字串、二進位、guid 資料類型，以及 scale 大於二的 datetimeoffset 資料類型。
升級至支援字串最小/最大區段消除的 SQL Server 版本之後，(SQL Server 2022 （16.x） 和更新版本)，在數據行存放區索引使用 ALTER INDEX REBUILD 或 CREATE INDEX WITH (DROP_EXISTING = ON)重建之前，無法受益於此功能。
區段消除不適用於 LOB 資料類型，例如 （max） 資料類型長度。
目前，只有 SQL Server 2022 (16.x) 和更新版本支援對 LIKE 謂詞前綴的叢集資料行存放區資料列群組消除功能，例如 column LIKE 'string%'。
LIKE的非前置詞使用不支援區段消除，例如 column LIKE '%string'。
已排序的數據行存放區索引 也受益於區段消除，尤其是字串數據行。 在已排序的數據行存放區索引中，索引鍵中第一個數據行的區段消除最為有效，因為它已排序。 由於數據表中其他數據列的區段消除，效能提升變得難以預測。 如需已排序資料行存放區索引的詳細資訊，請參閱 針對大型數據倉儲數據表使用已排序的數據行存放區索引。 如需已排序的數據行存放區索引可用性，請參閱 已排序的數據行索引可用性。
使用查詢連線選項 SET STATISTICS IO，您可以檢視區段刪除的運作情形。 尋找如下的輸出，以指出已發生區段刪除。 行群組是由列區段所組成，因此這可能表示區段消除。 查詢的下列 SET STATISTICS IO 輸出範例，查詢略過大約 83% 的數據：
...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...