MySQL查询为什么选择使用这个索引？——基于MySQL 8.0.22索引成本计算

短命女 2022-09-11 00:28 106阅读 0赞

> 本文参考《MySQL是怎样运行的》，与书中不同的是，书中数据库版本`5.7.22`，本文使用`8.0.22`，不同版本数据库计算成本常数是不同的，书中是`1W`条记录，我这里是近`10W`条记录，经过实践，是对于书中的补充和验证，计算的成本和实际成本对比，让大家更容易理解`MySQL`为什么要使用这个索引。

### 文章目录 ###

*  1.什么是成本
 *   *  1.1 如何查看MySQL的成本常数
 *  2.单表查询的成本
 *   *  2.1 基于成本的优化步骤
 *  3.连接查询的成本(作为开发人员，必看)
 *   *  3.1 连接查询的准备
     *  3.2 条件过滤 (Condition filtering)
     *  3.3 两表连接的成本分析（这部分对开发人员写sql很重要）
     *  3.4 多表连接的成本分析（选读，了解）
     *  3.5 外连接和内连接等同的情况（外连接可转为内连接）

# 1.什么是成本 #

我们知道，`MySQL`查询会选择成本最低，或代价最低的那种方式去真正的执行查询。

`MySQL`的查询成本分为下面两个部分

*  **`I/O`成本**

我们的表经常使用的`MyISAM`、`InnoDB`存储引擎都是将数据和索引都存储到磁盘上的，当查询表中的记录时，需要先把数据或者索引加载到内存中，然后再进行操作。**这个从磁盘到内存的加载过程损耗的时间称为`I/O`成本。**

*  **`CPU`成本**

读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为`CPU`成本。

对于`InnoDB`存储引擎来说，页是磁盘和内存之间交互的基本单位，`MySQL`规定：读取一个页面花费的成本默认是`1.0`，读取以及检测一条记录是否符合搜索条件的成本默认是`0.1`(`MySQL 5.7.22`版本是`0.2`)。`1.0`、`0.1`这些数字称之为成本常数。

> 在读取记录时，即使不需要检测记录是否符合`where`条件，其成本都算是`0.1`，即访问一条记录所需的成本常数就是`0.1`，不管有无`where`条件。

## 1.1 如何查看MySQL的成本常数 ##

SELECT *  FROM mysql.server_cost;

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56CW5Lia5rSLX18_size_20_color_FFFFFF_t_70_g_se_x_16][]

从`server_cost`中的内容可以看出来，目前在`server`层的一些操作对应的成本常数有以下几种：

<table> 
 <thead> 
  <tr> 
   <th>成本常数名称</th> 
   <th>默认值 （括号中是MySQL5.7.22测出的值）</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>disk_temptable_create_cost</td> 
   <td>20.0 （40.0）</td> 
   <td>创建基于磁盘的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。</td> 
  </tr> 
  <tr> 
   <td>disk_temptable_row_cost</td> 
   <td>0.5 （1.0）</td> 
   <td>向基于磁盘的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。</td> 
  </tr> 
  <tr> 
   <td>key_compare_cost</td> 
   <td>0.05 （0.1）</td> 
   <td>两条记录做比较操作的成本，多用在排序操作上，如果增大这个值的话会提升filesort的成本，让优化器可能更倾向于使用索引完成排序而不是filesort。</td> 
  </tr> 
  <tr> 
   <td>memory_temptable_create_cost</td> 
   <td>1.0 （2.0）</td> 
   <td>创建基于内存的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。</td> 
  </tr> 
  <tr> 
   <td>memory_temptable_row_cost</td> 
   <td>0.1 （0.2）</td> 
   <td>向基于内存的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。</td> 
  </tr> 
  <tr> 
   <td><strong>row_evaluate_cost</strong></td> 
   <td><strong>0.1 （0.2）</strong></td> 
   <td><strong>这个就是我们上面说的检测一条记录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描。</strong></td> 
  </tr> 
 </tbody> 
</table>

SELECT * FROM mysql.engine_cost;

![d3e894c44a0049d3bbf85397b58d2f6d.png][]

<table> 
 <thead> 
  <tr> 
   <th>成本常数名称</th> 
   <th>默认值 （括号中是MySQL5.7.22测出的值）</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>io_block_read_cost</strong></td> 
   <td><strong>1.0 （1.0）</strong></td> 
   <td><strong>从磁盘上读取一个块对应的成本。</strong> 请注意我使用的是块，而不是页这个词儿。<strong>对于InnoDB存储引擎来说，一个页就是一个块</strong>，不过对于MyISAM存储引擎来说，默认是以4096字节作为一个块的。增大这个值会加重I/O成本，可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描。</td> 
  </tr> 
  <tr> 
   <td>memory_block_read_cost</td> 
   <td>0.25 （1.0）</td> 
   <td>与上一个参数类似，只不过衡量的是<strong>从内存中读取一个块对应的成本</strong>。</td> 
  </tr> 
 </tbody> 
