重新学习Mysql数据库3:Mysql存储引擎与数据存储原理

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: 转自:https://draveness.me/mysql-innodb 作为一名开发人员,在日常的工作中会难以避免地接触到数据库,无论是基于文件的 sqlite 还是工程上使用非常广泛的 MySQL、PostgreSQL,但是一直以来也没有对数据库有一个非常清晰并且成体系的认知,所以最近两个月的时间看了几本数据库相关的书籍并且阅读了 MySQL 的官方文档,希望对各位了解数据库的、不了解数据库的有所帮助。

转自:https://draveness.me/mysql-innodb


作为一名开发人员,在日常的工作中会难以避免地接触到数据库,无论是基于文件的 sqlite 还是工程上使用非常广泛的 MySQL、PostgreSQL,但是一直以来也没有对数据库有一个非常清晰并且成体系的认知,所以最近两个月的时间看了几本数据库相关的书籍并且阅读了 MySQL 的官方文档,希望对各位了解数据库的、不了解数据库的有所帮助。


本文中对于数据库的介绍以及研究都是在 MySQL 上进行的,如果涉及到了其他数据库的内容或者实现会在文中单独指出。

数据库的定义

很多开发者在最开始时其实都对数据库有一个比较模糊的认识,觉得数据库就是一堆数据的集合,但是实际却比这复杂的多,数据库领域中有两个词非常容易混淆,也就是数据库实例

  • 数据库:物理操作文件系统或其他形式文件类型的集合;
  • 实例:MySQL 数据库由后台线程以及一个共享内存区组成;

对于数据库和实例的定义都来自于 MySQL 技术内幕:InnoDB 存储引擎 一书,想要了解 InnoDB 存储引擎的读者可以阅读这本书籍。

数据库和实例

在 MySQL 中,实例和数据库往往都是一一对应的,而我们也无法直接操作数据库,而是要通过数据库实例来操作数据库文件,可以理解为数据库实例是数据库为上层提供的一个专门用于操作的接口。

Database - Instance

在 Unix 上,启动一个 MySQL 实例往往会产生两个进程,mysqld 就是真正的数据库服务守护进程,而 mysqld_safe 是一个用于检查和设置 mysqld 启动的控制程序,它负责监控 MySQL 进程的执行,当 mysqld 发生错误时,mysqld_safe 会对其状态进行检查并在合适的条件下重启。

MySQL 的架构

MySQL 从第一个版本发布到现在已经有了 20 多年的历史,在这么多年的发展和演变中,整个应用的体系结构变得越来越复杂:

Logical-View-of-MySQL-Architecture

最上层用于连接、线程处理的部分并不是 MySQL 『发明』的,很多服务都有类似的组成部分;第二层中包含了大多数 MySQL 的核心服务,包括了对 SQL 的解析、分析、优化和缓存等功能,存储过程、触发器和视图都是在这里实现的;而第三层就是 MySQL 中真正负责数据的存储和提取的存储引擎,例如:InnoDBMyISAM 等,文中对存储引擎的介绍都是对 InnoDB 实现的分析。

数据的存储

在整个数据库体系结构中,我们可以使用不同的存储引擎来存储数据,而绝大多数存储引擎都以二进制的形式存储数据;这一节会介绍 InnoDB 中对数据是如何存储的。

在 InnoDB 存储引擎中,所有的数据都被逻辑地存放在表空间中,表空间(tablespace)是存储引擎中最高的存储逻辑单位,在表空间的下面又包括段(segment)、区(extent)、页(page):

Tablespace-segment-extent-page-row

同一个数据库实例的所有表空间都有相同的页大小;默认情况下,表空间中的页大小都为 16KB,当然也可以通过改变 innodb_page_size 选项对默认大小进行修改,需要注意的是不同的页大小最终也会导致区大小的不同:

Relation Between Page Size - Extent Size

从图中可以看出,在 InnoDB 存储引擎中,一个区的大小最小为 1MB,页的数量最少为 64 个。

如何存储表

MySQL 使用 InnoDB 存储表时,会将表的定义数据索引等信息分开存储,其中前者存储在 .frm 文件中,后者存储在 .ibd 文件中,这一节就会对这两种不同的文件分别进行介绍。

