Ⅰ. 初识索引​ 在 MySQL 中，索引是一种用于提高查询效率的数据结构。它可以帮助数据库系统快速定位和访问表中的数据。索引可以基于一个或多个列创建，并且可以应用于表中的任何列。

​ 不用加内存、不用改程序、不用调sql、只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的 IO。所以 索引的价值，在于提高一个海量数据的检索速度。

​ MySQL 支持多种类型的索引，包括下面几种：

主键索引（Primary Key Index）：主键索引是一种唯一性索引，用于标识表中的每一行。每个表只能有一个主键索引，它可以跨多个列定义。

唯一索引（Unique Index）：唯一索引确保索引列中的值是唯一的，但允许包含空值。一个表可以有多个唯一索引。

普通索引（Normal Index）：普通索引是最基本的索引类型，它没有唯一性或主键的限制。一个表可以有多个普通索引。

全文索引（Full-Text Index）：全文索引用于在文本数据中进行全文搜索。它可以提供更高级的搜索功能，如关键字搜索和排序。

组合索引（Composite Index）：组合索引是基于多个列创建的索引。它可以提高多列查询的性能，但只有在查询中使用了索引的第一个列时才能发挥作用。

​ 创建主键索引语句：

代码语言：javascript代码运行次数：0运行复制alter table 表名 add index(列名);​ 删除主键索引语句：

代码语言：javascript代码运行次数：0运行复制alter table 表名 drop primary key; ​ 注意：mysql 的所有数据操作如增删查改，本质都是在内存中进行的（索引也是如此），但是它会定期的将数据刷新到外设比如说磁盘中进行保存做 持久化。

案例​ 下面我们先整一个有海量数据的表，看看在查询的时候有没有索引的区别！

代码语言：javascript代码运行次数：0运行复制drop database if exists `_index`;

create database if not exists `_index` default character set utf8;

use `_index`;

-- 构建一个8000000条记录的数据

-- 构建的海量表数据需要有差异性，所以使用存储过程来创建， 拷贝下面代码就可以了，暂时不用理解

-- 产生随机字符串

delimiter $$

create function rand_string(n INT)

returns varchar(255)

begin

declare chars_str varchar(100) default

'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

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(5)

begin

declare i int default 0;

set i = floor(10+rand()*500);

return i;

end $$

delimiter ;

-- 创建存储过程，向雇员表添加海量数据

delimiter $$

create procedure insert_emp(in start int(10),in max_num int(10))

begin

declare i int default 0;

set autocommit = 0;

repeat

set i = i + 1;

insert into EMP values ((start+i)

,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());

until i = max_num

end repeat;

commit;

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 '部门编号'

);

-- 执行存储过程，添加8000000条记录

call insert_emp(100001, 8000000); ​ 下面我们尝试着去查找员工编号为 998877 的员工：

​ 可以看到平均耗时大概四秒多，这还是在本机一个人来操作，在实际项目中，如果放在公网中，假如同时有 1000 个人并发查询，那很可能就死机。

​ 下面我们为 empno 也就是员工编号常见一个索引，然后再访问这个员工试试看效果：

​ 效果显著！对于创建索引的细节，我们后面会慢慢谈到！

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

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

题外话：

从上图可以看出来，在半径方向上，距离圆心越近，扇区越小，距离圆心越远，扇区越大那么，所有扇区都是默认 512 字节吗❓❓❓ 目前是的，我们也这样认为。因为保证一个扇区多大，是由比特位密度决定的。不过最新的磁盘技术，已经慢慢的让扇区大小不同了，不过我们现在暂时不考虑。​ 我们在使用 Linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的。(当然，有一些内存文件系统，如： proc ， sys 之类，我们不考虑)

​ 数据库文件，本质其实就是保存在磁盘的盘片当中，就是一个一个的文件。所以，找到一个文件的内容，本质就是在磁盘找到所有保存文件的扇区。而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。

定位扇区 柱面/磁道：多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面。每个盘面都有一个磁头，那么磁头和盘面的对应关系便是一对一的。所以我们只需要知道，磁头（Heads）、柱面（Cylinder，等价于磁道）、扇区（Sector）对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做 CHS 。不过实际系统软件使用的并不是 CHS （但是硬件是），而是 LBA ，一种线性地址，可以想象成虚拟地址与物理地址。系统将 LBA 地址最后会转化成为 CHS ，交给磁盘去进行数据读取。不过，我们现在 不关心转化细节，知道这个东西，让我们逻辑自洽起来即可。结论​ 我们现在已经能够在硬件层面定位任何一个基本数据块了，那么在系统软件上，直接按照扇区（512 字节，部分 4096 字节）进行 IO 交互可以吗❓❓❓

​ 答案是肯定不能，原因如下所示：

如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的 IO 代码，就和硬件强相关，换言之，如果硬件发生变化，系统必须跟着变化从目前来看，单次 IO 512 字节，还是太小了。IO 单位小，意味着读取同样的数据内容，需要进行多次磁盘访问，会带来效率的降低。之前我们学习过文件系统，就是在磁盘的基本结构下建立的，文件系统读取基本单位不是扇区，而是数据块。​ 所以 系统读取磁盘，是以块为单位的，基本单位是 4KB，如下图所示：

