MYSQL 索引

原创于 2025-09-02 01:11:54 发布 · 726 阅读

24 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

文章目录

进一步认识索引

什么是索引

数据库中的索引是一种用于加速对表中数据行检索的分散存储结构，类似书本目录，能帮助快速定位数据。

索引的作用

建立索引，主要是为了提高查询速度！
注意看：在没有引入索引的时候，mysql在一张八百万行的表中查找一个特定行的时间
在这里插入图片描述

我们现在给员工编号建立一个索引
在这里插入图片描述

建立索引之后再查找，时间极快
在这里插入图片描述

数据库文件存放在哪里？

MySQL 中的数据文件，是以page为单位保存在磁盘的扇区中的

如何定位一个文件？

定位一个文件→定位一个扇区

想要找到一个文件的全部，本质就是在磁盘中找到所有保存该文件数据的扇区。而我们如果能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的到此为止，定位一个文件的问题，就被我们转化成了定位一个扇区的问题

如何定位一个扇区？

CHS地址
盘面号+柱面号+扇区号
磁头（Heads）、柱面(Cylinder)、扇区(Sector)对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做 CHS
LBA地址
实际系统软件使用的并不是 CHS （但是硬件是），而是 LBA ，一种线性地址，LBA与CHS之间的关系可以想象成虚拟地址与物理地址之间的关系。系统先将LBA传给磁盘控制器，然后磁盘控制器就会自动将 LBA 地址最后会转化成为 CHS ，交给磁盘去进行数据读取。不过，我们现在不关心转化细节，知道这个东西，让我们逻辑自洽起来即可

为什么操作系统不使用CHS地址，而使用LBA地址访问磁盘呢？
因为硬件的型号是容易更换的，如果我操作系统不做到和硬件解耦，那硬件一但更换，操作系统也得跟着瘫痪

Mysql与磁盘交互的基本单位是什么？

我们现在已经能够在硬件层面定位任何一个基本数据块了。那么在系统软件上，是不是以扇区为单位进行IO交互呢？

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

MySQL 进行IO的基本单位

MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， Mysql与磁盘交互的基本单位是 16KB（一个mysql的IO对应系统的四次IO）
下面我们来证明 MySQL 进行IO的基本单位是 16KB
命令行输入SHOW GLOBAL STATUS LIKE 'innodb_page_size';
这条命令用于在 MySQL 中查询全局状态变量里与 innodb_page_size 匹配的信息。SHOW GLOBAL STATUS 用于查看 MySQL 服务器的全局状态变量，LIKE 关键字用于进行模糊匹配，这里精确匹配 innodb_page_size 变量。
在这里插入图片描述

表头：Variable_name 表示变量名称，Value 表示对应变量的值。
数据行：显示变量 Innodb_page_size 的值为 16384 ，16 * 1024 = 16384 ，即 InnoDB 存储引擎的页大小为 16KB 。

用户修改数据库中1KB的数据，与整体替换该1KB数据所属的磁盘块，哪个时间更久？

一样的！哪怕你只改1个比特位，你花的时间和改16KB的数据都是一样的

mysql与磁盘的交互

mysql与磁盘的交互目的是？（mysql为什么需要进行IO操作？）

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

mysql与磁盘的交互过程

mysql应用程序通过内部的缓冲区，以PAGE（16KB）为单位与操作系统进行交互。操作系统通过内核缓冲区以4KB为单位与磁盘进行交互
mysql中对任何数据进行修改，根本不是直接对磁盘文件进行任何修改！！ 具体步骤如下：

程序通过open系统调用打开对应的磁盘文件，底层调用磁盘驱动程序将磁盘中对应的page读取到内存中
操作系统调用系统调用read，将page中的数据读取到程序中
mysql在这个PAGE中查找对应的数据
找到之后对其进行相应修改
然后使用特定的刷新策略刷新回PAGE（操作系统调用系统调用write）
将内存中的PAGE刷新回磁盘（磁盘驱动程序）

mysql所有CURD操作，其实都是对内存中对应的PAGE做操作，然后定期刷新到磁盘中的！！！

为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢？用多少，加载多少不香吗？

决定IO效率的，往往不是IO的数据大小，而是IO的次数
采用Page的方案进行交互，如果我多次查找的数据恰好都在一个PAGE中，那么总共就只需要一次IO，这就节约了大量时间，提升了效率

