【MySQL学习】6.执行原理

MySQL 性能优化
MySQL学习
发布日期:   2023-06-07
文章字数:   6.1k
阅读时长:   22 分

1 单表访问之索引合并

MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，也可能在一个查询中使用到多个二级索引，MySQL中这种使用到多个索引来完成一次查询的执行方法称之为：索引合并/index merg。e

1.1 Intersection 合并

这里是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集，比方说下边这个查询:

SELECT * FROM order_exp WHERE order_no = 'a' AND expire_time = 'b';

假设这个查询使用 Intersection 合并的方式执行的话，那这个过程就是这样的:

  • 从 idx_order_no 二级索引对应的 B+树中取出 order_no= 'a'的相关记录。
  • 从 idx_expire_time 二级索引对应的 B+树中取出 expire_time= 'b'的相关记录。
  • 二级索引的记录都是由索引列 + 主键构成的，所以我们可以计算出这两个结果集中 id 值的交集。
  • 按照上一步生成的 id 值列表进行回表操作，也就是从聚簇索引中把指定 id 值的完整用户记录取出来，返回给用户。

为啥不直接使用 idx_order_no 或者 idx_expire_time 只根据某个搜索条件去读取一个二级索引，然后回表后再过滤另外一个搜索条件呢?

这里要分析一下两种查询执行方式之间需要的成本代价。

  • 只读取一个二级索引的成本：按照某个搜索条件读取一个二级索引，根据该二级索引得到的主键值进行回表操作，然后再过滤其他的搜索条件
  • 读取多个二级索引之后取交集成本：按照不同的搜索条件分别读取不同的二级索引，将从多个二级索引得到的主键值取交集，然后进行回表操作。

虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读取二级索引的操作是顺序 I/O，而回表操作是随机 I/O，所以如果只读取一个二级索引时需要回表的记录数特别多，而读取多个二级索引之后取交集的记录数非常少，当节省的因为回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

MySQL 在某些特定的情况下才可能会使用到 Intersection 索引合并，哪些情况呢?

  • 情况一：等值匹配：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。
  • 情况二：主键列可以是范围匹配。

之所以在二级索引列都是等值匹配的情况下才可能使用 Intersection 索引合并，是因为只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。Intersection 索引合并会把从多个二级索引中查询出的主键值求交集，如果从各个二级索引中查询的到的结果集本身就是已经按照主键排好序的，那么求交集的过程就很容易。

1.2 Union 合并

Union 是并集的意思，适用于使用不同索引的搜索条件之间使用 OR 连接起来的情况。

比如：

SELECT * FROM order_exp WHERE order_no = 'a' OR expire_time = 'b'

与 Intersection 索引合并类似，MySQL 在某些特定的情况下才可能会使用到 Union 索引合并:

  • 情况一：等值匹配分析同 Intersection 合并
  • 情况二：主键列可以是范围匹配 分析同 Intersection 合并
  • 情况三：使用 Intersection 索引合并的搜索条件。就是搜索条件的某些部分使用 Intersection 索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，比方说这个查询:
    SELECT * FROM order_exp WHERE insert_time = 'a' AND order_status = 'b' AND expire_time = 'c' OR (order_no = 'a' AND expire_time = 'b');
    优化器可能采用这样的方式来执行这个查询:
  • 先按照搜索条件 order_no = 'a' AND expire_time = 'b'从索引 idx_order_no 和 idx_expire_time 中使用 Intersection 索引合并的方式得到一个主键集合。
  • 再按照搜索条件 insert_time = 'a' AND order_status = 'b' AND expire_time = 'c'从联合索引 u_idx_day_status 中得到另一个主键集合。
  • 采用 Union 索引合并的方式把上述两个主键集合取并集，然后进行回表操作，将结果返回给用户。

当然，查询条件符合了这些情况也不一定就会采用 Union 索引合并，也得看优化器的心情。优化器只有在单独根据搜索条件从某个二级索引中获取的记录数比较少，通过 Union 索引合并后进行访问的代价比全表扫描更小时才会使用 Union 索引合并。

1.3 Sort_Union 合并

