自适应计划与 CBO 与 RBO

• AHI 由 InnoDB 运行时按访问模式自动构建，无需 DDL
• 只对等值点查有效，占用 buffer pool 内存
• 代价：内存、latch 争用与不可预测性；监控入口

自适应哈希索引（AHI）不是用户创建的索引，而是 InnoDB 在运行期根据观察到的访问模式自动构建：当存储引擎发现某些 B-Tree 页面被反复等值访问（如主键或二级索引的等值点查），会在内存中为这些页建立哈希索引项（键为索引键前缀，值为指向 B-Tree 页/记录的指针），后续点查直接从哈希表命中，跳过 B-Tree 的中间层遍历，减少 CPU 与锁等待。构建完全在运行时发生：由每页访问计数器驱动，无需 DDL 或人工干预，innodb_adaptive_hash_index=ON 时启用（8.0 默认 ON），并可用 innodb_adaptive_hash_index_parts 分区哈希表降低争用。

代价：① 占用 buffer pool 内存（哈希表本身）；② 构建与维护有 CPU 与 latch 开销，高并发下可能成为争用热点（因此部分场景反而关闭它）；③ 只对等值查询（=、IN）有效，范围/排序查询无收益。监控：SHOW ENGINE INNODB STATUS 中的 hash index 相关统计，或通过 performance_schema 观察；若热点等值查询无明显提升，可评估关闭并压测对比。

本题考察 AHI 的机制与代价：运行时自动构建、等值专用、内存与争用成本，以及"默认开启但需监控评估"的工程态度。

• PG 没有执行期切换 join 方法的自适应算子
• 自适应体现在 generic/custom plan 选择与统计驱动的计划更新
• SQL Server Adaptive Join 才是运行期切换，PG 用统计校准与 Hint 近似

严格说，PostgreSQL 没有 SQL Server 式的"执行期自适应连接"（同一计划在执行过程中根据实际行数在 Hash Join 与 Nested Loop 之间切换），PG 的计划在执行前完全确定。PG 的"自适应"体现在两个层面：① 参数化查询的计划自适应——prepared statement 先用 custom plan 试探参数，后续按"generic 与 custom 代价比较"自动选择计划类型（plan_cache_mode 可强制）；② 统计驱动的计划自愈——统计更新后下一次执行自动生成新计划。

因此"PG 自适应连接的应用场景"应理解为：当中间结果基数高度不确定、统计无法准确估算时，PG 的应对手段是——提高统计精度（扩展统计）、用 auto_explain 观察实际行数、必要时用 pg_hint_plan 固定 join 方法；若追求真正的运行期自适应，SQL Server 2017+ 的 Adaptive Join（开始时按阈值选 hash 或 NL，执行中发现不符可切换）与 Oracle 的自适应计划才是该特性的原生实现。

本题考察概念辨析：先澄清 PG 无执行期 join 切换，再指出 PG 的自适应形态（generic/custom 与统计驱动），最后对比 SQL Server 的原生实现。

• 自适应点：generic/custom plan、autovacuum 统计更新、JIT 编译、GEQO
• 局限：计划生成后执行中不变、哈希溢出只能落盘
• 替代：扩展统计、pg_hint_plan、并行参数

PostgreSQL 的"自适应优化"是一组松散机制而非单一特性：① 计划层——prepared statement 的 generic/custom plan 自动选择（前 5 次 custom 试探 + 代价比较）；② 统计层——autovacuum 按修改行数阈值自动 ANALYZE，统计变化推动计划自我更新；③ 执行层——JIT（LLVM）在优化器估算代价高（行数大、表达式复杂）时自动编译表达式与谓词，提升执行速度；④ 规划层——连接数超过 geqo_threshold 时切换到 GEQO 启发式搜索，避免动态规划爆炸。

局限：计划一旦生成不会在执行中改变（无自适应 join 与内存自适应）；哈希表溢出只能落盘、无法扩容重选；JIT 只覆盖表达式求值。替代/增强：对不确定的中间结果用扩展统计与 target 提升估算，关键语句用 pg_hint_plan 固定，大查询配合并行参数（max_parallel_workers_per_gather）手动调优——整体遵循"先校准统计，再考虑 Hint"。

