SQL MySQL 的 optimizer_switch 的各项开关（index_merge / mrr 等）生产默认推荐

mysql 8.0 生产环境 optimizer_switch 默认值基本够用但不推荐直接照搬：应显式启用 index_merge 和 mrr，关闭 condition_fanout_filter 和 subquery_cache，并结合 explain format=tree 验证执行计划。

MySQL 8.0 生产环境 optimizer_switch 默认值是否够用

够用，但不推荐直接照搬默认值——index_merge 和 mrr 在多数 OLTP 场景下应显式启用，而 condition_fanout_filter 建议关闭。

为什么 index_merge 默认关闭却该开

index_merge 允许 MySQL 同时用多个单列索引做 union 或 INTERSECT，对没有复合索引但查询条件含多个等值字段（如 WHERE status = ? AND type = ?）特别有用。默认关闭是因为早期版本在某些 JOIN 场景下误判成本，导致执行计划变差；但 MySQL 5.7+ 和 8.0 已大幅优化。

• 开启后，EXPLAIN 可能出现 index_merge 类型 + using sort_union(...) 提示
• 若表有大量 OR 条件（如 WHERE a=1 OR b=2），不开就只能走全表扫描或单索引
• 风险点：当存在覆盖索引时，index_merge 可能放弃使用它，转而回表多次——需配合 EXPLAIN FORMAT=TREE 观察实际访问行数

mrr 和 batch_key_access 的协同开关逻辑

mrr（Multi-Range Read）把随机主键回表变成顺序读，batch_key_access（BKA）则是 JOIN 时用 MRR 优化被驱动表访问。两者默认都开启，但生产中常因磁盘 I/O 模式或缓冲区配置失效。

• mrr=on 必须搭配合理的 read_rnd_buffer_size（建议 256K–1M），太小则无法触发 MRR
• batch_key_access=on 仅在 join_buffer_size 足够且驱动表结果集不大时生效；否则退化为 Block Nested-Loop
• 典型坑：SSD 环境下 mrr_cost_based=off 反而更稳——因为代价模型仍偏向 HDD 延迟假设

哪些开关必须关，以及为什么

condition_fanout_filter 和 subquery_cache 是两个常见“默认开着但该关”的选项。

• condition_fanout_filter=off：该开关试图在 JOIN 前预估过滤率并剪枝，但实际常高估，导致跳过本该走的索引；线上慢查中约 15% 的非预期全表扫描与此有关
• subquery_cache=off：子查询结果缓存极易被并发更新失效，且内存管理粗糙，反而增加锁争用；MySQL 8.0.22+ 已标记为废弃
• 其他谨慎项：hash_join=on 可保留，但若 JOIN 字段字符集不一致（如 utf8mb4 vs latin1），会静默退化为 BNL，需检查 EXPLAIN 中的 Hash Join 是否真实出现

真正关键的不是调多少个开关，而是每次修改后用 EXPLAIN FORMAT=TREE 对比真实执行路径，尤其注意 “rows” 和 “filtered” 列是否符合预期——optimizer_switch 的效果永远藏在执行计划细节里，不在参数列表上。