页与记录

Ⅰ、无主键的一个小测试

1.1 表上存在唯一键

(root@localhost) [test]> show create table test_key\G*************************** 1. row ***************************Table: test_keyCreate Table: CREATE TABLE `test_key` (  `a` int(11) DEFAULT NULL,  `b` int(11) NOT NULL,  `c` int(11) NOT NULL,  UNIQUE KEY `b` (`b`),  UNIQUE KEY `c` (`c`),  UNIQUE KEY `a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)(root@localhost) [test]> select *, _rowid from test_key;+------+---+---+--------+| a| b | c | _rowid |+------+---+---+--------+|1 | 2 | 3 |  2 ||4 | 5 | 6 |  5 ||7 | 8 | 9 |  8 |+------+---+---+--------+3 rows in set (0.00 sec)可以看出,b列被作为了主键(root@localhost) [test]> show create table test_key2\G*************************** 1. row ***************************Table: test_key2Create Table: CREATE TABLE `test_key2` (  `a` varchar(4) DEFAULT NULL,  `b` varchar(4) NOT NULL,  `c` varchar(4) NOT NULL,  UNIQUE KEY `b` (`b`),  UNIQUE KEY `c` (`c`),  UNIQUE KEY `a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)(root@localhost) [test]> select *, _rowid from test_key2;ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'这里_rowid只有当key类型为id时才有效换俩办法看即可法1：(root@localhost) [test]> select * from information_schema.columns where table_name='test_key2' and column_key='pri'\G*************************** 1. row ***************************TABLE_CATALOG: defTABLE_SCHEMA: test  TABLE_NAME: test_key2 COLUMN_NAME: bORDINAL_POSITION: 2  COLUMN_DEFAULT: NULL IS_NULLABLE: NODATA_TYPE: varcharCHARACTER_MAXIMUM_LENGTH: 4  CHARACTER_OCTET_LENGTH: 4NUMERIC_PRECISION: NULLNUMERIC_SCALE: NULL  DATETIME_PRECISION: NULL  CHARACTER_SET_NAME: latin1  COLLATION_NAME: latin1_swedish_ci COLUMN_TYPE: varchar(4)  COLUMN_KEY: PRIEXTRA:  PRIVILEGES: select,insert,update,references  COLUMN_COMMENT:GENERATION_EXPRESSION:1 row in set (0.00 sec)法2：(root@localhost) [test]> desc test_key2;+-------+------------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| a | varchar(4) | YES  | UNI | NULL||| b | varchar(4) | NO| PRI | NULL||| c | varchar(4) | NO| UNI | NULL||+-------+------------+------+-----+---------+-------+3 rows in set (0.00 sec)看到b列被作为主键

1.2 表上无唯一键

当表中未显式指定主键,且没有非空唯一键时,系统会自定义一个主键（6个字节,int型,全局,隐藏）

(root@localhost) [test]> show create table test_key3\G*************************** 1. row ***************************Table: test_key3Create Table: CREATE TABLE `test_key3` (  `a` int(11) DEFAULT NULL,  `b` int(11) DEFAULT NULL,  `c` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)(root@localhost) [test]> select * from test_key3;+------+------+------+| a| b| c|+------+------+------+|1 |2 |3 ||4 |5 |6 ||7 |8 |9 |+------+------+------+3 rows in set (0.00 sec)(root@localhost) [test]> select *, _rowid from test_key3;ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'(root@localhost) [test]> select * from information_schema.columns where table_name='test_key3' and column_key='pri'\GEmpty set (0.00 sec)(root@localhost) [test]> desc test_key3;+-------+---------+------+-----+---------+-------+| Field | Type| Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| a | int(11) | YES  | | NULL||| b | int(11) | YES  | | NULL||| c | int(11) | YES  | | NULL||+-------+---------+------+-----+---------+-------+3 rows in set (0.00 sec)由上可见,这种情况下_rowid我们是看不了的,对我们透明

tips：

假设有两张表都使用了系统定义的主键,则系统定义的主键的id并不是表内单调递增的,而是全局递增

该系统的rowid是定义在ibdata1.ibd中的sys_rowid中,全局自增

6个字节表示的数据量为 2^48 ,通常意义上是够用的

强烈建议必须自己显示定义主键

Ⅱ、页的结构

One Page

File Header

Page Header

Infimun + Supermum Records

User Records

Free Space

Page Directory

File Trailer

• 文件头、页头、文件结尾信息三部分大小固定分别为,38,56,8字节,这些信息用来记录page的一些信息,如其所在b+ tree的位置,checksum等
• 用户记录,空闲空间,页目录为实际的行记录存储空间,大小不固定
• Infimun + Supermum Records记录两条虚拟记录,一个最小,一个最大
• 一个page最多存放16k/2 - 200行记录,即7992行

