深入理解mysql-第八章 mysql查询优化-基于成本

待我称王封你为后i 2023-01-23 13:54 187阅读 0赞

**目录**

一、所谓成本

二、单表查询基于成本的优化（索引）

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

2.2 计算全表扫描的代价

2.3 计算使用不同索引执行查询的代价

2.4 对比各种执行方案的代价，找出成本最低的那一个

三、连接查询的成本优化

3.1 连接查询的成本计算方式

3.2 内连接的成本计算方式（驱动表的选择）

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

我们知道mysql服务器程序处理来自客户端的查询请求大致需要经过三个部分，分别是`连接管理`、`解析与优化`、`存储引擎。存储引擎我们已经前几章已经讲过了，下面几个章节主要研究下mysql的的优化。本章主要讲解对索引的最优选择，内连接的驱动表选择。`

# 一、所谓成本 #

`MySQL`执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。其实在`MySQL`中一条查询语句的执行成本是由下边这两个方面组成的：

*  `I/O`成本，我们的表经常使用的`MyISAM`、`InnoDB`存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为`I/O`成本。
 *  `CPU`成本，读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为`CPU`成本。

对于`InnoDB`存储引擎来说，页是磁盘和内存之间交互的基本单位，**`MySQL`规定读取一个页面花费的成本默认是`1.0`，读取以及检测一条记录是否符合搜索条件的成本默认是`0.2，当然这些成本常量是可以修改的`**。

# 二、单表查询基于成本的优化（索引） #

我们以这个建表语句所建的表进行讲解：

CREATE TABLE single_table (
        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 idx_key2 (key2),
        KEY idx_key3 (key3),
        KEY idx_key_part(key_part1, key_part2, key_part3)
    ) Engine=InnoDB CHARSET=utf8;

还是假设这个表里边儿有10000条记录（不同的数据量相同的SQL会有不同的优化策略，比如数据量很小，完全可以全表扫描不需要走二级索引，因为回表反而会增加查询时间），除`id`列外其余的列都插入随机值。下边正式开始基于成本的优化，主要分为四步：

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

下边我们就以一个实例来分析一下这些步骤，单表查询语句如下：

SELECT * FROM single_table WHERE 
        key1 IN ('a', 'b', 'c') AND 
        key2 > 10 AND key2 < 1000 AND 
        key3 > key2 AND 
        key_part1 LIKE '%hello%' AND
        common_field = '123';

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

我们分析一下上边查询中涉及到的几个搜索条件：

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

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

## 2.2 计算全表扫描的代价 ##

对于`InnoDB`存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=`I/O`成本+`CPU`成本，所以计算全表扫描的代价需要知道聚簇索引占用的页面数和该表中的数据数这两个数。然后第一节我们提到的页占用的成本是1.0，每个数据判断的成本0.2，开始计算其查询成本。

页数和数据量这两个数据从哪里来呢，Mysql为每个表维护了一系列的统计信息，提供了`SHOW TABLE STATUS + tablename`语句来查看表的统计信息。

mysql> SHOW TABLE STATUS LIKE 'single_table'\G
    *************************** 1. row ***************************
               Name: single_table
             Engine: InnoDB
            Version: 10
         Row_format: Dynamic
               Rows: 9693
     Avg_row_length: 163
        Data_length: 1589248
    Max_data_length: 0
       Index_length: 2752512
          Data_free: 4194304
     Auto_increment: 10001
        Create_time: 2018-12-10 13:37:23
        Update_time: 2018-12-10 13:38:03
         Check_time: NULL
          Collation: utf8_general_ci
           Checksum: NULL
     Create_options:
            Comment:
    1 row in set (0.01 sec)

我们从这个信息中看到Rows，就代表表中的记录条数，对于使用`MyISAM`存储引擎的表来说，该值是准确的，对于使用`InnoDB`存储引擎的表来说，该值是一个估计值。所以我们计算CPU成本是9693 x 0.2 + 1.0 = 1939.6（后面1.0是微调值）。

