理解MySQL查询优化处理过程

目录

• 解析器和预处理器

• 查询优化器

MySQL查询优化需要经过解析、预处理和优化三个步骤。在这些过程中，都有可能发生错误。本篇文章不会深入讨论错误处理，而是帮助理解 MySQL 执行查询的方式，以便可以写出更好的查询语句。

解析器和预处理器

一开始，MySQL 的解析器将查询语句拆分成一系列指令并从中构建一棵“解析树”。解析器使用 MySQL 的SQL 语法去翻译和验证查询语句。例如，解析器保证了查询中的指令是有效且次序正确，并且会检查那种类似字符串引号未配对的错误。

预处理器则检查构建好的解析树中那些解析器无法处理的语义信息。例如，检查数据表和列是否存在，并且处理字段名称和别名以保证列引用没有歧义。接下来，预处理器会检查权限，通常这会非常快（除非你的服务端有一大堆权限配置）。

查询优化器

经过解析器和预处理器后，解析树就被确定是有效的了，可以被优化器进行处理并最终转变为一个查询计划。一个具有相同结果的查询通常有很多种执行方式，而优化器的职责是找出其中最优的选项。

MySQL使用基于代价估计的优化器，这意味着它视图预测众多执行计划的代价，并选择代价最低的那个。最初的单位成本是随机的4KB 数据页读取，而现在变得更为复杂，包括了如执行 WHERE比较条件的代价。可以通过显示 Last_query_cost 会话变量来查看查询优化器估计查询语句的代价。

SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;
SHOW STATUS LIKE 'Last_query_cost';

显示的 Last_query_cost 意味着优化器估计需要执行对应次数的随机数据页访问才能完成查询。这是基于如下统计估算的结果：

• 数据表或索引占据的数据页数；

• 索引的候选值；

• 数据行、键及键值分布对应的数据长度。
  

优化器并不会考虑估计内容的缓存——它假设每次都从磁盘 I/O 读取结果。优化器并不是每次都能选择最优的执行计划，原因如下：

• 统计本身可能是错误的。服务端的统计结果依赖于存储引擎，而存储引擎可能十分准确也可能很不准确。例如，InnoDB 由于其 MVCC 架构，并不保留数据表的准确行数。

• 估计的代价和实际运行的代价并不等价，因此即便统计是准确的，查询的代价与 MySQL 的估计也会或多或少存在偏差。一个读取更多数据页的查询计划也可能代价更低，例如如果是有序的磁盘 I/O 访问就会更快，又或是结果本身就已经在缓存中。因此，优化器本身并不知道查询会引起多少次 I/O 操作。

• MySQL 人为的优化也许与我们期待的不同。我们要的可能是更快的执行时间，而 MySQL 并不是只追求快，它是最求最小化代价。因此，通过代价并不一定科学。

• MySQL并不考虑并发中的查询，而这可能会影响查询运行的速度。

• MySQL 并不是一直都按代价估计做优化。有时候仅仅是遵循一些规则，例如如果有一个全文匹配条件（MATCH 方法）则使用全文索引。即便是有一个更快的的其他索引和非全文条件查询，MySQL 也不会按更快的方式执行查询。

• 优化器对于不归它控制的操作的代价并不会考虑，例如执行存储过程或自定义函数。

• 优化器并不总是能够估计每一个执行计划，有些时候它会忽略一个更优的计划。
  

MySQL 查询优化器是其中非常复杂的一部分，使用了很多优化方式将查询语句转换成为一个查询执行计划。通常有两种优化方式：静态优化和动态优化。静态优化可以简单地通过检查解析树进行。例如，优化器可以将 WHERE 条件通过数学运算规则转换成一个等式。静态优化与具体的值无关，例如 WHERE条件的常量值。他们执行一次后会一直有效，即便是查询语句使用了不同的值再次执行。可以理解为是“编译时优化”。

相反，动态优化是基于具体的情景的，并依赖于多种因素。例如，WHERE 条件中的值或索引中对应的数据行数。这个过程在每次查询都需要重新估计，可以理解为是“运行时优化”。以下是一些 MySQL 的典型优化方式：

• 联合查询重新排序：数据表并不一定需要按照查询语句的顺序联合。决定最优的联合查询次序是十分重要的优化。

• 将外联接转换为内联接：一个外联接并不一定需要按外联接查询。有些因素，例如 WHERE 条件和数据表结构可以将外联接查询等价于内联接。MySQL 可以识别这些情况，并重写联合查询。

