Doris(七) -- 修改表、動態和臨時分區、join的優化

来源:https://www.cnblogs.com/paopaoT/archive/2023/06/01/17449967.html
-Advertisement-
Play Games

# 修改表 ## 修改表名 ```SQL -- 1.將名為 table1 的表修改為 table2 ALTER TABLE table1 RENAME table2; -- 示例 ALTER TABLE aggregate_test RENAME aggregate_test1; -- 2.將表 e ...


修改表

修改表名

-- 1.將名為 table1 的表修改為 table2
ALTER TABLE table1 RENAME table2;

-- 示例
ALTER TABLE aggregate_test RENAME aggregate_test1;

-- 2.將表 example_table 中名為 rollup1 的 rollup index 修改為 rollup2
ALTER TABLE base_table_name RENAME ROLLUP old_rollup_name new_rollup_name;

ALTER TABLE ex_user RENAME ROLLUP rollup_u_cost new_rollup_u_cost;

desc ex_user all;

-- 3.將表 example_table 中名為 p1 的 partition 修改為 p2
ALTER TABLE example_table RENAME PARTITION old_partition_name new_partition_name ;

-- 示例：
ALTER TABLE expamle_range_tbl RENAME PARTITION p201701 newp201701; 

show partitions from expamle_range_tbl \G;

表結構變更

用戶可以通過 Schema Change 操作來修改已存在表的 Schema。目前 Doris 支持以下幾種修改:
• 增加、刪除列
• 修改列類型
• 調整列順序
• 增加、修改 Bloom Filter index
• 增加、刪除 bitmap index

原理介紹

執行 Schema Change 的基本過程，是通過原 Index 的數據，生成一份新 Schema 的 Index 的數據。其中主要需要進行兩部分數據轉換：
一是已存在的歷史數據的轉換；
二是在 Schema Change 執行過程中，新到達的導入數據的轉換。

創建作業
Schema Change 的創建是一個非同步過程，作業提交成功後，用戶需要通過 SHOW ALTER TABLE COLUMN 命令來查看作業進度。

-- 語法：
ALTER TABLE [database.]table alter_clause;

schema change 的 alter_clause 支持如下幾種修改方式：
1.向指定 index 的指定位置添加一列

ALTER TABLE db.table_name
-- 如果增加的是key列 那麼，需要在 列類型後面增加key 這個關鍵字
-- 如果增加的是value列 那麼，是聚合表模型，需要指定列的聚合類型   如果是明細模型和唯一模型，不需要指定
ADD COLUMN column_name column_type [KEY | agg_type] [DEFAULT "default_value"]
[AFTER column_name|FIRST]  -- 確定列的位置   如果不寫，預設插在最後
[TO rollup_index_name]   -- 如果你是針對rollup表新增一個列，那麼這個列明基表中不能有
[PROPERTIES ("key"="value", ...)]

-- 明細模型中添加value列
ALTER TABLE test.expamle_range_tbl ADD COLUMN abc varchar AFTER age；

-- 明細模型中添加key 列
ALTER TABLE test.expamle_range_tbl ADD COLUMN abckey varchar key AFTER user_id;

-- 聚合模型中添加一個value列
mysql> ALTER TABLE test.ex_user ADD COLUMN abckey int sum AFTER cost;

註意：

  • 聚合模型如果增加 value 列，需要指定 agg_type
  • 非聚合模型（如 DUPLICATE KEY）如果增加key列，需要指定KEY關鍵字
  • 不能在 rollup index 中增加 base index 中已經存在的列（如有需要，可以重新創建一個 rollup index）

示例：

-- 源schema:

+-----------+-------+------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key  | Default | Extra |
+-----------+-------+------+------+------+---------+-------+
| tbl1      | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k3    | INT  | No   | true | N/A     |       |
|           |       |      |      |      |         |       |
| rollup2   | k2    | INT  | No   | true | N/A     |       |
|           |       |      |      |      |         |       |
| rollup1   | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
+-----------+-------+------+------+------+---------+-------+

-- 源schema中沒有k4和k5列，所以可以往rollup表中添加 k4和k5列，在往rollup表中添加的過程，也會往base表中添加一份
ALTER TABLE tbl1
ADD COLUMN k4 INT default "1" to rollup1,
ADD COLUMN k4 INT default "1" to rollup2,
ADD COLUMN k5 INT default "1" to rollup2;

-- 改變完成後，Schema 變為       base表中也會相應的添加k4和k5
+-----------+-------+------+------+------+---------+-------+
| IndexName | Field | Type | Null | Key  | Default | Extra |
+-----------+-------+------+------+------+---------+-------+
| tbl1      | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k3    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
|           | k5    | INT  | No   | true | 1       |       |
|           |       |      |      |      |         |       |
| rollup2   | k2    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
|           | k5    | INT  | No   | true | 1       |       |
|           |       |      |      |      |         |       |
| rollup1   | k1    | INT  | No   | true | N/A     |       |
|           | k2    | INT  | No   | true | N/A     |       |
|           | k4    | INT  | No   | true | 1       |       |
+-----------+-------+------+------+------+---------+-------+

-- 這樣的導入方式錯誤
-- 因為base表中已經存在k3，導入的時候無法將base表中在添加一個叫k3的列，重覆
ALTER TABLE tbl1
ADD COLUMN k3 INT default "1" to rollup1

2.向指定 index 添加多列