磁盘随机访问（Random Access）与连续访问（Sequential Access）随机访问：本次 IO 所给出的扇区地址和上次 IO 给出扇区地址不连续，这样的话磁头在两次 IO 操作之间需要作比较大的移动动作才能重新开始读/写数据。连续访问：如果当次 IO 给出的扇区地址与上次 IO 结束的扇区地址是连续的，那磁头就能很快的开始这次 IO 操作，这样的多个 IO 操作称为连续访问。​ 因此尽管相邻的两次 IO 操作在同一时刻发出，但如果它们的请求的扇区地址相差很大的话也只能称为随机访问，而非连续访问。而磁盘是通过机械运动进行寻址的，连续访问不需要过多的定位，故效率比较高。

Ⅲ. MySQL与磁盘交互基本单位 – page​ MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的 IO 场景，为了提高基本的 IO 效率， MySQL 进行 IO 的基本单位是 16 KB （后面统一使用 InnoDB 存储引擎讲解)

​ 我们可以通过 "innodb_page_size" 来查查看当前 mysql 的页大小：

代码语言：javascript代码运行次数：0运行复制mysql> show global status like 'innodb_page_size';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| Innodb_page_size | 16384 |

+------------------+-------+

1 row in set (0.01 sec)​ 也就是说磁盘这个 硬件设备的基本单位是 512 B，而 MySQL 中的 InnoDB 引擎使用 16KB 进行 IO 交互。即 MySQL 和磁盘进行数据交互的基本单位是 16 KB 。

​ 这个基本数据单元，在 MySQL 这里叫做 page（注意这和文件系统的 page 是不一样的）

​ 那么 MySQL 和操作系统、磁盘的关系大概就是如下图所示：

​ 一般来说，mysql 的缓冲区设置为 128MB，这可以在配置文件中设定！而一个 page 的大小是 16KB，那么相当于一个缓冲区最少可以放下八千多个 page！

Ⅲ. 建立共识MySQL 中的数据文件，是 以 page 为单位 保存在磁盘当中的。MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。 而只要涉及计算，就需要 CPU 参与。为了便于 CPU 参与，一定要能够先将数据移动到内存当中。所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。此时就涉及到磁盘和内存的数据交互，也就是 IO 了。而此时 IO 的基本单位就是 Page。为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了叫做 Buffer Pool 的大内存空间（也就是上图中的 mysql 缓冲区），来进行各种缓存。其实就是一块很大的内存空间，来和磁盘数据进行 IO 交互。为了更高的效率，一定要 尽可能的减少系统和磁盘 IO 的次数！Ⅳ. 理解索引1、索引的现象​ 下面建立一个测试表（记得下面 id 字段一定要添加主键哦，只有这样才会默认生成主键索引）：

代码语言：javascript代码运行次数：0运行复制create table if not exists user (

id int primary key,

age int not null,

name varchar(16) not null

);​ 然后不按 id 顺序的插入多条记录：

代码语言：javascript代码运行次数：0运行复制insert into user values(3, 18, '杨过'), (4, 16, '小龙女'), (2, 26, '黄蓉'), (5, 36, '郭靖'), (1, 56, '欧阳锋');

Query OK, 5 rows affected (0.01 sec)

Records: 5 Duplicates: 0 Warnings: 0​ 查看插入结果：

代码语言：javascript代码运行次数：0运行复制mysql> select * from user;

+----+-----+-----------+

| id | age | name |

+----+-----+-----------+

| 1 | 56 | 欧阳锋 |

| 2 | 26 | 黄蓉 |

| 3 | 18 | 杨过 |

| 4 | 16 | 小龙女 |

| 5 | 36 | 郭靖 |

+----+-----+-----------+

5 rows in set (0.00 sec)​ 这里就有一个现象：我们向具有主键的表中，乱序插入数据，发现数据会自动按照 id 大小排序，这是谁做的❓❓❓为什么这么做❓❓❓

​ 为了解答这些疑惑，我们就得重谈一下 page 也就是 mysql 中的一个页结构！

2、重谈 page​ 首先我们要先明白，为什么 MySQL 和磁盘进行 IO 交互的时候，要采用 Page 的方案进行交互呢，而不是用多少，就加载多少呢❓❓❓

​ 比如上面的五条记录，如果 MySQL 要查找 id = 2 记录的时候，先第一次加载 id = 1，然后第二次加载 id = 2，一次一条记录，那么就需要两次 IO。如果要找 id = 5 的记录，那么就需要五次 IO。

​ 但是如果这五条（或者更多）其实都被保存在一个 Page 中（一个 Page 是 16KB，能保存很多记录），那么第一次 IO 查找 id = 2 的时候，整个 Page 会被加载到 MySQL 的 Buffer Pool 也就是缓冲区中，这里完成了一次 IO。但是往后如果在查找 id=1,3,4,5 的时候，完全不需要进行 IO 了，而是直接在内存中进行了。简单地说，就是有了 Page 来存储这些索引记录的话，就能很好的利用局部性原理来减少 IO 次数！

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

