mysql性能优化器使用指南

本文由The Unofficial MySQL 8.0 Optimizer Guide翻译得来，虽然原文标题为《MYSQL 8.0非官方性能优化器指南》，但实际上大量内容可以运用在和上。所以我并不打算按原文一字不动翻译，会做部分修改，并添加一些本人的见解，添加一些更细致的说明。

虽然本文说是“非官方”，但是作者却是MySQL项目经理，而且得到了MySQL性能优化器团队的支持，可以认为依然属于官方的文档，只是没有放在MySQL官方文档站点上。

阅读本文需要基本的MySQL的知识，可以查阅官方文档。同时建议阅读《高性能MYSQL》，本文可以说是这本书的补充。

本文数据库基于引擎。

本文中diff代码块不代表增删，仅仅用于颜色标记。

顺便提一下，MySQL优化不是万能的，特别是包含+之类的sql。MySQL是OLTP（联机事务处理）数据库，对于OLAP（联机分析处理）的需求，建议从业务角度解决，或者使用其他数据库。

Morgan Tocker是Oracle的MySQL服务器产品经理。他之前曾担任过各种角色，包括支持、培训和社区。摩根总部位于加拿大多伦多。联系原作者twitter @morgo。

• 感谢MySQL Optimizer团队提供服务器架构、成本模型和查询优化的图表。
• 感谢Chrissy Cutting的复制编辑。

示例基于MySQL官网的数据库，它可以从http://dev.MySQL.com/doc/index-other.html下载。进入页面后，下载中的库导入到本地数据库中，即可同步实验。由于本文的sql语句表名首字母都是大写，所以最后需要重命名表名。

将查询(queries)作为输入，并通过本章中描述的过程产生一个执行计划(execution plan)作为输出。我喜欢将查询优化描述为类似于GPS导航：

1. 你输入一个地址作为目的地：
   • 大街3294号
2. 它告诉你如何以最有效的方式到达那里：
   • 继续直行2英里
   • 在万锦街左转
   • 继续500英尺
   • 右转
   • 继续1000英尺
   • 你的目的地在右边

地址是目的地。你没有指定如何到达那里，但你希望导航系统评估潜在路线并建议你最有效的路线。

SQL查询类似于地址，因为SQL语言是声明性的。它传达了最终状态，而不是如何到达那里的过程。同样，由于一个数据库系统会有很多索引（并且可以join更多表），所以也有很多路由可以达到相同的结果。

作为这个类比的最终用途，就像GPS导航并不总是将你引导到绝对最快的路线上一样，查询优化也是如此。正如并非总是有所有街道的交通数据一样，优化器只能在不完整的模型下工作。所以对于有经验的操作员，这可能会需要重写和调整查询语句的情况。

在高层次上，MySQL服务器由两个不同的部分组成：服务器层和存储引擎层。查询优化发生在存储引擎API之上的服务器层，并且在语义上分为四个阶段：

• 逻辑转换（Logical Transformations）
• 准备开始基于成本的优化（Preparation for Cost-based Optimization）
• 基于成本的优化（Cost-based Optimization）
• 计划细化（Plan Refinement）

将优化器划分为以上定义的多层是长期发展过来的。在MySQL的早期，优化器的查询优化较少，出于性能原因，查询优化的阶段没有很好地分离或定义。

由于新特性降低了可维护性，这就产生了重构的需求，重构已经在MySQL的多个版本（5.6、5.7和8.0）上实现。将执行分成多个阶段也为新功能铺平了道路。例如，如果没有明确的分离，计划缓存（plan caching）将难以实现，因为连接之间的重用变得困难。

虽然还有其他可用的索引类型，但添加索引通常意味着添加B+树索引，无论它是主键、唯一索引还是普通索引。通过很好地理解B+树，你不仅可以提高单个查询的性能，还可以减少必须在内存中的数据（工作集）。这反过来又有助于提高数据库的整体可扩展性。

为了解释B+树的工作原理，我想先解释二叉树的工作原理，然后描述它们的不同之处：

二叉树的主要特性是它具有二叉搜索（binary search）。这意味着要在有100万个条目的（平衡）二叉树中查找这个条目，应该不会超过20跳。与扫描整个列表中的100万个条目相比，这是一个相当大的改进：

但我还是想说二叉树是有不足的，如果它进行20次磁盘访问，那么20跳实际上是一个很高的数字。即使每次访问磁盘仅为了获取一行极少的数据，这些IO导致的资源消耗依然很昂贵。读取4K和500B的成本通常相似，B+树有助于分摊IO成本（即使对于SSD也是如此）。

之所以成本相似，是因为我们的文件系统创建时（分区时）通常会将block大小设置为4K。而不管是4K还是小于4K，操作系统读取数据时都会一次读取4K数据并缓存到内存中，为的是避免多次磁盘IO影响性能。如果你的系统有大量小于4K的文件，例如几百万500B的文件，可以在创建文件系统时选择更小的block，以避免磁盘浪费（每一个文件，至少占用1个block）。

相比之下，这是在仅使用2跳的B+tree中进行的相同搜索：

显示来自使用jeremycole/innodb_ruby分析的InnoDB B+tree的实际值

上图显示根索引页(root index page)指向25个不同的索引页(index page)，这些索引页本身均匀的指向1538个叶页(leaf page)。查找行的路径做了高亮显示：

