MySQL的索引及其数据结构

索引基本概念

索引：索引是一种按照关系表中指定列的取值顺序组织元组数据存储的数据结构，使用它可以加快表中数据的查询访问。其功能就像是一个目录。
索引作用及特点：支持对数据表中数据快速查找，其机理类似图像目录可以快速定位章节内容。索引优点为提高数据检索速度，快速连接关联表。索引开销主要是创建和维护都需要较大开销，索引会占用额外存储空间，数据操纵因维护索引带来系统性能开销。
索引的结构：主要有B+树、B树和哈希表索引
* B+ 树是一颗多叉树，叶子节点存放的是数据记录，非叶子节点存放的是索引。
* B树也是一颗多叉树，每个节点存放索引和数据记录；
* 哈希索引底层使用的是哈希表，将索引作为key，将数据记录作为value。

B树

首先，数据库索引使用树结构存储主要有以下几点：

• 树的查询效率高
• 保持有序
• 自平衡

为什么不用二叉查找树：数据库索引是存储在磁盘上的，当数据量较大时，索引的大小可能有几个G甚至更多。当我们利用索引查询时，只能逐一加载每一个磁盘页，这里的磁盘页对应索引树的节点。如果我们利用二叉查找树作为索引结构时，索引次数由索引树的高度决定。为了减少磁盘IO次数，就需要把原本“瘦高”的树结构变得“矮胖”。
什么是磁盘IO：磁盘IO时的过程：
第一步，首先是磁头径向移动来寻找数据所在的磁道。这部分时间叫寻道时间。
第二步，找到目标磁道后通过盘面旋转，将目标扇区移动到磁头的正下方。
第三步，向目标扇区读取或者写入数据。到此为止，一次磁盘IO完成。
故：单次磁盘IO时间 = 寻道时间 + 旋转延迟 + 存取时间。

B树是一种多路平衡查找树，它的每一个节点最多包含k个孩子，k被称为B树的阶，k的大小取决于磁盘页的大小。即，一个节点的子节点数目的最大值。
一个m阶的B树具备的特征

• 1、根节点至少有两个子女；
• 2、每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <=k <= m
• 3、每一个叶子节点都包含k-1个元素，其中m/2 <= k <= m （？）
• 4、所有的叶子节点都位于同一层；
• 5、每个节点的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域划分。

查询

以三阶B树为例

如(2，6) 节点，该节点有两个元素2和6，有三个孩子1，（3，5），8.其中1小于元素2，（3，5）正好在元素2，6之间，8大于（3，5），符合以上几条特征。

如查询数值5：第一次磁盘IO在内存中定位（和9比较）；第二次磁盘IO，在内存中定位（和2，6比较）；第三次磁盘IO，在内存中定位（和3，5比较）。

B树在查询中的比较次数其实不比二叉查找树少，尤其当单一节点的元素数量较多时，但是相比磁盘IO的速度，内存中的比较耗时是可以忽略的。所以只要树的高度足够低，IO次数足够少，就可以提高查找性能。相比之下，节点内部元素多一些也没有关系，只是多了几次内存交互==（？）==，只要不超过磁盘页的大小即可。

插入

如，插入的数值为4.
自顶向下查找4的节点位置，发现4应当插入到节点3，5之间。

节点3，5已经是两元素节点（第3点），无法再增加，父亲节点2，6也是两元素节点，也无法再增加，根节点9是单元素节点，可以升级为两元素节点，于是拆分节点3，5与节点2，6，让根节点9升级为两元素节点4，9.节点6独立为根节点的第二个孩子。

删除

如，删除元素11
自顶向下查找元素11的节点位置

删除11后，节点12只有一个孩子，不符合B树规范，因此找出12，13，15三个节点的中位数，取代节点12，而节点12自身下移称为第一个孩子（这个过程称为左旋）

B树主要应用在文件系统以及部分数据库索引。

B+树

B+树是基于B树的一种变体，有着比B树更高的查询性能
一个m阶的B+树具有如下特征

• 1、有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点；
• 2、所有的叶子节点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子节点本身依关键字的大小自小而大顺序链接；
• 3、所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

如三阶的B+树

