大家好，我是讯享网，很高兴认识大家。



 <svg xmlns="http://www.w3.org/2000/svg" style="display: none;"> <path stroke-linecap="round" d="M5,0 0,2.5 5,5z" id="raphael-marker-block" style="-webkit-tap-highlight-color: rgba(0, 0, 0, 0);"></path> </svg> <p>​ 本篇将详细的介绍有关MySQL中索引的知识&#xff0c;分别从底层硬件的特性和上层软件的实现来介绍关于MySQL中索引的相关知识。然后介绍了索引的创建&#xff0c;索引的查看&#xff0c;索引的删除</p> 

讯享网

​ 其中索引一共分为：主键索引、唯一键索引、普通索引、全文索引。

​ 索引的核心功能为：提供数据库的性能。

没有索引，将会出现什么问题

​ 索引：提高数据库的性能。不用加内存，不用改程序，不用调用sql，只需要执行正确的create index，查询速度就可以提高成百上千倍。虽然查询的速度提高了，但是是用插入、更新、删除的效率作为代价的，这些写操作增加了大量的IO，但在IO的同时，索引也会随之改变，所以索引也会给IO加入负担。所以索引的作用为提高海量数据的检索速度。

​ MySQL的服务器：mysqld本质是在内存中的，所有数据库中的CURD操作，全部都是在内存中进行的，索引也是如此。

​ 想要提高算法效率可以有两个条件：

​ 1. 组织数据的方式

​ 2.算法本身

​ 算法本身也就是算法设计的思路，比如动态规划、递归、dfs等，组织数据的方式就是数据被存储下的结构，比如按顺序表存储、按链表存储、或者顺序存储（数据组织方式和算法对应选择）。这些都可以提高算法的效率/

创建海量数据

​ 为了体现索引的速度，我们使用的800万条数据来凸显有索引和没有索引的效率区别，如下：

​ 1. 先在本地某个目录下创建一个SQL文件：index_data.sql：

讯享网

​ 2. 在MySQl命令行中执行source 目录/index_data.sql，如下：

​ 如上所示，该操作可能会花一些时间，几分钟到十几分钟不等。

​ 当如上操作成功之后，我们尝试将在这个表中查询一些数据，如下：
讯享网
​ 如上所示，当我们在表中查询数据的时候，需要查询两秒多才能查询到一条对应的数据，这样的查询速率是非常慢的，

​ 想要解决这样的效率问题，我们可以在表中对对应的列加上索引，如下：

​ 创建索引语法：

讯享网

​ 查询如下：

​ 如上所示，创建出索引之后，查询的效率明显提升。

MySQL与磁盘

​ MySQL给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设中。磁盘是计算机中一个机械设备，相比于计算机中其他的一些电子元件，磁盘的效率是比较低下的，在加上IO本身的特征，可以知道，如何提升效率，是MySQL中一个很重要的话题。

​ 磁盘如下所示：

​ 磁盘中的某个盘片：

扇区

​ 数据库文件，本质就是存储在磁盘的盘片中，也就是上图中的一个个小格子中，也就是我们经常所说的扇区。

​ 关于扇区：

​ 如上所示，在半径方向上，距离圆心越近扇区越小，距离圆心越远扇区越大。

​ 虽然扇区有大有小，但是所有扇区的存储大小都是一样的（大多数默认一个扇区的存储大小为512字节，也有更大的），存储大小一样，也就意味着里圆心近的存储密度大，里圆心远的存储密度小。

​ 我们使用的Linux，或者是windows操作系统，看到的大部分目录或者文件，其实都是保存在磁盘中的（除了一些内存文件系统），如下：

讯享网

​ 如上就是存储数据库文件的目录，其本质就是存储在磁盘中的盘片中的。所以，找到一个文件的全部，其本质就是在磁盘中找到所有保存文件的扇区

定位扇区

​ 磁盘的侧面如下图：

​ 柱面（磁道）：多盘磁盘，每盘都是双面的，大小完全相等，同半径的磁道，整体上便构成了一个柱面

​ 每个盘面都有一个磁头：磁头和盘面的对应关系为1对1

​ 所以我们只需要知道磁头（HEAD）、柱面（Cylinder）（磁道）、扇区（Sector）对应的编号，即可在磁盘定位所需要访问的扇区，这种磁盘数据定位方式叫做CHS。不过实际系统软件使用的并不是CHS（硬件是），而是LBA，一种线性地址，LBA最后会转换为CHS，交给次哦按去进行数据读取。

