索引的必知小知识

什么是索引

我们可以类比现实中的一个问题，当你看一本英文词典的时候没有目录的话，那么你要找某一个单词的话，那你是不是只能一页一页的翻找，这个效率真的会很低，所以这个时候索引就应运而生，让我们通过索引能够快速定位到相应的数据位置。
在mysql中索引跟执行引擎有关，比如MyISAM和InnoDB之间就存在一定的区别。

MyISAM和InnoDB索引的区别

1. MyISAM是非聚簇索引，数据和索引是分开的，索引的叶子节点保存的是数据的地址，并且针对于myisam来说，并不需要一定要有主键。
   InnoDB是聚簇索引，数据和索引是存储在一个data上面的，主键索引的叶子节点直接存储数据，其他索引的话叶子节点存储的是主键id。
2. MyISAM使用的最细粒度锁为表锁，InnoDB使用的最细粒度锁为行锁。
3. MyISAM没有事务概念，也不支持MVCC。
4. MyISAM不支持外键。

索引相关结构

B树索引

B-Tree的每一个节点其实都存储数据，并且叶子节点之间不存在指针的关系，所以针对于B-Tree来说时间复杂度是在O(1)–O(logn)之间的。
B-Tree不存在有冗余索引的情况，所以树整体更大。

B+树索引

B+树相对于B树来说还是有很大的不同的，在很多点上面都进行了优化：

1. B+树的非叶子节点不进行数据存储，只存储键值对，大大降低页的大小以及能够存储数据的数量，Innodb默认一页数据大小为16K，这样可以保证一页能够存储更多的键值对，使得树变得更矮更胖，查询所需执行的IO以及内存会有所减少。
2. 但是因为键值对不进行数据存储，也就导致可能出现回表的问题。
3. B+树的每个页之间都有一个双向链表链接，在排序查询，范围查询等方面，B+树表现得更为优秀。

hash索引

hash索引在某些情况下是比B+树效率更高的，比如搜索某一个固定的值，直接通过hash获取到键值对便能获取数据时间复杂度为O(1),但是在范围查询，排序等等操作下，hash的效率是很低的，而且容易出现hash冲突。

索引维护

索引维护方面主要从两方面讲起：

1. 为什么最好使用有序自增主键？
   为什么建议使用有序自增主键呢，因为B+树在插入新的值的时候都要确保数据的有序性，比如我们原始数据页中存在100，200，400三个主键数据，这个时候我们插入300这个数据，就可能会导致400需要向后挪动空出位置，如果此时刚好当前页满了，那么就会出现页分裂的问题，也就是需要将400挪到下一页，然后300才能插入，降低了空间的利用率，也会损耗部分性能。
   这里还有一个好处在于，如果不适用自增主键，普通索引的叶子节点可能就会很大，所以主键索引越小，普通索引占用的空间也就越小。
2. 为什么不建议物理删除？
   其实和上面的问题很类似，既然有页分裂那必然就会有页合并，页合并说的就是相邻的两个数据页物理删除了部分数据，利用率很低的情况下，就会将数据页合并。

覆盖索引

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `display_name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `age_idx` (`age`)
) ENGINE=InnoDB

我们创建如上表，这时候插入数据之后，我们要进行如下语句查询select * from user where age between 1 and 3，这个时候查询是怎样的呢。过程如下所示：
首先是在age_idx索引取到age=1的记录，然后到主键索引树上面查询到相应的数据；
然后是在age_idx索引取到age=3的记录，然后到主键索引树上面查询到相应的数据；
在age_idx索引树取下一个值的时候，不满足条件，循环结束。
从上面可以看出，我们进行了两次回表，那么我们如何进行避免，于是便引申出我们的覆盖索引。
如果换成如下查询就会达到索引覆盖的效果select id from user where age between 1 and 3，因为id在age_idx索引树上面已经存在所以就不需要回表。
联合索引也可以解决我们的大部分需求，避免回表，不如通过年龄查到所有的姓名，那么就可以针对姓名和年龄创建联合索引，避免回表，减少语句执行时间。

最左匹配原则

上面我们说到了联合索引，那么联合索引有一个最左匹配原则想必大家都清楚。

下面我们以上面表为例创建一个联合索引name_age_idx，这个时候我们查询如下语句select id from user where name = '张三' and age between 1 and 3，这个时候联合索引就起到了作用，能够很快查出结果，但是什么情况下会导致联合索引的失效呢？

1. 比如我们如下查询name like ‘张%’，索引不会失效，但是我们用’%张%‘或者’%张’，都会导致索引失效；
2. 在我们违反最左匹配原则的情况下也会失效，比如select id from user where age between 1 and 3 and name = '张三' ，这个时候并不会走联合索引；
3. 范围查询的情况右边会失效，假设我们创建的索引为age_name_idx，有如下查询语句select id from user where age > 1 and name = '张三' ，那么name就不会走索引。
   所以我们创建联合索引的时候一定要有限考虑到那个字段是在搜索的时候比较常用的，则将其排到前边，这样可以避免创建多索引，减少空间占用。

索引下推

索引下推其实可以从下面两张图可以看出区别。

我们通过name和age的联合索引查询的时候，在mysql5.6之前需要回表四次才能将所有数据取出，在5.6之后进行了优化，引入了索引下推的功能，如下图：

最大的区别在于在联合索引内部是否对数据进行了筛选，比如有了索引下推之后，我们就仅需要回表两次。