doris实战处理(一)doris表的建表规范、查询

感谢原文:https://mp.weixin.qq.com/s/tGbdkF62WU6qbAH0mqtXuA

第一部分:字符集规范

【强制】数据库字符集指定utf-8,并且只支持utf-8。

命令规范

  1. 【建议】库名统一使用小写方式,中间用下划线(_)分割,长度62字节内
  2. 【建议】表名称大小写敏感,统一使用小写方式,中间用下划线(_)分割,长度64字节内
第二部分:建表规范
  1. 【强制】确保每个tablet大小为1-3G之间。举例:假设表内单分区数据量在100G,按天分区,bucket数量100个。

  2. 【强烈建议】不要使用Auto Bucket ,按照自己的数据量来进行分区分桶,这样你的导入及查询性能都会得到很好的效果,Auto Bucket 会造成 tablet 数量过多,造成大量小文件的问题。

  3. 【强制】 5 亿以上的数据必须设置分区分桶策略

    a、没有办法分区的,数据又缓慢增长的:单个tablet数据量保持在1-3G;比如5亿数据大小在20G,bucket数量给20个

    b、没有办法分区的,数据又较快增长的,没办法按照时间动态分区,可以适当放大一下你的bucket数量,按照你的数据保存周期(180天)数据总量,来估算你的bucket数量应该是多少,建议还是单个bucket大小在1-3G。

    c、一个是对分桶字段进行加盐处理,业务上查询的时候也是要同样的加盐策略,这样能利用到分桶数据剪裁能力

    d、另外一个是数据随机分桶,这种缺点是没办法利用数据分桶剪裁能力,数据分布会很均匀

    e、避免数据倾斜的问题
    100M以内:1 buckets
    100M-1G :3-5 个 Buckets
    大于1G-3G :5-7个 buckets
    3-5G :7-10 个 buckets

    f、维度表:缓慢增长的,可以使用单分区,在分桶策略上使用常用查询条件(这个字段数据分步相对均衡)分桶,

    g、事实表

  4. 【建议】 1000w-2 亿以内数据为了方便可以不设置分区,直接用分桶策略。(不设置其实Doris内部会有个默认分区)

    a、参考上面第二点

  5. 【强制】 2000kw 以内数据禁止使用动态分区(动态分区会自动创建分区,而小表用户客户关注不到,会创建出大量不使用分区分桶)

    a、参考上面第二点

  6. 【强制】对于有大量历史分区数据,但是历史数据比较少,或者不均衡,或者查询概率的情况,使用如下方式将数据放在特殊分区。

对于历史数据,如果数据量比较小我们可以创建历史分区(比如年分区,月分区),将所有历史数据放到对应分区里
创建历史分区方式
例如:FROM (“2000-01-01”) TO (“2022-01-01”) INTERVAL 1 YEAR
具体参考:https://doris.apache.org/zh-CN/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-TABLE#partition_info
在这里插入图片描述

  1. 【强制】如果分桶字段存在30%以上的数据倾斜,则禁止使用Hash分桶策略,改使用random分桶策略

    参考上面第二点事实表部分

  2. 【建议】前缀索引的第一个字段一定是最长查询的字段,并且需要是高基字段。这里面选取分区分桶外最长查询且高基数的列

分桶字段注意事项:这个一般是数据分布比较均衡的,也是经常使用的字段,最好是高基数字段

Int(4)+ Int(4) + varchar(50),前缀索引长度只有28

Int(4) + varchar(50) + Int(4),前缀索引长度只有24

varchar(10) + varchar(50) ,前缀索引长度只有30

前缀索引(36位):第一个字段查询性能最好,前缀索引碰见varchar类型的字段,会自动截断前20个字符

最常用的查询字段如果能放到前缀索引里尽可能放到前前缀索引里,如果不能,可以放到分桶字段里

good case :UNIQUE KEY(user_id, age) user_id最长被查询,且数据分布比较散

bad case :UNIQUE KEY(age,user_id ) age是低基数列,且可能存在数据倾斜

  1. 【强制】表的副本数必须为3
  2. 【建议】前缀索引中的字段长度尽可能明确,因为Doris只有前36个字节能走前缀索引
  3. 【强制】除了UNIQUE KEY和aggregate key要构建key的情况,否则不要基数(例如user_type)小于50的字段建立任何索引。因为Doris内置了字典类型优化。

已经有了低基数优化了

Unique Key 是aggregate key 的一个特例,当aggregate key 的key 保持唯一其实就是Unqiue key 模型

  1. 【强制】BloomFilter索引必须在查询条件是in或者=,并且是高基(5000以上)列上构建。

首先BloomFilter适用于非前缀过滤。

查询会根据该列高频过滤,而且查询条件大多是 in 和 = 过滤。

不同于Bitmap, BloomFilter适用于高基数列。比如UserID。因为如果创建在低基数的列上,比如 “性别” 列,则每个Block几乎都会包含所有取值,导致BloomFilter索引失去意义。

数据基数在一半左右

类似身份证号这种基数特别高并且查询是等值(=)查询,使用Bitmap索引能极大加速

Bloomfilter 使用场景:

【强制】bitmap索引必须在一定基数范围内构建,太高或者太低的基数都不合适

Bitmap 索引支持类型

