MySQL 内部执行优化策略总结

待我称王封你为后i 2023-01-22 10:49 42阅读 0赞

### MySQL 内部执行优化策略总结 ###

*  前言
 *  一、查询优化策略
 *   *  1. ICP 索引条件下推
     *  2. MRR 多范围读取优化（批量回表）
     *  3. 索引合并
     *   *  (1) Intersection 合并
         *  (2) Union 合并
         *  (3) Sort-Union 合并
 *  二、连接查询优化策略
 *   *  1. Join Buffer 优化
 *  三、 MySQL 查询重写
 *   *  1. 条件化简
     *   *  (1) 表达式计算
         *  (2) 移除不必要的括号
         *  (3) 常量传递
         *  (4) 移除没用的条件
         *  (5) 常量表检测
     *  2. 外连接消除
     *  3. IN 子查询优化
     *   *  (1) 物化表
         *  (2) 半连接 Semi-Join
         *   *  a. Table pullout （子查询中的表上拉）
             *  b. DuplicateWeedout execution strategy （重复值消除）
             *  c. LooseScan execution strategy （松散扫描）
         *  (3) IN 转为 EXISTS

# 前言 #

所有的优化策略都不可能说百分百生效，都需要经过 MySQL 计算成本决定如何执行

所有的优化策略，要么完全不可能执行（不满足条件），要么有可能执行（MySQL 计算使用策略成本最低）

# 一、查询优化策略 #

## 1. ICP 索引条件下推 ##

Index Condition Pushdown，MySQL 5.6 中引入的一种优化策略

**举例：**

前提条件：a 字段上有索引

select * from table where a > "AA" and a like "%Z";

**正常执行顺序：**  
`a > "AA"` 能走索引。由于 `a like "%Z"` 为前模糊，无法走索引。

因此 MySQL 会根据索引，扫描 a 字段区间上的数据，每找到一条数据，就进行一次回表，再判断回表的数据是否满足 `a like "%Z"`。

这样无疑是非常浪费资源的，因为假如回表的数据不满足要求，就相当于白回表。

**MySQL 优化策略：**

1.  MySQL 根据索引查询到数据，先不回表
2.  判断索引是否满足 `a like "%Z"`
3.  不满足直接放弃进入下一条数据，满足就立刻回表

## 2. MRR 多范围读取优化（批量回表） ##

MRR，全称「Multi-Range Read Optimization」。

简单说：MRR 通过把「随机磁盘读」，转化为「顺序磁盘读」，从而提高了索引查询的性能。

**存储上：**  
我们的数据最终是存在磁盘上的，非连续的数据可能存在磁盘的各个地方，每次读取我们需要找到磁盘上的这个位置的数据所在页（数据按页存储，一次读取就会读取一页的数据），这个是非常耗时的，因此每次回表都是一次随机 IO。

MySQL在这方面做了优化，为了节省 IO 在寻道上的成本，会连续读取后面几个扇区的数据。假如我们数据在后面的扇区上，无疑就节省了，大量寻道的时间，这就是顺序 IO。

而连续的数据在磁盘的存储上，就大概率是顺序存储的。因此 MMR 利用这个，在读取了一页的数据后，就会读取后续回表的数据，直到找不到为止，也能知道后续数据不会再用到这一页的数据。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTQzNjcz_size_16_color_FFFFFF_t_70]  
**举例：**

前提条件：a 字段上有索引

select * from table where a > "AA" and a < "DD";

**正常执行顺序：**  
MySQL 会根据索引，扫描 a > “AA” and a < “DD” 索引范围内的数据，每找到一条数据，就进行一次回表。

注意 MySQL 索引的排序顺序是根据索引字段，也就是字段 a 排序的，这也就意味着 ID 主键是无序的，因此 ID 在回表时候就是随机 IO。也由于每次回表都是单独的，因此页上的数据也无法充分使用。

**MySQL 优化策略：**  
MySQL 为了提高查询速度，降低在随机 IO 上的开销，因此做了优化。

1.  MySQL 根据索引扫描出 a > “AA” and a < “DD” 范围内的数据，不回表
2.  对数据在缓存中根据主键进行排序，保证主键有序
3.  接下里再去磁盘上拉取回表数据，此时由于主键有序，因此页上的数据就能充分利用，减少 IO 次数，IO 也变为顺序 IO。

