初学MySQL的索引介绍

索引的介绍

索引可以看做成数据的目录，有助于帮助存放着海量数据的数据库进行准确，快速，高效的查找数据。就像是字典的索引目录一样，当我们给数据库添加了索引，查找数据的时候就能像我们查字典的时候一样根据目录去寻找我们要找的信息。 索引对数据库的性能的提高有着很大的作用。

索引的使用场景

要考虑对数据库进行索引的创建就要考虑一下的几种情况

1、数据库的数据是海量的，并且经常需要对这一列进行查询的数据，我们就可以对他创建一个索引。

2、当创建完成数据库之后，我们对该数据库表的插入操作以及对这些列的操作的修改频率都是比较低的。

3、索引会占用额外的磁盘空间。

满足以上的条件，我们的数据库的查询效率就会大大的提高，我们现在来解释一下为什么要遵守以上的条件。

空间的开销

我们的索引的创建是有一定的空间的开销的，就拿一本书来说，内容的页数与目录的页数是呈正相的，内容越多，我们需要目录的页数也就越多，所以，数据库的数据越多，我们创立的索引所需要的开销也就越大，所以索引虽好，也不能贪多，我们只有将最有价值的，最常查询的，数量也比较大的列创立索引，这样我们就可以将查询的时间和索引的创建所消耗的空间进行一个平衡，达到最优的状态。

时间的开销

创建索引会消耗不小的空间，所以我们要选择重要内容进行索引的创建，那么创建索引所需要的时间是多少呢？我们不妨设想一下，一本已经修好的书籍，目录和内容都已经编好，现在由于疏忽，内容少了一章，但是由于目录已经按照事前错误的章节编好，重新加了一章之后目录就需要重新修订了，所要的时间也会大大的增加，也就是说，当数据库中的数据已经录入很多了之后，再进行索引的创建，就会导致索引创建的时间大大的增加，所以我们进行一个数据库的创建，录入信息之前，一定要考虑好哪列需要索引，我们提前进行创建。

安全隐患

在生产过程中，如果我们插入完成数据之后再进行创建索引的操作，由于耗费的时间是根据数据量大小决定的，数据量如果很大，那么索引的创建将是十分的漫长的过程，这个过程会将网络的带宽全部占满，以至于其他的客户端在创建的期间不能访问数据库了。

创建约束的时候自动创建的索引

主键索引

主键索引，索引列中的值必须是不可重复的，唯一的且不能为空。

当数据库中的某一列使用primary key的时候自动创建主键索引。

我们创建一个车辆信息数据库，创建一个车辆所有人表，把所有人的身份识别ID设置成为unique，把车牌号设置成为主键的形式。

create database car_information char set utf8;
use car_information;
create table car_personal (id varchar(20) unique, name varchar(9) , car_id varchar(20) primary key);

创建完成之后，我们通过

show index from car_personal;

来查看表中的索引

 我们可以看到key_name的一行显示有primary key的主键形式，这样我们就创建了一个主键的索引。

唯一索引

唯一索引，索引列中的值必须是不可以重复的，可以是空的。

当数据库中的某一列使用unique约束的时候会自动创建唯一索引。

唯一索引与主键索引是类似的，我们仍然运用主键索引的例子，我们给车辆持有人的ID身份进行了唯一unique的约束，所以参照主键约束会自动创建主键索引的例子，unique创建时也会创建唯一索引。

 我们可以看到key_name的第二行显示id，这就是唯一索引。

外键索引

外键索引，索引的列中使用foreign key的时候自动创建索引。

我们再次创建一张car表，车辆的id使用外键索引。

create table car (id varchar(20) , name varchar(20), foreign key (id) references car_personal(car_id));

根据以上的例子，我们可以得到这样的一个结论，当我们创造主键，唯一，外键约束的时候，我们无需手动创建索引，创建约束的时候会默认的进行约束所对应的索引的创建。

创建普通索引的操作

如果一个列我们不需要他有约束条件，那么就不会自动给我们的这一列创建索引，而这一列又比较重要，查询次数较多的时候，我们就需要进行普通索引的创建。

查询索引

索引的查询我们之前也已经提到过，查看索引的关键字是index。

