Mysql 索引知识详解

本文目录结构：

引言

一、背景

二、Mysql索引

 1.概念说明

 2.常见索引模型

   哈希表
   有序数组
   二叉搜索树

 3.InnoDB索引模型

   索引分类
    (1).聚簇索引
     主键索引

    (2).非聚簇索引
     联合索引
     前缀索引
     普通索引
     唯一索引
     全文索引

    (3).索引原理
     聚簇索引
     非聚簇索引

   索引维护
   覆盖索引
   应用场景

三、总结

 

---

 

引言

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

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

索引是什么

常见索引模型

InnoDB索引模型

索引种类有哪些

索引的应用场景

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))。
 
树有二叉，也可以有多叉，多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右是递增的。

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

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

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

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表，且数据都是存储在B+树中的。
 
为什么使用的是B+树，而不是其他的数据索引模型呢？

(1). 减少磁盘IO次数

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

(2).每次查询的时间复杂度是固定的

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

(3).遍历效率更高

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

索引分类

(1).聚簇索引

主键索引

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

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

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

没有主键时，会用一个唯一且不为空的索引列做为主键，成为此表的聚簇索引。

如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

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

create table user(
id int primary key,
t int not null,
name varchar(16),
index (t))engine=InnoDB;

 

(2).非聚簇索引

联合索引

使用多个列字段建立的索引，称为联合索引，也叫组合索引。
联合索引为：(t,name)

其建表语句如下：

create table user(
id int primary key,
t int not null,
name varchar(16),
index(t),
index(t,name) )engine=innodb;

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

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

where条件为：
id = 1 或者
id = 1 and name = 'tom'
满足联合索引的最左匹配原则，是可以匹配索引来执行sql的
 
where条件为：
name = 'tom' and id = 1
也满足联合索引的最左匹配原则，因为Mysql优化器会自动调整id，name的顺序与索引顺序一致，这样就能用到联合索引了。

where条件为：
name = 'tom'
不满足联合索引的最左匹配原则，也就无法使用(id, name)的联合索引了。
 
[2].设定表T已建立联合索引(a, b, c, d)

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

前缀索引

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

前缀索引的使用原则是：降低重复的索引值
 
例如有以下一张学生表，st_num为学号

 

 

 

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

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

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

前缀索引创建方式如下：

create table `student` (
`st_num` varchar(255) not null,
`sex` int(10) not null,
`name` varchar(255) not null,
index (st_num(8))
)engine=InnoDB;

 

普通索引
如下user建表语句中的 t 就是一个普通索引，普通索引与主键索引的区别在于，普通索引的叶子节点存放的不是行数据，而是主键值。（在索引原理中会详细说明）
 
例如现有一个主键列为id的user表，表中有字段 t 和 name，并且在 t 上有索引。
建表语句如下：

create table user(
id int primary key,
t int not null,
name varchar(16),
index (t))engine=InnoDB;

例如：

select * from user where t=100;

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

 
唯一索引

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

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

全文索引

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

1.自然语言模式：通过match against 传递某个特定的字符串进行检索
2.布尔模式：可以为检查的字符串增加操作符
布尔操作符可以通过以下sql语句查看：

 

 

 

3.查询扩展模式：当查询的关键字太短，用户需要隐含知识时进行。

例如，对于单词operating system的查询，用户可能希望查询的结果除了包含operating system的文档，还应该包含linux，windows，unix的单词。
 
这种查询会分2次执行检索，第1次是使用给定的operating system的短语进行检索，第2次结合第一次相关性比较高的进行检索。
 
 

(3).索引原理

聚簇索引

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

 

 

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

 

 

 

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

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

select * from student where s_id='25';

 

 

 

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

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

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

非聚簇索引

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

如下图所示：

 

 

 

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

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

此时执行下列查询语句：

select * from student where name='Candy';

 

 

 

通过上图红线可以看出，查询路径是先从非聚簇索引树开始查找，然后找到聚簇索引后根据聚簇索引，在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，原则是尽量在一个页内存放多个索引。
 
 

覆盖索引

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

select * from student where name='Candy';

修改为：

select s_id from student where name='Candy';

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

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

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

应用场景

• 当只有一个索引，且该索引一定是唯一索引。这种场景适合用业务字段直接做主键。业务使用时尽量使用主键查询，避免回表。
• 当表是经常需要更新的不适合做索引，频繁更新会导致索引也会频繁更新，降低写的效率。
• 使用索引进行模糊查询时，切记 like 后的关键字的前面不能使用%（例如：name like "%三"），只能在关键字的后面加上%，因为索引是从左至右匹配的，如果在前面加上%就无法找到索引。
• 数据表过大时，当索引字段的字符长度过长则不适合作为索引。因为查询大量数据时，索引即使有效，但是速度依然慢。
• 表数据量大且字段值有较多相同值的时候适合选择使用普通索引。
• 当字段多且字段值没有重复的时候用唯一索引。
• 当where条件后查询字段较多，适合建立联合索引。
• 不会出现在where条件后的查询字段，不要建立索引。

 

三、总结

1. 项目代码在 code diff 时会发现常用的业务查询 sql，根据 where 条件来判断频繁查询字段，再根据本文分享的索引知识，来判断在sql审核中关于索引相关的操作是否合理且有效。

2. 测试过程中通过设置 slow sql 查询参数，找出对应的 sql 查询语句，分析 slow sql 产生的原因，并给出自己的解决方案，如添加必要的字段索引。

 

欢迎关注【无量测试之道】公众号，回复【领取资源】

Python+Unittest框架API自动化、

Python+Unittest框架API自动化、

Python+Pytest框架API自动化、

Python+Pandas+Pyecharts大数据分析、

Python+Selenium框架Web的UI自动化、

Python+Appium框架APP的UI自动化、

Python编程学习资源干货、

资源和代码 免费送啦~
文章下方有公众号二维码，可直接微信扫一扫关注即可。

备注：我的个人公众号已正式开通，致力于IT互联网技术的分享。

包含：数据分析、大数据、机器学习、测试开发、API接口自动化、测试运维、UI自动化、性能测试、代码检测、编程技术等。

微信搜索公众号：“无量测试之道”，或扫描下方二维码：

 

添加关注，让我们一起共同成长！