AI 助理
备案控制台
开发者社区 数据库 文章 正文

【MySQL技术内幕】5.4-B+树索引

2024-06-13 39
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 【MySQL技术内幕】5.4-B+树索引

1.聚集索引

  • Innodb中每张表都会有一个聚集索引，其行记录存在该索引的叶子节点上。
  • 叶子节点通过双向链表链接，按照主键的顺序排序
  • 页中的记录也是双向链表进行维护，物理上可以不按照顺序存储。
  • 所有索引只能定位到页，不能通过索引定位到具体的行，到页后通过Page Directory确定行。

聚集索引的另一个好处是,它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据。如用户需要查询一张注册用户的表,查询最后注册的10位用户,由于B+树索引是双向链表的,用户可以快速找到最后一个数据页,并取出10条记录。若用命令 EXPLAIN进行分析,可得:

image.png

可以看到虽然使用 ORDER BY对记录进行排序,但是在实际过程中并没有进行所谓的 filesort操作,而这就是因为聚集索引的特点另一个是范围查询( range query),即如果要查找主键某一范围内的数据,通过叶子节点的上层中间节点就可以得到页的范围,之后直接读取数据页即可,又如:

image.png

执行 EXPLAIN得到了 MySQL数据库的执行计划( execute plan),并且在rows列中给出了一个查询结果的预估返回行数。要注意的是,rows代表的是一个预估值,不是确切的值。

2.辅助索引

对于辅助索引( Secondary Index,也称非聚集索引),叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外,每个叶子节点中的索引行中还包含了一个书签( bookmark)。该书签用来告诉 InnoDB存储引擎哪里可以找到与索引相对应的行数据。由于 InnoDB存储引擎表是索引组织表,因此 InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

辅助索引的存在并不影响数据在聚集索引中的组织,因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时, InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键,然后再通过主键索引来找到个完整的行记录。举例说,如果在一棵高度为3的辅助索引树中查找数据,那需要对这棵辅助索引树遍历3次找到指定主键,如果聚集索引树的高度同样为3,那么还需要对聚集索引树进行3次查找,最终找到一个完整的行数据所在的页,因此一共需要6次逻辑IO访问以得到最终的一个数据页。

3.B+树索引的分裂

B+树索引页的分裂并不总是从页的中间记录开始,这样可能会导致页空间的浪费。原因参考juejin.im/post/684490…

InnoDB存储引擎的 Page Header中有以下几个部分用来保存插入的顺序信息:

  • PAGE_LAST_INSERT
  • PAGE_DIRECTION
  • PAGE_N_DIRECTION

通过这些信息, InnoDB存储引擎可以决定是向左还是向右进行分裂,同时决定将分裂点记录为哪一个。若插入是随机的,则取页的中间记录作为分裂点的记录,这和之前介绍的相同。若往同一方向进行插入的记录数量为5,并且目前已经定位( cursor)到的记录( InnoDB存储引擎插入时,首先需要进行定位,定位到的记录为待插人记录的前条记录)之后还有3条记录,则分裂点的记录为定位到的记录后的第三条记录,否则分裂点记录就是待插入的记录。

来看一个向右分裂的例子,并且定位到的记录之后还有3条记录,则分裂点记录如图所示。

image.png

图5-17向右分裂且定位到的记录之后还有3条记录, split record为分裂点记录最终向右分裂得到如图5-18所示的情况。 image.png

对于图5-19的情况,分裂点就为插入记录本身,向右分裂后仅插入记录本身,这在自增插人时是普遍存在的一种情况 image.png

4.B+树索引的管理

4.1 索引管理

索引的创建和删除可以通过两种方法,一种是 ALTER TABLE,另一种是 CREATE/DROP INDEX。通过 ALTER TABLE创建索引的语法为: image.png

CREATE/ DROP INDEX的语法同样很简单: image.png

用户可以设置对整个列的数据进行索引,也可以只索引一个列的开头部分数据,如

alter table t add key ids_b (b(100));

若用户想要查看表中索引的信息,可以使用命令 SHOW INDEX。

