【MySQL的执行原理（五）】

1.单表访问之索引合并

MySQL 在一般情况下执行一个查询时最多只会用到单个二级 索引，但存在有特殊情况，在这些特殊情况下也可能在一个查询中使用到多个二 级索引，MySQL 中这种使用到多个索引来完成一次查询的执行方法称之为:索引 合并/index merge。具体的索引合并算法有下边三种。
Intersection 合并
比方说下边这个查询:
SELECT * FROM order_exp WHERE order_no = ‘a’ AND expire_time = ‘b’;
从 idx_order_no 二级索引对应的 B+树中取出 order_no= 'a’的相关记录。
从 idx_insert_time 二级索引对应的 B+树中取出 insert_time= 'b’的相关记录。
二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个 结果集中 id 值的交集。
按照上一步生成的 id 值列表进行回表操作，也就是从聚簇索引中把指定 id 值的完整用户记录取出来，返回给用户。
**为啥不直接使用 idx_order_no 或者 idx_insert_time 只根据某个搜索条件去读 取一个二级索引，然后回表后再过滤另外一个搜索条件呢?**这里要分析一下两种 查询执行方式之间需要的成本代价。
只读取一个二级索引的成本:
按照某个搜索条件读取一个二级索引，根据从该二级索引得到的主键值进行 回表操作，然后再过滤其他的搜索条件
读取多个二级索引之后取交集成本:
按照不同的搜索条件分别读取不同的二级索引，将从多个二级索引得到的主 键值取交集，然后进行回表操作。
虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读 取二级索引的操作是顺序 I/O，而回表操作是随机 I/O，所以如果只读取一个二级 索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常 少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更 高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。
MySQL 在某些特定的情况下才可能会使用到 Intersection 索引合并，哪些情 况呢?

情况一:等值匹配
二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列
都必须等值匹配，不能出现只匹配部分列的情况。
而下边这两个查询就不能进行 Intersection 索引合并:
SELECT * FROM order_exp WHERE order_no> ‘a’ AND insert_time = ‘a’ AND order_status = ‘b’ AND expire_time = ‘c’;
这个查询是因为对 order_no 进行了范围匹配，
SELECT * FROM order_exp WHERE order_no = ‘a’ AND insert_time = ‘a’;
这个查询是因为联合索 引 u_idx_day_status 中的 order_status 和 expire_time 列并没有出现在搜索条件中， 所以这两个查询不能进行 Intersection 索引合并。

情况二:主键列可以是范围匹配
省略

Union 合并

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合 另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是 OR 关系。 有时候 OR 关系的不同搜索条件会使用到不同的索引，比方说这样:
SELECT * FROM order_exp WHERE order_no = ‘a’ OR expire_time = ‘b’

情况一:等值匹配
分析同 Intersection 合并
情况二:主键列可以是范围匹配
省略

连接查询(重点已关注+)

连接的本质

这个过程看起来就是把 e1 表的记录和 e2 的记录连起来组成新的更大的记录， 所以这个查询过程称之为连接查询。连接查询的结果集中包含一个表中的每一条 记录与另一个表中的每一条记录相互匹配的组合，像这样的结果集就可以称之为 笛卡尔积。
因为表 e1 中有 3 条记录，表 e2 中也有 3 条记录，所以这两个表连接 之后的笛卡尔积就有 3×3=9 行记录。
在 MySQL 中，连接查询的语法很随意，只要在 FROM 语句后边跟多个表名 就好了，比如我们把 e1 表和 e2 表连接起来的查询语句可以写成这样:
SELECT * FROM e1, e2;

内连接和外连接

左(外)连接的语法
左(外)连接的语法还是挺简单的，比如我们要把 e1 表和 e2 表进行左外连 接查询可以这么写:
SELECT * FROM e1 LEFT [OUTER] JOIN e2 ON 连接条件 [WHERE 普通过滤条 件];

内连接的语法
内连接和外连接的根本区别就是在驱动表中的记录不符合 ON 子句中的连接 条件时不会把该记录加入到最后的结果集，一种最简单的内连接语法，就是直接
把需要连接的多个表都放到 FROM 子句后边。