• 从开始查找：在页面上
• 从页面查找： 在叶子页面上
• 最后在叶子页面找到行

所以想对于二叉树来说，我们在B+树有2个特别的增强功能：

1. 将数据组织成页面。这是为从存储中读取和写入而组织在一起的基本数据单元。这种组织方式在数据库术语中称为集群（不要与数据库集群混淆）。
2. 树本身宽而不深。当每个索引页可以容纳1000+个键时，而每个键又可以指向另一个具有另外1000个键的索引页，在叶子页面之前有一个结构深度超过3层的B+树并不常见（而且InnoDB数据库主键包含整行数据）。另一种表述方式是B+树比二叉树能更快的定位获取数据。

将根页面加上25个内部索引页面保留在内存中只需要400KiB的内存（每个页面为16KiB）。38463个叶子页面将需要600MiB的存储空间，但不一定必须同时加载全部存储到内存中。大多数数据集都只有一部分热点页面，其他数据的基本不会使用到。InnoDB在内部使用修改后的LRU算法跟踪页面访问，如果需要腾出空间，将驱逐不常访问的页面。

通常，总数据大小可能远远超过内存，我们将需要保存在内存中的数据部分称为。每个数据集都有不同的工作集要求，数据库管理员的目标是找到可以减少它的优化。

一个有300GB工作集的500GB数据库，优化难度比只有100GB工作集的1TB数据库更难优化，优化难度往往和工作集大小关系更密切，工作集越大越难优化。

描述MySQL计划如何执行给定的查询。这就是说它并不真正执行查询，只是给出MySQL优化器的结果。不要与混淆，profiling是真正执行查询后给出的查询分析数据。

EXPLAIN在评估了数千种可以执行的查询计划后，打印出它认为的最佳计划。还有一个关于其他计划选择的简要介绍，但一般来说，你需要从中查看这些信息。

我建议始终使用，因为它提供查询成本（默认格式不提供）。成本（cost）很重要，因为它使我们从数量上思考和说话。我是说我听过数据库从业者说“创建临时表（temporary tables）不好”或“join不好”。很难直接回应这些评论，因为它们缺乏重要的背景。过量饮水对你也有害，不谈数据量的优化是个伪命题，只有几十行的表不需要优化。

除了在sql语句前加上之外，你还可以执行，这就可以对正在运行的连接查看它是如何对查询进行优化的。这对于诊断瞬时错误很有用，因为数据（和基础统计信息）的更改会影响计划选择。

示例1：Explain显示表扫描操作

示例1中显示查询将作为表扫描（access_type ALL）执行，成本为个成本单位。没有列出可用的索引，因为缺少输出部分（它通常出现在下面）。

计数等于表中的行数（239），这大致说明了世界上有多少个国家。需要指出的是，这些只是估计值，不需要100%准确，因为这样做可能会影响性能。

意味着在读取行时将应用过滤器：。要是有索引的话，中的附加条件就可能在读取行之前生效，过滤不需要的行。但遗憾的是，这里列出意味着情况并非如此。

我喜欢将与执行查询后发送给客户端的行数进行比较。

发送给客户端的行数也可在表中找到。

只有真正执行了查询才能获取到行数，我执行后看到有32行。这意味着该查询检查的行数比发送的行数多7.5倍（239/32约等于7.5），这表明这个查询语句应该可以优化。

一个非常小的比率（即使是1:1）并不表示优化是不可能的。非要优化的话，仍是有机会的，例如分区或更改页面大小。

提醒一下，总共239行数据不算多，所有行都可以加载到内存里。但是表扫描只在内存中很快，随着表越来越大，性能可能会断崖式下降。添加索引有助于提高性能，并随着数据的增长保持一致。

仅显示查询的预期执行计划，而没有显示为什么没有选择其他可选的执行策略。理解为什么没有选择可选方案可能会令人困惑：

• 是因为其他方案不合适吗(例如，一些优化只能应用于特定的用例)?
• 是因为人们认为其他方案成本更高吗?
• 如果另一种选择是更高的成本，是多少?

为这些问题提供了答案。它的设计目的是提供更多关于优化器的诊断数据，但它对于实际故障排除以及了解优化器成本模型的工作方式非常有用。

示例2:EXPLAIN显示未使用新添加的索引

在示例2中，我们可以看到索引在被添加到表后并没有被选中。显示它是一个候选，但它没有描述为什么它没有被选中。要找到原因，我们需要求助于。

示例3:OPTIMIZER_TRACE显示了为什么没有使用索引

在示例3中，列出了可选的索引，但是由于成本太高被淘汰了。输出甚至提供了使用该索引的估计成本：130.61个成本单位。相比之下，表扫描的成本单位为55.9。成本越低越好，因此首选表扫描。

要说明为什么会这样，我们首先需要了解索引的原理。在这个数据集中，表中大多数行的都大于5000000，优化器判断直接行扫描比用索引搜索行更快。因为不是，所以使用这种的原理是查询到键，然后回表取数据，然后再回到索引查找下一个键，再回表取数据，如此反复，所以使用索引反而导致成本更高。当然高级用户可以配置如何做出这些决策的成本。

MySQL优化器可能会以不影响结果的方式对你的查询进行更改。这些转换背后的目标是尝试以一种可以消除多余的工作，并且能更快执行查询来重写查询语句。例如，考虑以下查询：