先按照二级索引记录的主键值进行排序，之后按照 Union 索引合并方式执行的方式称之为 Sort-Union 索引合并.

1.4 联合索引替代Intersection 索引合并

SELECT * FROM order_exp WHERE order_no= 'a' And expire_time= 'z';

这个查询之所以可能使用 Intersection 索引合并的方式执行，还不是因为 idx_order_no 和 idx_expire_time 是两个单独的 B+树索引，要是把这两个列搞一个联合索引，那直接使用这个联合索引就把事情搞定了，何必用啥索引合并呢，就像这样:

ALTER TABLE order_exp drop index idx_order_no, idx_expire_time,
add index idx_order_no_expire_time(order_no, expire_time);

这样我们把 idx_order_no, idx_expire_time 都干掉，再添加一个联合索引 idx_order_no_expire_time，使用这个联合索引进行查询简直是又快又好，既不用多读一棵 B+树，也不用合并结果。

2 连接查询

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。

3 MySQL 的查询成本

3.1 成本组成

MySQL 执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的那种方案去真正地执行查询。

在 MySQL 中一条查询语句的执行成本是由下边这两个方面组成的:

  • I/O成本：表、索引等数据是存储在磁盘上的，当用户查询表数据时，MySQL需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为 I/O 成本。
  • CPU成本：读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为 CPU 成本。
  • 成本常数：对于 InnoDB 存储引擎来说，页是磁盘和内存之间交互的基本单位。常用的成本常数有（当然还有其他的成本常数）：
    • 读取一个页面花费的成本默认是 1.0
    • 读取以及检测一条记录是否符合搜索条件的成本默认是 0.2。

      注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是 0.2。

3.2 单表查询的成本

3.2.1 基于成本的优化步骤实战

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

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

3.2.2 举例

现有以下表结构

CREATE TABLE order_exp
(
    `id`              bigint(22)   NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
    `order_no`        varchar(50)  NOT NULL COMMENT '订单的编号',
    `order_note`      varchar(100) NOT NULL COMMENT '订单说明',
    `insert time`     datetime(0)  NOT NULL COMMENT '插入订单的时间',
    `expire_duration` bigint(22)   NOT NULL COMMENT '订单的过期时长, 单位秒',
    `expire_time`     datetime(0)  NOT NULL COMMENT '订单的过期时间',
    `order_status`    smallint(6)  NOT NULL DEFAULT 0 COMMENT '订单状态：0:未支付，1:已支付，-1: 已过期、关闭',
    PRIMARY KEY (`id`) USING BTREE,
    UNIQUE INDEX 'idx day status' (`insert time`, `order_status`) USING BTREE
) ENGINE = InnoDB
  AUTO_INCREMENT = 16
  CHARACTER SET utf8mb4
  COLLATE utf8_general_ci COMMENT ='订单表';

查询语句如下:

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 '%hello%'
  AND order_status = 0;

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

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

  • 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。

Tips：MySQL把一个查询中可能使用到的索引称之为 possible keys。

3.2.2.2 计算全表扫描的代价

对于InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。

由于查询成本 = I/O 成本 + CPU 成本，所以计算全表扫描的代价需要两个信息:

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

MySQL给我们提供了 SHOW TABLE STATUS 语句来查看表的统计信息，以上表order_exp为例，查询该表的统计信息语句如下：

SHOW TABLE STATUS LIKE 'order_exp' \G

结果如下：

mysql> SHOW TABLE STATUS LIKE 'order_exp' \G
****************************1.row***************************
Name: order_exp
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 10350
Avg_row_length: 153
Data_length: 1589248
Max_data_length: 0
Index_length: 1130496
Data_free: 4194304
Auto_increment: 10819
Create_time: 2021-03-31 15:39:43
Update_time: 2021-04-05 18:43:17
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

