SQL Hive、Presto SQL 查询优化

hive和presto查询变慢主因是分区裁剪失效、join未优化、宽表select *及类型不一致。应避免函数包裹分区字段，显式广播小表，只查必要列，统一join/group by字段类型。

WHERE 条件里别用函数包裹分区字段

分区裁剪失效是 Hive 和 Presto 查询变慢最常见原因。比如 dt 是按天分区的字段，写成 WHERE to_date(event_time) = '2024-01-01'，Hive 无法识别这是在过滤分区，会扫全表；Presto 虽然部分版本能推导，但不保证稳定。

• 正确做法是直接用分区列做等值或范围比较：WHERE dt = '2024-01-01' 或 WHERE dt BETWEEN '2024-01-01' AND '2024-01-07'
• 如果原始数据没存分区字段，得在 etl 层补上，而不是查时再算——查时计算既不能裁剪，又拖慢执行
• Presto 对 date_parse、from_iso8601_date 等函数包裹分区列也一样失效，本质是表达式不可下推

JOIN 顺序和小表驱动原则在 Presto 里依然管用

Hive 的 mapJoin 在新版本里基本被自动优化覆盖了，但 Presto 不会自动把大表广播，得手动干预。如果你写 SELECT * FROM large_table l JOIN small_table s ON l.id = s.id，而 small_table 实际有 500MB，Presto 默认按 hash join 处理，容易 OOM 或 shuffle 溢出。

• 显式提示 Presto 广播小表：SELECT /*+ JOIN_ORDER(s, l) */ * FROM large_table l JOIN small_table s ON l.id = s.id（注意：Presto 350+ 支持 JOIN_ORDER hint，旧版用 BROADCAST）
• 小表判断标准不是行数，而是内存占用——Presto 默认广播阈值是 100MB（可调 query.max-memory-per-node），超了就得考虑预聚合或改用 bucket join
• Hive 里 /*+ MAPJOIN(s) */ 在 Tez/spark 引擎下有效，但在 mr 引擎里可能被忽略，得看 hive.auto.convert.join 是否 true

SELECT * 在宽表场景下对 Presto 内存压力特别大

宽表（比如上百列）+ LIMIT 100 看似轻量，但 Presto 会为每一列分配内存 buffer，尤其遇到 varchar、Array、map 类型时，实际内存占用可能是结果集的 3–5 倍。

• 永远只 SELECT 真正需要的列，别图省事写 *——Presto 不像 Hive 那样支持列裁剪的深度优化
• 如果只是想看数据样例，用 LIMIT 前先加 SELECT col1, col2, ...，避免 driver 节点因内存不足 fallback 到 disk spilling
• Hive on Tez 受限于 tez.grouping.split-count，宽表 + 小文件容易触发大量小 task，反而比 Presto 更卡，这时要合并小文件或调大 split size

GROUP BY 字段类型不一致导致 Hive 和 Presto 结果不一致

比如 user_id 在一张表里是 String，另一张是 bigint，JOIN 后再 GROUP BY，在 Hive 里可能隐式转成 string 再分组，Presto 则严格按类型分组，结果行数、聚合值都可能不同。

• 统一类型再 JOIN：CAST(user_id AS STRING) 或 CAST(user_id AS BIGINT)，别依赖隐式转换
• Hive 的 hive.mapred.mode=strict 会报错，但默认是 nonstrict，容易漏掉问题；Presto 直接报 Cannot cast xxx to yyy，错误更早暴露
• 字符串前导空格、大小写、NULL 表示方式（'NULL' vs NULL）也会干扰 GROUP BY，建议清洗阶段就标准化

真正难调的不是语法，是那些「看起来应该一样」的隐式行为差异——比如 Hive 把 NULL 当作一个分组值，Presto 也是，但两者的 null 排序规则、collation 处理、甚至 timezone 解析默认值都可能差一点，一碰上时间窗口或去重就露馅。