我的CSDN: http://blog.csdn.net/FreeFishLy/article/details/79081764
Presto是一個(gè)開源的分布式SQL查詢引擎嘲更，適用于交互式分析查詢壹堰，數(shù)據(jù)量支持GB到PB字節(jié)。查詢語言是類ANSI SQL語句。筆者在多個(gè)項(xiàng)目中用到Presto做即席查詢，總結(jié)了一些優(yōu)化措施凹炸。
一土浸、數(shù)據(jù)存儲(chǔ)
-
合理設(shè)置分區(qū)
與Hive類似，Presto會(huì)根據(jù)元信息讀取分區(qū)數(shù)據(jù)蕾殴，合理的分區(qū)能減少Presto數(shù)據(jù)讀取量笑撞，提升查詢性能。 -
使用列式存儲(chǔ)
Presto對(duì)ORC文件讀取做了特定優(yōu)化钓觉，因此在Hive中創(chuàng)建Presto使用的表時(shí)茴肥，建議采用ORC格式存儲(chǔ)。相對(duì)于Parquet荡灾，Presto對(duì)ORC支持更好瓤狐。 -
使用壓縮
數(shù)據(jù)壓縮可以減少節(jié)點(diǎn)間數(shù)據(jù)傳輸對(duì)IO帶寬壓力瞬铸，對(duì)于即席查詢需要快速解壓，建議采用snappy壓縮 -
預(yù)先排序
對(duì)于已經(jīng)排序的數(shù)據(jù)础锐，在查詢的數(shù)據(jù)過濾階段嗓节，ORC格式支持跳過讀取不必要的數(shù)據(jù)。比如對(duì)于經(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)化
-
只選擇使用必要的字段
由于采用列式存儲(chǔ)鸵隧，選擇需要的字段可加快字段的讀取、減少數(shù)據(jù)量意推。避免采用*讀取所有字段豆瘫。
[GOOD]: SELECT time,user,host FROM tbl
[BAD]: SELECT * FROM tbl
-
過濾條件必須加上分區(qū)字段
對(duì)于有分區(qū)的表，where語句中優(yōu)先使用分區(qū)字段進(jìn)行過濾菊值。acct_day是分區(qū)字段靡羡，visit_time是具體訪問時(shí)間
[GOOD]: SELECT time,user,host FROM tbl where acct_day=20171101
[BAD]: SELECT * FROM tbl where visit_time=20171101
-
Group By語句優(yōu)化
合理安排Group by語句中字段順序?qū)π阅苡幸欢ㄌ嵘roup By語句中字段按照每個(gè)字段distinct數(shù)據(jù)多少進(jìn)行降序排列俊性。示例中uid是用戶id略步，比性別數(shù)據(jù)大很多。
[GOOD]: SELECT GROUP BY uid, gender
[BAD]: SELECT GROUP BY gender, uid
-
Order by時(shí)使用Limit
Order by需要掃描數(shù)據(jù)到單個(gè)worker節(jié)點(diǎn)進(jìn)行排序定页，導(dǎo)致單個(gè)worker需要大量?jī)?nèi)存趟薄。如果是查詢Top N或者Bottom N，使用limit可減少排序計(jì)算和內(nèi)存壓力典徊。
[GOOD]: SELECT * FROM tbl ORDER BY time LIMIT 100
[BAD]: SELECT * FROM tbl ORDER BY time
還有盡量將排序的字段減少些能加快計(jì)算杭煎。
-
使用近似聚合函數(shù)
Presto有一些近似聚合函數(shù)，對(duì)于允許有少量誤差的查詢場(chǎng)景卒落，使用這些函數(shù)對(duì)查詢性能有大幅提升羡铲。比如使用approx_distinct() 函數(shù)比Count(distinct x)有大概2.3%的誤差。
SELECT approx_distinct(user_id) FROM access
如果非要精確去重儡毕，請(qǐng)用Count+Group 語句代替
-
用regexp_like代替多個(gè)like語句
Presto查詢優(yōu)化器沒有對(duì)多個(gè)like語句進(jìn)行優(yōu)化也切，使用regexp_like對(duì)性能有較大提升
[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語句時(shí)將大表放在左邊
Presto中join的默認(rèn)算法是broadcast join，即將join左邊的表分割到多個(gè)worker腰湾，然后將join右邊的表數(shù)據(jù)整個(gè)復(fù)制一份發(fā)送到每個(gè)worker進(jìn)行計(jì)算雷恃。如果右邊的表數(shù)據(jù)量太大，則可能會(huì)報(bào)內(nèi)存溢出錯(cuò)誤费坊。
[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)存報(bào)錯(cuò)
1）修改配置distributed-joins-enabled (presto version >=0.196)
2）在每次查詢開始使用distributed_join的session選項(xiàng)
-- set session distributed_join = 'true'
SELECT ... FROM large_table1 join large_table2
on large_table1.id = large_table2.id
核心點(diǎn)就是使用distributed join. Presto的這種配置類型會(huì)將左表和右表同時(shí)以join key的hash value為分區(qū)字段進(jìn)行分區(qū). 所以即使右表也是大表,也會(huì)被拆分.
缺點(diǎn)是會(huì)增加很多網(wǎng)絡(luò)數(shù)據(jù)傳輸, 所以會(huì)比broadcast join的效率慢.
-
使用Rank函數(shù)代替row_number函數(shù)來獲取Top N
在進(jìn)行一些分組排序場(chǎng)景時(shí)倒槐，使用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
) 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
9.多用with語句
使用Presto分析統(tǒng)計(jì)數(shù)據(jù)時(shí)，可考慮把多次查詢合并為一次查詢附井，用Presto提供的子查詢完成讨越。
這點(diǎn)和我們熟知的MySQL的使用不是很一樣两残。注意下列子查詢中的逗號(hào)。
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有去重的功能, 所以會(huì)引發(fā)內(nèi)存使用的問題.
如果你只是拼接兩個(gè)或者多個(gè)SQL查詢的結(jié)果, 考慮用UNION ALL
三把跨、無縫替換Hive表
如果之前的hive表沒有用到ORC和snappy磕昼，那么怎么無縫替換而不影響線上的應(yīng)用：
比如如下一個(gè)hive表：
CREATE TABLE bdc_dm.res_category(
channel_id1 int comment '1級(jí)渠道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';
建立對(duì)應(yīng)的orc表
CREATE TABLE bdc_dm.res_category_orc(
channel_id1 int comment '1級(jí)渠道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是一個(gè)備份表节猿，若線上運(yùn)行一段時(shí)間后沒有出現(xiàn)問題，則可以刪除該表漫雕。
四滨嘱、注意事項(xiàng)
- ORC和Parquet都支持列式存儲(chǔ)，但是ORC對(duì)Presto支持更好（Parquet對(duì)Impala支持更好）
- 對(duì)于列式存儲(chǔ)而言浸间，存儲(chǔ)文件為二進(jìn)制的太雨，對(duì)于經(jīng)常增刪字段的表，建議不要使用列式存儲(chǔ)（修改文件元數(shù)據(jù)代價(jià)大）魁蒜。對(duì)比數(shù)據(jù)倉庫囊扳，dwd層建議不要使用ORC，而dm層則建議使用
若在使用Presto和Hive過程中有任何問題兜看，歡迎給我留言锥咸！
