SQL Server 列儲存索引 第三篇：維護

悅光陰發表於2020-10-31
SQLServer索引

列儲存索引分為兩種型別：聚集的列儲存索引和非聚集的列儲存索引，在一個表上只能建立一個聚集索引，要麼是聚集的列儲存索引，要麼是聚集的行儲存索引，然而一個表上可以建立多個非聚集索引。

一，建立列儲存索引

建立列儲存索引的語法如下：

SQL Server 列儲存索引 第三篇：維護 
-- Create a clustered columnstore index on disk-based table.  
CREATE CLUSTERED COLUMNSTORE INDEX index_name  
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }  
    [ WITH ( < with_option> [ ,...n ] ) ]  
    [ ON <on_option> ] 
[ ; ]  
  
--Create a nonclustered columnstore index on a disk-based table.  
CREATE [NONCLUSTERED]  COLUMNSTORE INDEX index_name   
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
        ( column  [ ,...n ] )  
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]
    [ WITH ( < with_option> [ ,...n ] ) ]  
    [ ON <on_option> ]
[ ; ]  
  
<with_option> ::=  
      DROP_EXISTING = { ON | OFF } -- default is OFF  
    | MAXDOP = max_degree_of_parallelism 
    | ONLINE = { ON | OFF } 
    | COMPRESSION_DELAY  = { 0 | delay [ Minutes ] }  
    | DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }  
      [ ON PARTITIONS ( { partition_number_expression | range } [ ,...n ] ) ]  
  
<on_option>::=  
      partition_scheme_name ( column_name )
    | filegroup_name
    | "default"
  
<filter_expression> ::=  
      column_name IN ( constant [ ,...n ]  
    | column_name { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< } constant
View Code

選項註釋：

  • DROP_EXISTING = [OFF] | ON ： 刪除已經儲存的索引，建立一個新的索引，預設值是ON
  • MAXDOP = max_degree_of_parallelism：設定最大併發度，0是預設值，根據系統的工作負載動態調正併發度；1表示單執行緒，禁止併發；>1 表示指定併發度，但是系統會根據當前的工作負載適當減少併發度。
  • COMPRESSION_DELAY = 0 | delay [ Minutes ]：預設值是0，對於disk-based表，該選項指定處於CLOSED 狀態的delta rowgroup必須保留在delta rowgroup的最小延遲，當超過該延遲後，SQL Server的後臺程式 tuple-mover 會把該CLOSED增量行組壓縮到列儲存中。
  • DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE：列儲存的資料壓縮演算法
  • ON on_opiton：用於指定分割槽架構或檔案組

1，建立聚集的列儲存索引

建立聚集的列儲存索引，實際上是把行版本的heap或行版本的聚集索引轉換為列儲存，聚集的列儲存索引實際上就是整個表。

建立聚集的列儲存索引，是對錶中所有行和列進行儲存，按照列進行壓縮和排序，在物理儲存上也會重建組織。

CREATE CLUSTERED COLUMNSTORE INDEX index_name  
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }  
    [ WITH ( < with_option> [ ,...n ] ) ]  
    [ ON <on_option> ]

2，建立非聚集的列儲存索引

在一個行儲存的heap或聚集索引上建立一個in-memoy的非聚集的列儲存索引（in-memory nonclustered columnstore index），該索引可以有過濾條件，不需要包含所有的資料行；該索引還可以有列過濾，不需要包含所有的列。

該索引需要足夠的空間來儲存資料的副本，它是可更新的， 隨著基表的更新而更新。

--Create a nonclustered columnstore index on a disk-based table.  
CREATE [NONCLUSTERED]  COLUMNSTORE INDEX index_name   
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
        ( column  [ ,...n ] )  
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]
    [ WITH ( < with_option> [ ,...n ] ) ]  
    [ ON <on_option> ]

3，列儲存索引不支援的資料型別

如果表中的欄位是如下資料型別，不能建立列儲存索引：

  • ntext, text, and image
  • nvarchar(max), varchar(max), and varbinary(max) (Applies to SQL Server 2016 (13.x) and prior versions, and nonclustered columnstore indexes)
  • rowversion (and timestamp)
  • sql_variant
  • CLR types (hierarchyid and spatial types)
  • xml
  • uniqueidentifier (Applies to SQL Server 2012 (11.x))

