一、连接查询的成本
1.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
1.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'
1
本查询和查询二类似，不过在驱动表s1选取idx_key1索引执行查询后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件：
key2 > 10 and key2 < 1000
common_field > ‘xyz’
也就是优化器需要猜在230条记录中有多少符合上述两个条件的。
说了这么多，其实就是想表达在这两种情况下计算驱动表扇出值时需要靠猜：
如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要猜满足搜索条件的记录到底有多少条
如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条
MySQL把这个猜的过程称之为condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是MySQL单纯的瞎猜，整个评估过程挺复杂的，所以我们就跳过了哈。
1.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表中
2.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';
1
步骤二： 让系统重新加载这个表的值，使用下边语句即可
flush optimizer_costs;
1
当然，在你修改完某个成本常数后想把它们再改回默认值的话，可以直接把cost_value的值设置为NULL，再使用flush optimizer_costs语句让系统重新加载它就好了。
2.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;