mysql> show index from t_reco_confirm_summary\G
*************************** 1. row ***************************
        Table: t_reco_confirm_summary
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 11422
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: t_reco_confirm_summary
   Non_unique: 1
     Key_name: idx_rd_date
 Seq_in_index: 1
  Column_name: record_date
    Collation: A
  Cardinality: 300
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: t_reco_confirm_summary
   Non_unique: 1
     Key_name: idx_channel
 Seq_in_index: 1
  Column_name: channel
    Collation: A
  Cardinality: 14
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: t_reco_confirm_summary
   Non_unique: 1
     Key_name: idx_channel
 Seq_in_index: 2
  Column_name: merchant_id
    Collation: A
  Cardinality: 74
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
4 rows in set (0.01 sec)

接着具体阐述命令 SHOW INDEX展现结果中每列的含义。

  • Table:索引所在的表名。
  • Non_unique:非唯一的索引,可以看到 primary key是0,因为必须是唯的
  • Key_name:索引的名字,用户可以通过这个名字来执行 DROP INDEX
  • Seq_in_index:索引中该列的位置,如果看联合索引idx_channel就比较直观了。
  • Column_name:索引列的名称。
  • Collation:列以什么方式存储在索引中。可以是A或NULL。B+树索引总是A,即排序的。如果使用了Heap存储引擎,并且建立了Hash索引,这里就会显示NULL了。因为Hash根据Hash桶存放索引数据,而不是对数据进行排序。
  • Cardinality:非常关键的值,表示索引中唯一值的数目的估计值。 Cardinality表的行数应尽可能接近1,如果非常小,那么用户需要考虑是否可以删除此索引。
  • Sub_part:是否是列的部分被索引。如果看idxb这个索引,这里显示100,表示只对b列的前100字符进行索引。如果索引整个列,则该字段为NULL
  • Packed:关键字如何被压缩。如果没有被压缩,则为NULL。
  • Null:是否索引的列含有NULL值。
  • Index type:索引的类型。 InnoDB存储引擎只支持B+树索引,所以这里显示的都是 BTREE。
  • Comment:注释。

Cardinality值非常关键,优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的,即并非每次索引的更新都会更新该值,因为这样代价太大了。因此这个值是不太准确的,只是一个大概的值。如果需要更新索引 Cardinality的信息,可以使用 ANALYZE TABLE命令,如:

mysql> analyze table t;
+----------+---------+----------+----------+
| Table    | Op      | Msg_type | Msg_text |
+----------+---------+----------+----------+
| mytest.t | analyze | status   | OK       |
+----------+---------+----------+----------+
1 row in set (0.02 sec)

4.2 Fast Index Creation

MySQL5.5版本之前(不包括55)存在的一个普遍被人诟病的问题是 MySQL数据库对于索引的添加或者删除的这类DDL操作, MySQL数据库的操作过程为:

  • 首先创建一张新的临时表,表结构为通过命令 ALTER TABLE新定义的结构
  • 然后把原表中数据导入到临时表。
  • 接着删除原表。
  • 最后把临时表重名为原来的表名。

可以发现,若用户对于一张大表进行索引的添加和删除操作,那么这会需要很长的时间。更关键的是,若有大量事务需要访问正在被修改的表,这意味着数据库服务不可用。

InnoDB存储引擎从 InnoDB1.0.x版本开始支持一种称为 Fast Index Creation(快速索引创建)的索引创建方式——简称FIC。

对于辅助索引的创建, InnoDB存储引擎会对创建索引的表加上一个S锁。在创建的过程中,不需要重建表,因此速度较之前提高很多,并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了, InnoDB存储引擎只需更新内部视图,并将辅助索引的空间标记为可用,同时删除 My SQL数据库内部视图上对该表的索引定义即可。这里需要特别注意的是,临时表的创建路径是通过参数 tmpdir进行设置的。用户必须保证 tmpdir有足够的空间可以存放临时表,否则会导致创建索引失败。

由于FC在索引的创建的过程中对表加上了S锁,因此在创建的过程中只能对该表进行读操作,若有大量的事务需要对目标表进行写操作,那么数据库的服务同样不可用。此外,FIC方式只限定于辅助索引,对于主键的创建和删除同样需要重建一张表。

4.3 Online DDL