二，資料載入和列儲存索引的更新

把資料載入到表中，總的來說，有兩種模式：bulk load和 trickle insert。

1，批量資料載入（bulk load）

批量載入是指將大量行新增到資料儲存中的方式，這是把資料移入列儲存索引的最高效方式，因為它對成批的行進行操作。 批量載入將行組填充到最大容量，並將其直接壓縮到列儲存中。 在載入結束時，只有不滿足每個行組最少102,400行的行才能進入增量儲存。

 在批量載入資料時，應該考慮到：

  • 批量載入不對資料進行預排序，資料按照接收的順序插入到行組中。
  • 如果批處理大小 > = 102400，則這些行將被直接放入壓縮的行組中。建議設定大於或等於102400的批大小以進行有效的批量匯入，這可以避免SQL Server把資料移入到增量行組（delta rowgroup）。
  • 如果批量大小<102,400，或其餘行<102,400，則將這些行載入到增量行組中。
  • 對於增量行組中的資料，如果資料累加到閾值102400行，那麼後臺執行緒Tuple mover（TM）最終把行移動到壓縮行組中。

批量載入的優點：

  • 併發載入：多個批量載入可以併發執行，每個都載入一個單獨的資料檔案。這與行儲存批量載入到SQL Server中不同，不需要指定TABLOCK，因為每個批量匯入執行緒都把資料排他地鎖定到單獨的行組（壓縮行或增量行組）中，並且排他鎖定。
  • 減少日誌記錄：把資料直接載入到壓縮的行組中可顯著減少日誌的大小。例如，如果把資料壓縮10倍，那麼相應的事務日誌大約小10倍，而無需TABLOCK或大容量日誌記錄/簡單恢復模型。轉到增量行組的所有資料均已完全記錄。這包括小於102,400行的任何批處理大小。最佳實踐是使用batchsize> =102400，由於不需要TABLOCK，因此可以並行載入資料。
  • 最小化日誌記錄：如果遵循最小化日誌記錄的前提條件，則可以進一步減少日誌記錄。但是，這與把資料載入到行儲存區不同，TABLOCK導致表上的X鎖而不是BU（批量更新）鎖，因此無法完成並行資料載入。
  • 鎖定優化：把資料載入到壓縮的行組中時，會自動獲取行組上的X鎖。但是，當批量載入到增量行組中時，在行組處會獲得X鎖，但SQL Server仍會鎖定PAGE / EXTENT，因為X行組鎖不屬於鎖定層次結構。

2，流插入

流插入（Trickle Insert）是指逐行移入到列儲存索引的方式，通常使用insert語句，使用流插入，所有行都會進入到deltastore，這對於少量的資料插入非常有用。

INSERT INTO <table-name>(....) 
VALUES (<set of values>)

一旦delta 行組包含1,048,576行，這個delta 行組的狀態由OPEN狀態轉換為CLOSED狀態，有一個後臺執行緒Tuple Mover（TM）每隔大約5分鐘週期性地檢查關閉的增量行組（CLOSED delta rowgroup），把資料壓縮到列儲存索引中。

如果delta行組處於CLOSED狀態，使用者可以顯式呼叫以下命令來壓縮封閉的增量行組：

ALTER INDEX <index-name> 
on <table-name> 
REORGANIZE

如果delta行組處於OPEN狀態，使用者也可以強制增量行組關閉和壓縮，可以執行以下命令：

ALTER INDEX <index-name> 
on <table-name> 
REORGANIZE 
with (COMPRESS_ALL_ROW_GROUPS = ON)

三，整理索引的碎片

列儲存索引的碎片，實際上是指標記為刪除的行。

1，檢查列儲存索引的碎片

通過使用  sys.dm_db_column_store_row_group_physical_stats，使用者可以探測到列儲存索引中被標記為刪除的行所佔的百分比，這是衡量列儲存索引中碎片程度的度量，