</table>

`5.7.22`版本怎么从内存中和从磁盘上读取一个块的默认成本都是`1.0`呢？这主要是因为在`MySQL 5.7.22`的实现中，并不能准确预测某个查询需要访问的块中有哪些块已经加载到内存中，有哪些块还停留在磁盘上。所以`MySQL`很粗暴的认为不管这个块有没有加载到内存中，使用的成本都是`1.0`。

至于为什么在`8.0+` 版本中成本常数变小了呢？那是因为数据库的算法在不断的优化，能更加准确的预测块是否加载到内存中了。所以在不同数据库版本查看`sql`执行计划，选择的实际索引可能有所不同。

>   我们这里查询的是`mysql`库里面的`server_cost`和`engine_cost`表，在大公司中，一般人根本没权限查看这个`mysql`库的内容。因为`mysql`库相当重要，它存储了`MySQL`的用户账户和权限信息、一些存储过程和事件的定义信息、一些运行过程中产生的日志信息、一些帮助信息以及时区信息等等。所以大家可以在自己电脑自行验证，公司电脑可能限制了权限。

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

# 2.单表查询的成本 #

和前面几篇文章一样，首先列出建表语句，后面例子均在此表基础上举例说明。

CREATE TABLE demo_info(
        id INT NOT NULL auto_increment,
        key1 VARCHAR(100),
        key2 INT,
        key3 VARCHAR(100),
        key_part1 VARCHAR(100),
        key_part2 VARCHAR(100),
        key_part3 VARCHAR(100),
        common_field VARCHAR(100),
        PRIMARY KEY (id),
        KEY idx_key1 (key1),
        UNIQUE KEY uk_key2 (key2),
        KEY  idx_key3 (key3),
        KEY idx_key_part(key_part1, key_part2, key_part3)
    )ENGINE = INNODB CHARSET=utf8mb4;

## 2.1 基于成本的优化步骤 ##

在真正执行一条单表查询语句之前，`MySQL`的优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的**执行计划**，之后才会调用存储引擎提供的接口真正的执行查询。过程如下：

> 1.根据搜索条件，找出所有可能使用的索引  
> 2.计算全表扫描的代价  
> 3.计算使用不同索引执行查询的代价（先分析唯一索引、再普通索引）  
> 4.对比各种执行方案的代价，找出成本最低的那一个

接下来，我们来看一下这`4`个步骤的细节分析

用一个`sql`来具体分析下

explain 
    select * from demo_info where 
        key1 in ('a', 'b', 'c') and 
        key2 > 10 and key2 < 1000 and
        key3 > key2 and 
        key_part1 like '%hello%' and
        common_field = '123';

![0b0a194def80409db08c0faf6771d0ef.png][]

可以看到这里实际使用的索引是`uk_key2`，这个结果优化器是怎么计算出来的呢？来，一起分析！

**1.根据搜索条件，找出所有可能使用的索引**

*  `key1 IN ('a', 'b', 'c')`，这个搜索条件可以使用普通索引`idx_key1`。
 *  `key2 > 10 AND key2 < 1000`，这个搜索条件可以使用唯一索引`uk_key2`。
 *  `key3 > key2`，这个搜索条件的索引列由于没有和常数比较，因此不能产生合适的扫描区间。
 *  `key_part1 LIKE '%hello%'`，`key_part1`通过`LIKE`操作符和以通配符开头的字符串做比较，不可以适用索引。
 *  `common_field = '123'`，由于该列上压根儿没有索引，所以不会用到索引。

综上所述，上边的查询语句可能用到的索引（也就是`possible keys`）有`idx_key1`和`uk_key2`。

**2.计算全表扫描的代价**

对于`InnoDB`存储引擎来说，**全表扫描的意思就是把聚集索引中的记录都依次与给定的搜索条件进行比较，把符合搜索条件的记录加入到结果集中。** 所以需要将聚集引**对应的页面**加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本 = `I/O` 成本 + `CPU`成本，所以计算全表扫描的代价需要两个信息：

> 1.  聚集索引占用的页面数
> 2.  该表中的记录数

那这两个信息从哪里来呢？

可以用`show table status`语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的`like`语句就好了，比方说我们要查看`demo_info`这个表的统计信息可以这么写：

show table status like 'demo_info'

切换到表单视图方便查看

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56CW5Lia5rSLX18_color_FFFFFF_t_70_g_se_x_16][]

表中实际有`99896`条记录，与预估的是不同的。

虽然出现了很多统计项，但是我们只需要关注两个选项。

*  `Rows`

