前言

谈到索引大家并不陌生，其目的就是为了提高查询效率，用一句话概括就是排好序的数据结构，MySQL中索引主要通过B+tree 实现，对B+tree感兴趣的可以看我上一篇文章。为了对索引有更深层次的了解，下面从以下几个方面介绍索引。

一、索引的分类

1.从存储结构上划分为：BTree索引（BTree或B+Tree索引）、Hash索引、全文索引、空间数据索引，其中B+Tree索引是我们最常用到的。

2.从应用层面分为：普通索引，唯一索引，主键索引，复合索引。

• 普通索引：建立在普通字段上，没有任何限制；
• 唯一索引：与“普通索引”相似，不同的是索引字段必须是唯一的，允许有空值；
• 主键索引：主键索引是特殊的唯一索引，建立在主键字段上，不允许有空值存在；
• 复合索引：又称组合索引，由多个字段组合建立的索引，复合索引可以代替多个单列索引，相比多个单列索引复合索引所需的开销更小;

3.从存储结构和查询逻辑方面划分：聚集索引，非聚集索引

二、聚集索引和非聚集索引

在 MySQL 数据库中 InnoDB 存储引擎，B+ 树可分为聚集索引和非聚集索引。聚集索引也叫聚簇索引，非聚集索引也叫辅助索引或者二级索引。
建表的时候都会创建一个聚集索引，每张表都有唯一的聚集索引，：

• 如果主键被定义了，那么这个主键就是作为聚集索引
• 如果没有主键被定义，那么该表的第一个唯一非空索引作为聚集索引
• 如果没有主键也没有唯一索引，InnoDB 内部会生成一个隐藏的主键作为聚集索引

二者区别

1. 聚集索引一张表只能有一个，而非聚集索引一张表可以存在多个；
2. 聚集索引：索引排序与数据物理顺序一致，具有更快的检索速度，但是新增删除数据会比较慢，需要花费额外的时间进行重新排序来保证与数据物理顺序一致；
3. 非聚集索引：物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序；
4. 聚集索引叶子节点存储的是整行的数据，非聚集索引叶子节点存储的是主键的值；

三、什么是回表？

了解了聚集索引和非聚集索引，什么是MySQL回表也就不难理解了。假设通过聚集索引来查询数据，例如 select * from tb_name where id=10; 那么只需要搜索其叶子节点就可以找到id等于“10”这行数据。如果是通过非聚集索引来查询数据，例如 select * from tb_name where name='javaboy'，那么此时需要先搜索 username 这一列的索引的 B+Tree，搜索完成后得到主键的值，然后再去搜索主键索引的 B+Tree，就可以获取到一行完整的数据。

上述先通过非聚集索引找到主键值，再通过主键去聚集索引查询记录的过程，称为回表查询

四、什么是覆盖索引？

指从辅助索引查找记录时，索引字段列已经覆盖了查询结果的列，就不需要再通过聚集索引查询完整的记录，这种情况就叫覆盖索引。

举个例子：建立辅助索引 index（name, age）,当执行如下SQL：select name, age form t where name = 'zhangsan' and age = '25'; 由于索引的节点上保存的索引列的组合name和age, 也就不需要再进行回表查询了。

这也是为什么不建议用select *查询的原因，只返回查询需要的列，当索引字段覆盖了查询需要的列时，也就不需要回表了，可以减少大量io操作提高查询速度。

五、索引在什么情况下失效？

熟悉explain关键字的都知道索引并不是时时都会生效的，比如以下几种情况，将导致索引失效：

• 查询条件中带有or，除非所有的查询条件都建有索引，否则索引失效；
• like查询是以%开头；
• 索引字段使用了函数或者计算；
• 发生了类型转换；例如：select * from t where no = 20; 当 no类型是字符类型，等号右边为int类型时，执行时会发生隐式类型转换，从而导致索引失效；
• 不满足最左匹配原则；

最左匹配原则

最左匹配原则又称最左前缀原则，指的是在一个组合索引中（a,b,c），B+ 树会按照从左往右的顺序建立搜索树，B+ 树会优先比较 a，如果 a 相同在依次比较 b 和 c，但是像查询（b，c）这样的数据没有 a 字段，B+树就不知道从哪个结点查起了, 因为搜索树的第一个比较因子就是 a

总结：

由此可见，索引用的好能大大提高查询速度，前提是对索引有足够的了解，否者反而会SQL执行速度，当然索引并不是越多越好，还要根据具体的业务场景来选择，索引适合建立在离散度高的列，也就是重复数据较少的列，像性别只有男和女的列就不适合建立索引。