一文带你了解MySQL之基于成本的优化

前言

我们之前老说MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询，怎么就带大家详细了解一下

一、什么是成本

我们之前老说MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正的执行查询。不过我们之前对成本的描述是非常模糊的，其实在MySQL中一条查询语句的执行成本是由下边这两个方面组成的：

• I/O成本

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

• CPU成本

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

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页面花费的成本默认是0.25（MySQL 5.7默认1.0），读取以及检测一条记录是否符合搜索条件的成本默认是0.1（MySQL 5.7默认0.2）。0.25和0.1这些数字称之为成本常数，这两个成本常数我们最常用到，其余的成本常数我们后边再说。

小提示：
需要注意的是，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.1。
这里使用的MySQL版本是8.0.32，各个版本之间成本会有差异。在本章后边详细讲解.

二、单表查询的成本

2.1 准备数据

为了我们正常的学习，我们还是用之前的demo8，怕大家忘记这个别表长啥样子，在给大家抄一篇：

mysql> USE testdb;

mysql> create table demo8 (    
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));

demo8 表一共创建了1个聚簇（主键）索引和4个二级索引：

• 为id列创建的聚簇索引；
• 为key1列创建的二级索引；
• 为key2列创建的唯一二级索引；
• 为key3列创建的二级索引；
• 为key_part1、key_part2、key_part3列创建的复合（联合）二级索引。

然后我们需要为这个表插入20000条记录，除id列外其余的列都插入随机值就好了

mysql> delimiter //
create procedure demo8data()
begin    
	declare i int;    
	set i=0;    
	while i<20000 do        
		insert into demo8(key1,key2,key3,key_part1,key_part2,key_part3,common_field) values(
		substring(md5(rand()),1,2),
		i+1,
		substring(md5(rand()),1,3),
		substring(md5(rand()),1,4),
		substring(md5(rand()),1,5),
		substring(md5(rand()),1,6),
		substring(md5(rand()),1,7)
		);        
		set i=i+1;    
	end while;
end;
//
delimiter ;

mysql> call demo8data();

下边正式开始我们的学习

2.2 基于成本的优化步骤

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

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

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

mysql> select * from demo8 where
	key1 in ('aa','bb','cc') and
	key2 > 10 and key2 < 1000 and
	key3 > key2 and
	key_part1 like '%3f%' and
	common_field='1281259';

是不是看上去很复杂，我们一步一步的分析一下

步骤一： 根据搜索条件，找出所有可能适用的索引

我们前边说过，对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN AND、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的范围区间（LIKE匹配字符串前缀也行），也就是说这些搜索条件都可能使用到索引，MySQL把一个查询中可能使用到的索引称之为possible keys。

我们分析一下上边查询涉及到的几个授权条件：

• key1 in ('aa','bb','cc')，这个搜索条件可以使用到二级索引idx_key1
• key2 > 10 and key2 < 1000，这个搜索条件可以使用到二级索引idx_key2
• key3 > key2，这个搜索条件的搜索列由于没有和常数比较，所以并不能使用到索引。
• key_part1 like '%3f%'，key_part1通过like操作符和以通配符开头的字符串做比较，不可以使用索引
• common_field=‘1281259’，由于该列压根没有索引，所以不会用到索引

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

步骤二： 计算全表扫描的代价

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

• 聚簇索引占用的页面数
• 该表中的记录数

这两个信息从哪来呢？MySQL为每个表维护了一系列的统计信息，关于这些统计信息是如何收集起来的我们放在本章后边详细讲解，现在看看怎么查看这些统计信息。MySQL给我们提供了show table status语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应
的like语句就好了，比如说我们要查看demo8这个表的统计信息可以这么写：

mysql> show table status like 'demo8' \G;
*************************** 1. row ***************************
           Name: demo8
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 20187
 Avg_row_length: 78
    Data_length: 1589248
Max_data_length: 0
   Index_length: 2785280
      Data_free: 4194304
 Auto_increment: 20001
    Create_time: 2023-05-16 16:36:53
    Update_time: 2023-05-16 16:38:21
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

虽然出现了很多统计选项，但我们目前只关心两个：

• Rows：本选项表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。从查询结果可以看出来，由于我们的demo8表使用的是InnoDB存储引擎，所以虽然表中实际记录有20000条记录，但是show table status显示的 Rows值为20187条记录。

