一、数据存储优化
-
合理设置分区 与Hive相似，Presto依据元数据读取分区数据。通过合理分区，Presto可以减少数据读取量，从而提升查询性能。
-
使用列式存储 Presto在读取ORC文件时有特别的优化。因此，建议在Hive中创建供Presto使用的表时，采用ORC格式进行存储。
-
使用数据压缩 数据压缩能减少节点间数据传输时的IO带宽压力。对于需要快速解压的即席查询，推荐使用snappy压缩。
-
预先排序 如果数据已经排序，ORC格式在查询时可以跳过不必要的数据读取。例如，频繁需要过滤的字段可以预先进行排序。
示例：
若经常需要按n_name字段过滤，则性能将得到提升。
SQL示例：
INSERT INTO table nation_orc partition(p) SELECT * FROM nation SORT BY n_name;
SELECT count(*) FROM nation_orc WHERE n_name='AUSTRALIA';
二、查询SQL优化
- 选择必要的字段
由于采用列式存储，选择所需字段可加快字段读取速度，减少数据传输。避免使用
*来读取所有字段。
优良示例：
SELECT time, user, host FROM tbl;
不佳示例：
SELECT * FROM tbl;
- 过滤条件中添加分区字段
对于分区表，在
where语句中优先使用分区字段进行数据过滤。例如，acct_day是分区字段，visit_time是具体访问时间。
优良示例：
SELECT time, user, host FROM tbl where acct_day=20171101;
不佳示例：
SELECT * FROM tbl where visit_time=20171101;
- 合理安排Group By语句中的字段顺序
合理排列
Group By语句中的字段顺序，按照每个字段的distinct值数量降序排列，可提升性能。
优良示例：
SELECT GROUP BY uid, gender;
不佳示例：
SELECT GROUP BY gender, uid;
- 使用Limit配合Order by
Order by需将数据扫描至单个工作节点进行排序，可能导致内存使用增加。如果是查询Top N或Bottom N，使用limit可减轻排序的计算压力和内存负担。
优良示例：
SELECT * FROM tbl ORDER BY time LIMIT 100;
不佳示例：
SELECT * FROM tbl ORDER BY time;
- 运用近似聚合函数
Presto提供一些近似聚合函数，在允许少量误差的场景下，使用这些函数大幅提升查询性能，例如使用
approx_distinct()比Count(distinct x)约有2.3%的误差。
示例：
SELECT approx_distinct(user_id) FROM access;
- 用regexp_like代替多个like语句
Presto查询优化器未对多个like语句进行优化，使用
regexp_like能显著提升性能。
优良示例：
SELECT ... FROM access WHERE regexp_like(method, 'GET|POST|PUT|DELETE');
不佳示例：
SELECT ... FROM access WHERE method LIKE '%GET%' OR method LIKE '%POST%' OR method LIKE '%PUT%' OR method LIKE '%DELETE%';
- 使用Join语句时将大表放在左边 Presto默认join算法是broadcast join，即将join左边的表分割到多个worker，然后将右边的表整表复制发送到每个worker进行计算。若右表数据量过大，可能导致内存溢出。
优良示例：
SELECT ... FROM large_table l join small_table s on l.id = s.id;
不佳示例：
SELECT ... FROM small_table s join large_table l on l.id = s.id;
- 在ON阶段尽可能完成Join条件的过滤，避免使用WHERE ON条件用于决定如何从表B检索数据行。如果B表中没有匹配ON条件的行， 则会额外生成一行所有列为NULL的数据。在匹配阶段，不会考虑WHERE子句的条件。仅在匹配完成后，才使用WHERE子句条件过滤结果。
优良示例：
select * from A inner join B on B.name = A.name left join C on C.name = B.name and C.status > 1 left join D on D.id = C.id and D.status = 1;
- 使用Join取代子查询 在大数据场景下，使用inner join替换exists，使用left join替换not exists，性能更优。
优良示例：
select t1.* from t1 inner join (select distinct r_id from t2) t2 on t1.id = t2.r_id;
不佳示例：
select * from t1 where exists(select 1 from t2 where t1.id=t2.r_id);
- 使用Rank函数代替row_number函数获取Top N 在进行分组排序时，使用rank函数性能更好。
优良示例：
sql SELECT checksum(rnk) FROM (SELECT rank() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk FROM lineitem) t WHERE rnk = 1;
不佳示例：
sql SELECT checksum(rnk) FROM (SELECT row_number() OVER (PARTITION BY l_orderkey, l_partkey ORDER BY l_shipdate DESC) AS rnk FROM lineitem) t WHERE rnk = 1;
-
避免使用WITH AS，因增加了额外的I/O开销 WITH AS不适合于大数据量且频繁查询的场景。
-
用UNION ALL代替UNION 与DISTINCT原因类似，UNION具有去重功能，会引发内存使用问题。如果仅是拼接两个或多个SQL查询的结果，考虑使用UNION ALL。
-
在内层查询中减少不必要的Order By操作 如果查询仅需要统计数量（count值），在内层查询中尽量减少不必要的Order By操作，因为Order By需要对数据进行排序，会增加额外的计算和内存开销。如果仅是为了统计数量，那么排序操作是多余的，可以省略。
希望通过这些优化技巧，您能有效提升Presto查询的性能。
