聚簇索引Clustering Key - Hologres

本文為您介紹在Hologres中聚簇索引Clustering Key使用的相關內容。

Clustering Key介紹

Hologres會按照聚簇索引在檔案內對資料進行排序，建立聚簇索引能夠加速在索引列上的範圍和過濾查詢。設定Clustering Key的文法如下，需要建表時指定。

-- Hologres V2.1版本起支援的文法
CREATE TABLE <table_name> (...) WITH (clustering_key = '[<columnName>[,...]]');

-- 所有版本支援的文法
BEGIN;
CREATE TABLE <table_name> (...);
CALL set_table_property('<table_name>', 'clustering_key', '[<columnName>{:asc} [,...]]');
COMMIT;

參數說明：

參數	說明
table_name	設定聚簇索引的表名稱。
columnName	設定聚簇索引的欄位名稱。

使用建議

Clustering Key主要適用於點查以及範圍查詢的情境，對於過濾操作有比較好的效能提升，即對於where a = 1或者where a > 1 and a < 5的情境加速效果比較好。可以同時設定Clustering Key和Bitmap Column以達到最佳的點查效能。
Clustering Key具備左匹配原則，因此一般不建議設定Clustering Key超過兩個欄位，否則適用情境受限。Clustering Key是用於排序，所以Clustering Key裡的列組合是有先後關係的，即排在前面列的排序優先順序高於後面的列。
指定Clustering Key欄位時，可在欄位名後添加:asc來構建索引時的排序方式。排序方式預設為asc，即升序。Hologres V2.1以前版本不支援設定構建索引時的排序方式為降序（desc），如果設定了降序，無法命中Clustering Key，導致查詢效能不佳；從V2.1版本開始，開啟如下GUC後支援設定Clustering Key為desc，但僅支援Text、Char、Varchar、Bytea、Int等類型的欄位，其餘資料類型的欄位暫不支援設定Clustering Key為desc。
```
set hg_experimental_optimizer_enable_variable_length_desc_ck_filter = on;
```
對於行存表，Clustering Key預設為主鍵（Hologres V0.9之前版本預設不設定）。如果設定和主鍵不同的Clustering Key，那麼Hologres會為這張表產生兩個排序（Primary Key排序和Clustering Key排序），造成資料冗餘。

使用限制

如需修改Clustering Key，請重建立表並匯入資料。
Clustering Key必須為not nullable的列或者列組合。Hologres V1.3.20~1.3.27版本支援Clustering Key為nullable，從V1.3.28版本開始不支援Clustering Key為nullable，為nullable的Clustering Key可能會影響資料正確性，如果業務有強需求設定Clustering Key為null，可以在SQL前添加如下參數。
```
set hg_experimental_enable_nullable_clustering_key = true;
```
不支援將Float、Float4、Float8、Double、Decimal(Numeric)、Json、Jsonb、Bit、Varbit、Money、Time With Time Zone及其他複雜資料類型的欄位設定為Clustering Key。
Hologres V2.1以前版本不支援設定構建索引時的排序方式為降序（desc），如果設定了降序，無法命中Clustering Key，導致查詢效能不佳；從V2.1版本開始，開啟如下GUC後支援設定Clustering Key為desc，但僅支援Text、Char、Varchar、Bytea、Int等類型的欄位，其餘資料類型的欄位暫不支援設定Clustering Key為desc。
```
set hg_experimental_optimizer_enable_variable_length_desc_ck_filter = on;
```
對於列存表，Clustering Key預設為空白，需要根據業務情境顯式指定。

在Hologres中，每個表只能設定一組Clustering Key。即建表的時候只能使用call命令一次，不能執行多次，如下樣本：

V2.1版本起支援的建表文法：

--正確樣本
CREATE TABLE tbl (
    a int NOT NULL,
    b text NOT NULL
)
WITH (
    clustering_key = 'a,b'
);

--錯誤樣本
CREATE TABLE tbl (
    a int NOT NULL,
    b text NOT NULL
)
WITH (
    clustering_key = 'a',
    clustering_key = 'b'
);

所有版本支援的建表文法：

--正確樣本
BEGIN;
CREATE TABLE tbl (a int NOT NULL, b text NOT NULL);
CALL set_table_property('tbl', 'clustering_key', 'a,b');
COMMIT;

