关于面试:是时候检查一下使用索引的姿势是否正确了

索引，能够无效进步咱们的数据库搜寻效率，各种数据库优化八股文里都有相干的知识点可背，不过单纯的被条目其实很容易遗记。

所以松哥想通过几篇文章，和大家认真聊一聊索引的正确应用姿态，联合一些具体的例子来帮忙大家了解索引优化，这是一个小小的系列，可能会有几篇文章，明天先来第一篇。

1. 索引列独立

当咱们将带有索引的列作为搜寻的条件的时候，须要确保索引不在表达式中，索引中也不蕴含各种运算。

我举个简略例子，假如我有如下一张表：

一个 user 表，里边就四个字段，每个字段上都建了索引，当初有三条测试数据：

咱们来比拟如下两个查问：

能够看到：

1. 第一个 type 为 ALL 示意全表扫描（没用上索引）；第二个 type 为 ref 示意通过索引查找数据，个别呈现等值匹配的时候，type 会为 ref。
2. 第二个的 key 指明了 MySQL 应用哪个索引来优化查问；rows 则显示了 MySQL 为了找到所需的值而要读取的行数.
3. 第一个的 Extra 为 Using where 示意这个搜寻须要在 server 层进行判断（过滤），即存储引擎层无奈返回满足条件的数据（当然这里也不须要回表，因为压根都没有用啥索引）。

从下面的剖析中能够看到，尽管 age-1=98 与 age=99 尽管在逻辑上并无二致，然而 MySQL 却无奈主动解析第一个表达式，进而导致第一个无奈应用索引。 所以，咱们不要在 where 条件中写表达式，不仅仅是下面这种表达式，一些应用了自带函数的表达式也不能应用，咱们要尽量简化 where 条件。

不过下面这个例子太牵强了，个别大家不会犯这种谬误，然而上面这个例子就不肯定了，可能会有小伙伴在下面栽跟头：查问最近一年出世的用户（birthday 列也是索引）：

在这张图里，我给出了两种不同的查问思路：

1. 对 birthday 做计算，如果 birthday 加上一年，失去的工夫大于以后工夫，那么阐明该用户出生日期在最近一年一年之内。
2. 对以后日期进行计算，如果以后日期减去一年失去的工夫小于 birthday，阐明 birthday 在一年之内。

依据上图 explain 的后果，很显著第一种计划没有用上索引，进行了全表扫描；而第二种计划则用上了索引，只读取了两行数据就能够了。究其原因，就是因为第一种计划在索引列上进行了函数运算，导致 MySQL 没法应用索引了。

2. 巧用笼罩索引

一般来说咱们不倡议在查问中间接应用 select *，应用 select * 有很多问题，其中一个问题就是无奈利用索引笼罩扫描（笼罩索引）。

那这里须要大家首先明确什么是笼罩索引。

在什么是 MySQL 的“回表”？一文中，松哥和大家聊了，索引依照物理存储形式能够分为聚簇索引和非聚簇索引。

咱们日常所说的主键索引，其实就是聚簇索引（Clustered Index）; 主键索引之外，其余的都称之为非主键索引，非主键索引也被称为二级索引（Secondary Index），或者叫作辅助索引。

对于主键索引和非主键索引，应用的数据结构都是 B+Tree，惟一的区别在于叶子结点中存储的内容不同：

• 主键索引的叶子结点存储的是一行残缺的数据。
• 非主键索引的叶子结点存储的则是主键值以及索引列的值。

这是两者最大的区别。

所以，搜寻时如果应用了非主键索引，那么一共会搜寻两棵 B+Tree，第一次搜寻 B+Tree 拿到主键值后再去搜寻主键索引的 B+Tree，这个过程就是所谓的回表。然而，如果搜寻的字段刚好就在二级索引的叶子结点上，那么是不是就不须要回表了？咱们来验证下。

假如我有如下一张表：

CREATE TABLE `user2` (`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gender` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `username` (`username`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

id 是主键，username 和 address 是复合索引。

这表有三条记录：

咱们来做个简略测试，先来看如下 SQL：

 explain select username,address from user2 where username='javaboy';

这个查问 SQL，咱们查问的字段是 username 和 address，因为这两个字段是复合索引，因而都保留在二级索引的 B+Tree 的叶子结点中，搜寻到 username 后也就能拿到 address 的值了， 因而不须要回表查问 。大家留神最初 Extra 中的 Using index 就是这意思。

Using index 示意应用索引笼罩扫描来返回记录，间接从索引中过滤不须要的记录并返回命中后果，这是在 MySQL 服务器层实现的，然而无须再回表查问记录。

雷同的情理，id 的值也存在于二级索引中，按理说也不须要回表，所以我略微批改一下查问 SQL，退出 id，大家来看下：

explain select username,address,id from user2 where username='javaboy';

能够看到跟咱们想的一样。

那么我再加上 gender 呢？如果要查问的字段中蕴含 gender，因为 gender 并没有保留在二级索引的的叶子结点中，那么此时就须要回表查问了：

explain select gender from user2 where username='javaboy';

能够看到，此时 Extra 为空，同时用到了二级索引 username，那么此时就须要回表了。

这个就是笼罩索引，巧用笼罩索引，能防止回表，进步查问效率。那么此时就要尽量避免应用 select * 了（因为一般来说不太可能给所有字段都建设一个复合索引）。

好啦，不晓得小伙伴看明确没有，下篇文章咱们持续~