【MySQL】索引原理及操作

目录

索引原理

初识索引

磁盘原理 

磁盘与系统之间的关系 

MySQL、系统、磁盘之间的关系 

理解索引

页目录 

页目录设计的数据结构问题

聚簇索引与非聚簇索引 

遗留问题

索引操作

创建索引

查询索引 

删除索引 

其他索引概念与操作 

索引原理

索引（Index）是数据库管理系统中一种非常重要的数据结构，它主要用于帮助数据库系统高效获取数据。

初识索引

实验准备

在正式开始观察索引对搜索数据的效率影响之前，我们得先拥有一张海量数据表

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添加到文件中，使用source语句可以直接导入一张800万数据的数据库

注意：导入时长可能需要10分钟左右

实验现象

使用select查找empno为998877的一行数据，看看用时

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

接下来我们为该表的empno列创建索引，既然说索引能提高数据库查找的效率，那么查找效率有多快呢？sad

创建索引语句：

alter table EMP add index(empno);

 创建索引并进行查找数据：

可以看到，从4.5秒左右到0.01秒，索引对于数据库查找效率的提升是数量级的！

索引是如何做到的呢？接下来正式开始索引原理方面的学习！

 MySQL与磁盘的关系 

MySQL为我们创建一个数据库，本质就是在系统中为我们创建一个目录，在Centos7.6中，该目录被保存在/var/lib/mysql路径下

MySQL为我们创建表，本质就是数据库目录下，创建一个普通文件。

所以总的来说，MySQL的一切都与文件息息相关，要弄懂索引原理，理解MySQL，我们必须从文件入手，而文件在硬件上是被写入到磁盘中的，所以变相的MySQL与磁盘的关系也是非常紧密的！所以接下来我们从磁盘角度入手，一步一步弄清楚索引的原理

磁盘原理 

对于磁盘，在文件系统时已经详细介绍过，若感兴趣可以去看看，而接下来我将对磁盘进行简要介绍 

结构

上图就是一个磁盘结构，其中较为关键的部位就是磁盘(盘片)和磁头

盘片：通过主轴的带动，盘片会进行高速旋转

磁头：磁头会进行左右摇摆

通过盘片和磁头的运动规律，我们可以让磁头定位到盘片中的任意区域！

盘片

从逻辑上，一块盘片是由若干个扇区组成

扇区如何构成？

如图中，盘片上划分出了多个不同半径的同心圆，两个同心圆之间的区域，我们称之为磁道

如图中，盘片上的以半径划分出的一条一条的线，两条线之间的区域我们称之为扇面

磁道和扇面的交集区域，我们称之为扇区

扇区是磁盘IO的基本单位，一个扇区通常能存储512字节的数据！

注意：尽管越靠近外侧，扇区的物理面积好像越大，但不管它多大，在数据存储方面始终都是只能存储512字节的数据，尽管在最新的磁盘技术中，为了更大的容量，外侧的扇区能存储的数据越多，但我们暂时不考虑！

由磁盘的硬件结构可以得知，我们要打开一个文件，首先是要找到一个文件，找到一个文件，也就是找到这个文件对应的扇区

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

通过磁盘的结构我们可以得知，磁盘要定位一个扇区首先是要通过盘片的高速旋转，磁头的高速摆动来定位的

尽管我们说它是高速运动，盘片的转动甚至可以到达几千转，但相对于计算机来说，物理高速运动是很慢的，所以磁盘I/O是很慢的！

磁盘要访问扇区是通过物理运动实现的，这也就决定了，磁盘在访问了A扇区继续访问B扇区，若B扇区在A扇区附近，那么磁盘可以很快定位到B扇区。若A扇区与B扇区相隔很远，磁盘要进行大幅度运动，那么它的下一次访问相对而言就较慢了！

其中，AB扇区相隔很近，磁盘连续访问AB扇区时，我们称磁盘在进行连续访问

 AB扇区相隔很远，磁盘大幅度运动定位不同扇区的方式，我们称磁盘在进行随机访问

注意：一般来说，好的软件设计应该是让磁盘尽量进行连续访问，减少随机访问！

磁盘与系统之间的关系 

