MySQL数据库操作篇5（索引的原理及用法）

索引是MySQL中的一大利器，这篇文章旨在讲清楚什么是索引，索引的底层实现原理是什么，使用索引能带来哪些好处，以及索引如何使用较好

此文章会用到前面提到过的一些术语，比如元组，一个元组就指表中的一行数据

目录

什么是索引

索引的底层实现原理 

B树 VS B+树

聚簇索引 vs 非聚簇索引 

索引的相关操作

1.创建主键索引 

2.创建唯一键索引

3.创建普通索引

索引的查询 

删除索引

索引创建原则

---

 

什么是索引

通过前面的学习，我们了解过，在数据库中创建一个表，我们就可以往表中插入数据，然后需要数据的时候在表中查找就行了，普通用户可以直接使用数据库设计者提供的功能，不用关心如何实现查找，作为程序开发人员，我们需要关心这个问题。前面我们讲了很多MySQL中查找数据的方法，但是这些查找总得有个底层的算法实现吧，我们最容易想到的查找算法是遍历，把表中所有的数据都遍历一遍，不就可以查到想要的数据了嘛

对于小批量的数据，确实可以这么做，但是数据库就是存放大批量数据的，一张表中可能有几万行数据，如果这个时候还使用遍历查找，那么查找效率会非常的低，为了解决这个问题，数据库设计者就提出了索引这个概念，所以索引就是来解决数据查找效率的问题的

索引的实现是靠比较优秀的查找算法，不用加内存，不用改程序，不用调sql，只要执行
正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度

索引的底层实现原理 

搞明白索引是干嘛的，有什么用之后，我们接着来看，如何实现索引。为了更透彻的理解索引，此篇文章要从硬件和软件两个方面同时介绍。

查找过程本质就是在做排除，把无用数据排除在外，高效率的查找就是在较短的时间内，排除掉尽可能多的数据，要做到这一点，就需要我们人为把数据分配整齐，想一想我们按照类别给数据进行划分，只需要去待查找数据的所属类别里查找即可，此时其他所有的类别就都被排除掉了。

例如我们书本中常见的目录就是一种高效的查询方式，书本根据内容的不同，划分为几个章节，目录中记录着这些章节的具体页码，当想要查找某个信息时，只需要去该信息所属的那个章节即可，而其他的章节在这个查找过程中就被排除掉了，这样就能很快查到信息，比遍历一本书要快速很多，索引的实现原理就类似于此

我们先来了解MySQL与磁盘的交互，关于磁盘的内容，笔者在Linux的IO篇里详细的讲解了，如果不明白磁盘工作原理的，请移步查看。操作系统每次与磁盘IO的大小是4kb。大家可以想想，每次4kb字节的IO，对于操作系统来说可能就够用了，但是对于数据库来说，小了一些，因为数据库中存放的数据是相当大的，因此数据库需要与磁盘一次IO更多，根据实践，数据库一次与磁盘进行16kb的IO是比较好的

 

但是之前我们了解过，只有操作系统才能够和硬件进行交互，而数据库本质是属于应用层的，所以它没有权利和磁盘直接进行IO，它与磁盘的IO操作要依托操作系统来完成

MySQL创建表本质就是创建一个文件，所以MySQL与操作系统进行交互时，也是通过文件来进行的，如上图，MySQL会申请一个缓冲区用来存取与OS进行IO操作的数据

这个过程是麻烦的，我们在逻辑上可以忽略OS这个角色，认为MySQL直接与磁盘进行IO操作，且一次16kb大小，也就是说MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫做page（注意和系统的page区分）

为什么要提到page呢？因为MySQL 中的数据文件，是以page为单位保存在磁盘当中的，一张表，也就是一个数据文件，是由一个或者多个page构成的。MySQL中的数据文件以page为单位，势必会造成存储空间不能完全利用的情况，为什么要以page为单位，而不是存多少用多少呢？这就涉及到IO的效率了，我们都知道与磁盘的IO效率是很低的，如果一次以表中一个元组（表中的一行）的大小为单位，那么在查找时，有100个元组就要进行100次IO操作，而使用page为单位，一次IO就可以把100个元组的数据都加载进内存

各个page之间通过双向链表连接起来，每个page里包含着所存的数据内容，同时也有指向前一个和后一个page的指针

 

大家可以想一想，如果一个page里存满了数据，那么在一个page里查找数据，如果使用线性遍历的方法进行查找的话，效率会很低，还记得我们前面的提到过比较高效的目录形式吗？为了提高在一个page中遍历的效率，我们可以将page中的数据先按顺序进行一个排序，排完序之后再将其按照序号划分成目录，就拿上面这个page为例来进行划分，划分结果如下图

 

 

