mysql中单表查询的成本

大家好。我们知道MySQL在执行一个查询时，经常会有多个执行方案，然后从中选取成本最低或者说代价最低的方案去真正的执行查询。今天我们来聊一聊单表查询的成本。

那么到底什么是成本呢？这里我们说的成本或者代价是由两方面组成的：

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

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

对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位。MySQL 规定读取一个页面花费的成本默认是1.0 ，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，其余的成本常数今天先不谈。 注意：不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。

一、单表查询的成本

为了方便讲解，我们先创建下面这个表：

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

在这张表中我们默认插入了10000条数据，下面我们就以这个表来探讨一下如何基于成本进行优化。

1、基于成本的优化步骤

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

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

下边我们通过这个sql语句来一步一步分析一下这四步：

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

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

对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL 、IS NOT NULL 、> 、< 、>= 、<= 、BETWEEN、!= （不等于也可以写成 <> ）或者 LIKE 操作符连接起来，就可以产生一个所谓的范围区间（LIKE匹配字符串前缀也行），也就是说这些搜索条件都可能使用到索引。一个查询中可能使用到的索引称之为possible keys。

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

key1 IN (‘a’, ‘b’, ‘c’) 可以使用二级索引 idx_key1 。
key2 > 10 AND key2 < 1000可以使用二级索引 idx_key2 。
key3 > key2 这个搜索条件的索引列由于没有和常数比较，所以并不能使用到索引。
key_part1 LIKE ‘%hello%’ ， key_part1 通过 LIKE 操作符和以通配符开头的字符串做比较，不可以使用索引。
common_field = ‘123’，由于该列没有索引，所以不会用到索引。

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

2、计算全表扫描的代价。

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

MySQL每个表维护了一系列的统计信息，我们可以通过SHOW TABLE STATUS语句来查看表的统计信息，如果要看指定的某个表的统计信息，在该语句后加对应的 LIKE 语句就 好了，比方说我们要查看single_table 这个表的统计信息可以这么写：

虽然出现了很多统计选项，但我们目前只关心两个：

Rows（本选项表示表中的记录条数）： 对于使用MyISAM 存储引擎的表来说该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。从查询结果我们也可以看出来，由于我们的single_table 表是使用 InnoDB 存储引擎的，所以虽然实际上表中有10000条记录，但是SHOW TABLE STATUS 显示的 Rows 值是10033条记录。
Data_length（本选项表示表占用的存储空间字节数）： 使用MyISAM存储引擎的表来说，该值就是数据文件的大小，对于使用InnoDB 存储引擎的表来说，该值就相当于聚簇索引占用的存储空间大小，也就是说可以这样计算该值的大小：

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

single_table 使用默认16KB 的页面大小，而上边查询结果显示 Data_length 的值是1589248 ，所以我们可以反向来推导出聚簇索引的页面数量： 聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97。

我们现在已经得到了聚簇索引占用的页面数量以及该表记录数的估计值，所以就可以计算全表扫描成本了。但是MySQL在真实计算成本时会进行一些微调，但是由于这些微调的值十分的小，并不影响我们分析。

现在可以看一下全表扫描成本的计算过程：

I/O 成本 97 x 1.0 + 1.1 = 98.1

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

CPU 成本： 10033x 0.2 + 1.0 = 2007.6

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

总成本： 98.1 + 2007.6 = 2105.7 综上所述，对于single_table 的全表扫描所需的总成本就是 2105.7 。

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

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

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

1. 使用 idx_key2 执行查询的成本分析： idx_key2 对应的搜索条件是：key2 > 10 AND key2 < 1000，对应的范围区间就是：(10, 1000) ， 所以使用idx_key2 搜索的示意图如下所示：

对于使用二级索引 + 回表方式的查询，MySQL计算这种查询的成本依赖两个方面的数据：

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

本例中使用idx_key2 的范围区间只有一个：(10, 1000) ，所以相当于访问这个范围区间的二级索引付出的I/O成本就是：1 x 1.0 = 1.0。

需要回表的记录数： 优化器需要计算二级索引的某个范围区间到底包含多少条记录，对于本例来说就是计算idx_key2在(10, 1000) 这个范围区间中包含多少二级索引记录，计算过程是这样的：

步骤1：先根据key2 > 10这个条件访问一下idx_key2对应的B+ 树索引，找到满足 key2 > 10 这个条件的第一条记录，我们把这条记录称之为区间最左记录。

