【MySQL】索引特性

一. 索引的概念

索引是数据库中用于提高查询速度的一种数据结构 (B+树)，它类似于书籍的目录，可以快速定位到数据在数据库中的位置，从而减少查询所需的时间。

• 数据库表中存储的数据都是以记录为单位的，如果在查询数据时直接一条条遍历表中的数据记录，那么查询的时间复杂度将会是 O(N)
• 索引的价值在于提高海量数据的检索速度，只要执行了正确的创建索引的操作，查询速度就可能提高成百上千倍。当一张表创建索引后，在数据库底层就会为表中的数据记录构建特定的数据结构，后续在查询表中数据时就能通过查询该数据结构快速定位到目标数据。
• 索引虽然提高了数据的查询速度，但在一定程度上也会降低数据增删改的效率，因为这时在对表中的数据进行增删改操作时，除了需要进行对应的增删改操作之外，可能还需要对底层建立的数据结构进行调整维护。

常见索引分为：

• 主键索引 (primary key)
• 唯一键索引 (unique key)
• 普通索引 (index)
• 全文索引 (fulltext)

二. 索引的价值

创建一个海量表，没有索引，查询的时候，效率非常低！

下面的 SQL 中创建了一个名为创建了一个名为 index_demon 的数据库，在该数据库中创建了一个名为 EMP 的员工表，并向表当中插入了八百万条记录。

drop database if exists `index_demon`;
create database if not exists `index_demon` default character set utf8mb4;
use `index_demon`;

-- 创建随机字符串函数
delimiter $$
create function rand_string(n int)
returns varchar(255)
deterministic
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i < n do
        set return_str = concat(return_str, substring(chars_str, floor(1 + rand() * 52), 1));
        set i = i + 1;
    end while;
    return return_str;
end $$
delimiter ;

-- 创建随机数字函数
delimiter $$
create function rand_num()
returns int
deterministic
begin
    declare i int default 0;
    set i = floor(10 + rand() * 500);
    return i;
end $$
delimiter ;

-- 创建雇员表
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 '部门编号'
);

-- 创建存储过程，向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start_num int(10), in max_num int(10))
begin
    declare i int default 0;
    declare end_num int default start_num + max_num - 1;
    set autocommit = 0;
    start transaction;
    while i < max_num do
        set i = i + 1;
        insert into EMP values (
            start_num + i,
            rand_string(6),
            'SALESMAN',
            0001,
            curdate(),
            2000,
            400,
            rand_num()
        );
    end while;
    commit;
end $$
delimiter ;

-- 调用存储过程，添加800万条记录
call insert_emp(100001, 8000000);

将上述 SQL 保存到文件 index_data.sql 中，然后在 MySQL 中使用 source 命令依次执行文件中的 SQL 即可，我这里花了 6 分钟。

查看数据库可以看到名称为 index_demon 的数据库。

进入该数据库，在数据库中可以看到一个名为EMP的员工表。

通过desc命令可以看到，目前EMP员工表中没有建立任何索引。

由于EMP表中有八百万条记录，因此在查看EMP表中的数据时可以带上limit子句。

查询EMP表中指定工号的员工信息，可以看到每次查询过程都需要花费 5.1 秒左右。这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有1000个人并发查询，那很可能就死机。

解决方法，创建索引，查询的时候，效率高！

当我们给员工表中的工号建立索引后，数据库底层就会为员工表中的数据记录构建特定的数据结构 (B+树)，需要注意的是，由于当前员工表中的数据量较大，因此建立索引时也需要花费较长时间，我这里花了 18 秒。

通过desc命令可以看到，目前EMP员工表中的 empno 建立了索引。

这时再查询EMP表中指定工号的员工信息，可以看到几乎检测不到查询时耗费的时间。

根本原因就是，给员工工号创建索引后再根据员工工号来查询数据，这时就能够直接通过底层建立的数据结构来快速定位到目标数据，从而提高数据的检索速度，这就是索引的价值。

三. 认识硬盘

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

先来研究一下磁盘：

磁盘中一个盘片

• 一个磁盘由多个盘片叠加而成，盘片的表面涂有磁性物质，这些磁性物质就是用来记录二进制数据的，因为盘片的正反两面都可涂上磁性物质，因此一个盘片有两个盘面。

磁盘中的一个盘片如下：

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

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

我们在使用Linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的，例如：数据库文件。

所以，最基本的，找到一个文件的全部，本质，就是在磁盘找到所有保存文件的扇区。而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。

