【MySQL篇】索引特性，索引的工作原理以及索引的创建与管理

用户11719958

发布于 2025-12-30 11:52:04

2180

一，初识索引

MySQL索引类似于书籍的索引（目录），每个书籍都有目录，通过目录可以快速定位到要查找的页。

MySQL索引是一种数据结构，用于加快数据库查询的速度和性能。

索引能够显著提高查询的速度，尤其是在大型表中进行搜索时。通过使用索引，MySQL可以直接定位到满足条件的数据行，不需要遍历整个表。

但是查询速度的提高，同时是以插入，更新，和删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。

常见的索引分类：

主键索引（primary key）
唯一键索引（unique）
普通索引（index）
全文索引（fulltext）

二，MySQL与磁盘交互的基本单位

MySQL在应用层给用户提供存储服务，用户可以进行CURD操作，而存储的都是数据，数据在磁盘这个外设中。磁盘是计算机的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的。我们平时对表的操作，都是需要进行IO的。

我们知道，操作系统和磁盘交互（IO）的基本单位是4KB。而MySQL作为一款应用层软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率，MySQL和磁盘进行数据交互的基本单位是16KB（存储引擎为Innodb）。这个基本数据单元，在MySQL中叫做page。

三，MySQL中数据文件的特性

MySQL中的数据文件，是以page为单位保存在磁盘上的。

MySQL的CURD操作，都是需要计算的，找到对应的插入位置，找到对应要修改或者查询的数据。

而只要涉及到计算，就需要CPU的参与，而为了便于CPU的参与，一定要先将数据移到内存中。

所以再特定时间内，数据一定是磁盘中有，内存中也有。后序操作完内存数据之后，以特点的刷新策略，刷新的磁盘上。而这时就涉及到磁盘和内存的数据交互，也就是IO了。此时IO的基本单位是page。

为了更好的进行上面的操作，MySQL服务器在内存中运行的时候，在服务器内部，就申请了Buffer pool的大内存空间，来进行各种缓存。其实就是很大的空间，来和磁盘进行IO交互。

在Innodb存储引擎下，Buffer pool的大小为128M。MySQL需要自己对这部分空间进行管理。

为了更高的效率，一定要尽可能的减少系统的磁盘的IO次数。

四，理解page和索引

MySQL内部，将来Buffer pool缓冲区中一定需要并且存在大量的page，所以MySQL必须将这些page管理起来。通过“先描述，再组织”。所以page内部并不是单纯的存储数据，page内部也需要写入对应的管理信息。我们目前可以简单的理解成一个个独立的文件是由一个或者多个page构成的。

思考一下：为何MySQL和磁盘进行IO交互时候，要采用page的方案进行交互呢？为什么不是用多少加载多少呢？

因为预加载可以有效减少IO的次数。通过局部性原理，当前访问某些数据或代码的某一行时，下次访问可能会在这次访问的周边进行访问。
往往IO效率低下的最主要矛盾不是单次IO数据量的大小，而是IO的次数。

不同的page，再MySQL中都是16KB，使用prev和next构成双向链表。

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

所以这时就需要引入目录了。

页目录：就以书籍为例，每本数都有目录。我们如果要看指定的章节，找到章节有两种做法：

从头逐页的向后翻，直到找到目标内容。
通过提供的目录，找到指定的页数。当然在找目录的过程中，可以顺序查找，不过因为目录肯定少，所以可以快速提高定位。
本质上，书本中的目录是花了额外的纸做的，但却提高了效率。
所以，目录是一种空间换时间的做法。

单页情况：

针对之前链式结构的page页，我们可以引入页目录。

那么当前，在一个page内部，引入了页目录。比如，要查找id=4的记录，之前线性遍历4次，才能拿到结果。现在直接通过目录2，直接定位新的其实位置，提高了效率。

多页情况：

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

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

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

解决方案，给page也带上目录：

使用一个目录项来指向某一页，而这个目录项不存放数据，只存放它指向page中最小数据的键值
和页内目录不同，这种目录管理的是页，而页内目录管理的是数据。
其中，每个目录项的构成是键值+指针。

目录页（page目录）管理一个个的目录，目录页中的数据存放的就是指向那一页中最小的数据。通过该数据，与我们要查找的数据进行比较，找到访问哪个page。