本选项表示表中的记录条数。对于使用`MyISAM`存储引擎的表来说，该值是准确的，对于使用`InnoDB`存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的`demo_info`表是使用`InnoDB`存储引擎的，尽管表实际有`99896`条记录，但是`show table status`显示的`Rows`值有`100548`条记录，**这个值比实际记录条数偏大偏小都有可能，总之对于`InnoDB`是不准确的。**

*  `Data_length`

表示表占用的存储空间字节数。使用`MyISAM`存储引擎的表来说，该值就是数据文件的大小，**对于使用`InnoDB`存储引擎的表来说，该值就相当于聚集索引占用的存储空间大小**，也就是说可以这样计算该值的大小：

> `Data_length` = 聚集索引的页面数量 x 每个页面的大小

我们的`demo_info`使用默认`16KB`的页面大小，而上边查询结果显示`Data_length`的值是`5783552`，所以可以反向来推导出聚集索引的页面数量：

> 聚簇索引的页面数量 = 5783552 ÷ 16 ÷ 1024 = 353

现在已经得到了聚集索引占用的页面数量以及该表记录数的估计值，接下来就可以计算全表扫描成本了，但是`MySQL`代码中有一些硬编码进行成本的微调，微调值没有注释，含义无从得知，但是不影响后续分析。

看一下全表扫描成本的计算过程

*  **`I/O`成本**

353 x 1.0 + 1.1 = 354.1

`353`指的是聚集索引占用的页面数，`1.0`指的是加载一个页面的成本常数，后边的`1.1`是一个微调值，我们不用在意。

*  **`CPU`成本：**

100548 x 0.1 + 1.0 = 10055.8

`100548`指的是统计数据中表的记录数(看`Rows`列)，对于`InnoDB`存储引擎来说是一个估计值，`0.1`指的是访问一条记录所需的成本常数，后边的`1.0`是一个微调值，我们不用在意。

总成本：

354.1 + 10055.8 = 10409.9

综上所述，对于`demo_info`的全表扫描所需的总成本就是`10409.9`。

>   完整的用户记录其实都存储在聚集索引对应`B+`树的叶子节点中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。也就是说全表扫描这个过程其实有的`B+`树非叶子节点是不需要访问的（即有的目录页是不需要访问的），但是`MySQL`在计算全表扫描成本时直接使用聚集索引占用的页面数（包含所有目录页）作为计算`I/O`成本的依据，是不区非叶子节点和叶子节点的，有点简单粗暴，大家注意一下。

**3.计算使用不同索引执行查询的代价**

从第`1`步分析我们得到，上述查询可能使用到`idx_key1`和`uk_key2`这两个索引，我们需要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。**`MySQL`查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本**，所以我们也先分析`uk_key2`的成本，然后再看使用`idx_key1`的成本。

**(1) 使用`uk_key2`执行查询的成本分析**

`uk_key2`对应的搜索条件是：`key2 > 10 AND key2 < 1000`，也就是说对应的范围区间就是：`(10, 1000)`，使用`uk_key2`执行查询的步骤如下

1.先从`uk_key2`索引中定位`key2`在`(10, 1000)`这个区间内的记录，找到记录对应的`id`列的值

2.再回表到聚集索引中根据上一步得到的`id`值，找到完整的用户记录

对于使用非聚集索引 + 回表方式的查询，`MySQL`查询的成本依赖两个方面的数据：扫描区间的数量和需要回表的记录数

*  **扫描区间的数量**

查询优化器粗暴的认为**读取索引的一个扫描区间的`I/O`成本和读取一个页面的`I/O`成本是相同的**。本例中使用`uk_key2`的扫描区间只有一个：`(10, 1000)`，**无论该扫描区间的非聚集索引有多少条(可能是很大的区间几十万条)、到底占用了多少页面，都只认为和读取一个页面的`I/O`成本一样（有多少个扫描区间，相当于和读对应数量的页的`I/O`成本一样）**，所以相当于访问这个扫描区间的非聚集索引付出的`I/O`成本就是：

1 x 1.0 = 1.0 （有几个扫描区间，就相当于要计算读几个页面的成本，这点容易忽视）

其中，`1`是一个扫描区间，`1.0`是表示读取一个页面的`I/O`成本

*  **需要回表的记录数**

查询优化器需要计算非聚集索引的某个扫描区间到底包含多少条记录，对于本例来说就是要计算`uk_key2`在`(10, 1000)`这个扫描区间中包含多少非聚集索引记录，计算过程是这样的：

步骤1：先根据`key2 > 10`这个条件访问一下`uk_key2`对应的`B+`树索引，找到满足`key2 > 10`这个条件的第一条记录（我们把这条记录称之为区间最左记录）。在`B+`数树中定位一条记录是非常快的，是常数级别的，这个过程的性能消耗是可以忽略不计。

步骤2：然后再根据`key2 < 1000`这个条件继续从`uk_key2`对应的`B+`树索引中找出最后一条满足这个条件的记录（我们把这条记录称之为区间最右记录），这个过程的性能消耗同样可以忽略不计的。

