• 1 条件优化

• 1.1 移除不必要的括号

• 1.2 常量传递

• 1.3 等值传递

• 1.4 移除没用的条件

• 1.5 表达式计算

• 2 Having子句和WHERE子句的合并

• 2.1 常量表检测

• 2.2 外连接消除

• 3 子查询优化

• 3.1 按与外层查询关系来区分子查询

• 3.2 子查询在布尔表达式中的使用

• 3.3 标量子查询、行子查询的执行方式

• 3.4 IN子查询优化

• 3.5 ANY/ALL子查询优化

• 3.6 EXISTS子查询的执行

• 3.7 对于派生表的优化

基于规则的查询优化

1 条件优化

1.1 移除不必要的括号

优化器会将用不到的括号去掉

1.2 常量传递

有时候某个表达式是某个列和某个常量做等值匹配，当这个表达式和其他涉及a的表达式用AND连接起来时，可以将其他表达式中的a的值替换为5，比如这样：

a = 5 AND b > a

复制

1.3 等值传递

多个列之间存在等值匹配的关系，如：

a = b and b = c and c = 5

复制

这个表达式可以被简化为：

a = 5 and b = 5 and c = 5

复制

1.4 移除没用的条件

对于一些明显永远为TRUE或者FALSE的表达式，优化器会移除它们

1.5 表达式计算

在查询开始执行之前，如果表达式中只包含常量的话，它的值会被先计算出来

2 Having子句和WHERE子句的合并

如果查询语句中没有出现诸如SUM、MAX等等的聚集函数以及GROUP BY子句，优化器就把HAVING子句和WHERE子句合并起来

2.1 常量表检测

以下两种查询的运行速度非常快

• 查询的表中一条记录没有，或者只有一条记录

“

因为InnoDB的统计数据不准确，所以这一条不能用于使用InnoDB作为存储引擎的表，只能适用于使用Memory或者MyISAM存储引擎的表。

”

• 使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表

通过上面两种方式查询的表称之为常量表。优化器在分析一个查询语句时，先首先执行常量表查询，然后把查询中涉及到该表的条件全部替换成常数，最后再分析其余表的查询成本。

2.2 外连接消除

内连接的驱动表和被驱动表的位置可以相互转换，而外连接驱动表和被驱动表是固定的。这就导致内连接可能通过优化表的连接顺序来降低整体的查询成本，而外连接不能。

当查询语句中没有on语句或者on里面的语句确定为NOT NULL的情况，外连接和内连接可以相互转换，也就是外连接可以通过改变连接顺序来执行查询

3 子查询优化

3.1 按与外层查询关系来区分子查询

不相关子查询

如果子查询可以单独运行出结果，而不依赖于外层查询的值，我们就可以把这个子查询称之为不相关子查询。

相关子查询

如果子查询的执行需要依赖于外层查询的值，我们就可以把这个子查询称之为相关子查询。

3.2 子查询在布尔表达式中的使用

子查询最多的地方就是把它作为布尔表达式的一部分来作为搜索条件用在WHERE子句或者ON子句里，子查询在布尔表达式中的使用场景有以下几种：

• 使⽤=
  、>
  、<
  、>=
  、<=
  、<>
  、!=
  、<=>
  作为布尔表达式的操作符。这里的子查询只能式标量子查询或者行子查询，也就是子查询的结果只能返回一个单一的值或者只能是一条记录。

• [NOT] IN/ANY/SOME/ALL
  子查询

• IN：用来判断某个操作数在不在由子查询结果集组成的集合中

  SELECT * FROM t1 WHERE (m1, n2) IN (SELECT m2, n2 FROM t2);
  
  复制

• ANY/SOME：只要子查询结果集中存在某个值和给定的操作数满足要求，则表达式结果为TRUE

  SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2);
  
  复制

• ALL：子查询结果集中所有的值和给定的操作数满足要求，表达式结果为TRUE；

  SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2);
  
  复制