我们只需要关心这两个参数：

  • Rows：表示表中的记录条数。对于使用 MyISAM 存储引擎的表来说，该值是准确的，对于使用 InnoDB 存储引擎的表来说，该值是一个估计值。
  • Data_length：表示表占用的存储空间字节数。使用 MyISAM 存储引擎的表来说，该值就是数据文件的大小，对于使用 InnoDB 存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小:
    Data_length = 聚簇索引的页面数量 * 每个页面的大小
    这里 order_exp 使用默认 16KB 的页面大小，而上边查询结果显示Data_length的值是1589248，所以我们可以反向来推导出聚簇索引的页面数量:
    聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97

现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了。

  • I/O 成本：97 * 1.0 + 1.1 = 98.1，97指的是聚簇索引占用的页面数，1.0指的是加载一个页面的成本常数，后边的1.1是一个微调值。

    TIPS：MySQL 在真实计算成本时会进行一些微调，这些微调的值是直接硬编码到代码里的，没有注释而且这些微调的值十分的小，并不影响我们分析。

  • CPU 成本：10350 * 0.2 + 1.0 = 2071，10350指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计值，0.2指的是访问一条记录所需的成本常数，后边的1.0是一个微调值。
  • 总成本：98.1 + 2071 = 2169.1

综上所述，对于 order_exp 的全表扫描所需的总成本就是2169.1。

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

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

MySQL查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本。这里的表order_exp只有两个普通二级索引，先算哪个都可以。

在计算全表扫描的代价一节中，可能用到的索引有idx_order_no,idx_expire_time。因而接下来，我们分析这两个索引的查询成本分别是多少。

3.2.2.3.1 使用 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 计算这种查询的成本依赖两个方面的数据:

  • 范围区间数量：不论某个范围区间的二级索引到底占用了多少页面，查询优化器认为读取索引的一个范围区间的 I/O 成本和读取一个页面是相同的。

    本例中使用 idx_expire_time 的范围区间只有一个，所以相当于访问这个范围区间的二级索引付出的 I/O 成本就是：1 * 1.0 = 1.0

  • 需要回表的记录数：优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是要计算 idx_expire_time 在('2021-03-22 18:28:28' ，'2021-03-22 18:35:09')这个范围区间中包含多少二级索引记录，计算过程是这样的:
    • 先根据 expire_time> '2021-03-22 18:28:28'这个条件访问一下 idx_expire_time 对应的 B+树索引，找到满足 expire_time> '2021-03-22 18:28:28' 这个条件的第一条记录，我们把这条记录称之为区间最左记录。
    • 再根据 expire_time<= '2021-03-22 18:35:09'这个条件继续从 idx_expire_time 对应的 B+树索引中找出最后一条满足这个条件的记录，我们把这条记录称之为区间最右记录。
    • 如果区间最左记录和区间最右记录相隔不太远(在 MySQL 5.7 这个版本里，只要相隔不大于 10 个页面即可)，那就可以精确统计出满足 expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09'条件的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。

那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？

解决这个问题还得回到B+树索引的结构中来。我们假设区间最左记录在页b中，区间最右记录在页c中，那么我们想计算区间最左记录和区间最右记录之间的页面数量，就相当于计算页b和页c之间有多少页面，而它们父节点中记录的每一条目录项记录都对应一个数据页，所以计算页b和页c之间有多少页面就相当于计算它们父节点(也就是页a)中对应的目录项记录之间隔着几条记录。在一个页面中统计两条记录之间有几条记录的成本就很小了。

不过还有问题，如果页 b 和页 c 之间的页面实在太多，以至于页 b 和页 c 对应的目录项记录都不在一个父页面中怎么办？

既然是树，那就继续递归，一个B+树有4层高已经很不得了了，所以这个统计过程也不是很耗费性能。

回到正题，现在计算idx_expire_time所花费的CPU成本。

MySQL根据上述算法测得 idx_expire_time 在区间('2021-03-22 18:28:28' ， '2021-03-22 18:35:09')之间大约有 39 条记录。可执行以下语句得到记录数Row：

explain SELECT * FROM order_exp WHERE expire_time> '2021-03-22 18:28:28' AND expire_time<= '2021-03-22 18:35:09';

读取这 39 条二级索引记录需要付出的 CPU 成本就是:

39 x 0.2 + 0.01 = 7.81

其中 39 是需要读取的二级索引记录条数，0.2是读取一条记录成本常数，0.01是微调。

