MySQL编程进阶之：`optimizer_switch`参数的调优：如何控制查询优化器的行为。

各位观众老爷，大家好！今天咱们来聊聊MySQL里一个听起来神秘兮兮，但其实挺好玩儿的参数：optimizer_switch。这玩意儿就像是MySQL查询优化器的大脑遥控器，通过它，你可以控制优化器的各种行为，让它更听你的话，而不是自己瞎琢磨。

开场白：优化器的那些事儿

咱们先简单回顾一下，MySQL的查询优化器是干嘛的？简单来说，就是把你写的SQL语句，变成MySQL觉得最高效的执行方式。但有时候，优化器自作聪明，选了个看起来“聪明”，实际上慢得要死的方案。这时候，你就需要optimizer_switch来出手干预了。

optimizer_switch是什么？

optimizer_switch是一个MySQL的全局参数，它包含了一系列的开关，每个开关控制着优化器的一个特定行为。你可以用SHOW GLOBAL VARIABLES LIKE 'optimizer_switch'; 命令查看当前设置。

SHOW GLOBAL VARIABLES LIKE 'optimizer_switch';

输出结果会像这样：

+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                         |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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,internal_tmp_mem_storage_engine=default |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

是不是觉得有点眼花缭乱？别怕，咱们慢慢来。每个开关=on/off/default都代表着一个优化器行为的开启、关闭或使用默认设置。

optimizer_switch的常用开关及使用场景

接下来，咱们挑几个常用的开关，结合实际场景，详细聊聊怎么用。

