【MySQL学习笔记（十一）】之基于成本的单表查询优化计算与连接查询的优化计算

绝地灬酷狼 2023-10-07 15:39 2阅读 0赞

#### 文章目录 ####

*   *  本文章由公号【开发小鸽】发布！欢迎关注！！！
     *  一． 基于成本的优化
     *   *  （一） 概述
         *  （二） 优化步骤
         *   *  1. 根据搜索条件，找出所有可能使用的索引
             *  2. 计算全表扫描的代价
             *  3. 计算使用不同索引执行查询的代价
             *  4. 对比各种执行方案的代价，找出成本最低的方案
         *  （三） 连接查询的成本
         *   *  1. 条件过滤
             *  2. 两表连接查询成本分析
             *  3. 多表连接查询成本分析
             *   *  （1） 提前结束某种连接顺序的成本计算
                 *  （2） 系统变量optimizer\_search\_depth

### 本文章由公号【开发小鸽】发布！欢迎关注！！！ ###

**老规矩–妹妹镇楼：**

![20200721223424816.JPG][]

### 一． 基于成本的优化 ###

#### （一） 概述 ####

所谓查询语句的执行成本由两个方面组成，一个是I/O成本，经常使用的InnoDB和MyISAM存储引擎都是将数据和索引存储到磁盘上，当查询表中的记录时，需要先把数据或者索引加载到内存中，然后再进行操作，从磁盘到内存的时间成本为I/O成本；还有一个是CPU成本，读取记录以及检测记录是否满足对应的搜索条件等操作的时间成本为CPU成本。

InnoDB读取一个页面花费的成本默认是1.0，读取一个记录以及检测一条记录是否符合搜索条件的成本默认是0.2，这些数字称为成本常数。

#### （二） 优化步骤 ####

MySQL的优化器会找出所有可以用来执行该语句的方案，在对比这些方案后找出成本最低的方案，即执行计划，之后才会调用存储引擎提供的接口执行查询。

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

根据所有的搜索条件，来判断是否可以生成合适的扫描区间，进而得到可能使用的索引。

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

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

通过查询表的统计信息，得到表中的记录数与表占用的存储空间字节数。

SHOW TABLE STATUS LILKE ‘表名’;

Rows表示表中的记录数，Data\_length表示表占用的字节数，对于MyISAM来说是数据文件的大小，对于InnoDB来说是聚簇索引的大小，可以除以16KB得出聚簇索引的页面数量。之后，计算I/O成本：

I/O成本 = 聚簇索引页面数量 x 1.0 + 1.1

1.0表示磁盘加载到内存的成本常数，1.1是微调值；

CPU成本 = 记录数 x 0.2 + 1.0

0.2是读取记录以及检测记录的成本常数，1.0 是微调值

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

分析单独使用不同的索引执行查询的代价，最后还要分析是否能够使用索引合并。一般使用的都是二级索引+回表方式执行的查询，在计算这种查询的成本时依赖两个数据：扫描区间的数量与需要回表的记录数。无论某个扫描区间的二级索引占用了几个页面，查询优化器粗暴地认为I/O成本与读取一个页面的成本相同。

根据回表的记录的主键值到聚簇索引中执行回表操作，MySQL中评估回表成本时认为每次回表相当于访问一个页面，即有多少条记录就有多少次页面I/O。回表得到完整的用户记录，再检测其他搜索条件是否成立即可，检测也是用读取记录与检测记录的成本计算。

最后对不同索引查询得到的二级索引记录是否按照主键排序，来决定是否索引合并。

##### 4. 对比各种执行方案的代价，找出成本最低的方案 #####