三高Mysql - Inndb存储引擎和索引介绍(下)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 三高Mysql - Inndb存储引擎和索引介绍(下)

InnoDb 的存储引擎结构



最后我们再回到InnoDb存储引擎了解InnoDb 的存储引擎基本结构。

如果想要详细的了解这部分的结构,建议阅读**《Mysql是怎么样运行》**这本书里面对于整个Mysql内部结构做了非常详细的介绍,对于理解InnoDb的存储结构十分有帮助。

下面为InnoDb存储引擎的数据存储简易结构图,更加详细的结构在视频中并没有展开,另外如果展开讲述的话一篇文章也是远远不够的,所以这里只能是大致了解


网络异常,图片无法展示
|


表空间:数据表在磁盘上的存储空间,默认情况下所有表的数据存在共享表空间,当然为了权限的使用每一个表的数据也可以放在独占的表空间,


网络异常,图片无法展示
|


:段分为叶子节点段和非叶子节点段,叶子节点段叫做B+树段节点,而非叶子节点就是索引页了。


网络异常,图片无法展示
|


:区通常由64个页组成,每一个段里面对应很多区,一个区段大小是1M,一般由连续段数据页组成,但是一般一次申请为申请3-4个。(需要考虑内存的承受能力)


网络异常,图片无法展示
|


数据页:**页是InnoDb的最小数据单位,默认为16kb,**一个数据页是B+树的节点,最关键的是数据页的设计考虑到了SSD和机械硬盘的设计,一个机械硬盘最小的读写单位是512KB,一个SSD最小的读写单位是4b,所以16KB是他们的倍数,可以节省空间。


网络异常,图片无法展示
|


数据行:数据行分为2种类型,包括inf和sup两个数据行,不管一个表是否有数据行,至少会有这两个数据行,同时每一行默认会隐藏三个字段,Trx Id多用于事务的控制。


网络异常,图片无法展示
|


为什么数据页不能太大,也不能太小?

回答:如果数据页太大,那么每次读取数据页如果只是查找几行数据,那么会浪费大量的计算机资源,因为IO的对于数据库系统是需要尽量避免的,如果数据页太小由于磁盘最小的读取单元存在限制,也可能会因为多次读取导致性能极速下降,而数据页太大如果需要的数据仅仅几条又十分浪费IO的性能。

所以mysql在设计数据页大小的时候考虑的固态磁盘和机械磁盘的读取单位的折中。


数据行


为了防止读者误解下面的所有的介绍都是针对InnoDb的存储引擎以及mysql5.7的版本中进行介绍。


数据行格式


提示:这里先提前打一下预防针,其实技术的改进都是细节的改进,了解完之后会发现其实也就那么一回事,但是关键在于魔鬼藏在细节中,所以需要小心区分对待。

数据行的内容比较特殊,由于历史的原因他进行了演变,也为了方便理解,我们需要记住mysql的数据行有两种格式,他们分别由AntelopeBarracuda 两种格式,为了方便理解我们称这两个人为AB吧,在mysql的数据行格式一共有四种类型,但是由于其中REDUNDANTCOMPACT两种格式是新版本中早就不再使用较老的格式,但是在面试中可能被问到所以有必要进行理解:

Antelope:包含REDUNDANTCOMPACT.

Barracuda:包含DYNAMIC(5.0之后以及8.0默认的建表行格式)和COMPRESSED(压缩格式)

如果需要了解当前mysql版本的默认行格式,可以使用SHOW VARIABLES LIKE "InnoDb_default_row_format"的命令进行查看。

下面我们按照从旧到新到顺序来看一下行格式演变。

A大叔的格式:

REDUNDANTREDUNDANT格式英文名称翻译过来叫做“冗余”格式,他是mysql5.0之前的默认行格式,需要注意到是下面的示例图分隔符实际是不存在的,在实际存储到过程中都是按照特定编码进行紧凑存储的。

