mita2 database life

主にMySQLに関するメモです

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を参考にさせて頂きました。この記事のSQLROW_FORMAT=COMPACT 用のSQLです。 ROW_FORMAT=DYNAMIC 用に上記の可変長カラムのオーバーフローページを考慮して微修正しました。

qiita.com

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.ccdict_index_too_big_for_treeCREATE 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);
        }