数据库索引原理（深入浅出的学习）

通过以上几篇别人的学习经验，经过自己的消化和总结出一些自己的东西。作为自己的学习笔记。

以下将会从MySQL中的MyISAM 和InnoDB两种存储引擎讨论它们的区别，以及两种不同的存储引擎的底层索引原理

MyISAM的存储文件分为 结构（***.frm）、数据（***.MYD）、索引（***.MYI） 三个存储文件

不支持事务和外键，对表的操作是以表级锁。只要是在具体操作表时，都是锁住整张表，所以并发性较InnoDB稍低。

但同时MyISAM又是Indexed Sequential Access Method (有索引的顺序访问方法) ，所以在执行一些简单的select操作的时候，速度会较InnoDB稍快。

总结：大量的单一查询操作、不常对表数据进行更新和写入。可优先考虑MyISAM存储引擎

InnoDB的存储文件分为：结构（***.frm）、数据索引文件（***.ibd） 两个存储文件

同MyISAM存储引擎不同的是，InnoDB是把索引文件和数据文件归并为同一文件。这是由于InnoDB的底层索引的原因（下文会讨论到）

InnoDB是支持事务、外键以及行级锁的。 因为具有行级锁的特性，所以相同情况下操作数据库，InnoDB的并发性会稍高于MyISAM，更加适合高并发下的大量的写入操作。

总结：InnoDB支持事务、外键以及行级锁，适合需要大量的写入操作。并发性较MyISAM稍高

说到索引，目前大多数主流索引的方式都为：BTree 、 B+-Tree 、 哈希索引 、全文索引 等等

今天主要讨论的MyISAM 和 InnoDB 索引的数据结构都是B+Tree，而哈希索引主要是用于内存存储引擎（Memory）。其他的索引的数据结构本次不过多讨论。

并且，需要了解为什么要使用B+Tree作为索引，而不用红黑树等其他数据结构还需要了解主存读取原理、磁盘读取原理等相关知识。由于上文学习链接中已经讲解的很好了，这里不做过多阐释。想了解的同学可以直接查看第二个学习链接（深）

这里以个人理解的角度做一个简单快速的总结概括：

主存（RAM）读取是快速的，不会因为“距离” （物理地址的间距）产生时间损耗。 主存读取的速度主要是受读取次数的影响，所以这并不是选择B+Tree作为索引的主要条件。

而磁盘（ROM）读取是需要寻道时间（改变磁头、转动磁盘等），会存在“距离”的影响。并且，一般情况下索引文件都是放在磁盘上，所以更多的是和磁盘打交道。所以数据库系统的设计者就很巧妙的将B+Tree的每一个节点设计为一个页的大小（4k左右）这样再根据计算机中著名的局部性原理，就可以减少因为缺页中断引起的寻道时间增加的问题（这部分主要是操作系统课程知识）

在上文中说到，MyISAM存储引擎的表是有三个文件的。其中数据文件和索引文件是分开的。 索引文件中的索引是以B+Tree数的形式存储。

树中有(key,data)字段，非叶子节点的data存放的是指向下一层节点的指针，叶子节点的data域存放的是真实记录的物理地址指针(记录在磁盘中的物理地址)。而key存放的是索引字段的值。

MyISAM的主键索引非聚集索引，它的所有辅助索引也是非聚集索引。*它们所有的叶子节点的data域存放的都是数据的物理地址。

（图来源都为上文的学习链接中）

而InnoDB是索引文件和数据文件归并在一起。因为InnoDB是在表创建的同时，就会通过主键创建B+Tree的聚集索引结构。 每个叶子节点里存放的并不是数据的物理地址，而是真实数据记录。

所以InnoDB通过主键索引的速度会很快

但同时，索引和数据归并在一起的文件所占据的存储空间也更为的庞大。在对表进行写操作的同时，也要耗费更多的资源去维护索引文件（B+Tree的结构）

InnoDB的辅助索引同MyISAM的辅助索引一样，都是非聚集索引。 但是InnoDB的辅助索引中，叶子节点的data域存放的并不是数据的物理地址，而是存放当前索引所对应的记录主键的值。

InnoDB的辅助索引查找过程分为两步：

1. 通过辅助索引找到主键的值
2. 再根据主键的聚集索引找到真实的数据记录

这是在索引都是单列存在的情况（只有一个字段作为辅助索引），当出现联合索引的时候，非聚集索引就会变为覆盖索引。在下文中将会和进行讨论。

需额外注意：在使用InnoDB存储引擎的时候，在可能的情况下，尽量选择一个与 业务无关，自动增长的字段作为主键（通常情况下的id）。避免选择如：学号、身份证号这种字段作为主键。

这是由于InnoDB的索引结构B+Tree在表创建时，会自动根据主键创建一颗索引树。（如果没有显示的给出主键，MySQL的内部机制会自己制定一个主键）

这颗B+Tree的Key就是主键的值，这就要求主键的长度尽量短，这样在索引的时候速度才能更快。

而由于B+Tree的特性，如果选择一个自动增长的主键，那么在添加的新记录的时候，就只要需要在最底层的叶子节点排列的最末尾增加节点，不需要任何移动节点（有点类似于单链表，在链表的尾部增加节点效率是最高的。而如果在中间插入一个新的节点，就需要移动节点，效率较差。 当然，这里的B+Tree可能还需要重新排序，逻辑更为复杂。）

原理图：（图来源都为上文的学习链接中）

上面的MyISAM是非聚集索引，而InnoDB是聚集索引。区别在于一个存储的是数据的物理地址，一个存储的是真实的数据。 而覆盖索引是在复合索引（多个字段联合构成的索引）中出现。

覆盖索引的原理是，你想查找一个字段的信息，索引中key的值就是你所需要的信息。那么就不必在通过data域中的物理地址或主键进行二次索引遍历另一颗索引树了。

原理图如下：（图来源都为上文的学习链接中）

这是非覆盖索引的情况：

这是覆盖索引的情况：

而联合索引中又包含最左前缀原理

最左前缀是在联合索引的时候出现的。当多个字段构成联合索引时，如：。如果查询条件为 where col1 = 20；则可以使用该联合索引。 如果为 where col2 = 20 则不能使用该联合索引

这与MySQL中的内部机制有关，如果要使用联合所用，必须是从联合索引字段的最左侧的第一个字段，这也是构建联合索引需要注意的（字段的选取，应按字段使用的频率从高到底排序）

还有一小技巧，如用到col1 和 col3的联合条件查询时，这是不能使用联合索引的。因为中间出现了一个“坑”（缺少col2）。那么在col2字段值的选择性较少的情况下，可通过把col2的所有字段可能取值通过 IN 包含进来。但是在字段值的选择性很多情况下，这种情况就不适用。可能就需要重新构建一个的联合索引。

1. 表中记录数很多的情况下 （一般以2000为分界线，2000以下不必构建索引，因为遍历整张表的速度并不会比索引慢多少）

2.字段的选择性较大的情况下（如果一个字段的选择性很低的情况下，如：性别，就不必建立索引了，因为即使构建了索引，效率一样不是很高，这是由于B+Tree的原理。 而如果索引的选择性很大，如：真实姓名 那么使用索引的查询效率就很有很大的提升）

以上就是MyISAM 和 InnoDB 存储引擎的区别（包括索引原理的区别）的学习总结了，还需要额外涉及到：数据结构、操作系统等课程的知识。

接下来有时间，会重新详细的记录一下B+Tree 等等 数据结构的知识

还处于学习阶段，如果有错误，希望大家可以指正