索引查询的代码语法为：show index from 表名;

当然是已选中数据库的情况下，再把上面的代码搬上进行举例：

use car_information;
show index from car_personal;

创建索引

对于非主键，非唯一，非外键的字段，我们可以创建普通索引

普通索引创建的语法：create index 索引名称 on 表名(列名);

 我们可以看到keyname中除了之前创建的外键索引以外，我们创建的普通索引也位列其中。

删除索引

删除索引的语法与创建索引的语法非常相似：drop index 索引名称 on 表名;

drop index carname on car;

我们可以看到我们之前创建的普通索引已经被我们删除了。

那么我们可不可以删除约束所创建的索引呢？我们来试一试。

根据索引的查询结果，现在car表中还存在一个名为id的外键索引，现在我们要把它进行删除。

drop index id on car;

我们发现出现了错误，这个错误意思是此索引需要被外键所约束，因此不能删除。我们可以得到这样一个结论：我们不能够删除一个由约束所创建的索引，这个索引是和约束共同存在的。

索引中的数据结构

索引有助于在海量的数据中进行快速的查询，那么索引是怎么实现的？索引底层的数据结构是什么？什么样的数据结构能够查找的这么迅速还准确？

Hash

首先我们想到的就是哈希表。

之前的数据结构我们学习过哈希表，哈希表的查找速度是非常迅速的，能够达到O(1)的时间复杂度，但是虽然哈希表查找的时间非常短，但是他不能够进行范围之间的快速查找，如果我们要找10~28，我们还是需要通过散列函数，散列地址，找到哈希表中的位置然后再一一比对才能找到，也就是说如果我们数据库查找是一个

select name from student where age > 13 and age < 19;

 这样的语句，我们的哈希查找就显得力不从心了。

二叉搜索树

说到支持范围查询且查询的速度要快，我们就想到了二叉搜索树，它的查找是折半查找，查找速度也非常的迅速，但是有一个致命的错误，当它内部存储的数据本来就是有序的，从小到大存入二叉搜索树中，就会把二叉搜索树退化成一个链表。

这样二叉搜索树的优势全部丧失了，查找时间退化成为线性查找O(n),显然二叉搜索树也不能胜任数据库的工作。

平衡二叉树

二叉平衡树继承了二叉树的特点，在二叉搜索树的基础上进行了大幅的优化，主要特征是左右的两个子树层数之间相差最多为1，在插入和删除数据的时候进行左旋和右旋的操作，这样就可以保持二叉树的平衡。

平衡二叉树的时间复杂度接近于二分查找，是O(logn),看似是一种非常好用的数据结构，但他也有很多缺点。

数据库中的数据会存储在磁盘之中，每次查询的时候都需要先把数据通过io操作从磁盘中读出来，读到内存之中，然后进行数据之间的比较，磁盘的io操作相对于数据之间在内存中的比较是耗时巨大的，我们要尽量减少磁盘的io操作。

缺点：

1、平衡二叉树每个结点只能分出两叉，如果存储上亿条数据，那么二叉树的层数就会非常的深，而树的层数跟磁盘io读取的次数有关，每层的每个结点都需要一次磁盘io读取，而每次读取的时间是10ms，高度很深就导致读取的时间是非常之高的。当数据量是海量的时候，读取的速度就得好久。

2、平衡二叉树也是不支持范围快速查询的，查询范围的时候要多次返回根节点，查询的效率并不高。

B树

因为二叉树每个结点只有两个分叉，导致树的高度非常高，就导致了磁盘io次数的增多，所以我们对查找时间的优化归根结底是优化磁盘io的次数，因此我们就要对二叉树进行改造，名字就叫做B树。

B树的特点：

1、B树由原来的二叉变为多叉的分支，每一层由原来存储一个节点变成了存储多个结点。

2、B树结点中的元素包含了键值和数据，键值是由小到大分布在B树结点中的，B树中的每个结点都存储着数据。

3、B树的父亲节点的值不在子节点中。

4、叶子节点的高度相同，之间没有指针链接。

B树简图：​​​​​​​

 

当我们等值查找数据19的时候：