对于内连接来说，选取哪个表为驱动表都没关系，而外连接的驱动表是固定 的，也就是说左(外)连接的驱动表就是左边的那个表，右(外)连接的驱动表 就是右边的那个表。

MySQL 的查询成本（重点+）

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

CPU 成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作 损耗的时间称之为 CPU 成本。
对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL 规定读取一个页面花费的成本默认是 1.0，读取以及检测一条记录是否符合搜索 条件的成本默认是 0.2。1.0、0.2 这些数字称之为成本常数，这两个成本常数我 们最常用到，当然还有其他的成本常数。
注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是 0.2。

单表查询的成本
在一条单表查询语句真正执行之前，MySQL 的查询优化器会找出执行该语句 所有可能使用的方案，对比之后找出成本最低的方案，这个成本最低的方案就是 所谓的执行计划，之后才会调用存储引擎提供的接口真正的执行查询，这个过程 总结一下就是这样:
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个
下边我们就以一个实例来分析一下这些步骤，单表查询语句如下:
SELECT * FROM order_exp
WHERE order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)
AND expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’
AND insert_time> expire_time
AND order_note LIKE ‘%7 排 1%’
AND order_status = 0;

我们分析一下上边查询中涉及到的几个搜索条件:
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) ，这个搜索条件可以使用二级索引 idx_order_no。
expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’，
这个搜索条件可以使用二级索引 idx_expire_time。
insert_time> expire_time，这个搜索条件的索引列由于没有和常数比较，所
以并不能使用到索引。
order_note LIKE ‘%hello%’，order_note 即使有索引，但是通过 LIKE 操作符和 以通配符开头的字符串做比较，不可以适用索引。
order_status = 0，由于该列上只有联合索引，而且不符合最左前缀原则，所 以不会用到索引。
综上所述，上边的查询语句可能用到的索引，也就是 possible keys 只有 idx_order_no,idx_expire_time。

计算全表扫描的代价

由 于查询成本=I/O 成本+CPU 成本，所以计算全表扫描的代价需要两个信息:
聚簇索引占用的页面数
该表中的记录数
这两个信息从哪来呢?MySQL 为每个表维护了一系列的统计信息，关于这些 统计信息是如何收集起来的我们放在后边再说，现在看看怎么查看这些统计信息。
MySQL 给我们提供了 SHOW TABLE STATUS 语句来查看表的统计信息，如果 要看指定的某个表的统计信息，在该语句后加对应的 LIKE 语句就好了，比方说 我们要查看 order_exp 这个表的统计信息可以这么写:
SHOW TABLE STATUS LIKE ‘order_exp’

Data_length
本选项表示表占用的存储空间字节数。使用 MyISAM 存储引擎的表来说，该 值就是数据文件的大小，对于使用 InnoDB 存储引擎的表来说，该值就相当于聚 簇索引占用的存储空间大小，也就是说可以这样计算该值的大小:
Data_length = 聚簇索引的页面数量 x 每个页面的大小
假如使用默认 16KB 的页面大小，而上边查询结果显示
Data_length 的值是 1589248，所以我们可以反向来推导出聚簇索引的页面数量:
聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所 以就可以计算全表扫描成本了。
现在可以看一下全表扫描成本的计算过程:
I/O 成本
97 x 1.0 + 1.1 = 98.1
97 指的是聚簇索引占用的页面数，1.0 指的是加载一个页面的成本常数，后 边的 1.1 是一个微调值。
TIPS:MySQL 在真实计算成本时会进行一些微调，这些微调的值是直接硬编 码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们分析。

