乐趣区

关于mysql:好的索引当然是要覆盖了

jiezi

如果你的查问中用到了索引，这是一个提高，如果可能更进一步，用到了笼罩索引，那么就更牛了！当咱们设计一个索引的时候，如果可能从一个更加全面的角度去设计这个索引，不仅思考到 where 中的条件，还可能思考到整个 SQL，那么无疑这个索引的设计将是十分胜利的。

当然不能为了笼罩而笼罩。

1. 什么是笼罩索引

要了解什么是笼罩索引，咱们须要先来回顾一下 InnoDB 中索引树的数据结构。

假如我有如下数据：

id(主键) username age address gender
1 ab 99 深圳 男
2 bw 95 天津 男
3 cx 93 深圳 男
4 bc 80 上海 女
5 bg 85 重庆 女
6 ac 98 广州 男
7 bw 99 海口 女
8 ck 90 深圳 男
9 cc 92 武汉 男
10 af 88 北京 女

当初我给 username、age 以及 address 三个字段建设一个联结索引，那么这个联结索引的 B+Tree 可能是这个样子：

下面这个索引树是一个非汇集索引或者也能够说是一个二级索引，这种索引区别于咱们之前文章跟大家聊的汇集索引（再聊 MySQL 聚簇索引），在汇集索引中，叶子结点就是这一行的数据，然而在二级索引中，叶子结点中保留的是主键值。

所以，当咱们搜寻的时候，如果应用的是二级索引，那么最终拿到的是主键值，有了主键值之后，咱们还须要再去到聚簇索引中进行搜寻，能力拿到残缺的数据，这个过程咱们也称之为回表。

很显著，如果进行了回表操作的话，那么执行效率显然就要降落一截，那么是否用到了二级索引就会回表呢？其实不然！如果是笼罩索引的话，就不须要回表。

那么什么是笼罩索引呢？

小伙伴们察看下面的索引树，大家发现在这个索引树中，离叶子结点最近的树枝上有 username、age 以及 address，而叶子结点上有 id，所以如果我想要查问的字段是 id、username、age 以及 address 中的任意一个或者任意几个的话，那么就不须要再去聚簇索引上查问了，以后这个 B+Tree 上间接就有现成的，间接返回即可，这个就是笼罩索引。

2. 实际

当初假如我有如下一张表：

CREATE TABLE `user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_prop_index` (`username`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

能够看到，这张表中有一个名为 user_prop_index 的索引，这个索引中一共存在三个字段，别离是 username、age 以及 address，当初咱们来看如下 SQL 的执行打算：

explain select address from user where username='ab' and age=99\G

小伙伴们看到，Extra: Using index 就示意应用到了笼罩索引，因为我的查问 SQL 中最终想要的值，都在以后这棵索引树上。

更进一步，假如我要查问 id、address 以及 age 字段，如下：

explain select id,address,age from user where username='ab'\G

很显著，因为这三个字段都在索引树上，所以间接间接通过回表获取到。

然而，如果想间接 select *，那么因为这个索引树上没有 gender 字段，此时就必须要回表能力拿到 gender 字段的值，如下：

explain select * from user where username='ab'\G

能够看到，这个时候没有用到笼罩索引了。

3. 笼罩索引的劣势

通过后面的介绍，笼罩索引的劣势置信小伙伴们也能本人总结进去：

  1. 笼罩索引不须要回表，间接在 B+Tree 这颗索引树上就能读取到须要的数据，这极大的缩小了数据库 IO 次数，在 IO 密集型利用中，这样的性能晋升十分无效。
  2. 基于 B+Tree 中联结索引数据的排序规定，笼罩索引中，如果波及到范畴搜寻，也是十分高效的（如果波及到回表的话，效率就会升高很多）。
退出移动版