1.优化
1.1 数据存储
- 合理设置分区：与Hive类似，Presto 会根据元信息读取分区数据，合理的分区能减少 Presto 数据读取量，提升查询性能。
- 使用列式存储：Presto 对 ORC 文件读取做了特定优化，因此在 Hive 中创建 Presto 使用的表时，建议采用 ORC 格式存储。相对于 Parquet，Presto 对 ORC 支持更好。
- 使用压缩：数据压缩可以减少节点间数据传输对 IO 带宽压力，对于即席查询需要快速解压，建议采用 Snappy 压缩。
- 预先排序：对于已经排序的数据，在查询的数据过滤阶段，ORC 格式支持跳过读取不必要的数据。
-- 对于经常需要过滤的字段可以预先排序 INSERT INTO TABLE table_name PARTITION ( field_name ) SELECT * FROM nation_file SORT BY sort_field_name; -- 如果通过 sort_field_name 字段对数据进行过滤则性能将提升 SELECT COUNT(*) FROM table_name WHERE sort_field_name='xxx';
1.2 查询SQL优化
- 只查询必要的字段：由于采用列式存储，选择需要的字段可加快字段的读取、减少数据量。避免采用 * 读取所有字段。
-- GOOD SELECT field_name_1, field_name_2, field_name_3 FROM table_name; -- NOT GOOD SELECT * FROM table_name;
- 过滤条件优先使用分区字段：对于有分区的表，WHERE 语句中优先使用分区字段进行过滤。partition_time 是分区字段，normal_time 是非分区字段。
-- GOOD SELECT field_name_1, field_name_2 FROM table_name WHERE partition_time='xxx'; -- NORMAL SELECT field_name_1, field_name_2 FROM table_name WHERE normal_time='xxx';
- GROUP BY 语句优化：合理安排 GROUP BY 语句中字段顺序对性能有一定提升。将 GROUP BY 语句中字段按照每个字段 DISTINCT 数据多少进行
降序排列。
-- GOOD SELECT field_name_1, field_name_2 FROM table_name GROUP BY id, type; -- NOT GOOD SELECT field_name_1, field_name_2 FROM table_name GROUP BY type, id;
- ORDER BY 时使用 LIMIT ：ORDER BY 需要扫描数据到单个 worker 节点进行排序，导致单个worker 需要大量内存。如果是查询 Top N 或者 Bottom N，使用 LIMIT 可减少排序计算和内存压力。
-- GOOD SELECT field_name_1, field_name_2 FROM table_name ORDER BY sort_field_name LIMIT 100; -- NOT GOOD SELECT field_name_1, field_name_2 FROM table_name ORDER BY sort_field_name;
- 使用近似聚合函数：Presto 有一些近似聚合函数，对于允许有少量误差的查询场景，使用这些函数对查询性能有大幅提升。比如使用 APPROX_DISTINCT(x) 函数比 COUNT(DISTINCT x) 有大概 2.3% 的误差。
SELECT APPROX_DISTINCT(field_name) FROM table_name;
- 用 REGEXP_LIKE 代替多个 LIKE 语句：Presto 查询优化器没有对多个 LIKE 语句进行优化，使用 REGEXP_LIKE 对性能有较大提升。
-- GOOD SELECT field_name_1, field_name_2 FROM table_name WHERE REGEXP_LIKE(field_name, 'GET|POST|PUT|DELETE'); -- NOT GOOD SELECT field_name_1, field_name_2 FROM table_name WHERE field_name LIKE '%GET%' OR field_name LIKE '%POST%' OR field_name LIKE '%PUT%' OR field_name LIKE '%DELETE%';
- 使用 JOIN 语句时将大表放在左边 ：Presto 中 JOIN 的默认算法是 BROADCAST JOIN，即将 JOIN 左边的表分割到多个 worker，然后将 JOIN 右边的表数据整个复制一份发送到每个 worker 进行计算。如果右边的表数据量太大，则可能会报内存溢出错误。
-- GOOD SELECT field_name_1, field_name_2 FROM large_table l JOIN small_table s ON l.id = s.id; -- NOT GOOD SELECT field_name_1, field_name_2 FROM small_table s JOIN large_table l ON l.id = s.id;
- 使用 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 -- NOT GOOD 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
1.3 无缝替换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.4 注意事项
ORC 和 Parquet 都支持列式存储，但是 ORC 对 Presto 支持更好（Parque t对 Impala 支持更好）
对于列式存储而言，存储文件为二进制的，对于经常增删字段的表，建议不要使用列式存储（修改文件元数据代价大）。对比数据仓库，dwd 层建议不要使用 ORC，而 dm 层则建议使用。
2.实践
2.1 加快在Presto上的数据统计
很多的时候，在 Presto 上对数据库跨库查询，例如 MySQL 数据库。这个时候 Presto 的做法是从MySQL 数据库端拉取最基本的数据，然后再去做进一步的处理，例如统计等聚合操作。
举个例子：
SELECT COUNT(id) FROM table_name WHERE condition_field=1;
上面的SQL语句会分为3个步骤进行：
- Presto发起到Mysql数据库进行查询
SELECT id FROM table_name WHERE condition_field=1;
- 对结果进行count计算
- 返回结果
对于 Presto 来说，其跨库查询的瓶颈是在数据拉取这个步骤。若要提高数据统计的速度，可考虑把 MySQL 中相关的数据表定期转移到 HDFS 中，并转存为高效的列式存储格式 ORC。定时归档是一个很好的选择，这里还要注意，在归档的时候要选择一个归档字段，如果是按日归档，可以用日期作为这个字段的值，采用 yyyyMMdd 的形式，例如20211214。
-- 创建归档数据库的SQL语句如下 CREATE TABLE IF NOT EXISTS table_name ( id INTEGER, ........ partition_date INTEGER ) WITH ( format = 'ORC', partitioned_by = ARRAY['partition_date'] ); -- 查看创建的库结构（只适用于 Presto） SHOW CREATE TABLE table_name;
带有分区的表创建完成之后，每天只要更新分区字段 partition_date 就可以了，Presto 就能将数据放置到规划好的分区了。如果要查看一个数据表的分区字段是什么，可以下面的语句：
SHOW PARTITIONS FROM table_name;
2.2 分区字段过滤
如果数据被规当到 HDFS 中，并带有分区字段。在每次查询归档表的时候，要带上分区字段作为过滤条件，这样可以加快查询速度。因为有了分区字段作为查询条件，就能帮助 Presto 避免全区扫描，减少 Presto 需要扫描的 HDFS 的文件数。
2.3 使用WITH语句
使用 Presto 分析统计数据时，可考虑把多次查询合并为一次查询，用 Presto 提供的子查询完成。这点和MySQL的使用不是很一样。例如：
-- 子查询 subquery_1 注意：多个子查询需要用逗号分隔 WITH subquery_1 AS ( SELECT a1, a2, a3 FROM Table_a WHERE a3 between 20180101 and 20180131 ), -- 最后一个子查询后不要带逗号 subquery_2 AS ( SELECT b1, b2, b3 FROM Table_b 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;
2.4 减少读表次数
具体做法是，将使用频繁的表作为一个子查询抽离出来，避免多次 read。
2.5 字段名引用
Presto 中的字段名引用使用双引号分割（跟 GreenPlum 一样），这个要区别于MySQL的反引号`。
SELECT field_name AS "fieldName" FROM table_name;
2.6 时间函数
对于 timestamp，需要进行比较的时候，需要添加 timestamp 关键字，而 MySQL 中对 timestamp 可以直接进行比较。
-- MySQL的写法 SELECT time_field FROM table_name WHERE time_field > '2017-01-01 00:00:00'; -- Presto中的写法 SELECT time_field FROM table_name WHERE time_field > timestamp '2017-01-01 00:00:00';
2.7 MD5函数使用
Presto 中 MD5 函数传入的是 binary 类型，返回的也是 binary 类型，要对字符串进行 MD5 操作时，需要转换。
SELECT TO_HEX(MD5(TO_UTF8('1212')));
2.8 不支持 INSERT OVERWRITE 语法
Presto中不支持 INSERT OVERWRITE 语法，只能先 DELETE，然后 INSERT INTO。
2.9 ORC和PARQUET格式
- Presto 中对 ORC 文件格式进行了针对性优化，但在 Impala 中目前不支持 ORC 格式的表，Hive 中支持 ORC 格式的表，所以想用列式存储的时候可以优先考虑 ORC 格式。
- Presto目前支持 PARQUET 格式，支持查询，但不支持 INSERT。