定位扇区：CHS寻址方式

• 一个磁盘由多个盘片叠加而成，每个盘片有两个盘面，所有盘面中半径相同的同心磁道构成一个柱面。
• 每个盘面都有一个对应的磁头，每个磁头都有一个编号，所有的磁头都是连在同一个磁臂上的。

示意图如下：

• 磁头（Heads）： 每个盘面都有一个对应的磁头，因此确定了磁头也就确定了数据在哪一个盘面。
• 柱面（Cylinder）： 所有盘面中半径相同的同心磁道构成柱面，因此在确定了数据在哪一个盘面的基础上，再确定柱面也就确定了数据在该盘面上的哪一个磁道。
• 扇区（Sector）： 每个磁道被划分成若干个扇区，因此在确定了数据在哪一个磁道的基础上，再确定扇区也就确定了数据在该磁道上的哪个扇区。

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

说明一下：

• CHS寻址方式是磁盘定位扇区的方式，但实际CHS寻址方式对磁盘以外的设备来说没什么作用，因此系统软件在定位磁盘上的数据时采用的是LBA（Logical Block Address，逻辑区块地址）
• LBA是描述计算机存储设备上数据所在区块的通用机制，LBA和CHS之间可以通过计算公式进行相互转换，LBA存在的意义就是对底层逻辑器件进行虚拟化，让系统软件可以不用关心底层硬件具体的寻址方式，而实际底层硬件采用的还是CHS寻址方式。

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

操作系统与磁盘进行IO交互的基本单位是4KB，而不是扇区的大小512字节，原因如下：

• 物理内存实际是被划分成一个个4KB大小的页框的，磁盘上的数据也会被划分成一个个4KB大小的页帧，因此操作系统与磁盘以4KB为单位进行IO交互，就能提高数据加载和保存的效率。
• 操作系统与磁盘进行IO交互时，如果直接以扇区的大小作为IO的基本单位，那么这时系统的IO代码和硬件就是强相关的，将来当硬件的扇区大小发生变化时就需要对应修改操作系统的IO代码。
• 此外，以扇区的大小作为IO的基本单位太小了，这就意味着读取同样的数据内容，需要进行更多次的磁盘访问，而磁盘的效率是比较低的，这样IO效率就降低了。

因此操作系统与磁盘以4KB作为IO交互的基本单位，一方面是为了提高IO效率，另一方面是为了实现硬件和系统的解耦。

磁盘随机访问 与 磁盘连续访问

• 随机访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址不连续，磁头在两次IO操作之间需要做比较大的移动动作才能找到目标扇区进行IO。
• 连续访问： 本次IO所给出的扇区地址与上次IO给出的扇区地址是连续的，磁头很快就能找到目标扇区进行IO。

需要注意的是，尽管两次IO是在同一时刻发出的，但如果它们请求的扇区地址相差很大，那也只能称为随机访问，因为连续访问中的连续指的是访问的扇区地址的连续，而不是访问时间的连续，由于连续访问不需要过多的定位，因此效率比较高。

四. MySQL 与磁盘交互基本单位：Page

MySQL作为一款应用软件，可以想象成是一种特殊的文件系统，它有着更高频的IO场景，所以，为了提高基本的IO效率，MySQL与磁盘交互的基本单位是16KB (后面的都是基于InnoDB存储引擎来讲解的)

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

也就是说，磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB引擎 使用 16KB 进行IO交互。即 MySQL 和磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫做page（注意和系统的page区分）

数据交互具体过程

• 在MySQL中进行的各种CRUD操作时，都需要先通过计算找到对应的操作位置，只要涉及计算就需要CPU参与，而冯诺依曼体系结构决定了CPU只能和内存打交道，因此为了便于CPU参与，就需要先将数据加载到内存当中。
• 所以在特定的时间内，MySQL中的数据一定是同时存在于磁盘和内存中的，当操作完内存数据后，再以特定的刷新策略将内存中的数据刷新到磁盘当中，这时MySQL和磁盘进行数据交互的基本单位就是Page。
• 为了更好的支持上述操作，MySQL服务器在启动的时候会预先申请一块内存空间来进行各种缓存，这块内存空间叫做Buffer Pool，后续磁盘中加载的数据就会保存在Buffer Pool中，刷新数据时也就是将Buffer Pool中的数据刷新到磁盘。
• 由于内核中是有内核缓冲区的，因此MySQL从磁盘读取数据时，需要先将数据从磁盘读取到内核缓冲区，再将数据从内核缓冲区读取到Buffer Pool，MySQL将数据刷新到磁盘时，同样需要先将数据从Buffer Pool刷新到内核缓冲区，再将数据从内核缓冲区刷新到磁盘。