系统读取IO大小

​ 当前我们已经在硬件层面定位了任意一共基本数据块（扇区）了，那么在系统层面，就直接按照扇区（512字节，部分4096字节）进行IO交互吗？不是的

​ 若操作系统直接使用硬件提供的数据大小进行交互，那么系统的IO代码，就直接和硬件强相关，也就意味着硬件发生变化，软件也需要发生变化。

​ 从目前的IO效率来看，单次512字节还是太小了，IO单位小，意味着读取同样的数据，需要进行更多次的磁盘访问，效率更低。

​ 系统中文件系统读取的基本单位不是扇区，而是数据块。

​ 所以系统读取磁盘，是以数据块为单位的，基本单位是4KB。

磁盘随机访问与连续访问

​ 随机访问：本次IO所给出的扇区地址和上次IO给出的扇区地址不连续，这样的话磁头在两次IO操作之间需要做比较大的移动动作才能重新开始读写数据。（效率相对较低）

​ 连续访问：此次IO给出的扇区地址与上次IO结束的扇区地址是连续的，那磁头就能很快的开始这次IO操作，这样的多个IO操作称之为连续访问，

​ 注：尽管相邻的两次IO在同一时刻发出，只要请求的扇区地址相差较大，也只能称为连续访问。磁盘是通过机械运动寻址的，连续访问不需要太多的定位，所以效率较高。

MySQL与磁盘交互基本单位

​ MySQL其实是一种特殊的文件系统，不过它有着更高的IO场景，所以MySQL为了提高基本的IO单位，MySQL进行IO的基本单位是16KB。如下为InnoDB存储引擎IO基本单位

​ 磁盘这个硬件设备的基本单位是512字节，而InnoDB存储引擎使用16KB进行IO交互，所以MySQL与磁盘进行数据交互的基本单位是16KB（这个基本数据单元，在MySQL这里称为page）。

​ MySQL中的数据文件，是以page为单位保存在磁盘当中的。

​ MySQL中的的CURD操作也都是需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。

​ 只要是涉及计算，就需要CPU的参与，而为了便于CPU的参与，需要将数据移动到内存当中。

​ 所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略刷新到磁盘中（刷新的基本单位是page）。

​ 为了方便以上的操作，MySQL服务器在内存中运行的时候，在服务器内部申请了一块大的内存空间（Buffer Pool）来进行数据缓存。（实际上就是为了方便一次性16KB的IO交互）

​ 为了提高效率，一定要减少系统和磁盘的IO次数。

索引的理解

测试举例

​ 如下创建测试表：

讯享网

​ 查询刚刚创建表中的数据，如下：

​ 如上所示，我们向表中插入数据的时候并不是有序插入的，但是当我们搜索出数据的时候，确实按照主键有序输出的（和主键索引相关，之后会介绍）。

​ 我们在IO交互的时候，为什么要采取page的方案进行交互呢？而不是用多少加载多少呢？

​ 如上的五条记录，当我们想要查询id=4的记录的时候，第一次加载id=1的数据，第二次id=2，第三次id=3，只有第四次IO交互的时候才能加载到id=4，我们想要的数据。用多少加载多少意味着我们的IO次数将会增加。

​ 但是当我们使用page进行IO的时候，一次性加载16KB的数据，一下就可以将id=4附近所有的数据都加载进来，当我们还想查询id=9数据的时候，大概率也已经被加载到内存中了（IO的局部性原理）

​ 往往IO效率低下的主要矛盾不是IO单词数据量的大小，而是IO的次数。

单个page

​ MySQL中要管理很多的数据表文件，而要管理好这些文件，就需要对这些文件进行组织起来，将其放入到存储内容的page当中（可以理解为一个个独立文件是由一个或者多个page组成的），如下：

​ 如上所示，在MySQL中，page使用prev和next指针指向前面的page以及后面的page。

​ 因为存在主键，MySQL会默认按照主键给我们的数据进行排序，那么为什么MySQL在数据插入之后要对数据进行排序呢？按照正常的插入数据不可以吗。

​ 这是因为插入数据时进行排序的目的就是优化查询的效率，页内部存放数据的模块，实质上就是一个链表的结构，链表的特点是增删快，查询修改慢，所以优化查询的效率是必须的。

