问题
一个用户问题，数据从ECS迁移到RDS，相同的语句，查询性能下降了几十倍。而实际上RDS这个实例在内存上的配置与原来ECS上的实例相当。
<code style="padding: 0px; font-family: inherit; font-size: 14px; color: inherit; border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; background-color: transparent; border: 0px; font-style: inherit; font-weight: inherit;">本文简单说明这个case的原因及建议。用户反馈性能变慢的语句为 （修改了真实表明和列名）select count(1)from HR hr join H h on h.hid = hr.hid join A e one.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.pidleft 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 );</code>
背景
<code style="padding: 0px; font-family: inherit; font-size: 14px; color: inherit; border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; background-color: transparent; border: 0px; font-style: inherit; font-weight: inherit;">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的选择就成了问题。</code>
<code style="padding: 0px; font-family: inherit; font-size: 14px; color: inherit; border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; background-color: transparent; border: 0px; font-style: inherit; font-weight: inherit;">MySQL Tips: MySQL中optimizer_search_depth默认值为62.也就是说默认为全排列计算。</code>
<code style="padding: 0px; font-family: inherit; font-size: 14px; color: inherit; border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; background-color: transparent; border: 0px; font-style: inherit; font-weight: inherit;">这样能够保证得到最优的执行计划，只是在有些场景下，决定执行计划的时间会远大于执行时间本身。</code>
量化分析
在ECS上，是用户自己维护的MySQL，没有设置optimizer_search_depth，因此为默认的62.
在RDS上，我们的配置是4。
分析到这里大家能猜到原因是RDS配置的4导致没有得到最优的执行计划。
<code style="padding: 0px; font-family: inherit; font-size: 14px; color: inherit; border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; background-color: transparent; border: 0px; font-style: inherit; font-weight: inherit;">下图是optimizer_search_depth=4时的explain结果（隐藏了业务相关的表名、字段名)<img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405KJ0BGZ-21153.jpg" class="lazy" alt="alt" style="max-width:90%">下图是optimizer_search_depth=62是的场景，当然这个case的join表是8个，因此62和8在这里是等效的。<img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405KJ0G0940-33412.jpg" class="lazy" alt="alt" style="max-width:90%">从图1可以看到，由于optimizer_search_depth=4，优化器认为自己选择了最优的join顺序(22039*1*1*1)，优于(41360*1*1*1)，而实际上后者才是全局最优。有趣的是，在这个case里面如果多看一层，就能得到最有解，因为第一个join顺序的第五个表评估rows为82720。这意味着，在这个case里面，设置为5与设置为62能得到相同的执行计划，当然设置为5时的优化器执行代价更小。这其实也就是提供optimizer_search_depth的本意：减少优化器执行时间，而且概率上还存在局部最优就是全局最优解的情况。</code>
关于实践
可配置的参数提供灵活性的同时，也提出一个头疼的问题：应该设置为多少才合适。
实际上当用户执行一个多表join的时候，对这个语句的整体RT的期望值就不会高。因此可以先定义一个预期，比如优化器决策join顺序的时间不能超过500ms。
用户规格与cpu相关，因此这个只能是建议值。
用户实践
实际上更重要的是对于用户来说：
<code style="padding: 0px; font-family: inherit; font-size: 14px; color: inherit; border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; background-color: transparent; border: 0px; font-style: inherit; font-weight: inherit;">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; 结果如图<img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405KJ0JDP-4T62.jpg" class="lazy" alt="alt" style="max-width:90%">继续执行set optimizer_search_depth=40;explain select .......show profile for query 4; <img src="/static/imghwm/default1.png" data-src="http://img.bitscn.com/upimg/allimg/c140719/1405KJ0MQ20-525A.jpg" class="lazy" alt="alt" style="max-width:90%">图中标红部分显示了两次优化器的执行时间差异。</code>
小结
<code style="padding: 0px; font-family: inherit; font-size: 14px; color: inherit; border-top-left-radius: 3px; border-top-right-radius: 3px; border-bottom-right-radius: 3px; border-bottom-left-radius: 3px; background-color: transparent; border: 0px; font-style: inherit; font-weight: inherit;">1)根据机器配置估算一个可接受的时间，用于优化器选择join顺序。2)用profiling确定是否设置了过大的optimizer_search_depth。3)业务上优化，尽量不要使用超过10张表的多表join。4)PS:不要相信银弹。MySQL文档说设置为0则表示能够自动选择</code>optimizer_search_depth的合理值，实际上代码上策略就是，如果join表数N
深入理解MySQL索引优化器工作原理Nov 09, 2022 pm 02:05 PM本篇文章给大家带来了关于mysql的相关知识，其中主要介绍了关于索引优化器工作原理的相关内容，其中包括了MySQL Server的组成，MySQL优化器选择索引额原理以及SQL成本分析，最后通过 select 查询总结整个查询过程，下面一起来看一下，希望对大家有帮助。
sybase是什么数据库Sep 22, 2021 am 11:39 AMsybase是基于客户/服务器体系结构的数据库，是一个开放的、高性能的、可编程的数据库，可使用事件驱动的触发器、多线索化等来提高性能。
visual foxpro数据库文件是什么Jul 23, 2021 pm 04:53 PMvisual foxpro数据库文件是管理数据库对象的系统文件。在VFP中，用户数据是存放在“.DBF”表文件中；VFP的数据库文件（“.DBC”）中不存放用户数据，它只起将属于某一数据库的 数据库表与视图、连接、存储过程等关联起来的作用。
数据库系统的构成包括哪些Jul 15, 2022 am 11:58 AM数据库系统由4个部分构成：1、数据库，是指长期存储在计算机内的，有组织，可共享的数据的集合；2、硬件，是指构成计算机系统的各种物理设备，包括存储所需的外部设备；3、软件，包括操作系统、数据库管理系统及应用程序；4、人员，包括系统分析员和数据库设计人员、应用程序员（负责编写使用数据库的应用程序）、最终用户（利用接口或查询语言访问数据库）、数据库管理员（负责数据库的总体信息控制）。
microsoft sql server是什么软件Feb 28, 2023 pm 03:00 PMmicrosoft sql server是Microsoft公司推出的关系型数据库管理系统，是一个全面的数据库平台，使用集成的商业智能（BI）工具提供了企业级的数据管理，具有使用方便可伸缩性好与相关软件集成程度高等优点。SQL Server数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使用户可以构建和管理用于业务的高可用和高性能的数据应用程序。
go语言可以写数据库么Jan 06, 2023 am 10:35 AMgo语言可以写数据库。Go语言和其他语言不同的地方是，Go官方没有提供数据库驱动，而是编写了开发数据库驱动的标准接口，开发者可以根据定义的接口来开发相应的数据库驱动；这样做的好处在于，只要是按照标准接口开发的代码，以后迁移数据库时，不需要做任何修改，极大方便了后期的架构调整。
数据库的什么是指数据的正确性和相容性Jul 04, 2022 pm 04:59 PM数据库的“完整性”是指数据的正确性和相容性。完整性是指数据库中数据在逻辑上的一致性、正确性、有效性和相容性。完整性对于数据库系统的重要性：1、数据库完整性约束能够防止合法用户使用数据库时向数据库中添加不合语义的数据；2、合理的数据库完整性设计，能够同时兼顾数据库的完整性和系统的效能；3、完善的数据库完整性有助于尽早发现应用软件的错误。
mysql查询慢的因素除了索引，还有什么？Jul 19, 2022 pm 08:22 PMmysql查询为什么会慢，关于这个问题，在实际开发经常会遇到，而面试中，也是个高频题。遇到这种问题，我们一般也会想到是因为索引。那除开索引之外，还有哪些因素会导致数据库查询变慢呢？
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment
mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),
SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.
WebStorm Mac version
Useful JavaScript development tools
DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software
