Mysql是怎样运行的：第十二章笔记

Mysql是怎样运行的：第十二章笔记

什么是成本

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

  • I/O 成本

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

  • CPU 成本

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

以 InnoDB 存储引擎为例，页是磁盘和内存之间交互的基本单位，那么读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2，1.0、0.2这些数字称之为成本常数。

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


单表查询的成本

前置准备

准备表 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(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)
) Engine=InnoDB CHARSET=utf8;

自行向表中插入 10000 条记录，除 id 列外其余列插入随机值即可，插入过程不再赘述。


基于成本的优化步骤

执行一条单表查询语句时，MySQL 查询优化器会找出执行该语句所有可能使用的方案，并找到其成本最低的方案，这便是执行计划。之后，才会调用存储引擎提供的接口真正的执行查询。

上述过程总结如下：

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

下面我们以一个查询实例来进行分析，查询实例如下：

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';

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

一个查询中可能使用到的索引被称为 Possible Keys 。

再次重申，对于 B+ 树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=（不等于也可以写成<>）或者LIKE操作符连接起来，就可以产生一个所谓的范围区间。

其中，Like 操作符只有在匹配字符串前缀的时候，才可以使用索引。

基于这些，我们分析一下上面提出的查询实例用到了哪些索引。

  • key1 IN ('a', 'b', 'c')，这个搜索条件可以使用二级索引 idx_key1 。
  • key2 > 10 AND key2 < 1000，这个搜索条件可以使用二级索引 idx_key2 。
  • key3 > key2 并不是索引列和常数比较，无法使用索引。
  • key_part1 LIKE '%hello%'不只是匹配字符串前缀，无法使用索引。
  • common_field = '123'没有对应索引，无法使用索引。

即，查询实例能使用的索引仅有 idx_key1 和 idx_key2 。


计算全表扫描的代价

假如对查询实例使用全表扫描，我们来计算一下它的成本。

全表扫描的过程即把聚簇索引中的记录依次和给定的搜索条件做比较，然后把符合搜索条件的记录加入到结果集。这个过程需要将聚簇索引对应的页面加载到内存中，然后检测记录是否符合搜索条件。

简而言之，计算全表扫描的代价需要两个信息：

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

通过SHOW TABLE STATUS加上 Like 关键字可以对表进行筛选，从而查询出一系列的统计信息（每个表都维护了一系列的统计信息），其中就包含了对应的表记录数信息。例如：

-- 查询 single_table 表的统计信息
SHOW TABLE STATUS LIKE 'single_table'\G

-- single_table 表的统计信息
*************************** 1. row ***************************
           Name: single_table
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           -- 表记录数。对于使用 MyISAM 存储引擎的表是准确值，对于使用 InnoDB 存储引擎的表是估计值
           -- 我们已知表 single_table 含有 10000 条记录，使用 InnoDB 存储引擎，很明显，9693 是估计值
           Rows: 9693
 Avg_row_length: 163
 	-- 表占用的存储空间字节数。
 	-- 对于使用 MyISAM 存储引擎的表，该值就是数据文件的大小
 	-- 对于使用 InnoDB 存储引擎的表，该值相当于聚簇索引占用的存储空间大小。
 	-- Data_length 单位为 Byte
    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:

通过 Rows 字段，我们知晓了 single_table 表的表记录数估计值为 9693 。

对于 Data_length 字段，我们可以进一步知晓，对于使用 InnoDB 存储引擎的表，有公式如下：

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

即聚簇索引的页面数量为：

1589248 / 16 / 1024 = 97（页）

注意：single_table 使用默认 16KB 的页面大小。

现在我们可以开始计算全表扫描的成本了，已知查询成本 = I/O 成本 + CPU 成本，且，有计算过程如下：

  • I/O 成本 = 97 * 1.0 + 1.1 = 98.1

    其中 1.0 是使用 InnoDB 存储引擎的表读取一个页面花费的默认成本，1.1 是一个微调值，不用在意。

  • CPU 成本 = 9693 * 0.2 + 1.0 = 1939.6

    其中 0.2 是使用 InnoDB 存储引擎的表读取以及检测一条记录是否符合搜索条件的默认成本，1.0 是一个微调值，不用在意。

