从磁盘的物理结构来看存取的最小单位是扇区,一个扇区是512字节。
微软操作系统(DOS、WINDOWS等)中磁盘文件存储管理的最小单位叫做“簇”。一簇就是一组连续的扇区,每个簇可以包括2、4、8、16、32或64个扇区。
一个文件通常存放在一个或多个簇里,但至少要单独占据一个“簇”。 也就是说两个文件不能存放在同一个簇中。
NTFS文件系统格式化的时候默认是8个扇区组成一个簇,即4096字节,即4kb。而MySQL读取数据的最小单位为页,默认大小为16kb。
lnnoDB逻辑存储结构
MySQL所有数据都被逻辑地存放在一个空间中 ,我们称之为表空间 ( tablespace ) 。表空间又由段 ( segment ) 、区 ( extent ) 、页 ( page ) 组成。默认情况下 InnoDB存储引擎有一个共享表空间 ibdata1 ,即所有数据都放在这个表空间内 。如果我们启用了参数innodb_file_per_table ,则每张表内的数据可以单独放到一个表空间内 。段就是表,区就是连续的几个页,页是最小单位。
在物理上,MySQL的文件表结构:在datadir目录下/var/lib/mysql/
show variables like 'datadir';
![点击并拖拽以移动 wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==](https://img-blog.csdnimg.cn/2022010613131885725.gif)
可以发现每一个数据库都有一个同名的文件夹,每个文件夹下都有ibd文件。
比如新建了一个mytabletest数据库和一个student表,就会在/var/lib/mysql/下有个mytabletest文件夹以及文件夹下一个students.ibd文件。students.ibd这个文件由多个段(segments)组成,每个段和一个索引相关,这个就是主键索引。
页的结构:
说明:
File Header :记录页的一些头信息,包括页类型,页的上一页和下一页。属于哪个表空间,以及在表空间中的偏移量,比如查找(10,1),表示在第十个表空间,第二页中。共占用38字节。
Page Header:记录数据页的状态信息,记录了页中记录的数量和事务最大ID,空闲空间地址。共占56个字节。
infimum和supermum record:虚拟的行记录,限定记录的边界。infimum记录的是比页中任何主键都要小的值,supermum值比任何可能大的值还要大的值。在页创建时被创建,任何情况下不会被删除,在compact和redundant行格式下,占用的字节数不同。
User page和Free page:User page是实际存储行记录的内容,innodb表总是B+树索引组织的;Free page是指空闲空间,是一个链表结构,在一条记录被删除后,该空间会被加入到空闲链表中。
Page directory(页目录):存放了记录(页)的相对位置,有时这些记录指针称为slots(槽)或directory slots(目录槽),在innodb中并不是每行拥有一个槽,innodb的槽是一个稀疏目录,即一个槽可能包含多个记录。
注:B+树索引本身并不能找到具体的一条记录,只能找到该记录所在的页。数据库把页载入到内存中,通过page directory再进行二叉查找。
MySQL记录的删除和插入都可能会造成空闲空间的增加。
删除记录的流程是:找到记录标记为删除,实际并不会回收空间。当需要插入数据id恰好在这之间就会复用这个空闲空间。
如果相邻的两个数据页利用率都很小,系统就会把这两个页上的数据合到其中一个页上,另外一个数据页就被标记为可复用。
删除delete操作并不会更改磁盘空间的大小。
插入的流程是:当需要插入一条记录时,如果页未满,找到对应的位置插入,如果所在页满了就会页分裂。增加页后如果数据页不满也会使得磁盘空间利用率降低。
重建表:原理是新建一个同结构的表,把原表的数据按id顺序一个一个插入新表。再交换表名,删除旧表。
可以使用alter table A engine=InnoDB命令来重建表使得空间利用率增加。
显然,花时间最多的步骤是往临时表插入数据的过程,如果在这个过程中,有新的数据要写入到表A的话,就会造成数据丢失。因此,在整个DDL过程中,表A中不能有更新。也就是说,这个DDL不是Online的。
而在MySQL 5.6版本开始引入的Online DDL,对这个操作流程做了优化。
我给你简单描述一下引入了Online DDL之后,重建表的流程:
-
建立一个临时文件,扫描表A主键的所有数据页;
-
用数据页中表A的记录生成B+树,存储到临时文件中;
-
生成临时文件的过程中,将所有对A的操作记录在一个日志文件(row log)中,对应的是图中state2的状态;
-
临时文件生成后,将日志文件中的操作应用到临时文件,得到一个逻辑数据上与表A相同的数据文件,对应的就是图中state3的状态;
-
用临时文件替换表A的数据文件。
Online DDL的原理是,在生成临时文件的过程中,将所有对A的操作记录在一个日志文件(row log)中,临时文件生成完后将日志文件应用到临时文件中。
通过重建表,就能大大增加磁盘的利用率了。
参考: