59.慢sql优化之otpimizer trace和show profile

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。

打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

执行SQL语句 ：

最后， 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 ：

对于以及之前的版本来说，查询优化器就像是一个黑盒子一样，你只能通过语句查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策。这对于一部分喜欢刨根问底的小伙伴来说简直是灾难：“我就觉得使用其他的执行方案比输出的这种方案强，凭什么优化器做的决定和我想的不一样呢？”

在以及之后的版本中，设计的大叔贴心的为这部分小伙伴提出了一个的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程，这个功能的开启与关闭由系统变量决定，我们看一下：

可以看到值为，表明这个功能默认是关闭的。

如果想打开这个功能，必须首先把的值改为，就像这样：

然后我们就可以输入我们想要查看优化过程的查询语句，当该查询语句执行完成后，就可以到数据库下的表中查看完整的优化过程。这个表有4个列，分别是：

• ：表示我们的查询语句。
• ：表示优化过程的JSON格式文本。
• ：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。
• ：表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是，我们暂时不关心这个字段的值。

完整的使用功能的步骤总结如下：

现在我们有一个搜索条件比较多的查询语句，它的执行计划如下：

可以看到该查询可能使用到的索引有3个，那么为什么优化器最终选择了而不选择其他的索引或者直接全表扫描呢？这时候就可以通过功能来查看优化器的具体工作过程：

我们直接看一下通过查询表得到的输出（我使用后跟随注释的形式为大家解释了优化过程中的一些比较重要的点，大家重点关注一下）：

大家看到这个输出的第一感觉就是这文本也太多了点儿吧，其实这只是优化器执行过程中的一小部分，设计的大叔可能会在之后的版本中添加更多的优化过程信息。不过杂乱之中其实还是蛮有规律的，优化过程大致分为了三个阶段：

• 阶段
• 阶段
• 阶段

我们所说的基于成本的优化主要集中在阶段，对于单表查询来说，我们主要关注阶段的这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；对于多表连接查询来说，我们更多需要关注这个过程，这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用语句所展现出的那种方案。

如果有小伙伴对使用语句展示出的对某个查询的执行计划很不理解，大家可以尝试使用功能来详细了解每一种执行方案对应的成本，相信这个功能能让大家更深入的了解查询优化器。

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

默认profiling是关闭的，可以通过set语句在Session级别开启profiling：

通过profile，我们能够更清楚地了解SQL执行的过程。

首先，我们可以执行一系列的操作，如下图所示：

执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时：

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：

字段	含义
Status	sql 语句执行的状态
Duration	sql 执行过程中每一个步骤的耗时
CPU_user	当前用户占有的cpu
CPU_system	系统占有的cpu