--錯誤樣本
BEGIN;
CREATE TABLE tbl (a int NOT NULL, b text NOT NULL);
CALL set_table_property('tbl', 'clustering_key', 'a');
CALL set_table_property('tbl', 'clustering_key', 'b');

COMMIT;

技術原理

Clustering Key在實體儲存體上是指在檔案內進行排序，預設為升序（asc），可以通過下圖理解Clustering Key的布局概念。

邏輯布局。
Clustering Key查詢具備左匹配原則，不匹配則無法使用Clustering Key查詢加速。如下情境樣本將為您說明Hologres中Clustering Key的邏輯布局。
準備一張表，其欄位分別包括Name、Date、Class。
- 設定Date為Clustering Key，會將表內的資料按照Date進行排序。
- 設定Class和Date為Clustering Key，會對錶先按照Class排序後再按照Date進行排序。
設定不同的欄位為Clustering Key，其最終的呈現結果也不同，具體如下圖所示。
實體儲存體布局。
Clustering Key的實體儲存體布局如下圖所示。

通過Clustering Key的布局原理可以看出：

Clustering Key適合範圍過濾的情境。比如where date= 1/1或者where a > 1/1 and a < 1/5的情境加速效果比較好。
Clustering Key查詢具備左匹配原則，不匹配則無法利用上Clustering Key查詢加速。即假設設定a,b,c三列為Clustering Key，如果是查a,b,c或者查a,b可以命中Clustering Key；如果查a,c只有a可以命中Clustering Key；如果查b,c則無法命中Clustering Key。

如下樣本，設定uid,class,date三列為Clustering Key。

V2.1版本起支援的文法：

CREATE TABLE clustering_test (
    uid int NOT NULL,
    name text NOT NULL,
    class text NOT NULL,
    date text NOT NULL,
    PRIMARY KEY (uid)
)
WITH (
    clustering_key = 'uid,class,date'
);

INSERT INTO clustering_test VALUES
(1,'張三','1','2022-10-19'),
(2,'李四','3','2022-10-19'),
(3,'王五','2','2022-10-20'),
(4,'趙六','2','2022-10-20'),
(5,'孫七','2','2022-10-18'),
(6,'周八','3','2022-10-17'),
(7,'吳九','3','2022-10-20');

所有版本支援的文法：

BEGIN;
CREATE TABLE clustering_test (
  uid int NOT NULL,
  name text NOT NULL,
  class text NOT NULL,
  date text NOT NULL,
  PRIMARY KEY (uid)
);
CALL set_table_property('clustering_test', 'clustering_key', 'uid,class,date');
COMMIT;

INSERT INTO clustering_test VALUES
(1,'張三','1','2022-10-19'),
(2,'李四','3','2022-10-19'),
(3,'王五','2','2022-10-20'),
(4,'趙六','2','2022-10-20'),
(5,'孫七','2','2022-10-18'),
(6,'周八','3','2022-10-17'),
(7,'吳九','3','2022-10-20');

只查uid列，可以命中Clustering Key。
```
SELECT * FROM clustering_test WHERE uid > '3';
```
通過查看執行計畫（explain SQL），如下所示執行計畫中有Cluster Filter運算元，表明命中了Clustering Key，查詢加速。
查uid,class列，可以命中Clustering Key。
```
SELECT * FROM clustering_test WHERE uid = '3' AND class >'1' ;
```
通過查看執行計畫（explain SQL），如下所示執行計畫中有Cluster Filter運算元，表明命中了Clustering Key，查詢加速。
查uid,class,date三列可以命中Clustering Key。
```
SELECT * FROM clustering_test WHERE uid = '3' AND class ='2' AND date > '2022-10-17';
```
通過查看執行計畫（explain SQL），如下所示執行計畫中有Cluster Filter運算元，表明命中了Clustering Key，查詢加速。
查uid,date兩列，不符合左匹配原則，因此只有uid可以命中Clustering Key，date則是走普通過濾。
```
SELECT * FROM clustering_test WHERE uid = '3'  AND date > '2022-10-17';
```
通過查看執行計畫（explain SQL），如下所示執行計畫中只有uid列有Cluster Filter運算元。
只查class,date兩列，不符合左匹配原則，都無法命中Clustering Key。
```
SELECT * FROM clustering_test WHERE class ='2' AND date > '2022-10-17';
```
通過查看執行計畫（explain SQL），如下所示執行計畫中沒有Cluster Filter運算元，表明未命中Clustering Key。

