【编程学习】关于Presto SQL与其优化方法

6Young2024-07-232024-09-24

Presto中的SQL查询优化

Presto的优缺点：

优点
- Presto 与 Hive 对比，都能够处理 PB 级别的海量****数据分析，但 Presto 是基于内存运算，减少没必要的硬盘 IO，所以更快。**
- 能够连接多个数据源，跨数据源连表查，如从 Hive 查询大量网站访问记录，然后从 Mysql 中匹配出设备信息**。
缺点
- 虽然能够处理 PB 级别的海量数据分析，但不是代表 Presto 把 PB 级别都放在内存中计算的。而是根据场景，如 count，avg 等聚合运算，是边读数据边计算，再清内存，再读数据再计算，这种耗的内存并不高。但是连表查，就可能产生大量的临时数据，因此速度会变慢，反而 Hive此时会更擅长。
- 为了达到实时查询，可能会想到用它直连 MySql 来操作查询，这效率并不会提升，瓶颈依然在 MySql，此时还引入网络瓶颈，所以会比原本直接操作数据库要慢。

Presto SQL优化

过滤条件优先使用分区字段：对于有分区的表，WHERE 语句中优先使用分区字段进行过滤。
GROUP BY 语句优化：合理安排 GROUP BY 语句中字段顺序对性能有一定提升。将 GROUP BY 语句中字段按照每个字段 DISTINCT 数据多少进行降序排列。（类别多的先分组）
用 REGEXP_LIKE 代替多个 LIKE 语句：Presto 查询优化器没有对多个 LIKE 语句进行优化，使用 REGEXP_LIKE 对性能有较大提升。
使用 JOIN 语句时将大表放在左边：Presto 中 JOIN 的默认算法是 BROADCAST JOIN，即将 JOIN 左边的表分割到多个 worker，然后将 JOIN 右边的表数据整个复制一份发送到每个 worker 进行计算。如果右边的表数据量太大，则可能会报内存溢出错误。

把多次查询合并为一次查询，用 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;

用group by 代替 select distinct，更快
数据倾斜：join：增加一段随机数；group by set hive.groupby.skewindata=true;

Mysql优化

顺便记录一下Mysql中常用的提高运行效率的技巧

小表驱动大表

小表驱动大表，也就是说用小表的数据集驱动大表的数据集。假如有order和user两张表，其中order表有10000条数据，而user表有100条数据。

select * from order
where user_id in (select id from user where status=1);

select * from order
where exists (select 1 from user where order.user_id = user.id and status=1);