vinin note
0%

MySQL笔记[InnoDB]表

发表于 阅读次数： Valine：
本文字数： 9k 阅读时长 ≈ 8 分钟

一、索引组织表

表是根据主键顺序组织存放的，称为索引组织表，在Innodb引擎中，每个表都必须有主键，如果建表是没有创建主键，则以以下方式来获取主键：
• 如果表中有非空唯一索引，则该列为主键；如果存在多列，则以定义索引的顺序的第一列；
• 如果不符合上述条件，Innodb引擎会自己创建一个6字节大小的指针；

如果主键为单列的情况，通过_rowid可以查看；

二、逻辑存储结构

所有数据被逻辑存储在一个空间里面，称为表空间；表空间由段（segment）、区（extend）、页（page）组成。

如下图：

innodb8

1、表空间

默认10M，名称为ibdata1的文件
参数innodb_data_file_path=datafile1[;datafile2]..
innodb_data_file_path=/db/ibdata1:2000M;/db/ibdata2:2000M:autextend
参数：innodb_file_per_table，每个表产生一个独立的表空间，命令为：表名.ibd
独立表空格仅存储表的数据、索引、插入缓冲BITMAP信息，其它信息还是存储在共享表空间中；

2、段

段分为数据段，索引段和回滚段；

3、区

区为连续页组成的空间，一个区的大小为1M，为了页的连续性，一般会连续申请4-5个区，页的大小一般为16K，所以一个区有64个页。

1.0.x版本后可以通过key_block_size参数来设置页的大小，页中的数据是压缩的；
1.2.x版本后通过innodb_page_size参数来设置页的大小，页中的数据是没有压缩的；

InnoDB引擎对区的申请方式：
先用32个页大小的碎片页来存储数据，使用完这些页后才会连续申请64页；

创建表t1:
CREATE TABLE t1(
col1 INT NOT NULL AUTO_INCREMENT,
col2 VARCHAR(7000),
PRIMARY KEY(col1)
) ENGINE=InnoDB;

查看创建的表大小：

innodb9

看到t1.ibd刚好是96K；

插入两条记录，应该在同一个页中；
INSERT INTO t1 SELECT NULL,REPEAT(‘a’,7000);

使用py_innodb_page_info.py工具查看：

D:\Python27\python.exe E:/gitrepos/pyscripts/innodb/py_innodb_page_info.py -v D:\wamp\bin\mysql\mysql5.6.17\data\test\t1.ibd
page offset 00000000, page type
page offset 00000001, page type
page offset 00000002, page type
page offset 00000003, page type , page level <0000>
page offset 00000000, page type
page offset 00000000, page type
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

Pageoffset 为3的页即为数据页，0表示叶子节点；所有的记录都在一个页中，没有非叶子节点；

再插入一条记录，就会产生一个非叶子节点：
注意：因为有插入缓冲，可能马上运行，还会看不到变化，当Master Thread任务执行完后，约几秒左右即可看到变化。
D:\Python27\python.exe E:/gitrepos/pyscripts/innodb/py_innodb_page_info.py -v D:\wamp\bin\mysql\mysql5.6.17\data\test\t1.ibd
page offset 00000000, page type
page offset 00000001, page type
page offset 00000002, page type
page offset 00000003, page type , page level <0001>
page offset 00000004, page type , page level <0000>
page offset 00000005, page type , page level <0000>
Total number of page: 6:
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 3
File Segment inode: 1

pageoffset为3的页，level由0变成了1；

再连续插入60条记录，一共63条记录，共32页；
先建立一个存储过程来插入记录:

DELIMITER //
CREATE PROCEDURE load_t1 (cnt INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1 ;
DECLARE c VARCHAR (7000) DEFAULT REPEAT(‘a’, 7000) ;
WHILE s <= cnt DO
INSERT INTO t1 SELECT NULL,c ;
SET s = s + 1 ;
END WHILE ;
END ;
//
DELIMITER ;
CALL load_t1(60);

此时，t1.ibd的大小为576K，再执行py_innodb_page_info查看：
D:\Python27\python.exe E:/gitrepos/pyscripts/innodb/py_innodb_page_info.py -v D:\wamp\bin\mysql\mysql5.6.17\data\test\t1.ibd
page offset 00000000, page type
page offset 00000001, page type
page offset 00000002, page type
page offset 00000003, page type , page level <0001>
page offset 00000004, page type , page level <0000>
page offset 00000005, page type , page level <0000>
page offset 00000006, page type , page level <0000>
page offset 00000007, page type , page level <0000>
page offset 00000008, page type , page level <0000>
page offset 00000009, page type , page level <0000>
page offset 0000000a, page type , page level <0000>
page offset 0000000b, page type , page level <0000>
page offset 0000000c, page type , page level <0000>
page offset 0000000d, page type , page level <0000>
page offset 0000000e, page type , page level <0000>
page offset 0000000f, page type , page level <0000>
page offset 00000010, page type , page level <0000>
page offset 00000011, page type , page level <0000>
page offset 00000012, page type , page level <0000>
page offset 00000013, page type , page level <0000>
page offset 00000014, page type , page level <0000>
page offset 00000015, page type , page level <0000>
page offset 00000016, page type , page level <0000>
page offset 00000017, page type , page level <0000>
page offset 00000018, page type , page level <0000>
page offset 00000019, page type , page level <0000>
page offset 0000001a, page type , page level <0000>
page offset 0000001b, page type , page level <0000>
page offset 0000001c, page type , page level <0000>
page offset 0000001d, page type , page level <0000>
page offset 0000001e, page type , page level <0000>
page offset 0000001f, page type , page level <0000>
page offset 00000020, page type , page level <0000>
page offset 00000021, page type , page level <0000>
page offset 00000022, page type , page level <0000>
page offset 00000023, page type , page level <0000>
Total number of page: 36:
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 33
File Segment inode: 1

可能看到B-tree Node的节点为33个，叶子节点为32个，即已经使用完32个碎片页，此时再插入一条记录，再查看：
D:\Python27\python.exe E:/gitrepos/pyscripts/innodb/py_innodb_page_info.py -v D:\wamp\bin\mysql\mysql5.6.17\data\test\t1.ibd
page offset 00000000, page type
page offset 00000001, page type
page offset 00000002, page type
page offset 00000003, page type , page level <0001>
page offset 00000004, page type , page level <0000>
page offset 00000005, page type , page level <0000>
page offset 00000006, page type , page level <0000>
page offset 00000007, page type , page level <0000>
page offset 00000008, page type , page level <0000>
page offset 00000009, page type , page level <0000>
page offset 0000000a, page type , page level <0000>
page offset 0000000b, page type , page level <0000>
page offset 0000000c, page type , page level <0000>
page offset 0000000d, page type , page level <0000>
page offset 0000000e, page type , page level <0000>
page offset 0000000f, page type , page level <0000>
page offset 00000010, page type , page level <0000>
page offset 00000011, page type , page level <0000>
page offset 00000012, page type , page level <0000>
page offset 00000013, page type , page level <0000>
page offset 00000014, page type , page level <0000>
page offset 00000015, page type , page level <0000>
page offset 00000016, page type , page level <0000>
page offset 00000017, page type , page level <0000>
page offset 00000018, page type , page level <0000>
page offset 00000019, page type , page level <0000>
page offset 0000001a, page type , page level <0000>
page offset 0000001b, page type , page level <0000>
page offset 0000001c, page type , page level <0000>
page offset 0000001d, page type , page level <0000>
page offset 0000001e, page type , page level <0000>
page offset 0000001f, page type , page level <0000>
page offset 00000020, page type , page level <0000>
page offset 00000021, page type , page level <0000>
page offset 00000022, page type , page level <0000>
page offset 00000023, page type , page level <0000>
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000040, page type , page level <0000>
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
page offset 00000000, page type
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

可以看到B-tree Node的页为34个，但Freshly Allocated Page的页为91个；

4、页

Innodb引擎中，常见的页类型为：
• 数据页（B-tree Node）
• undo页（undo log page）
• 系统页（system page）
• 事务数据页（Transaction system Page）
• 插入缓冲位图页（Insert Buffer Bitmap）
• 插入缓冲空闲列表页（Insert Buffer Free List）
• 未压缩的二进制大对象页（Uncompressed Blob Page）
• 压缩的二进制大对象页（Compressed Blob Page）

参考《MySQL技术内幕 -InnoDB存储引擎》整理，如侵权请联系vinin@163.com。