mysql optimizer_prune_level_mysql8 参考手册--控制查询优化器

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

服务器还维护有关列值的统计信息，尽管优化器尚未使用此信息。

控制查询计划评估

查询优化器的任务是找到执行SQL查询的最佳计划。由于“好”和“坏”计划之间的性能差异可能是数量级的(即秒与小时甚至天)，因此大多数查询优化器(包括MySQL的查询优化器)或多或少都会在所有可能的查询评估计划中执行详尽的搜索，以寻找最佳计划。对于连接查询，MySQL优化器调查的可能计划数随着查询中引用的表数呈指数增长。对于少量的表(通常小于7到10个表)，这不是问题。然而，当提交更大的查询时，查询优化所花费的时间很容易成为服务器性能的主要瓶颈。

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

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

1、optimizer_prune_level变量告诉优化器根据对每个表访问的行数的估计跳过某些计划。我们的经验表明，这种“有根据的猜测”很少会错过最佳计划，而且可能会大大减少查询编译时间。这就是为什么默认情况下此选项处于启用状态(optimizer_prune_level=1)。但是，如果您认为优化器错过了一个更好的查询计划，则可以关闭此选项(optimizer_prune_level=0)，这样可能会导致查询编译花费更长的时间。请注意，即使使用了这种启发式方法，优化器仍然会探索大致呈指数级的计划数。

2、optimizer_search_depth变量告诉优化器应该观察每个不完整计划的“未来”多远，以评估是否应该进一步扩展它。优化器搜索深度的值越小，查询编译时间就越短。例如，如果优化器搜索深度接近查询中的表数，则使用12、13或更多表的查询可能很容易需要几个小时甚至几天来编译。同时，如果使用等于3或4的optimizer_search_depth进行编译，优化器可以在不到一分钟的时间内为同一查询进行编译。如果不确定优化器搜索深度的合理值，可以将此变量设置为0，以告诉优化器自动确定该值。