【MySQL】索引

目录

1. 什么是索引

索引本质是mysqld进程中一个数据结构，它的作用是用来提高查询数据的速度的，但查询速度的提高是以写操作（插入、删除和修改）的速度为代价的，这是因为每当对这些数据执行写操作时，数据库系统都需要同时更新相关的索引，以确保索引的准确和一致性。这个过程会增加额外的IO操作，因为索引数据通常存储在磁盘上，而磁盘IO是影响数据库性能的关键因素之一，但是即便如此，索引对查询性能的提升作用通常远远超过了这些不足

所以索引的价值，在于提高一个海量数据的检索速度，如果没有索引，数据库系统可能需要遍历整个表才能找到所需的数据，这将导致查询性能低下。而有了索引之后，数据库系统可以利用索引快速定位到所需的数据，从而大大提高查询速度

想象一下有两本同样内容的书，一个有目录一个没有，如果要查询特定内容的话，查找效率必然是有目录的高，即使目录会占用更多的纸张，也是空间换时间的策略

常见索引分为：

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)–解决中子文索引问题。

2. 认识磁盘

2.1 数据库文件

前面说过创建一个数据库本质就是在系统中创建一个目录，而创建表就是在某个目录中创建一个文件，不管是目录还是文件，它们的属性和内容数据都是要长久保存在磁盘这个外设上的

磁盘是一个机械设备，相较于其它元器件它的效率是比较低的，因为涉及实际的物理运动（盘片和磁头的旋转），而数据库操作又包含大量对磁盘上的数据进行的io操作，这就注定了效率不高，因为得频繁访问磁盘，硬件层很难提高效率，因此就得在软件层进行，所以就有了索引等技术来提高MySQL与磁盘交互的效率，其实就是降低访问磁盘的频次

2.2 磁盘的组成

磁盘上包含多个盘片，每个盘片有两个盘面，每个盘面的组成如下：

从上图可以看出来，在半径方向上，距离圆心越近，扇区越小，距离圆心越远，扇区越大，那么，所有扇区都是默认512字节吗？

目前都这样认为，因为保证一个扇区多大，是由比特位密度决定的

最新的磁盘技术，已经慢慢的让扇区大小不同了，不过现在暂时不考虑

不管是数据库文件还是系统中的文件，其实它们的数据（属性和内容）都是保存在这一个个扇区中的，也就是一个个小格子，如果文件很大，那必然要占用多个扇区

因此找到一个文件的本质是找到它所占用的所有扇区

2.3 定位扇区

每个盘面都有一个磁头，那么磁头和盘面的对应关系便是1对1的，柱面(磁道): 多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面

所以，只需要知道，磁头、柱面(等价于磁道)、扇区对应的编号即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做CHS 。不过实际系统软件使用的并不是CHS （但是硬件是），而是LBA（逻辑块地址） ，一种线性地址，可以想象成虚拟地址与物理地址。系统将LBA 地址最后会转化成为CHS ，交给磁盘去进行数据读

那么在系统软件上就直接按照扇区512字节进行IO交互吗？
其实并不是，原因如下：

1. 如果OS直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，如果硬件发生变化，也就是扇区大小不再是512字节，那么IO代码必须跟着变化，这种会导致系统和硬件耦合度太高不便于扩展和维护
2. 单次IO512字节，还是太小了。IO单位小，意味着读取同样的数据内容，需要进行多次磁盘访问，机械运行很慢，这会导致效率的降低
3. 文件系统读取数据的基本单位，就不是扇区，而是数据块。

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

如果就读几个字节，却读上来4KB岂不是浪费时间？虽然是，但考虑到程序访问局部性原理，下次读到的数据有很大概率就是这次多读上来的，所以下次就不需要再去访问磁盘，而是直接在内存中读取上次的数据即可

2.4 随机访问与连续访问

随机访问：本次IO所给出的扇区地址和上次IO给出扇区地址不连续，这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据

连续访问：如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称为连续访问

因此尽管相邻的两次IO操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问

磁盘是通过机械运动进行寻址的，连续访问不需要过多的定位，故效率比较高

而且连续访问只需要少量的转动，相较于随机访问能提高磁盘的使用寿命

2.5. MySQL与磁盘交互的基本单位

MySQL有着更高的IO场景，所以，为了提高基本的IO效率，innoDB存储引擎的默认传输的基本单位为16KB：

mysql> show global status like 'innodb_page%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_page_size     |16384|| Innodb_pages_created |154|| Innodb_pages_read    |1170|| Innodb_pages_written |341|
+----------------------+-------+
4 rows inset(0.00 sec)