因为1总是等于1，而且这个条件是（而不是），所以它是完全多余的。在执行查询时检查每一行的1是否仍然等于1并没有任何好处，并且删除条件仍然会返回相同的结果。我们可以看到MySQL在中应用了这种转换，以及许多其他转换：

在执行了之后，在应用了所有转换后的重写的查询也可以在中使用。上述语句的改写形式如下：

下面是一些经过逻辑转换的查询示例。请注意，在几个示例中和索引在查询执行阶段之前被永久转换。由于这部分查询应该已经是最有效的形式，优化器在考虑执行计划之前会执行永久转换。这是另一个正在应用的优化，以减少需要考虑的计划数量。

code是主键，将值转换成常量重写查询。

因为主键里保存了所有字段值，所以不需要回表查询，直接从主键里面提取出来即可。

code是一个主键，但是这个主键在表中不存在(where条件无效)。

由于没有找到这个主键，所以所有字段值返回NULL。

另一种无法成立的where条件，虽然code值存在，但1=0是一个不可能的条件

虽然有这个主键，能找到其他字段的值，但是where为0所以不会返回什么。

对Country表的子查询派生表被合并成直接join Country表

重写形式：此行为取决于 (默认情况下)

视图的定义与用于查询它的查询条件合并。

将view里自带的where条件和查询这个view的语句中的where条件合并。

优化器决定如何执行查询的方式是基于一种称为基于成本的优化的方法。这一过程的简化如下:

1. 为每个操作分配成本。
2. 评估每个可能的计划将采取多少行动。
3. 把总数加起来。
4. 选择总成本最低的计划。

为什么我说上面是一个简化，是因为优化器不会穷尽搜索每个可能的执行计划。如果有5个表要join，每个表有5个可能的索引，那么可能超过种查询的执行方式:

• 每个索引可能有一个以上的潜在访问方法。(例如，索引扫描，范围扫描，或索引查找)。此外，每个表都可能使用表扫描。
• 对于一个查询，可以以任何顺序连接表(指定表的顺序无关紧要)。
• 当join时，可能有多个join缓存方法或子查询策略可用。

优化器评估每个潜在的执行计划是不可行的。例如，考虑这样一种情况：优化花费的时间比执行的时间长。为此，优化器将在默认情况下跳过对某些计划的评估。

默认。要禁用此启发式并评估所有计划，请将其设置为零。http://dev.mysql.com/doc/refman/5.7/en/controlling-query-plan-evaluation.html

也存在配置选项来限制查询计划的搜索深度，但默认情况下没有启用该选项。

默认。较低的值可能会减少计划评估时间，因为可能会降低最优计划的成本。

每个操作的成本（cost）可以通过库中的和表进行配置。下面是MySQL 8.0中使用的默认值:

MySQL 8.0引入了一个新特性，成本模型可以根据索引在内存中的百分比来调整。在以前版本的MySQL中，成本模型总是假设访问一个页面需要IO。

成本本身是一个代表资源使用情况的逻辑单位。一个单位不再有确切的含义，但它的起源可以追溯到20世纪90年代硬盘驱动器上的一个随机IO。

随着硬件的改进，它可能不会以一致的速率对所有组件进行处理（例如，SSD的存储延迟已大大改善）。类似地，当软件处理硬件的变化（具有压缩等特性）时，资源消耗也会发生变化。拥有可配置的成本常数可以细化处理这些情况。

示例4显示，将更改为原来的5倍会导致表扫描的成本大大增加（与通过索引消除工作相比）。这将导致优化器选择使用在示例2中创建的索引。

示例4:增加行评估的成本会使表扫描的成本更大

修改成本常量时要小心，因为许多查询计划可能会变得更糟！这里显示的是演示目的。在大多数生产环境中，添加查询提示（hint）会更好。

在继续讨论其他例子之前，要记得重置成本（cost）：

如示例3所示，数据的分布会影响执行计划的成本。优化器利用数据字典和统计信息作为其决策过程的一部分。

元数据

统计信息

由于统计信息的原因，QA和产品中看似相同的查询执行起来可能非常不同。即使是生产环境中的查询计划也会随着时间的推移而改变，就像数据分发一样。

[1] 统计信息在常规操作期间更新，但不能保证准确。

[2] 可以使用更改采样的页面数。较高的值可能会产生更准确的估计（在一定程度上增加成本产生）。

[3] 当列表超过项（默认值：200）时，优化器将切换到基数估计。

[4] 范围优化器限制为（默认值：8M）。使用多个列表的查询可能会超出此范围，因为选项组合可能会在内部扩展。

除了在mysql系统数据库中调整成本常量外，mysql还提供了一种方法来指定如何选择计划。使用查询提示有两个不同的原因:

1. 为了调试。显示了基于可用元数据做出的决策，添加提示可以强制执行一些其他的执行计划，并比较实际的执行时间。
2. 为了生产环境的性能。如果在调试时发现另一种执行计划更快，那么可以选择添加提示到生产环境来加速执行查询。

我想说的是，虽然我在调试中经常使用提示，但在生产中引入提示时，我总是很小心。这可能会造成维护负担，因为随着数据分布和索引的变化，提示可能仅仅是在某个时间点选择的特定计划中，添加的提示可能让查询变得更慢。

最佳实践建议，在MySQL主版本升级（例如，升级到MySQL 9.0）之后，应该重新查看提示有什么变化。你可能会发现，随着引入新的优化，你可以取消一些提示。