综上所述，对 single_table 表进行全表扫描的总成本为 2037.7（98.1 + 1939.6）。

其实，对于全表扫描而言 B+ 树内节点是不需要访问的（B+ 树的叶子节点组成一个双向链表，所以只要知道最左边的叶子节点，就可以遍历所有记录），但是 MySQL 在计算在计算全表扫描成本时直接使用聚簇索引占用的页面数作为计算 I/O 成本的依据，是不区分内节点和叶子节点的。有点简单粗暴，注意一下即可。


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

MySQL 查询优化器会先分析使用唯一二级索引的成本，而后再分析使用普通索引的成本。所以，我们先来分析一下对查询实例使用 idx_key2 唯一二级索引的成本。


使用 idx_key2 执行查询的成本分析

对于使用 二级索引+回表 方式的查询，计算其成本依赖于两方面的数据。

  • 范围区间数量

    查询优化器粗暴的认为读取索引的一个范围区间的 I/O 成本和读取一个页面是相同的。搜索条件key2 > 10 AND key2 < 1000能确定的范围区间只有一个，即(10, 1000)。所以访问这个范围区间的二级索引付出的 I/O 成本为：1 * 1.0 = 1 。

  • 需要回表的记录数

    查询优化器会先根据条件key2 > 10访问idx_key2对应的 B+ 树索引，找到满足key2 > 10条件的第一条记录，这条记录被称为区间最左记录。在 B+ 树中定位一条记录的过程是是常数级别的，所以这个过程的性能消耗可以忽略不计。

    而后再根据条件key2 < 1000访问idx_key2对应的 B+ 树索引，找到满足key2 < 1000条件的第一条记录，这条记录被称为区间最右记录。同样，这个过程的性能消耗可以忽略不计。

    最后，若区间最左记录和区间最右记录相隔不远（在MySQL 5.7.21这个版本里，只要相隔不大于10个页面即可），就可以精确的统计出满足搜索条件key2 > 10 AND key2 < 1000的二级索引记录条数。否则沿着区间最左记录向右读 10 个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量即可。

    那如何计算这个页面数量呢？我们知道目录项记录和数据页是一对一的关系，也就是说我们只要知道区间最左记录和区间最右记录所在的数据页对应的目录项记录之间有多少条记录，即知道了区间最左记录和区间最右记录之间有多少页面数量。

    假设区间最左记录在 页A ，区间最右记录在 页B 。所以计算 页A 和 页B 之间有多少页面就相当于计算它们父节点中对应的目录项记录之间隔着几条记录。若 页A 和 页B 之间的页面实在太多，以至于 页A 和 页B 对应的目录项记录都不在一个页面（父节点）中，那就递归计算即可。因为 B+ 树有 4 层高就已经很罕见了，所以这个统计过程不是很耗费性能。

    综上所述，假设 idx_key2 在区间(10, 1000)之间大约有 95 条记录。那么读取这 95 条记录的 CPU 成本为：95 * 0.2 + 0.01 = 19.01 。其中 0.01 是一个微调值，不用在意。

    还没完呢，这只是计算出了在二级索引中查询的成本，还有回表的成本呢。

    MySQL 只计算这个查找过程所需的 I/O 成本（即在内存中定位完整用户记录的成本忽略不计），并且认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少次回表操作（即进行多少次页面 I/O ），即回表 95 次。所以，回表的操作带来的 I/O 成本为：95 * 1.0 = 95.0 。

    还是没有结束，因为我们还有最后一步，根据回表操作后得到的完整用户记录，检测其他搜索条件是否成立。

    检测其他搜索条件是否成立的 CPU 成本为：95 * 0.2 = 19.0 。

综上所述，使用 idx_key2 执行查询的总成本为：1 + 19.01 + 95.0 + 19.0 = 134.01 。


使用 idx_key1 执行查询的成本分析

二级索引 idx_key1 对应搜索条件为key1 IN ('a', 'b', 'c')，这个搜索条件确定了三个单点区间，分别是['a', 'a']、['b', 'b']和['c', 'c']。