​ 因为有序，所以我们可以尽快的查找到想要的数据

多个page

​ 如上文，我们知道page的功能就是我们在查询某条数据的时候直接将一整页的数据加载到内存中，减少硬盘IO次数，从而提升性能，但是我们也可以看出，现在的页模式内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。

​ 假若我们的数据很多，那么一定会需要多个page来保存这么多的数据，多个page彼此之间使用双链表连接起来，而每个page内部的数据也是基于链表的。如下图所示：
​ 如上所示，多个page之间是使用指针连接起来的，那么查找一条特定的记录，肯定也是通过链表的线性查找，这样的效率还是很低。

页目录

​ 针对如上单页page，我们是否也能引入目录呢，让我们可以快速的查找到对应的数据，如下图：

​ 如上所示，我们在page中也加上目录，当我们想要查询id=4的数据的时候，我们可以直接通过目录2（映射id=3）来找到id=3的数据，下一个数据就是我们想要获取的数据。所以MySQL对插入的数据进行排序的作用还有一点就是方便引入目录。

page目录

​ 随着MySQL数据块中的数据量不断增大，一个page就已经不再满足存储需要了，所以就一定需要多个页来存储数据，如下：

​ 多个page之间使用指针双向指向，我们在page内部可以使用目录快速查找数据，但是我们在多个page之间还是需要通过便来定位数据，这样仿佛也存在一定的效率问题，所以我们可不可以也给page带上目录呢？如下：

​ 使用一个目录项来指向某一页，而这个目录项存放的就是就是将要指向page中存放的最小数据的键值。

​ 页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行。

​ 每个目录项的构成是：指针 + 键值。

​ 多页目录，如下图：

​ 存在一个目录来管理页目录，页目录中的数据存放就是指向那一页中最小的数据，有数据，就可以通过比较，找到该访问的那个page，进而通过指针，找到下一个page。

​ 注：页目录的本质也是页，普通页中存放的数据是用户数据，而页目录中存放的数据是普通页的地址。

​ 顶层的页目录少了，但是还是需要进行遍历查找，不过我们仍然可以继续加目录，如下：

​ 以此类推，我们可以不断的加目录，不过通常这样的三层结构就已经够了，因为一个page存储空间为16KB，所以页目录能存储的page地址是非常多的，也就意味着一个page目录内能指向很多的page，通常这样的结构就是一个矮胖结构。

​ 如上这样的数据结构也被称为B+Tree，总结如下：

​ page分为目录页和数据页，目录页只存放各个下级page的最小值。

​ 查找的时候自顶向下查找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少IO次数。

为什么索引的数据结构是B+TREE

​ InnoDB在建立索引结构来管理数据的时候，其他的数据结构为什么不行呢？如下：

​ 链表：线性遍历

​ 二叉搜索树：存在退化的风险，可能退化为线性结构

​ AVLorRBTREE（平衡搜索树或红黑树）：虽然这样的树形结构平衡或者近似平衡，但毕竟是二叉树结构，相比于能存放更多还在结点的B+Tree来说，意味着数的整体较高，自顶向下查找的过程中，越往下找，意味着与硬盘IO交互更多，所以选择IO次数更少的B+Tree

​ HASH：在官方的索引实现中，MySQL是支持HASH的，不过InnoDB和MyISAM并不支持，HASH依据其算法特征，决定了虽然有时候的是时间复杂度为O(1)，但是在面对范围查找（一次性查找多个数据）的时候就不行了。

B树和B+树

​ B树和B+树如下图，这两种都是一种矮胖型的树，都是便于查询可以减少IO操作的数据结构，为什么我们只选择B+树而不选择B树呢？

​ B树结点中，既有数据，又有page指针，而B+只有叶子结点存在数据，其他的目录页，只有键值和page指针。当目录页中也包含数据内柔的时候，会挤压page指针的空间，能存放page指针的数量更少。所以B+可以使得树更矮，IO操作的次数更少。

​ B+叶子结点之后全部相连，而B没有。叶子结点相连，便于在区间查找的时候，直接找到附近的数据，而不需要重新从上至下查找数据。

聚簇索引/非聚簇索引

​ MyISAM存储引擎-主键索引

​ MyISAM引擎同样是使用B+树作为索引结果，叶节点的data域存放的数据记录的地址，如下图（其中COL1是主键）：

