InnoDB(五)-InnoDB表 | HoleLin's Blog

参考文献

MySQL技术内幕 InnoDB存储引擎
极客时间–SQL必知必会(陈旸)

索引组织表

在InnoDB存储引擎中,表都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table).在InnoDB存储引擎表中,每张表都有个主键(Primary Key),如果在创建表时没有显示地定义主键,则InnoDB存储引擎会按如下方式选择或创建主键:
- 首先判断表中是否有非空的唯一索引(Unique Not Null),如果有,则该列即为主键.
- 如果不符合上述条件,InnoDB存储引擎会自动创建一个6字节大小的指针.

当表中有多个非空唯一索引,InnoDB存储引擎将选择建表时第一个定义的非空唯一索引为主键.需要特别注意的是,主键的选择根据的是定义索引的顺序,而不是建表是列的顺序.

create table z(
	a int not null,
	b int null,
	c int not null,
	d int not null,
	unique key (b),
	unique key (d),
	unique key (c)
);

上面建表语句中,有a,b,c,d四列,其中b,c,d三列都有唯一索引,不同的是b列允许为空值.由于没有显示地定义主键,因此会选择非空的唯一索引(即d列).可以通过下面SQL来判断表的主键值.

mysql> select a,b,c,d,_rowid from z;
+---+------+----+----+--------+
| a | b    | c  | d  | _rowid |
+---+------+----+----+--------+
| 1 |    2 |  3 |  4 |      4 |
| 5 |    6 |  7 |  8 |      8 |
| 9 |   10 | 11 | 12 |     12 |
+---+------+----+----+--------+
3 rows in set (0.00 sec)

_rowid可以显示表的主键.需要特别注意的是,_rowid只能用于查看单个列为主键的情况,对于多列组成的主键就显得无能为力了.

InnoDB逻辑存储结构

从InnoDB存储引擎的逻辑存储结构看,所有数据都被逻辑地存放在一个空间中,称为表空间(tablespace).表空间又由段(segment),区(extent),页(page)组成.页在一些文档中有时也称为块(block).

表空间

表空间可以看作InnoDB存储引擎逻辑结构的最高层,所有的数据都存放在表空间中.在默认情况下InnoDB存储引擎有一个共享表空间ibdata1,即所有的数据都存放在这个表空间内.如果用户启用了参数innodb_file_per_table,则每张表内的数据可以单独放到一个表空间内.
若启用了innodb_file_per_table的参数,需要注意的是每张表的表空间内存放的只是数据,索引和插入缓冲Bitmap页,其他的数据,如回滚(undo)信息,插入缓冲索引页,系统事务信息,二次写缓冲(double write buffer)等还是存放在原来的共享表空间内.这也说明了另一个问题:即使在启用了参数innodb_file_per_table之后,共享表空间还是会不断地增加其大小.

段

常见的段有数据段,索引段,回滚段等.InnoDB存储引擎表是索引组织的,因此数据即索引,索引即数据.
那么数据段即为B+树的叶子节点(Leaf node segment),索引段即为B+树的非叶子节点(Non-leaf node segment).

区

区是由连续页组成的空间,在任何情况下每个区的大小都为1MB.为了保证区中页的连续性,InnoDB存储引擎一次从磁盘申请4~5个区.在默认情况下,InnoDB存储引擎页的大小为16KB,即一个区中一共有64个连续的页.
InnoDB1.0.x版本开始引入压缩页,即每个页的大小可以用过参数KEY_BLOCK_SIZE设置为2K,4K,8K,因此每个区对应的页的数量就应该为512,256,128.
InnoDB1.2.x版本新增innodb_page_size,通过该参数可以将默认页的大小设置为4K,8K.但是页中的数据不压缩.这时区中页的数量同样也为256,128.总之,不论页的大小怎么变化,区的大小总是为1M.

页

页是InnoDB磁盘管理的最小单位.在InnoDB存储引擎中,默认每个页的大小为16KB.而从InnoDB1.2.x版本开始,可以通过参数innodb_page_size将页的大小设置为4K,8K,16K.若设置完成,则所有表中页的大小都为innodb_page_size,不可用对其再次进行修改.除非通过mysqldump导入和导出操作出来的新的库.
在InnoDB存储引擎中,常见的页类型有
- 数据页(B-tree Node)
- UNDO页(UNDO Log Page)
- 系统页(System Page)
- 事务数据页(Transaction system Page)
- 插入缓冲Bitmap页(Insert Buffer Bitmap)
- 插入缓冲空闲列表页(Insert Buffer Free List)
- 未压缩的二进制大对象页(Uncompressed BLOB Page)
- 压缩的二进制大对象页(compressed BLOB Page)

行

InnoDB存储引擎是面对列的(row-oriented),即数据是按行进行存放的.每个页存放的行记录也是有硬性定义的,最多允许存放16*1024B/2-200行,即7992行记录.

InnoDB行记录格式

InnoDB存储引擎和大多数数据库由于,记录是以行的形式存储的.这意味着页中保存这表中一行行的数据.
在InnoDB1.0.x版本之前,InnoDB存储引擎提供了Compact和Redundant两种格式来存放行记录数据,这也是目前使用最多的一种格式.
可以通过命令SHOW TABLE STATUS LIKE 'table_name'来查看当前表使用的行格式.,其中row_format属性表示当前所使用的行记录结构类型.

Named File Formats机制

随着InnoDB存储引擎的发展,新的页数据结构有啥用来支持新的功能特性.比如InnoDB1.0.x版本提供了新的页数据结构来支持压缩功能,完全的溢出(Off page)大变长字符类型字段的存储.这些新的页数据结构和之前版本的页并不兼容,因此从InnoDB1.0.x版本开始,InnoDB存储引擎Named File Formats机制来解决不同版本下页结构兼容性的问题.
InnoDB存储引擎将1.0.x版本之前的文件格式(File Format)定义为Antelope,将这个版本支持的文件格式定义为Barracuda.新的文件格式总是包含于之前的版本的页格式.Antelope文件格式有Compact和Redundant的行格式,Barracuda文件格式既包括Antelope所有的文件格式,另外新加入了Compressed和Dynamic格式.

ROW_FORMAT的值有DEFAULT,FIXED,DYNAMIC,COMPRESSED,REDUNDANT,COMPACT.

const char *ha_row_type[] = {
  "", "FIXED", "DYNAMIC", "COMPRESSED", "REDUNDANT", "COMPACT",
  /* Reserved to be "PAGE" in future versions */ "?",
  "?","?","?"
};

enum row_type { ROW_TYPE_NOT_USED=-1, ROW_TYPE_DEFAULT, ROW_TYPE_FIXED,
		ROW_TYPE_DYNAMIC, ROW_TYPE_COMPRESSED,
		ROW_TYPE_REDUNDANT, ROW_TYPE_COMPACT,
                /** Unused. Reserved for future versions. */
                ROW_TYPE_PAGE };

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2022-05-02 14:03:47
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.05 sec)

InnoDB数据页结构

数据库I/O操作的最小单位是页,与数据库相关的内容都会存储在页结构里.数据页包括七个部分

