[MySQL] MySQL中的索引

柔光的暖阳◎ 2024-04-22 22:25 13阅读 0赞

## ![5d04a40e545b493cad650a40754bd82a.gif][] ##

> **文章目录**
> 
> 一、初识索引
> 
> 1、1 索引的概念
> 
> 1、2 索引案例
> 
> 二、认识磁盘
> 
> 2、1 磁盘结构
> 
> 2、2 操作系统与磁盘的数据交互
> 
> 2、3 磁盘随机访问与连续访问
> 
> 2、4 MySQL与磁盘的数据交互
> 
> 三、索引的理解
> 
> 3、1 建立测试表
> 
> 3、2 为何MySQL与磁盘IO交互是 Page
> 
> 3、3 理解Page
> 
> 3、3、1 页目录
> 
> 3、3、2 多个Page
> 
> 3、4 索引所能够采用的数据结构 
> 
> 3、5 B树与B+树
> 
> 3、6 聚簇索引与非聚簇索引
> 
> 四、索引操作
> 
> 4、1 索引的创建
> 
> 4、1、1 主键索引的创建
> 
> 4、1、2 唯一索引的创建
> 
> 4、1、3 创建普通索引
> 
> 4、1、4 全文索引的创建
> 
> 4、2 查询索引
> 
> 4、3 删除索引
> 
> 4、4 索引创建原则
> 
> --------------------
> 
> **🙋♂️ 作者：@Ggggggtm 🙋♂️**
> 
> **👀 专栏：MySQL 👀**
> 
> **💥 标题：MySQL中的索引💥**
> 
>  **❣️ 寄语：与其忙着诉苦，不如低头赶路，奋路前行，终将遇到一番好风景 ❣️**

## 一、初识索引 ##

### 1、1 索引的概念 ###

> 在MySQL中，**索引是一种数据结构**，**用于提高查询效率和加速数据检索**。创建索引可以加快数据库中表的查询操作，减少查询所需的时间和资源消耗。但是天下没有免费的午餐，**查询速度的提高是以插入、更新、删 除的速度为代价的，这些写操作，增加了大量的IO**。所以它的价值，在于提高一个海量数据的检索速度。
> 
>  常见的索引类型有如下几种：
> 
>  *  主键索引（Primary Key Index）：用于唯一标识表中的每一行。一个表只能有一个主键索引。
>  *  唯一索引（Unique Index）：确保被索引的列中的值是唯一的。
>  *  普通索引（Normal Index）：最基本的索引类型，没有唯一性约束。
>  *  全文索引（Full-Text Index）：用于全文搜索，适用于大段文本的搜索。
> 
> 到这里你可能依然对索引的概念是一种模糊的状态，后文我们会详细解释。看会此篇文章再来回头看索引的概念，相信你就会理解了。

### 1、2 索引案例 ###

> 怎么来证明索引可以很好的提高查询效率呢？我们个人在平常使用数据库时，并没有感觉到查询的速度有很慢的情况。但是在实际应用中，**往往一张表中会包含了大量的数据**。**此时查询其中一条数据时，速度就会较慢**。接下来我们先弄出来了一张数据量较大的表，该表包含八百万条数据。具体的构建过程如下：
> 
>     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 ;
>     
>     -- 执行存储过程，添加8000000条记录
>     call insert_emp(100001, 8000000);
> 
> 注意：由于创建表的数据量过大，所以创建的过程中需要一定的时间。当创建完成时，不要查看表中所有的数据，因为数据量过大，且IO效率低。我们来看创建好后的表：
> 
> ![8ee595499592406db03369820e1a4e71.png][]

> 现在我们在此表中以员工工号来查询一个员工的数据，我们看看所用时间：
> 
> ![8cee3395673145a48b27260474a89366.png][]
> 
> 如上图所示，每条查询语句所用到的时间大概为4.5秒左右，且在查询的过程中明显能够感受到停顿。试想在实际应用中，查询一个用户的信息用了4秒时间，可以接受吗？当然不可以！下面我们创建一个索引，再来查询看看是否又效率提升！构建索引具体如下：
> 
> ![7eca1c9d2bdf48e8b8819a7140cc75b2.png][]
> 
> 上述我们也提到了，**索引本质上就是一种数据结构，当我们在给员工表中的工号建立索引时，实际上数据库底层就会以工号来构建特定的数据结构**。由于表中的数据量较大，建立索引时也需要花费较长时间。这时我们再来以工号查询员工信息，具体如下：
> 
> ![382f0649d9834a5896840f6751fff4e1.png][]
> 
> 这时候再查询时，发现速率大大的提升了。通过上图也能看到，耗费的时间小于0.01秒。那么问题来了：我们一直在说**索引本质上是一种数据结构，那到底是什么数据结构呢**？**在不添加索引的情况下，效率慢在哪里呢**？

## 二、认识磁盘 ##