创建一个索引的本质

为什么mysql内部要给我自动排序？

案例引入：建立测试表，乱序插入数据，但我在打印的时候，却发现系统自动给我按序输出了，为什么数据库在插入数据时要对其进行排序呢？我们按插入的先后顺序依次存数据不是也挺好的吗？
因为自动排序有利于查找
插入数据时排序的目的，就是优化查询的效率。
页内部存放数据的模块，实质上也是一个链表的结构，链表的特点也就是增删快，查询修改慢，所以优化查询的效率是必须的。
正是因为有序，在查找的时候，从头到后都是有效查找，没有任何一个查找是浪费的，而且，如果运气好，是可以提前结束查找过程的

索引文件是在内存中还是在磁盘中呢？

使用一定是在内存中使用的
我们用户对数据库做的增删查改操作，对象全都是内存中的索引结构（其实就是一颗B+树）
索引结构的最底层就是具体的数据，这些数据包含于索引结构，也都是在内存中的，使用前会将索引文件从磁盘读到内存中，磁盘会备份一份索引文件

MySQL对Page的管理原理（创建一个索引的本质）

创建一个索引的本质是什么？
创建一个索引的本质，其实就是在mysql中创建一个B+树

MYSQL对PAGE的管理

为什么要MYSQL管理PAGE？

mysql是做数据管理的，IO的基本单位是16KB的page，mysql要管理的数据量可能非常大，也就意味着需要大量的Page！！是不是意味着，在同一时刻，mysql内部会存在大量的page！，因此MySQL就需要对所有的Page进行统一管理

如何管理PAGE？

当然需要管理，先描述，再组织！（如何描述，如何组织？）
我们创建了一个结构体用来描述PAGE，将后续对数据的增删查改转化成对PAGE结构体的对应管理操作
如果让新手直观的想，可能是这样的：每一个Page 在 MySQL 中都是 16KB ，使用 prev 和 next 构成双向链表
注意：逻辑是是这样没错，但mysql实际上不是这么做的（我们后续有结构优化通过优化索引结构提高查找效率）

在这里插入图片描述

通过优化索引结构提高查找效率

为什么要优化结构提高查找效率？上面我们说的PAGE结构与查找方式有什么问题？

如果数据的量比较大，那么我们一定需要多个Page来保存这些数据
目前多个Page彼此使用双链表链接起来，而且每个Page内部的数据也是基于链表的。那么，查找特定一条记录，无论是页表内还是页表间都只能通过线性查找，时间复杂度是O(n)，这效率也太低了。

如何优化单个页表内的查找效率？

我们的方法就是：引入页目录

给每一张PAGE都创建一个对应的页目录

页目录主要就是通过以时间换空间的思路来提高效率
就和你纸质书的目录是一个原理，前面多加几页目录，通过查找目录快速定位你要看的章节位置
我们在看《谭浩强C程序设计》这本书的时候，如果我们要看<指针章节>，找到该章节有两种做法：

从头逐页的向后翻，直到找到目标内容
通过书提供的目录，发现指针章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的方案，可以顺序找，不过因为目录肯定少，所以可以快速提高定位

本质上，书中的目录，是多花了纸张，但是却提高了效率。所以，目录，是一种“空间换时间的做法

如何优化多个页表间的查找效率？

我们的方法就是：建立B+树
其实也是引入目录，这个目录采用map的数据结构，pair存储PAGE的ID与PAGE的指针
在这里插入图片描述

PAGE的ID值如何选取？

通常我们选取这一页PAGE中值最小的主键作为这一页的ID

目录中每条目录项具体存的是什么内容？一张目录可以存储多少条目录项呢？

目录页的大小为16KB
双向链表的两个指针需要16B
目录中每条记录存储的pair类型是pair<int, PAGE*>（仅仅记录一张PAGE的id与其存储地址之间的映射关系），我们就算它占16B
这样算的话，1页目录大约可以记录(16KB-16B)/16B=1023条目录项

如果我的数据量实在很大，目录页都很多，我线性遍历目录页也需要花很长时间，这时候我该怎么优化呢？

套娃，再加一层目录，类似于多级页表的管理方式（这其实是一棵B+ 树）
在这里插入图片描述

