列存索引建立指南 - PolarDB

本文介紹如何根據業務情境為目標表建立查詢效能更優的列存索引（CCI）。

適用情境

重要

列存索引是基於日誌節點（CDC）鏈路非同步構建而成，可以保證資料的查詢一致性，但由於主執行個體向列存唯讀執行個體同步資料存在秒級延遲，因此對於即時性要求極高的情境，不建議使用CCI。

混合負載情境或需要給複雜查詢加速的情境。
說明
列存索引可以有效提升複雜查詢（AP）的效能。
冷資料歸檔情境。
說明
因為列存的讀節點（CN節點）可以通過儲存在OSS中的列存索引資料來擷取中繼資料，所以您可以把冷資料使用列存索引技術轉儲至OSS，以降低儲存成本。更多資訊，請參見冷資料歸檔（TTL）。
歷史快照儲存和查詢情境。
說明
列存索引可以看作不會到期的歷史資料副本，可以儲存歷史快照，並提供歷史快照查詢，常用於審計和備份業務。
ETL情境。
說明
列存索引可以看作是主執行個體的資料副本，您可以串連列存唯讀執行個體，使用其中資料提供資料提取、轉換、載入（Extract Transform Load，ETL）服務，並將資料庫的資料轉儲到其他資料系統。

分區表

常用分區類型介紹

列存索引的分區是一種資料庫設計技術中的概念，主要用於大型表的資料分區或分割，以提高查詢效能和管理大量資料的效率。列存索引的分區策略文法如下：

PARTITION BY
HASH({column_name | partition_func(column_name)})
| KEY(column_list)
| RANGE({column_name | partition_func(column_name)})
| RANGE COLUMNS(column_list)
| LIST({column_name | partition_func(column_name)}) 
| LIST COLUMNS(column_list)} }

說明

定界分割（RANGE）：根據某一列的取值範圍劃分資料。例如：包含歷史銷售記錄的表，根據銷售年份進行定界分割，不同年份的資料會被儲存在不同的分區中。
列表分區（LIST）：根據列值是否屬於某個預定義列表劃分資料，常用於SAAS情境。例如：使用者資料表可以根據使用者所在國家進行列表分區，不同國家的使用者資料會被分別儲存在對應的分區中。
雜湊分割（HASH/KEY）：根據列值的雜湊值劃分資料，可以保證資料均勻分布。適用於無法預測的資料分布情況，或者需要均勻分布資料的情況。
更多資訊，請參見分區類型。

設定分區數的原則

分區數理論上與表的數量以及列存唯讀執行個體的規格有關。如果不明確指定分區數，則預設分區數為16。然而，通常不建議使用預設的分區數。一般遵循的原則是：列存索引的分區數的建議值為計算節點的個數*計算節點核心數。考慮到未來資料增長的潛力，最終確定的分區數也可以高於這個建議值。

說明

多表關聯操作時，為了減少需要資料重新分配的資料量，建議同一個執行個體中涉及的表，分區數保持一致。

選擇分區策略的原則

因為列存索引主要是為了提升AP類查詢的效能，且該類查詢大多是彙總和關聯查詢，所以為了發揮列存並行掃描和查詢優勢，建議採用雜湊分割。
查詢條件有明確的時間含義，這種情況下建議將日期、時間類型的欄位作為二級分區，除了該情境，其他情境如非必要請不要為列存索引建立二級分區。更多資訊，請參見二級分區。
沒有明確的範圍查詢，不建議使用定界分割；沒有基於某個預定義列表值的查詢，不建議使用列表分區；即便查詢具備上述特徵，也應優先考慮依賴行存來滿足上述查詢。
業務上依託CCI實現冷資料歸檔，建議使用時間列進行定界分割。

選擇分區鍵的原則

