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】环境变量是什么？如何配置？详解

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

&#x1f490; &#x1f338; &#x1f337; &#x1f340; &#x1f339; &#x1f33b; &#x1f33a; &#x1f341; &#x1f343; &#x1f342; &#x1f33f; &#x1f344;&#x1f35d; &#x1f35b; &#x1f364; &#x1f4c3;个人主页 &#xff1a;阿然成长日记 …
阅读更多...
C++11：并发新纪元 —— 深入理解异步编程的力量（1）

C++11：并发新纪元 —— 深入理解异步编程的力量（1）

hello &#xff01;大家好呀&#xff01; 欢迎大家来到我的Linux高性能服务器编程系列之《C11&#xff1a;并发新纪元 —— 深入理解异步编程的力量》&#xff0c;在这篇文章中&#xff0c;你将会学习到C新特性以及异步编程的好处&#xff0c;以及其如何带来的高性能的魅力&…
阅读更多...
【算法】动态规划之背包DP问题（2024.5.11）

【算法】动态规划之背包DP问题（2024.5.11）

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

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

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

JUC下的BlockingQueue详解

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

https://是怎么实现的？

默认的网站建设好后都是http访问模式&#xff0c;这种模式对于纯内容类型的网站来说&#xff0c;没有什么问题&#xff0c;但如果受到中间网络劫持会让网站轻易的跳转钓鱼网站&#xff0c;为避免这种情况下发生&#xff0c;所以传统的网站改为https协议&#xff0c;这种协议自己…
阅读更多...
信息检索（35）：LEXMAE: LEXICON-BOTTLENECKED PRETRAINING FOR LARGE-SCALE RETRIEVAL

信息检索（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部署临时版本

利用OpenShift的ImageStream部署临时版本

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

机器人系统仿真

0、何为仿真 通过计算机对实体机器人系统进行模拟的技术。 1、为何仿真 低成本&#xff1a; 机器人实体一般价格昂贵&#xff0c;为降低机器人学习、调试的成本&#xff1b;高效&#xff1a; 搭建的环境更为多样且灵活&#xff0c;可以提高测试效率以及测试覆盖率&#xff1b…
阅读更多...
【python】python中的argparse模块，教你如何自定义命令行参数

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

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

谁使用DITA？

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

栈实现队列

一、分析 栈的特点是先出再入&#xff0c;而队列的特点为先入先出&#xff0c;所以我们创造两个栈&#xff0c;一个用来存放数据&#xff0c;一个用来实现其它功能此时栈顶为队尾&#xff1b;当要找队头数据时将前n-1个数据移入到另一个栈中&#xff0c;此时剩余那个数据为队头…
阅读更多...
ASP.NET网上图书预约系统的设计

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

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

做题杂记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官方示例 -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＞＞

Java - Json字符串转List＜LinkedHashMap＜String,String＞＞

需求&#xff1a;在处理数据时&#xff0c;需要将一个Object类型对象集合转为有序的Map类型集合。 一、问题 1.原代码&#xff1a; 但在使用时出现报错&#xff1a; Incompatible equality constraint: LinkedHashMap<String, String> and LinkedHashMap 不兼容的相等…
阅读更多...
【软考】模拟考卷错题本2024-05-11

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

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

【C语言】动态内存管理

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

【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测试

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

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

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发