【MySQL数据库】：MySQL索引特性

目录

索引的概念

磁盘

磁盘的基本特征

MySQL与磁盘交互的基本单位

索引的理解

建立测试表

理解单个Page

理解多个Page  

页目录

单页情况

多页情况

索引的数据结构

聚簇索引 VS 非聚簇索引

索引操作

创建主键索引

 创建唯一索引

创建普通索引

 创建全文索引

查询索引

 删除索引

索引创建原则

索引的概念

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

常见的索引分为：

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

我们可以说主键（primary key）和唯一键（unique）是索引的具体应用形式，它们在实现数据的特定约束和提高查询性能方面发挥着重要作用，而索引为它们以及其他查询操作提供了基础支撑。比如，在通过主键或唯一键查找数据时，就是利用了它们所对应的索引来快速定位。同时，也可以根据具体需求创建其他类型的索引来进一步优化查询性能。

案例实现

使用如下SQL创建一个海量数据表：

drop database if exists `index_demon`;
create database if not exists `index_demon` default character set utf8;
use `index_demon`;-- 构建一个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);

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

将上述SQL保存到文件 index.sql 中，然后在MySQL中使用source命令依次执行文件中的SQL即可。

 SQL执行完毕后查看数据库就能看到一个名为index_demon的数据库，并且数据库中可以看到一个名为EMP的员工表。

由于EMP表中有八百万条记录，因此在查看EMP表中的数据时可以带上limit子句。并且目前EMP员工表中没有建立任何索引。 

查询EMP表中指定工号的员工信息，可以看到每次查询过程都需要花费1.63秒左右。 

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

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

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

磁盘

磁盘的基本特征

磁盘是一种存储设备，主要用于计算机系统中数据的存储。

它通常由磁性材料制成的盘片组成，通过磁头在盘片上读写数据。磁盘可以分为硬盘和软盘等类型，具有较大的存储容量，可以长期保存数据。

• 一个磁盘有很多盘片！！
• 一个盘片有很多的同心磁道！！
• 一圈磁道有很多的扇形的扇区 ！！
• 扇区是磁盘的最小的存储单元 ------- 512字节（0.5kb）

如果我想向一个扇区进行写入，我应该如何去寻址！！！

• 柱面号：确定扇区所在的柱面位置。
• 磁头号（哪一盘片）：确定读写头所在的磁头位置。
• 扇区号：确定具体要写入的扇区编号。

通过以上三个步骤，最终确定信息在磁盘的读写位置。柱面号（Cylinder）、磁头号（Head）和扇区号（Sector），简称为CHS定位法。

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

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

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

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

磁盘的随机访问（Random Access）与连续访问（Sequential Access）

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

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

MySQL与磁盘交互的基本单位

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

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

 SHOW GLOBAL STATUS LIKE 'innodb_page_size';

建立共识

建立测试表

create table if not exists user (
id int primary key, --一定要添加主键哦，只有这样才会默认生成主键索引
age int not null,
name varchar(16) not null
);

向表中插入一些数据，并且插入数据时没有按照主键的大小顺序插入。

 最终当我们查看表中的数据时，却发现显示出来的数据是按照主键进行有序排列的。

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

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

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

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

理解单个Page

• MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要 先描述，在组织 ,我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的

注意： 

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

为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？

理解多个Page  

• 通过上面的分析，我们知道，上面页模式中，只有一个功能，就是在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
• 如果有1千万条数据，一定需要多个Page来保存1千万条数据，多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，也一定是线性查找。这 效率也太低了。

页目录

我们在阅读书籍的时候

• 从头逐页的向后翻，直到找到目标内容
• 通过书提供的目录，发现章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的方案，可以顺序找，不过因为目录肯定少，所以可以快速提高定位
• 本质上，书中的目录，是多花了纸张的，但是却提高了效率
• 所以，目录，是一种“空间换时间“的做法

单页情况

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

那么当前，在一个 Page 内部，我们引入了目录。比如，我们要查找 id=4 记录，之前必须线性遍历 4 次，才能拿到结果。现在直接通过目录2[3] ，直接进行定位新的起始位置，提高了效率。

注意：

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

多页情况

MySQL 中每一页的大小只有 16KB ，单个 Page 大小固定，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。

1. 在单表数据不断被插入的情况下， MySQL 会在容量不足的时候，自动开辟新的Page来保存新的数据，然后通过指针的方式，将所有的Page组织起来。
2. 需要注意，上面的图，是理想结构，大家也知道，目前要保证整体有序，那么新插入的数据，不一定会在新Page上面，这里仅仅做演示。
3. 这样，我们就可以通过多个Page遍历，Page内部通过目录来快速定位数据。可是，貌似这样也有效率问题，在Page之间，也是需要 MySQL 遍历的，遍历意味着依旧需要进行大量的IO，将下一个Page加载到内存，进行线性检测。这样就显得我们之前的Page内部的目录，有点杯水车薪了。

那么如何解决呢？解决方案，其实就是我们之前的思路，给 Page 也带上目录。

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

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

索引的数据结构

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

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

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

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

 

聚簇索引 VS 非聚簇索引

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

MyISAM 存储引擎-主键索引

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

MyISAM 引擎同样使用 B+ 树作为索引结果，叶节点的 data 域存放的是数据记录的地址。下图为 MyISAM表的主索引， Col1 为主键。

其中， MyISAM 最大的特点是，将索引 Page 和数据 Page 分离，也就是叶子节点没有数据，只有对应数据的地址。 相较于 InnoDB 索引， InnoDB 是将索引和数据放在一起的。

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

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

MyISAM存储引擎的普通索引结构，其中Col2为索引列。

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

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

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

可以看到， InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的 key 值。

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

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

原因就是太浪费空间了。

注意：

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

索引操作

创建主键索引

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

方式二：在创建表的最后，指定某列或某几列为主键索引

方式三：创建表以后再添加主键

主键索引的特点

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

 创建唯一索引

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

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

方式三：创建表以后再添加唯一键

唯一索引的特点

• 一个表中，可以有多个唯一索引，一个唯一键可以由多个列同时承担。
• 唯一索引的查询效率高。
• 创建唯一索引的列，其列值可以为NULL，但是不能重复。
• 如果给唯一索引设置NOT NULL属性，则等价于主键索引。

创建普通索引

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

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

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

普通索引的特点

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

 创建全文索引

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

全文索引的案例 

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

我们向articles表中插入一些数据！！！

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

虽然查询出数据，但是没有使用到全文索引

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

 可以用 explain 工具看一下，是否使用到索引 ，key 为 null 表示没有用到索引 ！！

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

如何使用全文索引呢？ 

 SELECT * FROM articles where match (title,body) against ('database');

通过explain来分析这个sql语句，key用到了title！！！

注意： 

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

查询索引

方式一：使用show keys from 表名SQL查询，比如查询articles表中的索引信息。

方式二：使用show index from 表名SQL查询，比如查询articles表中的索引信息。

方式三：使用desc 表名SQL查询（信息比较简略）。

 删除索引

删除主键索引

alter table 表名 drop primary key

删除非主键索引 

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

索引创建原则

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

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