本题考察 PG 自适应能力的全景与边界：四个层面的自适应机制、执行中不可变的局限、以及配套调优手段。

• 所有估算（选择率、join 基数、扫描行数）都源自统计
• 陈旧后果：扫描方式、join 顺序与方法、并行度误判
• 典型场景：批量写入后未 ANALYZE；修复链路

CBO 的一切决策建立在统计之上：表行数（reltuples）、列分布（MCV/直方图/n_distinct/null_frac）、物理相关性（correlation）、索引基数（n_diff_pfx）——扫描方式、join 顺序、join 方法、并行度全部由这些估算驱动。统计陈旧时：① 行数高估/低估使"全表 vs 索引扫描"天平倾斜（如删掉 90% 数据后仍按旧行数选全表扫描）；② join 基数误估导致 join 顺序倒置（大表驱动小表）或 join 方法失配（该用 Hash 却用 NL）；③ 并行度按旧行数决定，可能过度或不足并行。

典型场景：ETL 批量灌入后未 ANALYZE、大表高频删除、数据变化低于自动触发阈值但绝对量巨大。修复链路：EXPLAIN ANALYZE 对比估算与实际行数定位偏差 → 手动 ANALYZE/ANALYZE TABLE → 必要时提升 target/采样页、建扩展统计 → 长期监控自动统计是否跟得上。

本题考察 CBO 对统计的依赖链条：估算输入→决策输出→陈旧后果→修复闭环，用典型场景串联。

• 转换规则：子查询展开、谓词下推/上拉、视图合并、外连接消除、常量折叠
• 先逻辑改写再代价搜索：改写改变计划空间
• optimizer_switch（MySQL）与 enable_* 开关（PG）的差异

CBO 的"转换规则"（transformations）在代价搜索之前把 SQL 改写为更优的等价形式，常见：① 子查询展开（unnest/decorrelation）——把 IN/EXISTS/派生表子查询改写为 join/半连接，使优化器能自由调整 join 顺序；② 谓词下推——把 WHERE/HAVING 条件压到数据源（扫描、join 输入、子查询内）提前减行，谓词上拉则把子查询内条件提升供外层利用；③ join 顺序与形状搜索——动态规划枚举左深/右深/稠密树，比较中间结果基数；④ 其他——视图合并、外连接转内连接、常量折叠、IN 物化 vs 半连接选择、聚合下推等。

协同方式：改写先"扩大/改变等价计划空间"，代价模型再在空间内搜索最低代价计划——改写不正确会漏掉最优计划（如未展开子查询导致 join 顺序被锁死），改写过度则搜索爆炸。MySQL 通过 optimizer_switch（semijoin、materialization 等）逐项开关；PG 相关开关（enable_* 系列）控制执行算子而非改写。诊断改写失败：EXPLAIN 观察子查询是否保留、派生表是否物化。

本题考察 CBO 改写与搜索的两阶段协同：改写定义计划空间、代价决定最终计划，以及两库的开关差异。

• 流程：解析→改写→路径生成→代价估算→选择最低代价
• 动态规划/GEQO 枚举 join 顺序，统计×代价参数决定胜负
• 局限：估算误差、搜索空间裁剪、无执行中自适应

CBO 的工作流程：① 解析与语义分析；② 逻辑改写（视图合并、子查询展开、谓词下推等）；③ 计划枚举——为每个关系生成访问路径（顺序扫描、索引扫描、位图扫描等），用动态规划（或 GEQO 启发式）枚举 join 形状与顺序，组合生成候选计划；④ 代价评估——把统计信息（行数、分布、页数）代入代价模型（I/O 权重、CPU 权重、算子开销）计算每棵候选计划的总代价；⑤ 选择总代价最小的计划交执行器执行。

本质是"统计 × 代价参数 → 数值比较"，因此"最优"是模型意义上的最优：统计失真、代价参数不匹配硬件（如 SSD 上默认 random_page_cost）、搜索空间被裁剪时，"最优"可能与实际最快计划偏离。局限：① 估算误差（均匀/独立假设）；② 动态规划指数爆炸（大连接数用启发式近优）；③ 无法预测运行时抖动（缓存、并发、硬件变化）；④ 无执行中自适应（PG）。理解局限是正确使用 Hint 与统计调优的前提。