步骤2：然后再根据key2 < 1000这个条件继续从 idx_key2 对应的 B+ 树索引中找出第一条满足这个条件的记录，我们把这条记录称之为区间最右记录。

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

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

如图，我们假设区间最左记录在页b中，区间最右记录在页c中，那么我们想计算区间最左记录和区间最右记录之间的页面数量就相当于计算页b和页c之间有多少页面，而每一条目录项记录都对应一个数据页，所以计算页b和页c之间有多少页面就相当于计算它们父节点（也就是 页a）中对应的目录项记录之间隔着几条记录。

如果页b和页c之间的页面非常多，以至于页b和页c对应的目录项记录都不在一个页面中时，就再统计页b和页c对应的目录项记录所在页之间有多少个页面，依次类推。

根据上述算法测得 idx_key2 在区间 (10, 1000) 之间大约有 95 条记录。读取这95条二级索引记录需要付出的CPU成本就是：95 x 0.2 + 0.01 = 19.01 其中95是需要读取的二级索引记录条数，0.2是读取一条记录成本常数，0.01是微调。

在通过二级索引获取到记录之后，还需要干两件事儿：

根据这些记录里的主键值到聚簇索引中做回表操作： MySQL认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记 录，就需要进行多少次回表操作，也就是需要进行多少次页面I/O。

我们上边统计了使用 idx_key2 二级索引执行查询时，预计有95条二级索引记录需要进行回表操作，所以回表操作带来的I/O成本就是：95 x 1.0 = 95.0。其中95是预计的二级索引记录数，1.0是一个页面的I/O成本常数。

回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立： 回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然后再检测除 key2 > 10 AND key2 < 1000 这个搜索条件以外的搜索条件是否成立。

因为我们通过范围区间获取到二级索引记录共95条，也就对应着聚簇索引中95条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件的CPU成本就是： 95 x 0.2 = 19.0。其中95是待检测记录的条数，0.2是检测一条记录是否符合给定的搜索条件的成本常数。

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

I/O 成本： 1.0 + 95 x 1.0 = 96.0 (范围区间的数量 + 预估的二级索引记录条数)

CPU 成本： 95 x 0.2 + 0.01 + 95 x 0.2 = 38.01 （读取二级索引记录的成本 + 读取并检测回表后聚簇索 引记录的成本）

综上所述，使用idx_key2 执行查询的总成本就是：96.0 + 38.01 = 134.01。

2. 使用 idx_key1 执行查询的成本分析： idx_key1 对应的搜索条件是：key1 IN (‘a’, ‘b’, ‘c’) ，也就是说相当于3个单点区间：[‘a’, ‘a’]、[‘b’, ‘b’]和[‘c’, ‘c’]。使用idx_key1 搜索的示意图如下：

与使用idx_key2 的情况类似，我们也需要计算使用 idx_key1 时需要访问的范围区间数量以及需要回表的记录数：

范围区间数量： 使用idx_key1 执行查询时很显然有3个单点区间，所以访问这3个范围区间的二级索引付出的I/O成本就是：3 x 1.0 = 3.0。

需要回表的记录数： 由于使用idx_key1 时有3个单点区间，所以每个单点区间都需要查找一遍对应的二级索引记录数：

查找单点区间[‘a’, ‘a’] 对应的二级索引记录数：计算单点区间对应的二级索引记录数和计算连续范围区间对应的二级索引记录数是一样的，都是先计算区间最左记录和区间最右记录，然后再计算它们之间的记录数。最后计算得到单点区间[‘a’, ‘a’] 对应的二级索引记录数是：35 。

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

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

所以，这三个单点区间总共需要回表的记录数就是：35 + 44 + 39 = 118，读取这些二级索引记录的CPU成本就是：118 x 0.2 + 0.01 = 23.61。

得到总共需要回表的记录数之后，我们还要考虑以下几点：

根据这些记录里的主键值到聚簇索引中做回表操作：所需的I/O成本就是：118 x 1.0 = 118.0。

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

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

I/O 成本：3.0 + 118 x 1.0 = 121.0 (范围区间的数量 + 预估的二级索引记录条数)

CPU 成本：118 x 0.2 + 0.01 + 118 x 0.2 = 47.21 （读取二级索引记录的成本 + 读取并检测回表后聚簇 索引记录的成本）

综上所述，使用idx_key1 执行查询的总成本就是：121.0 + 47.21 = 168.21

