《Mysql是怎样运行的》读书笔记三

    SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

    SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

    SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

    ((a = 5 AND b = c) OR ((a > c) AND (c < 5)))
    #查询优化器会将这些括号干掉
    (a = 5 and b = c) OR (a > c AND c < 5)

    a = 5 AND b > a
    被转化为
    a = 5 AND b > 5

    a=5 Or b>a  #a的条件不一定为a=5 

    (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

    a = 5 + 1 化简为 a=6;

    ABS(a) > 5
    -a < -8 //不会被化简

    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 WHERE t2.m2 = 2;

     SELECT * FROM t1 WHERE m1 = (SELECT MIN(m2) FROM t2);

     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);

    #相关子查询
     SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2);

* 使用 = 、 > 、 < 、 >= 、 <= 、 <> 、 != 、 <=> 作为布尔表达式的操作符

    操作数 操作符(子查询)，这里的子查询只能为 标量子查询或 行子查询

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

>  
>  
>             
> ```
>
>
>     * ANY/SOME (ANY和SOME语义一致)
>

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

>  
>  
>             
> ```
>
>

    * EXISTS子查询

有时候我们仅仅需要判断子查询结果集是否有记录，而不在乎他的记录具体形式，可以把EXISTS或 NOT EXISTS放在子查询语句之前

SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2);

##### 3.2.4子查询注意事项

  1. 

子查询必须用小括号括起来

 SELECT SELECT m1 FROM t1; #报错

2. 

在Select 子句的子查询必须为标量子查询

 SELECT (SELECT m1, n1 FROM t1); #报错

3. 

在想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用 LIMIT 1 语句来限制记录数量。

  4. 

**对于[NOT] IN/ANY/SOME/ALL子查询来说，子查询中不允许有LIMIT，下列子查询出现的语句也是多余**

    1. Order By子句
    2. DISTINCT语句
    3. 没有聚合函数以及 Having子句的Group子句

  5. 

**不允许在一条语句中删改某个表的记录的同时还对该表进行子查询**

 DELETE FROM t1 WHERE m1 < (SELECT MAX(m1) FROM t1);

#### 3.3 子查询在Mysql中的执行过程

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

  * 对于不相关的标量子查询 或 行子查询的查询语句，MYSQL会分别独立的执行外层查询和子查询，相当于两个单表查询
  * 对于相关子查询（标量或行子查询），其执行方式： 
    * 从外层查询获取一条记录
    * 从上一步骤得到记录找出子查询涉及到的值，进行子查询

##### 3.3.2 IN 子查询优化

对于不相关子查询

如果子查询的结果集中的记录数很少，那么把子查询和外层查询分别看成两个单独的单表查询

如果子查询的结果集太多内存放不下。对于外查询而言，子查询结果集太多，意味着IN子句的参数比较多，导致

无法有效使用索引，只能对外层查询进行全表扫描

由于 IN 子句中的参数太多，这会导致检测一条记录是否符合和 IN 子句

中的参数匹配花费的时间太长。

###### 临时表的提出：

​ 对于上述IN子查询（不相关子查询） 出现子查询结果集太多，Mysql设计了临时表。不直接将不相关子查询的结果当作外层查询的参数，而是将该结果集写入一个临时表里。

**写入临时表的过程：**

  1. 临时表的列就是子查询结果集中的列
  2. 写入临时表的记录会被去重
  3. 一般子查询的结果集不会大的离谱，所以为他建立基于内存使用Memory存储引擎的临时表，并为该表建立哈希索引（是hash索引，不是B+树索引），如果确实太大，超过了指定的阈值，临时表转而使用基于磁盘的存储引擎来保持记录，此时就用到B+索引

###### 物化的概念

Mysql将子查询结果集中的记录保存到临时表的过程称之为物化，我们就把那个存储子查询结果集的临时表称之为 物化表 。

###### 物化表转为连接

​ 将子查询的结果集物化之后，其实等价于一个内连接查询，外层查询的表和物化表的内连接查询。这样查询优化器又可以对内连接进行优化，通过评估不同连接顺序需要的成本，选取成本最低的那种查询方式进行查询

##### 将子查询转化为半连接（Semi-join）

​ 子查询转化为连接，需要将子查询的结果集进行物化（每次查询都会有建立临时表的成本）。为此Mysql有设法能不能不进行物化将子查询转化为连接呢？

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

上面sql执行和下面联合查询的执行很类似

SELECT s1.* FROM s1 INNER JOIN s2 
 ON s1.key1 = s2.common_field 
 WHERE s2.key3 = 'a';

但是还是有区别：我们不能保证s1表的某条记录，在s2表中有多少条记录满足`s1.key1 = s2.common_field` 这个筛选条件：

情况一：对于 s1 表的某条记录来说， s2 表中没有任何记录满足 s1.key1 = s2.common_field 这个条件，那么该记录自然也不会加入到最后的结果集。

情况二：对于 s1 表的某条记录来说， s2 表中有且只有记录满足 s1.key1 = s2.common_field 这个条件，那么该记录会被加入最终的结果集。

情况三：对于 s1 表的某条记录来说， s2 表中至少有2条记录满足 s1.key1 = s2.common_field 这个条件，那么该记录会被多次加入最终的结果集。

>
>
> 对于上面的子查询，我们子关系s1的key1 在s2表是否存在记录满足`s1.key1 = s2.common_field`这个条件，而不关心具体有多少条记录与其匹配，又因为有情况三的存在，所以我们上面俩个sql并不完全等价。
>
>