2.1 File Header

-	字节数	备注
FIL_PAGE_SPACE_OR_CHKSUM	4	4.0.14之前为0,现在为page的checksum值
Fil_PAGE_OFFSET	4	page的偏移位置,用于定位页在一个表空间中的位置
FIL_PAGE_PREV	4	上一个
FIL_PAGE_NEXT	4	下一个页
FIL_PAGE_LSN	8	该页ui后一次被修改的LSN
FIL_PAGE_TYPE	2	页的类型(0x45BF表示存放的是数据页)
FIL_PAGE_FILE_FLUSH_LSN	8	仅存在系统表空间的一个页中定义,表示文件至少被更新到了该LSN,独立表空间中该值为0
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID	4	4.1开始该值表示页属于哪个空间

InnoDB中页的类型：

B+树页,Undo Log页,索引节点,系统页,最新分配页,BLOB页,Insert Buffer空闲列表与位图等等

2.2 Page Header

记录数据页的状态信息,共由14个部分组成,占56个字节,此处不展开分析

2.3 Infimum和Supermum Record

表示两个虚拟记录,用于限定页的边界,页创建时即被创建,永远不被删除,不同行格式下,大小不一样

2.4 User Record 和 Free Space

User Record为实际存储记录的内容,Innodb中总是B+ tree索引组织

Free Space为空闲空间,是一个链表结构,一条记录被删,该空间被加入到空闲链表中

2.5 Page Directory

存放记录的相对位置(page的相对位置)

查一条记录不能直接查到,只能定位到此记录所在的page,将page拽到内存中,通过Page Directory进行二叉查找

2.6 File Trailer

用于检测page是否正常落盘

内部是一个FIL_PAGE_END_LSN,占用8字节,前4字节代表checksum,后4字节与File Header中FIL_PAGE_LSN相同,将这两个值和File_Header中FIL_PAGE_SPACE_OR_CHECKSUM、FIL_PAGE_LSN对比来保证page的完整性(not corrupted),对比的时候需要通过innodb的checksum函数来做,不是等值对比

tips：

每次从磁盘读取一个page检测完整性会带来一定的开销

5.6.6开始innodb_checksum_algorithm参数可控制checksum的算法,默认crc32,之前是innodb,前者更高效

若以strict算法来保存页,低版本MySQL将无法读取这些页,需要mysql_upgrade升级

strict_crc32是最快的校验方式,推荐

Ⅲ、记录

3.1 记录的格式——ROW_FORMAT

格式	说明
REDUDENT	4.1之前默认格式
COMPACT	5.6默认格式
COMPRESSED	支持压缩
DYNAMIC	5.7默认格式,优化了大对象

3.2 COMPACT结构

variable string length list	NULL flag	record header	col	col2	...
列长度小于255字节,用1字节表示,否则2字节(逆序)	1字节	5字节

tips：

• 记录头信息保存该行是否被删除等信息
• NULL不占实际存储
• 每行数据除了自定义列,还有两个隐藏列,事务id(6字节)和回滚指针(7字节),若无自定义主键会增加一个6字节的rowid列

3.2 DYNAMIC

DYNAMIC相比COMPACT,优化了大对象记录的存储

假设一条记录A B C D四列,i中D列是text类型,含有2w字节的长度

COMPACT会存储text中前768字节,剩余数据通过20字节的指针指向溢出页

DYNAMIC存储如下,no prefix data

DYNAMIC在一个页中存储的记录数更多(768字节的prefix,一调记录的字节假设是800,那16k的页只能存放20条记录),这样b+tree就可能变高,读取io次数变多,性能变差

3.3 记录的更新

原地更新(in place update)

• 原地更新不会占用新的存储空间
• 非原地更新需要删除(物理删除)原来的空间,然后更新后的数据插入到页的后面
• 删除的数据空间,会插入到Free_List链表头部
• 原地更新不会触发页的分裂

Free_List是将页中被删除的空间串联在一起(组成一个链表),当有数据被插到页内时,先看一下Free_list中第一个空间的大小,如果空间合适 ,就将该记录插入到第一个空间中去,如果不合适 ,直接插入到页的尾部的剩余空间(不会去看Free_list的第二个空间)

当该页的数据被插满了,不会马上进行分页,而是进行 reorganize 操作,即将页内的数据在内存中进行整理,然后覆盖原来的页（不影响性能）,所以InnoDB不需要碎片整理

Reorganize