什么是成本
I/O成本
我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
CPU成本
读取以及检测记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，设计MySQL的大叔规定读取一个页面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。1.0、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) ) 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‘;
1. 根据搜索条件，找出所有可能使用的索引
上边的查询语句可能用到的索引，也就是possible keys只有idx_key1和idx_key2。
2. 计算全表扫描的代价
由于查询成本=I/O成本+CPU成本，所以计算全表扫描的代价需要两个信息：
聚簇索引占用的页面数 和 该表中的记录数
mysql> USE xiaohaizi; Database changed mysql> SHOW TABLE STATUS LIKE ‘single_table‘\G *************************** 1. row *************************** Name: single_table Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 9693 Avg_row_length: 163 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
对于使用MyISAM存储引擎的表来说，该值是准确的，对于使用InnoDB存储引擎的表来说，该值是一个估计值。
Data_length
Data_length = 聚簇索引的页面数量 x 每个页面的大小(16*1024)
聚簇索引的页面数量 = 1589248 ÷ 16 ÷ 1024 = 97
I/O成本
97 x 1.0 + 1.1 = 98.1
//97指的是聚簇索引占用的页面数，1.0指的是加载一个页面的成本常数，后边的1.1是一个微调值，我们不用在意。
CPU成本：
9693 x 0.2 + 1.0 = 1939.6
//9693指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计值，0.2指的是访问一条记录所需的成本常数，后边的1.0是一个微调值，我们不用在意。
总成本：
98.1 + 1939.6 = 2037.7
综上所述，对于single_table的全表扫描所需的总成本就是2037.7。
3. 计算使用不同索引执行查询的代价
使用idx_key2执行查询的成本分析
范围区间数量
不论某个范围区间的二级索引到底占用了多少页面，查询优化器粗暴的认为读取索引的一个范围区间的I/O成本和读取一个页面是相同的。本例中使用idx_key2的范围区间只有一个：(10, 1000)，所以相当于访问这个范围区间的二级索引付出的I/O成本就是：
1 x 1.0 = 1.0
需要回表的记录数
步骤1：找到满足key2 > 10这个条件的第一条记录，我们把这条记录称之为区间最左记录
步骤2：找到满足key2 < 1000这个条件的最后一条记录，我们把这条记录称之为区间最右记录
步骤3：找到满足key2 > 10这个条件的第一条记录，我们把这条记录称之为区间最左记录
idx_key2在区间(10, 1000)之间大约有95条记录。
访问这个范围区间的二级索引付出的I/O成本就是： 1 x 1.0 = 1.0
回表操作带来的I/O成本就是：95 x 1.0 = 95.0
读取这95条二级索引记录需要付出的CPU成本就是：95 x 0.2 + 0.01 = 19.01
回表操作带来的CPU成本就是： 95 x 0.2 = 19.0
综上所述，使用idx_key2执行查询的总成本就是：
96.0 + 38.01 = 134.01
使用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
4.对比各种执行方案的代价，找出成本最低的那一个
全表扫描的成本：2037.7
使用idx_key2的成本：134.01
使用idx_key1的成本：168.21
所以当然选择idx_key2来执行查询喽。
基于索引统计数据的成本计算
SELECT * FROM single_table WHERE key1 IN (‘aa1‘, ‘aa2‘, ‘aa3‘, ... , ‘zzz‘);
获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记录之间有多少记录。通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称之为index dive。
对于InnoDB存储引擎来说，使用SHOW INDEX语句展示出来的某个索引列的Cardinality属性是一个估计值，并不是精确的。
当IN语句中的参数个数大于或等于系统变量eq_range_index_dive_limit的值的话，就不会使用index dive的方式计算各个单点区间对应的索引记录条数，而是使用索引统计数据
一个值的重复次数 ≈ Rows ÷ Cardinality
成本=一个值的重复次数*（IN语句中数量）