【MYSQL】索引的特性

准备

博主:大大怪先森(记得关注，下次不要迷路哦）

---

---

前言

本文将讲解MySQL当中索引的相关内容！！！

---

提示：以下是本篇文章正文内容

一、认识磁道

1.1 mysql与存储

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，需要提高效率是mysql的一大特点。

扇区：
数据库文件，本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中，就是我们经常所说的扇区。当然，数据库文件很大，也很多，一定需要占据多个扇区。

1.2 定位扇区

柱面(磁道): 多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面每个盘面都有一个磁头，那么磁头和盘面的对应关系便是1对1的所以，我们只需要知道，磁头（Heads）、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做 CHS 。不过实际系统软件使用的并不是 CHS （但是硬件是），而是 LBA ，一种线性地址，可以想象成虚拟地址与物理地址。系统将 LBA 地址最后会转化成为 CHS ，交给磁盘去进行数据读取。不过，我们现在不关心转化细节，知道这个东西，让我们逻辑自洽起来可。

1.3小结

我们现在已经能够在硬件层面定位，任何一个基本数据块了(扇区)。那么在系统软件上，就直接按照扇区(512字节，部分4096字节),进行IO交互吗？

不是

 - 如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，换言之，如果硬 件发生变化，系统必须跟着变化 
 - 从目前来看，单次IO 512字节，还是太小了。IO单位小，意味着读取同样的数据内容，需要进行多次磁盘访 问，会带来效率的降低。之前学习文件系统，就是在磁盘的基本结构下建立的，文件系统读取基本单位，就不是扇区，而是数据块

故，系统读取磁盘，是以块为单位的，基本单位是 4KB 。

二、索引的理解

2.1 mysql和IO交互的基本单位

MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率，仍然按照扇区的大小4KB来进行IO交互的话显然对于处理大量数据的话还是不太友好，于是MySQL的进行IO操作的基本单位时16KB。

2.2 理解page

举例：mysql的基本工作过程

通过上图简易的工作原理，我们能看出来mysql当中进行IO操作采取的是page的方案，这种方法相对于是用扇区大小的4Kb大大减少了IO进行交互的次数，我们都知道进行IO交互是需要耗费时间的，使用page方案可以大大的缩短时间，但是又是如何提高效率的呢？

1.2.1单个page

MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要 先描述，在组织 ,我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的。

不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。

1.2.2 多个page

• 通过上面的分析，我们知道，上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
• 如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了。

2.3 页目录

2.3.1 单页目录

针对上面的单页Page，我们能否也引入目录呢？方便我们对于索引的查找。

那么当前，在一个Page内部，我们引入了目录。比如，我们要查找id=4记录，之前必须线性遍历4次，才能拿到结果。现在直接通过目录2[3]，直接进行定位新的起始位置，提高了效率。通过主键对每个page页当中数据会进行排序。

2.3.2 多页情况

MySQL 中每一页的大小只有 16KB ，单个Page大小固定，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。

在单表数据不断被插入的情况下， MySQL 会在容量不足的时候，自动开辟新的Page来保存新的数据，然后通过指针的方式，将所有的Page组织起来。

这样，我们就可以通过多个Page遍历，Page内部通过目录来快速定位数据。可是，貌似这样也有效率问题，在Page之间，也是需要 MySQL 遍历的，遍历意味着依旧需要进行大量的IO，将下一个Page加载到内存，进行线性检测。这样就显得我们之前的Page内部的目录，有点杯水车薪了.

那么如何解决呢？解决方案，其实就是我们之前的思路，给Page也带上目录?

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

存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。

其实目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。

可是，我们每次检索数据的时候，该从哪里开始呢？虽然顶层的目录页少了，但是还要遍历啊？不用担心，可以在加目录页.
如下图：

这就是传说中的B+树啊！没错，至此，我们已经给我们的表user构建完了主键索引。

三、聚簇索引和非聚簇索引

上述讲解的page使用的索引就是按照聚簇索引的方式实现的叶子节点的page当中会存放数据，而非聚簇索引的的不同就是叶子节点里面存放的不是数据而是地址，数据会专门使用其他的page也来存放数据，非聚簇索引就是通过索引找到的地址来找到需要的数据。
如下图：

其中， MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。

四、索引的操作

4.1 创建主键索引

• 第一种方法

-- 在创建表的时候，直接在字段名后指定 primary key
create table user(id int primary key, name varchar(30));

• 第二种方法

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

• 第三种方法

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

主键索引的特点：
一个表中，最多有一个主键索引，当然可以使符合主键
主键索引的效率高（主键不可重复）
创建主键索引的列，它的值不能为null，且不能重复
主键索引的列基本上是in

这里注意唯一建创建和主键的创建方式类似，主要把关键词改成unique即可，这里不再过多赘述。

4.2 普通索引的创建

• 第一种方式

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

• 第二种方式

create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引

• 第三种方式

create table user10(id int primary key, name varchar(20), email varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

4.3 查询索引

• 第一种方法: show keys from 表名
• 第二种方法:show index from 表名
• 第三种方法：desc 表名；

---

4.4 删除索引

• 第一种方法 :删除主键

alter table 表名 drop primary key;

• 第二种方法

alter table 表名 drop index 索引名； 索引名就是show keys from 表名中的
Key_name 字段

• 第三种方法

drop index 索引名 on 表名

4. 5索引创建的规则：

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

总结

希望本篇文章能给各位带来帮助，如有不足还请指正！！！
码字不易，各位大大给个收藏点赞吧！！！

宝子们，点赞，支持。
三连走一波！！！