【MySQL】索引的原理及其使用

什么叫索引

MySQL中的索引是一种用于快速查找记录的数据结构。它类似于一个目录，可以加速数据检索的速度。在处理较大数据时一般都会建立先对应的索引来帮助查找。

对于以上索引的定义，我们要搞懂几个地方：

1. 为什么建立索引能便于快速查找？
2. 这种数据结构是什么？
3. 索引的类型有哪些？分别有什么用？

本篇文章将围绕以上问题展开叙述。

减少磁盘IO次数

在前面的学习中我们知道，数据库其实就是一个目录文件，而数据库中的表其实就是目录下的一个个文件。而文件中的数据是被存放在磁盘中的，当我们要检索表中的数据时，首先得从磁盘中读取数据，又由于磁盘IO的大小单位是固定的（块，4KB），于是检索数据的效率跟磁盘IO的次数成反比。也就是说，磁盘IO次数越多，检索数据的时间消耗就越高。

关于文件系统IO的原理在我之前的文章中有讲到，感兴趣的可以去看看。

缓存池(Buffer Pool）

为了减少跟磁盘IO的次数，MySQL使用了缓存池的概念，即开辟一块大小固定的内存空间，用于存储数据页和索引页，当查询数据时，优先访问缓存池中的数据，如果缓冲池有则直接从从该池中读取，如果没有再去内核级缓冲区和磁盘中去找。缓冲池其实就是一个软件与操作系统之间的一个用户级缓冲区。这种机制能够有效的减少磁盘IO次数。缓存池除了用作数据缓存，还有以下作用：

• 延迟写：当数据页被修改时，修改首先写入到缓存池中，而不是立即写入磁盘，Innodb会定期将这些修改后的数据页写回磁盘。这样减少了在大量写操作的情况下磁盘IO的次数。
• 预读机制：缓冲池会提前加载需要的数据页，进一步减少查询时的磁盘IO延迟。

值得注意的是，不同的存储引擎之间的内存管理机制可能会有不同。由于mysql默认的·存储引擎是innodb，所以本篇文章默认mysql的存储引擎是innodb。

上面说缓存池中存储的是数据页和索引页，那页的大小是多少呢？

MySQL的页

MySQL作为一个数据库服务软件，它有着较高的IO场景（大多时候我们都在查询数据），所以为了保证一定的IO效率，MySQL进行IO的基本单位是page(innodb存储引擎是16KB)。注意，这里的IO是指mysql这个软件与内核级缓冲区之间的IO,而磁盘IO是磁盘和内核级缓冲区之间的IO。不同存储引擎的默认页大小可能存在差异。

为什么将页设置为16KB能减少IO次数呢？

16kb也就是4个数据块的大小。根据局部性原理，当访问到某一个数据块时，其周围的数据块被访问的概率会上升，一次性多读几个连续的块，可以有效减少下次磁盘IO的可能性。

那为什么是16kb而不是更多呢？16KB的页大小是一个经过多年实践验证的设置，MySQL和InnoDB开发团队已经对其进行了大量优化，确保其在各种应用场景下都能提供良好的性能和稳定性。

下面来看一个page的内容：

不同的page在mysql中都是16kb，其内部有prev和next两个指针构成双向链表。

一个表文件可能需要诺干个page来存储数据，于是，对数据库中表的管理就变成了对page的管理了。值得注意的是，MySQL 会默认按照主键给我们的数据进行排序。当数据量比较大时，一定需要多个page来存储数据，多个page之间通过指针连接起来，形成一个双链表。当我们要查询某一条记录，没有索引的情况下只能线性查找，这样的效率无疑是非常低下的。

类似于内存管理中的页目录机制，mysql的page也可以通过建立表目录来提高查找page的效率。

页内目录

对于page内部来说，16kB的大小已经可以存很多记录了，要想在一个page中找到目标记录，我们就需要线性遍历整个page，这样的效率太低了，更何况还会有要找多个目标记录的情况。于是在page内部mysql也设置了一个目录，用于page内部查找记录。其大致结构如下：

将一个page中所有数据记录分成诺干个组，每个目录项的的内容都是一个指向对应组的指针。当我们要查询一个id为4的记录时（假设主键是id），直接扫描目录，对比目录指向记录的id，看是否在其范围内。这样我们就只需要遍历目录就好了。例如上图id=4，属于目录2【3】之后，目录3【5】之前。所以id为4的记录一定在目录2指向的组中。这样分组一定是需要顺序的，而这种顺序是靠主键来维持的。如果一个表中没有手动设置主键，那么innodb会选择一个非空唯一键来排序，如果非空唯一键也没有，那么innodb会设置一个隐藏的属性来排序，我们无需关心。

下面sql样例能证明mysql会根据主键自动排序：

这样一来，排序之后的记录建立的目录就能通过比较主键来确定要查找的数据在哪一个目录里。而且，有了顺序之后，范围查找的效率也会快很多！只需要查找主键最大值和最小值得记录，中间的全部记录就是在这个范围之内。

页目录

随着数据量的不断增大，一个表文件所需的page也会越来越多，虽然在page内部我们能较快的查找到目标但是找到目标page本身就是一个麻烦事。难不成遍历整个双链表么？这样一来不还是线性查找么？遍历意味着依旧需要进行大量的IO，将下一个page加载到内存，进行线性检测。

于是，为了能够快速定位到目标page，mysql给page也加上了目录。注意这里得目录和page内部目录的区别。用一些page来充当目录，其内部不存放记录，而是存放指向其它page的指针！该目录具有以下特征：

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

如下图所示：

这样一来，要想找到目标page我们只需要遍历页目录就可以了。

为了更好的理解页目录，我们可以假设这样一个例子：

现在一张学生表里有10000条记录，其主键是id，这10000条记录的id值从1-10000。我们将这1000条记录用100个page去存放数据。每个page就有100条记录，且每条记录之间的id值都是从小到大排好序的。

• 对于page内部来说，将这100条数据依次分为10组，每组的首条记录的id值与下一组的首条记录的id值相差10，比如1、11、21、31等。页内目录的目录项指向每组首条记录。假如要找52通过比较，该条记录在第5目录项和第6目录项之间，也就是在[51,61)之间，就去第5目录项指向的组中去找。对于任意一条记录找到目录项的次数不超过10次，在组中找不超过10次，加起来不超过20次就能在一个page内部找到一条记录。
• 对于page来说，将100个page同样依次分组，每组的page的首条记录的id值依次为1、1001、2001等一共也有10组，从前往后每组10个page。我们用一个page作为页目录，其目录项的键值依次为1、1001、2001等。一共有10个目录项。假设我们要找5005条记录，我们先遍历页目录，通过比较得知该条记录位于第5目录项和第6目录项之间，也就是[5001,6001),于是我们从第5组page中找。这样就能找到目标page。对于任意一条记录，从页目录找到page的比较次数不超过20次，从page内部找到该条记录的次数也不超过20次，于是在10000条记录里面，找到任意一条记录只需要比较最多40次就能找到了。

类似的，面对更大的数据量，我们可以往上再次建立二级页目录等。如下图：

这玩意不就是B+树吗？上面说的这个结构跟索引有什么关系呢？其实，建立索引就是建立这种便于查找的数据结构！为什么这种数据结构能提高查找效率呢？借助缓冲池，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数。

正确理解索引结构

上面说的B+树是Innodb存储引擎管理数据的索引结构，不同的存储引擎其索引结构可能会有差别。但是其目的只有一个，那就是加快查找速度，减少IO次数！。

为什么Innodb的索引是B+树结构

• 首先排除链表，线性遍历效率太低了
• 排除二叉搜索树。二叉搜搜索树可能会退化成单支树，效率不稳定
• AVL或者是红黑树？虽然是平衡的或者是接近平衡的，但是由于是二叉树，相较于B+树这种多叉树，二叉树的的高度更高，也就意味着向下查找的次数会较多，还是效率低于B+s树。
• hash结构查找的效率是O(1)的，但是范围查找的效率较低。有些存储引擎的索引结构是采用hash，InnoDB和MyISAM都是采用B+。
• B树？B树的节点既有数据又有page指针，而B+树只有叶子节点有指针，其他目录页，只有键值和Page指针。也就是说，B树的节点中除了存放数据还存放指针，而B+树只有叶子节点有数据，非叶子节点不存放数据。换句话来说就是B树的page由于存放了一部分数据，指向其它page的指针就不多了，也就导致B树的分支较少于B+树，从而导致整体树高于B+树。而树越高，查找的次数就会越多，OI次数也会越多（假设同一个表中数据）。除此之外，B+树的叶子节点是全部相连的，因此在范围查找的效率也会比B树高。因此B+树在数据库中应用比B树会更加出色。
  • B树：
    
  • B+树：
    

各种存储引擎支持的索引

存储引擎	主要索引结构
InnoDB	B-Tree索引（聚簇索引和辅助索引）、全文索引、空间索引
MyISAM	B-Tree索引
MyISAM	B-Tree索引
Memory	哈希索引、B-Tree索引
NDB	分布式哈希索引、B-Tree索引

聚簇索引和非聚簇索引

聚簇索引和非聚簇索引是索引的两个类型。注意与索引结构的区别。
虽然InnoDB和MyISAM都是采用B+树做为索引结构，但是实现的细节会有所差异。InnoDB采用的是聚簇索引，即叶子节点存放的就是数据本身，也就是说整个B+树和数据是一个整体。而MyISAM采用的是非聚簇索引，即叶子节点存放的是指向数据的地址，也就是说整个B+树和数据是分开的。

下面给出非聚簇索引结构示意图：

其中一个表只能有一个聚簇索引，可以有多个非聚簇索引。

如何证明innodb存储引擎是聚簇索引而Myisam是非聚簇索引呢？
来看下面的sql样例：
创建一个表，将其存储引擎设置为MyISAM，观察其文件结构


其中.MYD文件用来存储数据，.MYI文件存储表的索引结构,这证明了MyISAM存储引擎的索引类型是非聚簇的，即数据和索引结构是分开的。

再来看InnoDB
创建一个表，将其存储引擎设置为InnoDB，观察其文件结构


其中.ibd文件存储的是数据和索引,这证明了InnoDB的引类型是聚簇索引，即数据和索引结构放在一起。

索引类型

除了区分聚簇索引和非聚簇索引，更细地，索引类型还能区分为以下几种类型：

• 主键索引
  • 特点：这是表中唯一标识每条记录的字段索引，不能包含空值。一张表只能有一个主键索引
  • 用途：确保每一行都有唯一标识，并且加速基于主键的查询。
• 唯一键索引
  • 特点：保证列中的所有值唯一，可以包含空值。
  • 用途：防止列中出现重复值，加速基于该列的查询。
• 普通索引（辅助索引）
  • 特点：允许出现重复值和空值
  • 用途：用于加速某些列的查询
• 全文索引
  • 特点：用于列内全文搜索，支持自然语言查询。
  • 用途： 适用于大型文本字段（如VARCHAR和TEXT），加速关键词搜索。
• 组合索引
  • 特点：在多个列上创建的索引
  • 用途：加速基于这些列的组合的查询。

值得注意的是，辅助索引虽然也是B+树结构，但是其叶子节点并没有数据，而是存储对应的key值（主键），拿到这个歌key之后再去主键索引中找到目标记录。这个过程叫做回表查询。也就是说，辅助索引找到的并不是一条记录，而是记录的主键值。要想找到完整的记录就得再去主键索引中去找。这种机制避免了空间的浪费，否则每建立一个索引都需要存储一份表中数据。

关于索引的操作

创建主键索引

除了在创建表的时候指定主键，mysql自动建立主键索引，我们还能在创建表之后添加主键索引
如：

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

添加主键不只是给这一列添加了主键约束，同时也会建立一个索引结构。

唯一索引的创建

跟主键索引类似，除了在创建表的时候指定唯一键，mysql自动创建唯一键索引，我们还能在创建表之后添加唯一键索引，本质就是修改字段属性。
如：

create table user6(id int primary key, name varchar(30)）；
alter table user6 add unique(name);

注意，如果某列的属性是主键或者是唯一键，mysql就会自动生成相应的索引。

普通索引的创建

1. 在表的定义最后，指定某列为索引

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

2. 创建完表以后指定某列为普通索引

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

3. 创建一个索引名为 idx_name 的索引

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

查看索引

1. show keys from 表名
   
2. show index from 表名
   
3. desc 表名
   
   mul表示可重复，即普通索引。

删除索引

1. 主键索引删除可以使用：alter table 表名 drop primary key.本质就是删除一个列的主键属性。
2. 其他索引的删除： alter table 表名 drop index 索引名;索引名就是show keys from 表名中的 Key_name 字段
3. drop index 索引名 on 表名这是最常用的方法