共用方式為

資料行存放區索引 - 查詢效能

  • 發行項

適用於：Microsoft Fabric 中的 SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Platform System （PDW） SQL Database

本文包含使用數據行存放區索引達成快速查詢效能的建議。

數據行存放區索引在分析和數據倉儲工作負載上可達到最高 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 必須縮減為一個，才能在可用的記憶體授與中建置受限制記憶體下的索引。

由於 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 個資料行，而查詢只使用其中 5 個資料行，則資料行存放區索引只會從磁碟機擷取那 5 個資料行。 它會略過其他 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 型樹狀結構索引執行搜尋作業。
計算純量 評估純量值的運算式 是 是 是 和所有批次模式運算符一樣，數據類型也有一些限制。
串連 (concatenation) UNION 和 UNION ALL 否 是 是
filter 套用述詞 是 是 是
雜湊比對 雜湊型彙總函式，outer hash join、right hash join、left hash join、right inner join、left inner join 是 是 是 彙總限制︰對於字串沒有最小值/最大值。 可用的彙總函式有 sum/count/avg/min/max。
連結的限制：在非整數類型上不相符類型不能互相聯結。
合併連結 否 否 否
多執行緒查詢 是 是 是
巢狀迴圈 否 否 否
在 MAXDOP 1 下執行的單一執行緒查詢 否 否 是
具有序列查詢計畫的單一執行序查詢 否 否 是
sort 在 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 with precision <= 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 次，假設有一百萬個數據列。 使用字串述詞下推，查詢執行會根據字典中的值計算述詞。 如果述詞限定，參考字典值的所有數據列都會自動限定。 這藉由兩種方法改善效能：

  • 只會傳回限定的數據列數目，減少需要流出掃描節點的數據列數目。
  • 字串比較的數目會減少。 在此範例中，只需要對 1 百萬個比較項目進行 100 個字串比較。 有一些限制：
    • 差異資料列群組沒有字串述詞下推。 差異資料列群組中的資料行沒有字典。
    • 如果字典超過 64 KB 個項目則沒有字串述詞下推。
    • 不支援評估 Null 的運算式。

區段刪除

資料類型選擇可能會對資料行存放區索引查詢中以查詢效能為基礎的常見篩選述詞造成重大影響。

在資料行存放區資料中，資料列群組是由資料行區段所組成。 每個區段都有中繼資料，可讓您快速刪除區段，而不需讀取它們。 此區段消除適用於數值、日期和時間數據類型，以及 小於或等於兩個小數字數的 datetimeoffset 資料類型。 從 SQL Server 2022 （16.x） 開始，區段消除功能會延伸至字串、二進位、guid 數據類型，以及 大於兩個的 datetimeoffset 數據類型。

升級至支援字串最小/最大區段消除的 SQL Server 版本之後（SQL Server 2022 （16.x） 和更新版本之後，數據行存放區索引在使用 REBUILD 或 DROP/CREATE重建之前，將不會對此功能受益。

區段刪除不適用於 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.
...

相關內容