• Data_length：本选项表示占用的存储空间的字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小

  也就是说可以这样计算该值的大小：

  Data_length = 聚簇索引的页面数量 * 每个页面的大小

  我们的demo8表使用的默认16KB的页面大小，根据上面的查询结果，所以我们可以计算出聚簇索引的页面数量：

  聚簇索引的页面数 = 1589248 ÷ 16 ÷ 1024 = 97

我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，现在可以看一下全表扫描成的计算过程：

• I/O成本： 97 * 0.25 =24.25
97指的是聚簇索引占用的页面数，0.25指的是加载一个页面的成本常数
• CPU成本：20187 * 0.1 = 2018.7
20187指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计值，0.1是指访问一条记录所需的成本常数
• 总成本：24.25 +2018.7 = 2042.95

综上所述，对于demo8的全表扫描所需的总成本就是2042.95，直接上代码，不用废话，来个华丽的验证

mysql> explain format=json select * from demo8 ;

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

步骤三： 计算不同索引执行的查询代价

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

使用idx_key2执行的查询成本

idx_key2对应的搜索条件是：key2 > 10 and key2 < 1000，也就是说对应范围区间就是：(10,1000)，使用idx_key2搜索示意图就是如下：

对于二级索引+回表方式的查询，MySQL计算这种查询的成本依赖两个方面的数据：

• 范围区间的数量：不论某个范围区间的二级索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。本例中使用idx_key2的范围区间只有一个：(10,1000)，所以相当于访问这个范围区间的二级索引付出的I/O成本就是：1 * 0.25 = 0.25

• 需要回表的记录数： 优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于比例来说就要计算idx_key2在(10,1000)这个范围区间包含多少二级索引记录，计算过程如下：

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

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

  • 步骤3： 如果区间最左记录和区间最右记录相隔不太远（在MySQL 5.7.21这个版本中，只要相隔不小于10个页面即可），那就可以精确统计出满足key2> 10 AND key2 < 1000条件的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？解决这个问题还得回到B+树索引的结构中来：

    

  • 如图比如区间最左记录在页34中，区间最右记录在页40中，那么我想计算区间最左记录和区间最右记录之间的页面数量就相当于计算页34和页40之间有多少页面，而每一条目录项记录都对应一个数据页，所以计算页34和页41之间有多少页面就相当于计算它们父节点（也就是页42）中对应的目录项记录之间隔着几条记录不就可以了吗。如果页34和页41之前的页面太多（对应的目录项不在同一个父节点页面中），那就继续递归进行统计，这个统计过程在父节点页面进行，之前我们说过一个B+树有4层高已经很了不得了，所以也不是很耗费性能。

知道了如何统计二级索引某个范围区间的记录数之后，就需要回到现实问题中来，根据上述算法测得idx_key2在区间(10, 1000)之间大约有989条记录。读取这989条二级索引记录需要付出的CPU成本就是：989 * 0.1 = 98.9

其中989是需要读取的二级索引记录条数，0.1 是读取一条记录成本常数

在通过二级索引获取到记录记录，还需要干两件事：

• 根据这些记录里的主键值到聚簇索引中做回表操作

  这里需要大家使劲的仔细瞧，MySQL评估回表操作的I/O成本依旧很豪放，他们认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面I/O。我们上边统计了使用idx_key2二级索引执行查询时，预计有989条二级索引记录需要进行回表操作，所回表操作带来的I/O成本就是：989 x 0.25 = 247.25

  其中989是预计的二级索引记录数，0.25是一个页面的I/O成本常数。

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

  回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然后再检测除key2 > 10 and key2 < 1000这个搜索条件以外的搜索条件是否成立。因为我们通过范围区间获取到二级索引记录共989条，也就对应着聚簇索引中989条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件的CPU成本如下：989 x 0.1 = 98.9

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

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

• I/O成本：1.0x 0.25 + 989 x 0.25 = 247.5 (范围区间的数量 + 预估的二级索引记录条数)

• CPU成本：989 x 0.1 + 0.01 + 989 x 0.1 = 197.81 （读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）

综上所述，使用idx_key2执行查询的总成本就是：247.5 + 197.81 = 445.31 ，直接上代码，不用废话，来个华丽的验证：

mysql> explain format=json select * from demo8 where key2 > 10 and key2 < 1000 and key3 > key2 and key_part1 like '%3f%' and common_field='1281259';