① 理解单个 page​ MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要 先描述，在组织，所以，不要简单的认为 page 就是一个内存块，其实 page 的内部也必须要写入对应的管理信息，也就是用一个结构体管理起来，然后通过链表的形式，将每个 page 串联起来管理，而这个管理者就是 mysql 的缓冲区 Buffer Pool。

​ 单个 page 的结构大概如下图所示：

​ 不同的页在 mysql 中都是 16KB 大小，而每个页之间使用 prev 和 next 构成双向链表！

​ 因为有主键的关系， mysql 会默认按照主键给我们的数据进行排序，从上面的页内数据记录可以看出，数据是有序且彼此关联的。

​ 此时肯定就会有人问，那么我们创建表的时候不给表添加主键的话，最后还是按照 page 形式进行存储的吗❓❓❓

​ 答案是肯定的！如果我们不给一个表添加主键的话，那么 mysql 会默认给表添加一个主键，然后按照这个隐藏的主键进行排序和存储，但是我们在查询该表的某个记录的时候，就不是按照索引的方式查询了，只是在增删改的时候会 mysql 会根据隐藏主键去调整结构而已！

​ 而 插入数据时排序的目的，就是为了优化查询的效率。

​ 因为页结构体内部存放数据的模块，实质上也是一个链表的结构，而链表的特点也就是增删快，但是查询修改慢，所以优化查询的效率是必须的。正是因为有序，所以在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的（因为 局部性原理，数据热加载到内存中，如果大部分都是刚好想要访问的数据，那么就 减少了去磁盘读取其它页的次数，提高效率），并且如果运气好的话，是可以提前结束查找过程的！

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

​ 但是，我们也可以看到，现在的 页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据，在极端情况下，效率其实也不是很好，但是这个问题可以通过 页目录 来优化，这个我们下面会讲！

​ 此外，如果一个表中有一千万条数据，那么一定需要多个 Page 来保存一千万条数据，然后多个 Page 彼此使用双链表链接起来，而且每个 Page 内部的数据也是基于链表的。

​ (上面的 Page 结构其实画的不太应景，因为我们还没介绍页目录，不过这里就提前放出来看看！因为是内部数据也是链表结构，所以查找特定一条记录，一定是线性查找，这效率也太低了！所以我们必须引出 页目录 的概念！)

3、页目录​ 当我们在想要查找一本书的某一个知识点的时候，如果没有目录的话，我们只能从头逐页往后翻，直到找到想要的内容。但是如果有目录的话，我们的查询速度就会非常的快，因为目录帮助我们标记了书本每一章的知识点的页数，我们只需要根据这个页数翻过去就能找到对应的内容！

​ 本质上，书中的目录确实是多花了一些纸张用来，却提高了效率，我们在计算机中管这种做法叫做 空间换时间。

① 单页情况​ 对于 page 结构中的数据来说，同样是可以用空间换时间的做法，在 page 结构中其实存在目录一些数据记录，但是这些记录不是保存用户等信息，而是 保存了指向某个数据的索引，如下图所示：

​ 这样子一来好处就显露出来了，当我们去查找三号信息时候，我们就不需要从数据记录的开头遍历，而是先去查看页目录中的索引，判断要查找的 id 落在哪个索引上，就直接通过该目录索引到该数据记录上，就像这里的目录二，存放的就是三号信息的为止，此时直接就跳到了三号信息位置处！

​ 而如果要查找的是四号信息的话，那么就可以从目录二跳到三号信息，然后再往下遍历！

​ 可能就会有人问了，这样子好像看不出来快在哪里呀❓❓❓

​ 是的，当数据量小的时候，目录的作用不是很大，但是一旦数据量大起来了，那么作用就不容小觑了！

​ 现在我们就能回答上面索引现象留下来的一个问题了：我们向具有主键的表中，乱序插入数据，发现数据会自动按照 id 大小排序，这是谁做的❓❓❓为什么这么做❓❓❓

​ 很明显就能看到是 mysqld 服务端帮我们做的排序，而做 排序的目的，就是为了引入页目录，就是为页目录提供能定位的排序条件！

② 多页情况​ 因为 mysql 中每一页的大小只有 16KB ，又因为单个页的大小是固定的，所以随着数据量不断增大，16KB 也不可能存下所有的数据，那么必定会有多个页来存储数据。

​ 这我们在上面已经提到过了，就是页和页之间是以链表形式链接起来的！

​ 需要注意，上面的图是理想结构，因为是举例需要，要保证整体有序，其实新插入的数据，不一定会在新 Page 中，这里仅仅做演示！

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

​ 这样，我们就可以通过多个页遍历，页内部通过目录来快速定位数据。可是，貌似这样也有效率问题啊，在页之间，也是需要 mysql 遍历的，而遍历意味着依旧需要进行大量的 IO，mysql 如果遍历该页之后，发现该页没有存在想要的数据，那么会将下一个页加载到内存进行遍历。这样就 显得我们之前的 Page 内部的目录，有点杯水车薪了。

​ 并且我们之前也说过，mysql 的缓冲区 Buffer Pool 有 128MB，相当于可以容纳八千多个页，如果极端场景下就是想要遍历这么多个页的话，那么这个效率真的就看不下去了！

