MySQL系列之索引

🌹作者主页:青花锁 🌹简介:Java领域优质创作者🏆、Java微服务架构公号作者😄

🌹简历模板、学习资料、面试题库、技术互助

🌹文末获取联系方式 📝

在这里插入图片描述


往期热门专栏回顾

专栏描述
Java项目实战介绍Java组件安装、使用;手写框架等
Aws服务器实战Aws Linux服务器上操作nginx、git、JDK、Vue
Java微服务实战Java 微服务实战,Spring Cloud Netflix套件、Spring Cloud Alibaba套件、Seata、gateway、shadingjdbc等实战操作
Java基础篇Java基础闲聊,已出HashMap、String、StringBuffer等源码分析,JVM分析,持续更新中
Springboot篇从创建Springboot项目,到加载数据库、静态资源、输出RestFul接口、跨越问题解决到统一返回、全局异常处理、Swagger文档
Spring MVC篇从创建Spring MVC项目,到加载数据库、静态资源、输出RestFul接口、跨越问题解决到统一返回
华为云服务器实战华为云Linux服务器上操作nginx、git、JDK、Vue等,以及使用宝塔运维操作添加Html网页、部署Springboot项目/Vue项目等
Java爬虫通过Java+Selenium+GoogleWebDriver 模拟真人网页操作爬取花瓣网图片、bing搜索图片等
Vue实战讲解Vue3的安装、环境配置,基本语法、循环语句、生命周期、路由设置、组件、axios交互、Element-ui的使用等
Spring讲解Spring(Bean)概念、IOC、AOP、集成jdbcTemplate/redis/事务等

MySQL专栏回顾

专栏导航描述
MySQL- -MySQL DDL通用语法
MySQL- -MySQL DML通用语法
MySQL- -MySQL 约束篇
MySQL- -MySQL 多表查询
MySQL- -MySQL 事务
MySQL- -MySQL 存储引擎
MySQL- -MySQL 性能分析
MySQL- -MySQL 索引

前言

此为MySQL专栏文章之一,讲解MySQL 索引。

索引是帮助 MySQL 高效获取数据 的 数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查询算法,这种数据结构就是索引。

优点:
● 提高数据检索效率,降低数据库的IO成本
● 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗
缺点:
● 索引列也是要占用空间的
● 索引大大提高了查询效率,但降低了更新的速度,比如 INSERT、UPDATE、DELETE


1、索引结构

在这里插入图片描述
在这里插入图片描述

1.1、B-Tree

在这里插入图片描述

[二叉树]

二叉树的缺点可以用红黑树来解决:
在这里插入图片描述

[红黑树]

红黑树也存在大数据量情况下,层级较深,检索速度慢的问题。

为了解决上述问题,可以使用 B-Tree 结构。
B-Tree (多路平衡查找树) 以一棵最大度数(max-degree,指一个节点的子节点个数)为5(5阶)的 b-tree 为例(每个节点最多存储4个key,5个指针)
在这里插入图片描述

[B-Tree结构]

B-Tree 的数据插入过程动画参照:https://www.bilibili.com/video/BV1Kr4y1i7ru?p=68
演示地址:https://www.cs.usfca.edu/~galles/visualization/BTree.html

1.2、B+Tree

结构图:
在这里插入图片描述

[B+Tree结构]

演示地址:https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

与 B-Tree 的区别:
● 所有的数据都会出现在叶子节点
● 叶子节点形成一个双向链表(???双向还是单向)

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的 B+Tree,提高区间访问的性能。

在这里插入图片描述

[MySQL B+Tree 结构图]

1.3、Hash

哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。
如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。
在这里插入图片描述

[Hash索引原理图]

特点:
● Hash索引只能用于对等比较(=、in),不支持范围查询(betwwn、>、<、…)
● 无法利用索引完成排序操作
● 查询效率高,通常只需要一次检索就可以了,效率通常要高于 B+Tree 索引

存储引擎支持:
● Memory
● InnoDB: 具有自适应hash功能,hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的

1.4、面试题

  1. 为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构?
    ● 相对于二叉树,层级更少,搜索效率高
    ● 对于 B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针也跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低
    ● 相对于 Hash 索引,B+Tree 支持范围匹配及排序操作

2、索引分类

在这里插入图片描述
在 InnoDB 存储引擎中,根据索引的存储形式,又可以分为以下两种:
在这里插入图片描述
演示图:
在这里插入图片描述

[大致原理]

在这里插入图片描述

[演示图]

聚集索引选取规则:

● 如果存在主键,主键索引就是聚集索引
● 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引
● 如果表没有主键或没有合适的唯一索引,则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

思考题

  1. 以下 SQL 语句,哪个执行效率高?为什么?
    select * from user where id = 10;
    select * from user where name = ‘Arm’;
    – 备注:id为主键,name字段创建的有索引
    答:第一条语句,因为第二条需要回表查询,相当于两个步骤。

  2. InnoDB 主键索引的 B+Tree 高度为多少?
    答:假设一行数据大小为1k,一页中可以存储16行这样的数据。InnoDB 的指针占用6个字节的空间,主键假设为bigint,占用字节数为8.
    可得公式:n * 8 + (n + 1) * 6 = 16 * 1024,其中 8 表示 bigint 占用的字节数,n 表示当前节点存储的key的数量,(n + 1) 表示指针数量(比key多一个)。算出n约为1170。

    如果树的高度为2,那么他能存储的数据量大概为:1171 * 16 = 18736;
    如果树的高度为3,那么他能存储的数据量大概为:1171 * 1171 * 16 = 21939856。

    另外,如果有成千上万的数据,那么就要考虑分表,涉及运维篇知识。

3、索引语法

创建索引:

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);
如果不加 CREATE 后面不加索引类型参数,则创建的是常规索引

查看索引:

SHOW INDEX FROM table_name;

删除索引:

DROP INDEX index_name ON table_name;

案例:

-- name字段为姓名字段,该字段的值可能会重复,为该字段创建索引
create index idx_user_name on tb_user(name);
-- phone手机号字段的值非空,且唯一,为该字段创建唯一索引
create unique index idx_user_phone on tb_user (phone);
-- 为profession, age, status创建联合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);
-- 为email建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);-- 删除索引
drop index idx_user_email on tb_user;

4、使用规则

4.1、最左前缀法则

如果索引关联了多列(联合索引),要遵守最左前缀法则,最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。
如果跳跃某一列,索引将部分失效(后面的字段索引失效)。

联合索引中,出现范围查询(<, >),范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。

4.2、索引失效情况

  1. 在索引列上进行运算操作,索引将失效。如:explain select * from tb_user where substring(phone, 10, 2) = ‘15’;
  2. 类型不一致引起的隐式类型转换,;例如 字符串类型字段使用时,不加引号,索引将失效。如:explain select * from tb_user where phone = 17799990015;,此处phone的值没有加引号
  3. 模糊查询中,如果仅仅是尾部模糊匹配,索引是不会失效的;如果是头部模糊匹配,索引失效。如:explain select * from tb_user where profession like ‘%工程’;,前后都有 % 也会失效。
  4. 用 or 分割开的条件,如果 or 其中一个条件的列没有索引,那么涉及的索引都不会被用到。
  5. 如果 MySQL 评估使用索引比全表更慢,则不使用索引。

4.3、SQL 提示

是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

例如
使用索引:
explain select * from tb_user use index(idx_user_pro) where profession="软件工程";
不使用哪个索引:
explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";
必须使用哪个索引:
explain select * from tb_user force index(idx_user_pro) where profession="软件工程";use 是建议,实际使用哪个索引 MySQL 还会自己权衡运行速度去更改,force就是无论如何都强制使用该索引。

4.4、覆盖索引&回表查询

尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能找到),减少 select *。

explain 中 extra 字段含义:
using index condition:查找使用了索引,但是需要回表查询数据
using where; using index;:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询

如果在聚集索引中直接能找到对应的行,则直接返回行数据,只需要一次查询,哪怕是select *;
如果在辅助索引中找聚集索引,如select id, name from xxx where name=‘xxx’;,也只需要通过辅助索引(name)查找到对应的id,返回name和name索引对应的id即可,只需要一次查询;
如果是通过辅助索引查找其他字段,则需要回表查询,如select id, name, gender from xxx where name=‘xxx’;

所以尽量不要用select *,容易出现回表查询,降低效率,除非有联合索引包含了所有字段

面试题:
一张表,有四个字段(id, username, password, status),由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优方案:
select id, username, password from tb_user where username=‘wahaha’;

解:给 username 和 password 字段建立联合索引,则不需要回表查询,直接覆盖索引。

4.5、前缀索引

当字段类型为字符串(varchar, text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率,此时可以只降字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。

语法:create index idx_xxxx on table_name(columnn(n));

前缀长度:可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。

求选择性公式:

select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email, 1, 5)) / count(*) from tb_user;

show index 里面的 sub_part 可以看到截取的长度。

