页与记录
Ⅰ、无主键的一个小测试
1.1 表上存在唯一键
(root@localhost) [test]> show create table test_key\G
*************************** 1. row ***************************
Table: test_key
Create 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=latin1
1 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_key2
Create 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=latin1
1 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: def
TABLE_SCHEMA: test
TABLE_NAME: test_key2
COLUMN_NAME: b
ORDINAL_POSITION: 2
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 4
CHARACTER_OCTET_LENGTH: 4
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: latin1
COLLATION_NAME: latin1_swedish_ci
COLUMN_TYPE: varchar(4)
COLUMN_KEY: PRI
EXTRA:
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_key3
Create Table: CREATE TABLE `test_key3` (
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 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'\G
Empty 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
