当前位置：首页 > news >正文

MySQL索引详解

news 来源：原创 2025/5/17 14:56:25

MySQL索引详解

什么是索引
索引的原理
索引的分类
索引的数据结构
- 二叉树
- 平衡二叉树
- B树
- B+树
聚集索引与非聚集索引
- 概念
- 利用聚集索引查找数据
- 利用非聚集索引查找数据
- 覆盖索引与回表操作
如何正确使用索引

什么是索引

索引是存储引擎中一种数据结构，或者说数据的组织方式，又称之为键key，是存储引擎用于快速找到记录的一种数据结构。

为数据建立索引就好比是为书建目录，或者说是为字典创建音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

索引的原理

索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等

本质都是通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围査询(>、<、between、in)、模糊査询(like)、并集査询(and、or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段.……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是 lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万信左右，所以简单的搜索树难以满足复杂的应用场景。

索引的分类

B+树索引(等值查询与范围查询都快)(主要研究这个)
二叉树 -> 平衡二叉树 -> B树 -> B+树

HASH索弓(等值查询快，范围查询慢)
将数据打散再去查询

FULLTEXT：全文索引 (只可以用在MyISAM引擎)

通过关键字的匹配来进行查询，类似于like的模糊匹配
like + % 在文本比较少时是合适的，但是对于大量的文本数据检索会非常的慢
全文索引在大量的数据面前能比like快得多，但是准确度很低

mysql默认的存储引擎就是 innodb，而 innodb 存储引擎的索引结构就是B+树。

索引的数据结构

B+树是由二叉树 -----> 平衡二叉树 -----> B树 -----> B+树演变而来

数据结构	问题
二叉树	不平衡会导致查询效率低
平衡二叉树	一个节点中只存放一组键值对
B树	只擅长做等值查询
B+树

工作原理

提取每行记录中该字段的值，亦该值当作key，至于key对的value是什么？每种索引结构各不相同
然后以key值为基础构建索引结构
以后查询条件中使用了该字段，则会命中索引结构

二叉树

二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。
顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。
在这里插入图片描述
查找流程：如果要查找 id=12 这个用户的数据

将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点的右子节点作为当前节点。
继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。
把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data。

二叉树也可以是以下所示
在这里插入图片描述
这个时候可以看到我们的二又查找树变成了一个链表。如果我们需要查找id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。为了解决这个问题，我们需要保证二又查找树一直保持平衡，就需要用到平衛二叉树了。

平衡二叉树

平衡二叉树又称AVL树，在满足二又查找树特性的基础上，要求每个节点的左右子树的高度不能超过1。下面是平衡二又树和非平衡二叉树的对比
在这里插入图片描述
由平衡二叉树的构造我们可以发现第一张图中的二叉树其实就是一棵平衡二叉树。平衡二又树保证了树的构造是平衡的，当我们插入或删除数据导致不满足平衡二叉树不平衡时，平衡二又树会进行调整树上的节点来保持平衡。平衡二又树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

总结：因此可以得出实际树结构越高，查询次数越多，查询速度就会越慢。

那么是不是说基于平衡二又树构建索引的结构就可以了呢？答案是否！直接用平衡二叉树这种数据结构来构建索引有什么问题？

首先，因为内存的易失性。一般情况下，我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。
另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。
所以，如果我们单纯用平衡二又树这种数据结构作为索引的数据结构，即每个磁盘块只放一个节点，每个节点中只存放一组键值对，此时如果数据量过大，二叉树的节点则会非常多，树的高度也随即变高，我们查找数据的也会进行很多次磁盘IO，查找数据的效率也会变得极低。
综上，如果我们能够在平衡二叉的树的基础上，在一个节点中存放多组键值对，那么平衡二又树的弊端也就解决了。即构建一个单节点可以存储多个键值对的平衡树，这就是B树。

B树

B树(Balance Tree)是平衡树的意思

在这里插入图片描述

注意：

图中的 p 节点为指向子节点的指针，二又查找树和平衡二叉树其实也有，因为图的美观性，被省略了。
图中的每个节点里面放入了多组键值对，一个节点也称为页，一页即一个磁盘块，在mysql中数据读取的基本单位都是页，即一次IO读取一个页的数据，所以我们这里叫做页更符合mysql中索引的底层数据结构。

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(Key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衛二叉树高很多，假如我们要查找 id=28 的用户信息，那么我们在上图B树中查找的流程如下:

先找到根节点也就是页1，判断28是否在键值17和35之间，在的话那么我们根据页1中的指针p2找到页3
将28和页3中的键值相比较，28在26和30之间，我们根据页3中的指针p2找到页8
将28和页8中的键值相比较，发现有匹配的键值28，键值28对应的用户信息为(28,bv)

注意：

B树的构造是有一些规定的，但这不是本文的关注点，有兴趣的同学可以另行了解
B树也是平衡的，当增加或删除数据而导致B树不平衡时，也是需要进行节点调整的

但是B树只擅长做等值查询，对于范围查询(范围查询的本质就是n次等值查询)，或者说排序操作，B树帮不了我们

B+树

B+树是对B树的进一步优化，如下图

在这里插入图片描述
根据上图B+树和B树的不同

B+树非叶子节点 non-leaf node 上是不存储数据的，仅存储键，而B树的非叶子节点中不仅存储键，也会存储数据。B+树之所以这么做的意义在于：单个节点就是一个页，而数据库中页的大小是固定的，innodb存储引擎默认一页为16KB，所以在页大小固定的前提下，能往一个页中放入更多的节点，相应的树的阶数(节点的子节点树)就会更大，那么树的高度必然更矮更胖，如此来我们查找数据进行磁盘的IO次数就会再次减少，数据查询的效率也会更快。
B+树的阶数是等于键的数量的，例如上图，我们的B+树中每个节点可以存储3个键，3层B+树可以存储 333=27个数据。所以如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储 1000x1000x1000=10亿个数据。而一般根节点是常驻内存的，所以一般我们査找10亿数据，只需要2次磁盘IO。
因为B+树索引的所有数据均存储在叶子节点 leaf node，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。而B树因为数据分散在各个节点，要实现这一点是很不容易的，而且B+树中各个页之间也是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。其实上面的B树我们也可以对各个节点加上链表。其实这些不是它们之前的区别，是因为在mysql的innodb存储引擎中，索引就是这样存储的。也就是说上图中的B+树索引就是innodb中B+树索引真正的实现方式，准确的说应该是聚集索引。

通过上图可以看到，在innodb中，我们数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

MyISAM中的B+树索引实现与innodb中的略有不同。在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。

聚集索引与非聚集索引

概念

聚集索引(又称聚族索引、主键索引，一张表必须有且只有一个)：以 innodb 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为 innodb 是把数据存放在B+树中的，而B+树的键用的就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。

非聚集索引(又称非聚族索引、辅助索引，一张表可以创建多个辅助索引)：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据而是存储该列对应的主键(聚簇索引)，想要查找数据我们还需要根据主键再去聚集索引中进行查找。这个再根据聚集索引查找数据的过程，我们称为回表。明白了聚集索引和非聚集索引的定义，我们应该明白这样一句话：数据即索引，索引即数据。

利用聚集索引查找数据

根据上面B+树索引图，这就是聚集索引，表中的数据存储其中。

现在假设要查找 id>=18 并且 id<40 的用户数据。对应的 sql 语句为：select * from user where id>=18 and id <40，其中id为主键。具体的查找过程如下:

一般根节点都是常驻内存的，也就是说页1己经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。从内存中读取到页1，要查找这个 id>=18 and id<40 的范围值，我们首先需要找到 id=18 的键值。从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3。
要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3。从磁盘中读取页3后将页3放入内存中，然后进行查找，我们可以找到键值18，然后再拿到页3中的指针p1，定位到页8。
同样的页8页不在内存中，我们需要再去磁盘中将页8读取到内存中。将页8读取到内存中后。因为页中的数据是链表进行连接的，而目键值是按照顺序存放的，此时可以根据二分查找法定位到键值18。此时因为已经到数据页了，我们就已经找到一条满足条件的数据了，就是键值18对应的数据。因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。
因为页9不在内存中，就又会加载页9到内存中，并通过和页8中一样的方式进行数据的查找，直到将页12加载到内存中，发现41大于40，此时不满足条件。那么查找到此终止。最终我们找到满足条件的所有数据为：(18,Kl),(19,kl),(22,hj),(24,io),(25,vg),(29,jk),(31,jk),(33,rt),(34,ty).(35,yu),(37,rt).(39,rt)。共12条记录。

利用非聚集索引查找数据

在这里插入图片描述
在叶子节点中，不再存储所有的数据了，存储的是键和主键，对于叶子节点中的 x-y，比如10-23，左边的10表示的是索引的键值，右边的23表示的其对应的主键值，如果我们要找到幸运数字为33的用户信息，对应的sql语句为：selete * from user where LuckNum=33;，查找的流程跟聚焦索引一样，最终会找到主键值。

覆盖索引与回表操作

命中了辅助索引，然后要找的字段值不存在与辅助索引的叶子节点上，则需要根据拿到的主键值再去聚焦索引中查询一遍，然后在聚焦索引的叶子节点找到你想要的内容，这就叫做回表操作，例如只对name字段创建了索引：
create index xxx on user(name);

下述语句，名中了辅助索引，但是select需要查询出的除了辅助索引叶子节点有的name字段值外还想要age字段的值，那么需要进行回表操作
select name,age from user where name="Alan";

命中了某棵索引树，然后在其叶子节点就找到了你想要的值，即不需要回表操作，就是覆盖索引，例如：
create index xxx on user(name);

下述语句，覆盖了索引
select name from user where name="Alan";

如果id字段是主键，那么下述语句也覆盖了索引
select * from user where id=3;

如何正确使用索引

1、以什么字段的值为基础构建索引？

最好是不为空、唯一、占用空间小的字段

2、针对全表查询语句如何优化?

应用场景：用户想要浏览所有的商品信息

select count(id) from s1;

如何优化：开发层面分页查找，用户每次看先从数据中拿

3、针对等值查询

（1）以重复度低（即基数高）的字段（如主键ID）为基础创建索引加速效果明显，因为这些字段能有效地缩小查询范围，从而提高查询效率。

create index xxx on s1(id);
select count(id) from s1 where id= 33;

（2）以重复度高字段为基础创建索引加速在不同查询语句中效果不一样

create index yyy on s1(name);
select count(id) from s1 where name="Alan"; – 速度极慢，因为Alan重复度高
select count(id) from s1l where name!="Alan"; – 速度快
select count(id) from s1 where name="mm"; – 速度快

（3）以占用空间大字段为基础创建索引加速效果不明显

（4）总结：给重复低、且占用空间小的字段值为基础构建索引

4、关于范围查询
select count(id) from s1 where id=33;
select count(id) from s1 where id>33;
select count(id) from s1 where id>33 and id < 36;
select count(id) from s1 where id>33 and id < 1000000;

总结：

innodb存储能加速范围查询，但是查询范围不能特别大
> < != between and like 后的内容%应该往右放，并且左半部分的内容应该尽量精确，因为B+树索引是左前缀特性

5、关于条件字段参与运算
select count(id) from s1 where id*12 =10000;
select count(id) from s1 where id = 10000/12;
select count(id) from s1 where func(id)= 10000/12;

总结：不要让条件字段参与运算，或者说传递给函数

6、索引下堆技术

对于连续多个and的条件，mysql的优化器会分析出多条执行方案，选取最优的一种，即先找到某一个条件把范围缩小
select count(id) from s1 where name = "Alan" and email = "Alan@123" and gender = "male";
select count(id) from s1 where name = "Alan" or email = "Alan@123" or gender = "male";

总结:

and连接的多个条件：mysql的优化器会锁定的是一个小范围，
- 例如相亲：where 年龄=18 and 身高=1.70 and 长相=好看;
or连接的多个条件：锁定的是一个很大的范围，mysql优化没办法了，只能从左到右依次判断条件
- 例如找残次品：where 轮胎坏了 or 方向盘坏了 or 刹车坏了

7、联合索引与最左前缀匹配原则

什么时候创建联合索引：条件中需要用到多个字段，并且多次查询中的多个字段都包含某一个字段

需要注意的问题：重复都低且占用空间较少的字段应该尽量往左放，让其成为最左前缀

creat index xxx on s1(name,age,gender);

最左匹配原则，and连接的多个条件中有

name age gender
name age
name gender
name

select count(id) form s1 where name = "Alan" and email = "Alan@123" and gender ="male";

8、比较类型必须保持一致

例如 id = 10，不要 id =“10”

9、建表时尽可能把占用空间小的字段往左放

不好：creat table t1(name varchar(10),id int);

建议：creat table t1(id int,name varchar(10));

10、使用连接(JOIN)来代替子查询(Sub-Queries)

MySQL索引详解

MySQL索引详解什么是索引索引的原理索引的分类索引的数据结构二叉树平衡二叉树B树B树聚集索引与非聚集索引概念利用聚集索引查找数据利用非聚集索引查找数据覆盖索引与回表操作如何正确使用索引什么是索引索引是存储引擎中一种数据结构，或者说数据的组织方式&…...

编程日记 2025/5/17 14:56:25

【NEXT】网络编程——上传文件（不限于jpg/png/pdf/txt/doc等），或请求参数值是file类型时，调用在线服务接口

最近在使用华为AI平台ModelArts训练自己的图像识别模型，并部署了在线服务接口。供给客户端（如：鸿蒙APP/元服务）调用。 import核心能力： import { http } from kit.NetworkKit; import { fileIo } from kit.CoreFileK…...

编程日记 2025/5/17 6:51:23

【Qt】界面优化

界面优化设置全局样式样式文件使⽤ Qt Designer 编辑样式选择器设置子控件样式伪类选择器样式属性盒模型设置按钮样式设置复选框样式输入框样式列表样式菜单栏样式在 Qt 中对界面的优化和 CSS 类似。语法结构如下： 选择器 {属性名: 属性值; }例如： QP…...

编程日记 2025/5/17 14:11:28

机器学习算法在网络安全中的实践

机器学习算法在网络安全中的实践本文将深入探讨机器学习算法在网络安全领域的应用实践，包括基本概念、常见算法及其应用案例，从而帮助程序员更好地理解和应用这一领域的技术。"> 序言网络安全一直是信息技术领域的重要议题，随着互联…...

编程日记 2025/5/16 23:35:26

课题介绍：基于惯性与单目视觉信息融合的室内微小型飞行器智能自主导航研究

室内微小型飞行器在国防、物流和监测等领域中应用广泛，但在复杂的非合作环境中实时避障和导航仍面临诸多挑战。由于微小型飞行器的载荷和能源限制，迫切需要开发高效的智能自主导航系统。本项目旨在研究基于惯性导航与单目视觉信息融合的技术，…...

编程日记 2025/5/17 12:58:07

Observability：实现 OpenTelemetry 原生可观察性的商业价值

作者：来自 Elastic David Hope 利用开放标准和简化的数据收集转变组织的可观察性策略。现代组织面临着前所未有的可观察性挑战。随着系统变得越来越复杂和分散，传统的监控方法难以跟上步伐。由于数据量每两年翻一番，系统跨越多个云和技术&am…...

编程日记 2025/5/14 9:28:30

nginx 报错404

404：服务器无法正常解析页面，大多是配置问题(路径配置错误)、或访问页面不存在如果你也是用nginx来转接服务的话，那你有可能碰到过这种情况，当你启动服务后，在本地打开页面，发现404，然后你找遍…...

编程日记 2025/5/17 14:44:01

2.2.1 人眼色觉与色度图

文章目录人眼色觉色度图人眼色觉视网膜上的视杆细胞、视锥细胞在人眼色觉中起到重要作用。视杆细胞主要用在弱光暗环境下，其数量远远多于视锥细胞。视锥细胞负责明亮环境的视觉，有L,M,S三种类型的细胞，分别对长、中、短波长敏感&#xff0…...

编程日记 2025/5/17 14:50:42

DeepSeek 遭 DDoS 攻击背后：DDoS 攻击的 “千层套路” 与安全防御 “金钟罩”

当算力博弈升级为网络战争：拆解DDoS攻击背后的技术攻防战——从DeepSeek遇袭看全球网络安全新趋势在数字化浪潮席卷全球的当下，网络已然成为人类社会运转的关键基础设施，深刻融入经济、生活、政务等各个领域。从金融交易的实时清算&#xf…...

编程日记 2025/5/17 14:34:42

c语言（关键字）

前言： 感谢b站鹏哥c语言内容： 栈区（存放局部变量） 堆区静态区（存放静态变量） rigister关键字寄存器，cpu优先从寄存器里边读取数据 #include <stdio.h>//typedef，类型…...

编程日记 2025/5/14 23:47:27

眼见着折叠手机面临崩溃，三星计划增强抗摔能力挽救它

据悉折叠手机开创者三星披露了一份专利，通过在折叠手机屏幕上增加一个抗冲击和遮光层的方式来增强折叠手机的抗摔能力，希望通过这种方式进一步增强折叠手机的可靠性和耐用性，来促进折叠手机的发展。据悉三星和研发可折叠玻璃的企业的做法是在…...

编程日记 2025/5/17 14:38:24

Excel to form ？一键导入微软表单

一句话痛点 “你的Excel越强大，手动复制到Forms就越痛苦。” 合并单元格崩溃成乱码、下拉菜单变纯文本、条件逻辑消失无踪——这些不是技术问题，而是低效工作模式的死刑判决书。直击解决方案：3分钟，3步，300%效率 1…...

编程日记 2025/5/16 7:13:58

使用Ollama本地化部署DeepSeek

1、Ollama 简介 Ollama 是一个开源的本地化大模型部署工具，旨在简化大型语言模型（LLM）的安装、运行和管理。它支持多种模型架构，并提供与 OpenAI 兼容的 API 接口，适合开发者和企业快速搭建私有化 AI 服务。 Ollama …...

编程日记 2025/5/16 10:46:32

【xdoj-离散线上练习】T251（C++）

解题反思： 开始敲代码前想清楚整个思路比什么都重要嘤嘤嘤！看到输入m, n和矩阵，注意不能想当然地认为就是高m，宽n的矩阵，细看含义比如本题给出了树的邻接矩阵，就是n*n的，代码实现中没有用到m这…...

编程日记 2025/5/17 8:44:23

【数据结构】_链表经典算法OJ（力扣/牛客第二弹）

目录 1. 题目1：返回倒数第k个节点 1.1 题目链接及描述 1.2 解题思路 1.3 程序 2. 题目2：链表的回文结构 2.1 题目链接及描述 2.2 解题思路 2.3 程序 1. 题目1：返回倒数第k个节点 1.1 题目链接及描述题目链接： 面试题 …...

编程日记 2025/5/17 14:46:12

线性代数复习笔记

1. 课程学习 1.1 3Blue1Brown 线性代数 2. 基本术语 eigenvector（特征向量）：线性变换中方向保持不变的向量可以视作3D旋转矩阵形成的旋转的轴...

编程日记 2025/5/17 14:39:39

51单片机 01 LED

一、点亮一个LED 在STC-ISP中单片机型号选择 STC89C52RC/LE52RC；如果没有找到hex文件（在objects文件夹下），在keil中options for target-output- 勾选 create hex file。如果要修改编程 ：重新编译-下载/编程-单片机重…...

编程日记 2025/5/17 6:23:37

用一个例子详细说明python单例模式

单例模式是一种设计模式，它确保一个类只有一个实例，并提供一个全局访问点来访问该实例。这在需要控制资源（如数据库连接、文件系统等）的访问时非常有用。下面是一个使用Python实现单例模式的例子： class Singleton:…...

编程日记 2025/5/17 14:44:01

QT知识点复习

1.qt核心机制对象树、信号和槽、事件机制 2.对象树的作用优化了内存回收机制。子对象实例化的时候，被父对象放对象树上，父对象释放内存，子对象也释放内存 3.信号和槽的作用实现多个组件之间的通讯 4.信号和槽的几种连接方式 1.UI界面提…...

编程日记 2025/5/17 14:07:13

医学图像分割任务的测试代码

测试集进行测试 import os import torch import numpy as np from torch.utils.data import DataLoader from sklearn.metrics import (precision_score,recall_score,f1_score,roc_curve,auc,confusion_matrix, ) import matplotlib.pyplot as plt from utils import NiiData…...

编程日记 2025/5/16 8:50:12

MySQL索引详解

什么是索引

索引的原理

索引的分类

索引的数据结构

二叉树

平衡二叉树

B树

B+树

聚集索引与非聚集索引

概念

利用聚集索引查找数据

利用非聚集索引查找数据

覆盖索引与回表操作

如何正确使用索引

相关文章：