frm-and-ibd-file

.frm 文件

无论在 MySQL 中选择了哪个存储引擎,所有的 MySQL 表都会在硬盘上创建一个 .frm 文件用来描述表的格式或者说定义;.frm 文件的格式在不同的平台上都是相同的。

CREATE TABLE test_frm(
    column1 CHAR(5),
    column2 INTEGER
);

当我们使用上面的代码创建表时,会在磁盘上的 datadir 文件夹中生成一个 test_frm.frm 的文件,这个文件中就包含了表结构相关的信息:

frm-file-hex

MySQL 官方文档中的 11.1 MySQL .frm File Format 一文对于 .frm文件格式中的二进制的内容有着非常详细的表述,在这里就不展开介绍了。

.ibd 文件

InnoDB 中用于存储数据的文件总共有两个部分,一是系统表空间文件,包括 ibdata1ibdata2 等文件,其中存储了 InnoDB 系统信息和用户数据库表数据和索引,是所有表公用的。

当打开 innodb_file_per_table 选项时,.ibd 文件就是每一个表独有的表空间,文件存储了当前表的数据和相关的索引数据。

如何存储记录

与现有的大多数存储引擎一样,InnoDB 使用页作为磁盘管理的最小单位;数据在 InnoDB 存储引擎中都是按行存储的,每个 16KB 大小的页中可以存放 2-200 行的记录。

当 InnoDB 存储数据时,它可以使用不同的行格式进行存储;MySQL 5.7 版本支持以下格式的行存储方式:

Antelope-Barracuda-Row-Format

Antelope 是 InnoDB 最开始支持的文件格式,它包含两种行格式 Compact 和 Redundant,它最开始并没有名字;Antelope 的名字是在新的文件格式 Barracuda 出现后才起的,Barracuda 的出现引入了两种新的行格式 Compressed 和 Dynamic;InnoDB 对于文件格式都会向前兼容,而官方文档中也对之后会出现的新文件格式预先定义好了名字:Cheetah、Dragon、Elk 等等。

两种行记录格式 Compact 和 Redundant 在磁盘上按照以下方式存储:

COMPACT-And-REDUNDANT-Row-Format

Compact 和 Redundant 格式最大的不同就是记录格式的第一个部分;在 Compact 中,行记录的第一部分倒序存放了一行数据中列的长度(Length),而 Redundant 中存的是每一列的偏移量(Offset),从总体上上看,Compact 行记录格式相比 Redundant 格式能够减少 20% 的存储空间。

行溢出数据

当 InnoDB 使用 Compact 或者 Redundant 格式存储极长的 VARCHAR 或者 BLOB 这类大对象时,我们并不会直接将所有的内容都存放在数据页节点中,而是将行数据中的前 768 个字节存储在数据页中,后面会通过偏移量指向溢出页。

Row-Overflo

但是当我们使用新的行记录格式 Compressed 或者 Dynamic 时都只会在行记录中保存 20 个字节的指针,实际的数据都会存放在溢出页面中。

Row-Overflow-in-Barracuda

当然在实际存储中,可能会对不同长度的 TEXT 和 BLOB 列进行优化,不过这就不是本文关注的重点了。

想要了解更多与 InnoDB 存储引擎中记录的数据格式的相关信息,可以阅读 InnoDB Record Structure

数据页结构

页是 InnoDB 存储引擎管理数据的最小磁盘单位,而 B-Tree 节点就是实际存放表中数据的页面,我们在这里将要介绍页是如何组织和存储记录的;首先,一个 InnoDB 页有以下七个部分:

InnoDB-B-Tree-Node

每一个页中包含了两对 header/trailer:内部的 Page Header/Page Directory 关心的是页的状态信息,而 Fil Header/Fil Trailer 关心的是记录页的头信息。

在页的头部和尾部之间就是用户记录和空闲空间了,每一个数据页中都包含 Infimum 和 Supremum 这两个虚拟的记录(可以理解为占位符),Infimum 记录是比该页中任何主键值都要小的值,Supremum 是该页中的最大值:

Infimum-Rows-Supremum

