为什么修改表后ibd文件这么大
最近碰到一个很奇怪的问题, 在更改一个 MyISAM 表为 InnoDB 存储引擎后, 表数据文件变的非常大. 原始的表为 MyISAM 引擎, 数据文件和索引文件如下所示:
-rw-r----- 1 mysql mysql 8.7K Aug 17 13:34 user_follow.frm
-rw-r----- 1 mysql mysql 1.9G Aug 17 13:35 user_follow.MYD
-rw-r----- 1 mysql mysql 1.5G Aug 17 13:35 user_follow.MYI
表结构如下, 统计信息显示大概有 6.2kw记录, 平均行大小 31 字节:
CREATE TABLE `user_follow` (
`uid` int(11) unsigned NOT NULL DEFAULT '0',
`username` varchar(255) NOT NULL DEFAULT '',
`followuid` int(11) unsigned NOT NULL DEFAULT '0',
`fusername` varchar(255) NOT NULL DEFAULT '',
`bkname` varchar(255) NOT NULL DEFAULT '',
`status` tinyint(1) NOT NULL DEFAULT '0',
`mutual` tinyint(1) NOT NULL DEFAULT '0',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`uid`,`followuid`),
KEY `idx_fuid` (`followuid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
表数据大致如下:
+----------+-----------------------+-----------+--------------------+--------+--------+--------+------------+
| uid | username | followuid | fusername | bkname | status | mutual | dateline |
+----------+-----------------------+-----------+--------------------+--------+--------+--------+------------+
| 6235195 | crrapitt87 | 13644917 | coormayi | | 0 | 0 | 1347433303 |
| 17425562 | 1402453108yoa | 13919966 | zopczczc0 | | 0 | 0 | 1347433673 |
+----------+-----------------------+-----------+--------------------+--------+--------+--------+------------+
Name: user_follow
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 62833131
Avg_row_length: 31
Data_length: 1948544532
Max_data_length: 281474976710655
Index_length: 1563675648
Data_free: 0
在使用下面任何一种 sql 更改存储引擎的时候, innodb 的数据文件会变的非常大:
alter table user_follow engine = innodb;
create table udb_innodb like user_follow;
insert into udb_innodb select * from user_follow;
udb_innodb 表概要信息如下, 行数的统计信息接近6亿多, 增加了快10倍, ibd 文件也增长到 268G :
Name: udb_innodb
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 632893731
Avg_row_length: 440
Data_length: 278985179136
Max_data_length: 0
Index_length: 4585422848
Data_free: 7340032
-rw-rw---- 1 mysql mysql 8.7K Aug 28 11:10 udb_innodb.frm
-rw-rw---- 1 mysql mysql 268G Aug 28 11:50 udb_innodb.ibd
另: MySQL 版本为 5.5.25-log 以及 Percona Server 版本 5.5.23-rel25.3-240 和 5.5.33-rel31.1-566;
为什么会出现这种问题? idb 文件里肯定有很多未知(或者多余)的数据, 使用 optimize table udb_innodb 来整理下udb_innodb表的碎片数据, 结果如我们所期望的, ibd文件的大小变成4.5G, 这也从侧面验证了原先的 idb文件中有 260多G都为碎片信息. 我们使用 hexdump 命令看看268G的ibd文件中到底存了什么信息, 我们采用二分查找方法找出正常数据和异常数据的边界信息:
hexdump -C -v -s 1776742187 -n 1048576 udb_innodb.ibd >/tmp/udb.txt
从ibd 文件的1776742187的位置开始, udb.txt 文件的内容主要包含下面两类:
69efffeb eb 10 e4 3d 2c 3d 46 3d 60 11 18 00 63 14 a6 0b |...=,=F=`...c...|
69effffb f3 ab 8a 4b c7 9e cd 51 c8 00 01 a7 c0 00 01 a7 |...K...Q........|
69f0000b 15 00 01 a7 14 00 00 00 7b 82 25 70 da 45 bf 00 |........{.%p.E..|
69f0001b 00 00 00 00 00 00 00 00 00 06 7f 00 02 00 89 80 |................|
69f0002b 03 00 00 00 00 00 7d 00 05 00 00 00 01 00 00 00 |......}.........|
69f0003b 00 00 00 00 00 00 01 00 00 00 00 00 00 0f ee 00 |................|
69f0004b 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
69f0005b 00 00 00 01 00 02 00 1a 69 6e 66 69 6d 75 6d 00 |........infimum.|
69f0006b 02 00 0b 00 00 73 75 70 72 65 6d 75 6d 00 00 11 |.....supremum...|
69f0007b ff f3 02 1a 4f 7c 02 19 2f 23 00 01 a7 67 00 00 |....O|../#...g..|
69f0008b 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
69f0009b 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
63c06806b 02 00 0b 00 00 73 75 70 72 65 6d 75 6d 00 0a 09 |.....supremum...|
63c06807b 00 00 10 ff f0 02 1d 79 f2 02 09 61 a7 00 00 00 |.......y...a....|
63c06808b 40 3e 33 93 00 05 00 04 0c c3 e4 ba 8e e8 8e b9 |@>3.............|
63c06809b e5 a9 b7 77 65 6c 6f 76 65 32 30 30 38 80 80 55 |...welove2008..U|
63c0680ab 58 fe a5 00 00 00 00 00 00 00 00 00 00 00 00 00 |X...............|
63c0680bb 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
从 supremum 行开始, 即为异常的数据, 16进制内容都显示 00, 从地址 69f0006b 开始第二索引非叶子节点的行格式(Secondary key - Non-Leaf Pages), 转换为十进制为 1777336427, 以及第二索引叶子节点的行格式(Secondary key - Leaf-Pages), 从这方面看我们可以认为在 268G 的 ibd 文件中, 这两类内容占据了绝大多数. 下面详细分析上述两类的记录内容.
Innodb 行记录格式详见: http://blog.jcole.us/2013/01/10/the-physical-structure-of-records-in-innodb/
先来分析第一类的内容:
69f0006b 02 00 0b 00 00 73 75 70 72 65 6d 75 6d 00 00 11 |.....supremum...|
69f0007b ff f3 02 1a 4f 7c 02 19 2f 23 00 01 a7 67 00 00 |....O|../#...g..|
73 75 70 72 65 6d 75 6d 表示 supremum类型的记录
00 00 11 ff f3 表示5字节的 record header 信息, ff f3 为相对下条记录的偏移值, 已经远大于一个页的大小.
02 1a 4f 7c 对应 Cluster key Fields, 主键信息, 由于 PKV 的原因, 不会在之前显示 Secondary key 的列值信息.
02 19 2f 23 对应 集合索引中的 followuid 列信息
00 01 a7 67 子叶数(Btree+ 叶子节点数)
再来看第二类内容:
63c06806b 02 00 0b 00 00 73 75 70 72 65 6d 75 6d 00 0a 09 |.....supremum...|
63c06807b 00 00 10 ff f0 02 1d 79 f2 02 09 61 a7 00 00 00 |.......y...a....|
63c06808b 40 3e 33 93 00 05 00 04 0c c3 e4 ba 8f e8 8e b8 |@>3.............|
63c06809b e5 a9 b7 77 65 6c 6f 76 65 32 30 30 38 80 80 55 |...welove2008..U|
63c0680ab 58 fe a5 00 00 00 00 00 00 00 00 00 00 00 00 00 |X...............|
63c0680bb 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
73 75 70 72 65 6d 75 6d 表示 supremum类型的记录
00 0a 09 表示变长字段的长度, 逆序, 即 bkname 长度为0, fusername 长度为 10, username长度为 9
00 00 10 ff f0 表示5字节的 record header 信息, ff f0 为相对下条记录的偏移值, 远大于一个页的大小
02 1d 79 f2 表示 Cluster key 信息, 即 uid 列值信息, 由于 PKV的原因, 不会在之前显示 followuid的列值
02 09 61 a7 表示 集合索引中的 followuid 列值信息
00 00 00 40 3e 33 6字节的 transaction id 信息
93 00 05 00 04 0c c3 7字节的 roll pointer 信息
e4 ba 8e ........ 剩余为非索引列的信息
按照 jcole 的说法, non-leaf 页因为不是 MVCC特性的, 所以不会保存 transaction ID 和 Roll pointer 信息. 另外表结构中的三个 varchar 列都声明为 NOT NULL, null 标志位可以省略. 对于非叶子节点而言一般都会有子节点(左子树、右字数等), 当然接近一个页的大小(ff f3)也浪费了很多空间.
我们详细看看第二类信息, 十六进制 uid 02 1d 79 f2 转换为十进制 35486194, followuid 的 02 09 61 a7 转换为十进制 34169255, 我们通过 uid 和 followuid 查询表中的记录:
mysql root@[localhost:s3306 test] > select * from user_follow where uid = 35486194 and followuid = 34169255;
+----------+-----------+-----------+------------+--------+--------+--------+------------+
| uid | username | followuid | fusername | bkname | status | mutual | dateline |
+----------+-----------+-----------+------------+--------+--------+--------+------------+
| 35486194 | 亏莸婷 | 34169255 | welove2008 | | 0 | 0 | 1431895717 |
+----------+-----------+-----------+------------+--------+--------+--------+------------+
1 row in set (0.00 sec)
bkname, fusername, username 三列的长度对应上述16进制的内容, 分别为 0, a, 9; 非索引列的部分中, e4 ba 8e e8 8e b9 e5 a9 b7 (9 字节长)对应utf8编码的汉字, 77 65 6c 6f 76 65 32 30 30 38(10字节长)对应 fusername, 剩下的两个字节 80 80 对应 status 和 mutual字段(备注: 因为int, tinyint 有正负数表示, 所以数字0 从80开始表示,81表示1, -1表示7f等等, 如果是unsigned字段则从00开始)，最后4字节 55 58 fe a5对应dateline字段:
mysql root@[localhost:s3306 test] > select 0xe4ba8fe88eb8e5a9b7, 0x77656c6f766532303038, hex(1431895717);
+----------------------+------------------------+-----------------+
| 0xe4ba8ee88eb9e5a9b7 | 0x77656c6f766532303038 | hex(1431895717) |
+----------------------+------------------------+-----------------+
| 亏莸婷 | welove2008 | 5558FEA5 |
+----------------------+------------------------+-----------------+
目前看来行记录的格式是正确的, 不过有一个问题需要引起注意, 那就是行记录的header信息 00 00 10 ff f0, ff f0 为下条记录的偏移量, 其大小为 65520 字节, 远大于一个页的大小(16KB), 而实际上存储该行需要的大小为42字节(从 00 0a 09 到 55 58 fe a5)， 不过我们继续查看 63c06806b 之后的 16KB字节信息还是出现 supremum 信息, 如下:
63c06c06b 02 00 0b 00 00 73 75 70 72 65 6d 75 6d 00 00 09 |.....supremum...|
63c06c07b 00 00 10 ff f0 02 1d 79 f2 02 09 61 a6 00 00 00 |.......y...a....|
63c06c08b 40 3e 33 93 00 05 00 04 0c d6 e4 ba 8e e8 8e b9 |@>3.............|
从这方面来看 ff f0 的偏移值是错误的, 不能大于一个页(16KB), 当然到这里可以明白为什么转换后的ibd文件是 268G, 因为部分行记录使用 65520字节空间来存储 42字节的空间. 不过遗憾的是没有找到为什么会出现这种情况的线索， 根据笔者的测试user_follow表在小于5kw左右的时候不会引起该问题, 所以猜想可能是因为行记录过多, Innodb 需要维护索引和事务信息而引起的一个bug, 值得一提的是 percona-server 5.6.21(可能5.6开始)版本的测试一切正常.
再来看看统计信息:
Rows: 632893731
Avg_row_length: 440
Data_length: 278985179136
这里avg_row_length 和 rows 都是一个预估值, data_length可以比较精确的得到, 所以 Row =~ data_length / avg_row_length 算下来就有 6亿多记录;
如何来避免这种问题? 原则上讲, 上述问题不会引起表数据的丢失, 只是占用很多额外的空间, 引起该问题的原因可能是在一个事务里执行了大表的更改, 所以可以通过分组的方式修改表结构避免该问题的出现, 比如使用 pt-online-schema-change工具分组修改或者按条件插入数据, 比如 insert into … select .. where uid < .. ; 当然如果手工修改表后碰到了该问题可以使用 optimize 释放空间.