同样的，因为这也是使用 二级索引+回表 的方式进行的查询，所以我们当我们计算其成本时，也需要考虑两方面的数据。

  • 范围区间数量

    三个单点区间也就意味着三个范围区间，同样的，查询优化器粗暴的认为读取索引的一个范围区间的 I/O 成本和读取一个页面是相同的。所以访问这3个范围区间的二级索引付出的 I/O 成本为：3 * 1.0 = 3.0 。

  • 需要回表的记录数

    这里的过程可以参考使用 idx_key2 执行查询的成本分析中计算需要回表的记录数的过程。

    在确定了一个单点区间的 区间最左记录 和 区间最右记录 后，我们就可以开始计算 区间最左记录 和 区间最右记录 之间的记录数了。

    我们直接给出结果（假设）：

    单点区间['a', 'a']对应的二级索引记录数为：35 。

    单点区间['b', 'b']对应的二级索引记录数为：44 。

    单点区间['c', 'c']对应的二级索引记录数为：39 。

    即这三个单点区间总共需要回表的记录数为：35 + 44 + 39 = 118 。

    那么，读取这 118 条记录的 CPU 成本为：118 * 0.2 + 0.01 = 23.61 。

    继续推进，我们可以计算出这 118 条记录的主键值到聚簇索引中做回表操作的 I/O 成本为：118 * 1.0 = 118.0 。

    最后，就是比较其他搜索条件是否成立的 CPU 成本了，为：118 * 0.2 = 23.6 。

综上所述，使用二级索引 idx_key1 执行查询的总成本为：3.0 + 23.61 + 118.0 + 23.6 = 168.21 。


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

MySQL 只会在两种特定的情况下使用 Intersection 索引合并：

  • 二级索引列是等值匹配的情况。
  • 主键列可以是范围匹配。

很明显，虽然有关 key1 和 key2 的搜索条件是使用 AND 关键字连接起来的，但是对于二级索引 idx_key1 和唯一二级索引 idx_key2 而言，查询都是范围查询，不符合上述特定情况中，二级索引列是等值匹配的情况（不是等值匹配意味着查找到的二级索引记录不一定是按照主键值进行排序的）。所以，并不会使用索引合并。

注意：这里由于 MySQL 查询优化器计算索引合并成本的算法比较麻烦，所以也就没过多介绍了。


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

明显的，使用唯一二级索引 idx_key2 的成本最低，所以最后用于执行查询的索引为：idx_key2 。

注意：优化器在单表查询中对比各种执行方案代价的方式并不是简单的数值对比，即选取成本数值最小的执行方案。但其对比方式可以简单的理解为数值对比。


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

使用索引执行查询时，可能会有许多的单点区间。例如，对拥有普通二级索引的列使用 IN 关键字会产生许多的单点区间。

那么，当 IN 关键字涉及使用的索引为普通二级索引（若为唯一二级索引，就可以确定一个单点区间对应的二级索引记录的条数有多少了），那么我们就需要去计算，一个单点区间对应的二级索引记录的条数有多少。计算的过程可以参考使用 idx_key2 执行查询的成本分析中计算需要回表的记录数的过程。这过程使用的是一种通过直接访问索引对应的 B+ 树来计算某个范围区间对应的索引记录条数的方式，这种方式被称为：Index Dive 。

当 IN 关键字括号中的参数只有几个，使用 Index Dive 方式计算单点区间对应的记录数无伤大雅，但是，如果参数数量庞大呢？假设有 20000 个！那就要进行 20000 次 Index Dive 操作！这样的话计算索引成本的成本，可能就已经超过了全表扫描的成本！

为了限制上述情况，存在系统变量eq_range_index_dive_limit，我们可以通过 SQL 语句 SHOW VARIABLES LIKE '%dive%'; 来查看他的值，默认是 200 。

这个系统变量的作用就是，当 IN 关键字括号中的参数少于 200 个，即使用 Index Dive 方式计算单点区间对应的记录数，大于或等于的话就不会使用 Index Dive 了，而是使用索引统计数据来进行估算。

