【MySQL 11】索引 (带思维导图)

🌈 一、索引介绍

⭐ 1. 索引的概念

• 索引 (index) 是帮助 MySQL 高效获取数据的数据结构 (有序)。
• 索引不用加内存，不用改程序，也不用调用 sql 语句，只需要执行正确的 create index 即可将查询速度提升千百倍。
• 索引也是有代价的，索引在提高查询速度的同时，会降低 插入、修改、删除 操作的速度，索引的价值就在于提高海量数据的检索速度。

⭐ 2. 索引的分类

1. 主键索引 (primary key)：针对表中的主键所创建的索引。
2. 唯一键索引 (unique key)：避免同一个表中某数据列中的值重复。
3. 常规索引 (index)：快速定位特定数据。
4. 全文索引 (fulltext)：根据文本中的关键词进行索引。

⭐ 3. 索引的价值

• 索引的价值就在于提高海量数据的检索速度。
  • 现在以一个简单的例子来证明索引的价值。

0. 准备工作：创建一个海量数据表

1. 创建一个名为 index_demon 的数据库，并使用它。

drop database if exists `bit_index`;
create database if not exists `bit_index` default character set utf8;
use `bit_index`;

2. 产生随机字符串

delimiter $$
create function rand_string(n INT)returns varchar(255)
begindeclare chars_str varchar(100) default'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare return_str varchar(255) default '';declare i int default 0;while i < ndoset return_str = concat(return_str, substring(chars_str, floor(1 + rand() * 52), 1));set i = i + 1;end while;return return_str;
end $$
delimiter ;

3. 产生随机数字

delimiter $$
create function rand_num()returns int(5)
begindeclare i int default 0;set i = floor(10 + rand() * 500);return i;
end $$
delimiter ;

4. 创建存储过程，向员工表添加海量数据。
   • 构建一个 800w 条记录的数据，构建的海量数据需要有差异性，因此使用存储过程来创建。

delimiter $$
create procedure insert_emp(in start int(10), in max_num int(10))
begindeclare i int default 0;set autocommit = 0;repeatset i = i + 1;insert into EMPvalues ( (start + i), rand_string(6), 'SALESMAN', 0001, curdate(), 2000, 400, rand_num());until i = max_numend repeat;commit;
end $$
delimiter ;

5. 创建员工表。

