【MySQL精通之路】查询优化器的使用(8)

MySQL通过影响查询计划评估方式的系统变量、可切换优化、优化器和索引提示以及优化器成本模型提供优化器控制。

服务器在column_statistics数据字典表中维护有关列值的直方图统计信息（请参阅第10.9.6节“Optimizer统计信息”）。与其他数据字典表一样，用户无法直接访问此表。相反，您可以通过查询information_SCHEMA来获取直方图信息。COLUMN_STATISTICS，它被实现为数据字典表上的视图。您还可以使用ANALYZE TABLE语句执行直方图管理。

1.控制查询计划评估

        查询优化器的任务是找到执行SQL查询的最佳计划。由于“好”和“坏”计划之间的性能差异可能是几个数量级（即秒与小时甚至天），因此大多数查询优化器，包括MySQL的查询优化器，都会在所有可能的查询评估计划中或多或少地搜索最优计划。

        对于联接查询，MySQL优化器调查的可能计划的数量随着查询中引用的表的数量呈指数级增长。对于少量的表（通常少于7到10个），这不是问题。然而，当提交更大的查询时，用于查询优化的时间很容易成为服务器性能的主要瓶颈。

        一种更灵活的查询优化方法使用户能够控制优化器在搜索最佳查询评估计划时的详尽程度。一般的想法是，优化器调查的计划越少，编译查询所花费的时间就越少。另一方面，由于优化器跳过了一些计划，它可能无法找到最佳计划。

优化器相对于其评估的计划数量的行为可以使用两个系统变量进行控制：

optimizer_prune_level变量告诉优化器根据每个表访问的行数估计跳过某些计划。

我们的经验表明，这种“有根据的猜测”很少会错过最佳计划，并可能大大减少查询编译时间。这就是为什么默认情况下此选项处于启用状态（optimizer_prune_level=1）。

但是，如果您认为优化器错过了更好的查询计划，则可以关闭此选项（optimizer_prune_level=0），这样可能会导致查询编译耗时更长。

请注意，即使使用了这种启发式方法，优化器仍然会探索大致指数数量的计划。

optimizer_search_depth变量告诉优化器应该在每个不完整计划的“未来”中将查看的深度，以评估是否应该进一步扩展。

optimizer_search_depth的值越小，查询编译时间就越小。

例如，如果optimizer_search_depth接近查询中的表数，则具有12个、13个或更多表的查询

可能很容易需要数小时甚至数天才能编译。

同时，如果使用等于3或4的optimizer_search_depth进行编译，则优化器可以在不到一分钟的时间内对同一查询进行编译。

如果您不确定optimizer_search_depth的合理值是多少，可以将该变量设置为0，以告诉优化器自动确定该值。

2.可切换优化

优化器开关系统变量可以控制优化器的行为。

它的值是一组标志，每个标志的值为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)

 要更改optimizer_switch的值，请指定一个由一个或多个命令的逗号分隔列表组成的值：

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

每个命令值都应具有下表所示的其中一种形式。

命令	意义
default	将每个优化重置为其默认值
opt_name=default	将命名优化设置为其默认值
opt_name=off	禁用命名优化
opt_name=on	启用命名优化

值中命令的顺序无关紧要，尽管默认命令会首先执行（如果存在）。

将opt_name标志设置为default会将其设置为on或off中的默认值。

不允许在值中多次指定任何给定的opt_name，这会导致错误。

值中的任何错误都会导致赋值失败并出现错误，使优化器开关的值保持不变。

---

以下列表描述了按优化策略分组的允许的opt_name标志名称：

2.1 批处理密钥访问标志

batched_key_access（默认关闭）

控制BKA联接算法的使用。

batched_key_access在设置为on时要有任何效果，mrr标志也必须为on。

目前，mrr的成本估计过于悲观。因此，也有必要关闭mrr_cost_based以使用BKA。

有关更多信息，请参阅“块嵌套循环和批处理Key访问连接”。

【MySQL精通之路】SQL优化(1)-查询优化(12)-块嵌套循环和批处理Key访问联接-CSDN博客

2.2 块嵌套循环标志

block_nested_roop（默认启用）

控制BNL联接算法的使用。

在MySQL 8.0.18及更高版本中，这也控制了散列联接的使用，BNL和NO_BNL优化器提示也是如此。

在MySQL 8.0.20及更高版本中，从MySQL服务器中删除了块嵌套循环支持，该标志仅控制散列联接的使用，引用的优化器提示也是如此。

有关更多信息，请参阅“块嵌套循环和批处理Key访问连接”。

【MySQL精通之路】SQL优化(1)-查询优化(12)-块嵌套循环和批处理Key访问联接-CSDN博客

2.3 条件筛选标志

condition-fanout-filter（默认打开）

控制条件筛选的使用。

【MySQL精通之路】SQL优化(1)-查询优化(13)-条件过滤-CSDN博客

未完待续。。。