MySQL 不仅会为每个表维护一份统计数据，也会为表中的每一个索引维护一份统计数据。索引统计数据可以通过 SHOW INDEX FROM 表名 查出，它的结构如下：

属性名 描述
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 索引注释信息。

这里我们需要着重阐述一下统计数据的 Cardinality 字段，中文译为基数，表示索引列中不重复值的个数。也就是说，对于一个 10000 行记录的表来说，某个索引列的 Cardinality 字段值若为 10000 ，则表示该列中没有重复的值。若 Cardinality 字段值为 1 ，则意味着该列的值全部是重复的。需要注意的是，对于 InnoDB 存储引擎，使用 SHOW INDEX 语句展示出来的某个索引列的 Cardinality 属性是一个估计值。

那，Cardinality 字段有什么用处呢？当然是估值，如何估计？

使用SHOW TABLE STATUS语句我们可以查询出表的统计数据，其中的 Rows 字段就记录了我们查询的表中有多少条记录。

使用SHOW INDEX语句我们可以查询出，表对应索引，索引对应的索引列中不重复值的数量，即 Cardinality 字段。

Rows 字段的值和数据 Cardinality 字段的值，就是我们所说的索引统计数据。

于是，我们就可以计算出，索引列中平均一个值重复多少次，即一个值的重复次数 = Rows ÷ Cardinality。

假设 single_table 表的 idx_key1 索引，表的 Rows 值为 9693 ，idx_key1 索引的 Cardinality 值为 968 。那么对于 key1 列而言，平均单个值的重复次数为：9693 ÷ 968 ≈ 10。

那么，如果对拥有普通二级索引的列使用 IN 关键字，其括号中的参数有 20000 个，则 IN 关键字确定出的单点区间的总二级索引记录的条数，就是 20000 * 10 即 200000 个！这也是最终需要回表的总记录数。

这样估算单点区间对应的索引记录条数可太简单了，但是他致命就致命在它是估值！这就意味着，使用统计数据算出来的查询成本与实际所需的成本可能相差非常大！

注意：在 MySQL 5.7.3 以及之前的版本中，eq_range_index_dive_limit 的默认值为 10 ，之后的版本默认值为200。当查询成本的计算方式多为索引统计数据的方式而不是 Index Dive 方式，就可以考虑是否是 eq_range_index_dive_limit 值太小导致的。


连接查询的成本

我们构建一个和 single_table 表一模一样的 single_table2 表，并将 single_table 表称为 s1 表，single_table2 表称为 s2 表。


Condition Filtering 介绍

我们知道对于 MySQL 的连接查询而言，它使用的是嵌套循环连接算法。也就是说，驱动表会被访问一次，被驱动表可能会被访问多次（被驱动表的查询次数取决于对驱动表进行查询后得到的记录条数）。那么，对于两表连接查询来说，它的查询成本由两方面构成：

  • 单次查询驱动表的成本。
  • 多次查询被驱动表的成本。

我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文：Fanout），可以确定的是，扇出值越小，被驱动表的查询次数也就越少，连接查询的总成本就越低。

有时候计算扇出值是非常容易的，如：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2;

假设 s1 表为驱动表，显然对驱动表的单表查询使用的只能是全表扫描，那么扇出值就是驱动表中总记录数。在 s1 表的统计数据中，我们可以拿到这个总记录数，姑且设为 9693 吧。也就是说，查询优化器会直接把 9693 这个值当作 s1 表的扇出值。

再来一个简单的例子：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
WHERE s1.key2 >10 AND s1.key2 < 1000;

仍然假设 s1 是驱动表。显然，对于驱动表我们可以使用 idx_key2 索引执行查询。从查询语句中我们可以确定 idx_key2 索引对应的范围区间是(10, 1000)，那么其中有多少条记录呢？姑且设为 95 条吧，这便是 s1 表的扇出值。

现在来点复杂的。

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
WHERE s1.common_field > 'xyz';

查询优化器是不会去执行真正的查询的，本查询语句的查询条件也不适用于任何索引，所以查询优化器只能猜，猜 s1 表中有多少条记录满足查询条件s1.common_field > 'xyz'; 。上文中我们设了 s1 表的总记录数为 9693 ，也就是说，查询优化器会在这 9693 条记录中猜测符合查询条件的记录。