本题考察 CBO 的整体画像：五步流程、代价比较的数学本质、以及"模型最优≠实际最优"的四类局限。

• geqo_threshold 默认 12，超过后放弃精确动态规划
• 遗传算法：种群、适应度、交叉、变异迭代搜索近似最优
• 局限：结果近优、计划随机性、可调参数与关闭选项

GEQO（Genetic Query Optimizer）是 PG 在连接数过多时采用的近似搜索算法：当 FROM 子句的表数超过 geqo_threshold（默认 12）时，精确的动态规划（连接顺序枚举呈阶乘级爆炸）被放弃，改为遗传算法——随机生成一批候选 join 顺序（种群），通过"适应度（代价）评估 → 选择 → 交叉 → 变异"迭代若干代（由 geqo_pool_size、geqo_generations、geqo_effort 控制），输出近似最优的连接顺序。

收益：连接数大时规划时间保持可控（毫秒级而非指数级）。代价：① 结果不保证全局最优（近优计划可能显著差于最优）；② 随机性——同一查询每次规划可能得到不同计划，统计变化后计划漂移更明显；③ 调试困难。调优：对关键大连接查询可调整 geqo_effort 或显式用 pg_hint_plan 指定 join 顺序；极端情况可 enable_geqo=off 换取精确搜索（规划时间可能暴涨，慎用）。

本题考察 GEQO 的触发与机制：12 表阈值、遗传算法流程、近优与随机性代价，以及调优与关闭选项。

• RBO 按固定规则选计划、不参考数据分布
• Oracle 10g 起默认 CBO，RBO 路径弃用，统计缺失时 CBO 用默认值兜底
• 遗留：规则性改写仍存在，理解"为何优化器不按规则走"

RBO（基于规则的优化器）按一组固定优先级规则选择执行计划（如"有索引优先索引""小表优先驱动"），完全不参考数据分布与统计，因此在数据量/分布变化时无法自适应：同样的 SQL 在 1 万行与 1 亿行表上会得到同一个僵化计划。Oracle 10g 起默认启用 CBO，RBO 不再作为可选优化器（10g 起 RULE hint 与 RBO 路径被弃用，12c 起已不再支持），统计缺失时 CBO 用默认选择率兜底而非回退规则。

RBO 的"遗产"仍在：现代优化器中仍保留部分规则性改写（谓词下推、子查询展开等启发式），MySQL 早期版本中也有"索引优先于全表"的规则残留；但计划选择的主干已是代价驱动。理解 RBO→CBO 演进，能解释"为什么优化器有时选看似不合理（不按规则）的计划"——那是代价比较的结果。

本题考察 RBO 的历史地位：僵化缺陷、Oracle 的弃用时间线、以及规则性改写在现代优化器中的残留。

• 收益：运行时按实际行数/内存自愈（SQL Server Adaptive Join、Memory Grant Feedback、Oracle adaptive plans）
• 代价：行为不确定、调试困难、与固定计划机制冲突
• 生产取舍：默认开启 + 关键语句监控与强制计划

自适应计划的收益：解决"统计估算与运行时实际不匹配"——SQL Server 的 Adaptive Join（hash 与 nested loop 运行时切换）、内存授予反馈（Memory Grant Feedback，根据实际消耗修正后续执行的内存授予）、Oracle 的自适应计划（adaptive join/并行调整）都能在估算失准时自我纠正，减少人工调优。

代价：① 计划行为不确定性上升——同一语句每次执行可能走不同路径，性能波动与调试困难；② 执行器复杂度与额外运行时开销（每行/每批的检查）；③ 与计划缓存/SPM 等稳定性机制相互作用复杂（强制计划通常禁用自适应）。生产取舍：默认开启以获得自愈能力，但对 SLA 关键语句——用 Query Store 监控计划变化与回归、必要时强制计划（禁用自适应）；变更前在测试环境用真实数据分布验证；监控 P95 延迟与计划分布，出现抖动再逐个定位（禁用某个 adaptive 特性或固定计划）。

本题考察自适应计划的工程权衡：自愈收益与不确定性代价，以及"默认开启+关键语句锁定"的分级治理。