​ 如上图所示，同时也是MyISAM最大的特点，将索引page和数据page分离：叶子结点没有数据，只有对应数据的地址。相比较InnoDB索引，InnoDB是将数据存储到叶子结点。

​ 在MySQL创建数据库目录下MyISAM和InnoDB的文件如下形式：

讯享网

​ 如上所示，MyISAM这种用户数据与索引数据分离的索引方式称之为非聚簇索引。InnoDB这种用户数据与索引数据放在一起的索引方案叫做聚簇索引。

辅助索引

​ 对于MyISAM和InnoDB这两种搜索引擎，除了默认会建立主键索引外，用户也可以建立按照其他列信息建立的索引，这种一般可以叫做辅助（普通）索引。

​ 对于MyISAM建立的辅助索引和主键索引基本没有区别，因为叶子结点存储的是数据的地址，但唯一的区别就是辅助索引可以重复而主键索引不可以。

​ InnoDB建立的辅助索引，叶子结点只会存储记录下对应的key值，获取key值之后再次使用主键到主索引中检索获取记录，这样的过程叫做回表查询。InnoDB设计的这种辅助索引的场景，是因为假若在给普通索引加上所有数据，太浪费空间。

索引操作

​ 以上首先从底层介绍了MySQL中数据插入和查询的原理，然后介绍了MySQL中的数据组织形式，阐述了MySQL进行IO的原理，接下来将会介绍MySQL中关于索引的操作。

主键索引

​ 创建主键索引一共有三种方式，如下：

​ 如上三种所示，其本质都是对某一列的属性上加上主键属性，因为MySQL默认主键就是一种索引。主键索引的特点有：

​ 一个表中最多有一个主键索引，也可以是复合索引。

​ 主键索引的效率高（因为主键不可以重复）。

​ 创建主键索引的列，它的值不能为null，且不能重复。

​ 主键索引的列基本都是int类型

唯一键索引

​ 唯一键索引的创建和主键索引的创建非常类似，给某列加上唯一键属性。如下：

讯享网

​ 如上所示，唯一键索引可以和主键索引并存。唯一键索引的特点如下：

​ 一个表中，可以存在多个唯一键索引。

​ 查询效率高。

​ 若在某一列建立唯一键索引，必须保证这列不能有重复数据

​ 若一个唯一键索引指定not null，等价于主键索引

普通索引

​ 普通索引的创建方式仍然有三种，不过创建方式和唯一键/主键索引不同，如下：

​ 普通索引的特点：

​ 一个表中可以有多个普通索引，普通索引在实际开发中用得比较多。

​ 若某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。（当存在重复值且需要创建索引时，只能选择创建普通索引）

全文索引

​ 当对文章字段或有着大量文字的字段进行检索的时候，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎一定得是MyISAM，而且默认的全文索引支持英文，不支持中文。（若需要对中文进行全文检索，可以使用sphinx的中文版(coreseek)）

​ 注：在平时的开发场景中很少使用全文索引，大多都是使用上面三种索引。

​ 如下：

讯享网

​ 如上所示，创建出以title和body的复合索引。查看我们在全文中查询某关键字是否有使用索引，如下：

​ 如上所示，当我们在全文查询中想要检测某关键字时，若想使用索引帮助检索，需要使用特定的语法，如下：

讯享网

查询索引

​ 查询一个表的索引信息有如下三种方式：

​ 第一种，语法：

​ 如下，查看articles表格的索引信息：

讯享网

​ 如上所示，当某个索引为复合索引的时候，该索引名用第一个表示，查询索引的时候复合索引的信息会分别显示。

​ 查询表索引信息的第二种方式：

​ 如下：

讯享网

​ 第三种方式：

讯享网

删除索引

​ 删除索引也有三种方式，如下：

​ 第一种，删除主键索引：

​ 第二种，其他所有的删除：

讯享网

​ 索引名就是我们在查询表索引属性的key_name字段，如下所示，删除user10表中的普通索引：

​ 第三种：

讯享网

​ 如下所示：

索引创建原则

​ 索引创建有利于我们查询数据，却是以插入，更新，删除的效率为代价的，所以索引的创建应该有一定的原则，让其“物尽其用”，如下：

​ 比较频繁作为查询条件的字段应该创建索引

​ 唯一性太差的字段不适合单独创建索引，即使频繁作为查询对象

​ 更新非常频繁的字段不适合创建索引

​ 不会出现在where子句中的字段不该创建索引