磁盘I/O的基本单位是扇区(512字节)，那么系统和磁盘I/O的基本单位也是扇区(512字节)吗？

并不是，系统与磁盘进行交互的基本单位一般是4096字节，即4KB

换句话来说，对于系统来说，哪怕我只访问1个字节的空间，磁盘也必须把完整4KB的空间给我

为什么系统与磁盘I/O交互要设计成4KB

系统I/O基本单位为4KB，主要是因为如下原因：

• 可移植性
• 效率问题 

可移植性： 如果操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就和硬件强相关，换言之，如果硬件发生变化，系统必须跟着变化，这显然是不合理的！

效率问题：512字节对于I/O来说还是太小了。例如对于一个4KB的连续空间，若采取512字节为单位，那么需要进行8次I/O。若采取4KB为单位，那么只需要进行1次I/O。众所周知I/O是非常耗时的！

为什么磁盘I/O的基本单位是512字节

前面，我们说了，若以512字节为单位，此时I/O的效率是很低的，那么磁盘I/O的基本单位为什么是512字节呢？

事实上，现如今的不少磁盘I/O的基本单位是4KB，但尽管如此，还是有以512字节为I/O基本单位的磁盘存在的！

这是随着时代的发展而导致的存量问题。尽管在我们现在看来，磁盘I/O为512字节是不合理的，但在计算机发展早期，由于磁盘普遍的大小都不大，对于当时的人来说，512字节是合理的，并制定下了磁盘I/O为512字节的标准。随着时代发展，磁盘变得越来越大，此时对于人们来说最首要的不是存储问题，而是效率问题，所以后期操作系统发展时人们慢慢意识到了这个问题，并对系统I/O基本单位进行了重新定制！

补充：系统I/O时的基本单位，我们称之为数据块

MySQL、系统、磁盘之间的关系 

结论：MySQL的I/O默认基本单位是16KB，我们称之为page 

MySQL的I/O默认单位16KB的必要性 

 我们在使用数据库时，最常使用的操作就是对数据库表的CURD操作，可以说对数据库的使用，百分之八十都涉及I/O操作，正因为大量I/O的存在，为了提高效率，所以MySQL进一步提高I/O基本单位的大小，力求减少I/O次数从而提高效率

MySQL怎么做到I/O基本单位为16KB的？

MySQL本质上就是一个应用层的一个软件，MySQL也是程序员进行编写的！

对于MySQL来说，我只需要在I/O之前提前new/malloc一堆空间，当需要使用时直接拿取即可！

MySQL提前new/malloc开辟好的空间我们称之为Buffer Pool，这个Buffer Pool是在MySQL加载到内存时自动开辟的！

对于MySQL的读取来说，即MySQL调用read从内核文件缓冲区中指定拿取16KB，放入到Buffer Pool的一个page中即可，此时数据就已经从磁盘到达了内存

对于MySQL的写入来说，就是从Buffer Pool中拿取一个已经开辟好但还没有写入的page，对这个page直接写入，再调用write把数据写入到内核文件缓冲区中，再使用fsync调用强制把文件缓冲区中的数据刷新到磁盘上，此时数据就从内存到达了磁盘！

正确理解MySQL的page、系统数据块、磁盘扇区之间的关系

尽管我们说，MySQL的I/O是16KB，即MySQL与磁盘交互的基本单位是16KB。

但MySQL是一款应用层软件，而它无法从应用层直接跨越到物理层，应用层和物理层之间隔着操作系统，所以MySQL也必须要经过操作系统。

MySQL进行I/O写入时，无非就是调用系统的接口进行写入，而写入也并不是直接写入到磁盘扇区中，而是写入到内核的文件缓冲区，再由操作系统以4KB为单位写入磁盘。但数据真正到了磁盘以后，磁盘对它的管理，是512字节为单位的！

我们所谓的MySQL与磁盘I/O基本单位是16KB，指的是在MySQL看来，我调用接口写入16KB以后，数据就已经到了磁盘上了。但实际情况是数据从MySQL到系统再到磁盘！

建立共识 

通过上述我们能得出的结论有以下：

