随便写写
0%

MySQL的一个表最多可以有多少个字段

Posted on Edited on

MySQL的一个表最多可以有多少个字段

问题由来

引用我们客户的原话：
创建如下表，提示我：

1
2
[Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

如果我将下面表中的varchar（200），修改成text（或blob）：报错变为另一个：

1
Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

我们查阅了很多的资料，不确定The maximum row size到底是65535 还是8126？原理是什么？

三种报错的疑惑

我们整理了一下，其实类似的错误有三种：

  • 错误1 创建表报maximum row size > 65535
    1
    [Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
  • 错误2 创建表报Row size too large (> 8126)
    1
    Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
  • 错误3 表创建成功但是插入报 Row size too large (> 8126)
    1
    ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

到底要闹哪样

这么多错误，还都不一样，MySQL到底要闹那样
别急，一个问题一个问题的看

错误1:

这个报错其实我们查询MySQL官方手册就可以查询到， 对于一行记录最大的限制是65535字节。为什么是65535，不要问我，手册也没说:)—-一行数据里面字段长度定义有64k，我也是醉了

错误2:

既生瑜何生亮？有了65535的限制以后还有一个8126的限制是为什么列？

MySQL是分两层的，MySQL Server层 + 存储引擎层。
第2个问题其实是MySQL除了在Server层做了一次限制还会在Innodb存储引擎层在做一次限制。

innodb为了保证B+TREE是一个平衡树结构，强制要求一条记录的大小不能超过一个页大小的一半。这也就是我们上面看到的第二个错误。

下面是innodb B+树的结构，我们可以想象一下二分查找时，一个页的只有一条数据会是什么样子？

每个页只有一条数据的查找就变成了链表查找了。这样就没有二分查找的意义了。
而MySQL中默认的页大小是16K，16K的一半是8196字节减去一些元数据信息就得出了8126这个数字。
这就是8126的由来

错误3:

突破错误2

8126是不是不能突破的列？

我们这里就有个案例：按照附1的建表语句建立一个150个字段，每个字段是100个字符（特地使用了ASCII字符集，这样一个字符就是一个字节）的表。

150 * 100=15000 > 8126。按照上面的说法，应该要报错的，
但是各位可以在自己的数据库上试一下，表能够建立成功，这是为什么列？

其实MySQL在计算字段长度的时候并不是按照字段的全部长度来记的。
列字段小于40个字节的都会按实际字节计算，如果大于20 * 2=40 字节就只会按40字节。

对应到MySQL代码中storage/innobase/dict/dict0dict.cc的dict_index_too_big_for_tree()中:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
field_max_size = dict_col_get_max_size(col);
field_ext_max_size = field_max_size < 256 ? 1 : 2;


if (field->prefix_len) {
    if (field->prefix_len < field_max_size) {
        field_max_size = field->prefix_len;
    }
} else if (field_max_size > BTR_EXTERN_FIELD_REF_SIZE * 2
     ¦ && dict_index_is_clust(new_index)) {


    /* In the worst case, we have a locally stored
    column of BTR_EXTERN_FIELD_REF_SIZE * 2 bytes.
    The length can be stored in one byte.  If the
    column were stored externally, the lengths in
    the clustered index page would be
    BTR_EXTERN_FIELD_REF_SIZE and 2. */
    field_max_size = BTR_EXTERN_FIELD_REF_SIZE * 2;
    field_ext_max_size = 1;
}

也就是说，如果字段长度超过BTR_EXTERN_FIELD_REF_SIZE * 2，字段就只算20 * 2=40(BTR_EXTERN_FIELD_REF_SIZE=20)
举例如下:

  • 创建一个300个字段长度类型为varchar(30)的表，在创建时不会创建成功。因为varchar(30)没有超过202，那么总长度就是30030=9000 > 8126就会创建失败。
  • 创建一个150个字段长度类型为varchar(100)的表可以创建成功。因为varchar(100) 大于了202那么就只会按40计算 总长度就是15020*2=6000 < 8126 就会创建成功。

这个20字节是不是看着有点眼熟，可以联系到InnoDB的一个参数：innodb_file_format。该参数用于设置Innodb表内部存储的文件格式，该参数可设置为Antelope,Barracuda两种格式。

  • Antelope是MySQL原始的记录格式，是较古老的记录格式。
    在这种格式记录下Innodb 对于大字段的处理如下:

对于大字段，innodb只会存放前DICT_ANTELOPE_MAX_INDEX_COL_LEN（768）字节在数据页中，超过768字节都会放到溢出页中。这种方式也是B+TREE结构，但是也并不是完美的，因为我们将大字段存放到了数据页中会造成叶子节点的个数会很多，同样会造成非叶子节点的的个数增加。最终导致索引层级增高，访问IO次数增加。

  • Barracuda格式是InnoDB新的存储格式
    他的存储方式如下:

在Barracuda格式下，会用20字节的指针指向溢出页，这样做的好处就是不会造成索引层级的增高。

回到错误3

回归正题，第二个错误我们可以越过去，但是我们是不是能够真的插入150个100字符的字段列。
用附2的插入语句试一下就知道，错误3也会报错出来。
也就是说表可以创建成功但是插入却失败，原因如下：

  • Antelope格式下的COMPACT大字段按照DICT_ANTELOPE_MAX_INDEX_COL_LEN（768）字节溢出页。varchar(100)没有存储为溢出页。
  • Barracuda的DYNAMIC和COMPRESSED格式下只有长字段才会用20字节溢出页的方式，varchar(100)也没有存储为溢出页。

引用reference的原文如下：

1
2
3
4
When a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, long variable-length column values (for VARBINARY,VARCHAR, BLOB, and TEXT columns) are stored fully off-page, and the clustered index record contains only a 20-byte pointer to the overflow page. InnoDB will also store long CHAR column values off-page if the column value is greater than or equal to 768 bytes, which can occur when the maximum byte length of the character set is greater than 3, as it is with utf8mb4, for example.

Whether any columns are stored off-page depends on the page size and the total size of the row. When the row is too long, InnoDBchooses the longest columns for off-page storage until the clustered index record fits on the B-tree page. TEXT and BLOB columns that are less than or equal to 40 bytes are always stored in-line.

总结

总的来说，

  • MySQL Server最多只允许4096个字段
  • InnoDB 最多只能有1000个字段
  • 字段长度加起来如果超过65535，MySQL server层就会拒绝创建表
  • 字段长度加起来（根据溢出页指针来计算字段长度，大于40的，溢出，只算40个字节）如果超过8126，InnoDB拒绝创建表
  • 表结构中根据ROW_FORMAT的存储格式确定行内部保留的字节数（20 VS 768），最终确定一行数据是否小于8126，如果大于8126，报错。

建议

  1. 放弃使用Antelope这种古老的存储格式吧，原因上面也说到了把大字段的前768字节放在数据页中，这样会导致索引的层级很高，会直接影响到查询的性能。
  2. 对于大字段类型建议单独存放到一张表中，不要与经常访问的表放在一起，会造成物理IO的增加。

参考

MySQL reference: Limits on Table Column Count and Row Size
MySQL reference: innodb row format dynamic
mysqlserverteam:Externally Stored Fields in InnoDB

MySQL · 引擎特性 · InnoDB 文件系统之文件物理结构

附1. 建表语句

附上测试的建表语句和insert语句，有兴趣的朋友可以自己按照上面的几种方式在Antelope和Barracuda的几种不同ROW_FORMAT格式上试试。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
create table xjblg1
(
  jydm                         VARCHAR(100),
  rq                           VARCHAR(100),
  cb                           VARCHAR(100),
  khdkjdkjjse                  VARCHAR(100),
  xyhjkjzje                    VARCHAR(100),
  khckhtycfkxjzje              VARCHAR(100),
  qzkhck                       VARCHAR(100),
  tyjqtjrjgcfk                 VARCHAR(100),
  shcftyjqtjrjgje              VARCHAR(100),
  crzjxjlr                     VARCHAR(100),
  shdcczjje                    VARCHAR(100),
  xsdmchgssddxj                VARCHAR(100),
  shdmrfsxje                   VARCHAR(100),
  czjyxjrzcjzje                VARCHAR(100),
  sqlxsxfjyjdxj                VARCHAR(100),
  sdlx                         VARCHAR(100),
  sddsxf                       VARCHAR(100),
  jrqywlsrsddxj                VARCHAR(100),
  zjywsrsddxj                  VARCHAR(100),
  zqtzsysddxj                  VARCHAR(100),
  hdjsysddxj                   VARCHAR(100),
  qtyyjyywjsrsddxj             VARCHAR(100),
  crbzjsddxj                   VARCHAR(100),
  sddwtzj                      VARCHAR(100),
  gjsxjlr                      VARCHAR(100),
  txsddxj                      VARCHAR(100),
  xssptglwsddxj                VARCHAR(100),
  sddsffh                      VARCHAR(100),
  qtfzzjlrdxj                  VARCHAR(100),
  shdyyyqndhxddkjyskx          VARCHAR(100),
  qtyfzskjslcdxj               VARCHAR(100),
  czdzzcsddxj                  VARCHAR(100),
  sdqtyjyhdygdxj               VARCHAR(100),
  jyhdxjlrxj                   VARCHAR(100),
  khdkjdkjzje                  VARCHAR(100),
  cfzyyhhtykxjzje              VARCHAR(100),
  cfzyyh                       VARCHAR(100),
  cftyjqtjgck                  VARCHAR(100),
  cczjjxjlc                    VARCHAR(100),
  chzyyhjkxzyyhjkjjse          VARCHAR(100),
  zfdck                        VARCHAR(100),
  jstyjqtjrjgcfje              VARCHAR(100),
  chtyjqtjrjgcrje              VARCHAR(100),
  chmchgkxje                   VARCHAR(100),
  zfmrfskxje                   VARCHAR(100),
  czkgcsjrzcjjse               VARCHAR(100),
  zflxsxfjyjdxj                VARCHAR(100),
  zfdlx                        VARCHAR(100),
  sxfzczfdxj                   VARCHAR(100),
  zfgzgyjwzgzfdxj              VARCHAR(100),
  zfdgxsf                      VARCHAR(100),
  yxjzfdyyfy                   VARCHAR(100),
  yjrjgwlzcdxjje               VARCHAR(100),
  txzfdxj                      VARCHAR(100),
  yhxdzdkjlxsh                 VARCHAR(100),
  gjsxjlc                      VARCHAR(100),
  gmspjslwzfdxj                VARCHAR(100),
  jrqtzjjjse                   VARCHAR(100),
  qtzcjszcdxj                  VARCHAR(100),
  qtyszfkjsshdxj               VARCHAR(100),
  zfqtyjyhdygdxj               VARCHAR(100),
  jyhdxjlcxj                   VARCHAR(100),
  jyhdcsdxjllje                VARCHAR(100),
  shtzsddxj                    VARCHAR(100),
  qdtzsysddxj                  VARCHAR(100),
  qzfdglhlrssddxj              VARCHAR(100),
  czgdzcwxzcjqtzcesddxj        VARCHAR(100),
  czgqtzssddxj                 VARCHAR(100),
  qdzgsjqtyydwssddxjje         VARCHAR(100),
  sdqtytzhdygdxj               VARCHAR(100),
  tzhdxjlrxj                   VARCHAR(100),
  tzzfdxj                      VARCHAR(100),
  qyxtzzzzfdxj                 VARCHAR(100),
  zqtzszfdxj                   VARCHAR(100),
  gmzgslyqyjhyqytzszfdxjje     VARCHAR(100),
  zjzjgcszfdxj                 VARCHAR(100),
  gjgdzcwxzchqtcqzczfdxj       VARCHAR(100),
  qdzgsjqtyydwzfdxjje          VARCHAR(100),
  zfdqtytzhdygdxj              VARCHAR(100),
  tzhdxjlcxj                   VARCHAR(100),
  tzhdcsdxjllje                VARCHAR(100),
  xstzssddxj                   VARCHAR(100),
  fxzqhzcsxsdxj                VARCHAR(100),
  qdjksddxj                    VARCHAR(100),
  fxzqsddxj                    VARCHAR(100),
  qzfxcjzqsddxj                VARCHAR(100),
  zjgbssddxj                   VARCHAR(100),
  sdqtyczhdygdxj               VARCHAR(100),
  czhdxjlrxj                   VARCHAR(100),
  chzwszfdxj                   VARCHAR(100),
  fpgllrhcflxzfdxj             VARCHAR(100),
  qzcflxszfdxj                 VARCHAR(100),
  zfxgfxfy                     VARCHAR(100),
  zfqtyczhdygdxj               VARCHAR(100),
  czhdxjlcxj                   VARCHAR(100),
  czhdcsdxjllje                VARCHAR(100),
  hlbddxjjxjdjwdyx             VARCHAR(100),
  xjjxjdjwjzje                 VARCHAR(100),
  qcxjjxjdjwye                 VARCHAR(100),
  qmxjjxjdjwye                 VARCHAR(100),
  jlr                          VARCHAR(100),
  ssgdqy                       VARCHAR(100),
  qzjtdhzzb                    VARCHAR(100),
  jtddksszb                    VARCHAR(100),
  jtdzcjzzb                    VARCHAR(100),
  chcftyjzzb                   VARCHAR(100),
  jtdzzcjzzb                   VARCHAR(100),
  jtqtzcjzzb                   VARCHAR(100),
  gdzczjyqzcchscxswzccj        VARCHAR(100),
  tzxfdczj                     VARCHAR(100),
  wxzcdyzcjqtzcdtx             VARCHAR(100),
  qzwxzctx                     VARCHAR(100),
  cqdtfytx                     VARCHAR(100),
  czgdzcwxzchqtcqzcdsssy       VARCHAR(100),
  cztzxfdcdsssy                VARCHAR(100),
  gdzcbfss                     VARCHAR(100),
  cwfy                         VARCHAR(100),
  dtfydjszj                    VARCHAR(100),
  ytfydzjjs                    VARCHAR(100),
  tzssjsy                      VARCHAR(100),
  gyjzbdsyss                   VARCHAR(100),
  hdsy                         VARCHAR(100),
  ysjrgjjyjsy                  VARCHAR(100),
  chdjs                        VARCHAR(100),
  dkdjs                        VARCHAR(100),
  ckdzj                        VARCHAR(100),
  cjkxdjz                      VARCHAR(100),
  yjfzdzj                      VARCHAR(100),
  sdyhxkx                      VARCHAR(100),
  jrfzdzj                      VARCHAR(100),
  dysdszcdjs                   VARCHAR(100),
  dysdsfzdzj                   VARCHAR(100),
  jyxysxmdjs                   VARCHAR(100),
  jyxyfxmdzj                   VARCHAR(100),
  jyxqtzcdjs                   VARCHAR(100),
  jyxqtfzdzj                   VARCHAR(100),
  qt                           VARCHAR(100),
  jyhdxjllje                   VARCHAR(100),
  ygdzcchzw                    VARCHAR(100),
  ytzchzw                      VARCHAR(100),
  ygdzcjxtz                    VARCHAR(100),
  zyzwzb                       VARCHAR(100),
  ynndqdkzhgszq                VARCHAR(100),
  rzzrgdzc                     VARCHAR(100),
  qtbsjxjszdtzhczhdje          VARCHAR(100),
  xjdqmye                      VARCHAR(100),
  jxjdqcye                     VARCHAR(100),
  xjdjwdqmye                   VARCHAR(100),
  jxjdjwdqcye                  VARCHAR(100),
  xjjxjdjwjzje1                VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=ascii;;

附2. insert 语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
insert into xjblg1 values(
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100),
repeat('y',100));