## 3. 索引合并 ##

索引合并（Index Merge Optimization），针对单表查询的优化技术

### (1) Intersection 合并 ###

对应SQL 中的 AND 场景

**使用场景：**

1.  AND 连接查询
2.  等值匹配（如果是联合索引，联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况）
3.  主键列可以是范围匹配（二级索引的范围查找无法使用索引合并）

**举例1：**

*  a，b，c 联合索引
 *  d 二级索引

SELECT * 
    FROM table 
    WHERE a = 'a' AND b = 'a' AND c = 'c' AND d = 'd';

**原理：**

1.  MySQL 会先走 a，b，c 索引获取数据
2.  接着 MySQL 会走 d 索引获取数据
3.  最后会用来个索引返回的数据取交集

**举例2：**

//SQL1：
    SELECT * FROM table WHERE a = 'a' AND d = 'd';
    //SQL2：
    SELECT * FROM table WHERE a = 'a' AND b = 'a' AND c = 'c' AND d > 'd';
    //SQL3：
    SELECT * FROM table WHERE a = 'a' AND b = 'a' AND c = 'c' AND id > 10;

答：  
SQL1：不能走索引合并，因为 a 字段属于 abc 联合索引的一部分，并没要保证每个联合索引都进行等值匹配。

SQL2：不能走索引合并，因为做范围查到 d 字段属于二级索引，非主键索引

SQL3：可以走索引合并，abc 联合索引满足锁全部等值匹配，id 为主键会走聚簇索引，最后只需要将 abc 的数据到 id 聚簇索引的返回数据上比对就有了

### (2) Union 合并 ###

对应SQL 中的 OR 场景（where条件是等值判断）

**使用场景：**

1.  OR 连接查询
2.  等值匹配（如果是联合索引，联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况）
3.  主键列可以是范围匹配（二级索引的范围查找无法使用索引合并）
4.  使用 Intersection 索引合并的搜索条件（Union 索引合并使用的是 Intersection 索引合并的结果，因此被合并的两方都要完全遵守索引合并原则）

**举例：**

*  a，b，c 联合索引
 *  d 二级索引

SELECT * 
    FROM table 
    WHERE 
    (a = 'a' AND b = 'a' AND c = 'c' AND d = 'd') 
    or 
    (d = 'c' and id > 10);

**原理：**

1.  MySQL 根据 abc 组合索引与 d 索引进行索引合并得出结果，此时主键有序
2.  MySQL d 索引与 id 聚簇索引进行索引合并得出结果，此时主键有序
3.  采用 Union 索引合并的方式把上述两个主键集合取并集，然后进行回表操作， 将结果返回给用户

### (3) Sort-Union 合并 ###

对应SQL 中的 OR 场景（where条件是范围查询）

**使用场景：**

1.  OR 连接查询
2.  范围查询走索引

**举例：**

*  a 二级索引
 *  b 二级索引

SELECT * 
    FROM table 
    WHERE 
    a > 'a' or b < 'b';

**原理：**

1.  由于 a 与 b 字段都是二级索引，因此无法使用 Union 合并，但是两个都有索引，因此 MySQL 做了优化
2.  根据 a 二级索引扫描区间获取数据，此时主键有序
3.  根据 b 二级索引扫描区间获取数据，此时主键有序
4.  把上述两个主键集合取并集，然后进行回表操作

# 二、连接查询优化策略 #

## 1. Join Buffer 优化 ##

**举例：**

前提条件：a 字段上有索引

SELECT * 
    FROM e1 
    LEFT JOIN e2 ON e1.m1 = e2.m2

**关联查询原理：**

从以下伪代码中可以看明白，MySQL 关联查询时通过这种递归的方式做的，e1 中的每天数据，都要和 e2 中的数据逐行比对。

这里就存在问题，比如 e2 的第一行记录，在 e1 的第一条记录比对完后，再 e1 的后续记录中还要继续比对。

要知道 e2 每次获取都是一页的数据，这样就导致 e1 中有多少条记录，e2 中该条数据所在的页就要获取多少次。能否优化？

