Hive SQL 优化

概述

Hive 查询慢，九成以上是"没有裁剪、倾斜没处理、Join 策略选错"。本文按"先裁剪 → 再 Join → 后参数"的顺序给出 Hive SQL 优化方法论：数据倾斜、Join 优化、Map/Reduce 调优、谓词下推与矢量化查询。

---

一、优化总原则

1. 数据裁剪先行：分区裁剪、列裁剪、谓词下推（减少数据量）
2. Join 策略选型：大小表、倾斜处理（减少计算量）
3. 参数与资源：并行度、内存、矢量化（提升单任务效率）
4. 最后看数据分布：小文件、数据倾斜（治本）

先治数据，再调参数。数据量没降下来，参数怎么调都有限。

---

二、数据裁剪

2.1 分区裁剪

所有查询必须带上分区条件，否则全表扫描：

-- 错误：全表扫
SELECT * FROM dwd_order WHERE create_time='2026-08-22';
-- 正确：分区裁剪
SELECT * FROM dwd_order WHERE dt='20260822';

2.2 列裁剪

只 SELECT 需要的列，Hive 会裁剪文件读取（ORC/Parquet 按列读取）：

-- 只取两列，避免整行反序列化
SELECT order_id, amount FROM dwd_order WHERE dt='20260822';

2.3 谓词下推

把过滤条件下推到 Scan 阶段，尽早减少数据：

-- Hive 自动把 user_name='张三' 下推到读取阶段（ORC 列裁剪 + 过滤）
SELECT o.order_id FROM dwd_order o
JOIN dim_user u ON o.user_id = u.user_id
WHERE u.user_name = '张三' AND o.dt = '20260822';

检查执行计划确认下推生效：EXPLAIN SELECT ... 看 Filter 是否在 Join 之前。

---

三、Join 优化

3.1 大小表 Join（MapJoin）

小表（可入内存，如维度表）用 MapJoin，在 Map 端广播小表，省掉 Reduce 阶段：

-- 自动开启（默认 hive.auto.convert.join=true）
SELECT /*+ MAPJOIN(u) */ o.order_id, u.user_name
FROM dwd_order o JOIN dim_user u ON o.user_id = u.user_id;

-- 或参数控制
SET hive.auto.convert.join=true;
SET hive.mapjoin.smalltable.filesize=25000000;  -- 小表阈值 25MB

3.2 Join 顺序

hive.cbo.enable=true（默认开）时，CBO 按数据量自动重排 Join 顺序：小表在前，大表在后，减少中间结果。

3.3 倾斜 Join 处理

场景：Join 键分布极不均匀（如某个用户占 80% 订单）。

方案	说明
大小表倾斜（MapJoin）	大表小表用 MAPJOIN 规避 Reduce 倾斜
随机前缀扩容	热点键加随机前缀，小表对应键扩容 N 倍
过滤热点	先单独处理热点键，再合并结果
桶 Join	按相同桶键分桶，桶内 Join 均匀

3.4 参数开关

-- 倾斜 Join 自动处理（Hive 3 支持）
SET hive.optimize.skewjoin=true;

---

四、Map/Reduce 数调优

4.1 Map 数

Map 数由输入分片决定，小文件过多导致 Map 过多：

手段	说明
合并小文件	hive.merge.mapfiles=true 合并 Map 输出
控制输入分片	mapreduce.input.fileinputformat.split.maxsize
源头治理	写侧控制文件数（见第 3 周规范）

4.2 Reduce 数

参数	说明
hive.exec.reducers.bytes.per.reducer	每个 Reducer 处理字节数（默认 256MB），决定默认 Reduce 数
mapreduce.job.reduces	手动指定 Reduce 数
hive.exec.reducers.max	Reduce 数上限

Reduce 数 = 数据量 / 每 Reducer 处理量。手动设置时避免与自动推断冲突。

4.3 并行与内存

-- 无依赖阶段并行执行
SET hive.exec.parallel=true;
-- 容器内存
SET mapreduce.map.memory.mb=2048;
SET mapreduce.reduce.memory.mb=4096;

---

五、矢量化查询

矢量化（Vectorization）让引擎一批一批处理行而非逐行，显著提升 CPU 效率：

SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled=true;

场景	收益
大表过滤/聚合	明显（2~5 倍）
复杂 UDF	有限（需支持向量化）
小查询	忽略

搭配 ORC + ZSTD + 列裁剪，是大表扫描的黄金组合。

---

六、数据倾斜专项

6.1 倾斜现象

• 某个/某几个 Reduce 任务跑几个小时，其余秒完。
• 同一 Join 键数据占比失衡（订单按用户、日志按 IP）。

6.2 定位方法

-- 查看 Join/Group 键的分布
SELECT user_id, COUNT(*) FROM dwd_order GROUP BY user_id ORDER BY 2 DESC LIMIT 10;

6.3 两阶段聚合（Group By 倾斜）

-- 第一步：加随机前缀打散，局部聚合
INSERT OVERWRITE TABLE tmp_agg
SELECT user_id, prefix, SUM(cnt) FROM (
  SELECT user_id, rand()*10 AS prefix, COUNT(*) AS cnt
  FROM dwd_order GROUP BY user_id, rand()*10
) t GROUP BY user_id, prefix;

-- 第二步：去掉前缀，全局聚合
SELECT user_id, SUM(cnt) FROM tmp_agg GROUP BY user_id;

求和可用两阶段聚合；去重/取中位数需特殊处理（Bitmap 或扩展）。

6.4 倾斜根治

层面	手段
数据	上游打散热点键、均衡分区
表设计	分桶 + 桶键选均匀列
SQL	两阶段聚合、MapJoin、随机前缀
资源	热点任务单独分配大资源

---

七、优化排查流程

1. EXPLAIN 看计划：分区裁剪/谓词下推是否生效
2. 看作业日志：Map/Reduce 数量、各任务耗时分布
3. 检查输入：数据量、小文件数、倾斜键
4. 逐项优化：先裁剪 → 再 Join → 后参数
5. 对比验证：优化前后耗时与资源

常见问题速查

现象	原因与处理
查询扫全表	无分区条件，或分区字段类型/值不匹配
某 Reduce 卡死	数据倾斜，用两阶段聚合或 MapJoin
Map 数量爆炸	小文件过多，先合并再查询
Join 极慢	未走 MapJoin，检查小表阈值与 join 提示
内存溢出	Reduce 数据量大，调大容器内存或增加 Reduce 数