内容简介：InnoDB备忘录 - 逻辑存储
本文主要介绍 InnoDB 存储引擎的 逻辑存储结构
逻辑存储结构
Tablespace
- Tablespace是InnoDB存储引擎逻辑存储结构的
最高层，所有数据都存放在Tablespace中 - 分类
-
System Tablespace -
Separate Tablespace -
General Tablespace
-
System Tablespace
-
System Tablespace即我们常见的共享表空间，变量为innodb_data_file_path，一般为ibdata1文件 - 里面存放着
undo logs，change buffer，doublewrite buffer等信息（后续将详细介绍），在没有开启file-per-table的情况下，还会包含所有表的索引和数据信息 - 没有开启
file-per-table时存在的问题- 所有的表和索引都会在
System Tablespace中，占用空间会越来越大 -
碎片越来越多（如truncate table时，占用的磁盘空间依旧保留在System Tablespace）
- 所有的表和索引都会在
mysql> SHOW VARIABLES LIKE 'innodb_data_file_path'; +-----------------------+------------------------+ | Variable_name | Value | +-----------------------+------------------------+ | innodb_data_file_path | ibdata1:12M:autoextend | +-----------------------+------------------------+ 1 row in set (0.01 sec) mysql> SHOW VARIABLES LIKE '%datadir%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.01 sec) mysql> system sudo ls -lh /var/lib/mysql/ibdata1 [sudo] password for zhongmingmao: -rw-r----- 1 mysql mysql 76M May 6 20:00 /var/lib/mysql/ibdata1
Separate Tablespace
- MySQL参考手册中并没有
Separate Tablespace这个术语，这里只为了行文方便，表示在开启file-per-table的情况下，每个表有自己独立的表空间，变量为innodb_file_per_table - 里面存放在
每个表的索引和数据信息，后缀一般为.ibd - 默认初始大小为
96KB - 好处
- 避免
System Tablespace越来越大 - 减少碎片（
truncate table，操作系统会自动回收空间）
- 避免
mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | t | +----------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'innodb_file_per_table'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE '%datadir%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.01 sec) mysql> system sudo ls -lh /var/lib/mysql/test total 112K -rw-r----- 1 mysql mysql 61 Apr 28 10:18 db.opt -rw-r----- 1 mysql mysql 8.4K May 7 17:03 t.frm -rw-r----- 1 mysql mysql 96K May 7 17:03 t.ibd
General Tablespace
-
15.7.9 InnoDB General TablespacesGeneral Tablespace是MySQL 5.7.6引入的新特性，具体内容请参照下面链接
Segment
- Segment分为三种
-
Leaf node segment：数据段，B+Tree的叶子节点 -
Non-Leaf node segment：索引段，B+Tree的非叶子节点 -
Rollback segment：回滚段，存放undo log，默认是位于System Tablespace
-
- InnoDB中的
B+Tree索引，由Leaf node segment和Non-Leaf node segment组成 - 一个Segment由
多个Extent和Page组成
Extent
-
Extent是由连续页（默认页大小为16KB）组成，在默认页大小时，为64个连续页，大小为64*16KB=1MB- 不同页大小：
4KB*256or8KB*128or16KB*64or32KB*64or64KB*64
- 不同页大小：
- 为了保证
页的连续性，InnoDB可以一次性从磁盘申请4个Extent - 为了
节省磁盘空间，如表的数据量很小（Leaf node segment和Non-Leaf node segment都很小）或Rollback segment，Segment一开始不会直接申请Extent，而是先用32个碎片页（用于叶子节点）来存放数据，用完之后才继续对Extent(1MB)的申请
创建表
# 创建表
mysql> CREATE TABLE t (
-> a INT NOT NULL AUTO_INCREMENT,
-> b VARCHAR(7000),
-> PRIMARY KEY (a)
-> ) ENGINE=INNODB ROW_FORMAT=COMPACT CHARSET=LATIN1;
Query OK, 0 rows affected (0.03 sec)
mysql> system sudo ls -lh /var/lib/mysql/test/t.ibd
-rw-r----- 1 mysql mysql 96K May 7 17:09 /var/lib/mysql/test/t.ibd
# 查看表空间信息 $ sudo python py_innodb_page_info.py -v /var/lib/mysql/test/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
# 查看表空间文件十六进制（Vim,%!xxd） # page offset=3 0000c000: 18f8 857f 0000 0003 ffff ffff ffff ffff ................ 0000c010: 0000 0000 4087 2c32 45bf 0000 0000 0000 ....@.,2E....... 0000c020: 0000 0000 0111 0002 0078 8002 0000 0000 .........x...... 0000c030: 0000 0005 0000 0000 0000 0000 0000 0000 ................ 0000c040: 0000 0000 0000 0000 0155 0000 0111 0000 .........U...... 0000c050: 0002 00f2 0000 0111 0000 0002 0032 0100 .............2.. 0000c060: 0200 0d69 6e66 696d 756d 0001 000b 0000 ...infimum...... 0000c070: 7375 7072 656d 756d 0000 0000 0000 0000 supremum........ 0000c080: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0000c090: 0000 0000 0000 0000 0000 0000 0000 0000 ................ ...... 0000ffd0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0000ffe0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0000fff0: 0000 0000 0070 0063 18f8 857f 4087 2c32 .....p.c....@.,2
-
py_innodb_page_info.py是姜承尧大神用 Python 写的用来分析表空间中的各页类型和信息的工具，向大神致敬 -
b VARCHAR(7000)能保证一个页中最多存放两条记录，2 < 16KB/7000B < 3 - 单独表空间
t.ibd的默认大小为96KB - 单独表空间
t.ibd目前只有一个B+Tree叶子节点（page level <0000>），还有两个可用页（Freshly Allocated Page）-
page offset=3，(16K)*3 = 0xc000，该页范围为0xc000 ~ 0xffff - 理论上
0xc078为第一个记录的开始，此时尚未插入任何记录，所以为0（行记录格式ROW_FORMAT后续将详细介绍）
-
插入2条记录
# 插入2条记录
mysql> INSERT INTO t SELECT NULL,REPEAT('a',7000);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t SELECT NULL,REPEAT('a',7000);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> system sudo ls -lh /var/lib/mysql/test/t.ibd
[sudo] password for zhongmingmao:
-rw-r----- 1 mysql mysql 96K May 7 17:26 /var/lib/mysql/test/t.ibd
mysql>
# 查看表空间信息 $ sudo python py_innodb_page_info.py -v /var/lib/mysql/test/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
# 查看表空间文件十六进制（Vim,%!xxd） # page offset=3 0000c000: f185 f4c0 0000 0003 ffff ffff ffff ffff ................ 0000c010: 0000 0000 4087 697e 45bf 0000 0000 0000 ....@.i~E....... 0000c020: 0000 0000 0111 0002 375a 8004 0000 0000 ........7Z...... 0000c030: 1bf1 0002 0001 0002 0000 0000 0000 0000 ................ 0000c040: 0000 0000 0000 0000 0155 0000 0111 0000 .........U...... 0000c050: 0002 00f2 0000 0111 0000 0002 0032 0100 .............2.. 0000c060: 0200 1d69 6e66 696d 756d 0003 000b 0000 ...infimum...... 0000c070: 7375 7072 656d 756d 589b 0000 0010 1b71 supremumX......q 0000c080: 8000 0001 0000 0014 0869 cc00 0002 1001 .........i...... 0000c090: 1061 6161 6161 6161 6161 6161 6161 6161 .aaaaaaaaaaaaaaa 0000c0a0: 6161 6161 6161 6161 6161 6161 6161 6161 aaaaaaaaaaaaaaaa ...... 0000dbd0: 6161 6161 6161 6161 6161 6161 6161 6161 aaaaaaaaaaaaaaaa 0000dbe0: 6161 6161 6161 6161 6158 9b00 0000 18e4 aaaaaaaaaX...... 0000dbf0: 7f80 0000 0200 0000 1408 6acd 0000 01a4 ..........j..... 0000dc00: 0110 6161 6161 6161 6161 6161 6161 6161 ..aaaaaaaaaaaaaa ...... 0000f730: 6161 6161 6161 6161 6161 6161 6161 6161 aaaaaaaaaaaaaaaa 0000f740: 6161 6161 6161 6161 6161 6161 6161 6161 aaaaaaaaaaaaaaaa 0000f750: 6161 6161 6161 6161 6161 0000 0000 0000 aaaaaaaaaa...... ...... 0000ffd0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0000ffe0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0000fff0: 0000 0000 0070 0063 f185 f4c0 4087 697e .....p.c....@.i~ ......
- 表空间大小依旧是
96KB，2条记录可以完全放入page offset=3的B+Tree叶子节点中 - 第1条记录位于page offset=3的页，地址范围为
0xc078 ~ 0xdbe8，占用7025 Byte - 第2条记录位于page offset=3的页，地址范围为
0xdbe9 ~ 0xf759，占用7025 Byte - 此时，
page offset=3的页已经无法再容纳下一条同样长度的记录，但此时还有2个可用页，可用于B+Tree的分裂（此时只有叶子节点）
插入第3条记录
# 插入第3条记录
mysql> INSERT INTO t SELECT NULL,REPEAT('a',7000);
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> system sudo ls -lh /var/lib/mysql/test/t.ibd
-rw-r----- 1 mysql mysql 96K May 7 17:40 /var/lib/mysql/test/t.ibd
# 查看表空间信息 $ sudo python py_innodb_page_info.py -v /var/lib/mysql/test/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 <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
# 查看表空间文件十六进制（Vim,%!xxd） # page offset=4 00010000: 669d db54 0000 0004 ffff ffff 0000 0005 f..T............ 00010010: 0000 0000 4087 e2e4 45bf 0000 0000 0000 ....@...E....... 00010020: 0000 0000 0111 0002 375a 8004 1bf1 1b71 ........7Z.....q 00010030: 0000 0005 0000 0001 0000 0000 0000 0000 ................ 00010040: 0000 0000 0000 0000 0155 0000 0000 0000 .........U...... 00010050: 0000 0000 0000 0000 0000 0000 0000 0100 ................ 00010060: 0200 1d69 6e66 696d 756d 0002 000b 0000 ...infimum...... 00010070: 7375 7072 656d 756d 589b 0000 0010 fff0 supremumX....... 00010080: 8000 0001 0000 0014 0869 cc00 0002 1001 .........i...... 00010090: 1061 6161 6161 6161 6161 6161 6161 6161 .aaaaaaaaaaaaaaa ...... 00011bd0: 6161 6161 6161 6161 6161 6161 6161 6161 aaaaaaaaaaaaaaaa 00011be0: 6161 6161 6161 6161 6158 9b00 0000 1800 aaaaaaaaaX...... 00011bf0: 0080 0000 0200 0000 1408 6acd 0000 01a4 ..........j..... 00011c00: 0110 6161 6161 6161 6161 6161 6161 6161 ..aaaaaaaaaaaaaa ...... 00013740: 6161 6161 6161 6161 6161 6161 6161 6161 aaaaaaaaaaaaaaaa 00013750: 6161 6161 6161 6161 6161 0000 0000 0000 aaaaaaaaaa...... 00013760: 0000 0000 0000 0000 0000 0000 0000 0000 ................ ...... 00013fd0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 00013fe0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 00013ff0: 0000 0000 0070 0063 669d db54 4087 e2e4 .....p.cf..T@... # page offset=5 00014000: 946a 9d01 0000 0005 0000 0004 ffff ffff .j.............. 00014010: 0000 0000 4087 e2e4 45bf 0000 0000 0000 ....@...E....... 00014020: 0000 0000 0111 0002 375a 8004 0000 0000 ........7Z...... 00014030: 1bf1 0005 0000 0002 0000 0000 0000 0000 ................ 00014040: 0000 0000 0000 0000 0155 0000 0000 0000 .........U...... 00014050: 0000 0000 0000 0000 0000 0000 0000 0100 ................ 00014060: 0200 1d69 6e66 696d 756d 0003 000b 0000 ...infimum...... 00014070: 7375 7072 656d 756d 589b 0000 0010 1b71 supremumX......q 00014080: 8000 0002 0000 0014 086a cd00 0001 a401 .........j...... 00014090: 1061 6161 6161 6161 6161 6161 6161 6161 .aaaaaaaaaaaaaaa ...... 00015bd0: 6161 6161 6161 6161 6161 6161 6161 6161 aaaaaaaaaaaaaaaa 00015be0: 6161 6161 6161 6161 6158 9b00 0000 18e4 aaaaaaaaaX...... 00015bf0: 7f80 0000 0300 0000 1408 6fd0 0000 0211 ..........o..... 00015c00: 0110 6161 6161 6161 6161 6161 6161 6161 ..aaaaaaaaaaaaaa ....... 00017740: 6161 6161 6161 6161 6161 6161 6161 6161 aaaaaaaaaaaaaaaa 00017750: 6161 6161 6161 6161 6161 0000 0000 0000 aaaaaaaaaa...... 00017760: 0000 0000 0000 0000 0000 0000 0000 0000 ................ ...... 00017fd0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 00017fe0: 0000 0000 0000 0000 0000 0000 0000 0000 ................ 00017ff0: 0000 0000 0070 0063 946a 9d01 4087 e2e4 .....p.c.j..@...
- 插入第3条记录后，表空间大小依旧为
96KB，因为插入之前还有两个可用页，有足够的空间让B+Tree分裂 -
page offset=3的page level为<0001>，表示这是倒数第一层的B+Tree索引节点 - 实际的记录存放在
page offset为4和5的B+Tree叶子节点，即上一操作的可用页 - 第1条记录位于page offset=4的页，地址范围为
0x10078 ~ 0x11be8，占用7025 Byte - 第2条记录位于page offset=4的页，地址范围为
0x11be9 ~ 0x13759，占用7025 Byte- 第2条记录同时也位于page offset=5的页，地址范围为
0x14078 ~ 0x15be8，占用7025 Byte
- 第2条记录同时也位于page offset=5的页，地址范围为
- 第3条记录位于page offset=5的页，地址范围为
0x15be9 ~ 0x17759，占用7025 Byte - 此时，
page offset=4和page offset=5的页都已经无法再容纳同样长度的记录，而且表空间初始的96KB中已无可用页- 在插入同样长度的记录，表空间会增大
创建存储过程
mysql> DELIMITER //
mysql> CREATE PROCEDURE load_t (count INT UNSIGNED)
-> BEGIN
-> DECLARE s INT UNSIGNED DEFAULT 1;
-> DECLARE c VARCHAR(7000) DEFAULT REPEAT('a',7000);
-> WHILE s <= count DO
-> INSERT INTO t SELECT NULL,c;
-> SET s=s+1;
-> END WHILE;
-> END;
-> //
Query OK, 0 rows affected (0.09 sec)
mysql> DELIMITER ;
插入60条记录
# 通过调用存储过程，插入60条记录 mysql> CALL load_t(60); Query OK, 1 row affected (0.67 sec) mysql> system sudo ls -lh /var/lib/mysql/test/t.ibd -rw-r----- 1 mysql mysql 592K May 8 01:58 /var/lib/mysql/test/t.ibd
# 查看表空间信息 $ sudo python py_innodb_page_info.py -v /var/lib/mysql/test/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 <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 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
- 此时，表空间大小依旧小于
Extent大小（1MB），目前还是通过碎片页来申请数据空间 - 上一步操作中，默认的表空间大小已无法再容纳新的同样长度的记录，且已使用了
2个B+Tree叶子节点，申请Extent前可以再使用30个B+Tree叶子节点，所以再插入60条记录（每页只能容纳2条记录） - 此时处于
临界状态，B+Tree叶子节点为32个，再插入同样长度的记录时，将进行Extent的申请
插入第64条记录
# 插入第64条记录 mysql> CALL load_t(1); Query OK, 1 row affected (0.05 sec) mysql> system sudo ls -lh /var/lib/mysql/test/t.ibd [sudo] password for zhongmingmao: -rw-r----- 1 mysql mysql 2.0M May 8 02:14 /var/lib/mysql/test/t.ibd
# 查看表空间信息 $ sudo python py_innodb_page_info.py -v /var/lib/mysql/test/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 <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 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> ...... page offset 00000000, page type <Freshly Allocated Page> page offset 00000040, page type <B-tree Node>, page level <0000> page offset 00000000, page type <Freshly Allocated Page> ...... page offset 00000000, page type <Freshly Allocated Page> page offset 00000000, page type <Freshly Allocated Page> Total number of page: 128: Freshly Allocated Page: 91 Insert Buffer Bitmap: 1 File Space Header: 1 B-tree Node: 34 File Segment inode: 1
- 插入第64条记录时，就需要进行
Extent的申请，从page offset=0x40处申请一个Extent（0x40*16KB=1MB），之前的部分空间作为可用页，此时表空间大小为2MB
Page
-
Page是InnoDB磁盘管理的最小单位，变量为innodb_page_size
mysql> SHOW VARIABLES LIKE 'innodb_page_size'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | innodb_page_size | 16384 | +------------------+-------+ 1 row in set (0.17 sec)
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