ALTER TABLE db.table_name
ADD COLUMN (column_name1 column_type [KEY | agg_type] DEFAULT "default_value", ...)
[TO rollup_index_name]
[PROPERTIES ("key"="value", ...)]

-- 添加的時候根據key和value列，添加在對應的列之後
ALTER TABLE test.expamle_range_tbl ADD COLUMN (abc int,bcd int);

mysql> ALTER TABLE test.expamle_range_tbl ADD COLUMN (a int key ,b int);
Query OK, 0 rows affected (0.01 sec)

mysql> desc expamle_range_tbl all;

3.從指定 index 中刪除一列

ALTER TABLE db.table_name
DROP COLUMN column_name
[FROM rollup_index_name]

-- 刪除明細表中的value列
ALTER TABLE test.expamle_range_tbl DROP COLUMN abc;

-- 刪除明細表中的key列
ALTER TABLE test.expamle_range_tbl DROP COLUMN abckey;

-- 刪除聚合模型中的value列
ALTER TABLE test.ex_user DROP COLUMN abckey;

-- 註意：
-- 不能刪除分區列
-- 如果是從 base index 中刪除列，則如果 rollup index 中包含該列，也會被刪除

4.修改指定 index 的列類型以及列位置

ALTER TABLE db.table_name
MODIFY COLUMN column_name column_type [KEY | agg_type] [NULL | NOT NULL] [DEFAULT "default_value"]
[AFTER column_name|FIRST]
[FROM rollup_index_name]
[PROPERTIES ("key"="value", ...)]

-- 註意：
-- 聚合模型如果修改 value 列，需要指定 agg_type
-- 非聚合類型如果修改key列，需要指定KEY關鍵字
-- 分區列和分桶列不能做任何修改

5.對指定 index 的列進行重新排序

ALTER TABLE db.table_name
ORDER BY (column_name1, column_name2, ...)
[FROM rollup_index_name]
[PROPERTIES ("key"="value", ...)]

-- 註意：
-- index 中的所有列都要寫出來
-- value 列在 key 列之後

示例：

-- 1.向 example_rollup_index 的 col1 後添加一個key列 new_col(非聚合模型)
ALTER TABLE example_db.my_table
ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1
TO example_rollup_index;

-- 2.向example_rollup_index的col1後添加一個value列new_col(非聚合模型)
ALTER TABLE example_db.my_table  
ADD COLUMN new_col INT DEFAULT "0" AFTER col1 
TO example_rollup_index;

-- 3.向example_rollup_index的col1後添加一個key列new_col(聚合模型)
ALTER TABLE example_db.my_table   
ADD COLUMN new_col INT DEFAULT "0" AFTER col1    
TO example_rollup_index;

-- 4.向example_rollup_index的col1後添加一個value列new_col SUM聚合類型(聚合模型)
ALTER TABLE example_db.my_table   
ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1    
TO example_rollup_index;

-- 5.向 example_rollup_index 添加多列(聚合模型)
ALTER TABLE example_db.my_table
ADD COLUMN (col1 INT DEFAULT "1", col2 FLOAT SUM DEFAULT "2.3")
TO example_rollup_index;

-- 6.從 example_rollup_index 刪除一列
ALTER TABLE example_db.my_table
DROP COLUMN col2
FROM example_rollup_index;

-- 7.修改 base index 的 key 列 col1 的類型為 BIGINT，並移動到 col2 列後面。
ALTER TABLE example_db.my_table 
MODIFY COLUMN col1 BIGINT KEY DEFAULT "1" AFTER col2;

-- 註意：無論是修改 key 列還是 value 列都需要聲明完整的 column 信息

-- 8.修改 base index 的 val1 列最大長度。原 val1 為 (val1 VARCHAR(32) REPLACE DEFAULT "abc")
ALTER TABLE example_db.my_table 
MODIFY COLUMN val1 VARCHAR(64) REPLACE DEFAULT "abc";

-- 9.重新排序 example_rollup_index 中的列（設原列順序為：k1,k2,k3,v1,v2）
ALTER TABLE example_db.my_table
ORDER BY (k3,k1,k2,v2,v1)
FROM example_rollup_index;

-- 10.同時執行兩種操作
ALTER TABLE example_db.my_table
ADD COLUMN v2 INT MAX DEFAULT "0" AFTER k2 TO example_rollup_index,
ORDER BY (k3,k1,k2,v2,v1) FROM example_rollup_index;

查看作業

SHOW ALTER TABLE COLUMN 可以查看當前正在執行或已經完成的 Schema Change 作業。當一次 Schema Change 作業涉及到多個 Index 時，該命令會顯示多行，每行對應一個 Index

SHOW ALTER TABLE COLUMN\G;
*************************** 1. row ***************************
        JobId: 20021
    TableName: tbl1
   CreateTime: 2019-08-05 23:03:13
   FinishTime: 2019-08-05 23:03:42
    IndexName: tbl1
      IndexId: 20022
OriginIndexId: 20017
SchemaVersion: 2:792557838
TransactionId: 10023
        State: FINISHED
          Msg: 
     Progress: NULL
      Timeout: 86400
1 row in set (0.00 sec)

-- JobId：每個 Schema Change 作業的唯一 ID。
-- TableName：Schema Change 對應的基表的表名。
-- CreateTime：作業創建時間。
-- FinishedTime：作業結束時間。如未結束，則顯示 "N/A"。
-- IndexName： 本次修改所涉及的某一個 Index 的名稱。
-- IndexId：新的 Index 的唯一 ID。
-- OriginIndexId：舊的 Index 的唯一 ID。
-- SchemaVersion：以 M:N 的格式展示。其中 M 表示本次 Schema Change 變更的版本，N 表示對應的 Hash 值。每次 Schema Change，版本都會遞增。
-- TransactionId：轉換歷史數據的分水嶺 transaction ID。
-- State：作業所在階段。
-- 	PENDING：作業在隊列中等待被調度。
-- 	WAITING_TXN：等待分水嶺 transaction ID 之前的導入任務完成。
-- 	RUNNING：歷史數據轉換中。
-- 	FINISHED：作業成功。
-- 	CANCELLED：作業失敗。
-- Msg：如果作業失敗，這裡會顯示失敗信息。
-- Progress：作業進度。只有在 RUNNING 狀態才會顯示進度。進度是以 M/N 的形式顯示。其中 N 為 Schema Change 涉及的總副本數。M 為已完成歷史數據轉換的副本數。
-- Timeout：作業超時時間。單位秒。

取消作業

在作業狀態不為 FINISHED 或 CANCELLED 的情況下,可以通過以下命令取消Schema Change作業：

CANCEL ALTER TABLE COLUMN FROM tbl_name;

註意事項
• 一張表在同一時間只能有一個 Schema Change 作業在運行。
• Schema Change 操作不阻塞導入和查詢操作。
• 分區列和分桶列不能修改。
• 如果 Schema 中有 REPLACE 方式聚合的 value 列，則不允許刪除 Key 列。
• 如果刪除 Key 列，Doris 無法決定 REPLACE 列的取值。
• Unique 數據模型表的所有非 Key 列都是 REPLACE 聚合方式。
• 在新增聚合類型為 SUM 或者 REPLACE 的 value 列時，該列的預設值對歷史數據沒有含義。
• 因為歷史數據已經失去明細信息，所以預設值的取值並不能實際反映聚合後的取值。
• 當修改列類型時，除 Type 以外的欄位都需要按原列上的信息補全。
• 如修改列 k1 INT SUM NULL DEFAULT "1" 類型為 BIGINT，則需執行命令如下：
• ALTER TABLE tbl1 MODIFY COLUMN k1 BIGINT SUM NULL DEFAULT "1";
• 註意，除新的列類型外，如聚合方式，Nullable 屬性，以及預設值都要按照原信息補全。
• 不支持修改列名稱、聚合類型、Nullable 屬性、預設值以及列註釋。

partition的增減

-- 1.增加分區, 使用預設分桶方式：現有分區 \[MIN, 2013-01-01)，增加分區 \[2013-01-01, 2014-01-01)
ALTER TABLE example_db.my_table ADD PARTITION p1 VALUES LESS THAN ("2014-01-01");

-- 2.增加分區，使用新的分桶數
ALTER TABLE example_db.my_table ADD PARTITION p1 VALUES LESS THAN ("2015-01-01") 
DISTRIBUTED BY HASH(k1) BUCKETS 20; 

-- 3.增加分區，使用新的副本數 
ALTER TABLE example_db.my_table ADD PARTITION p1 VALUES LESS THAN ("2015-01-01") 
("replication_num"="1"); 

-- 4.修改分區副本數 
ALTER TABLE example_db.my_table MODIFY PARTITION p1 SET("replication_num"="1"); 
-- 5.批量修改指定分區
ALTER TABLE example_db.my_table MODIFY PARTITION (p1, p2, p4) SET("in_memory"="true"); 

-- 6.批量修改所有分區 
ALTER TABLE example_db.my_table MODIFY PARTITION (*) SET("storage_medium"="HDD"); 

-- 7.刪除分區 
ALTER TABLE example_db.my_table DROP PARTITION p1; 
-- 8.增加一個指定上下界的分區 
ALTER TABLE example_db.my_table ADD PARTITION p1 VALUES [("2014-01-01"), ("2014-02-01"));

rollup的增減

-- 1.創建 index: example_rollup_index，基於 base index（k1,k2,k3,v1,v2）。列式存儲。 
ALTER TABLE example_db.my_table ADD ROLLUP example_rollup_index(k1, k3, v1, v2);

-- 2.創建 index: example_rollup_index2，基於 example_rollup_index（k1,k3,v1,v2）
ALTER TABLE example_db.my_table ADD ROLLUP example_rollup_index2 (k1, v1) 
FROM example_rollup_index;

-- 3.創建 index: example_rollup_index3, 基於base index (k1,k2,k3,v1), 自定義rollup超時時間一小時
ALTER TABLE example_db.my_table ADD ROLLUP example_rollup_index(k1, k3, v1) 
PROPERTIES("timeout" = "3600"); 

-- 4.刪除 index: example_rollup_index2
ALTER TABLE example_db.my_table DROP ROLLUP example_rollup_index2;

動態分區和臨時分區

動態分區

原理

在某些使用場景下，用戶會將表按照天進行分區劃分，每天定時執行例行任務，這時需要使用方手動管理分區，否則可能由於使用方沒有創建分區導致數據導入失敗，這給使用方帶來了額外的維護成本。通過動態分區功能，用戶可以在建表時設定動態分區的規則。FE 會啟動一個後臺線程，根據用戶指定的規則創建或刪除分區。用戶也可以在運行時對現有規則進行變更。

使用方式

動態分區的規則可以在建表時指定，或者在運行時進行修改。當前僅支持對單分區列的分區表設定動態分區規則

-- 建表時指定
CREATE TABLE tbl1
(...)
PROPERTIES
(
-- 添加動態分區的規則
    "dynamic_partition.prop1" = "value1",
    "dynamic_partition.prop2" = "value2",
    ...
)

-- 運行時修改
ALTER TABLE tbl1 SET
(
    "dynamic_partition.prop1" = "value1",
    "dynamic_partition.prop2" = "value2",
    ...
)

動態分區規則參數

  1. dynamic_partition.enable：是否開啟動態分區特性。預設是true
  2. dynamic_partition.time_unit：動態分區調度的單位。可指定為 HOUR、DAY、WEEK、MONTH。分別表示按小時、按天、按星期、按月進行分區創建或刪除。
  3. dynamic_partition.time_zone：動態分區的時區，如果不填寫，則預設為當前機器的系統的時區
  4. dynamic_partition.start：動態分區的起始偏移，為負數。以當天（星期/月）為基準，分區範圍在此偏移之前的分區將會被刪除。如果不填寫，則預設為 -2147483648，即不刪除歷史分區。
  5. dynamic_partition.end：動態分區的結束偏移，為正數。根據 time_unit 屬性的不同，以當天（星期/月）為基準，提前創建對應範圍的分區。
  6. dynamic_partition.prefix：動態創建的分區名首碼。
  7. dynamic_partition.buckets：動態創建的分區所對應的分桶數量
  8. dynamic_partition.replication_num：動態創建的分區所對應的副本數量，如果不填寫，則預設為該表創建時指定的副本數量
  9. dynamic_partition.start_day_of_week：當 time_unit 為 WEEK 時，該參數用於指定每周的起始點。取值為 1 到 7。其中 1 表示周一，7 表示周日。預設為 1，即表示每周以周一為起始點。
  10. dynamic_partition.start_day_of_month：當 time_unit 為 MONTH 時，該參數用於指定每月的起始日期。取值為 1 到 28。其中 1 表示每月1號，28 表示每月28號。預設為 1，即表示每月以1號位起始點。暫不支持以29、30、31號為起始日，以避免因閏年或閏月帶來的歧義
  11. dynamic_partition.create_history_partition：為 true 時代表可以創建歷史分區，預設是false
  12. dynamic_partition.history_partition_num：當 create_history_partition 為 true 時，該參數用於指定創建歷史分區數量。預設值為 -1， 即未設置。
  13. dynamic_partition.hot_partition_num：指定最新的多少個分區為熱分區。對於熱分區，系統會自動設置其 storage_medium 參數為SSD，並且設置 storage_cooldown_time 。hot_partition_num：設置往前 n 天和未來所有分區為熱分區，並自動設置冷卻時間
  14. dynamic_partition.reserved_history_periods：需要保留的歷史分區的時間範圍。

修改動態分區屬性

ALTER TABLE tbl1 SET
(
    "dynamic_partition.prop1" = "value1",
    ...
);


ALTER TABLE partition_test SET
(
    "dynamic_partition.time_unit" = "week",
    "dynamic_partition.start" = "-1",
    "dynamic_partition.end" = "1"
);

某些屬性的修改可能會產生衝突。假設之前分區粒度為 DAY，並且已經創建瞭如下分區：

p20200519: ["2020-05-19", "2020-05-20")
p20200520: ["2020-05-20", "2020-05-21")
p20200521: ["2020-05-21", "2020-05-22")

如果此時將分區粒度改為 MONTH，則系統會嘗試創建範圍為 ["2020-05-01", "2020-06-01") 的分區，而該分區的分區範圍和已有分區衝突，所以無法創建。而範圍為 ["2020-06-01", "2020-07-01") 的分區可以正常創建。因此，2020-05-22 到 2020-05-30 時間段的分區，需要自行填補。

查看動態分區表調度情況

-- 通過以下命令可以進一步查看當前資料庫下，所有動態分區表的調度情況：
SHOW DYNAMIC PARTITION TABLES;

-- LastUpdateTime: 最後一次修改動態分區屬性的時間
-- LastSchedulerTime: 最後一次執行動態分區調度的時間
-- State: 最後一次執行動態分區調度的狀態
-- LastCreatePartitionMsg: 最後一次執行動態添加分區調度的錯誤信息
-- LastDropPartitionMsg: 最後一次執行動態刪除分區調度的錯誤信息

臨時分區

規則

• 臨時分區的分區列和正式分區相同，且不可修改。
• 一張表所有臨時分區之間的分區範圍不可重疊，但臨時分區的範圍和正式分區範圍可以重疊。
• 臨時分區的分區名稱不能和正式分區以及其他臨時分區重覆。

操作

臨時分區支持添加、刪除、替換操作。

添加臨時分區

可以通過 ALTER TABLE ADD TEMPORARY PARTITION 語句對一個表添加臨時分區：

ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp1 VALUES LESS THAN("2020-02-01");


ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp2 VALUES LESS THAN("2020-02-02")
("in_memory" = "true", "replication_num" = "1")
DISTRIBUTED BY HASH(k1) BUCKETS 5;


ALTER TABLE tbl3 ADD TEMPORARY PARTITION tp1 VALUES IN ("Beijing", "Shanghai");

ALTER TABLE tbl3 ADD TEMPORARY PARTITION tp1 VALUES IN ("Beijing", "Shanghai")
("in_memory" = "true", "replication_num" = "1")
DISTRIBUTED BY HASH(k1) BUCKETS 5;


-- 添加操作的一些說明：
-- 臨時分區的添加和正式分區的添加操作相似。臨時分區的分區範圍獨立於正式分區。
-- 臨時分區可以獨立指定一些屬性。包括分桶數、副本數、是否是記憶體表、存儲介質等信息。

刪除臨時分區

-- 可以通過 ALTER TABLE DROP TEMPORARY PARTITION 語句刪除一個表的臨時分區：
ALTER TABLE tbl1 DROP TEMPORARY PARTITION tp1;
-- 刪除臨時分區，不影響正式分區的數據。

替換分區

可以通過 ALTER TABLE REPLACE PARTITION 語句將一個表的正式分區替換為臨時分區。

-- 正式分區替換成臨時分區以後，正是分區的數據會被刪除，並且這個過程是不可逆的
-- 用之前要小心
ALTER TABLE tbl1 REPLACE PARTITION (p1) WITH TEMPORARY PARTITION (tp1);

ALTER TABLE partition_test REPLACE PARTITION (p20230104) WITH TEMPORARY PARTITION (tp1);

ALTER TABLE tbl1 REPLACE PARTITION (p1, p2) WITH TEMPORARY PARTITION (tp1, tp2)
PROPERTIES (
    "strict_range" = "false",
    "use_temp_partition_name" = "true"
);

-- strict_range：預設為 true。
-- 	對於 Range 分區，當該參數為 true 時，表示要被替換的所有正式分區的範圍並集需要和替換的臨時分區的範圍並集完全相同。當置為 false 時，只需要保證替換後，新的正式分區間的範圍不重疊即可。
-- 	對於 List 分區，該參數恆為 true。要被替換的所有正式分區的枚舉值必須和替換的臨時分區枚舉值完全相同。
-- use_temp_partition_name：預設為 false。當該參數為 false，並且待替換的分區和替換分區的個數相同時，則替換後的正式分區名稱維持不變。如果為 true，則替換後，正式分區的名稱為替換分區的名稱。


LTER TABLE tbl1 REPLACE PARTITION (p1) WITH TEMPORARY PARTITION (tp1);
-- use_temp_partition_name 預設為 false，則在替換後，分區的名稱依然為 p1，但是相關的數據和屬性都替換為 tp1 的。 如果 use_temp_partition_name 預設為 true，則在替換後，分區的名稱為 tp1。p1 分區不再存在。

ALTER TABLE tbl1 REPLACE PARTITION (p1, p2) WITH TEMPORARY PARTITION (tp1);
-- use_temp_partition_name 預設為 false，但因為待替換分區的個數和替換分區的個數不同，則該參數無效。替換後，分區名稱為 tp1，p1 和 p2 不再存在。


-- 替換操作的一些說明：
-- 分區替換成功後，被替換的分區將被刪除且不可恢復。

數據的導入和查詢

導入臨時分區

根據導入方式的不同，指定導入臨時分區的語法稍有差別。這裡通過示例進行簡單說明

-- 查詢結果用insert導入
INSERT INTO tbl TEMPORARY PARTITION(tp1, tp2, ...) SELECT ....

-- 查看數據
SELECT ... FROM
tbl1 TEMPORARY PARTITION(tp1, tp2, ...)
JOIN
tbl2 TEMPORARY PARTITION(tp1, tp2, ...)
ON ...
WHERE ...;

doris中join的優化原理

Shuffle Join（Partitioned Join）

和mr中的shuffle過程是一樣的,針對每個節點上的數據進行shuffle，相同數據分發到下游的節點上的join方式叫shuffle join

-- 訂單表
CREATE TABLE  test.order_info_shuffle
(
 `order_id` varchar(20) COMMENT "訂單id",
 `user_id` varchar(20) COMMENT "用戶id",
 `goods_id` VARCHAR(20) COMMENT "商品id",
 `goods_num` Int COMMENT "商品數量",
 `price` double COMMENT "商品價格"
)
duplicate KEY(`order_id`)
DISTRIBUTED BY HASH(`order_id`) BUCKETS 5;

-- 導入數據：
insert into test.order_info_shuffle values\
('o001','u001','g001',1,9.9 ),\
('o001','u001','g002',2,19.9),\
('o001','u001','g003',2,39.9),\
('o002','u002','g001',3,9.9 ),\
('o002','u002','g002',1,19.9),\
('o003','u002','g003',1,39.9),\
('o003','u002','g002',2,19.9),\
('o003','u002','g004',3,99.9),\
('o003','u002','g005',1,99.9),\
('o004','u003','g001',2,9.9 ),\
('o004','u003','g002',1,19.9),\
('o004','u003','g003',4,39.9),\
('o004','u003','g004',1,99.9),\
('o004','u003','g005',4,89.9);


-- 商品表
CREATE TABLE  test.goods_shuffle
(
 `goods_id` VARCHAR(20) COMMENT "商品id",
 `goods_name`  VARCHAR(20) COMMENT "商品名稱",
 `category_id` VARCHAR(20) COMMENT "商品品類id"
)
duplicate KEY(`goods_id`)
DISTRIBUTED BY HASH(`goods_id`) BUCKETS 5;

-- 導入數據：
insert into test.goods_shuffle values\
('g001','iphon13','c001'),\
('g002','ipad','c002'),\
('g003','xiaomi12','c001'),\
('g004','huaweip40','c001'),\
('g005','headset','c003');


-- sql示例
EXPLAIN 
select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_shuffle as oi
-- 我們可以不指定哪一種join方式，doris會自己根據數據的實際情況幫我們選擇
JOIN goods_shuffle as gs
on oi.goods_id = gs.goods_id;

EXPLAIN select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_shuffle as oi
-- 可以顯式的hint指定我們想要的join類型
JOIN [broadcast] goods_shuffle as gs
on oi.goods_id = gs.goods_id;

適用場景：不管數據量，不管是大表join大表還是大表join小表都可以用
優點：通用
缺點：需要shuffle記憶體和網路開銷比較大，效率不高

Broadcast Join

當一個大表join小表的時候，將小表廣播到每一個大表所在的每一個節點上(以hash表的形式放在記憶體中)這樣的方式叫做Broadcast Join，類似於mr裡面的一個map端join

-- 顯式使用 Broadcast Join：
EXPLAIN 
select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_broadcast as oi
JOIN [broadcast] goods_broadcast as gs
on oi.goods_id = gs.goods_id;

適用場景：
左表join右表，要求左表的數據量相對來說比較大，右表數據量比較小
優點：避免了shuffle，提高了運算效率
缺點：有限制，必須右表數據量比較小

Bucket Shuffle Join

利用建表時候分桶的特性，當join的時候，join的條件和左表的分桶欄位一樣的時候，將右表按照左表分桶的規則進行shuffle操作，使右表中需要join的數據落在左表中需要join數據的BE節點上的join方式叫做Bucket Shuffle Join。

-- 從 0.14 版本開始預設為 true，新版本可以不用設置這個參數了！
show variables like '%bucket_shuffle_join%'; 
set enable_bucket_shuffle_join = true;

-- 通過 explain 查看 join 類型
EXPLAIN 
select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_bucket as oi
-- 目前 Bucket Shuffle Join不能像Shuffle Join那樣可以顯示指定Join方式，
-- 只能讓執行引擎自動選擇，
-- 選擇的順序：Colocate Join -> Bucket Shuffle Join -> Broadcast Join -> Shuffle Join。
JOIN goods_bucket as gs
where oi.goods_id = gs.goods_id;




EXPLAIN select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_bucket as oi
-- 目前 Bucket Shuffle Join不能像Shuffle Join那樣可以顯示指定Join方式，
-- 只能讓執行引擎自動選擇，
-- 選擇的順序：Colocate Join -> Bucket Shuffle Join -> Broadcast Join -> Shuffle Join。
JOIN goods_bucket1 as gs
where oi.goods_id = gs.goods_id;

-- 註意事項:
-- Bucket Shuffle Join 只生效於 Join 條件為等值的場景
-- Bucket Shuffle Join 要求左表的分桶列的類型與右表等值 join 列的類型需要保持一致，否則無法進行對應的規劃。 
-- Bucket Shuffle Join 只作用於 Doris 原生的 OLAP 表，對於 ODBC，MySQL，ES 等外表，當其作為左表時是無法規劃生效的。 
-- Bucket Shuffle Join只能保證左表為單分區時生效。所以在 SQL 執行之中，需要儘量使用 where 條件使分區裁剪的策略能夠生效。

Colocation Join

中文意思叫位置協同分組join，指需要join的兩份數據都在同一個BE節點上，這樣在join的時候，直接本地join計算即可，不需要進行shuffle。

名詞解釋
• Colocation Group（位置協同組CG）：在同一個 CG內的 Table 有著相同的 Colocation Group Schema，並且有著相同的數據分片分佈(滿足三個條件)。
• Colocation Group Schema（CGS）：用於描述一個 CG 中的 Table，和 Colocation 相關的通用 Schema 信息。包括分桶列類型，分桶數以及分區的副本數等。

使用限制

  1. 建表時兩張表的分桶列的類型和數量需要完全一致，並且桶數一致，才能保證多張表的數據分片能夠一一對應的進行分佈控制。
  2. 同一個 CG 內所有表的所有分區（Partition）的副本數必須一致。如果不一致，可能出現某一個Tablet 的某一個副本，在同一個 BE 上沒有其他的表分片的副本對應
  3. 同一個 CG 內的表，分區的個數、範圍以及分區列的類型不要求一致。

使用案例

-- 建兩張表，分桶列都為 int 類型，且桶的個數都是 5 個。副本數都為預設副本數

-- 編寫查詢語句，並查看執行計劃
EXPLAIN 
select 
oi.order_id,
oi.user_id,
oi.goods_id,
gs.goods_name,
gs.category_id,
oi.goods_num,
oi.price
from order_info_colocation as oi
-- 目前 Colocation Join不能像Shuffle Join那樣可以顯示指定Join方式，
-- 只能讓執行引擎自動選擇，
-- 選擇的順序：Colocate Join -> Bucket Shuffle Join -> Broadcast Join -> Shuffle Join。
JOIN goods_colocation as gs
where oi.goods_id = gs.goods_id;


-- 查看 Group
SHOW PROC '/colocation_group';

