【MySQL】基于规则的优化（内含子查询优化；派生表；物化表；半连接；标量子查询；行子查询）

本文主要是介绍【MySQL】基于规则的优化（内含子查询优化；派生表；物化表；半连接；标量子查询；行子查询），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

概念

常量表：下述两种查询方式查询的表：
- 类型1：查询的表中一条记录都没有，或者只有一条记录。
- 类型2：使用主键等值匹配或者唯一二级索引列等值匹配作为搜索条件来查询某个表
派生表：放在FROM子句后面的子查询称为派生表。
物化：将子查询结果集中的记录保存到临时表的过程。
物化表：存储子查询结果集的临时表。
- 基于内存的物化表有哈希索引；
- 基于磁盘的物化表有 B+树索引。
- 正因为物化表中的记录都建立了索引，通过索引来判断某个操作数是否存在子查询结果集中时，速度会变得非常快，从而提升了子查询语句的性能。

第14章基于规则的优化（内含子查询优化）

14.1 条件化简

移除不必要的括号
常量传递
移除没用的条件
表达式计算
HAVING子句和WHERE子句合并
- 如果查询语句中没有出现聚集函数及GROUP BY子句，则可合并。
常量表检测
- 查询优化器在分析一个查询语句时，首先执行常量表查询，然后把查询中涉及该表的条件全部替换为常数，最后再分析其余表的查询成本。

14.2 外连接消除

外连接和内连接本质区别：
- 对于外连接的驱动表的记录来说，如果无法在被驱动表中找到匹配ON子句中过滤条件的记录，那么该驱动表记录仍然会被加入到结果集中，对应的被驱动表记录的各个字段使用NULL值填充；
- 而内连接的驱动表的记录如果无法在被驱动表中找到匹配ON子句中过滤条件的记录，那么该驱动表记录会被舍弃。

空值拒绝（reject-NULL）：在外连接查询中，指定的WHERE子句中包含被驱动表中的列不为NULL值的条件称为空值拒绝。

即：显式指定被驱动表的某个列符合 IS NOT NULL，或者，隐含点，直接指定被驱动表的某个列符合指定的值，此时就能将外连接转为内连接。

select * from t1 left join t2 on t1.m1=t2.m2 where t2.n2 is not NULL;
# 等价于
select * from t1 inner join t2 on t1.m1 = t2.m2;

select * from t1 left join t2 on t1.m1=t2.m2 where t2.n2 = 2;
# 直接指定'被驱动表的某个列'符合指定的值，此时就能将 外连接 转为 内连接。

在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。
好处：优化器通过评估表的不同连接顺序的成本，选出成本最低的连接顺序执行查询。

14.3 子查询优化

14.3.1 子查询语法

子查询可以在一个外层查询的各种位置出现。
- 在SELECT子句中
- 在FROM子句中
- 在WHERE 或 ON子句中
- 在 ORDER BY 子句和 GROUP BY子句中，虽然语法支持，但无意义。
按照返回结果集区分子查询：
- 标量子查询：只返回一个单一值的子查询。
- 行子查询：返回一条记录的子查询（一条记录可能包含多个列）。
- 列子查询：查出一个列的数据（这个列可能包含多条记录）。
- 表子查询：子查询的结果既包含很多条记录，又包含很多个列。
按与外层查询的关系来区分子查询：
- 不相关子查询：子查询可以单独运行出结构，而不依赖于外城查询的值。
- 相关子查询：子查询的执行需要依赖于外层查询的值。
子查询在布尔表达式中的使用
1. 使用 =、>、<、>=、 <=、 <>、 !=、 <=> 作为布尔表达式的操作符
  - 注意：这里的子查询只能是标量子查询or行子查询，即，子查询的结果只能返回一个单一的值or只能是一条记录。
```
 # 标量子查询select * from t1 where m1 < (select MIN(m2 from t2); # 行子查询select * from t1 where (m1,n1)=(select m2,n2 from t2 limit 1);
```
2. [NOT] IN / ANY / SOME / ALL 子查询
  略
3. EXISTS子查询
  略
子查询语法注意事项
- 子查询必须用小括号括起来。
- 在SELECT子句中的子查询必须是标量子查询（注意，不是from/where等子句）。
```
  # slect 子句中的子查询  是 标量子查询select (select m1, n1 from t1); # where/from子句中的子查询 也可以是 行子查询```