我们计算页面数需要用到Data\_length，对于使用`InnoDB`存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，因为每个页占用16K，所以我们推算页面数就等于Data\_length/16\*1024。示例中的聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97，所以我们计算IO成本=97 x 1.0 + 1.1 = 98.1（后面1.1是微调值）。

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

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

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

`idx_key2`对应的搜索条件是：`key2 > 10 AND key2 < 1000`，也就是说对应的范围区间就是：`(10, 1000)`，使用`idx_key2`搜索的示意图就是这样子：

![image\_1d6cb8nolj1714dimrf1iu64l99.png-124.3kB][image_1d6cb8nolj1714dimrf1iu64l99.png-124.3kB]

如图，对于使用`二级索引 + 回表`方式的查询，计算成本主要分以下几步:

1.  计算二级索引的I/O成本，这里不论二级索引占用了几个页面，mysql都粗暴认为是一个页面，所以二级索引付出的`I/O`成本就是1.0。
2.  计算二级索引的CPU成本,这里需要知道需要回表的记录数。mysql计算记录数主要根据取值区间的能拿到记录数据的最左边和最右边数据，`最左记录`向右读10个页面，计算平均每个页面中包含多少记录，然后根据B+树计算出来最左和最右记录之间的页面数，两者相乘就可以拿到回表记录数。运用此方法测得大概有95条数据，那么95 x 0.2 + 0.01 = 19.01就是CPU成本。
3.  计算回表的I/O成本，mysql粗暴认为每次回表操作都相当于访问一个页面，所以这里`I/O`成本就是1.0\*95=95（其中`95`是预计的二级索引记录数）。
4.  计算回表的CPU成本，也就对应着聚簇索引中`95`条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件，其`CPU`成本如下95 x 0.2 = 19.0。

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

1+19.1+95+19=134.01

同理我们计算出使用`idx_key1`执行查询的总成本就是：

121.0 + 47.21 = 168.21

另外注意，在一些特殊情况，mysql还会使用索引合并（`idx_key2和idx_key1合并`）。但本例中二级索引记录并不是按照主键值进行排序的，并不满足使用`Intersection`索引合并的条件，所以并不会使用索引合并。

## 2.4 对比各种执行方案的代价，找出成本最低的那一个 ##

下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来：

*  全表扫描的成本：`2037.7`
 *  使用`idx_key2`的成本：`134.01`
 *  使用`idx_key1`的成本：`168.21`

很显然，使用`idx_key2`的成本最低，所以当然选择`idx_key2`来执行查询。

# 三、连接查询的成本优化 #

因为是连表查询，我们按照上面的single\_table复制出来s1和s2表。

## 3.1 连接查询的成本计算方式 ##

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

*  单次查询驱动表的成本
 *  多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

我们把对驱动表进行查询后得到的记录条数称之为驱动表的`扇出`（英文名：`fanout`）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值。扇出值的计算比较麻烦，有的是全表扫描知道全表数量就是扇出值，有的是索引可以算出数量，但比如这些加上一些无索引字段的区间条件，就只能靠猜出扇出值。

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

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

实际计算驱动表和被驱动表的成本，都是和第二节的方式是一样的，分别为驱动表和被驱动表选择成本最低的访问方法。

## 3.2 内连接的成本计算方式（驱动表的选择） ##

对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以可以按照3.1直接去计算就可以了，但是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以就要考虑不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。

比如：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
        ON s1.key1 = s2.common_field 
        WHERE s1.key2 > 10 AND s1.key2 < 1000 AND 
              s2.key2 > 1000 AND s2.key2 < 2000;

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

但是要注意的是，有太多表进行内连接查询这种极端情况的话，这种组合排列的方式就有很多种情况，mysql会提前结束某种顺序的成本评估。

#  #

[image_1d6cb8nolj1714dimrf1iu64l99.png-124.3kB]: /images/20221004/157f5cec462a40f591db7c55fd969b38.png