> 为了数据持久化，最终mysql中的数据是要存储到磁盘上的。我们前面也了解到了，在创建数据库和表时，就是在对应的目录下创建了文件夹和特定文件。所以我们很有必要来了解一下磁盘。
> 
>  磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一个重要话题。

### 2、1 磁盘结构 ###

> 磁盘的整体结构如下图：
> 
> ![0c2466ab2c6e43e3b2e37ed4c4f5f9a1.png][]
> 
> 重要组成硬件解释：
> 
> 1.  **盘片：**磁盘中最重要的组成部分之一。通常，磁盘中会有多个盘片叠放在一起，每个盘片都是由坚固且非磁性的材料制成，如铝合金或玻璃。盘片上覆盖着一个可磁化的薄膜，用来存储数据。
> 2.  **磁头：**位于磁盘中的磁头负责读写数据。每个盘片都有两个磁头，一个位于盘片的上表面，另一个位于下表面。这些磁头能够飞行在盘片上方，通过电磁感应读取和写入数据。
> 3.  **主轴：**主轴是磁盘驱动器中的一个旋转轴，它用来支撑盘片并使其高速旋转。主轴由电机驱动，通常转速在5400到15000转/分钟之间。
> 4.  **永磁铁：** 机械硬盘的存储方式与磁带比较类似，磁体具有记忆的功能，永磁铁是为了保证磁性的稳定。
> 5.  **音圈马达：** 硬盘读取数据的关键部位，主要作用是将存储在磁盘上的信息转换为电信号向外传输。
> 6.  **空气滤波片：** 过滤空气硬盘透气孔中进入的空气，保证硬盘内部清洁，同时还可以防止硬盘内部的零件氧化，确保硬盘安全使用。
> 7.  **串行接口：** 用来连接电脑与硬盘的接口，起到传输的作用。
> 
>  在上图中我们看到盘片只有一片，实际上是有多片叠放在一起的。且每个盘片的两面都是可以存储数据的。具体如下图：
> 
> ![a4c69f38b22c4f60ac6611f835db077d.png][]
> 
>  每个盘片又被划分为多个区域，且每个区域所能够存储容量大小是相同的，我们称该区域为**扇区**。具体如下图：
> 
> ![d63549986db44575a91e4ee5f260b719.png][]
> 
>  从上图可以看出来，在半径方向上，距离圆心越近，扇区越小，距离圆心越远，扇区越大那么，所有扇区都是默认512字节吗？目前是的，我们也这样认为。因为保证一个扇区多大，是由比特位密度决定的。不过最新的磁盘技术，已经慢慢的让扇区大小不同了，不过我们现在暂时不考虑。 
> 
>  从上图中也能看出，盘片表面划分为多个同心圆轨道，每个轨道称为一个磁道。实际上是每个磁道又被细分为扇区，用于存储数据。

>  数据库文件就是保存在磁盘中的一个个扇区中的，因此找到一个文件本质就是，在磁盘上找到保存该文件的所有扇区。而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。那么**怎么可以很快的定位到一个扇区呢**？
> 
>  我们先看如下图：
> 
> ![b9c3a38dc8814bf69cd2ff1ae64d889d.png][]
> 
>  柱面是多盘磁盘，每盘都是双面，大小完全相等。那么同半径的磁道，整体上便构成了一个柱面 。每个盘面都有一个磁头，那么磁头和盘面的对应关系便是1对1的。
> 
>  那么现在寻址的思路就很明确了：**先确定柱面，在确定磁头。这下就确定在那个盘片上了。然后在找对应的扇区，就可以很好的找到对应的存储位置了**。我们也称之为**CHS寻址（柱面（Cylinder）- 磁头（Heads）- 扇区（Sector）寻址）**。 在CHS寻址中，通过指定柱面号、磁头号和扇区号来确定数据的物理位置。通过这三个参数，计算机可以准确地访问磁盘上的数据。
> 
> **不过实际系统软件使用的并不是 CHS （但是硬件是），而是 LBA ，一种线性地址，可以想象成虚拟地址与物理地址**。系统将 LBA 地址最后会转化成为 CHS （这个转换过程由磁盘控制器负责执行，它根据磁盘的物理结构和磁盘参数表来进行适当的转换），交给磁盘去进行数据读取。

### 2、2 操作系统与磁盘的数据交互 ###

