【MySQL】索引特性

---

一、索引的概念

数据库表中存储的数据都是以记录为单位的，如果在查询数据时直接一条条遍历表中的数据记录，那么查询的时间复杂度将会是 O(N)。

索引 的价值在于提高海量数据的检索速度，只要执行了正确的创建索引的操作，查询速度就可能提高成百上千倍。当一张表创建索引后，在数据库底层就会为表中的数据记录构建特定的数据结构，后续在查询表中数据时就能通过查询该数据结构快速定位到目标数据。

索引虽然提高了数据的查询速度，但在一定程度上也会降低数据增删改的效率，因为这时在对表中的数据进行增删改操作时，除了需要进行对应的增删改操作之外，可能还需要对底层建立的数据结构进行调整维护。

常见的索引分为：主键索引（primary key）、唯一索引（unique）、普通索引（index）、全文索引（fulltext）。

---

索引的价值

先创建一张海量表，在查询的时候，看看没有索引时有什么问题？

-- index_data.sql
drop database if exists `bit_index`;
create database if not exists `bit_index` default character set utf8;
use `bit_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);

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

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

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

这里我们看到每次查询指定工号的员工信息，都需要花费5秒多的时间。

当我们给员工表中的工号建立索引后，数据库底层就会为员工表中的数据构建特定的数据结构。

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

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

---

二、MySQL与磁盘

磁盘中的一个盘片

说明一下：

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

定位扇区

定位扇区时采用的是 CHS寻址 方式：

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

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

MySQL与磁盘交互的基本单位

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

mysql> show global status like 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 | -- 16*1024=16384
+------------------+-------+
1 row in set (0.00 sec)

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

1. mysqld本质就是一个进程，一定是操作系统之上运行的
2. 对 MySQL 内部的数据等做操作 (CURD) 时，本质就是操作文件
3. 想对文件内容做任何操作，文件必须先被打开，然后将文件内容加载到内存中，在内存中进行操作，都不是直接在磁盘设备上做操作的。
4. 如果要操作的数据不在内存里，就需要将数据先加载到内存中。如果加载的数据占用太多的内存，导致内存不够用，内存将会把一些不常用的数据换出，进行持久化操作。
5. MySQL为了提高效率，一定会提前将一部分数据加载到内存中，那么其启动的时候，一定会预先申请一批内存空间

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

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

---

三、索引的理解

观察主键索引现象

创建一个用户表，表当中包含用户的id、年龄和姓名，并将用户的id设置为主键。如下：

mysql> create table user(
    -> id int primary key,
    -> age int not null,
    -> name varchar(16) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

创建完表后向表中插入一些数据，并插入数据时没有按照主键大小顺序进行插入，如下：

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

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

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

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

---

推导主键索引结构的构建

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

• MySQL将内存中的每一个Page都用一个结构体描述起来，然后再将各个结构体以双向链表的形式组织起来，因此一个Page结构体内部既包含数据字段，也包含属性字段。
• 此外，为了方便后续数据的插入和删除，每个Page结构体内部存储的数据记录会以单链表的形式组织起来，并且各个记录之间会按照主键进行排序。

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

为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？插入数据时排序的目的，就是优化查询的效率。

页内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。正是因为有序，在查找的时候，从头到尾都是有效查找，没有任何一个查找是浪费的，而且如果运气好，是可以提前结束查找过程的。

单个Page内创建页内目录

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

那么当前，我们在一个 Page 内部引入了目录。比如，我们要查找 id=4 记录，之前必须线性遍历 4 次才能拿到结果。现在直接少量的遍历找到目录 2[3]，直接进行定位新的起始位置，提高了效率。现在我们可以再次正式回答上面的问题了，MySQL 为何会通过键值会自动排序？因为只有 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+ 树啊！没错，至此我们已经给我们的表 user 构建完了主键索引。索引本质就是 B+ 树。随便找一个 id，我们可以发现，现在查找的 Page 数一定减少了，也就意味着 IO 次数减少了，那么效率也就提高了。其实操作系统中的页表本质上也是B+树结构。

---

索引结构可以采用的数据结构

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

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

---

聚簇索引 VS 非聚簇索引

聚簇索引（Clustered Index）

聚簇索引是将表的数据行直接存储在索引中，而不是将数据和索引分开存储。在聚簇索引中，数据行的物理存储顺序与索引顺序一致，因此表的数据行实际上是按照聚簇索引的键值顺序来排列的。每个表只能有一个聚簇索引，通常是主键索引，因为主键是表中的唯一标识。由于数据行和索引行在聚簇索引中存储在一起，因此聚簇索引能够提供较快的数据检索性能，特别是在范围查询和排序操作时。

非聚簇索引（Non-Clustered Index）

非聚簇索引是将索引和数据分开存储。在非聚簇索引中，索引行包含了索引键值以及指向对应数据行的指针（一般是数据行的物理地址或主键值）。每个表可以有多个非聚簇索引，用于加速特定列的检索。由于数据和索引在非聚簇索引中分开存储，因此非聚簇索引的数据行在物理上是随机散落存储的。因此，当进行范围查询或排序操作时，非聚簇索引的性能可能较聚簇索引稍慢。

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

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

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

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

当采用innoDB存储引擎创建表时，在数据库对应的目录下会新增两个文件。如下：

当采用MyISAM存储引擎创建表时，在数据库对应的目录下会新增三个文件。如下：

说明一下：

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

---

四、索引操作

创建主键索引

方式一

创建表时，直接在对应的字段名后指定primary key。如下：

mysql> create table user1(
    -> id int primary key,
    -> name varchar(20)
    -> );

方式二

在创建表的最后，指定某列或者某几列为主键索引

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> primary key(id)
    -> );

方式三

创建表后，使用alter命令给指定字段添加主键索引。

主键索引的特点：

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

---

创建唯一索引

方式一

在创建表时，直接在对应的字段名后指定unique。

mysql> create table user4(
    -> id int primary key,
    -> name varchar(20) unique
    -> );

方式二

在创建表的最后，指定某一列或某几列为唯一索引。

mysql> create table user5(
    -> id int primary key,
    -> name varchar(20),
    -> unique(name)
    -> );

方式三

在创建表后，使用alter命令给指定字段添加唯一索引。

唯一索引的特点：

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

---

创建普通索引

方式一

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

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

方式二

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

方式三

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

普通索引的特点：

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

---

创建全文索引

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

下面向表当中插入一些测试数据。

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

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

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

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

说明一下：

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

---

查询索引

方式一

使用 show keys from 表明 SQL 查询，比如查询 articles表中的索引信息。

说明一下：

• Table： 表示创建索引的表的名称。
• Non_unique： 表示该索引是否是唯一索引，如果是则为0，如果不是则为1。
• Key_name： 表示索引的名称。
• Seq_in_index： 表示该列在索引中的位置，如果索引是单列的，则该列的值为1，如果索引是复合索引，则该列的值为每列在索引定义中的顺序。
• Column_name： 表示定义索引的列字段。
• Collation： 表示列以何种顺序存储在索引中，“A”表示升序，NULL表示无分类。
• Cardinality： 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
• Sub_part： 表示列中被编入索引的字符的数量，若列只是部分被编入索引，则该列的值为被编入索引的字符的数目，若整列被编入索引，则该列的值为NULL。
• Packed： 指示关键字如何被压缩。若没有被压缩，则值为NULL。
• Null： 用于显示索引列中是否包含NULL，若包含则为YES，若不包含则为NO。
• Index_type： 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
• Comment： 显示评注。

方式二

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

方式三

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

---

删除索引

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

删除主键索引

使用 alter table 表名 drop primary key SQL即可删除主键索引。

alter table user10 drop primary key;

删除非主键索引

使用 alter table 表名 drop index 索引名 SQL即可删除指定的非主键索引。

此外，也可以使用 drop index 索引名 on 表名 SQL也可以删除指定的非主键索引。

drop index email on user10;

说明一下：

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

---

索引创建原则

索引创建的原则如下：

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

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

---