Mysql——》查询成本

╰半夏微凉° 2024-03-17 21:33 96阅读 0赞

> 推荐链接：  
> [总结——》【Java】][Java]  
> [总结——》【Mysql】][Mysql]  
> [总结——》【Redis】][Redis]  
> [总结——》【Kafka】][Kafka]  
> [总结——》【Spring】][Spring]  
> [总结——》【SpringBoot】][SpringBoot]  
> [总结——》【MyBatis、MyBatis-Plus】][MyBatis_MyBatis-Plus]  
> [总结——》【Linux】][Linux]  
> [总结——》【MongoDB】][MongoDB]  
> [总结——》【Elasticsearch】][Elasticsearch]

#### Mysql——》查询成本 ####

*  一、成本的概念
 *   *  （1）I/O成本
     *  （2）CPU成本
 *  二、成本常数
 *   *  1、查看成本常数
     *   *  （1）mysql.server\_cost
         *  （2）mysql.engine\_cost
     *  2、修改成本常数
     *  3、修改成本常数使用默认值
 *  三、单表查询的成本
 *   *  1、根据搜索条件，找出所有可能使用的索引
     *  2、计算全表扫描的代价
     *   *  （1）I/O成本
         *  （2）CPU成本
         *  （3）总成本
     *  3、计算使用不同索引执行查询的代价
     *   *  （1）使用idx\_order\_no执行查询的成本分析
         *  （2）使用idx\_expire\_time执行查询的成本分析
         *  （3）是否有可能使用索引合并（Index Merge）
     *  4、对比各种方案，找出成本最低的那一个
 *  三、Explain与查询成本
 *  四、Optimizer Trace与查询成本
 *  五、表连接查询的成本
 *   *  1、Condition filtering
     *   *  （1）MySQL 5.7之前版本，查询优化器计算驱动表扇出
         *  （2）MySQL 5.7版本，查询优化器计算驱动表扇出
     *  2、两表连接的成本分析
     *   *  （1）外连接：左（外）连接和右（外）连接
         *  （2）内连接
     *  3、多表连接的成本分析
     *   *  （1）多表连接可能产生的连接顺序
         *  （2）减少计算多种连接顺序的成本的方法
         *   *  （2.1）提前结束某种顺序的成本评估
             *  （2.2）系统变量optimizer\_search\_depth
             *  （2.3）根据某些规则压根儿就不考虑某些连接顺序

## 一、成本的概念 ##

MySQL执行一个查询可以有不同的执行方案，它会选择其中**成本（代价）最低**的方案去执行查询。  
一个查询语句的执行成本 = **I/O成本 + CPU成本。**

### （1）I/O成本 ###

> MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。  
>   
>   
> 对于InnoDB存储引擎，**页**是**磁盘和内存之间交互**的基本单位。

**从磁盘加载内存**的过程损耗的时间称之为**I/O成本**。

### （2）CPU成本 ###

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

## 二、成本常数 ##

一个查询语句的执行其实是分为两层的：**server层**、**存储引擎层**，分别对应不同的常数表。

<table> 
 <thead> 
  <tr> 
   <th></th> 
   <th align="left">server层</th> 
   <th align="left">存储引擎层</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>功能</strong></td> 
   <td align="left">进行连接管理、查询缓存、语法解析、查询优化等操作（与存储引擎无关）</td> 
   <td align="left">执行具体的数据存取操作</td> 
  </tr> 
  <tr> 
   <td><strong>对应常数表</strong></td> 
   <td align="left">mysql.server_cost</td> 
   <td align="left">mysql.engine_cost</td> 
  </tr> 
  <tr> 
   <td><strong>常用成本常数</strong></td> 
   <td align="left">row_evaluate_cost<br>功能：检测一条记录是否符合搜索条件的成本<br>默认：0.2<br>注意：不管读取记录时需不需要检测是否满足搜索条件，哪怕是空数据，其成本都算是0.2。</td> 
   <td align="left">io_block_read_cost<br>功能：读取一个页面花费的成本<br>默认：1.0</td> 
  </tr> 
 </tbody> 
</table>

### 1、查看成本常数 ###

-- 查看server对应的所有成本常数
    SELECT * FROM mysql.server_cost;
    
    -- 查看存储引擎对应的所有成本常数
    SELECT * FROM mysql.engine_cost;