虽然FIC可以让 InnoDB存储引擎避免创建临时表,从而提高索引创建的效率。但正如前面小节所说的,索引创建时会阻塞表上的DML操作。MySQL5.6版本开始支持 Online DDL(在线数据定义)操作,其允许辅助索引创建的同时,还允许其他诸如 INSERT、 UPDATE, DELETE这类DML操作,这极大地提高了 MySQL数据库在生产环境中的可用性。

此外,不仅是辅助索引,以下这几类DDL操作都可以通过“在线”的方式进行操作:

  • 辅助索引的创建与删除
  • 改变自增长值
  • 添加或删除外键约束
  • 列的重命名

通过新的ALTER TABLE语法,用户可以选择索引的创建方式:

image.png

ALGORITHM指定了创建或删除索引的算法,COPY表示按照 MySQL5.1版本之前的工作模式,即创建临时表的方式。 INPLACE表示索引创建或删除操作不需要创建临时表。 DEFAULT表示根据参数 old_alter_table来判断是通过 INPLACE还是COPY的算法,该参数的默认值为OFF,表示采用 INPLACE的方式,如:

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.00 sec)
 
mysql> show variables like 'old_alter_table';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| old_alter_table | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

1 row in set (0.00 sec)

LOCK部分为索引创建或删除时对表添加锁的情况,可有的选择为:

(1) NONE

执行索引创建或者删除操作时,对目标表不添加任何的锁,即事务仍然可以进行读写操作,不会收到阻塞。因此这种模式可以获得最大的并发度。

(2) SHARE

这和之前的FC类似,执行索引创建或删除操作时,对目标表加上一个S锁。对于并发地读事务,依然可以执行,但是遇到写事务,就会发生等待操作。如果存储引擎矿支持 SHARE模式,会返回一个错误信息。

(3) EXCLUSIVE

在 EXCLUSIVE模式下,执行索引创建或删除操作时,对目标表加上一个X锁。读写事务都不能进行,因此会阻塞所有的线程,这和COPY方式运行得到的状态类似,但是不需要像COPY方式那样创建一张临时表。

