记一次“探索MySQL优化器的工作原理”（网上提到的索引失效情况真的一定会发生吗？）

    当看见触手可及的结论时大多讨厌尝试那拐弯抹角的过程，所以沿途美丽的风景也可能会错过吧。（测试用的MySQL版本为8.0.18)

    今日记录一篇“探索有关MySQL优化器如何工作”的实战经历，这篇文章体现的中心思想就是“MySQL的优化器会按照`最优路径选择算法`给出一个较优的执行计划”，会打破我们的固有思维。

第一个知识点（覆盖索引），即存储引擎仅通过检索（相关知识点`ICP`)二级索引并通过条件筛选的key-value 就满足查询字段。

实例：（这是一个简单且形象的介绍覆盖索引和探索优化器工作原理的例子）

#建表SQL
create table why_index(
    id int unsigned primary key auto_increment comment '自增主键id',
    k1 int unsigned not null default "0" comment '该字段将添加一个索引',
    c1 varchar(32) not null default "" comment '对照字段`另有它用`',
    index idx_k1(k1)
) engine = innodb, character set utf8mb4, comment "探究优化器选择索引";

#插入数据SQL
insert into why_index(k1, c1) values
(5, 'a5'),
(3, 'a3'),
(4, 'a4'),
(2, 'a2'),
(1, 'a1');

我们可以通过SQL最终的检索结果，反推SQL的执行路径

select * from why_index limit 3;

 结果集是 [{1, 5, a5}, {2, 3, a3}, {3, 4, a4}] ，id是顺序返回的，可以推测其执行计划是`全表扫描`（可以通过explain验证）

select c1 from why_index limit 3;

 结果集是[a5, a3, a4]，同上一个结果集检索的结果顺序一致，也可以推测其执行计划是`全表扫描`（可以通过explain验证）

select k1 from why_index limit 3;

 该SQL检索的结果集并不是[5, 3, 4]，而是[1, 2, 3]，至于为什么？

select id from why_index limit 3;

 该SQL检索结果集并不是[1, 2, 3]，而是[5, 4, 2]，对应今天讲的主题！

图示底层数据结构：

从B+树叶子节点所占用的存储空间大小可以预测（这里暂不考虑真实的B+树结构），扫描二级索引的耗时是小于扫描主键的耗时，优化器会选择较少扫描时间的索引k1生成执行计划。

 

然后讨论一个一般索引失效的场景：（组合索引`idx_k12`在不满足最左前缀规则情况下使用，优化器会选择该索引并生成执行计划吗？）

#组合索引范围搜索 优化器选择校验
create table mutil_key(
    id int unsigned primary key auto_increment,
    k1 int unsigned not null default "0",
    k2 int unsigned not null default "0",
    index idx_k12(k1, k2)
) engine=innodb, character set utf8mb4, comment '组合索引范围搜索 优化器选择校验';

#插入测试数据
insert into mutil_key(k1, k2) values
(4, 5),
(3, 1),
(5, 2),
(1, 3),
(2, 4);

已k2作为查询条件

select * from mutil_key where k2 > 1;

返回的结果集是： 

[{4, 1, 3}, {5, 2, 4}, {1, 4, 5}, {3, 5, 2}]

从结果集就可以证明（当然也可以通过explain验证）数据引擎是通过扫描idx_k12获取到查询的字段的（索引idx_k12叶子节点k-v覆盖查询字段）;

改一下表结构后

alter table mutil_key add k3 int unsigned not null default "0";

结果集是:

[{1, 4, 5}, {3, 5, 2}, {4, 1, 3}, {5, 2, 4}]

再执行相同的SQL时，优化器就并没有选择组合索引idx_k12，而是全表扫描；

 

不过深入研究一下会发现，优化器在选择了组合索引 idx_k12 时，数据引擎实际扫描了所有叶子节点，并没有用到B+树查询的特性。

其实创建索引时按照一般索引创建规则，没啥问题，毕竟都是前辈们的经验。