数据库索引

为什么要有索引？？

索引，就是帮助我们快速获取访问数据库中表的信息。比如一本没有目录的书，和有一本有目录的书，自己想想吧。

怎么实现的呢？ 在一个没有结构无序的数据中查找（没有数据结构），怎么建立索引？？？—— 只有扫描整个表

当然，建立索引肯定是基于某种数据结构上的，我们每次更新，删除，插入，都要去维护这个数据结构，才能帮助我们快速访问数据库中表的信息。就拿上面目录的列子，你在书中改了某些内容，不更新目录怎么行？？

所以：索引的实现通常使用B树及其变种B+树

索引的优点

• 加快数据检索速度，这也是创建索引的主要原因
• 创建唯一性索引，保证数据表中的每一行数据的唯一性
• 加速表和表之间的连接，？？？
• 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
• 通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能。？？

索引的缺点

全是好处，那不岂不是每个表都来一个索引？？反过来想一下，建立索引不需要维护和额外的空间吗？

• 创建索引和维护索引要耗费时间，这种时间随着数据量增加而增加
• 索引需要占物理空间（磁盘嘛），总的存储吧，不可能存在内存中吧？每个一个索引都要占一定的物理空间，如果是聚簇索引，需要的空间更多
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度

有哪些索引，怎么分类？

普通索引和唯一性索引

• 普通索引： CREATE INDEX mycolumn_index(索引名称) ON table (table_column);
• 唯一性索引：CREATE UNIQUE INDEX mycolumn_index(索引名称) ON table (table_column); 保证在索引中的全部数据都是唯一的，对聚簇索引和非聚簇索引都可以使用。
• 注意唯一性索引与主键索引的区别；

主键就是唯一索引，但是唯一索引不一定是主键，唯一索引可以为空，但是空值只能有一个，主键不能为空。

普通唯一索引：单个字段上建立唯一索引，需要此字段所在的列上不能有重复的值，属于二级索引。

复合唯一索引：多个字段上联合建立唯一索引，属于二级索引。

单个索引和组合索引（联合索引，复合索引）

• 单个索引：就是只针对一列。
• 组合索引：又叫联合索引，复合索引。就是在多个列一起组合而成的索引，最大16个字段。 CREATE INDEX mycolumn_index(索引名称) ON table (table_column1,table_column2,table_column3…)

聚簇索引和非聚簇索引

对于mysql存储引擎来说，InnoDB 主要使用的是聚簇索引，MyISAM 不管是主键索引还是二级索引都是使用的非聚簇索引。

• 聚簇索引：存储数据的顺序和索引顺序一致。一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

  • 创建语句

• 非聚簇索引：记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；

• 数据库中索引的优缺点

• 聚簇索引与非聚簇索引

覆盖索引

通过查询索引就能确定最终的数据，不用再利用叶子节点中存储的主键值去查询对应的数据。
覆盖索引的性能是极高的。

全文索引 / Full Text

所谓全文索引，是一种通过建立倒排索引，快速匹配文档的方式。

Tips：InnoDB引擎对FULLTEXT索引的支持是MySQL5.6新引入的特性，之前只有MyISAM引擎支持FULLTEXT索引。对于FULLTEXT索引的内容可以使用MATCH(column)…AGAINST(val)语法进行查询。

---

mysql 中的存储引擎和索引

在 MySQL 中，主要有四种类型的索引，分别为： B-Tree 索引， Hash 索引， Fulltext 索引和 R-Tree 索引。我们主要分析B-Tree 索引。

一般来说， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的结构来存储的，也就是所有实际需要的数据都存放于 Tree 的 Leaf Node(叶子节点) ，而且到任何一个 Leaf Node 的最短路径的长度都是完全相同的，所以我们大家都称之为 B-Tree 索引。

当然，可能各种数据库（或 MySQL 的各种存储引擎）在存放自己的 B-Tree 索引的时候会对存储结构稍作改造。

如 Innodb 存储引擎的 B-Tree 索引实际使用的存储结构实际上是 B+Tree，也就是在 B-Tree 数据结构的基础上做了很小的改造，在每一个Leaf Node 上面出了存放索引键的相关信息之外，还存储了指向与该 Leaf Node 相邻的后一个 LeafNode 的指针信息（增加了顺序访问指针），这主要是为了加快检索多个相邻 Leaf Node 的效率考虑。

MyISAM和InnoDB两个存储引擎的索引实现方式：

MyISAM索引实现

这也涉及到上文所说的，在MyISAM索引中主要采用的是非聚簇索引，MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图：

• 主键索引（Primary Key）

这里设表一共有三列，假设我们以Col1为主键，是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。

• 二级索引（Secondary Key）

在MyISAM中，主索引和二级索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

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

InnoDB索引实现

InnoDB也使用B+Tree作为索引结构，但其采用聚簇索引的实现方式，索引于MyISAM完全不同

• 主键索引（Primary Key）
  MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

• 这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）。

• 如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

• 二级索引（Secondary Key）

InnoDB的所有二级索引都引用主键作为data域。例如，下图为定义在Col3上的一个二级索引：

InnoDB 表是基于聚簇索引建立的。 因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的二级索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

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

为什么不建议使用过长的字段作为主键？

所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大

为什么建议用单调（递增或者递减）的字段作为主键。或者说为什么要用一般建议用自增ID

因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

InnoDB索引和MyISAM索引的区别：

• 一是主索引的区别，InnoDB的数据文件本身就是索引文件。而MyISAM的索引和数据是分开的。

• 二是辅助索引的区别：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。而MyISAM的辅助索引和主索引没有多大区别。

为什么使用b-tree/b+tree做为索引的数据结构

平衡树（balance Tree）还有红黑树等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构。

  一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。为什么使用B-/+Tree，还跟磁盘存取原理有关。

局部性原理与磁盘预读

  磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：

• 当一个数据被用到时，其附近的数据也通常会马上被使用。
• 程序运行期间所需要的数据通常比较集中。
• 由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。

  预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。
  当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

我们上面分析B-/+Tree检索一次最多需要访问节点：

$h=lo{g}_{\frac{m}{2}}(\frac{n+1}{2})+1$

  数据库系统巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B- Tree还需要使用如下技巧：

• 每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

• B-Tree中一次检索最多需要h-1次I/O（根节点常驻内存），渐进复杂度为O（h）=O（$lo{g}_{m}N$）。一般实际应用中，m是非常大的数字，通常超过100，因此h非常小（通常不超过3）。

    综上所述，用B-Tree作为索引结构效率是非常高的。

红黑树这种结构，h明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的I/O渐进复杂度也为O（h），效率明显比B-Tree差很多。

关于B-/+/* Tree 总结

• B-树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
• B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；
• B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；