>  我们在对mysql内部的数据等做操作(CURD)的时候，本质其实就是操作文件内容，那么文件必须先被打开。对文件内容做任何操作，都不是直接在磁盘设备上做操作的！通过冯诺依曼结构我们知道，CPU只与内存进行数据交互。这就意味着**任何磁盘数据，在进程中要进行操作，本质都必须在内存中进行**！那么在**对磁盘数据进行操作时，一定会先将磁盘的数据加载到内存当中，由操作系统进行管理**。
> 
>  操作系统与磁盘进行IO交互的基本单位是4KB，而不是扇区的大小512字节，原因如下：
> 
> 1.  物理内存实际是被划分成一个个4KB大小的页框的，磁盘上的数据也会被划分成一个个4KB大小的页帧，因此操作系统与磁盘以4KB为单位进行IO交互，就能提高数据加载和保存的效率。
> 2.  操作系统与磁盘进行IO交互时，如果直接以扇区的大小作为IO的基本单位，那么这时系统的IO代码和硬件就是强相关的，将来当硬件的扇区大小发生变化时就需要对应修改操作系统的IO代码。主要实现了OS与磁盘的解耦。
> 3.  此外，以扇区的大小作为IO的基本单位太小了，这就意味着读取同样的数据内容，需要进行更多次的磁盘访问，而磁盘的效率是比较低的，这样IO效率就降低了。以4KB为基本单位可以减小IO次数。

### 2、3 **磁盘随机访问****与连续访问** ###

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

> **连续访问：** 本次IO所给出的扇区地址与上次IO给出的扇区地址是连续的，磁头很快就能找到目标扇区进行IO。

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

### 2、4 MySQL与磁盘的数据交互 ###

> 而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB。我们通常称这个基本单元为 Page。这里的 Page 与系统的 Page 并不相同，需要区分一下的。 在Linux下，MySQL分为客服端（mysql）与服务端（mysqld）。
> 
>  *  上文中也提到了，在MySQL中进行的各种CRUD操作时，都需要先通过计算找到对应的操作位置，只要涉及计算就需要CPU参与，而冯诺依曼体系结构决定了CPU只能和内存打交道，因此为了便于CPU参与，就需要先将数据加载到内存当中。
>  *  那么在特定的时间内，MySQL中的数据一定是同时存在于磁盘和内存中的，当操作完内存数据后，再以特定的刷新策略将内存中的数据刷新到磁盘当中，这时MySQL和磁盘进行数据交互的基本单位就是Page。
>  *  为了更好的支持上述操作，MySQL服务器在启动的时候会预先申请一块内存空间来进行各种缓存，这块内存空间叫做Buffer Pool，后续磁盘中加载的数据就会保存在Buffer Pool中，刷新数据时也就是将Buffer Pool中的数据刷新到磁盘。
>  *  由于内核中是有内核缓冲区的，因此MySQL从磁盘读取数据时，需要先将数据从磁盘读取到内核缓冲区，再将数据从内核缓冲区读取到Buffer Pool，MySQL将数据刷新到磁盘时，同样需要先将数据从Buffer Pool刷新到内核缓冲区，再将数据从内核缓冲区刷新到磁盘。
> 
> ![063c8e9cf2664c61b942fbcfca347ff8.png][]
> 
> 实际上，mysqld并不直接与磁盘打交道，而是利用了中间的内核缓冲区。所谓MySQL 进行IO的基本单位是 16KB，我们可以理解为一次从Buffer Pool向内核缓冲区 读取/写入 的大小为16KB，然后再让内核缓冲区 读取/刷新 到磁盘上。

## 三、索引的理解 ##

### 3、1 建立测试表 ###

> 建表代码如下：
> 
>     create table if not exists user (
>         id int primary key, --一定要添加主键哦，只有这样才会默认生成主键索引
>         age int not null,
>         name varchar(16) not null
>     );
> 
> 具体如下图：
> 
> ![6c2574ad6af247809bdf2408dde68fa5.png][]
> 
> 我们在向表中插入一些数据，具体如下图：
> 
> ![474e2a3003144ad5b7ebc0df45a2f018.png][]
> 
> 发现插入数据时id值是乱序进行插入的，但是我们查询表中的信息发现是有序的！**排序的工作是谁做的呢**？**为什么要进行排序呢**？

### 3、2 **为何MySQL与磁盘****IO****交互是** Page ###

>  为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少不香吗? 
> 
>  如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。 如果要找id=5，那么就需要5次IO。 但如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查id=1,3,4,5等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，**大大减少了IO的次数**。我们知道IO的效率是很慢的。
> 
>  你怎么保证，用户一定下次找的数据，就在这个Page里面？我们不能严格保证，但是有很大概率，因为有局部性原理。什么是局部性原理？**计算机的局部性原理**（Locality Principle）是指在程序执行过程中，访问内存的趋势倾向于集中在某些特定的内存区域或数据块上。当一个数据被访问后，其附近的数据也很可能会被访问。比如，当程序访问某个内存地址时，它很可能会在接下来的指令中继续访问相邻的内存地址。这种局部性原理可以通过预取（Prefetching）和预读（Prefetching）等技术来优化内存访问效率。
> 
> **往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数**。

### 3、3 理解Page ###