<table> 
 <thead> 
  <tr> 
   <th><strong>列</strong></th> 
   <th align="left"><strong>描述</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>engine_name</strong></td> 
   <td align="left">表示成本常数适用的存储引擎名称。如果值为default，表示适用于所有的存储引擎。</td> 
  </tr> 
  <tr> 
   <td><strong>device_type</strong></td> 
   <td align="left">表示存储引擎使用的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘，不过在MySQL 5.7.X这个版本中并没有对机械硬盘的成本和固态硬盘的成本作区分，所以该值默认是0。</td> 
  </tr> 
  <tr> 
   <td><strong>cost_name</strong></td> 
   <td align="left">表示成本常数的名称</td> 
  </tr> 
  <tr> 
   <td><strong>cost_value</strong></td> 
   <td align="left">表示成本常数对应的值。如果值为NULL，表示采用默认值。</td> 
  </tr> 
  <tr> 
   <td><strong>last_update</strong></td> 
   <td align="left">表示最后更新记录的时间。</td> 
  </tr> 
  <tr> 
   <td><strong>comment</strong></td> 
   <td align="left">注释。</td> 
  </tr> 
  <tr> 
   <td><strong>default_value</strong></td> 
   <td align="left">表示成本常数对应的默认值。</td> 
  </tr> 
 </tbody> 
</table>

#### （1）mysql.server\_cost ####

![image.png][]

<table> 
 <thead> 
  <tr> 
   <th><strong>成本常数</strong></th> 
   <th align="left"><strong>描述</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>disk_temptable_create_cost</strong></td> 
   <td align="left">创建基于磁盘的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。</td> 
  </tr> 
  <tr> 
   <td><strong>disk_temptable_row_cost</strong></td> 
   <td align="left">向基于磁盘的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。</td> 
  </tr> 
  <tr> 
   <td><strong>key_compare_cost</strong></td> 
   <td align="left">两条记录做比较操作的成本，多用在排序操作上，如果增大这个值的话会提升filesort的成本，让优化器可能更倾向于使用索引完成排序而不是filesort。</td> 
  </tr> 
  <tr> 
   <td><strong>memory_temptable_create_cost</strong></td> 
   <td align="left">创建基于内存的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。</td> 
  </tr> 
  <tr> 
   <td><strong>memory_temptable_row_cost</strong></td> 
   <td align="left">向基于内存的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。</td> 
  </tr> 
  <tr> 
   <td><strong>row_evaluate_cost</strong></td> 
   <td align="left">检测一条记录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描。</td> 
  </tr> 
 </tbody> 
</table>

> **Q：什么场景会创建临时表？**  
> A：执行以下查询都可能在内部先创建一个临时表：
> 
> 1.  DISTINCT查询
> 2.  分组查询
> 3.  Union查询
> 4.  某些特殊条件下的排序查询  
>       
>       
>     **数据量大**：创建**基于磁盘**的临时表，使用MyISAM、InnoDB等存储引擎  
>     **数据量小**：创建**基于内存**的临时表，使用Memory存储引擎

#### （2）mysql.engine\_cost ####

![image.png][image.png 1]

<table> 
 <thead> 
  <tr> 
   <th><strong>成本常数</strong></th> 
   <th align="left"><strong>描述</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><strong>io_block_read_cost</strong></td> 
   <td align="left">从磁盘上读取一个块对应的成本。增大这个值会加重I/O成本，可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描。</td> 
  </tr> 
  <tr> 
   <td><strong>memory_block_read_cost</strong></td> 
   <td align="left">从内存中读取一个块对应的成本。</td> 
  </tr> 
 </tbody> 
</table>

> **Q：不同存储引擎对块的定义不一样？**  
> A：InnoDB：一个页 = 一个块  
> MyISAM：一个页 = 以4096字节作为一个块

> **Q：怎么从内存中和从磁盘上读取一个块的默认成本是一样的？**  
> A：这主要是因为在MySQL目前的实现中，并不能准确预测某个查询需要访问的块中有哪些块已经加载到内存中，有哪些块还停留在磁盘上，所以MySQL简单的认为不管这个块有没有加载到内存中，使用的成本都是1.0。

### 2、修改成本常数 ###

-- 修改成本常数值，一定要让系统重新加载才生效
    update mysql.server_cost set cost_value = 20 where cost_name = 'disk_temptable_create_cost';
    
    -- 系统重新加载
    FLUSH OPTIMIZER_COSTS;

### 3、修改成本常数使用默认值 ###

-- 修改成本常数使用默认值，一定要让系统重新加载才生效
    update mysql.server_cost set cost_value = null where cost_name = 'disk_temptable_create_cost'
    
    -- 系统重新加载
    FLUSH OPTIMIZER_COSTS;