1. MySQL中的数据文件，是以page为单位保存在磁盘当中的。
2. MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为Buffer Pool的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
3. 为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数

除了上述结论，我们还要继续补充的结论：

我们在使用MySQL中，进行了大量的对数据的CURD操作，这些对数据CURD操作都是需要CPU参与的，比如查找一个数据时对数据库的遍历操作。

由于有了CPU的参与，所以MySQL必须先把数据加载到内存中！

所以在我们对数据进行CURD操作的同时，数据一定是有两份的，在磁盘中一份，在内存中一份！

当我们对数据的CURD操作结束时，MySQL通过特定刷新策略把内存中的数据刷新到磁盘中，这也就是所谓的磁盘与MySQL之间的I/O，I/O单位为page，大小是16KB

理解索引

MySQL的I/O基本单位是page，那么我们可以简单理解数据库文件为单个或多个page组成的。

如何管理page 

MySQL一个表中的page可能有很多，即表较大！这就要求MySQL必须对page进行管理，管理方式就是我们熟知的“先描述、后组织”

而对于page的管理，我们可以把多个page使用双向链表连接起来。

所以一个page中，可能不仅仅只有数据，也有它的前驱和后继指针

此时对于page的管理，就转化成了对数据结构的增删查改

页目录 

页目录的引入

若仅仅是把多个page使用双向链表相连，那么数据库查找一个数据只能是线性遍历，这是由链表结构决定的！但线性遍历的查找效率还是太慢了，所以我们引入了一个新的结构：页目录

如何提高查找效率？

 我们先思考一下，为什么线性遍历查找一个数据会比较慢呢？

实际上所谓的查找的本质就是排除。排除掉所有其他数据，不就是目标数据了吗？

线性遍历查找效率较低，是因为一次只能排除一个数据。

那么我们的优化方案只需要让MySQL查找时一次排除一批数据，排除的越多，查找效率就越快

生活角度理解页目录 

我们在看一本书，假设是谭浩强的《C程序设计》的时候，我们需要找到指针章节，如何才能找到呢？

实际上，对于书来说，都会在书的开头设计一个目录，目录中记录着每个章节的起始位置，当我们需要访问特定章节时，我们只需要在目录中，找到该章节的起始位置 ，就可以直接翻到该章节，无需一页一页遍历。MySQL的页目录的设计思路也是如此

这种目录的设计方案对于查找来说是一种以空间换时间的设计方案，即原本目录页是可以写入更多书的有效数据的，但为了搜索效率，我选择不写入书的有效数据，而是放一个目录！MySQL的页目录设计也是如此！

目录的方式能实现搜索效率的提高，一定是因为书的页码是有序的，这一点很好理解。MySQL在设计页目录时的前提也是让主键先有序，此时的页目录才有效

实验

我们上面说，MySQL默认是主键有序的，那么是否如此呢？接下来做个实验看看

实验准备：创建一个实验表：

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

接下来以主键无序的方式插入一些数据：

最后查看表，观察现象:

观察到，尽管插入时是主键无序方式插入的，但mysql为了引入页目录，也会对数据进行排序，默认情况按照主键进行排序 

单page的页内目录 

一个page能保存16KB的数据，这个数据量是较大的，页目录首先要解决的就是一个page的查找问题

如图中，目录1指向的起始位置是1，目录2指向的起始位置是3.

若我们需要查找主键为2的数据，那么只需要判断该数据是在目录1和目录2之间的，再从目录1的起始位置遍历数据，就能找到该数据。

这种遍历方式，一次能排除大量的数据量，即排除目录1和目录2之间以外的其他所有数据！查找效率得到提高

实际场景

假设此时page中有100个数据，主键是1-100有序排列

此时我们每10个数据归属于一个目录，那么目录1表示的主键范围是[1-10]，目录2表示的主键范围是[11,20]，依次类推。

假设此时查找主键为85的数据，我们以线性遍历和带了目录的遍历方式分别讨论：

线性遍历：

• 从1开始按顺序遍历所有数据，一共要遍历85次

带目录遍历：

• 先依次遍历每个目录的起始主键，直到遍历到目录9，该目录起始位置>目标主键
• 回退到目录8，开始线性遍历
• 遍历次数为：遍历目录9次，线性遍历5次，10+5 = 15次 

