本文同时发布在https://github.com/zhangyachen/zhangyachen.github.io/issues/96

方今,在工作中碰着了MySQL中什么存款和储蓄长度较长的字段类型难题,于是花了八日多的时间抽空学习了1晃,并且记录下来。

MySQL大概的逻辑存款和储蓄结构在这篇作品中有介绍,做为基本概念:InnoDB
逻辑存储结构

注:文中所指的大数量指的是长度较长的数量字段,包括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: 81920
Max_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=latin1

mysql> insert into row(content) select repeat('a',65532);
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

笔者们运用py_innodb_page_info.py工具来查阅表中的页分布:

[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: 1
Uncompressed BLOB Page: 4
File Space Header: 1
B-tree Node: 1
File Segment inode: 1

能够见见,第伍页的<B-tree Node>, page level <0000>格式为数据页,存放着MySQL的行数据。<Uncompressed BLOB Page>能够知晓为MySQL存放大数指标位置,暂且叫作外部存款和储蓄页。Compact格式未有将大数额总体位于数据页中,而是将1些数据放在了表面存款和储蓄页中。那么,是一切数量在外表存款和储蓄页中,如故有的数目。固然是1局地数据,那1局地是有些啊?

笔者们使用hexdump -Cv row.ibd翻看一下数据页<B-tree Node>, page level <0000>,约等于第伍页:

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字节,然后剩余部分存款和储蓄在表面存款和储蓄页中。那么数据页与表面存款和储蓄页、外部存款和储蓄页与外表存款和储蓄页是什么连接在一起的呢?

大家旁观那1行:

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字节的末段。注意最终的1几个字节:

  • 00 00 00 02:四字节,代表外部存款和储蓄页所在的space id
  • 00 00 00 0肆:肆字节,代表第二个外表页的Page no
  • 00 00 00 26:4字节,值为38,指向blob页的header
  • 00 00 00 00 00 00 fc
    fc:8字节,代表该列存在外部存款和储蓄页的总参谋长度。此处的值为64764,加上前缀768刚好是6553二。(注意一点,即使表示BLOB长度的是捌字节,实际唯有5个字节约使用,全部对于BLOB字段,存款和储蓄数据的最大尺寸为4GB。)

表达下第1个外表存储页的头顶信息:

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|
...
...

前三十八个字节为File Header(关于InnoDB数据页的详实结构请参见《MySQL技术内幕
InnoDB存款和储蓄引擎》四.四),那一个差不离提一下:

  • cd c3 b6 8e:4字节,该页的checksum。
  • 00 00 00 04:4字节,页偏移,此页为表空间中的第肆个页。
  • 00 00 00
    00:肆字节,当前页的上三个页。此页为<Uncompressed BLOB Page>,所以未有上一页。
  • 00 00 00
    00:四字节,当前页的下一个页。此页为<Uncompressed BLOB Page>,所以未有下1页。
  • 00 00 00 00 00 0陆 b八 a二:八字节,该页最终被改动的日志体系地方LSN。
  • 00 0a:2字节,页类型,0x000A代表BLOB页。
  • 00 00 00 00 00 00 00 00:8字节,略过。
  • 00 00 00 02:页属于哪个表空间,此处指表空间的ID为二。

之后是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|
...
...

最终壹页的有效数据大小为0x0000三d九e=1577四,76八+16330*三+15774 =
6553二字节,符合起始插入数据的深浅。
由于那是最后三个<Uncompressed BLOB Page>,所以针对下一个<Uncompressed BLOB Page>的指针为ff
ff ff ff。

透过大家能够很清楚的看来数据页与BLOB页的三番五次关系(引用天猫数据库月报上的一张图):
json 1

小编们来再看一个相比好玩的例证。:

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

作者们树立一张新表,有13个blob字段。然后向每种字段插入一千字节的数额,MySQL会唤醒ERROR 1030 (HY000): Got error 139 from storage engine,什么看头呢?

InnoDB是以B+树来组织数量的,若是每1行数据都挤占一整个Page页,那么B+树将向下为单链表,所以InnoDB规定了1个Page必须蕴含两行数据。也正是单排数据存款和储蓄在Page上的轻重缓急大致为玖仟字节。
而地点的例证,1行数据有拾个一千字节的多少,Page层肯定放不下,所以在Page层留下76八*1一=844八字节,已经超(Jing Chao)越了八千字节,所以MySQL会提醒ERROR 1030 (HY000): Got error 139 from storage engine。大家很自在的定义2个字段,来存款和储蓄1一千个字节,可是却一筹莫展将她们分成十二个字段来储存,有点意思!

那么哪些消除地点的标题啊?

  • 将行格式转为接下去要说的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.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>
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: 1
Uncompressed BLOB Page: 9
File Space Header: 1
B-tree Node: 1
File Segment inode: 1

作者们能够看来那一行数据有玖个外表存款和储蓄页,而小编辈归总就插入了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  |.......&.......@|
...
...

依据前面包车型地铁辨析,大家后天得以看出,外部存款和储蓄页是不共享的,就算一个列的数额多出二个字节,这一个字节也是垄断一个1六KB空间的高低,那很浪费存款和储蓄空间。(当然,那对当代电脑大概不是题材,呵呵)。

说了这么多,计算下Compact格式存款和储蓄大数额的败笔:

  • 出于存在768字节的前缀在Page页,所以会设有能定义三个字段,存款和储蓄11000字节,可是不能够定义十一个字段,每一种字段存储一千字节的”bug”。
  • 表面存款和储蓄页不共享,即便多余贰个字节也是独享1陆KB的页面。

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: 16384
Max_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格式1样的表:

CREATE TABLE `row` (
  `content` varchar(65532) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=latin1

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 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: 1
Uncompressed BLOB Page: 5
File Space Header: 1
B-tree Node: 1
File Segment inode: 1

第陆页是数据页,第陆-九页是2进制页。咱们一贯看磁盘中第6页的多少:

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行格式不会留76八字节在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会将大数量总体位于外部存款和储蓄页,数据页只留指向外部存款和储蓄页的指针。
  • 表面存款和储蓄页不共享,尽管多余1个字节也是独享1陆KB的页面。

鉴于有较多的实验进程,所以显得相比较乱,提议看到那篇小说人温馨执行二次,终究本身动手会思索越多的难题与细节,驾驭的也正如长远,哈哈哈。

参考资料:http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html
http://mysqlserverteam.com/externally-stored-fields-in-innodb/
https://www.percona.com/blog/2010/02/09/blob-storage-in-innodb/
http://mysql.taobao.org/monthly/2016/02/01/

相关文章

网站地图xml地图