使用樣本

樣本1：命中Clustering Key的情境。

V2.1版本起支援的文法：

CREATE TABLE table1 (
    col1 int NOT NULL,
    col2 text NOT NULL,
    col3 text NOT NULL,
    col4 text NOT NULL
)
WITH (
    clustering_key = 'col1,col2'
);

--如上的建表sql，query可以被加速的情況如下：
-- 可加速
select * from table1 where col1='abc';

-- 可加速
select * from table1 where col1>'xxx' and col1<'abc';

-- 可加速
select * from table1 where col1 in ('abc','def');

-- 可加速
select * from table1 where col1='abc' and col2='def'; 

-- 不可加速
select col1,col4 from table1 where col2='def';

所有版本支援的文法：

begin;
create table table1 (
  col1 int not null,
  col2 text not null,
  col3 text not null,
  col4 text not null
);
call set_table_property('table1', 'clustering_key', 'col1,col2');
commit;

--如上的建表sql，query可以被加速的情況如下：
-- 可加速
select * from table1 where col1='abc';

-- 可加速
select * from table1 where col1>'xxx' and col1<'abc';

-- 可加速
select * from table1 where col1 in ('abc','def');

-- 可加速
select * from table1 where col1='abc' and col2='def';

-- 不可加速
select col1,col4 from table1 where col2='def';

樣本2：Clustering Key設定為asc/desc。

V2.1版本起支援的文法：

CREATE TABLE tbl (
    a int NOT NULL,
    b text NOT NULL
)
WITH (
    clustering_key = 'a:desc,b:asc'
);

所有版本支援的文法：

BEGIN;
CREATE TABLE tbl (
  a int NOT NULL, 
  b text NOT NULL
);
CALL set_table_property('tbl', 'clustering_key', 'a:desc,b:asc');
COMMIT;

進階調優手段

和傳統資料庫（MySQL或SQLServer）中的聚簇索引不同，Hologres的排序僅做到了檔案內的排序，並非是全表資料的排序，因此在Clustering Key上做order by操作仍然有一定的代價。

Hologres從V1.3版本開始針對Clustering Key的情境使用做了較多的效能最佳化，實現在使用Clustering Key時有更好的效能，主要包含如下兩個情境最佳化。如果您的版本低於1.3版本，請您使用自助升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。

針對Clustering Keys做Order By情境

在Hologres中，檔案內是按照Clustering Keys定義排序的，但在V1.3版本之前，最佳化器無法利用檔案內的Clustering Keys有序性產生最優執行計畫；同時經過Shuffle節點時也無法保障資料有序輸出（多路歸併），這就容易導致實際的計算量更大，耗時較久。在Hologres V1.3版本針對上面的情況進行最佳化，保證了產生的執行計畫能夠利用Clustering Keys的有序性，並能保障跨Shuffle保序，從而提高查詢效能。但要注意：

當表沒有對Clustering Keys做過濾時，預設走的是SeqScan，而不是IndexScan（只有IndexScan才會利用Clustering Keys的有序屬性）。
最佳化器並不保障總是產生基於Clustering Keys有序的執行計畫，因為利用Clustering Keys有序性是有些代價的（檔案內有序但記憶體中需要額外排序的）。

樣本如下。

表的DDL如下。

V2.1版本起支援的文法：

DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys;

CREATE TABLE test_use_sort_info_of_clustering_keys (
    a int NOT NULL,
    b int NOT NULL,
    c text
)
WITH (
    distribution_key = 'a',
    clustering_key = 'a,b'
);

INSERT INTO test_use_sort_info_of_clustering_keys SELECT i%500, i%100, i::text FROM generate_series(1, 1000) as s(i);

ANALYZE test_use_sort_info_of_clustering_keys;

所有版本支援的文法：