CPU 成本
10350x 0.2 + 1.0 = 2071
10350x 指的是统计数据中表的记录数，对于 InnoDB 存储引擎来说是一个估 计值，0.2 指的是访问一条记录所需的成本常数，后边的 1.0 是一个微调值。
总成本:
98.1 + 2071 = 2169.1
综上所述，对于 order_exp 的全表扫描所需的总成本就是 2169.1。
TIPS:我们前边说过表中的记录其实都存储在聚簇索引对应 B+树的叶子节点 中，所以只要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组 成的双向链表把所有记录都查看一遍。
也就是说全表扫描这个过程其实有的 B+树非叶子节点是不需要访问的，但 是 MySQL 在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算 I/O成本的依据，是不区分非叶子节点和叶子节点的。

使用索引的成本分析

使用 idx_expire_time 执行查询的成本分析
idx_expire_time 对应的搜索条件是:expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’ ，也就是说对应的范围区间就是: (‘2021-03-22 18:28:28’ , ‘2021-03-22 18:35:09’ )。
使用 idx_expire_time 搜索会使用用二级索引 + 回表方式的查询，MySQL 计 算这种查询的成本依赖两个方面的数据:
1、范围区间数量
不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索 引的一个范围区间的 I/O 成本和读取一个页面是相同的。本例中使用 idx_expire_time 的范围区间只有一个，所以相当于访问这个范围区间的二级索引 付出的 I/O 成本就是:1 x 1.0 = 1.0
2、需要回表的记录数
优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来 说就是要计算 idx_expire_time 在(‘2021-03-22 18:28:28’ ，‘2021-03-22 18:35:09’) 这个范围区间中包含多少二级索引记录，计算过程是这样的:
步骤 1:先根据 expire_time> ‘2021-03-22 18:28:28’这个条件访问一下 idx_expire_time 对应的 B+树索引，找到满足 expire_time> ‘2021-03-22 18:28:28’ 这个条件的第一条记录，我们把这条记录称之为区间最左记录。我们前头说过在 B+数树中定位一条记录的过程是很快的，是常数级别的，所以这个过程的性能消 耗是可以忽略不计的。
步骤 2:然后再根据 expire_time<= ‘2021-03-22 18:35:09’这个条件继续从 idx_expire_time 对应的 B+树索引中找出最后一条满足这个条件的记录，我们把 这条记录称之为区间最右记录，这个过程的性能消耗也可以忽略不计的。
步骤 3:如果区间最左记录和区间最右记录相隔不太远(在 MySQL 5.7 这个 版本里，只要相隔不大于 10 个页面即可)，那就可以精确统计出满足 expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’条件的二级索引记 录条数。否则只沿着区间最左记录向右读 10 个页面，计算平均每个页面中包含 多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量 就可以了。那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少 个页面呢?解决这个问题还得回到 B+树索引的结构中来。
知道了如何统计二级索引某个范围区间的记录数之后，就需要回到现实问题 中来，MySQL 根据上述算法测得 idx_expire_time 在区间(‘2021-03-22 18:28:28’ ， ‘2021-03-22 18:35:09’)之间大约有 39 条记录。
读取这 39 条二级索引记录需要付出的 CPU 成本就是:
39 x 0.2 + 0.01 = 7.81
其中 39 是需要读取的二级索引记录条数，0.2 是读取一条记录成本常数，0.01 是微调。
在通过二级索引获取到记录之后，还需要干两件事儿:
1、根据这些记录里的主键值到聚簇索引中做回表操作
MySQL评估回表操作的I/O成本依旧很简单粗暴，他们认为每次回表操作都 相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少 次回表操作，也就是需要进行多少次页面 I/O。我们上边统计了使用 idx_expire_time 二级索引执行查询时，预计有 39 条二级索引记录需要进行回表 操作，所以回表操作带来的 I/O 成本就是:
39 x 1.0 = 39 .0
其中 39 是预计的二级索引记录数，1.0 是一个页面的 I/O 成本常数。
2、回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立
回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的 用户记录，然后再检测除 expire_time> ‘2021-03-22 18:28:28’ AND expire_time< '2021-03-22 18:35:09’这个搜索条件以外的搜索条件是否成立。
因为我们通过范围区间获取到二级索引记录共 39 条，也就对应着聚簇索引 中 39 条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索 条件的 CPU 成本如下:
39 x 0.2 =7.8
其中 39 是待检测记录的条数，0.2 是检测一条记录是否符合给定的搜索条 件的成本常数。
所以本例中使用 idx_expire_time 执行查询的成本就如下所示:
I/O 成本:
1.0 + 39 x 1.0 = 40 .0 (范围区间的数量 + 预估的二级索引记录条数)
CPU 成本:
39 x 0.2 + 0.01 + 39 x 0.2 = 15.61 (读取二级索引记录的成本 + 读取并检测 回表后聚簇索引记录的成本)
综上所述，使用 idx_expire_time 执行查询的总成本就是: 40 .0 + 15.61 = 55.61