小提示：
若走索引，读二级索引条件有微调，读聚簇索引没有，扫描区间、回表任何一条记录都相当于读取一页。若不走索引，微调值另外分析，和前者不同。

使用idx_key2执行的查询成本

idx_key1对应的搜索条件是 key1 in ('aa','bb','cc')，也相当于3个单点区间：

• ['aa','aa']
• ['bb','bb']
• ['cc','cc']

使用idx_key1搜索示意图就是如下：

与使用的idx_key2的情况类似，我们也需要使用idx_key1时需要的访问的范围区间数量以及需要回表的记录数。

• 范围区间数量：使用idx_key1查询时很显然有三个单点区间，所以访问这三个范围区间二级索引付出的 I/O成本是 3 x 0.25 = 0.75
• 需要回表的记录数：
  • 查找单点区间['aa','aa']对应的二级索引记录数和查找连续范围区间对应的二级索引记录数是一样的，都是先计算区间最左记录和区间最右记录，然后计算出它们之间的记录数，具体方法已经讲过了，就不在唠叨了，最后我们得出单点区间['aa','aa']二级索引记录是：67
  • 查找单点区间['bb','bb']对应的二级索引记录是：88
  • 查找单点区间['cc','cc']对应的二级索引记录是：75

所以这三个单点区间总共需要回表的记录数是：67+88+75 = 230，读取这些二级索引记录的CPU成本就是：230 x 0.1 + 0.01 = 23.01

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

• 根据这些记录里的主键值到聚簇索引中做回表操作，所需要的I/O成本就是：230 x 0.25 = 57.5
• 回表操作后得到完整的用户记录，然后在比较其他搜索条件是否成立的此步骤对应的CPU成本就是：230 x 0.1 = 23

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

• I/O成本：0.75 + 57.5 = 58.25
• CPU成本：23 + 23.01 = 46.01

综上所述，使用idx_key1的执行查询总成本就是：58.25 + 46.01 = 104.26 ，直接上代码，不用废话，来个华丽的验证：

mysql> explain format=json select * from demo8 where key1 in ('aa','bb','cc') and key2 > 10 and key2 < 1000 and key3 > key2 and key_part1 like '%3f%' and common_field='1281259';

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

本例中有关key1和key2的搜索条件是使用AND连接起来的，而对于idx_key1和idk_key2都是范围查询，也就是说查找到的非聚集索引记录并不是按照主键值进行排序的，并不满足使用Intersection索引合并的条件，所以并不会使用索引合并。

小提示：
MySQL查询优化器计算索引合并成本的算法也比较麻烦，这里不讲，理解成本如何计算，知道MySQL会按照这种算法选择索引即可。

步骤四： 对比各种执行方案的代价，找出成本最低的一个

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

• 全表扫描的成本：2042.95
• 使用idx_key2的成本：445.31
• 使用idx_key1的成本：104.26

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

2.3 基于索引统计数据的成本计算

有时候使用索引执行查询时会有许多单点区间，比如使用IN语句就很容易产生非常多的单点区间，比如下边这个查询（下边查询语句中的…表示还有很多参数）：

select * from demo8 where key1 in ('aa', 'bb', 'cc', ... , 'ee');

很显然，这个查询可能使用到的索引就是idx_key1，由于这个索引并不是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，需要我们去计算。计算方式我们上边已经介绍过了，就是先获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确计算，多的时候只能估算）。MySQL把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。

小提示：
dive直译为中文的意思是跳水、俯冲的意思，原谅我的英语。 index dive，索引跳水？索引俯冲？好像都不太合适，所以压根就不翻译了。不过大家要意会index dive就是直接利用索引对应的B+树来计算某个范围区间对应的记录条数。

有几个单点区间的话，使用index dive的方式去计算这些单点区间对应的记录数也不是什么问题，可是你架不住有的小伙伴们使劲往IN语句塞东西，如果IN语句里有20000个参数的，这就意味着MySQL的查询优化器为了计算这些单点区间对应的索引记录条数，要进行20000次index dive操作，这性能损耗可就高了，搞不好计算这些单点区间对应的索引记录条数的成本比直接全表扫描的成本都高了。MySQL当然考虑到了这种情况，所以提供了一个系统变量eq_range_index_dive_limit，我们看一下这个系统变量的默认值：

mysql> show variables like '%dive%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+
1 row in set (0.00 sec)