## 三、单表查询的成本 ##

> 在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案，这个**成本最低的方案就是所谓的执行计划**，之后才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样：
> 
> 1.  根据搜索条件，找出所有可能使用的索引
> 2.  计算全表扫描的代价
> 3.  计算使用不同索引执行查询的代价
> 4.  对比各种执行方案的代价，找出成本最低的那一个

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

MySQL把一个查询中可能使用到的索引称之为possible keys。在SQL查询的前面加上**EXPLAIN**关键字。

> **Q：可能用到索引的情况？**  
> A：  
> 情况1：索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=、<>  
> 情况2：LIKE操作符（LIKE匹配字符串前缀也行）

> **Q：可能索引失效的情况？**  
> A：  
> 情况1：LIKE操作符，并且和以通配符开头的字符串做比较  
> 情况2：如果列只有联合索引，并且不符合最左前缀原则

### 2、计算全表扫描的代价 ###

> 对于**InnoDB**存储引擎来说，全表扫描的过程：  
> 1、首先把聚簇/主键索引对应的页面加载到内存中  
> 2、然后检测记录是否符合搜索条件，并且把符合搜索条件的记录加入到结果集。  
>   
>   
> 计算全表扫描的代价需要两个信息（参考链接：[Mysql——》show table status][Mysql_show table status]）：  
> 1、聚簇索引占用的页面数  
> 2、表中的记录数

#### （1）I/O成本 ####

**I/O成本 = 30 \* 1.0 +1.1 = 31.1**

<table> 
 <thead> 
  <tr> 
   <th><strong>参数值</strong></th> 
   <th align="left"><strong>描述</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>30</td> 
   <td align="left">聚簇索引占用的页面数</td> 
  </tr> 
  <tr> 
   <td>1.0</td> 
   <td align="left">加载一个页面的成本常数</td> 
  </tr> 
  <tr> 
   <td>1.1</td> 
   <td align="left">微调值</td> 
  </tr> 
 </tbody> 
</table>

> **Q：微调值？**  
> A：\_MySQL\_在真实计算成本时会进行一些微调，这些微调的值是**直接硬编码到代码里**，没有注释而且这些微调的值十分的小，并不影响我们分析。

#### （2）CPU成本 ####

CPU成本 = 10093 x 0.2 + 1.0 = 2019.6

<table> 
 <thead> 
  <tr> 
   <th><strong>参数值</strong></th> 
   <th align="left"><strong>描述</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>10093</td> 
   <td align="left">统计数据中表的记录数</td> 
  </tr> 
  <tr> 
   <td>0.2</td> 
   <td align="left">访问一条记录所需的成本常数</td> 
  </tr> 
  <tr> 
   <td>1.0</td> 
   <td align="left">微调值</td> 
  </tr> 
 </tbody> 
</table>

#### （3）总成本 ####

查询成本=I/O成本+CPU成本 = \*\*31.1 + \*\*2019.6 = 2050.7  
所以，test全表扫描所需的总成本就是2050.7。

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

假设有可能使用索引idx\_order\_no、idx\_expire\_time，需要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。

#### （1）使用idx\_order\_no执行查询的成本分析 ####

如果是使用二级索引+回表，需要2个信息：

1.  范围区间数量
2.  需要回表的记录数

不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。

#### （2）使用idx\_expire\_time执行查询的成本分析 ####

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

> 参考链接：[Mysql——》index merge 索引合并][Mysql_index merge]

### 4、对比各种方案，找出成本最低的那一个 ###

## 三、Explain与查询成本 ##

查看执行计划花费的成本的方式：EXPLAIN FORMAT = json 后再拼接真正的查询语句

EXPLAIN FORMAT = json select * from student where age >=3 and age <=10 and name like '%a%'

![image.png][image.png 2]

<table> 
 <thead> 
  <tr> 
   <th><strong>参数</strong></th> 
   <th align="left"><strong>描述</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>query_cost</td> 
   <td align="left">查询总成本</td> 
  </tr> 
  <tr> 
   <td>cost_info</td> 
   <td align="left">成本明细</td> 
  </tr> 
  <tr> 
   <td>perfix_cost</td> 
   <td align="left">查询总成本 = read_cost + eval_cost</td> 
  </tr> 
 </tbody> 
</table>

## 四、Optimizer Trace与查询成本 ##

参考链接：[Mysql——》查询优化器][Mysql 1]

## 五、表连接查询的成本 ##