因此所谓的操作系统和磁盘交互的基本单位是4KB，就是指内核缓冲区与磁盘之间是以4KB为单位进行交互的。而MySQL的Buffer Pool和磁盘实际并不是直接交互的，因此所谓的MySQL与磁盘交互的基本单位是16KB，指的是MySQL的Buffer Pool与内核缓冲区之间是以16KB为单位进行交互的。只不过在说的时候更关注的是MySQL和磁盘之间的关系，所以直接说的是MySQL与磁盘交互的基本单位是16KB，相当于忽略了中间的内核缓冲区。

示意图如下：

五. 索引的理解

1. 建立测试表

id 需要添加主键，只有这样才会默认生成主键索引。

create table user(
    id int primary key,
    age int not null,
    name varchar(16) not null
);

show create table user \G

2. 插入多条记录

并没有按照主键的大小顺序插入。

insert user (id, age, name) values (3, 18, '杨过');
insert user (id, age, name) values (4, 16, '小龙女');
insert user (id, age, name) values (2, 26, '黄蓉');
insert user (id, age, name) values (5, 36, '郭靖');
insert user (id, age, name) values (1, 56, '欧阳锋');

3. 查看插入结果

发现默认是有序的，为什么？

select * from user;

根本原因就是，因为我们创建表时设置了主键，即便向表中插入数据时是乱序插入的，MySQL底层也会自动按照主键对插入的数据进行排序。

4. 为什么 MySQL 与磁盘交互的基本单位是 Page

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

也就是说，MySQL与磁盘进行交互时以Page为基本单位，可以减少与磁盘IO交互的次数，进而提高IO的效率。

• 如上面的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的次数。

5. 理解单个 Page

• MySQL中要管理很多数据文件，在运行期间一定有大量的Page需要被换入换出，因此MySQL一定需要将内存中大量的Page管理起来。
• 先描述再组织：MySQL将内存中的每一个Page都用一个结构体描述起来，然后再将各个结构体以双链表的形式组织起来，因此一个Page结构体内部既包含数据字段，也包含属性字段。
• 此外，为了方便后续数据的插入和删除，每个Page结构体内部存储的数据记录会以单链表的形式组织起来，并且各个记录之间会按照主键进行排序。

假设上述测试表中的记录都在同一个Page当中，那么该Page的结构大致如下：

说明：

• 每个Page结构体内部的数据会按照主键进行排序，目的是为了优化数据查询的效率，因为单链表在查找的时候是顺序查找的，有序就意味着在查找的过程中有机会提前结束查询过程。
• 这也就是前面所说的，只要设置了主键，即便向表中插入的数据是乱序的，MySQL底层也会自动按照主键对插入的数据进行排序，因此查询得到的数据是按照主键进行有序排序的。

单个Page内创建页内目录

• Page结构体内部存储的数据记录是以单链表的形式组织起来的，当页内部的数据量增多时，本质在页内部进行的还是线性遍历，效率低下。
• 这时可以在Page结构体内部引入页内目录，将Page结构体内部存储的数据记录按照主键划分为若干区域，页内目录中就存储着这若干区域的最小键值。
• 在Page结构体内部引入页内目录后，在页内部查询数据时就可以先通过页内目录找到目标数据所在区域的起始记录，然后再从该记录开始向后遍历找到目标记录。

比如在之前的Page内部引入页内目录后的结构大致如下：

说明：

• 在每个Page结构体内部引入页内目录，目的是为了加速在单个Page内部数据查询的效率。由于这个页内目录也是保存在Page内部的，而单个Page的大小是固定的，因此添加页内目录后Page内部能够保存的数据记录变少了，所以在Page内部引入页内目录本质是一种空间换时间的做法，就像给书添加目录需要花费更多的纸张一样。
• 每个Page结构体内部的数据会按照主键进行排序，其实就是为了引入页内目录，因为只有数据按照主键排序后引入页内目录才有意义，就像书中每一页都是按照页码进行排序的一样，如果一本书的页码是乱序的，那么它的目录根本就没有意义。

6. 理解多个 Page