也就是说如果我们的IN语句中的参数个数小于200个的话，将使用index dive的方式计算各个单点区间对应的记录条数，如果大于或等于200个的话，可就不能使用index dive了，要使用所谓的索引统计数据来进行估算。怎么个估算法？继续往下看。

MySQL会为每个表维护一份统计数据一样，MySQL也会为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用show index from 表名的语法，比如我们查看一下demo8的各个索引的统计数据可以这么写：

mysql> show index from demo8;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| demo8 |          0 | PRIMARY      |            1 | id          | A         |       18750 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| demo8 |          0 | idx_key2     |            1 | key2        | A         |       18565 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| demo8 |          1 | idx_key1     |            1 | key1        | A         |         256 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| demo8 |          1 | idx_key3     |            1 | key3        | A         |        4053 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| demo8 |          1 | idx_key_part |            1 | key_part1   | A         |       16122 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| demo8 |          1 | idx_key_part |            2 | key_part2   | A         |       18570 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| demo8 |          1 | idx_key_part |            3 | key_part3   | A         |       18570 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
7 rows in set (0.02 sec)

是不是有很多属性，不过这些属性都不难理解，我们这里把这些属性都简单的介绍一遍：

属性名	描述
Table	索引所属表的名称
Non_unique	索引列的值是否是唯一的，聚簇索引和唯一二级索引的该列值为0，普通二级索引该列值为1
Key_name	索引的名称
Seq_in_index	索引列在索引中的位置，从1开始计数。比如对于联合索引idx_key_part，来说，key_part1、key_part2和key_part3对应的位置分别是1、2、3
Column_name	索引列的名称
Collation	索引列中的值是按照何种排序方式存放的，值为A时代表升序存放，为NULL时代表降序存放
Cardinality	索引列中不重复值的数量。后边我们会重点看这个属性的。
Sub_part	对于存储字符串或者字节串的列来说，有时候我们只想对这些串的前n个字符或字节建立索引，这个属性表示的就是那个n值。如果对完整的列建立索引的话，该属性的值就是NULL
Packed	索引列如何被压缩，NULL值表示未被压缩。这个属性我们暂时不了解，可以先忽略掉
Null	该索引列是否允许存储NULL值。
Index_type	使用索引的类型，我们最常用的就是BTREE，其实也就是B+树索引
Comment	索引列注释信息
Index_comment	索引注释信息

上述属性除了Packed大家可能看不懂以外，应该没有啥看不懂的了，如果有的话肯定是大家看前边文章的时候跳过了。其实我们现在最在意的是Cardinality属性，Cardinality直译过来就是基数的意思，表示索引列中不重复值的个数。比如对于一个一万条记录的表来说，某个索引列的Cardinality属性是10000，那意味着该列中没有重复的值，如果Cardinality属性是1的话，就意味着该列的值全部是重复的。不过需要注意的是，对于InnoDB存储引擎来说，使用show index语句展示出来的某个索引列的Cardinality属性是一个估计值，并不是精确的。关于这个Cardinality属性的值是如何被计算出来的我们后边再说，先看看它有什么用途。

前边说过，当IN语句中的参数个数>=系统变量eq_range_index_dive_limit的值的话，就不会使用index dive的方式计算各个单点区间对应的索引记录条数，而是使用索引统计数据，这里所指的索引统计数据指的是这两个值：

• 使用show table status语句展示出的Rows值，也就是一个表中有多少条记录

• 使用show index语句展示出的Cardinality属性

结合上一个Rows统计数据，我们可以针对索引列，计算出平均一个值重复多少次。一个值的重复次数≈Rows÷Cardinality，以demo8表的idx_key1索引为例，它的Rows值是20187，它对应索引列key1的Cardinality值是256，所以我们可以计算key1列平均单个值的重复次数就是：20187÷256≈79

此时再看上面那条查询语句：

select * from demo8 where key1 in ('aa', 'bb', 'cc', ... , 'ee');

假设IN语句中有20000个参数的话，就直接使用统计数据来估算这些参数需要单点区间对应的记录条数了，每个参数大约对应79条记录，所以总共需要回表的记录数就是：20000 x 79 = 1580000

使用统计数据来计算单点区间对应的索引记录条数可比index dive的方式简单多了，但是它的致命弱点就是：不精确！使用统计数据算出来的查询成本与实际所需的成本可能相差非常大。

小提示：
当你的查询中使用到了IN查询，但是却实际没有用到索引，就应该考虑一下是不是由于eq_range_index_dive_limit值太小导致的

