MySQL – optimizer_search_depth - 技术文章

Working on customer case today I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just in case) which would take 5 seconds even though in the read less than 1000 rows and doing it completely in memory. The plan optimizer picked was very good one, yet you could notice EXPLAIN itself was taking same 5 seconds, which points to problem with optimizer performance. Note though if you have subqueries these might need to be executed during EXPLAIN phase yet making it unusable to check the optimizer performance.

Solution for this problem was to use set optimizer_search_depth=0, rarely used option which as per manual will chose best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. Low values, such as 3,4 provided a bit better performance but I decided against using this as I did not want to risk likehood of execution plans changing for some over queries joining less number of tables.

I was wondering if 0 is automatic selection why do we have value of 62 being default in MySQL 5.5 which can produce very expensive plan selections ? Investigating this further I found the following explanation from Timour Katchaounov in MySQL mailing list archives

I have some recollection that there were few main reasons for the
decision to keep exhaustive search as the default:
- backwards compatibility,
- the hypothesis that most users have joins with few tables,
- it is not clear how far from optimal plans do we get by using a greedy
search.

From the same discussion we can learn how automatic selection works – it picks value of min(number of tables, 7) essentially limiting search depth to no more than 7 at which complexity is reasonable. This makes Timour explanation somewhat conflicting though as if we assume MySQL users do not join lots of tables (less than 7) when using 0 as default value would not impact them.
For people who have more than 7 tables in join I think faster execution plan computation would be more important than backward compatibility.

In MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer heuristics are improved so much higher search depths are feasible now.

A customer of ours had an interesting problem regarding a query that was taking too long, around 55s. Looking at the query with the query profiler we found that it was spending most of its time in the "statistics" phase. Now the query was pretty complex, it contained nearly 20 tables with INNER JOINs, LEFT JOINs and even some subqueries. However the tables were small and fetching all the data shouldn‘t have taken the 55 seconds the query was taking. The problem was that the optimiser was spending too much time evaluating and finding the optimal execution plan.

There are two options in MySQL with which you can control the optimiser‘s behaviour a bit. The first one is optimizer_prune_level. The pruner discards non-optimal execution plans early without evaluating them fully. It is turned on by default and is not recommended to turn off unless there‘s a really good reason. For testing purposes we turned the pruner off for this query, but after evaluating the query for over half an hour we gave up on the test and decided to call it infinity. This is understandable, for a 20 table join there are 20! (~2.4 x 10^18) possible execution paths, so evaluating all of them would take forever (now this query contained LEFT JOINs and subqueries so that number is not exactly correct, but still used here to spread fear).

The second interesting option is the optimizer_search_depth. This defines how deep into the execution path the optimizer should look before deciding which plan to use. If this is set to a value higher than the number of tables in the query it means that all possible execution plans (except pruned ones) will be examined. If it‘s set lower than the number of tables in the query, the optimiser will not evaluate each path to its full extent but choose the first level based on all plans evaluated to the specified depth and then do a new evaluation for the next level and so on until the full execution plan has been chosen. Trying a few different values we got the following results:

SET SESSION optimizer_search_depth = 1;
-> statistics 0.000591

SET SESSION optimizer_search_depth = 5;
-> statistics 0.002321

SET SESSION optimizer_search_depth = 10;
-> statistics 0.365812

SET SESSION optimizer_search_depth = 15;
-> statistics 5.054150

SET SESSION optimizer_search_depth = 0;
-> statistics 0.026904

All of the above are much better than the 58.497217s we got with the default search depth of 62. Note that the value 0 (zero) is a special case where the optimiser chooses and sets the optimal search depth for each query, thus adding a bit of overhead to the optimisation process.

So why not just tune down the search depth to 1 for all queries? The problem here is that you can then miss optimal execution plans as the optimizer won‘t go deep enough down the execution plan before choosing which plan to use at each level. You might get an execution plan that is horribly wrong. So the best way to go if you have many queries with more than 15 tables is to choose an intermediate value of 7-8 or so, which will produce the optimal execution plan in 99% of all cases, but won‘t waste too much time finding it.

The best solution is of course to optimize all the 15+ table queries yourself but that‘s another story!

一个用户工单:数据从ECS迁移到RDS，相同的语句，查询性能下降了几十倍。而实际上RDS这个实例在内存上的配置与原来ECS上的实例相当。

本文简单说明这个case的原因及建议。

用户反馈性能变慢的语句为（修改了真实表名和列名）
select count(1) from HR hr join H h on h.hid = hr.hid
join A e on e.aid = h.eid
join A t on t.aid = e.pid
join A c on c.aid = t.pid
join A p on p.aid = c.pid
left join U u on u.uid = hr.uId
left join E emp on emp.eid = hr.oid
where ( hr.s in (1,2,3,4) and hr.cn = 0 );