• optimizer_switch 是一组"特性=on/off"开关，会话/全局可设
• 常用开关：semijoin、materialization、firstmatch、derive_merge、index_condition_pushdown
• 修改后需 EXPLAIN 与 optimizer trace 验证，避免全局副作用

optimizer_switch 是 MySQL 的优化器开关集合：一个以逗号分隔的"特性=on/off"字符串，控制各类优化规则的启用状态，可在会话或全局设置（SET SESSION/GLOBAL optimizer_switch='...'）。常用开关：semijoin（IN/EXISTS 半连接转换）、materialization（子查询物化）、subquery_materialization_cost_based（物化 vs 半连接的代价比较）、firstmatch/loosescan/duplicateweedout（半连接各执行策略）、derive_merge（派生表合并）、index_condition_pushdown（ICP 索引下推）、mrr、batched_key_access 等。

使用场景：优化器选错策略时临时关闭某规则（如发现子查询物化更差则关 materialization 开 semijoin），或对新特性做 A/B 验证；修改后必须用 EXPLAIN 验证计划变化，并用 optimizer trace（SET optimizer_trace='enabled=on' 后 SHOW WARNINGS 查看）确认规则是否被采纳。注意：开关是全局性止血手段，长期应依赖统计校准与 SQL 改写，避免"改开关影响所有查询"的副作用。

本题考察 optimizer_switch 的机制与用法：开关体系、常用项、验证方法与"全局副作用"风险。

• 决策依据：统计+代价 vs 固定规则
• 适应性：随分布变化 vs 僵化；可预测性相反
• 结论：现代数据库以 CBO 为主，规则性改写仅作补充

核心区别在决策依据：RBO 依据固定规则集（有无索引、驱动表启发式、访问路径优先级），结果确定、可解释、无统计依赖，但完全不适应数据分布变化——同样的 SQL 在大小不同的表上行为一致，常产生次优计划；CBO 依据统计信息 × 代价模型做数值比较，随数据分布自适应，能在复杂查询中找到真正较优的计划，但依赖统计质量（陈旧/失真即出错）且计划行为对统计敏感（可能抖动）。

优缺点：RBO——优点简单、可预测、开销小；缺点僵化、无法处理倾斜与分布差异。CBO——优点自适应、可扩展到复杂连接；缺点估算误差、参数调优成本、计划不稳定。演进结论：主流数据库（Oracle 10g+、PG、MySQL 8、SQL Server）均以 CBO 为核心，仅保留少量规则性改写（谓词下推、子查询展开）作为补充；RBO 仅在统计完全缺失时以默认值兜底，不再作为独立优化器。

本题考察两类优化器的对比维度：决策依据、适应性、可预测性，以及现代数据库的演进结论。

• 默认 12：FROM 表数超过该值启用 GEQO
• 调大：搜索更精确但规划时间指数增长
• 调小：更早近似；对多表查询用 Hint 或拆分更可取

geqo_threshold 默认 12，控制 GEQO 的启用阈值：一条查询的 FROM 中表（含子查询展开后的连接对象）数量超过该值时，PG 从精确动态规划切换为遗传算法近似搜索 join 顺序。调大该值：大连接查询获得更精确的搜索（可能找到更优计划），但规划时间随连接数阶乘级增长——连接数 15~20 时动态规划可能耗时数秒到数分钟；调小：更早进入近似模式，规划快但计划质量下降。

注意事项：该参数是全局/会话级 GUC，改动影响所有查询；对大连接查询更推荐：控制连接数（拆查询/中间表）、用 pg_hint_plan 显式指定 join 顺序、或在会话级临时调整；对含大量派生表的查询，先确认展开后的真实连接数再判断是否由 GEQO 导致计划质量差。

本题考察 GEQO 阈值的语义与调优：12 的默认值、调大调小的后果，以及优于改全局参数的三类替代手段。

• 概念：执行期反馈/运行时调整而非静态计划
• SQL Server：Adaptive Join、Memory Grant Feedback；Oracle：adaptive plans
• MySQL 与 PG：无执行期计划切换，靠重规划与选择