步骤3：如果区间最左记录和区间最右记录相隔不太远（在`MySQL 5.7.22`这个版本里，只要相隔不大于`10`个页面即可），就可以精确统计出满足`key2 > 10 AND key2 < 1000`条件的二级索引记录条数。否则需要沿着区间最左记录向右读`10`个页面，计算每个页面平均包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。

>   数据页结构中有一个`Page Header`的部分，`Page Header`中有一个`PAGE_N_RECS`的属性，表示该页面中目前有多少条记录。所以，在区间不太远的情况下，我们可以把每个页面中的`PAGE_N_RECS`属性值加起来就好了，而不用依次遍历每条记录再统计。

那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？解决这个问题还得回到`B+`树索引的结构中来：

![21796b258e474ee6b90d427f64f38fff.png][]

如图，我们假设区间最左记录在`页b`中，区间最右记录在`页c`中，那么计算区间最左记录和区间最右记录之间的页面数量就是计算`页b`和`页c`之间有多少页面，此时只需要计算`页b`和`页c`的父级目录页之间隔着几条记录即可，在一个页面中统计两条记录之间有几条记录的成本就相当低了。

如果`页b`和`页c`之间的页面实在太多，以至于`页b`和`页c`对应的目录项记录都不在一个页面中，则继续递归到父级目录的父级目录，再统计所在页面之间有多少个页面。一个`B+`树有`4`层高已经很了不得了，所以这个统计过程也不是很耗费性能。

知道了如何统计非聚集索引某个扫描区间的记录数之后，让我们回到具体问题中来，就是要找到需要回表的记录数，根据上述算法测得`uk_key2`在区间`(10, 1000)`之间有`883`条记录（我的表中查出来就是`883`条记录）。读取这`883`条二级索引记录需要付出的 **`CPU`成本** 就是：

883 x 0.1 + 0.01 = 88.31

其中`883`是需要读取的非聚集索引记录条数，`0.1`是读取一条记录成本常数，`0.01`是微调。

**在通过非聚集索引获取到记录之后，还有`2`个步骤：**

*  **1.回表，根据这些记录里的主键值到聚集索引中执行回表操作**

`MySQL`评估回表操作的`I/O`成本依旧很粗略，**`MySQL`认为每次回表操作都相当于访问一个页面，也就是说非聚集索引扫描区间中有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面`I/O`**。前面在使用`uk_key2`唯一索引执行查询时，有`883`条非聚集索引记录需要进行回表操作，所以回表操作带来的 **`I/O`成本** 就是：

883 x 1.0 = 883.0

其中`883`是预计的非聚集索引记录数，`1.0`是一个页面的`I/O`成本常数。

*  **2.回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立**

回表操作的本质就是通过二级索引记录的主键值到聚集索引中找到完整的用户记录，然后再检测除`key2 > 10 AND key2 < 1000`这个搜索条件以外的搜索条件是否成立。因为我们通过扫描区间获取到非聚集索引记录共`883`条，也就对应着聚集索引中`883`条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件的`CPU`成本为

883 \* 0.1 = 88.3

其中`883`是待检测的记录的条数，`0.1`是检测一条记录是否符合给定搜索条件的成本常数。

所以本例使用`uk_key2`执行查询成本如下：

`I/O`成本: 1.0 + 883 \* 1.0 = 884.0 (1个扫描区间的成本 + 非聚集索引回表的成本)

`CPU`成本: 883 \* 0.1 + 0.01 + 883 \* 0.1 = 176.61（根据搜索条件读取非聚集索引的成本 + 回表后根据搜索条件读取聚集索引的成本）

综上，使用`uk_key2`执行查询的总成本就是`884.0 + 176.61 = 1060.61`

> 注意：方便记忆法：若走索引，读非聚集索引条件有微调，读聚集索引没有，扫描区间、回表任何一条记录都相当于读取一页。若不走索引，微调值另外分析，和前者不同。

怎么验证？  
直接上代码，不用废话，来个华丽的验证

explain format=json
    select * from demo_info where 
        key1 in ('a', 'b', 'c') and 
        key2 > 10 and key2 < 1000 and
        key3 > key2 and 
        key_part1 like '%hello%' and
        common_field = '123';

运行结果

