6.2.3 【MySQL】InnoDB的B+树索引的注意事项

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 6.2.3 【MySQL】InnoDB的B+树索引的注意事项

6.2.3.1 根页面万年不动窝

B+ 树的形成过程是这样的:


每当为某个表创建一个 B+ 树索引(聚簇索引不是人为创建的,默认就有)的时候,都会为这个索引创建一个 根节点 页面。最开始表中没有数据的时候,每个 B+ 树索引对应的 根节点 中既没有用户记录,也没有目录项记录。

随后向表中插入用户记录时,先把用户记录存储到这个 根节点 中。

当 根节点 中的可用空间用完时继续插入记录,此时会将 根节点 中的所有记录复制到一个新分配的页,比如 页a 中,然后对这个新页进行 页分裂 的操作,得到另一个新页,比如 页b 。这时新插入的记录根据键值(也就是聚簇索引中的主键值,二级索引中对应的索引列的值)的大小就会被分配到 页a 或者 页b 中,而根节点 便升级为存储目录项记录的页。

要注意的是:一个B+树索引的根节点自诞生之日起,便不会再移动。这样只要我们对某个表建立一个索引,那么它的 根节点 的页号便会被记录到某个地方,然后凡是 InnoDB 存储引擎需要用到这个索引的时候,都会从那个固定的地方取出 根节点 的页号,从而来访问这个索引。


6.2.3.2 内节点中目录项记录的唯一性

我们知道 B+ 树索引的内节点中目录项记录的内容是 索引列 + 页号 的搭配,但是这个搭配对于二级索引来说有点儿不严谨。还拿 index_demo 表为例,假设这个表中的数据是这样的:

c1

c2

c3

1

1

'u'

3

1

'd'

5

1

'y'

7

1

'a'

如果二级索引中目录项记录的内容只是 索引列 + 页号 的搭配的话,那么为 c2 列建立索引后的 B+ 树应该长这样:

         

为了让新插入记录能找到自己在那个页里,我们需要保证在B+树的同一层内节点的目录项记录除 页号 这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的:


索引列的值

主键值

页号

也就是我们把 主键值 也添加到二级索引内节点中的目录项记录了,这样就能保证 B+ 树每一层节点中各条目录项记录除 页号 这个字段外是唯一的。

6.2.3.3 一个页面最少存储2条记录

B+树本质上就是一个大的多层级目录,每经过一个目录时都会过滤掉许多无效的子目录,直到最后访问到存储真实数据的目录。InnoDB 的一个数据页至少可以存放两条记录。

6.2.4 MyISAM中的索引方案简单介绍

InnoDB 中索引即数据,也就是聚簇索引的那棵 B+ 树的叶子节点中已经把所有完整的用户记录都包含了,而 MyISAM 的索引方案虽然也使用树形结构,但是却将索引和数据分开存储:


将表中的记录按照记录的插入顺序单独存储在一个文件中,称之为 数据文件 。这个文件并不划分为若干个数据页,有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。但是在插入数据的时候并没有刻意按照主键大小排序,所以我们并不能在这些数据上使用二分法进行查找。

使用 MyISAM 存储引擎的表会把索引信息另外存储到一个称为 索引文件 的另一个文件中。 MyISAM 会单独为表的主键创建一个索引,只不过在索引的叶子节点中存储的不是完整的用户记录,而是 主键值 + 行号 的组合。也就是先通过索引找到对应的行号,再通过行号去找对应的记录!

这一点和 InnoDB 是完全不相同的,在 InnoDB 存储引擎中,我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录,而在 MyISAM 中却需要进行一次 回表 操作,意味着 MyISAM 中建立的索引相当于全部都是 二级索引 !

如果有需要的话,我们也可以对其它的列分别建立索引或者建立联合索引,原理和 InnoDB 中的索引差不多,不过在叶子节点处存储的是 相应的列 + 行号 。这些索引也全部都是 二级索引 。

6.2.5 MySQL中创建和删除索引的语句

InnoDB 和 MyISAM 会自动为主键或者声明为 UNIQUE 的列去自动建立 B+ 树索引,但是如果我们想为其他的列建立索引就需要我们显式的去指明。为啥不自动为每个列都建立个索引呢?别忘了,每建立一个索引都会建立一棵 B+ 树,每插入一条记录都要维护各个记录、数据页的排序关系,这是很费性能和存储空间的。

