MySQL是怎样存储数据的?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: MySQL是怎样存储数据的?

MySQL是怎样存储数据的?

在现代数据库系统中,MySQL的InnoDB存储引擎通过精巧的数据结构设计和高效的索引算法,为海量数据提供了稳定、快速且持久化的存储服务。

本文将自顶向下详细解读MySQL如何组织和管理数据,从宏观的表空间概念出发,层层剥茧至微观的记录存储,并阐述InnoDB所采用的B+树索引结构以及基于此结构查找数据的流程。

(文末附视频链接)

表空间的组成

在MySQL中记录是如何进行存储的呢?

MySQL存储数据的方式大体上取决于所使用的存储引擎(这里主要以最常用的InnoDB存储引擎为例来说明)

MySQL会将数据存储在data目录中 show variables like 'datadir'

其中包含日志与数据文件,日志包括:redo log、bin log、慢SQL日志、错误日志等,而数据文件包括系统的和我们创建的

在data目录中以库为单位生成目录,库的目录中存储表相关的文件

在Innodb中,表相关的文件包括表结构文件和表空间文件

表结构文件:声明表结构信息 表名.frm

表空间文件:存储数据(记录)表名.idb

如果使用的是myisam存储引擎,存储数据的文件还会分为数据文件和索引文件

(Innodb中数据即索引,索引即数据,因此只有一个文件)

表空间文件又分为独立表空间和共享表空间,独立表空间用于存储用户数据,共享表空间则是服务于元数据(管理用户数据)ibdata1

(这里的用户指的是使用MySQL的用户)

自顶向下查看MySQL的存储情况:表空间->段(逻辑)->区->页->记录 非/叶子节点段构建索引B+树

image.png

为了方便管理,表空间逻辑上使用段进行管理,段由区、零散页组成

独立表空间中的段用于存储索引数据(用户数据),索引数据时分为叶子节点段和非叶子节点段

系统表空间的段用于存储元数据如:回滚段(存储undo log)

Innodb存储数据使用改进的B+树,叶子节点中的记录存储用户数据,非叶子节点中的记录存储下层节点的信息

在物理上表空间由多个区组成,区为在物理上连续的64个页,而页是内存、磁盘交互的基本单位 默认为16KB

使用区的好处是页连续,这样在进行范围扫描时IO是顺序的,如果用零散页范围扫描时可能出现随机IO

但是一个区占用的空间太大,连续的64个页,如果存储小数据量的表会造成空间浪费

因此申请空间时会先使用零散页,当数据量逐步上升时申请空间以区为单位

页内存储着记录,记录由额外信息与数据组成,额外信息可能记录一些数据如:事务ID、回滚指针、字段额外长度等

聚簇索引的存储

在Innodb中索引即数据,在创建表时会默认生成聚簇(主键)索引,如果创建表时未设置主键,则会使用记录的隐藏列作为主键

聚簇索引的特点是以主键排序并拥有完整的记录

在叶子节点中记录以主键升序维护成单向链表,非叶子节点中记录则是以下层目录页中最小主键升序维护成单向链表

为了方便范围查找同级节点之间会维护成双向链表

image.png

当查询时会从根节点(非叶子节点)一步一步查询到叶子节点

页中的记录维护成单向链表,在一个页中搜索记录的时间复杂度为O(n),当数据量较大时只能进行遍历

由于页内记录是有序的,为了加快查找速度将页内的记录分为多个组,将每个组中的最大记录维护成一个升序列表

图中不同颜色的记录为不同的组,每个组的最大值维护成升序列表(infimum,2,4,6,supermum)

image.png

页内默认有最小的记录infimum和最大的记录supermum,其中infimum记录单独为一组,supermum可以和其他记录为一组

(它们的加入是为了方便加间隙锁,防止幻读)

这样在进行页内查找时可以使用二分法进行查找,将时间复杂度降低为O(log n)

比如查询条件为 id >= 7

image.png

  1. 在根节点上使用二分法找到第一个小于等于目标值的记录(假设这里升序列表为1、17、33,7在1、17之间,则会去查找1对应的页)
  2. 在第二层上使用二分法找到第一个小于等于目标值的记录(假设这里升序列表为1、5、9、13,7在5、9之间,则会去查找5对应的页)
  3. 在第三层(叶子节点层)上使用二分法找到第一个小于等于目标值的记录(假设这里升序列表为5、6、7、8,则就定位到7的记录),然后通过记录中维护的单向链表,页与页维护的双向链表进行范围扫描

二级索引的存储