#此处表示遍历满足对 e1 单表查询结果集中的每一条 记录，N 条
    for each row in e1 {
    	#此处表示对于某条 e1 表的记录来说，遍历满足 对 e2 单表查询结果集中的每一条记录，M 条 
    	for each row in e2 { 
    		#判断 e2 当前行数据，是否满足关联条件，满足将数据添加到返回列中
    		if row e1.m1 = e2.m2 {
    			send to client
    		}
    	} 
    }

**MySQL 优化策略：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTQzNjcz_size_16_color_FFFFFF_t_70 1]

MySQL 觉得，既然你 e2 表每页的数据，在 e1 表每行记录都要比对，那我 e1 表就批量比对。以此减少 e2 表去获取同样页的次数。

1.  MySQL 在内存中划分出一个 Join Buffer 缓冲区。
2.  缓冲区大小可通过 `join_buffer_size` 参数控制，默认大小为 262144 字节（也就是 256KB），最小可以设置为 128 字节。
3.  MySQL 会把驱动表，也就是 e1 表的数据尽可能读取到 Join Buffer 缓冲区里
4.  e2 表获取一页的数据后，MySQL 会标缓冲区中所有的 e1 表数据都与 e2 表比对是否符合要求。
5.  因此后续这些 e1 表的数据就不再需要比对了，因此大幅减少了 IO 次数。

# 三、 MySQL 查询重写 #

## 1. 条件化简 ##

### (1) 表达式计算 ###

MySQL 会对一些可以化简的表达式进行化简

**举例：**

select * 
    from table
    where
    a = 5 + 2

化简后

select * 
    from table
    where
    a = 7

### (2) 移除不必要的括号 ###

MySQL 会对 SQL 中多余的，无意义的括号进行清除

**举例：**

select * 
    from table
    where
    ((a = 5 AND b = c) 
    OR 
    ((a > c) AND (c < 5)))

化简后

select * 
    from table
    where
    (a = 5 and b = c) 
    OR 
    (a > c AND c < 5)

### (3) 常量传递 ###

有时候某个表达式是某个列和某个常量做等值匹配，如果其中的变量其实是个常量，MySQL 会进行优化

**举例：**

select * 
    from table
    where
    a = b and b = c and c = 5

化简后

select * 
    from table
    where
    a = 5 and b = 5 and c = 5

### (4) 移除没用的条件 ###

MySQL 会对一些无意义的表达式进行化简

**举例1：**

select * 
    from table
    where
    1 = 1 and a = 5

化简后

select * 
    from table
    where
    a = 5

**举例2：**

select * 
    from table
    where
    (a < 1 and b = b) OR (a = 6 OR 5 != 5)

化简过程

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

化简结果

select * 
    from table
    where
    a < 1 OR a = 6

### (5) 常量表检测 ###

如果优化器在分析一个查询语句时，认为聚簇索引或者主键索引查询都非常快，花费的时间特别少，少到可以忽略，所以也把通过这两种方式查询的表称之为常量表。

此时MySQL 就会先查询一个返回结果，再将常量替换，再进行查询。

**条件：** 使用主键等值匹配或者唯一性二级索引列等值匹配

**举例：**  
table2.column2 为唯一性二级索引

SELECT * 
    FROM table1 
    INNER JOIN table2 ON table1.column1 = table2.column2 
    WHERE table1.id = 1;

MySQL 先去对 table1 进行查询，可以看到此时 id 表会走聚簇索引，因此非常的快

SELECT * 
    FROM table1
    WHERE id = 1;

假如 table1 返回的字段中 column1 的值为 10。接下来会利用 table1 返回的常量对 SQL 进行替换

SELECT table1表记录的各个字段的常量值, table2.*
    FROM table1 
    INNER JOIN table2 ON 10 = table2.column2;

## 2. 外连接消除 ##

MySQL 会将能够优化的外连接，优化为内连接查询

**举例1：**

SELECT * 
    FROM table1 
    LEFT JOIN table2 ON table1.column1 = table2.column2 
    WHERE 
    table2.column2 IS NOT NULL

table1 表作为驱动表必然是全部数据

而由于 table2.column2 作为关联字段，因此 table2 关联出来的数据也不可能为 null

因此可知 `table2.column2 IS NOT NULL` 无意义

优化后

SELECT * 
    FROM table1 
    INNER JOIN table2 ON table1.column1 = table2.column2

**举例2：**

