美文网首页
vertica数据库SQL编写规范

vertica数据库SQL编写规范

作者: mr_酱 | 来源:发表于2019-02-13 10:05 被阅读13次

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’。
【推荐】优先使用projection中的order by字段
【参考】修改业务逻辑,减少大结果集的group by或order by

INSERT 插入数据优化

【规则】insert时建议指定字段名,避免字段顺序变动后数据插入错误
【强制】当需要插入大量数据时,可以使用copy方式装载,避免执行大量insert语句
【强制】大量数据装载时,使用direct直接路径加载避免占用wos空间
【推荐】create table as select时,可以使用copy_table函数提高数据复制效率

UPDATE 更新优化

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

DELETE 删除优化

【强制】禁止delete语句无where条件,无where条件的delete语句,改写成truncate语句。
【规则】删除大量数据时(百万数据量),使用direct直接路径加载避免占用wos空间;
【规则】delete where方式删除的数据量达到10万级别以上的,delete之后使用执行purge_table操作,示例select purge_table('schema_name.table_name');。

筛选与子查询

由于数据仓库数据量大,要尽量避免执行全表扫描的动作,所以在SQL中要使用筛选条件或子查询。考虑到SQL的执行效率,筛选条件中要有分区字段。

关联

在使用SQL处理数据的过程中,经常会执行表和表之间关联的动作,常用的关联有:内连接、左连接、右连接、全连接。
考虑到SQL书写的复杂度和执行效率,一个SQL语句中关联表的数量不能超过5个,处理逻辑确实需要多个表关联的,可以拆分借助临时表进行处理。
在使用关联时需遵循以下原则:

  • 小结果集驱动大结果集
  • 保证关联表的连接字段都提前排序
  • 合理设计segmented分布,合理的数据分片会避免大量问网络交换
  • Hash Join策略中,小表(inner表,驱动表)会放入内存hash散列,然后再逐条扫描大表(Outer表,被驱动表),查看是否能匹配上
  • 两张大表进行关联,可以将两张表的Protection按照关联字段预先排好序,并按相同的字段在节点间分段,那么优化器会选择更有效率的Merge Join
  • join语句中关联键不使用trim、to_char等函数做处理,特别是在已优化为merge join的情况下,关联键再做函数处理后,查询计划会变为hash join。
  • 举例:
1、左连接 LEFT JOIN
select a.statis_date, a.prov_code, a.col_name 
    from 用户名.表名 a
        left join (select user_id 
                from 用户名.表名  
                where statis_date=’20181219’
                ) b on a.user_id=b.user_id
    where a.statis_date=’20181219’;

2、右连接 RIGHT JOIN
Select b.user_id from csapdws.tb_dws_ct_86hl_xxx_day a
right join (select user_id from csapdws.tb_dws_ct_86hl_yyy_day  
where statis_date=’20181219’) b on a.user_id=b.user_id
where a.statis_date=’20181219’;

3、内连接 INNER JOIN
select a.* form table_a a inner join table_b  b on a.name_id =b.name_id
where a.statis_date=’20181219’and b.statis_date=’20181219’;

4、全连接 FULL OUTER JOIN
FULL OUTER JOIN 产生表A和表B的并集。但是需要注意的是,对于没有匹配的记录,则会以NULL做为值,可以使用IF NULL 判断。
select a.*,b.* from table_a  a full outer join table_b  b on a.name_id =b.name_id;
where a.statis_date=’20181219’and b.statis_date=’20181219’;

表数据清理

【强制】禁止delete语句无where条件,无where条件的delete语句,改写成truncate语句。
【规则】删除大量数据时(百万数据量),使用direct直接路径加载避免占用wos空间;
【规则】delete where方式删除的数据量达到10万级别以上的,delete之后使用执行purge_table操作示例select purge_table('schema_name.table_name');
【规则】分区表采用drop partition;
【规则】中间表采用temporary table;
【规则】全表清空数据使用truncate table;
【规则】必须使用delete where方式的,按照规范使用direct hint;delete where方式删除的数据量达到10万级别以上的,delete之后使用执行purge_table操作,示例select purge_table('schema_name.table_name');
举例:

    taskPosition="清空统计期结果表"
    verticasql = []
    verticasql.append('''select drop_partition('用户名.表名', '%(ARG_OPTIME)s')'''% vars())
VerticaExe(verticasql, name, verticaschema, dates,taskPosition)
taskPosition="插入结果表数据 - 系统呼叫量汇总"
verticasql = []
verticasql.append('''
        insert /*+direct*/ into csapdw.tb_dw_ct_86hl_system_call_list_day
        (statis_date,
               prov_code,
        ''' % vars() )
    VerticaExe(verticasql, name, verticaschema, dates,taskPosition)

相关文章

  • vertica数据库SQL编写规范

    SELECT 查询优化 【强制】明确查询的字段,禁止使用select *说明:防止表结构变更导致程序异常;明确字段...

  • Vertica的这些事(二)——SQL Server、Oracl

    SQL Server、Oracle、MySQL和Vertica数据库常用函数对比Vertica数据库是HP公司新收...

  • MYSQL数据库 SQL开发设计规范

    1 编写目的 本手册是为指导开发人员按照文档中的规范进行MYSQL数据库设计及SQL编码。 2 数据库对象定义规范...

  • Mysql 规范

    Mysql 规范 数据库命名规范总结 数据库基本设计规范 索引设计规范 数据库字段设计规范 数据库SQL开发规范 ...

  • 阿里数据库开发规范:谁还敢说你的数据库设计的“烂”

    数据库设计几个规范: 数据库命名规范、数据库基本设计规范、数据库索引设计规范、数据库字段设计规范、数据库SQL开发...

  • Vertica介绍

    Vertica数据库介绍,看这里:http://www.amazingkoala.com.cn/Vertica/2...

  • SQL/Vertica

    有aggregate projection的table不能编辑。编辑之前要先select export_objec...

  • 数据库设计规范

    【MySQL】MySQL数据库设计规范 1. 规范背景与⽬的 MySQL数据库与 Oracle、 SQL Serv...

  • 数据库开发设计规范

    数据库开发设计规范 命名规范 基础规范 字段设计规范: 索引规范 索引禁忌 SQL设计 操作规范: 注意事项:

  • 初步认识服务器

    1.编写数据库SQL创建语句和注释 2.编写数据库先关操作的RepositoryAppServer-commons...

网友评论

      本文标题:vertica数据库SQL编写规范

      本文链接:https://www.haomeiwen.com/subject/edfvjqtx.html