4.6、单列索引&联合索引

单列索引:即一个索引只包含单个列
联合索引:即一个索引包含了多个列
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。

单列索引情况:
explain select id, phone, name from tb_user where phone = ‘17799990010’ and name = ‘韩信’;
这句只会用到phone索引字段

注意事项

● 多条件联合查询时,MySQL优化器会评估哪个字段的索引效率更高,会选择该索引完成本次查询

5、设计原则

  1. 针对于数据量较大,且查询比较频繁的表建立索引
  2. 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引
  3. 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
  4. 如果是字符串类型的字段,字段长度较长,可以针对于字段的特点,建立前缀索引
  5. 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率
  6. 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价就越大,会影响增删改的效率
  7. 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询


资料获取,更多粉丝福利,关注下方公众号获取

在这里插入图片描述

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/324823.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

【Linux】环境变量是什么?如何配置?详解

&#x1f490; &#x1f338; &#x1f337; &#x1f340; &#x1f339; &#x1f33b; &#x1f33a; &#x1f341; &#x1f343; &#x1f342; &#x1f33f; &#x1f344;&#x1f35d; &#x1f35b; &#x1f364; &#x1f4c3;个人主页 &#xff1a;阿然成长日记 …

C++11:并发新纪元 —— 深入理解异步编程的力量(1)

hello &#xff01;大家好呀&#xff01; 欢迎大家来到我的Linux高性能服务器编程系列之《C11&#xff1a;并发新纪元 —— 深入理解异步编程的力量》&#xff0c;在这篇文章中&#xff0c;你将会学习到C新特性以及异步编程的好处&#xff0c;以及其如何带来的高性能的魅力&…

【算法】动态规划之背包DP问题(2024.5.11)

前言&#xff1a; 本系列是学习了董晓老师所讲的知识点做的笔记 董晓算法的个人空间-董晓算法个人主页-哔哩哔哩视频 (bilibili.com) 动态规划系列 【算法】动态规划之线性DP问题-CSDN博客 01背包 步骤&#xff1a; 分析容量j与w[i]的关系&#xff0c;然后分析是否要放…

OGG几何内核开发-BRepAlgoAPI_Fuse与BRep_Builder.MakeCompound比较

最近在与同事讨论BRepAlgoAPI_Fuse与BRep_Builder.MakeCompound有什么区别。 一、从直觉上来说&#xff0c;BRepAlgoAPI_Fuse会对两个实体相交处理&#xff0c;相交的部分会重新的生成相关的曲面。而BRep_Builder.MakeCompound仅仅是把两个实体组合成一个新的实体&#xff0c;…

JUC下的BlockingQueue详解

BlockingQueue是Java并发包(java.util.concurrent)中提供的一个接口&#xff0c;它扩展了Queue接口&#xff0c;增加了阻塞功能。这意味着当队列满时尝试入队操作&#xff0c;或者队列空时尝试出队操作&#xff0c;线程会进入等待状态&#xff0c;直到队列状态允许操作继续。这…

https://是怎么实现的?

默认的网站建设好后都是http访问模式&#xff0c;这种模式对于纯内容类型的网站来说&#xff0c;没有什么问题&#xff0c;但如果受到中间网络劫持会让网站轻易的跳转钓鱼网站&#xff0c;为避免这种情况下发生&#xff0c;所以传统的网站改为https协议&#xff0c;这种协议自己…

信息检索(35):LEXMAE: LEXICON-BOTTLENECKED PRETRAINING FOR LARGE-SCALE RETRIEVAL

LEXMAE: LEXICON-BOTTLENECKED PRETRAINING FOR LARGE-SCALE RETRIEVAL 标题摘要1 引言2 相关工作3 LEXMAE&#xff1a;词典瓶颈屏蔽自动编码器3.1 语言建模编码器3.2 词典瓶颈模块3.3 弱化掩蔽式解码器3.4 词汇加权检索器的预训练目标和微调 4 实验4.1 主要评估4.2 效率分析与…

利用OpenShift的ImageStream部署临时版本

公司是港企&#xff0c;项目都部署在OpenShift上统一管理&#xff0c;因为运行环境为香港网络(外网)&#xff0c;配置、中间件等大陆无法直接访问联通。因此在大陆开发时&#xff0c;测试是个很大的问题。为了避免往Git上频繁提交未确定可用的版本&#xff0c;选择用利用OpenSh…

机器人系统仿真

