MySQL查询为什么选择使用这个索引？——基于MySQL 8.0.22索引成本计算

发布于

本文参考《MySQL是怎样运行的》，与书中不同的是，书中数据库版本5.7.22，本文使用8.0.22，不同版本数据库计算成本常数是不同的，书中是1W条记录，我这里是近10W条记录，经过实践，是对于书中的补充和验证，计算的成本和实际成本对比，让大家更容易理解MySQL为什么要使用这个索引。

1.什么是成本

我们知道，MySQL查询会选择成本最低，或代价最低的那种方式去真正的执行查询。

MySQL的查询成本分为下面两个部分

  • I/O成本

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

  • CPU成本

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

  对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定：读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.1(MySQL 5.7.22版本是0.2)。1.0、0.1这些数字称之为成本常数。

在读取记录时，即使不需要检测记录是否符合where条件，其成本都算是0.1，即访问一条记录所需的成本常数就是0.1，不管有无where条件。

1.1 如何查看MySQL的成本常数

代码语言：javascript
复制
SELECT *  FROM mysql.server_cost;

从server_cost中的内容可以看出来，目前在server层的一些操作对应的成本常数有以下几种：

成本常数名称

默认值 （括号中是MySQL5.7.22测出的值）

描述

disk_temptable_create_cost

20.0 （40.0）

创建基于磁盘的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。

disk_temptable_row_cost

0.5 （1.0）

向基于磁盘的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。

key_compare_cost

0.05 （0.1）

两条记录做比较操作的成本，多用在排序操作上，如果增大这个值的话会提升filesort的成本，让优化器可能更倾向于使用索引完成排序而不是filesort。

memory_temptable_create_cost

1.0 （2.0）

创建基于内存的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。

memory_temptable_row_cost

0.1 （0.2）

向基于内存的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。

row_evaluate_cost

0.1 （0.2）

这个就是我们上面说的检测一条记录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描。

代码语言：javascript
复制
SELECT * FROM mysql.engine_cost;

成本常数名称

默认值 （括号中是MySQL5.7.22测出的值）

描述

io_block_read_cost

1.0 （1.0）

从磁盘上读取一个块对应的成本。 请注意我使用的是块，而不是页这个词儿。对于InnoDB存储引擎来说，一个页就是一个块，不过对于MyISAM存储引擎来说，默认是以4096字节作为一个块的。增大这个值会加重I/O成本，可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描。

memory_block_read_cost

0.25 （1.0）

与上一个参数类似，只不过衡量的是从内存中读取一个块对应的成本。

5.7.22版本怎么从内存中和从磁盘上读取一个块的默认成本都是1.0呢？这主要是因为在MySQL 5.7.22的实现中，并不能准确预测某个查询需要访问的块中有哪些块已经加载到内存中，有哪些块还停留在磁盘上。所以MySQL很粗暴的认为不管这个块有没有加载到内存中，使用的成本都是1.0。

  至于为什么在8.0+ 版本中成本常数变小了呢？那是因为数据库的算法在不断的优化，能更加准确的预测块是否加载到内存中了。所以在不同数据库版本查看sql执行计划，选择的实际索引可能有所不同。

  我们这里查询的是mysql库里面的server_cost和engine_cost表，在大公司中，一般人根本没权限查看这个mysql库的内容。因为mysql库相当重要，它存储了MySQL的用户账户和权限信息、一些存储过程和事件的定义信息、一些运行过程中产生的日志信息、一些帮助信息以及时区信息等等。所以大家可以在自己电脑自行验证，公司电脑可能限制了权限。

2.单表查询的成本

和前面几篇文章一样，首先列出建表语句，后面例子均在此表基础上举例说明。

代码语言：javascript
复制
CREATE TABLE demo_info(
    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 uk_key2 (key2),
    KEY  idx_key3 (key3),
    KEY idx_key_part(key_part1, key_part2, key_part3)
)ENGINE = INNODB CHARSET=utf8mb4;

2.1 基于成本的优化步骤

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

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

接下来，我们来看一下这4个步骤的细节分析

用一个sql来具体分析下

代码语言：javascript
复制
explain 
select * from demo_info where 
    key1 in ('a', 'b', 'c') and 
    key2 > 10 and key2 < 1000 and
    key3 > key2 and 
    key_part1 like '%hello%' and
    common_field = '123';

