0. MySQL本身的文件:
表结构定义文件,以frm为后缀名,还可以存放视图的定义
1. 存储引擎的文件
1.1 表空间文件
(1)InnoDB将存储的数据按表空间(tablespace)进行存放。 以ibd为后缀名
启用参数innodb_file_per_table
之后,每个表会创建一个单独的表空间。这个单独的表空间文件存放:该表的数据、索引和插入缓冲BITMAP等信息,其余信息(如undo信息、插入缓冲索引页、系统事务信息、doublewrite)仍存放在默认的表空间中(ibdata1
)。
在本地中存储位置为/var/lib/mysql/mysql
1.2 重做日志文件
(1)当前redo log文件的命名规则为ib_redoXXX
或ib_redoXXX_tmp
,其中前者为活动重做日志文件,后者表示备用重做日志文件;
(2)InnoDB的redo log文件记录的是关于每个Page的更改情况。
而二进制日志文件仅在事务提交前进行提交,只写磁盘一次。在事务进行过程中,需要不断写入redo log。
2. 表
2.1 索引组织表
(1)索引组织表:InnoDB中,表都是按照主键顺序存放的。即数据以B+树的形式存放。
主键的来源有三种情况:
- 创建表时,显示给定主键
- 表中有唯一非空索引(选择建表时第一个定义的非空唯一索引作为主键)
- 自动生成隐藏的6字节的自增值(GEN_CLUST_INDEX)
_rowid
只适用于查看单个列为主键的情况,上述第3种情况也不适用。
(2)在 InnoDB 的内存中维护了一个Fil_system
的数据结构来缓存整个文件管理。共有64个Fil_shard
。
一个Fil_shard
管理一个或多个 Tablespace,每个Tablespace有一个或多个文件(数据Tablespace仅有一个物理文件,而系统表有多个文件)
2.2 Tablespace
Tablespace有一个存储元信息数据的page,为前4个page。
(1)Page 0;
XDES Entry为Extent的描述符;FSP_FREE
链表管理空闲Extent。
(2)Page 1;
插入缓存的BitMap
(3)Page 2;
关于Segment信息的
(4)Page 3;
保存了聚集索引的root page
2.3 存储结构
(1)段包含数据段、索引段、回滚段等,因为InnoDB是索引组织表,数据段和索引段一样。
(2)一次从磁盘申请4~5个区;区由连续的页组成,其大小总为1MB,一般由64个连续的页组成
(3)在每个段开始时,先用32个页大小的碎片页来存放数据。使用完这些后,才是64个连续页的申请(即一个区的申请)。
这样申请可以基本确保是顺序IO。
(4)页是InnoDB磁盘管理的最小单位,一般为16KB,通过参数page_size
设定
(5)按行存储,每页最多允许存放16KB/2 - 200个记录
2.4 行记录格式
InnoDB 到现在为止设计了4种不同类型的行格式 ,分别是 Compact 、 Redundant 、Dynamic 和 Compressed 行格式,mysql5.0之后默认为Compact ,5.7之后默认为Dynamic格式
2.4.1 Compact格式
- 变长字段长度;只存储VARCHAR列对应数据的实际长度,以列顺序逆序存储;如果某个变长列为NULL,并非存储00,而是直接不存储。
- NULL标志位;最少占用1字节;是BitMap作用,某位为1,代表对应列为NULL。仍然是按照列的顺序逆序排列
每超过8个列,就多占用1个字节(这里统计的列是允许存储 NULL 的列,不包括主键列和使用 NOT NULL 修饰的列)
当数据表的字段都定义成 NOT NULL 的时候,这时候表里的行格式就不会有 NULL 值列表了 - 记录头信息;占用5字节;是否被删除标志、n_owned表示该槽拥有的记录数、next_record指向下一条记录(可以组成链表形式,其实存储的是到下一个记录的偏移量)等;
- 主键信息。若没有定义主键,还会增加一个rowid列(隐藏主键,6 字节)
- 隐藏列数据;每行数据还有两个隐藏列,事务ID列和回滚指针列(分别为6和7字节)。
- 列数据;为NULL则不占用空间。
对固定长度的字段,若实际长度小于固定长度,则会进行填充。
2.4.2 Redundant格式
- 字段长度偏移;倒序记录,每列都需要记录长度(包括隐藏列),前缀和形式。最高位标记字段是否为 NULL,除此外仍遵循长度累加。
- 记录头信息;固定6个字节,包括列的数量
n_field
、列对应偏移量使用 1 字节还是 2 字节表示1byte_offs_flag
等
记录的真实数据大小(不包含头信息这些)小于 127 时,每个列对应的偏移量使用 1 字节
大于 127,小于等于 32767 时,占用 2 字节
大于 32767 时,发生页溢出,每列占用 2 字节(实际上在8000左右就会溢出,溢出后仍然会使用特殊的) - 隐藏列;
- 列数据;CHAR类型的NULL占用空间,VARCHAR不占用。
2.4.3 行溢出
所有VARCHAR列的长度总和不超过65535。(并且应该包含 storage overhead 开销,即存储变长字段长度 + NULL标志位的字节(如果有的话) + 所有VARCHAR列的长度 + 其他列占用的字节 <= 65535)
溢出数据存放在Uncompress BLOB页中。(指向溢出页的大小为20字节)
每个页至少应该有两条行记录,否则就失去了B+Tree的意义了,变成链表了。因此,如果页只能存放一条记录,就会将数据存放到溢出页中。
2.4.4 Dynamic 和 Compressed 行格式
对存放在BLOB中的数据采用了完全的行溢出方式。
此外,Compressed对存储在其中的行数据以zlib算法进行压缩。
2.4.5 CHAR的存储
对于多字节字符编码的CHAR数据类型的存储,InnoDB在内部将其视为VARCHAR类型。
2.5 数据页格式
页类型为B-tree Node
(1)File Header;Checksum、页偏移、该页最后被修改的LSN、前/后一个页(PAGE_NEXT
和PAGE_PREV
);
(2)Page Header;当前页在索引树中的位置、索引ID、槽数量(每个槽2字节);
PAGE_FREE
表示删除的 Record 的链表
PAGE_HEAP_TOP
代表空闲空间开始位置的偏移量
(3) Infimum和Supremum;每个数据页都有的两个虚拟行记录,表示记录的边界,Infimum是比该页中任意记录主键值都要小。
-
Infimum 包含 recorder header 由记录头信息和实际存储内容组成(内容就是其名称)。
通过记录的next_record
就可以遍历整个页中的所有记录
PAGE_NEXT
和PAGE_PREV
可以遍历所有的页,从而遍历整个表中的数据。 -
Supremum 与上述类似,只不过其偏移量为0;
-
记录头信息(长度固定不存在变长字段),第2个字节存储的是组内记录数量,最后两个字节存储到第一条记录的偏移量
(4)User Records;是实际数据存放空间
(5)Free Space;链表结构
(6)Page Directory;将记录分为一个个的组,每个槽指向组中最大的主键元素,这些槽组成了目录,方便进行二分查找。
- 槽是逆序存放的。槽中实际存放的是记录的相对位置。(页开始地址 + 记录的相对位置 = 记录存放位置)
- 既然数据是按照主键顺序存放的,为什么不直接对记录进行二分查找,而要建立Page Directory?
通过上节行记录格式可知,一方面记录中含有deleted_flag
标志,可能有删除的记录。另一方面,User Records中并非全局有序的,可能部分是按照插入顺序存放的,只是组成的链表是有序的。
并且槽的数据是按照主键顺序存放的。 - Slot 的数量限制,防止某个 Slot 中元素过多,导致退化为 O(n) 的时间复杂度。
第一个 Slot 中的记录只能有 1 条记录,即指向 Infimum 记录。
最后一个 Slot 中的记录条数范围只能在 1-8 条之间,包含 Supremum 记录;
剩下的分组中记录条数范围只能在 4-8 条之间。
(7)File Trailer;检查页是否被完整写回磁盘
上述这些都没有提到存储表一共有几列,哪一列是变长类型,列若为固定长度类型,长度为多少在哪存储的?等信息,猜测这些应该是存储在File Space Header页中。
插入过程
-
会在
PAGE_FREE
找到最近一个被删除的 Record, 判断大小是否合适(只判断头结点是否合适,如果大于要插入的记录,即可重用,但可能产生碎片)
这些碎片空间在整个页面快使用完前不会被重新利用,当页面快满时,再插入新纪录后,页面中不能分配一条完整记录空间时,会查看 PAGE_GARBAGE 的空间和剩余可用空间的和是否可用容纳这条记录,如果可用,InnoDB 会尝试重新组织页内的记录,即先开辟一个临时页,把页面内的记录依次插入一遍,之后再把临时页的内容复制到本页面中(比较耗费性能)。 -
若大小不合适,从
PAGE_HEAP_TOP
指向的位置分配空间 -
根据页目录找到其应该插入的 Slot,更改前后 Record 的指针。
找到主键值比待插入记录大且差值最小的 Slot(即第一个比它大的 Slot) -
更新数据页的页目录(Page Directory), 即对应的 REC_NEW_N_OWNED 自增1,假如超过了 PAGE_DIR_SLOT_MAX_N_OWNED 即8个Record, 数据目录需要分裂成两个
(只有页目录指向的那个记录的n_owned
不为0,其余都为0)
2.6 约束
主键的约束名为PRIMARY,UNIQUE索引的约束名与列名相同。
MySQL数据库的外键是即使检查的,在数据的导入操作,会对导入的每一行都进行外键检查,比较耗时,可以在导入过程中忽略外键检查。
InnoDB在外键建立时会自动对该列加一个索引
2.6.1 触发器
触发器的作用是在执行INSERT、DELETE和UPDATE命令之前或之后自动调用SQL命令或存储过程。
CREATE TRIGGER;创建触发器
当前只支持FOR EACH ROW的触发方式。
2.7 视图
(1)视图(View)是一个命名的虚表,可以当表使用,视图中的数据没有实际的物理存储。
(2)可更新视图(updatable view):用户可以对某些视图进行更新操作,其本质是通过视图的定义来更新基本表。
定义视图时加上WITH CHECK OPTION,会对更新视图插入的数据进行检查,不满足视图定义条件的,会抛出异常。
(3)MySQL不支持物化视图,可以通过触发器实现部分物化视图的功能。
2.8 分区
分区是将一个表或索引分解为更小的部分(逻辑上仍为一个整体,但物理上由不同部分组成)。只支持水平分区(拆分行)。
局部分区索引:一个分区中即存放了数据又存放索引
注意:MySQL8.0之后 分区表功能移到引擎层实现
表中存在主键或唯一索引时,分区列必须是唯一索引的一个组成部分(不需要整个唯一索引列都是分区列,允许NULL值)。
启用分区后,表不再是一个ibd文件,而由各个分区ibd文件组成。
2.8.1 分区类型
(1)RANGE分区;给定连续区间的值放入分区
- 当插入一个不在分区中定义的值时,会抛出异常;
- maxvalue可以视为是正无穷;
- 主要用于日期列的分区,例如,提取年/月的函数 YEAR(date),MONTH(data)
- 查询优化时,可以进行Partition Pruning(分区修建)优化,即只需去变量某个分区,而不用遍历整个表。
需要注意的是,优化器只能对YEAR()、TO_DAYS()、TO_SECONDS()、UNIX_TIMESTAMP()进行优化选择。
当前版本只使用EXPALIN即可。 - 使用VALUES LESS THAN
- 分区中的NULL值,会将其视为小于任何一个非NULL值。
(2)LIST分区;离散的值
- 使用VALUES IN
- 插入多个值时,遇到分区未定义的值,不会数据插入
- 必须显式指出哪个分区中放入NULL值,否则插入NULL会报错
(3)HASH分区;根据用户自定义的表达式的返回值进行分区,返回值不能为负数
- PARTITION BY HASH(expr)
PARTITIONS num,表示分割成分区的数量,如果不写该子句,默认为1 - 任何分区函数都会将含有NULL值的记录返回0;
(4)KEY分区;根据MySQL提供的哈希函数进行分区
任何分区函数都会将含有NULL值的记录返回0;
(5)COLUMNS分区;可以直接使用非整型的数据进行分区。包括字符串类型、日期类型。与RANGE和LIST搭配使用
如果按照主键进行分区,进行主键的查询,分区是有意义的。对其他索引列查询可能就需要遍历所有分区。
2.8.2 子分区
允许在RANGE和LIST分区上再进行HASH或KEY的子分区(SUBPARTITION)
子分区的名字必须是唯一的