• 随着数据量不断增大，单个Page中无法存下所有数据，这时就需要用多个Page来存储数据。
• 这时在查询数据时就需要，先遍历Page双链表确定目标数据在哪一个Page，然后再在该Page内部找到目标数据。

多个Page的示意图如下：

Page之上创建页目录

• 虽然在单个Page内部能够通过页内目录来快速定位数据，但在遍历Page双链表寻找目标Page时本质进行的还是线性遍历。
• 这时可以给各个Page结构体也建立页目录，页目录中的每个目录项都指向一个Page，而这个目录项存放的就是其指向的Page中存放的最小数据的键值。
• 在给各个Page结构体建立页目录后，在查询数据时就可以先通过遍历页目录找到目标数据所在的Page，然后再在该Page内部找到目标数据。

给各个Page建立页目录后的示意图如下：

说明：

• 这里的页目录与之前的页内目录的区别在于，页目录管理的是一个个的Page，而页内目录管理的是一条条的记录。此外，页内目录与其管理的多条记录是保存在同一个Page中的，而页目录是重新申请的一个Page结构体来保存的。
• 随着数据量不断增大，Page变得越来越多，这时一个页目录无法管理所有的Page，这时就需要更多个的页目录。这些页目录也是一个个的Page结构体，只不过这些Page结构体中存放的不是数据记录，而是各个Page的目录信息。但是在MySQL看来，无论Page当中存储的是什么数据，都应该被管理起来，因此这些Page页目录也需要用双链表连接起来。

页目录之上再创建页目录

• 就算给各个Page结构体也建立了页目录，但随着数据量不断增大，页目录的数量也会越来越多，这时在遍历页目录寻找目标Page时本质进行的还是线性遍历。
• 类似的，我们可以不断在页目录之上再创建页目录，最终就一定能够得到一个入口页目录，这时在查询数据时就可以从入口页目录开始不断查询页目录，最终找到目标数据所在的Page，然后再在该Page内部找到目标数据。

最终构建出来的结构如下：

说明：

• 最终构建出来的实际就是一棵B+树，这棵B+树就是InnoDB的索引结构，其中每一层Page的作用就是加速它的下一层的查找效率。一般我们再表中插入数据的时候，就是在 B+ 树结构下进行 “增删查改” 的。
• 如果我们创建表时设置了主键，那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来，保存在Buffer Pool当中，当我们查询数据时就可以通过查询这棵B+树来提高查询效率。
• MySQL中可能同时有大量的表正在被处理，因此Buffer Pool中可能会存在多个索引结构，也就是同时存在多个B+树结构，当我们查询表时访问的就是这张表对应的B+树结构。

B+树中的Page结点是否需要全量加入到Buffer Pool中？

• 当对MySQL中的某张表进行增删查改操作时，不需要将其对应B+树的所有结点全量加入到Buffer Pool中，甚至在刚开始时只需要将B+树的根结点加入到Buffer Pool中。
• 当后续访问表中的数据时，再将该数据对应路径上的结点加入到Buffer Pool中即可，对于其他不需要的结点根本不用加入到Buffer Pool中，这一点和操作系统中的页表是很像的。
• 此外，在刷新数据时也不需要将B+树中所有的结点都进行刷新，在Page结构体中有一个标记位用来标记当前Page是否被修改过，如果被修改过则说明这是一个脏数据，在刷新数据时只有脏数据才需要被刷新到磁盘上。
• 由于B+树中的结点都是16KB大小的Page，因此无论是刷新数据到磁盘函数从磁盘加载数据到Buffer Pool，都是以Page为单位进行的，这也就是所谓的MySQL与磁盘交互的基本单位是Page

如果把这棵B+树逆时针旋转90度，就会发现这其实就是操作系统中的页表结构，本质操作系统中的页表也是B+树结构。

以32位平台为例，页表将一个虚拟地址转换成物理地址的过程如下：

• 选择虚拟地址的前10个比特位在页目录当中进行查找，找到对应的页表。
• 再继续选择虚拟地址后续的10个比特位在对应的页表当中进行查找，找到物理内存中对应页框的起始地址。
• 最后选择虚拟地址中剩下的12个比特位作为偏移量，从对应页框的起始地址处向后进行偏移，最终得到的就是转换后的物理地址。

说明：

• 12个比特位有 2^12 中取值，字节对应就是4KB，所以物理内存中一个页框的大小就是4KB，这也就是为什么操作系统与磁盘交互的基本单位是4KB的原因。
• 此外，页表中的各个B+树结点也不需要全量加入到内存中，而只需要加入访问到的结点即可，所以页表占用的内存大小实际是可控的，这也就是为什么二级页表可行的原因。