> 在MySQL中，不仅仅是只有一张表。大概率会同时管理着多张表数据。那么而要管理好这些表，就需要 **先描述，在组织** 。我们目前可以简单理解成一个个独立表是有一个或者多个Page构成的。那么MySQL将内存中的每一个Page都用一个结构体描述起来，然后再将各个结构体以双链表的形式组织起来，因此一个Page结构体内部既包含数据字段，也包含属性字段。我们用上述的测试数据来理解，如下图：
> 
> ![499c72e595f648f19b66407c610c5c86.png][]
> 
> 我们也知道，再插入数据时是无序的，为什么查询的时候就有序了呢？也就是为什么要进行排序。我们接着往下看。

#### 3、3、1 页目录 ####

> 我们知道，当一张表中的数据很多时，查询的速度就会变的很慢。本质上还是因为进行的遍历查询。那么有没有什么办法，能够**加快我们的查询速度**呢？我们在看《谭浩强C程序设计》这本书的时候，如果我们要看<指针章节>，找到该章节有两种做法：
> 
> 1.  从头逐页的向后翻，直到找到目标内容（遍历，较慢）。
> 2.  通过书提供的目录，发现指针章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的方案，可以顺序找，不过因为目录肯定少，所以可以快速提高定位。
> 3.  本质上，书中的目录，是多花了纸张的，但是却提高了效率。
> 4.  所以，目录，是一种“空间换时间的做法” 
> 
> 通过上述例子，我们就想到：**一个Page中能不能也引入目录呢？答案是可以的**。MySQL也正是这么做的。具体如下图：
> 
> ![f763c1de752242ff9ed8040f4fe5f320.png][]
> 
> 当数据量很大的时候，目录就起到的很好的过滤无用数据的作用。那么当前，在一个Page内部，我们引入了目录。比如，我们要查找id=4记录，之前必须线性遍历4次，才能拿到结 
> 
> 果。现在直接通过目录 2\[3\]，直接进行定位新的起始位置，提高了效率。当我们在添加页内目录时，Page内部能够保存的数据记录变少了，所以 **在Page内部引入页内目录本质是一种空间换时间的做法，就像给书添加目录需要花费更多的纸张一样**。
> 
>  但现在或许你就理解了**为什么要对插入的数据进行排序**了。**就是为了能够更好的引入目录，提高查询的速度**。因为我们在创建表的时候，设置了id为主键，所以MySQL会以主键为索引进行排序的。

> 但是，随着数据量不断增大，单个Page中无法存下所有数据，这时就需要用多个Page来存储数据。这时在查询数据时就需要，先遍历Page双链表确定目标数据在哪一个Page，然后再在该Page内部找到目标数据。具体如下图：
> 
> ![14b2f628eba44171b97a84f75ddfa251.png][]
> 
> 那么当我们线性遍历在那个Page当中时，注意访问Page时，就需要将Page从磁盘加载到内存当中，这就意味着需要多次IO，效率也是比较低的。怎么解决多个Page效率低的问题呢？

#### 3、3、2 多个Page ####

> 能不能在多个Page之上再次引入目录呢？答案是可以的。解决方案，其实就是我们之前的思路，给Page也带上目录。 
> 
> 1.  使用一个目录项来指向某一页，而这个目录项存放的就是将要指向的页中存放的最小数据的键值。 
> 2.  和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。 
> 3.  其中，每个目录项的构成是：键值\+指针。
> 
> 具体如下图：
> 
> ![ab5c22e5a1074089a3504a92762d1aa2.png][]
> 
> 随着数据量不断增大，Page变得越来越多，这时一个页目录无法管理所有的Page，这时就需要更多个的页目录。这些页目录也是一个个的Page结构体，只不过这些Page结构体中存放的不是数据记录，而是各个Page的目录信息。
> 
> 那么好了，当数据量更多的时候，存在着很多的用Page存储目录时，我们只需要在该Page之上再次创建Page目录进行管理即可。具体如下图：
> 
> ![6d9af1397c794b719d9e1618cf35a802.png][]
> 
> 我们上图中，最终构建出来的就是一棵B+树。到这里，我们已经给我们的表user构建完了主键索引。我们接下来再来**详细的分析和总结一些问题**。
> 
> 1.  索引本质上就是一种数据结构，那么这个数据结构就是指的B+树。
> 2.  当我们创建一个表时，如果该表中有主键，那么MySQL在底层就会自动将这张表中的的数据以B+树的形式组织起来，保存在Buffer Pool当中，当我们查询数据时就可以通过查询这棵B+树来提高查询效率。
> 3.  那要是没有主键呢？MySQL会自动形成隐藏主键，同样会构建成B+树将数据存储起来。只不过我们在查询时，并不是按照主键进行查询的，所以本质上还是进行的线性遍历。
> 4.  我们上述讲述的B+树存储结构中，只有在叶子节点中存储数据。
> 5.  一个Page的大小为16KB，可存储大量数据。当一个Page中只存储目录项时，可想而知能够管理多大的数据。所以B+树整体是一颗矮胖的多叉树。查找的时候，自定向下找，只需要加载部分目录页到内存（并不需要加载整颗B+树，最开始只需要根节点即可），即可完成算法的整个查找过程，大大减少了IO次数。
> 
>  相信到这里，你就会对索引有一个很好的了解。那么索引能不能采用其他的数据结构呢？