同样的，对于下面的查询语句：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
WHERE s1.key2 > 10 AND s1.key2 < 1000 AND
s1.common_field > 'xyz';

这个查询语句可以使用 idx_key2 索引，在上文中，我们设了 idx_key2 索引对应的范围区间记录数为 95 条，但是我们并不知道这 95 条记录中，我们有多少条记录符合查询条件s1.common_field > 'xyz';，这时候查询优化器也只能猜。

以此类推，最后来一个查询语句：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
WHERE s1.key2 > 10 AND s1.key2 < 1000 AND
s1.key1 IN ('a', 'b', 'c') AND
s1.common_field > 'xyz';

这个查询语句也可以使用 idx_key2 索引，还是设 idx_key2 索引对应的范围区间记录数为 95 条，那么，查询优化器就要在以下的两个搜索条件中，猜 95 条记录中有多少同时符合这两个搜索条件的记录：

  • s1.key1 IN ('a', 'b', 'c')
  • s1.common_field > 'xyz'

综上所述，计算扇出值情况可以分以下两种：

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

虽然情况分两种，但有一个共同的特点，就是猜。

这个猜的过程被称为 Condition Filtering 。这个过程可能会用到索引、统计数据或者就是单纯的瞎猜（也不完全是瞎猜，因为这里的瞎猜被专业术语称之为：启发式规则，英文 heuristic ）。由于过程复杂，不再深入研究。

注意：在MySQL 5.7之前的版本中，查询优化器计算动表扇出值，如果是使用全表扫描的话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就直接使用满足范围条件的索引记录条数作为扇出值。在之后的版本中才引入了 Condition Filtering ，目的就是让成本估算更加精确。


两表连接的成本分析

连接查询的成本计算公式如下：

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本

明显的，对于左、右外连接而言，驱动表和被驱动表是固定的，所以对于它们，只需要分别为驱动表和被驱动表选择成本最低的访问方法即可得到最优的查询方案。

但是对于内连接，驱动表和被驱动表是可以调换的，那么，我们就需要考虑两方面的问题了：

  • 最优的表连接顺序（因为不同的表作为驱动表最终的查询成本可能不同）。
  • 分别为驱动表和被驱动表选择成本最低的访问方法。

下面我们来看看如何计算出最优的内连接查询方案。

现有如下查询：

SELECT * FROM single_table AS s1 INNER JOIN single_table2 AS s2 
ON s1.key1 = s2.common_field 
WHERE s1.key2 > 10 AND s1.key2 < 1000 AND 
s2.key2 > 1000 AND s2.key2 < 2000;

查询优化器需要分别考虑以下两种情况下的最优查询成本。

  • s1 连接 s2（s1 为驱动表，s2 为被驱动表）
  • s2 连接 s1（s2 为驱动表，s1 为被驱动表）

然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。


以 s1 为驱动表

首先分析驱动表成本最低的执行方案。

涉及 s1 表单表的搜索条件有s1.key2 > 10 AND s1.key2 < 1000,这个搜索条件可能用到 idx_key2 索引，那么就需要在全表扫描和使用 idx_key2 这两个方案中进行选择，明显后者更好。

然后分析被驱动表成本最低的执行方案。

涉及 s2 表的搜索条件有：

  • s2.common_field = 常数 ,这里的常数是因为对驱动表s1结果集中的每一条记录，都需要进行一次被驱动表s2的访问，此时那些涉及两表的条件现在相当于只涉及被驱动表s2了。明显，这个搜索条件没什么用，因为它没用到任何索引。
  • s2.key2 > 1000 AND s2.key2 < 2000，这里用到了 idx_key2 索引。

那么对于 s2 表可用的方案也是全表扫描和使用 idx_key2 两种。

不考虑 Join Buffer 对成本的影响，以 s1 作为驱动表的总成本为：

使用idx_key2访问s1的成本 + s1的扇出 × 使用idx_key2访问s2的成本

以 s2 为驱动表

首先分析驱动表成本最低的执行方案。

