MySQL是怎样存储数据的?

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
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视频链接

西瓜视频:


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
11天前
|
安全 关系型数据库 MySQL
如何将数据从MySQL同步到其他系统
【10月更文挑战第17天】如何将数据从MySQL同步到其他系统
74 0
|
17天前
|
SQL 前端开发 关系型数据库
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
33 0
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
|
22天前
|
关系型数据库 MySQL 数据库
mysql 里创建表并插入数据
【10月更文挑战第5天】
96 1
|
24天前
|
分布式计算 关系型数据库 MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
44 3
|
7天前
|
SQL Java 关系型数据库
java连接mysql查询数据(基础版,无框架)
【10月更文挑战第12天】该示例展示了如何使用Java通过JDBC连接MySQL数据库并查询数据。首先在项目中引入`mysql-connector-java`依赖,然后通过`JdbcUtil`类中的`main`方法实现数据库连接、执行SQL查询及结果处理,最后关闭相关资源。
|
4天前
|
SQL 关系型数据库 MySQL
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
定时任务频繁插入数据导致锁表问题 -> 查询mysql进程
13 1
|
5天前
|
SQL 关系型数据库 MySQL
mysql数据误删后的数据回滚
【11月更文挑战第1天】本文介绍了四种恢复误删数据的方法:1. 使用事务回滚,通过 `pymysql` 库在 Python 中实现;2. 使用备份恢复,通过 `mysqldump` 命令备份和恢复数据;3. 使用二进制日志恢复,通过 `mysqlbinlog` 工具恢复特定位置的事件;4. 使用延迟复制从副本恢复,通过停止和重启从库复制来恢复数据。每种方法都有详细的步骤和示例代码。
|
17天前
|
存储 关系型数据库 MySQL
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取(pydicom 库使用)
PACS系统 中 dicom 文件在mysql 8.0 数据库中的 存储和读取(pydicom 库使用)
17 2
|
17天前
|
存储 关系型数据库 MySQL
面试官:MySQL一次到底插入多少条数据合适啊?
本文探讨了数据库插入操作的基础知识、批量插入的优势与挑战,以及如何确定合适的插入数据量。通过面试对话的形式,详细解析了单条插入与批量插入的区别,磁盘I/O、内存使用、事务大小和锁策略等关键因素。最后,结合MyBatis框架,提供了实际应用中的批量插入策略和优化建议。希望读者不仅能掌握技术细节,还能理解背后的原理,从而更好地优化数据库性能。
|
17天前
|
存储 关系型数据库 MySQL
Key_Value 形式 存储_5级省市城乡划分代码 (mysql 8.0 实例)
本文介绍了如何使用MySQL8.0数据库中的Key_Value形式存储全国统计用区划代码和城乡划分代码(5级),包括导入数据、通过数学函数提取省市区信息,以及查询5级行政区划的详细数据。
26 0

推荐镜像

更多