### 3、4 索引所能够采用的数据结构 ###

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

### 3、5 B树与B+树 ###

> MySQL的索引采用了B+树，为什么不采用B树呢？
> 
>  *  普通B树中的所有结点中都同时包括索引信息和数据信息，由于一个Page的大小是固定的，因此非叶子结点中如果包含了数据信息，那么这些结点中能够存储的索引信息一定会变少，这时这棵树形结构一定会变得相对B+树更高更瘦，当查询数据时就可能需要与磁盘进行更多次的IO操作。
>  *  普通B树中的各个叶子结点之间没有连接起来，这将不利于进行数据的范围查找，需要多次遍历，这就意味着会有更多的IO操作。而B+树的各个叶子结点之间是连接起来的，当我们进行范围查找时，直接先找到第一个数据然后继续向后遍历找到之后的数据即可，因此将各个叶子结点连接起来更有利于进行数据的范围查找。
> 
> 下图是B树的结构，可以结合的理解一下：
> 
> ![a40ffd64d80443dd93c9fa4c78f21f70.png][]

### 3、6 聚簇索引与非聚簇索引 ###

> **聚簇索引（Clustered Index）**： 聚簇索引是将表的数据按照索引键的顺序物理地存储在磁盘上。每张表只能有一个聚簇索引，因为它决定了表中数据的物理存储方式。当创建聚簇索引时，表的数据会根据索引键的顺序进行重新排序，并且数据行的物理位置与其在索引键中的顺序一致。
> 
> 我们上述讲解索引的数据结构B+树时，所解释索引的类型就是聚簇索引。**最明显的特点就是数据存储在B+树的叶子节点当中**。在前面文章我们也讲到过，再创建一张表时默认的存储引擎是InnoDB。具体如下图：
> 
> ![2ab8c3f7c1e046caac1c5e860e8e837e.png][]
> 
> 那我们大概也就知道了，**InnoDB存储引擎的索引结构采用的是B+树，同时索引类型为聚簇索引**。

> **非聚簇索引（Non-clustered Index）**： 非聚簇索引是在单独的数据结构中存储索引键和对应的行指针，而不是存储实际的数据行。一个表可以有多个非聚簇索引。非聚簇索引通过维护一个索引树，加速数据查询的过程。查询操作首先定位到索引树上的特定值，然后使用该值来获取对应的行指针，最后再通过行指针访问实际的数据行。
> 
> **MyISAM存储引擎的索引类型就是采用的非聚簇索引**。其索引的结构也是B+树。**最大的特点就是B+树中不再存储有效数据，而是存储的指针，该指针指向的就是数据所在的地址**。下图为MyISAM存储引擎的主键索引结构，其中Col1为主键。如下：
> 
> ![0fdd4d386c3e4aebbad778409a8a0a64.png][]
> 
>  当然， MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。 
> 
>  MyISAM存储引擎的普通索引采用的也是B+树结构，与主键索引唯一不同的地方就是普通索引的B+树中的键值可以重复。下图为MyISAM存储引擎的普通索引结构，其中Col2为索引列，和主键索引没有差：
> 
> ![dfaa14c917cd498e9070f15907159f12.png][]

>  有一个问题：我们知道创建索引时，本质上就是以我们所选中的列创建对用的数据结构，比如主键索引就会以主键为节点的数据，来创建B+树。但是我们也能够创建普通索引，这也就意味着一张表会存在多个索引和多个B+树。**有多个B+树的同时，那么会不会每个B+树中都存储了数据信息呢？也就是会不会造成空间的浪费呢**？首先，MyISAM存储引擎采用的是非聚簇索引，B+树中存储的是指针，所以并不会造成空间浪费。那InnoDB呢？我们以上表中的 Col3 建立对应的辅助索引如下图：
> 
> ![c8801a6c48d54939a5e6c7d6ba235a1f.png][]
> 
>  可以看到，**InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值**。 
> 
> 所以通过辅助（普通）索引，找到目标记录，需要两遍索引：**首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询**。
> 
> InnoDB存储引擎的普通索引的B+树叶子结点中没有保存整条数据记录，是为了节省空间，因为同一张表可能会创建多个普通索引，每个普通索引的B+树中都保存一份数据会造成数据冗余，所以通过回表查询主键索引对应的B+来获取整个数据记录，该做法本质一种以时间换取空间的做法。当根据普通索引查询数据时，其实也不一定需要进行回表查询，因为有可能我们要查询的就是这条记录对应的主键值，因此查询完普通索引对应B+树后即可完成查询。