-- 當 Group 中最後一張表徹底刪除後（徹底刪除是指從回收站中刪除。通常，一張表通過DROP TABLE 命令刪除後，會在回收站預設停留一天的時間後，再刪除），該 Group 也會被自動刪除。
-- 修改表 Colocate Group 屬性
ALTER TABLE tbl SET ("colocate_with" = "group2");
-- 如果被修改的表原來有group，那麼會直接將原來的group刪除後創建新的group, 如果原來沒有組，就直接創建

-- 刪除表的 Colocation 屬性
ALTER TABLE tbl SET ("colocate_with" = ""); 
-- 當對一個具有 Colocation 屬性的表進行增加分區（ADD PARTITION）、修改副本數時,Doris 會檢查修改是否會違反 Colocation Group Schema，如果違反則會拒絕

Runtime Filter

Runtime Filter會在有join動作的 sql運行時，創建一個HashJoinNode和一個ScanNode來對join的數據進行過濾優化，使得join的時候數據量變少，從而提高效率

使用

-- 指定 RuntimeFilter 類型 
set runtime_filter_type="BLOOM_FILTER,IN,MIN_MAX";

set runtime_filter_type="MIN_MAX";

參數解釋：

  • runtime_filter_type: 包括Bloom Filter、MinMax Filter、IN predicate、IN Or Bloom Filter
    • Bloom Filter: 針對右表中的join欄位的所有數據標註在一個布隆過濾器中，從而判斷左表中需要join的數據在還是不在
    • MinMax Filter: 獲取到右表表中數據的最大值和最小值，看左表中查看，將超出這個最大值最小值範圍的數據過濾掉
    • IN predicate: 將右表中需要join欄位所有數據構建一個IN predicate，再去左表表中過濾無意義數據
  • runtime_filter_wait_time_ms: 左表的ScanNode等待每個Runtime Filter的時間，預設1000ms
  • runtime_filters_max_num: 每個查詢可應用的Runtime Filter中Bloom Filter的最大數量，預設10
  • runtime_bloom_filter_min_size: Runtime Filter中Bloom Filter的最小長度，預設1M
  • runtime_bloom_filter_max_size: Runtime Filter中Bloom Filter的最大長度，預設16M
  • runtime_bloom_filter_size: Runtime Filter中Bloom Filter的預設長度，預設2M
  • runtime_filter_max_in_num: 如果join右表數據行數大於這個值，我們將不生成IN predicate，預設102400

示例

-- 建表
CREATE TABLE test (t1 INT) DISTRIBUTED BY HASH (t1) BUCKETS 2  
PROPERTIES("replication_num" = "1"); 

INSERT INTO test VALUES (1), (2), (3), (4); 

CREATE TABLE test2 (t2 INT) DISTRIBUTED BY HASH (t2) BUCKETS 2  
PROPERTIES("replication_num" = "1"); 

INSERT INTO test2 VALUES (3), (4), (5); 

-- 查看執行計劃
set runtime_filter_type="BLOOM_FILTER,IN,MIN_MAX";

EXPLAIN SELECT t1 FROM test JOIN test2 where test.t1 = test2.t2;

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 開源.NetCore通用工具庫Xmtool使用連載 - 發送簡訊篇
    1. 透過現象看本質 反射被譽為是 c#中的黑科技 ，在很多領域中都有反射的身影，例如，我們經常使用的ORM框架，ABP框架 等。 反射指程式可以訪問、檢測和修改它本身狀態或行為的一種能力。. 程式集包含模塊，而模塊包含類型，類型又包含成員。. 反射則提供了封裝程式集、模塊和類型的對象。. 您可以使 ...
  • 第一單元 單元測試
    1. 為什麼需要單元測試 在我們之前，測試某些功能是否能夠正常運行時，我們都將代碼寫到Main方法中，當我們測試第二個功能時，我們只能選擇將之前的代碼清掉，重新編寫。此時，如果你還想重新測試你之前的功能時，這時你就顯得有些難為情了，因為代碼都被你清掉了。當然你完全可以把代碼寫到一個記事本中進行記錄， ...
  • Ado.Net 資料庫訪問技術(.Net 6版本)
    1. ADO.NET的前世今生 ADO.NET的名稱起源於ADO（ActiveX Data Objects），是一個COM組件庫，用於在以往的Microsoft技術中訪問數據。之所以使用ADO.NET名稱，是因為Microsoft希望表明，這是在NET編程環境中優先使用的數據訪問介面。 ADO.NE ...
  • 在 Net Core 開發中如何解決 Cannot access a disposed object 這個問題
    ### 前言 當我們編寫 C# 代碼時，經常需要處理大量的數據集合。在傳統的方式中，我們往往需要先將整個數據集合載入到記憶體中，然後再進行操作。但是如果數據集合非常大，這種方式就會導致記憶體占用過高，甚至可能導致程式崩潰。 C# 中的`yield return`機制可以幫助我們解決這個問題。通過使用`y ...
  • Spectre.Console-實現自己的CLI
    ## 引言 最近發現自己喜歡用的 Todo 軟體總是差點意思，畢竟每個人的習慣和工作流不太一樣，我就想著自己寫一個小的[Todo 項目]( https://github.com/circler3/TodoTrack )，核心的功能是自動記錄 Todo 執行過程中消耗的時間（尤其面向程式員），按照自己 ...
  • 操作系統教程1簡介
    目錄 一、函數 1.傳參 二、返回值 return 三、終止符 exit 四、實驗 一、函數 概念：函數是定義一個函數名，可以調用函數方法，完成便捷處理。 函數作用： 1.避免方法名重覆。 2.將代碼分割成一塊一塊，便於查看。 傳參：內部傳參 外部傳參 混合外部內部傳參混合外部內部傳參 例子演示： ...
  • 掌握嵌入式Linux編程1開始
    Linux技術文檔 1.說明 研究思路：從Linux外部向內部研究，從外部工具到系統內核深入的研究思路。本文檔主要針對外部工具進行實驗。並賦予實驗結論。 2.系統內核初識 1.系統記憶體管理：管理物理記憶體與虛擬記憶體（內核通過硬碟上的存儲空間來時間虛擬記憶體，這塊區域稱為交換空間） 2.軟體程式管理：in ...
  • STM32-RTC
    RTC簡介： RTC是一個獨立的定時器，它可以連續計數和提供了時鐘日曆功能。使用BKP寄存器存儲具有掉電保存功能 存粹的計時的， 觸發中斷 ——鬧鐘中斷，用來產生一個可編程的鬧鐘中斷。 ——秒中斷，用來產生一個可編程的周期性中斷信號(最快1秒，最慢1秒，只能1秒) ——溢出中斷，指示內部可編程計數器 ...
