一篇带给你MySQL索引知识详解

发布时间：2025-11-04 23:36:56 来源：技术快报 作者：系统运维

引言

通过本篇文章，篇带我们可以收获：

1、索引熟悉MySQL索引的知识基础知识：

2、如何提高开发、详解DBA和QA 在项目过程中关于 Mysql 索引相关操作的篇带技术分析能力。

一、索引背景

分享这篇文章的知识目的：提升开发、DBA、详解QA在项目过程中关于提测 sql 和 sql 变更中关于添加、篇带修改、索引删除索引合理性的知识分析能力。

二、详解MySQL索引

1、篇带概念说明

简单来说，索引索引的知识出现其实就是为了提高数据查询的效率，在表数据量较大时，索引的重要性尤为突出，可以理解为索引就像书的目录一样。

例如：一本1000页的书，如果你想快速找到其中某个知识点，如果不按照目录来查找，源码下载直接一页页翻开查找，无疑效率是十分低下的。

类比于数据库的表而言，索引其实就是它的“目录”。

2、常见索引模型

哈希表是一种以键-值(Key-Value)格式存储数据的结构，通过输入待查找的 Key值，就可以找到该 Key 对应的 Value。

哈希的思想比较简单，将值放在数组里，再使用哈希函数将输入的 Key 值换算成一个确定位置的值，最后把 Value 放在数组的这个确定的位置。

因为多个输入的 Key 值在使用哈希函数进行换算时，会出现多个 Key 换算出来是同一个值的情况，如下图中的 id1 和 idn 换算的云服务器提供商结果都为：x，这种情况下哈希表给出的处理方案是拉出一个链表。

例如，现有一张用户表信息，需要根据用户 id 来查找用户 name，对应的哈希索引示意图如下：

这时当你要查 id1 对应的名字是什么，处理步骤是：

首先，将 id1 通过哈希函数算出 x。

然后，按照顺序遍历，找到 User1，即可查询到对应的 name 名称。

图中的 id 的值并不是有序递增的，这样做的好处是增加新的 User 时速度比较快，只需要往后追加。

但缺点也很明显，因为不是有序的，香港云服务器所以哈希索引做区间查询的速度是很慢的。因为需要进行全表扫描一遍。

哈希表这种结构适用于只有等值查询的场景，比如一些NoSQL(非关系型数据库)引擎。

有序数组在等值查询和范围查询场景中的性能是十分优秀的。

还是上面的根据用户 id 来查找用户 name 的例子，如果使用有序数组来实现的话，对应的示意图如下：

假设这里的 id 没有重复，数组就是按照 id 递增的顺序进行保存的，这时如果你要查 id2 对应的名字，用二分法就可以快速得到，这个时间复杂度是O(log(N))。这种索引结构能很好的支持范围查询 。

例如你想要查询 [idm, idn] 区间的 User 的 name 信息，可以先用二分法找到 idm，如果不存在 idm，就去寻找大于 idm 的第一个 User，然后依次向右遍历，直至查询到第一个大于 idn 的 id 号，退出循环。

单从查询效率来看，有序数组就是最好的数据结构了。思考一个问题，当这种数据结构在遇到更新数据(插入或删除)时，会怎样?

比如你删除或插入一条记录，就会非常麻烦，因为插入数据需要将后半部分的数据往后挪动一个位置，删除数据需要将后半部分的数据往前挪动一个位置，成本太高了。

有序数组索引只适用于静态存储引擎，适合存储不会再修改的数据。

如果还是用上面使用 id 来查询 name 的例子，来看下使用二叉搜索树的数据结构来实现，对应的示意图如下：

父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。

如果你要查id2的信息话，按照图中的搜索顺序就是按照UserA—>UserC—>UserF—>User2这个路径得到，这个时间复杂度是O(log(N))。

树有二叉，也可以有多叉，多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右是递增的。

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。原因是索引不仅存在内存中，也要写到磁盘上。

在 Mysql 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即使用不同的存储引擎，其对应索引的工作方式并不一样。

InnoDB存储引擎在Mysql数据库中使用最为普遍，下面来看下InnoDB的索引模型。

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表，且数据都是存储在B+树中的。

为什么使用的是B+树，而不是其他的数据索引模型呢?

