第八章：查询性能优化
在前几章中，我们解释了模式优化和索引，这对于高性能是必要的。但这还不够——您还需要设计良好的查询。如果您的查询不好，即使是设计最佳的模式和索引也不会表现良好。
查询优化、索引优化和模式优化是相辅相成的。随着在 MySQL 中编写查询的经验增加，您将学会如何设计表和索引以支持高效的查询。同样，您所学到的关于最佳模式设计将影响您编写的查询类型。这个过程需要时间，因此我们鼓励您在学习过程中参考这三章。
本章从一般查询设计考虑开始：当查询性能不佳时，您应该首先考虑的事项。然后我们深入研究查询优化和服务器内部。我们将向您展示如何找出 MySQL 如何执行特定查询，并学习如何更改查询执行计划。最后，我们将看看 MySQL 无法很好地优化查询的一些地方，并探索有助于 MySQL 更有效地执行查询的查询优化模式。
我们的目标是帮助您深入了解 MySQL 如何真正执行查询，以便您可以思考什么是高效或低效的，利用 MySQL 的优势，避免其弱点。
查询为什么慢？
在尝试编写快速查询之前，请记住这一切都关乎响应时间。查询是任务，但它们由子任务组成，这些子任务消耗时间。要优化查询，必须通过消除它们、减少发生次数或加快发生速度来优化其子任务。
一般来说，您可以通过在脑海中跟随查询的序列图，从客户端到服务器，解析、规划和执行，然后再返回客户端，来思考查询的生命周期。执行是查询生命周期中最重要的阶段之一。它涉及大量调用存储引擎以检索行，以及后检索操作，如分组和排序。
在完成所有这些任务的同时，查询在网络、CPU 和诸如统计、规划、锁定（互斥等待）以及尤其是调用存储引擎检索行等操作上花费时间。这些调用在内存操作、CPU 操作以及特别是 I/O 操作中消耗时间，如果数据不在内存中的话。根据存储引擎的不同，可能还涉及大量的上下文切换和/或系统调用。
在每种情况下，由于操作是不必要地执行、执行次数过多或速度太慢，可能会消耗过多时间。优化的目标是通过消除或减少操作或使其更快来避免这种情况。
再次强调，这并不是查询生命周期的完整或准确图景。我们在这里的目标是展示理解查询生命周期的重要性，并从时间消耗的角度思考。有了这个理念，让我们看看如何优化查询。
慢查询基础知识：优化数据访问
查询性能不佳的最基本原因是因为它处理了太多数据。有些查询必须筛选大量数据，这是无法避免的。不过，这种情况并不常见；大多数糟糕的查询可以更改以访问更少的数据。我们发现分析性能不佳的查询有两个步骤是有用的：
- 查找应用程序是否检索了比所需更多的数据。通常这意味着它访问了太多行，但也可能访问了太多列。
- 查找MySQL 服务器是否分析了比所需更多的行。
您是否请求了不需要的数据？
有些查询请求了比所需更多的数据，然后丢弃了其中一些。这会给 MySQL 服务器增加额外的工作量，增加网络开销，并在应用程序服务器上消耗内存和 CPU 资源。
以下是一些典型的错误：
检索比所需更多的行
一个常见的错误是假设 MySQL 按需提供结果，而不是计算并返回完整的结果集。我们经常在由熟悉其他数据库系统的人设计的应用程序中看到这种情况。这些开发人员习惯于发出返回许多行的SELECT语句，然后获取前*N*行并关闭结果集（例如，在新闻网站上获取最近的 100 篇文章，而他们只需要在首页显示其中的 10 篇）。他们认为 MySQL 会提供这 10 行并停止执行查询，但 MySQL 实际上会生成完整的结果集。客户端库然后获取所有数据并丢弃大部分数据。最佳解决方案是在查询中添加LIMIT子句。
从多表连接中检索所有列
如果你想检索出出现在电影Academy Dinosaur中的所有演员，不要这样写查询：
SELECT * FROM sakila.actor INNER JOIN sakila.film_actor USING(actor_id) INNER JOIN sakila.film USING(film_id) WHERE sakila.film.title = 'Academy Dinosaur';
这会返回三个表中的所有列。相反，将查询写成如下形式：
SELECT sakila.actor.* FROM sakila.actor...;
检索所有列
当你看到SELECT *时，你应该持怀疑态度。你真的需要所有的列吗？可能不需要。检索所有列可能会阻止优化，如覆盖索引，并为服务器增加 I/O、内存和 CPU 开销。一些数据库管理员普遍不赞成SELECT *，因为这个事实以及为了减少当有人修改表的列列表时出现问题的风险。
当然，并不总是坏事要求比实际需要的数据更多。在我们调查的许多情况下，人们告诉我们这种浪费的方法简化了开发，因为它允许开发人员在多个地方使用相同的代码片段。只要你知道这在性能方面的代价，��是一个合理的考虑。如果你的应用程序中使用某种类型的缓存，或者你有其他目的，检索比实际需要的数据更多可能也是有用的。检索和缓存完整对象可能比运行许多单独的查询检索对象的部分更可取。
重复检索相同的数据
如果不小心，很容易编写应用程序代码，从数据库服务器中重复检索相同的数据，执行相同的查询以获取它。例如，如果你想找出用户的个人资料图片 URL 以显示在评论列表旁边，你可能会为每条评论重复请求这个信息。或者你可以在第一次获取后缓存它并在以后重复使用。后一种方法更有效。
MySQL 是否检查了太多数据？
一旦确定你的查询只检索你需要的数据，你可以寻找生成结果时检查了太多数据的查询。在 MySQL 中，最简单的查询成本指标是：
- 响应时间
- 检查的行数
- 返回的行数
这些指标都不是衡量查询成本的完美方式，但它们大致反映了 MySQL 执行查询时必须内部访问多少数据，并大致转化为查询运行的速度。这三个指标都记录在慢查询日志中，因此查看慢查询日志是发现检查了太多数据的查询的最佳方法之一。
响应时间
警惕只看查询响应时间。嘿，这不是我们一直告诉你的相反吗？其实不然。响应时间仍然很重要，但有点复杂。
响应时间是服务时间和队列时间的总和。服务时间是服务器实际处理查询所需的时间。队列时间是响应时间中服务器实际上并未执行查询的部分——它在等待某些事情，比如等待 I/O 操作完成、等待行锁等。问题在于，除非你可以单独测量这些组件，否则你无法将响应时间分解为这些组件。通常，你会遇到的最常见和重要的等待是 I/O 和锁等待，但你不应该只依赖这两种，因为情况变化很大。I/O 和锁等待之所以重要，是因为它们对性能的影响最大。
因此，在不同负载条件下，响应时间并不一致。其他因素——如存储引擎锁（如行锁）、高并发和硬件——也会对响应时间产生相当大的影响。响应时间也可能是问题的症状和原因，而且并不总是明显哪种情况。
当你查看查询的响应时间时，你应该问自己查询的响应时间是否合理。我们在本书中没有详细解释的空间，但你实际上可以使用 Tapio Lahdenmaki 和 Mike Leach 的书关系数据库索引设计和优化器（Wiley）中解释的技术计算查询响应时间的快速上限估计（QUBE）。简而言之：检查查询执行计划和涉及的索引，确定可能需要多少个顺序和随机 I/O 操作，并将这些乘以硬件执行它们所需的时间。把它们加起来，你就有一个判断查询是否比可能或应当更慢的标准。
检查的行数和返回的行数
在分析查询时，考虑检查的行数是有用的，因为你可以看到查询是否高效地找到你需要的数据。然而，这并不是一个找到“坏”查询的完美指标。并非所有行访问都是相等的。较短的行访问速度更快，从内存中获取行比从磁盘中读取行要快得多。
理想情况下，检查的行数应该与返回的行数相同，但实际上这很少可能。例如，在构建连接行时，服务器必须访问多个行以生成结果集中的每一行。检查的行数与返回的行数的比率通常很小——比如，1:1 到 10:1 之间，但有时可能相差几个数量级。
扫描的行数和访问类型
当你考虑查询的成本时，考虑在表中找到单个行的成本。MySQL 可以使用多种访问方法来查找和返回行。有些需要检查许多行，但其他可能能够在不检查任何行的情况下生成结果。
访问方法出现在EXPLAIN输出的type列中。访问类型从完整表扫描到索引扫描、范围扫描、唯一索引查找和常量。每种访问类型都比前一种更快，因为它需要读取的数据更少。你不需要记住访问类型，但你应该理解扫描表、扫描索引、范围访问和单值访问的一般概念。
如果你没有得到一个好的访问类型，通常解决问题的最佳方法是添加一个适当的索引。我们在前一章讨论了索引；现在你可以看到为什么索引对查询优化如此重要。索引让 MySQL 能够以更有效的访问类型找到行，从而减少数据的检查。
例如，让我们看一个在 Sakila 示例数据库上的简单查询：
SELECT * FROM sakila.film_actor WHERE film_id = 1;
这个查询将返回 10 行，EXPLAIN显示 MySQL 使用idx_fk_film_id索引上的ref访问类型来执行查询：
mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: film_actor partitions: NULL type: ref possible_keys: idx_fk_film_id key: idx_fk_film_id key_len: 2 ref: const rows: 10 filtered: 100.00 Extra: NULL
EXPLAIN 显示 MySQL 估计只需要访问 10 行。换句话说，查询优化器知道所选的访问类型可以有效地满足查询。如果查询没有合适的索引会发生什么？如果我们删除索引并再次运行查询，MySQL 将不得不使用一个不太优化的访问类型，正如我们可以看到的：
mysql> ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film; mysql> ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id; mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: film_actor partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5462 filtered: 10.00 Extra: Using where 1 row in set, 1 warning (0.00 sec)
可预测的是，访问类型已经变为全表扫描（ALL），MySQL 现在估计它将需要检查 5,462 行来满足查询。Extra列中的“Using where”显示 MySQL 服务器正在使用WHERE子句在存储引擎读取行后丢弃行。
一般来说，MySQL 可以以三种方式应用WHERE子句，从最好到最差：
- 将条件应用于索引查找操作，以消除不匹配的行。这发生在存储引擎层。
- 使用覆盖索引（
Extra列中的“Using index”）避免行访问，并在从索引检索每个结果后过滤掉不匹配的行。这发生在服务器层，但不需要从表中读取行。 - 从表中检索行，然后过滤不匹配的行（“在
Extra列中使用 where”）。这发生在服务器层，需要服务器在过滤行之前从表中读取行。
这个例子说明了拥有良好索引是多么重要。良好的索引帮助你的查询获得良好的访问类型，并仅检查它们需要的行。然而，添加索引并不总是意味着 MySQL 将访问和返回相同数量的行。例如，这里有一个使用COUNT()²聚合函数的查询：
mysql> SELECT actor_id, COUNT(*) -> FROM sakila.film_actor GROUP BY actor_id; +----------+----------+ | actor_id | COUNT(*) | +----------+----------+ | 1 | 19 | | 2 | 25 | | 3 | 22 | .. omitted.. | 200 | 20 | +----------+----------+ 200 rows in set (0.01 sec)
此查询仅返回 200 行，但它需要读取多少行？我们可以通过EXPLAIN来检查，就像我们在上一章中讨论的那样：
mysql> EXPLAIN SELECT actor_id, COUNT(*) -> FROM sakila.film_actor GROUP BY actor_id\G *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: film_actor partitions: NULL type: index possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 5462 filtered: 100.00 Extra: Using index
糟糕！读取数千行只需要 200 行意味着我们做了比必要更多的工作。对于这样的查询，索引无法减少检查的行数，因为没有WHERE子句来消除行。
不幸的是，MySQL 不会告诉你它访问的行中有多少被用来构建结果集；它只告诉你它访问的总行数。这些行中的许多行可能会被WHERE子句消除，并最终不会对结果集做出贡献。在前面的例子中，删除sakila.film_actor上的索引后，查询访问了表中的每一行，而WHERE子句丢弃了除了其中的 10 行之外的所有行。只有剩下的 10 行被用来构建结果集。理解服务器访问了多少行以及它实际使用了多少行需要对查询进行推理。
如果发现为了生成相对较少的结果而检查了大量行，你可以尝试一些更复杂的修复方法：
- 使用覆盖索引，它存储数据，使得存储引擎不必检索完整的行。（我们在第七章中讨论过这些。）
- 更改模式。一个例子是使用摘要表（在第六章中讨论）。
- 重写一个复杂的查询，以便 MySQL 优化器能够最佳地执行它。（我们将在本章后面讨论这个问题。）
重构查询的方法
在优化有问题的查询时，你的目标应该是找到获取你想要的结果的替代方法，但这并不一定意味着从 MySQL 中获得相同的结果集。有时候，你可以将查询转换为返回相同结果且性能更好的等价形式。然而，你也应该考虑重写查询以检索不同的结果，如果这样做能提高效率的话。最终，通过改变应用程序代码以及查询，你可能能够完成相同的工作。在本节中，我们将解释一些技术，帮助你重构各种查询，并告诉你何时使用每种技术。
复杂查询与多个查询
一个重要的查询设计问题是是否更倾向于将复杂查询分解为几个简单查询。传统的数据库设计方法强调尽可能用尽可能少的查询来完成尽可能多的工作。这种方法在历史上更好，因为网络通信的成本和查询解析和优化阶段的开销。
然而，这个建议在 MySQL 上不太适用，因为它被设计为非常高效地处理连接和断开连接，并且对小型、简单的查询作出快速响应。现代网络也比以前快得多，减少了网络延迟。根据服务器版本，MySQL 可以在商品服务器硬件上每秒运行超过十万个简单查询，并且在千兆网络上从单个对应方每秒运行超过两千个 QPS，因此运行多个查询并不一定是一件坏事。
与 MySQL 每秒内部遍历的行数相比，连接响应仍然很慢，尽管对于内存数据，每秒可以计数为百万级。其他条件相同的情况下，尽可能使用较少的查询仍然是个好主意，但有时你可以通过分解查询并执行几个简单的查询而不是一个复杂的查询来使查询更有效。不要害怕这样做；权衡成本，选择导致工作量较少的策略。我们稍后在本章中展示了一些这种技术的例子。
使用过多查询是应用设计中的常见错误。例如，一些应用程序执行 10 个单行查询以从表中检索数据，而实际上它们可以使用一个包含 10 行的查询。我们甚至看到一些应用程序会逐个检索每一列，多次查询每一行！
分解查询
另一种分解查询的方法是分而治之，保持基本相同但在影响更少行的情况下以较小的“块”运行它。
清理旧数据是一个很好的例子。定期清理作业可能需要删除大量数据，如果在一个巨大的查询中执行此操作可能会锁定很多行很长时间，填满事务日志，占用资源，并阻塞不应被中断的小查询。分解DELETE语句并使用中等大小的查询可以显著提高性能，并在查询被复制时减少复制延迟。例如，与其运行这个庞大的查询：
DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH);
您可以做类似以下伪代码：
rows_affected = 0 do { rows_affected = do_query( "DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000") } while rows_affected > 0
每次删除 10,000 行通常是一个足够大的任务，使每个查询都有效，并且足够短的任务以最小化对服务器的影响³（事务存储引擎可能受益于更小的事务）。在DELETE语句之间添加一些休眠时间以分散负载并减少锁定时间也可能是个好主意。
连接分解
许多高性能应用程序使用连接分解。您可以通过运行多个单表查询而不是多表连接来分解连接，然后在应用程序中执行连接。例如，与其这样的单个查询：
SELECT * FROM tag JOIN tag_post ON tag_post.tag_id=tag.id JOIN post ON tag_post.post_id=post.id WHERE tag.tag='mysql';
您可能会运行这些查询：
SELECT * FROM tag WHERE tag='mysql'; SELECT * FROM tag_post WHERE tag_id=1234; SELECT * FROM post WHERE post.id in (123,456,567,9098,8904);
为什么要这样做？乍一看，这看起来是浪费的，因为您增加了查询的数量，却没有得到任何回报。然而，这种重组实际上可以带来显著的性能优势：
- 缓存可能更有效。许多应用程序会缓存直接映射到表的“对象”。在这个例子中，如果带有标签
mysql的对象已经被缓存，应用程序可以跳过第一个查询。如果在缓存中找到 ID 为 123、567 或 9098 的帖子，可以将它们从IN()列表中移除。 - 有时逐个执行查询可以减少锁争用。
- 在应用程序中进行连接使得通过将表放置在不同的服务器上更容易扩展数据库。
- 查询本身可能更有效率。在这个例子中，使用
IN()列表而不是连接让 MySQL 对行 ID 进行排序，并更优化地检索行，这可能比使用连接更有效。 - 您可以减少冗余的行访问。在应用程序中进行连接意味着您只检索每行一次，而在查询中进行连接本质上是一种反规范化，可能会重复访问相同的数据。出于同样的原因，这种重组也可能减少总网络流量和内存使用。
因此，在应用程序中进行连接时，如果您从先前的查询中缓存和重复使用大量数据，将数据分布在多个服务器上，将连接替换为IN()列表在大表上，或者连接多次引用同一表时，可能会更有效率。
查询执行基础知识
如果您需要从 MySQL 服务器获得高性能，最好的投资之一是学习 MySQL 如何优化和执行查询。一旦您理解了这一点，大部分查询优化都是根据原则推理，查询优化变得非常逻辑。
让我们重新审视我们之前讨论的内容：MySQL 执行查询的过程。图 8-1 展示了当您向 MySQL 发送查询时会发生什么：
- 客户端将 SQL 语句发送到服务器。
- 服务器将其解析、预处理并优化为查询执行计划。
- 查询执行引擎通过调用存储引擎 API 执行计划。
- 服务器将结果发送给客户端。
图 8-1. 查询的执行路径
每个步骤都有一些额外的复杂性，我们将在接下来的章节��讨论。我们还会解释在每个步骤中查询将处于哪些状态。查询优化过程特别复杂且重要。还有一些例外或特殊情况，比如在使用准备语句时执行路径的差异；我们将在下一章中讨论。
MySQL 客户端/服务器协议
尽管您不需要了解 MySQL 客户端/服务器协议的内部细节，但您需要在高层次上了解它是如何工作的。该协议是半双工的，这意味着在任何给定时间 MySQL 服务器可以发送或接收消息但不能同时进行两者。这也意味着没有办法截断消息。
这种协议使得 MySQL 通信简单快速，但也在某些方面限制了它。首先，这意味着没有流量控制；一旦一方发送消息，另一方必须在回复之前获取整个消息。这就像一个来回传球的游戏：任何时候只有一方拿着球，除非你拿到球，否则你无法传球（发送消息）。
客户端将查询作为单个数据包发送到服务器。这就是为什么如果您有大型查询，max_allowed_packet 配置变量很重要。⁴ 一旦客户端发送查询，它就不再控制局面；它只能等待结果。
相比之下，服务器的响应通常由许多数据包组成。当服务器响应时，客户端必须接收整个结果集。它不能简单地获取一些行然后要求服务器不再发送其余的行。如果客户端只需要返回的前几行，它要么等待所有服务器的数据包到达然后丢弃它不需要的部分，要么不正常地断开连接。这两种方式都不是好主意，这就是为什么适当使用LIMIT子句如此重要。
这里有另一种思考方式：当客户端从服务器获取行时，它认为自己是在拉它们。但事实是，MySQL 服务器在生成行时是在推行。客户端只接收被推送的行；它无法告诉服务器停止发送行。客户端就像在“从消防水龙头中喝水”，可以这么说。（是的，这是一个技术术语。）
大多数连接到 MySQL 的库都可以让您获取整个结果集并将其缓冲在内存中，或者在需要时获取每一行。默认行为通常是获取整个结果并将其缓冲在内存中。这很重要，因为在获取所有行之前，MySQL 服务器不会释放查询所需的锁和其他资源。查询将处于“发送数据”状态。当客户端库一次性获取所有结果时，它减少了服务器需要做的工作量：服务器可以尽快完成并清理查询。
大多数客户端库让您将结果集视为从服务器获取，尽管实际上您只是从库内存中的缓冲区获取。这在大多数情况下运行良好，但对于可能需要很长时间才能获取并使用大量内存的大型结果集，这不是一个好主意。如果指示库不缓冲结果，您可以使用更少的内存并更早开始处理结果。缺点是，当您的应用程序与库交互时，服务器上的锁和其他资源将保持打开状态。⁵
让我们看一个使用 PHP 的示例。这是您通常从 PHP 查询 MySQL 的方式：
<?php $link = mysql_connect('localhost', 'user', 'p4ssword'); $result = mysql_query('SELECT * FROM HUGE_TABLE', $link); while ( $row = mysql_fetch_array($result) ) { // Do something with result } ?>
代码似乎表明您只在需要时在while循环中获取行。然而，代码实际上通过mysql_query()函数调用将整个结果获取到缓冲区中。while循环只是遍历缓冲区。相比之下，以下代码不会缓冲结果，因为它使用mysql_unbuffered_query()而不是mysql_query()：
<?php $link = mysql_connect('localhost', 'user', 'p4ssword'); $result = mysql_unbuffered_query('SELECT * FROM HUGE_TABLE', $link); while ( $row = mysql_fetch_array($result) ) { // Do something with result } ?>
编程语言有不同的方法来覆盖缓冲。例如，Perl 的DBD::mysql驱动程序要求您指定 C 客户端库的mysql_use_result属性（默认为mysql_buffer_result）。这是一个示例：
#!/usr/bin/perl use DBI; my $dbh = DBI->connect('DBI:mysql:;host=localhost', 'user', 'p4ssword'); my $sth = $dbh->prepare('SELECT * FROM HUGE_TABLE', { mysql_use_result => 1 }); $sth->execute(); while ( my $row = $sth->fetchrow_array() ) { # Do something with result }
注意，调用prepare()指定“使用”结果而不是“缓冲”结果。您也可以在连接时指定这一点，这将使每个语句都是非缓冲的：
my $dbh = DBI->connect('DBI:mysql:;mysql_use_result=1', 'user', 'p4ssword');
查询状态
每个 MySQL 连接，或线程，都有一个状态，显示其在任何给定时间正在做什么。有几种查看这些状态的方法，但最简单的方法是使用SHOW FULL PROCESSLIST命令（状态显示在Command列中）。随着查询在其生命周期中的进展，其状态会多次更改，有数十种状态。MySQL 手册是所有状态信息的权威来源，但我们在这里列出了一些并解释了它们的含义：
休眠
线程正在等待来自客户端的新查询。
查询
线程正在执行查询或将结果发送回客户端。
锁定
线程正在等待服务器级别授予表锁。由存储引擎实现的锁，例如 InnoDB 的行锁，不会导致线程进入Locked状态。
分析和统计
线程正在检查存储引擎统计信息并优化查询。
复制到临时表[在磁盘上]
线程正在处理查询并将结果复制到临时表，可能是为了GROUP BY，进行文件排序，或满足UNION。如果状态以“on disk”结尾，MySQL 正在将内存表转换为磁盘表。
排序结果
线程正在对结果集进行排序。
至少了解基本状态是有帮助的，这样您就可以了解查询的“谁在掌握主动权”。在非��繁忙的服务器上，您可能会看到通常很短暂的状态，例如statistics，开始占用大量时间。这通常表示出现了问题。
查询优化过程
查询生命周期中的下一步将 SQL 查询转换为查询执行引擎的执行计划。这包括几个子步骤：解析、预处理和优化。错误（例如，语法错误）可能在过程的任何时候引发。我们并不打算在这里记录 MySQL 的内部情况，因此我们将采取一些自由，例如即使它们通常为了效率而完全或部分地合并，我们也会单独描述步骤。我们的目标只是帮助您了解 MySQL 如何执行查询，以便您可以编写更好的查询。
解析器和预处理器
首先，MySQL 的解析器将查询分解为标记，并从中构建“解析树”。解析器使用 MySQL 的 SQL 语法来解释和验证查询。例如，它确保查询中的标记有效且顺序正确，并检查是否存在未终止的引号字符串等错误。
预处理器然后检查解析树的结果，以解决解析器无法解决的附加语义。例如，它检查表和列是否存在，并解析名称和别名以确保列引用不会产生歧义。
接下来，预处理器检查权限。除非您的服务器具有大量权限，否则这通常非常快。
查询优化器
解析树现在有效并准备好供优化器将其转换为查询执行计划。一个查询通常可以以许多不同的方式执行并产生相同的结果。优化器的工作是找到最佳选项。
MySQL 使用基于成本的优化器，这意味着它试图预测各种执行计划的成本并选择最便宜的。成本单位最初是一个单个随机的 4 KB 数据页读取，但现在已变得更加复杂，现在包括诸如执行WHERE子句比较的估计成本等因素。您可以通过运行查询，然后检查Last_query_cost会话变量来查看优化器估计查询的成本有多昂贵：
mysql> SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor; +----------+ | count(*) | +----------+ | 5462 | +----------+ mysql> SHOW STATUS LIKE 'Last_query_cost'; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 1040.599000 | +-----------------+-------------+
这个结果意味着优化器估计执行查询需要大约 1040 个随机数据页读取。它基于统计数据：每个表或索引的页数，索引的基数（不同值的数量），行和键的长度，以及键的分布。优化器在其估计中不包括任何类型缓存的影响；它假设每次读取都会导致磁盘 I/O 操作。
优化器可能并不总是选择最佳计划，原因有很多：
- 统计数据可能不准确。服务器依赖存储引擎提供统计信息，它们的准确性可能从完全正确到极不准确。例如，InnoDB 存储引擎由于其 MVCC 架构不维护关于表中行数的准确统计信息。
- 成本度量标准并不完全等同于运行查询的真实成本，因此即使统计数据准确，查询的成本可能比 MySQL 的近似值更昂贵或更便宜。在某些情况下，读取更多页的计划实际上可能更便宜，例如当读取是顺序的时，磁盘 I/O 更快，或者当页已缓存在内存中时。MySQL 也不了解哪些页在内存中，哪些页在磁盘上，因此它实际上不知道查询会导致多少 I/O。
- MySQL 的“最佳”概念可能与您的不同。您可能希望获得最快的执行时间，但 MySQL 实际上并不试图使查询快速；它试图最小化它们的成本，正如我们所见，确定成本并不是一门确切的科学。
- MySQL 不考虑同时运行的其他查询，这可能会影响查询运行的速度。
- MySQL 并不总是进行基于成本的优化。有时它只是遵循规则，例如“如果有一个全文
MATCH()子句，如果存在FULLTEXT索引，则使用它”。即使使用不同的索引和带有WHERE子句的非FULLTEXT查询更快，它也会这样做。 - 优化器不考虑不受其控制的操作的成本，例如执行存储函数或用户定义的函数。
- 正如我们将在后面看到的，优化器并不总是能够估计每种可能的执行计划，因此可能会错过最佳计划。
MySQL 的查询优化器是一个非常复杂的软件部分，它使用许多优化来将查询转换为执行计划。有两种基本类型的优化，我们称之为静态和动态。静态优化 可以通过检查解析树简单地执行。例如，优化器可以通过应用代数规则将 WHERE 子句转换为等效形式。静态优化与值无关，例如 WHERE 子句中常量的值。它们可以执行一次，并且在使用不同值重新执行查询时始终有效。您可以将其视为“编译时优化”。
相比之下，动态优化 基于上下文，并且可能取决于许多因素，例如 WHERE 子句中的值或索引中的行数。它们必须在每次执行查询时重新评估。您可以将其视为“运行时优化”。
在执行预处理语句或存储过程时，区别很重要。MySQL 可以进行静态优化一次，但必须每次执行查询时重新评估动态优化。有时，MySQL 甚至在执行过程中重新优化查询。⁶
以下是 MySQL 知道如何执行的一些优化类型：
重新排序连接
表不一定要按照查询中指定的顺序连接。确定最佳连接顺序是一项重要的优化；我们稍后在本章中深入解释。
将 OUTER JOIN 转换为 INNER JOIN
OUTER JOIN 不一定要作为 OUTER JOIN 执行。某些因素，例如 WHERE 子句和表模式，实际上可能导致 OUTER JOIN 等效于 INNER JOIN。MySQL 可以识别这一点并重写连接，从而使其有资格进行重新排序。
应用代数等价规则
MySQL 应用代数变换来简化和规范化表达式。它还可以折叠和减少常量，消除不可能的约束和常量条件。例如，术语 (5=5 AND a>5) 将简化为 a>5。类似地，(a 变为 b>5 AND b=c AND a=5。这些规则对于编写条件查询非常有用，我们稍后在本章中讨论。
COUNT()、MIN() 和 MAX() 优化
索引和列的可空性通常可以帮助 MySQL 优化这些表达式。例如，要找到 B 树索引中最左边的列的最小值，MySQL 可以只请求索引中的第一行。它甚至可以在查询优化阶段执行此操作，并将该值视为常量用于查询的其余部分。类似地，要找到 B 树索引中的最大值，服务器会读取最后一行。如果服务器使用此优化，您将在 EXPLAIN 计划中看到“选择表已优化” 。这实际上意味着优化器已将表从查询计划中移除，并用常量替换。
评估和简化常量表达式
当 MySQL 检测到表达式可以简化为常量时，它会在优化过程中这样做。例如，如果用户定义的变量在查询中没有更改，它可以转换为常量。算术表达式是另一个例子。
也许令人惊讶的是，即使你可能认为是一个查询的东西也可以在优化阶段被减少为一个常量。一个例子是对索引的MIN()。这甚至可以扩展到对主键或唯一索引的常量查找。如果WHERE子句对这样的索引应用一个常量条件，优化器知道 MySQL 可以在查询开始时查找值。然后它将在查询的其余部分将该值视为常量。这里有一个例子：
mysql> EXPLAIN SELECT film.film_id, film_actor.actor_id -> FROM sakila.film -> INNER JOIN sakila.film_actor USING(film_id) -> WHERE film.film_id = 1\G *************************** 1\. row *************************** id: 1 select_type: SIMPLE table: film partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 2 ref: const rows: 1 filtered: 100.00 Extra: Using index *************************** 2\. row *************************** id: 1 select_type: SIMPLE table: film_actor partitions: NULL type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 5462 filtered: 10.00 Extra: Using where; Using index
MySQL 以两个步骤执行此查询，对应于输出中的两行。第一步是在film表中找到所需的行。MySQL 的优化器知道只有一行，因为film_id列上有一个主键，并且在查询优化阶段已经查询了索引以查看将找到多少行。因为查询优化器有一个已知数量（WHERE子句中的值）用于查找，所以这个表的ref类型是const。
在第二步中，MySQL 将第一步找到的film_id列视为已知数量。它可以这样做，因为优化器知道当查询到达第二步时，它将知道第一步的所有值。请注意，film_actor表的ref类型是const，就像film表的一样。
另一种你会看到常量条件应用的方式是通过从一个地方传播值的常量性到另一个地方，如果有一个WHERE、USING或ON子句将值限制为相等。在这个例子中，优化器知道USING子句强制film_id在查询中的任何地方具有相同的值；它必须等于WHERE子句中给定的常量值。
覆盖索引
当索引包含查询所需的所有列时，MySQL 有时可以使用索引来避免读取行数据。我们在上一章节中详细讨论了覆盖索引。
MySQL 可以将某些类型的子查询转换为更高效的替代形式，将它们减少为索引查找而不是单独的查询。
早期终止
高性能 MySQL 第四版（GPT 重译）（三）(2)https://developer.aliyun.com/article/1484349