{
      "query_block": {
        "select_id": 1,
        "cost_info": {
          "query_cost": "1060.61" ========================看见没？看我，我就是成本，好好看，仔细看
        },
        "table": {
          "table_name": "demo_info",
          "access_type": "range",
          "possible_keys": [
            "uk_key2",
            "idx_key1"
          ],
          "key": "uk_key2",
          "used_key_parts": [
            "key2"
          ],
          "key_length": "5",
          "rows_examined_per_scan": 883,
          "rows_produced_per_join": 1,
          "filtered": "0.19",
          "index_condition": "((`i-me`.`demo_info`.`key2` > 10) and (`i-me`.`demo_info`.`key2` < 1000))",
          "cost_info": {
            "read_cost": "1060.45",
            "eval_cost": "0.16",
            "prefix_cost": "1060.61",
            "data_read_per_join": "3K"
          },
          "used_columns": [
            "id",
            "key1",
            "key2",
            "key3",
            "key_part1",
            "key_part2",
            "key_part3",
            "common_field"
          ],
          "attached_condition": "((`i-me`.`demo_info`.`key1` in ('a','b','c')) and (cast(`i-me`.`demo_info`.`key3` as double) > cast(`i-me`.`demo_info`.`key2` as double)) and (`i-me`.`demo_info`.`key_part1` like '%hello%') and (`i-me`.`demo_info`.`common_field` = '123'))"
        }
      }
    }

截个图代表结果不是我手动改的，计算和验证结果一样！！

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56CW5Lia5rSLX18_size_20_color_FFFFFF_t_70_g_se_x_16 1][]

**（2）使用`idx_key1`执行查询的成本分析**

`idx_key1`对应的搜索条件是：`key1 IN ('a', 'b', 'c')`，也就是说相当于`3`个单点区间：`['a', 'a']，['b', 'b']，['c', 'c']`  
与使用`uk_key2`的情况类似，我们也需要计算使用`idx_key1`时需要访问的范围区间数量以及需要回表的记录数：

*  扫描区间数量

使用`idx_key1`执行查询时很显然有`3`个单点扫描区间，所以访问这`3`个扫描间的非聚集索引付出的`I/O`成本就是：  
3 x 1.0 = 3.0

*  需要回表的记录数

每个单点区间都需要查找一遍对应的非聚集索引记录数：

> 查找单点区间`['a', 'a']`对应的非聚集索引记录数
> 
>   计算单点区间对应的非聚集索引记录数和计算连续范围区间对应的非聚集索引记录数是一样的，都是先计算区间最左记录和区间最右记录，然后再计算它们之间的记录数，具体算法上边已经说过。最后计算得到单点区间`['a','a']`对应的非聚集索引记录数是：`99861`。

> 查找单点区间`['b', 'b']`对应的二级索引记录数
> 
> 与上同理，计算得到本单点区间对应的记录数是：`0`。

> 查找单点区间`['c', 'c']`对应的二级索引记录数
> 
> 与上同理，计算得到本单点区间对应的记录数是：`1`。

由于我造数据有点特殊，几乎所有记录都在`['a', 'a']`扫描区间内，但是不影响分析。

所以，这三个单点区间总共需要回表的记录数就是：99861 + 0 + 1 = 99862，读取这些非聚集索引记录的`CPU`成本就是：99862 \* 0.1 + 0.01 = 9986.21

得到总共需要回表的记录数之后，还要继续考虑：

*  根据这些记录中的主键值到聚集索引中执行回表操作，所需的`I/O`成本就是：
    
    99862 x 1.0 = 99862.0 （每次回表相当于访问一个页面）
 *  针对回表操作后得到的完整用户记录，比较其他搜索条件是否成立。这一步骤对应的`CPU`成本  
    99862 \* 0.1 = 9986.2

所以本例中使用`idx_key1`执行查询的成本就如下所示：

*  `I/O`成本：3.0 + 99862 x 1.0 = 99865.0 (范围区间的数量 + 预估的二级索引记录条数)
 *  `CPU`成本：99862 x 0.1 + 0.01 + 99862 x 0.1 = 19972.41 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

综上所述，使用`idx_key1`执行查询的总成本就是：

99865.0 + 19972.41 = 119837.41

这成本远大于全表扫描，所以如果只有`idx_key1`索引，那么优化器一定会选择全表扫描。

**（3）是否有可能使用索引合并（`Index Merge`）**

本例中有关`key1`和`key2`的搜索条件是使用`AND`连接起来的，而对于`idx_key1`和`uk_key2`都是范围查询，也就是说查找到的非聚集索引记录并不是按照主键值进行排序的，并不满足使用`Intersection`索引合并的条件，所以并不会使用索引合并。 `MySQL`查询优化器计算索引合并成本的算法也比较麻烦，这里不讲，理解成本如何计算，知道`MySQL`会按照这种算法选择索引即可。

**4. 对比各种执行方案的代价，找出成本最低的那一个**  
全表扫描成本：10409.9  
使用`uk_key2`的成本：1060.61  
使用`idx_key1`的成本：119837.41

综上，优化器会选择`uk_key2`索引执行查询

> 在这个例子中，不管采用`idx_key1`和`uk_key2`执行查询，它们对应的都是`range`方法。在使用`range`访问方法执行查询的时候，扫描区间中包含多少条记录，优化器就认为需要进行多少次回表操作，也就相当于需要进行多少次页面`I/O`.