通过对比我们能明显发现带了目录页以后遍历次数的减少

页内目录的扩展

至此，我们引入页目录以后，单个page内的数据查找效率已经得到了极大的提高，但MySQL可不仅仅只有一个page，我们只解决了page内部的数据查找问题，但一个数据在哪个page仍旧需要线性遍历的方式查找，而只要线性遍历，那么带来的就是效率问题，那么我们一定要对查找page的遍历方式进行优化！

如何优化？实际上可以参考我们单个page内部的页目录模式

我们单独为多个page建立一个目录表，目录表是一个单独的page，这个表中不存储有效数据，存储的是page的起始位置

并且多个目录表之间也使用双向链表的方式相连

这个目录表与页内目录的不同点在于：表中不存储有效数据，而只存储page目录

如图中，最底部的即为page，我们为每个page从左到右编一个号，即page1、page2、page3、page4

其中，page1的起始主键是1，page2的起始主键是6，它们由一个目录表共同管理！

我们要找到一个page，那么只需要先遍历多个目录表，查看它们的起始位置，如图中一个目录表的起始位置是1，一个目录表的起始位置是11。若需要找到主键为8的数据所处的page，那么这个数据一定是在第一个目录表当中的，因为8是在1和11之间的，再按照页内目录的遍历方式，即可定位出唯一的一个有效page

概念区分

至此，我们已经得到了一个基本的兄弟节点链表相连的多叉树结构

其中，这棵树的叶子节点，即存储有效数据的page，我们称之为数据页

非叶子节点，我们称之为页目录 

完整结构 

我们得到了一个两层高的多叉树，假设页目录变得很多很多，页目录也发生线性遍历导致的查找效率问题，那么我们可以为页目录继续创建页目录！创建方式跟之前一摸一样，于是整体结构就为如下：

而这，就是一个典型的B+树结构！这也是MySQL的Innodb存储引擎所采取的基本方案

若我们在这棵B+树中需要找到唯一主键对应的数据只需要自顶向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数

本质上，找到对应的页目录也就意味着其他所有页目录都被排除掉了，排除的越多，查找效率越高，所以这颗B+树的查找效率是非常高的

页目录设计的数据结构问题

我们了解了MySQL的Innodb的相关数据结构，接下来我们来聊聊为什么要使用B+树，而不用其他数据结构呢？

首先，我们对于页目录设计选择数据结构时，一定要保证的是查找效率，因为数据库会进行大量查找。故排除线性表

接下来留给我们的选择主要是二叉树与多叉树与哈希表

为什么不采用二叉树结构？

先考虑二叉树，首先能想到的就是绝对平衡的AVL树与相对平衡的红黑树

尽管对于AVL树和红黑树来说，它们是平衡的，这可以保证它们在搜索时的快速查找

但由于二叉树的结构，一个节点只能拥有两个孩子节点，这会导致它们的高度与B+树相比是较高的。对于MySQL来说，若需要找到一条记录，那么最少也要进行高度次查找。红黑树和AVL树的二叉结构就导致了它们一定需要比B+这种多叉树结构I/O更多次

为什么不采用哈希表？

MySQL中有些存储引擎是支持hash的，但MyISAM和Innodb不支持。

尽管哈希在遍历单个数据时的效率非常快，可以到达O(1)

但由于我们在搜索时更多需要的是范围查找，而对于哈希来说，由于每一个数据都是通过哈希函数进行映射的，这也就决定了拿到每一个数据都需要进行哈希映射。一次拿的数据量大了就会导致效率过低

回顾我们的B+树，为什么能很好进行范围查找呢？

因为页目录和页目录之间的起始位置一定是有序的，数据页与数据页也是。并且它们之间是用双向链表相连，这意味着需要连续拿取多个page时的效率是比较高的，B+树的结点间的链式结构也就决定了可以很好的支持范围查找！

为什么不采用B树？ 

B树和B+树同样都是多叉树，若需要搞懂Innodb与MyISAM为什么不采用B树，我们得先知道两种树的差别！

B树：

B+树

从图中我们能发现的B树和B+树的区别：

