深入理解mysql-第九章 mysql查询优化-基于规则

╰半夏微凉° 2022-10-04 13:57 602阅读 0赞

**目录**

一、条件化简

1.1 移除不必要的括号

1.2 常量传递

1.3 移除没用的条件

1.4 表达式计算

二、外连接消除（转化为内连接）

三、子查询优化

3.1 子查询的分类

3.1.1 根据结果划分

3.1.2 按与外层查询关系来区分

3.2 子查询的执行方式

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

3.2.2 IN子查询优化

四、对于派生表的优化

4.1 派生表物化

4.2 将派生表和外层的表合并

--------------------

`本章我们继续开始对sql语句的查询优化讲解，上一章我们主要讲mysql内部对索引的选择优化。本章我们讲一下mysql`依据一些规则，把糟糕的SQL语句转换成可以比较高效执行的语句，这个过程也可以被称作`查询重写。`

# 一、条件化简 #

## 1.1 移除不必要的括号 ##

有时候表达式里有许多无用的括号，比如这样：

((a = 5 AND b = c) OR ((a > c) AND (c < 5)))

优化器会把那些用不到的括号给干掉，就是这样：

(a = 5 and b = c) OR (a > c AND c < 5)

## 1.2 常量传递 ##

表达式中两个常量值使用`AND`连接起来时（用OR肯定就不可以）：

a = 5 AND b > a

就可以被转换为：

a = 5 AND b > 5

## 1.3 移除没用的条件 ##

对于一些明显永远为`TRUE`或者`FALSE`的表达式，优化器会移除掉它们，比如这个表达式：

(a < 1 and b = b) OR (a = 6 OR 5 != 5)

实际就是：

(a < 1 and TRUE) OR (a = 6 OR FALSE)

可以继续被简化为：

a < 1 OR a = 6

## 1.4 表达式计算 ##

a = 5 + 1

因为`5 + 1`这个表达式只包含常量，所以就会被化简成：

a = 6

但是这里需要注意的是，如果某个列并不是以单独的形式作为表达式的操作数时，比如出现在函数中，出现在某个更复杂表达式中，就像这样：

ABS(a) > 5

所以最好让索引列以单独的形式出现在表达式中。

# 二、外连接消除（转化为内连接） #

我们前边说过，`内连接`的驱动表和被驱动表的位置可以相互转换，而`左（外）连接`和`右（外）连接`的驱动表和被驱动表是固定的。这就导致`内连接`可能通过优化表的连接顺序来降低整体的查询成本，而`外连接`却无法优化表的连接顺序。所以在特定情况下，mysql会将外连接转化为内连接。

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

因此，**只要我们在搜索条件中指定关于被驱动表相关列的值不为`NULL`，在这种情况下外连接和内连接也就没有什么区别了。我们把这种在外连接查询中，指定的`WHERE`子句中包含被驱动表中的列不为`NULL`值的条件称之为`空值拒绝`（英文名：`reject-NULL`）**。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

# 三、子查询优化 #

## 3.1 子查询的分类 ##

### 3.1.1 根据结果划分 ###

因为子查询本身也算是一个查询，所以可以按照它们返回的不同结果集类型而把这些子查询分为不同的类型：

*  标量子查询，只返回一个单一值的子查询称之为`标量子查询。`
 *  行子查询，就是返回一条记录的子查询，不过这条记录需要包含多个列。
    
        SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1);
 *  列子查询，查询出一个列的数据，不过这个列的数据需要包含多条记录。
    
        SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2);
 *  表子查询，查询的结果既包含很多条记录，又包含很多个列。
    
        SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2);

### 3.1.2 按与外层查询关系来区分 ###

包括了不相关子查询和相关子查询：

#不相关子查询
    SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2);
    
    #相关子查询（子查询中出现了s1.key2 = s2.key2，意味着该子查询的执行依赖着外层查询的值）
    SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE s1.key2 = s2.key2);

## 3.2 子查询的执行方式 ##

### 3.2.1 标量子查询、行子查询的执行方式 ###

子查询使用`=`、`>`、`<`、`>=`、`<=`、`<>`、`!=`、`<=>`等操作符和某个操作数组成一个布尔表达式，这样的子查询必须是标量子查询或者行子查询。

对于不相关标量子查询或者行子查询来说，它们的执行方式是简单的，比方说下边这个查询语句：

SELECT * FROM s1 
        WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);

它的执行方式：

*  先单独执行`(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1)`这个子查询。
 *  然后在将上一步子查询得到的结果当作外层查询的参数再执行外层查询`SELECT * FROM s1 WHERE key1 = ...`。

对于相关的标量子查询或者行子查询来说，比如下边这个查询：

SELECT * FROM s1 WHERE 
        key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);

