IT数码 购物 网址 头条 软件 日历 阅读 图书馆
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
图片批量下载器
↓批量下载图片,美女图库↓
图片自动播放器
↓图片自动播放器↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁
 
   -> 大数据 -> Mysql 索引知识详解 -> 正文阅读

[大数据]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已经覆盖了我们的查询需求,因此称为覆盖索引。

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

应用场景

  1. 当只有一个索引,且该索引一定是唯一索引。这种场景适合用业务字段直接做主键。业务使用时尽量使用主键查询,避免回表。

  2. 当表是经常需要更新的不适合做索引,频繁更新会导致索引也会频繁更新,降低写的效率。

  3. 使用索引进行模糊查询时,切记 like 后的关键字的前面不能使用%(例如:name like "%三"),只能在关键字的后面加上%,因为索引是从左至右匹配的,如果在前面加上%就无法找到索引。

  4. 数据表过大时,当索引字段的字符长度过长则不适合作为索引。因为查询大量数据时,索引即使有效,但是速度依然慢。

  5. 表数据量大且字段值有较多相同值的时候适合选择使用普通索引。

  6. 当字段多且字段值没有重复的时候用唯一索引。

  7. 当where条件后查询字段较多,适合建立联合索引。

  8. 不会出现在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自动化、性能测试、代码检测、编程技术等。

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

在这里插入图片描述?

添加关注,让我们一起共同成长!

  大数据 最新文章
实现Kafka至少消费一次
亚马逊云科技:还在苦于ETL?Zero ETL的时代
初探MapReduce
【SpringBoot框架篇】32.基于注解+redis实现
Elasticsearch:如何减少 Elasticsearch 集
Go redis操作
Redis面试题
专题五 Redis高并发场景
基于GBase8s和Calcite的多数据源查询
Redis——底层数据结构原理
上一篇文章      下一篇文章      查看所有文章
加:2022-01-12 00:04:27  更:2022-01-12 00:04:32 
 
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁

360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年11日历 -2024/11/24 14:20:38-

图片自动播放器
↓图片自动播放器↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT数码