一周排行
    -Advertisement-
    Play Games
  • 如何基於surging跨網關跨語言進行緩存降級
    基於.NET Framework 4.8 開發的深度學習模型部署測試平臺，提供了YOLO框架的主流系列模型，包括YOLOv8~v9，以及其系列下的Det、Seg、Pose、Obb、Cls等應用場景，同時支持圖像與視頻檢測。模型部署引擎使用的是OpenVINO™、TensorRT、ONNX runti... ...
  • 十年沉澱，重啟開發之路
    十年沉澱，重啟開發之路 十年前，我沉浸在開發的海洋中，每日與代碼為伍，與演算法共舞。那時的我，滿懷激情，對技術的追求近乎狂熱。然而，隨著歲月的流逝，生活的忙碌逐漸占據了我的大部分時間，讓我無暇顧及技術的沉澱與積累。 十年間，我經歷了職業生涯的起伏和變遷。從初出茅廬的菜鳥到逐漸嶄露頭角的開發者，我見證了 ...
  • C#.NET體系圖文概述—2024最全總結
    C# 是一種簡單、現代、面向對象和類型安全的編程語言。.NET 是由 Microsoft 創建的開發平臺，平臺包含了語言規範、工具、運行，支持開發各種應用，如Web、移動、桌面等。.NET框架有多個實現，如.NET Framework、.NET Core（及後續的.NET 5+版本），以及社區版本M... ...
  • 班級擂臺（光榮）榜 - 極簡教育小工具
    前言 本文介紹瞭如何使用三菱提供的MX Component插件實現對三菱PLC軟元件數據的讀寫，記錄了使用電腦模擬，模擬PLC，直至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1. PLC開發編程環境GX Works2，GX Works2下載鏈接 https:// ...
  • web server apache tomcat11-31-websocket
    前言 整理這個官方翻譯的系列，原因是網上大部分的 tomcat 版本比較舊，此版本為 v11 最新的版本。 開源項目 從零手寫實現 tomcat minicat 別稱【嗅虎】心有猛虎，輕嗅薔薇。 系列文章 web server apache tomcat11-01-官方文檔入門介紹 web serv ...
  • day31-jQuery
    1、jQuery介紹 jQuery是什麼 jQuery是一個快速、簡潔的JavaScript框架，是繼Prototype之後又一個優秀的JavaScript代碼庫（或JavaScript框架）。jQuery設計的宗旨是“write Less，Do More”，即倡導寫更少的代碼，做更多的事情。它封裝 ...
  • aardio爬蟲) 實戰篇：逆向有道翻譯web介面
    前言 之前的文章把js引擎(aardio封裝庫) 微軟開源的js引擎(ChakraCore))寫好了，這篇文章整點js代碼來測一下bug。測試網站：https://fanyi.youdao.com/index.html#/ 逆向思路 逆向思路可以看有道翻譯js逆向（MD5加密，AES加密）附完整源碼 ...
  • 面試官：這就是你理解的Java多線程基礎？
    引言 現代的操作系統（Windows，Linux，Mac OS）等都可以同時打開多個軟體（任務），這些軟體在我們的感知上是同時運行的，例如我們可以一邊瀏覽網頁，一邊聽音樂。而CPU執行代碼同一時間只能執行一條，但即使我們的電腦是單核CPU也可以同時運行多個任務，如下圖所示，這是因為我們的 CPU 的 ...
  • Python文本統計與分析從基礎到進階
    掌握使用Python進行文本英文統計的基本方法，並瞭解如何進一步優化和擴展這些方法，以應對更複雜的文本分析任務。 ...
  • Redis多數據源，看這篇就夠了
    背景 Redis多數據源常見的場景： 分區數據處理：當數據量增長時，單個Redis實例可能無法處理所有的數據。通過使用多個Redis數據源，可以將數據分區存儲在不同的實例中，使得數據處理更加高效。 多租戶應用程式：對於多租戶應用程式，每個租戶可以擁有自己的Redis數據源，以確保數據隔離和安全性。 ...
所有分類