CREATE TABLE `EMP`
(`empno`    int(6) unsigned zerofill NOT NULL     COMMENT '雇员编号',`ename`    varchar(10)              DEFAULT NULL COMMENT '雇员姓名',`job`      varchar(9)               DEFAULT NULL COMMENT '雇员职位',`mgr`      int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',`hiredate` datetime                 DEFAULT NULL COMMENT '雇佣时间',`sal`      decimal(7, 2)            DEFAULT NULL COMMENT '工资月薪',`comm`     decimal(7, 2)            DEFAULT NULL COMMENT '奖金',`deptno`   int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);

6. 执行存储过程，添加 800w 条记录（这个过程可能会很漫长，请耐心等待）。

call insert_emp(100001, 8000000);

• 创建号之后再查看表结构，在 Key 这一列可以看到，当前并没有对任何一个字段建立任何的索引。

1. 案例：查询员工编号 empno 为 998877 的员工

• 查这么一个人都需要耗时 6.94 秒，这还是在我的机器上一个人来进行操作，如果同时有 1000 个人并发查询，大概是要死机了。

• 解决办法：建立索引，为员工编号 empno 字段建立索引，数据库底层就会为员工表中的数据记录构建特定的数据结构。
  • 注：当前是为员工表中的 empno 这一列的 800w 个数据建立索引，因此过程可能会很漫长。

• 为 empno 添加完索引之后，再根据 empno 的值进行查找就几乎没有时间消耗了。

🌈 二、认识磁盘

⭐ 1. MySQL 与存储

• MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。
• 磁盘是计算机中的一个机械设备，其效率是比较低的，在加上 IO 本身的特征。
• 如何提高效率，是 MySQL 绕不开的一个重要话题。

⭐ 2. 磁盘的物理结构

1. 磁盘的整体结构

1. 空气过滤片：过滤空气硬盘透气孔中进入的空气，保证磁盘内部的情清洁，同时方式硬盘内部的零件氧化。
2. 主轴：需要保证电机稳定的转动，磁盘转动才能读取数据。
3. 音圈马达：硬盘读取数据的关键部位，主要用于将存储再磁盘上的信息转换为电信号向外传输。
4. 永磁铁：保证磁性的稳定，磁盘是由无数个光滑的小磁铁组成的，通过改变这些磁铁的正负极修改数据。
5. 串行接口：连接电脑与硬盘的接口。
6. 磁盘：存储文件。
7. 磁头：读取盘片上的信息。
8. 磁头停泊区：机械硬盘在不使用的状态，磁头不会放在存储区上面，而是会将磁头放在停泊区。
9. 磁头臂：支持磁头的可移动臂，通过电动机或电磁力控制，可以使磁头在盘片上移动到不同的磁道位置。

2. 磁盘中的盘片介绍

• 磁盘由多个盘片叠加而成，每张盘片都有两个盘面。
• 盘片是由无数个小磁铁组合成的，这些小磁铁通过调整正负极的方式记录二进制的 0 / 1。

1. 磁道：磁盘表面被分为许多同心圆，每个同心圆称为一个磁道，每个磁道都有一个编号，最外面的是 0 号磁道。
2. 扇区：每个磁道被划分成若干个扇区，每个扇区的存储容量为 512 个字节，每个扇区都有着属于自己的编号。

• 由于每个扇区的存储容量相同，因此最内侧磁道上的扇区数据密度最大，而最外侧磁道上的扇区数据密度最小。
• 近三十年来，扇区大小一直是 512 字节，但最近几年正在迁移到更大、更高效的 4096 字节扇区，通常称为 4K 扇区。
• 数据库文件就是保存在磁盘中的一个个扇区中的，因此找到一个文件本质就是，在磁盘上找到保存该文件的所有扇区。

3. 通过 HCS 的方式定位扇区

1. 磁头 (Heads)： 每个盘面都有一个与其对应的磁头，只要确定使用哪个磁头磁头即可确定使用哪个盘面。
2. 柱面 (Cylinder)： 同半径的磁道，整体上构成了一个柱面。在确定了盘面之后，再确定柱面即可确定数据在盘面上的哪一个磁道。
3. 扇区 (Sector)： 每个磁道又划分成若干个扇区，在确定了磁盘之后，再确定扇区即可确定数据在该磁道上的哪个扇区。

• HCS 寻址方式就是先通过 H 确定数据所在的盘面，再通过 C 确定数据所在的磁道，最后通过 S 定位到目标扇区。

4. MySQL 的表就是磁盘上的文件

• MySQL 中的数据库，就是存储在磁盘上的目录，而数据库中的表则是对应目录中的一个文件。
  • 这些数据库文件存储在 /var/lib/mysql 路径下， 蓝色字体的为目录文件，都是当前所对应的数据库。

5. 操作系统与磁盘交互的基本单位

• OS 在读磁盘的时候不是以 512 字节为单位的，这样实在是太慢了。系统读取磁盘以块为单位，1 块占 4KB。
• 物理内存实际上是被划分成多个 4KB 大小的页框，磁盘上的数据也会被划分成多个 4KB 大小的页帧，操作系统与磁盘以 4KB 为单位进行 IO 交互，就能提高数据加载和保存的效率。

⭐ 3. 磁盘的随机访问与连续访问

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

• 连续访问 (建议)：本次 IO 所给出的扇区地址与上次 IO 结束的扇区地址连续，这样磁头就能很快的开始这次 IO 操作，这样的多个 IO 操作称为连续访问。

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

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

🌈 三、MySQL 与磁盘交互

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

• MySQL作为一款应用软件，可以当成是一种特殊的文件系统，其有着更高频的 IO 场景，因此为了提高基本的IO效率，MySQL与磁盘交互的基本单位是 16KB，这个基本数据单元在 MySQL 这里也叫做 Page。

  • 注意：之后的是以InnoD存储引擎为例进行讲解。

• 在 InnoDb 存储引擎下，通过 show 命令也可以查看系统中的全局变量，可以看到InnoDB存储引擎交互的基本单位是 16KB。

show global status like 'innodb_Page_size';

⭐ 2. MySQL 与磁盘、操作系统之间的关系

• 逻辑上，MySQL 与磁盘交互一次获取 16KB 貌似是 MySQL 亲自从磁盘上读取内容，但 MySQL 可没本事直接碰到磁盘。
• 实际上，MySQL 向操作系统伸手要空间，然后操作系统从磁盘上将文件的内容导入到文件缓冲区，再让 MySQL 以 16KB 为单位拿取。

⭐ 3. Buffer Pool

• MySQL 中的数据文件，是以 Page（16KB）为单位保存在磁盘当中的。
• MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
• 只要涉及计算，就需要 CPU 参与，而为了便于 CPU 参与，一定要能够先将数据移动到内存当中。
• 所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。
• 而这时，就涉及到磁盘和内存的数据交互，也就是 IO 了，而此时 IO 的基本单位就是 Page。
• 为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
• 为了更高的效率，一定要尽可能的减少系统和磁盘 IO 的次数。

⭐ 4. 为何 MySQL 与磁盘交互时以 Page 为基本单位

• 先说结论，MySQL 与磁盘进行交互时以 Page 为基本单位，可以减少与磁盘 IO 交互的次数，进而提高 IO 效率。
• 当查询表中的某一条记录时，如果 MySQL 只从磁盘中将这一条记录加载到内存当中，那么当继续查询表中的其他记录时，MySQL 就一定需要再次与磁盘进行 IO 交互。
• 而如果当查询表中的某一条记录时，MySQL 直接将这条记录所在的整个 Page 都加载到内存当中，那么当继续查询表中的其他记录时，MySQL很可能就不再需要与磁盘进行 IO 交互了，因为这条记录很可能也在被加载进来的 Page 中，这时直接在内存中进行查询即可，大大减少了IO的次数。
• 不能保证用户下一次要访问的数据一定就在本次加载进来的 Page 中，但是根据统计学原理，当一个数据正在被访问时，那么下一次有很大可能会访问其周围的数据 (局部性原理)，因此有较大概率保证用户下一次要访问的数据和本次访问的数据在同一个 Page 当中，如果局部性原理没有起作用，那就再把对应的 Page 加载到内存当中即可。

🌈 四、索引的理解

⭐ 1. 自动按照主键对数据排序

• 在建表时，如果设置了主键，即便向表中插入数据时是乱序插入的，MySQL 底层会自动按照主键对插入的数据进行排序。

举个栗子

1. 创建表结构：创建一个名为 user 的用户表，表中包含用户的主键 id、年龄 age 和姓名 name 四个字段。
   • 只有添加主键才会默认生成主键索引。

2. 往表中无序的插入数据：插入数据时并没有按照主键的大小顺序插入。

3. 查询表内容：发现表中数据已经按照主键 id 自动排好序了。

⭐ 2. 如何管理 Page

• 在 MySQL 内部，一定需要并且会存在大量的 Page，也就决定了 MySQL 必须要将同时存在的多个 Page 管理起来。

• 不要单纯的认为 Page 就是一个内存块，Page 内部也必须写入对应的管理信息，再用链表将这多个 Page 结构体管理起来。

  • Page 就是一个大结构体。

struct Page
{struct Page* next;struct Page* prev;......   char buffer[NUM];
};	// 16 KB, new Page 将所有的 Page 用链表管理起来

🌙 2.1 管理单个 Page

• 假设 user 表中的记录都在同一个 Page 中，那么该 Page 的结构大致如下：
  • Page 结构体内部的数据也是用单链表进行存储的。

• 为了提高数据查询的效率，每个 Page 结构体内部的数据会按照主键进行排序。
• 因为单链表在查找的时候是顺序查找的，有序就意味着在查找的过程中有机会提前结束查询过程。
• 这就是为什么只要设置了主键索引，就会对表中数据自动按照主键顺序进行排序，有序才能构建页目录。

为单个 Page 内创建页内目录

• 针对上面的单页 Page，我们也可以引入目录。
• 由于 Page 内部的数据也是以单链表的形式进行存储的，当数据过多时，会在内部对单链表进行遍历，效率还是很低。
• 因此可以将在 Page 内部引入目录，使用目录将存储的多个数据按照键值划分成若干区域， 每个目录记录的是对应区域的最小键值。
• 引入了目录之后，如果想查找某个数据，会根据键值从而推算出该数据在那个目录管辖的区域，再在该区域内进行遍历即可。

🌙 2.2 管理多个 Page

• 如果数据表中存储的数据越来越多，一个 Page 存储不下了，此时就需要多个 Page 进行存储。
• 属于同一个表的多个 Page 之间也需要被双链表组织管理起来，在查询数据时，先找到在哪个 Page，再在 Page 内进行查询即可。

在多个 Page 之上创建页目录

• 同理，如果 Page 一多，再继续使用遍历双链表的方式查找 Page 效率就很低下了，因此也需要引入目录来管理这多个 Page。
• 这些目录使用的也是 Page 结构体，只不过结构体中的目录项执行的不再是数据记录，而是一个个 Page。
• 这样一来，在查询数据时就能先通过遍历页目录找到目标数据所在的 Page，再在该 Page 内部通过页内目录找到目标数据。
• 存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。
  • 有数据，就可通过比较，找到该访问那个 Page，进而通过指针，找到下一个 Page

🌙 2.3 为页目录创建页目录形成 B+ 树

• 即使给各个页目录也建立了页目录，但随着数据量不断增大，页目录的数量也会越来越多，这时再遍历页目录寻找目标 Page 本质还是线性遍历。
• 可以不断在页目录之上再创建页目录，最终就一定能够得到一个入口页目录，这时在查询数据时就可以从入口页目录开始不断查询页目录，最终找到目标数据所在的 Page，然后再在该 Page 内部找到目标数据。
  • 最终形成的这货就是一棵 B+ 树 ，它是一棵多叉树，此时已经给我们的 user 表构建完了主键索引。

⭐ 3. B+ 树作为索引结构的优势

• 只有叶子结点才用来保存数据，而非叶子结点只保存目录项，不保存数据。

  • 非叶子结点 page 不保存数据，就可以存储更多的目录项，就能管理更多的叶子 page。

• 叶子结点全部用链表关联起来，在查找时，比较希望进行范围查找。

• Page 分为目录页和数据页。目录页只放各个下级 Page 的最小键值。

• 查找的时候，自顶向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了 IO 次数。

⭐ 4. 其他数据结构为何不能充当索引

• 链表：查找时使用的是线性遍历，效率低下。
• 二叉搜索树：可能退化成线性结构，这时查找还是线性遍历。
• AVL 树和红黑树：虽然是平衡或者近似平衡，但其结构为二叉树。
  • 相对于身为多叉树的 B+ 树，AVL 和红黑树整体过高，意味着系统与硬盘更少的 IO Page 交互。
• 哈希表：官方的索引实现方式中 MySQL 是支持 HASH 的，只不过 InnoDB 和 MyISAM 存储引擎并不支持。
• 哈希表的优点就是它的查找时间复杂度是 O(1) 的，但哈希表不利于进行数据的范围查找。

常见存储引擎及其支持的索引类型

⭐ 5. B 树和 B+ 树的对比

• 听名字就知道，这两货一定很像，事实也是如此，B+ 树是 B 树的一种变形结构。

1. B 树和 B+ 树的区别

• B 树：所有结点即保存数据，也保存目录信息 (Page 指针)。

• B+ 树：只有叶子结点存储数据，其他目录页只有键值和 Page 指针，叶子结点全部用链表关联起来，而 B 树则没有。

2. 为何选择 B+ 树而不是 B 树

• B+ 树的非叶子结点不存储数据，可以用来存储更多的目录项，使得树变得更矮，导致 IO 变少，最终实现更高得效率。
• B+ 树的叶子结点之后使用链表关联，更有利于进行范围查找。

⭐ 6. 聚簇索引 VS 非聚簇索引

• MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。
• 对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

🌙 6.1 MyISAM 存储引擎

• MyISAM 最大的特点是，将索引 Page 和数据 Page 分离，也就是叶子节点没有数据，只有对应数据的地址。
• 像这种将数据和索引分离的的索引方案，叫做非聚簇索引。

1. 主键索引结构

• MyISAM 引擎同样使用 B+ 树作为索引结构，叶节点的 data 域存放的不是数据记录的地址，而是数据记录的地址。
  • 下图为 MyISAM 表的主索引， col1 为主键。
• 主键索引结构的 B+ 树的键值不能重复。

2. 普通索引结构

• MyISAM 存储引擎中，普通索引与主键索引唯一不同的地方就是普通索引的 B+ 树中的键值可以重复。
• 一张张表虽然可能会同时存在多个 B+ 树结构，但由于 MyISAM 存储引擎的 B+ 树叶结点中，存储的是对应的数据记录的地址，因此有效数据只会存储一份。

🌙 6.2 InnoDB 存储引擎

• InnoDB 存储引擎同样采用 B+ 树的索引结构，叶结点中存储的不是数据记录，而是对应数据记录的主键值。
• 相较于 MyISAM 而言，InnoDB 将数据和索引放在一起，这种索引方式被称作聚簇索引。

普通索引结构

• 通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
  • 这种过程，就叫做回表查询。

🌙 6.3 聚簇索引与非聚簇索引

• 聚簇索引：像 InnoDB 存储引擎这种，将数据记录与索引结构放在一起的索引方案，叫做聚簇索引。
• 非聚簇索引：像 MyISAM 存储引擎这种，将数据记录与索引结构分离的索引方案，叫做非聚簇索引。

举个栗子

• 当采用 InnoDB 存储引擎创建表时，在数据库对应的目录下会新增一个 .ibd 文件。
  • 在 .ibd 文件中存储索引和数据的相关信息，这就是所谓的聚簇索引，索引和数据存储在同一个文件中。

• 当采用 MyISAM 存储引擎创建表时，在数据库对应的目录下分别新增了 .sdi、.MYD、.MYI 这三种文件，这就是非聚簇索引。
  • 在 .sdi 文件中存储表结构相关信息，在 .MYD 文件中存储索引相关信息，在 .MYI 文件中存储数据相关信息。

🌈 五、创建索引

⭐ 1. 创建主键索引

primary key

1. 方法一：在建表语句中，在对应的字段名后指定使用主键 primary key。

2. 方法二：在建表语句的最后，指定某个字段为主键索引或某几个字段为复合主键索引。

3. 方法三：在建完表之后，使用 alter 指定为指定字段添加主键索引。

主键索引的特点

• 一个表中，最多只能有一个主键索引，可以由多个字段共同构成复合主键。
• 主键索引的查询效率高。
• 创建主键索引的字段，其字段值不能为 NULL，且不能重复。
• 主键索引的字段类型一般是数值类型。

⭐ 2. 创建唯一索引

unique key

1. 方法一：在建表语句中，直接在对应的字段名后指定 unique key。

2. 方法二：在建表语句的最后，对指定的某个字段或某几个字段设置 unique key。

3. 方法三：在建完表之后，使用 alter 指定为指定字段添加唯一键索引。

⭐ 3. 创建普通索引

index

1. 方法一：在建表语句的最后，使用 index 指定某个字段或某几个字段为普通索引。

2. 方法二：在建完表之后，使用 alter 指定为表中指定字段添加索引。

3. 方法三：在建完表之后，使用 create 指令为指定字段创建普通索引，并指定索引名。

普通索引的特点

• 一张表可以有多个普通索引，普通索引在实际开发的过程中用的比较多。
• 如果需要为某列创建索引，但是该列有重复的值，此时就可以使用普通索引。

⭐ 4. 创建全文索引

fulltext

• 全文索引比较常见的案例就是对文章中的词进行搜索，当对文章字段或有大量文字的字段进行检索时，会用到全文索引。
  • 例：平常使用的 ctrl + f 就是全文索引的一个使用场景。
• MyISAM 存储引擎一直支持全文索引，但 InnoDB 存储引擎是在 5.6 以后才开始支持全文索引。
  • 目前只支持英文索引。

创建全文索引案例

• 创建一张名为 articles 的文章表，表中包含文章的 id、文章标题 title、文章主体 body 三个字段。
  • 在建表语句的最后通过 fulltext 给 title 和 body 字段设置复合全文索引。

• 向表中的 title 字段和 body 字段插入测试数据，id 字段就让它自增去。

• 在 body 内部使用模糊匹配查询哪些文章包含了 database 这个单词。

• 这种查找方式实际上并没有用到全文索引，可以在 sql 之前加上 explain 关键字，用来查看 sql 语句的执行计划。
  • key 属性对应的值为 NULL，表示这条 sql 语句在执行过程中没有用到任何索引。

• 想要使用索引可以使用 match 和 against 关键字。
  • match：要匹配的索引，against：要匹配的关键字。

• 此时再使用 explain 查看这条 sql 的性能分析，可以看到 Key 属性的值已经变成了 title，这表示这条 sql 用上了 title 和 body 这两个字段的复合全文索引。

explain 执行计划各参数的含义

⭐ 5. 索引创建原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。
2. 针对于常作为查询条件 (where)、排序 (order by)、分组 (group by) 操作的字段建立索引。
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7. 如果索引列不能存储 NULL 值，要在创建表时使用 NOT NULL 约束它，当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引能最有效地用于查询。

🌈 六、查询索引

• 查看索引的方式有如下三种：

show keys from 表名;	// 如果想要显示的格式好看，可将 ; 换成 \G
show index from 表名;	// 如果想要显示的格式好看，可将 ; 换成 \G
desc 表名;

1. 使用 show keys from 表名 \G 查看 user1 表的索引信息。

2. 使用 show index from 表名 \G 查询 user2 表的索引信息。

3. 使用 desc 表名; 查看 user3 表的索引信息，使用之后在 Key 那一列查看对应的索引信息。

🌈 七、删除索引

⭐ 1. 删除主键索引

alter table 表名 drop primary key;

• 删除 user2 表的主键索引

⭐ 2. 删除普通索引

alter table 表名 drop index 索引名;
drop index 索引名 on 表名;

• 使用 alter table 表名 drop index 索引名; 删除 test1 表中 name 字段的唯一键索引。

• 使用 drop index 索引名 on 表名; 删除 test2 表中的 name 字段的唯一键索引。