DROP TABLE if exists test_use_sort_info_of_clustering_keys;
BEGIN;
CREATE TABLE test_use_sort_info_of_clustering_keys
(
          a int NOT NULL,
          b int NOT NULL,
          c text
);
CALL set_table_property('test_use_sort_info_of_clustering_keys', 'distribution_key', 'a');
CALL set_table_property('test_use_sort_info_of_clustering_keys', 'clustering_key', 'a,b');
COMMIT;

INSERT INTO test_use_sort_info_of_clustering_keys SELECT i%500, i%100, i::text FROM generate_series(1, 1000) as s(i);

ANALYZE test_use_sort_info_of_clustering_keys;

查詢語句。

explain select * from test_use_sort_info_of_clustering_keys where a > 100  order by a, b;

執行計畫對比

V1.3之前版本（V1.1）的執行計畫（執行explain SQL）如下。

 Sort  (cost=0.00..0.00 rows=797 width=11)
   ->  Gather  (cost=0.00..2.48 rows=797 width=11)
         Sort Key: a, b
         ->  Sort  (cost=0.00..2.44 rows=797 width=11)
               Sort Key: a, b
               ->  Exchange (Gather Exchange)  (cost=0.00..1.11 rows=797 width=11)
                     ->  Decode  (cost=0.00..1.11 rows=797 width=11)
                           ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys  (cost=0.00..1.00 rows=797 width=11)
                                 Cluster Filter: (a > 100)

V1.3版本的執行計畫如下。

 Gather  (cost=0.00..1.15 rows=797 width=11)
   Merge Key: a, b
   ->  Exchange (Gather Exchange)  (cost=0.00..1.11 rows=797 width=11)
         Merge Key: a, b
         ->  Decode  (cost=0.00..1.11 rows=797 width=11)
               ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys  (cost=0.00..1.01 rows=797 width=11)
                     Order by: a, b
                     Cluster Filter: (a > 100)

V1.3版本的執行計畫相較於之前版本，利用表Clustering Keys的有序性直接做歸併輸出，整個執行可Pipeline起來，不用再擔心資料量大的時候排序慢的問題。從執行計畫對比中可以看到，V1.3版本產生的是Groupagg，相比Hashagg，處理複雜度更低，效能會更好。

針對Clustering Keys做Join的情境（Beta）

Hologres在V1.3版本新增了SortMergeJoin類型，以保證產生的執行計畫能夠利用Clustering Keys的有序性，減少計算量，從而提高效能。但需要注意：

當前該功能還處於Beta版本，預設不開啟，需要在Query前添加如下參數開啟。
```
-- 開啟merge join
set hg_experimental_enable_sort_merge_join=on;
```
當表沒有對Clustering Keys做過濾時，預設走的是SeqScan，而不是IndexScan（只有IndexScan才會利用Clustering Keys的有序屬性）。
最佳化器並不保障總是產生基於Clustering Keys有序的執行，因為利用Clustering Keys有序性是有些代價的（檔案內有序但記憶體中需要額外排序）。

樣本如下。

表的DDL如下。

V2.1版本起支援的文法：

DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys1;
CREATE TABLE test_use_sort_info_of_clustering_keys1 (
    a int,
    b int,
    c text
)
WITH (
    distribution_key = 'a',
    clustering_key = 'a,b'
);

INSERT INTO test_use_sort_info_of_clustering_keys1 SELECT i % 500, i % 100, i::text FROM generate_series(1, 10000) AS s(i);
ANALYZE test_use_sort_info_of_clustering_keys1;

DROP TABLE IF EXISTS test_use_sort_info_of_clustering_keys2;
CREATE TABLE test_use_sort_info_of_clustering_keys2 (
    a int,
    b int,
    c text
)
WITH (
    distribution_key = 'a',
    clustering_key = 'a,b'
);

INSERT INTO test_use_sort_info_of_clustering_keys2 SELECT i % 600, i % 200, i::text FROM generate_series(1, 10000) AS s(i);
ANALYZE test_use_sort_info_of_clustering_keys2;

所有版本支援的文法：

drop table if exists test_use_sort_info_of_clustering_keys1;
begin;
create table test_use_sort_info_of_clustering_keys1
(
  a int,
  b int,
  c text
);
call set_table_property('test_use_sort_info_of_clustering_keys1', 'distribution_key', 'a');
call set_table_property('test_use_sort_info_of_clustering_keys1', 'clustering_key', 'a,b');
commit;
insert into test_use_sort_info_of_clustering_keys1 select i%500, i%100, i::text from generate_series(1, 10000) as s(i);
analyze test_use_sort_info_of_clustering_keys1;