我们通常建立索引，本质就是直接或者间接建立B+树
补充：其实内存地址用的也是B+树的结构，当我们真正访问某些物理内存，需要建立映射关系的时候，对应的页表条目，才需要被加载在这里插入图片描述

为什么数据库建立索引用B+树，不用其他的其他数据结构呢？

为什么数据库建立索引用B+树而不用B树？

B树的特点是，非叶子节点上面存的也有数据，这就会导致B树变得更高
B树的特点是，非叶子节点上面存的也有数据，而B+树只有在叶子节点上面存的有数据
如果目录页（非叶节点）也保存数据，那么 16kb 的大小中，能够作为目录项的内容就变少了，就意味着，这一页目录中索引的page 变少了
这就意味着需要更多目录页来进行管理叶子 page，也可能进一步需要使用更多的目录页来管理子级目录页
最终会导致这棵树“瘦且高”，但这不是我们想要的，因为树的高度增加，也就意味着我们的检索次数增加，这会大大降低查找效率，我们希望这棵树“矮且胖”
B树叶子节点不会相互连接，这样就不太支持范围查找，而B+树是支持范围查找的，因此更方便

为什么不能是链表？

链表？线性遍历？显然不行

为什么不能是二叉搜索树？

二叉搜索树存在退化问题，可能退化成为线性结构

为什么不能是AVL（平衡二叉树） &&红黑树？

虽然是平衡或者近似平衡，但是毕竟是二叉结构，非叶子是要存数据的，相比较多阶B+，AVL（平衡二叉树） &&红黑树整体过高
大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。
虽然你很好，但B+树比你更好 （其实归根到底还是因为B+树非叶子节点不存数据，导致这棵树是最矮的）

为什么不能是Hash？

官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.
Hash根据其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行

什么是范围查找？
我想查找这个数据库中在8~20这个范围内的所有数，像这种就是范围查找

聚簇索引 VS 非聚簇索引（什么是聚簇索引？什么是非聚簇索引）

聚簇索引

比如B+树中，虽然数据在叶节点中，索引在非叶节点中，但是他们都属于同一颗二叉树，没有分开。
像这样数据与索引目录没有分离的索引，就叫做聚簇索引

非聚簇索引

非聚簇索引的意思就是数据与索引结构分开存储的索引（啥叫分离存储？）
就是数据与索引不在同一个数据结构中

比如在MyISAM中，同样采用B+树索引，但与Innodb不同的是，MyISAM的B+树叶节点存放的是数据的指针，而不是有效数据本身（下面我们来验证一下）
我们在数据库中创建一个基于MyIsam存储引擎的数据库，然后在linux系统下查看这个刚刚创建出来的数据库目录下有哪些文件，结果如下
在这里插入图片描述

其中frm文件用于存储表结构（这个表有哪些列，每一列的变量类型以及名字）
MYD结尾的文件就是用来存储具体的数据信息
MYI文件就是用来存储索引信息

对比innodb，只有一个ibd文件，也就是说在innodb的存储引擎下，一张表中具体的数据和数据的索引都存放在ibd文件中
在这里插入图片描述

辅助索引

什么是辅助索引？

mysql在创建一张表时，会默认对这张表的主键建立主键索引，而对于其他非主键属性，不会自发地建立索引
所以如果我们想要对其他非主键属性建立索引，这种索引就叫做辅助索引

Myisam建立辅助索引的方式是什么？

对于存储引擎Myisam而言，建立辅助索引的本质就是以这个属性为比较标准，新创建一棵B+树
注意：在查询频率很高，修改频率很低的场景下，MyISAM用的比较多

Innodb建立辅助索引的方式是什么？

对于存储引擎Innodb而言，建立辅助索引的本质也是以这个属性为比较标准，新创建一棵B+树

但Innodb建立索引的方式与Myisam的区别在于：
Myisam的辅助索引对应B+树叶节点中保存的是数据页的指针 （索引键值 + 该记录对应的物理地址指针） ，但Innodb的辅助索引B+树叶节点中保存的仅仅是数据的一列主键信息 （索引键值 + 主键值），并不会把一行的所有数据都保存进叶节点

InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的主键值。所以通过辅助（普通）索引，找到目标记录，需要两遍索引：

