什么是成本
我们之前老说MySQL在执行一个查询时可以有不同的执行方案。它会选择其中成本最低，或者说代价最低的那种方案去真正地执行查询。不过我们之前对成本的描述是非常模糊的，其实一条查询语句在 MySQL 中的执行成本是由两个方面组成的。
- I/O成本：我们的表经常使用的 MyISAM、InnoDB 存储引擎都是将数据和索引存储到磁盘上。当查询表中的记录时，需要先把数据或者索引加载到内存中，然后再进行操作。这个从磁盘到内存的加载过程损耗的时间称为 I/O 成本。
- CPU成本：该取记录以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时问称为 CPU 成本。
对InnoDB 存储引擎来说，页是磁盘和内存之间进行交互的基本单位。设计 MySQL 的大叔规定：读取一个页面花费的成本默认是 1.0；读取以及检测一条记录是否符合搜索条件的成本默认是 0.2。1.0、02 这些数字称为成本常数，这两个成本常数最常用到，其余的成本常数会在后面再说。
需要注意的是，在读取记录时，即时不需要检测记录是否符合搜索条件，其成本也算作0.2
单表查询的成本
2.1 准备工作
为了故事的顺利发展，我们还得把之前用到的 single_table 表搬出来。为了避免大家忘记这个表的模样，这里再给大家抄一遍：
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 (I00),
common_field VARCHAR (100),
PRIMARY KEY (id),
KEY idx_key1 (key1),
UNIQUE KEY uk_key2 (key2),
KEY idx_key3 (key3),
KEY idx_key_part (key _part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;
还是假设这个表中有 10,000 条记录，除id列外其余的列都插入随机值。下边正式开始我们的表演。
2.2 基于成本的优化步骤
在真正执行一条单表查询语句之前，MySQL 的优化器会找出所有可以用来执行该语句的方案，并在对比这些方案之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。之后才会调用存储引擎提供的接口真正地执行查询。这个过程总结一下就是下面这样。
- 根据搜索条件，找出所有可能使用的索引。
- 计算全表扫描的代价。
- 计算使用不同索引执行查询的代价。
- 对比各种执行方案的代价，找出成本最低的那个方案。
下边以一个实例来分析一下这些步骤。单表查询语句如下：
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.2.1 根据搜索条件，找出所有可能使用的索引
前文说过，对于 B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、！=（不等于也可以写成<>）或者LIKE操作符连接起来，就会产生一个扫描区间(用 LIKE 匹配字符串前缀时，也会产生一个扫描区间)。
也就是说，这些搜索条件都可能使用到索引，设计 MySQL 的大叔把一个查询中可能使用到的索引称之为 possible keys。
我们分析一下上面的查询语句中涉及的几个搜索条件。
- key1 in (‘a’, ‘b’, ‘c’)：这个搜索条件可以使用二级索引 idx_key1。
- key2>10 AND key2<1000：这个搜索条件可以使用二级索引 uk_key2。
- key3>key2：这个搜索系件的索引列由于没有与常数进行比较，因此不能产生合适的扫描区间。
- key_part_1 LIKE ‘%hello%’: key_part_1 通过 LIKE 操作符与以通配符开义的字符串进行比较，不能产生合适的扫描区间。
- common_field = ‘123’：由于压根儿没有在该列上建立索引，所以不会用到索引。
综上所述，上面的查询语句可能使用到索引（也就是possible keys）有idx_key1和uk_key2。
2.2.2 计算全表扫描的代价
对InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次与给定的搜索条件进行比较，并把符合搜索条件的记录加入到结果集中。所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本= I/O成本＋CPU成本，所以在计算全表扫描的代价时需要两个信息：
- 聚簇索引占用的页面数；
- 该表中的记录数。
这两个信息从哪里来呢？设计MySQL的大叔为每个表维护了一系列的统计信息。关于这统计信息的收集方式，将会在下一章详细唠叨。现在先看一下怎么查看这些统计信息。设计MySQL的大叔提供了SHOW TABLE STATUS语句来查看表的统计信息。如果要看某个指定表的统计信息，在该语句后添加对应的LIKE语句就好了。比如，我们要查看single_table表统计信息，可以这么写：
mysql> USE xiaohaizi;
Database changed
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存储引擎的表来说，该值是一个估计值。从查询结果中也可以看出，由于single_table表使用的是InnoDB存储引擎，尽管表实际有10,000条记录，但是执行SHOW TABLE STATUS语句后显示的Rows值是9693，即只有9693条记录。
- Data_length：表示表占用的存储空间字节数。对于使用MyISAM存储引擎的表来说，该值就是数据文件的大小；对于使用InnoDB存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说，可以按照下面的公式来计算该值的大小：
Data_length = 聚簇索引的页面数量 x 每个页面的大小
我们的single_table表使用默认的16KB页面大小，而上面查询结果中显示Data_length的值是1,589,248，所以可以反向推导出聚簇索引的页面数量：
聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，接下来就可以计算全表扫描成本了。但是，设计MySQL的大叔在真正计算成本时会进行一些微调，这些微调的值是直接硬编码到代码中的。由于没有注释，我也不知道这些微调值是个啥意思。但是由于这些微调的值十分小，并不影响我们分析，所以也就没有必要在这些微调值上纠结了。现在可以看一下全表扫描成本的计算过程。
- I/O成本：97 * 1.0 + 1.1 = 98.1
97指的是聚簇索引占用的页面数，1.0指的是加载一个页面的成本常数，后边的1.1是一个微调值，我们不用在意。
- CPU成本：9693 * 0.2 + 1.0 = 1939.6
9693指的是统计数据中表的记录数，对于InnoDB存储引擎来说这是一个估计值；0.2指的是访问一条记录所需的成本常数，后边的1.0是一个微调值，我们不用在意。
- 总成本：98.1 + 1939.6 = 2037.7
综上所述，针对single_table的全表扫描所需的总成本就是2037.7。
前文说过，完整的用户记录其实都存储在聚簇索引对应的B＋树的叶子节点中，所以我们只要通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。也就是说在全表扫描的过程中，其实有的B＋树内节点是不需要访问的，但是设计MySQL的大叔在计算全表扫描成本时，直接使用聚簇索引占用的页面数作为计算I／O成本的依据，并没有区分内节点和叶子节点。这有点儿“简单粗暴”，大家注意一下就好了。
2.2.3 计算使用不同索引执行查询的代价
在前面的“根据搜索条件，找出所有可能使用的索引”小节中得知，前述查询可能使用到idx_key1和uk_key2这两个索引，我们需要分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。这里需要注意的一点是，MySQL查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本，所以我们也先分析uk_key2的成本，然后再看使用idx＿key1的成本。
2.2.3.1 使用uk_key2执行查询的成本分析
uk_key2对应的搜索条件是key2 > 1 0 AND key2 < 1000，也就是说对应的扫描区间就是（10，1000）。使用uk_key2执行查询的示意图如图12-1所示。
对于使用二级索引 + 回表方式执行的查询，设计MySQL的大叔在计算这种查询的成本时，依赖于两方面的数据：扫描区间数量和需要回表的记录数。
- 扫描区间数量
无论某个扫描区间的二级索引到底占用了多少页面，查询优化器粗暴地认为读取索引的一个扫描区间的I/O成本与读取一个页面的I/O成本是相同的。本例中使用uk_key2的扫描区间只有一个：（10,1000），所以相当于访问这个扫描区间的二级索引所付出的I/O成本就是1 * 1.0 = 1.0 。
- 需要回表的记录数
查询优化器需要计算二级索引的某个扫描区间到底包含多少条记录，对于本例来说就是要计算uk_key2在（10,1000）扫描区间中包含多少二级索引记录。计算过程是这样的。
**步骤1. **先根据key 2 > 10条件访问uk_key2对应的B＋树索引，找到满足key2 > 10条件的第一条记录（我们把这条记录称为区间最左记录）。前文说过，在B＋树中定位一条记录的过程是贼快的，是常数级别的，所以这个过程的性能消耗可以忽略不计。
步骤2．然后再根据key2 < 1000条件继续从uk_key2对应的B＋树索引中找出最后一条满足这个条件的记录（我们把这条记录称为区间最右记录）。这个过程的性能消耗也可以忽略不计。
步骤3．如果区间最左记录和区间最右记录相隔不太远（在MySQL5.7.22版本中，只要相隔不大于10个页面即可），就可以精确统计出满足key2 > 10 AND key2 < 1000条件的一级索引记录的条数。
别忘了数据页有一个Page Header部分。Page Header中有一个名为PAGE＿N＿RECS的属性，该属性代表了该页面中目前有多少条记录。所以，如果区间最左记录和区间最右记录所在的页面相隔不太远，我们可以直接遍历这些页面，把这些页面中的PAGE_N＿RECS属性值加起来就好了。
否则只沿着区间最左记录向右读10个页面，计算每个页面平均包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。那么问题又来了：怎么估计区间最左记录和区间最右记录之间有多少个页面呢？要解决这个问题，还得回到B +树索引的结构中来，如图12-2所示。
在图12-2中，假设区间最左记录在页b中，区间最右记录在页c中，那么要计算区间最左记录和区间最右记录之间的页面数量，就相当于计算页b和页c之间有多少页面。而每一条目录项记录都对应一个数据页，所以计算页b和页c之间有多少页面就相当于计算它们的父节点（也就是页a）中对应的目录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本就相当低了。
不过还有问题：如果页b和页c之间的页面实在太多，以至于页b和页c对应的目录项记录都不在一个页面中该咋办？继续递归啊，也就是再统计页b和页c对应的目录项记录所在页之间有多少个页面。我们之前说过，一个B＋树能有4层就已经比较高了，所以这个统计过程也不是很消耗性能。
知道了如何统计二级索引某个扫描区间的记录数之后，就需要回到现实问题中来。根据上述算法测得uk_key2在区间（10,1000）中大约有95条记录。读取这95条二级索引记录需要付出的CPU成本就是95 * 0.2 + 0.01 = 19.01。其中95是需要读取的二级索引的记录条数，0.2是读取一条记录的成本常数，0.01是微调值。
在通过二级索引共取到记录之后，还需要干两件事儿。
- 根据这些记录的主键值到聚簇索引中执行回表操作
这里需要大家使劲睁大自己的眼睛看仔细了，设计MySQL的大叔在评估回表操作的I/O成本时依旧很豪放：他们认为每次回表操作都相当于访问一个页面，也就是说二级索引扫描区间中有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面I/O。前面在使用uk_key2二级索引执行查询时，预计有95条二级索引记录需要进行回表操作，所以回表操作带来的I/O成本就是95 * 1.0 = 95.0。其中95是预计的二级索引记录数，1.0是读取一个页面的I／O成本常数。
- 回表操作后得到完整的用户记录，然后再检测其他搜索条件是否成立。
回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然后再检测除key2 > 10 AND key2 < 1000这个搜索条件以外的其他搜索条件是否成立。因为我们通过扫描区间获取到的二级索引记录共有95条，这也就对应着聚簇索引中95条完整的用户记录。读取并检测这些完整的用户记录是否符合其余的搜索条件的CPU成本为95 * 0.2 = 19.0 。其中95是待检测记录的条数，0.2是检测一条记录是否符合给定搜索条件的成本常数。
所以本例中使用uk_key2执行查询的成本就如下所示。
- I/O成本：1.0 + 95 * 1.0 = 96.0（扫描区间的数量＋预估的二级索引记录条数）。
- CPU成本：95 * 0.2 + 0.01 + 95 * 0.2 = 38.01（读取二级索引记录的成本＋读取并检测回表操作后聚簇索引记录的成本）。
综上所述，使用uk_key2执行查询的总成本就是96.0 + 38.01 = 134.01。
需要注意的一点是，大家在阅读MySQL5.7.22版本的源代码时，会发现设计MySQL的大叔最初在比较使用uk_key2索引与使用全表扫描的成本时，在计算使用uk_key2索引的成本的过程中并没有把读取并检测回表操作后聚簇索引记录的CPU成本包含在内（也就是95x0.2）。按照这样的算法比较完成本之后，如果使用uk_key2索引的成本比较低，最终会再算一遍使用uk_key2索引的成本，此时会把读取并检测回表操作后聚簇索引记录的CPU成本包含在内。后面在分析使用idx_key1的查询成本时，也有这个问题。由于担心把里面的各种繁琐步骤都写出来会严重影响阅读体验，所以采用了更容易理解的成本计算方式来讲解。
2.2.3.2 使用idx_key1执行查询的成本分析
idx_key1对应的搜索条件是key1 IN ( ‘a’ , ‘b’ , ‘c’ )，也就是说相当于3个单点扫描区间：
- [ ‘a’ , ‘a’ ]；
- [ ‘b’ , ‘b’ ]；
- [ ‘c’ , ‘c’ ]。
使用idx_key1执行查询的示意图如图12-3所示。
与使用uk_key2的情况类似，我们也需要计算使用idx_key1时需要访问的扫描区间的数量以及需要回表的记录数。
- 扫描区间的数量
在使用idx_key1执行查询时，很显然有3个单点扫描区间，所以访问这3个扫描区间的二级索引付出的I/O成本就是3 * 1.0 = 3.0。
- 需要回表的记录数
由于在使用idx_key1时存在3个单点扫描区间，所以每个单点扫描区间都需要查找一遍对应的二级索引记录数。
- 查找单点扫描区间[ 'a', 'a' ]对应的二级索引记录数：计算单点扫描区间对应的二级索引记录数与计算范围扫描区间对应的二级索引记录数是一样的，都是先找到区间最左记录和区间最右记录，然后再计算它们之间的记录数。具体算法已经在前面唠叨过了，就不赘述了。最后计算得到的单点扫描区间[ 'a' , 'a' ]对应的二级索引记录数是35。
- 查找单点扫描区间[ 'b' , 'b' ]对应的二级索引记录数：与上同理，计算得到的本单点扫描区间对应的记录数是44。
- 查找单点扫描区间[ 'c' , 'c' ]对应的二级索引记录数：与上同理，计算得到的本单点扫描区间对应的记录数是39。
所以，这3个单点扫描区间总共需要回表的记录数就是35 + 44 + 39 = 118。读取这些二级索引记录的CPU成本就是118 * 0.2 + 0.01 = 23.61 。
在得到总共需要回表的记录数之后，还要考虑下述事项。
- 根据这些记录中的主键值到聚簇索引中执行回表操作：所需的IO成本就是118 * 1.0 = 118.0 。
- 针对回表操作后读取到的完整用户记录，比较其他搜索条件是否成立。这一步骤对应的CPU成本就是118 * 0.2 = 23.6。
所以本例中使用idx_key1执行查询的成本如下所示。
- I/O成本：3.0 * 1.0 + 118 * 1.0 = 121.0（扫描区间的数量＋预估的二级索引记录条数）。
- CPU成本：118 * 0.2 + 0.01 + 118 * 0.2 = 47.21（读取二级索引记录的成本＋读取并检测回表操作后聚簇索引记录的成本）。
综上所述，使用idx_key1执行查询的总成本就是121.0 + 47.21 = 168.21。
（3）是否有可能使用索引合并（Index Merge）
本例中有关key1和key2的搜索条件是使用AND操作符连接起来的，而对于idx＿key1和uk_key2都是范围查询。也就是说，查找到的二级索引记录并不是按照主键值进行排序的，不满足使用Intersection合并的条件，所以并不会使用索引合并。
MySQL查询优化器计算索引合并成本的算法也比较麻烦，所以这里也就不展开唠叨了。
2.2.4 对比各种执行方案的代价，找出成本最低的那个方案
下面把本例查询的各种可执行方案以及它们对应的成本列出来。
- 全表扫描的成本：2037.7。
- 使用uk_key2的成本：134.01。
- 使用idx_key1的成本：168.21。
很显然，使用uk_key2的成本最低，所以当然选择uk_key2来执行查询。
再一次强调，为了提升大家的阅读体验，前文的成本计算方式其实与MySQL5.7.22中的成本计算方式稍有不同，但是核心思路没变，我们把大致的精神传递正确就好了。对于自己阅读代码的读者，就需要更深层次地了解更多细节了。
另外，不论是采用idx_key1还是uk_key2执行查询，它们对应的都是range访问方法。在使用range访问方法执行查询时，扫描区间中包含多少条记录，优化器就认为需要进行多少次回表操作，也就相当于需要进行多少次页面I/O。不过对于ref访问方法来说，设计InnoDB的大叔在计算因回表操作带来的I/O成本时设置了天花板，也就是ref访问方法因回表操作带来的I／O成本最多不能超过相当于访问全表记录数的1/10个页面的I/O成本或者全表扫描的I/O成本的3倍。之所以设置这样的天花板，我觉得是因为在使用ref访问方法时，需要扫描的二级索引记录的id值离得更近，一次回表操作可能将多条需要访问的聚簇索引记录都从磁盘加载到了内存。也就是说，即使在range访问方法中与在ref访问方法中需要扫描的记录数相同，ref访问方法也更有优势。
