Hive SQL优化建议

加入收藏设为首页

SQL Life: Hive SQL优化建议
时间：2024/3/10 11:45:14 作者：枫叶来源：solgle.com 查看：418 评论：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分布式事务技术

相关评论

本类更新

本类推荐

本类排行

: 国家数据 | 中证指数 | Oracle官方文档 | 沪深行情 | 流量统计 | 深交所 | 上交所 | 中国证券登记 | 同业拆放利率 | 巨潮资讯 | 指数估值 | 看财报 |

联系我们 - 关于我们 - 网站同盟