B+树的数据结构模型将所有数据都放到叶子节点，且叶子节点形成一个列表(可以做范围查询)，非叶子节点只放键值，这样每个数据叶中可存放的有效数据就多了，可以有效减少磁盘IO次数。

在B+树中，由于分支节点只是叶子节点的索引，所以对于任意关键字的查找都必须从根节点走到分支节点，所有关键字查询路径长度相同，每次查询的时间复杂度是固定的。但是在B树中，其分支节点上也保存有数据，对于每一个数据的查询所走的路径长度是不一样的，所以查询效率也不一样。

由于B+树的数据都存储在叶子节点上，分支节点均为索引，方便扫库，只需扫一遍叶子即可。但是B树在分支节点上都保存着数据，要找到具体的顺序数据，需要执行一次中序遍历来查找。所以B+树更加适合范围查询的情况，在解决磁盘IO性能的同时解决了B树元素遍历效率低下的问题。

索引分类

聚簇索引

在Innodb中，Mysql中的数据是按照主键的顺序来存放的。那么聚簇索引就是按照每张表的主键来构造一颗B+树，叶子节点存放的就是整张表的行数据。由于表里的数据只能按照一颗B+树排序，因此一张表只能有一个聚簇索引。

在Innodb中，聚簇索引默认就是主键索引。

假如表没有设定主键，则按照下列规则来创建聚簇索引。

例如现有一个主键列为id的user表，表中有字段 t 和 name，并且在 t 上有索引。

建表语句如下：

非聚簇索引

使用多个列字段建立的索引，称为联合索引，也叫组合索引。

联合索引为：(t,name)。

其建表语句如下：

说到联合索引，一定要谈谈最左匹配原则。

所谓最左匹配原则指的就是如果 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配，值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。

设定表T已建立联合索引(id, name)。

where条件为：

满足联合索引的最左匹配原则，是可以匹配索引来执行sql的。

where条件为：

也满足联合索引的最左匹配原则，因为Mysql优化器会自动调整id，name的顺序与索引顺序一致，这样就能用到联合索引了。

where条件为：

不满足联合索引的最左匹配原则，也就无法使用(id, name)的联合索引了。

设定表T已建立联合索引(a, b, c, d)。

where条件为：

这个where条件，只有a, b, c能使用到联合索引，d无法使用索引，因为c>100属于范围查询，将后面d的索引匹配给中断了。

当索引列的字符比较多时，索引很大且速度很慢，此时可以优化索引列，只索引列开始的部分字符串，以此节约索引空间，提高索引效率。

前缀索引的使用原则是：降低重复的索引值。

例如有以下一张学生表，st_num为学号：

从上表可以发现 st_num 字段前7位都是重复的，都是以0102021开头的。

如果使用前1-7位字符来做前缀索引就会出现大量索引值重复的情况。

此时索引值重复性高，查询效率低下，不符合前缀索引的原则，因此可以依据具体需求来决定使用前8-10位字符来做前缀索引。

前缀索引创建方式如下：

如下user建表语句中的 t 就是一个普通索引，普通索引与主键索引的区别在于，普通索引的叶子节点存放的不是行数据，而是主键值。(在索引原理中会详细说明)。

例如现有一个主键列为id的user表，表中有字段 t 和 name，并且在 t 上有索引。

建表语句如下：

例如：

这个查询sql会通过 t 这个普通索引在自身的 B+ 树上找到对应主键：1，然后再使用1在主键索引所在的B+树上查询出真实表的行数据后返回结果，这个操作被称为回表。

与普通索引类似，不同点在于唯一索引的索引列的值必须唯一，但允许有空值，这点与主键不同(主键索引列的值唯一，但不能为空)。

如果是多个字段组成的联合索引，则列值的组合必须唯一，创建方法与普通索引类似。

5.6版本之后InnoDB存储引擎开始支持全文索引，Mysql允许在char、varchar、text类型上建立全文索引。

Mysql支持三种模式的全文检索模式：

布尔操作符可以通过以下sql语句查看：

例如，对于单词operating system的查询，用户可能希望查询的结果除了包含operating system的文档，还应该包含linux，windows，unix的单词。