3. 是否有可能使用索引合并（ Index Merge ）： 本例中有关key1 和 key2 的搜索条件是使用 AND 连接起来的，而对于idx_key1 和 idx_key2 都是范围查询，也就是说查找到的二级索引记录并不是按照主键值进行排序的，并不满足使用Intersection 索引合并的条件，所以并不会使用索引合并。

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

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

全表扫描的成本：2105.7
使用idx_key2 的成本：134.01
使用idx_key1 的成本：168.21

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

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

有时候使用索引执行查询时会有许多单点区间，比如使用IN语句就很容易产生非常多的单点区间，比如下边这个查询（语句中的…表示还有很多参数）：

SELECT * FROM single_table WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz');

很显然，这个查询可能使用到的索引就是idx_key1 ，由于这个索引并不是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，需要我们去计算。计算方式我们上边已经介绍过了，就是先获取索 引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录（记录条数少的时候可以做到精确计算，多的时候只能估算）。这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。

有零星几个单点区间的话，使用index dive 的方式去计算这些单点区间对应的记录数也不是什么问题，但是如果单点区间多的话，比如IN语句里有20000个参数，这就意味着MySQL 的查询优化器为了计算这些单点区间对应的索引记录条数，要进行20000次index dive 操作，这性能损耗会很大。

为了防止这种情况， MySQL提供了一个系统变量 eq_range_index_dive_limit ，如果我们的IN语句中的参数个数小于eq_range_index_dive_limit 的话，将使用index dive的方式计算各个单点区间对应的记录条数，如果大于或等于eq_range_index_dive_limit 个的话，可就不能使用index dive了，要使用所谓的索引统计数据来进行估算。

像会为每个表维护一份统计数据一样，MySQL也会为表中的每一个索引维护一份统计数据，查看某个表中索引的统计数据可以使用SHOW INDEX FROM 表名的语法，比如我们查看一下 single_table 的各个索引的统计数据：

下面我们介绍一下这些属性：

属性名	描述
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	这个属性我们暂时不了解，可以先忽略掉。
Index_type	该索引列是否允许存储NULL值。
Comment	使用索引的类型，我们最常见的就是BTREE，其实也就是B+树索引。
Index_comment	索引列注释信息。 索引注释信息。

上述属性中我们现在最在意的是Cardinality 属性， Cardinality 直译过来就是基数的意思，表示索引列中不重复值的个数。比如对于一个一万行记录的表来说，某个索引列的Cardinality属性是10000，那意味着该列中没有重复的值，如果Cardinality属性是1的话，就意味着该列的值全部是重复的。

注意：对于InnoDB存储引擎来说，使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值，并不是精确的。

前边说道，当IN语句中的参数个数大于或等于系统变量eq_range_index_dive_limit 的值的话，就不会使用index dive的方式计算各个单点区间对应的索引记录条数，而是使用索引统计数据，这里所指的索引统计数据指的是这两个值：

使用SHOW TABLE STATUS 展示出的 Rows 值，也就是一个表中有多少条记录。

使用SHOW INDEX 语句展示出的 Cardinality 属性。

结合上一个Rows 统计数据，我们可以针对索引列，计算出平均一个值重复多少次。一个值的重复次数 ≈ Rows ÷ Cardinality。

以single_table 表的 idx_key1 索引为例，它的 Rows 值是 10033，它对应索引列 key1 的 Cardinality 值也是 10033，所以我们可以计算key1 列平均单个值的重复次数就是：10033÷ 10033=1（条）。

此时再看上边那条查询语句：

SELECT * FROM single_table WHERE key1 IN ('aa1', 'aa2', 'aa3', ... , 'zzz');

假设IN 语句中有20000个参数的话，就直接使用统计数据来估算这些参数需要单点区间对应的记录条数了，每个参数对应1条记录，所以总共需要回表的记录数就是：20000 x 1 = 20000 使用统计数据来计算单点区间对应的索引记录条数可比index dive 的方式简单多了，但是它的致命弱点就是：不精确！使用统计数据算出来的查询成本与实际所需的成本可能相差非常大。

好了，到这里我们就讲完了，今天我们主要讲了成本是什么以及单表查询如何计算成本，欢迎大家在评论区留言讨论，也希望大家能给作者点个关注，谢谢大家！最后依旧是请各位老板有钱的捧个人场，没钱的也捧个人场，谢谢各位老板！