涉及 s2 表单表的搜索条件有s2.key2 > 10 AND s2.key2 < 1000,这个搜索条件可能用到 idx_key2 索引，那么就需要在全表扫描和使用 idx_key2 这两个方案中进行选择，明显后者更好。

然后分析被驱动表成本最低的执行方案。

涉及 s1 表的搜索条件有：

  • s1.key1 = 常数
  • s1.key2 > 1000 AND s1.key2 < 2000

对于以上的搜索条件，使用 idx_key1 索引可以进行 ref 方式的访问，使用 idx_key2 索引可以进行 range 方式的访问。那么现在就需要在 idx_key1 索引、idx_key2 索引和全表扫描之间进行选择了。

注意：一般而言，ref 访问方式要比 range 访问方式成本更低。

使用 idx_key2 索引计算成本的方式我们已经熟知。但是使用 idx_key1 索引呢？对于它对应的搜索条件s1.key1 = 常数中的常数，我们是未知的。那怎么衡量？

我们不是还有索引统计数据吗？通过索引统计数据计算出的一个值的重复次数（即索引列平均一个值重复多少次），我们就可以衡量出使用 idx_key1 索引的成本了。

综上所述，以 s2 为驱动表时的总成本为：

使用idx_key2访问s2的成本 + s2的扇出 × 使用idx_key1访问s1的成本

总结

我们分别以 s1 表作为驱动表或者 s2 表作为驱动表分析了对应的成本组成，最后，优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。

综合两个不同的情况来看，占连接查询成本大头的是驱动表扇出数 x 单次访问被驱动表的成本，所以优化的重点也就突出了，即

  • 尽量减少驱动表的扇出
  • 对被驱动表的访问成本尽量低

我们应该在被驱动表的连接列上建立索引，如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。


多表连接的成本分析

首先需要考虑多表连接时可能产生出多少种连接顺序，相信学过排列组合的同学们都知道，n 个表能产生 n! 种连接顺序。

那对于 n 个表的 n! 种连接顺序，MySQL 查询优化器对每一种连接顺序的成本都要计算吗？

是的，都要，但是可以精简，精简的方式主要有以下三种：

  1. MySQL 在计算各种连接顺序的成本之前，会维护一个全局变量。这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序成本时，该成本已经超过当前最小的连接查询成本，那就不对该连接顺序继续往下分析。比方说 A、B、C 三个表进行连接，已经得到连接顺序 ABC 是当前的最小连接成本，为 10.0 。那么，在计算连接顺序 BCA 时，如果发现 B 和 C 的连接成本已经大于 10.0 了，就不再继续往后分析 BCA 这个连接顺序的成本了。
  2. optimizer_search_depth系统变量的值表示连接表的个数，如果连接表的个数小于该值，就继续穷举分析每一种连接顺序的成本，否则只对与optimizer_search_depth值相同数量的表进行穷举分析。显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长。否则，得到的不是很好的执行计划，但可以省掉很多分析连接成本的时间。
  3. 使用启发式规则（即根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序不分析。在 MySQL 中存在系统变量optimizer_prune_level来控制是否启用这些启发式规则。显然，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。

调节成本常数

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

  • 读取一个页面花费的成本默认是 1.0 。
  • 检测一条记录是否符合搜索条件的成本默认是 0.2 。

除了这两个成本常数以外，MySQL 还维护了很多的成本常数在 mysql 数据库中，可以通过以下 SQL 查询：

-- 查询语句
SHOW TABLES FROM mysql LIKE '%cost%';

-- 查询结果
-- 查询结果中存在两张维护成本常数的表，这是因为一条语句的执行分两层，server 层和存储引擎层
-- server 层负责进行连接管理、查询缓存、语法解析、查询优化等操作，存储引擎层负责执行具体的数据存取操作
+--------------------------+
| Tables_in_mysql (%cost%) |
+--------------------------+
| engine_cost              |
| server_cost              |
+--------------------------+

区分 server 层和存储引擎层也就意味着：一条语句在 server 层中执行的成本，和这条语句操作的表使用的存储引擎无关。所以 server 层中的操作对应的成本常数就存储在了 server_cost 表中，依赖于存储引擎的操作对应的成本常数就存储在了 engine_cost 表中。