​ 那么如何解决呢❓❓❓

​ 解决方案其实就是我们之前的思路：使用页目录，但是这次是给整个 Page 带上目录！如下图所示：

存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。只要有数据的话就可以通过键值的比较，找到该访问那个 Page，进而通过指针，找到下一个 Page，以此循环。和页内目录项的不同地方在于：这种目录管理的级别是页，而**页内目录管理的级别是行**。其中，每个目录项的构成是：键值+指针（上图中并没有画全）​ 可以看出 目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。

​ 并且一个页用来存放目录页，是可以存放很多的，因为一个页可以存放 16KB，那么一个键值加上指针，加上其它一些管理数据，顶多一个指向也就是十几个字节，这样子算下来一个页就能存放上千个目录页，相当于是课本中多个章节被划分为了一个单元，而一本书有多个单元的意思！

​ 这样子的话 能存放更多的目录页的话，侧面说明了 IO 的次数就能大大减少了，很好的提高了效率！

​ 可是，我们每次检索数据的时候，该从哪里开始呢，虽然顶层的目录页少了许多，但是这样子的话还是需要遍历啊❓❓❓

​ 不用担心，我们 可以再加一层目录页，如下图所示：

​ 从上图我们也可以看出，其实 mysql 中 InnoDB 存储引擎的索引结构，就是我们数据结构所学的 B+树 结构！

​ 那 B+ 树结构意味着有什么优势呢❓❓❓

首先在 B+树中数据都是存放在叶子节点中的，也就是说非叶子节点中没有存放数据，只有目录项，这意味着非叶子节点可以存储更多的目录项，而只要目录页一多的话，那么这棵树，就会越趋向于 ”矮胖型“ 的树，即深度很小的树，换言之就是 IO 次数非常少。此外，B+树的 叶子节点是以链表的形式串联起来的，这在范围查询的时候是非常有用的。且对于没有索引的字段进行全文扫描的时候也是很方便的。4、为什么不选择其它数据结构做数据库索引❓❓❓​ 我们学过多种搜索结构，如下表：

​ 但是以上结构适合用于数据量不是很大的情况，如果数据量非常大，一次性无法加载到内存中，所以使用上述结构就不是很方便。比如：使用平衡树搜索一个大文件

​ 上面方法其实只在内存中保存了每一项数据信息中需要查找的字段以及数据在磁盘中的位置，整体的数据实际也在磁盘中。

使用平衡二叉树搜索树的缺陷： 平衡二叉树搜索树的高度是 log_2 n，这个查找次数在内存中是很快的。但是当数据都在磁盘中时，访问磁盘速度很慢，在数据量很大时，

log_2 n 次的磁盘访问，是一个难以接受的结果，因为深度太大了，这样子会导致 IO 次数过多而效率低！

使用哈希表的缺陷： 哈希表的查找时间复杂度很优秀，为 O(1)，但是一些极端场景下某个位置冲突很多，导致访问次数剧增，也是难以接受的。并且哈希表对于范围查找的支持不是很友好，这点就很打击了！5、为什么说B+树比B-树更适合做数据库索引❓❓❓​ B- 树的结构如下所示：

B+树的磁盘读写代价更低 B+ 树的分支节点并没有指向关键字具体信息的指针，因此其非叶子节点相对 B- 树更小。如果把同一分支节点的关键字放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读取内存中的需要查找的关键字也就越多，相对来说 IO 读写次数也就降低了。简单地说，就是 B+树的分支节点全都是索引值，占用的空间更小，这样子的话可以更多的加载到缓存 Cache 中去，提高速度！B+树查询效率更稳定 由于非终端节点并不是最终指向文件内容的节点，而只是叶子节点关键字的索引。索引任何关键字的查找必须走一条根节点到叶子节点的路。因为 所有关键字查询的路径长度相同，所以每一个数据的查询效率相当。B+树便于范围查询（最重要的原因，范围查找是数据库的常态） B-树在提高了 IO 性能的同时并没有解决掉元素遍历低下的问题，正是为了解决这个问题，B+树应用而出，B+ 树只需要去遍历叶子节点就可以实现整棵树的遍历。而且再数据库中基于范围的查询是非常频繁的，而 B- 树不支持这样的操作或者说效率太低。B-树的范围查找用的是 中序遍历，而B+ 树用的是在 链表上遍历​ B+树是什么

​

Ⅴ. 聚簇索引 && 非聚簇索引1、两者的区别聚簇索引（Clustered Index）和非聚簇索引（Non-clustered Index）是数据库中两种常见的索引类型，它们在存储和查询数据时有一些区别。