drop table if exists test_use_sort_info_of_clustering_keys2;
begin;
create table test_use_sort_info_of_clustering_keys2
(
  a int,
  b int,
  c text
);
call set_table_property('test_use_sort_info_of_clustering_keys2', 'distribution_key', 'a');
call set_table_property('test_use_sort_info_of_clustering_keys2', 'clustering_key', 'a,b');
commit;
insert into test_use_sort_info_of_clustering_keys2 select i%600, i%200, i::text from generate_series(1, 10000) as s(i);
analyze test_use_sort_info_of_clustering_keys2;

查詢語句如下。

explain select * from test_use_sort_info_of_clustering_keys1 a join test_use_sort_info_of_clustering_keys2 b on a.a = b.a and a.b=b.b where a.a > 100 and b.a < 300;

執行計畫對比

V1.3之前版本（V1.1）的執行計畫如下。

 Gather  (cost=0.00..3.09 rows=4762 width=24)
   ->  Hash Join  (cost=0.00..2.67 rows=4762 width=24)
         Hash Cond: ((test_use_sort_info_of_clustering_keys1.a = test_use_sort_info_of_clustering_keys2.a) AND (test_use_sort_info_of_clustering_keys1.b = test_use_sort_info_of_clustering_keys2.b))
         ->  Exchange (Gather Exchange)  (cost=0.00..1.14 rows=3993 width=12)
               ->  Decode  (cost=0.00..1.14 rows=3993 width=12)
                     ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys1  (cost=0.00..1.01 rows=3993 width=12)
                           Cluster Filter: ((a > 100) AND (a < 300))
         ->  Hash  (cost=1.13..1.13 rows=3386 width=12)
               ->  Exchange (Gather Exchange)  (cost=0.00..1.13 rows=3386 width=12)
                     ->  Decode  (cost=0.00..1.13 rows=3386 width=12)
                           ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys2  (cost=0.00..1.01 rows=3386 width=12)
                                 Cluster Filter: ((a > 100) AND (a < 300))

V1.3版本的執行計畫如下。

  Gather  (cost=0.00..2.88 rows=4762 width=24)
   ->  Merge Join  (cost=0.00..2.46 rows=4762 width=24)
         Merge Cond: ((test_use_sort_info_of_clustering_keys2.a = test_use_sort_info_of_clustering_keys1.a) AND (test_use_sort_info_of_clustering_keys2.b = test_use_sort_info_of_clustering_keys1.b))
         ->  Exchange (Gather Exchange)  (cost=0.00..1.14 rows=3386 width=12)
               Merge Key: test_use_sort_info_of_clustering_keys2.a, test_use_sort_info_of_clustering_keys2.b
               ->  Decode  (cost=0.00..1.14 rows=3386 width=12)
                     ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys2  (cost=0.00..1.01 rows=3386 width=12)
                           Order by: test_use_sort_info_of_clustering_keys2.a, test_use_sort_info_of_clustering_keys2.b
                           Cluster Filter: ((a > 100) AND (a < 300))
         ->  Exchange (Gather Exchange)  (cost=0.00..1.14 rows=3993 width=12)
               Merge Key: test_use_sort_info_of_clustering_keys1.a, test_use_sort_info_of_clustering_keys1.b
               ->  Decode  (cost=0.00..1.14 rows=3993 width=12)
                     ->  Index Scan using holo_index:[1] on test_use_sort_info_of_clustering_keys1  (cost=0.00..1.01 rows=3993 width=12)
                           Order by: test_use_sort_info_of_clustering_keys1.a, test_use_sort_info_of_clustering_keys1.b
                           Cluster Filter: ((a > 100) AND (a < 300))

V1.3版本的執行計畫相較於之前版本的執行計畫，利用Clustering Index的有序性，在Shard內做歸併排序後直接進行SortMergeJoin，讓整個執行Pipeline起來；可規避資料量大較大時，HashJoin需將Hash Side填充至記憶體而導致的OOM問題。