InnoDB数据存储猜测之INSERT

冒险王

1      背景

DBA每天要面对的是，大量的INSERT语句不断地涌入你的DBServer，随着而来的是，磁盘空间快速地缩减。那么，问题来了，INSERT语句实际上在DB中是怎样存储的？加不加索引有多大区别呢？

2      基础环境说明

操作系统：SUSELinux Enterprise Server 11
CPU型号：Intel(R)Xeon(R) CPU E5-2658 0 @ 2.10GHz
CPU线程数：8
内存容量：24G
MySQL版本：5.6.13-logMySQL Community Server (GPL)

3      工具准备

py_innodb_page_info.py（所有权来自MySQL技术内幕作者，请自己在code.google.com上搜索david-mysql-tools）

4      初始化脚本4.1      建库及建表

mysql> createdatabase wdd default charset latin1;

Query OK, 1 rowaffected (0.01 sec)

mysql> use wdd

Database changed

mysql> CREATETABLE test_innodb(name varchar(7500)) CHARACTER SET latin1;

Query OK, 0 rowsaffected (0.15 sec)

说明：使用latin1字符集，可以保证在使用varchar设置的长度为最大可使用的字节数。

4.2      导入存储过程

mysql>DELIMITER //

mysql> CREATEDEFINER=`root`@`localhost` PROCEDURE load_data(count INT UNSIGNED)

    -> BEGIN

    -> DECLARE localCount INT UNSIGNEDDEFAULT 1;

    -> DECLARE c VARCHAR(7500) DEFAULTREPEAT('a',7500);

    -> while localCount <= count DO

    -> INSERT INTO test_innodb SELECT c;

    -> SET localCount = localCount+1;

    -> END WHILE;

    -> END;

    -> //

Query OK, 0 rowsaffected (0.00 sec)

说明：改存储过程用于指定插入N记录到test_innodb表中，其中的每条记录占用7500字节（约7KB大小，innodb默认数据页占用16KB的空间，也就只能存储2条记录了）。

4.3      当前占用文件空间备注

-rw-rw---- 1mysql mysql 13K 2014-11-26 04:36 wdd/test_innodb.frm

-rw-rw---- 1mysql mysql 96K 2014-11-26 06:40wdd/test_innodb.ibd

说明：在启用innodb参数innodb_file_per_table后，创建的表默认为96K大小，该空间有定量的预留，里面可额外存储少量的数据。

5      INSERT数据插入分析5.1      INSERT 2条记录，占用1个数据页

mysql> insertinto test_innodb select repeat('a',7500);

Query OK, 1 rowaffected (0.01 sec)

Records: 1  Duplicates: 0 Warnings: 0

mysql> insertinto test_innodb select repeat('a',7500);

Query OK, 1 rowaffected (0.00 sec)

Records: 1  Duplicates: 0 Warnings: 0

mysql> systemls -lh wdd/test_innodb*

-rw-rw---- 1mysql mysql 13K 2014-11-26 04:36 wdd/test_innodb.frm

-rw-rw---- 1 mysqlmysql 96K 2014-11-26 06:40 wdd/test_innodb.ibd

原因：

       通过工具py_innodb_page_info.py来检查ibd文件，

./py_innodb_page_info.py  -v  wdd/test_innodb.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>

pageoffset 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条记录存放在1个数据页中。

5.2      额外INSERT 1条记录，数据页拆分

mysql> callload_data(1);

Query OK, 1 rowaffected (11.18 sec)

mysql> systemls -lh wdd/test_innodb*

-rw-rw---- 1mysql mysql 13K 2014-11-26 06:55 wdd/test_innodb.frm

-rw-rw---- 1mysql mysql 96K 2014-11-26 07:20 wdd/test_innodb.ibd

通过工具py_innodb_page_info.py来检查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>

pageoffset 00000003, page type <B-tree Node>, page level <0001>

pageoffset 00000004, page type <B-tree Node>, page level <0000>

pageoffset 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

也即：

5.3      再次INSERT 1条记录，数据页再拆分

mysql> callload_data(1);

Query OK, 1 rowaffected (0.03 sec)

mysql> systemls -lh wdd/test_innodb*

-rw-rw---- 1mysql mysql  13K 2014-11-26 06:55wdd/test_innodb.frm

-rw-rw---- 1mysql mysql 128K 2014-11-26 07:22 wdd/test_innodb.ibd

通过工具py_innodb_page_info.py来检查ibd文件，

page offset00000000, page type <File Space Header>

page offset00000001, page type <Insert Buffer Bitmap>

page offset00000002, 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 offset00000000, page type <Freshly Allocated Page>

Total number ofpage: 8:

FreshlyAllocated Page: 1

Insert BufferBitmap: 1

File SpaceHeader: 1

B-tree Node: 4

File Segmentinode: 1

发现占用的数据页为3个，而不是2个。可以发现，InnoDB对数据页的处理，并非填鸭式地加满为止。

5.4      清空表，插入1968条数据，B-Tree树会进行拆分

mysql>truncate test_innodb;

Query OK, 0 rowsaffected (0.15 sec)

mysql> call load_data(1968);

Query OK, 1 rowaffected (4.05 sec)

通过工具py_innodb_page_info.py来检查ibd文件，

./py_innodb_page_info.py/opt/huawei/db/data/wdd/test_innodb.ibd

Total number ofpage: 1472:

FreshlyAllocated Page: 481

Insert BufferBitmap: 1

File SpaceHeader: 1

B-tree Node: 988

File Segmentinode: 1

./py_innodb_page_info.py-v wdd/test_innodb.ibd | grep '<0001>'

page offset 00000024, page type<B-tree Node>, page level <0001>

page offset 00000025, page type<B-tree Node>, page level <0001>

./py_innodb_page_info.py-v wdd/test_innodb.ibd | grep '<0002>'

page offset 00000003, page type<B-tree Node>, page level <0002>

对应数据存储结构的图类似为：

我们大体可以知道一个事实，MySQL一个非叶子节点可以指向近上1000个叶子节点。所以，3层树结构中，最大可以存储1000*1000=100w个数据页（近16k*100w=16G空间）。由此可以推知，innodb对应的B+ Tree的高度普遍在2-4层之间。所以，MySQL的索引如果使用合理的话，可大大减少磁盘I/O的次数。