三、连接查询的成本

2.1 准备数据

连接查询至少是要有两个表的，只有一个demo8表是不够的，所以为了故事的顺利发展，我们直接构造二个和demo8表一模一样的s1表和s2表

mysql> create table s1 (    
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));
Query OK, 0 rows affected (0.04 sec)

mysql> create table s2 (    
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));
Query OK, 0 rows affected (0.04 sec)

mysql> insert into s1 select * from demo8;
Query OK, 20000 rows affected (0.83 sec)
Records: 20000  Duplicates: 0  Warnings: 0

mysql> insert into s2 select * from demo8;
Query OK, 20000 rows affected (0.89 sec)
Records: 20000  Duplicates: 0  Warnings: 0

2.2 Condition filtering介绍

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

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

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

查询一：

select * from s1 inner join s2;

假设使用s1表作为驱动表，很显然对驱动表的单表查询只能使用全表扫描的方式执行，驱动表的扇出值也很明确，那就是驱动表中有多少记录，扇出值就是多少。统计数据中s1表的记录是数是20250，也就是说优化器就直接会把20250当作在s1表的扇出值。

查询二：

select * from s1 inner join s2 where s1.key2 > 10 and s1.key2 < 1000;

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

当然，事情不会总是一帆风顺的，要不然剧情就太平淡了。有的时候扇出值的计算就变得很棘手，比方说下边这个查询：

查询三：

select * from s1 inner join s2 where s1.common_field > 'xyz'

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

查询四：

select * from s1 inner join s2 where s1.key2 > 10 and s1.key2 < 1000 and s1.common_field > 'xyz'

不过因为本查询可以使用idx_key2索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合common_field > 'xyz'条件，也就是只需要猜在989条记录中有多少符合common_field > 'xyz'条件

查询五：

select * from s1 inner join s2 where s1.key2 > 10 and s1.key2 < 1000 and s1.key1 in('aa','bb','cc') and s1.common_field > 'xyz'

本查询和查询二类似，不过在驱动表s1选取idx_key1索引执行查询后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件：

• key2 > 10 and key2 < 1000
• common_field > ‘xyz’

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

说了这么多，其实就是想表达在这两种情况下计算驱动表扇出值时需要靠猜：

• 如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条
• 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条

MySQL把这个猜的过程称之为condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是MySQL单纯的瞎猜，整个评估过程挺复杂的，所以我们就跳过了哈。

2.3 多表连接的成本分析

这里我们首先考虑一下多表连接时可能产生出多少连接顺序：

• 对于两表连接，比如表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!

四、调节成本的常数

我们之前介绍了两个成本常数：

• 读取一个页面花费的成本默认是：0.25
• 检测一条记录是否符合搜索条件的成本默认是:0.1

其实除了这两个成本常数外，MySQL还支持好多，他们被存储到了mysql数据库（这是一个系统数据库，我们之前介绍过）的两个表中：

mysql> show tables from mysql like '%cost%';
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost              |
| server_cost              |
+--------------------------+
2 rows in set (0.06 sec)

我们在之前就说过，一条语句执行其实是分为两层的：

• server层
• 存储引擎层

在server层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储引擎层执行具体的数据存取操作。也就是说一条语句在server层中执行的成本是和它操作的表使用的存储引擎是没关系的，所以关于这些操作对应的成本常数就存储在了server_cost表中，而依赖于存储引擎的一些操作对应的成本常数就存储在了engine_cost表中

4.1 server_cost 表

server_cost表中在server层进行的一些操作对应的成本常数，具体内容如下：

mysql> select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+
| cost_name                    | cost_value | last_update         | comment | default_value |
+------------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost   |       NULL | 2023-04-24 19:39:12 | NULL    |            20 |
| disk_temptable_row_cost      |       NULL | 2023-04-24 19:39:12 | NULL    |           0.5 |
| key_compare_cost             |       NULL | 2023-04-24 19:39:12 | NULL    |          0.05 |
| memory_temptable_create_cost |       NULL | 2023-04-24 19:39:12 | NULL    |             1 |
| memory_temptable_row_cost    |       NULL | 2023-04-24 19:39:12 | NULL    |           0.1 |
| row_evaluate_cost            |       NULL | 2023-04-24 19:39:12 | NULL    |           0.1 |
+------------------------------+------------+---------------------+---------+---------------+
6 rows in set (0.00 sec)

