Doris(七) -- 修改表、动态和临时分区、join的优化
修改表
修改表名
-- 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",
...
)
动态分区规则参数
- dynamic_partition.enable：是否开启动态分区特性。默认是true
- dynamic_partition.time_unit：动态分区调度的单位。可指定为 HOUR、DAY、WEEK、MONTH。分别表示按小时、按天、按星期、按月进行分区创建或删除。
- dynamic_partition.time_zone：动态分区的时区，如果不填写，则默认为当前机器的系统的时区
- dynamic_partition.start：动态分区的起始偏移，为负数。以当天（星期/月）为基准，分区范围在此偏移之前的分区将会被删除。如果不填写，则默认为 -2147483648，即不删除历史分区。
- dynamic_partition.end：动态分区的结束偏移，为正数。根据 time_unit 属性的不同，以当天（星期/月）为基准，提前创建对应范围的分区。
- dynamic_partition.prefix：动态创建的分区名前缀。
- dynamic_partition.buckets：动态创建的分区所对应的分桶数量
- dynamic_partition.replication_num：动态创建的分区所对应的副本数量，如果不填写，则默认为该表创建时指定的副本数量
- dynamic_partition.start_day_of_week：当 time_unit 为 WEEK 时，该参数用于指定每周的起始点。取值为 1 到 7。其中 1 表示周一，7 表示周日。默认为 1，即表示每周以周一为起始点。
- dynamic_partition.start_day_of_month：当 time_unit 为 MONTH 时，该参数用于指定每月的起始日期。取值为 1 到 28。其中 1 表示每月1号，28 表示每月28号。默认为 1，即表示每月以1号位起始点。暂不支持以29、30、31号为起始日，以避免因闰年或闰月带来的歧义
- dynamic_partition.create_history_partition：为 true 时代表可以创建历史分区，默认是false
- dynamic_partition.history_partition_num：当 create_history_partition 为 true 时，该参数用于指定创建历史分区数量。默认值为 -1， 即未设置。
- dynamic_partition.hot_partition_num：指定最新的多少个分区为热分区。对于热分区，系统会自动设置其 storage_medium 参数为SSD，并且设置 storage_cooldown_time 。hot_partition_num：设置往前 n 天和未来所有分区为热分区，并自动设置冷却时间
- 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 信息。包括分桶列类型，分桶数以及分区的副本数等。
使用限制
- 建表时两张表的分桶列的类型和数量需要完全一致，并且桶数一致，才能保证多张表的数据分片能够一一对应的进行分布控制。
- 同一个 CG 内所有表的所有分区（Partition）的副本数必须一致。如果不一致，可能出现某一个Tablet 的某一个副本，在同一个 BE 上没有其他的表分片的副本对应
- 同一个 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;