两分钟，谈谈 MySQL 索引的一些错误认识

你好，我是yes。

今天就来谈谈有关 MySQL InnoDB索引的一些比较容易被误解的点。

话不多说，直接发车！

不一定会用上你认为会用上的索引

为了故事可以顺利的发展，先来一张表， DDL 如下：

现在要执行这个语句SELECT * FROM yes.t1 where f3 = 11;

你觉得用的是哪个索引？

很多同学可能会觉得虽然有 idx_f2_f3 这个联合索引，但是查询条件是 f3，没有 f2，不符合最左匹配原则，所以很显然只能进行全表扫描。

我们来 explain 一下，看看：

可以看到结果，key 是 idx_f2_f3 ，所以这个查询用上了 idx_f2_f3 这个索引！

这里可能有人就理解不了，怎么不符合最左索引原则也能用上联合索引了呢？

从 explain 结果来看，确实用上的联合索引，但是它的 type 是 index，所以它不是你认为的那种用上索引，它等于全扫描的联合索引来获取结果。

现在不太理解没事，听下面好好解释。

到底什么叫用了索引？

我们都知道 InnoDB 是基于聚簇索引来构建数据的，在没有创建其它二级索引的前提下，你查询数据就必须且只能用聚簇索引，因为数据就只在这个索引上，那你找数据不用这个索引那用什么呢？

如果你建起了其他二级索引，那么查找数据就可能不用聚簇索引，而是只用上二级索引就能直接得到想要的数据(就是上面查询的例子)。

综上所述，你在 InnoDB 查找任何数据，都只能用索引。

那为什么平日里有人说这个不走索引，走了全表扫描呢？

我们都知道默认 InnoDB 的索引结构是 B+ 树，这个树有个特性，它的叶子节点之间是通过链表相连的。

所以只要我们找到主键索引最左边的叶子结点，然后往右顺序遍历扫描，就能得到这张表的所有记录，这样的扫描方式就叫全表扫描。

小贴士：我不清楚 MySQL 是记录了最左叶子节点的位置还是根据根节点查找到最左叶子节点的，反正意思就是那个意思，不影响我们理解全表扫描。

那什么叫用上索引？

看红色的查询路线没，能利用树的快速查找能力来定位到数据所在的页，这样的查询才能算用上索引。

那从主键索引反推到二级索引，其实是一样的。能利用二级索引树的特性来查找数据，就是用上了索引。

但是如果用不上二级索引树的快速查找，是否有像全表扫描这样，直接扫描二级索引的所有叶子节点的操作呢？

有的，就是我们上面的例子，我再把执行结果的截图搬下来：

这次查询 MySQL 就是全扫描二级索引 idx_f2_f3 来直接返回所要的数据。

原因就是二级索引已经包含了查询所要到全部数据:f1、f2、f3，所以直接全扫描二级索引即可。

可以看到 explain 的 type=index，即全扫描二级索引。

这也叫用上了索引。

此时我们换用 f2 来作为查询条件，这样就符合最左匹配原则了，我们来看看 explain 有什么区别：

可以看到还是用了 idx_f2_f3 ，只是 type 变成 ref ，这个 ref 就表明可以与常量进行等值匹配时来快速查询得到结果。

这也叫用上了索引。

好了，我们来总结一下到底什么叫用了索引：

• 利用主键索引快速查找
• 利用二级索引快速查找
• 全扫描二级索引进行查找

以上这三种就是我们平日里所说的用上了索引，然后全扫描主键索引就是我们所说的全表扫描（等价于没用上索引，但是我们知道它其实是用上了主键索引对吧？）。

至此，我想你应该已经清楚，到底什么叫用了索引了。

小疑惑

关于 MySQL 索引的一些认识都讲完了，不过看了上面的例子，有些同学可能会有点疑惑：

既然都要全扫描，为什么要扫描二级索引，而不是主键索引？

因为主键索引存储了所有的数据，包括数据行相关的事务ID、回滚指针等等。

而二级索引不包含这些，它只有主键值和索引列的值。

所以相对而言直接读取二级索引开销会更小一些，所以在索引能覆盖返回值的时候，一般都会选择二级索引来查找。

比如我把上面的表再加个字段：f4。

现在我们再来执行SELECT * FROM yes.t1 where f3 = 11;：

可以看到，这个时候就没用上二级索引了，而是直接全表扫描了。

原因就是二级索引无法覆盖返回值，因为多了个 f4 的字段。

如果用了 idx_f2_f3 这个索引，想要得到 f4 的值，还需要再得到的主键再去主键索引里面进行查找，然后才能得到 f4 的值，这样回表的效率就低了。

当然其实结果还不一定，有时候也有可能 MySQL 的统计信息有误，用错了索引。因为 MySQL 是基于成本来选择执行计划的，有 I/O 成本和 CPU 成本，哪个索引成本低就用哪个索引。

所以，下篇我再来分析分析这个成本到底是怎么计算的！

最后

相信你已经完全理解到底什么才算用上索引了吧？

好了，今天的文章就到此为止，等我下篇的 MySQL 成本分析吧！

可以点击下方的名片，关注我哟~

---

我是yes，从一点点我亿点点我们下篇见。