7. 索引结构可以采用哪些数据结构

除了InnoDB存储引擎所采用的B+树结构，索引结构还可以采用哪些数据结构呢？

• 链表：查找时是线性遍历，效率太低。
• 普通二叉搜索树：可能退化成线性结构，这时查找还是线性遍历。
• AVL树和红黑树：虽然保证了二叉树是绝对或近似平衡的，不会退化成线性结构，但AVL树和红黑树都是二叉树结构，这就意味着树的层高会比较高，而查询数据时都是从根结点开始向下进行查找的，这也就意味着在查询过程中需要遍历更多结点，如果这些结点还没有被加载到Buffer Pool中，这时就需要进行更多次的IO操作，所以最终没有选择其作为索引结构。
• 哈希表：官方的索引实现方式中MySQL是支持HASH的，只不过InnoDB和MyISAM存储引擎并不支持。哈希表的优点就是它的时间复杂度是 O(1) 的，但哈希表也有一个缺点就是不利于进行数据的范围查找。

下面是几个常见的存储引擎，与其所支持的索引类型：

存储引擎	支持的索引结构
InnoDB	B+树
MyISAM	B+树
MEMORY/HEAP	哈希表、B+树
NOB	哈希表、B+树

8. B树 vs B+树

B树

B+树

B+树是B树的一种变形结构，那为什么我们没有采用普通的B树作为索引结构呢？

• 首先，普通B树中的所有结点中都同时包括索引信息和数据信息，由于一个Page的大小是固定的，因此非叶子结点中如果包含了数据信息，那么这些结点中能够存储的索引信息一定会变少，这时这棵树形结构一定会变得更高更瘦，当查询数据时就可能需要与磁盘进行更多次的IO操作。
• 其次，普通B树中的各个叶子结点之间没有连接起来，这将不利于进行数据的范围查找，而B+树的各个叶子结点之间是连接起来的，当我们进行范围查找时，直接先找到第一个数据然后继续向后遍历找到之后的数据即可，因此将各个叶子结点连接起来更有利于进行数据的范围查找。

9. 聚簇索引 vs 非聚簇索引

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

create database index_db;
use index_db;

create table InnoDB_table(
    id int primary key,
    name varchar(20) not null
)engine=innodb;