首先检索辅助索引获得主键
然后用主键到主索引中检索获得记录

这种根据辅助索引查询记录的方式，就叫做回表查询

为什么Innodb的辅助索引叶节点中存的不是完整的数据记录，而只存该记录的主键值呢？

因为建立B+树也是很耗空间的，Myisam这种方式虽然简单，但如果我想给所有非主键属性（假如说8个）都做辅助索引，就得建8个和主键索引一样大小的B+树，太占空间了！
因此我们要采取一种用时间换空间的策略
而采用Innodb的方案，虽然增加了查找次数（要查一个辅助索引和一个主键索引），但是却大大减少了空间消耗（假如说一行数据总共36B，主键占4B，那同样的辅助索引，Myisam的一个叶节点大小就是Innodb的9倍）

什么叫做Innodb的回表查询操作？

假如说在stu表中，id作为主键，name作为一个普通属性，我现在想通过小明的名字在数据库中找到小明同学的完整信息。
对于存储引擎Myisam而言，我需要根据name这一栏建立一个辅助索引，然后拿着小明的名字在name这个辅助索引对应的B+树中查找，我总能够在某一个叶节点中找到小明同学的完整信息
但是对于存储引擎Innodb而言，我重复上面的步骤，最终只能在name这个辅助索引对应B+树的某一个叶节点中找到小明同学的主键信息（即学生id），所以这时候我们还需要拿着学生id去主键索引（主键对应的B+树）中查询，最终就可以在这棵B+树中找到小明同学的完整信息
上面拿着查辅助索引得到的主键值，再去查主键索引的动作，就被叫做回表操作

到此为止，我们对数据库的增删查改操作，就转化成了对一棵B+树的增删查改操作。
回头想想，我们是怎么做到这件事情的呢？就是通过创建索引结构完成的！这也就是创建索引结构最大的作用！

进一步认识索引

如何查看一张表中有哪些索引？

你这意思是一张表的索引还不止一个？
没错，主键索引+各种辅助索引，可不是不止一个嘛

查询索引的方法一共有三种

方法一：`show index from + 表名 + \G;（这个最常用）`

上面\G是啥意思？
要是不使用 \G，查询结果会以表格形式输出，也就是一行对应一条记录，一列对应一个字段。例如执行 SHOW INDEX FROM employees; 后，结果类似如下样式：
在这里插入图片描述

当查询结果的字段较多时，表格可能会很宽，需要左右滚动屏幕才能查看完整信息，这样不太方便查看。
当在指令末尾添加 \G 后，查询结果会以垂直格式输出，也就是一条记录的所有字段会依次罗列，每个字段单独占一行。例如执行 SHOW INDEX FROM employees\G，输出类似下面这样：
在这里插入图片描述

这种垂直格式的输出，能让每条记录的各个字段清晰展示，避免了表格过宽的问题，便于查看和分析查询结果，尤其是在字段较多的情况下优势更为明显。

方法二：`show keys from + 表名`

方法三：desc + 表名

什么样的属性字段应该被添加索引？

这个字段经常作为查询条件，即这个字段经常被查

什么样的属性字段不适合被添加索引？

唯一性太差的字段（比如性别，只有男和女两个值）
经常被修改的字段
不会出现在where子句中的字段

主键索引

什么是主键索引？如何创建一个主键索引？

就是基于主键字段创建的索引
你对数据库中的数据进行排序，然后分组，总得有个比较的依据吧，主键索引就是以主键值作为这个比较的依据，你比我大你就排在我后面

一般主键索引会在指定主键时默认创建好，也就是说，主键字段天然就具有主键索引，不用我们手动创建。建立主键索引的操作等价于将一个字段设置为主键的操作

那如何将一个字段设置为主键呢？

定义一个字段时，在其类型的后面加上primary key字段，比如：create table user1(id int primary key, name varchar(30));
在创建表语句的最后专门用primary key指定：create table user2(id int, name varchar(30), primary key(id));
创建表完成之后，专门再添加主键：alter table + 表名 + add primary key(主键字段);

如何删除主键索引？

删除主键索引的操作等价于取消这个字段主键身份的操作
alter table + 表名 + drop primary key(主键字段);

既然主键索引还快还好用（主键索引比辅助索引快很多），那为什么要有辅助索引，都用主键索引不就完了吗？