可以看到这里实际使用的索引是uk_key2，这个结果优化器是怎么计算出来的呢？来，一起分析！

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

  • key1 IN ('a', 'b', 'c')，这个搜索条件可以使用普通索引idx_key1。
  • key2 > 10 AND key2 < 1000，这个搜索条件可以使用唯一索引uk_key2。
  • key3 > key2，这个搜索条件的索引列由于没有和常数比较，因此不能产生合适的扫描区间。
  • key_part1 LIKE '%hello%'，key_part1通过LIKE操作符和以通配符开头的字符串做比较，不可以适用索引。
  • common_field = '123'，由于该列上压根儿没有索引，所以不会用到索引。

综上所述，上边的查询语句可能用到的索引（也就是possible keys）有idx_key1和uk_key2。

2.计算全表扫描的代价

  对于InnoDB存储引擎来说，全表扫描的意思就是把聚集索引中的记录都依次与给定的搜索条件进行比较，把符合搜索条件的记录加入到结果集中。 所以需要将聚集引对应的页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本 = I/O 成本 + CPU成本，所以计算全表扫描的代价需要两个信息：

  1. 聚集索引占用的页面数
  2. 该表中的记录数

那这两个信息从哪里来呢？

  可以用show table status语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的like语句就好了，比方说我们要查看demo_info这个表的统计信息可以这么写：

代码语言：javascript
复制
show table status like 'demo_info'

切换到表单视图方便查看

表中实际有99896条记录，与预估的是不同的。

虽然出现了很多统计项，但是我们只需要关注两个选项。

  • Rows

  本选项表示表中的记录条数。对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的demo_info表是使用InnoDB存储引擎的，尽管表实际有99896条记录，但是show table status显示的Rows值有100548条记录，这个值比实际记录条数偏大偏小都有可能，总之对于InnoDB是不准确的。

  • Data_length

  表示表占用的存储空间字节数。使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB存储引擎的表来说，该值就相当于聚集索引占用的存储空间大小，也就是说可以这样计算该值的大小：

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

  我们的demo_info使用默认16KB的页面大小，而上边查询结果显示Data_length的值是5783552，所以可以反向来推导出聚集索引的页面数量：

聚簇索引的页面数量 = 5783552 ÷ 16 ÷ 1024 = 353

  现在已经得到了聚集索引占用的页面数量以及该表记录数的估计值，接下来就可以计算全表扫描成本了，但是MySQL代码中有一些硬编码进行成本的微调，微调值没有注释，含义无从得知，但是不影响后续分析。

看一下全表扫描成本的计算过程

  • I/O成本

353 x 1.0 + 1.1 = 354.1

353指的是聚集索引占用的页面数，1.0指的是加载一个页面的成本常数，后边的1.1是一个微调值，我们不用在意。

  • CPU成本：

100548 x 0.1 + 1.0 = 10055.8

100548指的是统计数据中表的记录数(看Rows列)，对于InnoDB存储引擎来说是一个估计值，0.1指的是访问一条记录所需的成本常数，后边的1.0是一个微调值，我们不用在意。

总成本：

354.1 + 10055.8 = 10409.9

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

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

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

  从第1步分析我们得到，上述查询可能使用到idx_key1和uk_key2这两个索引，我们需要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。MySQL查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本，所以我们也先分析uk_key2的成本，然后再看使用idx_key1的成本。

(1) 使用uk_key2执行查询的成本分析

uk_key2对应的搜索条件是：key2 > 10 AND key2 < 1000，也就是说对应的范围区间就是：(10, 1000)，使用uk_key2执行查询的步骤如下

1.先从uk_key2索引中定位key2在(10, 1000)这个区间内的记录，找到记录对应的id列的值

2.再回表到聚集索引中根据上一步得到的id值，找到完整的用户记录

  对于使用非聚集索引 + 回表方式的查询，MySQL查询的成本依赖两个方面的数据：扫描区间的数量和需要回表的记录数

  • 扫描区间的数量

  查询优化器粗暴的认为读取索引的一个扫描区间的I/O成本和读取一个页面的I/O成本是相同的。本例中使用uk_key2的扫描区间只有一个：(10, 1000)，无论该扫描区间的非聚集索引有多少条(可能是很大的区间几十万条)、到底占用了多少页面，都只认为和读取一个页面的I/O成本一样（有多少个扫描区间，相当于和读对应数量的页的I/O成本一样），所以相当于访问这个扫描区间的非聚集索引付出的I/O成本就是：