1、进行第一次的磁盘io，将磁盘1中的数据加载到内存当中，在内存中把磁盘1中的数据从头到尾进行比较，因为键值都是从小到大进行排列的，所以我们找到的19在23之前，所以根据s1指向的地址我们找到了磁盘2

2、进行第二次磁盘io，将磁盘2中的数据加载到内存当中，在内存中把磁盘2中的数据从头到尾进行比较，因为键值都是从小到大排列的，所以19>13&&19<20，所以根据s2指向的地址找到磁盘6.

3、进行第三次磁盘io，将磁盘6中的数据读入内存，在内存中把磁盘6中的数据从头到尾进行比较，发现19 == 19，我们的等值查找就完成了。

 

B树进行等值查询的时候的性能是优于二叉搜索树和平衡二叉树的，因为此次寻找仅仅需要三次磁盘io就能够找到，而磁盘io是最费时的，所以我们大大减少了磁盘io的次数，也就相当于减少了查询的时间。

那么B树看似完美，其实也有几个缺点，也不能当作索引的数据结构使用。

缺点1：与前面的二叉树搜索树和平衡二叉树相似，如果我们要查询10~34的数据，我们以23为分界分出了不同的枝杈，找完10~23的数据之后我们还要返回根节点，重新进行23~34数据的io操作，这样我们的B树查找的效率又会下降，所以并不适用于范围快速查询。

缺点2：B树的结点是由键值和数据构成的，如果B树的结点保存的不是列的信息而是行的信息，随着列数的增多，结点中存储其他磁盘的地址的空间就会变小，原来能分5个叉，现在就能分两个叉了，由此树的高度就会变高，因此磁盘的io次数就会变大，也会时间也会变的更高。

 B+树

B+树是在B树的基础上改进而来的，与B树有着两个不同的地方。

1、B+树的的非叶子节点只存储键值，叶子节点才会存储数据。

2、叶子节点之间会用双向指针进行链接，形成一个双向链表。

B+树简图

 B+树因为数据都存在叶子节点上，所以每次索引都会一直找到叶子结点，又因为非叶子节点存储的都是键值，所以他的分叉就会很多，理论上来说树的高度要比B树小。

当我们等值查找13的时候。

第一次先对磁盘1进行io操作，读取数据之后进行比较，因为13<15，所以找到s1所指向的磁盘2.

第二次先对磁盘2进行io操作，读取数据之后进行比较，因为13>2&&13<14所以找到s2所指向的磁盘6。

第三次先对磁盘6进行io操作，读取数据之后进行比较，从小到大依次进行比较，比较到13==13的时候，找到了数据，就此返回。

 

 

当我们范围查找 13~32的时候：

如果按照B树的查找方式，我们查找的顺序为磁盘1-磁盘2-磁盘6-磁盘1-磁盘2-磁盘9-磁盘1-磁盘3-磁盘10-磁盘1-磁盘3-磁盘7。

参考下图

 

如果我们按照B+树的查找方法，我们要查找13~32，最小的数据是13，所以先找13.

第一次先对磁盘1进行io操作，读取数据之后进行比较，因为13<15，所以找到s1所指向的磁盘2.

第二次先对磁盘2进行io操作，读取数据之后进行比较，因为13>2&&13<14所以找到s2所指向的磁盘6。

第三次先对磁盘6进行io操作，读取数据之后进行比较，从小到大依次进行比较，比较到13==13的时候，找到了数据。

找到了数据之后，因为叶子结点之间存在双向链表，我们可以直接根据指针找到磁盘9，磁盘10，磁盘7.

 

采用B+树的形式进行范围查询，就是磁盘1-磁盘2-磁盘6-磁盘9-磁盘10-磁盘7，对比B树的磁盘1-磁盘2-磁盘6-磁盘1-磁盘2-磁盘9-磁盘1-磁盘3-磁盘10-磁盘1-磁盘3-磁盘7，查找的效率可以大大加快。

总结：

1、索引分为主键索引，唯一索引，外键索引，普通索引，前三个在创建对应约束的时候自动创建，普通索引需要自己单独创建。 

2、索引要在插入数据之前创建好，不可以数据都已经入库之后才创建。

3、索引使用的数据结构是B+树，能够实现范围的快速查询也可以保证磁盘读取的次数尽可能的少，使查询的速度增加。