Hive SQL 性能诊断一条慢 SQL 从定位到优化的完整路径一条 Hive SQL 跑了 40 分钟还没出来别急着加资源先诊断再开方可能改一行代码就快了 10 倍。一、先定位瓶颈Hive SQL 的慢到底慢在哪大部分 Hive SQL 慢不是机器不够好是写法有问题。但在优化之前你得先知道它到底卡在哪一步。Hive 的执行流程可以拆成三个阶段三个阶段里Shuffle 阶段最容易出问题。因为数据要从 Map 端通过网络传输到 Reduce 端而且某个 key 如果数据特别多对应的 Reduce task 就会卡住其他 task 干等着这就是数据倾斜。怎么快速判断慢在哪里打开 YARN 的 ApplicationMaster 界面或者 Hue/Hive 的查询计划看三点Map 阶段耗时占比如果 Map 阶段就占了 80% 以上的时间说明读取效率有问题小文件太多 / 扫描数据量太大Shuffle 数据量如果 Shuffle 传输了几百 G 数据说明 join 或 group by 的 key 太多了Reduce 任务进度打开 Reduce task 列表如果 99% 的 task 都跑完了就剩 1 个跑了十几分钟那就是典型的数据倾斜二、最常见的坑数据倾斜怎么治数据倾斜是 Hive SQL 优化里最高频的问题。说白了就是 group by 或 join 的时候某个 key 的数据特别多导致处理这个 key 的 Reduce task 成为瓶颈。-- -- 问题场景按 user_id 统计订单数 -- 有些大用户一天几百单有些只有几单 -- 处理大用户的 Reduce task 会特别慢 -- SELECT user_id, COUNT(1) AS order_cnt, SUM(amount) AS total_amount FROM order_detail WHERE dt 20240715 GROUP BY user_id;解决方案 1两阶段聚合加盐去盐思路是先把倾斜的 key 打散聚合一次后再合并。这样大 key 的数据就不会全落到一个 Reduce task 里。-- -- 第一阶段给 key 加随机后缀打散数据 -- concat floor(rand()*100) 随机分成 100 份 -- 这样大用户的订单被分到了 100 个 Reduce task -- WITH salted AS ( SELECT -- 给 user_id 加 0~99 的随机后缀 concat(user_id, _, floor(rand() * 100)) AS salted_key, amount FROM order_detail WHERE dt 20240715 ), -- -- 第一阶段聚合在 100 个分片里各自聚合 -- partial_agg AS ( SELECT salted_key, COUNT(1) AS partial_cnt, SUM(amount) AS partial_amount FROM salted GROUP BY salted_key ) -- -- 第二阶段去掉随机后缀合并最终结果 -- split 函数取 _ 前面的原始 user_id -- SELECT split(salted_key, _)[0] AS user_id, SUM(partial_cnt) AS order_cnt, SUM(partial_amount) AS total_amount FROM partial_agg GROUP BY split(salted_key, _)[0];这种方式的代价是多了一次聚合但如果数据倾斜严重收益远大于代价。通常能把几十分钟的查询压缩到几分钟。不过需要注意rand() 函数在 Hive 中会生成随机数如果数据量特别大加盐的随机后缀建议控制在10-50份之间分片太多反而会让第一次聚合产生大量小结果集。解决方案 2Map 端 Join小表 join 大表如果一张表很小比如维度表可以用 Map Join 避免 Shuffle-- -- /* MAPJOIN(b) */ 把小表 b 广播到每个 Map task -- join 在 Map 端完成不需要 Shuffle -- 前提小表能放进内存默认 25MB可调 -- SELECT /* MAPJOIN(dim_user) */ o.order_id, o.amount, dim_user.user_level, dim_user.region FROM order_detail o JOIN dim_user ON o.user_id dim_user.user_id WHERE o.dt 20240715;三、文件层优化小文件是隐形的性能杀手很多 Hive 慢的问题根子在存储层——表下面有几万个甚至几十万个小文件。小文件为什么慢每个文件都要启动一个 Map task 去读。2 万个文件 2 万个 Map task光是启动 task 的开销就得好几分钟更别说 YARN 的调度等待。解决策略-- -- 1. 合并小文件写入 -- 设置每个文件至少 256MB减少文件数量 -- SET hive.merge.mapfiles true; -- Map-only 任务结束时合并 SET hive.merge.mapredfiles true; -- MapReduce 任务结束时合并 SET hive.merge.size.per.task 268435456; -- 合并后目标大小 256MB SET hive.merge.smallfiles.avgsize 16777216; -- 平均小于 16MB 触发合并 -- -- 2. 调整 Map 数量 -- 不是越多越好合理的 Map 数 ≈ 数据量 / 每个 Map 处理量 -- SET mapreduce.input.fileinputformat.split.maxsize 268435456; -- 256MB SET mapreduce.input.fileinputformat.split.minsize 134217728; -- 128MB另外如果表是分区表且分区特别多比如按小时分区保留了几年记得定期清理历史小分区或者做好分区生命周期管理。四、执行计划分析EXPLAIN 才是最好的医生上面说的都是常见经验但真正要精准优化还是得看执行计划。-- -- EXPLAIN 显示执行计划不实际执行 -- 重点看Map Join 是否生效、数据倾斜提示、Stage 数量 -- EXPLAIN SELECT o.user_id, u.region, COUNT(1) AS cnt FROM order_detail o JOIN dim_user u ON o.user_id u.user_id WHERE o.dt 20240715 GROUP BY o.user_id, u.region;看执行计划要关注几个关键信号是否有 Map Join执行计划里出现Map Join Operator说明小表广播生效了有多少个 StageStage 越多中间写磁盘的次数越多越慢是否有 Reduce Sink Operator每一个 Reduce Sink 就是一次 Shuffle能少则少还有就是开启 CBOCost-Based Optimizer让 Hive 根据统计信息自动选择最优执行计划-- 开启基于代价的优化器 SET hive.cbo.enable true; -- 开启后Hive 会在 join 时自动选择 Broadcast Hash Join 还是 Shuffle Hash Join -- 先收集统计信息CBO 依赖统计信息做决策 ANALYZE TABLE order_detail COMPUTE STATISTICS; ANALYZE TABLE order_detail COMPUTE STATISTICS FOR COLUMNS user_id, amount;五、总结Hive SQL 优化的思路我总结为四步走先看业务这个 SQL 真的需要扫这么多数据吗分区裁剪、列裁剪做了吗再看执行计划EXPLAIN 告诉你瓶颈在哪个 Stage是 Map 慢还是 Shuffle 慢对症下药数据倾斜就加盐小文件就合并大表 join 小表就 Map Join验证效果改完后看执行时间必要时反复调最重要的是别一上来就加资源。很多时候加了 3 倍的 executor速度才快 20%就是因为瓶颈不在算力而在数据分布和 IO。先诊断再开方这才是专业的数据工程师该干的事。本文由朱大喜原创下一篇聊 AI 用户画像自动打标欢迎关注~最后提醒一点这个方案在上生产之前建议先用灰度流量验证一周确认资源消耗在预期范围内再全量推送。我们在实际项目中因为跳过了这步有一次把缓存集群打挂了教训深刻。