文件头(File Header)
页头(Page Header)
最大最小记录(Infimum+supremum)
用户记录(User Records)
空闲空间(Free Space)
页目录(Page Directory)
文件尾(File Trailer

名称	占用大小	说明
`File Header`	38字节	文件头,描述页的信息
`Page Header`	56字节	页头,页的状态信息
`Infimum+Supremum`	26字节	最小和最大记录,这是两个虚拟的行记录
`User Records`	不确定	用户记录,存储行记录内容
`Free Space`	不确定	空闲空间,页中还没有被使用的空间
`Page Directory`	不确定	页目录,存储用户记录的相对位置
`File Trailer`	8字节	文件尾,校验页是否完整

`File Header`

File Header用来记录页的一些头信息.由8个部分组成,共占用38字节.

名称	字节	说明
`FIL_PAGE_SPACE_OR_CHKSUM`	4	当MySQL为MySQL4.0.14之前的版本时,该值为0.在之后的MySQL版本中,该值代表页的checksum值(一种新的checksum值).
`FIL_PAGE_OFFSET`	4	表空间中页的偏移值.如某独立表空间a.ibd的大小为1GB,如果页的大小为16KB,那么总共有65536页.FIL_PAGE_OFFSET表示该页在所有页中的位置.若此表空间的ID为10,那么搜索页(10,1)就表示查找a中的第二个页.
`FIL_PAGE_PREV`	4	当前页的上一个页,B+Tree特性决定了叶子节点必须是双向列表
`FIL_PAGE_NEXT`	8	当前页的下一个页,B+Tree特性决定了叶子节点必须是双向列表
`FIL_PAGE_LSN`	8	该值代表该页最后被修改的日志序列位置LSN(Log Sequence Number)
`FIL_PAGE_TYPE`	2	InnoDB存储引擎页的类型.常见的类型见MySQL-File Page Type,特别注意`0x45BF`,该值代表了存放的是数据页,即实际记录的存储空间.
`FIL_PAGE_FILE_FLUSH_LSN`	8	该值仅在系统表空间的一个页中定义,代表文件至少被更新到了该LSN值.对于独立表空间,该值都为0.
`FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID`	4	从MySQL4.1开始,该值代表页属于哪个表空间.

`Page Header`

该部分用来记录数据页的状态信息,由14个部分组成,用占用56字节.

名称	字节	说明
`PAGE_N_DIR_SLOTS`	2	在Page Directory(页目录)中的Slot(槽)数.
`PAGE_HEAP_TOP`	2	堆中第一个记录的指针,记录在页中是根据堆的形式存放的
`PAGE_N_HEAP`	2	堆中的记录数.一共占用2字节,但是第15位表示行记录格式.
`PAGE_FREE`	2	指向可重用空间的首指针
`PAGE_GARBAGE`	2	已删除记录的字节数,即行记录结构中delete flag为1的记录大小的总数.
`PAGE_LAST_INSERT`	2	最后插入记录的位置.
`PAGE_DIRECTION`	2	最后插入的方向.可能的取值为: * PAGE_LEFT(0x01) * PAGE_RIGHT(0x02) * PAGE_SAME_REC(0x03) * PAGE_SAME_PAGE(0x04) * PAGE_NO_DIRECTION(0x05)
`PAGE_N_DIRECTION`	2	一个方向连续插入记录的数量
`PAGE_N_RECS`	2	该页中记录的数量
`PAGE_MAX_TRX_ID`	8	修改当前页的最大事务ID,注意该值仅在Secondary Index中定义
`PAGE_LEVEL`	2	当前页在索引树中的位置,0x00代表叶节点,即叶节点总是在第0层
`PAGE_INDEX_ID`	8	索引ID,表示当前页属于哪个索引
`PAGE_BTR_SEG_LEAF`	10	B+树数据页非叶子节点所在段的segment header.注意该值仅在B+树的Root页中定义
`PAGE_BTR_SEG_TOP`	10	B+树数据页所在段的segment header.注意该值仅在B+树的Root页中定义

Infimum和Supremum Record

在InnoDB存储引擎中,每个数据页中有两个虚拟的行记录,用来限定记录的边界.Infimum记录是比该页中任何主键值都要小的值,Supermum指比任何可能大的值还要大的值.这两个值在页创建时被建立,并且在任何情况下不会被删除.

User Record和Free Space

User Record即实际存储行记录的内容.InnoDB存储引擎表总是B+树索引组织的.
Free Space即空闲空间,同样也是链表数据结构.在一条记录被删除后,该空间会被加入到空闲链表中.

Page Directory

Page Directory(页目录)中存放了记录的相对位置(注意,这里存放的是页相对位置,而不是偏移量),有些时候这些记录指针称为Slots(槽)或者目录槽(Directory Slots).与其他数据库系统不同的是,在InnoDB中并不是每个记录拥有一个槽,InnoDB存储引擎的槽是一个稀疏目录(sparse directory),即一个槽中可能包含多个记录.伪记录Infimum的n_owned值总是为1,记录Supremum的n_owned的取值范围为[1,8],其他用户记录n_owned的取值范围为[4,8].当记录被插入或删除时需要对槽记性分裂或平衡的维护操作.
在Solts中记录按照索引键值顺序存放,这样可以利用二叉查找迅速找到记录的指针.
由于InnoDB存储引擎中Page Direcotry是稀疏目录,二叉查找的结果只是一个粗略的结果,因此InnoDB存储引擎必须通过record header 中的next_record来继续查找相关记录.
需要牢记的是,B+树索引本身并不能找到具体的一条记录,能找到的只是记录所在的页.数据库把页载入到内存,然后通过Page Directory在进行二叉查找.只不过二叉查找的时间复杂度很低,同时在内存中查找很快,因此通常忽略这部分查找所用的时间.

File Trailer

为了检测页是否已经完整地写入磁盘(如可能发生的写入过程中磁盘损坏,机器关机等),InnoDB存储引擎的页设置了File Trailer部分.
File Trailer只有一个FIL_PAGE_END_LSN部分,占用8字节.前4字节代表该页的checksum值,最后4字节和File Header中的FIL_PAGE_LSN相同.将这两个值与FIL_PAGE_SPACE_OR_CHKSUM和FIL_PAGE_LSN值进行比较,看是否一致(checksum的比较需要通过InnoDB的checksum函数来进行比较,不是简单的等值比较),以此来保证页的完整性(not corrupted).

约束

数据完整性

数据完整性有三种形式:
- 实体完整性保证表中有一个主键.在InnoDB存储引擎表中,用户可以通过定义Primary Key或Unique Key约束来保证实体的完整性.还有通过编写一个触发器来保证数据完整性.
- 域完整性保证数据每列的值满足特定的条件.在InnoDB存储引擎表中,域完整性可以通过以下几种途径来保证:
  - 选择合适的数据类型确保一个数据值满足特性条件.
  - 外键(Foregin Key)约束
  - 编写触发器
  - 还可以考虑DEFAULT约束作为强制域完整性的一个方面.
- 参照完整性保证两张表之间的关系.InnoDB存储引擎支持外键,因此允许用户定义外键以强制参照完整性,也可以通过编写触发器以强制执行.
- 对于InnoDB存储引擎本身而言,提供以下几种约束:
  - Primary Key
  - Unique Key
  - Foregin Key
  - Default
  - NOT NULL

约束的创建和查找

约束的创建可以采用以下两种方式:
- 表建立时就进行约束定义
- 利用ALTER TABLE命令来进行创建约束

对于Unique Key的约束,还可以通过命令CREATE UNIQUE INDEX来建立.对于主键约束而言,其默认约束名为PRIMARY.对于Unique Key约束,默认约束名和列名一样.

mysql> select constraint_name,constraint_type from information_schema.TABLE_CONSTRAINTS where table_schema='nacos' and table_name='tenant_info'\G
*************************** 1. row ***************************
CONSTRAINT_NAME: PRIMARY
CONSTRAINT_TYPE: PRIMARY KEY
*************************** 2. row ***************************
CONSTRAINT_NAME: uk_tenant_info_kptenantid
CONSTRAINT_TYPE: UNIQUE
2 rows in set (0.00 sec)

通过information_schema架构下的表TABLE_CONSTRAINTS来查看当前MySQL库下所有的约束信息.对于外键约束的命令,还可以通过查看表REFERENTIAL_CONSTRAINTS

mysql> select * from information_schema.REFERENTIAL_CONSTRAINTS where constraint_schema='flowable-demo-data'\G
*************************** 1. row ***************************
       CONSTRAINT_CATALOG: def
        CONSTRAINT_SCHEMA: flowable-demo-data
          CONSTRAINT_NAME: ACT_FK_APP_DEF_DPLY
UNIQUE_CONSTRAINT_CATALOG: def
 UNIQUE_CONSTRAINT_SCHEMA: flowable-demo-data
   UNIQUE_CONSTRAINT_NAME: PRIMARY
             MATCH_OPTION: NONE
              UPDATE_RULE: NO ACTION
              DELETE_RULE: NO ACTION
               TABLE_NAME: ACT_APP_APPDEF
    REFERENCED_TABLE_NAME: ACT_APP_DEPLOYMENT

约束和索引的区别

约束是一个逻辑的概念,用来保证数据的完整性,而索引是一个数据结构,既有逻辑上的概念,在数据库中还代表着物理存储的方式.

分区表

MySQL数据库在5.1版本时添加了对分区的支持.分区的过程是将一个表或索引分解为多个更小,更可管理的部分.就访问数据库的应用而言,从逻辑上讲,只有一个表或一个索引,但是在物理上这个表或索引可能与数十个物理分区组成,每个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理.
MySQL数据库支持的分区类型为水平分区.并不支持垂直分区.此外MySQL数据库分区是局部分区索引,一个分区中既存放数据有存放了索引.而全局分区是指,数据存放在各个分区中,但是所有数据的索引放在一个对象中.
- 水平分区:指将同一表中不同行的记录分配到不同的物理文件中.
- 垂直分区:指将同一表中不同列的记录分配到不同的物理文件中.