这里数据量比较小，不过不影响我们理解这种思想，假设查找id为4的人是谁，大致过程为在目录区中使用二分法查找id为4属于哪个目录，图中只有两个目录，第二个目录标记的最大id值为3，因为4>3，我们就从目录二进入到id为3的数据，然后向下移动一位，就找到了id为4的数据，随后返回结果即可。这里数据量很小，效果并不明显，随着数据量增大，这种查询算法的优势愈加明显

当然，我们前面说过一张表可能有一个page或者多个page构成，为了展示整张表的全貌，这里假设一个page只能存放五个元组，MySQL中一张表的存储结构大致如下（为了作图方便，数据中人物的名字是重复的，只关注数据的id序号即可）

理解完上面的内容，接下来还得思考一些问题，数据库就是用来存放数据的，那么大量的数据势必会造成page数目的剧增。我们前面解决的是如何在一个page内部高效的查找数据，当page数目变得非常多的时候，我们就得考虑，如何在page和page之间进行高效的查找，以上图为例，假设我们要查找id值为14的全部数据，那么会先加载第一个page，通过内部的查询后发现不在该page里，于是加载第二个page，通过内部的查询后发现不在该page里，于是加载第三个page，终于在内部查询到了，返回查询结果

将每一个page加载到内存，进行线性检测。这样就显得我们之前的page内部的目录，有点杯水车薪了，那么如何解决呢？解决方案，其实就是我们之前的思路，给page也带上目录

使用一个目录项来指向某一页，这个目录项存放的是将要指向的页中存放的最小数据的键值
和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行
其中，每个目录项的构成是：键值+指针

大致过程如下图

将page也放到一个目录中，这样就不用怕随着page数目的增多，查询效率低下的问题了

在上图中最下层，存放着真实数据的页，我们称之为普通页，而第二层，标志普通页的目录的页我们称之为目录页，目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址
虽然目录页改善了普通页的查找效率，但我们每次检索数据的时候，该从哪里开始呢？虽然顶层的目录页少了，但是还要遍历啊？不用担心，我们还可以给目录页再加一层目录页

大致操作如下图

 

通过这三层目录结构的搭配使用，查找数据的效率将会变得非常之高，这个结构其实就是B+树， 也是索引的底层实现原理，不过这并不意味着，所有的数据库的索引都是靠B+树来实现的，这取决于数据库使用的存储引擎，我们常用的InnoDB使用的就是B+树来实现，也有的存储引擎使用B树，使用哈希等结构，根据不同的应用场景来选择合适的

B树 VS B+树

B树和B+树都是常用的数据结构，也是MySQL数据库都支持的结构形式，不同的存储引擎所使用的数据结构都不尽相同，这里简单展示一下B树和B+树是如何在数据库中应用的，以及两者之间的区别，关于这两种数据结构如何创建，笔者将会在数据结构栏里详谈

下图是B树的结构图 

 

 下图是B+树的结构图

 

 

观察两图可得出， B树节点，既有数据，又有page指针，而B+，只有叶子节点有数据，其他目录页，只有键值和page指针
B+叶子节点，全部相连，而B没有

聚簇索引 vs 非聚簇索引 

在使用索引时，我们可能经常会听到聚簇索引和非聚簇索引这些概念，这些具体指代的是什么呢？如下图，在这个B+树索引中，叶子节点存放的并不是数据，而是数据在内存上的指针，通过这个指针就可以找到数据，大家可以发现，在这个过程中，数据就和索引结构分离开了，像这样的索引方案，我们就称之为非聚簇索引

在下面这张图中，B+树的叶子节点存放的就是数据本身，而不是数据的指针，像这样索引结构和数据放在一起的我们就称之为聚簇索引

InnoDB是采用聚簇索引，MyISAM采用的是非聚簇索引，不过这两个存储引擎的索引结构均是B+树，你可能好奇，为什么要分成聚簇索引和非聚簇索引呢？下面就是两者各自的优缺点

聚簇索引的优点：

  聚簇索引将数据行物理上按照索引的顺序存储在磁盘上，因此对于按索引列进行范围查询或排序的操作，聚簇索引可以提供更高的查询性能

  聚簇索引可以减少磁盘I/O操作，因为相邻索引键值的数据行通常在磁盘上也是相邻存储的，减少了磁盘读取的次数

  聚簇索引可以节省存储空间，因为数据行按照索引顺序存储，可以减少数据行中重复的索引列的存储

