Presto查詢如何優化

這篇文章主要介紹了Presto查詢如何優化，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

Presto是一個開源的分布式SQL查詢引擎，適用于交互式分析查詢，數據量支持GB到PB字節。查詢語言是類ANSI SQL語句。

一、數據存儲

1. 合理設置分區
   與Hive類似，Presto會根據元信息讀取分區數據，合理的分區能減少Presto數據讀取量，提升查詢性能。

2. 使用列式存儲
   Presto對ORC文件讀取做了特定優化，因此在Hive中創建Presto使用的表時，建議采用ORC格式存儲。相對于Parquet，Presto對ORC支持更好。

3. 使用壓縮
   數據壓縮可以減少節點間數據傳輸對IO帶寬壓力，對于即席查詢需要快速解壓，建議采用snappy壓縮

4. 預先排序
   對于已經排序的數據，在查詢的數據過濾階段，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優化

1. 只選擇使用必要的字段
   由于采用列式存儲，選擇需要的字段可加快字段的讀取、減少數據量。避免采用*讀取所有字段。

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

1. 過濾條件必須加上分區字段
   對于有分區的表，where語句中優先使用分區字段進行過濾。acct_day是分區字段，visit_time是具體訪問時間

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

1. Group By語句優化
   合理安排Group by語句中字段順序對性能有一定提升。將Group By語句中字段按照每個字段distinct數據多少進行降序排列。示例中uid是用戶id，比性別數據大很多。

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

1. 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

還有盡量將排序的字段減少些能加快計算。

1. 使用近似聚合函數
   Presto有一些近似聚合函數，對于允許有少量誤差的查詢場景，使用這些函數對查詢性能有大幅提升。比如使用approx_distinct() 函數比Count(distinct x)有大概2.3%的誤差。

SELECT approx_distinct(user_id) FROM access

如果非要精確去重，請用Count+Group 語句代替

1. 用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%'

1. 使用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的效率慢.

1. 使用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) tWHERE 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) tWHERE rnk = 1

9.多用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, b3FROM Table_2WHERE b3 between 20180101 and 20180131)               SELECT 
    subquery_1.a1, subquery_1.a2, 
    subquery_2.b1, subquery_2.b2FROM subquery_1JOIN subquery_2ON subquery_1.a3 = subquery_2.b3;

1. 盡量用UNION ALL代替UNION
   和distinct的原因類似, UNION有去重的功能, 所以會引發內存使用的問題.
   如果你只是拼接兩個或者多個SQL查詢的結果, 考慮用UNION ALL

三、無縫替換Hive表

如果之前的hive表沒有用到ORC和snappy，那么怎么無縫替換而不影響線上的應用：
比如如下一個hive表：

CREATE TABLE bdc_dm.res_category(channel_id1 int comment '1級渠道id',province string COMMENT '省',city string comment '市', uv int comment 'uv')comment 'example'partitioned by (landing_date int COMMENT '日期:yyyymmdd')ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':' LINES TERMINATED BY '\n';

建立對應的orc表

CREATE TABLE bdc_dm.res_category_orc(channel_id1 int comment '1級渠道id',province string COMMENT '省',city string comment '市', uv int comment 'uv')comment 'example'partitioned by (landing_date int COMMENT '日期:yyyymmdd')row format delimited fields terminated by '\t'stored as orc 
TBLPROPERTIES ("orc.compress"="SNAPPY");

先將數據灌入orc表，然后更換表名

insert overwrite table bdc_dm.res_category_orc partition(landing_date)select * from bdc_dm.res_category where landing_date >= 20171001;ALTER TABLE bdc_dm.res_category RENAME TO bdc_dm.res_category_tmp;ALTER TABLE bdc_dm.res_category_orc RENAME TO bdc_dm.res_category;

其中res_category_tmp是一個備份表，若線上運行一段時間后沒有出現問題，則可以刪除該表。

四、注意事項

1. ORC和Parquet都支持列式存儲，但是ORC對Presto支持更好（Parquet對Impala支持更好）

2. 對于列式存儲而言，存儲文件為二進制的，對于經常增刪字段的表，建議不要使用列式存儲（修改文件元數據代價大）。對比數據倉庫，dwd層建議不要使用ORC，而dm層則建議使用

感謝你能夠認真閱讀完這篇文章，希望小編分享的“Presto查詢如何優化”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!