選擇值分布均勻的欄位作為分區鍵，例如交易ID、裝置ID、使用者ID或者自增列作為分區鍵。
說明
盡量不要選擇日期、時間和時間戳記類型的欄位作為分區鍵，寫入時容易發生傾斜影響寫入效能，且多數查詢通常是限定了日期或者時間段，如：查詢最近一天或者一個月的資料，可能會導致要查詢的資料只存在於一個節點上，無法充分利用分散式資料庫中所有節點的處理能力。這種情況下可以考慮將日期、時間類型的欄位建議作為二級分區來考慮。
儘可能將需要JOIN和GROUP BY的欄位作為分區鍵，可以有效減少資料重分布。例如，需要按照顧客維度查看歷史訂單資訊，可以選擇顧客ID作為分區鍵。
儘可能選擇頻繁出現在非範圍查詢條件中的欄位作為分區鍵，從而實現按分區鍵進行資料裁剪。
每張表只能選擇一個分區鍵，一個分區鍵可以包含一個或多個欄位。分區鍵的欄位越少就越在複雜的查詢情境中具備通用性。

重要

在建立表時，如果沒有指定分區鍵，系統會將主鍵作為分區鍵；對於沒有顯式定義主鍵的表，系統會將隱式主鍵作為分區鍵。
建立列存索引後，可以通過執行check columnar partition db_name.tbl_name命令來查看各個分區的資料量，以此判斷所選分區鍵是否合適，是否存在資料扭曲的情況。

排序鍵

排序鍵介紹

列存索引的排序鍵定義了資料在索引檔案中的排序方式，即資料按照該列有序儲存。每個列資料區塊的中繼資料套件含了該列資料區塊中所有資料的最小值和最大值等資訊。在查詢資料時，通常需要遍曆指定列的所有列資料區塊。啟用Pruner功能後，會根據查詢條件與中繼資料資訊將所有列資料區塊分為三類：相關、可能相關和不相關。在讀取資料時，僅考慮相關和可能相關的列資料區塊。由於列資料區塊可以有不同的排列順序，形成不同的組合，Pruner功能也會產生不同的過濾效果。因此，您可以改變查詢條件來調整列資料區塊的排列順序，以進一步提高查詢效能。

選擇排序鍵的原則

資料表被頻繁使用範圍查詢的情境下，建議使用該範圍條件的列作為排序鍵。
使用分頁查詢的情境下，建議使用ORDER BY列作為排序鍵
其他情境下，建議使用分區鍵作為排序鍵。

字典編碼列

字典編碼列介紹

字典編碼可以將字串的比較轉換為數位比較，從而提升字串列的GROUP BY、FILTER等查詢的效能，並提升資料的壓縮比，進一步降低儲存成本。在PolarDB-X中，可以在建立列存索引時對指定欄位進行字典編碼，即為這些欄位的值構建字典映射。樣本：

# 顯示指定字典列
DICTIONARY_COLUMNS='col1,col2';
# 顯式指定字典列建立列存索引
CREATE CLUSTERED COLUMNAR INDEX `cc_i_seller` ON t_order (`seller_id`) partition by hash(`order_id`) partitions 16
dictionary_columns='order_id,seller_id';

選擇字典編碼列的原則

建議將基數較小的字元列設定為字典編碼列，例如該列代表性別、地區等具備有限種類的分類資訊。
不建議將所有的字元列都設定為字典編碼列，因為這樣做會帶來額外的編碼、解碼開銷。

說明

對於列基數較小的字元列使用字典編碼不僅可以實現資料壓縮，減少儲存空間的佔用，還能最佳化查詢效能。然而，在分散式資料庫查詢中，會涉及到字典的解析和合併作業，這會引入額外的開銷。因此，在查詢過程中，基於字典的查詢預設是關閉的。如果需要啟用，需要將ENABLE_COLUMNAR_SLICE_DICT參數設定為TRUE。

常見問題

在叢集變更配置，是否會對分區數產生影響？
答：不會產生影響。
列存索引是否支援修改分區鍵、排序鍵、分區數、字典編碼列？
答：不支援。如有需要，請您刪除並重建該列存索引。
建立列存索引，需要購買列存唯讀執行個體嗎？
答：可以直接在主執行個體上建立列存索引，如果要查詢列存索引資料建議購買列存唯讀執行個體。