​ **这里Mysql提出了`半连接`：表s1与表s2进行半连接，指对于s1表的某条记录来说，我们只关心在s2表中是否存在与之匹配的记录是否存在，而不关心具体有多少条记录与之匹配**

注：半连接semi-join 只是在Mysql内部采用的一种子查询方式，并没面向用户提供

##### 半连接的实现方式

  1. Table pullout（子查询中的表上拉）

当子查询的查询列表处只有 主键或者唯一索引列时，可以把子查询中的表上拉到外层查询中，并把子查询中的搜索条件合并到外层查询的搜索条件中

 SELECT * FROM s1

2. DuplicateWeedout execution strategy(重复值消除)

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

​ 某条 s1 表中的记录要加入结果集时，就首先把这条记录的 id 值加入到这个临时表里，如果添加成功，说明之前这条 s1 表中的记录并没有加入最终的结果集，现在把该记录添加到最终的结果集；如果添加失败，说明这条之前这条 s1 表中的记录已经加入过最终的结果集，这里直接把它丢弃就好了，这种使用临时表消除 semi-join 结果集中的重复值的方式称之为 DuplicateWeedout 。

  3. LooseScan execution strategy （松散索引扫描）

 SELECT * FROM s1 
 WHERE key3 IN (SELECT key1 FROM s2 WHERE key1 > 'a' AND key1 < 'b');

转换为内连接后，将子查询的表作为 驱动表的话，执行过程如下：
![请添加图片描述](https://ad.itadn.com/c/weblog/blog-img/images/2025-05-03/TjIKqx6sZLAOyvbYdWo2SDBMPu7g.png)

  * 驱动表idx_key1二级索引值为‘aa’的记录有三条，只需取第一条值到s1表中查找s1.key3=‘aa’ 对应的记录。一次类推，其它值相同的二级索引，也只需要去第一条记录的值到s1表匹配。这种方式称为（松散索引扫描）

  4. 

Semi-join Materialization execution strategy

即上面的物化子查询结果集

  5. 

FirstMatch execution strategy（首次匹配策略）

FirstMatch 作为最原始的半连接的执行方式，就是先取一条外层查询中的记录，然后到子查询的表中寻找符合匹配条件的记录，**如果能找到一条，则将该外层查询的记录放入最终的结果集并且停止查找匹配 更多的记录**

##### 对于相关子查询 （In子查询）

  * 也可以转换为半连接查询，上边介绍的table pullout（前提是子查询的查询列表处于主键或唯一二级索引）、复制消除、松散索引扫描、首次匹配 都可以使用
  * 唯独 物化子查询结果集不能，因为相关子查询并不是一个独立的查询

##### 半连接（semi-join）的适用条件

  1. 子查询必须是和IN语句组成的布尔表达式，并在外层查询的WHERE或ON子句中出现
  2. 外层查询也可以有其他的搜索条件，只不过和 IN 子查询的搜索条件必须使用 AND 连接起来。
  3. 该子查询必须是一个单一的查询，不能是由若干查询由 UNION 连接起来的形式。
  4. 该子查询不能包含 GROUP BY 或者 HAVING 语句或者聚集函数。

##### 半连接（semi-join）的不适用条件

  1. 外层查询的WHERE条件中有其他搜索条件与IN子查询组成的布尔表达式使用 OR 连接起来
  2. 使用 NOT IN 而不是 IN 的情况
  3. 在 SELECT 子句中的IN子查询的情况
  4. 子查询中包含 GROUP BY 、 HAVING 或者聚集函数的情况
  5. 子查询中包含 UNION 的情况

如果IN子查询不符合转换为Semi-join的条件，查询优化器会执行两种策略

  1. 

先将子查询物化之后再执行查询

 SELECT * FROM s1

 WHERE key1 NOT IN (SELECT common_field FROM s2 WHERE key3 = 'a')

虽然用不了semi-join，但物化后，执行效率还是显著增强

  2. 

执行IN to EXISTS转换

#### 3.3.3 ANY/ALL子查询优化
![请添加图片描述](https://ad.itadn.com/c/weblog/blog-img/images/2025-05-03/n7j3BIQPF4vlUrRq61VHYawEzZuS.png)

#### 3.3.4 【NOT】EXISTS子查询的执行

​ 如果 [NOT] EXISTS 子查询是不相关子查询，可以先执行子查询，得出该 [NOT] EXISTS 子查询的结果是 TRUE 还 是 FALSE ，并重写原先的查询语句

SELECT * FROM s1 
 WHERE EXISTS (SELECT 1 FROM s2 WHERE key1 = 'a') 
 OR key2 > 100;

因为这个语句里的子查询是不相关子查询，所以优化器会首先执行该子查询，假设该EXISTS子查询的结果为

TRUE ，那么接着优化器会重写查询为：

SELECT * FROM s1 

 WHERE TRUE OR key2 > 100

#### 3.3.5 对于派生表的优化

派生表：把子查询放在外层的FROM子句后，那么这个子查询的结果就像对于一个派生表

对于派生表的优化，Mysql有两种策略

  1. 

将派生表物化

>
>
> 此时涉及到一个延迟物化的策略，也就是在查询中正真使用到派生表的时候才回去尝试物化派生表。
>
>

  2. 

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