1 x 1.0 = 1.0 （有几个扫描区间，就相当于要计算读几个页面的成本，这点容易忽视）

其中，1是一个扫描区间，1.0是表示读取一个页面的I/O成本

  • 需要回表的记录数

  查询优化器需要计算非聚集索引的某个扫描区间到底包含多少条记录，对于本例来说就是要计算uk_key2在(10, 1000)这个扫描区间中包含多少非聚集索引记录，计算过程是这样的：

  步骤1：先根据key2 > 10这个条件访问一下uk_key2对应的B+树索引，找到满足key2 > 10这个条件的第一条记录（我们把这条记录称之为区间最左记录）。在B+数树中定位一条记录是非常快的，是常数级别的，这个过程的性能消耗是可以忽略不计。

  步骤2：然后再根据key2 < 1000这个条件继续从uk_key2对应的B+树索引中找出最后一条满足这个条件的记录（我们把这条记录称之为区间最右记录），这个过程的性能消耗同样可以忽略不计的。

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

  数据页结构中有一个Page Header的部分，Page Header中有一个PAGE_N_RECS的属性，表示该页面中目前有多少条记录。所以，在区间不太远的情况下，我们可以把每个页面中的PAGE_N_RECS属性值加起来就好了，而不用依次遍历每条记录再统计。

  那么问题又来了，怎么估计区间最左记录和区间最右记录之间有多少个页面呢？解决这个问题还得回到B+树索引的结构中来：

  如图，我们假设区间最左记录在页b中，区间最右记录在页c中，那么计算区间最左记录和区间最右记录之间的页面数量就是计算页b和页c之间有多少页面，此时只需要计算页b和页c的父级目录页之间隔着几条记录即可，在一个页面中统计两条记录之间有几条记录的成本就相当低了。

  如果页b和页c之间的页面实在太多，以至于页b和页c对应的目录项记录都不在一个页面中，则继续递归到父级目录的父级目录，再统计所在页面之间有多少个页面。一个B+树有4层高已经很了不得了，所以这个统计过程也不是很耗费性能。

  知道了如何统计非聚集索引某个扫描区间的记录数之后，让我们回到具体问题中来，就是要找到需要回表的记录数，根据上述算法测得uk_key2在区间(10, 1000)之间有883条记录（我的表中查出来就是883条记录）。读取这883条二级索引记录需要付出的 CPU成本 就是：

883 x 0.1 + 0.01 = 88.31

其中883是需要读取的非聚集索引记录条数，0.1是读取一条记录成本常数，0.01是微调。

在通过非聚集索引获取到记录之后，还有2个步骤：

  • 1.回表，根据这些记录里的主键值到聚集索引中执行回表操作

MySQL评估回表操作的I/O成本依旧很粗略，MySQL认为每次回表操作都相当于访问一个页面，也就是说非聚集索引扫描区间中有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面I/O。前面在使用uk_key2唯一索引执行查询时，有883条非聚集索引记录需要进行回表操作，所以回表操作带来的 I/O成本 就是：

883 x 1.0 = 883.0

其中883是预计的非聚集索引记录数，1.0是一个页面的I/O成本常数。

  • 2.回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立

  回表操作的本质就是通过二级索引记录的主键值到聚集索引中找到完整的用户记录，然后再检测除key2 > 10 AND key2 < 1000这个搜索条件以外的搜索条件是否成立。因为我们通过扫描区间获取到非聚集索引记录共883条，也就对应着聚集索引中883条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件的CPU成本为

883 * 0.1 = 88.3

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

所以本例使用uk_key2执行查询成本如下：

I/O成本: 1.0 + 883 * 1.0 = 884.0 (1个扫描区间的成本 + 非聚集索引回表的成本)

CPU成本: 883 * 0.1 + 0.01 + 883 * 0.1 = 176.61（根据搜索条件读取非聚集索引的成本 + 回表后根据搜索条件读取聚集索引的成本）

综上，使用uk_key2执行查询的总成本就是884.0 + 176.61 = 1060.61

注意：方便记忆法：若走索引，读非聚集索引条件有微调，读聚集索引没有，扫描区间、回表任何一条记录都相当于读取一页。若不走索引，微调值另外分析，和前者不同。

怎么验证？ 直接上代码，不用废话，来个华丽的验证