> 如果是`ref`方法，`MySQL`就给因为回表操作带来的`I/O`成本设置了天花板，也就是`ref`访问方法因回表操作带来的`I/O`成本最多不能超过访问**全表记录数** `÷ 10`的页面`I/O`成本或者**全表扫描**的`I/O`成本的`3`倍。

实际中，我们想分析`MySQL`为什么选择这个索引，直接如下例子，强制索引后分析成本，根本不用自己手动计算，本文是给大家分析，让大家理解思路。

explain format=json
    	select * from demo_info 
    	- 强制索引分析成本
    	force index(idx_key1)
    	where 
        key1 in ('a', 'b', 'c') and 
        key2 > 10 and key2 < 1000 and
        key3 > key2 and 
        key_part1 like '%hello%' and
        common_field = '123';

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

# 3.连接查询的成本(作为开发人员，必看) #

## 3.1 连接查询的准备 ##

我们直接构造一个和`demo_info`表一模一样的`demo_info2`表。为了简便起见，我们把`demo_info`表称为`d1`表，把`demo_info2`表称为`d2`表。

## 3.2 条件过滤 (Condition filtering) ##

我们上一篇说过，`MySQL`中连接查询采用的是嵌套循环连接算法，驱动表只访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：

*  单次查询驱动表的成本
 *  多次查询被驱动表的成本（每查询到一条驱动表记录，就会去查询被驱动表，所以具体查询多少次取决于驱动表查询的结果集中有多少条记录）

**我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（`fanout`）**。显然，驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值，有的时候扇出值的计算是很容易的，比如下面这两个查询：

*  查询一：

SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2;

假设使用`d1`表作为驱动表，很显然就只能使用全表扫描的方式对驱动表执行单表查询，驱动表中有多少记录，扇出值就是多少。我们前边说过，`show table status like 'demo_info'`统计数据中`d1`表的记录行数是`100548`，也就是说优化器就直接会把`100548`当作在`d1`表的扇出值。没有连接条件的表连接查询会产生笛卡尔积，一般都会写条件。

> **  为什么我们分析内连接老是假设驱动表？难道左表不是驱动表？不一定，内连接左右表顺序可以任意互换，优化器会优化其连接顺序的。后面会分析，优化器会计算成本后再确定哪个做为驱动表。**

*  查询二：

SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2 
    WHERE d1.key2 >10 AND d1.key2 < 1000;

仍然假设`d1`表是驱动表的话，很显然对驱动表的单表查询可以使用`uk_key2`索引执行查询。此时`uk_key2`的扫描区间`(10, 1000)`中有多少条记录，那么扇出值就是多少。我们前边计算过，满足`uk_key2`的扫描区间`(10, 1000)`的记录数是`883`条，也就是说本查询中优化器会把`883`当作驱动表`d1`的扇出值。

> 有的时候扇出值的计算非常困难，比如这两种情况下计算驱动表扇出值时需要靠 ‘猜’：
> 
>  *  如果使用全表扫描的方式执行单表查询，那么计算驱动表扇出值的时候需要猜测满足全部搜索条件的记录有多少条。
>  *  如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜测除了满足形成索引扫描区间的搜索条件外，还满足其他搜索条件的记录有多少条。

例子如下：

*  查询三：

SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2 
        WHERE d1.common_field > 'xyz';

本查询和查询一类似，只不过对于驱动表`d1`多了一个`common_field > 'xyz'`的搜索条件。查询优化器又不会真正的去执行查询，所以它只能猜这`100548`记录里有多少条记录满足`common_field > 'xyz'`条件。

*  查询四：

SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2 
        WHERE d1.key2 > 10 AND d1.key2 < 1000 AND
              d1.common_field > 'xyz';

本查询和查询二类似，只不过在查询驱动表`d1`也多了一个`common_field > 'xyz'`的搜索条件。不过因为本查询可以使用`uk_key2`索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合`common_field > 'xyz'`条件，也就是只需要在`883`条记录中猜测有多少条记录符合`common_field > 'xyz'`条件。

*  查询五：

SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2 
        WHERE d1.key2 > 10 AND d1.key2 < 1000 AND
              d1.key1 IN ('a', 'b', 'c') AND
              d1.common_field > 'xyz';

本查询和查询二类似，不过在驱动表`d1`选取`uk_key2`索引执行查询后，优化器需要在非聚集索引扫描区间的记录中猜有多少条记录符合下边两个条件：

key1 IN ('a', 'b', 'c')
    
    common_field > 'xyz'

也就是优化器需要猜在`883`条记录中有多少符合上述两个条件的。

>   这个猜的过程称之为`condition filtering`。当然，这个过程可能会使用到索引，也可能使用到统计数据，评估过程很复杂，我也不知道。引用书中的话说，这个`condition filtering`的功能，就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。 设计`MySQL`的大叔们称之为启发式规则（`heuristic`），大家有兴趣的可以再深入了解一下哈。