1. index_merge系列开关：索引合并

   • index_merge=on/off：控制是否启用索引合并优化。
   • index_merge_union=on/off：控制是否启用索引合并的UNION算法。
   • index_merge_sort_union=on/off：控制是否启用索引合并的SORT-UNION算法。
   • index_merge_intersection=on/off：控制是否启用索引合并的INTERSECTION算法。

   场景： 当你的SQL语句中，WHERE条件使用了多个索引，优化器可能会选择索引合并。

   例子：

   CREATE TABLE `users` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `name` varchar(255) DEFAULT NULL,
     `age` int(11) DEFAULT NULL,
     `city` varchar(255) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `idx_name` (`name`),
     KEY `idx_age` (`age`),
     KEY `idx_city` (`city`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
   
   INSERT INTO `users` (`name`, `age`, `city`) VALUES
   ('Alice', 25, 'New York'),
   ('Bob', 30, 'London'),
   ('Charlie', 28, 'Paris'),
   ('David', 35, 'New York'),
   ('Eve', 25, 'London');

   SELECT * FROM users WHERE name = 'Alice' OR age = 30;

   这条SQL语句，如果开启了index_merge_union，优化器可能会选择先用idx_name找到name = 'Alice'的记录，再用idx_age找到age = 30的记录，然后将两部分结果合并。

   坑： 索引合并虽然听起来很美好，但有时候会适得其反。特别是当每个索引返回的记录都很多时，合并操作会消耗大量资源，导致查询变慢。

   调优： 如果你发现使用了索引合并的查询很慢，可以尝试关闭index_merge或者更精确地关闭index_merge_union、index_merge_sort_union、index_merge_intersection中的某一个。

   SET SESSION optimizer_switch='index_merge=off'; -- 关闭当前会话的索引合并
   SET GLOBAL optimizer_switch='index_merge=off'; -- 关闭全局的索引合并 (需要SUPER权限)

   建议： 在决定关闭某个index_merge开关之前，先用EXPLAIN命令分析一下查询计划，看看是否真的使用了索引合并，以及索引合并是否是导致查询变慢的原因。

2. engine_condition_pushdown和index_condition_pushdown：条件推送到存储引擎

   • engine_condition_pushdown=on/off：控制是否将部分WHERE条件推送到存储引擎进行过滤。
   • index_condition_pushdown=on/off：控制是否将索引条件下推到存储引擎。

   场景： 某些存储引擎（比如InnoDB）支持在存储引擎层面对数据进行过滤，这样可以减少不必要的数据读取，提高查询效率。

   例子： 假设users表有一个city字段，并且有索引idx_city。

   SELECT * FROM users WHERE city LIKE 'New%';

   如果开启了engine_condition_pushdown和index_condition_pushdown，MySQL可能会将city LIKE 'New%'这个条件推送到InnoDB存储引擎，让InnoDB先根据idx_city索引过滤掉不符合条件的记录，然后再将剩下的记录返回给MySQL服务器。

   坑： 并不是所有的条件都可以被推送到存储引擎。比如，包含函数调用的条件，或者涉及到多个表的关联条件，通常无法被推送。

   调优： 一般情况下，保持engine_condition_pushdown和index_condition_pushdown开启即可。但如果你发现某个查询的性能异常，可以尝试关闭这些开关，看看是否有所改善。

   SET SESSION optimizer_switch='engine_condition_pushdown=off,index_condition_pushdown=off';

3. mrr和mrr_cost_based：多范围读取（MRR）

   • mrr=on/off：控制是否启用多范围读取优化。
   • mrr_cost_based=on/off：控制是否根据成本估算来决定是否使用MRR。

   场景： 当你的SQL语句需要根据二级索引（非主键索引）读取大量数据时，MRR可以提高查询效率。

   原理： MRR会先将二级索引查到的主键ID排序，然后再根据排序后的主键ID，回表读取数据。这样可以减少随机IO，提高读取效率。

   例子：

   SELECT * FROM users WHERE age BETWEEN 20 AND 30 ORDER BY name;

   如果age字段上有索引，并且开启了MRR，MySQL会先用age索引找到所有age在20到30之间的记录的主键ID，然后将这些主键ID排序，最后根据排序后的主键ID，回表读取完整的记录。

   坑： MRR并非万能。当二级索引返回的记录很少时，MRR的优化效果并不明显。而且，MRR会占用一定的内存，如果内存不足，可能会导致查询变慢。

   调优： 通常保持mrr=on和mrr_cost_based=on即可。如果发现某个查询使用了MRR，但性能反而下降，可以尝试关闭MRR。

   SET SESSION optimizer_switch='mrr=off';

4. block_nested_loop：块嵌套循环连接（BNL）

   • block_nested_loop=on/off：控制是否启用块嵌套循环连接算法。

   场景： 当你的SQL语句需要进行表连接，并且其中一个表没有合适的索引时，MySQL可能会选择使用BNL算法。

   原理： BNL会将一个表的数据读取到内存中的一个buffer（块），然后用另一个表的数据逐条与buffer中的数据进行比较，找到匹配的记录。

   例子：

   SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.city = 'New York';

   如果orders表的user_id字段没有索引，MySQL可能会选择将users表的数据读取到内存中的一个buffer，然后用orders表的数据逐条与buffer中的数据进行比较，找到o.user_id = u.id的记录。

   坑： BNL算法对内存消耗较大。如果参与连接的表很大，BNL可能会导致内存溢出，或者查询速度极慢。

   调优： 如果发现某个连接查询使用了BNL，并且性能很差，可以尝试关闭BNL。但更有效的解决方案是给参与连接的字段添加索引。

   SET SESSION optimizer_switch='block_nested_loop=off';

5. batched_key_access：批量键访问 (BKA)

   • batched_key_access=on/off：控制是否启用批量键访问优化。

   场景： BKA是针对IN()子查询的一种优化。当你在WHERE子句中使用IN()子查询，并且子查询的结果集较大时，BKA可以提高查询效率。

   原理： BKA会将IN()子查询的结果集缓存起来，然后批量地将这些值传递给外层查询，进行索引查找。

   例子：

   SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = 'New York');

   如果batched_key_access=on，MySQL可能会先执行子查询SELECT id FROM users WHERE city = 'New York'，将结果集缓存起来，然后批量地将这些id值传递给外层查询SELECT * FROM orders WHERE user_id IN (...)，进行索引查找。

   坑： BKA需要一定的内存来缓存子查询的结果集。如果内存不足，可能会导致查询变慢。

   调优： 通常保持batched_key_access=on即可。如果发现使用了BKA的查询很慢，可以尝试关闭BKA。

   SET SESSION optimizer_switch='batched_key_access=off';

6. materialization和subquery_materialization_cost_based：物化

   • materialization=on/off：控制是否启用物化优化。
   • subquery_materialization_cost_based=on/off：控制是否根据成本估算来决定是否使用子查询物化。

   场景： 物化是一种优化子查询的策略。它会将子查询的结果集存储到一个临时表中（称为物化表），然后在外层查询中引用这个临时表。

   原理： 物化可以避免重复执行子查询，提高查询效率。

   例子：

   SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = 'New York');

   如果开启了物化，MySQL可能会先执行子查询SELECT id FROM users WHERE city = 'New York'，将结果存储到一个临时表中，然后将外层查询改写成从这个临时表中读取数据。

   坑： 物化需要创建和维护临时表，会占用一定的磁盘空间和IO资源。

   调优： 通常保持materialization=on和subquery_materialization_cost_based=on即可。如果发现使用了物化的查询很慢，可以尝试关闭物化。

   SET SESSION optimizer_switch='materialization=off';