自适应计划指优化器在"执行过程中"利用运行时反馈动态调整执行策略，而不仅依赖编译期统计估算。各库实现差异：SQL Server 2017+ 的 Adaptive Join 在第一个输入流返回时根据实际行数决定切换 Hash Join 或 Nested Loop，Memory Grant Feedback 根据首次执行的实际内存使用修正后续执行的内存授予（避免溢出落盘或过度授予）；Oracle 的 Adaptive Plans 会在运行时根据实际行数调整 join 方法与并行度（11g 起，12c 扩展），并通过统计反馈自动重规划（19c 的 automatic reoptimization）。

PostgreSQL 与 MySQL 都没有执行期计划切换：PG 的"自适应"只有 generic/custom plan 选择（编译期选择）与统计更新后的重规划；MySQL 除 AHI 外计划固定。理解差异对选型与调优重要：有自适应能力的库可容忍统计失配，但仍需监控（如 SQL Server Query Store 记录计划版本与运行时指标）。

本题考察自适应计划的概念与实现矩阵：定义、两库的原生实现、两库的缺失与近似机制。

• 原理：为高频等值访问的 B-Tree 页构建内存哈希索引，O(1) 点查
• 仅等值有效：哈希定位需要完整键
• 权衡：内存占用、维护 latch 成本与不可预测性，写密集/扫描型负载可关闭

AHI 的原理：InnoDB 观察每个 B-Tree 页的访问模式，当某页被等值查询（=、IN 精确匹配）高频命中时，在内存中构建"索引键→页/记录"的哈希表，后续点查直接 O(1) 定位，跳过 B-Tree 逐层检索与相关 latch。为何只对等值有效：哈希定位需要完整键，范围扫描、前缀匹配、排序无法用哈希表加速。

代价与不可预测性：① 内存——哈希表占 buffer pool 空间，与数据争用缓存；② 维护开销——每次构建/失效要加锁（8.0 前全局 latch 是争用热点，8.0 用分区缓解），写密集与扫描场景下构建频繁，CPU 反而上升；③ 不可预测——构建与替换由引擎内部决策，无精确监控接口（SHOW ENGINE INNODB STATUS 只有粗略统计），行为随负载漂移。权衡建议：热点等值查询稳定（如 ID 点查比例高）时默认开启收益大；写密集、全表/大范围扫描为主、或观察到 latch 争用时评估关闭，用压测对比决定。

本题考察 AHI 的机制与工程权衡：哈希定位的等值约束、内存与 latch 成本、不可预测性，以及"按负载开关"的决策框架。

• ACS（11g+）：同一 SQL 的不同绑定值可对应多个执行计划
• 演进：bind-sensitive → bind-aware，按直方图桶区间分别选计划
• 治理：SQL Profile/SPM 协同，监控 v$sql 状态列

自适应游标共享（ACS）解决"绑定变量 + 数据倾斜"的经典矛盾：传统绑定变量复用同一计划，倾斜数据下某些值次优；ACS 允许同一 SQL 文本对应多个执行计划。工作过程：① 初始计划被标记为 bind-sensitive：执行后监控各次执行的绑定值与实际行数/性能；② 若发现不同绑定值在性能上有显著差异（估算与实际偏差大），该游标升级为 bind-aware：此后按绑定值所在的直方图桶范围（value range）分别生成/复用不同计划——每个值区间一组，从而"热点值走索引、普通值走全表"成为可能；③ 计划选择时用对应桶的计划并持续验证。

局限与治理：ACS 增加游标数量与内存、行为依赖统计质量；可通过 SQL Profile/SPM 固定计划（与绑定感知协同）或隐藏参数关闭；监控视图：v$sql 的 bind_aware/bind_sensitive 列、v$sql_cs_histogram 与 v$sql_cs_statistics。

本题考察 ACS 的机制与演进：绑定敏感到绑定感知的两级升级、按直方图桶分计划、以及监控与治理手段。

• Hint 是绕过代价评估的例外通道，应短期使用
• 统计失真时的正确顺序：先修统计再考虑 Hint
• 维护风险：计划锁死、静默失效、掩盖根因；替代：SPM/Query Store/统计校准

