成本的概念

MySQL语句执行的成本主要有两块：

I/O成本：InnoDB把存储在磁盘上的.ibd文件加载到内存中的成本，读取一个16KB的页，成本单位为1.0
CPU成本：读取记录行的成本（包括检测是否符合where条件，以及排序），不管条件是否判断，都耗费一个成本，读取一行记录，成本单位为0.2

单表查询成本

本质上就是对I/O成本和CPU成本的总量进行计算，最终选择使用哪个索引，或哪种查询方式

以以下表为例：

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;

有一个查询语句：

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

该查询可能用到的索引如下：

key1 IN ('a', 'b', 'c') 能匹配到二级索引idx_key1
key2 > 10 AND key2 < 1000 能匹配到二级索引idx_key2
key3 > key2 非常数比较，无法匹配索引
key_part1 LIKE '%hello%' 字符串比较，但是不满足最左前缀，也匹配不到

因此该语句备选查询方案就是全表扫描、基于idx_key1的range查询，基于idx_key2的range查询，三种

计算全表扫描成本

首先看I/O成本，I/O成本就是看扫描页数，因此就要找到总页数

使用如下语句：SHOW TABLE STATUS LIKE 'single_table'，看到一个结果：Data_length: 1589248

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

默认每个页16KB，因此可以计算到页数为1589248 ÷ 16 ÷ 1024 = 97

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

这里这个1.1是一个微调值，这个值是硬编码写死的，不需要关注其具体含义

再算CPU成本，即找下记录数，还是SHOW TABLE STATUS LIKE 'single_table'，看到一个结果：Rows: 9693

这里的ROWS是一个估计值，可能与实际值不一样，但是直接以这个算就行

因此CPU成本 = 9693 x 0.2 + 1.0 = 1939.6，这里1.0页数微调值

因此全表扫描总成本为98.1 + 1939.6 = 2037.7

计算索引查询的成本

以idx_key2为例，key2 > 10 AND key2 < 1000 实际上是一个区间(10, 1000)，也就是说要找到这个区间占了几个页，几条记录

首先是I/O成本，MySQL直接认为，一个区间就是一个页的成本，因此这里I/O成本 = 1 x 1.0 = 1.0

再看CPU成本

记录数是通过区间的左右端点所在的页，计算其中间有几个页，然后再乘以每页平均记录数得到的

怎么计算中间有几个页，可以到其目录页中，即找到左右端点所在页的目录，看看目录中的节点的链表的长度就行了

补链接图

如果目录的两个节点页跨页了，那就继续向上再找父节点，直到推导到在同一个页的

假设该案例算出来是95条记录，则CPU成本 = 95 x 0.2 + 0.01 = 19.01，其中0.01也是微调值

但是到这里还没完，因为上面只是计算了查询二级索引的成本，还需要回表

因为有95条记录，需要回表95次，引擎认为回表一次相当于一个页的成本，因此回表的I/O成本 = 95 x 1.0 = 95.0

回表的CPU成本 = 95 x 0.2 = 19.0

因此使用idx_key2的总成本是1.0 + 19.01 + 95.0 + 19.0 = 134.01

同样可以算出idx_key1的，然后就可以比较出哪个成本更低了，MySQL的计算引擎也就是通过这种方式选择使用哪个索引的

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

有时候使用IN语句可能产生多个单点区间，例如

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

这种情况下，如果区间少，一个区间一个区间通过直接访问父页的方式算有几个记录，还算方便，这种访问方式叫index dive

但是如果区间太多，直接访问就不方便了，MySQL就会使用统计数据来粗略计算

使用SHOW INDEX FROM single_table; ，我们得到一个结果：Cardinality: 968

这个Cardinality的含义就是不重复值的数量，是一个估计值

先前我们能取到ROW = 9693，那么计算9693 / 968得到的结果就是一个值重复多少次的估算值

然后乘以单点区间数量，就可以估算到一个记录数的结果，显然，也不是一个准确值

MySQL使用index dive还是估算值是通过一个属性决定的：

使用SHOW VARIABLES LIKE '%dive%'; 看到eq_range_index_dive_limit：200

即单点区间超过200，就使用估算值

连接查询的成本

在连接查询中，驱动表的成本是精确可以计算的，而被驱动表则是估算的

调节成本常数

在前面计算时使用的I/O成本1.0和CPU成本的0.2，都是可以调节的

执行SHOW TABLES FROM mysql LIKE '%cost%'; 得到的就是这两个值

而其真实存储在mysql.server_cost表中，可以使用查询语句：SELECT * FROM mysql.server_cost;

除此之外，mysql.engine_cost表还存储了一些引擎操作对应的常数

目录CONTENT

MySQL语句执行成本