代码语言：javascript
复制
explain format=json
select * from demo_info where 
    key1 in ('a', 'b', 'c') and 
    key2 > 10 and key2 < 1000 and
    key3 > key2 and 
    key_part1 like '%hello%' and
    common_field = '123';

运行结果

代码语言：javascript
复制
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1060.61" ========================看见没？看我，我就是成本，好好看，仔细看
    },
    "table": {
      "table_name": "demo_info",
      "access_type": "range",
      "possible_keys": [
        "uk_key2",
        "idx_key1"
      ],
      "key": "uk_key2",
      "used_key_parts": [
        "key2"
      ],
      "key_length": "5",
      "rows_examined_per_scan": 883,
      "rows_produced_per_join": 1,
      "filtered": "0.19",
      "index_condition": "((`i-me`.`demo_info`.`key2` > 10) and (`i-me`.`demo_info`.`key2` < 1000))",
      "cost_info": {
        "read_cost": "1060.45",
        "eval_cost": "0.16",
        "prefix_cost": "1060.61",
        "data_read_per_join": "3K"
      },
      "used_columns": [
        "id",
        "key1",
        "key2",
        "key3",
        "key_part1",
        "key_part2",
        "key_part3",
        "common_field"
      ],
      "attached_condition": "((`i-me`.`demo_info`.`key1` in ('a','b','c')) and (cast(`i-me`.`demo_info`.`key3` as double) > cast(`i-me`.`demo_info`.`key2` as double)) and (`i-me`.`demo_info`.`key_part1` like '%hello%') and (`i-me`.`demo_info`.`common_field` = '123'))"
    }
  }
}

截个图代表结果不是我手动改的，计算和验证结果一样！！

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

idx_key1对应的搜索条件是：key1 IN ('a', 'b', 'c')，也就是说相当于3个单点区间：['a', 'a']，['b', 'b']，['c', 'c'] 与使用uk_key2的情况类似，我们也需要计算使用idx_key1时需要访问的范围区间数量以及需要回表的记录数：

  • 扫描区间数量

使用idx_key1执行查询时很显然有3个单点扫描区间，所以访问这3个扫描间的非聚集索引付出的I/O成本就是： 3 x 1.0 = 3.0

  • 需要回表的记录数

每个单点区间都需要查找一遍对应的非聚集索引记录数：

查找单点区间['a', 'a']对应的非聚集索引记录数   计算单点区间对应的非聚集索引记录数和计算连续范围区间对应的非聚集索引记录数是一样的，都是先计算区间最左记录和区间最右记录，然后再计算它们之间的记录数，具体算法上边已经说过。最后计算得到单点区间['a','a']对应的非聚集索引记录数是：99861。

查找单点区间['b', 'b']对应的二级索引记录数 与上同理，计算得到本单点区间对应的记录数是：0。

查找单点区间['c', 'c']对应的二级索引记录数 与上同理，计算得到本单点区间对应的记录数是：1。

由于我造数据有点特殊，几乎所有记录都在['a', 'a']扫描区间内，但是不影响分析。

所以，这三个单点区间总共需要回表的记录数就是：99861 + 0 + 1 = 99862，读取这些非聚集索引记录的CPU成本就是：99862 * 0.1 + 0.01 = 9986.21

得到总共需要回表的记录数之后，还要继续考虑：

  • 根据这些记录中的主键值到聚集索引中执行回表操作，所需的I/O成本就是： 99862 x 1.0 = 99862.0 （每次回表相当于访问一个页面）
  • 针对回表操作后得到的完整用户记录，比较其他搜索条件是否成立。这一步骤对应的CPU成本 99862 * 0.1 = 9986.2

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

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

综上所述，使用idx_key1执行查询的总成本就是：

99865.0 + 19972.41 = 119837.41

这成本远大于全表扫描，所以如果只有idx_key1索引，那么优化器一定会选择全表扫描。

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

  本例中有关key1和key2的搜索条件是使用AND连接起来的，而对于idx_key1和uk_key2都是范围查询，也就是说查找到的非聚集索引记录并不是按照主键值进行排序的，并不满足使用Intersection索引合并的条件，所以并不会使用索引合并。 MySQL查询优化器计算索引合并成本的算法也比较麻烦，这里不讲，理解成本如何计算，知道MySQL会按照这种算法选择索引即可。

