作者:kay

擅长 Oracle、MySQL、PostgresSQL 等多种数据库领域;

擅长 Oracle、MySQL 性能优化、数据库架构设计、数据库故障修复、数据迁移以及恢复;

热衷于研究 MySQL 数据库内核源码、分享技术文章,并拥有 Oracle OCP 认证;

就职于江苏国泰新点软件有限公司,DBA 技术团队成员。

一、MySQL 限制回顾

之前在《MySQL Text 字段的限制》一文中讲过了关于 MySQL 在 Server 层和 InnoDB 层的限制,但是限制的算法没有详细展开,这里进行补充说明,先回顾一下 MySQL 的两个限制:

1.  MySQL Server 层 的限制为单条记录的大小不超过 65535 字节;

2.  InnoDB 层 不能超过 innodb_page_size 大小的一半(实际上还要小一点,因为要扣除一些页中元数据信息), 以默认的 16K 设置为例,其限制为 8126。

另:以下计算方式均已 MySQL 5.7.27 进行说明。

二、Server 层限制的计算方法

2.1 计算过程

一般说来,如果是 MySQL Server 层做了限制,则返回如下报错:
  1. Row size too large. The maximum row size for the used table type,

  2. not counting BLOBs, is 65535.

  3. This includes storage overhead, check the manual.

  4. You have to change some columns to TEXT or BLOBs

所以可以根据这个报错来定位到计算方式,其关键函数路径如下:
  1. mysql_prepare_create_table /* sql/sql_table.cc:3474 */

  2. --> mysql_create_frm /* sql/unireg.cc:107 */

  3. --> pack_header /* sql/unireg.cc:733 */

pack_header 中存在这部分代码:
  1. if (reclength > (ulong) file->max_record_length()) /* 右值为 65535 */

  2. {

  3. my_error(ER_TOO_BIG_ROWSIZE, /* 这个就是上面的报错信息对应的状态值 */

  4. MYF(0),

  5. static_cast<long>(file->max_record_length()));

  6. DBUG_RETURN(1);

  7. }