User Records 就是整个页面中真正用于存放行记录的部分,而 Free Space 就是空余空间了,它是一个链表的数据结构,为了保证插入和删除的效率,整个页面并不会按照主键顺序对所有记录进行排序,它会自动从左侧向右寻找空白节点进行插入,行记录在物理存储上并不是按照顺序的,它们之间的顺序是由 next_record 这一指针控制的。

B+ 树在查找对应的记录时,并不会直接从树中找出对应的行记录,它只能获取记录所在的页,将整个页加载到内存中,再通过 Page Directory 中存储的稀疏索引和 n_ownednext_record 属性取出对应的记录,不过因为这一操作是在内存中进行的,所以通常会忽略这部分查找的耗时。

InnoDB 存储引擎中对数据的存储是一个非常复杂的话题,这一节中也只是对表、行记录以及页面的存储进行一定的分析和介绍,虽然作者相信这部分知识对于大部分开发者已经足够了,但是想要真正消化这部分内容还需要很多的努力和实践。下文是详细分析。



innodb数据存储详细分析

本文主要介绍InnoDB存储引擎的逻辑存储结构

逻辑存储结构

table-space

Tablespace

  1. Tablespace是InnoDB存储引擎逻辑存储结构的最高层所有数据都存放在Tablespace中
  2. 分类
    • System Tablespace
    • Separate Tablespace
    • General Tablespace

System Tablespace

  1. System Tablespace即我们常见的共享表空间,变量为innodb_data_file_path,一般为ibdata1文件
  2. 里面存放着undo logschange bufferdoublewrite buffer等信息(后续将详细介绍),在没有开启file-per-table的情况下,还会包含所有表的索引和数据信息
  3. 没有开启file-per-table时存在的问题
    • 所有的表和索引都会在System Tablespace中,占用空间会越来越大
    • 碎片越来越多(如truncate table时,占用的磁盘空间依旧保留在System Tablespace
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql>  SHOW VARIABLES LIKE 'innodb_data_file_path';
+-----------------------+------------------------+
| Variable_name         | Value                  |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.01 sec)

mysql>  SHOW VARIABLES LIKE '%datadir%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql> system sudo ls -lh /var/lib/mysql/ibdata1
[sudo] password for zhongmingmao:
-rw-r----- 1 mysql mysql 76M May  6 20:00 /var/lib/mysql/ibdata1

Separate Tablespace

  1. MySQL参考手册中并没有Separate Tablespace这个术语,这里只为了行文方便,表示在开启file-per-table的情况下,每个表有自己独立的表空间,变量为innodb_file_per_table
  2. 里面存放在每个表的索引和数据信息,后缀一般为.ibd
  3. 默认初始大小为96KB
  4. 好处
    • 避免System Tablespace越来越大
    • 减少碎片(truncate table,操作系统会自动回收空间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t              |
+----------------+
1 row in set (0.00 sec)

mysql>  SHOW VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

mysql>  SHOW VARIABLES LIKE '%datadir%';                                                                                               +---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

mysql> system sudo ls -lh /var/lib/mysql/test
total 112K
-rw-r----- 1 mysql mysql   61 Apr 28 10:18 db.opt
-rw-r----- 1 mysql mysql 8.4K May  7 17:03 t.frm
-rw-r----- 1 mysql mysql  96K May  7 17:03 t.ibd

General Tablespace

  1. General TablespaceMySQL 5.7.6引入的新特性,具体内容请参照下面链接
    15.7.9 InnoDB General Tablespaces

Segment

segment

  1. Segment分为三种
    1. Leaf node segment数据段,B+Tree的叶子节点
    2. Non-Leaf node segment索引段,B+Tree的非叶子节点
    3. Rollback segment:回滚段,存放undo log,默认是位于System Tablespace
  2. InnoDB中的B+Tree索引,由Leaf node segmentNon-Leaf node segment组成
  3. 一个Segment由多个Extent和Page组成

Extent

  1. Extent是由连续页(默认页大小为16KB)组成,在默认页大小时,为64个连续页,大小为64*16KB=1MB
    • 不同页大小:4KB*256 or 8KB*128 or 16KB*64 or 32KB*64 or 64KB*64
  2. 为了保证页的连续性,InnoDB可以一次性从磁盘申请4个Extent
  3. 为了节省磁盘空间,如表的数据量很小(Leaf node segmentNon-Leaf node segment都很小)或Rollback segment,Segment一开始不会直接申请Extent,而是先用32个碎片页(用于叶子节点)来存放数据,用完之后才继续对Extent(1MB)的申请

Page

  1. Page是InnoDB磁盘管理的最小单位,变量为innodb_page_size
1
2
3
4
5
6
7
mysql>  SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.17 sec)