create table MyISAM_table(
	id int primary key, 
	name varchar((20) not null 
)engine=myisam;

说明：

• 采用InnoDB存储引擎创建表时会生成一个xxx.ibd文件，该文件中存储的是索引和数据相关的信息，这就是所谓的聚簇索引，索引和数据是存储在同一个文件中的。
• 采用MyISAM存储引擎创建表时会生成一个xxx.MYD文件和一个xxx.MYI文件，其中xxx.MYD文件中存储的是数据相关的信息，而xxx.MYI文件中存储的是索引相关的信息，这就是所谓的非聚簇索引，索引和数据是分开存储的。

MyISAM存储引擎 - 主键索引结构

• 之前推导的主键索引结构是InnoDB存储引擎的主键索引结构，而MyISAM存储引擎同样采用B+树作为索引的基本数据结构。
• 与InnoDB存储引擎的B+树不同的是，MyISAM存储引擎的B+树的叶子结点存放的不是数据记录，而是数据记录对应的地址。

比如下图为MyISAM存储引擎的主键索引结构，其中Col1为主键：

MyISAM存储引擎 - 普通索引结构

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

比如下图为MyISAM存储引擎的普通索引结构，其中Col2为索引列：

InnoDB存储引擎 - 普通索引结构

• InnoDB存储引擎的普通索引采用的也是B+树结构，但普通索引的B+树中的键值可以重复，并且B+树的叶子结点中存储的不是数据记录，而是对应数据记录的主键值。
• 当根据普通索引查询数据时，会先查找普通索引对应的B+树找到目标记录的主键值，然后再查找主键索引对应的B+树找到目标记录，这个过程就叫做回表查询。

比如下图为InnoDB存储引擎的普通索引结构，其中Col3为索引列：

说明：

• InnoDB存储引擎的普通索引的B+树叶子结点中没有保存整条数据记录，是为了节省空间，因为同一张表可能会创建多个普通索引，每个普通索引的B+树中都保存一份数据会造成数据冗余，所以通过回表查询主键索引对应的B+来获取整个数据记录，该做法本质一种以时间换取空间的做法。
• 当根据普通索引查询数据时，其实也不一定需要进行回表查询，因为有可能我们要查询的就是这条记录对应的主键值，因此查询完普通索引对应B+树后即可完成查询。
• 采用InnoDB存储引擎建立的每张表都会有一个主键，就算用户没有设置，InnoDB也会自动帮你创建一个不可见的主键，因为完整数据记录只会存储在主键索引对应的B+树中的，因此采用InnoDB存储引擎建立的表必须有主键。

六. 索引的操作

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

使用 desc 查看表的结构

主键索引的特点

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

2. 创建唯一键索引

方式一：在创建表的时候，直接在字段名后指定 unique

create table user4(
	id int primary key, 
	name varchar(30) 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);

使用 desc 查看表的结构

唯一键索引的特点

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

3. 创建普通索引

方式一：在创建表的最后，指定某列或某几列为普通索引

create table user7(
	id int primary key,
	name varchar(20),
	index(name)
);

方式二：创建表后，使用alter命令给指定字段添加普通索引

create table user8(
	id int primary key,
	name varchar(20)
);
alter table user8 add index(name);

方式三：创建表后，使用create命令给指定字段创建普通索引，并指定索引名

create table user9(
	id int primary key,
	name varchar(20)
);
create index index_name on user9(name);

使用 desc 查看表的结构

普通索引的特点

• 一个表中，可以有多个普通索引，一个普通索引可以由多个列同时承担。
• 创建普通索引的列，其列值可以为NULL，也可以重复。

4. 创建全文索引

全文索引比较常见的案例就是对文章中的词进行搜索，比如下面创建一个文章表，表当中包含文章的id、文章名称、文章内容，并在创建表的最后通过 fulltext 给 title 和 body 列创建全文索引。

create table articles(
    id int unsigned auto_increment primary key,
    title varchar(20),
    body text,
    fulltext(title, body)
)engine=MyISAM;

desc articles;
show index from articles \G

插入一些数据

insert articles (title, body) values
	('MySQL Tutorial','DBMS stands for DataBase ...'),
	('How To Use MySQL Well','After you went through a ...'),
	('Optimizing MySQL','In this tutorial we will show ...'),
	('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
	('MySQL vs. YourSQL','In the following database comparison ...'),
	('MySQL Security','When configured properly, MySQL ...');

select * from articles;

如果要查询哪些文章中包含database关键字，我们可以通过模糊匹配进行查找。

select * from articles where body like '%database%';

但实际这种查找方式并没有用到全文索引，在SQL语句前面加上explain，可以看到key对应的值为NULL，表示这条SQL在执行过程中没有用到任何索引。

explain select * from articles where body like '%database%' \G

如果要通过全文索引来查询，需要使用match against进行搜索。

select * from articles where match(title, body) against('%database%');

在这条SQL语句前面加上explain，可以看到key对应的值为title，表示这条SQL在执行过程中用到了索引名为title的索引。

explain select * from articles where match(title, body) against('%database%') \G

说明：

• MyISAM存储引擎是支持全文索引的，而InnoDB存储引擎是在5.6以后才开始支持全文索引的。
• 同时使用 title 和 body 建立全文索引时，相当于建立了一个复合索引，默认会选择 fulltext 中的第一个列名作为这个复合索引的索引名，所以这里 explain 中 key 对应的索引名为 title
• 由于是 title 和 body 共同建立的全文索引，所以如果文章当中没有出现关键字，但文章名称中出现了关键字则也会被筛选出来。

5. 查询索引

方式一

show keys from user1 \G

方式二

show index from user4 \G

方式三

desc user7;

6. 删除索引

创建测试表

创建一个用户表用于测试索引的删除，表中包含用户的id、姓名和邮箱，并将这三列分别设置为主键索引、唯一索引和普通索引。

create table user10(
	id int primary key,
	name varchar(20) unique,
	email varchar(30),
	index(email)
);

desc user10;

删除主键索引

alter table user10 drop primary key;
desc user10;

删除非主键索引

alter table user10 drop index name;
desc user10;

drop index email on user10;
desc user10;

说明：

• 一个表只有一个主键索引，所以在删除主键索引的时候不用指明索引名。
• 而一个表中可能有多个非主键索引，所以在删除非主键索引时需要指明索引名。

7. 索引创建原则

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

时刻要记住，创建索引的目的就是为了提高查询的效率。