在 MySQL 中， optimizer_switch 是一个用于控制查询优化器是否启用某些特定优化特性的系统变量。它是一个由多个 flag=on/off 对组成的字符串，每个开关对应一种优化策略或算法。通过调整这些开关，DBA 或开发者可以针对特定查询或负载调整优化器的行为，例如启用新的优化特性、规避某些因优化器决策导致的性能问题，或者进行调试分析。

optimizer_switch 的常见开关及其含义（基于 MySQL 8.4）

以下列表列举了 MySQL 8.4 中 optimizer_switch 包含的主要开关及其简要说明。需要注意的是，不同 MySQL 版本可能增加、废弃或修改某些开关，因此实际完整列表应以 官方文档 为准。

索引与表访问相关

• index_merge
  控制是否允许使用索引合并（Index Merge）访问方法，即对同一张表使用多个索引进行扫描，然后对结果合并（交集、并集等）。

• index_merge_intersection
  允许使用索引合并的“交集”算法（多个索引条件的 AND 组合）。

• index_merge_union
  允许使用索引合并的“并集”算法（多个索引条件的 OR 组合）。

• index_merge_sort_union
  允许使用索引合并的“排序并集”算法（对 OR 条件且需要排序的情况进行优化）。

• engine_condition_pushdown
  控制是否将查询条件（WHERE 条件）下推到存储引擎层进行处理，以减少返回给服务器层的行数（常用于 NDB 和 MyISAM，InnoDB 也有部分支持）。

• mrr (Multi-Range Read)
  启用后，优化器尝试将随机 I/O 转化为顺序 I/O，通过先对二级索引扫描到的主键排序，再回表读取数据行，以提高 I/O 效率。

• mrr_cost_based
  决定是否基于成本模型来决策是否使用 MRR。如果关闭，则在 mrr=on 时总是尝试使用 MRR；如果开启（默认），则优化器会评估成本后决定。

• skip_scan
  允许优化器使用 Skip Scan 访问方法，即在复合索引的前导列上跳过某些值，直接扫描后续列，相当于对前导列做隐式分组后扫描（MySQL 8.0.13 引入）。

连接（Join）算法相关

• block_nested_loop
  控制是否允许使用块嵌套循环连接（Block Nested-Loop Join）算法。在 MySQL 8.0.18 之前，该算法用于无法使用索引的连接；从 8.0.18 开始，大部分场景已被 Hash Join 取代，此开关通常只在没有其他算法可用时起作用。

• batched_key_access (BKA)
  启用批处理键访问连接算法，结合 MRR 和 multi‑join 缓存，对连接表进行批量主键查找，以优化连接性能。

• hash_join
  控制是否允许使用哈希连接（Hash Join）算法（MySQL 8.0.18 引入，8.0.20 后默认启用，对等值连接有效）。

子查询与派生表优化相关

• semijoin
  控制半连接（Semi-join）优化，将 IN 、 EXISTS 子查询转换为半连接，以减少不必要的行数。

• loosescan
  允许使用松散扫描（LooseScan）策略执行半连接，用于高效的重复值消除。

• firstmatch
  允许使用匹配（FirstMatch）策略执行半连接，即找到第一个匹配行后就跳过后续相同值。

• materialization
  控制子查询物化策略，允许将子查询结果物化为临时表，然后与外部查询进行连接。

• subquery_materialization_cost_based
  决定是否基于成本模型在物化和 IN 到 EXISTS 转换之间选择。关闭时，优化器会强制使用成本评估；开启时（默认），由成本模型决定。

• derived_merge
  控制是否将派生表（FROM 子句中的子查询）合并到外部查询中，而不是物化派生表。合并可以减少临时表开销，但也可能增加重复计算。

• subquery_to_derived
  允许优化器尝试将标量子查询转换为派生表连接（MySQL 8.0.21 引入），可能提高并行执行能力。

其他优化开关

• use_invisible_indexes
  控制优化器是否可以使用被标记为不可见（ INVISIBLE ）的索引。默认关闭，即不可见索引不被优化器考虑。

• hypergraph_optimizer
  控制是否启用超图优化器（MySQL 8.4 实验性功能）。超图优化器是一种新的基于超图模型的连接顺序搜索算法，旨在处理复杂多表连接时找到更优的执行计划。

• prefer_ordering_index
  控制当查询包含 ORDER BY 或 GROUP BY 时，优化器是否倾向于使用能够避免排序的索引，即使该索引可能不是成本最低的访问路径。

如何查看当前 optimizer_switch 的值

SHOW VARIABLES LIKE 'optimizer_switch';

返回结果是一个逗号分隔的 key=value 字符串，例如：

index_merge=on,index_merge_union=on,index_merge_sort_union=on,...

修改 optimizer_switch

可以在全局或会话级别动态修改，例如：

SET GLOBAL optimizer_switch='hash_join=off';SET SESSION optimizer_switch='derived_merge=off';

注意事项

• 开关的可用性和默认值可能随 MySQL 版本变化，务必参考对应版本的官方文档。

• 修改 optimizer_switch 可能会影响查询执行计划，建议在充分测试后再推广到生产环境。

• 对于特定查询，可以使用 /*+ SET_VAR(optimizer_switch='...') */ 优化器提示（hint）来临时改变会话级别的开关，以调整单个查询的执行计划。