存储引擎规则
1.数据要根据用户指定的分区列(只能是数字或日期类型)划分成若干个分区(patition)
2.在每个分区内,数据还可以根据用户指定的分桶列进行hash分桶,每个分桶就是一数据片段(tablat),tablat是数据划分的最小逻辑单元。
3.patition可视为逻辑上最小的管理单元,数据的导入导出都可以或仅能针对一个patition进行。
4.tablet直接的数据是没有交集的(不会重复存储),独立存储,tablet是数据移动,复制等操作的最小物理存储单元
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VxjTCsqz-1679125901401)(images/image-20230305213646669.png)]
Doris SQL
建表语句
doris的表分为单分区(只指定bucket)和多分区(同时指定bucket和patition)
单分区表
doris的单分区表指只指定分桶bucket的表
示例:创建一个名为table1的表,分桶为user_id,桶数为10。
create table table1(
`user_id` bigint comment '用户id', -- Key列
`username` varchar(32) default '' comment '用户名', -- Key列
`visit_page` varchar(512) comment '访问页面', -- Key列
`pv` bigint sum default '0' -- value列,注意sum表示聚合,当key列相同时,value列会按照指定的聚合函数进行统计
)
aggregate key(username,visit_page) -- 指定 聚合列
distributed by hash(bigint) buckets 10
properties("replication_num" = "1"); -- 指定其他属性 设置部分数为1(默认3分区)
-- 执行下面语句后,最终表内只有2条数据:
-- 1, zs, /page1, 3 #2条数据key相同,value合并=3
-- 2, zs, /page1, 1
insert into table1 values(1,'zs','/page1',1);
insert into table1 values(1,'zs','/page1',2);
insert into table1 values(2,'zs','/page1',1);
create table table1(
`user_id` bigint comment '用户id', -- Key列
`username` varchar(32) default '' comment '用户名', -- Key列
`visit_page` varchar(512) comment '访问页面', -- Key列
`pv` bigint sum default '0' -- value列,注意sum表示聚合,当key列相同时,value列会按照指定的聚合函数进行统计
)
duplicate key(username,visit_page) -- 指定 可重复主键列
distributed by hash(bigint) buckets 10
properties("replication_num" = "1"); -- 指定其他属性 设置部分数为1(默认3分区)
复合分区表
doris的复合分区表值同时指定分桶buckets和分区的表
注意:
- 分区列必须为value列,
- 分区的命名规范必须是首字母开头后可跟字母下划线美元符号
- 建表时分区必须现在分桶前面
#创建range分区
create table score(
sid int comment '学生id',
cid int comment '课程id',
exam_date date comment '考试时间',
score int replace comment '成绩',
exam_do_date date replace comment '学生考试时间',
exam_count int sum default '0' comment '考试次数'
)
aggregate key(sid,cid,exam_date)
PARTITION by range(exam_date)
(
partition `p2023_01` values less than ('2023-02-01'),
partition `p2023_02` values less than ('2023-03-01'),
partition `p2023_03` values less than ('2023-04-01')
)
distributed by hash(sid) buckets 1
properties(
"replication_num" = "1"
);
#创建list分区
create table score(
sid int comment '学生id',
cname int comment '课程名称',
exam_date date comment '考试时间',
score int replace comment '成绩',
exam_do_date date replace comment '学生考试时间',
exam_count int sum default '0' comment '考试次数'
)
aggregate key(sid,cname,exam_date)
PARTITION by range(cname)
(
partition `pchanese` values in ('语文'),
partition `pmath` values in ('数学'),
partition `penglish` values in ('英语')
)
distributed by hash(sid) buckets 1
properties(
"replication_num" = "1"
);
临时分区
在 0.12 版本中,Doris 支持了临时分区功能,主要功能是在建表后为表创建新的分区。
- 临时分区的分区列和正式分区相同,且不可修改。
- 一张表所有临时分区之间的分区范围不可重叠,但临时分区的范围和正式分区范围可以重叠。
- 临时分区的分区名称不能和正式分区以及其他临时分区重复。
-- 创建临时分区
ALTER TABLE tbl1 ADD TEMPORARY PARTITION tp1 VALUES LESS THAN("2020-02-01");
-- 删除临时分区
ALTER TABLE tbl1 DROP TEMPORARY PARTITION tp1;
-- 替换分区 可以将临时分区或正式分区替换为新的分区
ALTER TABLE tbl1 REPLACE PARTITION (p1) WITH TEMPORARY PARTITION (tp1);
动态分区
doris除了手动指定分区,还可以动态的指定分区 DynamicPatition。
动态分区是doris0.12引入的新功能,目的是实现动态添加分区和动态删除分区的功能。
在某些场景下,用户会将表按照天进行划分,每天执行任务,这时就可以通过动态分区的方式来自动创建分区。
-- 创建一个只保留7天前的分区,并预先保留未开3天分区数据的分区
create table test1(
id bihint,
name varchar(255),
money double,
date date
)
duplicate key(id,date)
patition by range(time)() -- 指定动态分区列 只能使用range分区函数
distributed by hash(id) buckets 10,
properties(
"dynamic_patition.enable"="true", -- 开启动态分区
"dynamic_patition.time_unit" = "DAY", -- 按天创建分区
"dynamic_patition.start" = '-7', -- 自动删除7天前的分区 不指定则不删除历史分区
"dynamic_patition.end" = "3", -- 只允许插入3天后的数据
"dynamic_patition.prefix" = "p", -- 指定动态分区的前缀
"dynamic_patition.buckets" = "10", -- 指定每个分区的副本数
"replication_num" = "1"
)
-- 查看表的所有分区
show patitions from tablename;
-- 查看开启动态分区的表
show dynamic patition tables;
数据插入
#语法 insert into table_name [partition_name] [with label label_name];
insert into table1(name,age) values ('zs',1);
insert into table1(name,age) with label label1 values('zs',2);
insert into table1(name,age) select name,age from db2.test2;
insert into table1(name,age) partition1 with label label2 select name,age from db2.test2;
数据删除
#语法 delete from table_name [partition_name] where id >10;
# 注意多分区表必须指定partition_name
# where 后面必须跟条件,否则无法执行
# where 后面不能跟or
# where后的字段必须是key列
列的增减和查询
#查询表字段信息
desc table_name;
#新增 value列
alter table table_name add column age int default '0' after name;
#新增 key列
alter table table_name add pv age int sum default '0' after age;
#删除列
alter table table_name drop column age;
#查询所有字段操作记录
show alter table table_name;
#当alter table column还未执行成功时可以取消操作
cancel alter table column from table_name;
归纳表 Rollup
doris 提供了Rollup(归纳表)的功能来解决宽表查询效率低下的问题,我们可以指定部分常用的字段来创建rollup,查询时doris会根据sql来确定是否使用rollup。
rollup不是视图,而是真正的指定的字段创建了新的表
#查询已创建的rollup
show alter table rollup from table_name;
#创建rollup
alter table table_name add rollup rollup_name(name,age);
#当alter table rollup还未生效时可以取消
cancel alter table rollup from table_name;
#查看sql是否命中rollup表,当出现 rollup rollup_name时表示命中rollup表
explain select name,sum(age) from table_name;
#删除rollup
alter table table_name drop rollup rollup_name;
物化视图 Materialized View
物化视图类似于mysql中视图,区别在于物化视图是对数据预先存储的表,会对数据进行拷贝形成新的表的。
物化视图的数据会随着原表的数据更新而更新,查询时也会自动匹配最优的物化视图。
物化视图和rollup表的区别:
rollup只能选择表内的字段来生成新的表,无法进行聚合。物化视图可以根据sql语句来创建视图表。
物化视图是rollup的超集,rollup表的功能都能通过 materialized view实现。
-- 创建 物化视图
create materialized view mv_1 as select a.id,a.name,sum(b.age) from table1 a join table2 b on a.id = b.id group by a.id,a.name;
-- 查看表所有的物化视图
desc table1 all;
-- 可以通过explain查看是否使用到物化视图
explain select a.name,sum(b.age) from table1;
-- 删除物化视图
drop materialized view vm_1 on table1;
-- 查看物化视图是否构建完成
show alter table materialized view from table1;
物化视图局限性:
- 物化视图的聚合函数只支持单列,如 sum(a+b)多列的情况就不支持
- delete数据是where后面跟的列在物化视图不存在时,无法对原表的数据进行删除。如果一定要删除只能先吧物化视图删除。
- 当一张表的物化视图操作10张后会影响增删改的效率,因为在增删改时也要同时维护物化视图的数据。
- unique key数据模型的表在建立物化视图是无法使用聚合函数,只能对列的顺序进行更改(相当于rollup表)
Join 查询
doris的join查询主要分为三种:
broadcast join:将小表现进行过滤,然后将过滤后的数据发送到所有大表的节点形成内存hash表进行join。
shuffle join:将大表和小表的join字段进行hash操作,确定小表的数据对应大表的服务器,可以减少数据传输。
colocation join:创建组归纳表以实现join操作在本地即可完成,无需数据传输。性能最高
FE在分布式查询时的选择顺序: colocation join => bucket shuffle join => broadcast join => suffle join
broadcast join
broadcast join(广播模式) 是系统默认的join方式,是将小表先进行条件过滤后,将过滤后的数据广播传输到各个节点上,形成一个内存hash表,然后流式读取大表的数据进行hash join。
doris在join查询时会预估小表的大小,如果数据过大时并满足shuffle join条件时doris会自动尝试切换为shuffle join。
如果显示的指定了broadcast join doris依然会自动切换为shuffle join
-- 不指定默认broadcast join
select * from student s
inner join score sc on s.sid = sc.sid
-- 显示指定 broadcast join
select * from student s
inner join [broadcast] score sc on s.sid = sc.sid
shuffle join
如果当小表过滤后的数据量过大无法放入内存的话,broadcast join将无法完成,通常会报内存超限,可显示指定 shuffle join(也叫partition join) 。即将小表和大表的join列都进行hash操作,然后根据hash匹配的方式将不同数据分发到不同服务器进行分布式计算,可以减少内存的消耗。
-- 指定 shuffle join
select sum(name) from table1 join table2 [shuffle] on table1.id = table2.id;
bucket shuffle join
doris 0.14引入的功能,是对shuffle join 的优化。当进行 shuffle join 的2个表的某一列是分桶列,doris会将shuffle join 升级为 bucket shuffle join。
相对于shuffle join,bucket shuffle join减少了内存消耗。
-- shuffle join的某个字段是分桶列时会自动升级为bucket shuffle join
select sum(name) from table1 join table2 [shuffle] on table1.id = table2.id;
colocation join
colocation join 是doris0.9版本引入的新功能。旨在为某些joint查询提供本地性能优化来减少数据在节点传输耗时,提高查询速度。
colocation join 是将多个表归纳到一个组内,并保证表内数据分片会落在同一BE节点上,保证在进行join时无需对数据进行传输,直接在本地进行join,减少了数据在节点间的传输耗时。
多个表建立 colocation group 时有限制:2张表的分桶列和分桶要完全一致,分区数和副本数也需要一致。
创建 colocation join表语句:
-- 创建表示加properties 'colocate_with'='group1' 加入 分组
create table tb1( k1 int,v1int sum) distributed by hash(k1) properties( 'colocate_with'='group1');
-- 查看所有group信息
-- groupId: group的id groupName:group的名称 tableIds:组内表的id列表 bucketsNum:分桶数量
-- replicationNum 副本数量 distCols:分桶列类型 isStable:是否稳定
show proc '/colocation_group';
-- 根据id查看group信息
show proc '/colocation_group/{groupId}';
修改表的分组
-- 修改分组
alter table tb1 set("colocate_with"="groupName")
-- 不加入分组
alter table tb1 set("colocate_with"="")
-- 通过desc查询是否使用 colocation join
explain select * from t1 join t2 on t1.id = t2.id
Doris 数据模型
doris中表内的列分为两大类:key(纬度列)和value(指标列)
doris中的数据模型分为三类 aggregate模型,unique模型,duplicate模型
无论哪种模型,建表时都有要按字段的顺序指定,并且不能从第二个字段后开始指定
aggregate模型
聚合模型的特点就是将表中的列分为了key和value两种,key就是纬度列用于存放数据,value这是指标列,如总访问量,平均薪资等,每个指标列都需要指定聚合函数,入:sum,min,max,count,replace(新值替换旧值)和bitmap_union等。当key列完全重复时value列就会触发聚合操作。
create table table1(
`user_id` bigint comment '用户id', -- Key列
`username` varchar(32) default '' comment '用户名', -- Key列
`visit_page` varchar(512) comment '访问页面', -- Key列
`pv` bigint sum default '0' -- value列,注意sum表示聚合,当key列相同时,value列会按照指定的聚合函数进行统计
)
aggregate key(user_id,username,visit_page) -- 指定 聚合列
distributed by hash(bigint) buckets 10
properties("replication_num" = "1"); -- 指定其他属性 设置部分数为1(默认3分区)
Unique模型
Unique模型是一种特殊的aggregate模型, 当key重复时,其他value列会进行replace操作
create table user(
`user_id` bigint comment '用户id', -- Key列
`username` varchar(32) default '' comment '用户名',
`age` smallint comment '访问页面',
`phone` varchar(32)
)
unique key(username,visit_page) -- 指定 唯一列
distributed by hash(bigint) buckets 10
properties("replication_num" = "1"); -- 指定其他属性 设置部分数为1(默认3分区)
duplicate 模型
复制模型的特点就是无论数据是否一致都不会处理,只是指定排序字段
-- 注意: 指定duplicate key时必须按列顺序指定,否则报错
create table if not exists access_log(
access_time datetime not null default CURRENT_TIMESTAMP comment '访问时间',
user_id bigint not null comment '访问用户id',
access_url varchar(1024) comment '访问地址',
return_code smallint comment '响应码'
)
duplicate key(access_time,user_id)
distributed by hash(access_time) buckets 10;
Doris 索引
doris主要支持两类索引:
- doris内建的索引,包括前缀索引和ZoneMap索引。
- 用户创建的二级索引,包括Bloom Filter索引和Bitmap倒排索引。
前缀索引
doris会将一行数据的前36个字节作为这行数据的前缀索引, 当遇到varchar时前缀索引就会直接截断(varchar类型只截取前20个字节)。
前缀索引是根据字段的顺序来建立的,因此要尽量根据查询场景进行建表,尽量不要将varchar放在字段的最前面。
# doris 会根据 id,age,name的前20个字节作为前缀索引
create table test1(
id bigint not null comment'主键', -- 8个字节
age smallint comment '年龄', -- 2个字节
name varchar(64) comment '姓名', -- 64个字节
gender char(4) comment '性别', -- 4个字节
iq smallint comment '智商', -- 2个字节
)
aggregate key(id,age,name)
distributed by hash(id) buckets 10;
# doris 会根据name的前20个字节作为前缀索引,遇到varchar直接截断
create table test2(
name varchar(64), -- 64个字节
id bigint not null comment'主键', -- 8个字节
age smallint -- 2个字节
)
aggregate key(id,age,name)
distributed by hash(id) buckets 10;
前缀索引是doris自动为我们创建的,我们无需干预。但是在查询的时候要注意合理使用前缀索引
-- 性能高,遵循最佳左前缀原则,匹配 id,age,name的索引
select * from test1 where id =1 and age = 20 and name ='zhangsan';
-- 性能高,遵循最佳左前缀原则,匹配 id,age的索引
select * from test1 where id =1 and age = 20;
-- 性能低,没遵循最佳左前缀原则,没用到索引
select * from test1 where age = 20 and name ='zhangsan';
前缀索引是doris在建表时就自动创建好的,创建后无法进行更改,但是我们可以通过创建rollup表的方式来更改前缀索引的列(前提是查询时必须命中rollup表)
-- 创建的rollup表会根据id,gender,age,iq,那么的前20个字节创建前缀索引
alter table test1 add rollup rollup_1(id,gender,age,iq,name);
Doris ODBC 外部表
Doris支持通过数据库访问标准接口(ODBC)来访问外部表,外部表省去了频繁的数据导入工作,当doris具有了访问各种数据库的能力。
在doris创建外部表:
方式一: 不使用Resource直接创建odbc表
createe external table test1(
k1 int not null,
k2 varchar(255) not null,
k3 decimal(12,2) not null
) engine=ODBC
comment 'dbbc表'
properties(
"host" = "192.168.0.1",
"port" = "3306",
"user" = "root",
"password" = "root",
"database" = "test_db",
"table" = "test1",
"driver" ="MySQL driver"
"odbc_type" ="MySQL"
);
方式二: 通过resource方式创建odbc外部表
-- 先创建resource对象
create external resource `mysql_odbc_1`
properties(
"type":"odbc_catalog",
"host" = "192.168.0.1",
"port" = "3306",
"user" = "root",
"password" = "root",
"database" = "test_db"
"driver" ="MySQL driver"
"odbc_type" ="MySQL"
);
-- 根据resource对象创建odbc表
createe external table test1(
k1 int not null,
k2 varchar(255) not null,
k3 decimal(12,2) not null
) engine=ODBC
comment 'dbbc表'
properties(
"odbc_catalog_resource" = "mysql_odbc_1",
"database" = "test_db",
"table" = "test1"
)
Doris 优化
web页面查看QueryProfile
doris FE web页面提供了queryProfile,可以更好的帮助我们了解doris的执行情况,并有针对性的进行响应Debug与调优工作。
使用放方法:
- 开启profile默认为false
set enable_profile = true;
- 执行sql后web页面会生成profile
http://FE_ip:8020/QueryProfile
Join Reorder
Joink Reorder功能可以通过代价模型自动调整sql的join顺序,以获得最优的join效率,建议开启该功能文章来源:https://www.toymoban.com/news/detail-440701.html
set enable_cost_based_join_reorder = true;
Doris 目前支持基于规则的 join reorder算法,它的逻辑是:文章来源地址https://www.toymoban.com/news/detail-440701.html
- 让大表尽量跟小表做join,因为与小表生成的结果是较小的。
- 把有where条件的join表往前放,尽量提前过滤数据。
- hash join的优先级高于 Nest Loop Join。
到了这里,关于Doris教程笔记的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!