MySQL的早期版本仅支持一些直接扩展SQL语法的提示。例如:

虽然这些提示在MySQL 8.0中继续被支持，但它们被部分替换为注释风格的提示。在示例5中我们可以看到，由于使用了提示，示例2中没被选中的索引被选中了。尽管有, 仍然显示其真实成本为152.21，而表扫描成本约为53。

示例5:不管成本如何，强制使用索引

新Comment-Style提示

MySQL 8.0扩展了MySQL 5.7中引入的注释风格提示的新风格，具有控制表连接顺序的能力（类似于）。比起旧的SQL语法扩展提示，我更喜欢注释风格，原因有三:

1. 通过将SQL的声明性本质与暗示如何执行的内容分开，它们易于读写。
2. 它们具有明确的语义提示而不是指令。这意味着，如果提示不能操作，它将导致语句警告，而不是错误。这与等提示形成对比，其中缺少索引将导致错误。例如，这种行为类似于Oracle数据库中的提示。
3. 它们的控制粒度更细。这赋予了DBA在使用提示时更多的灵活性。

在示例6中，禁用了特定表的范围优化。这导致索引被忽略，尽管它的选择性（selective）非常高。在这个表中，只有两行数据。

示例6:禁用范围优化意味着索引不能被使用

简单回顾一下：优化器的作用是从成千上万的选择中选择最佳的执行计划。在这些选择中，可能有几个不同的索引而且每个索引有不同的访问方法。到目前为止，我们已经演示了一个关于的索引和两个不同的执行计划:

1. 范围扫描
2. 表扫描

由于索引被证明选择性不高，我现在将在上添加一个索引。在典型的生产环境中，你现在可能希望删除索引，因为它没有提供价值。但我将把它留在这里，因为我希望演示优化器可以成功地评估许多选择。

示例7:在continent上添加索引

在示例7中，我们可以看到索引优于索引和表扫描。它以索引的形式执行其工作，即消除工作。优化器估计在使用索引之后，只需要检查51行（rows_examined_per_scan）。另一种解释是，亚洲的国家比较少，因此该索引的选择性高。

在示例8中，我们可以看到，通过稍微修改查询，where条件使用而不是过滤数据，索引选择变成了。原因就是世界上只有两个国家的人口达到5亿，因此该索引变得更具选择性。

示例8:在人口众多的情况下，p的范围扫描优于c

随着索引增加，我们现在至少有四个可能的执行计划:

1. 范围（range）扫描，使用
2. 表扫描
3. ref查询，使用
4. 范围（range）扫描，使用

除了这些计划，我们还可以使用索引合并，结合使用索引和，在示例9的输出中，我们可以看到对索引的范围扫描也被评估但被拒绝了。

我已经描述了来显示索引的选择性，但是还有另外两个统计信息要指出示例7和示例8之间的区别:

1. key_length。用于的数据类型是1字节（enum），而用于的数据类型是4字节。如果选择性相等，则更短的键长度更好，因为你可以在每个页面中容纳更多的键，从而让索引更好的在内存中安置。
2. access_type。在所有条件相同的情况下，ref的访问类型比range的成本要小。

示例9:优化器跟踪将索引p和c与更多的population进行比较

由于不是所有的国家都在，这两个查询条件的结合应该能提高指标消除工作的能力。也就是说，对于这个数据集，复合索引将提高选择性。

复合索引有两种可能的选择:

1. 索引
2. 索引

复合索引排序的差异非常重要。事实上，因为是一个范围，所以优化器只能让使用索引的第一部分。因此，在这个查询中，对于并没有什么改进。在强制使用索引之后，我们可以清楚地看到这一点。

对于，如果是的条件是，IN里面是固定值的范围搜索，它能够在使用完整索引。

示例10:复合索引(population, continent)没有改善查询

这个限制是由于B+树的索引结构导致的。一种简单的理解方式是复合索引中的“范围扫描的列放到复合索引的右边”。考虑到这一点，在示例11中演示了索引。这两列组合建立的复合索引比仅在上建立的索引选择性更高，因此成本从28.20（示例7）下降到24.83。由于这两个列在索引中被有效地连接起来，所以访问方法被认为是一个范围。

示例11:效果更好的复合索引(Continent, Population)

确定复合索引中列的正确顺序是一件棘手的事情。以下是一些需要注意的事项:

1. 最左规则。一个索引还可以用于需要索引的查询，但不能用在需要索引的查询。尝试以这样一种方式设计复合索引，使它们能够被尽可能多的查询复用。
2. 范围在右。一个索引不能用于满足查询。或者更具体地说：复合索引的其余部分将不会在第一个范围条件之后使用。更通俗的说：a如果是个范围查询，那么b就无效了。
3. 高选择性的列在左边。想想如何尽快消除不需要的行。这通常还可以降低内存的占用，因为需要访问的索引页更少。
4. 更改索引顺序时要小心。混合或可能会影响可以使用多少复合索引。

第2条中同前面说明的，这种IN里面是固定值的查询可以用到复合索引的全部列。

覆盖索引是一种特殊类型的复合索引，其中所有列都存在于索引中。在这种情况下，MySQL能够应用一种优化，它只从索引中返回数据而不访问回表获取行。

研究一个案例，我们使用条件，而且只想查询表的列，所以不要使用。如示例12所示，复合索引可以使用前两列过滤行，并从第三列返回值。