• 应用数学等价公式：MySQL 应用数学等价转换简化表达式。可以做到展开和减少常量，排除不可能的情况和常量表达式。例如，表达式(5=5 AND a>5)会精简为(a>5)。同样的，(a 5 AND b=c AND a=5.这些规则对带条件的查询十分有用。

• COUNT()，MIN()和 MAX()优化：索引和空值列通常可以帮助 MySQL 优化这些函数。例如，查找二叉树最左侧一列的最小值时，MySQL 可以只请求索引的第一行数据。甚至可以在查询优化阶段完成这个事情，而对于剩余的查询当作是常量值。而对于查询最大值也是一样，只需要读取最后u 一行即可。如果服务端使用了这种优化，可以在 EXPLAIN 中看到“Select tables optimized away”。这意味着优化器将数据表从查询计划中移除并用常量替代了。类似地，COUNT(*)查询在没有指定 WHERE 条件时也可以在某些存储引擎被优化（例如 MyISAM，会一直保存数据表的准确行数）。

• 评估和精简常量表达式：一旦 MySQL 检测到一个表达式可以精简为一个常量，那在优化阶段就会完成该操作。例如，一个用户定义的变量如果在查询过程中没有变化，就可以转换为常量。令人惊奇的是，在优化阶段，有些你认为是一个查询的语句也会被转换为常量。一个例子就是 索引上的MIN()。这种情况也可以扩展到对主键或独立索引的常量查询。如果 WHERE 条件对这样的索引指定了常量，优化器会知道 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;

MySQL 会将这个查询拆分为2步，因此分析结果会有两行。第一步是是在 film 表中查找对应的数据行。由于 是按主键film_id查询的，MySQL 知道只有一行数据。 因此，此时的查询分析结果的 ref 是常量。在第二步中，MySQL 会将 film_id 作为已知值，因此对 film_actor 的查询的 ref 也是常量。其他类似的场景还有 WHERE，USING或 ON 条件中的约束条件是等式。在这个例子中，MySQL 知道 USING条件的 film_id 在查询语句中都是相同的值，这个值必须和 WHERE条件的 film_id 相同。

• 覆盖索引：MySQL 有时候会利用索引数据而避免读数行数据，如果索引包含了查询所需的全部列的话。

• 子查询优化：MySQL 能够将一些类型的子查询转换为更有效的变体形式，从而简化它们为索引查询而不是相互独立的查询。

• 提前中止：MySQL 可以在满足查询结果后提前中止查询过程。最明显的例子是 LIMIT条件。也有一些其他的提前中止的情形。例如，MySQL 检测导一个可能条件后，可以中止整个查询，如下面的例子所示：

EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id=1;

在分析结果中的 Extra字段会看到“Impossible WHERE noticed after reading const tables”。在其他情形也会有提前中止的情况，例如：

SELECT film.film_id
FROM sakila.film
LEFT OUTER JOIN sakila.film_actor USING(film_id)
WHERE sakila.film_actor.film_id IS NULL;

这个查询排除那些有演员的电影。每部电源都可能有多名演员，但是只要找到一名演员后，MySQL 就会停止处理当前的这部电影，而去处理下一部。对于 DISTINCT，NOT EXISTS 也会有类似的情况。

• 等效传递：MySQL 会识别导查询语句中保持的列是否是等效的。例如，在 JOIN 条件中，WHERE 条件会影响导相同的列，如下面的查询：

SELECT film.film_id
FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id)
WHERE film.film_id > 500;

MySQL 会知道 WHERE 条件的约束不仅适用于 film 表，同样也适用于 film_actor 表。但对于其他数据库则未必会有这样的优化效果。

• IN 查询比较：对于很多数据库服务器，IN 查询比等价为多个 OR 条件，在逻辑上二者是等效的。但在 MySQL 中不是这样，MySQL会对 IN 查询的列表值进行排序，并使用二分查找法去检查查询值是否在列表中。这会使得算法复杂度从 O(n)降低导 O(log n)。

实际上，MySQL 使用的优化手段比上述列举的多得多，这里没法一一列举。只是需要记住 MySQL 的优化器的复杂性及其智能化程度。因此，应当让优化器发挥其作用，而不是无限优化查询语句直到 MySQL 的优化器没有用武之地。当然，虽然 MySQL 的优化器很聪明，但是它给出的并不一定是最优结果，有些时候你知道最优结果，而 MySQL 未必知道。这种情况下，你可以对查询语句进行优化从而帮助 MySQL 完成优化工作，而有些时候则需要增加查询的提示，或是重写查询，修改数据表设计或增加索引。

来源：https://juejin.cn/post/6954708924738043911