• 节点结构不同：对于B树来说，每一个节点都存储有效数据和索引与指针域。对于B+树来说，只有叶子节点存储有效数据和索引，非叶子节点只存储索引和指针域
• 同一层的节点之间结构不同：对于B+树来说，每一个兄弟节点都被连接在同一张链表中。对于B树来说则没有

所以回归主题，MyISAM和InnoDB为什么不采用B树结构呢？

聚簇索引与非聚簇索引 

聚簇索引是InnoDB存储引擎维护表的方式

非聚簇索引是MyISAM存储引擎维护表的方式

聚簇索引 

在理解索引中，我们讲的一直都是聚簇索引，即Innodb采取的存储模式

这种索引的典型特征是把有效数据直接放入到数据页中

非聚簇索引 

非聚簇索引是MyISAM存储引擎采用的存储模式

这种索引的典型特征是数据页中不直接放入有效数据，而是放入有效数据的地址

如下图：

所以对于非聚簇索引来说，除了要维护一个B+树以外还需要维护一张有效数据表！

聚簇索引的优势与非聚簇索引的劣势

聚簇索引与非聚簇索引相比带来了一些优势：

• 数据访问更快
• 更好支持范围查询

数据访问更快：

• 聚簇索引的数据页中直接保存了有效数据，找到数据页后不需要额外消耗就可以直接访问到数据。
• 非聚簇索引找到数据页后还需要定位到数据表才可以访问到数据，数据访问相对较慢

更好支持范围查询：

• 聚簇索引将数据按照索引列的顺序存储在磁盘上，这种物理存储方式使得相关数据在磁盘上也是相邻的。因此，当进行范围查询时，数据库系统可以直接读取相邻的数据页，从而减少了磁盘I/O操作的次数，提高了查询效率。
• 非聚簇索引在物理存储上并不保证数据的连续性。非聚簇索引的索引和有效数据是分开存储的，索引存储在索引文件中，而数据存储在数据文件中。因此，当进行范围查询时，非聚簇索引可能需要访问多个不连续的数据页，从而增加了磁盘I/O操作的次数和查询的复杂性。

聚簇索引的劣势与非聚簇索引的优势

 聚簇索引与非聚簇索引也存在一些劣势：

• 插入和更新代价高：聚簇索引的插入速度严重依赖于插入顺序。如果不按照主键顺序插入数据，可能会出现页分裂现象，严重影响性能。此外，更新主键的代价也很高，因为这将导致被更新的行移动。
• 二级索引访问复杂：在聚簇索引之上创建的索引称为辅助索引或二级索引。二级索引访问数据需要两次索引查找：第一次找到主键值，第二次根据主键值找到行数据。这增加了查询的复杂性

遗留问题

列创建索引的本质 

在本文的最开始，我们做了一个实验，即为雇员表的empno创建了一个索引，这个行为的本质是什么？

实际上，为empno所对应的列创建索引就是以empno为B+树的键值创建一棵B+树，当我们查询empno时直接去这棵新的B+树查询即可，这样的话查询效率当然就大大提高了。

若我们不以empno为键值，而是以主键为键值。即表创建时的默认情况，那么我们查找empno列的数据时候的本质遍历以主键为key的B+树，这样查找效率当然就慢了！

若表没有主键，表的结构是否还是为B+树？ 

 表没有主键，表的结构还是为B+树。实际上，若我们没有手动构建主键，那么mysql会为我们的表默认生成主键，当然这个主键我们是看不到的，是mysql内部自己维护的，我们称它为虚拟主键。若表中没有主键，那么mysql会以虚拟主键为键值为表构建B+树

索引操作

索引一共分为4种：

• 主键索引
• 唯一索引
• 普通索引
• 全文索引

创建索引

主键索引 

创建主键索引的方式有三种！

一般来说，我们在表创建的时候若指定了主键，那么MySQL会自动构建主键索引。这种创建索引的方式是MySQL的默认行为，主要方法有两个，我们归为一类

第一种方式：

-- 在创建表的时候，直接在字段名后指定 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);

主键索引的特点 