【强制】亿级别以上数据,如果有模糊匹配,使用倒排索引或者是 NGram Bloomfilter

【建议】如果某个范围数据在分区分桶和前缀索引中都不好设计,可以考虑引入倒排索引加速。

【强制】单表物化视图不能超过6个

单笔物化视图是实时构建

在unique 模型上物化视图只能起到 Key 重新排序的作用,不能做数据的聚合,因为Unqiue模型的聚合模型是replace

【建议】建议使用JSON数据类型代替字符串类型存放JSON数据的使用方式

第三部分:数据变更规范

【强制】应用程序不可以直接使用delete或者update语句变更数据,使用CDC的upsert方式来实现。

低频操作上使用,比如 Update 几分钟更新一次

如果使用 Delete 一定带上分区条件

【强制】DBA执行delete后者update语句时必须带分区条件

【强制】禁止使用INSERT INTO tbl1 VALUES (“1”), (“a”);这种方式写入数据。

【建议】特殊大的ETL操作,简单单独在Session中设置超时时间

SELECT/+ SET_VAR(query_timeout = 1/ sleep(3);
第四部分:数据查询规范
select * from kunpeng_risk_record krr where krr.event_occur_time_date between ‘2023-10-01 00:00:00’ and ‘2023-10-25 23:59:59’ and krr.partner_code = ‘liveme’ order by krr.sequence_id desc limit 20;
3. 表属性级别

“enable_unique_key_merge_on_write” = “true”,
“store_row_column” = “true”
be.conf
disable_storage_row_cache 是否开启行缓存, 默认不开启

  1. 使用PrepareStatement模板

【强制】in 中条件超过 2000 后,必须修改为子查询

【强制】禁止使用REST API(Statement Execution Action)执行大量SQL查询,改接口仅仅用于集群维护。

例如将 from table order by datatime desc limit 10 优化为from table where datatime=‘2023-10-20’ order by datatime desc limit 10

【强制】2个以上大于3亿的表 JOIN 使用 Colocate JOIN

Colocate Join 的使用参照:https://doris.apache.org/zh-CN/docs/query-acceleration/join-optimization/colocation-join

【强制】亿级别大表禁止使用select * 查询,查询时需要明确要查询的字段

  1. SQL Block方式禁止这种操作

  2. 如果是高并发点查,建议开启行存

【强制】亿级以上表数据查询必须带分区分桶条件

【建议】一次insert into select 数据超过1亿条后,建议拆分为多个insert into select语句执行,分成多个批次来执行。

set parallel_fragment_exec_instance_num = 8 或者 16 建议是你CPU内核的一半
insert into new_tbl select * from old_tbl

如果真的是要这样执行,在集群资源相对空闲的时候可以通过调整并发度来加快的数据导入速度

2.0 以后版开启了Pipeline 就不需要设置并发度了

【强制】query查询条件返回结果在5w条以上,使用JDBC Catalog或者OUTFILE方式导出。不然大量FE上数据传输将占用FE资源,影响集群稳定性

如果你是交互式查询,建议使用分页方式(offset limit),分页要加Order by

如果是数据导出提供给第三方使用,建议使用 outfile 或者 export 方式

【建议】query查询如果有大量的数据传输需求,建议部署observer节点并在该该节点执行查询(私有化部署)

建议的方式是 1 FE(Follower) + 多个 OBserver(FE)方式,读写分析,所有的写连接 Follower,所有的读连接Observer

【建议】尽量不要使用OR 作为 JOIN条件

【建议】大量数据排序(5亿以上)后返回部分数据,建议先减少数据范围在执行排序,否则大量排序会影响性能。

  • 25
    点赞
  • 25
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Doris 建设数仓建表规范主要包括以下几个方面。 1. 规范名应具有明确的含义,能够清晰地反映的内容和用途。名应该使用小写字母,可以使用下划线分隔单词,遵循命名规范,以提高可读性。 2. 字段命名规范:字段名也应该具有明确的含义,用于描述字段所代的数据意义。字段名应使用小写字母,如果字段名由多个单词组成,可以使用下划线分隔,遵循命名规范,以提高可读性。 3. 字段类型规范:根据实际的数据类型选择适当的字段类型,以减少存储空间的占用和提高查询效率。常见的字段类型包括整型、浮点型、日期时间型、字符型等。 4. 主键设置规范:每张应该有一个主键,用于唯一标识每条记录。主键可以是单个字段或多个字段的组合,根据实际情况进行选择。主键的选择应尽量避免频繁变更和冲突。 5. 索引规范:根据查询的需求,合理设置索引,以提高查询效率。索引可以加快数据的查询速度,但同时也会增加写入和更新的时间。应根据实际情况进行权衡和选择。 6. 关系规范:如果有多张之间存在关联关系,应该明确定义和建立之间的关系,如外键约束。这样可以保证数据的完整性,减少冗余和错误。 7. 数据分区规范:对于大型,可以进行数据分区,将数据按照某个字段进行划分,以提高查询处理的效率。数据分区可以根据时间、地域等维度进行划分。 通过遵循这些建表规范,可以提高数据仓库的可维护性、可扩展性和查询性能,减少数据质量问题和冗余数据的产生。同时,也能提高数据分析和业务应用的效果。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值