再计算I/O成本。

在通过二级索引获取到记录之后，还需要干两件事儿:
1）根据这些记录里的主键值到聚簇索引中做回表操作
MySQL评估回表操作的I/O成本依旧很简单粗暴，其认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面 I/O。

上面得到了39条记录，需要进行39次的回表，因而得出I/O成本为：

39 * 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 * 0.2 =7.8

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

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

  • I/O 成本：1.0 + 39 * 1.0 = 40 .0 (范围区间的数量 + 预估的二级索引记录条数)
  • CPU 成本：39 * 0.2 + 0.01 + 39 * 0.2 = 15.61 (读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本)

综上所述，使用 idx_expire_time 执行查询的总成本就是: 40 .0 + 15.61 = 55.61

3.2.2.3.2 使用 idx_order_no 执行查询的成本分析

idx_order_no 对应的搜索条件是：order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S')，也就是说相当于 3 个单点区间。

与使用 idx_expire_time 的情况类似，我们也需要计算使用 idx_order_no 时需要访问的范围区间数量以及需要回表的记录数，计算过程与上面类似，我们不详列所有计算步骤和说明了。

  • 范围区间数量
    使用 idx_order_no 执行查询时很显然有 3 个单点区间，所以访问这 3 个范围区间的二级索引付出的 I/O 成本就是：
    3 * 1.0 = 3.0
  • 需要回表的记录数
    由于使用 idx_expire_time 时有 3 个单点区间，所以每个单点区间都需要查找一遍对应的二级索引记录数，三个单点区间总共需要回表的记录数是58。语句如下：
    explain SELECT * FROM order_exp WHERE order_no IN ('DD00_6S', 'DD00_9S', 'DD00_10S');
    读取这些二级索引记录的 CPU 成本就是：
    58 * 0.2 + 0.01 = 11.61

再计算I/O成本。

根据这些记录里的主键值到聚簇索引中做回表操作，所需的 I/O 成本就是:

58 * 1.0 = 58.0

回表操作后得到的完整用户记录，然后再比较其他搜索条件是否成立。此步骤对应的 CPU 成本就是:

58 * 0.2 = 11.6

综上，使用 idx_order_no 执行查询的成本就如下所示:

  • I/O 成本：3.0 + 58 x 1.0 = 61.0 (范围区间的数量 + 预估的二级索引记录条数)
  • CPU 成本：58 x 0.2 + 58 x 0.2 + 0.01 = 23.21 (读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本)

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

3.2.2.3.3 是否有可能使用索引合并(Index Merge)

本例中有关 order_no 和 expire_time 的搜索条件是使用 AND 连接起来的，而对于 idx_order_no 和 idx_expire_time 都是范围查询，也就是说查找到的二级索引记录并不是按照主键值进行排序的，并不满足使用 Intersection 索引合并的条件，所以并不会使用索引合并。而且 MySQL 查询优化器计算索引合并成本的算法也比较麻烦，所以我们也不会细说。

3.2.3 对比各种方案，找出成本最低的那一个

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

  • 全表扫描的成本：2148.7
  • 使用 idx_expire_time 的成本：55.61
  • 使用 idx_order_no 的成本：84.21

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

Warning:

  • MySQL 的源码中对成本的计算实际要更复杂，但是基本思想和算法是没错的。

  • 在 MySQL 的实际计算中，在和全文扫描比较成本时，使用索引的成本会去除读取并检测回表后聚簇索引记录的成本，也就是说，我们通过 MySQL 看到的成本将会是：

    • idx_expire_time 为47.81(55.61-7.8)，
    • idx_order_no 为 72.61(84.21-11.6)。

    但是 MySQL 比较完成本后，会再计算一次使用索引的成本，此时就会加上读取并检测回表后聚簇索引记录的成本，也就是我们计算出来的值。

    4 InnoDB 中的统计数据

5 MySQL 的查询重写规则

6 再深入查询优化

文章作者: Kezade
文章链接: https://zyxelva.github.io/posts/3501501421.html
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 Kezade !
MySQL 性能优化
赏
评论
  目录