重点在于 reclength 的值是否 大于 65535因此了解一下 reclength 的计算过程:
  1. /* sql/unireg.cc:843 */

  2. if (field->offset + data_offset + length > reclength)

  3. reclength= field->offset + data_offset + length;


  4. /*

  5. field->offset 的计算方式

  6. sql/sql_table.cc:3816

  7. */

  8. while ((sql_field=it++)) {

  9. /* 省略部分 */

  10. sql_field->offset= record_offset; /* 当前记录的数据偏移量 */

  11. if (MTYP_TYPENR(sql_field->unireg_check) == Field::NEXT_NUMBER)

  12. auto_increment++;


  13. if (sql_field->stored_in_db)

  14. record_offset+= sql_field->pack_length; /* 累加当前字段的pack_length */

  15. }



  16. /*

  17. data_offset 的计算方式

  18. sql/unireg.cc:139

  19. */

  20. data_offset= (create_info->null_bits + 7) / 8;



  21. /*

  22. length 的计算方式

  23. sql/unireg.cc:833

  24. */

  25. length=field->pack_length;

  26. /* 这个pack_length就是和字段类型相关,以varchar为例*/


  27. /* sql/sql_table.cc:3082 */

  28. sql_field->sql_type= MYSQL_TYPE_VAR_STRING;

  29. sql_field->pack_length= calc_pack_length(sql_field->sql_type,

  30. (uint) sql_field->length);


  31. 关于 `calc_pack_length` 的计算方式如下:



  32. size_t calc_pack_length(enum_field_types type, size_t length)

  33. {

  34. switch (type) {

  35. case MYSQL_TYPE_VAR_STRING:

  36. case MYSQL_TYPE_STRING:

  37. case MYSQL_TYPE_DECIMAL: return (length);

  38. case MYSQL_TYPE_VARCHAR: return (length + (length < 256 ? 1: 2));

  39. case MYSQL_TYPE_YEAR:

  40. case MYSQL_TYPE_TINY : return 1;

  41. case MYSQL_TYPE_SHORT : return 2;

  42. case MYSQL_TYPE_INT24:

  43. case MYSQL_TYPE_NEWDATE: return 3;

  44. case MYSQL_TYPE_TIME: return 3;

  45. case MYSQL_TYPE_TIME2:

  46. return length > MAX_TIME_WIDTH ?

  47. my_time_binary_length(length - MAX_TIME_WIDTH - 1) : 3;

  48. case MYSQL_TYPE_TIMESTAMP: return 4;

  49. case MYSQL_TYPE_TIMESTAMP2:

  50. return length > MAX_DATETIME_WIDTH ?

  51. my_timestamp_binary_length(length - MAX_DATETIME_WIDTH - 1) : 4;

  52. case MYSQL_TYPE_DATE:

  53. case MYSQL_TYPE_LONG : return 4;

  54. case MYSQL_TYPE_FLOAT : return sizeof(float);

  55. case MYSQL_TYPE_DOUBLE: return sizeof(double);

  56. case MYSQL_TYPE_DATETIME: return 8;

  57. case MYSQL_TYPE_DATETIME2:

  58. return length > MAX_DATETIME_WIDTH ?

  59. my_datetime_binary_length(length - MAX_DATETIME_WIDTH - 1) : 5;

  60. case MYSQL_TYPE_LONGLONG: return 8; /* Don't crash if no longlong */

  61. case MYSQL_TYPE_NULL : return 0;

  62. case MYSQL_TYPE_TINY_BLOB: return 1+portable_sizeof_char_ptr;

  63. case MYSQL_TYPE_BLOB: return 2+portable_sizeof_char_ptr;

  64. case MYSQL_TYPE_MEDIUM_BLOB: return 3+portable_sizeof_char_ptr;

  65. case MYSQL_TYPE_LONG_BLOB: return 4+portable_sizeof_char_ptr;

  66. case MYSQL_TYPE_GEOMETRY: return 4+portable_sizeof_char_ptr;

  67. case MYSQL_TYPE_JSON: return 4+portable_sizeof_char_ptr;

  68. case MYSQL_TYPE_SET:

  69. case MYSQL_TYPE_ENUM:

  70. case MYSQL_TYPE_NEWDECIMAL:

  71. abort(); return 0; // This shouldn't happen

  72. case MYSQL_TYPE_BIT: return length / 8;

  73. default:

  74. return 0;

  75. }

  76. }

2.2 小结

根据上面计算方式的梳理,在 MySQL Server 层计算长度的公式,可以写成这样的伪代码:
  1. data_offset = (number_null_field + 7) / 8; /*number_null_field表示允许为null的字段数*/


  2. total_length = 0;

  3. for (int i = 0; i < n_fileds; i++) {

  4. total_length += calc_pack_length(field_type, length)

  5. }


  6. /*

  7. field_type:数据类型

  8. length:数据的字节数

  9. 注意varchar(100) 的字节数,在utf8mb4环境下,字节数为400

  10. */


  11. total_length += data_offset;

通过上述计算,需要满足 total_length <= 65535,即可通过 MySQL Server 层的检查。

三、InnoDB 层限制的计算方法

3.1 计算过程

InnooDB 层面如果出现长度超过限制,报错如下所示:
  1. ERROR 1118 (42000): Row size too large (> 8126).

  2. Changing some columns to TEXT or BLOB may help.

  3. In current row format, BLOB prefix of 0 bytes is stored inline.

通过对报错信息的搜索,其判断方式如下:
  1. /* dict0dict.cc:2515 */

  2. /* Check the size limit on leaf pages. */

  3. if (rec_max_size >= page_rec_max) {

  4. ib::error_or_warn(strict)

  5. << "Cannot add field " << field->name

  6. << " in table " << table->name

  7. << " because after adding it, the row size is "

  8. << rec_max_size

  9. << " which is greater than maximum allowed"

  10. " size (" << page_rec_max

  11. << ") for a record on index leaf page.";


  12. return(TRUE);

  13. }