为了方便用户，用户觉得主键索引(id索引)不方便，用户就喜欢用类似于名字这样的关键字来进行索引

唯一键索引

什么是唯一键索引？唯一键索引和辅助索引有什么区别？

唯一键索引（Unique Key Index）是带有 “唯一性约束” 的辅助索引—— 在辅助索引 “加速查询” 的基础上，额外强制索引列（或列组合）的值 “不可重复”（部分数据库允许一个 NULL，如 MySQL）。它是辅助索引的特殊类型，本质是 “辅助索引 + 唯一性约束” 的结合体。

在 InnoDB 中，唯一键索引和普通辅助索引的B + 树结构完全一致，仅在 “数据写入时的校验逻辑” 上有差异。

对B+树执行插入/更新操作时，普通辅助索引是直接将 “键值 + 主键值” 插入索引的 B+ 树，无需判断键值是否重复；
而唯一键索引在插入 / 更新前，还会先在索引的 B+ 树中查询 “新键值是否已存在”。若存在：直接返回 Duplicate entry 错误，拒绝写入；若不存在：才将 “键值 + 主键值” 插入索引，保证索引键值唯一。

为什么唯一键索引查询效率高？

唯一键索引查询效率高的本质是键值唯一性”带来的精准定位能力 ：

键值不重复，使得 B+ 树查找可“一次命中”，无需范围扫描，从而减少了磁盘 I/O 次数和数据处理量；
与主键索引协同时，回表效率更高；（普通索引可能返回多个主键值，需多次回表，唯一键索引只需要一次回表）
覆盖查询场景下可直接返回结果，避免回表操作，减少额外开销。

这种效率优势在“等值查询”（=）场景中尤为明显，这也是唯一键索引适合作为“高频查询的唯一标识列”（如手机号、订单号）的核心原因。

唯一键索引与主键索引有什么区别

主键索引的特点是什么？

一个表中，最多有一个主键索引
主键索引的效率高（主键不可重复）
创建主键索引的列，它的值不能为null，且不能重复

唯一键索引的特点是什么？

一个表中，可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引，必须保证这列不能有重复数据

这俩的区别可以看看下面的表格

对比维度	唯一键索引（Unique Key Index）	主键索引（Primary Key Index）
唯一性约束	强制唯一，允许NULL（部分数据库）	强制唯一，不允许NULL（所有数据库）
数量限制	一张表可创建多个唯一键索引	一张表仅能有1个主键索引
InnoDB 中的角色	属于“辅助索引”，叶子节点存“索引值+主键值”	默认是“聚簇索引”，叶子节点存完整行数据
数据完整性作用	避免特定列（如手机号、邮箱）重复	唯一标识表中的每一行数据（行的“唯一ID”）

总结来说就是，唯一键索引和主键索引中的数据都不能重复，
但是主键索引中，不支持主键值为空的情况，而唯一键索引支持
如果一个唯一索引上指定not null，唯一键索引在功能上就等价于主键索引

唯一键索引的底层结构（以 InnoDB 为例）

在 InnoDB 存储引擎中，唯一键索引属于辅助索引，其 B+ 树结构与普通辅助索引一致，但会额外校验“值的唯一性”：

非叶子节点：存储“唯一键的键值 + 指向子节点的指针”，用于索引导航；
叶子节点：存储“唯一键的键值 + 对应的主键值”（而非完整行数据）。

当通过唯一键索引查询时，流程为：

在唯一键索引的 B+ 树中找到匹配的叶子节点，获取对应的主键值；
用主键值到“主键聚簇索引”中查找完整行数据（即“回表”操作，与普通辅助索引一致）。

示例：若user表主键为id，为email列建唯一键索引，则该索引的叶子节点存储(email值, id值)；查询WHERE email = 'a@xxx.com'时，先通过唯一键索引拿到id，再到主键索引取完整数据。

唯一键索引的适用场景

唯一键索引的核心价值是“在保证数据唯一性的同时加速查询”，典型适用场景包括：

存储“天然唯一”的业务字段，如用户的手机号、邮箱、身份证号（需避免重复注册/录入）；
关联表的唯一标识字段，如订单表的order_no（需确保订单号不重复，同时支持按订单号快速查询）；
多列组合唯一的场景，如课程表course中，teacher_id + course_time需唯一（同一老师同一时间不能上两节课），此时可建立“teacher_id + course_time”的组合唯一键索引。