背景

MySQL执行语句过程中涉及到两大流程：优化器和执行器。其中优化器最主要的任务，是选择索引和在多表连接时选择连接顺序。在这个case中，join顺序的选择影响了执行性能。

确定join执行顺序就需要估算所有join操作的代价。默认配置下MySQL会估算所有可能的组合。

MySQL Tips: MySQL里限制一个查询的join表数目上限为61.

对于一个有61个表参与的join操作，理论上需要61!(阶乘)次的评估。当然这是最坏情况下，实际上减枝算法会让这个数字看起来稍微好一点，但是仍然很恐怖。

在多表join的场景下，为了避免优化器占用太多时间，MySQL提供了一个参数 optimizer_search_depth 来控制递归深度。
这个参数对算法的控制可以简单描述为：对于所有的排列，只取前当前join顺序的前optimizer_search_depth个表估算代价。举例来说，20张表的，假设optimizer_search_depth为4，那么评估次数为20*19*18*17，虽然也很大（因此我们特别不建议这么多表的join），比20！好多了。

于是optimizer_search_depth的选择就成了问题。

MySQL Tips: MySQL中optimizer_search_depth默认值为62.也就是说默认为全排列计算。

这样能够保证得到最优的执行计划，只是在有些场景下，决定执行计划的时间会远大于执行时间本身。

量化分析

在ECS上，是用户自己维护的MySQL，没有设置optimizer_search_depth，因此为默认的62。在RDS上，我们的配置是4。分析到这里大家能猜到原因是RDS配置的4导致没有得到最优的执行计划。

下图是optimizer_search_depth=4时的explain结果（隐藏了业务相关的表名、字段名)

下图是optimizer_search_depth=62是的场景，当然这个case的join表是8个，因此62和8在这里是等效的。

从图1可以看到，由于optimizer_search_depth=4，优化器认为自己选择了最优的join顺序(22039*1*1*1)，优于(41360*1*1*1)，而实际上后者才是全局最优。

关于实践

可配置的参数提供灵活性的同时，也提出一个头疼的问题：应该设置为多少才合适。实际上当用户执行一个多表join的时候，对这个语句的整体RT的期望值就不会高。因此可以先定义一个预期，比如优化器决策join顺序的时间不能超过 500ms。用户规格与cpu相关，因此这个只能是建议值。

用户实践

实际上更重要的是对于用户来说：

1) 当出现实例迁移后，多表join执行结果差异较大的时候，要考虑调整这个值。该参数是允许线程单独设置，因此对于应用层来说，每个连接应该都能得到一个较优的值。

2) 反过来，当设置为默认的optimizer_search_depth=62时，我们我们如何评估我们这个设置是否过大?
MySQL Tips:MySQL profiling 可以用于查看各执行环节的消耗时间。

如下是笔者构造的一个60个表join查询的查询，使用profiling查看执行环节消耗的过程。
set profiling=1;
set optimizer_search_depth=4;
explain select …….
show profile for query 2;
结果如图

继续执行
set optimizer_search_depth=40;
explain select …….
show profile for query 4;

小结

1)根据机器配置估算一个可接受的时间，用于优化器选择join顺序。

2)用profiling确定是否设置了过大的optimizer_search_depth。

3)业务上优化，尽量不要使用超过10张表的多表join。

4)PS:不要相信银弹。MySQL文档说明设置为0则表示能够自动选择optimizer_search_depth的合理值，实际上代码上策略就是，如果join表数N<=7，则optimizer_search_depth=N+1，否则选N.

optimizer_search_depth

Command-Line Format	`--optimizer_search_depth[=#]`
Option-File Format	`optimizer_search_depth`
System Variable Name	`optimizer_search_depth`
Variable Scope	Global, Session
Dynamic Variable	Yes
	Permitted Values
	Type	`numeric`
	Default	`62`
	Min Value	`0`
	Max Value	`62`

The maximum depth of search performed by the query optimizer. Values larger than the number of relations in a query result in better query plans, but take longer to generate an execution plan for a query. Values smaller than the number of relations in a query return an execution plan quicker, but the resulting plan may be far from being optimal. If set to 0, the system automatically picks a reasonable value.（这是假的，是按照这个来做的，min(number of tables, 7) ）

参考：

http://www.percona.com/blog/2012/04/20/joining-many-tables-in-mysql-optimizer_search_depth/

https://mariadb.com/blog/setting-optimizer-search-depth-mysql

http://blog.aliyun.com/428