Row

  1. InnoDB存储引擎的数据是按行进行存放的
  2. 行记录格式Row_FORMAT将在后续详细介绍

接下来是Page数据页详解,这是最重要的一部分。

本文主要介绍InnoDB存储引擎的数据页结构

数据页结构

page-structure

File Header

参考链接:Fil Header

  1. 总共38 Bytes,记录页的头信息
名称 大小(Bytes) 描述
FIL_PAGE_SPACE 4 该页的checksum
FIL_PAGE_OFFSET 4 该页在表空间中的页偏移量
FIL_PAGE_PREV 4 该页的上一个页
FIL_PAGE_NEXT 4 该页的下一个页
FIL_PAGE_LSN 8 该页最后被修改的LSN
FIL_PAGE_TYPE 2 该页的类型,0x45BF为数据页
FIL_PAGE_FILE_FLUSH_LSN 8 独立表空间中为0
FIL_PAGE_ARCH_LOG_NO 4 该页属于哪一个表空间

Page Header

参考链接:Page Header

  1. 总共56 Bytes,记录页的状态信息
名称 大小(Bytes) 描述
PAGE_N_DIR_SLOTS 2 Page DirectorySlot的数量,初始值为2
PAGE_HEAP_TOP 2 堆中第一个记录的指针
PAGE_N_HEAP 2 堆中的记录数,初始值为2
PAGE_FREE 2 指向可重用空间的首指针
PAGE_GARBAGE 2 已标记为删除(deleted_flag)的记录的字节数
PAGE_LAST_INSERT 2 最后插入记录的位置
PAGE_DIRECTION 2 最后插入的方向,PAGE_LEFT(0x01)PAGE_RIGHT(0x02)PAGE_NO_DIRECTION(0x05)
PAGE_N_DIRECTION 2 一个方向上连续插入记录的数量
PAGE_N_RECS 2 该页中记录(User Record)的数量
PAGE_MAX_TRX_ID 8 修改该页的最大事务ID(仅在辅助索引中定义)
PAGE_LEVEL 2 该页在索引树中位置,0000代表叶子节点
PAGE_INDEX_ID 8 索引ID,表示该页属于哪个索引
PAGE_BTR_SEG_LEAF 10 B+Tree叶子节点所在Leaf Node Segment的Segment Header(无关紧要)
PAGE_BTR_SEG_TOP 10 B+Tree非叶子节点所在Non-Leaf Node Segment的Segment Header(无关紧要)

Infimum + Supremum Records

参考链接:The Infimum and Supremum Records

  1. 每个数据页中都有两个虚拟的行记录,用来限定记录(User Record)的边界(Infimum为下界Supremum为上界
  2. InfimumSupremum页被创建是自动创建,不会被删除
  3. CompactRedundant行记录格式下,InfimumSupremum占用的字节数是不一样

infimum-supremum

User Records

参考链接:User Records

  1. 存储实际插入的行记录
  2. Page HeaderPAGE_HEAP_TOPPAGE_N_HEAPHEAP,实际上指的是Unordered User Record List
    • InnoDB不想每次都依据B+Tree键的顺序插入新行,因为这可能需要移动大量的数据
    • 因此InnoDB插入新行时,通常是插入到当前行的后面(Free Space的顶部)或者是已删除行留下来的空间
  3. 为了保证访问B+Tree记录的顺序性,在每个记录中都有一个指向下一条记录的指针,以此构成了一条单向有序链表

Free Space

  1. 空闲空间,数据结构是链表,在一个记录被删除后,该空间会被加入到空闲链表中

Page Directory

参考链接:Page Directory

  1. 存放着行记录User Record)的相对位置(不是偏移量)
  2. 这里的行记录指针称SlotDirectory Slot,每个Slot占用2Byte
  3. 并不是每一个行记录都有一个Slot,一个Slot中可能包含多条行记录,通过行记录中n_owned字段标识
  4. Infimum的n_owned总是1Supremum的n_owned为[1,8]User Record的n_owned为[4,8]
  5. Slot是按照索引键值的顺序进行逆序存放(Infimum是下界,Supremum是上界),可以利用二分查找快速地定位一个粗略的结果,然后再通过next_record进行精确查找
  6. B+Tree索引本身并不能直接找到具体的一行记录,只能找到该行记录所在的页
    • 数据库把页载入到内存中,然后通过Page Directory再进行二分查找
    • 二分查找时间复杂度很低,又在内存中进行查找,这部分的时间基本开销可以忽略

