Mysql基于成本而选择索引| 小册免费学-一一网

成本怎么定义

IO成本：我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为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.根据搜索条件，找出所有可能使用的索引

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

显然，，key_part1通过LIKE操作符和以通配符开头的字符串做比较，不可以适用索引，
possible keys包含有idx_key1和idx_key2。

2.计算全表扫描的代价

全表索引：全表扫描的意思就是把聚簇索引中的记录（B+树可以直接遍历叶子节点）都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集

因此成本就是IO次数+比较次数(记录数量)

怎么知道这个呢

mysql> SHOW TABLE STATUS LIKE 'single_table'\G
复制代码

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

以使用idx_key2执行查询的成本分析为例

回表：回表操作的本质就是通过二级索引记录的主键值到聚簇索引中找到完整的用户记录

对于使用二级索引 + 回表方式的查询，成本取决于

范围区间占了多少个页面（mssql粗暴的认为是一个，就离谱）
需要回表的记录数

先根据key2 > 10这个条件访问一下idx_key2对应的B+树索引，找到满足key2 > 10这个条件的第一条记录，我们把这条记录称之为区间最左记录。
然后再根据key2 < 1000这个条件继续从idx_key2对应的B+树索引中找出最后一条满足这个条件的记录，我们把这条记录称之为区间最右记录
如果区间最左记录和区间最右记录相隔不太远（在MySQL 5.7.21这个版本里，只要相隔不大于10个页面即可），那就可以精确统计出满足key2 > 10 AND key2 < 1000条件的二级索引记录条数。否则只沿着区间最左记录向右读10个页面，计算平均每个页面中包含多少记录，然后用这个平均值乘以区间最左记录和区间最右记录之间的页面数量就可以了。