> 我们接下来观察一下InnoDB存储引擎和MyISAM存储引擎两者所形成的文件的区别。
> 
> 我们现在创建一个表时，指定其存储引擎为InnoDB，我们观察一下其形成的文件。建表如下：
> 
> ![09ff04006aed4a1b959e61e008d52cc2.png][]
> 
> 我们在 \\var\\lib\\mysql目录下查看其形成的文件。具体如下：
> 
> ![49b6071c7e0d4867a69a874f127979a7.png][]

> 我们再来创建一个表，该表的存储引擎为MyISAM，具体建表如下图：
> 
> ![2fb2fcdc8d994d358b379d0e73514494.png][] 在 \\var\\lib\\mysql目录下形成对应的文件。具体如下：
> 
> ![52a8bfbed3e042a2aa602f7d900dadd6.png][]

> 通过上述的对比我们来总结一下：
> 
> 1.  采用 InnoDB 和 MyISAM 存储引擎创建表时都会生成`xxx.frm`文件，该文件中存储的是表结构相关的信息。
> 2.  当创建一个存储引擎为InnoDB表时，会生成一个与表名相同的.ibd文件，其中包含了数据页和索引页。这就是聚簇索引，索引和数据是存储在同一个文件中的。
> 3.  InnoDB是MySQL的默认存储引擎，它支持事务处理和行级锁定。InnoDB的聚簇索引是通过B+树数据结构实现的，将主键索引与实际数据存储在一起。
> 4.  InnoDB的聚簇索引是按照主键顺序进行存储的，这样可以提高查询效率。
> 5.  当创建一个MyISAM表时，会生成与表名相同的.MYI和.MYD文件，分别存储索引和数据。这就是所谓的非聚簇索引，索引和数据是分开存储的。
> 6.  MyISAM是MySQL的另一个存储引擎，不支持事务处理和行级锁定。MyISAM的非聚簇索引也是通过B+树数据结构实现的，但是将索引和实际数据分开存储。

## 四、索引操作 ##

### 4、1 索引的创建 ###

#### 4、1、1 主键索引的创建 ####

>  第一种方式：在创建表的时候，直接在字段名后指定 primary key。具体如下：
> 
> ![ef0fb315d10940278558439a4ebd2719.png][]
> 
> 第二种方式：在创建表的最后，指定某列或某几列为主键索引。具体如下图：
> 
> ![813b03272ac745de972153975180c319.png][]
> 
> 第三种方式：创建表以后再添加主。具体如下图：
> 
> ![514a19190193469d9f45c793d3df9af4.png][]
> 
> 主键索引的特点： 
> 
> 1.  一个表中，最多有一个主键索引，当然可以使复合主键。
> 2.  主键索引的效率高（主键不可重复）。
> 3.  创建主键索引的列，它的值不能为null，且不能重复。
> 4.  主键索引的列基本上是int。

#### 4、1、2 **唯一索引的创建** ####

> 第一种方式：在表定义时，在某列后直接指定unique唯一属性。具体如下：
> 
> ![bf6fb353ba6e4634b30b18c807d18e5a.png][] 第二种方式：创建表时，在表的后面指定某列或某几列为unique 。如下：
> 
> ![584fc153d1724276b5c76aea4d5a4d49.png][]
> 
> 第三种方式：创建表以后再添加唯一键。具体如下：
> 
> ![1ce67851abc64631ab1b6241a39f2847.png][]
> 
>  唯一索引的特点： 
> 
> 1.  一个表中，可以有多个唯一索引。
> 2.  查询效率高。
> 3.  如果在某一列建立唯一索引，必须保证这列不能有重复数据，可以为NULL。
> 4.  如果一个唯一索引上指定not null，等价于主键索引。

#### 4、1、3 创建普通索引 ####

> 第一种方式：在表的定义最后，指定某列为索引。具体如下图：
> 
> ![772bc34899db4b7b834ece66d202b037.png][]
> 
>  第二种方式：创建完表以后指定某列为普通索引。如下：
> 
> ![bbe747d210674c10b877d8d03e7e326a.png][]
> 
> 第三种方式：创建一个索引名为 idx\_name 的索引，相当于我们可以给索引指定名称（下文会解释）。具体如下：
> 
> ![6583f91d028f4a43bb94cb52d271e56f.png][]
> 
> 普通索引的特点： 
> 
> 1.  一个表中可以有多个普通索引，普通索引在实际开发中用的比较多。
> 2.  如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。

#### 4、1、4 **全文索引的创建** ####

> 当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求：你需要确保你的表使用的是支持全文索引的存储引擎，所以可选存储引擎是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。全文索引的创建如下：
> 
> ![5c9ff0a8796247b3b3ed08dc8fe2883a.png][]
> 
> 上图是通过fulltext给title和body列创建全文索引。接下来我们再插入一下数据，如下图：
> 
> ![9c47702a0d404eadb79ec2f03004c4c9.png][]

