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的次数。
|