示例12：覆盖索引c_p_n

输出表示使用了覆盖索引。覆盖索引是一个被低估的优化，许多从业者错误地认为覆盖索引只会“成本减半”，因为使用了索引，但没有触及表行。我们可以看到，示例12与示例11中的非覆盖索引相比，成本缩减近。

在生产环境中，由于索引的聚集效应，用覆盖索引做查询可能比用其他的做查询具有更好的内存利用。也就是说，如果要访问的二级索引与聚集索引（主键）不相关[1]，则可能需要访问更多数量的聚集键页。

[1]：通过前面的内容，我的意思是“大致遵循相同的顺序”。例如，为列创建二级索引与主键高度相关，而为和创建的二级索引则不太可能与主键相关，主键是三个字母的国家代码。

MySQL Workbench包含Visual Explain，它可以帮助你更容易理解复杂的执行计划。这个特性由内部提供支持的，因此需要注意的是，Visual EXPLAIN在常规之上不会有任何额外的输出。事实上，为了简单起见，它省略了一些输出，比如覆盖索引的使用。

Visual Explain访问方法用颜色区分：

• 是绿色
• 是橙色
• (表扫描)和(索引扫描)为红色

正如我们在目前的示例中所看到的，这可能是一个小的简化，因为高选择性范围比低选择性ref访问更可取。

有时类似的查询可能有非常不同的执行计划。到目前为止，我们已经通过修改总体查询条件看到了这一点：在示例8中，通过分别指定和搜索条件，最后两者使用了不同的索引。

这在生产环境中非常常见，其中查询的部分可能由用户输入生成:

• 在包含数月数据的数据集上指定从昨天到现在的日期范围可能会使用日期列上的索引，指定从去年到现在可能不会。需要查询的行更多，可能导致使用表扫描。
• 查找记录可能会过滤掉一些记录，非常适合索引。同样，可能不会，因为全表数据都满足这个条件。

根据输入的查询的范围，以上两个示例也可用于分区（partitioning）章节说明。

这是预期的行为，并且是收集统计信息的结果（在前面的“元数据和统计信息”中提到过）。下表显示了索引的相对成本如何随着和的变化而变化。（1M=1000000, c：continent，p：population）

• 表扫描有一个相对固定的成本，因为它总是查看表中的每一行。
• 随着输入值能够更有效地过滤，使用或索引的成本会发生变化（Antarctica的国家更少，population更多的国家更少）。
• 复合索引在降低大多数查询的成本方面是相对有效的。
• 由于前面解释的原因，和索引的成本是相同的。由于左侧范围，索引将仅使用索引的部分。
• 在这个数据集中，Antarctica有5个国家，人口都为零。所以上的复合索引导致成本最低的查询，因为结果为零。

这些信息也可以用视图表示出来。注意，在continent的和访问具有固定的成本，并且当选择性不高时，上的复合索引与上的访问的成本相同。随着数量的增加，它变得更具有选择性。

如果复合索引不是很有效，你会看到一种情况，即它会更接近单个索引（population或continent）的成本。最后，由于所有超过3亿的国家都在，因此选择性并不比仅针对的索引好。

下面的视图显示了执行100次时的中值，强制使用索引并改变范围（从到）。执行时间取自，并转换为微秒(μs)。我怀疑这是因为数据集很小，而且因为很少的国家很少，所以在访问同一组不同的页面时，执行时间存在一些集群。但尽管如此，两者还是有一定的相关性。

这些可视化高度依赖于数据分布。在检查示例数据集时，你可能会注意到有一些不准确的地方。数据来自芬兰统计局（1990年前后），并没有统一更新。

优化器有许多可用于优化子查询的执行策略，包括将查询重写为连接（join）、半连接和物化。使用的策略取决于子查询的类型和位置。

标量子查询的子查询只返回一行，可以在执行期间进行优化和缓存。在示例13中，我们可以看到一个标量子查询的示例，它试图找到的。重要的是要确定优化器将此视为两个查询，成本分别为1.20和4862.60。

第二个查询缺少索引，因此正在执行表扫描。我可以看到这一点，因为在中提到的列没有索引。添加索引之后，查询得到了优化。

示例13:标量子查询

示例14:添加索引以改进标量子查询

示例15显示了一个子查询，其中返回的是主键，所以可以确定每一个结果都是唯一的。因此，可以安全地将该子查询转换为查询并返回相同的结果。通过在之后执行，我们可以看到情况确实如此:

这个子查询的效率比较高。我们可以看到它首先访问Country表（只使用了覆盖索引），然后对于匹配的每一行，使用索引在City表中查找一组行。

示例15:一个可转换的IN子查询

在示例15中，子查询被重写为，因为它返回了一组值都是唯一的数据。当子查询不是唯一的时候，MySQL优化器必须选择不同的策略。

在示例16中，子查询试图找到拥有至少一种官方语言（CountryCode）的国家。因为有些国家有不止一种官方语言，所以子查询的结果不是唯一的。

（示例17）的输出表明，优化器识别出查询不能直接重写为JOIN语句，而需要一个“半连接”。优化器有几种策略来执行半连接(, , )。在本例中，它认为materializing（创建缓冲区来存储临时结果）是执行该查询的成本最低的方法。

示例16:不能重写到INNER JOIN的子查询

示例17:分析子查询的半连接策略

