如果您的資料表經常需要進行GROUP BY、JOIN操作或為了避免資料扭曲,您可以在建表時設定分布鍵(Distribution Key),合適的分布鍵可以協助資料均勻分布在所有計算節點上,顯著提高計算和查詢效能。本文為您介紹Hologres中為表設定Distribution Key。
Distribution Key介紹
在Hologres中,Distribution Key屬性指定了表資料的分布策略,系統會保證Distribution Key相同的記錄被分配到同一個Shard上。建表時設定文法如下:
-- Hologres V2.1版本起支援的文法
CREATE TABLE <table_name> (...) WITH (distribution_key = '[<columnName>[,...]]');
-- 所有版本支援的文法
BEGIN;
CREATE TABLE <table_name> (...);
call set_table_property('<table_name>', 'distribution_key', '[<columnName>[,...]]');
COMMIT;
參數說明如下:
參數 | 說明 |
table_name | 設定分布鍵的表名稱。 |
columnName | 設定分布鍵的欄位名稱。 |
Distribution Key是非常重要的分布式概念,合理設定Distribution Key可以達到如下效果:
顯著提高計算效能。
不同的Shard可以進行並行計算,從而提高計算效能。
顯著提高每秒查詢率(QPS)。
當您以Distribution Key做過濾條件時,Hologres可以直接篩選出資料相關的Shard進行掃描。否則Hologres需要讓所有的Shard參與計算,影響QPS。
顯著提高Join效能。
當兩張表在同一個Table Group內,並且Join的欄位是Distribution Key時,那麼資料分布保證表A一個Shard內的資料和表B同一Shard內的資料對應,只需要直接在本節點Join本節點資料(Local Join)即可,可以顯著提高執行效率。
使用建議
Distribution Key設定原則總結如下:
Distribution Key盡量選擇分布均勻的欄位,否則容易因為資料扭曲導致負載傾斜,使得查詢效率變低,排查資料扭曲請參見查看Worker傾斜關係。
選擇
Group By
頻繁的欄位作為Distribution Key。Join情境中,設定Join欄位為Distribution Key,實現Local Join,避免資料Shuffle。同時進行Join的表需要在同一個Table Group內。
不建議為一個表設定多個Distribution Key,建議設定的Distribution Key不超過兩個欄位。設定多欄位為Distribution Key,查詢時若沒有全部命中,容易出現資料Shuffle。
支援單列或者多列設定為Distribution Key,指定列時如設定單列,命令語句中不要保留多餘空格;如設定多個列,則以半形逗號(,)分隔,同樣不要保留多餘空格。指定多列為Distribution Key時,列的順序不影響資料的布局和查詢效能。
表設定了主鍵(PK)時,Distribution Key必須為PK或者PK中的部分欄位(不可為空,即不指定任何列),因為要求同一記錄的資料只能屬於一個Shard。如果沒有額外指定Distribution Key,預設將PK設定為Distribution Key。
使用限制
設定Distribution Key需要在建表時設定,建表後如需修改Distribution Key需要重建立表並匯入資料。
不支援修改Distribution Key對應列的值,如需修改請重建立表。
不支援將Float、Double、Numeric、Array、Json及其他複雜資料類型的欄位設為Distribution Key。
表未設定PK時,Distribution Key沒有限制,可以為空白(不指定任何列)。如果為空白,即隨機Shuffle,資料隨機分布到不同Shard上。從Hologres V1.3.28版本開始,Distribution Key禁止為空白,樣本用法如下。
--從1.3.28版本開始,寫法將會被禁止 CALL SET_TABLE_PROPERTY('<tablename>', 'distribution_key', '');
Distribution Key列的值中有
null
時,當作“”
(空串)看待,即Distribution Key為空白。
技術原理
Distribution Key指定了表的分布策略。根據實際的業務情境,存在以下情形。
設定Distribution Key
為表設定了Distribution Key之後,資料會根據Distribution Key被分配到各個Shard上,演算法為Hash(distribution_key)%shard_count
,結果為對應的Shard。系統會保證Distribution Key相同的記錄會被分配到同一個Shard上,樣本如下。
V2.1版本起支援的建表文法:
--設定a列為distribution key,系統會對a列的值做hash操作,再模數,即hash(a)%shard_count = shard_id,結果相同的一組資料分布在同一個Shard內 CREATE TABLE tbl ( a int NOT NULL, b text NOT NULL ) WITH ( distribution_key = 'a' ); --設定a、b兩列為distribution key,系統對a,b兩個列的值做hash操作,再模數,即hash(a,b)%shard_count = shard_id,結果相同的一組資料分布在同一個Shard內 CREATE TABLE tbl ( a int NOT NULL, b text NOT NULL ) WITH ( distribution_key = 'a,b' );
所有版本支援的建表文法:
--設定a列為distribution key,系統會對a列的值做hash操作,再模數,即hash(a)%shard_count = shard_id,結果相同的一組資料分布在同一個Shard內 begin; create table tbl ( a int not null, b text not null ); call set_table_property('tbl', 'distribution_key', 'a'); commit; --設定a、b兩列為distribution key,系統對a,b兩個列的值做hash操作,再模數,即hash(a,b)%shard_count = shard_id,結果相同的一組資料分布在同一個Shard內 begin; create table tbl ( a int not null, b text not null ); call set_table_property('tbl', 'distribution_key', 'a,b'); commit;
資料分布示意圖如下:但在設定Distribution Key時,需要關注設定為Distribution Key欄位的資料最好是分布均勻的。Hologres的Shard數和Worker節點數有一定的關聯關係,詳情請參見基本概念。如果設定了資料分布不均勻的欄位作為Distribution Key之後,那麼資料會集中分布在某些Shard上,導致大部分的計算集中到部分Worker上,出現長尾效應,查詢效率降低。排查以及處理資料的傾斜情況詳情請參見查看Worker傾斜關係。
不設定Distribution Key
不設定Distribution Key時,資料將會被隨機分布在各個Shard,相同的資料可能會在相同Shard,也可能在不同的Shard,樣本如下。
--不設定distribution key
begin;
create table tbl (
a int not null,
b text not null
);
commit;
資料分布示意圖如下:
Group By彙總情境設定Distribution Key
為表設定了Distribution Key,那麼相同的資料就分布在相同的Shard上,同時對於Group By彙總情境,資料在計算時按照設定的Distribution Key重新分配,因此可以將Group By頻繁的欄位設定為Distribution Key,這樣資料在Shard內就已經彙總,減少資料在Shard間的重分配,提高查詢效能,樣本如下。
V2.1版本起支援的建表文法:
CREATE TABLE agg_tbl ( a int NOT NULL, b int NOT NULL ) WITH ( distribution_key = 'a' ); --樣本查詢,對a列做彙總查詢 select a,sum(b) from agg_tbl group by a;
所有版本支援的建表文法:
begin; create table agg_tbl ( a int not null, b int not null ); call set_table_property('agg_tbl', 'distribution_key', 'a'); commit; --樣本查詢,對a列做彙總查詢 select a,sum(b) from agg_tbl group by a;
通過查看執行計畫(explain SQL),如下所示執行計畫結果中沒有redistribution
運算元,說明資料沒有重分布。
兩表關聯情境設定Distribution Key
兩表Join欄位設定為Distribution Key
在兩表關聯(Join)的情境,如果兩表Join欄位分別在對應表裡都設定為Distribution Key,那麼Join欄位相同的資料就會分布在相同的Shard,這樣就能實現Local Join,從而實現查詢加速的效果,樣本如下。
建表DDL。
V2.1版本起支援的建表文法:
--tbl1按照a列分布,tbl2按照c列分布,當tbl1與tbl2以a=c關聯條件join時,對應的資料分布在同一個Shard內,這種查詢可以實現Local Join的加速效果 BEGIN; CREATE TABLE tbl1 ( a int NOT NULL, b text NOT NULL ) WITH ( distribution_key = 'a' ); CREATE TABLE tbl2 ( c int NOT NULL, d text NOT NULL ) WITH ( distribution_key = 'c' ); COMMIT;
所有版本支援的建表文法:
--tbl1按照a列分布,tbl2按照c列分布,當tbl1與tbl2以a=c關聯條件join時,對應的資料分布在同一個Shard內,這種查詢可以實現Local Join的加速效果 begin; create table tbl1( a int not null, b text not null ); call set_table_property('tbl1', 'distribution_key', 'a'); create table tbl2( c int not null, d text not null ); call set_table_property('tbl2', 'distribution_key', 'c'); commit;
查詢語句。
select * from tbl1 join tbl2 on tbl1.a=tbl2.c;
資料分布示意圖如下。通過查看執行計畫(explain SQL),如下所示執行計畫結果中沒有
redistribution
運算元,說明資料沒有重分布。兩表Join欄位未都設定為Distribution Key
在兩表關聯(Join)的情境,如果兩表Join欄位在對應表裡未都設定為Distribution Key,那麼查詢時資料就會在各個Shard Shuffle(執行計畫會根據關聯的兩個表大小,來判斷是進行Shuffle還是Broadcast)。如下樣本,設定
tbl1
的a
欄位為Distribution Key,tbl2
的d
欄位為Distribution Key,而Join條件是a=c
,那麼c
欄位就會在每個Shard Shuffle一遍,從而導致查詢效率變低。建表DDL。
V2.1版本起支援的建表文法:
BEGIN; CREATE TABLE tbl1 ( a int NOT NULL, b text NOT NULL ) WITH ( distribution_key = 'a' ); CREATE TABLE tbl2 ( c int NOT NULL, d text NOT NULL ) WITH ( distribution_key = 'd' ); COMMIT;
所有版本支援的建表文法:
begin; create table tbl1( a int not null, b text not null ); call set_table_property('tbl1', 'distribution_key', 'a'); create table tbl2( c int not null, d text not null ); call set_table_property('tbl2', 'distribution_key', 'd'); commit;
查詢語句。
select * from tbl1 join tbl2 on tbl1.a=tbl2.c;
資料分布示意圖如下。通過查看執行計畫(explain SQL),如下所示執行計畫中有
redistribution
運算元,說明資料進行了重分布,表明Distribution Key設定的不合理,需要重新設定。
多表關聯情境設定Distribution Key
多表關聯的情境比較複雜,可以遵循如下原則:
每個表的Join欄位都相同,那麼將Join欄位都設定為Distribution Key。
每個表的Join欄位不同,優先考慮大表間的Join,將大表的Join欄位設定為Distribution Key。
通過以下幾種情況舉例說明(本文中以三個表Join為例說明,大於三個表的Join情況可以參考本樣本)。
三個表的Join欄位相同
在三個表Join的情境中,三個表的Join欄位都相同,那麼這種情況是最簡單的,可以直接將三個表的Join欄位都設定為Distribution Key,實現Local Join的能力。
V2.1版本起支援的建表文法:
BEGIN; CREATE TABLE join_tbl1 ( a int NOT NULL, b text NOT NULL ) WITH ( distribution_key = 'a' ); CREATE TABLE join_tbl2 ( a int NOT NULL, d text NOT NULL, e text NOT NULL ) WITH ( distribution_key = 'a' ); CREATE TABLE join_tbl3 ( a int NOT NULL, e text NOT NULL, f text NOT NULL, g text NOT NULL ) WITH ( distribution_key = 'a' ); COMMIT; --3表join查詢 SELECT * FROM join_tbl1 INNER JOIN join_tbl2 ON join_tbl2.a = join_tbl1.a INNER JOIN join_tbl3 ON join_tbl2.a = join_tbl3.a;
所有版本支援的建表文法:
begin; create table join_tbl1( a int not null, b text not null ); call set_table_property('join_tbl1', 'distribution_key', 'a'); create table join_tbl2( a int not null, d text not null, e text not null ); call set_table_property('join_tbl2', 'distribution_key', 'a'); create table join_tbl3( a int not null, e text not null, f text not null, g text not null ); call set_table_property('join_tbl3', 'distribution_key', 'a'); commit; --3表join查詢 SELECT * FROM join_tbl1 INNER JOIN join_tbl2 ON join_tbl2.a = join_tbl1.a INNER JOIN join_tbl3 ON join_tbl2.a = join_tbl3.a;
通過查看執行計畫(explain SQL),如下所示執行計畫中:
沒有
redistribution
運算元,說明資料沒有重分布,實現了Local Join。exchange
運算元代表檔案層級彙總到Shard層級彙總,這樣就只需要對應Shard的資料,提升資料的查詢效率。
三個表的Join欄位不同
在實際業務中,多表關聯時會有Join欄位不相同的情境,這個時候可以根據如下原則來設定Distribution Key:
核心最佳化原則是優先考慮大表間的Join,設定大表的Join欄位為Distribution Key;小表因其資料量較少,無需過多考慮。
表資料量大致相同的情況,可以設定Group By頻繁的Join欄位為Distribution Key。
如下樣本,有三個表相互Join,Join的欄位不完全一樣,這個時候選擇大表的Join欄位為Distribution Key,
join_tbl_1
這個表資料量有一千萬條,join_tbl_2
和join_tbl_3
分別有一百萬條,以join_tbl_1
為主要最佳化對象。V2.1版本起支援的建表文法:
BEGIN; -- join_tbl_1為1kw資料量 CREATE TABLE join_tbl_1 ( a int NOT NULL, b text NOT NULL ) WITH ( distribution_key = 'a' ); -- join_tbl_2為100w資料量 CREATE TABLE join_tbl_2 ( a int NOT NULL, d text NOT NULL, e text NOT NULL ) WITH ( distribution_key = 'a' ); -- join_tbl_3為100w資料量 CREATE TABLE join_tbl_3 ( a int NOT NULL, e text NOT NULL, f text NOT NULL, g text NOT NULL ); WITH ( distribution_key = 'a' ); COMMIT; --join key不相同時,選擇大表的join key為distribution key。 SELECT * FROM join_tbl_1 INNER JOIN join_tbl_2 ON join_tbl_2.a = join_tbl_1.a INNER JOIN join_tbl_3 ON join_tbl_2.d = join_tbl_3.f;
所有版本支援的建表文法:
begin; --join_tbl_1為1kw資料量 create table join_tbl_1( a int not null, b text not null ); call set_table_property('join_tbl_1', 'distribution_key', 'a'); --join_tbl_2為100w資料量 create table join_tbl_2( a int not null, d text not null, e text not null ); call set_table_property('join_tbl_2', 'distribution_key', 'a'); --join_tbl_3為100w資料量 create table join_tbl_3( a int not null, e text not null, f text not null, g text not null ); --call set_table_property('join_tbl_3', 'distribution_key', 'a'); commit; --join key不相同時,選擇大表的join key為distribution key。 SELECT * FROM join_tbl_1 INNER JOIN join_tbl_2 ON join_tbl_2.a = join_tbl_1.a INNER JOIN join_tbl_3 ON join_tbl_2.d = join_tbl_3.f;
通過查看執行計畫(explain SQL),如下所示執行計畫表明:
在
join_tbl_2
和join_tbl_3
表之間有redistribution
運算元,因為join_tbl_3
是小表,Join欄位與Distribution Key不一致,所以資料進行了重分布。join_tbl_1
和join_tbl_2
表之間沒有redistribution
運算元,因為兩表將Join欄位都設定為Distribution Key,因此資料不會重分布。
使用樣本
V2.1版本起支援的建表文法:
--單表設定為distribution key CREATE TABLE tbl ( a int NOT NULL, b text NOT NULL ) WITH ( distribution_key = 'a' ); --設定多個distribution key CREATE TABLE tbl ( a int NOT NULL, b text NOT NULL ) WITH ( distribution_key = 'a,b' ); --join情境,設定join key為distribution key BEGIN; CREATE TABLE tbl1 ( a int NOT NULL, b text NOT NULL ) WITH ( distribution_key = 'a' ); CREATE TABLE tbl2 ( c int NOT NULL, d text NOT NULL ) WITH ( distribution_key = 'c' ); COMMIT; SELECT b, count(*) FROM tbl1 JOIN tbl2 ON tbl1.a = tbl2.c GROUP BY b;
所有版本支援的建表文法:
--設定一個distribution key begin; create table tbl (a int not null, b text not null); call set_table_property('tbl', 'distribution_key', 'a'); commit; --設定多個distribution key begin; create table tbl (a int not null, b text not null); call set_table_property('tbl', 'distribution_key', 'a,b'); commit; --join情境,設定join key為distribution key begin; create table tbl1(a int not null, b text not null); call set_table_property('tbl1', 'distribution_key', 'a'); create table tbl2(c int not null, d text not null); call set_table_property('tbl2', 'distribution_key', 'c'); commit; select b, count(*) from tbl1 join tbl2 on tbl1.a = tbl2.c group by b;