0、何为仿真 通过计算机对实体机器人系统进行模拟的技术。 1、为何仿真 低成本&#xff1a; 机器人实体一般价格昂贵&#xff0c;为降低机器人学习、调试的成本&#xff1b;高效&#xff1a; 搭建的环境更为多样且灵活&#xff0c;可以提高测试效率以及测试覆盖率&#xff1b…

【python】python中的argparse模块,教你如何自定义命令行参数

✨✨ 欢迎大家来到景天科技苑✨✨ &#x1f388;&#x1f388; 养成好习惯&#xff0c;先赞后看哦~&#x1f388;&#x1f388; &#x1f3c6; 作者简介&#xff1a;景天科技苑 &#x1f3c6;《头衔》&#xff1a;大厂架构师&#xff0c;华为云开发者社区专家博主&#xff0c;…

谁使用DITA?

▲ 搜索“大龙谈智能内容”关注公众号▲ Keith根据LinkedIn上的数据进行的统计&#xff0c;主要反应的西方世界使用DITA的公司。因为LinkedIn在国内不能访问&#xff0c;笔者认为针对中国的数据并不准确。 作者 | John Walker - NXP销售和市场营销业务分析师 2013年4月18日 …

栈实现队列

一、分析 栈的特点是先出再入&#xff0c;而队列的特点为先入先出&#xff0c;所以我们创造两个栈&#xff0c;一个用来存放数据&#xff0c;一个用来实现其它功能此时栈顶为队尾&#xff1b;当要找队头数据时将前n-1个数据移入到另一个栈中&#xff0c;此时剩余那个数据为队头…

ASP.NET网上图书预约系统的设计

摘 要 《网上图书预约系统的设计》是以为读者提供便利为前提而开发的一个信息管理系统&#xff0c;它不仅要求建立数据的一致性和完整性&#xff0c;而且还需要应用程序功能的完备、易用等特点。系统主要采用VB.NET作为前端的应用开发工具&#xff0c;利用SQL Server2000数据…

做题杂记666

[XYCTF2024] 铜匠 题目描述&#xff1a; from Crypto.Util.number import * from secrets import flagm bytes_to_long(flag) m1 getRandomRange(1, m) m2 getRandomRange(1, m) m3 m - m1 - m2def task1():e 149p getPrime(512)q getPrime(512)n p * qd inverse(e,…

VTK官方示例

VTK官方示例 -vtk字體 #!/usr/bin/env python# noinspection PyUnresolvedReferences import vtkmodules.vtkInteractionStyle # noinspection PyUnresolvedReferences import vtkmodules.vtkRenderingFreeType # noinspection PyUnresolvedReferences import vtkmodules.vtk…

Java - Json字符串转List<LinkedHashMap<String,String>>

需求&#xff1a;在处理数据时&#xff0c;需要将一个Object类型对象集合转为有序的Map类型集合。 一、问题 1.原代码&#xff1a; 但在使用时出现报错&#xff1a; Incompatible equality constraint: LinkedHashMap<String, String> and LinkedHashMap 不兼容的相等…

【软考】模拟考卷错题本2024-05-11

1 设计模式- 适配器模式 基本上上述的图解已经涵盖了绝大多数主流的设计模式和其特点。理解记忆下即可&#xff0c;这里对下午的考题也有帮助的。 2 计算机组成原理 cpu 访问速度 这个真的是憨憨咯~看到内存就选内存&#xff0c;题目都没审好。这里的速度比cpu内部的要比外部的…

【C语言】动态内存管理

一、为什么有动态内存分配 在进入正文前&#xff0c;我们简单了解一下变量在内存中的位置&#xff08;在最后具体讲&#xff09;&#xff1a; 函数形参&#xff0c;局部变量&#xff1a;栈区 动态开辟的空间&#xff1a;堆区 全局变量&#xff0c;静态变量&#xff08;static修…

【QVariant类型剖析】

QVariant类型剖析 &#x1f31f; 官方文档中给出的定义&#x1f31f; 特性&#x1f338;QVariant实战应用&#x1f338;项目成果展示 &#x1f31f; 官方文档中给出的定义 &#x1f4d8;Because C forbids unions from including types that have non-default constructors or…

Rancher-Kubewarden-保姆级教学-含Demo测试

一、什么是Kubewarden&#xff1f; What is Kubewarden? | Kubewarden 1、就是容器集群的准入策略引擎。 1、使用的策略其实就是k8s原生的security context. 2、使用WebAssembly来编写策略。 1、WebAssembly&#xff0c;可以使用擅长的开发语言来编写策略。&#xff08;下面的…