一、使用Database Desiger
1、创建查询sql,此sql为常用查询,vertica通过查询sql生成优化的PROJECTION,生成部分脚本,存放在设置过程中的
(1)多预测查询:常用sql语句
SELECT * FROM QUERY_PROFILES
(2)order操作
(3)数据分布算法(数据广播,数据切割)
2、通过查看query_events系统表查看需要优化的项
select * from QUERY_EVENTS
(1)主要查看event_type字段
- PREDICATE OUTSIDE HISTOGRAM
- NO HISTOGRAM
- MEMORY LIMIT HIT
- GROUP_BY_SPILLED
- JOIN_SPILLED
- PARTITIONS_ELIMINATED
- MERGE_CONVERTED_TO_UNION
3、分析执行计划
(1)通过explain或者是查询QUERY_PLAN_PROFILES系统表,查询数据流向及耗时
4、修改字段编码
(1)通过减少磁盘空间的使用从而减少查询的IO,间接提高查询效率
5、对float类型字段优化
(1)将float类型转为Number(最多18位),可以改善数据压缩,减少数据库的磁盘大小,提高查询的性能
6、通过运行长度编码(Run Length Encoding,即RLE)优化查询
(1)类似于连续的100个M,30个F,则存储为(100,M),(30,F)
7、在where条件中优化
(1)从filter列中,将该列往RLE方向靠
8、GROUP BY 优化
9、避免在Projection Design中使用到GROUP BY HASH
(1)在vertica查询中使用GROUP BY,vertica会估算结果为流水线(GROUPBY PIPELINED)或散列的(GROUPBY HASH)
- GROUP BY字段不存在于Projection的列
- GROUP BY字段小于Projection的列
-
GROUP BY子句不会出现第一个投影的ORDER BY子句,WHERE条件需要是常量
关键在于Projection定义是否为NOT NULL
10、避免在GROUP BY 中在分割
CREATE PROJECTION … SEGMENTED BY HASH(a,b) ALL NODES 类似于这样的投影
(1)在GROUP BY字段中使用所有在Projection定义的NOT NULL列
11、DISTINCT 优化
(1)GROUP BY取代DISTINCT操作
(2)使用Approximate Count Distinct Functions,需要业务支持
(3)使用空间换取性能配置
12、JOIN优化
(1)Merge Joins
通过对join_key进行order by
(2)where条件中,“=”左边变量来自谓语,“=”右边变量来自游标
SELECT * FROM T JOIN X WHERE T.a + T.b = X.x1 - X.x2;优于SELECT * FROM T JOIN X WHERE T.a = X.x1 + T.b
(3)INNER & OUTER 优化
- 大表作为左表
- 避免在join过程中再分割:join的key类型相同;
13、ORDER BY优化
(1)创建Projection,指定order by的字段
order by不允许指定 ASC/DESC,vertica默认使用ASC,若在查询过程中使用,则vertica会重新排序
14、窗口(分析函数)优化
(1)使用over(order by x)时,主要null值处理
NULLS first, NULLS last, NULLS auto
15、INSERT-SELECT优化
(1)建立插入排序projection
16、DELETE-UPDATE优化