MySQL的索引——索引的介绍及其数据结构B+树 &amp； 索引的类型 &amp； 索引的使用及其失效场景 &amp； 相关名词解释_数据库索引结构

先自我介绍一下，小编浙江大学毕业，去过华为、字节跳动等大厂，目前阿里P7

深知大多数程序员，想要提升技能，往往是自己摸索成长，但自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年最新软件测试全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上软件测试知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

如果你需要这些资料，可以添加V获取：vip1024b （备注软件测试）

正文

• 图中的p节点为指向子节点的指针，二叉查找树和平衡二叉树其实也有，图中的每个节点称为页，页就是我们上面说的磁盘块，在mysql中数据读取的基本单位都是页。
• 从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的B树为3阶B树，高度也会很低。
• 基于这个特性，B树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

（4）假如我们要查找id=28的用户信息，那么我们在上图B树中查找的流程如下：

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

5、B+树

B+树是对B树的进一步优化。让我们先来看下B+树的结构图：

（1）B+树非叶子节点上是不存储数据的，仅存储键值，这么做是因为在数据库中页的大小是固定的，innodb中页的默认大小是16KB。如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数有会再次减少。

（2）B+树的阶数是等于键值的数量，如果我们的B+树一个节点可以存储1000个键值，那么3层B+树可以存储1000×1000×1000=10亿个数据。一般根节点是常驻内存的，所以一般我们查找10亿数据，只需要2次磁盘IO。

（3）因为B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的。那么B+树使得范围查找，排序查找，分组查找以及去重查找变得非常简单。

（4）B+树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。上图中的B+树索引就是innodb中B+树索引的实现方式，准确的说应该是聚集索引，在MyISAM中，B+树索引的叶子节点并不存储数据，而是存储数据的文件地址。

三、聚集索引与非聚集索引

在MySQL中，B+树索引按照存储方式的不同分为聚集索引和非聚集索引

1、聚集索引（聚簇索引）

以innodb作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中存储了行数据，可以直接在聚集索引中查找到想要的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。

2、非聚集索引（非聚簇索引）

（1）以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。

（2）非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

（3）如果使用了覆盖索引，则不需要回表，直接通过辅助索引就可以查找到想要的数据。覆盖索引就是指select查询的数据只需要在索引中就能取得，而不必读取数据行，换句话说就是，查询列要被所建的索引覆盖。

3、利用聚集索引查找数据

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

（1）根节点是常驻内存的，也就是说页1已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。从内存中读取到页1，要查找这个id>=18 and id <40或者范围值，我们首先需要找到id=18的键值。从页1中我们可以找到键值18，此时我们需要根据指针p2，定位到页3。

（2）要从页3中查找数据，我们就需要拿着p2指针去磁盘中进行读取页3。从磁盘中读取页3后将页3放入内存中，然后进行查找，我们可以找到键值18，然后再拿到页3中的指针p1，定位到页8。

（3）同样的页8页不在内存中，我们需要再去磁盘中将页8读取到内存中。将页8读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值18。

（4）因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页8中的键值依次进行遍历查找并匹配满足条件的数据。我们可以一直找到键值为22的数据，然后页8中就没有数据了，此时我们需要拿着页8中的p指针去读取页9中的数据。因为页9不在内存中，就又会加载页9到内存中，并通过和页8中一样的方式进行数据的查找，直到将页12加载到内存中，发现41大于40，此时不满足条件。那么查找到此终止。

4、利用非聚集索引查找数据

（1）非聚集索引叶子节点中，不再存储所有的数据，存储的是键值和主键。非聚集索引也称之为辅助索引（普通索引、二级索引），一个表中只有一个聚集索引可以有多个非聚集索引。

（2）辅助索引的搜索过程：

• 比如我们给name字段建立索引，InnoDB 都会给每个加了索引的字段生成索引树，此时会建立name字段的索引B+树，节点中存储的是name索引字段的数据，叶子节点中存储了索引字段的数据和主键的值。
• 拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在name 索引树找到的主键 KEY 查找到对应的数据。【两次查询，回表操作】

（3）执行select * from user where name = ‘jim’ 的执行过程

• 根节点是常驻内存的，也就是说页1已经在内存中了，从内存中读取到页1，根据指针P1定位到页2
• 根据页2中的P2指针定位到页6
• 比较找到索引对应的值为jim，同时获取到主键为20
• 在根据20这个主键到主键索引中获取到这个叶子节点中存储的行数据。

四、索引的类型

1、主键索引

• 在创建表的时候，添加了主键PRIMARY KEY 的字段，会在该字段上默认创建主键索引。

创建表employee：

create table employee(
empno int primary key not null auto_increment,
ename varchar(10),
job varchar(10),
sal decimal(10,2),
hiredate datetime,
deptno int
);

2、唯一索引

• 唯一索引，索引列的值必须唯一
• 唯一索引和主键索引的区别就是，唯一索引允许出现空值，而主键索引不能为空

– 创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))

– 修改表结构的方式创建索引
ALTER TABLE table_name ADD UNIQUE index indexName (column(length))

– 删除索引
DROP INDEX index_name ON table

创建索引

删除索引

create table employee(
empno int primary key not null auto_increment,
ename varchar(10),
job varchar(10),
sal decimal(10,2),
hiredate datetime,
deptno int
);

create unique index idx_name on employee(ename);

alter table employee add unique index idx_name (ename);