4. 对比各种执行方案的代价，找出成本最低的那一个 全表扫描成本：10409.9 使用uk_key2的成本：1060.61 使用idx_key1的成本：119837.41

综上，优化器会选择uk_key2索引执行查询

在这个例子中，不管采用idx_key1和uk_key2执行查询，它们对应的都是range方法。在使用range访问方法执行查询的时候，扫描区间中包含多少条记录，优化器就认为需要进行多少次回表操作，也就相当于需要进行多少次页面I/O.

如果是ref方法，MySQL就给因为回表操作带来的I/O成本设置了天花板，也就是ref访问方法因回表操作带来的I/O成本最多不能超过访问全表记录数 ÷ 10的页面I/O成本或者全表扫描的I/O成本的3倍。

实际中，我们想分析MySQL为什么选择这个索引，直接如下例子，强制索引后分析成本，根本不用自己手动计算，本文是给大家分析，让大家理解思路。

代码语言：javascript
复制
explain format=json
	select * from demo_info 
	- 强制索引分析成本
	force index(idx_key1)
	where 
    key1 in ('a', 'b', 'c') and 
    key2 > 10 and key2 < 1000 and
    key3 > key2 and 
    key_part1 like '%hello%' and
    common_field = '123';

3.连接查询的成本(作为开发人员，必看)

3.1 连接查询的准备

  我们直接构造一个和demo_info表一模一样的demo_info2表。为了简便起见，我们把demo_info表称为d1表，把demo_info2表称为d2表。

3.2 条件过滤 (Condition filtering)

  我们上一篇说过，MySQL中连接查询采用的是嵌套循环连接算法，驱动表只访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：

  • 单次查询驱动表的成本
  • 多次查询被驱动表的成本（每查询到一条驱动表记录，就会去查询被驱动表，所以具体查询多少次取决于驱动表查询的结果集中有多少条记录）

我们把对驱动表进行查询后得到的记录条数称之为驱动表的扇出（fanout）。显然，驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值，有的时候扇出值的计算是很容易的，比如下面这两个查询：

  • 查询一：
代码语言：javascript
复制
SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2;

  假设使用d1表作为驱动表，很显然就只能使用全表扫描的方式对驱动表执行单表查询，驱动表中有多少记录，扇出值就是多少。我们前边说过，show table status like 'demo_info'统计数据中d1表的记录行数是100548，也就是说优化器就直接会把100548当作在d1表的扇出值。没有连接条件的表连接查询会产生笛卡尔积，一般都会写条件。

  为什么我们分析内连接老是假设驱动表？难道左表不是驱动表？不一定，内连接左右表顺序可以任意互换，优化器会优化其连接顺序的。后面会分析，优化器会计算成本后再确定哪个做为驱动表。

  • 查询二：
代码语言：javascript
复制
SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2 
WHERE d1.key2 >10 AND d1.key2 < 1000;

  仍然假设d1表是驱动表的话，很显然对驱动表的单表查询可以使用uk_key2索引执行查询。此时uk_key2的扫描区间(10, 1000)中有多少条记录，那么扇出值就是多少。我们前边计算过，满足uk_key2的扫描区间(10, 1000)的记录数是883条，也就是说本查询中优化器会把883当作驱动表d1的扇出值。

有的时候扇出值的计算非常困难，比如这两种情况下计算驱动表扇出值时需要靠 ‘猜’：

  • 如果使用全表扫描的方式执行单表查询，那么计算驱动表扇出值的时候需要猜测满足全部搜索条件的记录有多少条。
  • 如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要猜测除了满足形成索引扫描区间的搜索条件外，还满足其他搜索条件的记录有多少条。

例子如下：

  • 查询三：
代码语言：javascript
复制
SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2 
    WHERE d1.common_field > 'xyz';

  本查询和查询一类似，只不过对于驱动表d1多了一个common_field > 'xyz'的搜索条件。查询优化器又不会真正的去执行查询，所以它只能猜这100548记录里有多少条记录满足common_field > 'xyz'条件。

  • 查询四：
代码语言：javascript
复制
SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2 
    WHERE d1.key2 > 10 AND d1.key2 < 1000 AND
          d1.common_field > 'xyz';

  本查询和查询二类似，只不过在查询驱动表d1也多了一个common_field > 'xyz'的搜索条件。不过因为本查询可以使用uk_key2索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合common_field > 'xyz'条件，也就是只需要在883条记录中猜测有多少条记录符合common_field > 'xyz'条件。

  • 查询五：
