1.1.2　优化器与优化器追踪（Optimizer Trace）

在1.1.1节中，我们介绍了SQL语句在服务层的运行过程，也介绍了优化器的几个主要步骤。MySQL并不因为优化器而著称，甚至相反，MySQL的优化器经常让人觉得不够聪明。

MySQL优化器特点一：不存储执行计划

首先要知道MySQL优化器有别于其他关系型数据库的一大特点，几乎只有MySQL每次都重新生成执行计划，这在Oracle中叫作硬解析（Hard Parse），在SQL Server中叫作重编译（Recompile），而在其他主流的关系型数据库中都会把执行计划缓存起来，以便下次需要时取用。这是为什么呢？

Oracle/SQL Server在生成执行计划时，需要进行大量的演算，因为算法的复杂性，调用了很多统计信息等其他元数据作为参考。这个开销是难以忽略的，甚至在某些场景中会成为瓶颈。

下面的两个经典场景，可能会因为执行计划的重新生成而带来性能问题。

场景1：变量嗅探（Parameter Sniffing）。为了重用执行计划，对于相同的SQL语句，不同的值，依然会使用相同的执行计划。比如select * from table_1 where id=@1;，如果id有索引，优化器很有可能选择使用索引进行书签查找（Bookmark Lookup）来获取所有记录。但如果@1是一个异常的值，比如@1=2时将会返回表中80%的数据，那么使用索引显然并不是好的选择。因为执行计划的重用，我们会发现某类SQL语句在特定的谓词变量下性能变得很差。

场景2：缺失绑定变量（Bind Variables）。在Oracle中，因为每次生成执行计划时代价都非常大，所以Oracle会绑定变量来稳定SQL模板。如果频繁执行一类相似的SQL语句，但每次都以显式的值的场景来执行[又称为动态SQL（Ad-Hoc）]，则无疑会导致Oracle进行大量硬解析，消耗大量资源，出现CPU跑高的情况。

在关系型数据库中，这类问题十分常见。无论是DBA，还是数据库厂商，都在通过各种方式来优化性能，追踪更多的历史情况。比如SQL Server，就提供了“query store”的功能，帮助优化器更加智能、准确地选择适合每次查询的执行计划。

我们回头看MySQL，似乎在执行计划的管理上，它还停留在“刀耕火种”的时代。这时有些读者可能会问，MySQL会不会遇到“场景2”中的性能问题呢？

答案是不会，因为MySQL的优化器算法并没有那么复杂，所以优化器的开销也没有那么大。然而，有时候它的执行计划也显得不那么靠谱，比如对于非常复杂的子查询或者join关系转换时，就很难保证性能。

MySQL优化器特点二：Arbitrary

Arbitrary，一般用来形容一个人专制武断，用今天流行的话说，叫作“任性”。这个词并不是笔者自创的，而是在以往的工作中，和MySQL优化器的核心专家讨论时，对方给出的评价。

在MySQL 5.6中，这个特点尤为明显。我们来看下面这个例子。

上面的表结构大致是这样的：sample_table的主键是id，sample_id上有一个二级索引idx_1，但它是组合的，即idx_1（sample_id, org_id, tpl_id）。

可以看到，当sample_id =125时，优化器选择了idx_1，之后再进行“回表”（即书签查找，对应这里的Using where），最后根据order by进行外排序（Using filesort）。

而当sample_id=135时，优化器会直接选择主键，并不会使用二级索引。

是不是很困惑？有没有什么办法知道优化器是怎么做选择的呢？有，使用优化器追踪，可以知道优化器大致的思索过程。通过“set optimizer_trace ='enabled=on'”，打开Session级别的追踪，然后再执行一次explain，这一次执行explain的结果就会存在于information_schema下的optimizer_trace表中。

这样就会返回大量结果（这里省略了结果）。注意，这里的“/G”表示按文本格式返回，比较适合trace这类结果。

按照这种方法，我们采集了上述两个不同的执行计划的生成过程。

我们会发现，优化器一开始都想使用idx_1的ref请求，但是在sample_id=135的执行计划中，因为这样做成本较高，所以优化器决定改使用主键，原因如粗体字代码所示，优先考虑排序。

事实上，我们发现，优化器对主键的评估是有问题的，它评估的主键访问行数（estimated rows）只有4000行（前文执行explain的结果）。实际上，在优化器trace中，二级索引任务的访问行数应该有6000行。很多时候，“LIMIT 1”会给优化器一些误导，让它总觉得可以选择主键，笔者在多个案例中也看到过这种情况。

那么，这个问题有没有办法解决呢？

办法肯定是有的。但是要想从根源上解决这个问题，有两个选择：一是从索引上解决，这里的索引没有做到全覆盖，而且返回条件和排序条件是一致的，可以考虑强制做索引覆盖。

id是主键，可加可不加。

二是使用Hint，绑定索引。

这时可能有读者会问，Hint到底好不好呢？

Hint的好处显而易见，它让优化器不要再做其他挣扎，按照我们的指定来执行就行了。但是Hint也有缺点——如果创建的索引失效了，那么这个执行计划会变成使用主键，执行速度会非常慢。

索引失效的场景并不太多，主要有如下两个场景。

场景一：使用DDL删除了索引，这就需要在线上对DDL有良好的管控。关于如何有效管理线上SQL语句的审计，我们会在第5章中讲述最佳实践。

场景二：表的数据分布发生了很大变化，使用索引可能还不如使用全表来得快，就如我们前面讲到的第一个例子。本质上，Hint发挥的功效和缓存执行计划相同，所以它也要承担相同的风险。

最新版本的MySQL 8.0 提供了一个新的参数，即prefer_ordering_index，允许不考虑排序对索引选择的影响。其优点是能够稳定地通过谓词判断索引；其缺点是，以前也许能够快速凑齐第一个分页的执行计划，带有很强的运气色彩，现在关闭后，性能会趋于平均。

可以说，优化器并不是MySQL的强项，在1.3节中，当我们讲到PGSQL的优化器时，读者可以再对比一下。但在MySQL的一系列发展过程中，我们能够明显地看到类似于MySQL 5.6中存在的这样的问题越来越少。在MySQL 8.0中，不仅引入了Hash Join这样的新特性，同时还增加了很多令人翘首以盼的加强功能，比如并行扫描（Parallel Scan）的引入，虽然目前它可能只能在select count的场景下使用，但其趋势是看好的。