需要注意的是MySQL服务器只是一个应用层进程，它无权直接访问磁盘，只能通过OS提供的系统调用来访问，所以16KB实际上是MySQL与系统之间数据传输的单位，然后系统与磁盘传输数据的基本单位是4KB，因此系统一般会把MySQL传输过来的数据按照4KB分成多份交给磁盘

这个16KB基本数据单元，在MySQL中叫做page，与系统中的page不是一个概念，其中的数据文件，是以page为单位保存在磁盘当中的

page默认大小是16KB，但也是可以配置的

为了提高效率，MySQL服务器内部会开辟一块很大的内存空间，也叫做缓冲池用来存放和操作临时数据（读的话先检测在不在池中，在就直接读，不在从磁盘加载，写就直接写），然后在合适的时机刷到系统层面上的缓冲区中，这里数据传输的基本单位是16KB，然后最终由系统实际的写到磁盘上，这里的传输单位是4KB，有了这个缓冲池可以在一定程度减少磁盘的IO操作，间接提高数据库的性能

buffer_pool默认大小：

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size |134217728|
+-------------------------+-----------+
1 row inset(0.06 sec)# 转换成MB
mysql>select134217728 / 1024 / 1024;
+-------------------------+
|134217728 / 1024 / 1024|
+-------------------------+
|128.00000000|
+-------------------------+
1 row inset(0.00 sec)

3. 索引的理解

创建一张测试表并插入一些数据：

# 表如下
mysql> show create table user \G;
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user`(`id` int NOT NULL COMMENT '一定要添加主键哦，只有这样才会默认生成主键索引',
  `age` int NOT NULL,
  `name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row inset(0.00 sec)

ERROR: 
No query specified

# 插入多条记录，注意并没有按照主键的大小顺序插入
mysql> insert into user (id, age, name) values(3, 18, '杨过');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user (id, age, name) values(4, 16, '小龙女');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user (id, age, name) values(2, 26, '黄蓉');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user (id, age, name) values(5, 36, '郭靖');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user (id, age, name) values(1, 56, '欧阳锋');
Query OK, 1 row affected (0.00 sec)

查询表内容：

mysql>select * from user;
+----+-----+-----------+
|id| age | name      |
+----+-----+-----------+
|1|56| 欧阳锋    ||2|26| 黄蓉      ||3|18| 杨过      ||4|16| 小龙女    ||5|36| 郭靖      |
+----+-----+-----------+
5 rows inset(0.00 sec)

可以发现的是插入是乱序，而查询的结果却是按照id进行升序排序了，问题是谁干的？为什么要这么干呢？

要回答这些问题需要重新认识一下MySQL中的基本数据单元page了，它大小设置为16KB本质是为了减少磁盘的IO操作提高MySQL性能的，在缓冲池中保存的就是一个个page，其中这些page中的数据可能有的新加载的，有的是加载了很长时间了又有的可能是已经被修改过的等等，MySQL为了对这么多page进行合理的操作就需要把它们管理起来，通过先描述再组织的方式，用类或者结构体进行描述，然后再用合适的数据结构把这一个个结点管理起来，这样对所有page的管理就变成了对数据结构的增删查改

不能简单的认为一个page是一个内存块，page内部必须要写入对应的管理信息

为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少不香吗?

如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。
如果要找id=5，那么就需要5次IO。但，如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录)，那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数

那怎么保证，用户一定下次找的数据，就在这个Page里面？ 不能严格保证，但是有很大概率，因为有局部性原理

往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数

在MySQL中一个个page对象都是使用一条双向链表管理起来的，页内部存放数据的模块也是一个链表：

因为有主键的问题， MySQL会默认按照主键给数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的

如果没有主键索引，那么输出顺序就是按照插入的顺序了

为什么要自动排序呢？
本质就是为了提高查找效率，正是因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程的，也可以根据有序的特性设计一个更高效的查找算法

上面页模式中，只有一个优势，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能

但不难发现，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这效率也太低了，那该如何提高查询效率呢？

这里提高效率需要从两个方向考量，一个是多page之间如何提高查找某个特定page的效率以及找到了之后，单page内部又该如何提高链式查找的效率

这时需要引出页目录的概念，可以把它理解成一个书本的目录，这样如果要查询书上某个内容时就不需要从头开始一点点遍历了，只需要遍历目录找到相关的关键字然后就可以直接跳转到相应的页码进行精确查找，这样可以大幅度提高查询效率，虽然书中的目录是多花了纸张的，这些纸张本可以保存更多内容数据，但是却提高了查询效率，所以，目录是一种“空间换时间的做法”

