MySQL 8.4 Release Notes
The task of the query optimizer is to find an optimal plan for executing an SQL query. Because the difference in performance between “good”以及“bad” plans can be orders of magnitude (that is, seconds versus hours or even days), most query optimizers, including that of MySQL, perform a more or less exhaustive search for an optimal plan among all possible query evaluation plans. For join queries, the number of possible plans investigated by the MySQL optimizer grows exponentially with the number of tables referenced in a query. For small numbers of tables (typically less than 7 to 10) this is not a problem. However, when larger queries are submitted, the time spent in query optimization may easily become the major bottleneck in the server's performance.The task of the query optimizer is to find an optimal plan for executing an SQL query. Because the difference in performance between “good” and “bad” plans can be orders of magnitude (that is, seconds versus hours or even days), most query optimizers, including that of MySQL, perform a more or less exhaustive search for an optimal plan among all possible query evaluation plans. For join queries, the number of possible plans investigated by the MySQL optimizer grows exponentially with the number of tables referenced in a query. For small numbers of tables (typically less than 7 to 10) this is not a problem. However, when larger queries are submitted, the time spent in query optimization may easily become the major bottleneck in the server's performance.
一种更灵活的查询优化方法使用户能够控制优化器在寻找最优查询执行计划时的全面程度。总体思路是，优化器调查的计划越少，编译查询所花的时间就越少。另一方面，由于优化器跳过了一些计划，它可能会错过找到最优计划的机会。A more flexible method for query optimization enables the user to control how exhaustive the optimizer is in its search for an optimal query evaluation plan. The general idea is that the fewer plans that are investigated by the optimizer, the less time it spends in compiling a query. On the other hand, because the optimizer skips some plans, it may miss finding an optimal plan.
优化器在评估计划数量方面的行为可以通过两个系统变量进行控制：The behavior of the optimizer with respect to the number of plans it evaluates can be controlled using two system variables:
optimizer_prune_level变量告诉优化器基于对每个表访问行数的估计来跳过某些执行计划。我们的经验表明，这种类型的 “有根据的猜测” 很少会错过最优计划，并且可能大幅减少查询编译时间。这就是为什么该选项默认开启 (optimizer_prune_level=1)。然而，如果你认为优化器遗漏了更好的查询计划，可以关闭此选项 (optimizer_prune_level=0)，风险是查询编译可能会花费更长时间。请注意，即使使用这种启发式方法，优化器仍然会探索大致呈索引级的计划数量。Theoptimizer_prune_levelvariable tells the optimizer to skip certain plans based on estimates of the number of rows accessed for each table. Our experience shows that this kind of “educated guess” rarely misses optimal plans, and may dramatically reduce query compilation times. That is why this option is on (optimizer_prune_level=1) by default. However, if you believe that the optimizer missed a better query plan, this option can be switched off (optimizer_prune_level=0) with the risk that query compilation may take much longer. Note that, even with the use of this heuristic, the optimizer still explores a roughly exponential number of plans.该
optimizer_search_depth未来“中应查看多远，以评估是否应进一步扩展它。较小的”值。optimizer_search_depth可能导致查询编译时间减少数个数量级。例如，如果查询中有 12、13 个或更多表，当optimizer_search_depth接近查询中的表数时，编译可能轻松需要数小时甚至数天。同时，如果使用optimizer_search_depth等于 3 或 4 进行编译，对于同一查询，优化器可能在不到一分钟内完成编译。如果你不确定optimizer_search_depth的合理值是多少，可以将该变量设置为 0，让优化器自动确定值。Theoptimizer_search_depthvariable tells how far into the “future” of each incomplete plan the optimizer should look to evaluate whether it should be expanded further. Smaller values ofoptimizer_search_depthmay result in orders of magnitude smaller query compilation times. For example, queries with 12, 13, or more tables may easily require hours and even days to compile ifoptimizer_search_depthis close to the number of tables in the query. At the same time, if compiled withoptimizer_search_depthequal to 3 or 4, the optimizer may compile in less than a minute for the same query. If you are unsure of what a reasonable value is foroptimizer_search_depth, this variable can be set to 0 to tell the optimizer to determine the value automatically.