存储方式： 聚簇索引：聚簇索引决定了数据在磁盘上的物理存储顺序。表中的数据按照聚簇索引的键值进行排序，并且相邻的数据行在磁盘上也是相邻存储的。每个表只能有一个聚簇索引。非聚簇索引：非聚簇索引是独立于实际数据存储顺序的索引结构。它包含索引键值和指向实际数据行的指针。一个表可以有多个非聚簇索引。数据访问方式： 聚簇索引：由于数据按照聚簇索引的键值进行排序，因此通过聚簇索引可以快速地获取到满足特定条件的数据行，所以聚簇索引适合于范围查询和顺序访问。非聚簇索引：非聚簇索引通过索引键值和指针来定位实际数据行。当使用非聚簇索引进行查询时，数据库需要先通过索引找到对应的数据行，然后再访问实际数据，所以非聚簇索引适合于精确查找和排序查询。索引的更新： 聚簇索引：由于数据的物理存储顺序与聚簇索引的键值相关，因此对 聚簇索引的更新可能会导致数据的物理重排，这意味着对聚簇索引的频繁更新可能会影响性能。非聚簇索引：对非聚簇索引的更新不会导致数据的物理重排，因为索引和实际数据是分开存储的。因此，对非聚簇索引的更新不会对数据的物理存储顺序产生影响。​ 简单地说，聚簇索引就是索引和数据放在同一颗树的节点中，更适合于范围查询和顺序访问；而 非聚簇索引就是索引和数据分开存储，更适合于精确查找和排序查询。

2、MyISAM存储引擎 – 非聚簇索引​ MyISAM 引擎是 MySQL5.5.8 版本之前默认的存储引擎，不支持事物，但支持全文检索。其使用 B+树作为索引结构，叶节点的 data 域存放的是数据记录的地址。

​ 下图为 MyISAM 表的主索引，其中 Col1 是主键：

​ 其中，MyISAM 存储引擎最大的特点就是，将索引 Page 和数据 Page 分离，也就是叶子节点没有数据，只有对应数据的地址。

​ 而相较于 InnoDB 索引，InnoDB 存储引擎是将索引和数据放在一起的，这个我们下面会讲！

​ 下面我们简单的创建一张 MyISAM 存储引擎的表来看看其文件的类型：

代码语言：javascript代码运行次数：0运行复制mysql> create table myisam_test(

-> id int primary key,

-> name varchar(11) not null

-> )engine=MyISAM; --使用engine=MyISAM存储引擎

Query OK, 0 rows affected (0.00 sec)

[root@VM-8-7-centos test]# ll

total 20

-rw-r----- 1 mysql mysql 61 Aug 3 20:12 db.opt

-rw-r----- 1 mysql mysql 8586 Aug 4 10:15 myisam_test.frm --表结构数据

-rw-r----- 1 mysql mysql 0 Aug 4 10:15 myisam_test.MYD --该表对应的数据，当前没有数据，所以是0

-rw-r----- 1 mysql mysql 1024 Aug 4 10:15 myisam_test.MYI --该表对应的主键索引数据​ 当然，MySQL 除了默认会建立主键索引外，一般我们也有可能按照其它列信息建立索引，这种索引可以叫做普通索引。而对于 MyISAM，建立普通索引和主键索引本质没有什么差别，无非就是主键不能重复并且具有唯一性，而非主键可重复且可有多个。

​ 简单地说，普通索引也是会创建其独立的一颗索引树的！

​ 下图就是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别：

​ 这个普通索引同样也是一棵 B+树，其中 data 域保存数据记录的地址。因此，MyISAM 中索引检索的算法会首先按照 B+ 树搜索算法进行索引，如果指定的 Key 存在，则直接取出其 data 域的值，然后以 data 域的值为地址，读取表中的相应数据记录。

​ MyISAM 存储引擎的索引方式也叫做 非聚集索引，这是为了 方便索引树和主键树可以映射同样的数据。

​ 但是现在的版本就不再使用 MyISAM 为默认索引引擎了，因为它 不支持事务，而要知道的是我们现在生活中存在着大量的事务。比如说微信支付，这个时候要考虑收入和支出的问题，还有就是如果支付失败了，那么钱得返回去，这些就是事务的例子，这也是为什么 MyIASM 会被取消默认索引引擎的理由。而我们下面要介绍的 InnoDB 是支持事务的！

💥需要注意的是，表只有一张，而索引树是有多颗的，它们指向同一张表！

3、InnoDB存储引擎 – 聚簇索引​ InnoDB 存储引擎 支持事务，其设计目标主要面向在线事务处理的应用，从 MySQL 5.5.8 版本开始，InnoDB 存储引擎是默认的存储引擎。其支持 B+ 树索引、全文索引、哈希索引。

​ 但 InnoDB 使用 B+ 树作为索引结构时，具体实现方式却与 MyISAM 存储引擎截然不同，体现在节点的存储内容上。

​ 它们的区别如下所示：

第一个区别是 InnoDB 的索引文件本身就是数据文件，而 MyISAM 的索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而 InnoDB 索引的数据文件本身就是按 B+树组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。这个索引的 key 是数据表的主键，因此 InnoDB 的数据文件本身就是主索引。

​ 第二个区别是 InnoDB 的辅助索引 data 域存储相应记录主键的地址而不是值，所有辅助索引都引用主键作为 data 域。

​ 上述内容看起来有点绕，但其实不难，好好听这段解释就懂了！