因此就有了下面这种page模型：
牺牲了一小部分保存数据的空间用来保存目录信息，这个目录中都保存两个字段，一个是保存起始记录数据的key值（这块空间最小的主键或者唯一键值），另一个是指向这个记录数据的指针，这样在查询时就不用去查一条条记录了，而是先查看目录，通过比较目录key值来判断要查询的记录是否在当前目录所指向的那块空间中，如果在直接跳转到该目录所指向的记录开始向后线性遍历查找

因此目录的作用就是把一大块空间划分成了一个个小空间，然后对这一小块空间进行遍历查找，进而可以大大提高查询效率

或者说划分成了一个个子链表

这时就可以回答MySQL为什么要按照主键值排序了，因为方便添加页内目录

解决了单page中查询的效率问题，那多page之间呢？因为二者之间的问题是一样的，就是线性遍历，过多的磁盘IO和遍历导致效率低下，因此解决方式也是一样的，就是给多个page也带上目录：
与叶子page（普通页）不同的地方在于，上层page（目录页）中并不保存记录数据，而是只保存目录数据，这些目录实际指向一个个下层page，具体来说就是每个目录都包含一个键值（通常是该页中最小记录的键，如主键或唯一索引值）和一个指向子页（或数据页，对于叶子页来说是实际的数据页）的指针

可以大概估算一下一个page最多能保存多少个子page，16KB *
1024个字节，只考虑目录中的两个类型，假设一个整形4字节和一个指针8字节，再除以个12字节大概是一千三百多个page，一个page是16KB，所以换算成MB的话大概是21MB

有了上层page在查询数据时又可以进一步提高查询效率，因为查询一次可以淘汰掉更多的数据

虽然如此，如果当上层的目录页过多又会导致同样的问题，线性遍历效率低，这时可以再给当前层的目录页添加上层目录页，里面保存的数据就是当前层的一个个目录页…这种多层目录页结构本质是一颗B+树，它是一棵多叉树，这意味着只要选择了一个结点，那么同层的其它所有结点都被排除掉了，查询效率能得到很大提高

细节1： 只有叶子结点会实际保存数据库中的记录数据，非叶子结点则没有，它们只保存目录项，这些目录项通常包括键和指向子结点的指针。键用于指导查找过程，而指针则指向包含实际数据的叶子结点或进一步细分的非叶子结点

这样做的好处在于，非叶子节点可以把所有的空间用于存放目录项，进而可以管理更多的子page，管理的page越多意味着树的高度会变低，那么在自顶向下查询时途径过的结点就会变少，同样的IO次数也会变少，因为只需要加载相关的几个page结点进来即可，这样不就相当于提高了查询效率

细节2： 只有叶子结点使用链表连接起来，路上同层结点没有连接关系，它们通过父子关系和兄弟关系（通常不直接通过链表）在树中组织起来

叶子使用链表是B+树本身的特性，是MySQL选择了B+树，这种特性适用于数据库索引，其次叶子中保存的记录数据被目录划分成了一个个子链表，这方便进行范围查找

这整个树结构就是MySQL innodb存储引擎下的索引结构，因此对表进行操作实际上就是在对这颗树进行增删查改

需要注意的是，即使不设置主键，系统会自动添加一个默认主键，并以此构建一颗B+树，可以认为任何一张表的结构都是B+树

3.1 为什么选择B+而不是其它数据结构

二叉搜索树？
二叉树相较于多叉树意味着它的高度会更高，从根到叶子会途径更多的结点，这意味着IO的次数会变多，所以但看这一点二叉树是不如多叉树的，其次，普通的二叉搜索树在极端情况下会变成链表，高度就变成了链表的长度，那么查询时效率会更低，所以二叉搜索树不合适

AVL和红黑树？
虽然解决了BST的极端情况，但本质还是二叉树，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互，虽然你很秀，但是有更秀的

哈希表？
在一些存储引擎中是支持哈希的，只不过主流的InnoDB和MyISAM不支持，查询起来虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行，因为每次查询都要通过哈希去算位置，而B+则是一旦找到范围查询的起始点（即满足查询条件的最小键值所在的叶子节点），B+树就可以沿着链表顺序遍历直到达到范围的终点，而不是重新搜索这棵树找到范围终点

B树？
与B+树的区别在于，非叶子结点中也保存记录数据，而且叶子结点并不通过链表连接起来，前一个区别意味着，一个page中可以保存子page的数量是少于B+树，这会导致B树的整体高度会高于B+树，在查询时会途径更多的结点，进而增加IO次数，降低查询效率，另一个区别意味着，B树不支持范围查找，一旦范围涉及到多个叶子page，那就得搜索这棵树这么多次，而B+则是找到了范围的起点，沿着链表遍历找到终点即可，查询次数也是要少于B树的

