【innodb阅读笔记】之 索引组织表
一、组织索引表主键规则
在Innodb存储引擎中，表都是根据主键索引的顺序组织存放的，这种存放方式的表称为索引组织表，在innodb存储引擎表中，每个表都有一个主键，如果在创建表的时候没有显示的定义主键，则innodb会按如下方式选择或创建主键：
1. 首先判断表是否有非空唯一索引，如果有，则该列为主键，若过有多个唯一非空索引，则按照定义顺序，选择第一个为主键
2. 如果不符合上述条件，innodb存储引擎自动创建一个6字节大小的指针
创建数据库 但是没有指定主，也没有创建唯一索引
CREATE TABLE z (
a INT NOT NULL,
b INT NULL,
c INT NOT NULL,
d INT NOT NULL
);
# 插入数据
insert into z select 1, 2, 3, 4
# 查询主键报错，证明是自动生成的,
# _rowid 可以查询非自动生成的，同时不是联合索引的主键
mysql> SELECT _rowid FROM z ;
ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'
# 创建唯一索引 d的顺序在c的前面，b不是非空索引
mysql> 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)
-> );
Query OK, 0 rows affected (0.03 sec)
# 插入数据
insert into z select 1, 2, 3, 4;
insert into z select 5, 6, 7, 8;
insert into z select 9, 10, 11, 12;
# 发现选择第一个定义的非空唯一索引为数据库主键
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)
二、innodb逻辑存储结构
从Innodb存储引擎的逻辑存储结构看，所有数据都被逻辑的存放在一个空间中，称为表空间。表空间又由段、区、页组成，存储结构大致如下：
1. 表空间
表空间可以看做是innodb存储引擎逻辑结构的最高层，所有数据都存放在表空间中，如果启用了 innodb_file_per_table 的参数，则每张表存放自己独立的数据，索引，插入缓存bitMap页，其他类的数据，如回滚信息、插入缓存索引页、系统事务信息等一些数据，还存放在共享表空间ibdata1 中，即使启用了 innodb_file_per_table 参数，共享表空间还是会不断的增加其大小，现在，我们做一个实验：
# 开启独立表空间
mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
1 row in set, 1 warning (0.00 sec)
# 共享空间大小为 12MB,
# 使用刚才创建好的表结构
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)
);
# 开启显示提交事务
begin;
# 修改数据，可以添加数据 数据越多，效果越明显
mysql> update z set a = a + 1;
Query OK, 7 rows affected (0.00 sec)
Rows matched: 7 Changed: 7 Warnings: 0
# 再重复执行了1000次后，共享空间大小为 12.5MB, 证明，事务等数据信息存放在共享表空间中
# 有人或许会问，当事务回滚以后，表空间会恢复之前的大小吗？
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
# 回滚事务后，我们发现共享表空间没有变小，所以不会进行回收，
# 但是下次会判断这些数据是否可以被覆盖，如果不需要，进行标记，以提供下次使用，
# 下面是innodb存储引擎大佬写的一个插件，通过这个插件，我们可以查看表空间页的具体信息
D:\readBooks\mysql\david-mysql-tools-master\py_innodb_page_type>py_innodb_page_info.py -v D:/readBooks/mysql/t.ibd
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
2. 段
上图显示了表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。innodb存储引擎表是索引组织的，所以，索引既数据，数据既索引。那么数据段既 B+ 树的叶子节点，索引段既为 B+ 树的非叶子节点。
在innodb存储引擎中，对段的管理都是由索引自身所完成的，DBA不能也没有必要对其进行控制。
3. 区
区是由连续的页组成的，在任何情况下每个区的大小都为 1MB，为了保证区中页的连续性，Innodb存储引擎一次从磁盘申请 4 到 5 个区，在默认情况下，每个页的大小为16KB，一个区一共有 64 个连续的页。
这有一个问题，当我们创建表的时候，表的大小默认为 96 KB，区中是64个连续的页，创建的表最小也应该是1MB才对？ 这是因为，每个段在开始的时候，先用32个页大小的碎片页来存放数据，在使用完这些页之后才是64个连续页的申请，这样做的目的是，对于一些小表，可以在开始的时候申请比较少的空间，节省空间，防止空间浪费。
# 创建数据表
mysql> create table t1 (
-> col1 int not null auto_increment,
-> col2 varchar(7000),
-> primary key(col1)
-> ) engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)
# 数据文件大小
D:\ProgramData\MySQL\MySQL Server 5.7\MySQL_data\innodb_test>dir
2024/12/05 10:58 98,304 t1.ibd
# 插入数据
mysql> insert t1 select null, repeat('a', 7000);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>
mysql> insert t1 select null, repeat('a', 7000);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
# 通过 py_innodb_page_info 查看表空间情况
D:\ProgramData\MySQL\py_innodb_page_type>py_innodb_page_info.py -v
"D:/ProgramData/MySQL/MySQL Server 5.7/MySQL_data/innodb_test/t1.ibd"
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 6:
Freshly Allocated Page: 2
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 1
File Segment inode: 1
# page offset 00000003 这个表示的是数据页，page level 等于0， 表示叶子节点，
# 因为现在所有数据都处于一个页当中，所以没有非叶子节点
# 我们再插入 2 条数据，再次查询表空间情况
D:\ProgramData\MySQL\py_innodb_page_type>py_innodb_page_info.py -v
"D:/ProgramData/MySQL/MySQL Server 5.7/MySQL_data/innodb_test/t1.ibd"
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>
page offset 00000004, page type <B-tree Node>, page level <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
Total number of page: 6:
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 3
File Segment inode: 1
# 此时，我们发现 产生了两个 page level 等于 0 的叶子节点，
# 同时产生了一个 page level 等于 1 的非叶子节点
# 我们再插入60次数据，当前数据库数据保存为63条
mysql> select count(1) from t1;
+----------+
| count(1) |
+----------+
| 63 |
+----------+
1 row in set (0.00 sec)
# 可以看到 我们在插入63条数据的时候，数据库的大小还是小于 1MB
D:\ProgramData\MySQL\MySQL Server 5.7\MySQL_data\innodb_test>dir
2024/12/05 11:13 606,208 t1.ibd
# 通过查看表空间情况，可以观察到 b-tree node页，一共 33个，
# 除去一个非叶子节点，一共32个非叶子节点，也就是说已经有32个碎片页了
D:\ProgramData\MySQL\py_innodb_page_type>py_innodb_page_info.py -v
"D:/ProgramData/MySQL/MySQL Server 5.7/MySQL_data/innodb_test/t1.ibd"
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>
page offset 00000004, page type <B-tree Node>, page level <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
page offset 00000006, page type <B-tree Node>, page level <0000>
page offset 00000007, page type <B-tree Node>, page level <0000>
page offset 00000008, page type <B-tree Node>, page level <0000>
page offset 00000009, page type <B-tree Node>, page level <0000>
page offset 0000000a, page type <B-tree Node>, page level <0000>
page offset 0000000b, page type <B-tree Node>, page level <0000>
page offset 0000000c, page type <B-tree Node>, page level <0000>
page offset 0000000d, page type <B-tree Node>, page level <0000>
page offset 0000000e, page type <B-tree Node>, page level <0000>
page offset 0000000f, page type <B-tree Node>, page level <0000>
page offset 00000010, page type <B-tree Node>, page level <0000>
page offset 00000011, page type <B-tree Node>, page level <0000>
page offset 00000012, page type <B-tree Node>, page level <0000>
page offset 00000013, page type <B-tree Node>, page level <0000>
page offset 00000014, page type <B-tree Node>, page level <0000>
page offset 00000015, page type <B-tree Node>, page level <0000>
page offset 00000016, page type <B-tree Node>, page level <0000>
page offset 00000017, page type <B-tree Node>, page level <0000>
page offset 00000018, page type <B-tree Node>, page level <0000>
page offset 00000019, page type <B-tree Node>, page level <0000>
page offset 0000001a, page type <B-tree Node>, page level <0000>
page offset 0000001b, page type <B-tree Node>, page level <0000>
page offset 0000001c, page type <B-tree Node>, page level <0000>
page offset 0000001d, page type <B-tree Node>, page level <0000>
page offset 0000001e, page type <B-tree Node>, page level <0000>
page offset 0000001f, page type <B-tree Node>, page level <0000>
page offset 00000020, page type <B-tree Node>, page level <0000>
page offset 00000021, page type <B-tree Node>, page level <0000>
page offset 00000022, page type <B-tree Node>, page level <0000>
page offset 00000023, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 37:
Freshly Allocated Page: 1
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 33
File Segment inode: 1
# 现在我们再插入一条数据，发现表空间大小变为 2MB
# 因为已经用完32个碎片页，新的页区会采用区的方式进行空间的申请，
D:\ProgramData\MySQL\MySQL Server 5.7\MySQL_data\innodb_test>dir
2024/12/05 11:19 2,097,152 t1.ibd
# 此时 在查看表空间情况，会出现有很多空闲页
Freshly Allocated Page: 914. 页
在innodb存储引擎中，页是innodb磁盘管理的最小单位，默认每个页的大小为16KB，通过参数 innodb_page_size 可以将页的大小设置为 4KB、8KB、16KB，若设置完成，每个页大小都为 innodb_page_size，不可再次对其发生更改。
在Innodb存储引擎中，常见的页有：数据页、undo页、系统页、事务数据页、插入缓存位图页、插入缓存空闲列表页、未压缩的二进制大数据页、压缩的二进制数据页。
# 页默认大小
mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set, 1 warning (0.00 sec)5. 行
Innodb存储引擎是面向列。也就是说数据时按照行进行存放的，每个页存放的行记录是有硬性定义的，最多存放 16KB / 2 - 200 行的记录，既7992行记录。