5.2 查询优化技术

5.2 查询优化技术

本节摘要：优化器是那台替你做选择却从不出面的同事。本节讲清它的两班倒机制——规则改写先把语句变成"更容易打分的样子"，代价模型再在候选计划里挑路——然后集中火力拆解 Join 的五种分发策略与各自的启用条件。对多数团队而言，这一节的投入产出比是全书最高的：其中一半手段不花一分硬件钱。

学习目标

阅读完本节，你应当能够：

1. 区分规则改写与代价选择的职责边界；
2. 背出五种 Join 策略的触发条件与网络开销量级；
3. 用 Hint 点名干预优化器并知道何时该收手；
4. 判断一条查询适不适合用物化视图接管（细节留到第 6 章）。

一、两班倒：先改形，再选路

第一班，基于规则的改写（RBO）。它做的都是稳赚的买卖：谓词下推让过滤在扫描时就地发生；列裁剪让宽表只读被引用的字段；常量折叠把 where day >= today-7 这类表达式提前算死；子查询展开把嵌套结构拉平成可优化的关联代数；投影合并消掉冗余算子层。这一班不问成本、只认逻辑等价，因此可以放心托管。

第二班，基于代价的选择（CBO）。给同一份逻辑计划枚举若干物理变体——Join 顺序、Join 分发方式、聚合是否两阶段——再用统计信息估算每种方案的 IO、CPU 与网络加权值，选出最便宜的。它的准确性完全押注在统计信息的新鲜度上，这也是 2.2 强调定时 ANALYZE 的另一半理由：CBO 拿着过期地图画路线，画得越认真错得越远。

新版 Nereids 优化器的显著差异在于第一班的深度（复杂表达式的记忆化与合并）和第二班的搜索广度（更大空间的动态规划），对十几张表的复杂关联尤其受益。升级带来的计划变化要观察一段时间——极少数老语句会因新计划反而抖动，此时用 Hint 固定旧路径作为过渡。

二、Join 五策：一张矩阵打天下

分布式环境下，两张表的网络关系决定了代价结构：

选择权排序是：能本地化的绝不搬运，能量小的绝不广播大的。日常操作两条：

-- 观察 CBO 选了哪条路 其一
EXPLAIN JOIN 结构看 BROADCAST 或 SHUFFLE 字样

-- 前缀命中不了自动推断时 显式点名 其二
SELECT /*+ SHUFFLE_JOIN(d) */ ...
​

点名之后必须在注释里写明撤除条件——统计信息补齐或版本升级后应择期摘除提示，否则固化就变成了债务。

三、聚合同样有讲究

两阶段聚合是默认形态：先局部预聚合再全局归并。但当分组基数极高（如按 user_id 明细分组）时，局部阶段几乎压不动行数，中间结果的传输量反而成为主要开销。识别特征是 Profile 里局部聚合输出的行数接近输入行数。对策视语句而定：确认业务是否真需要这个粒度；能否把大基数分组下沉为明细导出任务；或接受这台查询本来就该慢的现实，转而限制其并发。

TopN 的深度同理影响走向：深分页翻到十万开外时，堆排序与传输都会明显吃紧。产品交互层面若能约定"百万数据只提供前万级浏览"，会替数据库挡掉大量无意义的深翻页。

四、免费午餐清单

以下手段属于"改写即生效、零架构变更"级别，建议逐条纳入 SQL 评审规范：

1. 过滤条件直接写在基表列上，别套函数包一层（date(dt) 改为区间比较）；
2. 大查询显式列出所需列，杜绝无脑 SELECT 星号穿透三层视图；
3. 子查询能拉平就用关联展开，复杂的标量子查询考虑前置物化；
4. 维度表的常用过滤维度做前缀，保证 Runtime Filter 有生成素材；
5. 相似形态的报表共用一条 SQL 模板，享受缓存与计划复用的红利。

💡 关键直觉：优化器管得住物理计划，管不住语义膨胀。三层套娃视图加一层查询生成器，神仙也裁不出干净的扫描边界。

五、一个对照实验的记忆点

同样的双表关联，三条件下的实测对比常年稳定复现：维表三万行未过滤 Broadcast 耗时基准记为一倍；维表先被 WHERE 收敛到三百行再广播，降到零点四倍；两表 Colocation 化后落到零点三倍以内且方差最小。三个数字背后分别对应着本章的三件武器——谓词下推、Runtime Filter 与物理布局协同。调优的本质就是不断追问下一个数字还能不能更小。

六、统计信息：CBO 的燃料管理

前文反复说 CBO 押注统计信息的新鲜度，这里把采集本身讲成可执行的制度。手动采集用 ANALYZE 系列语句，可以对库、表或指定列执行；生产上更常见的是配一条周期任务，覆盖"行数变化超过阈值"的表。三个实操要点：

-- 对核心事实表采集直方图 高基数关联键单独指定
ANALYZE TABLE dwd_order_detail UPDATE HISTOGRAM ON user_id, cate_id;

-- 查看采集状态与过期情况
SHOW COLUMN STATS FROM dwd_order_detail;
​

其一，采集要挑时段。统计采集本身是一次扫描，安排在导入完成后的低谷期，避免与大促前的压测抢 IO。其二，不是越全越好：直方图对高基数关联键收益明显，对低基数布尔列纯属浪费，按列的区分度区别对待。其三，警惕"统计很新但计划仍差"：这说明代价模型的估算在该形态上失准，此时 Hint 才有登场资格——先确认燃料没问题，再怀疑发动机。

判断统计是否过期有个快捷信号：EXPLAIN 估算行数与 Profile 实际行数差出一个数量级，且偏差方向稳定（长期高估或长期低估），就是采集节奏没跟上数据膨胀速度。把这对数字的比值纳入巡检脚本，比等用户投诉便宜得多。

常见疑问

问：Hint 用多了会不会失控？ 会，所以要有台账。每个 Hint 必须登记三件事：加它的原因（哪次执行计划的证据）、期望的失效条件（统计补齐、版本升级）、复查日期。没有台账的 Hint 库存会在一年后变成没人敢删的雷区——优化器演进后，固化的旧提示反而阻止它选到更好的新计划。

问：Bucket Shuffle 与 Colocation 怎么选？ 看建模自由度。Bucket Shuffle 只要求两表"以相同方式分桶"，建表后即可受益，是无 Preparatory 工作的默认甜点；Colocation 额外要求声明同布属性并绑定副本分布，换来完全零重分布的极限性能——为高频核心关联买断，长尾关联留给前者。两者都不满足时才退到普通 Shuffle。

本节要点回顾

• RBO 改形、CBO 选路：前者免费托管，后者依赖统计信息。
• 五策略记网络代价：本地化优于复制优于广播优于双重 Shuffle。
• Hint 是止血带不是假肢：标注撤除条件，定期回顾。
• 高基数聚合与深翻页是天然贵的：先审语义再审实现。
• 评审规范五条免费午餐：写进去就能省出半台机器。

下一节转向参数与观测的实操面：Profile 怎么长期收集，资源怎么隔离，哪些参数值得动手。