MySQL索引

定义

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。 正确的创建 合适的索引是提升数据库查询性能的 基础。

索引的作用

1、索引能极大的减少存储引擎需要扫描的数据量；

2、索引可以把随机IO变成顺序IO（覆盖索引）；

3、索引可以帮助我们在进行分组、排序等操作时，避免使用临时表；

MySQL为什么使用B+tree作为索引的结构

二叉查找树(Binary search tree)

二叉查找树会有存在不平衡的情况导致查找效率不稳定，极端情况会成为链表的形式。二叉树结构图如下：

平衡二叉查找树(Balanced binary search tree)

平衡二叉查找树的每个节点包含关键字、数据区、节点引用三部分；每个节点都有卫星数据。

缺点：

1、相同数据量时树的深度太深：数据处的(高)深度决定着他的IO操作次数，IO操作耗时大；

2、磁盘块的数据量太小：每个磁盘块(节点/页)保存的数据量太小，没有很好利用磁盘IO的预读能力(空间局部性原理)，从而带来频繁的IO操作；

空间局部性原理：一个数据被用到，通常它附近的数据也会被用到。正是由于这个原理操作系统在读取数据的时候才会最少读4K大小的数据。

多路平衡树(B-Tree/BTree)

多路平衡树的每个节点 包含n个关键字和n+1个节点引用。 每个节点也有卫星数据。 多路平衡树和平衡二叉查找树的区别就是度(路数)更多、关键字更多。

加强版多路平衡查找树(B+Tree)

B+Tree的每个 非叶子节点不 包含卫星数据 。 上一级的关键字是下一级关键字中的最大或最小关键字 。 B+Tree 采用左闭合区间 。

B-Tree与B+Tree的区别

1、B+节点关键字搜索采用闭合区间；

2、B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用；

3、B+关键字对应的数据保存在叶子节点中；

4、B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系；

为什么使用B+Tree

1、B+树是B-树的变种(PLUS版)多路绝对平衡查找树，他拥有B-树的优势，B+树扫库/表能力更强；

2、B+树的磁盘读写能力更强；

3、B+树的排序能力更强，B+树的查询效率更加稳定(仁者见仁、智者见智)；

MySQL B+Tree索引体现形式

MySQL中B+Tree索引体现形式-MyISAM

使用MyISAM存储引擎的表包含三个文件， FRM文件是各个存储引擎都需要的表定义文件， MYI是MyISAM引擎的索引文件，MYD是MyISAM引擎的数据文件。结构如下图：

MyISAM引擎的主键索引和其他索引在存储形式上没什么差别， 叶子节点的卫星数据都是数据文件里数据的地址引用。多个索引结构图如下：

MySQL中B+Tree索引体现形式-MyISAM

Innodb存储引擎 以主键为索引来组织数据的存储。如果不存在主键索引则使用唯一索引，如果也不存在唯一索引则隐式生成一个6Byte的Int作为主键来组织数据多存储。主键索引结构图如下：

Innodb存储引擎存在多个索引时， 辅助索引的卫星数据是主键，通过辅助索引搜索数据时需要先在辅助索引的B+Tree中定位到卫星数据，然后再根据卫星数据(主键)去主键索引的B+Tree中找到卫星数据（存储的真实数据）。

两种存储引擎查找数据过程

通过两种存储引擎查找数据的过程如下图，左边是Innodb存储引擎，右边是MyISAM存储引擎：

索引知识补充

列的离散性

选择创建索引的列时，列的 离散性越高选择性越好。在B+Tree结构中，每个节点都是多路的，离散性大的列在查找时可走的路会更少，更快的定位到数据。

最左匹配原则

对索引中关键字进行计算(对比)，一定是从左往右依次进行， 且不可跳过。所以 建立联合(复合)索引时应保证列小且离散性大的列放在联合索引的左面。

联合索引

单列索引是特殊的联合索引。

例子：

    单列索引：节点中关键字[name]

    联合索引：节点中关键字[name,phoneNum]

说明：这两个索引没必要同时存在，只建立上面的联合索引就够了。

联合索引列选择原则

1、经常用的列优先 【最左匹配原则】；

2、选择性(离散度)高的列优先【离散度高原则】；

3、宽度小的列优先【最少空间原则】；

覆盖索引

如果查询列可通过索引节点中的关键字直接返回，则该索引称之为覆盖索引。 覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能。