mysql optimizer_switch : 查询优化器优化策略深入解析

码到三十五 ： 个人主页

---

在 MySQL 数据库中，查询优化器是一个至关重要的组件，它负责确定执行 SQL 查询的最有效方法。为了提供DBA和开发者更多的灵活性和控制权，MySQL 引入了 optimizer_switch 系统变量。这个强大的工具允许用户开启或关闭特定的优化策略，从而可以根据具体的工作负载和数据分布调整查询的执行计划。

optimizer_switch 的概念

optimizer_switch 是一个由多个标志组成的字符串，每个标志控制一个特定的优化器行为。这些标志可以被设置为 on 或 off，以启用或禁用相应的优化策略。通过调整这些标志，数据库管理员可以精细地控制查询优化器的行为，以达到最佳的性能表现。

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=on
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

修改optimizer_switch的值

要修改optimizer_switch的值，指定一个由一个或多个命令组成的逗号分隔的值:

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';
1

每个命令值应该具有下表所示的形式之一：

该值中命令的顺序并不重要，但如果存在，默认命令将首先执行。将opt_name标志设置为default将其设置为on或off中的任意一个为其默认值。不允许在值中多次指定任何给定的opt_name，这会导致错误。该值中的任何错误都会导致赋值失败，并导致optimizer_switch的值保持不变。

主要优化标志介绍

1. index_merge

   index_merge 控制是否允许索引合并优化。当查询条件可以通过多个索引来满足时，MySQL 可以合并这些索引以更有效地检索数据。在复杂查询中，这可以显著提高性能。

2. index_condition_pushdown (ICP)

   ICP 允许将 WHERE 子句中的条件推送到存储引擎层进行处理。这减少了存储引擎需要返回给优化器的数据量，因为它可以在检索数据时就过滤掉不符合条件的行。

3. materialization

   当查询包含子查询时，materialization 标志控制是否将子查询的结果物化（即临时存储）。物化子查询可以减少重复计算，但也可能增加内存使用。

4. semijoin 和 loosescan

   这两个标志与半连接优化相关。半连接是一种在处理包含 EXISTS 或 IN 子句的查询时特别有效的优化策略。semijoin 控制是否使用这种优化，而 loosescan 则允许在某些情况下进行更高效的扫描。

5. derived_merge

   当查询中包含派生表（由子查询生成的临时表）时，derived_merge 标志控制是否尝试将这些派生表合并到外部查询中。这可以减少查询的复杂性并提高性能。

6. exists_to_in

   在某些情况下，将 EXISTS 子句转换为 IN 子句可能会改变查询的执行计划并提高性能。exists_to_in 标志控制是否进行这种转换。

7. mrr (Multi-Range Read)

   MRR 是一种优化技术，用于改善范围查询和JOIN操作的性能。当设置为on时，MySQL 会尝试使用 MRR 来更有效地从磁盘读取数据。这通常可以减少磁盘I/O，并提高查询速度。

8. mrr_cost_based

   当此标志设置为on时，MySQL 将基于成本决定是否使用 MRR。如果查询优化器认为使用 MRR 会更有效，那么它就会使用这种技术。否则，它将回退到传统的读取方法。

9. block_nested_loop

   这个标志控制是否使用块嵌套循环连接（Block Nested Loop Join, BNLJ）。BNLJ 是一种在处理连接操作时减少I/O次数的方法。当设置为on时，MySQL 将考虑使用 BNLJ 来优化连接操作。

10. batched_key_access

当此标志启用时，MySQL 会尝试使用批处理键访问（Batched Key Access, BKA）来优化某些类型的 JOIN 操作。BKA 可以减少在 JOIN 操作中访问索引的次数，从而提高性能。

11. use_index_extensions

这个标志允许优化器使用索引扩展来优化某些类型的查询。索引扩展是一种技术，其中优化器可以使用索引中的额外信息来过滤结果集，而无需回表查找数据行。

12. condition_fanout_filter

当此标志设置为on时，优化器将尝试使用条件扇出过滤器（Condition Fanout Filter, CFF）来优化查询。CFF 是一种在处理具有多个可能值的列时减少不必要行扫描的技术。

13. use_invisible_indexes

这个标志控制优化器是否考虑使用标记为“不可见”的索引。在某些情况下，数据库管理员可能希望将索引标记为不可见以进行测试或维护，而不影响现有查询的性能。当此标志设置为on时，即使索引被标记为不可见，优化器也会考虑使用它们。

14. skip_scan

skip_scan 允许优化器在某些情况下使用跳跃扫描来优化范围查询。跳跃扫描是一种技术，其中优化器可以跳过某些索引条目以更快地找到满足查询条件的条目。

15. duplicateweedout

在执行某些类型的 JOIN 操作时，可能会出现重复的行。当 duplicateweedout 设置为on时，优化器将尝试在结果集中删除这些重复的行，从而提高查询结果的准确性。

16. subquery_materialization_cost_based

    当此标志设置为on时，优化器将基于成本决定是否物化子查询。物化子查询是将子查询的结果集存储在临时表中，以便在外部查询中重复使用。这可以提高某些类型查询的性能，但也可能增加内存使用。

如何使用 optimizer_switch

要使用 optimizer_switch，你首先需要查看其当前设置：

SHOW VARIABLES LIKE 'optimizer_switch';
1

这将返回一个包含所有当前设置的标志及其状态的列表。

要更改设置，你可以使用 SET 语句。例如，要启用 ICP，你可以执行：

SET optimizer_switch='index_condition_pushdown=on';
1

注意，上述命令只会更改当前会话的设置。如果你想全局更改设置，需要使用 GLOBAL 关键字：

SET GLOBAL optimizer_switch='index_condition_pushdown=on';
1

注意事项和最佳实践

• 在更改 optimizer_switch 设置之前，最好先在测试环境中验证更改的效果。
• 不是所有的优化标志都适用于所有版本的 MySQL。在更改设置之前，请查阅相关文档以确保你了解每个标志的具体行为和限制。
• 避免在生产环境中盲目更改设置。应该基于实际的性能分析和测试来做出决策。
• 监控数据库的性能指标，以便及时发现并解决潜在问题。

结论

optimizer_switch 是一个强大的工具，允许数据库管理员和开发者精细地控制 MySQL 查询优化器的行为。合理地调整这些设置，可以提高数据库的性能并优化查询效率。使用时也要谨慎并基于充分的测试和分析。

---

听说...关注下面公众号的人都变牛了，纯技术，纯干货 !