mysql.server_cost 表

我们可以查询出 server_cost 表内容如下：

-- 查询语句
SELECT * FROM mysql.server_cost;

-- 查询结果
+------------------------------+------------+---------------------+---------+
| cost_name                    | cost_value | last_update         | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost   |       NULL | 2018-01-20 12:03:21 | NULL    |
| disk_temptable_row_cost      |       NULL | 2018-01-20 12:03:21 | NULL    |
| key_compare_cost             |       NULL | 2018-01-20 12:03:21 | NULL    |
| memory_temptable_create_cost |       NULL | 2018-01-20 12:03:21 | NULL    |
| memory_temptable_row_cost    |       NULL | 2018-01-20 12:03:21 | NULL    |
| row_evaluate_cost            |       NULL | 2018-01-20 12:03:21 | NULL    |
+------------------------------+------------+---------------------+---------+

首先我们来介绍 server_cost 表的列含义：

  • cost_name：成本常数的名称。
  • cost_value：成本常数对应的值。若该列值为 NULL ，则对应成本常数采取默认值。
  • last_update：最后更新记录的时间。
  • comment：注释。

其次，我们来介绍 server_cost 表维护的成本常数：

成本常数名称 默认值 描述
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 检测一条记录是否符合搜索条件的成本。增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描。

修改 server_cost 表中成本常数的值

修改 server_cost 表中成本常数的值需要两步：

  1. 更新对应成本常数。例如：

    UPDATE mysql.server_cost 
SET cost_value = 0.4
WHERE cost_name = 'row_evaluate_cost';

  2. 使系统重新加载 server_cost 表维护的成本常数。

    FLUSH OPTIMIZER_COSTS;

当然，如果我们想让我们修改的成本常数不使用我们指定的值而使用默认的值，只需要将对应的成本常数的值置为 NULL ，然后使用FLUSH OPTIMIZER_COSTS;语句即可。


小补充

MySQL在执行诸如DISTINCT查询、分组查询、Union查询以及某些特殊条件下的排序查询都可能在内部先创建一个临时表，使用这个临时表来辅助完成查询。

例如，对于DISTINCT查询可以建一个带有UNIQUE索引的临时表，直接把需要去重的记录插入到这个临时表中，插入完成之后的记录就是结果集了。

在数据量大的情况下可能创建基于磁盘的临时表，也就是为该临时表使用MyISAM、InnoDB等存储引擎。在数据量不大时可能创建基于内存的临时表，也就是使用Memory存储引擎。

创建临时表和对临时表进行写入和读取的操作代价是很高的。


mysql.engine_cost 表

我们可以查询出 engine_cost 表内容如下：

-- 查询语句
SELECT * FROM mysql.engine_cost;

-- 查询结果
+-------------+-------------+------------------------+------------+---------------------+---------+
| engine_name | device_type | cost_name              | cost_value | last_update         | comment |
+-------------+-------------+------------------------+------------+---------------------+---------+
| default     |           0 | io_block_read_cost     |       NULL | 2018-01-20 12:03:21 | NULL    |
| default     |           0 | memory_block_read_cost |       NULL | 2018-01-20 12:03:21 | NULL    |
+-------------+-------------+------------------------+------------+---------------------+---------+

与 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 1.0 与上一个参数类似，只不过衡量的是从内存中读取一个块对应的成本。

仔细看这两个成本常数的值，竟然是一样的！硬盘竟然和内存的读取速度相提并论了？

这是因为无法预测块有无加载到内存中，即无法判断某个查询需要访问的块中有哪些已经加载到内存中，有哪些还停留在磁盘上，所以两个成本常数的值被粗暴的设成了一个，也就是 1.0 。


向 engine_cost 表中插入记录

修改engine_cost 表中成本常数的值与修改 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');

  • 使系统重新加载 engine_cost 表维护的成本常数。

    FLUSH OPTIMIZER_COSTS;
posted @ 2024-07-22 17:44  苏沐问  阅读(20)  评论(0)    收藏  举报