> 如果要查询哪些文章中包含database关键字，当然我们可以通过模糊匹配进行查找。如下：
> 
> ![4178a41c5c6e4912a6eca9829618cb81.png][]
> 
> 但是一旦数据量过大时，最终是需要进行线性遍历的，查询速度会很慢。我们可以使用可以用explain工具看一下，是否使用到索引。如下图：
> 
> ![db8ff4f0e358468986f8f10521de0bd1.png][]
> 
> 上图中的 key 为NULL。标示并没有用到任何索引。那么如何使用全文索引呢？使用全文索引进行查询： 一旦创建了全文索引，你可以使用**MATCH AGAINST语句来执行全文搜索查询**。这个语句将会返回与搜索条件匹配的记录，并根据相关度进行排序。使用模板如下：
> 
>     SELECT * FROM articles WHERE MATCH(title, content) AGAINST('keyword');
> 
> 具体使用例子如下：
> 
> ![1702782feba046489f65ef808e525304.png][]
> 
> 我们再次使用explain工具看一下，具体如下：
> 
> ![864f8a73d9eb4841ab43ae4e7aaf5814.png][]

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

### 4、2 查询索引 ###

> **第一种方法：****show keys from 表名\\G**。上述在创建普通索引时，我们提到可以给索引命名。那么我们看一下user10表中的索引。user10的表结构如下：
> 
> ![0ebdbba9d48f43a58c21a8ba9d2e9c28.png][]
> 
>  查询结果具体如下：
> 
> ![4c9ebda4f39a466ba1e79d96d4103f84.png][]
> 
> 下面是对查询结果中的属性进行解释：
> 
> 1.  **`Table`**: 表示索引所属的表名。
> 2.  **`Non_unique`**: 表示索引是否是唯一索引。如果值为0，表示索引是唯一索引；如果值为1，表示索引不是唯一索引。注意，这里的唯一索引指的是在索引列上的值是否允许重复。
> 3.  **`Key_name`**: 表示索引的名称。
> 4.  **`Seq_in_index`**: 表示索引中列的顺序。如果一个索引由多个列构成，在该属性中会显示每个列的顺序号。
> 5.  **`Column_name`**: 表示索引中的列名。
> 6.  **`Collation`**: 表示列排序规则。例如，utf8\_general\_ci表示不区分大小写的Unicode字符集。
> 7.  **`Cardinality`**: 表示索引中唯一值的数量估计。该值用于优化查询语句，较大的值通常意味着更好的性能。
> 8.  **`Sub_part`**: 表示索引列的部分长度。如果整个列被索引，该值为NULL。
> 9.  **`Packed`**: 表示如何存储索引。如果为NULL，表示没有使用特殊的存储方式。
> 10. **`Null`**: 表示索引列是否允许NULL值。如果值为YES，表示列允许为NULL；如果值为NO，则表示列不允许为NULL。
> 11. **`Index_type`**: 表示索引的类型。常见的索引类型包括BTREE（B+树索引）和HASH（哈希索引）等。
> 12. **`Comment`**: 提供关于索引的额外注释信息。

> **第二种方法：show index from 表名;** 具体如下图：
> 
> ![cc97a537f20b4e208c5b028232d755ce.png][]
> 
> **第三种方式：desc 表名；**SQL查询（信息比较简略），比如查询articles表中的索引信息。如下：
> 
> ![d87a2a561dfe44548890a0d1eac9e134.png][]

### 4、3 删除索引 ###

> **第一种方法\-删除主键索引： alter table 表名 drop primary key****。**具体如下图：
> 
> ![95118793ab474119bfbd5c3f0d1211d4.png][]
> 
> 由于主键只有一个，所以我们在删除的时候并不需要指定其列名。
> 
> **第二种方法-其他索引的删除： alter table 表名 drop index** **索引名。**注意：**索引名就是show keys from** **表名中的Key\_name 字段**。具体例子如下：
> 
> ![b648cacdcbac40c39ef9406c3d9a188e.png][]
> 
> **第三种方法方法： drop index 索引名 on** **表**。具体如下图：
> 
> ![d8422a1a089f4872a11f65fc1947c263.png][]

### 4、4 索引创建原则 ###

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