我们先看一下server_cost各个列都分别是什么意思：

• cost_name：表示成本常数的名称
• cost_value：表示成本常数对应的值。如果该列的值为NULL的话，意味着对应的成本常数会采用默认值
• last_update：表示最后更新记录的时间
• comment：注释
• default_value ：默认值

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

成本常数名称	默认值	描述
disk_temptable_create_cost	40.0	创建基于磁盘的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表
disk_temptable_row_cost	1.0	向基于磁盘的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表
key_compare_cost	0.1	两条记录做比较操作的成本，多用在排序操作上，如果增大这个值的话会提升filesort的成本，让优化器可能更倾向于使用索引完成排序而不是filesort
memory_temptable_create_cost	2.0	创建基于内存的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表
memory_temptable_row_cost	0.2	向基于内存的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表
row_evaluate_cost	0.2	这个就是我们之前一直使用的检测一条记录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描

小提示：
MySQL在执行入如DISTINCT查询、分组查询、Union查询以及某些特殊条件下的排序查询都可能在内部先创建一个临时表，使用这个临时表来辅助完成查询（比如对于DISTINCT查询可以建一个带有UNIQUE索引的临时表，直接把需要去重的记录插入到这个临时表中，插入完成之后的记录就是结果集了）。在数据量大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB等存储引擎，在数据量不大时可能创建基于内存的临时表，也就是使用品那个Memory存储引擎。这里大家知道创建临时表和对这个临时表进行写入和读取的操作代价还是很高的就行了。

这些成本常数在server_cost中的初始值都是NULL，意味着优化器会使用它们的默认值来计算某个操作的成本，如果我们想修改某个成本常数的值的话，需要做两个步骤：

步骤一： 对我们感兴趣的成本常数做更新操作

比如说我们想把检测⼀条记录是否符合搜索条件的成本增大到0.3，那么就可以这样写更新语句：

update mysql.server_cost set cost_value = 0.4 where cost_name = 'row_evaluate_cost';

步骤二： 让系统重新加载这个表的值，使用下边语句即可

flush optimizer_costs;

当然，在你修改完某个成本常数后想把它们再改回默认值的话，可以直接把cost_value的值设置为NULL，再使用flush optimizer_costs语句让系统重新加载它就好了。

4.2 engine_cost 表

engine_cost表表中在存储引擎层进行的一些操作对应的成本常数，具体内容如下：

mysql> select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
| default     |           0 | io_block_read_cost     |       NULL | 2023-04-24 19:39:12 | NULL    |             1 |
| default     |           0 | memory_block_read_cost |       NULL | 2023-04-24 19:39:12 | NULL    |          0.25 |
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+
2 rows in set (0.01 sec)

与server_cost相比，engine_cost多了两个列：

• engine_name列：指成本常数适用的存储引擎名称。如果该值为default，意味着对应的成本常数适用于所有的存储引擎
• device_type列：指存储引擎使⽤的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘，不过在MySQL 5.7.21这个版本中并没有对机械硬盘的成本和固态硬盘的成
  本作区分，所以该值默认是0

我们从engine_cost表中的内容可以看出来，目前支持的存储引擎成本常数只有两个：

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

大家看完这两个成本常数的默认值是不是有些疑惑，怎么从内存中和从磁盘上读取一个块的默认成本是不一样的？这主要是因为随着MySQL的发展，MySQL可以准确预测哪些块在磁盘上，那些块在内存中。

与更新server_cost表中的记录一样，我们也可以通过更新engine_cost表中的记录来更改关于存储引擎的成本常数，我们也可以通过为engine_cost表插入新记录的方式来添加只针对某种存储引擎的成本常数：

步骤一： 插入针对某个存储引擎的成本常数
比如我们想增大InnoDB存储引擎页面I/O的成本，书写正常的插入语句即可：

insert into mysql.engine_cost values ('innodb', 0, 'io_block_read_cost', 2.0, current_timestamp, 'increase innodb i/o cost');

步骤二： 让系统重新加载这个表的值使用下边语句即可：

flush optimizer_costs;

至此今天的学习就到此结束了，愿您成为坚不可摧的自己~~~
​

You can’t connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your future.You have to trust in something - your gut, destiny, life, karma, whatever. This approach has never let me down, and it has made all the difference in my life

如果我的内容对你有帮助，请 点赞、评论、收藏，创作不易，大家的支持就是我坚持下去的动力