MySQL索引原理

MySQL索引原理

 

索引的本质

 

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

 

一个索引的例子：

 

图1展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log₂n)的复杂度内获取到相应数据。

 

其中我们会想到二叉树、红黑树，但是索引并没有用他们来实现，为什么呢？

 

 

 

B-Tree和B+Tree

大部分数据库索引都是由 B-Tree 或 B+Tree 来实现，其中MySQL的索引用 B+Tree 实现。

 

B-Tree

1. 由多个节点组成，每个节点中有内节点

2. 每个内节点由key和data组成

3. 两个内节点之间的是指针，指向下一个大的节点

4. 插入和删除会影响树的结构

如下图：

 

 

B+Tree

1. 每个节点的指针上限为2d而不是2d+1

2. 内节点不存储data，只存储key；叶子节点不存储指针

 

由于并不是所有节点都具有相同的域，因此B+Tree中叶节点和内节点一般大小不同。这点与B-Tree不同，虽然B-Tree中不同节点存放的key和指针可能数量不一致，但是每个节点的域和上限是一致的，所以在实现中B-Tree往往对每个节点申请同等大小的空间。

 

一般来说，B+Tree比B-Tree更适合实现外存储索引结构，具体原因与外存储器原理及计算机存取原理有关。

 

 

 带有顺序访问指针的B+Tree（加上顺序访问指针）

 如果查找18~49的数据，只要先找到18，然后根据顺序指针一直查到49就好，效率会明显提高很多。

 

 

B-Tree 和 B+Tree 的比较

 

1. B-Tree，将key和数据放在同一个节点中；B+Tree，非叶子几点只存放key，而数据都放在叶子几点中。

 

2. B-Tree的有些查找比较快，而B+Tree每次查找都要做到叶子节点，看上去好像B-Tree更快，实际不是，因为B+Tree的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-Tree多，树高比B-Tree小，这样带来的好处是减少磁盘访问次数。尽管B+Tree找到一个记录所需的比较次数要比B-Tree多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+Tree的性能可能还会好些。

 

3. B+Tree的叶子节点使用指针连接在一起，方便顺序遍历。

 

 

为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？ 

 

1. B+Tree的磁盘读写代价更低 

B+Tree的非叶子节点没有数据，这样每一层的节点就可以有更多的内部节点，层数更少，读到内存中的内部节点跟多；而相对于B-Tree，每个节点有数据，他每一层的内部节点数据就更少，层数更多，相对于内存操作，这种IO操作会更慢。

 

2. B+Tree的查询效率更加稳定 

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

 

 

MySQL索引实现

根据数据存储引擎的不同，对索引的实现也不一样，主要分为MyISAM、InnoDB。

 

MyISAM索引实现（非聚集）

 

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

 同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

 

 

InnoDB索引实现（聚集）

 

1. 第一个重大区别是InnoDB的就是数据而不是指向物理地址的指针。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

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

 

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

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

 

 

了解索引的原理为我们带来的好处

例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

 

参考：

http://www.uml.org.cn/sjjm/201107145.asp

http://m.blog.csdn.net/article/details?id=51306340