第15章：MySQL之InnoDB 表空间内部组织结构
第15章：MySQL之InnoDB 表空间内部组织结构
修订日期：2021-01-08
一. 表空间内部组织结构
| 表空间 |
|---|
| 内部有多个段对象（Segment） 组成 |
| 每个段（Segment）由区（Extent） 组成 |
| 每个区（Extent）由页（Page）组成 |
| 每个页里面报存数据 (或者叫记录 Row) |
|
1.段对用户来说是透明的
2.段也是一个 逻辑概念
3.目前为止在 information_schema 中无法找到段的概念
4.重点需要理解区（Extent） 和页（Page） 的概念
1.1表空间 – 区
-
区是最小的空间申请单位，区的
固定大小为1M- innodb设置为
page_size= 16K， 那么一个区1M * 1024 / 16 = 64个页 - 同理
page_size= 8K就是128个页 - 同理
page_size= 4K就是256个页
- innodb设置为
-
通常说来，一次申请
4个区（4M）的大小（存在一次申请5个区的时候，但是绝大部分情况就是申请4个区） -
单个区的
1M空间内，物理上是连续的（一次申请的4个区的空间之间（1M和1M之间）不保证连续）
1.2 空间 – 页
1.2.1 页的定义
- 页是
最小的I/O操作单位 data的最小单位不是页，而是页中的记录（row）- 普通用户表中MySQL
默认的每个页为16K - 从MySQL5.6开始使用
innodb_page_size可以控制页大小 - 一旦数据库通过
innodb_page_size创建完成，则后续无法更改 innodb_page_size是针对普通表的，压缩表不受其限制
1.2.2 如何定位到页
-
每个
表空间都对应 一个SpaceID，而表空间又对应一个ibd文件，那么一个ibd文件也对应一个SpaceID -
因为
表空间<-->idb文件，表空间<-->SpaceID，所以ibd文件<-->SpaceID -
ibdata1 对应的 SpaceID 为 0
-
每创建一个表空间（ibd文件） ， SpaceID
自增长（全局） -
PageNumber
- 在一个表空间中，第几个
16K的页（假设 innodb_page_size = 16K） 即为 PageNumber
- 在一个表空间中，第几个
每个表空间中，都是从0开始递增，且仅仅是表空间内
唯一
每次读取
Page时，都是通过SpaceID和PageNumber进行读取；
可以简单理解为从表空间的开头读多少个
PageNumber * PageSize的字节（偏移量）
想像成数组，数组的名字就是
SpaceID， 数组的下标就是PageNumber
1).可以通过（ SpaceID ， PageNumber ）定位到某一个页
2).在一个SpaceID（ibd文件）中， PageNumber 是唯一且自增 的
3).这里的区（extent） 的概念已经弱化。在这个例子中，第一个区的PageNumber是（0~63） 且这 64个页在物理上是连续 的；第二个区的PageNumber是（64~127） 且这 64个页在物理上也是连续 的；但是（0~63） 和（64~127）之间 在物理上则不一定是连续的 ，因为区和区之间在物理上不一定是连续的。
4).删除表的时候， SpaceID不会回收 ，SpaceID是全局自增长的。
mysql> select * from information_schema.innodb_sys_tables limit 1\G ; -- INNODB_SYS_TABLES 表
*************************** 1. row ***************************
TABLE_ID: 14
NAME: SYS_DATAFILES
FLAG: 0
N_COLS: 5
SPACE: 0
FILE_FORMAT: Antelope
ROW_FORMAT: Redundant
ZIP_PAGE_SIZE: 0
SPACE_TYPE: System
1 row in set (0.01 sec)
mysql>select name, space,table_id from information_schema.innodb_sys_tables where space=0 ;
+------------------+-------+----------+
| name | space | table_id |
+------------------+-------+----------+
| SYS_DATAFILES | 0 | 14 |
| SYS_FOREIGN | 0 | 11 |
| SYS_FOREIGN_COLS | 0 | 12 |
| SYS_TABLESPACES | 0 | 13 |
| SYS_VIRTUAL | 0 | 15 |
+------------------+-------+----------+
5 rows in set (0.01 sec)
mysql> select name, space,table_id from information_schema.innodb_sys_tables where space<>0 limit 5 ;
+-----------------------------+-------+----------+
| name | space | table_id |
+-----------------------------+-------+----------+
| employees/departments | 32 | 47 |
| employees/dept_emp | 34 | 49 |
| employees/dept_manager | 33 | 48 |
| employees/employee_comps_1 | 48 | 63 |
| employees/employee_comps_2k | 50 | 65 |
+-----------------------------+-------+----------+
5 rows in set (0.00 sec)
-- 独立表空间的table_id 和 SpaceID 一一对应
-- 共享表空间是多个table_id 对应 一个 SpaceID
1.3 压缩表
- 基于页的压缩,每个表的页大小可以不同（针对压缩表来讲）
-- ger3_space的file_block_size=4096，不是innodb_page_size的大小
-- 所在在创建 普通表 的时候，报错了
mysql> create table test_ger (a int) tablespace=ger3_space;
ERROR 1478 (HY000): InnoDB: Tablespace `ger3_space` uses block size 4096 and cannot contain a table with physical page size 8192
-- 使用压缩表的方式
mysql> create table comps_test1 (a int) row_format=compressed, key_block_size=4; -- 1K, 2K, 4K, 8K, 16K 只有这几个页大小可以选择
Query OK, 0 rows affected (0.13 sec)
-- 在之前的ger3_space中创建压缩表
mysql> create table comps_test2 (a int)tablespace=ger3_space row_format=compressed, key_block_size=4;
-- 由于ger3_space是4K的，所以这里页大小也只能是4K Query OK, 0 rows affected (0.09 sec)
-- 修改存在的表变成压缩表
mysql> alter table t1 row_format=compressed,key_block_size=4; Query OK, 0 rows affected (0.17 sec)
Records: 0 Duplicates: 0 Warnings: 0
注意：
虽然SQL语法中写的是row_format=compressed，但是压缩是针对页的，而不是记录 ；即读页的时候解压 ，写页的时候压缩 ，并不会在读取或写入单个记录（row）时就进行解压或压缩操作。
1.3.1 key_block_size的含义
-
key_block_size的可选项是1k，2k，4k，8k，16k（是页大小，不是比例） -
不是将原来
innodb_page_size页大小的数据压缩成key_block_size的页大小，因为有些数据可能不能压缩，或者压缩不到那么小 -
压缩是将原来的页的数据通过压缩算法压缩到一定的大小，然后用
key_block_size大小的页去存放。
比如原来的
innodb_page_size大小是 16K ，现在的 key_block_size 设置为 8K ,某表的数据大小是 24k ，原先使用 2 个 16k 的页存放；压缩后,数据从 24k –> 18k ；由于现在的 key_block_size=8k 所以需要 3 个 8K 的页存放压缩后的 18k 数据多余的空间可以留给下次插入或者更新
压缩比和设置的
key_block_size没有关系。压缩比看数据本身和算法（zlib）， key_block_size 仅仅是设置存放压缩数据的页大小，不解压也能插入数据，通过在剩余空间直接存放redo log，然后页空间存放满后，再解压，利用redo log更新完成后，最后再压缩存放（此时就没有redo log 了）。减少解压和压缩的次数。
- 查看压缩比
mysql> use employees ;
Database changed
mysql> create table employee_comps_1 like employees;
Query OK, 0 rows affected
mysql> alter table employee_comps_1 row_format=compressed,key_block_size=4;
Query OK, 0 rows affected
mysql> > show create table employee_comps_1\G
*************************** 1. row ***************************
Table: employee_comps_1
Create Table: CREATE TABLE `employee_comps_1` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4
1 row in set (0.00 sec)
-- 插入数据
mysql> insert into employee_comps_1 select * from employees;
Query OK, 300024 rows affected (6.65 sec)
Records: 300024 Duplicates: 0 Warnings: 0
-- 查看压缩比
mysql> use information_schema;
Database changed
mysql>select * from INNODB_CMP;
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 18599 | 13442 | 2 | 5157 | 0 |
| 8192 | 0 | 0 | 0 | 0 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.01 sec)
mysql> select 13442/18599; -- compress_ops_ok / compress_ops
+-------------+
| 13442/18599 |
+-------------+
| 0.7227 | -- 压缩比在90%
+-------------+
1 row in set (0.00 sec)
mysql> select * from INNODB_CMP_RESET;
-- 查询INOODB_CMP_RESET，会把INNODB_CMP表中的数据复制过来，并清空INNODB_CMP
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 18599 | 13442 | 2 | 5157 | 0 |
| 8192 | 0 | 0 | 0 | 0 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.00 sec)
mysql> select * from INNODB_CMP; -- 查询该表，数据已经被清空了
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 0 | 0 | 0 | 0 | 0 |
| 4096 | 0 | 0 | 0 | 0 | 0 |
| 8192 | 0 | 0 | 0 | 0 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.00 sec)
-- 注意，这个表里面的数据是累加的，是全局信息，没法对应到某一张表
shell> ll -h employee*.ibd # 可以看出磁盘占用还是有明显减小的
-rw-r-----. 1 mysql mysql 14M Jan 4 13:41 employee_comps_1.ibd
-rw-r-----. 1 mysql mysql 22M Dec 2 21:32 employees.ibd
mysql> show variables like "%innodb_cmp_per_index%";
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_cmp_per_index_enabled | OFF | -- 该功能目前是关闭的
+------------------------------+-------+
1 row in set (0.02 sec)
mysql> set global innodb_cmp_per_index_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%innodb_cmp_per_index%";
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| innodb_cmp_per_index_enabled | ON |
+------------------------------+-------+
1 row in set (0.01 sec)
mysql> use employees
Database changed
mysql> create table employee_comps_2k like employees;
Query OK, 0 rows affected (0.13 sec)
mysql> alter table employee_comps_2k row_format=compressed,key_block_size=2; -- 设置成2K的页大小
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into employee_comps_2k select * from employees; -- 插入数据
Query OK, 300024 rows affected (9.68 sec)
Records: 300024 Duplicates: 0 Warnings: 0
mysql> use information_schema;
Database changed
mysql> select * from INNODB_CMP;
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| page_size | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
| 1024 | 0 | 0 | 0 | 0 | 0 |
| 2048 | 34676 | 23729 | 2 | 10947 | 0 |
| 4096 | 0 | 0 | 0 | 0 | 0 |
| 8192 | 0 | 0 | 0 | 0 | 0 |
| 16384 | 0 | 0 | 0 | 0 | 0 |
+-----------+--------------+-----------------+---------------+----------------+-----------------+
5 rows in set (0.01 sec)
mysql> select 23729/34676;
+-------------+
| 23729/34676 |
+-------------+
| 0.6843 | -- 2K时，压缩比是68%
+-------------+
1 row in set (0.01 sec)
mysql> select * from INNODB_CMP_PER_INDEX; -- 开启innodb_cmp_per_index_enabled才有数据
+---------------+-------------------+------------+--------------+-----------------+---------------+----------------+-----------------+
| database_name | table_name | index_name | compress_ops | compress_ops_ok | compress_time | uncompress_ops | uncompress_time |
+---------------+-------------------+------------+--------------+-----------------+---------------+----------------+-----------------+
| employees | employee_comps_2k | PRIMARY | 34676 | 23729 | 2 | 10947 | 0 |
+---------------+-------------------+------------+--------------+-----------------+---------------+----------------+-----------------+
1 row in set (0.00 sec)
-- 可以看到employees.employee_comps_2k这个表的 索引的压缩比（在INNODB中索引即数据）；
-- 在page_size=2K只有一个压缩表的时候，INNODB_CMP和INNODB_CMP_PER_INDEX的值是一样的，并且能够知道是哪个表的情况
-
innodb_cmp_per_index_enabled这个参数默认关闭，开启对性能有影响 -
key_block_size=16的含义
- 假设
innodb_page_size = 16K - 设置
key_block_size = 16是有意义的 key_block_size的设置不影响压缩本身（只和数据本身以及zlib算法有关），只是确定压缩后的数据存放的页大小- 字段类型如果是
varchar，text等类型的数据，压缩的效果还是比较明显的 - 设置
row_format=compressed就会压缩数据，是否压缩和设置key_block_size没有关系
所以key_block_size=16的设置是有意义的，因为数据还是进行了压缩，压缩后的数据存放在16K大小的页中
- 假设
1.3.2 压缩后的存储以及性能
从上图可以得到如下信息：
innodb_page_size=16k的的数据设置key_block_size=16是可以压缩的，且效果比较明显；- 并不是
key_block_size设置的越小，压缩率就越高，上图中 8K 和 4K 的压缩率几乎一样； - 在启用压缩后， 16K 和 8K 的插入性能 要好于 原来 未压缩 的插入性能，所以 启用了压缩，性能不一定会变差 ；
- 在I/O Bound（IO密集型）的业务场景下，减少I/O操作的次数对性能提升比较明显。
key_block_size的设置的值（ 经验值 ）通常为 innodb_page_size 的 1/2
在MySQL的官方文档中，上面（包括之前谈的）都称之为InnoDB Table Compression，其实不够准确，因为他是 基于页的压缩
1.3.3 透明表空间压缩
官方文档
透明表空间压缩 官方测试
在MySQL官方文档中， 透明表空间压缩 称为 InnoDB Page Compression 以区别于原来的 InnoDB Table Compression ，但是他们其实 都是基于页（Page）的压缩 。
透明表空间压缩 应该写成 InnoDB Transparent Page Compression 更为贴切。
1.3.3.1 透明表空间压缩的创建
-- 透明表空间压缩的创建
mysql> create table trans_test_1 (a int ) compression="zlib"; -- 使用zlib的压缩算法
Query OK, 0 rows affected, 1 warning (0.14 sec) -- 存在warning
mysql> create table trans_test_2 (a int ) compression="lz4"; -- 使用lz4的压缩算法
Query OK, 0 rows affected, 1 warning (0.13 sec)
mysql> alter table trans_test_2 compression="zlib";
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> optimize table trans_test_2; -- 官方文档中提及如果是已存在的表，需要执行optimize table操作
+------------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+------------------------+----------+----------+-------------------------------------------------------------------+
| burn_test.trans_test_2 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| burn_test.trans_test_2 | optimize | status | OK |
+------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set, 1 warning (0.18 sec) -- 还是有warning
mysql> show create table trans_test_1\G
*************************** 1. row ***************************
Table: trans_test_1
Create Table: CREATE TABLE `trans_test_1` (
`a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMPRESSION='zlib'
1 row in set (0.00 sec)
-- zlib的压缩比更高
-- lz4的速度更快
在上述创建过程中，并 没有指定页大小 ，而是使用了文件系统（filesystem）层中 稀疏文件 的特性，来达到压缩的目的。
1.3.3.2 稀疏文件
Wiki介绍
如上图所示，可以简单的理解为，文件中数据连续为0的部分不占用磁盘空间
# 创建一个零时的，且数据部分全0的文件
shell> dd of=sparse-file bs=1k seek=5120 count=0 # 创建5M大小，内容全为0的文件
0+0 records in
0+0 records out
0 bytes (0 B) copied, 6.7872e-05 s, 0.0 kB/s # 无数据写入到磁盘
shell> ls -hl sparse-file
-rw-r--r--. 1 root root 5.0M Jan 5 00:07 sparse-file # 显示该文件大小为5M
shell> du --block-size=1 sparse-file # 检查文件占用多少空间
0 sparse-file # 显示占用空间为0
- 压缩后，原来16K的数据压缩成了4K；
- 剩余的12K空间用特殊的字符填充（比如说是0）；
- 在写入文件系统时调用
Punching holes写入，实则只写入4K的数据； - 被填充的12K的空间，可以提供给后序的插入，更新等使用；
- 从innodb的角度看还是16K的页大小，只是文件系统知道该页只需要4K就能够存储（对innodb是透明的）；
- SpaceID 和 PageNumber 的读取方式没有改变（细节由文件系统屏蔽）；
- 由于文件系统的快大小是4K，所以压缩后存储的空间也是4K对齐的
- 比如16K压缩成了 10K ，那就需要 3个4K 去存储
mysql> desc information_schema.INNODB_SYS_TABLESPACES;
+----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| SPACE | int(11) unsigned | NO | | 0 | |
| NAME | varchar(655) | NO | | | |
| FLAG | int(11) unsigned | NO | | 0 | |
| FILE_FORMAT | varchar(10) | YES | | NULL | |
| ROW_FORMAT | varchar(22) | YES | | NULL | |
| PAGE_SIZE | int(11) unsigned | NO | | 0 | |
| ZIP_PAGE_SIZE | int(11) unsigned | NO | | 0 | |
| SPACE_TYPE | varchar(10) | YES | | NULL | |
| FS_BLOCK_SIZE | int(11) unsigned | NO | | 0 | | -- 文件系统的块大小
| FILE_SIZE | bigint(21) unsigned | NO | | 0 | | -- 文件大小
| ALLOCATED_SIZE | bigint(21) unsigned | NO | | 0 | | -- 文件实际分配的大小
| COMPRESSION | varchar(5) | NO | | | |
+----------------+---------------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
1.3.3.3 系统以及文件系统的支持
-
操作系统以及内核
- RHEL7 kernel >= 3.10.0-123
- Debian 7 kernel >= 3.2
- Ubuntu 12.04LTS kernel >= 3.2
- Ubuntu 14.0.4LTS kernel >= 3.13
- Oracle和Suse可以参考官方文档
-
文件系统
- 支持Hole Punch
- 比如XFS，EXT4，NTFS等
1.3.3.4 关于warning
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Warning | 138 | InnoDB: Punch hole not supported by the file system or the tablespace page size is not large enough. Compression disabled |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 原因1是我的内核版本太低，不支持
-- 原因2是因为general方式安装的mysql不支持透明压缩，需要自己编译
-- http://bugs.mysql.com/bug.php?id=77974
二. 索引组织表（IOT）
在InnoDB存储引擎中，表都是根据
主键顺序组织存放的，这种存储方式的表称为索引组织表（index organized table），或者叫聚集索引（clustered index）
- 每张表都
必须有一个主键 - 根据
主键的值构造一棵B+树 - 这棵B+树的
叶子节点（leaf page）存放所有的记录（Row） 非叶子节点（Non-leaf page）存放的主键和指针（ 若干个{主键，指针}组成一个非页节点 ）- 这里的
指针其实就是PageNumber（这里 不需要SpaceID ，因为SpaceID对应的是 ibd文件 ，我们现在是在 ibd文件内部 查找数据）
- 这里的
2.1 主键
如果创建表的时候 没有指定主键 ，则InnoDB会按照如下方式选择或创建主键：
- 判断表中是否有
非空的唯一索引，如果有，该列即为主键 ；- 如果存在
多个非空唯一索引，以创建表时第一个定义的非空唯一索引为准，而 不是（columns）定义的顺序
- 如果存在
- 如果
上述条件都不符合，则InnoDB自动创建一个6字节大小的指针；
2.2 索引组织表与堆表
2.2.1 堆表
堆表将索引和数据分开（如MyISAM），索引中叶子节点存放的是数据的位置，而不是数据本身
2.2.2 索引组织表
2.2.2.1 索引组织表
索引组织表将索引和数据放在了一起，索引的叶子节点（leaf page）存放了所有完整的记录（Row）。
索引即数据，数据即索引
注意：
非叶子节点（Non-leaf page）中不会存放所有的数据（Row）的{主键, PageNumber}，而是从叶子节点（leaf page）中选出一个数据的主键，将这个主键和该页的PageNumber填入到非叶节点（Non-leaf page）中- 从逻辑上看，是
一棵B+树，但是从物理上看都是每个页（非叶子节点和叶子节点）通过指针串在一起，使得逻辑有序。
2.2.2.2 二级索引
二级索引中的
叶子节点不存放数据本身，而是存放主键。
2.2.3 查询数据对比
- 堆表查询
- 索引组织表以及二级索引查询
2.2.4 Page的空间申请
- 叶子节点（leaf page） 由
leaf page segment进行申请空间 - 非叶子节点（Non-leaf page） 由
Non-leaf page segment进行申请空间,所以索引由两个段组成- leaf page segment
- Non-leaf page segment
段（segment）是由区（extent）组成，申请空间就按照区（extent）进行申请（一般情况下一次申请4个区）
三. 主键测试
3.1 多个唯一非空键
mysql> create table test_key (
-> a int,
-> b int not null,
-> c int not null,
-> unique key(a),
-> unique key(c),
-> unique key(b)
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> insert into test_key values(1,2,3),(4,5,6),(7,8,9);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_key;
+------+---+---+
| a | b | c |
+------+---+---+
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
+------+---+---+
3 rows in set (0.00 sec)
mysql> select *, _rowid from test_key; -- _rowid是主键值
+------+---+---+--------+
| a | b | c | _rowid |
+------+---+---+--------+
| 1 | 2 | 3 | 3 | -- 可以发现，这里的主键是c
| 4 | 5 | 6 | 6 |
| 7 | 8 | 9 | 9 |
+------+---+---+--------+
3 rows in set (0.00 sec)
mysql> create table test_key_2 (
-> a varchar(8), -- 使用varchar类型
-> b varchar(8) not null,
-> c varchar(8) not null,
-> unique key(a),
-> unique key(c),
-> unique key(b)
-> );
Query OK, 0 rows affected (0.15 sec)
mysql> insert into test_key_2 values('a','b','c'),('d','e','f'),('g','h','i');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test_key_2;
+------+---+---+
| a | b | c |
+------+---+---+
| a | b | c |
| d | e | f |
| g | h | i |
+------+---+---+
3 rows in set (0.00 sec)
mysql> select *, _rowid from test_key_2;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list' -- 报错了
-- _rowid只能是在key的类型为整型时才有效
-- 方法一
mysql> select * from information_schema.columns where table_name="test_key_2" and column_key="PRI"\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: burn_test
TABLE_NAME: test_key_2
COLUMN_NAME: c -- 该列的列名是 c
ORDINAL_POSITION: 3
COLUMN_DEFAULT: NULL
IS_NULLABLE: NO
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 8
CHARACTER_OCTET_LENGTH: 32
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_general_ci
COLUMN_TYPE: varchar(8)
COLUMN_KEY: PRI -- 该列是主键
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
1 row in set (0.00 sec)
-- 方法二
mysql> desc test_key_2;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a | varchar(8) | YES | UNI | NULL | |
| b | varchar(8) | NO | UNI | NULL | |
| c | varchar(8) | NO | PRI | NULL | | -- key 是PRI ，就可以知道 c 列是主键
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
3.2 系统定义主键（系统rowid）
- 当用户表中没有显示的
指定主键，且没有非空唯一键时，系统会自定义一个主键（6个字节，int型，全局，隐藏）
mysql> create table test_key_3(
-> a int,
-> b int,
-> c int);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into test_key_3 values(1,2,3),(4,5,6),(7,8,9);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select *,_rowid from test_key_3;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list' --这里无法用_rowid查看，因为系统rowid对用户是透明的
假设有 a 和 b 两张表都使用了
系统定义的主键，则系统定义的主键的ID不是在表内进行单独递增的，而是全局递增。
该系统的rowid是定义在ibdata1.ibd中的sys_rowid中，全局自增
6个字节表示的数据量为2^48，通常意义上是够用的
注意：强烈建议自己显示定义主键
四. 页的结构
- File Header
在一个页中不仅仅只有记录，还有 File Header , Page Header , File Trailer 等
五. 记录
5.1 ROW_FORMAT
• REDUDENT： 兼容老版本的InnoDB，MySQL 4.1版本之前
• COMPACT： MySQL 5.6 版本的默认格式
• COMPRESSED：支持压缩
• DYNAMIC：大对象记录优化， MySQL 5.7 版本默认格式
5.2 COMPACT 结构
• variable string length list
- 变长字段 列表，表示有 多少个 变长字段，且序号 逆序 显示
• NULL flag
- 是否有NULL值
• rowid
- B+树索引键值
• trx id
- 事物ID，6个字节
• roll pointer
- 回滚指针，7个字节
5.3 COMPACT 示例
5.3.1 创建mytest表
- 创建mytest表，格式为compact，且没有显示定义主键和非空唯一键，故使用系统定义的ROWID。并插入三条记录。
create table mytest (
t1 varchar(10),
t2 varchar(10),
t3 char(10),
t4 varchar(10))
engine=innodb row_format=compact;
insert into mytest values (‘a’,‘bb’,‘bb’,‘ccc’);
insert into mytest values (‘d’,‘ee’,‘ee’,‘fff’);
insert into mytest values (‘d’,NULL,NULL,‘fff’);
5.3.2 将mytest表结构进行dump
图中红色部分对应第一条记录，黄色部分对应第二条记录，深蓝色部分对应第三条记录
- 5.3.3 将红色部分对应的第一条记录进行解析
- variable string length list
- 03 02 01 ，表示有三个变长字段
（varchar，varbinary，text等），且逆序存放（为了提高CPU的cache的命中率）
- 03 02 01 ，表示有三个变长字段
- NULL flag
- 00 , 这条记录中不存在NULL
- Record Header
- 5个字节，比较底层（比如看该记录有没有被删除）
- RowID
- 主键ID,
00 00 00 2b 68 00，从这个值可以看出，不是每张表从1开始递增的，是全局的ROWID
- 主键ID,
- TransactionID
事物ID
- Roll Pointer
- 回滚指针
5.3.3 char 和 varchar 的区别
在 多字节字符集 （如UTF8mb4）下：
- char(N) 中存储的数据的长度
范围是 N ~ 4N ，当存储数据的长度M， 未达到N 时，则填充空格（0x20）,且空格的长度取最小的长度N-M，而不是4N-M - varchar(N) 则
不填充空格
注意：char数据类型本来是定长数据，但是在多字节字符集下，表现的行为和varchar类似，失去了原来的优势，当数据更新变长后可能无法原地更新
5.3.4 原地更新（in place update）
- 原地更新不会占用
新的存储空间 - 非原地更新需要删除
（物理删除）原来的空间的数据，然后将更新后的数据插入到页的后面 - 删除的数据的空间，会插入到
Free_List链表的头部 - 原地更新
不会触发页的分裂
Free_List是将页中被删除的空间串联在一起（组成一个 链表 ），当有数据被插到页内时，先看一下Free_list中 第一个空间 的大小，如果空间合适，就将该记录插入到第一个空间中去，如果不合适，直接插入到页的尾部的剩余空间。（ 不会去看Free_list的第二个空间 ）
当该页的数据被插满了，不会马上进行分页，而是进行
reorganize操作，即将页内的数据在内存 中进行整理，然后覆盖原来的页（不影响性能），所以InnoDB不需要碎片整理。
5.3.5 Reorganize
5.4 DYNAMIC
-
DYNAMIC相比COMPACT，优化了大对象记录的存储。
- 假设有一条记录有A，B，C，D 四列，其中D列的是text类型，且含有2W个字节的长度。
-
COMPACT
- COMPACT会存储text中的前768个字节的数据，剩余的数据通过20个字节的指针指向溢出页
相对COMPACT，DYNAMIC在一个页中存储的 记录数 更多（因为有768字节的prefix，一条记录的字节假设是800字节，那16K的页只能存放20条记录，而之前我们测算可以存放80条记录），这样一来，B+树的高度可能会变高，读取的IO次数可能会变多。
一个页能存放的记录越多，则性能越优
六. heap number
-
heap_number表示页中每个 记录插入 的顺序 序号
-
假设 插入 的数据是 a, b, d, e, g ；则对应的 heap_number 为 2，3，4，5，6
-0 和 1 被 infimum 和 supermum 所使用
- infimum 对应最小的heap_number - supermum 对应最大的heap_number，随着数据的插入，该值会更新-
update对heap_number没有影响
-
heap_number是物理的，存储在row的 record_header 字段中
-
-- 终端1
mysql> create table test_heap(a int primary key); Query OK, 0 rows affected (0.13 sec)
mysql> insert into test_heap values (1); -- 插入a=1的记录
Query OK, 1 row affected (0.03 sec)
mysql> begin; -- 开启事物
Query OK, 0 rows affected (0.00 sec)
mysql> delete from test_heap where a=1; -- 删除a=1的记录，此时加上了锁
Query OK, 1 row affected (0.00 sec)
-- 终端2
mysql>mysql> show variables like "%innodb_status_output_locks%";
+---------------------------- + -------+
| Variable_name | Value |
+---------------------------- + -------+
| innodb_status_output_locks | OFF |
+---------------------------- + -------+
mysql> set global innodb_status_output_locks=1;
Query OK, 0 rows affected (0.00 sec)
mysql> pager less -- 使用类似linux中的less命令方式进行查看，可上下翻页
PAGER set to 'less'
mysql> show engine innodb status\G
-- -----------省略其他输出-------------
TABLE LOCK table `burn_test`.`test_heap` trx id 16943 lock mode IX
RECORD LOCKS space id 122 page no 3 n bits 72 index PRIMARY of table `burn_test`.`test_heap` trx id 16943 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000001; asc ;; -- 插入的主键a=1，8的二进制1000，最高位为1，表示有符号的
1: len 6; hex 00000000422f; asc B/;; -- 0x422f的 十进制就是16943 ，表示事物id（trx id）
2: len 7; hex 2c000000450dcf; asc , E ;; -- roll pointer（回滚指针）
-- -----------省略其他输出-------------
-- space id 122 : 表空间id是122
-- page no 3 : 对应的页号是3 （表示第4个页，是root页）
-- heap no 2 : heap number是2 （表示是新插入的第一条记录）
-- heap no = 1 的一种情况
-- 终端1
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_heap where a>1 for update;
Empty set (0.00 sec)
-- 终端2
mysql> show engine innodb status\G
-- -----------省略其他输出-------------
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;; -- 一条伪记录
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 00000000422e; asc B.;;
2: len 7; hex ab000000470110; asc G ;;
-- -----------省略其他输出-------------
