使用已排序的列存儲索引進行效能調整
適用於：
Microsoft Fabric 中的 SQL Server 2022 （16.x） Azure SQL 資料庫 Azure SQL 受控執行個體SQL 資料庫
藉由啟用有效率的區段消除，已排序的數據行存放區索引會略過大量不符合查詢述詞的已排序數據，以提供更快的效能。 將數據載入已排序的數據行存放區索引，並透過索引重建進行排序可能需要比非排序索引更長的時間，因為數據排序作業，但是使用已排序的數據行存放區索引查詢之後可以更快執行。
當使用者查詢數據行存放區數據表時，優化工具會檢查儲存在每個區段中的最小值和最大值。 查詢述詞界限外的區段不會從磁碟讀取到記憶體。 如果要讀取的區段數目及其大小總計較小，查詢可以更快完成。
如需已排序的資料列存放區索引可用性，請參閱 已排序的數據行存放區索引可用性。
如需最近新增資料行存放區索引功能的詳細資訊，請參閱 資料行存放區索引中的新功能。
已排序與非已排序的數據行存放區索引
在數據行存放區索引中，每個數據列群組中每個數據行中的數據都會壓縮成個別的區段。 每個區段都包含描述其最小值和最大值的元數據，因此查詢執行期間不會從磁碟讀取超出查詢述詞界限的區段。
當列存儲索引未排序時，索引產生器不會先對數據進行排序再將其壓縮成區段。 這表示可能會發生重疊值範圍的區段，導致查詢從磁碟讀取更多區段，而且需要較長的時間才能完成。
當您建立已排序的數據行存放區索引時，Database Engine 會依照您在索引產生器將現有數據壓縮成區段之前所指定的順序索引鍵來排序現有數據。 使用已排序的數據時，區段重迭會減少或消除，讓查詢有更有效率的區段消除，因此效能較快，因為從磁碟讀取的區段較少。
根據可用的記憶體、資料大小、平行處理的程度、索引類型（叢集與非叢集），以及索引建立的類型（離線與在線），排序的列存儲索引可能是完整的（無區段重疊）或部分的（某些區段重疊）。 例如，當可用記憶體不足而無法進行完整排序時，就會發生部分排序。 使用已排序數據行存放區索引的查詢通常會比使用非排序索引執行得更快，即使已排序索引是使用部分排序所建置也一樣。
針對使用 ONLINE = ON 和 MAXDOP = 1 選項建立或重建的已排序叢集數據行存放區索引，提供完整排序。 在此情況下，排序不會受限於可用的記憶體，因為它會使用 tempdb 資料庫來溢出不符合記憶體的數據。 這可能會讓索引建置程式變慢，因為額外的 tempdb 輸入/輸出操作。 不過，透過在線索引重建，查詢可以在重建新的已排序索引時繼續使用現有的索引。
如果排序的數據量足夠小，足以完全適合可用記憶體，則針對透過 ONLINE = OFF 和 MAXDOP = 1 選項建立或重建的有序叢集及非叢集分欄存放區索引，也可以提供完整排序。
在其他所有情況下，列存儲索引中的排序都是部分的。
注意
目前，已排序的列存儲索引只有在 Azure SQL Database 和 Azure SQL 受控實例中，並使用 Always-up-to-date 更新政策時，才能在線建立或重建。
若要檢查數據行的區段範圍，並判斷是否有任何區段重疊，請使用下列查詢，以架構、數據表和數據行名稱取代佔位元：
SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
o.name AS table_name,
cols.name AS column_name,
pnp.index_id,
cls.row_count,
pnp.data_compression_desc,
cls.segment_id,
cls.column_id,
cls.min_data_id,
cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
AND
cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
AND
o.name = '<Table Name>'
AND
cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;
例如，此查詢中完全排序的列存儲索引的輸出可能如下所示。 請注意，不同區段的 min_data_id 和 max_data_id 數據行中沒有重疊。
schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo Table1 Column1 1 479779 COLUMNSTORE 0 1 -17 1469515
dbo Table1 Column1 1 887658 COLUMNSTORE 1 1 1469516 2188146
dbo Table1 Column1 1 930144 COLUMNSTORE 2 1 2188147 11072928
注意
在已排序的數據行存放區索引中，來自相同 DML 批次或數據載入作業的新數據只會在該批次內排序。 數據表中沒有包含現有數據的全域排序。
若要在插入新數據或更新現有數據之後排序索引中的數據，請重建索引。
針對分區行存儲索引的離線重建，重建時每次重建一個分區。 在重建該分割區完成之前，正在重建中的數據將無法使用。
在在線重建期間，數據仍可供使用。 如需詳細資訊，請參閱 線上執行索引作業。
查詢性能
從已排序的數據行存放區索引獲得效能取決於查詢模式、數據大小、排序數據的方式、區段的實體結構，以及可用於查詢執行的計算資源。
具有下列模式的查詢通常會使用已排序的數據行存放區索引更快執行。
- 具有相等、不相等或範圍述詞的查詢。
- 在查詢中，謂詞欄位與已排序的CCI欄位相同。
在此範例中，數據表T1 具有依序由Col_C、 Col_B 和 Col_A 排序的叢集列存儲索引。
CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);
查詢 1 和 2 的效能能夠比查詢 3 和 4 更受益於已排序的數據行存放區索引，因為它們會參考所有已排序的數據行。
-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';
-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';
-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';
-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';
數據載入效能
數據載入到具有有序列存儲索引的數據表中的效能類似於分區表。 由於數據排序作業，載入資料所需的時間比使用非排序的數據行存放區索引還要長，不過查詢之後的執行速度可能會更快。
減少區段重迭
重疊區段的數量取決於需要排序的數據大小、可用的記憶體，以及在有序的列存儲索引建立過程中設定的最大平行處理度（MAXDOP）。 下列策略可減少區段重疊，不過它們可能會讓索引建置程式更久。
- 如果有在線索引建置可用，請在建立已排序的叢集數據行存放區索引時，同時使用
ONLINE = ON和MAXDOP = 1選項。 這會建立完全排序的索引。 - 如果無法使用在線索引組建，請使用 [
MAXDOP = 1] 選項。 - 在載入之前，先依排序索引鍵預先排序數據。
當 MAXDOP 大於 1 時，用於建立有序列儲存索引的每個線程都會對資料的子集進行運作，並在各自的本地範圍內進行排序。 不會對由不同線程排序的資料進行全域排序。 使用平行線程可以縮短建立索引的時間，但它會產生比使用單一線程時更多的重疊區段。 使用單個線程作業可提供最高的壓縮品質。 您可以使用 CREATE INDEX 命令來指定 MAXDOP。
範例
檢查已排序的數據行和順序序數
SELECT object_name(c.object_id) AS table_name,
c.name AS column_name,
i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
AND
c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;
建立已排序的數據行存放區索引
叢集排序的資料列存放區索引：
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);
非叢集排序的資料列存放區索引：
CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);
新增或移除排序欄位，並重建現有的已排序列存儲索引
叢集排序的資料列存放區索引：
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);
非叢集排序的資料列存放區索引：
CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);
在堆積數據表上線上建立具排序功能的列存儲叢集索引
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);
在線上使用完整排序重建排序的叢集列存儲索引
CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);