• 一个表中，最多有一个主键索引，这个特点也符合主键约束的定义
• 由于每张表的主键列的值都是不能重复的，具有唯一性，所以主键索引查询时一般效率较高
• 创建主键索引的列，它的值不能为null，且不能重复
• 主键索引的列基本上是int类型

唯一索引 

唯一索引的定义是：为具有唯一键约束的列添加索引。由于有唯一性约束，所以一般唯一索引查询的效率较高，同时MySQL会自动为具有唯一键约束的列添加索引！

唯一索引的创建与主键索引基本相同，不再过多介绍

第一种方式：

-- 在表定义时，在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);

第二种方式：

-- 创建表时，在表的后面指定某列或某几列为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);

唯一索引的特点 

唯一索引的特点：

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

普通索引

MySQL中对于普通列(非主键、非唯一键)不会有默认行为，需要我们手动指定列来创建索引

普通索引的创建方式也分为两类，三种

第一类是在表创建之前手动指定某列，为该列创建普通索引，创建方式只有一种，语法如下：

create table user8(id int primary key,
 name varchar(20),
 email varchar(30),
 index(name) --在表的定义最后，指定某列为索引
);

第二类是在表创建完以后，我们手动创建索引，对应的方式有两种，如下语法：

第一种方式： 

create table user9(id int primary key, name varchar(20), email 
varchar(30));
alter table user9 add index(name); --创建完表以后指定某列为普通索引

第二种方式：

create table user10(id int primary key, name varchar(20), email 
varchar(30));
-- 创建一个索引名为 idx_name 的索引 
create index idx_name on user10(name);

普通索引的特点 

普通索引的特点：

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
• 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引  

索引创建原则 

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

查询索引 

MySQL提供了查询索引的方式，它允许我们查看表中的哪些列属性被打上了索引，索引名字，索引类型...

MySQL中提供了三种查询索引的方式：

第一种方式：

show keys from 表名;

第二种方式：

show index from 表名;

第三种方式：

desc 表名；

其中，第三种方式查询到的索引信息较为简略，不再过多介绍，我们介绍前两种查询方式查询出来的结果

索引属性字段 

第一行(图中画红框的)：表示索引的编号 

• Table：显示索引所属的表名。

• Non_unique：如果索引不能包含重复词，则该列为0。如果可以，则该列为1。在MySQL中，除了主键索引（PRIMARY KEY）外，其他索引（如UNIQUE、FULLTEXT和SPATIAL索引）都可能包含重复的值，因此它们的Non_unique值通常为1（对于唯一索引，如果表中没有重复值，但从索引的角度看，它仍然被标记为可以包含重复值，因为索引结构本身不强制唯一性，只是约束了表中数据的唯一性）。然而，这个字段的解释可能因MySQL版本和具体实现而有所不同。在某些情况下，唯一索引的Non_unique值也可能为0，表示该索引是唯一的。

• Key_name：显示索引的名称。对于主键索引，名称总是PRIMARY。对于唯一索引，名称是在创建索引时指定的（如果未指定，则MySQL会自动生成一个名称）。对于其他类型的索引，名称也是在创建时指定的。

• Seq_in_index：显示索引中的列序号。在复合索引（即包含多个列的索引）中，每一列都有一个序号，表示它在索引中的位置。对于单列索引，这个值总是1。

• Column_name：显示索引中的列名。这是索引所基于的表中的列。

• Collation：显示列以什么顺序存储在索引中。'A'表示升序，'D'表示降序，NULL表示不适用（例如，对于哈希索引或全文索引，排序是不适用的）。

• Cardinality：显示索引中唯一值的估计数量。这个值是一个估计值，可能并不完全准确。但是，它可以用于了解索引的选择性，即索引中不同值的数量与表中总行数的比例。高选择性的索引通常能提供更好的查询性能。

• Sub_part：显示索引只是使用列的一部分字符。如果索引是整列，则该列为NULL。对于BLOB和TEXT类型的列，可以只索引列的前缀。

• Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。

• Null：如果列可以包含NULL，则该列为YES。如果不，则该列为''（空字符串）。对于不允许NULL的列，索引可能会更有效，因为它们不包含NULL值，这可以减少索引的大小并提高查询性能。

