mysql 表 区 块 页_MySQL中的表-区

MySQL中的区

无论何种情况，区的大小总是1M，但是刚建表表分配的比较少。

1.创建一个数据库

mysql> create database vastdata;

Query OK, 1 row affected (0.01 sec)

2.使用这个数据库

mysql> use vastdata

Database changed

3.创建一张表

mysql> create table t1 (col1 int not null auto_increment,col2 varchar(7000),primary key(col1))engine=InnoDB;

Query OK, 0 rows affected (0.01 sec)

4.插入两条数据 其中repeat('a',7000)为将a连续7000次，也就是7000个a。所以我们将一条记录视为7K大小。

在MySQL中一个数据页的大小是16KB，所以两条记录就会占用一个页

mysql> insert t1 select null,repeat('a',7000);

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

mysql> insert t1 select null,repeat('a',7000);

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

5.查看t1表所对应的数据文件大小

mysql> system ls -lh /data/mydb/vastdata/t1.ibd

-rw-rw---- 1 mysql mysql 96K Oct 17 01:09 /data/mydb/vastdata/t1.ibd

6.使用py_innodb_page_info.py工具查看数据文件中页的分布

[root@vast python]# python py_innodb_page_info.py -v /data/mydb/vastdata/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     #系统中Insert Buffer Bitmap对应的页数

File Space Header: 1        #系统中File Space Header对应的页数

B-tree Node: 1                #存放数据的页数

File Segment inode: 1     系统中File Segment inode对应的页数

7.查看当前t1对应的数据文件大小

-rw-rw---- 1 mysql mysql 96K Oct 17 01:09 /data/mydb/vastdata/t1.ibd

这里会发现，t1表大小只有96K，没有直接分配1M。

8.再次插入一行记录，大小约为7KB

mysql> insert t1 select null,repeat('a',7000);

Query OK, 1 row affected (0.01 sec)

Records: 1  Duplicates: 0  Warnings: 0

9.再次查看数据文件大小，依然为96K

mysql> system ls -lh /data/mydb/vastdata/t1.ibd

-rw-rw---- 1 mysql mysql 96K Oct 17 01:15 /data/mydb/vastdata/t1.ibd

10.查看一下当前此数据文件的内部页分布

[root@vast python]# python py_innodb_page_info.py -v /data/mydb/vastdata/t1.ibd

page offset 00000000, page type

page offset 00000001, page type

page offset 00000002, page type

page offset00000003, page type , page level <0001>

page offset00000004, 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

我们可以的看到，空闲页没有了。其中00000004,00000005为存放数据块00000003为根所对应的块。

11.创建一个存储过程，目的是简化插入。

mysql> delimiter //

mysql> create procedure load_t1(count INT UNSIGNED)

-> begin

-> declare s int unsigned default 1;

-> declare c varchar(7000) default repeat('a',7000);

-> while s <= count DO

-> insert into t1 select NULL,c;

-> set s = s+1;

-> end while;

-> end;

-> //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

12.执行这个存储过程，60则表示在表中插入60行，每行都是7000个a

mysql> call load_t1(60);

Query OK, 1 row affected (0.06 sec)

13.查看表有多少行记录，可见为63行

mysql> select count(*) from t1;

+----------+

| count(*) |

+----------+

|       63 |

+----------+

1 row in set (0.00 sec)

14.查看数据文件大小

mysql> system ls -lh /data/mydb/vastdata/t1.ibd

-rw-rw---- 1 mysql mysql 576K Oct 17 01:39 /data/mydb/vastdata/t1.ibd

63 * 7KB = 441KB 两行记录需要一个数据页，故这63行需要32个数据页

15.查看数据文件中页的分布，验证一下猜想

[root@vast python]# python py_innodb_page_info.py -v /data/mydb/vastdata/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

这里看到存放数据的数据页为33个，其中一个为根。32+1=33

16.在插入一行记录

mysql> call load_t1(1);

Query OK, 1 row affected (0.01 sec)

17.查看数据文件大小，发现这时，数据文件为2M

mysql> system ls -lh /data/mydb/vastdata/t1.ibd

-rw-rw---- 1 mysql mysql 2.0MOct 17 01:50 /data/mydb/vastdata/t1.ibd

mysql>

18.查看数据文件中页的分布。

[root@vast python]# python py_innodb_page_info.py  /data/mydb/vastdata/t1.ibd

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

可见这时，剩余91个空闲页。

结论： 创建表的默认大小是96K。 innodb会先用32个页大小的碎片页(fragment page)来存放数据。 碎片页耗尽后，才连续申请64个连续页。64*16KB=1024KB=1M

目的：对于一些小表，或者是undo这类的段，可以在开始时申请较少的空间，节省磁盘容量的开销。