三、MySQL索引与算法

1. 知识准备

一般而言，较为常用的存储引擎目前为InnoDB和MyISAM，故本节主要针对这两种存储引擎展开分析。在此之前，了解如下几个知识点，将会有助于理解MySQL底层存储原理。

1.1 相关理论

• 主存存取原理
  当系统需要读取主存时，则将地址信号放到地址总线上传给主存，主存读到地址信号后，解析信号并定位到指定存储单元，然后将此存储单元数据放到数据总线上，供其它部件读取。
  写主存的过程类似，系统将要写入单元地址和数据分别放在地址总线和数据总线上，主存读取两个总线的内容，做相应的写操作。
  主存存取的时间仅与存取次数呈线性关系，因为不存在机械操作，两次存取的数据的“距离”不会对时间有任何影响。

• 磁盘存取原理
  与主存不同，磁盘I/O存在机械运动耗费，因此磁盘I/O的时间消耗是巨大的。当需要从磁盘读取数据时，系统会将数据逻辑地址传给磁盘，磁盘的控制电路按照寻址逻辑将逻辑地址翻译成物理地址，即确定要读的数据在哪个磁道，哪个扇区。为了读取这个扇区的数据，需要将磁头放到这个扇区上方，为了实现这一点，磁头需要移动对准相应磁道，这个过程叫做寻道，所耗费时间叫做寻道时间，然后磁盘旋转将目标扇区旋转到磁头下，这个过程耗费的时间叫做旋转时间。

• 局部性原理与磁盘预读
  由于存储介质的特性，磁盘本身存取速度比主存慢很多，再加上机械运动耗费，往往是主存的几百分之一。磁盘往往不是严格地按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。
  这样做的理论依据是计算机科学中著名的局部性原理。预读的长度一般为页（Page）的整数倍。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储分割为连续的大小相等的块。每个存储块称为一页（在许多操作系统中，页的大小通常为 4k），主存和磁盘以页为单位交换数据，当程序要读取的数据不在主存中时，会触发一个缺页异常。此时系统会向磁盘发出读盘信息，磁盘会找到数据的起始位置并向后连续读取一页或几页的数据载入内存中，然后异常返回，程序继续运行。

1.2 相关数据结构

• B-Tree(多路平衡查找树)
  

一棵m阶的B-Tree有如下特性：
- 每个节点最多有m个孩子
- 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子
- 若根节点不是叶子节点，则至少有2个孩子
- 所有叶子节点都在同一层，且不包含其它关键字信息
- 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
- 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
- ki(i=1,…n)为关键字，且关键字升序排序。
- Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)

• B+Tree
  B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构。
  
  B+Tree和B-Tree的区别：B-Tree每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null，叶子结点不包含任何关键字信息。而B+Tree所有的叶子结点中包含了全部关键字的信息，非叶子节点只存储键值信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有叶子节点之间都有一个链指针。

1.3 二分查找算法

二分查找也称为折半查找，它是一种效率较高的查找方法。这种方法充分利用了元素间的次序关系，采用分治策略。
基本原理是：首先找到中值，将要查找的目标与中值进行比较，如果目标小于中值，则在前半部分找，如果目标小于中值，则在后半部分找；假设在前半部分找，则再与前半部分的中值相比较，如果小于中值，则在中值的前半部分找，如果大于中值，则在后半部分找。以此类推，直到找到目标为止。

由于B-Tree的特性，在B-Tree中按key检索数据的过程：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。

2. MySQL索引

索引是一种用来实现 MySQL 高效获取数据的数据结构。目前大多数数据库系统及文件系统都采用 B-Tree 或其变种 B+Tree 结构。B+Tree是由二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree）逐步优化而来。 InnoDB 和 MyISAM 都是用 B+Tree 来存储数据。

为什么使用B+Tree？
a.尽量减少查找过程中磁盘I/O的存取次数。 更好利用局部性原理与磁盘预读。
b.数据都在叶子节点且叶子节点之间都是链表的结构，所以每次查询的时间复杂度是固定的，稳定性得到保证了，同时可以支持范围查询。

2.1 MyISAM索引结构

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

MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引叫做“非聚集索引”。

2.2 InnoDB索引结构

InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM不同。

MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这种索引叫做“聚集索引”。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。InnoDB的辅助索引data域存储相应记录主键的值而不是地址。

上一篇：二、MySQL底层存储结构