– 删除索引
DROP INDEX idx_name ON employee

– 创建表的时候同时创建索引
CREATE TABLE table (
id INT ( 11 ) NOT NULL AUTO_INCREMENT,
title CHAR ( 255 ) NOT NULL,
content text NULL,
time INT ( 10 ) NULL DEFAULT NULL,
PRIMARY KEY ( id ),
index idx_title (title)
);

3、组合索引

• 在多个列上创建的索引，就是组合索引。

alter table t add index index_name(a,b,c);

示例：

alter table employee add index idx_name_job_sal(ename,job,sal);

• 组合索引的用处，假设我现在表有个多个字段：id、name、age、gender，然后我经常使用以下的查询条件

select * from user where name = ‘xx’ and age = xx

这个时候，我们就可以通过组合 name 和 age 来建立一个组合索引，加快查询效率。

• 在多个字段上创建索引，遵循最左匹配原则。

五、索引的使用原则

1、什么情况下不建索引

• 表记录太少
• 区分度不高的字段不适合建立索引，如性别等
• where条件中用不到的字段不适合建立索引
• 经常插入、删除、修改的表要减少索引
• text，image 等类型不适合建立索引。
• MySQL能估计出全表扫描比使用索引更快的时候，不使用索引
• 参与列计算的列不适合建索引

2、索引失效场景

（1）不满足最左前缀

所谓最左前缀，可以想象成依次执行的过程，假设我们有一个复合索引【ename,sal,job】，那这个依次顺序是：ename，sal，job。

create index idx_ename_job_sal on employee(ename,sal,job)

• 以下sql会走索引

select * from employee where ename=‘zs’ and sal=1000 and job=‘sales’

• 最左前缀，出现跳跃的情况，会导致索引失效。

select * from employee where job=‘sales’
select * from employee where job=‘sales’ and sal>1000

• 出现跳跃的情况，满足最左前缀会走索引

select * from employee where ename=‘zs’ and sal>1000
select * from employee where ename=‘zs’ and job=‘sales’
select * from employee where sal>1000 and ename=‘zs’ – 内部优化器会进行调整

（2）范围查询之后

范围查询之后的索引字段，会失效，但本身用来范围查询的那个索引字段依然有效。

以下示例，job列的索引失效，通过长度可以看出。

select * from employee where ename=‘zs’ and sal>1000 and job=‘sales’

（3）索引字段做运算

对索引字段做运算，使用函数等都会导致索引失效。

select * from employee where substring(ename,2,3)=‘aa’;

（4）隐式类型转换

索引字段为字符串类型，由于在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。

select * from employee where ename=11

（5）避免使用select *

无法使用覆盖索引，消耗更多的 CPU 和 IO 以网络带宽资源。

（6）or分割的条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

select * from employee where ename=‘zs’ or deptno=20

（7） 以%开头的Like模糊查询

解决方法：使用覆盖索引

如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。（即select和where条件中的字段都出现在索引中，即为覆盖索引）

select ename from employee where ename like ‘%A%’

（8）order by导致索引失效

在基于order by和limit进行使用时，是否走索引涉及到数据库版本。

主键使用order by时，可以正常走索引。

六、MySQL技术名词

1、回表

首先我们需要知道，建立几个索引，就会生成几棵B+Tree，但是带有原始数据行的B+Tree只有一棵，另外一棵树上的叶子节点存储的是主键值。

例如，我们通过主键建立了主键索引，在叶子节点上存放的是行数据。

当我们创建了两个索引时，一个是主键，一个是name，此时会在生成两棵B+Tree。name索引这棵树的叶子节点存放的是name列的值和主键值，当我们通过name进行查找数据时，会得到一个主键，然后在通过主键再去上面的这个主键B+Tree中进行查找数据，这个操作称之为回表

当执行下面的SQL语句时，会查找第一颗树，直接返回数据。

select * from tb where id = 1

当执行下面的SQL查询时，会先查找第二棵树得到主键的值，然后根据主键的值再去主键索引的B+Tree中查找数据。

select * from tb where name = ‘xm’

2、索引覆盖

我们看下面的两个SQL语句，看看它们的查询过程是一样的么？

select * from tb where id = 1
select name from tb where name = ‘zs’

答案是不一样的，首先我们看第二个语句，输出的列就是索引列。当我们通过name建立的B+Tree进行查询时，此时叶子节点就已经包含要查找的name数据，无需再到主键索引B+Tree上进行数据查找，这样的查询称之为索引覆盖。索引覆盖不需要进行回表查询，大大提示性能。

3、最左匹配

这里提到的 最左匹配 和 索引下推 都是针对于组合索引的。

例如，我们有这样一个索引

name age：组合索引`

必须要先匹配name，才能匹配到age。这个我们就被称为最左匹配。

例如下面的几条SQL语句，那些语句不会使用组合索引？

where name = ? and age = ?

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
称之为索引覆盖。索引覆盖不需要进行回表查询，大大提示性能。

3、最左匹配

这里提到的 最左匹配 和 索引下推 都是针对于组合索引的。

例如，我们有这样一个索引

name age：组合索引`

必须要先匹配name，才能匹配到age。这个我们就被称为最左匹配。

例如下面的几条SQL语句，那些语句不会使用组合索引？

where name = ? and age = ?

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化的资料的朋友，可以添加V获取：vip1024b （备注软件测试）
[外链图片转存中…(img-uV6dWIEc-1713338917099)]

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！