> MySQL中连接查询采用**嵌套循环连接算法**，驱动表会被访问一次，被驱动表可能会被访问多次，所以两表连接的查询成本由下边两个部分构成：
> 
> 1.  **单次查询驱动表的成本**
> 2.  **多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）**

> **Q：扇出fanout？**  
> A：对驱动表进行查询后得到的记录条数称之为驱动表的扇出。  
> 驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。

### 1、Condition filtering ###

当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值，有的时候扇出值的计算是很容易的，很多时候在计算数据的时候很多时候只能靠猜。MySQL把这个**猜**的过程称之为 **condition filtering** 。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是MySQL单纯的瞎猜，整个评估过程非常复杂。

#### （1）MySQL 5.7之前版本，查询优化器计算驱动表扇出 ####

<table> 
 <thead> 
  <tr> 
   <th align="left"><strong>驱动表</strong></th> 
   <th align="left"><strong>扇出值</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">使用<strong>全表扫描</strong></td> 
   <td align="left"><strong>表中记录的数量</strong></td> 
  </tr> 
  <tr> 
   <td align="left">使用<strong>索引</strong></td> 
   <td align="left"><strong>满足范围条件的索引记录条数</strong></td> 
  </tr> 
 </tbody> 
</table>

#### （2）MySQL 5.7版本，查询优化器计算驱动表扇出 ####

在MySQL 5.7版本中，引入condition filtering功能，就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。我们所说的纯粹瞎猜其实是很不严谨的，MySQL称之为启发式规则。

### 2、两表连接的成本分析 ###

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

#### （1）外连接：左（外）连接和右（外）连接 ####

特点：驱动表是固定的  
最优的查询方案：只需要分别为驱动表和被驱动表选择成本最低的访问方法。

#### （2）内连接 ####

特点：驱动表和被驱动表的位置是可以互换的  
需要考虑两个方面的问题：不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。  
最优的查询方案：分别为驱动表和被驱动表选择成本最低的访问方法。

### 3、多表连接的成本分析 ###

#### （1）多表连接可能产生的连接顺序 ####

#### （2）减少计算多种连接顺序的成本的方法 ####

> n表进行连接，产生n!种连接顺序，MySQL查询优化器要每一种连接顺序的成本都计算一遍，不过MySQL用了很多办法减少计算非常多种连接顺序的成本的方法。

##### （2.1）提前结束某种顺序的成本评估 #####

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

##### （2.2）系统变量optimizer\_search\_depth #####

如果连接表的个数小于optimizer\_search\_depth值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer\_search\_depth值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉很多分析连接成本的时间。

SHOW VARIABLES LIKE 'optimizer_search_depth';

![image.png][image.png 3]

##### （2.3）根据某些规则压根儿就不考虑某些连接顺序 #####

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

SHOW VARIABLES LIKE 'optimizer_prune_level';

![image.png][image.png 4]

[Java]: https://blog.csdn.net/weixin_43453386/article/details/84788317
[Mysql]: https://blog.csdn.net/weixin_43453386/article/details/88667709
[Redis]: https://blog.csdn.net/weixin_43453386/article/details/127966762
[Kafka]: https://blog.csdn.net/weixin_43453386/article/details/128189472
[Spring]: https://blog.csdn.net/weixin_43453386/article/details/124900806
[SpringBoot]: https://blog.csdn.net/weixin_43453386/article/details/84788714
[MyBatis_MyBatis-Plus]: https://blog.csdn.net/weixin_43453386/article/details/84788053
[Linux]: https://blog.csdn.net/weixin_43453386/article/details/89241912
[MongoDB]: https://blog.csdn.net/weixin_43453386/article/details/84788450
[Elasticsearch]: https://blog.csdn.net/weixin_43453386/article/details/108583782
[image.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/d43fc8e50cc041d1868342ad51c72d0f.png
[image.png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/d561dd975db849538cbc1c00eb70ba1f.png
[Mysql_show table status]: https://www.yuque.com/xiaoxian-6hqwc/rv5wvu/mbu8qn31ldm1ffox
[Mysql_index merge]: https://blog.csdn.net/weixin_43453386/article/details/131703869
[image.png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/8c2fd00c06ce480e8fd2e11ce377f4a6.png
[Mysql 1]: https://www.yuque.com/xiaoxian-6hqwc/rv5wvu/genery
[image.png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/88d4119760e9471db838eb684e7933d1.png
[image.png 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/02e16ae6b1bc44538620aa046ac8f220.png