## 3.3 两表连接的成本分析（这部分对开发人员写sql很重要） ##

连接查询的成本计算公式是这样的：

> 连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出值 x 单次访问被驱动表的成本

对于**左（外）连接和右（外）连接**查询来说，它们的**驱动表是固定**的，左连接驱动表是左表，右连接驱动表是右表。只需要分别为驱动表和被驱动表选择成本最低的访问方法，就可以得到最优的查询方案。

可是对于**内连接**来说，**驱动表和被驱动表的位置是可以互换**的，所以需要考虑两个方面的问题：

*  不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。
 *  然后分别为驱动表和被驱动表选择成本最低的访问方法。

很显然，**计算内连接查询成本的方式更麻烦一些**，下边我们就以内连接为例来看看如何计算出最优的连接查询方案。

> 左（外）连接和右（外）连接查询在某些特殊情况下可以被优化为内连接查询，我们在后面的文章会提到。

来来来，看例子，用例子说话

SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2 
        ON d1.key1 = d2.common_field 
        WHERE d1.key2 > 10 AND d1.key2 < 1000 AND 
              d2.key2 > 1000 AND d2.key2 < 2000;

**首先要告诉大家的是，连接、搜索条件写在`on`和写在`where`是没有性能上的区别的，优化器会自动优化，之所以分开写，完全是因为这样可读性好而不是性能原因，请一定记住这句话。**

可以选择的内连接顺序有两种：

*  `d1`连接`d2`，也就是`d1`作为驱动表，`d2`作为被驱动表。
 *  `d2`连接`d1`，也就是`d2`作为驱动表，`d1`作为被驱动表。

查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。我们分别来看一下（定性分析）：

**1.使用`d1`作为驱动表**

分析对于驱动表的成本最低的执行方案，看一下涉及`d1`表单表的搜索条件有哪些：

*  `d1.key2 > 10 AND d1.key2 < 1000`

这个查询可能使用到`uk_key2`索引，从全表扫描和使用`uk_key2`这两个方案中选出成本最低的那个，这个过程我们上边说过了，很显然使用`uk_key2`执行查询的成本更低些。

然后分析对于被驱动表的成本最低的执行方案，此时涉及被驱动表`d2`的连接搜索条件就是：

*  `d2.common_field` = 常数（这对于`d2`只能全表扫描了）
 *  `d2.key2 > 1000 AND d2.key2 < 2000` （可以用到`uk_key2`索引）

很显然，第一个条件由于`common_field`没有用到索引，此时访问`d2`表时可用的方案也是全表扫描和使用`uk_key2`两种，显然使用`uk_key2`的成本更小。

所以此时使用`d1`作为驱动表时的总成本就是（暂时不考虑使用`join buffer`对成本的影响）：

> 使用`uk_key2`访问`d1`的成本 + `d1`的扇出 × 使用`uk_key2`访问`d2`的成本

**2.使用`d2`作为驱动表**

分析对于驱动表的成本最低的执行方案，看一下涉及`d2`表单表的搜索条件有哪些：

*  `d2.key2 > 1000 AND d2.key2 < 2000`

所以这个查询可能使用到`uk_key2`索引，从全表扫描和使用`uk_key2`这两个方案中选出成本最低的那个，显然使用`uk_key2`执行查询的成本更低些。

然后分析对于被驱动表的成本最低的执行方案，此时涉及被驱动表`d1`的搜索条件就是：

*  `d1.key1` = 常数
 *  `d1.key2 > 10 AND d1.key2 < 2000`

这时就很有趣了，使用`idx_key1`可以进行`ref`方式的访问，使用`uk_key2`可以使用`range`方式的访问。这是优化器需要从全表扫描、使用`idx_key1`、使用`uk_key2`这几个方案里选出一个成本最低的方案。

这里有个问题，因为`uk_key2`的范围区间是确定的：`(10, 1000)`，怎么计算使用`uk_key2`的成本我们上边已经说过了，可是在没有真正执行查询前，`d1.key1` = 常数中的常数值我们是不知道的，一般情况下，`ref`的访问方式要比`range`成本更低，但是上面分析过，使用`uk_key2`访问`d1`成本是更低的。

所以此时使用`d2`作为驱动表时的总成本就是：

> 使用`uk_key2`访问`d2`的成本 + `d2`的扇出 × 使用`uk_key2`访问`d1`的成本

**最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。** 从上边的计算过程也可以看出来，连接查询成本 **“占大头”** 的其实是**驱动表扇出数 x 单次访问被驱动表的成本**，所以我们的优化重点其实是下边这两个部分：

*  尽量减少驱动表的扇出（小表作为驱动表）
 *  对被驱动表的访问成本尽量低 （好的索引很重要）