idx_order_no也是一样分析
综上所述，使用 idx_order_no 执行查询的总成本就是: 61.0 + 23.21 = 84.21

比各种方案，找出成本最低的那一个 下边把执行本例中的查询的各种可执行方案以及它们对应的成本列出来:
全表扫描的成本:2169.1
使用 idx_expire_time 的成本:55.61
使用 idx_order_no 的成本:84.21
很显然，使用 idx_expire_time 的成本最低，所以当然选择 idx_expire_time 来执行查询。

请注意:1、MySQL 的源码中对成本的计算实际要更复杂，但是基本思想和 算法是没错的。
2、在 MySQL 的实际计算中，在和全文扫描比较成本时，使用索引的成本会 去除读取并检测回表后聚簇索引记录的成本，也就是说，我们通过 MySQL 看到 的成本将会是:idx_expire_time 为 47.81(55.61-7.8)，idx_order_no 为 72.61(84.21-11.6)。但是 MySQL 比较完成本后，会再计算一次使用索引的成本， 此时就会加上去除读取并检测回表后聚簇索引记录的成本，也就是我们计算出来 的值。

再深入 Explain

在 EXPLAIN 单词和真正的查询语句中间加上 FORMAT=JSON。 这样我们就可以得到一个 json 格式的执行计划，里边包含该计划花费的成
本，比如这样:

调节成本常数

读取一个页面花费的成本默认是 1.0
检测一条记录是否符合搜索条件的成本默认是 0.2
从 server_cost 中的内容可以看出来，目前在 server 层的一些操作对应的成 本常数有以下几种:
disk_temptable_create_cost 默认值 20.0 创建基于磁盘的临时表的成本，如 果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
disk_temptable_row_cost 默认值 0.5 向基于磁盘的临时表写入或读取一 条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
key_compare_cost 0.0.5 两条记录做比较操作的成本，多用在排序操作上， 如果增大这个值的话会提升 filesort 的成本，让优化器可能更倾向于使用索引完 成排序而不是 filesort。
memory_temptable_create_cost默认值1.0 创建基于内存的临时表的成本， 如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
memory_temptable_row_cost 默认值 0.1 向基于内存的临时表写入或读 取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临 时表。
row_evaluate_cost 默认值0.1 这个就是我们之前一直使用的检测一条记 录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是 直接全表扫描。

InnoDB中的统计数据

基于磁盘的永久性统计数据
当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里:
SHOW TABLES FROM mysql LIKE ‘innodb%’;

可以看到，这两个表都位于 mysql 系统数据库下边，其中: innodb_table_stats 存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
innodb_index_stats 存储了关于索引的统计数据，每一条记录对应着一个索 引的一个统计项的统计数据。
直接看一下这个 innodb_table_stats 表中的各个列都是干嘛的:

database_name 数据库名
table_name 表名
last_update 本条记录最后更新时间
n_rows 表中记录的条数
clustered_index_size 表的聚簇索引占用的页面数量 sum_of_other_index_sizes 表的其他索引占用的页面数量 我们直接看一下这个表里的内容:

n_rows 的值是 10350，表明 order_exp 表中大约有 10350 条记录，注意这个 数据是估计值。
clustered_index_size 的值是 97，表明 order_exp 表的聚簇索引占用 97 个页 面，这个值是也是一个估计值。
sum_of_other_index_sizes 的值是 81，表明 order_exp 表的其他索引一共占用 81 个页面，这个值是也是一个估计值。

