B+Tree做索引及索引失效

为什么MySQL采用B+Tree作为索引

前言：

1. 磁盘的读写比内存的访问慢上万倍甚至几十万倍

2. 磁盘读写的最小单位是扇区，一个扇区512B，

3. 操作系统最小读写单位是块，Linux中块的大小为4KB

4. 一次I/O操作会直接读写8个扇区

5. 数据库的数据和索引是存储在磁盘上的

所以通过上述描述，我们知道，在进行数据库读写时是特别慢的，所以我们希望索引的数据结构能尽可能的减少磁盘的I/O操作，这样消耗的时间也就越小

数据最好可以是有序的，这样我们就可以使用二分查找

1. 二分查找树是天然的非线性适合二分查找的数据结构，但是当每次插入数据都是树内最大值，会导致二分查找树退化成一个单向链表

2. 自平衡二叉树，这个解决了退化的问题，但是因为每个节点只能保存两个子节点，但数据量特别大时，会导致树的高度变高，也就意味着更多次的I/O操作

3. 此时想出一个M叉的树，也就是B树但是B树也有自己的缺点

   1. B树的每个节点都包含数据(索引+记录)，所以每次都需要读取记录的数据，增加了磁盘I/O次数

   2. 当我们读取记录数据而不是索引数据时，会将记录数据从磁盘拷贝到内存，占用内存空间

4. 最后选定数据结构B+Tree，除叶子节点有记录数据外，其他节点只记录索引数据

   1. B Tree和B+Tree区别

   2. B+Tree只有叶子节点会存放记录数据，其他节点只存放索引数据

   3. B+Tree的叶子节点之间有从左指向右和从右指向左的节点，在叶子节点之间构成一个有序链表

   4. 有多少个非叶子节点，就有多少个索引

   5. 相同数据量下，B+Tree的层高会低一些

索引失效

前言：InnoDB和MyISAM都支持B+树索引，但是潭门数据的存储结构实现方式不同，不同之处在于

1. InnoDB存储引擎：B+树索引的叶子节点保存数据本身

2. MyISAM存储引擎：B+树索引的叶子节点保存数据的物理地址

对索引使用左或者左右模糊匹配

当我们使用左或者左右模糊匹配的时候，也就是like %xx或者like %xx%这两种方式会造成索引失效，而like m%这种不会失效

原因：

我们以查询一个字符串进行举例，假如我们查询一个字符串me，

1. 会先根据ASCLL码值进行排序，a-g-o-z，可能第一层索引是这样的，我们知道m在g-o之间

2. 接着在g-o之间的下一层进行索引，可能是g-i-l-o，我们知道在l-o之间

3. 到达叶子节点l-m-n-o，在这一层找到m，里面包括ma-mb-.......-mz

4. 之后查找到me，这是一个局部有序的索引检测

5. 但是如果是查找%m，我们不知道m前面是a还是b或者是x，是无序的，索引失效

对索引使用函数

索引使用的是字段的原始值，当使用函数后，字段的值发生该表，导致索引失效

注意

MySQL8.0开始，索引特性增加了函数索引，我们可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值

索引进行表达式计算

例如，我们要根据id查询数据数据，我们此时如果是id + 1 =11；这个时候索引就会失效，失效原理与对索引使用函数差不多，但是如果我们写的是id = 10+1；此时索引不会失效。

注意

既然id = 10+1可以，那么数据库为什么不在内部进行优化，将id + 1 =11；优化成 id = 11-1

原因：因为数据运算的可能性有多种，如果我们进行判断优化的话，代码量会比较臃肿，与其进行内部优化，不如告诉程序员在使用时直接指定

对索引隐式类型转换

但数据发生隐式转换的时候会失效，varchar类型会隐式转换成int类型

如果表中字段类型为varchar，而传参时是int类型，会发生隐形转换，导致索引失效

举例

phone varchar(255)

select name from user where phone = 123456;

此时查找是全表查找，索引失效

注意

如果表中字段类型是int，传参时是varchar，索引不会失效

举例

phone int;

select name from user where phone = "123456";

此时是走索引扫描的

联合索引最左匹配原则

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效。

where子句中的or

where语句中的or，如过or前的条件是索引列，而or后的条件不是索引列，会导致全表搜索，解决方法，将or后条件设置为索引列