唯一键索引的创建

基本创建语法（用UNIQUE KEY关键字修饰）

建表时创建：

CREATE TABLE user (
  id INT PRIMARY KEY AUTO_INCREMENT,
  phone VARCHAR(20) NOT NULL,
  email VARCHAR(50),
  -- 单个列的唯一键索引
  UNIQUE KEY idx_phone (phone),
  -- 组合列的唯一键索引
  UNIQUE KEY idx_email (email)
);

建表后添加：

-- 为已存在的表添加唯一键索引
ALTER TABLE user ADD UNIQUE KEY idx_phone (phone);
-- 或用 CREATE UNIQUE INDEX
CREATE UNIQUE INDEX idx_email ON user (email);

创建唯一键索引有什么条件吗？

你这个属性字段得符合唯一键的特性，即不能有重复的数据，确保数据的唯一性

和主键一样，当我们给某一列属性设定成唯一键之后，系统就会自动给唯一键创建对应的唯一键索引，所以建立唯一键索引的操作也等价于将一个字段设置为唯一键的操作
将一个字段设置为唯一键的操作也有三种：
1.创建表时指定：create table user4(id int primary key, name varchar(30) unique);
2.在创建表指令的最后指定：create table user5(id int primary key, name varchar(30), unique(name));
3.创建完之后单独指定

create table user6(id int primary key, name varchar(30));
alter table user6 add unique(name);

如何删除唯一键索引？

删除唯一键索引的方式和删除普通索引的方式是一样的，都是：alter table + 表名 + drop index + 索引名
那唯一键的删除格式就是：alter table + 表名 + drop index + 唯一键索引名

普通索引

普通索引名

对于唯一键索引和主键索引，他们的索引名就是主键属性名和唯一键属性名
对于普通索引，由于通过create语句创建普通索引时可以指定普通索引的名字，这种情况下普通索引的索引名就是create语句指定的名字
其他方式创建普通索引时，普通索引的索引名就还是索引对应的属性名

如何创建一个普通索引？

什么是普通索引？

不是唯一键索引的辅助索引就是普通索引

创建普通索引

创建普通索引也有三种方法：

在创建表的最后指定某一列为普通索引：index(索引字段)
在表创建完之后指定某一列为普通索引：alter table + 表名 + add index(索引字段);
在表创建完之后用create语句创建普通索引：create index 索引名 on 表名(索引字段)

如何删除一个普通索引？

删除唯一键索引的方式和删除普通索引的方式是一样的，都是：alter table + 表名 + drop index + 索引名
还有一种格式也可以：
drop index + 索引名 + on + 表名;

普通索引和唯一键索引的区别

普通索引中数据可以重复，唯一键索引不行
在实际应用的时候，其实我们普通索引用的最多

创建好了这些索引之后，我们如何使用这些索引快速查找呢？

就用select语句进行正常的查找操作即可
只要你建立好了这些索引，不用特地使用，它们自会给select提速

全文索引

什么是全文索引？

全文索引就是在一大段文字中去查找某个特定关键字
全文索引查找的对象是文本数据

全文索引和普通索引有什么区别？

（1）用途上的区别

全文索引主要用于需要在大量文本内容（如文章、博客、产品描述等）中查找包含特定关键词或短语的记录
例如，在一个新闻网站的文章数据库中，用户通过输入关键词搜索相关新闻报道，使用全文索引可以快速定位到包含这些关键词的文章。
普通索引主要用于精确匹配和范围查询。
普通索引常用于对数值型、日期型、字符串等字段进行快速查找，比如根据用户 ID 查询用户信息、根据订单日期范围查询订单记录等。在电商系统中，根据商品 ID 查找商品详情就可以使用普通索引提高查询速度。

（2）数据处理方式上的区别

全文索引会对文本内容进行分词处理，将文本拆分成一个个独立的单词或词组，并为这些词建立索引。 例如，对于句子 “我爱自然语言处理”，全文索引可能会将其分词为 “我”“爱”“自然语言处理” 等词，并分别记录它们在文档中的位置和出现频率等信息。
而普通索引直接对字段的值进行索引，不进行分词处理。
以用户表中的姓名字段为例，普通索引会将姓名作为一个整体进行索引，而不会对姓名进行拆分。

