【innodb阅读笔记】之索引组织表

标签：level MySQL 笔记索引 innodb offset type page

一、组织索引表主键规则

在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: 91

4. 页

在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行记录。

标签：level,MySQL,笔记,索引,innodb,offset,type,page
From： https://blog.csdn.net/weixin_43262384/article/details/144256401

【innodb阅读笔记】之索引组织表

一、组织索引表主键规则

二、innodb逻辑存储结构

1. 表空间

2. 段

3. 区

4. 页

5. 行

相关文章

赞助商

阅读排行

【innodb阅读笔记】之 索引组织表

一、组织索引表主键规则

二、innodb逻辑存储结构

1. 表空间

2. 段

3. 区

4. 页

5. 行

相关文章

赞助商

阅读排行

【innodb阅读笔记】之索引组织表