​ 以下图为例，就是以 id 为主键，其中 叶子节点数据本身就是一个表的一部分数据，这样子的话方便我们对数据的操作。同时若是有其它索引，比如下图中的 name 为辅助索引，那么我们不可能在 name 的索引树里面也存一份完整的数据呀，那也 太浪费空间了！

​ 所以一个很妙的方法就是 除了主键树以外的其他索引树的叶子节点中存放的都是指向主键树叶子节点的地址，而通过索引树找到对应的地址再到主键树去查找对应数据的时候，而这个过程叫做 回表！

​ 所以通过辅助索引 name，可以找到目标记录，然后需要进行两遍索引：首先检索辅助索引获得主键，然后用主键到主索引树中检索获得最终的完整数据记录！

​ 可以看到主键树中叶子节点包含了完整的数据记录，这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集，所以 InnoDB 要求表必须有主键（ MyISAM 可以没有主键）。

​ 如果 InnoDB 没有显式指定主键的话，则 MySQL 系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则 MySQL 自动为 InnoDB 表生成一个隐含字段作为主键，这个字段长度为 6 个字节，类型为长整型。

​ 最后我们简单的创建一张 InnoDB 存储引擎的表来看看其文件的类型：

代码语言：javascript代码运行次数：0运行复制mysql> create table innodb_test(

-> id int primary key,

-> name varchar(10) not null

-> )engine=InnoDB; --使用engine=InnoDB

Query OK, 0 rows affected (0.02 sec)

[root@VM-8-7-centos test]# ll

total 112

-rw-r----- 1 mysql mysql 61 Aug 3 20:12 db.opt

-rw-r----- 1 mysql mysql 8586 Aug 4 10:49 innodb_test.frm --表结构数据

-rw-r----- 1 mysql mysql 98304 Aug 4 10:49 innodb_test.ibd --该表对应的主键索引和用户数据，虽然现在一行数据没有，但是该表并不为0，因为有主键索引数据4、两种存储引擎的区别区别项

InnoDB

MyISAM

事务

支持

不支持

锁粒度

行锁，适合高并发

表锁，不适合高并发

是否默认

默认

非默认

支持外键

支持外键

不支持

适合场景

读写均衡。适合写大于读、需要事务的场景

适合读多写少、不需要事务的场景

全文索引

可以通过插件实现

支持

​ 其它区别项会在后面的学习中也体现出来！

Ⅵ. 索引的操作1、索引的创建原则​ 索引最大的好处是提高查询速度，但是索引也是有缺点的，比如：

需要占用物理空间，数量越大，占用空间越大；创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。​ 所以，索引并不是万能钥匙，它也是根据场景来使用的。

什么时候适用创建索引❓❓❓字段有唯一性限制的，适用创建索引，比如商品编码。经常用于 where 查询条件的字段。这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。经常用于 group by 和 order by 的字段。这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+ 树中的记录都是排序好的。什么时候不适合创建索引❓❓❓表数据太少 的时候，不适合创建索引。where 条件、group by 和 order by 里用不到的字段，不适合创建索引。索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。字段中存在大量重复数据，不适合创建索引。比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。经常需要更新的字段，不适合创建索引。比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 **B+**树的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。唯一性太差 的字段不适合单独创建索引，即使该字段频繁地作为查询条件也不适合。2、查询索引​ 下面我们先讲查询索引和删除索引的操作，因为情况比较少！

​ 查询操作一般有三种方式：

第一种方法：

代码语言：javascript代码运行次数：0运行复制show keys from 表名;第二种方法：

代码语言：javascript代码运行次数：0运行复制show index from 表名;第三种方法（显示信息比较简略）：

代码语言：javascript代码运行次数：0运行复制desc 表名;​ 下面以第一种方式举个例子：

代码语言：javascript代码运行次数：0运行复制mysql> show keys from innodb_test\G;

*************************** 1. row ***************************

Table: innodb_test -- 表名

Non_unique: 0 -- 0表示具有唯一性

Key_name: PRIMARY -- 主键索引的名字

Seq_in_index: 1

Column_name: id -- 表示索引是在哪个列字段

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE -- 以B+树形式的索引

Comment:

Index_comment:

1 row in set (0.00 sec)3、删除索引① 删除主键索引代码语言：javascript代码运行次数：0运行复制alter table 表名 drop primary key;② 删除其它索引​ 对于其它索引来说，删除操作有两种方式：

代码语言：javascript代码运行次数：0运行复制alter table 表名 drop index 索引名;​ 或者下面这种：

代码语言：javascript代码运行次数：0运行复制drop index 索引名 on 表名;

​ 注意，主键的索引删除操作，只能单独使用主键索引特定的删除操作，我们可以实验一下，这里已经提前知道主键的名字为 PRIMARY：

代码语言：javascript代码运行次数：0运行复制-- 使用删除其它索引的方式一，结果失败

mysql> drop index PRIMARY on t;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY on t' at line 1

-- 使用删除其它索引的方式二，结果失败

mysql> alter table t drop index PRIMARY;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIMARY' at line 1

-- 使用删除主键索引特定的方式，结果删除成功！

mysql> alter table t drop primary key;

Query OK, 0 rows affected (0.05 sec)

Records: 0 Duplicates: 0 Warnings: 0​ 这可能是因为主键是唯一的原因才有这种特定的删除方式吧~