子查询既可以使用策略，也可以使用已存在的策略。为了描述两者之间的区别，考虑以下两个例子:

1. SELECT * FROM City WHERE CountryCode NOT IN (SELECT code FROM Country);
2. SELECT * FROM City WHERE CountryCode NOT IN (SELECT code FROM Country WHERE continent IN (‘Asia’, ‘Europe’, ‘North America’));

在第一个查询中，内部子查询或多或少处于其最佳形式。列是表的主键，可以通过索引扫描检索一组唯一的值。唯一的缺点（如果有的话）是索引扫描主键可能比索引扫描二级键慢，因为主键距离行值很远。确认优化器确实为这个查询选择了一个策略，但是这个策略可以被一个提示覆盖:

在第二个查询中，有一个额外的查询条件:。由于City表中的每一行都需要与中的查询进行比较，因此实现和缓存匹配的行是有意义的。这样就不必重复为City表中的每一行检查查询条件的过程。

示例18:使用物化的NOT IN子查询

from子句中的子查询不需要。MySQL通常可以将它“合并”回去，就像视图将它的定义与你将在查询中使用的查询条件合并一样。示例19显示了将派生查询合并到外部查询中的情况。

示例19:被“合并”回的派生表

这种“合并”的潜在缺点是，它使一些查询不再合法。如果升级时收到警告，可以选择禁用优化。这将导致查询成本的增加，因为可能很昂贵。

示例20:禁用合并和触发物化

VIEW是一种保存查询以供以后重用的方法，它们对应用程序来说就像一个表一样,这允许将复杂的SQL查询分解并分阶段简化。CTE本身与VIEW非常相似，不同之处在于它们的生命周期较短，只与单个语句绑定。

MySQL优化器有两种主要的策略来执行CTE和VIEW:

1. 合并（Merge）。转换查询，以便CTE或VIEW的定义与查询的其余部分合并。通过在运行执行可以看到合并的结果。
2. 物化（Materialize）。执行CTE或VIEW，并将结果存储在临时表中，查询的其余部分将对临时表执行。物化选项通常是较慢的方法，并且在合并选项不合适的时候被选择。也有例外，尽早物化可以缩减工作并导致更快的执行。

示例21:对合并视图的查询

示例22:视图上的查询不能合并

MySQL使用嵌套循环连接算法执行连接。它不支持其他一些数据库中可能提供的哈希或排序合并连接（sort merge joins），这使得它不太适合某些分析/数据仓库风格的查询。不过，MySQL优化器确实有缓冲策略，可以减少嵌套循环连接的最坏情况。

示例23显示了、和表之间的3表JOIN。执行该查询的完整步骤如下:

1. 优化器必须首先决定一个驱动表(Country)，以及用以连接其余表的索引(City, CountryLanguage)。
2. 执行从遍历第一个表开始(Country)一次一行。对于匹配过滤条件的每一行(Country.Continent=’Asia’)，则会查询下一个表(City)。
3. 对于City表中匹配的每一行，将在最终的表(CountryLanguage)中进行查找。CountryLanguage表将应用一个额外的过滤器：检查。

当工作可以在连接开始之前消除时，嵌套循环算法工作得最好。这意味着“最好的情况”是驱动表有许多高度选择性的查询条件。

最坏的情况是，查询条件分散在许多表中，在需要连接所有表之前，索引不能消除足够的工作。在这种情况下，对数据库进行反规范化设计是很常见的。

反规范化很有帮助，因为通过在驱动表上冗余地维护一组列，可以添加复合索引，并且可以在必须访问行或连接到其他表之前应用过滤。

示例23:在三个表上的嵌套循环连接

INNER JOIN在语义上要求在连接的左边和右边都存在一行。有了这种语义，就意味着MySQL能够以任意一种顺序连接两个表。选择成本最低的顺序是优化器的责任。

在语义上意味着连接右侧的一行是可选的（因此，也意味着左侧是可选的）。因为连接的一端是可选的，所以执行被强制从首先存在的一端开始访问。因为强制了顺序，所以优化器不能考虑所有可以应用于的潜在连接顺序（执行计划）。因此，这导致了简化，较慢。

从MySQL 5.7开始，优化器现在考虑除了驱动表之外的条件的过滤效果。这个用于细化后续表的连接顺序。

条件过滤非常适合于直方图（histograms），因为如果没有它，它将使用一种更简单的启发式，这种启发式在数据倾斜或不平衡时可能不准确。它还将利用索引提供的统计信息（索引的成本更高）。

在MySQL 5.7中，大量的查询回归是由于条件扇出过滤器和倾斜的数据集。在MySQL 8.0中，可以考虑将直方图添加到用于连接条件中使用的表的列中。

操作需要按照排序顺序读取行，或者需要一个临时表来缓冲中间结果以进行聚合。也就是说，MySQL可以通过以下方式为使用索引:

1. 宽松的索引扫描。如果条件被索引，MySQL可能会选择从头到尾扫描索引，避免出现中间结果。如果没有非常有选择性的查询条件，并且要创建的临时表非常大，那么这种操作是首选的。

   

2. 过滤行。索引可用于标识行，然后将这些行存储在临时表中。然后将结果汇总到临时表中，默认情况下按条件排序。

   

   此行为已弃用，将来可能会删除。如果不需要排序，则建议显式排序GROUP BY x ORDER BY NULL。

