转自:https://draveness.me/mysql-innodb
作为一名开发人员,在日常的工作中会难以避免地接触到数据库,无论是基于文件的 sqlite 还是工程上使用非常广泛的 MySQL、PostgreSQL,但是一直以来也没有对数据库有一个非常清晰并且成体系的认知,所以最近两个月的时间看了几本数据库相关的书籍并且阅读了 MySQL 的官方文档,希望对各位了解数据库的、不了解数据库的有所帮助。
本文中对于数据库的介绍以及研究都是在 MySQL 上进行的,如果涉及到了其他数据库的内容或者实现会在文中单独指出。
数据库的定义
很多开发者在最开始时其实都对数据库有一个比较模糊的认识,觉得数据库就是一堆数据的集合,但是实际却比这复杂的多,数据库领域中有两个词非常容易混淆,也就是数据库和实例:
- 数据库:物理操作文件系统或其他形式文件类型的集合;
- 实例:MySQL 数据库由后台线程以及一个共享内存区组成;
对于数据库和实例的定义都来自于 MySQL 技术内幕:InnoDB 存储引擎 一书,想要了解 InnoDB 存储引擎的读者可以阅读这本书籍。
数据库和实例
在 MySQL 中,实例和数据库往往都是一一对应的,而我们也无法直接操作数据库,而是要通过数据库实例来操作数据库文件,可以理解为数据库实例是数据库为上层提供的一个专门用于操作的接口。
在 Unix 上,启动一个 MySQL 实例往往会产生两个进程,mysqld
就是真正的数据库服务守护进程,而 mysqld_safe
是一个用于检查和设置 mysqld
启动的控制程序,它负责监控 MySQL 进程的执行,当 mysqld
发生错误时,mysqld_safe
会对其状态进行检查并在合适的条件下重启。
MySQL 的架构
MySQL 从第一个版本发布到现在已经有了 20 多年的历史,在这么多年的发展和演变中,整个应用的体系结构变得越来越复杂:
最上层用于连接、线程处理的部分并不是 MySQL 『发明』的,很多服务都有类似的组成部分;第二层中包含了大多数 MySQL 的核心服务,包括了对 SQL 的解析、分析、优化和缓存等功能,存储过程、触发器和视图都是在这里实现的;而第三层就是 MySQL 中真正负责数据的存储和提取的存储引擎,例如:InnoDB、MyISAM 等,文中对存储引擎的介绍都是对 InnoDB 实现的分析。
数据的存储
在整个数据库体系结构中,我们可以使用不同的存储引擎来存储数据,而绝大多数存储引擎都以二进制的形式存储数据;这一节会介绍 InnoDB 中对数据是如何存储的。
在 InnoDB 存储引擎中,所有的数据都被逻辑地存放在表空间中,表空间(tablespace)是存储引擎中最高的存储逻辑单位,在表空间的下面又包括段(segment)、区(extent)、页(page):
同一个数据库实例的所有表空间都有相同的页大小;默认情况下,表空间中的页大小都为 16KB,当然也可以通过改变 innodb_page_size
选项对默认大小进行修改,需要注意的是不同的页大小最终也会导致区大小的不同:
从图中可以看出,在 InnoDB 存储引擎中,一个区的大小最小为 1MB,页的数量最少为 64 个。
如何存储表
MySQL 使用 InnoDB 存储表时,会将表的定义和数据索引等信息分开存储,其中前者存储在 .frm
文件中,后者存储在 .ibd
文件中,这一节就会对这两种不同的文件分别进行介绍。
.frm 文件
无论在 MySQL 中选择了哪个存储引擎,所有的 MySQL 表都会在硬盘上创建一个 .frm
文件用来描述表的格式或者说定义;.frm
文件的格式在不同的平台上都是相同的。
CREATE TABLE test_frm(
column1 CHAR(5),
column2 INTEGER
);
当我们使用上面的代码创建表时,会在磁盘上的 datadir
文件夹中生成一个 test_frm.frm
的文件,这个文件中就包含了表结构相关的信息:
MySQL 官方文档中的 11.1 MySQL .frm File Format 一文对于
.frm
文件格式中的二进制的内容有着非常详细的表述,在这里就不展开介绍了。
.ibd 文件
InnoDB 中用于存储数据的文件总共有两个部分,一是系统表空间文件,包括 ibdata1
、ibdata2
等文件,其中存储了 InnoDB 系统信息和用户数据库表数据和索引,是所有表公用的。
当打开 innodb_file_per_table
选项时,.ibd
文件就是每一个表独有的表空间,文件存储了当前表的数据和相关的索引数据。
如何存储记录
与现有的大多数存储引擎一样,InnoDB 使用页作为磁盘管理的最小单位;数据在 InnoDB 存储引擎中都是按行存储的,每个 16KB 大小的页中可以存放 2-200 行的记录。
当 InnoDB 存储数据时,它可以使用不同的行格式进行存储;MySQL 5.7 版本支持以下格式的行存储方式:
Antelope 是 InnoDB 最开始支持的文件格式,它包含两种行格式 Compact 和 Redundant,它最开始并没有名字;Antelope 的名字是在新的文件格式 Barracuda 出现后才起的,Barracuda 的出现引入了两种新的行格式 Compressed 和 Dynamic;InnoDB 对于文件格式都会向前兼容,而官方文档中也对之后会出现的新文件格式预先定义好了名字:Cheetah、Dragon、Elk 等等。
两种行记录格式 Compact 和 Redundant 在磁盘上按照以下方式存储:
Compact 和 Redundant 格式最大的不同就是记录格式的第一个部分;在 Compact 中,行记录的第一部分倒序存放了一行数据中列的长度(Length),而 Redundant 中存的是每一列的偏移量(Offset),从总体上上看,Compact 行记录格式相比 Redundant 格式能够减少 20% 的存储空间。
行溢出数据
当 InnoDB 使用 Compact 或者 Redundant 格式存储极长的 VARCHAR 或者 BLOB 这类大对象时,我们并不会直接将所有的内容都存放在数据页节点中,而是将行数据中的前 768 个字节存储在数据页中,后面会通过偏移量指向溢出页。
但是当我们使用新的行记录格式 Compressed 或者 Dynamic 时都只会在行记录中保存 20 个字节的指针,实际的数据都会存放在溢出页面中。
当然在实际存储中,可能会对不同长度的 TEXT 和 BLOB 列进行优化,不过这就不是本文关注的重点了。
想要了解更多与 InnoDB 存储引擎中记录的数据格式的相关信息,可以阅读 InnoDB Record Structure
数据页结构
页是 InnoDB 存储引擎管理数据的最小磁盘单位,而 B-Tree 节点就是实际存放表中数据的页面,我们在这里将要介绍页是如何组织和存储记录的;首先,一个 InnoDB 页有以下七个部分:
每一个页中包含了两对 header/trailer:内部的 Page Header/Page Directory 关心的是页的状态信息,而 Fil Header/Fil Trailer 关心的是记录页的头信息。
在页的头部和尾部之间就是用户记录和空闲空间了,每一个数据页中都包含 Infimum 和 Supremum 这两个虚拟的记录(可以理解为占位符),Infimum 记录是比该页中任何主键值都要小的值,Supremum 是该页中的最大值:
User Records 就是整个页面中真正用于存放行记录的部分,而 Free Space 就是空余空间了,它是一个链表的数据结构,为了保证插入和删除的效率,整个页面并不会按照主键顺序对所有记录进行排序,它会自动从左侧向右寻找空白节点进行插入,行记录在物理存储上并不是按照顺序的,它们之间的顺序是由 next_record
这一指针控制的。
B+ 树在查找对应的记录时,并不会直接从树中找出对应的行记录,它只能获取记录所在的页,将整个页加载到内存中,再通过 Page Directory 中存储的稀疏索引和 n_owned
、next_record
属性取出对应的记录,不过因为这一操作是在内存中进行的,所以通常会忽略这部分查找的耗时。
InnoDB 存储引擎中对数据的存储是一个非常复杂的话题,这一节中也只是对表、行记录以及页面的存储进行一定的分析和介绍,虽然作者相信这部分知识对于大部分开发者已经足够了,但是想要真正消化这部分内容还需要很多的努力和实践。下文是详细分析。
innodb数据存储详细分析
本文主要介绍InnoDB
存储引擎的逻辑存储结构
逻辑存储结构
Tablespace
- Tablespace是InnoDB存储引擎逻辑存储结构的
最高层
,所有数据
都存放在Tablespace中 - 分类
System Tablespace
Separate Tablespace
General Tablespace
System Tablespace
-
System Tablespace
即我们常见的共享表空间
,变量为innodb_data_file_path
,一般为ibdata1
文件 - 里面存放着
undo logs
,change buffer
,doublewrite buffer
等信息(后续将详细介绍),在没有开启file-per-table
的情况下,还会包含所有表的索引和数据
信息 - 没有开启
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
- MySQL参考手册中并没有
Separate Tablespace
这个术语,这里只为了行文方便,表示在开启file-per-table
的情况下,每个表有自己独立的表空间
,变量为innodb_file_per_table
- 里面存放在
每个表的索引和数据信息
,后缀一般为.ibd
- 默认初始大小为
96KB
- 好处
- 避免
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
-
General Tablespace
是MySQL 5.7.6
引入的新特性,具体内容请参照下面链接
15.7.9 InnoDB General Tablespaces
Segment
- Segment分为三种
-
Leaf node segment
:数据段
,B+Tree的叶子节点 -
Non-Leaf node segment
:索引段
,B+Tree的非叶子节点 -
Rollback segment
:回滚段,存放undo log
,默认是位于System Tablespace
-
- InnoDB中的
B+Tree索引
,由Leaf node segment
和Non-Leaf node segment
组成 - 一个Segment由
多个Extent和Page
组成
Extent
-
Extent
是由连续页(默认页大小为16KB
)组成,在默认页大小
时,为64个连续页
,大小为64*16KB=1MB
- 不同页大小:
4KB*256
or8KB*128
or16KB*64
or32KB*64
or64KB*64
- 不同页大小:
- 为了保证
页的连续性
,InnoDB可以一次性从磁盘申请4个Extent
- 为了
节省磁盘空间
,如表的数据量很小(Leaf node segment
和Non-Leaf node segment
都很小)或Rollback segment
,Segment一开始不会直接申请Extent
,而是先用32个碎片页
(用于叶子节点
)来存放数据,用完之后才继续对Extent(1MB)
的申请
Page
-
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
- InnoDB存储引擎的数据是
按行
进行存放的 - 行记录格式
Row_FORMAT
将在后续详细介绍
本文主要介绍InnoDB
存储引擎的数据页结构
数据页结构
File Header
参考链接:Fil Header
- 总共
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
- 总共
56 Bytes
,记录页的状态信息
名称 | 大小(Bytes) | 描述 |
---|---|---|
PAGE_N_DIR_SLOTS | 2 | 在Page Directory 中Slot 的数量,初始值为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
- 每个数据页中都有两个
虚拟的行记录
,用来限定记录(User Record
)的边界(Infimum为下界
,Supremum为上界
) -
Infimum
和Supremum
在页被创建
是自动创建,不会被删除
- 在
Compact
和Redundant
行记录格式下,Infimum
和Supremum
占用的字节数是不一样
的
User Records
参考链接:User Records
- 存储
实际插入的行记录
- 在
Page Header
中PAGE_HEAP_TOP
、PAGE_N_HEAP
的HEAP
,实际上指的是Unordered User Record List
- InnoDB不想每次都
依据B+Tree键的顺序
来插入新行
,因为这可能需要移动大量的数据
- 因此InnoDB插入新行时,通常是插入到当前行的后面(
Free Space的顶部
)或者是已删除行留下来的空间
- InnoDB不想每次都
- 为了保证访问B+Tree记录的
顺序性
,在每个记录中都有一个指向下一条记录的指针
,以此构成了一条单向有序链表
Free Space
- 空闲空间,数据结构是
链表
,在一个记录被删除
后,该空间会被加入到空闲链表中
Page Directory
参考链接:Page Directory
- 存放着
行记录
(User Record
)的相对位置
(不是偏移量) - 这里的
行记录指针称
为Slot
或Directory Slot
,每个Slot
占用2Byte
-
并不是每一个行记录都有一个Slot
,一个Slot中可能包含多条行记录,通过行记录中n_owned
字段标识 -
Infimum
的n_owned总是1
,Supremum
的n_owned为[1,8]
,User Record
的n_owned为[4,8]
-
Slot
是按照索引键值的顺序
进行逆序
存放(Infimum是下界,Supremum是上界
),可以利用二分查找
快速地定位一个粗略的结果
,然后再通过next_record
进行精确查找
-
B+Tree索引
本身并不能直接找到具体的一行记录
,只能找到该行记录所在的页
- 数据库把页载入到
内存
中,然后通过Page Directory
再进行二分查找
- 二分查找时间复杂度很低,又在内存中进行查找,这部分的时间基本开销可以忽略
- 数据库把页载入到
微信公众号【黄小斜】大厂程序员,互联网行业新知,终身学习践行者。关注后回复「Java」、「Python」、「C++」、「大数据」、「机器学习」、「算法」、「AI」、「Android」、「前端」、「iOS」、「考研」、「BAT」、「校招」、「笔试」、「面试」、「面经」、「计算机基础」、「LeetCode」 等关键字可以获取对应的免费学习资料。