这种查询会分2次执行检索，第1次是使用给定的operating system的短语进行检索，第2次结合第一次相关性比较高的进行检索。

索引原理

以下面一张学生表student为例，其中s_id为主键。

对应的聚簇索引结构图如下：

从图中可以看下结构图共分为上下部分，上部分是：由主键s_id形成聚簇索引(B+树)，下部分是：student表存储在磁盘上的真实数据。

当我们使用主键s_id作为查询条件时，来看下以下sql的执行过程。

如上图所示，从根开始，经过3次查找，就可以找到s_id=25对应的真实数据。如果不使用索引，那就要在磁盘上，进行逐行扫描，直到找到数据位置。

显然，使用索引速度会快。但是在写入数据的时候，需要维护这颗B+树的结构，因此写入性能会下降!

聚簇索引(主键索引)的叶子节点存储的是整行数据。

还是以上述的学生表 student 为例，给该表添加普通索引 name 后，结构图中新增一棵 name 字段的非聚簇索引的 B+ 树。

如下图所示：

因此， 我们每加一个索引，就会增加表的体积，占用磁盘存储空间。

请注意看name字段的非聚簇索引B+树上的叶子节点，非聚簇索引的叶子节点并不是真实数据，它的叶子节点依然是索引节点，存放的是该索引字段的值以及对应的主键(s_id)索引(聚簇索引)。

此时执行下列查询语句：

通过上图红线可以看出，查询路径是先从非聚簇索引树开始查找，然后找到聚簇索引后根据聚簇索引，在s_id的聚簇索引的B+树上，找到完整的数据!这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

因为B+树为了维护索引有序性，在插入新值或删除数据的时候需要做必要的维护。

以上图为示例，如果需要插入新的s_id值为50，则需要在s_id=44的记录后面插入一行新记录。但如果插入的s_id的值为：28，则需要将s_id=31的数据往后挪动。

假如s_id=44所在的数据页满了，根据B+树的算法，此时需要申请一个新的数据页，然后将部分数据挪动到新的数据页上，这个过程称为页分裂。这种情况下，性能自然会受到影响。

页分裂带来的不仅是性能的影响，还会影响数据页的利用率。原本放在一个页的数据，现在分到2个数据页上，整体空间利用率大幅下降。

当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。

基于上述对索引维护过程的说明，下面来讨论一个具体案例：

我们知道自增主键是指自增列上定义的主键，在建表语句中一般是使用关键字：NOT NULL PRIMARY KEY AUTO_INCREMENT来定义的。

这样在插入新的记录时，是不需要指定自增主键列 id 值的，因为系统会获取当前 id 最大值后+1作为下一条记录的自增主键列 id 的值。

这种插入数据的模式都是追加操作，不涉及到挪动其他记录的操作，也就不会触发叶子节点的分裂了。

如果使用业务逻辑的字段做主键，则相比自增主键id，不太容易保证有序插入，这样写数据成本相对较高。

假设user表中有一个字符串类型的身份证号字段，且是唯一不重复的，此时是用身份证号做主键，还是使用自增字段做主键比较好呢?

前面讲索引原理中讲到非聚簇索引的叶子节点上都是主键的值，如果使用身份证号做主键，那么每个非主键索引的叶子节点占用约20个字节，而如果使用整型做主键，则只需要4个字节，如果是长整型(bigint)则是8个字节。

由此可知，主键长度越小，普通索引的叶子节点就越小，普通索引整体占用的空间也就越小。

因此从性能和存储空间两方面来考虑，使用自增主键作为索引是更优的选择。

单个索引的值字符长度不能过大，因为B+树索引并不能直接找到行，只是找到行所在的页，通过从磁盘把整页读入内存，再在内存中查找。

其中每页的大小是有规定的，页是InnoDB管理存储空间的基本单位：1页=16kb，原则是尽量在一个页内存放多个索引。

还是以上述例子来讲解，现将下列查询语句：

修改为：

这时只需要查 s_id 的值，而 s_id 的值已经在普通索引 name上了，因此可以从非聚簇索引B+树上直接返回查询结果，不需要回表操作。

也就是说，在这个查询里面，索引name已经覆盖了我们的查询需求，因此称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

应用场景

三、总结

相关文章