为表中某个列建立索引时,可以称这个索引为二级索引

聚簇索引与二级索引较大的区别为:聚簇索引存储完整的记录,而二级索引上的记录只存储索引列、主键

比如为表中列包含:id 主键、age、student_name、info

image.png

聚簇索引中的记录则会以id升序并存储所有列的信息

建立age、student_name的联合索引(二级索引)

二级索引中记录则只存储age、student_name、id的信息,并以age、student_name、id的顺序升序排序

当age相等时,根据student_name升序排序;当student_name相等时,再根据id升序排序

如果使用二级索引时要获取完整数据还需要回表查询聚簇索引,比如使用二级索引时还要获取info列则需要回表查询聚簇索引

总结

本篇文章自顶向下描述MySQL的Innodb如何进行存储数据

在MySQL的data目录中会存储日志、系统库、用户库等数据,其中库以目录为单位,表文件存储在对应库中

Innodb下表文件通常包括表结构文件(.frm存储表结构) 和表空间文件(.idb存储记录-用户数据)

表空间分为共享表空间和独立表空间,共享表空间服务元数据存储回滚段等,独立表空间服务用户数据存储非叶子节点段、叶子节点段等

段是逻辑上的概念方便于管理不同功能的空间,段由若干个区和零散页组成

区由连续64个页组成,连续页便于范围扫描顺序IO,页是内存与磁盘IO交互的基本单位,默认是16KB用于存储记录

非叶子节点中存储的记录通常用于“路由”,真正的数据存储在叶子节点中的记录

页内记录按照索引列升序排序维护成单向链表,同层级的页与页之间维护成双向链表方便范围查询

页中记录会分为多个组,记录每个组中最大记录维护成升序列表,当查找时在升序列表上使用二分法进行查找

聚簇索引以主键值升序排序并存储完整数据,如果未规定主键则在记录的隐藏列自动记录

二级索引则按照索引列进行排序,并且只存储索引列和主键的数据,如果使用二级索引时要获取完整数据还需要回表查询聚簇索引

最后(不要白嫖,一键三连求求拉~)

本篇文章被收入专栏 MySQL进阶之路,感兴趣的同学可以持续关注喔

本篇文章笔记以及案例被收入 gitee-StudyJavagithub-StudyJava 感兴趣的同学可以stat下持续关注喔~

有什么问题可以在评论区交流,如果觉得菜菜写的不错,可以点赞、关注、收藏支持一下~

关注菜菜,分享更多干货,公众号:菜菜的后端私房菜

bilibili视频链接

西瓜视频:


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7天前
|
canal 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在进行整库同步MySQL数据到StarRocks时,遇到全量数据可以同步,但增量数据无法同步,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
3天前
|
SQL 缓存 关系型数据库
|
2天前
|
存储 关系型数据库 MySQL
关系型数据库mysql数据文件存储
【6月更文挑战第15天】
9 4
|
2天前
|
canal 关系型数据库 MySQL
蓝易云 - 详解canal同步MySQL增量数据到ES
以上就是使用Canal同步MySQL增量数据到Elasticsearch的基本步骤。在实际操作中,可能还需要根据具体的业务需求和环境进行一些额外的配置和优化。
19 2
|
6天前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版操作报错合集之同步MySQL数据并EP(复杂事件处理)时,编译报错,如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
6天前
|
存储 自然语言处理 关系型数据库
✅生产问题之Emoji表情如何操作存储,MySQL是否支持
MySQL支持存储Emoji表情,需使用UTF8MB4编码。UTF8MB3,MySQL早期的UTF-8实现,不支持部分Unicode字符包括Emoji,已被弃用。推荐使用UTF8MB4,它支持全部Unicode字符。转换时,现有UTF8MB3表需转换为UTF8MB4,列和表都需设置相应字符集。
|
7天前
|
JSON 关系型数据库 MySQL
实时计算 Flink版产品使用问题之在使用CDAS语法同步MySQL数据到Hologres时,如果开启了字段类型宽容模式,MySQL中的JSON类型会被转换为什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
7天前
|
关系型数据库 MySQL Java
实时计算 Flink版产品使用问题之如何提高Flink从MySQL读取数据的速度并减少延迟
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
7天前
|
关系型数据库 MySQL 数据处理
实时计算 Flink版产品使用问题之第一次启动全库同步了, 然后增删改的mysql数据没有及时同步,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
7天前
|
关系型数据库 MySQL Serverless
实时计算 Flink版产品使用问题之使用cdas语法同步mysql数据到sr serverless是否支持动态加表
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。