聚簇索引的缺点：

  聚簇索引的维护代价相对较高。当插入或删除数据时，因为数据行的物理存储顺序可能需要进行调整，所以维护聚簇索引的代价较高

  聚簇索引一般只能创建在唯一键或主键上，对于非唯一键的查询性能可能较差

非聚簇索引的优点：

  非聚簇索引的维护成本相对较低。当插入或删除数据时，不需要调整数据行的物理存储顺序，因此维护非聚簇索引的代价较低

  非聚簇索引可以创建在任意列上，不限唯一键或主键。可以提供对非唯一键的高效查询

非聚簇索引的缺点：

  非聚簇索引的查询性能可能较低。因为数据行在磁盘上的物理存储顺序与索引顺序不一定一致，对于范围查询或排序操作，可能需要进行更多的磁盘I/O操作

  非聚簇索引通常需要占用更多的存储空间，因为索引结构中需要存储索引列的值和指向数据行的指针。 根据具体的应用场景和查询需求，选择合适的索引类型可以提高数据库的查询性能。聚簇索引适用于经常进行范围查询或排序的列，而非聚簇索引适用于非唯一键或不经常进行范围查询的列。

需要大家注意的是，聚簇索引一定要有唯一键索引，也就是主键索引，如果自己没有设置，MySQL会默认设置，除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引，关键点来了，普通索引的叶子节点可不像主键索引那样存放着数据，而是存放着对应的主键值，拿到这个主键值后，还要回过头根据这个主键值，再去查一遍主键索引，最终找到数据，这个过程叫做回表查询，这也是为什么强调聚簇索引一定要有主键索引的原因。为什么不把普通索引的叶子节点也放上数据呢？因为主键所引上已经存放数据了，再存放一次就会造成数据的重复，空间的极大浪费

 

索引的相关操作

了解完索引是什么，以及索引的底层实现原理之后，我们接下来进入实操环节，看看如何在MySQL中创建和使用索引，在创建数据库时要根据未来的需求来确定采用哪种存储引擎，默认情况下都是采用InnoDB存储引擎

1.创建主键索引 

在InnoDB中会默认根据主键创建主键索引，故而只需要指明主键即可

1.-- 在创建表的时候，直接在字段名后指定 primary key
create table test(id int primary key, name varchar(30));
在InnoDB中会默认根据主键创建一个主键索引

2.-- 在创建表的最后，指定某列或某几列为主键索引
create table test(id int, name varchar(30), primary key(id));

3.create table test(id int, name varchar(30));
-- 创建表以后再添加主键
alter table test add primary key(id);

主键索引的特点：
一个表中，最多有一个主键索引，当然可以使符合主键
主键索引的效率高（主键不可重复）
创建主键索引的列，它的值不能为null，且不能重复
主键索引列的数据类型基本上是int，有利于数据内容的排序和划分

2.创建唯一键索引

在InnoDB中会默认根据唯一键创建唯一键索引，故而只需要指明唯一键即可

1. -- 在表定义时，在某列后直接指定unique唯一属性。
create table test(id int primary key, name varchar(30) unique);

2.-- 创建表时，在表的后面指定某列或某几列为unique
create table test(id int primary key, name varchar(30), unique(name));

3.create table test(id int primary key, name varchar(30));

-- 创建表以后再添加唯一键
alter table test add unique(name);

唯一索引的特点：
一个表中，可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引，必须保证这列不能有重复数据
如果一个唯一索引上指定not null，等价于主键索引

3.创建普通索引

1.create table test(id int primary key,
name varchar(20),
email varchar(30),
index(name) --在表的定义最后，指定某列为索引
);
 

2.create table test(id int primary key,

name varchar(20),
email varchar(30),

);

alter table user9 add index(name); --创建完表以后指定某列为普通索引

3.create table test(id int primary key,

name varchar(20),
email varchar(30),

);

-- 创建一个索引名为 idx_name 的索引
create index idx_name on test(name);

普通索引的特点：
一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

索引的查询 

第一种方法: show keys from 表名
第二种方法: show index from 表名;
第三种方法（信息比较简略）：desc 表名；

删除索引

第一种方法-删除主键索引： alter table 表名 drop primary key;

第二种方法-其他索引的删除： alter table 表名 drop index 索引名；

索引名就是show keysfrom 表名中的 Key_name 字段

第三种方法方法： drop index 索引名 on 表名

索引创建原则

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引