微信公众号【黄小斜】大厂程序员,互联网行业新知,终身学习践行者。关注后回复「Java」、「Python」、「C++」、「大数据」、「机器学习」、「算法」、「AI」、「Android」、「前端」、「iOS」、「考研」、「BAT」、「校招」、「笔试」、「面试」、「面经」、「计算机基础」、「LeetCode」 等关键字可以获取对应的免费学习资料。 


                     wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
28天前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
140 14
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
|
8天前
|
存储 移动开发 数据库
HTML5 Web IndexedDB 数据库常用数据存储类型
IndexedDB 支持多种数据存储类型,满足复杂数据结构的存储需求。它包括基本数据类型(如 Number、String、Boolean、Date)、对象(简单和嵌套对象)、数组、Blob(用于二进制数据如图像和视频)、ArrayBuffer 和 Typed Arrays(处理二进制数据)、结构化克隆(支持 Map 和 Set 等复杂对象),以及 JSON 数据。尽管不直接支持非序列化数据(如函数和 DOM 节点),但可以通过转换实现存储。开发者应根据具体需求选择合适的数据类型,以优化性能和使用体验。
|
2天前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
|
2天前
|
SQL 关系型数据库 MySQL
sql注入原理与实战(三)数据库操作
sql注入原理与实战(三)数据库操作
sql注入原理与实战(三)数据库操作
|
28天前
|
存储 缓存 关系型数据库
MySQL高级篇——存储引擎和索引
MyISAM:不支持外键和事务,表锁不适合高并发,只缓存索引,内存要求低,查询快MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务、行级锁、外键,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。5.5之前默认的存储引擎优势是访问的速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高表名.frm 存储表结构;表名.MYD 存储数据 (MYData);
MySQL高级篇——存储引擎和索引
|
2天前
|
SQL 存储 Java
sql注入原理与实战(二)数据库原理
sql注入原理与实战(二)数据库原理
|
24天前
|
存储 关系型数据库 分布式数据库
揭秘PolarDB:中国云原生数据库的超级英雄,如何颠覆传统数据存储?
在数字化时代,数据成为企业的核心资产,而云原生数据库则是推动企业转型的关键。PolarDB凭借其先进的存储计算分离架构,在性能、可靠性和易用性方面脱颖而出,成为国内领先的选择。它支持多种数据库引擎,提供多副本存储机制,并采用按量付费模式,有效降低管理和成本压力,助力企业实现高效、可靠的数字化转型。
34 1
|
3天前
|
存储 NoSQL Java
使用 Java 了解和学习 NoSQL 数据库:三个主要优势
使用 Java 了解和学习 NoSQL 数据库:三个主要优势
8 0
|
2月前
|
消息中间件 Kafka 数据库
深入理解Kafka的数据一致性原理及其与传统数据库的对比
【8月更文挑战第24天】在分布式系统中,确保数据一致性至关重要。传统数据库利用ACID原则保障事务完整性;相比之下,Kafka作为高性能消息队列,采用副本机制与日志结构确保数据一致性。通过同步所有副本上的数据、维护消息顺序以及支持生产者的幂等性操作,Kafka在不牺牲性能的前提下实现了高可用性和数据可靠性。这些特性使Kafka成为处理大规模数据流的理想工具。
53 6
|
2月前
|
SQL 关系型数据库 MySQL
说一下MySQL主从复制的原理?
【8月更文挑战第24天】说一下MySQL主从复制的原理?
52 0

热门文章

最新文章