```
- 想要得到标量子查询or行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用 LIMIT 1 语句来限制记录数量。
- 对于[NOT] IN / ANY / SOME / ALL 子查询来说，子查询中不允许有LIMIT语句。
  - 注意：对于[NOT] IN / ANY / SOME / ALL 子查询来说，在子查询中使用 ORDER BY子句、DISTINCT子句，以及没有聚集函数和 HAVING子句的GROUP BY 子句是无意义的。因为，子查询的结果其实相当于是一个集合，集合里的值是否排序一点也不重要。比如：
```
select * from t1 where m1 in (selct m2 from t2 order by m2);
```
  - 在没有聚集函数以及HAVING子句时，GROUP BY 子句就是个摆设。比如：
```
select * from t1 where m1 in (selct m2 from t2 group by m2);
```
- 不允许在一条语句中增删改某个表的记录时，同时还对该表进行子查询。

14.3.2 子查询在MySQL中是怎么执行的

1. 标量子查询、行子查询的执行方式

不相关标量/行子查询语句：独立执行外层查询和子查询（当作两个单表查询）。
相关标量/行子查询语句：例1，
```
# 例1
select * from s1 where key = (select common_field from s2 where s1.key3 = s2.key3 limit 1);
```
执行方式：
- 步骤1：先从外层查询中获取一条记录。在例1中，先从s1表中获取一条记录。
- 步骤2：然后从这条记录中找出子查询中涉及的值。在例1中，从s1表中获取的那条记录中找出s1.key3列的值，然后执行子查询。
- 步骤3：最后根据子查询的查询结果来检测外层查询WHERE子句的条件是否成立。如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
- 步骤4：跳到步骤1，直到外层查询中获取不到记录为止。

2. IN子查询优化

物化表的提出。
- 物化：将子查询结果集中的记录保存到临时表的过程。
- 物化表：存储子查询结果集的临时表。
  - 基于内存的物化表有哈希索引；
  - 基于磁盘的物化表有 B+树索引。
  - 正因为物化表中的记录都建立了索引，通过索引来判断某个操作数是否存在子查询结果集中时，速度会变得非常快，从而提升了子查询语句的性能。
物化表转连接
```
# 查询1
select * from s1
where key1 in (slecet common_field from s2 where key3 = 'a);# 当把子查询物化之后，假设子查询物化表的名称为 materialized_table, 
# 该物化表存储的子查询结果集的列为 m_val。# 则相当于表s1与子查询物化表 materialized_table进行内连接。
# 即，查询1 等价于 查询2# 查询2
select s1.* from s1 
inner join materialized_table on key1 = m_val;
```
- 转换为内连接之后，查询优化器可以评估不同连接顺序需要的成本是多少，然后从中选取成本最低的那种方式执行查询。
- 分析查询2（表s1和物化表materialized_table内连接）的成本构成：
  1. 如果使用 s1表作为驱动表，总查询成本如下：
  - 物化子查询时需要的成本；
  - 扫描s1表时的成本；
  - s1表中的记录数量 x 通过条件 m_val=xxx 对 materialized_table表进行单表访问的成本（由于物化表中的记录时不重复的，并且为物化表中的列建立了索引，所以此步骤非常快）。
  1. 如果使用 materialized_table 表作为驱动表，总查询成本如下：
  - 物化子查询时需要的成本；
  - 扫描物化表时的成本；
  - 物化表中的记录数量 x 通过条件 key1=xxx 对 s1 表进行单表访问的成本（由于在key1列上建立了索引，所以此步骤非常快）。
将子查询转换为半连接

将子查询物化之后再执行查询，会有建立临时表的成本，那，能不能不进行物化操作，直接把子查询转换为连接呢？
```
# 查询1
select * from s1
where key1 in (slecet common_field from s2 where key3 = 'a);
```
- 提出新概念——半连接
- 注意：半连接，是为了 IN子查询优化 的。
- 将 s1表和s2表进行半连接的意思是：
  - 对于s1表中的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录，而不关心具体由多少条记录与之匹配，最终的结果集中只保留s1表的记录。
  - ```
  # 半连接，只是在MySQL内部采用的一种执行子查询的方式，
  # MySQL没有提供面向用户的半连接语法。
  # 所以此语句不能放在黑框框中运行。
  select s1.* from s1 SEMI JOIN s2on s1.key1 = s2.common_fieldwhere key3 = 'a';
```
- 实现半连接方法（策略）：
  - Table pullout （子查询中的表上拉）
  - Duplicate Weedout （重复值消除）
  - LooseScan（松散扫描）
  - Semi-join Materialization（半连接物化）
  - FirstMatch（首次匹配）
- 半连接的适用条件 （只有符合下述条件的子查询才可以转换为半连接）
  - 该子查询必须是与IN操作符组成的布尔表达式，并且在外城查询的WHERE 或者ON子句中出现；
  - 外层查询也可以由其他的搜索条件，只不过必须使用AND操作符与IN子查询的搜索条件连接起来；
  - 该子查询必须是一个单一的查询，不能是由UNION连接起来的若干个查询；
  - 该子查询不能包含GROUP BY、HAVING语句或者聚集函数。
- 不适用于半连接的情况
  - 在外层查询的WHERE子句中，存在其他搜索条件使用OR操作符与IN子查询组成的布尔表达式连接起来的情况。
  - 使用 NOT IN 而不是 IN 的情况。
  - 位于 SELECT 子句中的 IN 子查询的情况。
  - 子查询中包含 GROUP BY 、HAVING 或者聚集函数的情况。
  - 子查询中包含 UNION 的情况。
  - 对于不相关的子查询，可以尝试把它们物化之后再参与查询。
  - 无论子查询是相关的还是不相关的，都可以把 IN 子查询尝试转为 EXISTS 子查询。
- 小结
  - 如果 IN 子查询符合转换为半连接的条件，查询优化器会优先把该子查询转换为半连接，再考虑下面5种执行半连接的策略中哪个的成本最低，最后从中选择成本最低的执行策略来执行子查询。
    - Table pullout （子查询中的表上拉）
    - Duplicate Weedout （重复值消除）
    - LooseScan（松散扫描）
    - Semi-join Materialization（半连接物化）
    - FirstMatch（首次匹配）
  - 如果 IN 子查询不符合转换为半连接的条件，那么查询优化器会从下面两种策略中找出一种成本更低的方式来执行子查询：
    - 先将子查询物化，再执行查询；
    - 执行 IN 到 EXISTS 的转换。