（3）索引结构的区别

全文索引通常采用倒排索引结构
倒排索引是一种从关键词到文档的映射关系，它记录了每个关键词在哪些文档中出现以及出现的位置等信息。这种结构使得在进行全文搜索时能够快速定位到包含关键词的文档。
普通索引常见的结构是B +树和哈希表
B - 树索引适用于范围查询和排序操作，它可以高效地支持大于、小于、等于等比较操作；哈希索引则基于哈希表实现，适用于精确匹配查询，能够在常数时间内完成查找操作。

mysql支持全文索引的条件是什么呢？

InnoDB 存储引擎支持全文索引，但该支持是从 MySQL 5.6 版本开始引入的。在这之前，MySQL 的全文索引功能主要由 MyISAM 存储引擎提供。
默认的全文索引仅支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)

全文索引的索引结构是什么呢？是B+树吗？

全文索引采用的索引结构并不是B+树，而是倒排索引结构

什么是倒排索引？什么是正排索引？

倒排索引就是通过分词找id。正排索引就是通过id找文章（数据）
在实际应用倒排的过程中，我们通常都是采用倒排+正排的联合索引，比如：先通过分词找id，再通过id找文章
本质就是建立两张哈希表，第一张建立分词与包含这个分词的文章id的映射关系，第二张建立映射文章id与文章内容的映射关系

下图就是倒排索引的示例

下图是正排+倒排联合索引的示例
在这里插入图片描述

对一张表建立全文索引的过程是怎样的？

如何创建全文索引？

如下图索引，这个articles表中包含文章标题title和文章内容body，我们可以在创建articles表语句的最后加上fulltext(title, body)，完成全文索引的创建
在这里插入图片描述

如何使用全文索引？

举个例子：我们现在想在表articles中查找关键词database，我们使用正常的select查找语句select * from articles where body like ‘%database%’; ，结果如下
在这里插入图片描述

我们通过explain工具预执行这条指令，发现在查找的过程中并没有使用到全网索引（key值为null表示没有用到索引）
在这里插入图片描述

那么究竟怎样才能使用全文索引呢？
全文索引不像普通索引一样，建立好之后我们正常select就可以使用，想要使用全文索引，就需要有专门的触发关键词

比如在上面我们通过fulltext(title, body)创建了一个全文索引，下面我们就可以在select语句中加入match(title, body) against ('检索关键字')来使用刚刚创建出来的全文索引（match语句表示啥意思？）
在这里插入图片描述

match语句的作用是指明查找的范围，比如使用 MATCH(title, content) 就表明要在title和content这两列中查找符合条件的内容。

此时我们再次通过explain工具，发现key值为title，在这次查找的过程中就使用到我们刚刚建立的全文索引了
在这里插入图片描述

复合索引（联合索引）

什么是复合索引？

类似于多个关键字匹配查询，就是你索引字段不止一个
联合索引（复合索引）是由多个字段组合而成的索引，在数据库中常以 B+树结构存储。以一个包含 (col1, col2, col3) 的联合索引为例，B+ 树节点首先按照 col1 的值进行排序，当 col1 的值相同时，再按照 col2 的值排序，若 col2 的值也相同，最后按照 col3 的值排序。

复合索引支持精准查询吗？支持范围查询吗？

复合索引对精准查询的支持较好，只要查询条件匹配最左前缀列就能有效利用索引加速查询。
对范围查询，只有当范围查询列处于复合索引的最左前缀列中，且后续列不影响索引使用的情况下，才能部分利用复合索引；如果范围查询列没有处于最左前缀，或者影响了后续列的索引使用，可能导致复合索引无法被使用。

复合索引的过程中一旦遇到范围查询，后面的字段将无法使用该复合索引进行查询（为什么）

数据库在使用联合索引进行查询时，会从最左边的字段开始，根据索引的有序性快速定位符合条件的记录范围。当遇到范围查询（如 >、<、BETWEEN 等）时，由于范围查询的结果不是一个确定的值，而是一个区间，这个区间内 col2 或者后续字段的值是无序的，数据库无法再利用索引的有序性来进一步快速定位后续字段的值，所以后面的字段无法再使用该联合索引进行查询。