代码语言：javascript
复制
SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2 
    WHERE d1.key2 > 10 AND d1.key2 < 1000 AND
          d1.key1 IN ('a', 'b', 'c') AND
          d1.common_field > 'xyz';

本查询和查询二类似，不过在驱动表d1选取uk_key2索引执行查询后，优化器需要在非聚集索引扫描区间的记录中猜有多少条记录符合下边两个条件：

代码语言：javascript
复制
key1 IN ('a', 'b', 'c')

common_field > 'xyz'

也就是优化器需要猜在883条记录中有多少符合上述两个条件的。

  这个猜的过程称之为condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，评估过程很复杂，我也不知道。引用书中的话说，这个condition filtering的功能，就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。 设计MySQL的大叔们称之为启发式规则（heuristic），大家有兴趣的可以再深入了解一下哈。

3.3 两表连接的成本分析（这部分对开发人员写sql很重要）

连接查询的成本计算公式是这样的：

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

对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，左连接驱动表是左表，右连接驱动表是右表。只需要分别为驱动表和被驱动表选择成本最低的访问方法，就可以得到最优的查询方案。

可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：

  • 不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。
  • 然后分别为驱动表和被驱动表选择成本最低的访问方法。

很显然，计算内连接查询成本的方式更麻烦一些，下边我们就以内连接为例来看看如何计算出最优的连接查询方案。

左（外）连接和右（外）连接查询在某些特殊情况下可以被优化为内连接查询，我们在后面的文章会提到。

来来来，看例子，用例子说话

代码语言：javascript
复制
SELECT * FROM demo_info AS d1 INNER JOIN demo_info2 AS d2 
    ON d1.key1 = d2.common_field 
    WHERE d1.key2 > 10 AND d1.key2 < 1000 AND 
          d2.key2 > 1000 AND d2.key2 < 2000;

首先要告诉大家的是，连接、搜索条件写在on和写在where是没有性能上的区别的，优化器会自动优化，之所以分开写，完全是因为这样可读性好而不是性能原因，请一定记住这句话。

可以选择的内连接顺序有两种：

  • d1连接d2，也就是d1作为驱动表，d2作为被驱动表。
  • d2连接d1，也就是d2作为驱动表，d1作为被驱动表。

查询优化器需要分别考虑这两种情况下的最优查询成本，然后选取那个成本更低的连接顺序以及该连接顺序下各个表的最优访问方法作为最终的查询计划。我们分别来看一下（定性分析）：

1.使用d1作为驱动表

  分析对于驱动表的成本最低的执行方案，看一下涉及d1表单表的搜索条件有哪些：

  • d1.key2 > 10 AND d1.key2 < 1000

  这个查询可能使用到uk_key2索引，从全表扫描和使用uk_key2这两个方案中选出成本最低的那个，这个过程我们上边说过了，很显然使用uk_key2执行查询的成本更低些。

  然后分析对于被驱动表的成本最低的执行方案，此时涉及被驱动表d2的连接搜索条件就是：

  • d2.common_field = 常数（这对于d2只能全表扫描了）
  • d2.key2 > 1000 AND d2.key2 < 2000 （可以用到uk_key2索引）

很显然，第一个条件由于common_field没有用到索引，此时访问d2表时可用的方案也是全表扫描和使用uk_key2两种，显然使用uk_key2的成本更小。

所以此时使用d1作为驱动表时的总成本就是（暂时不考虑使用join buffer对成本的影响）：

使用uk_key2访问d1的成本 + d1的扇出 × 使用uk_key2访问d2的成本

2.使用d2作为驱动表

  分析对于驱动表的成本最低的执行方案，看一下涉及d2表单表的搜索条件有哪些：

  • d2.key2 > 1000 AND d2.key2 < 2000

  所以这个查询可能使用到uk_key2索引，从全表扫描和使用uk_key2这两个方案中选出成本最低的那个，显然使用uk_key2执行查询的成本更低些。

  然后分析对于被驱动表的成本最低的执行方案，此时涉及被驱动表d1的搜索条件就是：

  • d1.key1 = 常数
  • d1.key2 > 10 AND d1.key2 < 2000

  这时就很有趣了，使用idx_key1可以进行ref方式的访问，使用uk_key2可以使用range方式的访问。这是优化器需要从全表扫描、使用idx_key1、使用uk_key2这几个方案里选出一个成本最低的方案。

