mysql索引分析

前言

一直想深入的研究一下mysql的索引原理，奈何工作太忙没有时间，最近数据量过大，做了好多sql优化...终于，是时候研究一波了。

索引的本质

聊索引之前，我们得先知道索引是什么？有什么用？目前常用的索引是以何种形式呈现的？

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

我们知道，查询在数据库中经常用到，但是设想，如果没有索引，每次找到一条数据都需要以时间复杂度为O(n)去查找，当数据量少的时候没什么感觉，但是数据量大了以后，这是一个巨大的数字...你的查询简直不可直视。所以，我们需要在数据旁边，建立一些索引，当我们要查某些数据的时候，如果对应字段有索引，我们可以根据索引找到数据的指向，然后去对应地址拿出数据，岂不很快（和小时候新华字典查的时候好像，哈哈哈）？下面是我在网上找的一个图，可以很好的解释我刚刚描述的。左边代表存在disk上的数据，右边代表索引（二叉树的方式）。

可以看到，我们在col2上建立了一个索引，当我们要找col2 = 5 的数据的时候，我们只需要到索引里面找到5，根据他的指针指向，就可以去拿到对应数据了。二叉树的时间复杂度是O(log2n) ,节约了不少时间！

但是真是情况我们会这样做，但是不会使用二叉树去做索引，原因在这篇文章的后面一点介绍。
要想理解索引理解的更加深刻，我觉得需要理解一下计算机的硬盘，内存相关的知识。

内存/硬盘存取原理

RAM，运行时内存。cpu在与内存交互的时候，比如读数据：会通过地址总线把要读取的地址发给RAM，然后RAM读取完数据再将数据放到数据总线，供CPU读取，如下图：

比如地址总线传过来C1，那么数据总线就会给出相应地址的数据“f”.并且内存速度很快，内存IO基本上不会对速度有影响。

DISK，磁盘，也叫外存。我们知道，数据库的数据基本上都是放在磁盘里面。但是磁盘的速度就不像内存那么快了，我们简单的看下磁盘的结构：

如上图：一个磁盘由大小相同且同轴的圆形盘片组成，磁盘可以转动（各个磁盘必须同步转动）。在磁盘的一侧有磁头支架，磁头支架固定了一组磁头，每个磁头负责存取一个磁盘的内容。磁头不能转动，但是可以沿磁盘半径方向运动（实际是斜切向运动），每个磁头同一时刻也必须是同轴的，即从正上方向下看，所有磁头任何时候都是重叠的（不过目前已经有多磁头独立技术，可不受此限制）。

那么定位数据位置是怎样的呢？我们看下面这张图：

盘片被划分成一系列同心环，圆心是盘片中心，每个同心环叫做一个磁道，所有半径相同的磁道组成一个柱面。磁道被沿半径线划分成一个个小的段，每个段叫做一个扇区，每个扇区是磁盘的最小存储单元。为了简单起见，我们下面假设磁盘只有一个盘片和一个磁头。

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

所以，磁盘读取数据耗时 = 寻道时间 + 旋转时间 （很重要！！！说了这么多计算机硬件相关，就是为了这句话...好辛苦...）
基于这个公式，我们发现，如果顺序读取数据，寻道时间可以变小或者不需要，只需要旋转时间该！这就是后面使用B树的核心原因之一。

这里还有一个硬件方面的概念需要解释一下，就是页。
计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。所以计算机读取数据是以页为单位的。也就是说，假设我读取了数据id为1的数据，可能id=2，3的数据也在这个页上，也读取了。

B+树

那么什么是B+树，mysql为什么会使用B+树呢？（B+树懂了再去看B-树会很简单，暂不介绍B-树了）

1.之前介绍过，影响索引速度的瓶颈是IO次数。而检索一次最多要访问h个结点里面的数据，而数据库在设计的时候，将每个结点设置成页的倍数（利用预读），这样，一个结点就只用扫描一次就好

2.我们可以看到，在B+树里面，数据全部存在叶子结点上面。B树的时间复杂度是O(logdN) 其中d是开度。开度越大，那么所花费的时间越短。

MySQL索引实现

mysql索引常见的有MyISAM和InnoDB两个存储引擎，下面分别分析：

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

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

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

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB

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

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

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

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

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

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

好了，本文比较深入的介绍了mysql的索引相关知识，感谢网上相关文档，本文在写的过程中，参考了https://blog.csdn.net/u013967...
https://www.cnblogs.com/aspir...