vertica数据库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’。
【推荐】优先使用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)