这里有个问题，因为uk_key2的范围区间是确定的：(10, 1000)，怎么计算使用uk_key2的成本我们上边已经说过了，可是在没有真正执行查询前，d1.key1 = 常数中的常数值我们是不知道的，一般情况下，ref的访问方式要比range成本更低，但是上面分析过，使用uk_key2访问d1成本是更低的。

所以此时使用d2作为驱动表时的总成本就是：

使用uk_key2访问d2的成本 + d2的扇出 × 使用uk_key2访问d1的成本

最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。 从上边的计算过程也可以看出来，连接查询成本 “占大头” 的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以我们的优化重点其实是下边这两个部分：

  • 尽量减少驱动表的扇出（小表作为驱动表）
  • 对被驱动表的访问成本尽量低 （好的索引很重要）

这一点对于我们实际书写连接查询语句时十分有用，我们需要尽量在被驱动表的连接列上建立索引，这样就可以使用ref访问方法来降低访问被驱动表的成本了。如果可以，被驱动表的连接列最好是该表的主键或者唯一二级索引列，这样就可以把访问被驱动表的成本降到更低了。

所谓的大表还是小表，得等到条件过滤之后记录数的多少才能区分大表还是小表，而不是只看表里总记录数量来判断大表小表。

3.4 多表连接的成本分析（选读，了解）

在分析多表连接的成本之前，首先要考虑一下多表连接时可能产生出多少种连接顺序：

  • 对于两表连接，比如表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!）种连接顺序。

  有n个表进行连接，MySQL查询优化器要每一种连接顺序的成本都计算一遍么？不会！MySQL有很多办法减少因计算不同连接顺序下的查询成本带来的性能损耗。

  • 提前结束某种顺序的成本评估

MySQL在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。比如说A、B、C三个表进行连接，已经得到连接顺序ABC是当前的最小连接成本（假设为10.0），在计算连接顺序BCA时，发现B和C的连接成本就已经大于10.0时，就不再继续往后分析连接A的成本了。

  • 系统变量optimizer_search_depth

  为了防止无穷无尽的分析各种连接顺序的成本，MySQL有一个optimizer_search_depth系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与optimizer_search_depth值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到的就不是很好的执行计划，但可以省掉了连接成本的分析时间。

  • 某些规则压根儿就不考虑某些连接顺序

  即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以MySQL有一些所谓的启发式规则（就是根据以往经验指定的一些规则），凡是不满足这些规则的连接顺序就不分析，这样可以极大的减少需要分析的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量optimizer_prune_level来控制是否使用这些启发式规则。

3.5 外连接和内连接等同的情况（外连接可转为内连接）

  外连接和内连接在某种情况下是等价的，也就是说可以相互转换的，写[inner] join或者left | right join 效果一样。只要在where子句的搜索条件中指定被驱动表的列不为NULL，最后的结果集就和内连接结果集一样。

例子如下

代码语言：javascript
复制
SELECT * FROM demo_info AS d1 LEFT JOIN demo_info2 AS d2 
    ON d1.key1 = d2.common_field 
    WHERE d2.key_part2 is not null;

  因为指定了被驱动表d2表的key_part2列不允许为NULL，所以d1和d2的左连接和内连接查询是一样的。当然，我们也可以不用显式指定被驱动表的某个列 is not null，只要隐含就行，比如

代码语言：javascript
复制
SELECT * FROM demo_info AS d1 LEFT JOIN demo_info2 AS d2 
    ON d1.key1 = d2.common_field 
    WHERE d2.key_part2 = 2;

  在where子句指定了被驱动表d2的key_part2列等于2，那就肯定不为null了，这和下面内连接语句是等价的。

代码语言：javascript
复制
SELECT * FROM demo_info AS d1 JOIN demo_info2 AS d2 
    ON d1.key1 = d2.common_field 
    WHERE d2.key_part2 = 2;

  我们把这种在外连接查询中，指定where子句中包含被驱动表中的列不为null值的条件称为空值拒绝（reject-NULL），在被驱动表的where子句符合空值拒绝的条件后，外连接和内连接可以相互转换。 这种转换带来的好处就是优化器可以通过评估表的不同顺序的成本，选出最低成本的连接顺序进行查询。

#mysql#连接#搜索#索引#优化