MySQL之单表查询成本

通过前面学习我们知道了MySQL优化器的作用：MySQL优化器会从生成的多个不同执行方案中，选择成本最低的方案去真正的执行查询。在MySQL中，一条查询语句的执行成本由两部分组成：

• I/O成本：查询数据时，需要把数据加载到内存中后再操作，从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。

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

InnoDB存储引擎是以16KB大小的页（Page）作为磁盘和内存交互的基本单位，MySQL规定读取一个页（Page）花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。

1.0、0.2这些数字称之为成本常数。注意：不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。

测试数据准备

我们还是先来准备一些测试数据：

--Create DatabaseCREATE DATABASE test_cost;--Create TableUSE test_cost;CREATE TABLE `table_query_cost` (    `id` INT(11) NOT NULL AUTO_INCREMENT,    `key1` VARCHAR(100),    `key2` INT(11) ,    `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 uq_key2 (`key2`),    KEY idx_key3 (`key3`),    KEY idx_key_part(`key_part1`, `key_part2`, `key_part3`)) Engine=InnoDB;--Create ProcedureDELIMITER ;;CREATE PROCEDURE cost_data()BEGIN    DECLARE i INT;    SET i=0;    WHILE i<10000 DO        INSERT INTO table_query_cost(key1,key2,key3,key_part1,key_part2,key_part3,common_field) VALUES(SUBSTRING(MD5(RAND()),1,2),i+1,SUBSTRING(MD5(RAND()),1,3),SUBSTRING(MD5(RAND()),1,4),SUBSTRING(MD5(RAND()),1,5),SUBSTRING(MD5(RAND()),1,6),SUBSTRING(MD5(RAND()),1,7));        SET i=i+1;    END WHILE;END;;DELIMITER ;--Call ProcedureCALL cost_data();--Query DataSELECT COUNT(*) FROM test_cost.table_query_cost;SELECT * FROM test_cost.table_query_cost LIMIT 20;

基于成本的计算过程

4、对比各个执行方案的代价，找出成本最低的那个进行查询。

SELECT * FROM test_cost.table_query_cost WHERE     key1 IN ('e4', '2f', 'd7') AND     key2 > 99 AND key2 < 999 AND     key3 > key2 AND     key_part1 LIKE '%ead%' AND    common_field = 'a6c5a45';

分析上述SQL涉及到的搜索条件：

• key1 IN ('e4', '2f', 'd7') ，可以使用到二级索引idx_key1。

• key2 > 99 AND key2 < 999，可以使用到二级（唯一）索引uq_key2。

• key3 > key2，搜索条件的索引列没有和常数比较，不能使用索引。

• key_part1 LIKE '%ead%'，LIKE操作符和以通配符开头的字符串做比较，不能使用索引。

• common_field = 'a6c5a45'，common_field字段无索引，不能使用索引。

综上所述，SQL语句可能用到的索引possible keys只有idx_key1、uq_key2。

• 聚簇索引总共占用的页面数

• 表中的记录数

我们可以通过查询统计信息的方式得到：

SHOW TABLE STATUS LIKE 'table_query_cost'\G;

• Rows：对于MyISAM存储引擎的表来说，该值是准确的。对于InnoDB来说，该值是预估值。我们实际插入了10000行记录，但是查看表状态的行数信息却有10056行记录。
• Data_length：表示表占用的存储空间字节数。MyISAM存储引擎，该值就是数据文件的大小。InnoDB存储引擎，该值就相当于聚簇索引占用的存储空间大小（单位：字节，B），也就是说可以这样计算该值的大小：

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

一个Page大小为16KB，由上面的公式可以得到聚簇索引的页面数量为97：

聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97

得到了聚簇索引占用的页面数量以及该表记录数的预估值，下面就可以计算全表扫描成本了，但是MySQL在真实计算成本时会进行一些微调，这些微调的值是直接硬编码到代码里的。

• I/O成本：

97 x 1.0 + 1.1 = 98.1

• CPU成本：

10056 x 0.2 + 1.0 = 2012.2

• 总成本：

98.1 + 2012.2 = 2110.3

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

综上可知，可能用到的索引（possible keys）有idx_key1、uq_key2，需要分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。MySQL查询优化器先分析使用唯一二级索引的成本，再分析使用普通索引的成本，所以我们也先分析uq_key2的成本，然后再看使用idx_key1的成本。

※计算使用uq_key2执行查询的成本

• 范围区间数量：不论某个范围区间的二级索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。本例中使用uq_key2的范围区间只有一个：(99,999)，所以相当于访问这个范围区间的二级索引付出的I/O成本就是：

1 x 1.0 = 1.0

• 需要回表的记录数：优化器需要计算二级索引的某个范围区间到底包含多少条记录，计算过程如下：

• 步骤1：根据key2 > 99这个条件访问一下uq_key2对应的B+树索引，找到满足条件的第一条记录，称之为区间最左记录。
• 步骤2：根据key2 < 999这个条件继续从uq_key2对应的B+树索引中找出最后一条满足这个条件的记录，称之为区间最右记录。

• 步骤3：如果区间最左记录和区间最右记录相隔不太远（相隔不大于10个页面），那就可以精确统计出满足key2 > 99 AND key2 < 999条件的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。如何估计区间最左记录和区间最右记录之间有多少个页面呢？还记得MySQL之B+树索引的二级索引树形结构图吧，回顾一下。

86 x 0.2 + 0.01 = 17.21

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

• 步骤4：根据这些记录里的主键值到聚簇索引中做回表操作。MySQL评估回表操作的I/O成本依旧很豪放，他们认为每次回表操作都相当于访问一个页面，也就是说二级索引范围区间有多少记录，就需要进行多少次回表操作，也就是需要进行多少次页面I/O。有86条记录需要回表，所以回表操作的I/O成本就是：

86 x 1.0 = 86.0

• 步骤5：回表操作后得到的完整用户记录，然后再检测其他搜索条件是否成立。回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录，然后再检测除key2 > 99 AND key2 < 999这个搜索条件以外的搜索条件是否成立。因为我们通过范围区间获取到二级索引记录共86条，也就对应着聚簇索引中86条完整的用户记录，读取并检测这些完整的用户记录是否符合其余的搜索条件的CPU成本如下：MySQL只计算这个查找过程所需的I/O成本，也就是我们上一步骤中得到的86.0，在内存中的定位完整用户记录的过程的成本是忽略不计的。在定位到这些完整的用户记录后，需要检测除key2 > 99 AND key2 < 999这个搜索条件以外的搜索条件是否成立，这个比较过程花费的CPU成本就是：

86 x 0.2 = 17.2

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

使用uq_key2执行查询的成本：

• I/O成本：

1.0 + 86 x 1.0 = 87.0 (范围区间的数量 + 预估的二级索引记录条数)

• CPU成本：

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

综上，使用uq_key2索引执行查询的总成本是：

87.0 + 34.41 = 121.41

※计算使用idx_key1执行查询的成本

idx_key1的搜索条件是key1 IN ('e4', '2f', 'd7')，相当于3个单点区间：['e4', 'e4']、['2f', '2f']、['d7', 'd7']。

• 范围区间数量：使用idx_key1执行查询有3个单点区间，访问这3个范围区间的二级索引付出的I/O成本就是：

3 x 1.0 = 3.0

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

• 查找单点区间['e4', 'e4']对应的二级索引记录数：方法同上述连续范围区间计算方法，不再赘述。最后得到的单点区间['e4', 'e4']对应的二级索引记录数是：42。
• 查找单点区间['2f', '2f']对应的二级索引记录数：计算得到本单点区间对应的记录数是：39。
• 查找单点区间['d7', 'd7']对应的二级索引记录数：计算得到本单点区间对应的记录数是：34。
  

42 + 39 + 34 = 115

读取这些二级索引记录的CPU成本就是：

115 x 0.2 + 0.01 = 23.01

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

115 x 1.0 = 115.0

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

115 x 0.2 = 23.0

• I/O成本：

3.0 + 115 x 1.0 = 118.0 (范围区间的数量 + 预估的二级索引记录条数)

• CPU成本：

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

综上，使用idx_key1索引执行查询的总成本是：

118.0 + 46.01 = 164.01

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

• 全表扫描的成本：2110.3。
• 使用uq_key2的成本：121.41。
• 使用idx_key1的成本：164.01。

• 一条SQL语句的查询成本=I/O成本+CPU成本。
• 计算查询成本的元素包括：成本常数、微调值。

• 计算通过全表扫描的代价的元素包括：I/O成本元素（聚簇索引的页面数量、Data_length = 聚簇索引的页面数量 x 每个页面的大小）、CPU成本元素（该表中的记录数、Rows为表行数的预估值）。全表扫描成本计算公式：

全表扫描的总成本 = ((Data_length ÷ 16 ÷ 1024) × 1.0 + 1.1) + (Rows × 0.2 + 0.1)

• 计算通过二级索引的查询代价的元素包括：I/O成本元素（范围区间的数量 + 预估的二级索引记录条数）、CPU成本元素（读取二级索引记录的成本 + 读取并检测回表后聚簇索引记录的成本）。二级索引成本计算公式：

二级索引查询的总成本 = (范围区间的数量 × 1.0 + 预估的二级索引记录条数 × 1.0) + (预估的二级索引记录条数 × 0.2 + 读取并检测回表后聚簇索引记录条数 × 0.2 + 0.01)

今天就到这里吧，我们下篇见。站在巨人肩膀上，每天进步一点点。