其中 page_rec_max 的定义如下:

  1. /* dict0dict.cc:2431 */

  2. page_rec_max = srv_page_size == UNIV_PAGE_SIZE_MAX

  3. ? REC_MAX_DATA_SIZE - 1

  4. : page_get_free_space_of_empty(comp) / 2;


  5. /* srv0srv.cc:222 */

  6. ulong srv_page_size = UNIV_PAGE_SIZE_DEF;


  7. /* 这个是默认值(DEF),如果my.cnf中配置page_size为32K,那这个值就是32K */


  8. /* univ.i:343 */

  9. # define UNIV_PAGE_SIZE_DEF (1 << UNIV_PAGE_SIZE_SHIFT_DEF)


  10. /* univ.i:332 */

  11. # define UNIV_PAGE_SIZE_SHIFT_DEF 14


  12. /* univ.i:341 */

  13. # define UNIV_PAGE_SIZE_MAX (1 << UNIV_PAGE_SIZE_SHIFT_MAX)


  14. /* univ.i:330 */

  15. # define UNIV_PAGE_SIZE_SHIFT_MAX 16


  16. /* rem0rec.h:1092 */

  17. # define REC_MAX_DATA_SIZE 16384

从上面的源码定义中可以看到:
1. srv_page_size 的默认值为 1<<14 即 16384,而我们配置也是 16384;
2. UNIV_PAGE_SIZE_MAX 的值为 1<<16 即 65536;

因此 srv_page_size==UNIV_PAGE_SIZE_MAX 不成立,所以 page_rec_max = page_get_free_space_of_empty(comp) / 2

其中 page_get_free_space_of_empty 的定义如下:
  1. /*************************************************************//**

  2. Calculates free space if a page is emptied.

  3. @return free space */

  4. UNIV_INLINE

  5. ulint

  6. page_get_free_space_of_empty(

  7. /*=========================*/

  8. ulint comp) /*!< in: nonzero=compact page layout */

  9. {

  10. if (comp) { /* 现在发布版本的配置,会走这个逻辑 */

  11. return((ulint)(UNIV_PAGE_SIZE

  12. - PAGE_NEW_SUPREMUM_END

  13. - PAGE_DIR

  14. - 2 * PAGE_DIR_SLOT_SIZE));

  15. } /* 16384 - 120 - 8 - 4 = 16252 */


  16. return((ulint)(UNIV_PAGE_SIZE

  17. - PAGE_OLD_SUPREMUM_END

  18. - PAGE_DIR

  19. - 2 * PAGE_DIR_SLOT_SIZE));

  20. }


  21. /* univ.i:312 */

  22. # define UNIV_PAGE_SIZE ((ulint) srv_page_size)

  23. /* 在我们的版本中为 16384 */


  24. /* page0page.h:122 */

  25. # define PAGE_NEW_SUPREMUM_END (PAGE_NEW_SUPREMUM + 8)

  26. /* 38 + 36 + 2*10 + 2*5 + 8 + 8 = 120 */


  27. /* page0page.h:119 */

  28. # define PAGE_NEW_SUPREMUM (PAGE_DATA + 2 * REC_N_NEW_EXTRA_BYTES + 8)


  29. /* page0page.h:104 */

  30. # define PAGE_DATA (PAGE_HEADER + 36 + 2 * FSEG_HEADER_SIZE)


  31. /* page0page.h:56 */

  32. # define PAGE_HEADER FSEG_PAGE_DATA


  33. /* fsp0types.h:68 */

  34. # define FSEG_PAGE_DATA FIL_PAGE_DATA


  35. /* fil0fil.h:517 */

  36. # define FIL_PAGE_DATA 38U


  37. /* fsp0types.h:81 */

  38. # define FSEG_HEADER_SIZE 10


  39. /* rem0rec.h:52 */

  40. # define REC_N_NEW_EXTRA_BYTES 5


  41. /* page0page.h:152 */

  42. # define PAGE_DIR FIL_PAGE_DATA_END


  43. /* fil0fil.h:525 */

  44. # define FIL_PAGE_DATA_END 8


  45. /* page0page.h:155 */

  46. # define PAGE_DIR_SLOT_SIZE 2

