❝MySQL查询优化器是一个至关重要的组件，它负责确定执行SQL查询的最有效方法。为了提供DBA和开发者更多的灵活性和控制权，MySQL引入了
❞optimizer_switch系统变量。允许用户开启或关闭特定的优化策略，从而可以根据具体的工作负载和数据分布调整查询的执行计划。
optimizer_switch 的概念
optimizer_switch 是一个由多个标志组成的字符串，每个标志控制一个特定的优化器行为。这些标志可以被设置为 on 或 off，以启用或禁用相应的优化策略。通过调整这些标志，dba可以精细地控制查询优化器的行为，以达到最佳的性能表现。
ptimizer_switch系统变量可以控制优化器行为。它的值是一组标志，每个标志都有一个on或off值，用于指示相应的优化器行为是启用还是禁用。此变量具有全局值和会话值，可以在运行时更改。全局默认值可以在服务器启动时设置。
查看当前的优化器标志集
mysql> SELECT @@optimizer_switch\G*************************** 1. row ***************************@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on1 row in set (0.00 sec)
修改optimizer_switch的值
要修改optimizer_switch的值，指定一个由一个或多个命令组成的逗号分隔的值:
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';每个命令值应该具有下表所示的形式之一：
命令的顺序并不重要，但如果存在，默认命令将首先执行。将opt_name标志设置为default将其设置为on或off中的任意一个为其默认值。不允许在值中多次指定任何给定的opt_name。
主要优化标志
「index_merge」
index_merge控制是否允许索引合并优化。当查询条件可以通过多个索引来满足时，MySQL 可以合并这些索引以更有效地检索数据。在复杂查询中，这可以显著提高性能。「index_condition_pushdown (ICP)」
ICP 允许将 WHERE 子句中的条件推送到存储引擎层进行处理。这减少了存储引擎需要返回给优化器的数据量，因为它可以在检索数据时就过滤掉不符合条件的行。
「materialization」
当查询包含子查询时，
materialization标志控制是否将子查询的结果物化（即临时存储）。物化子查询可以减少重复计算，但也可能增加内存使用。「semijoin」 和 「loosescan」
这两个标志与半连接优化相关。半连接是一种在处理包含 EXISTS 或 IN 子句的查询时特别有效的优化策略。
semijoin控制是否使用这种优化，而loosescan则允许在某些情况下进行更高效的扫描。「derived_merge」
当查询中包含派生表（由子查询生成的临时表）时，
derived_merge标志控制是否尝试将这些派生表合并到外部查询中。这可以减少查询的复杂性并提高性能。「exists_to_in」
在某些情况下，将 EXISTS 子句转换为 IN 子句可能会改变查询的执行计划并提高性能。
exists_to_in标志控制是否进行这种转换。「mrr (Multi-Range Read)」
MRR 是一种优化技术，用于改善范围查询和JOIN操作的性能。当设置为
on时，MySQL 会尝试使用 MRR 来更有效地从磁盘读取数据。这通常可以减少磁盘I/O，并提高查询速度。「mrr_cost_based」
当此标志设置为
on时，MySQL 将基于成本决定是否使用 MRR。如果查询优化器认为使用 MRR 会更有效，那么它就会使用这种技术。否则，它将回退到传统的读取方法。「block_nested_loop」
这个标志控制是否使用块嵌套循环连接（Block Nested Loop Join, BNLJ）。BNLJ 是一种在处理连接操作时减少I/O次数的方法。当设置为
on时，MySQL 将考虑使用 BNLJ 来优化连接操作。「batched_key_access」
当此标志启用时，MySQL 会尝试使用批处理键访问（Batched Key Access, BKA）来优化某些类型的 JOIN 操作。BKA 可以减少在 JOIN 操作中访问索引的次数，从而提高性能。
「use_index_extensions」
这个标志允许优化器使用索引扩展来优化某些类型的查询。索引扩展是一种技术，其中优化器可以使用索引中的额外信息来过滤结果集，而无需回表查找数据行。
「condition_fanout_filter」
当此标志设置为on时，优化器将尝试使用条件扇出过滤器（Condition Fanout Filter, CFF）来优化查询。CFF 是一种在处理具有多个可能值的列时减少不必要行扫描的技术。
「use_invisible_indexes」
这个标志控制优化器是否考虑使用标记为“不可见”的索引。在某些情况下，数据库管理员可能希望将索引标记为不可见以进行测试或维护，而不影响现有查询的性能。当此标志设置为on时，即使索引被标记为不可见，优化器也会考虑使用它们。
「skip_scan」
skip_scan 允许优化器在某些情况下使用跳跃扫描来优化范围查询。跳跃扫描是一种技术，其中优化器可以跳过某些索引条目以更快地找到满足查询条件的条目。
「duplicateweedout」
在执行某些类型的 JOIN 操作时，可能会出现重复的行。当 duplicateweedout 设置为on时，优化器将尝试在结果集中删除这些重复的行，从而提高查询结果的准确性。
「subquery_materialization_cost_based」
当此标志设置为
on时，优化器将基于成本决定是否物化子查询。物化子查询是将子查询的结果集存储在临时表中，以便在外部查询中重复使用。这可以提高某些类型查询的性能，但也可能增加内存使用。
如何使用 optimizer_switch
先需要查看其当前设置：
SHOW VARIABLES LIKE 'optimizer_switch';返回所有当前设置的标志及其状态
要更改设置，使用SET语句。如启用 ICP：
SET optimizer_switch='index_condition_pushdown=on';全局更改设置，要用GLOBAL关键字：
SET GLOBAL optimizer_switch='index_condition_pushdown=on';太强 ! SpringBoot中出入参增强的5种方法 : 加解密、脱敏、格式转换、时间时区处理
太强 ! SpringBoot中优化if-else语句的七种绝佳方法实战
提升编程效率的利器: Google Guava库之RateLimiter优雅限流
SpringBoot中大量数据导出方案：使用EasyExcel并行导出多个excel文件并压缩zip后下载
Elasticsearch揭秘：高效写入与精准检索的流程原理全解析
Spring Boot中Druid连接池与多数据源切换的方案
【Elasticsearch系列】深入解析Elasticsearch中脚本原理