3.2 聚簇索引和非聚簇索引

二者底层使用的数据结构都是B+树，只是对叶子结点的设计有些差别，聚簇索引的叶子节点既保存目录又保存记录数据，都保存在一个page中，而非聚簇索引的叶子只保存了目录，实际的数据并不保存在叶子里而是其它空间中，通过目录中的数据指针来找到数据

InnoDB引擎使用的是聚簇索引，而MyISAM使用的是非聚簇索引：

db1是InnoDB表，db1.ibd就是该表的数据和索引文件
db2是MyISAM表，db2.MYD是数据文件，db2.MYI是索引文件

MySQL 除了默认会建立主键索引外，用户也有可能建立按照其它列信息建立的索引，一般这种索引可以叫做辅助（普通）索引

对于MyISAM，建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复

对于InnoDB而言，InnoDB 的非主键索引中叶子节点并不会像主键索引的叶子结点一样，它不保存数据，而只保存对应记录的主键值，所以通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询

这里可以看出，一个表是可能建立多个B+树的，由于大概率不会把整个树加载到内存，所以并不会特别浪费空间

为何InnoDB 针对这种辅助（普通）索引的场景，不给叶子节点也附上数据呢？

原因就是太浪费空间了

4. 索引操作

4.1 创建主键索引

有如下几种方式：

# 在创建表的时候，直接在字段名后指定 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);

创建主键底层就会按照主键值创建B+索引

主键索引的特点：

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

4.2 创建唯一键索引

有如下几种方式：

# 在创建表的时候，直接在字段名后指定 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 user3(id int, name varchar(30));
alter table user6 add unique(name);

4.3 创建普通键索引

如下几种方式：

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);

普通索引的特点：

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

可以创建普通复合索引，语法类似，用括号指定，之间使用逗号隔开，区别在于复合索引使用组合值作为key而不是单单一列值

4.4 查询索引

有如下几种方式：

show keys from 表名;

show index from 表名;

desc 表名;

mysql> show index from db1 \G;
*************************** 1. row ***************************
        Table: db1
   Non_unique: 1
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 row inset(0.00 sec)

ERROR: 
No query specified

4.5 删除索引

如下几种写法：

# 删除主键索引： 
alter table 表名 drop primary key;# 其他索引的删除： 
alter table 表名 drop index 索引名; 
索引名就是show keysfrom 表名中的 Key_name 字段

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

5. 建议与其它相关概念

索引创建的建议：

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

5.1 最左匹配原则

最左匹配原则是数据库复合索引（也称为组合索引或联合索引）使用中的一个重要概念。它指的是当使用复合索引进行查询时，查询条件必须包含索引定义中最左边的连续列，可以全部包含，也可以包含一部分，但必须保证从左到右依次匹配，这样索引才能被有效使用

复合索引是在数据库表的多个列上创建的索引。在创建复合索引时，需要指定列的顺序。这个顺序决定了索引的排序方式，也影响了查询优化器如何使用索引

最左匹配原则的具体含义：

• 必须包含最左列：查询条件中必须包含复合索引定义中最左边的列。这是使用复合索引的最低要求。
• 可以包含后续列：如果查询条件中除了最左列外，还包含了复合索引定义中的后续列，那么索引的使用会更加高效。这是因为索引已经按照这些列的顺序进行了排序。
• 跳列不可行：查询条件不能跳过复合索引定义中的列。例如，如果复合索引是（A，B，C），那么查询条件不能是仅包含B或C的，因为这样无法利用索引的排序特性。
• 列顺序很重要：复合索引中的列顺序对查询性能有很大影响。因此，在设计复合索引时，应该根据查询需求，将最常出现在查询条件中的列放在最前面。

5.2 索引覆盖

索引覆盖是指当执行一个查询时，所需的所有数据都可以从索引中直接获取，而无需再进行回表查询。这通常发生在非聚簇索引（二级索引）上，当查询的字段完全包含在索引中时，就可以实现索引覆盖

在数据库中，索引通常用于加速数据的检索。对于非聚簇索引，索引中存储的是指向数据表中数据行的指针或主键值。然而，在某些情况下，如果索引包含了查询所需的所有字段，那么数据库就可以直接从索引中获取数据，而无需再访问数据表。这就是索引覆盖的基本原理

要实现索引覆盖，通常需要在创建索引时指定多个列，这些列应该包含查询中可能需要的所有字段