这一点对于我们实际书写连接查询语句时十分有用，我们需要**尽量在被驱动表的连接列上建立索引**，这样就可以使用`ref`访问方法来降低访问被驱动表的成本了。**如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。**

**所谓的大表还是小表，得等到条件过滤之后记录数的多少才能区分大表还是小表，而不是只看表里总记录数量来判断大表小表。**

## 3.4 多表连接的成本分析（选读，了解） ##

在分析多表连接的成本之前，首先要考虑一下多表连接时可能产生出多少种连接顺序：

*  对于两表连接，比如`表A`和`表B`连接，只有 `AB`、`BA`这两种连接顺序。（其实相当于`2 × 1 = 2`种连接顺序）
 *  对于三表连接，比如`表A`、`表B`、`表C`进行连接，有`ABC`、`ACB`、`BAC`、`BCA`、`CAB`、`CBA`这`6`种连接顺序。（其实相当于`3 × 2 × 1 = 6`种连接顺序）
 *  对于四表连接，则会有`4 × 3 × 2 × 1 = 24`种连接顺序。
 *  对于`n`表连接的话，则有 `n × (n-1) × (n-2) × ··· × 1`种连接顺序，就是`n`的阶乘（`n!`）种连接顺序。

有n个表进行连接，MySQL查询优化器要每一种连接顺序的成本都计算一遍么？不会！MySQL有很多办法减少因计算不同连接顺序下的查询成本带来的性能损耗。

*  提前结束某种顺序的成本评估

`MySQL`在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。比如说`A`、`B`、`C`三个表进行连接，已经得到连接顺序`ABC`是当前的最小连接成本（假设为`10.0`），在计算连接顺序`BCA`时，发现`B`和`C`的连接成本就已经大于`10.0`时，就不再继续往后分析连接`A`的成本了。

*  系统变量`optimizer_search_depth`

为了防止无穷无尽的分析各种连接顺序的成本，`MySQL`有一个`optimizer_search_depth`系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与`optimizer_search_depth`值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到的就不是很好的执行计划，但可以省掉了连接成本的分析时间。

*  某些规则压根儿就不考虑某些连接顺序

即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以`MySQL`有一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量`optimizer_prune_level`来控制是否使用这些启发式规则。

## 3.5 外连接和内连接等同的情况（外连接可转为内连接） ##

外连接和内连接在某种情况下是等价的，也就是说可以相互转换的，写`[inner] join`或者`left | right join` 效果一样。只要在`where`子句的搜索条件中指定被驱动表的列不为`NULL`，最后的结果集就和内连接结果集一样。

例子如下

SELECT * FROM demo_info AS d1 LEFT JOIN demo_info2 AS d2 
        ON d1.key1 = d2.common_field 
        WHERE d2.key_part2 is not null;

因为指定了被驱动表`d2`表的`key_part2`列不允许为`NULL`，所以`d1`和`d2`的左连接和内连接查询是一样的。当然，我们也可以不用显式指定被驱动表的某个列 `is not null`，只要隐含就行，比如

SELECT * FROM demo_info AS d1 LEFT JOIN demo_info2 AS d2 
        ON d1.key1 = d2.common_field 
        WHERE d2.key_part2 = 2;

在`where`子句指定了被驱动表`d2`的`key_part2`列等于`2`，那就肯定不为`null`了，这和下面内连接语句是等价的。

SELECT * FROM demo_info AS d1 JOIN demo_info2 AS d2 
        ON d1.key1 = d2.common_field 
        WHERE d2.key_part2 = 2;

我们把这种在外连接查询中，指定`where`子句中包含被驱动表中的列不为`null`值的条件称为**空值拒绝**（`reject-NULL`），**在被驱动表的`where`子句符合空值拒绝的条件后，外连接和内连接可以相互转换。** 这种转换带来的好处就是优化器可以通过评估表的不同顺序的成本，选出最低成本的连接顺序进行查询。

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

欢迎一键三连~  
  
有问题请留言，大家一起探讨学习  
  
\----------------------Talk is cheap, show me the code-----------------------

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56CW5Lia5rSLX18_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/87fbbf5b27aa45a69e96b4c2c7dbfc73.png
[d3e894c44a0049d3bbf85397b58d2f6d.png]: /images/20220829/e22361b6f5a7437594ec188b42b2ce17.png
[0b0a194def80409db08c0faf6771d0ef.png]: /images/20220829/2ed20c065bc1420589171a0157ac51e7.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56CW5Lia5rSLX18_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/15cd386dd8894392af833669f5c9809e.png
[21796b258e474ee6b90d427f64f38fff.png]: /images/20220829/dd4fb86d6fc14fa29c368b060931bf7c.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA56CW5Lia5rSLX18_size_20_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220829/da5ca25277c8466caa666ff85f69de50.png