(4 DEFAULT

DEFAULT模式首先会判断当前操作是否可以使用NONE模式,若不能,则判断是否可以使用 SHARE模式,最后判断是否可以使用 EXCLUSIVE模式。也就是说DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式。

InnoDB存储引擎实现 Online DDl的原理是在执行创建或者删除操作的同时,将INSERT、 UPDATE、 DELETE这类DML操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上,以此达到数据的一致性。这个缓存的大小由参数 innodb_online_alter_log_max_size控制,默认的大小为128MB。若用户更新的表比较大,并且在创建过程中伴有大量的写事务,如遇到 innodb_online_alter_log_max_size的空间不能存放日志时,会抛出类似如下的错误:

Error: 1799SQLSTATE: HY000(ER INNODB ONLINE LOG TOO BIG)

Message: Creating index tidx aaa required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again

对于这个错误,用户可以调大参数innodb_online_alter_log_max_size,以此获得更大的日志缓存空间。此外,还可以设置 ALTER TABLE的模式为 SHARE,这样在执行过程中不会有写事务发生,因此不需要进行DML日志的记录。

需要特别注意的是,由于 Online DDl在创建索引完成后再通过重做日志达到数据库的最终一致性,这意味着在索引创建过程中,SQL优化器不会选择正在创建中的索引


文章标签：
云数据库 RDS MySQL 版
日志服务
关系型数据库
索引
存储
SQL
MySQL
关键词：
云数据库 RDS MySQL 版技术
云数据库 RDS MySQL 版索引
云数据库 RDS MySQL 版b+树
云数据库 RDS MySQL 版技术内幕
云数据库 RDS MySQL 版技术索引
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
hsfxuebao
目录
相关文章
技术自由圈/原疯狂创客圈
|
23天前
|
存储 关系型数据库 MySQL
阿里面试：为什么要索引？什么是MySQL索引？底层结构是什么？
尼恩是一位资深架构师，他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构，主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型，如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树，它能够有效支持范围查询和顺序遍历，同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点，并提供了多个面试题及其解答，帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
技术自由圈/原疯狂创客圈
66 6
潇洒洒
|
5天前
|
监控 关系型数据库 MySQL
MySQL自增ID耗尽应对策略：技术解决方案全解析
在数据库管理中，MySQL的自增ID（AUTO_INCREMENT）属性为表中的每一行提供了一个唯一的标识符。然而，当自增ID达到其最大值时，如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
潇洒洒
18 3
土木林森
|
14天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型，以及如何创建和维护索引，结合实战案例分析查询执行计划，帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
土木林森
74 1
跃@sir
|
25天前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理？
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理？
跃@sir
53 1
土木林森
|
15天前
|
监控 关系型数据库 MySQL
数据库优化：MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件，其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引（如B-Tree、复合索引）和优化查询语句（如使用EXPLAIN、优化分页查询），可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志，持续优化性能。
土木林森
46 0
蓝易云
|
26天前
|
监控 关系型数据库 MySQL
mysql8索引优化
综上所述，深入理解和有效实施这些索引优化策略，是解锁MySQL 8.0数据库高性能查询的关键。
蓝易云
28 0
技术自由圈/原疯狂创客圈
|
30天前
|
SQL 关系型数据库 MySQL
美团面试：mysql 索引失效？怎么解决？ （重点知识，建议收藏，读10遍+）
本文详细解析了MySQL索引失效的多种场景及解决方法，包括破坏最左匹配原则、索引覆盖原则、前缀匹配原则、`ORDER BY`排序不当、`OR`关键字使用不当、索引列上有计算或函数、使用`NOT IN`和`NOT EXISTS`不当、列的比对等。通过实例演示和`EXPLAIN`命令分析，帮助读者深入理解索引失效的原因，并提供相应的优化建议。文章还推荐了《尼恩Java面试宝典》等资源，助力面试者提升技术水平，顺利通过面试。
技术自由圈/原疯狂创客圈
32 0
听风de歌
|
7天前
|
SQL 关系型数据库 MySQL
go语言数据库中mysql驱动安装
【11月更文挑战第2天】
听风de歌
21 4
路卿老师
|
5天前
|
SQL 关系型数据库 MySQL
12 PHP配置数据库MySQL
路老师分享了PHP操作MySQL数据库的方法，包括安装并连接MySQL服务器、选择数据库、执行SQL语句（如插入、更新、删除和查询），以及将结果集返回到数组。通过具体示例代码，详细介绍了每一步的操作流程，帮助读者快速入门PHP与MySQL的交互。
路卿老师
17 1
m1a6
|
1月前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构，类似于书籍中的索引。它允许用户快速找到数据，而无需扫描整个表。MySQL中的索引可以显著提升查询速度，使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
m1a6
61 3
Mysql(4)—数据库索引

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    【MySQL用法】MySQL中大于，大于等于，小于，小于等于的转义写法
  • 4
    mysql连接池DataSource，DruidDataSource的理解及其使用
  • 5
    一文搞懂阿里云服务器部署MySQL并整合Spring Boot
  • 6
    应用MySQL MGR
  • 7
    干货分享 ｜ 揭秘下一代云数据库引擎MyBase for MySQL
  • 8
    Linux操作系统上的mysql安装
  • 9
    mysql中char与varchar的区别分析
  • 10
    【Navicat 连接MySQL时出现错误1251：客户端不支持服务器请求的身份验证协议；请考虑升级MySQL客户端】
  • 1
    中间件MySQL性能瓶颈
    56
  • 2
    mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file
    278
  • 3
    MySQL上亿数据查询优化：实践与技巧
    289
  • 4
    如何理解Mysql的索引及他们的原理--------二叉查找树和平衡二叉树和B树和B+树
    50
  • 5
    MySQL：union all与union区别详解
    89
  • 6
    学习mysql中使用inner join，left join 等
    49
  • 7
    【最佳实践】MySQL数据库迁移到PXC集群
    91
  • 8
    MySQL PXC集群配置IPv6
    49
  • 9
    【实操记录】MySQL主从配置
    140
  • 10
    【实操记录】MySQL二进制安装包部署
    190

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • 灵活弹性的PolarDB Serverless确保数据业务持续在线
  • 云端MySQL的安装及应用
  • 体验PolarDB MySQL多主集群
  • 基于ECS+RDS的在线留言系统
  • PolarDB MySQL HTAP：实时数据分析加速

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    阿里云OSS设置跨域访问