• EXIST
  子查询。判断子查询的结果集中是否有记录

3.3 标量子查询、行子查询的执行方式

下面两个情景中经常用到标量子查询和行子查询：

1. SELECT
   子句中，子查询必须为标量子查询
2. 子查询用比较运算符或者某个操作数组成一个布尔表达式，必须用标量子查询或者行子查询

对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，就当作两个单表查询。

3.4 IN子查询优化

物化表

对于日常生活中使用频率最高的IN子查询，MySQL也做了相当多的优化。对于不相关的IN子查询，如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率还是很高的，但是一旦单独执行子查询后的结果集中的记录数过多的话，就会出现以下问题：

• 结果集太多，内存中放不下
• 结果集中记录太多，导致IN子句的参数也过多，无法有效利用索引而进行全表扫描，影响效率

对于IN子查询，不直接将子查询的结果当作外层查询的参数，而是将该结果集写入一个临时表里。写入临时表的过程是这样的：

• 该临时表的列就是子查询结果集中的列
• 写入临时表的记录会被去重
• 一般情况下子查询结果集建立在基于内存的使用Memory存储引擎的临时表中，建立哈希索引

这个过程称为物化
。存储子查询结果集的临时表被称为物化表。因为物化表中的记录都建立了索引，因此通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快。

物化表转连接

前面介绍过，查询优化器可以评估不同连接顺序需要的成本是多少，上面的物化表和外层查询的表实际上是一种内连接的关系。也就是查询优化器以物化表或者外层查询的表为驱动表的成本，选择合适的驱动表。

将子查询转换为semi-join

将子查询进行物化之后再执行查询有建立临时表的成本，我们能不能省去这一步骤让子查询直接转换为连接呢？通过上面的步骤我们发现，这个物化的过程与内连接是非常相像的，但是会出现被驱动表有多个重复记录的情况。

对于驱动表来说并不关心有多少记录与其匹配，因此引入了数据库中的一个概念半连接
——只要被驱动表中存在与驱动表中匹配的记录，立即停止查找驱动表，并将驱动表记录存入结果集。也就是完成去重操作。

关于MySQL中的半连接优化可以去查看博客，这里不再赘述。

“

并不是所有的IN连接都可以使用semi-join优化的，semi-join必须满足外层连接不关心内层连接的记录数，而是只关心外层连接的记录能否放入结果集的情况才能使用。

”

3.5 ANY/ALL子查询优化

3.6 EXISTS子查询的执行

如果Exists子查询是不相关子查询，可以先执行子查询得出EXISTS子查询的结果是TRUE或者是FALSE，并重写原先得到的子查询语句。

对于相关子查询语句来说，就只能按照最普通的查询规则来了

3.7 对于派生表的优化

如果子查询放在外层查询的FROM子句后，那么这个子查询的结果相当于一个派生表，比如下面这个查询：

SELECT * FROM ( 
    SELECT id AS d_id, key3 AS d_key3 FROM s2 
    WHERE key1 = 'a' 
) AS derived_s1 
WHERE d_key3 = 'a';

复制

对于含有派生表的查询，MySQL提供了两种执行策略：

• 派生表物化

  可以将派生表的结果集写道一个内部的临时表中，然后就把这个物化表当作普通表一样参与查询。MySQL使用了一种称为延迟物化
  的策略，也就是拆卸那种真正使用到派生表时才会去尝试物化派生表。

• 将派生表和外层的表合并，也就是将查询重写为没有派生表的形式

  当然，并不是所有的派生表都能成功的和外层表合并，以下几种语句不能和外层查询合并：

• 聚集函数，比如MAX()
  、MIN()
  、SUM()
  等
• DISTINCT
• GROUP BY
• HAVING
• LIMIT
• UNION或者UNION ALL
• 派生表对应的子查询的SELECT子句中含有另一个子查询