SQL Life

Hive SQL优化建议

时间:2024/3/10 11:45:14  作者:枫叶  来源:solgle.com  查看:386  评论:0
内容摘要:根据大家 sql 写法上存在的一些问题,提出以下优化建议:一、 先减少基表数据量后再做关联1. 【说明】1.1. 将 sql 语句中 where 谓词逻辑都尽可能提前执行,减少下游处理的数据量(缩小数据集,再与外表关联)1.2. 不是所有字段都需要的时候,少用 select *,...
根据大家 sql 写法上存在的一些问题,提出以下优化建议:
 
一、 先减少基表数据量后再做关联
1. 【说明】
1.1. 将 sql 语句中 where 谓词逻辑都尽可能提前执行,减少下游处理的数据量(缩小数
据集,再与外表关联)
1.2. 不是所有字段都需要的时候,少用 select *,只筛选自己需要的字段
1.3. 尽量给出时间范围或者分区限定等条件
2. 【举例】
2.1. 举例 1
select a.*,b.*
from tablea a
left join tableb b on a.c1=b.c1
where a.p_date='xx' and b.p_date='xx'
改写为:
select a.c1,a.c2,b.c1,b.c2 、
from
(selct c1,c2 from tablea where p_date='xx') a ---过滤条件在内部处理, 只筛选需要的字段
left join
(selct c1,c2 from tableb where p_date='xx') b
on a.c1=b.c1
注:现在的高级优化器已经可以自己识别,做到部分合理改写。
 
二、 避免关联产生笛卡尔积
1. 【说明】
1.1. 从多表查询数据时,表与表之间应有相等关联的 where 条件
1.2. Join 关联时,表间需 on 条件做相等关联,并且多个条件之间用 and 连接
2. 【举例】
2.1. 举例 1
select aa.*, bb.wjfl,bb.dqsxed, bb.hxrq
from a_tab1 aa,
b_tab2 bb
where aa.hxjyrq > bb.hxrq; ---两表就缺少相等的关联条件
修改为:
select aa.*, bb.wjfl,bb.dqsxed, bb.hxrq
from a_tab1 aa,
b_tab2 bb
where aa.pkid=bb.pkid and aa.hxjyrq > bb.hxrq;
2.2. 举例 2
Select a.f1,a.f2,b.f3.b.f4
From x_tab1 a
Inner join x_tab2 b on a.dgckzh<> b.dgckzh
应改为:
Select a.f1,a.f2,b.f3.b.f4
From x_tab1 a
Inner join x_tab2 b on a.pkid=b.pkid
where a.dgckzh<> b.dgckzh
 
三、 正确区分使用 inner join,left join,right_join
1. 【说明】
1.1. 优先使用 inner join,缺点是只返回两个表条件同时满足的记录
1.2. 当 a left join b,说明 a 将保留所有记录,并扩充 b 表的选择字段,而 b 表不参与条
件判断
1.3. 当 a left join b,说明 b 表只做选择字段,若 b 表需要 where 非空条件判断,那应考
虑用 inner join
1.4. right join 同理略
2. 【举例】
2.1. 举例 1
Select a.f1,a.f2,b.f3.b.f4
From x_tab1 a
Left join x_tab2 b on a.pkid=b.pkid
Where a.f1>5
and b.f2=’2023-12-31’
and a.f5>b.f5
改用 inner join 结果是一样的,因为 left join 是 a 表全部返回,b 表会有空值行,一旦加上
b 表的非空判断条件,空值行就会被剔除掉。
select a.f1,a.f2,b.f3.b.f4
From (select f1,f2,f5,pkid from x_tab1 where a.f1>5) a
inner join (select f3,f4,f5,pkid from x_tab2 where a.f2=’2023-12-31’) b on
a.pkid=b.pkid
where a.f5>b.f5
 
四、 多使用 limit
1. 【说明】
1.1. 只是查看样例数据,需要加 limit
1.2. 限制记录条数,或可以不返回全量数据,可以加 limit
1.3. 使用 order by 后尽量使用 limit 限制
2. 【举例】
2.1. 举例,查看 10 条样例数据
select a.* from x_tab1 a limit 10;
 
五、 关联中小表前置,大表后置
1. 【说明】
1.1. Hive 解析关联的 sql 语句时,会默认将最后一个表作为大表,将前面的表作为小表
并试图读入内存,如果表顺序写反了,引发 out of memory 的风险很高。
1.2. 通常计算顺序会从上往下,这时调整 Join 中表的顺序,小表与大表先 join,减少后
续的计算量。
2. 【举例】
2.1. 举例:a 表数据量最多 2000 条记录,b 表和 c 表数据量都在 2 亿条记录。
select a.col1,a.col2,b.col1,b.col2 ,c.col1
From (select col2,col5 from large_table1 where col1>5000) b
Inner join (select col1,col5 from large_table2 where col1>5000) c on b.col5=c.col5
Inner join smaill_table a on b.col5=a.col5;
应改为:
select a.col1,a.col2,b.col1,b.col2 ,c.col1
From smaill_table a
Inner join (select col2,col5 from large_table1 where col1>5000) b on a.col5=b.col5
Inner join (select col1,col5 from large_table2 where col1>5000) c on a.col5=c.col5;
 
六、 对关联字段做非空处理
1. 【说明】
1.1. 如果关联字段空值较多,产生数据倾斜,会影响关联效率,可以替换成不影响逻辑
的数据后再做联接。
1.2. 用转换后的连接字段去做关联。
2. 【举例】
2.1. 举例 1,用随机数替换空值为负数,正常情况下 uid 只有正数
case when a.uid is null then cast(rand() * -10240 as int) else a.uid end uid ,
或者
nvl(uid,rand() * -1)
2.2. 举例 2:剔除空值的关联,再合并空值
select a.* from a join b on a.user_id is not null and a.user_id=b.user_id
union all
select a.* from a where a.user_id is null
 
七、 少使用「%」前导的查询
1. 【说明】
1.1. 对于 like 语句,尽量不使用%前置,即 ‘%机构%’,在确定机构打头的情况下采用 ’
机构%’来匹配
1.2. 若对正则表达式比较熟悉,可以使用 REGEXP,RLIKE
 
八、 使用 exists 代替 in,用 not exists 代替 not in
1. 【说明】
1.1. 当 in 的数据量比较大时用 exist 来代替
1.2. not exists 同理略
2. 【举例】
select a.f1,a.f2
From a
Where a.f1 in (select f1 from b)
应改为
select a.f1,a.f2
From a
Where exists (select 1 from b where a.f1=b.f1)
 
九、 持久化,建立中间落地表,一次生成,多次引用
1. 【说明】
1.1. 将复杂计算结果保存在持久化临时表中可以避免重复计算。提高查询性能并降低负
载。
1.2. 针对从多个表中检索数据,并对其进行聚合、过滤或排序。将这些数据存储在持久
化临时表中可以提高查询性能,避免对底层表的重复访问。



 

solgle.com 版权所有,欢迎分享!!!

上一篇:没有了
下一篇:Oceanbase分布式事务技术
相关评论
 img1 img2 img3 img4 img5 img6 img7 img8 img9 img10
评论者:      验证码:  点击获取验证码
   Copyright © 2013-2028 solgle.com,All rights reserved.[solgle.com] 公安机关备案号:51010802000219
Email:solgle@solgle.com; weixin:cd1008610000 ICP:蜀ICP备14011070号-1