Presto查詢?nèi)绾蝺?yōu)化

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

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

一、數(shù)據(jù)存儲

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

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

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

4. 預(yù)先排序
   對于已經(jīng)排序的數(shù)據(jù)，在查詢的數(shù)據(jù)過濾階段，ORC格式支持跳過讀取不必要的數(shù)據(jù)。比如對于經(jīng)常需要過濾的字段可以預(yù)先排序。

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優(yōu)化

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

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

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

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

1. Group By語句優(yōu)化
   合理安排Group by語句中字段順序?qū)π阅苡幸欢ㄌ嵘?。將Group By語句中字段按照每個字段distinct數(shù)據(jù)多少進行降序排列。示例中uid是用戶id，比性別數(shù)據(jù)大很多。

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

1. Order by時使用Limit
   Order by需要掃描數(shù)據(jù)到單個worker節(jié)點進行排序，導(dǎo)致單個worker需要大量內(nèi)存。如果是查詢Top N或者Bottom N，使用limit可減少排序計算和內(nèi)存壓力。

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

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

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

SELECT approx_distinct(user_id) FROM access

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

1. 用regexp_like代替多個like語句
   Presto查詢優(yōu)化器沒有對多個like語句進行優(yōu)化，使用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右邊的表數(shù)據(jù)整個復(fù)制一份發(fā)送到每個worker進行計算。如果右邊的表數(shù)據(jù)量太大，則可能會報內(nèi)存溢出錯誤。

[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

如果左表和右表都比較大怎么辦?為了防止內(nèi)存報錯
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為分區(qū)字段進行分區(qū). 所以即使右表也是大表,也會被拆分.
缺點是會增加很多網(wǎng)絡(luò)數(shù)據(jù)傳輸, 所以會比broadcast join的效率慢.

1. 使用Rank函數(shù)代替row_number函數(shù)來獲取Top N
   在進行一些分組排序場景時，使用rank函數(shù)性能更好

[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分析統(tǒng)計數(shù)據(jù)時，可考慮把多次查詢合并為一次查詢，用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有去重的功能, 所以會引發(fā)內(nèi)存使用的問題.
   如果你只是拼接兩個或者多個SQL查詢的結(jié)果, 考慮用UNION ALL

三、無縫替換Hive表

如果之前的hive表沒有用到ORC和snappy，那么怎么無縫替換而不影響線上的應(yīng)用：
比如如下一個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';

建立對應(yīng)的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");

先將數(shù)據(jù)灌入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是一個備份表，若線上運行一段時間后沒有出現(xiàn)問題，則可以刪除該表。

四、注意事項

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

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

感謝你能夠認真閱讀完這篇文章，希望小編分享的“Presto查詢?nèi)绾蝺?yōu)化”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學(xué)習(xí)!