presto查询优化

约定不等于承诺〃 2023-01-14 10:56 117阅读 0赞

**资料：https://www.jianshu.com/p/f435ce79c966**

### 数据存储优化 ###

*  合理设置分区  
    与Hive类似，Presto会根据元信息读取分区数据，合理的分区能减少Presto数据读取量，提升查询性能。
 *  使用列式存储  
    Presto对ORC文件读取做了特定优化，因此在Hive中创建Presto使用的表时，建议采用ORC格式存储。相对于Parquet，Presto对ORC支持更好。
 *  使用压缩  
    数据压缩可以减少节点间数据传输对IO带宽压力，对于即席查询需要快速解压，建议采用snappy压缩
 *  预先排序  
    对于已经排序的数据，在查询的数据过滤阶段，ORC格式支持跳过读取不必要的数据。比如对于经常需要过滤的字段可以预先排序。

INSERT INTO table nation_orc partition(p) SELECT * FROM nation SORT BY n_name;

如果需要过滤n\_name字段，则性能将提升。

SELECT count(*) FROM nation_orc WHERE n_name=’AUSTRALIA’;

### 查询SQL优化 ###

*  只选择使用必要的字段  
    由于采用列式存储，选择需要的字段可加快字段的读取、减少数据量。避免采用\*读取所有字段。

[GOOD]: SELECT time,user,host FROM tbl
    [BAD]:  SELECT * FROM tbl

*  过滤条件必须加上分区字段  
    对于有分区的表，where语句中优先使用分区字段进行过滤。acct\_day是分区字段，visit\_time是具体访问时间

[GOOD]: SELECT time,user,host FROM tbl where acct_day=20171101
    [BAD]:  SELECT * FROM tbl where visit_time=20171101

### Group By语句优化 ###

合理安排Group by语句中字段顺序对性能有一定提升。将Group By语句中字段按照每个字段distinct数据多少进行降序排列。示例中uid是用户id，比性别数据大很多。

[GOOD]: SELECT GROUP BY uid, gender
    [BAD]:  SELECT GROUP BY gender, uid

### Order by时使用Limit ###

Order by需要扫描数据到单个worker节点进行排序，导致单个worker需要大量内存。如果是查询Top N或者Bottom N，使用limit可减少排序计算和内存压力。

[GOOD]: SELECT * FROM tbl ORDER BY time LIMIT 100
    [BAD]:  SELECT * FROM tbl ORDER BY time

还有尽量将排序的字段减少些能加快计算。

### 使用近似聚合函数 ###

Presto有一些近似聚合函数，对于允许有少量误差的查询场景，使用这些函数对查询性能有大幅提升。比如使用approx\_distinct() 函数比Count(distinct x)有大概2.3%的误差。

SELECT approx_distinct(user_id) FROM access

如果非要精确去重，请用Count+Group 语句代替

### 用regexp\_like代替多个like语句 ###

Presto查询优化器没有对多个like语句进行优化，使用regexp\_like对性能有较大提升

[GOOD]
    SELECT
      ...
    FROM
      access
    WHERE
      regexp_like(method, 'GET|POST|PUT|DELETE')
      
    [BAD]
    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，然后将join右边的表数据整个复制一份发送到每个worker进行计算。如果右边的表数据量太大，则可能会报内存溢出错误。

[GOOD] SELECT ... FROM large_table l join small_table s on l.id = s.id
    [BAD] SELECT ... FROM small_table s join large_table l on l.id = s.id

如果左表和右表都比较大怎么办?为了防止内存报错  
1）修改配置distributed-joins-enabled (presto version >=0.196)  
2）在每次查询开始使用distributed\_join的session选项

-- set session distributed_join = 'true'
    SELECT ... FROM large_table1 join large_table2
    on large_table1.id = large_table2.id

核心点就是使用distributed join. Presto的这种配置类型会将左表和右表同时以join key的hash value为分区字段进行分区. 所以即使右表也是大表,也会被拆分.  
缺点是会增加很多网络数据传输, 所以会比broadcast join的效率慢.

### 使用Rank函数代替row\_number函数来获取Top N ###

在进行一些分组排序场景时，使用rank函数性能更好

[GOOD]
    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
    
    [BAD]
    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语句 ###

使用Presto分析统计数据时，可考虑把多次查询合并为一次查询，用Presto提供的子查询完成。  
这点和我们熟知的MySQL的使用不是很一样。注意下列子查询中的逗号。

WITH subquery_1 AS (
        SELECT a1, a2, a3 
        FROM Table_1 
        WHERE a3 between 20180101 and 20180131
    ),              
    subquery_2 AS (
        SELECT b1, b2, b3
        FROM Table_2
        WHERE b3 between 20180101 and 20180131
    )               
    SELECT 
        subquery_1.a1, subquery_1.a2, 
        subquery_2.b1, subquery_2.b2
    FROM subquery_1
        JOIN subquery_2
        ON subquery_1.a3 = subquery_2.b3;

### 尽量用UNION ALL代替UNION ###

和distinct的原因类似, UNION有去重的功能, 所以会引发内存使用的问题.  
如果你只是拼接两个或者多个SQL查询的结果, 考虑用UNION ALL