optimizer_switch - xiaoboluo768/qianjinliangfang GitHub Wiki
- 控制查询优化器优化行为的参数（>=5.6.9版本）
- optimizer_switch系统变量允许控制优化器行为。 此变量的值是一组标签，每个标签（子选项）具有on或off值，以指示相应的优化程序行为是启用还是禁用。 该参数的各个子选项之间没有顺序的限制。
- 该参数有众多子选项，全局，会话，动态变量，set类型，全局默认值可以在服务器启动时设置，默认值为：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,subquery_materialization_cost_based=on,use_index_extensions=on
- 每个标签有三个有效值：
- default：重置该子选项为server默认值，在你经过一些修改之后不记得默认值是什么的时候有用
- on： 开启该子选项对应的优化器行为
- off：关闭该子选项对应的优化器行为
- 以下是每个标签（子选项）的含义如下表（所有子选项中，在5.6.9之后的版本默认值只有batched_key_access才是OFF，而BKA特性对于join查询有帮助，所以建议默认开启，要注意，目前基于成本的MRR估算太悲观，所以要使用MRR和BKA，必须要将mrr_cost_based设置为OFF，即5.6.x版本中除了mrr_cost_based建议设置为OFF之外，其他的子选项都建议设置为ON）：
| 优化特性名 | 标志名称 | 含义 | 默认值 |
|---|---|---|---|
| 批量键访问 | batched_key_access | 控制是否开启BKA连接算法 | OFF |
| 块嵌套循环 | block_nested_loop | 控制是否开启BNL连接算法 | ON |
| 引擎条件下推 | engine_condition_pushdown | 控制是否开启引擎条件下推 | ON |
| 索引条件下推 | index_condition_pushdown | 控制是否开启索引条件下推 | ON |
| 索引扩展 | use_index_extensions | 控制是否开启索引扩展优化 | ON |
| 索引合并 | index_merge | 控制是否开启所有的索引合并优化特性 | ON |
| index_merge_intersection | 控制是否开启索引合并交集查询优化 | ON | |
| index_merge_sort_union | 控制是否开启索引合并排序联合查询优化 | ON | |
| index_merge_union | 控制是否开启索引合并联合查询优化 | ON | |
| 多范围读取 | mrr | 控制是否开启多范围读取优化策略 | ON |
| mrr_cost_based | 如果mrr = on，则该子选项控制是否开启基于成本的MRR优化策略 | ON | |
| 半连接 | semijoin | 控制是否开启所有半连接查询优化策略 | ON |
| firstmatch | 控制是否开启半连接FirstMatch优化策略 | ON | |
| loosescan | 控制是否开启半连接LooseScan优化策略（不要与用于GROUP BY的LooseScan混淆，这里的是用于semijoin查询的LooseScan） | ON | |
| 物化子查询 | materialization | 控制是否开启物化查询（包括半连接物化查询） | ON |
| subquery_materialization_cost_based | 控制是否开启基于成本的物化子查询选择 | ON |
- 控制查询优化器优化行为的参数（>=5.7.8版本）
- 与5.6类似，以下列出5.7中的默认值，与5.6相同的选项就不再列举，只列出5.7新增的优化器策略，5.7默认值为：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
- 以下列出5.7.8以上的5.7.x版本中与5.6.9以上的5.6.x版本中相比多出来的查询优化器策略：
- duplicateweedout：控制半连接重复Weedout策略是否开启
- condition_fanout_filter：控制在计算查询优化器代价时，是否计算条件过滤的策略（5.7 在代价类型上分为io,cpu和me，mysql 5.7代价计算相对之前的版本有较大的改进。例如 * 代价模型参数可以动态配置，可以适应不同的硬件 * 区分考虑数据在内存和在磁盘中的代价 * 代价精度提升为浮点型 * jion计算时不仅要考虑condition,还要考虑condition上的filter，此参数就是控制是否使用condition上的filter的，参考链接：https://yq.aliyun.com/articles/57650?utm_source=qq）
- derived_merge：控制是否将派生表和视图合并到外部查询块中
- PS：
- 如果在join查询中，开启了BKA特性，驱动表有排序字段，且where条件与排序字段是一个联合索引时，可能导致驱动表执行计划中出现'Using temporary; Using filesort'，此时请关闭BKA特性，关闭之后驱动表使用ICP特性进行数据过滤（开启BKA无法使用索引排序的原因是：BKA是先根据where条件在二级索引中找出符合的主键字段值，再在join buffer里面根据主键排序，然后使用主键再去join被驱动表，如果驱动表有二级索引的排序字段，那么此时就无法再使用二级索引进行排序了），BKA特性如果在驱动表没有按照二级索引排序时，可以打开，该特性默认关闭
上一篇：max_length_for_sort_data | 下一篇：memlock