如上所示,page_get_free_space_of_empty(comp) 返回的值为 16252,即 page_rec_max = 16252 / 2,刚好等于8126,其实从上面的报错结果( > 8126)也可以推测出来。

接下来我们看一下一条记录实际长度( rec_max_size)的计算方式:

我们把代码精简一下,其计算过程如下:
  1. /* 下面是函数 dict_index_too_big_for_tree 中关于 rec_max_size 计算部分的内容 */

  2. /* 涉及到的辅助函数,下面也有相关说明 */


  3. rec_max_size = comp /* comp 为 true */

  4. ? REC_N_NEW_EXTRA_BYTES /* rem0rec.h:52:#define REC_N_NEW_EXTRA_BYTES 5 */

  5. : REC_N_OLD_EXTRA_BYTES; /* rem0rec.h:49:#define REC_N_OLD_EXTRA_BYTES 6 */


  6. rec_max_size += UT_BITS_IN_BYTES(new_index->n_nullable);


  7. /*

  8. 这个宏的实现如下:


  9. ut0ut.h:222

  10. #define UT_BITS_IN_BYTES(b) (((b) + 7) / 8)


  11. 即 (允许为空的字段个数 + 7) / 8

  12. */


  13. for (i = 0; i < new_index->n_fields; i++) {

  14. const dict_field_t* field

  15. = dict_index_get_nth_field(new_index, i);

  16. const dict_col_t* col

  17. = dict_field_get_col(field);

  18. ulint field_max_size;

  19. ulint field_ext_max_size;


  20. /* In dtuple_convert_big_rec(), variable-length columns

  21. that are longer than BTR_EXTERN_LOCAL_STORED_MAX_SIZE

  22. may be chosen for external storage.


  23. Fixed-length columns, and all columns of secondary

  24. index records are always stored inline. */


  25. /* Determine the maximum length of the index field.

  26. The field_ext_max_size should be computed as the worst

  27. case in rec_get_converted_size_comp() for

  28. REC_STATUS_ORDINARY records. */


  29. field_max_size = dict_col_get_fixed_size(col, comp);


  30. /* 本质上是判断是否定长,并获得定长字段的字节数*/


  31. /*如果是定长,则直接 goto add_field_size 进行求值*/


  32. if (field_max_size && field->fixed_len != 0) {

  33. /* dict_index_add_col() should guarantee this */

  34. ut_ad(!field->prefix_len

  35. || field->fixed_len == field->prefix_len);

  36. /* Fixed lengths are not encoded

  37. in ROW_FORMAT=COMPACT. */

  38. field_ext_max_size = 0;

  39. goto add_field_size;

  40. }


  41. /*如果是变长,则计算最大值,然后求 field_ext_max_size的长度,

  42. 这个是用户存储字段长度的变量

  43. 这个只是默认初始值,后面会根据长度进行溢出页的设置

  44. */


  45. field_max_size = dict_col_get_max_size(col);

  46. field_ext_max_size = field_max_size < 256 ? 1 : 2;


  47. if (field->prefix_len) {

  48. /* 建表判断长度时,直接走else即可,这里会在建立二级索引时用到,但是和聚集索引不是一颗B_Tree,因此可以忽略 */

  49. if (field->prefix_len < field_max_size) {

  50. field_max_size = field->prefix_len;

  51. }

  52. } else if (field_max_size > BTR_EXTERN_LOCAL_STORED_MAX_SIZE

  53. && dict_index_is_clust(new_index)) {


  54. /*

  55. btr0types.h:56

  56. #define BTR_EXTERN_LOCAL_STORED_MAX_SIZE \

  57. (BTR_EXTERN_FIELD_REF_SIZE * 2)


  58. btr0types.h:53

  59. #define BTR_EXTERN_FIELD_REF_SIZE FIELD_REF_SIZE


  60. page0size.h:32

  61. #define FIELD_REF_SIZE 20


  62. 所以这段代码的意思为:

  63. 如果变长字段的最大值大于40 (溢出页指针的2倍),则这个字段在页内

  64. 只保留40个字节,且长度变量设置为1,即总共占用41个字节。*/


  65. /* In the worst case, we have a locally stored

  66. column of BTR_EXTERN_LOCAL_STORED_MAX_SIZE bytes.

  67. The length can be stored in one byte. If the

  68. column were stored externally, the lengths in

  69. the clustered index page would be

  70. BTR_EXTERN_FIELD_REF_SIZE and 2. */


  71. field_max_size = BTR_EXTERN_LOCAL_STORED_MAX_SIZE;

  72. field_ext_max_size = 1;

  73. }


  74. if (comp) {

  75. /* Add the extra size for ROW_FORMAT=COMPACT.

  76. For ROW_FORMAT=REDUNDANT, these bytes were

  77. added to rec_max_size before this loop. */

  78. rec_max_size += field_ext_max_size;

  79. }

  80. add_field_size:

  81. rec_max_size += field_max_size;


  82. /* Check the size limit on leaf pages. */

  83. if (rec_max_size >= page_rec_max) {

  84. ib::error_or_warn(strict)

  85. << "Cannot add field " << field->name

  86. << " in table " << table->name

  87. << " because after adding it, the row size is "

  88. << rec_max_size

  89. << " which is greater than maximum allowed"

  90. " size (" << page_rec_max

  91. << ") for a record on index leaf page.";


  92. return(TRUE);

  93. }


  94. / * 省略后续代码,相关辅助函数:*/


  95. ulint

  96. dict_col_get_fixed_size(

  97. /*====================*/

  98. const dict_col_t* col, /*!< in: column */

  99. ulint comp) /*!< in: nonzero=ROW_FORMAT=COMPACT */

  100. {

  101. return(dtype_get_fixed_size_low(col->mtype, col->prtype, col->len,

  102. col->mbminmaxlen, comp));

  103. /*

  104. 根据实现,可以简单的计算为:

  105. 1. 定长的为显示指定的 字符数*字符宽度

  106. - 例如 char(10), 在 utf8mb4 里面,字节为 40

  107. 2. 变长的直接返回0

  108. */

  109. }


  110. /***********************************************************************//**

  111. Returns the size of a fixed size data type, 0 if not a fixed size type.

  112. @return fixed size, or 0 */

  113. UNIV_INLINE

  114. ulint

  115. dtype_get_fixed_size_low(

  116. /*=====================*/

  117. ulint mtype, /*!< in: main type */

  118. ulint prtype, /*!< in: precise type */

  119. ulint len, /*!< in: length */

  120. ulint mbminmaxlen, /*!< in: minimum and maximum length of

  121. a multibyte character, in bytes */

  122. ulint comp) /*!< in: nonzero=ROW_FORMAT=COMPACT */

  123. {

  124. switch (mtype) {

  125. case DATA_SYS:

  126. # ifdef UNIV_DEBUG

  127. switch (prtype & DATA_MYSQL_TYPE_MASK) {

  128. case DATA_ROW_ID:

  129. ut_ad(len == DATA_ROW_ID_LEN);

  130. break;

  131. case DATA_TRX_ID:

  132. ut_ad(len == DATA_TRX_ID_LEN);

  133. break;

  134. case DATA_ROLL_PTR:

  135. ut_ad(len == DATA_ROLL_PTR_LEN);

  136. break;

  137. default:

  138. ut_ad(0);

  139. return(0);

  140. }

  141. # endif /* UNIV_DEBUG */

  142. // Fall through.

  143. case DATA_CHAR:

  144. case DATA_FIXBINARY:

  145. case DATA_INT:

  146. case DATA_FLOAT:

  147. case DATA_DOUBLE:

  148. case DATA_POINT:

  149. return(len);

  150. case DATA_MYSQL:

  151. # ifndef UNIV_HOTBACKUP

  152. if (prtype & DATA_BINARY_TYPE) {

  153. return(len);

  154. } else if (!comp) {

  155. return(len);

  156. } else {

  157. #ifdef UNIV_DEBUG

  158. ulint i_mbminlen, i_mbmaxlen;


  159. innobase_get_cset_width(

  160. dtype_get_charset_coll(prtype),

  161. &i_mbminlen, &i_mbmaxlen);


  162. ut_ad(DATA_MBMINMAXLEN(i_mbminlen, i_mbmaxlen)

  163. == mbminmaxlen);

  164. # endif /* UNIV_DEBUG */

  165. if (DATA_MBMINLEN(mbminmaxlen)

  166. == DATA_MBMAXLEN(mbminmaxlen)) {

  167. return(len);

  168. }

  169. }

  170. # else /* !UNIV_HOTBACKUP */

  171. return(len);

  172. # endif /* !UNIV_HOTBACKUP */

  173. /* fall through for variable-length charsets */

  174. case DATA_VARCHAR:

  175. case DATA_BINARY:

  176. case DATA_DECIMAL:

  177. case DATA_VARMYSQL:

  178. case DATA_VAR_POINT:

  179. case DATA_GEOMETRY:

  180. case DATA_BLOB:

  181. return(0);

  182. default:

  183. ut_error;

  184. }


  185. return(0);

  186. }


  187. ulint

  188. dict_col_get_max_size(

  189. /*==================*/

  190. const dict_col_t* col) /*!< in: column */

  191. {

  192. return(dtype_get_max_size_low(col->mtype, col->len));

  193. }


  194. ulint

  195. dtype_get_max_size_low(

  196. /*===================*/

  197. ulint mtype, /*!< in: main type */

  198. ulint len) /*!< in: length */

  199. {

  200. switch (mtype) {

  201. case DATA_SYS:

  202. case DATA_CHAR:

  203. case DATA_FIXBINARY:

  204. case DATA_INT:

  205. case DATA_FLOAT:

  206. case DATA_DOUBLE:

  207. case DATA_MYSQL:

  208. case DATA_VARCHAR:

  209. case DATA_BINARY:

  210. case DATA_DECIMAL:

  211. case DATA_VARMYSQL:

  212. case DATA_POINT:

  213. return(len);

  214. case DATA_VAR_POINT:

  215. case DATA_GEOMETRY:

  216. case DATA_BLOB:

  217. break;

  218. default:

  219. ut_error;

  220. }


  221. return(ULINT_MAX);

  222. }

3.2 小结

根据上面计算方式的梳理,在 InnoDB Server 层计算长度的公式,可以写成这样的伪代码:
  1. rec_max_size = 5;

  2. /*

  3. innodb header 中固定的两个值

  4. DB_TRX_ID: 6 Byte

  5. DB_ROLL_PTR: 7 Byte

  6. */

  7. rec_max_size += 6+7;


  8. /* rem0rec.h:70

  9. # define REC_NODE_PTR_SIZE 4


  10. dict0dict.cc:2536

  11. rec_max_size + REC_NODE_PTR_SIZE >= page_ptr_max

  12. */

  13. rec_max_size += 4;


  14. rec_max_size += (number_null_field + 7) / 8; /*number_null_field表示允许为null的字段数*/


  15. for (int i = 0; i < n_fileds; i++) {

  16. /* 定长字段 */

  17. if (field.type is fixed) {

  18. rec_max_size += filed.length;

  19. /*char需要 x4个字节的 utf8mb4类型*/

  20. continue;

  21. }


  22. /* 变长字段 */

  23. field_ext_max_size = 1;

  24. if (field.type is variable) {

  25. field_max_size = field.length > 40 ? 40 : field.length;

  26. }


  27. rec_max_size += field_max_size + field_ext_max_size;

  28. }

通过上述计算,需要满足 rec_max_size < 8126,即可通过 InnoDB 层的检查。

四、总结

1. 必须在 MySQL Server 层和 InnoDB 层同时满足上述条件,才能建表成功;
2. 如果出现上述报错情况,大部分是因为 varchar 等设置过大,建议可以将一些字段逐步缩小,或者用 text 进行代替;