vertica数据库

通用约束

【强制】字符集默认UTF8

说明：Vertica数据库目前仅支持UTF8字符格式。

【规则】禁止在数据库中使用大字段（blob、text），合理使用各数据类型长度

说明：为提高存储压缩性能、提高查询效率和降低查询时消耗的内存，需要按照实际需求合计指定字段长度，避免字段长度浪费。

【规则】默认数据大小写敏感，数据库对象名称不区分大小写
【规则】数据进行生命周期管理

说明：为避免存储空间浪费和提高数据库性能，请根据业务需求合理对数据进行生命周期管理

通用规范

【强制】禁用数据库保留字，如desc、range、datetime、time等

【规则】选用合适的数据类型、字符存储长度，优化底层存储，提升检索效率

【推荐】schema名与应用模块名称尽量一致，长度应控制在15个字符以内

说明：模式名和应用名称一致，便于识别和管理

表/Projection设计

【强制】大表或事实表（百万记录级以上）必须针对关联字段进行排序，针对基数大(唯一性强)的字段进行数据分片, (实事表/大表分段存储，维度表/小表不分段)

说明：为提高并行处理能力和提高查询效率，需要对关联条件进行order by排序，需要针对事实表基数大的列（如主键、流水号）进行segmented by数据分片

【强制】创建projection时，Segmented by字段选择依据：选择基数大(唯一性强)的字段，Where条件中的关联列，两个大表相关联列

说明：基数大的列可以使数据在节点间均匀分布，where条件或关联列进行数据分布可以提高查询性能，实现本地merge join

【强制】创建projection时，Order by字段选择依据:等值过滤条件放前面，基数低的列放前面，高基数的列放在最后。针对查询中使用的where条件、group by、order by、关联字段进行排序

说明：更快的找到目标数据，减少IO

【强制】Vertica单表最大支持1024个分区，接近临界值需及时调整分区粒度，避免数据装载失败
【强制】数据量在百万级以下的表，均不用做分区表，但需要按照statis_date、statis_month等字段进行order by排序。

说明：表分区太多会导致数据库的catalog size过大，过大的catalog会导致故障恢复时间增加，查询系统表速度变慢等问题

【规则】把关联或分组用的列放在排序Order by字段第一位(MergeJoin, Group By Pipe)
【强制】如果存储的固定字符串长度，使用 CHAR 定长字符串类型。

示例：中国省级行政区的字母缩写，统一的是两个字母，BJ—北京市；SH—上海市；字段类型可以定义为 char(2)。

【强制】不同表之间的相同字段或者关联字段，字段类型要保持一致

说明：避免隐式转换，降低查询效率。

【强制】批处理中的中间表能够用temp表的使用temp表
【强制】临时表、备份表、历史表要使用后缀tmp、bak、his字段标明，并提供数据生命周期

说明：规范模型命名，见名知意
【规则】varchar类型，定义长度要合理，避免过长导致存储浪费、影响性能。
说明：存储字符串过长，会导致sql运行时申请过多的内存造成资源浪费影响数据库性能。
【强制】禁止使用字段属性default,该功能直接在程序中实现。
说明：规范程序开发，加快数据装载

SQL书写规范

通用规范

【规则】SQL规范基本原则和oracle、mysql相同
【强制】进行delete、update操作后及时进行commit或rollback操作

说明：vertica锁机制不同与关系型数据库，在进行delete或update操作时会进行X lock table，所以进行上述操作后需要及时释放锁资源

【强制】禁止频繁的commit
【规则】避免使用子查询、or，将子查询转化为表连接方式，or转化为in

说明：or操作很难优化，or可转换为union all或in操作

【推荐】使用explain查看执行计划，使用profile查看sql运行需要内存

说明：explain查看SQL语句执行计划

select语句

【强制】明确查询的字段，禁止使用select *

说明：防止表结构变更导致程序异常；明确字段，避免读取所有字段列（列式数据库特性）

【强制】表关联时，改变表驱动顺序，使用小表驱动大表
【强制】禁止使用select …for update

说明：导致数据行被锁，容易造成死锁，影响业务正常运行。

【推荐】多表关联或排序所涉及的字段建议projection中优先进行order by排序
【强制】禁止出现隐式转换，保持SQL中变量类型与字段类型一致

示例：条件语句中数据类型以传入参数为准。user_id定义为int型，在程序中使用user_id=’1’，就会发生隐式转换，将user_id转换成varchar类型，影响数据库性能，因此应使用user_id=1。

【规则】where条件所涉及的过滤字段尽量选择基数低的列放前面

说明：过滤字段选用高选择性的字段

【规则】禁止对“=”左侧字段使用函数、运算，避免在join、group by中使用函数，这样可以让优化器能更好的发挥作用
【规则】避免使用 not、<>、is null, is not null条件
【规则】like子句尽量前置匹配，前置不加%，like区分大小写，ilike不区分大小写

说明：“%”前缀模糊匹配效率低

【规则】order by 字段顺序尽量与projection中order by顺序保持一致

说明：projection中已经排序过的数据
【推荐】多用=操作；>,<操作转换为>=,<=
说明：“=”号操作可直接定位到相应的位置，>、<操作需要再移一位才能定位

【推荐】减少无意义的order by、group by操作

示例：
select name,val from tabA where userid in (select user_id from tabB order by user_id);
select user_id from tabB order by rand()

【推荐】尽可能使用where条件替代having子句

说明：SQL语句中先执行where后执行having，过滤的结果越多，group by的代价越小。
【推荐】不在where 条件中使用中文过滤数据
说明：例如where prov_name=‘全国’可以替换为where prov_code=‘999’。

insert语句

【规则】insert时建议指定字段名，避免字段顺序变动后数据插入错误
【强制】当需要插入大量数据时，可以使用copy方式装载，避免执行大量insert语句
【强制】大量数据装载时，使用direct直接路径加载避免占用wos空间

insert /+direct/ into tb_name

【推荐】create table as select时，可以使用copy_table函数提高数据复制效率

update语句

【强制】禁止update语句无where条件
【强制】update语句会造成表级别的X型锁，update操作后需及时提交
【强制】大量数据修改时（100MB数据以上），使用direct直接路径加载避免占用wos空间

delete语句

【强制】禁止delete语句无where条件

无where条件的delete语句，改写成truncate语句。

【规则】删除大量数据时，使用direct直接路径加载避免占用wos空间,同时后续跟一个commit 提交语句