3. 过滤和排序的组合。此优化适用于过滤行的索引已经具有操作的正确顺序的数据。

   

示例24:使用松散索引扫描的GROUP BY

示例25：使用索引然后排序的GROUP BY

示例26：使用索引进行过滤和排序的GROUP BY

MySQL没有对应用任何特殊优化，它在语义上将两个查询的结果组合在一起并删除重复项。如示例27所示，重复数据删除是在中间临时表中执行的。临时表用于所有查询，因此没有分配成本（或可能基于成本的优化）。

简单的UNION的例子:

假想的优化:

与子查询和视图不同（对同一个表的多次访问可以在内部合并为一次访问），MySQL不会对查询执行类似的优化。它也不会发现任何不可能重复的情况，并且可以将重写为。这留下了许多情况，熟练的操作员可以对查询进行手动修改（在应用程序中或通过查询重写）并提高性能。

示例27:一个需要临时表的联合查询

U在语义上与相似，但有一个重要的区别：不需要删除重复数据。这意味着在某些情况下，MySQL能够直接传递查询的结果，而不必在中间表中物化和去重复行。

在内部，总是为查询创建一个临时表，但是是否需要使用它来物化行可以在中看到。示例28显示了一个使用的查询示例。在添加之后，查询现在需要使用中间临时表。

示例28:没有临时表的UNION ALL

示例29：由于ORDER BY的原因，需要一个临时表的UNION ALL

MySQL有四种方法以排序的顺序返回结果，这可能需要作为或的一部分（没有）。将显示数据是否需要排序操作，但它不会列出使用的排序算法，该信息可以在中找到。返回排序结果的四种方法如下:

1. 通过一个索引。B+树索引是按排序顺序维护的，因此一些查询根本不需要排序。

2. 通过优先队列。带有少量的可以将完整的结果集存储在一个临时缓冲区中。例如，考虑以下查询:

   此查询将进行表扫描，并保留具有10个最高人口（population）的行的缓冲区。由于较新的行被识别为具有较高的人口（population），较早的行可以从优先级队列推掉。

3. 通过备选排序算法。如果没有TEXT或BLOB列，则使用此算法。它在MySQL手册中定义为:
   1. 读取WHERE子句匹配的行。
   2. 对于每一行，记录由排序键值和查询引用的附加字段组成的值元组。
   3. 当排序缓冲区满时，按内存中的排序键值对元组进行排序，并将其写入一个临时文件。
   4. 在对临时文件进行合并排序之后，按排序顺序检索行，直接从排序的元组中读取所需的列。
4. 通过原始的排序算法。当出现TEXT或BLOB列时，将使用此算法。它在MySQL手册中定义为:
   1. 根据键或通过表扫描读取所有行。跳过不匹配WHERE子句的行。
   2. 对于每一行，在排序缓冲区中存储一个由一对值（排序键值和行ID）组成的元组。
   3. 如果每一对元组都适合排序缓冲区，则不会创建临时文件。否则，当排序缓冲区满时，在内存中对其运行qsort(快速排序)，并将其写入一个临时文件。保存一个指向排序块的指针。
   4. 重复上述步骤，直到读取所有行。
   5. 将多个区域合并到另一个临时文件中的一个块中。重复操作，直到第一个文件中的所有块都在第二个文件中。
   6. 重复以下操作，直到剩下的块少于MERGEBUFF2(15)。
   7. 在最后一次多合并中，只有行ID（元组的最后一部分）被写入结果文件。
   8. 使用结果文件中的行ID按排序顺序读取行。要优化这一点，可以读取大块的行ID，对它们进行排序，然后使用它们按顺序将行读取到行缓冲区中。行缓冲区大小为系统变量的值。该步骤的代码在源文件。

示例30：由索引提供的排序

示例31:OPTIMIZER_TRACE显示优先级队列的使用

优化器能够操作分区划分。这意味着它能够分析传入的查询，将其与字典信息进行比较，然后只访问所需的表分区。

你可以将分区视为类似于视图，因为它是一个表的逻辑表示，下面是一组表。在所有查询都遵循一个通用模式的情况下，分区比索引更适合。例如:

• 软删除。许多应用程序实现了软删除（例如:is_deleted），而通常只访问已删除或未删除的数据。
• 多版本的模式[1]。一些应用程序具有永不删除数据的策略，为了历史目的保留较老的几代行。例如，你更新了一个用户的地址，但保留了所有以前的地址。根据地址是否过期进行分区可以帮助提高内存。
• 时间取向。例如，按季度或财务周期划分发票。
• 位置。例如，按可以或分区。