对于一个page目录，它的大小是16KB，假设忽略掉前后指针，该page只存储一个数据和对应的指针，在64位环境下，16*1024/(4+8)=1365。即一个page目录，大概可以管理1365个page。也就是1365*1024/1024/1024=21MB，大概可以管理21MB的数据。

但是，我们的page目录也可能会产生线性遍历造成的多次IO，降低效率问题。同样，我们可以在上层再加一层目录page。

一般而言，两三层的设计已经足够了，可以管理特别大的数据。

这个结构就是B+树。

但是，实际存储的时候，除了叶子节点之间还会以链表的形式连接，其他节点都不会连接。这刚好符合B+树！

注意：

叶子节点保存有数据，其他节点不保存数据，只保存目录项。？？？ 原因：非叶子节点不保存数据，那么就可以存储更多的目录项，目录页，就可以管理更多的page。换句话说，查找数据时，可以淘汰掉的目录页更多，进行 IO的次数就可以大大减少。在IO层面，提高了效率。同时，每一个page节点，都有目录项，大大提高了搜索效率。

叶子节点为什么全部链接起来？？？ 首先，这是B+树的特点。所以MySQL使用这种数据结构。 方便进行范围查找。

上面的图，描述的就是MySQL innodb 下的索引结构。我们在建表的时候，就会生成这样一颗B+树，他会将我们表中的主键一列作为索引，而如果我们在建表的时候没有指明主键，系统会默认生成一个主键。一般我们插入数据的时候，就是在该结构下进行CURD的。
总结：

page分为目录页和数据页。目录页只存放各个下级page的最小键值。
查找的时候，自顶向下，只需加载部分目录页到内存，即可完成查找过程，大大较少了IO次数。

五，聚簇索引和非聚簇索引

前面所讲到的都是innodb存储引擎下的结构。

聚簇索引：innodb存储引擎下的结构就是聚簇索引，在叶子节点中，索引page和数据page放在一起存储。

非聚簇索引：MyISAM存储引擎下的结构就是非聚簇索引，在叶子节点中，索引page和数据page分开存储。也就是说叶子节点没有数据，只有对应数据的地址。

验证：

mysql> create table test1( -> id int primary key, -> name varchar(20) not null)engine=innodb;

mysql> create table test2( id int primary key, name varchar(20) not nuull)engine=MyISAM;

当然，MySQL除了默认会建立主键索引外，我们用户也有可能按照其他列信息建立索引，一般这种索引叫做普通索引。

对于MyISAM而言，建立主键索引和普通索引没有区别，主键索引的叶子节点存储的是指向数据的指针，那么创建普通索引的时候，就是再创建一个B+树，以指定列作为键值，叶子节点存储指向数据的指针即可。所以，建立普通索引和主键索引没有区别。
而对于Innode而言，我们知道主键索引对应的B+树，叶子节点会存放主键值和数据。在我们创建普通索引的时候，同样会创建一颗B+树，以指定列为键值，但是叶子节点中不存储数据，而是存储主键值。在查数据的时候，需要两边索引：首先通过普通索引（普通索引对应的B+树）查找到主键值，然后通过主键值在主键索引（主键对应的B+树）中查找数据。这种过程，叫做回表查询。

所以，建立索引本质就是以该列为键值，创建一颗B+树。

六，索引操作

查询索引

show keys from 表名； show index from 表名；

创建主键索引

第一种方式

-- 在创建表的时候，直接在字段名后指定 primary key create table user1(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，且不能重复
主键索引的列基本上是int
一个表中，最多有一个主键索引，当然可以使用复合主键
主键索引的效率高（主键不可重复）

唯一索引的创建

第一种方式

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

第二种方式

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

第三种方式

create table user6(id int primary key, name varchar(30)）； alter table user6 add unique(name);
唯一索引的特点：

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

案列：

mysql> alter table test1 add unique(name);

普通索引的创建

第一种方式

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);//给索引起名字idx_name
普通索引的特点：

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

全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。 MySQL 提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM ，而且默认的全文索引支持英文，不支持中文。

案列：

mysql> create table articles( -> id int unsigned auto_increment not null primary key, -> title varchar(200), -> body text, -> FULLTEXT(title,body))engine=myisam;