4、创建索引① 创建主键索引​ 第一种方式：在创建表的时候，直接在字段名后指定 primary key：

代码语言：javascript代码运行次数：0运行复制create table user1(

id int primary key,

name varchar(30)

);​ 第二种方式：在创建表的最后，指定某列或某几列为主键索引（注意主键还是只有一个，只不过是复合的主键）：

代码语言：javascript代码运行次数：0运行复制create table user2(

id int,

name varchar(30),

primary key(id)

);​ 第三种方式：创建表以后再添加主键：

代码语言：javascript代码运行次数：0运行复制alter table 表名 add primary key(列名);主键索引的特点：

一个表中只能有一个主键索引，当然可以使用复合主键主键索引的效率高主键值不允许重复，且不能为 null主键索引的列最好是整型比如 int主键索引的列 最好是自增类型，这样子能最大化的利用好索引的优势② 创建唯一键索引​ 第一种方式：在表定义时，在某列后直接指定 unique 唯一属性：

代码语言：javascript代码运行次数：0运行复制create table user4(

id int primary key,

name varchar(30) unique

);​ 第二种方式：创建表时，在表的后面指定某列或某几列为**unique**：

代码语言：javascript代码运行次数：0运行复制create table user5(

id int primary key,

name varchar(30),

unique(name)

);​ 第三种方式：创建表以后再添加主键：

代码语言：javascript代码运行次数：0运行复制alter table 表名 add unique(列名);唯一索引的特点：

一个表中，可以有多个唯一索引查询效率高如果在某一列建立唯一索引，必须保证这列不能有重复数据如果一个唯一索引上指定 not null，等价于主键索引③ 创建普通索引​ 第一种方式：在表的定义最后，指定某列为索引：

代码语言：javascript代码运行次数：0运行复制create table user8(

id int primary key,

name varchar(20),

email varchar(30),

index(name) --在表的定义最后，指定某列为索引

);​ 第二种方式：创建完表以后指定某列为普通索引：

代码语言：javascript代码运行次数：0运行复制alter table 表名 add index(列名);​ 第三种方式：创建表以后再添加普通索引，并且可以对索引进行命名：

代码语言：javascript代码运行次数：0运行复制create index 索引名 on 表名(列名);普通索引的特点：

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引④ 创建全文索引 – match && against​ 当对文章字段或有大量文字的字段进行检索时，就可以使用全文索引。例如在新闻文章、博客帖子、产品描述等文本内容中进行搜索。它可以帮助用户快速找到与他们的查询相关的文档。

​ MySQL 提供全文索引机制，但是有要求，要求表的存储引擎必须是 MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用 sphinx 的中文版(coreseek)。

添加全文索引操作代码语言：javascript代码运行次数：0运行复制-- 第一种方式

add fulltext index 索引名 on 表名 (column1, column2, ...);

-- 第二种方式

alter table 表名 add fulltext 索引名 on column1, column2, ...;

-- 第三种方式

create index 索引名 on 表名 (column1, column2, ...);

-- 第四种方式：直接在创建表的时候指定

fulltext (column1, column2, ...);​ 下面我们用第四种方式来测试一下：

代码语言：javascript代码运行次数：0运行复制-- 插入一张MyISAM存储引擎的表

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

)engine=MyISAM;

-- 插入数据

INSERT INTO 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 ...');​ 首先我们使用以前学过的模糊匹配来进行文本筛选：

代码语言：javascript代码运行次数：0运行复制mysql> select * from articles where body like '%database%';

+----+-------------------+------------------------------------------+

| id | title | body |

+----+-------------------+------------------------------------------+

| 1 | MySQL Tutorial | DBMS stands for DataBase ... |

| 5 | MySQL vs. YourSQL | In the following database comparison ... |

+----+-------------------+------------------------------------------+

2 rows in set (0.00 sec)​ 看似我们好像把需要的文本该检索出来了，但是其实用的并不是全文索引，只是普通遍历而已！

​ 这我们可以使用 mysql 提供的 explain 工具分析一下当前的语句，是否用到了索引：

代码语言：javascript代码运行次数：0运行复制mysql> explain select * from articles where body like '%database%' \G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: articles

partitions: NULL

type: ALL

possible_keys: NULL

key: NULL --表示没有用到索引

key_len: NULL

ref: NULL

rows: 6

filtered: 16.67

Extra: Using where

1 row in set, 1 warning (0.00 sec)查询全文索引操作​ 既然上面没有用到索引，那么如何使用全文索引呢❓❓❓

​ 这里就需要使用 match 和 against 关键字来配合，其语法如下所示：

代码语言：javascript代码运行次数：0运行复制select 列名 from 表名

where match(column1, column2, ...) against('search_expression' [in 'search_mode'] );​ 其中 column1, column2, ... 是要搜索的列名，search_expression 是要搜索的文本内容，search_mode是搜索模式，可以是全文检索模式（默认模式）或模糊匹配模式。

​ 测试如下所示：

代码语言：javascript代码运行次数：0运行复制mysql> select * from articles where match(title, body) against('database');

+----+-------------------+------------------------------------------+

