同一条SQL在不同机器上效率差异有哪些情况？

用户遇到的问题是数据从ECS迁移到RDS。对于同一条语句，查询性能下降了几十倍。事实上，RDS实例的内存配置与原ECS实例的内存配置相当。

文章简要阐述了本案的原因及建议。用户反映性能较慢的语句为（修改真实表名和列名） 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 数据库执行语句涉及两个主要进程：优化器和执行器。优化器最重要的任务是选择索引以及连接多个表时选择连接顺序。在这种情况下，连接顺序的选择会影响执行性能。确定连接执行的顺序需要估计所有连接操作的成本。在默认配置中，MySQL 将评估所有可能的组合。 MySQL提示：MySQL限制一次查询的连接表数量上限为61个。对于涉及61张表的连接操作，理论上是61个！需要进行（阶乘）评估。当然这是最坏的情况，分支减少算法实际上让这个数字看起来稍微好一点，但仍然很可怕。在多表join的场景下，为了防止优化器占用过多的时间，MySQL提供了一个参数optimizer_search_depth来控制递归深度。这个pa的控制该算法上的参数可以简单地描述为：对于所有排列，仅采用当前连接顺序中的第一个optimizer_search_depth表来估计成本。例如，对于20张表，假设optimizer_search_depth为4，则评估次数为20*19*18*17，仍然很大（所以我们不建议与这么多表进行join），高于20！好多了。所以optimizer_search_depth的选择就成了一个问题。 

MySQL提示：MySQL中optimizer_search_depth默认值为62，也就是说默认是全排列计算。 

这样可以保证最优的执行计划，但在某些场景下，确定执行计划的时间会比执行时间本身长很多。 

定量分析

在ECS上，是用户维护的MySQL。 Optimizer_search_depth没有设置，所以是默认的62。
在RDS上，我们的配置是4。
分析完后这个，大家可以猜到原因是RDS配置4导致没有得到最优的执行计划。

下图是optimizer_search_depth=4时的解释结果（业务相关的表名和字段名被隐藏），下图是optimizer_search_depth=62时的场景。当然，本例中有 8 个连接表，因此这里 62 和 8 是等效的。从图1可以看出，因为optimizer_search_depth=4，优化器认为自己选择了最优的连接顺序（22039*1*1*1），比（41360*1*1*1）要好，但是在事实上后者是全局最优的。有趣的是，如果在这种情况下再看一层，您可以获得最佳解决方案，因为第一个 jo 顺序的第五个表计算行数为 82720。这意味着在这种情况下，将其设置为 5，并将其设置为62可以得到相同的执行计划。当然，设置为5时优化器执行成本更小。这实际上是原点提供optimizer_search_depth的最终目的：减少优化器的执行时间，并且局部最优解有一定概率是全局最优解。 

关于实践
虽然可配置参数提供了灵活性，但也带来了一个令人头痛的问题：应该设置多少为合适的值。
事实上，用户在执行多表连接时，对于这条语句的整体RT期望值不会太高。因此，您可以首先定义一个期望。例如，优化器决定连接顺序所花费的时间不能超过500ms。
用户规格与CPU相关，因此这只能是推荐值。

用户实践
其实对于用户来说更重要的是：

1）实例迁移时，多表join执行当结果差异较大时，请考虑调整该值。该参数允许单独设置线程，因此 fo在应用层，每个连接应该能够获得更好的值。 2）反之，当设置为默认的optimizer_search_depth=62时，我们如何评估我们的设置是否太大呢？ MySQL小技巧：可以通过MySQL profiling查看各个执行环节所消耗的时间。以下是作者构造的60表连接查询。使用profiling可以查看执行链路消耗的进程。设置分析=1；设置optimizer_search_depth=4；解释选择.......显示查询2的配置文件；结果如图 继续执行 set optimizer_search_depth=40;解释选择.......显示查询4的配置文件；图中红色部分显示了两个优化器执行时间的差异。 

总结

1）根据机器配置估计优化器选择连接序列的可接受时间。 2) 使用profiling来确定optimizer_search_depth是否设置得太大。 3）对于商业o优化，尽量不要使用超过10张表的多表连接。 4）PS：不要相信灵丹妙药。 MySQL文档说，将其设置为0意味着可以自动选择optimizer_search_depth的合理值。其实代码策略是，如果join表个数N<=7，则optimizer_search_depth=N+1，否则选择N。 多表join的参数
这个这里分享《同一条SQL在不同机器上效率差异有哪些情况》的文章。希望以上内容能够给大家带来一些帮助，让大家能够学到更多的知识，如果您觉得文章不错，请分享出去，让更多的人看到。