• Index_type：显示使用的索引方法（BTREE, FULLTEXT, HASH, RTREE）。这是索引的物理实现方式。

• Comment：关于索引的额外信息。

• Index_comment：这个字段在某些MySQL版本中可能不存在，但在其他版本中，它提供了关于索引的额外注释或说明。这个字段的内容是在创建索引时通过COMMENT子句指定的。

全文索引 

全文索引是一种高效检索信息的技术，允许用户通过关键词快速找到数据库中关键词出现的某一行。

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

实验：使用全文索引 

创建一个表：

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=MyISAM;

这张表是用来存储文章数据的，比如文章标题(title)，文章内容(body)...

插入一些数据：

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

 接下来我们用普通查找的方式，查找一下这个表中含有database的行数据

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

查询结果如下：

注意：虽然这里找到了包含database的字段，但并没有使用到索引，而就是普通查询

验证是否用到索引：

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

explain工具：explain工具是MySQL（以及其他一些关系型数据库管理系统）中一个非常有用的性能分析工具。它提供了查询执行计划的详细信息，有助于开发者了解查询的性能瓶颈，并进行相应的优化。

图中，key属性字段为NULL，表示的是该条SQL没有索引键值，也就没有使用索引查询 

接下来我们要使用全文索引查询，而全文索引有它自己的语法，如下：

SELECT * FROM articles WHERE MATCH (title,body) AGAINST
('database');

• match(title,body)：指定了要搜索的字段，在匹配关键字'database'时，MySQL会在这些属性字段中匹配
• against('database')：指定要匹配的关键字为'database'

删除索引 

删除索引操作较为简单，分为三种：

第一种方式：（删除主键索引）

alter table 表名 drop primary key;

第二种方式：（删除普通/唯一索引）

alter table 表名 drop index 索引名;

注意：索引名就是我们查询索引时的key_name字段

第三种方式：

drop index 索引名 on 表名;

其他索引概念与操作 

 复合索引

复合索引是数据库索引技术中的一种，它是指在数据库表的多个列上建立的索引。这种索引可以显著提高涉及多个列的查询性能。 

复合索引是包含两个或更多个列的索引。它利用索引中的附加列来缩小搜索的范围，从而提高查询效率。

换句话来说，普通索引和复合索引的区别在于，普通索引底层构建B+树时，使用的只是一个列属性字段。而对于复合索引构建B+树时，是使用多个列属性字段共同构成一个键值。

复合索引的创建 

CREATE INDEX idx_name ON table_name (col1, col2);

• 该语句会在table_name表的col1和col2列上创建一个名为idx_name的复合索引。 

创建完复合索引后，当执行涉及col1和col2的查询时，数据库查询优化器会尝试使用复合索引来加速查询。但是，复合索引的使用也遵循一定的规则，如最左匹配原则。 

最左匹配原则

最左前缀原则是指查询中使用的列必须从复合索引的最左边开始连续匹配。

例如：如果有一个复合索引(col1, col2, col3)，那么查询中可以使用col1、(col1, col2)或(col1, col2, col3)来利用索引，但不能仅使用col2或(col2, col3)。 

注意：最左前缀原则是复合索引使用的关键。只有遵循这个原则，查询才能有效地利用复合索引来提高性能。 

为什么复合索引要满足最左匹配原则？ 

这主要涉及复合索引底层B+树Key值的设计：

复合索引在数据库中的存储结构通常是按照索引列的顺序进行排序的。这意味着，当你创建一个复合索引，如 (col1, col2, col3) 时，数据库会首先根据 col1 的值对表中的行进行排序。如果 col1 的值相同，则根据 col2 的值进行排序，以此类推。

索引覆盖的简介

索引覆盖是数据库索引技术中的一个重要概念，它指的是一个索引包含了查询所需的所有字段，使得查询操作可以直接通过索引来获取结果，而无需再访问表中的数据行。 

例如：有一个索引底层B+树使用的是id作为键值。假设我们需要查找id为520的字段，并且只看id，那么我们在这个B+树中找到了id为520的数据行，此时直接把520返回，不需要访问id为520的整个数据行。减少了I/O，提高了效率