MySQL 1レコード 8000バイトの壁を確認するSQL ROW_FORMAT=DYNAMIC 編
InnoDBのレコード長の限界
InnoDBのデフォルトのページサイズは、16Kです。この場合、最大レコード長は約8000バイトです。マニュアルで「約8000バイト」と曖昧な言い回しになっているのは、データの保存に使える領域以外にメタデータを保存する領域が必要だからでしょう。
可変長カラム (VARBINARY、VARCHAR、BLOB、および TEXT) を除き、行の最大長はデータベースページの半分より少し短くなります。つまり、デフォルトページサイズの 16K バイトでは、行の最大長が約 8000 バイトになります。
https://dev.mysql.com/doc/refman/5.6/ja/column-count-limit.html
また、「可変長カラム (VARBINARY、VARCHAR、BLOB、および TEXT) を除き」は可変長カラムはオーバーフローページに分割して保存されることを指し示しています。 可変長カラムは複数のページに分割して保存されるため、可変長カラムを含むテーブルでは、8000バイト以上保存できます。
-- 固定長のCHARだと、250 x 33 = 8250 バイトでエラー mysql> CREATE TABLE table_with_char (pk int primary key, c1 CHAR(250) NOT NULL, c2 CHAR(250) NOT NULL, c3 CHAR(250) NOT NULL, ： c33 CHAR(250) NOT NULL ) ROW_FORMAT=DYNAMIC CHARACTER SET 'latin1'; 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. -- varchar にすれば、一行8000バイトの壁を越えられる mysql> CREATE TABLE table_with_varchar (pk int primary key, c1 VARCHAR(250) NOT NULL, c2 VARCHAR(250) NOT NULL, c3 VARCHAR(250) NOT NULL, ： c33 VARCHAR(250) NOT NULL ) ROW_FORMAT=DYNAMIC CHARACTER SET 'latin1'; Query OK, 0 rows affected (0.02 sec)
では、可変長カラムであれば、いくらでも保存できるかというとそうではありません。 可変長カラムであっても、ポインタ分の 20バイト 消費します。
ROW_FORMAT=DYNAMIC または ROW_FORMAT=COMPRESSED で作成されたテーブルでは、カラムの長さおよび行全体の長さによっては、BLOB、TEXT、または VARCHAR カラムの値が完全にオフページに格納される場合もあります。オフページに格納されるカラムでは、クラスタ化されたインデックスのレコードに、オーバーフローページへの 20 バイトのポインタのみがカラムごとに 1 つずつ含まれます。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-compression-internals.html
また、TEXT/BLOB 型では、実際のデータの長さによっては、20バイトではなく、40バイト消費するケースもあります。
ROW_FORMAT=DYNAMIC または ROW_FORMAT=COMPRESSED で作成されたテーブルでは、40 バイト以下の TEXT および BLOB カラムは、常にインラインに格納されます。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-compression-internals.html
ROW_FORMAT=DYNAMIC で 8000バイトの壁を確認するSQL
さて、上記をふまえて、各テーブルのレコード長（オーバーフローページ分を除く）を表示するSQLを考えてみます。
こちらの記事のSQLを参考にさせて頂きました。この記事のSQLは ROW_FORMAT=COMPACT 用のSQLです。
ROW_FORMAT=DYNAMIC 用に上記の可変長カラムのオーバーフローページを考慮して微修正しました。
SELECT
TABLE_SCHEMA,
TABLE_NAME,
SUM(
CASE
WHEN DATA_TYPE = 'tinyint' then 1
WHEN DATA_TYPE = 'smallint' then 2
WHEN DATA_TYPE = 'mediumint' then 3
WHEN DATA_TYPE = 'int' then 4
WHEN DATA_TYPE = 'bigint' then 8
WHEN DATA_TYPE = 'float' then 4
WHEN DATA_TYPE = 'double' then 8
WHEN DATA_TYPE = 'decimal' then 0
+ FLOOR((NUMERIC_PRECISION - NUMERIC_SCALE) / 9) * 4
+ CEIL((NUMERIC_PRECISION - NUMERIC_SCALE) % 9 / 2)
+ FLOOR((NUMERIC_SCALE) / 9) * 4
+ CEIL((NUMERIC_SCALE) % 9 / 2)
WHEN DATA_TYPE = 'char' then CHARACTER_OCTET_LENGTH
WHEN DATA_TYPE = 'binary' then CHARACTER_OCTET_LENGTH
-- varchar/varbinary ではポインタ分 20BYTE
WHEN DATA_TYPE = 'varchar' then 20
WHEN DATA_TYPE = 'varbinary' then 20
-- text/blob では 40BYTE
WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?text$' then 40
WHEN DATA_TYPE REGEXP '^(tiny|medium|long)?blob$' then 40
WHEN DATA_TYPE = 'datetime' then 8
WHEN DATA_TYPE = 'date' then 3
WHEN DATA_TYPE = 'time' then 3
WHEN DATA_TYPE = 'year' then 1
WHEN DATA_TYPE = 'timestamp' then 4
WHEN DATA_TYPE = 'enum' then IF((CHARACTER_OCTET_LENGTH / CHARACTER_MAXIMUM_LENGTH) < 256, 1, 2)
ELSE NULL
END
) as SIZE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
GROUP BY TABLE_SCHEMA, TABLE_NAME ORDER BY TABLE_SCHEMA, TABLE_NAME;
実行結果例）
+--------------+--------------------+------+ | TABLE_SCHEMA | TABLE_NAME | SIZE | +--------------+--------------------+------+ | test | col1017 | 1020 | | test | t2 | 44 | | test | table_with_varchar | 664 | | test | text_x_197 | 7884 | | test | text_x_200 | 7884 | | test | text_x_null197 | 7884 | | test | var1 | 20 | +--------------+--------------------+------+ 7 rows in set (0.03 sec)
ソースコード
MySQL 5.6 では storage/innobase/dict/dict0dict.cc の dict_index_too_big_for_tree でCREATE TABLE 時のレコード長のチェックがされてました。
rec_max_size の値を追いかけると、各型が何バイトで計算されるかわかりました。
/****************************************************************//**
If a record of this index might not fit on a single B-tree page,
return TRUE.
@return TRUE if the index record could become too big */
static
ibool
dict_index_too_big_for_tree(
/*========================*/
const dict_table_t* table, /*!< in: table */
const dict_index_t* new_index) /*!< in: index */
{
ulint zip_size;
ulint comp;
ulint i;
/* maximum possible storage size of a record */
ulint rec_max_size;
/* maximum allowed size of a record on a leaf page */
ulint page_rec_max;
/* maximum allowed size of a node pointer record */
ulint page_ptr_max;
<snip>
add_field_size:
rec_max_size += field_max_size;
/* Check the size limit on leaf pages. */
if (UNIV_UNLIKELY(rec_max_size >= page_rec_max)) {
return(TRUE);
}
/* Check the size limit on non-leaf pages. Records
stored in non-leaf B-tree pages consist of the unique
columns of the record (the key columns of the B-tree)
and a node pointer field. When we have processed the
unique columns, rec_max_size equals the size of the
node pointer record minus the node pointer column. */
if (i + 1 == dict_index_get_n_unique_in_tree(new_index)
&& rec_max_size + REC_NODE_PTR_SIZE >= page_ptr_max) {
return(TRUE);
}
