|
最近，在工作中遇到了MySQL中如何存储长度较长的字段类型问题，于是花了一周多的时间抽空学习了一下，并且记录下来。 注：文中所指的大数据指的是长度较长的数据字段，包括varchar/varbinay/text/blob。 Compact行格式我们首先来看一下行格式为Compact是如何存储大数据的： mysql> select version();+-----------+| version() |+-----------+| 5.1.73 |+-----------+1 row in set (0.01 sec)mysql> show table status like 'row'\G;*************************** 1. row *************************** Name: row Engine: InnoDB Version: 10 Row_format: Compact Rows: 1 Avg_row_length: 81920 Data_length: 81920Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2017-01-04 21:46:02 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)我们建立一张测试表，插入数据： CREATE TABLE `row` ( `content` varchar(65532) NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET=latin1mysql> insert into row(content) select repeat('a',65532);Query OK, 1 row affected (0.03 sec)Records: 1 Duplicates: 0 Warnings: 0[root@localhost mysql]# python py_innodb_page_info.py -v com/row.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>page offset 00000003, page type <B-tree Node>, page level <0000>page offset 00000004, page type <Uncompressed BLOB Page>page offset 00000005, page type <Uncompressed BLOB Page>page offset 00000006, page type <Uncompressed BLOB Page>page offset 00000007, page type <Uncompressed BLOB Page>Total number of page: 8:Insert Buffer Bitmap: 1Uncompressed BLOB Page: 4File Space Header: 1B-tree Node: 1File Segment inode: 1可以看出，第4页的<B-tree Node>, page level <0000>格式为数据页，存放着MySQL的行数据。<Uncompressed BLOB Page>可以理解为MySQL存放大数据的地方，暂且叫作外部存储页。Compact格式没有将大数据全部放在数据页中，而是将一部分数据放在了外部存储页中。那么，是全部数据在外部存储页中，还是一部分数据。假如是一部分数据，这一部分是多少呢？ 我们使用hexdump -Cv row.ibd查看一下数据页<B-tree Node>, page level <0000>，也就是第4页： 3073 0000c000 8c 25 17 57 00 00 00 03 ff ff ff ff ff ff ff ff |.%.W....????????|3074 0000c010 00 00 00 00 00 07 3a b8 45 bf 00 00 00 00 00 00 |......:?E?......|3075 0000c020 00 00 00 00 00 02 00 02 03 a6 80 03 00 00 00 00 |.........?......|3076 0000c030 00 7f 00 05 00 00 00 01 00 00 00 00 00 00 00 00 |................|3077 0000c040 00 00 00 00 00 00 00 00 00 13 00 00 00 02 00 00 |................|3078 0000c050 00 02 00 f2 00 00 00 02 00 00 00 02 00 32 01 00 |...?.........2..|3079 0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00 |...infimum......|3080 0000c070 73 75 70 72 65 6d 75 6d 14 c3 00 00 10 ff f1 00 |supremum.?...??.|3081 0000c080 00 00 00 04 03 00 00 00 00 13 12 80 00 00 00 2d |...............-|3082 0000c090 01 10 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |..aaaaaaaaaaaaaa|3083 0000c0a0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|3084 0000c0b0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|3085 0000c0c0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|........3128 0000c370 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|3129 0000c380 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|3130 0000c390 61 61 00 00 00 02 00 00 00 04 00 00 00 26 00 00 |aa...........&..|3131 0000c3a0 00 00 00 00 fc fc 00 00 00 00 00 00 00 00 00 00 |....??..........|3132 0000c3b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|3133 0000c3c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|3134 0000c3d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|......4093 0000ffc0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|4094 0000ffd0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|4095 0000ffe0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|4096 0000fff0 00 00 00 00 00 70 00 63 01 a1 6c 2b 00 07 3a b8 |.....p.c.?l+..:?|我们可以看出，数据页中存储了一部分数据，算下来一共是768字节，然后剩余部分存储在外部存储页中。那么数据页与外部存储页、外部存储页与外部存储页是如何连接在一起的呢？ 我们观察这一行： 3130 0000c390 61 61 00 00 00 02 00 00 00 04 00 00 00 26 00 00 |aa...........&..|3131 0000c3a0 00 00 00 00 fc fc 00 00 00 00 00 00 00 00 00 00 |................|这一行是前缀768字节的结尾。注意最后的20个字节： - 00 00 00 02：4字节，代表外部存储页所在的space id
- 00 00 00 04：4字节，代表第一个外部页的Page no
- 00 00 00 26：4字节，值为38，指向blob页的header
- 00 00 00 00 00 00 fc fc：8字节，代表该列存在外部存储页的总长度。此处的值为64764，加上前缀768正好是65532。（注意一点，虽然表示BLOB长度的是8字节，实际只有4个字节能使用，所有对于BLOB字段，存储数据的最大长度为4GB。）
验证下第一个外部存储页的头部信息： 4097 00010000 cd c3 b6 8e 00 00 00 04 00 00 00 00 00 00 00 00 |?ö.............|4098 00010010 00 00 00 00 00 06 b8 a2 00 0a 00 00 00 00 00 00 |......??........|4099 00010020 00 00 00 00 00 02 00 00 3f ca 00 00 00 05 61 61 |........??....aa|4100 00010030 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|......前38个字节为File Header(关于InnoDB数据页的详细结构请参见《MySQL技术内幕 InnoDB存储引擎》4.4)，这个简单提一下： - cd c3 b6 8e：4字节，该页的checksum。
- 00 00 00 04：4字节，页偏移，此页为表空间中的第5个页。
- 00 00 00 00：4字节，当前页的上一个页。此页为<Uncompressed BLOB Page>，所以没有上一页。
- 00 00 00 00：4字节，当前页的下一个页。此页为<Uncompressed BLOB Page>，所以没有下一页。
- 00 00 00 00 00 06 b8 a2：8字节，该页最后被修改的日志序列位置LSN。
- 00 0a：2字节，页类型，0x000A代表BLOB页。
- 00 00 00 00 00 00 00 00：8字节，略过。
- 00 00 00 02：页属于哪个表空间，此处指表空间的ID为2。
之后是4字节的00 00 3f ca，这里的值为16330，代表此BLOB页的有效数据的字节数。00 00 00 05代表下一个BLOB页的page number。 我们看最后一个<Uncompressed BLOB Page>，第8个页： 7169 0001c000 fa 78 9b 27 00 00 00 07 00 00 00 00 00 00 00 00 |?x.'............|7170 0001c010 00 00 00 00 00 07 3a b8 00 0a 00 00 00 00 00 00 |......:?........|7171 0001c020 00 00 00 00 00 02 00 00 3d 9e ff ff ff ff 61 61 |........=.????aa|7172 0001c030 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|7173 0001c040 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|......最后一页的有效数据大小为0x00003d9e=15774，768+16330*3+15774 = 65532字节，符合初始插入数据的大小。
由于这是最后一个<Uncompressed BLOB Page>，所以指向下一个<Uncompressed BLOB Page>的指针为ff ff ff ff。 由此我们可以很清晰的看出数据页与BLOB页的连接关系(引用淘宝数据库月报上的一张图)：
![]() 我们来再看一个比较有意思的例子。： CREATE TABLE `testblob` ( `blob1` blob NOT NULL, `blob2` blob NOT NULL, `blob3` blob NOT NULL, `blob4` blob NOT NULL, `blob5` blob NOT NULL, `blob6` blob NOT NULL, `blob7` blob NOT NULL, `blob8` blob NOT NULL, `blob9` blob NOT NULL, `blob10` blob NOT NULL, `blob11` blob NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;mysql> insert into testblob select repeat('a',1000),repeat('b',1000),repeat('c',1000),repeat('d',1000),repeat('e',1000),repeat('f',1000),repeat('g',1000),repeat('h',1000),repeat('i',1000),repeat('j',1000),repeat('k',1000);ERROR 1030 (HY000): Got error 139 from storage engine我们建立一张新表，有11个blob字段。然后向每个字段插入1000字节的数据，MySQL会提示ERROR 1030 (HY000): Got error 139 from storage engine，什么意思呢？ InnoDB是以B+树来组织数据的，假如每一行数据都占据一整个Page页，那么B+树将退化为单链表，所以InnoDB规定了一个Page必须包含两行数据。也就是一行数据存储在Page上的大小大概为8000字节。
而上面的例子，一行数据有11个1000字节的数据，Page层肯定放不下，所以在Page层留下768*11=8448字节，已经超过了8000字节，所以MySQL会提示ERROR 1030 (HY000): Got error 139 from storage engine。我们很轻松的定义一个字段，来存储11000个字节，但是却无法将他们分成11个字段来存储，有点意思！ 那么如何解决上面的问题呢？ - 将行格式转为接下来要说的Dynamic格式。此种格式只用20字节指向外部存储空间。
- 将多个blob字段转为一个blob字段。多个字段可以用数组存储，然后json_encode打包进blob。
我们向表中插入一条有效记录： mysql> insert into testblob(blob1,blob2,blob3,blob4,blob5,blob6,blob7,blob8,blob9) select repeat('a',8000),repeat('b',8000),repeat('c',8000),repeat('d',8000),repeat('e',8000),repeat('f',8000),repeat('g',8000),repeat('h',8000),repeat('i',8000);Query OK, 1 row affected (0.12 sec)Records: 1 Duplicates: 0 Warnings: 0[root@localhost mysql]# python py_innodb_page_info.py -v com/testblob.ibdpage offset 00000000, page type <File Space Header>page offset 00000001, page type <Insert Buffer Bitmap>page offset 00000002, page type <File Segment inode>page offset 00000003, page type <B-tree Node>, page level <0000>page offset 00000004, page type <Uncompressed BLOB Page>page offset 00000005, page type <Uncompressed BLOB Page>page offset 00000006, page type <Uncompressed BLOB Page>page offset 00000007, page type <Uncompressed BLOB Page>page offset 00000008, page type <Uncompressed BLOB Page>page offset 00000009, page type <Uncompressed BLOB Page>page offset 0000000a, page type <Uncompressed BLOB Page>page offset 0000000b, page type <Uncompressed BLOB Page>page offset 0000000c, page type <Uncompressed BLOB Page>Total number of page: 13:Insert Buffer Bitmap: 1Uncompressed BLOB Page: 9File Space Header: 1B-tree Node: 1File Segment inode: 1我们可以看出这一行数据有9个外部存储页，而我们一共就插入了9列数据，是不是当每一列的数据在page页放不下，都单独申请一个外部存储页，而互相之前不共享外部存储页。我们看一下page页的结构就知道了： 3130 0000c390 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa| 3131 0000c3a0 61 61 61 61 00 00 00 05 00 00 00 04 00 00 00 26 |aaaa...........&|...... 3180 0000c6b0 62 62 62 62 62 62 62 62 00 00 00 05 00 00 00 05 |bbbbbbbb........| 3181 0000c6c0 00 00 00 26 00 00 00 00 00 00 1c 40 63 63 63 63 |...&.......@cccc|...... 3229 0000c9c0 63 63 63 63 63 63 63 63 63 63 63 63 00 00 00 05 |cccccccccccc....| 3230 0000c9d0 00 00 00 06 00 00 00 26 00 00 00 00 00 00 1c 40 |.......&.......@|......根据前面的分析，我们现在可以看出，外部存储页是不共享的，即使一个列的数据多出一个字节，这一个字节也是独占一个16KB空间的大小，这很浪费存储空间。（当然，这对现代计算机可能不是问题，呵呵）。 说了这么多，总结下Compact格式存储大数据的缺点： - 由于存在768字节的前缀在Page页，所以会存在能定义一个字段，存储11000字节，但是不能定义11个字段，每个字段存储1000字节的"bug"。
- 外部存储页不共享，即使多余一个字节也是独享16KB的页面。
Dynamic行格式接着我们首先看一下行格式为Dynamic是如何存储大数据的： mysql> select version();+-----------+| version() |+-----------+| 5.7.14 |+-----------+1 row in set (0.00 sec)mysql> show table status like 'row'\G;*************************** 1. row *************************** Name: row Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 0 Avg_row_length: 0 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2017-01-03 22:45:16 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment:1 row in set (0.00 sec)创建和compact格式一样的表： CREATE TABLE `row` ( `content` varchar(65532) NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET=latin1insert into row(content) select repeat('a',65532);Query OK, 1 row affected (0.03 sec)Records: 1 Duplicates: 0 Warnings: 0看下页分布： [root@localhost mysql]# python py_innodb_page_info.py -v row.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>page offset 00000003, page type <B-tree Node>, page level <0000>page offset 00000004, page type <Uncompressed BLOB Page>page offset 00000005, page type <Uncompressed BLOB Page>page offset 00000006, page type <Uncompressed BLOB Page>page offset 00000007, page type <Uncompressed BLOB Page>page offset 00000008, page type <Uncompressed BLOB Page>Total number of page: 9:Insert Buffer Bitmap: 1Uncompressed BLOB Page: 5File Space Header: 1B-tree Node: 1File Segment inode: 1第4页是数据页，第5-9页是二进制页。我们直接看磁盘中第4页的数据： 3073 0000c000 dc 2d b0 f5 00 00 00 03 ff ff ff ff ff ff ff ff |.-..............|3074 0000c010 00 00 00 00 00 a3 4b 59 45 bf 00 00 00 00 00 00 |......KYE.......|3075 0000c020 00 00 00 00 00 36 00 02 00 a6 80 03 00 00 00 00 |.....6..........|3076 0000c030 00 7f 00 05 00 00 00 01 00 00 00 00 00 00 00 00 |................|3077 0000c040 00 00 00 00 00 00 00 00 00 64 00 00 00 36 00 00 |.........d...6..|3078 0000c050 00 02 00 f2 00 00 00 36 00 00 00 02 00 32 01 00 |.......6.....2..|3079 0000c060 02 00 1c 69 6e 66 69 6d 75 6d 00 02 00 0b 00 00 |...infimum......|3080 0000c070 73 75 70 72 65 6d 75 6d 14 c0 00 00 10 ff f1 00 |supremum........|3081 0000c080 00 00 00 02 00 00 00 00 00 07 07 a7 00 00 01 1b |................|3082 0000c090 01 10 00 00 00 36 00 00 00 04 00 00 00 26 00 00 |.....6.......&..|3083 0000c0a0 00 00 00 00 ff fc 00 00 00 00 00 00 00 00 00 00 |................|3084 0000c0b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|3085 0000c0c0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|3086 0000c0d0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|3087 0000c0e0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|.........和Compact格式有着明显的不同，当大数据在Page页存放不下时，Dynamic行格式不会留768字节在Page页，并且将全部大数据都放在外部存储页。具体的数据页和外部存储页的连接关系同Compact格式一样。 我们再看看Dynamic格式的外部存储页是不是每一个列独享外部存储空间，还是同Compact格式实验过程一样： CREATE TABLE `testblob` ( `blob1` blob NOT NULL, `blob2` blob NOT NULL, `blob3` blob NOT NULL, `blob4` blob NOT NULL, `blob5` blob NOT NULL, `blob6` blob NOT NULL, `blob7` blob NOT NULL, `blob8` blob NOT NULL, `blob9` blob NOT NULL, `blob10` blob NOT NULL, `blob11` blob NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=latin1;mysql> insert into testblob(blob1,blob2,blob3,blob4,blob5,blob6,blob7,blob8,blob9,blob10,blob11) select repeat('a',8000),repeat('b',8000),repeat('c',8000),repeat('d',8000),repeat('e',8000),repeat('f',8000),repeat('g',8000),repeat('h',8000),repeat('i',8000),repeat('j',8000),repeat('k',8000);Query OK, 1 row affected (0.10 sec)Records: 1 Duplicates: 0 Warnings: 0看一下外部存储页数据： 4599 00011f60 61 61 61 61 61 61 61 61 61 61 61 61 61 61 00 00 |aaaaaaaaaaaaaa..| 4600 00011f70 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|好的，可以不用向下看其他列的了，Dynamic的外部存储页也不是共享的。 但是MySQL为什么要这么设计呢？可能是为了实现简单吧，沿着链表通过有效数据大小就能读取blob的全部数据。假如多个字段的blob混在一起，可能设计更复杂，要更新每个字段的偏移量之类的，更新的话页数据管理也比较麻烦。我的个人猜测，呵呵。 总结下Dynamic格式存储大数据的特点： - 当数据页放不下时，MySQL会将大数据全部放在外部存储页，数据页只留指向外部存储页的指针。
- 外部存储页不共享，即使多余一个字节也是独享16KB的页面。
将列放入外部存储页的标准当一行中的数据不能在数据页中放下，需要申请外部存储页时，MySQL需要决定将哪一列的数据放到外部存储页，遵循的规则如下： - 长度固定的字段不会被放到外部存储页（int、char(N)等）
- 长度小于20字节的字段不会被放到外部存储页。（假如放到外部存储页，不仅会单独占据16KB，还要额外的20字节指针，没有必要）
- 对于Compact和REDUNDANT格式的行数据，长度小于768字节的字段不会被放到外部存储页。（这个原因很显然，本来就不够768字节的前缀，总不能生搬硬凑吧）。
当有多个大数据字段满足上面条件，需要被放到外部存储页时，MySQL会优先选择大的字段放到外部存储页，因为这样可以最大限度的省下数据页的空间，使得更多的字段能够被放到数据页。 由于有较多的实验过程，所以显得比较乱，建议看到这篇文章人自己实践一遍，毕竟自己动手会思考更多的问题与细节，理解的也比较深刻，哈哈哈。
|
收藏
淘帖
踩