[5d04a40e545b493cad650a40754bd82a.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/ab85cde2ace64a54b36b482c517533f3.gif
[8ee595499592406db03369820e1a4e71.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/5d029cb624774c32ae26c0d6885f09a7.png
[8cee3395673145a48b27260474a89366.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/75395ad110404b40ade38bc3e73d87b1.png
[7eca1c9d2bdf48e8b8819a7140cc75b2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/207b1d0929134494844593c374d1b312.png
[382f0649d9834a5896840f6751fff4e1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/6175be805d1f40d587bdae9504541311.png
[0c2466ab2c6e43e3b2e37ed4c4f5f9a1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/d33c72e481424cd6ac50746bf3b1d509.png
[a4c69f38b22c4f60ac6611f835db077d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/5b696963abee49baa48499f1d95410a4.png
[d63549986db44575a91e4ee5f260b719.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/70b9f45660dc477b99bedb564620ad9e.png
[b9c3a38dc8814bf69cd2ff1ae64d889d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/e1295d61e8ad480a97b4604ea37ad605.png
[063c8e9cf2664c61b942fbcfca347ff8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/d38cb10e9b2a438f8056c23da4ab6071.png
[6c2574ad6af247809bdf2408dde68fa5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/e5d2e1c76e264b92a48028686f76a29a.png
[474e2a3003144ad5b7ebc0df45a2f018.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/73a503ca21804824a7fb0c3799c53b97.png
[499c72e595f648f19b66407c610c5c86.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/e0f0050e2e87406a8636affd9e20711f.png
[f763c1de752242ff9ed8040f4fe5f320.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/468832f9e5ee4cfbaf7752c0b6fcfe69.png
[14b2f628eba44171b97a84f75ddfa251.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/fb78973d4e2149ab8d7d90b6e14c956f.png
[ab5c22e5a1074089a3504a92762d1aa2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/fdae90ac95244abfbe5e322fc3bd83f6.png
[6d9af1397c794b719d9e1618cf35a802.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/d0a22ebe783e4ecf83ebf34b84f66c9b.png
[a40ffd64d80443dd93c9fa4c78f21f70.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/318e0439708a4cd6ab2798abeb9eba23.png
[2ab8c3f7c1e046caac1c5e860e8e837e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/3df63726f8da460baf5dae413610bdb2.png
[0fdd4d386c3e4aebbad778409a8a0a64.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/26cb65d9a513443897b0e47737ca294b.png
[dfaa14c917cd498e9070f15907159f12.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/4fd1e710a1a445808483b849c3016f28.png
[c8801a6c48d54939a5e6c7d6ba235a1f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/23171f1b3ce040298535616d12c19d22.png
[09ff04006aed4a1b959e61e008d52cc2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/aedb93e7652c4e91b97b99ffd0688565.png
[49b6071c7e0d4867a69a874f127979a7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/175c5dcf07af40588a3f80010a508cb2.png
[2fb2fcdc8d994d358b379d0e73514494.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/c9d87d56371b4c5898ed3575660661f8.png
[52a8bfbed3e042a2aa602f7d900dadd6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/9c93441714a54270b1492f8f086d7726.png
[ef0fb315d10940278558439a4ebd2719.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/065b817043b844c59136cccefa9465b5.png
[813b03272ac745de972153975180c319.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/672727db7bcc40e383a9c43d3f3b6e8d.png
[514a19190193469d9f45c793d3df9af4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/65e433da923746b58b3687f73b81dd58.png
[bf6fb353ba6e4634b30b18c807d18e5a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/69f8c60d216a4c5d8d9d35060650c832.png
[584fc153d1724276b5c76aea4d5a4d49.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/84df2ca2945843cc8d8a3243dff0f937.png
[1ce67851abc64631ab1b6241a39f2847.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/82a1de7af0de443193c0f9db7e81d0ed.png
[772bc34899db4b7b834ece66d202b037.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/30afc9cbe0684fba90e55bd23065be13.png
[bbe747d210674c10b877d8d03e7e326a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/316d3527ee5e48f5bc7a973f7a181408.png
[6583f91d028f4a43bb94cb52d271e56f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/5804b4a916684e06b89ce3c545c576d2.png
[5c9ff0a8796247b3b3ed08dc8fe2883a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/e8fe613d828d42e2a9c25cf7659323a8.png
[9c47702a0d404eadb79ec2f03004c4c9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/65e90404aa614e0eb26c9828e61f26a3.png
[4178a41c5c6e4912a6eca9829618cb81.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/51ee637379bc448a963f966e607562f1.png
[db8ff4f0e358468986f8f10521de0bd1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/5340290ab8cc42bab22217b3cf376eb5.png
[1702782feba046489f65ef808e525304.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/3d58991ee3f04a7b91149341798e260a.png
[864f8a73d9eb4841ab43ae4e7aaf5814.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/c4da168d11794925ae3b342b7bccdd7d.png
[0ebdbba9d48f43a58c21a8ba9d2e9c28.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/2d5c54ec51da4d2a9a6b8b0e6d51aed2.png
[4c9ebda4f39a466ba1e79d96d4103f84.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/e36c05648d6249afa3a5a7d8528e0598.png
[cc97a537f20b4e208c5b028232d755ce.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/8b2b872eea744e9495361d5d76ddc22e.png
[d87a2a561dfe44548890a0d1eac9e134.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/468dd599e0b54bbeaf9a59d92a80e136.png
[95118793ab474119bfbd5c3f0d1211d4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/bbb6d6804e40469e9ade8e349ba457a1.png
[b648cacdcbac40c39ef9406c3d9a188e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/ab9267b773f040a7a23c71fa2464d030.png
[d8422a1a089f4872a11f65fc1947c263.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/22/f7d41bdcfe4448609b62fa194ac3a8c8.png