7. semijoin系列开关：半连接

   semijoin本身是个大的优化策略，下面是一些相关的开关。

   • semijoin=on/off：控制是否启用半连接优化。
   • loosescan=on/off：控制是否启用Loose Scan半连接策略。
   • firstmatch=on/off：控制是否启用FirstMatch半连接策略。
   • duplicateweedout=on/off：控制是否启用Duplicate Weedout半连接策略。

   场景： 半连接是一种优化IN或EXISTS子查询的策略。

   原理： 半连接的目标是找到满足子查询条件的记录，但不需要返回子查询的结果。类似于EXISTS的语义，找到一个匹配的就行，不需要像JOIN那样返回所有匹配的记录。

   例子：

   SELECT * FROM orders WHERE EXISTS (SELECT 1 FROM users WHERE users.id = orders.user_id AND city = 'New York');

   或者

   SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = 'New York');

   MySQL可以使用多种半连接策略，例如：

   • Loose Scan: 如果子查询可以使用索引，Loose Scan会利用索引的特性，只扫描满足条件的第一个值，避免重复扫描。
   • FirstMatch: 对于外层表的每一行，子查询只执行一次，找到第一个匹配的记录就停止。
   • Duplicate Weedout: 如果子查询的结果集中有重复值，Duplicate Weedout会先去除重复值，然后再进行连接，避免重复返回结果。

   坑： 半连接的优化效果取决于子查询的复杂度和数据的分布。有时候，半连接策略反而会降低查询效率。

   调优： 通常保持semijoin=on即可。如果发现使用了半连接的查询很慢，可以尝试关闭semijoin，或者更精确地关闭某个具体的半连接策略（例如loosescan=off、firstmatch=off、duplicateweedout=off）。

   SET SESSION optimizer_switch='semijoin=off';

8. derived_merge 和 subquery_to_derived：派生表合并

   • derived_merge=on/off：控制是否允许将派生表合并到外部查询中。
   • subquery_to_derived=off/on：控制是否将某些子查询转换为派生表。

   场景： 当你在查询中使用子查询作为表（派生表）时，这两个参数会影响MySQL如何处理这些子查询。

   例子：

   SELECT * FROM (SELECT id, name FROM users WHERE age > 20) AS derived_table WHERE name LIKE 'A%';

   如果derived_merge=on，MySQL可能会尝试将派生表 (SELECT id, name FROM users WHERE age > 20) 合并到外部查询中，从而避免创建临时表。 这通常可以提高查询效率，特别是当派生表的数据量较大时。

   subquery_to_derived=on 允许MySQL将某些类型的子查询转换为派生表，这使得derived_merge 有机会发挥作用。

   坑： 并非所有的派生表都可以被合并。 例如，如果派生表包含GROUP BY、HAVING、ORDER BY、LIMIT 等子句，或者使用了UNION，通常无法进行合并。 另外，某些情况下，合并派生表可能会导致查询计划变得复杂，反而降低查询效率。

   调优： 通常保持 derived_merge=on 和 subquery_to_derived=off是合理的。 如果发现使用了派生表的查询很慢，可以尝试关闭 derived_merge。如果关闭后性能提升明显，说明派生表合并可能不是最佳选择。 开启subquery_to_derived有时候会帮助优化器找到更好的执行计划，但也要仔细评估。

   SET SESSION optimizer_switch='derived_merge=off';

9. use_index_extensions：使用索引扩展

   • use_index_extensions=on/off：控制是否使用索引统计信息扩展（Index Statistics Extensions）。

   场景： MySQL会维护索引的统计信息，用于优化查询计划。 索引扩展提供更准确的统计信息，帮助优化器做出更明智的选择。

   例子： 考虑一个 products 表，包含 category 和 price 字段，并且有一个复合索引 idx_category_price(category, price)。 如果 products 表中某个 category 的 price 分布非常不均匀（例如，大部分商品价格都很低，只有少数商品价格很高）， 传统的统计信息可能无法准确反映这种情况。 索引扩展可以提供更详细的统计信息，例如每个 category 的价格分布直方图，帮助优化器更好地估算查询成本。

   坑： 使用索引扩展会增加一定的系统开销，因为MySQL需要维护更详细的统计信息。

   调优： 通常保持 use_index_extensions=on 即可。如果发现某个查询的执行计划不佳，可以尝试更新索引统计信息（ANALYZE TABLE），或者关闭 use_index_extensions。

   SET SESSION optimizer_switch='use_index_extensions=off';

