擅長 Oracle、MySQL、PostgresSQL 等多種數據庫領域；
擅長 Oracle、MySQL 性能優化、數據庫架構設計、數據庫故障修復、數據遷移以及恢復；
熱衷於研究 MySQL 數據庫內核源碼、分享技術文章，並擁有 Oracle OCP 認證；
就職於江蘇國泰新點軟件有限公司，DBA 技術團隊成員。
一、MySQL 限制回顧
之前在 一文中講過了關於 MySQL 在 Server 層和 InnoDB 層的限制，但是限制的算法沒有詳細展開，這裡進行補充說明，先回顧一下 MySQL 的兩個限制：
1. MySQL Server 層 的限制為單條記錄的大小不超過 65535 字節；
2. InnoDB 層 不能超過 innodb_page_size 大小的一半（實際上還要小一點，因為要扣除一些頁中元數據信息）, 以默認的 16K 設置為例，其限制為 8126。
另：以下計算方式均已 MySQL 5.7.27 進行說明。
二、Server 層限制的計算方法
2.1 計算過程
一般說來，如果是 MySQL Server 層做了限制，則返回如下報錯：
<code>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/<code>
所以可以根據這個報錯來定位到計算方式，其關鍵函數路徑如下：
<code>mysql_prepare_create_table /* sql/sql_table.cc:3474 */ --> mysql_create_frm /* sql/unireg.cc:107 */ --> pack_header /* sql/unireg.cc:733 *//<code>
在 pack_header 中存在這部分代碼：
<code>if (reclength > (ulong) file->max_record_length()) /* 右值為 65535 */{ my_error(ER_TOO_BIG_ROWSIZE, /* 這個就是上面的報錯信息對應的狀態值 */ MYF(0), static_cast<long>(file->max_record_length())); DBUG_RETURN(1);}/<long>/<code>重點在於 reclength 的值是否 大於 65535。因此瞭解一下 reclength 的計算過程：
<code>/* sql/unireg.cc:843 */if (field->offset + data_offset + length > reclength) reclength= field->offset + data_offset + length;/*field->offset 的計算方式sql/sql_table.cc:3816*/while ((sql_field=it++)) { /* 省略部分 */ sql_field->offset= record_offset; /* 當前記錄的數據偏移量 */ if (MTYP_TYPENR(sql_field->unireg_check) == Field::NEXT_NUMBER) auto_increment++; if (sql_field->stored_in_db) record_offset+= sql_field->pack_length; /* 累加當前字段的pack_length */}/*data_offset 的計算方式sql/unireg.cc:139*/data_offset= (create_info->null_bits + 7) / 8;/*length 的計算方式sql/unireg.cc:833*/length=field->pack_length; /* 這個pack_length就是和字段類型相關，以varchar為例*//* sql/sql_table.cc:3082 */sql_field->sql_type= MYSQL_TYPE_VAR_STRING;sql_field->pack_length= calc_pack_length(sql_field->sql_type, (uint) sql_field->length);關於 `calc_pack_length` 的計算方式如下：size_t calc_pack_length(enum_field_types type, size_t length){ switch (type) { case MYSQL_TYPE_VAR_STRING: case MYSQL_TYPE_STRING: case MYSQL_TYPE_DECIMAL: return (length); case MYSQL_TYPE_VARCHAR: return (length + (length < 256 ? 1: 2)); case MYSQL_TYPE_YEAR: case MYSQL_TYPE_TINY : return 1; case MYSQL_TYPE_SHORT : return 2; case MYSQL_TYPE_INT24: case MYSQL_TYPE_NEWDATE: return 3; case MYSQL_TYPE_TIME: return 3; case MYSQL_TYPE_TIME2: return length > MAX_TIME_WIDTH ? my_time_binary_length(length - MAX_TIME_WIDTH - 1) : 3; case MYSQL_TYPE_TIMESTAMP: return 4; case MYSQL_TYPE_TIMESTAMP2: return length > MAX_DATETIME_WIDTH ? my_timestamp_binary_length(length - MAX_DATETIME_WIDTH - 1) : 4; case MYSQL_TYPE_DATE: case MYSQL_TYPE_LONG : return 4; case MYSQL_TYPE_FLOAT : return sizeof(float); case MYSQL_TYPE_DOUBLE: return sizeof(double); case MYSQL_TYPE_DATETIME: return 8; case MYSQL_TYPE_DATETIME2: return length > MAX_DATETIME_WIDTH ? my_datetime_binary_length(length - MAX_DATETIME_WIDTH - 1) : 5; case MYSQL_TYPE_LONGLONG: return 8; /* Don't crash if no longlong */ case MYSQL_TYPE_NULL : return 0; case MYSQL_TYPE_TINY_BLOB: return 1+portable_sizeof_char_ptr; case MYSQL_TYPE_BLOB: return 2+portable_sizeof_char_ptr; case MYSQL_TYPE_MEDIUM_BLOB: return 3+portable_sizeof_char_ptr; case MYSQL_TYPE_LONG_BLOB: return 4+portable_sizeof_char_ptr; case MYSQL_TYPE_GEOMETRY: return 4+portable_sizeof_char_ptr; case MYSQL_TYPE_JSON: return 4+portable_sizeof_char_ptr; case MYSQL_TYPE_SET: case MYSQL_TYPE_ENUM: case MYSQL_TYPE_NEWDECIMAL: abort(); return 0; // This shouldn't happen case MYSQL_TYPE_BIT: return length / 8; default: return 0; }}/<code>2.2 小結
根據上面計算方式的梳理，在 MySQL Server 層計算長度的公式，可以寫成這樣的偽代碼：
<code>data_offset = (number_null_field + 7) / 8; /*number_null_field表示允許為null的字段數*/total_length = 0;for (int i = 0; i < n_fileds; i++) { total_length += calc_pack_length(field_type, length)}/*field_type:數據類型length:數據的字節數注意varchar(100) 的字節數，在utf8mb4環境下，字節數為400*/total_length += data_offset;/<code>通過上述計算，需要滿足 total_length <= 65535，即可通過 MySQL Server 層的檢查。
三、InnoDB 層限制的計算方法
3.1 計算過程
InnooDB 層面如果出現長度超過限制，報錯如下所示：
<code>ERROR 1118 (42000): Row size too large (> 8126).Changing some columns to TEXT or BLOB may help.In current row format, BLOB prefix of 0 bytes is stored inline./<code>
通過對報錯信息的搜索，其判斷方式如下：
<code>/* dict0dict.cc:2515 *//* Check the size limit on leaf pages. */if (rec_max_size >= page_rec_max) { ib::error_or_warn(strict) << "Cannot add field " << field->name << " in table " << table->name << " because after adding it, the row size is " << rec_max_size << " which is greater than maximum allowed" " size (" << page_rec_max << ") for a record on index leaf page."; return(TRUE);}/<code>其中 page_rec_max 的定義如下：
<code>/* dict0dict.cc:2431 */page_rec_max = srv_page_size == UNIV_PAGE_SIZE_MAX ? REC_MAX_DATA_SIZE - 1 : page_get_free_space_of_empty(comp) / 2;/* srv0srv.cc:222 */ulong srv_page_size = UNIV_PAGE_SIZE_DEF;/* 這個是默認值(DEF)，如果my.cnf中配置page_size為32K，那這個值就是32K *//* univ.i:343 */# define UNIV_PAGE_SIZE_DEF (1 << UNIV_PAGE_SIZE_SHIFT_DEF)/* univ.i:332 */# define UNIV_PAGE_SIZE_SHIFT_DEF 14/* univ.i:341 */# define UNIV_PAGE_SIZE_MAX (1 << UNIV_PAGE_SIZE_SHIFT_MAX)/* univ.i:330 */# define UNIV_PAGE_SIZE_SHIFT_MAX 16/* rem0rec.h:1092 */# define REC_MAX_DATA_SIZE 16384/<code>
從上面的源碼定義中可以看到：
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 的定義如下：
<code>/*************************************************************//**Calculates free space if a page is emptied.@return free space */UNIV_INLINEulintpage_get_free_space_of_empty(/*=========================*/ ulint comp) /*!< in: nonzero=compact page layout */{ if (comp) { /* 現在發佈版本的配置，會走這個邏輯 */ return((ulint)(UNIV_PAGE_SIZE - PAGE_NEW_SUPREMUM_END - PAGE_DIR - 2 * PAGE_DIR_SLOT_SIZE)); } /* 16384 - 120 - 8 - 4 = 16252 */ return((ulint)(UNIV_PAGE_SIZE - PAGE_OLD_SUPREMUM_END - PAGE_DIR - 2 * PAGE_DIR_SLOT_SIZE));}/* univ.i:312 */# define UNIV_PAGE_SIZE ((ulint) srv_page_size)/* 在我們的版本中為 16384 *//* page0page.h:122 */# define PAGE_NEW_SUPREMUM_END (PAGE_NEW_SUPREMUM + 8)/* 38 + 36 + 2*10 + 2*5 + 8 + 8 = 120 *//* page0page.h:119 */# define PAGE_NEW_SUPREMUM (PAGE_DATA + 2 * REC_N_NEW_EXTRA_BYTES + 8)/* page0page.h:104 */# define PAGE_DATA (PAGE_HEADER + 36 + 2 * FSEG_HEADER_SIZE)/* page0page.h:56 */# define PAGE_HEADER FSEG_PAGE_DATA/* fsp0types.h:68 */# define FSEG_PAGE_DATA FIL_PAGE_DATA/* fil0fil.h:517 */# define FIL_PAGE_DATA 38U/* fsp0types.h:81 */# define FSEG_HEADER_SIZE 10/* rem0rec.h:52 */# define REC_N_NEW_EXTRA_BYTES 5/* page0page.h:152 */# define PAGE_DIR FIL_PAGE_DATA_END/* fil0fil.h:525 */# define FIL_PAGE_DATA_END 8/* page0page.h:155 */# define PAGE_DIR_SLOT_SIZE 2/<code>如上所示，page_get_free_space_of_empty(comp) 返回的值為 16252，即 page_rec_max = 16252 / 2，剛好等於8126，其實從上面的報錯結果( > 8126)也可以推測出來。
接下來我們看一下一條記錄實際長度（ rec_max_size）的計算方式:
我們把代碼精簡一下，其計算過程如下：
<code>/* 下面是函數 dict_index_too_big_for_tree 中關於 rec_max_size 計算部分的內容 *//* 涉及到的輔助函數，下面也有相關說明 */rec_max_size = comp /* comp 為 true */ ? REC_N_NEW_EXTRA_BYTES /* rem0rec.h:52:#define REC_N_NEW_EXTRA_BYTES 5 */ : REC_N_OLD_EXTRA_BYTES; /* rem0rec.h:49:#define REC_N_OLD_EXTRA_BYTES 6 */rec_max_size += UT_BITS_IN_BYTES(new_index->n_nullable);/*這個宏的實現如下：ut0ut.h:222#define UT_BITS_IN_BYTES(b) (((b) + 7) / 8)即 (允許為空的字段個數 + 7) / 8*/for (i = 0; i < new_index->n_fields; i++) { const dict_field_t* field = dict_index_get_nth_field(new_index, i); const dict_col_t* col = dict_field_get_col(field); ulint field_max_size; ulint field_ext_max_size; /* In dtuple_convert_big_rec(), variable-length columns that are longer than BTR_EXTERN_LOCAL_STORED_MAX_SIZE may be chosen for external storage. Fixed-length columns, and all columns of secondary index records are always stored inline. */ /* Determine the maximum length of the index field. The field_ext_max_size should be computed as the worst case in rec_get_converted_size_comp() for REC_STATUS_ORDINARY records. */ field_max_size = dict_col_get_fixed_size(col, comp); /* 本質上是判斷是否定長，並獲得定長字段的字節數*/ /*如果是定長，則直接 goto add_field_size 進行求值*/ if (field_max_size && field->fixed_len != 0) { /* dict_index_add_col() should guarantee this */ ut_ad(!field->prefix_len || field->fixed_len == field->prefix_len); /* Fixed lengths are not encoded in ROW_FORMAT=COMPACT. */ field_ext_max_size = 0; goto add_field_size; } /*如果是變長，則計算最大值，然後求 field_ext_max_size的長度， 這個是用戶存儲字段長度的變量 這個只是默認初始值，後面會根據長度進行溢出頁的設置 */ field_max_size = dict_col_get_max_size(col); field_ext_max_size = field_max_size < 256 ? 1 : 2; if (field->prefix_len) { /* 建表判斷長度時，直接走else即可，這裡會在建立二級索引時用到，但是和聚集索引不是一顆B_Tree，因此可以忽略 */ if (field->prefix_len < field_max_size) { field_max_size = field->prefix_len; } } else if (field_max_size > BTR_EXTERN_LOCAL_STORED_MAX_SIZE && dict_index_is_clust(new_index)) { /* btr0types.h:56 #define BTR_EXTERN_LOCAL_STORED_MAX_SIZE \\ (BTR_EXTERN_FIELD_REF_SIZE * 2) btr0types.h:53 #define BTR_EXTERN_FIELD_REF_SIZE FIELD_REF_SIZE page0size.h:32 #define FIELD_REF_SIZE 20 所以這段代碼的意思為： 如果變長字段的最大值大於40 （溢出頁指針的2倍），則這個字段在頁內 只保留40個字節，且長度變量設置為1，即總共佔用41個字節。*/ /* In the worst case, we have a locally stored column of BTR_EXTERN_LOCAL_STORED_MAX_SIZE 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_LOCAL_STORED_MAX_SIZE; field_ext_max_size = 1; } if (comp) { /* Add the extra size for ROW_FORMAT=COMPACT. For ROW_FORMAT=REDUNDANT, these bytes were added to rec_max_size before this loop. */ rec_max_size += field_ext_max_size; } add_field_size: rec_max_size += field_max_size; /* Check the size limit on leaf pages. */ if (rec_max_size >= page_rec_max) { ib::error_or_warn(strict) << "Cannot add field " << field->name << " in table " << table->name << " because after adding it, the row size is " << rec_max_size << " which is greater than maximum allowed" " size (" << page_rec_max << ") for a record on index leaf page."; return(TRUE); }/ * 省略後續代碼，相關輔助函數：*/ulintdict_col_get_fixed_size(/*====================*/ const dict_col_t* col, /*!< in: column */ ulint comp) /*!< in: nonzero=ROW_FORMAT=COMPACT */{ return(dtype_get_fixed_size_low(col->mtype, col->prtype, col->len, col->mbminmaxlen, comp)); /* 根據實現，可以簡單的計算為： 1. 定長的為顯示指定的 字符數*字符寬度 - 例如 char(10), 在 utf8mb4 裡面，字節為 40 2. 變長的直接返回0 */}/***********************************************************************//**Returns the size of a fixed size data type, 0 if not a fixed size type.@return fixed size, or 0 */UNIV_INLINEulintdtype_get_fixed_size_low(/*=====================*/ ulint mtype, /*!< in: main type */ ulint prtype, /*!< in: precise type */ ulint len, /*!< in: length */ ulint mbminmaxlen, /*!< in: minimum and maximum length of a multibyte character, in bytes */ ulint comp) /*!< in: nonzero=ROW_FORMAT=COMPACT */{ switch (mtype) { case DATA_SYS:# ifdef UNIV_DEBUG switch (prtype & DATA_MYSQL_TYPE_MASK) { case DATA_ROW_ID: ut_ad(len == DATA_ROW_ID_LEN); break; case DATA_TRX_ID: ut_ad(len == DATA_TRX_ID_LEN); break; case DATA_ROLL_PTR: ut_ad(len == DATA_ROLL_PTR_LEN); break; default: ut_ad(0); return(0); }# endif /* UNIV_DEBUG */ // Fall through. case DATA_CHAR: case DATA_FIXBINARY: case DATA_INT: case DATA_FLOAT: case DATA_DOUBLE: case DATA_POINT: return(len); case DATA_MYSQL:# ifndef UNIV_HOTBACKUP if (prtype & DATA_BINARY_TYPE) { return(len); } else if (!comp) { return(len); } else {#ifdef UNIV_DEBUG ulint i_mbminlen, i_mbmaxlen; innobase_get_cset_width( dtype_get_charset_coll(prtype), &i_mbminlen, &i_mbmaxlen); ut_ad(DATA_MBMINMAXLEN(i_mbminlen, i_mbmaxlen) == mbminmaxlen);# endif /* UNIV_DEBUG */ if (DATA_MBMINLEN(mbminmaxlen) == DATA_MBMAXLEN(mbminmaxlen)) { return(len); } }# else /* !UNIV_HOTBACKUP */ return(len);# endif /* !UNIV_HOTBACKUP */ /* fall through for variable-length charsets */ case DATA_VARCHAR: case DATA_BINARY: case DATA_DECIMAL: case DATA_VARMYSQL: case DATA_VAR_POINT: case DATA_GEOMETRY: case DATA_BLOB: return(0); default: ut_error; } return(0);}ulintdict_col_get_max_size(/*==================*/ const dict_col_t* col) /*!< in: column */{ return(dtype_get_max_size_low(col->mtype, col->len));}ulintdtype_get_max_size_low(/*===================*/ ulint mtype, /*!< in: main type */ ulint len) /*!< in: length */{ switch (mtype) { case DATA_SYS: case DATA_CHAR: case DATA_FIXBINARY: case DATA_INT: case DATA_FLOAT: case DATA_DOUBLE: case DATA_MYSQL: case DATA_VARCHAR: case DATA_BINARY: case DATA_DECIMAL: case DATA_VARMYSQL: case DATA_POINT: return(len); case DATA_VAR_POINT: case DATA_GEOMETRY: case DATA_BLOB: break; default: ut_error; } return(ULINT_MAX);}/<code>3.2 小結
根據上面計算方式的梳理，在 InnoDB Server 層計算長度的公式，可以寫成這樣的偽代碼：
<code>rec_max_size = 5;/* innodb header 中固定的兩個值 DB_TRX_ID: 6 Byte DB_ROLL_PTR: 7 Byte*/rec_max_size += 6+7;/* rem0rec.h:70# define REC_NODE_PTR_SIZE 4dict0dict.cc:2536rec_max_size + REC_NODE_PTR_SIZE >= page_ptr_max*/rec_max_size += 4;rec_max_size += (number_null_field + 7) / 8; /*number_null_field表示允許為null的字段數*/for (int i = 0; i < n_fileds; i++) { /* 定長字段 */ if (field.type is fixed) { rec_max_size += filed.length; /*char需要 x4個字節的 utf8mb4類型*/ continue; } /* 變長字段 */ field_ext_max_size = 1; if (field.type is variable) { field_max_size = field.length > 40 ? 40 : field.length; } rec_max_size += field_max_size + field_ext_max_size;}/<code> 通過上述計算，需要滿足 rec_max_size < 8126，即可通過 InnoDB 層的檢查。
四、總結
1. 必須在 MySQL Server 層和 InnoDB 層同時滿足上述條件，才能建表成功；
2. 如果出現上述報錯情況，大部分是因為 varchar 等設置過大，建議可以將一些字段逐步縮小，或者用 text 進行代替；
分享到:
閱讀更多 愛可生 的文章