SELECT * 
    FROM table1 
    LEFT JOIN table2 ON table1.column1 = table2.column2 
    WHERE 
    table2.column2 = 10

table1 表作为驱动表必然是全部数据

而由于 table2.column2 作为关联字段，此时该字段在 where 中已知为 10

优化后

SELECT * 
    FROM table1 
    INNER JOIN table2 ON table1.column1 = table2.column2 
    WHERE 
    table2.column2 = 10

**举例3：**  
table2.m2 在表设计上不可为 NULL

SELECT * 
    FROM table1 
    LEFT JOIN table2 ON table1.column1 = table2.column2 
    WHERE 
    table2.m2 IS NOT NULL

优化后

SELECT * 
    FROM table1 
    INNER JOIN table2 ON table1.column1 = table2.column2

## 3. IN 子查询优化 ##

### (1) 物化表 ###

对于不相关的 IN 子查询，比如：

SELECT * 
     FROM s1 
     WHERE 
     order_note IN (SELECT order_note FROM s2 WHERE order_no = 'a');

可以看到 IN 内部的子查询与外部的完全无关

如果不进行优化的话，就是把外层查询和子查询当作两个独立的单表查询来对待

在数据量太大情况下就会产生问题：

1.  结果集太多，可能内存中都放不下
2.  对于外层查询来说，如果子查询的结果集太多，那就意味着 IN 子句中的参数特别多，这就导致：无法有效的使用索引，只能对外层查询进行全表扫描。

**MySQL 优化策略1：子查询物化为临时表**

MySQL 的改进是不直接将不相关子查询的结果集当作外层查询的参数，而是将结果集写入一个临时表里（子查询数据先存临时表）。

1.  该临时表的列就是子查询结果集中的列
2.  写入临时表的记录会被去重，临时表也是个表，只要为表中记录的所有 列建立主键或者唯一索引

假如子查询结果集不大，它会建立基于内存的使用 Memory 存储引擎的临时表，而且会为该表建立哈希索引。

假如子查询的结果集非常大，超过了系统变量 `tmp_table_size` 或者 `max_heap_table_size`，临时表会转而使用基于磁盘的存储引擎来保存结果集中的记录，索引类型也对应转变为 B+树索引。

**MySQL 优化策略2：对转化出来的物化表与主查询进行内连接查询**

也就是物化表会与外层 SQL 进行内连接查询

假设子查询物化表的名称为 materialized\_table

该物化表存储的子查询结果集的列为 m\_val

优化后：

SELECT s1.* 
    FROM s1 
    INNER JOIN materialized_table ON order_note = m_val;

### (2) 半连接 Semi-Join ###

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

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

**举例：**  
假设 s2 中存在这个一个 key2 列，列上有唯一性索引

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

**子查询上拉后**

再次提醒，子查询 s2 表的列表处只有 key2 字段，且 key2 字段为唯一索引。因此父表的一条记录与子表的记录形成了一对一对照关系

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

#### b. DuplicateWeedout execution strategy （重复值消除） ####

SELECT * FROM s1 
    WHERE order_note IN 
    (SELECT order_note FROM s2 WHERE order_no= 'a');

由于是 IN 查询，因此 s1 表的 order\_note 可能对应 s2 表的多条数据

但是这些多条数据是没有意义的对于 s1 表，因此 MySQL 想到了去重

为了去重，MySQL 需要使用临时表进行去重

接下来就简单了，首先创建一个临时表，并且以 order\_note 作为主键

s2 表的数据一条条进入临时表，如果成功代表不重复，失败代表重复

#### c. LooseScan execution strategy （松散扫描） ####

order\_no 为索引列

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

MySQL 会根据 order\_no 索引走 B+ 树进行范围扫描

接下来只要获取第一个 order\_note 的数据即可，后续相同的 order\_note 数据就不需要获取

### (3) IN 转为 EXISTS ###

这个就不需要多解释了

换句话说，如果不知道该用 IN 还是 EXISTS，那就用 IN 好了（MySQL 5.7 及以后）

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTQzNjcz_size_16_color_FFFFFF_t_70]: /images/20221020/8add8aa54bf3457d9646750b8c5d6f73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM3MTQzNjcz_size_16_color_FFFFFF_t_70 1]: /images/20221020/66d9f36151f7453e8be6d487f553b1fe.png