Hint 在 CBO 体系中的定位是"例外通道"：优化器以代价模型为主，Hint 允许在特定语句上绕过或约束代价决策（指定索引/join 方法/顺序），用于统计暂时失真且无法立即修复、特殊数据形态、紧急止血。若把 Hint 当作常规手段（每个 SQL 都加、长期不撤），则成为调优反模式：计划被锁死失去自适应能力、SQL 与索引变更后 Hint 静默失效、掩盖统计与建模问题、增加维护与交接成本。

统计失真时的正确处理顺序：① 先刷新统计（ANALYZE/ANALYZE TABLE、autovacuum 调优）；② 提升估算精度（列级 STATISTICS、扩展统计、直方图、采样页）；③ 用 EXPLAIN ANALYZE 验证是否恢复；④ 仍不行再上 Hint（加注释说明原因与失效条件）；⑤ 长期用 SPM/Query Store 类机制管理计划而非裸 Hint。维护风险：Hint 与表别名绑定、与优化器版本绑定、索引重建改名后失效——需配套回归测试与定期审计（检查 Hint 是否仍被采纳、是否仍必要）。

本题考察 Hint 的定位与治理：例外通道而非常规手段、统计修复优先的顺序、以及维护风险与替代机制。

• PG：autovacuum_analyze_threshold（50）+ scale_factor（0.1）×行数
• SQL Server：小表约 500+20%×行数，2016+ 大表动态阈值
• 配置与监控：表级参数、last_analyze、modification_counter

PG 的自动 ANALYZE 阈值：被修改行数 > autovacuum_analyze_threshold（默认 50）+ autovacuum_analyze_scale_factor（默认 0.1）× 当前估算行数时触发（如 100 万行表修改超过约 10 万行触发）；公式保证小表敏感、大表宽松。SQL Server 的自动统计更新（AUTO_UPDATE_STATISTICS，默认 ON）：对约 25,000 行以下的表，阈值 ≈ 500 行 + 20%×表行数；2016+ 对更大表使用动态阈值（约 sqrt(1000×行数)），减少大表频繁重编译；统计过期由查询触发同步更新（导致编译等待）或异步（AUTO_UPDATE_STATISTICS_ASYNC）。

配置：PG 调整两个 autovacuum_analyze 参数或表级存储参数（ALTER TABLE ... SET (autovacuum_analyze_threshold=...)）；SQL Server 用 sp_autostats/sp_updatestats 或数据库选项。监控：PG 查 pg_stat_user_tables 的 last_analyze/n_mod_since_analyze；SQL Server 用 DBCC SHOW_STATISTICS 或 DMV 对比 modification_counter。阈值过小→频繁重算与计划抖动，过大→统计陈旧，需按写入模式平衡。

本题考察两库统计更新的阈值机制：PG 的线性公式、SQL Server 的固定与动态阈值、配置与监控入口。

• 规则示例：访问路径等级、受限表驱动、索引优先、小表驱动
• 统计缺失时的价值：无统计也能给出经验合理计划
• 现代 CBO 用默认选择率与规则性兜底

RBO 的经典启发式规则：① 访问路径等级（rank）——ROWID 单行访问 > 唯一索引等值 > 普通索引 > 全表扫描（早期 Oracle RBO 的等级体系）；② 驱动表/表顺序——选择"过滤条件最多（最受限制）"的表作为驱动表，外层小、内层大；③ 连接方法——优先 Nested Loop，索引存在时走索引嵌套循环；④ 单表访问——等值主键/唯一约束先行；⑤ 回避排序——能走索引顺序则避免显式排序。这些规则本质是"统计时代之前最优实践的编码"。

统计信息缺失时 RBO 仍有用：它不需要任何统计即可在毫秒级给出"经验上合理"的计划——对唯一索引点查、小表驱动等确定性场景，规则与 CBO 结论一致；现代 CBO 在无统计时也会用默认选择率（如 PG 的 0.01 默认）与规则性兜底（如强制走主键索引），相当于"内置 RBO 最低保障"。局限在于规则无法处理倾斜、分布差异与复杂 join 基数，这正是 CBO 存在的理由。

本题考察 RBO 的规则遗产与价值边界：四类启发式规则、"无统计也能用"的兜底价值、以及处理不了分布差异的局限。