B+树实现MySQL索引原理及索引失效问题

常见的索引实现方式优缺点分析（为什么选择B+树？）：
哈希表：
1、Hash存储需要将所有的数据文件全部加载到内存，比较浪费存储空间
2、Hash存储适合等值查询，而目前根据范围区间查询数据使用的更多

平衡二叉查找树 / B树：
尽管平衡二叉查找树查询的性能也很高，时间复杂度是 O(logn)。而且，对树进行中序遍历，我们还可以得到一个从小到大有序的数据序列，但这仍然不足以支持按照区间快速查找数据。

B+树：
B+树中的节点并不存储数据本身，而是只是作为索引。除此之外，每个叶子节点串在一条链表上，链表中的数据是从小到大有序的。如果我们要求某个区间的数据。我们只需要拿区间的起始值，在树中进行查找，当查找到某个叶子节点之后，我们再顺着链表往后遍历，直到链表中的结点数据值大于区间的终止值为止。所有遍历到的数据，就是符合区间值的所有数据。

我们知道，B+树的度 m 越大，那树的高度就越小，那B+树中的 m 是不是越大越好呢？到底多大才最合适呢？
不管是内存中的数据，还是磁盘中的数据，操作系统都是按页（一页大小通常是 4KB，这个值可以通过 getconfig PAGE_SIZE 命令查看）来读取的，一次会读一页的数据。如果要读取的数据量超过一页的大小，就会触发多次 IO 操作。所以，我们在选择 m 大小的时候，要尽量让每个节点的大小等于一个页的大小。读取一个节点，只需要一次磁盘 IO 操作。

尽管索引可以提高数据库的查询效率，但是，你应该也知道，索引有利也有弊，它也会让写入数据的效率下降。这是为什么呢？
数据的写入或删除过程，会涉及索引节点的更新，这是索引导致写入变慢的主要原因。

对于一个 B+ 树来说，m 值是根据页的大小事先计算好的，也就是说，每个节点最多只能有 m 个子节点。在往数据库中写入数据的过程中，这样就有可能使索引中某些节点的子节点个数超过 m；同样，在数据库中删除数据的过程中，这样也有可能使索引中某些节点的子节点个数少于 m/2 (B+树规定每个节点的关键字大于等于 m/2 ，小于等于 m)；这样就会涉及B+树的分裂与合并，B+树的调整大大增加了时间开销；所以，索引的存在会导致数据库写入和删除的速度降低。

下面说一说MySQL索引失效问题
索引失效：
1、违反最左匹配原则
最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上，如不连续，则匹配不上。
如：建立索引为(a,b)的联合索引，那么只查 where b = 2 则不生效。换句话说：如果建立的索引是(a,b,c)，也只有(a),(a,b),(a,b,c)三种查询可以生效。
2、遇到范围查询（>、<、between、like）就会停止匹配
比如：a= 1 and b = 2 and c>3 and d =4 如果建立(a,b,c,d)顺序的索引，d 是用不到索引的，因为 c 字段是一个范围查询，它之后的字段会停止匹配。
3、在索引列上做任何操作
如计算、函数、（手动或自动）类型转换等操作，会导致索引失效而进行全表扫描。

explain select * from user where left(name,3) = 'zhangsan' and age =20

这里对 name 字段进行了 left 函数操作，导致索引失效。
4、使用不等于（!= 、<>）

explain select * from user where age != 20;

explain select * from user where age <> 20;

5、like 中以通配符开头(’%abc’)
索引失效：

explain select * from user where name like ‘%zhangsan’;

索引生效：

explain select * from user where name like ‘zhangsan%’;

6、or 连接索引失效

explain select * from user where name = ‘2000’ or age = 20 or pos =‘cxy’;

参考：B+树：MySQL数据库索引是如何实现的？