| id | title | body |

+----+-------------------+------------------------------------------+

| 5 | MySQL vs. YourSQL | In the following database comparison ... |

| 1 | MySQL Tutorial | DBMS stands for DataBase ... |

+----+-------------------+------------------------------------------+

2 rows in set (0.00 sec)

-- 使用explain进行分析语句

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

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: articles

partitions: NULL

type: fulltext

possible_keys: title

key: title --可以看到，查找方式是通过索引的

key_len: 0

ref: const

rows: 1

filtered: 100.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)5、复合索引​ 上面我们的操作都是将单个字段设置为索引，其实我们也是可以通过将多个字段组合成一个索引的，该索引就被称为复合索引（联合索引）。

​ 比如，将商品表中的 product_no 和 name 字段组合成联合索引 (product_no, name)，创建联合索引的方式如下：

代码语言：javascript代码运行次数：0运行复制create index index_product_no_name on product(product_no, name); ​ 可以看到，联合索引的非叶子节点用两个字段的值作为 B+ 树的 key 值。当在联合索引查询数据时，先按 product_no 字段比较，在 product_no 相同的情况下再按 name 字段比较。

​ 也就是说，联合索引查询的 B+ 树是先按 product_no 进行排序，然后再 product_no 相同的情况再按 name 字段排序。

​ 因此，使用联合索引时，存在 最左匹配原则，也就是 按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。

​ 最左匹配原则 是指在数据库中使用复合索引进行查询时，索引会按照索引字段的顺序进行匹配。当查询条件中包含多个字段时，索引会从左到右逐个匹配字段，直到找到第一个不匹配的字段为止。这意味着，如果查询条件中只使用了索引的前缀字段，那么索引可以被充分利用；而如果查询条件中使用了索引的后续字段，那么索引的效率会降低。

​ 比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；where a=1 and b=2 and c=3；where a=1 and b=2； ​ 需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

​ 但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效：

where b=2；where c=3；where b=2 and c=3； ​ 上面这些查询条件之所以会失效，是因为 (a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到复合索引的！

Ⅶ. 索引覆盖​ 既然我们上面提到了复合查询以及全文索引，那么我们可以来讨论一下如何优化它们！就是通过下面的索引覆盖来优化！

​ 索引覆盖（Index Covering）是数据库中一个重要的概念，它指的是一个查询可以完全通过过程中的索引来执行，而无需访问实际的数据行。这可以大大提高查询的性能，因为数据库引擎 可以直接从索引节点中获取所需的数据，而无需额外的磁盘访问。

​ 这是因为当一个查询需要访问数据库中的某些数据时，通常需要扫描表中的行，这可能会导致较高的 IO 开销和查询时间。然而，如果查询的列在一个或多个索引中都有覆盖，那么数据库可以直接使用索引中的数据，而无需访问实际的数据行，从而提高查询性能。

​ 索引覆盖的好处包括：

减少 IO 开销：通过避免扫描实际的数据行，可以减少磁盘 IO 操作，提高查询性能。减少 CPU 开销：索引通常比实际的数据行小，因此在内存中处理索引数据比处理实际数据行更高效。减少网络开销：如果数据库是分布式的，索引覆盖可以减少从存储节点到查询节点之间的数据传输量。​ 要实现索引覆盖，查询中涉及的列必须在索引中都有覆盖。这意味着索引必须包含查询中涉及的所有列，或者是一个包含所有查询列的覆盖索引。此外，查询的条件也必须与索引的列匹配，以便数据库可以有效地使用索引来执行查询。

​ 需要注意的是，并非所有的查询都适合索引覆盖。在某些情况下，索引覆盖可能会导致索引变得过大，从而降低性能。因此，在设计数据库索引时，需要综合考虑查询的特点和性能需求，以确定是否使用索引覆盖。

举例​ 以下是一个简单的例子来说明索引覆盖的概念。假设我们有一个包含以下列的数据库表：

代码语言：javascript代码运行次数：0运行复制create table Customers (

CustomerID INT primary key,

Name VARCHAR(50),

Phone VARCHAR(20)

);​ 现在，我们想要查询所有姓氏为 Smith 的客户的电话号码。我们可以创建一个索引来加快这个查询：

代码语言：javascript代码运行次数：0运行复制create index idx_Name on Customers (Name);​ 如果我们只创建了上述索引，那么执行以下查询时，数据库引擎将需要访问实际的数据行来获取电话号码：

代码语言：javascript代码运行次数：0运行复制select Phone from Customers where Name = 'Smith';​ 但是，如果我们创建一个覆盖索引（也就是一个多列的索引），该索引包含了我们查询要查询的结果列字段，那么数据库引擎就可以直接从索引中获取电话号码，而无需访问实际的数据行。这可以通过以下方式创建覆盖索引：

代码语言：javascript代码运行次数：0运行复制create index idx_Name on Customers (Name, Phone);​ 现在，当执行相同的查询时，数据库引擎可以通过覆盖索引来获取电话号码，而无需额外的磁盘访问。这将大大提高查询的性能！

​ 上图画的不是很标准，但是这里只是想体现出覆盖索引的特点