mysql innodb索引总结

innodb索引数据结构

基础数据结构：B+树

innodb索引是一个B+树的结构。我们知道，B树是一个用各种旋转法则来维持平衡（就是整棵树看着很匀称，没有某一个分支特别长或者特别短的局面）的一种多叉树，而B+树就是在此基础上，把所有的叶子节点用双向循环链表连起来。具体如此：

B+树为了维持平衡，在插入和删除时会做多种旋转操作，比如说插入操作：

删除操作：

这里的填充因子最小是50%，什么意思呢？就是说当一个k叉树叶子节点里存放的值小于0.5k了，这就需要进行合并操作了。具体的操作可以看上面的表，这里不再细讲，因为B树和B+树的维护本来就比较复杂，而这并不是本文的重点。

为什么用B+树而不用B树？ 这个问题关键就在于那些用双向循环链表连接在一起的叶子节点上。数据库最常用的操作之一就是范围查询，而B树的叶子节点不连接，导致要在B树上做范围查询时只能多次地去查找非叶子节点，而这就会导致不必要的磁盘I/O和查找操作。如果是B+树的话，找到一个，其他的顺着链表查下去就行了。

在磁盘上的存储

innodb的磁盘存储有“页”这个概念，页是读取磁盘的最小单位。一个页默认是16KB，系统经过解析sql语句，首先读取装有非叶子节点page页，遍历非叶子节点，这个过程随着节点的遍历会将一个或多个page页加载到内存，直到定位到这条记录的叶子节点，然后遍历找出该条记录。
需要注意的是页和上面这个B+树结构的节点没有强相关关系，也就是说不是一个节点就是一个页。一个节点有可能被拆到了两个页上。页之间是双向链表，页内是单向链表。

读取数据的时候，是一个页一个页地往内存中加载数据。

和MYISAM的区别

二者底层都是b+树，最大的区别：MYISAM底层的叶子结点是指向磁盘的指针，不会直接存储数据；而INNODB的叶子结点直接存储数据，也就是一个叶子结点事实上是表中的一行。
还有一个小区别：MYISAM不支持事务，而INNODB支持。原因就是MYISAM只存指针，所以只能支持到表锁，而事务所需的行锁回滚就无法支持了。所以MYISAM只适用于日志存储等数据量大且不太重要的需求。

聚集索引和非聚集索引

聚集索引：就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。每个数据页都通过双向链表链接，就像我们上面说的一样。
非聚集索引（辅助索引）：并不按照主键来，也就是表中建立的除主键外的所有索引。叶子节点并不包含全部的行数据，但是包含了一个书签，用来告诉InnoDB引擎哪里可以找到与索引相对应的行数据。这个书签实际上就是行数据对应的主键。
在辅助索引中查找数据，即使找到了，也需要进行一次根据书签查找主索引（聚集索引）的操作，这个操作就称为回表。当然，如果聚集索引的叶子节点恰好包含了用户所需要的数据（因为这个用户他可能没想要全部的字段……），此时也可以直接返回，这种情况我们就称为覆盖索引。

优化器如何选择索引（为什么选择不使用索引）

一般见于这种情况：用户要查询的是整行数据（或辅助索引无法覆盖到的数据），此时如果需要查询的数据行数比较少，则使用辅助索引；若数据行数较多，则直接使用PRIMARY聚集索引，也就是全表扫描。
为什么呢？这是因为，一旦辅助索引不能覆盖到完整的查询信息，就需要进行再一次的书签查找。虽然辅助索引上的数据是有序的，但这再一次的书签查找则是无序的，因此变成了磁盘上的离散访问操作。这种情况下，只有几行数据倒也罢了，大量的数据还不如直接全表扫描呢。

其余几种选择不使用索引的情况：
1、order by 不使用索引。
2、没有搜索条件、没有排序条件、没有group by条件时，就不使用索引。
3、union 查询操作，哪个字段是第一个索引字段，就在那个查询子句上使用索引。
4、下面的最左匹配原则中，不符合的都不会使用索引。

联合索引和最左匹配原则

联合索引：就是在表的好几个字段上建立索引，如建立索引为：（a,b,c）三个字段，其实相当于建立了(a)、(a,b)、(a,b,c)三个索引。这样看起来索引是向左边聚合的，故又称最左匹配原则。注意这里，查(b)或(b,c)的话是不走索引的，这也是一个经常的考点。
利用联合索引加速查询时，联合查询条件符合“交换律”，也就是where a = 1 and b = 1 等价于 where b = 1 and a = 1，这两种写法都能利用索引KEY union_index (a,b,c)。
创建联合索引时，我们可以考虑以下几点：
1、如果a、b、c中存在主键，优先选它做a。
2、如果a、b、c中存在访问最频繁的键（其实一般也就是主键……），优先选它做a。
3、如果a、b、c中访问频次都差不多，并且有一个键最短，优先选它做a。（键的长短关系到建立好的索引的存储空间）
其实最左匹配个人认为还可以把几种情况划归进去。

模糊查询： like’%xxx’。此时like字符串的最左端不能是通配符，否则不会走索引。必须是like’xxx%'才可以。此时，如果一定要求要%xxx，可以创建一个反向键索引。

group by： 使用索引时，查询字段在第一个字段时 ，使用索引，查询字段在第二个字段时，不使用索引。

in查询操作：查询字段在第一个字段时 ，使用索引，查询字段在第二个字段时，不使用索引（not in在表中的查询数量大于30%的时候是全表扫描的，用不到索引。所以没有可以优化的。）

唯一索引和普通索引的性能对比

如果有一个数据列不会有重复数据现在需要选择创建索引，我们应该如何去选择呢？
1.普通索引
查找时，遇第一个不符合条件的才返回。
更新时，找到位置直接更新。
2.唯一索引
查找时，遇到第一个符合条件的直接返回。
更新时，先查找是否唯一，再找到位置然后再更新。
可以看出，普通索引查找时的开销更大，而唯一索引更新时的开销更大。实际上，能够用普通索引的话，还是不要用唯一索引，具体可以参考这一篇文章。