我们可以在创建表的时候指定需要建立索引的单个列或者建立联合索引的多个列:

CREATE TALBE 表名 ( 
各种列的信息 ··· ,  
[KEY|INDEX] 索引名 (需要被索引的单个列或多个列))  

其中的 KEY 和 INDEX 是同义词,任意选用一个就可以。我们也可以在修改表结构的时候添加索引:

ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);

也可以在修改表结构的时候删除索引:

ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

比方说我们想在创建 index_demo 表的时候就为 c2 和 c3 列添加一个 联合索引 ,可以这么写建表语句:

CREATE TABLE index_demo( 
c1 INT, c2 INT, c3 CHAR(1), PRIMARY KEY(c1), 
INDEX idx_c2_c3 (c2, c3));

在这个建表语句中我们创建的索引名是 idx_c2_c3 ,这个名称可以随便起,不过我们还是建议以 idx_ 为前缀,后边跟着需要建立索引的列名,多个列名之间用下划线 _ 分隔开。

如果我们想删除这个索引,可以这么写:

ALTER TABLE index_demo DROP INDEX idx_c2_c3;
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
28天前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
2月前
|
存储 网络协议 关系型数据库
MySQL8.4创建keyring给InnoDB表进行静态数据加密
MySQL8.4创建keyring给InnoDB表进行静态数据加密
85 1
|
24天前
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
63 12
|
2月前
|
SQL 存储 关系型数据库
MySQL选错索引了怎么办?
本文探讨了MySQL中因索引选择不当导致查询性能下降的问题。通过创建包含10万行数据的表并插入数据,分析了一条简单SQL语句在不同场景下的执行情况。实验表明,当数据频繁更新时,MySQL可能因统计信息不准确而选错索引,导致全表扫描。文章深入解析了优化器判断扫描行数的机制,指出基数统计误差是主要原因,并提供了通过`analyze table`重新统计索引信息的解决方法。
|
2月前
|
SQL 缓存 关系型数据库
使用温InnoDB缓冲池启动MySQL测试
使用温InnoDB缓冲池启动MySQL测试
52 0
|
3月前
|
自然语言处理 关系型数据库 MySQL
MySQL索引有哪些类型?
● 普通索引:最基本的索引,没有任何限制。 ● 唯一索引:索引列的值必须唯一,但可以有空值。可以创建组合索引,则列值的组合必须唯一。 ● 主键索引:是特殊的唯一索引,不可以有空值,且表中只存在一个该值。 ● 组合索引:多列值组成一个索引,用于组合搜索,效率高于索引合并。 ● 全文索引:对文本的内容进行分词,进行搜索。
|
1月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
3月前
|
关系型数据库 MySQL Java
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
【YashanDB知识库】原生mysql驱动配置连接崖山数据库
|
1月前
|
存储 关系型数据库 MySQL
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
本文详细介绍了在 MySQL 中创建数据库和表的方法。包括安装 MySQL、用命令行和图形化工具创建数据库、选择数据库、创建表(含数据类型介绍与选择建议、案例分析、最佳实践与注意事项)以及查看数据库和表的内容。文章专业、严谨且具可操作性,对数据管理有实际帮助。
大数据新视界 --面向数据分析师的大数据大厂之 MySQL 基础秘籍:轻松创建数据库与表,踏入大数据殿堂
|
1月前
|
SQL 关系型数据库 MySQL
MySQL下载安装全攻略!小白也能轻松上手,从此数据库不再难搞!
这是一份详细的MySQL安装与配置教程,适合初学者快速上手。内容涵盖从下载到安装的每一步操作,包括选择版本、设置路径、配置端口及密码等。同时提供基础操作指南,如数据库管理、数据表增删改查、用户权限设置等。还介绍了备份恢复、图形化工具使用和性能优化技巧,帮助用户全面掌握MySQL的使用方法。附带常见问题解决方法,保姆级教学让你无忧入门!
MySQL下载安装全攻略!小白也能轻松上手,从此数据库不再难搞!

推荐镜像

更多