它的执行方式就是这样的：

1.  先从外层查询中获取一条记录，本例中也就是先从`s1`表中获取一条记录。
2.  然后从上一步骤中获取的那条记录中找出子查询中涉及到的值，本例中就是从`s1`表中获取的那条记录中找出`s1.key3`列的值，然后执行子查询。
3.  最后根据子查询的查询结果来检测外层查询`WHERE`子句的条件是否成立，如果成立，就把外层查询的那条记录加入到结果集，否则就丢弃。
4.  再次执行第一步，获取第二条外层查询中的记录，依次类推～

也就是说使用标量子查询以及行子查询的场景中，`MySQL`优化器的执行方式并没有什么新鲜的。

### 3.2.2 IN子查询优化 ###

IN子查询优化，主要针对的列子和表子这种多结果值的子查询的优化，执行方式是在3.2.1的基础上进行了优化。对于不相关的`IN`子查询，比如这样：

SELECT * FROM s1 
        WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');

如果子查询的结果集中的记录条数很少，那么把子查询和外层查询分别看成两个单独的单表查询效率还是蛮高的，但是如果单独执行子查询后的结果集太多的话，就会导致这些问题：

*  子查询结果集太多，可能内存中都放不下
 *  对于外层查询来说，如果子查询的结果集太多，可能会导致检测是否符合条件时间变长，甚至全表扫描

于是mysql就将子查询结果集去重并写入一个临时表（物化过程），一般情况下子查询结果集不会大的离谱，所以会为它建立基于内存的使用`Memory`存储引擎的临时表，而且会为该表建立哈希索引。如果子查询的结果集非常大，超过了系统变量`tmp_table_size`或者`max_heap_table_size`，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为`B+`树索引。

有时候，`s1`和子查询物化表`materialized_table可以`进行内连接查询，查询优化器可以评估不同连接顺序需要的成本是多少，选取成本最低的那种查询方式执行查询。

在特定条件下，物化操作直接把子查询转换为连接查询，就是半连接semi-join。如果`IN`子查询符合转换为`semi-join`的条件，查询优化器会优先把该子查询转换为`semi-join`，然后再考虑5种执行半连接的策略中哪个成本最低，选择成本最低的那种执行策略来执行子查询。

特别注意并不是所有包含`IN`子查询的查询语句都可以转换为`semi-join。`semi-join只是在MySQL内部采用的一种执行子查询的方式，MySQL并没有提供面向用户的semi-join语法。

扩展一下：MySQL在执行诸如DISTINCT查询、分组查询、Union查询以及某些特殊条件下的排序查询也都可能在内部先创建一个临时表，使用这个临时表来辅助完成查询（比如对于DISTINCT查询可以建一个带有UNIQUE索引的临时表，直接把需要去重的记录插入到这个临时表中，插入完成之后的记录就是结果集了）。在数据量大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB等存储引擎，在数据量不大时可能创建基于内存的临时表，也就是使用Memory存储引擎。

# 四、对于派生表的优化 #

子查询放在外层查询的`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`提供了两种执行策略。

## 4.1 派生表物化 ##

我们可以将派生表的结果集写到一个内部的临时表中，然后就把这个物化表当作普通表一样参与查询。需要注意的是，在对派生表进行物化时，`MySQL`使用了一种称为`延迟物化`的策略，也就是在查询中真正使用到派生表时才回去尝试物化派生表，而不是还没开始执行查询呢就把派生表物化掉。比方说对于下边这个含有派生表的查询来说：

SELECT * FROM (
            SELECT * FROM s1 WHERE key1 = 'a'
        ) AS derived_s1 INNER JOIN s2
        ON derived_s1.key1 = s2.key1
        WHERE s2.key2 = 1;

如果采用物化派生表的方式来执行这个查询的话，那么执行时首先会到`s2`表中找出满足`s2.key2 = 1`的记录，如果找不到数据，说明参与连接的`s2`表记录就是空的，所以整个查询的结果集就是空的，所以也就没有必要去物化查询中的派生表了。

## 4.2 将派生表和外层的表合并 ##

对于4.1的SQL，我们可以将派生表与外层查询的表合并，然后将派生表中的搜索条件放到外层查询的搜索条件中，就像这样：

SELECT * FROM s1 INNER JOIN s2 
        ON s1.key1 = s2.key1
        WHERE s1.key1 = 'a' AND s2.key2 = 1;

这样通过将外层查询和派生表合并的方式成功的消除了派生表，也就意味着我们没必要再付出创建和访问临时表的成本了。可是并不是所有带有派生表的查询都能被成功的和外层查询合并，所以`MySQL`在执行带有派生表的时候，优先尝试把派生表和外层查询合并掉，如果不行的话，再把派生表物化掉执行查询。