这样就会带来一个问题,比如我们要找到col1或者找到coln要怎么查?所以最前端的字段偏移列表的作用就是来帮助mysql快速定位到具体要查找到列的,但是我们又需要注意字段偏移列表使用了逆序存储的方式进行处理,我们直接通过一个例子进行解释字段偏移列表的作用:

再次强调字段偏移列表不是固定记录变长列长度的,而是存放的是相邻两个列之间的偏移长度,假设当前有三列varchar数据,顺序存储长度分别为1,2,3,按照字段偏移列表的规则为1,3(3-1=2),6(6-3=3),这几个值字段偏移列表逆序存储同时,真实数据按照16进制表示,所以最终的结果为:06 03 01(注意中间空格为了方面阅读加入,实际是紧凑的排列060301


网络异常,图片无法展示
|


header部分比较好理解,比如表示字段偏移列表的单位以及记录列的数量,以及一个十分重要的元素:下一个数据行的地址信息等。

rowId在之前的笔记中提到过,如果在建表的时候没有指定主键,那么mysql就会使用这个rowId作为隐藏的主键,TxID用于事务控制,然后是roll pointer用于undo log回滚实现MVCC的机制,最后便是col1,col2,col3的列是真实的数据。


如果还是好奇为什么字段偏移列表要逆序存储,其实仔细观察上面的行格式结构可以看出端倪,以roll point为界限,左边是头信息和字段偏移列表,右边是真实数据。官方说法是把记录分为记录头信息和真实数据两部分,而使用逆序存储的方式可以让长度和真实数据列“对称”,指针向左移,一个指针向右移动,效率高一些。


B大叔的格式:


COMPACT格式:这个格式比REDUNDANT精致很多,可以看到上面的字段偏移列表不是很直观,每次都需要进行一次减法才能算出列的真实长度,所以Compact使用了变长字段列表改进,变长字段列表直接存储列的长度并且以逆序的方式存储,并且在此基础上加入了NULL值列表来维护每一列是否为NULL,使用位表的方式标志每一列是否为NULL,0为NULL,1为非NULL,并且同样是逆序存储。

记录头的信息差异并不是特别大,所以这里直接忽略了,重点关注“变长字段列表”和“NULL值列表”的改动,技术的进步总是微小但是十分有效的。

COMPRESSED的优点是对于过大的页会进行压缩存储,但是压缩存储的问题是读取的时候需要解包读取,会更多耗费一定的性能。


网络异常,图片无法展示
|


最后我们可以从下面的图看到基本的行格式的具体特性,这个表来自于mysql5.7的官方文档,地址为:dev.mysql.com/doc/refman/…


行格式 紧凑存储特性 增强的可变长度色谱柱存储 大索引键前缀支持 Compression Support 支持的 table 空间类型 所需文件格式
REDUNDANT No No No No 系统,每 table 文件,常规 Antelope or Barracuda
COMPACT Yes No No No 系统,每 table 文件,常规 Antelope or Barracuda
DYNAMIC Yes Yes Yes No 系统,每 table 文件,常规 Barracuda
COMPRESSED Yes Yes Yes Yes file-per-table, general Barracuda


可变列和不可变列


我们都知道Mysql支持的数据类型是很多的比如varcharcharintblobtext等等。这里我们重点关注变长列的和不变长列的数据类型,变长列指的是指定长度和实际长度不一致的列比如varchar,其中的var单词就是代表variableke(可变),所以称之为可变列,不变长列也就是字符长度固定的列char,我们发现无论是学校学习还是各种网上百科,通常介绍会认为char是固定长度的,varchar是不固定长度的。

真的是这样吗?然而随着时代的发展char其实也发生了变化这里,可以看mysql5.7的文档解释:

参考:dev.mysql.com/doc/refman/…


InnoDb将长度大于或等于 768 字节的固定长度字段编码为可变长度字段在页外存储。例如 CHAR(255)如果字符集的最大字节长度大于 3,则列可能超过 768 个字节,就像utf8mb4


原文:InnoDb encodes fixed-length fields greater than or equal to 768 bytes in length as variable-length fields, which can be stored off-page. For example, a CHAR(255) column can exceed 768 bytes if the maximum byte length of the character set is greater than 3, as it is with utf8mb4.


一个varchar最大长度是多少


在mysql4.1之前,varchar的最大值为255,这大概也是很多数据库管理工具默认给varchar(255)的一个原因。

在5.0以上的 版本中varchar最多可以占用65535个字节,为什么是65535?是因为InnoDb最多给一个字段分配2个字节,也就是说一个varchar最多只有16位,2的16次方-1= 65535(受到二进制补位的影响)。

注意这里说的是字节而不是字符,由于字符串实际上是通过字节进行特殊编码翻译而来,所以对于一些变长编码的存储长度是实时变化的,比如utf8mb4的编码最多占4个字节,套入上面的数据65535/4约等于16383个字符。

所以针对utf8mb4编码的varchar列最大长度为16383?真的是这样么?实际上这个值也是一个参考,虽然理论上确实应该存储这么多数据,但是实际上是肯定长度达不到16383的,至于理由其实可以实际建立一个表尝试,会发现创建失败或者修改字段失败。

这和mysql的底层数据结构有关系,由于变长字段需要记录长度,同时mysql为了记录信息需要用一些额外的记录空间进行存储。


备注:length函数不是记录字符的个数,而是实际占用的长度,由于中文需要3个字符长度存储,所以实际存储的长度为63000/3=21000


网络异常,图片无法展示
|


提示:如果修改报错内容如下

1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs


写在最后


从Mysql的B+树结构和其他可能的数据库数据结构设计,可以发现B+树是多种数据结构兼容和平衡,而Mysql在实践的过程有还是做了改进,理论和实践之间总是有某种差异

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
15天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
13天前
|
存储 SQL 关系型数据库
MySQL存储引擎
本文介绍了数据库优化的多个方面,包括选择合适的存储引擎、字段定义原则、避免使用外键和触发器、大文件存储策略、表拆分及字段冗余处理等。强调了从业务层面进行优化的重要性,如通过活动设计减少外部接口调用,以及在高并发场景下的流量控制与预处理措施。文章还提供了具体的SQL优化技巧和表结构优化建议,旨在提高数据库性能和可维护性。
MySQL存储引擎
|
17天前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理?
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理?
41 1
|
6天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
38 0
|
7天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
34 0
|
19天前
|
监控 关系型数据库 MySQL
mysql8索引优化
综上所述,深入理解和有效实施这些索引优化策略,是解锁MySQL 8.0数据库高性能查询的关键。
27 0
|
22天前
|
SQL 关系型数据库 MySQL
美团面试:mysql 索引失效?怎么解决? (重点知识,建议收藏,读10遍+)
本文详细解析了MySQL索引失效的多种场景及解决方法,包括破坏最左匹配原则、索引覆盖原则、前缀匹配原则、`ORDER BY`排序不当、`OR`关键字使用不当、索引列上有计算或函数、使用`NOT IN`和`NOT EXISTS`不当、列的比对等。通过实例演示和`EXPLAIN`命令分析,帮助读者深入理解索引失效的原因,并提供相应的优化建议。文章还推荐了《尼恩Java面试宝典》等资源,助力面试者提升技术水平,顺利通过面试。
|
26天前
|
存储 SQL 关系型数据库
Mysql学习笔记(二):数据库命令行代码总结
这篇文章是关于MySQL数据库命令行操作的总结,包括登录、退出、查看时间与版本、数据库和数据表的基本操作(如创建、删除、查看)、数据的增删改查等。它还涉及了如何通过SQL语句进行条件查询、模糊查询、范围查询和限制查询,以及如何进行表结构的修改。这些内容对于初学者来说非常实用,是学习MySQL数据库管理的基础。
103 6
|
23天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
56 3
Mysql(4)—数据库索引
|
9天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
45 2