MySQL优化器参数全攻略

🚀 引言

optimizer_switch 是 MySQL中一个重要的系统变量，它用于控制优化器在执行查询时是否启用或禁用某些优化功能。这个参数可以接受多个值，每个值代表一个特定的优化器开关，合理配置这些参数可以显著提高数据库的查询性能和响应时间。

可以使用以下的命令获取当前数据库优化器参数：

SELECT @@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. 表访问优化参数

这些参数主要影响如何访问和扫描表，特别是与索引使用和条件下推相关的优化。

1. index_merge=on
   • 含义: 启用索引合并优化功能。
   • 使用场景: 当查询可以使用多个索引组合来提高效率时。
   • 引入版本: MySQL 5.1.
2. index_merge_union=on
   • 含义: 允许使用多个索引进行UNION操作。
   • 使用场景: 当查询中涉及多个条件，需要联合多个索引进行优化时。
   • 引入版本: MySQL 5.1.
3. index_merge_sort_union=on
   • 含义: 启用排序UNION的索引合并。
   • 使用场景: 当查询需要对合并后的索引结果进行排序时。
   • 引入版本: MySQL 5.1.
4. index_merge_intersection=on
   • 含义: 允许使用多个索引进行交集操作。
   • 使用场景: 当查询条件需要多个索引的交集来优化时。
   • 引入版本: MySQL 5.1.
5. engine_condition_pushdown=on
   • 含义: 启用存储引擎条件下推。
   • 使用场景: 将WHERE条件下推到存储引擎层以减少返回的行数，提高查询性能。
   • 引入版本: MySQL 5.1.
6. index_condition_pushdown=on
   • 含义: 启用索引条件下推。
   • 使用场景: 在索引扫描期间，将WHERE条件下推到存储引擎中，以减少读取的行数。
   • 引入版本: MySQL 5.6.
7. mrr=on
   • 含义: 启用Multi-Range Read (MRR)。
   • 使用场景: 优化范围扫描以减少磁盘I/O，提高查询效率。
   • 引入版本: MySQL 5.6.
8. mrr_cost_based=on
   • 含义: 基于成本的MRR决策。
   • 使用场景: 根据成本模型决定是否启用MRR以提高查询效率。
   • 引入版本: MySQL 5.6.
9. use_index_extensions=on
   1. 含义: 启用索引扩展使用。
   2. 使用场景: 使用索引扩展技术来优化查询。
   3. 引入版本: MySQL 5.6.
10. use_invisible_indexes=off
    1. 含义: 禁用不可见索引。
    2. 使用场景: 控制查询是否使用不可见索引进行优化。
    3. 引入版本: MySQL 8.0.
11. skip_scan=on
    1. 含义: 启用跳跃扫描。
    2. 使用场景: 在多列索引的情况下，通过跳过不必要的扫描来提高查询性能。
    3. 引入版本: MySQL 8.0.

2. 表关联优化参数

这些参数主要影响表与表之间的连接操作，旨在提高连接查询的效率。

1. block_nested_loop=on
   • 含义: 启用块嵌套循环连接。
   • 使用场景: 用于提高嵌套循环连接的性能，特别是在大数据集上。
   • 引入版本: MySQL 5.6.
2. batched_key_access=off
   • 含义: 批量键访问（BKA）优化。
   • 使用场景: 适用于连接操作，通过批量获取键值来提高查询性能。
   • 引入版本: MySQL 5.6（默认关闭）。
3. hash_join=on
   • 含义: 启用哈希连接。
   • 使用场景: 优化大数据集的连接操作，提高查询效率。
   • 引入版本: MySQL 8.0.
4. condition_fanout_filter=on
   • 含义: 启用条件扇出过滤。
   • 使用场景: 优化连接操作中的条件过滤，以减少数据扫描量。
   • 引入版本: MySQL 5.7.

3. 子查询优化参数

这些参数主要影响子查询的处理方式，旨在优化子查询的执行效率。

1. materialization=on
   • 含义: 启用子查询物化。
   • 使用场景: 将子查询的结果存储在临时表中以提高查询性能。
   • 引入版本: MySQL 5.6.
2. semijoin=on
   • 含义: 启用半连接优化。
   • 使用场景: 优化存在子查询（EXISTS）的性能。
   • 引入版本: MySQL 5.6.
3. loosescan=on
   • 含义: 启用松散扫描优化。
   • 使用场景: 优化IN子查询的执行，特别是在存在重复值的情况下。
   • 引入版本: MySQL 5.6.
4. firstmatch=on
   • 含义: 启用首匹配优化。
   • 使用场景: 优化存在子查询，使其在找到第一个匹配项后即停止扫描。
   • 引入版本: MySQL 5.6.
5. duplicateweedout=on
   • 含义: 启用重复消除优化。
   • 使用场景: 在连接操作中消除重复行。
   • 引入版本: MySQL 5.6.
6. subquery_materialization_cost_based=on
   • 含义: 基于成本的子查询物化决策。
   • 使用场景: 根据成本模型决定是否物化子查询以提高性能。
   • 引入版本: MySQL 5.7.
7. subquery_to_derived=off
   • 含义: 禁用将子查询转换为派生表。
   • 使用场景: 控制查询优化器是否将子查询转换为派生表。
   • 引入版本: MySQL 8.0.

4. 其他优化参数

这些参数涉及其他类型的优化，例如排序、查询结果一致性等。

1. derived_merge=on
   • 含义: 启用派生表合并。
   • 使用场景: 优化派生表查询，将其合并到主查询中执行。
   • 引入版本: MySQL 5.7.
2. prefer_ordering_index=on
   • 含义: 优先使用排序索引。
   • 使用场景: 在ORDER BY操作中优先使用索引进行排序以提高性能。
   • 引入版本: MySQL 8.0.
3. hypergraph_optimizer=off
   • 含义: 禁用超图优化器。
   • 使用场景: 控制是否使用新的超图优化器进行查询优化。
   • 引入版本: MySQL 8.0.20.
4. derived_condition_pushdown=on
   • 含义: 启用派生表条件下推。
   • 使用场景: 将WHERE条件下推到派生表中以减少数据扫描量，提高查询性能。
   • 引入版本: MySQL 8.0.

🌟 使用场景

• 性能调优：数据库管理员可以使用 optimizer_switch 来调整查询性能，特别是在面对特定类型的查询或工作负载时。
• 问题诊断：在查询性能问题诊断过程中，调整 optimizer_switch 参数可以帮助识别性能瓶颈。
• 测试和开发：在开发和测试环境中，开发者可以通过调整这些参数来观察不同优化策略对查询性能的影响。

⚠️ 注意事项

• 在调整 optimizer_switch 参数时，应谨慎并进行充分的测试，因为某些更改可能会对性能产生负面影响。
• 需要根据具体的查询模式和数据库架构来选择合适的优化器开关。
• 某些优化器开关在不同的 MySQL 版本中行为不同，因此需要参考特定版本的官方文档。
• 通过合理配置 optimizer_switch，可以显著提高数据库的查询性能和响应时间。这不仅有助于数据库管理员优化性能，还能在开发和测试过程中提供有价值的洞察。