SELECT i.object_id,
    object_name(i.object_id) AS TableName,
    i.index_id,
    i.name AS IndexName,
    100*(ISNULL(SUM(CSRowGroups.deleted_rows),0))/NULLIF(SUM(CSRowGroups.total_rows),0) AS 'Fragmentation'
FROM sys.indexes AS i  
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups
    ON i.object_id = CSRowGroups.object_id
    AND i.index_id = CSRowGroups.index_id
WHERE object_name(i.object_id) = 'FactResellerSalesXL_CCI'
GROUP BY i.object_id, i.index_id, i.name
ORDER BY object_name(i.object_id), i.name;

2，整理列儲存索引的碎片

整理列儲存索引的碎片，主要是索引的重組和重建：

  • 重組索引是指： 重組索引的葉級別上的資料，通過對葉級頁面進行物理重新排序以匹配葉節點的邏輯順序（從左到右），可以對錶和檢視上的聚簇索引和非聚簇索引的葉級別進行碎片整理。重新組織還會壓縮索引頁。 
  • 重建索引是指：刪除原始的索引，並重建建立一個新的索引，對於列儲存索引來說，重建索引會移除碎片、把所有的資料行都移入列儲存，把標記為刪除的行所佔用的儲存空間釋放。

3，舉個例子，重組索引

第一次執行以下命令，用於把列儲存索引上的所有CLOSED 和OPEN 的行組進入到colunstore中

ALTER INDEX columnstore_index_name
ON table_name 
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

再次執行該命令，SQL Server會把小的行組合併到一個大的壓縮行組中。

4，舉個例子，重建索引

ALTER INDEX columnstore_index_name
ON table_name 
REBUILD;

四，列儲存索引的後設資料

對列儲存索引的操作，分為物理儲存上的統計和邏輯層面的操作上的統計。

行組由兩種格式，一種是columnstore格式的的行組，一種是rowstore格式的行組。在列儲存索引中，deltastore中的行組是rowstore格式的，而columnstroe索引中的行組是columnstore格式的。

1，列儲存行組的物理儲存上統計

系統檢視：sys.dm_db_column_store_row_group_physical_stats

關鍵欄位註釋：

  • row_group_id：行組的ID，-1 表示in-memory tail。
  • delta_store_hobt_id：在deltastore中的行組的hobt_id，如果為NULL表示行組不在deltastroe中。
  • state和state_desc：行組的狀態，
    • INVISIBLE：用於行組，表示行組正在建立，資料正在被壓縮，該行組在列儲存中是不可見的，當壓縮完成之後，狀態由INVISIBLE轉換為COMPRESSED，行組由rowstore格式轉換為columnstore格式。
    • COMPRESSED：用於行組，表示行組使用列儲存壓縮演算法被壓縮，被儲存到columnstore中
    • OPEN：用於deltastore，表示deltastore中的行組可以接收資料行，一個OPEN的行組是rowstore格式，並且沒有被壓縮到columnstore格式。
    • CLOSED：用於deltastore， 表示deltastore中行組包含了最大資料行，正在等待被後臺的tuple mover程式壓縮到列儲存，
    • TOMBSTONE：一個行組曾經在deltastore，並且不再被引用。
  • total_rows：行組實際儲存的行數，包含新增的和標記為刪除的行
  • delted_rows：標記為刪除的行

2，列儲存索引的邏輯操作統計

系統檢視：sys.dm_db_column_store_row_group_operational_stats

關鍵欄位註釋:

  • row_group_id：行組的ID
  • scan_count：通過行組進行掃描的次數
  • delete_buffer_scan_count：使用刪除緩衝區確定此行組中已刪除行的次數，即被刪除的行被重新查詢的次數。
  • index_scan_count：列儲存索引分割槽被掃描的次數
  • rowgroup_lock_count：對行組申請鎖定的次數
  • rowgroup_lock_wait_count：對行組申請鎖定時需要等待的次數
  • rowgroup_lock_wait_in_ms：對行組申請鎖定時等待的時間，單位是毫秒

 

 

 

參考文件：

COLUMNSTORE METADATA

CREATE COLUMNSTORE INDEX (Transact-SQL)

Resolve index fragmentation by reorganizing or rebuilding indexes

相關文章