[1］在数据仓库中，这被称为缓慢变化的维度https://en.wikipedia.org/wiki/Slowly_changing_dimension

当大多数查询一次只需要访问分区的一个或一个小子集时，分区性能最好。如果考虑我们的示例模式，可能会出现这样的情况：对表的所有查询都只针对官方支持的语言。如果为，则按进行分区，如下所示:

示例32:一个分区表被优化器修剪

除了分区划分之外，MySQL还支持仅针对选定数量的分区的语法。这可以由应用程序以类似的方式用于查询提示：;

MySQL支持在服务器端重写语句的能力。你可以将其视为类似于服务器端正则表达式，以便能够找到匹配特定模式的语句，并将它们重写为新模式。

该特性的设计目标之一是允许dba向语句中插入查询提示。在应用程序本身不能被修改的情况下（比如使用ORM的情况），或者应用程序是专有的情况下，这就提供了缓解。

应该注意的是，虽然我将查询重写描述为类似于正则表达式，但它的内部效率要高得多。在解析语句时，要根据应该重写的语句的内部哈希表来检查摘要（也称为预处理语句形式）。如果确定某个语句需要重写，服务器将执行此步骤，然后重新解析查询。这意味着不需要重写的查询成本很小，而需要重写的查询只需要解析两次。

示例33:使用查询重写更改查询服务器端

MySQL服务器支持查询重写插件。这里展示的例子是一个解析后查询重写插件（称为rewrite），它包含在MySQL服务器分发版中。某些查询可能需要预解析插件，这也是支持的。更多信息请参见MySQL手册：https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite-plugin.html

隐形索引是MySQL 8.0中的一个新特性，它提供了将索引标记为不可用的能力。这意味着在修改数据时，索引仍将被维护并保持最新，但是不允许任何查询使用该索引（即使查询使用）。

隐形索引不应该与禁用索引混淆，禁用索引是MyISAM存储引擎实现的（禁用索引会停止索引的维护）。对于隐形索引，有两个值得注意的用例:

1. 软删除。无论何时在生产中执行破坏性操作，都希望能够在更改永久之前进行观察。把它想象成一个索引“回收站”如果你弄错了，并且正在使用索引，那么只需修改元数据就可以使索引再次可见，这比从备份中重新创建或恢复索引要快得多。例如:

2. 分阶段上线。每当添加索引时，一定要考虑到它们可能会改变你现有的查询计划，有时是以不希望看到的方式。隐形索引提供了一个机会，可以在合适的时间上线索引，可能会远离峰值负载，并且在你能够积极观察系统的时候推出索引。例如:

删除不需要的索引是个好主意。虽然大多数实践者都知道索引会损害修改(插入、更新)的性能，但这本身就是一种简化。它们还会损害读取的性能，因为优化器需要对它们进行评估以选择计划。

只显示预执行查询成本的分析，它不提供关于查询真正执行的任何统计信息，而可以显示更完整的分析。例如，当优化器不能从索引中删除行（并在中向表中添加一个）时，它不知道将删除多少行。这可能会在中产生涓滴效应，因为对后续表的查找可能非常多，也可能很少。

通过支持对每个执行阶段花费的时间进行初步分析，它取代了之前的命令（该命令在MySQL中已经被弃用）。例如，我使用基于数据库的替换，可以如下安装:

此文件以无法下载，但依然可以使用performance_schema分析，更多章节见MySQL官方文档。

示例34：使用性能模式分析查询

可以使用函数来演示在执行的特定阶段花费的大量时间。在这个查询中，MySQL将在每一行匹配时休眠5秒:

您可能会发现分析输出并不总是像它可能的那样分阶段细化。例如，状态只是意味着在存储引擎和服务器之间传输行。重要的是，临时表和排序的执行时间被分解了：

除了这些帮助程序公开的分析信息之外，还具有关于需要排序的实际行数以及发送的行数的附加统计信息。此执行级别分析补充了中看到的执行前分析：

MySQL服务器支持无模式数据存储，并具有以下特性:

1. JSON数据类型。JSON值在插入/更新、验证时进行解析，然后以二进制优化格式存储。在读取值时，JSON数据类型不需要任何解析或验证。
2. JSON函数。一组20多个SQL函数，用于搜索、操作和创建JSON值。
3. 生成的列。虽然不是特别绑定到JSON，生成的列工作类似函数索引并允许提取JSON文档的一部分并建立索引。

当查询JSON数据[1]时，优化器将自动从生成的列中查找匹配的索引。在示例35中，用户首选项存储在JSON列中。最初，对请求更新通知()的用户进行查询会导致表扫描操作。通过添加带有索引的虚拟生成列，表明现在可以使用索引。

［1]: 简写就是一个例子操作符（->）。提取字符串时，应该使用简写提取和反引号操作符（-?）。

示例35：用户偏好的无模式表示

示例36：添加带有索引的虚拟生成列

MySQL 8.0支持最新的Unicode 9.0版本，其名称为。是可变长度的，每个字符占用1到4个字节。在字节和字符之间有许多命名，它们是可变长度的:

1. 将列创建为VARCHAR(n)时，n表示字符长度。字节存储要求可能高达4倍（但通常更少）。
2. InnoDB存储引擎在内部始终[1]将存储为、和数据类型的可变长度（在索引和表行中）。
3. 在内存中用作物化的一部分临时表是固定长度的。这可能会导致临时表更大，或者在使用字符集时更早溢出到磁盘的情况。
4. 用于对数据进行排序的缓冲区是可变长度的（从 MySQL 5.7 开始）。
5. 将始终显示可变长度索引的最大长度（字节长度）。通常存储要求要低得多。

［1]：在使用、和行格式时。使用早期的行格式通常没有实际用途。

示例37显示了报告使用拉丁字符集类型的列上的索引。在将表转换为之后，表的存储需求没有增加，但是现在显示增加了。

示例37：EXPLAIN显示索引的最大键长度（拉丁字符集）

示例38：EXPLAIN显示索引的最大键长度（utf8mb4字符集）

• 上一篇: 最贵54万一张！NBA总决赛票价创历史新高：库里人气高 科比门徒吸粉
• 下一篇: 带飞全场[电竞]