10. condition_fanout_filter：条件扇出过滤

    • condition_fanout_filter=on/off：控制是否启用条件扇出过滤。

    场景： 当WHERE子句包含多个AND连接的条件，并且这些条件分别对应不同的索引时，MySQL可以使用条件扇出过滤来优化查询。

    原理： 条件扇出过滤会根据每个条件的扇出（即符合条件的记录数）来选择最佳的执行顺序。 它会优先执行扇出最小的条件，尽可能快地减少需要处理的记录数。

    例子：

    SELECT * FROM products WHERE category = 'Electronics' AND price > 100 AND brand = 'XYZ';

    如果 category、price 和 brand 字段都有索引，条件扇出过滤会评估每个条件的扇出，例如：

    • category = 'Electronics' 返回 1000 条记录
    • price > 100 返回 500 条记录
    • brand = 'XYZ' 返回 200 条记录

    优化器可能会选择先执行 brand = 'XYZ'，因为它的扇出最小，可以最快地减少需要处理的记录数。

    坑： 条件扇出过滤的优化效果取决于数据的分布和索引的选择。 有时候，它可能会选择错误的执行顺序，导致查询效率下降。

    调优： 通常保持 condition_fanout_filter=on 即可。 如果发现使用了条件扇出过滤的查询很慢，可以尝试关闭它，或者调整索引的选择。

    SET SESSION optimizer_switch='condition_fanout_filter=off';

11. skip_scan：跳跃扫描

    • skip_scan=on/off: 控制是否启用跳跃扫描。

    场景： 当你在复合索引上进行查询，但没有指定索引的前导列时，可以使用跳跃扫描。

    例子：

    CREATE TABLE `test_skip_scan` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `col1` varchar(255) DEFAULT NULL,
      `col2` varchar(255) DEFAULT NULL,
      `col3` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `idx_col1_col2` (`col1`,`col2`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    INSERT INTO `test_skip_scan` (`col1`, `col2`, `col3`) VALUES
    ('A', 'X', 'Data1'),
    ('A', 'Y', 'Data2'),
    ('B', 'Z', 'Data3'),
    ('B', 'X', 'Data4'),
    ('C', 'Y', 'Data5');

    SELECT * FROM test_skip_scan WHERE col2 = 'X';

    在这个例子中，我们有一个复合索引 idx_col1_col2，但查询只指定了 col2 的值，而没有指定 col1 的值。 如果启用了跳跃扫描，MySQL 可以跳过 col1 的不同值，直接扫描 col2 = 'X' 的部分索引。 优化器会识别出 col1 有几个不同的值（在这里是 A、B、C），然后对每个 col1 的值，在索引中查找 col2 = 'X' 的记录。

    坑： 跳跃扫描的效率取决于 col1 的不同值的数量。 如果 col1 有很多不同的值，跳跃扫描的效率可能会很低。

    调优： 通常保持 skip_scan=on 即可。 如果发现使用了跳跃扫描的查询很慢，可以考虑以下几点：

    • 检查 col1 的不同值的数量是否过多。 如果是，可以考虑修改查询，使其包含 col1 的条件，或者创建一个只包含 col2 的索引。
    • 关闭跳跃扫描。

    SET SESSION optimizer_switch='skip_scan=off';

总结：遥控器不是随便按的

optimizer_switch 提供了强大的控制能力，但它就像一个精密的遥控器，按错了按钮可能会导致意想不到的问题。 在修改 optimizer_switch 的值之前，一定要充分了解每个开关的作用，并进行充分的测试。

一些建议：

• 不要轻易修改全局设置： 全局设置会影响所有会话，修改全局设置需要谨慎。 尽量使用会话级别设置进行测试。
• 使用 EXPLAIN 分析查询计划： EXPLAIN 命令可以帮助你了解优化器是如何选择执行计划的。 通过分析查询计划，你可以判断是否需要调整 optimizer_switch 的值。
• 监控查询性能： 在修改 optimizer_switch 的值之后，一定要监控查询性能，确保修改确实带来了改善。
• 了解你的数据： 优化器是根据统计信息来选择执行计划的。 了解你的数据分布，可以帮助你更好地理解优化器的行为，并做出更明智的决策。
• 从小范围开始： 先在一个或几个关键查询上进行测试，确认效果后再推广到整个系统。

总而言之，optimizer_switch 是一个强大的工具，但需要谨慎使用。 只有深入理解了优化器的行为，才能真正掌握这个遥控器，让 MySQL 跑得更快更稳。

好了，今天的讲座就到这里。希望大家以后在遇到MySQL性能问题时，能够想起今天的内容，灵活运用optimizer_switch，让你的数据库飞起来！ 感谢各位的观看！