n_rows 统计项的收集
InnoDB 统计一个表中有多少行记录是这样的:
按照一定算法(并不是纯粹随机的)选取几个叶子节点页面，计算每个页面 中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节 点的数量就算是该表的 n_rows 值。

innodb_index_stats
直接看一下这个 innodb_index_stats 表中的各个列都是干嘛的:

database_name 数据库名
table_name 表名
index_name 索引名
last_update 本条记录最后更新时间
stat_name 统计项的名称
stat_value 对应的统计项的值 sample_size为生成统计数据而采样的页面数量 stat_description 对应的统计项的描述
innodb_index_stats 表的每条记录代表着一个索引的一个统计项。

定期更新统计数据
随着我们不断的对表进行增删改操作，表中的数据也一直在变化， innodb_table_stats 和 innodb_index_stats 表里的统计数据也在变化。
MySQL 提供 了如下两种更新统计数据的方式:
开启 innodb_stats_auto_recalc。
手动调用 ANALYZE TABLE 语句来更新统计信息
再一次强调，InnoDB 默认是以表为单位来收集和存储统计数据的。

MySQL 的查询重写规则

对于一些执行起来十分耗费性能的语句，MySQL 还是依据一些规则，竭尽全 力的把这个很糟糕的语句转换成某种可以比较高效执行的形式，这个过程也可以 被称作查询重写。

再深入查询优化

MySQL 查询的生命周期大致可以按照顺序来看:从客户端，到服务器，然后 在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行” 可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存 储引擎的调用以及调用后的数据处理，包括排序、分组等。
在完成这些任务的时候，查询需要在不同的地方花费时间，包括网络，CPU 计算，生成统计信息和执行计划、锁等待(互斥等待)等操作，尤其是向底层存 储引擎检索数据的调用操作,这些调用需要在内存操作,CPU 操作和内存不足时导 致的 IO 操作上消耗时间。根据存储引擎不同，可能还会产生大量的上下文切换 以及系统调用。
优化查询的目的就是减少和消除这些操作所花费的时间。

查询执行的流程再回顾

MySQL 客户端/服务器通信协议
MySQL 客户端和服务器之间的通信协议是“半双工” 的，这意味着，在任何一个时刻，要么是由服务器向客户端发送数据，要么是由 客户端向服务器发送数据，这两个动作不能同时发生。

查询状态
对于一个 MySQL 连接，或者说一个线程，任何时刻都有一个状态，该状态 表示了 MySQL 当前正在做什么。在一个查询的生命周期中，状态会变化很多次。

通过 show profile 分析 SQL
当我们通过应用程序访问 MySQL 服务时，有时候性 能不一定全部卡在语句的执行上。当然通过慢查询日志定位那些执行效率较低的 SQL 语句时候我们常用的手段，但是:

一、慢查询日志在查询结束以后才记录，在应用反映执行效率出现问题的时 候查询未必执行完成;
二、有时候问题的产生不一定是语句的执行，有可能是其他原因导致的。慢 查询日志并不能定位问题。

show processlist
这个时候通过 show processlist;查看线程状态非常有用
1、首先检查当前 MySQL 是否支持 profile

2、默认 profiling 是关闭的，可以通过 set 语句在 Session 级别开启 profiling: select @@profiling;
set profiling=1;
3、执行一个 SQL 查询
select count() from order_exp;
4、通过 show profiles 语句，看到当前 SQL 的 Query ID show profiles;
5、通过 show profile for query 语句能够看到执行过程中线程的每个状态和消 耗的时间
通过仔细检查 show profile for query 的输出，能够发现在执行 COUNT()的过 程中，时间主要消耗在 Sending data 这个状态上。
6、在获取到最消耗时间的线程状态后，MySQL 支持进一步选择 all、cpu、 block io、contextswitch、page faults 等明细类型来查看 MySQL 在使用什么资源上 耗费了过高的时间:
show profile all for query 1G

能够发现 Sending data 状态下，时间主要消耗在 CPU 上了。