数据库索引详解

mysql索引详解（1）–B/B+索引，聚集和非聚集索引

rwangnqian 2019-03-11 16:45:52 941 收藏 3
分类专栏： mysql
版权
一、索引介绍
1.什么是索引？

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

2.为什么要有索引呢？

索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。可以理解成为排好序的快速查找数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要（排序和查找）。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

二、索引的原理
    索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

为了加快col2的查找，可以维护一个上面所示意的二叉查找树，每个节点分别包含索引键值和一个指向对应数据物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速检索出符合条件的记录。

二叉查找
要了解索引，必须了解用于查找数据的二叉查找算法：

二叉树只能查找已经排好序的数据。二分查找通过比较数组中间的数据与目标数据的大小，可以得知目标数据是在数组的左边还是右边。因此比较一次就可以把查找范围缩小一半。重复执行该操作就可以找到目标数据。

但是长期的插入和删除可能会导致二叉树变成线性结构。实际使用的二叉搜索树都是在原有的二叉搜索树的基础上加上平衡算法，即“平衡二叉树”。节点分布均匀的平衡算法是平衡二叉树的关键，平衡算法就是一种在二叉搜索树中插入和删除节点的策略。

B树和B+树
B-tree
B树是一种多路搜索树,节点的关键字和可以拥有的子节点数都有限制。

树形结构：由根节(root)、分支(branches)、叶(leaves)三级节点组成，其中分支节点可以有多层。

（阶的定义就是一个节点的子节点数目的最大值）

1.假设一个M阶的B-tree，那么非叶子节点也就是根节点的子节点数就是[2,M]；

2.根节点中的关键字的个数为[1,m-1];

3.非根节点及叶子节点的节点，至少有[m/2]个子节点（m/2数值向上取整）；

4.非根节点及叶子节点的节点，其关键字个数为[m/2-1,m-1]（m/2-1中数值向下取整）；

4.在B-树中，每个结点中关键字从小到大排列，并且当该结点的孩子是非叶子结点时，该k-1个关键字正好是k个孩子包含的关键字的值域的分划。

如果是表格中其他数据：

每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。

模拟查找关键字29的过程：

根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】
比较关键字29在区间（17,35），找到磁盘块1的指针P2。
根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】
比较关键字29在区间（26,30），找到磁盘块3的指针P2。
根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。
分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。

B+Tree:
与B-Tree相比，B+Tree有以下不同点：

1.非叶子节点不存储data，只存储索引key；只有叶子节点才存储data。

2.所有叶子节点之间都有一个链指针；

3.数据记录都存放在叶子节点中。

结构如下图：

Mysql中B+Tree：在经典B+Tree的基础上进行了优化，增加了顺序访问指针。在B+Tree的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+Tree。这样就提高了区间访问性能：如果要查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率(无需返回上层父节点重复遍历查找减少IO操作)。

为何mysql选取B+索引：
       结合操作系统存储结构优化处理： mysql巧妙运用操作系统存储结构(一个节点分配到一个存储页中->尽量减少IO次数) & 磁盘预读(缓存预读->加速预读马上要用到的数据).

1.B+Tree 单个节点能放多个子节点，相同IO次数，检索出更多信息。

2.B+TREE 只在叶子节点存储数据 & 所有叶子结点包含一个链指针 & 其他内层非叶子节点只存储索引数据。只利用索引快速定位数据索引范围，先定位索引再通过索引高效快速定位数据。

详解：Mysql设计利用了磁盘预读原理，将一个B+Tree节点大小设为一个页大小，在新建节点时直接申请一个页的空间，这样就能保证一个节点物理上存储在一个页里，加之计算机存储分配都是按页对齐的，这样就实现了每个Node节点只需要一次I/O操作。

聚集索引和非聚集索引：

下面是MySQL文档中关于索引的说明：文档说明

每个InnoDB表有一个特殊的指数称为聚集索引所在的行的数据存储。通常，聚集索引是主键的同义词。从查询，插入性能最好，和其他的数据库操作，必须了解InnoDB使用聚集索引来优化每个表最常见的查询和DML操作。
     当你定义你的表的主键，InnoDB使用它作为聚集索引。为您创建的每个表定义一个主键。如果没有逻辑唯一的和非空的列或列集，添加一个新的自动增量列，它的值自动填充。
     如果你不确定你的表的主键、唯一索引，MySQL定位第一所有键列不为空，InnoDB使用它作为聚集索引。
如果表没有主键或唯一索引InnoDB内部适用，生成一个隐藏的聚集索引为合成列包含行ID值gen_clust_index。行的ID，InnoDB分配在这样一个表中的行排序。行ID是一个6字节字段的单调增加，在插入新行。因此，行id命令的行在物理上是插入顺序。

数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据；辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键。

聚集索引和非聚集索引（辅助索引）的区别：

1.聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

2.  聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，只要找到第一个索引值记录，其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢，因为为了保证表中记录的物理和索引顺序一致，在记录插入的时候，会对数据页重新排序。

3.非聚集索引制定了表中记录的逻辑顺序，但是记录的物理和索引不一定一致，两种索引都采用B+树结构，非聚集索引的叶子层并不和实际数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多，不会造成数据重排。

1. MyISAM索引实现：
   1）主键索引：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引文件仅仅保存数据记录的地址。(底层存储结构： frm -表定义、 myi -myisam索引、 myd-myisam数据）

2）辅助索引（Secondary key）

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

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

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

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

1）主键索引：

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

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

2）. InnoDB的辅助索引

和聚集索引相比，辅助索引中叶子节点中并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点的索引行还包含了一个书签（bookmark），该书签用来告诉InnoDB哪里可以找到与索引相对应的行数据。（也就是辅助索引中的叶子数据data）

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

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

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

InnoDB索引和MyISAM索引的区别：

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

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

索引的优劣
索引主要分为四种：普通索引、主键、唯一索引、复合索引。这边文章只是介绍了主键索引和普通索引的原理。

优点：
1.可以大大加快数据的检索速度，降低了IO成本。
2.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
3.通过索引列对数据进行排序，减少查询中分组和排序的时间。降低了CPU的消耗。
4.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

缺点：
1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
2.索引实际上也是一张表，保存了主键与索引字段并指向实体表记录。所以索引也需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。
3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度
————————————————
版权声明：本文为CSDN博主「rwangnqian」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/rwangnqian/article/details/87995011