MySQL索引详解

索引

索引好文章
索引是帮助MySQL高效获取数据的数据结构。

左边是数据表，最左边是数据记录的物理地址。
为了加快Col2的查找，可以维护右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应记录物理地址的指针

我对索引的理解：被设置为索引的字段，比如主键ID，会被设置为索引值并形成树，树中节点包含索引值和data(data的具体实现内容取决于不同的存储引擎)

Btree索引

B-/B+树

B-树

B-树，这里的B代表balance，B-树是一种多路平衡搜索树，类似普通的平衡二叉树，不同的是B-树允许每个节点有更多的子节点。它的每一个节点最多包含M个孩子，M就是B树的阶，M的大小取决磁盘页的大小。
ps(B-树就是B树，不要念成B减树)

特点

• 所有的键值分布在整棵树中
• 任何关键字(data)出现且只出现在一个节点中
• 搜索有可能在非叶子节点就结束了
• 性能逼近二分查找

B+树

B+树是B-树的变体，也是一种多路搜索树，与B-树的不同之处在于：

• 所有关键字(data)存储在叶子节点
• 为所有叶子节点增加了一个链指针，形成了有序的链表
• 每个父节点的元素都同时存在于子节点中，是子节点中最大或最小的元素
• 由于父节点的信息都包含在子节点中，因此所有的叶子节点包含了全部的元素信息

为什么使用B-/B+树？

红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构
MySQL 是基于磁盘的数据库系统,索引往往以索引文件的形式存储的磁盘上,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取，I/O存取的消耗要高几个数量级,索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。为什么使用B-/+Tree，还跟磁盘存取原理有关。
局部性原理与磁盘预读
由于磁盘的存取速度与内存之间鸿沟,为了提高效率,要尽量减少磁盘I/O.磁盘往往不是严格按需读取，而是每次都会预读,磁盘读取完需要的数据,会顺序向后读一定长度的数据放入内存。而这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用
程序运行期间所需要的数据通常比较集中

由于磁盘顺序读取的效率很高(不需要寻道时间，只需很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率.预读的长度一般为页(page)的整倍数
MySQL(默认使用InnoDB引擎),将记录按照页的方式进行管理,每页大小默认为16K(这个值可以修改).linux 默认页大小为4K

B-Tree树每次新建节点时，直接新建一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配是按页对齐的，就实现了一个结点只需一次IO，逻辑上相近的结点，物理地址也接近；
而红黑树这种结构，逻辑上相近的结点物理上可能很远，无法利用局部性。

为什么使用B+树？

1. B+树更适合外部存储，由于内节点无data，一个节点可以存储更多的内节点，每个节点能索引的范围更大，同样大小的磁盘页可以容纳更多的节点元素，因此查询时IO会更少
2. B-树想要范围查询只能依靠繁琐的中序遍历，而B+树只需要在链表上遍历即可，B+树范围查询方便
3. B+树的查询必须是最终找到叶子节点，而B-树只需要找到匹配的元素，无论匹配元素是中间节点还是叶子节点，因此B-树的查找性能不稳定

哈希索引

• Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到Hash索引
• 对于Hash索引中的所有行，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码

Hash索引的限制

• Hash查找数据需要进行两次读取，因为Hash索引中保存的是哈希码，必须先通过哈希码找到对应的行，再对行的记录进行读取
• Hash索引无法用于排序，由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系不一定和Hash运算前的键值一样，所以数据库无法利用哈希索引的数据进行任何排序
• Hash索引不支持部分索引查找，只能进行全值匹配，对于组合索引，Hash索引在计算哈希码的时候是组合索引键合并后再在一起计算Hash值，而不是单独计算Hash值，所以没办法进行部分索引
• Hash索引不支持范围查找，如果键值不是唯一的，就需要先找到该键所在的位置，然后再根据链表往后扫描，直到找到相应的数据
• 哈希索引存在哈希冲突(不适合用在键值对重复值很多的字段，比如性别)

不同引擎下索引的实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址

MyISAM的索引文件仅仅记录数据记录的地址，在MyISAM中，主索引和辅助索引在结构上没有任何差别，只是主索引要求key是唯一的
因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
ps：(主索引和辅助索引都是B+树，叶子节点都存储的是数据记录的地址，索引文件和数据文件是分离的，主索引和辅助索引都不会影响数据文件)

Innodb索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶结点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶结点包含了完整的数据记录(从上往下代表(字段1、字段2、字段3：15，34，Bob)），这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是 InnoDB的辅助索引data域存储相应记录主键的值而不是地址
换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段(如UUID或者字符类型)作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择