首先，每一个父节点的元素都出现在子节点中，是子节点中的最大或最小元素；根节点元素8是子节点2，5，8的最大元素，也是叶子节点6，8的最大元素。同理根节点15.注意，根节点的最大元素等同于整个B+树的最大元素，始终要保持最大元素在根节点当中。而叶子节点，由于父节点的元素都出现在子节点，因此所有叶子节点包含了全部元素信息。并且每一个叶子节点都带有指向下一个节点的指针，形成了一个有序链表。

卫星数据是指索引元素所指向的数据记录，比如数据库中的某一行，在B树中，无论中间节点还是叶子节点都带有卫星数据。
B树中的卫星数据

B+树中的卫星数据

在B+树中，只有叶子节点带有卫星数据，其余中间节点仅仅是索引，没有任何数据关联。需要补充的是，在数据库的聚集索引中，叶子节点直接包含卫星数据，在非聚集索引中，叶子节点带有指向卫星数据的指针。

查询

如查询元素3;
第一次磁盘IO：

第二次磁盘IO：

第三次磁盘IO：

B树与B+树的对比

• 由于B+树的中间节点没有卫星数据，所以同样大小的磁盘页可以容纳更多的节点元素，即，在数据量相同的情况下，B+树的结构比B树更加矮胖，，因此查询时IO次数也更少；
• 其次，B+树的查询必须最终查找到叶子节点，而B树只要找到匹配元素即可，无论是中间节点还是叶子节点。因此B树的查找性能不稳定，而B+树的每次查找都是稳定的；
• B树的范围查找过程只能采用繁琐的中序遍历，而B+树的范围查找只需在链表上做遍历，范围查询简便。

哈希索引

hash索引是基于哈希表实现的，只有精准匹配索引所有列的查询才有效。查询速度相对于B树索引要快。
哈希索引的特点

• hash索引中只有hash值和行数得指针，因此无法直接使用索引来避免读取行，但是因为这种索引读取快，性能影响不明显；
• hash索引不是按照索引值顺序存储，无法使用于排序；
• 不支持部分列匹配查找，这里使用索引列得全部内容来计算哈希值，例如（A,B）两列一起建索引，单纯使用A一列无法使用索引，B-Tree索引的话，因为支持匹配最左前缀，所以这种情况适用性偏好；
• 哈希索引只支持等值查询，包括= in() <=> 不支持where age > 10这种范围查询；
• 哈希冲突很多的话，维护索引操作的代价也很高。

聚集索引与非聚集索引

聚集索引

聚集索引是指数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。类似于汉语字典的拼音索引。

注：第一列的地址表示该行数据在磁盘中的物理地址，后面三列是SQL里表的列，其中id是主键，建立了聚集索引。

聚集索引的叶子节点就是对应的数据节点，可以直接获取到对应的全部列的数据，而非聚集索引在索引没有覆盖到对应的列时需要进行二次查询，因此在查询上，聚集索引的速度往往更占优势。

非聚集索引

非聚集索引是指该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。类似于汉语字典的部首+检字表型的索引。
非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块。如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么还需要进行第二次查询，查询节点上对应的数据列的数据。

其中有聚集索引id，非聚集索引username，使用以下语句查询时不需要进行二次查询就可以直接从非聚集索引的节点里面获取到查询列的数据。

select id, username from t1 where username = '小明'
 
select username from t1 where username = '小明'

但是使用以下语句进行查询时，就需要二次查询去获取原数据行的score：

select username, score from t1 where username = '小明'

小结

• 聚集索引的约束是唯一性，而字段却不要求唯一。一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。所以聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。
• 是不是聚集索引就一定要比非聚集索引性能优呢?
  　　如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？

答：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引，此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好。

索引创建SQL语句
语句基本格式：create index <索引名> on <表名><(列名)>
如：在学生信息表Student中，为出生日期Birthday列创建索引，以便支持按出生日期快速查询学生信息。
create index Birthday_Idx on student (Birthday);

索引修改SQL语句
语句基本格式：alter index <索引名> <修改项>;
如：在学生信息表Student中，将原索引Birthday_Idx更名为Bday_Idx，其索引修改SQL语句如下：alter index Birthday_Idx rename to Bday_Idx;

索引删除SQL语句
语句基本格式：drop index <索引名>;
如：在学生信息表Student中，删除Bday_Idx索引，其索引删除SQL语句如下：drop index Bday_Idx;