| |
|
开发:
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索引模型 ???索引分类 ????(2).非聚簇索引 ????(3).索引原理 ???索引维护 三、总结 ? 引言 通过本篇文章,我们可以收获: 1、熟悉Mysql索引的基础知识:
2、如何提高开发、DBA和QA 在项目过程中关于 Mysql 索引相关操作的技术分析能力。 一、背景 分享这篇文章的目的:提升开发、DBA、QA在项目过程中关于提测 sql 和 sql 变更中关于添加、修改、删除索引合理性的分析能力; 二、Mysql索引 1.概念说明 类比于数据库的表而言,索引其实就是它的“目录”。 这时当你要查 id1 对应的名字是什么,处理步骤是: 但缺点也很明显,因为不是有序的,所以哈希索引做区间查询的速度是很慢的。因为需要进行全表扫描一遍。 有序数组在等值查询和范围查询场景中的性能是十分优秀的。 还是上面的根据用户 id 来查找用户 name 的例子,如果使用有序数组来实现的话,对应的示意图如下: 假设这里的 id 没有重复,数组就是按照 id 递增的顺序进行保存的,这时如果你要查 id2 对应的名字,用二分法就可以快速得到,这个时间复杂度是O(log(N))。这种索引结构能很好的支持范围查询 。 ? 如果还是用上面使用 id 来查询 name 的例子,来看下使用二叉搜索树的数据结构来实现,对应的示意图如下: 二叉搜索树的特点:
二叉树是搜索效率最高的,但是实际上大多数的数据库存储却并不使用二叉树。原因是索引不仅存在内存中,也要写到磁盘上。 在 Mysql 中,索引是在存储引擎层实现的,所以并没有统一的索引标准,即使用不同的存储引擎,其对应索引的工作方式并不一样。 InnoDB存储引擎在Mysql数据库中使用最为普遍,下面来看下InnoDB的索引模型。 在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表,且数据都是存储在B+树中的。 (1). 减少磁盘IO次数 B+树的数据结构模型将所有数据都放到叶子节点,且叶子节点形成一个列表(可以做范围查询),非叶子节点只放键值,这样每个数据叶中可存放的有效数据就多了,可以有效减少磁盘IO次数。 (2).每次查询的时间复杂度是固定的 在B+树中,由于分支节点只是叶子节点的索引,所以对于任意关键字的查找都必须从根节点走到分支节点,所有关键字查询路径长度相同,每次查询的时间复杂度是固定的。但是在B树中,其分支节点上也保存有数据,对于每一个数据的查询所走的路径长度是不一样的,所以查询效率也不一样。 (3).遍历效率更高 由于B+树的数据都存储在叶子节点上,分支节点均为索引,方便扫库,只需扫一遍叶子即可。但是B树在分支节点上都保存着数据,要找到具体的顺序数据,需要执行一次中序遍历来查找。所以B+树更加适合范围查询的情况,在解决磁盘IO性能的同时解决了B树元素遍历效率低下的问题。 索引分类 (1).聚簇索引 主键索引 在Innodb中,Mysql中的数据是按照主键的顺序来存放的。那么聚簇索引就是按照每张表的主键来构造一颗B+树,叶子节点存放的就是整张表的行数据。由于表里的数据只能按照一颗B+树排序,因此一张表只能有一个聚簇索引。 在Innodb中,聚簇索引默认就是主键索引。 假如表没有设定主键,则按照下列规则来创建聚簇索引
?
? (2).非聚簇索引 联合索引 使用多个列字段建立的索引,称为联合索引,也叫组合索引。 其建表语句如下:
? 所谓最左匹配原则指的就是如果 SQL 语句中用到了联合索引中的最左边的索引,那么这条 SQL 语句就可以利用这个联合索引去进行匹配,值得注意的是,当遇到范围查询(>、<、between、like)就会停止匹配。 where条件为: where条件为: where条件为: 前缀索引 当索引列的字符比较多时,索引很大且速度很慢,此时可以优化索引列,只索引列开始的部分字符串,以此节约索引空间,提高索引效率。 前缀索引的使用原则是:降低重复的索引值
如果使用前1-7位字符来做前缀索引就会出现大量索引值重复的情况。 此时索引值重复性高,查询效率低下,不符合前缀索引的原则,因此可以依据具体需求来决定使用前8-10位字符来做前缀索引。
? 普通索引
?
这个查询sql会通过 t 这个普通索引在自身的 B+ 树上找到对应主键:1,然后再使用1在主键索引所在的B+树上查询出真实表的行数据后返回结果,这个操作被称为回表。
与普通索引类似,不同点在于唯一索引的索引列的值必须唯一,但允许有空值,这点与主键不同(主键索引列的值唯一,但不能为空)。 如果是多个字段组成的联合索引,则列值的组合必须唯一,创建方法与普通索引类似。
5.6版本之后InnoDB存储引擎开始支持全文索引,Mysql允许在char、varchar、text类型上建立全文索引。 1.自然语言模式:通过match against 传递某个特定的字符串进行检索 3.查询扩展模式:当查询的关键字太短,用户需要隐含知识时进行。 例如,对于单词operating system的查询,用户可能希望查询的结果除了包含operating system的文档,还应该包含linux,windows,unix的单词。 (3).索引原理 聚簇索引 以下面一张学生表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的数据往后挪动。
基于上述对索引维护过程的说明,下面来讨论一个具体案例:
如果使用业务逻辑的字段做主键,则相比自增主键id,不太容易保证有序插入,这样写数据成本相对较高。 从存储空间角度看: 假设user表中有一个字符串类型的身份证号字段,且是唯一不重复的,此时是用身份证号做主键,还是使用自增字段做主键比较好呢?
其中每页的大小是有规定的,页是InnoDB管理存储空间的基本单位:1页=16kb,原则是尽量在一个页内存放多个索引。 覆盖索引 还是以上述例子来讲解,现将下列查询语句:
修改为:
这时只需要查 s_id 的值,而 s_id 的值已经在普通索引 name上了,因此可以从非聚簇索引B+树上直接返回查询结果,不需要回表操作。 也就是说,在这个查询里面,索引name已经覆盖了我们的查询需求,因此称为覆盖索引。 ? 应用场景
三、总结 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自动化、性能测试、代码检测、编程技术等。 微信搜索公众号:“无量测试之道”,或扫描下方二维码: ? 添加关注,让我们一起共同成长! |
|
|
上一篇文章 下一篇文章 查看所有文章 |
|
开发:
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- |
|
网站联系: qq:121756557 email:121756557@qq.com IT数码 |