MySQL の型定義から行サイズを見積もり、設計リスクを判定する

MySQL

Last updated at 2026-07-23Posted at 2025-09-04

MySQL の型定義から行サイズを見積もり、設計リスクを判定する

MySQL、特にInnoDBには、行サイズに関する主な制限が2つある。

1つ目は、MySQLサーバー層にある 1行あたり最大65,535バイトという制限。

2つ目は、InnoDBがデータベースページ内に保持できる、クラスタインデックスレコードのローカルサイズ制限である。デフォルトの16KBページでは約8KBで、代表的なエラーメッセージでは8,126バイトと表示される。

ERROR 1118 (42000): Row size too large (> 8126).
Changing some columns to TEXT or BLOB may help.

ただし、DDLの型定義だけから、InnoDB内部の最終的なレコードサイズを完全に再現するのは難しい。

InnoDBは、行がページ内に収まらない場合、長い可変長列を順番にページ外へ移動する。どの列がページ外へ移動するかは、実際の値の長さ、ROW_FORMAT、ページサイズ、主キーなどによって変化するためである。(dev.mysql.com)

そこで本記事では、単一の「推定行サイズ」を出すのではなく、複数のシナリオを計算し、設計上のリスクを判定する。

MySQLの65,535バイト制限に対する定義上の概算
TEXT / BLOBを除く、宣言最大長ベースの行内サイズ
ROW_FORMAT=DYNAMICで最大限オフページ化した場合の下限
ROW_FORMAT=COMPACTで最大限オフページ化した場合の下限
オフページ化による削減量と依存率
PASS_CANDIDATE、WARNING、FAILなどの判定

背景：2種類の行サイズ制限

MySQLの65,535バイト制限

ストレージエンジンがより大きな行を扱える場合でも、MySQLの内部表現では、全カラムの合計サイズが65,535バイトに制限される。

この制限を評価する場合、VARCHARやVARBINARYは宣言上の最大バイト数と長さ情報を含めて計算される。

一方、TEXTとBLOBは本文全体ではなく、型に応じて9～12バイトだけが65,535バイト制限に寄与する。(dev.mysql.com)

型	65,535バイト制限への寄与
`TINYTEXT` / `TINYBLOB`	9バイト
`TEXT` / `BLOB`	10バイト
`MEDIUMTEXT` / `MEDIUMBLOB`	11バイト
`LONGTEXT` / `LONGBLOB`	12バイト

ここで重要なのは、9～12バイトという値はMySQLの65,535バイト制限を計算するための値であり、InnoDBの実際の行内サイズではないという点である。

短いTEXTやBLOBの値は、実際にはページ内に保存される場合がある。

InnoDBのローカル行サイズ制限

InnoDBのページサイズが4KB、8KB、16KB、32KBの場合、ページ内にローカル保存できる最大行サイズは、ページの半分弱となる。

デフォルトの16KBページでは約8,000バイトで、一般的なエラーメッセージには8,126バイトと表示される。

64KBページの場合でも、最大行サイズは約16KBに制限される。(dev.mysql.com)

行がこの制限を超えると、InnoDBは長い可変長列を選び、行が収まるまでオフページストレージへ移動する。

DYNAMICとCOMPACTの違い

ROW_FORMAT=DYNAMICでは、長いVARCHAR、VARBINARY、TEXT、BLOBなどを完全にオフページへ移動できる。

その場合、クラスタインデックスレコードには20バイトの外部参照が残る。また、外部保存された列の長さ情報には2バイトが使われるため、本記事のSQLでは1列あたり 22バイトとして計算する。(dev.mysql.com)

DYNAMICの外部化後
= 20バイトの外部参照
+ 2バイトの長さ情報
= 22バイト

ROW_FORMAT=COMPACTでは、外部化した値の先頭768バイトが行内に残り、その後に20バイトの外部参照が保存される。

本記事のSQLでは、長さ情報2バイトを加え、1列あたり 790バイトとして計算する。(dev.mysql.com)

COMPACTの外部化後
= 768バイトのprefix
+ 20バイトの外部参照
+ 2バイトの長さ情報
= 790バイト

ただし、すべての可変長値が無条件にオフページ化されるわけではない。

40バイト以下の可変長フィールドは外部化の対象にならない
COMPACTでは768バイト以下の値は外部化の対象にならない
行全体がページ内に収まる場合は、長い値も行内に保存される
大きい列から順番に外部化される

そのため、22バイトや790バイトは実際の通常時サイズではなく、外部化可能な列を最大限外部化した場合の下限寄りの値である。(dev.mysql.com)

計算に含めるInnoDBの管理領域

COMPACTおよびDYNAMIC形式のクラスタインデックスレコードには、主に次の管理領域がある。

項目	サイズ
レコードヘッダ	5バイト
トランザクションID	6バイト
ロールポインタ	7バイト
NULLビットマップ	`CEIL(NULL許可列数 / 8)`
隠し行ID	明示的な主キーがない場合に6バイト

可変長フィールドには、さらに1バイトまたは2バイトの長さ情報が必要になる。(dev.mysql.com)

明示的なPRIMARY KEYがない場合、InnoDBは最初のUNIQUE NOT NULLインデックスをクラスタインデックスとして使用する。それも存在しない場合は、6バイトの隠し行IDを生成する。(dev.mysql.com)

この挙動を完全に静的SQLで再現すると複雑になるため、今回のSQLでは明示的な主キーがないテーブルをMANUAL_REVIEW_NO_PRIMARY_KEYとして扱う。

実際のSQL

MySQL 8.0およびMySQL 8.4を想定している。

SET @schema = 'your_schema';
SET @table_filter = NULL;          -- 全テーブルなら NULL
SET @local_limit_override = NULL;  -- 上限を手動指定する場合に設定

WITH
config_base AS (
  SELECT
    @@GLOBAL.innodb_page_size AS innodb_page_size,
    COALESCE(
      @local_limit_override,
      CASE @@GLOBAL.innodb_page_size
        WHEN 4096  THEN 2000
        WHEN 8192  THEN 4000
        WHEN 16384 THEN 8126
        WHEN 32768 THEN 16200
        WHEN 65536 THEN 16200
        ELSE LEAST(
          FLOOR(@@GLOBAL.innodb_page_size * 0.49),
          16200
        )
      END
    ) AS estimated_local_limit_bytes
),
config AS (
  SELECT
    innodb_page_size,
    estimated_local_limit_bytes,
    FLOOR(
      estimated_local_limit_bytes * 0.85
    ) AS review_local_limit_bytes
  FROM config_base
),
table_meta AS (
  SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    t.ENGINE,
    UPPER(t.ROW_FORMAT) AS row_format,
    EXISTS (
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
      WHERE tc.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA
        AND tc.TABLE_NAME = t.TABLE_NAME
        AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
    ) AS has_primary_key
  FROM INFORMATION_SCHEMA.TABLES t
  WHERE t.TABLE_SCHEMA = @schema
    AND t.TABLE_TYPE = 'BASE TABLE'
    AND (
      @table_filter IS NULL
      OR t.TABLE_NAME = @table_filter
    )
),
cols AS (
  SELECT
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.ORDINAL_POSITION,
    c.COLUMN_NAME,
    LOWER(c.DATA_TYPE) AS data_type,
    c.IS_NULLABLE,
    c.CHARACTER_OCTET_LENGTH,
    c.NUMERIC_PRECISION,
    c.NUMERIC_SCALE,
    c.DATETIME_PRECISION,

    EXISTS (
      SELECT 1
      FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
      WHERE kcu.CONSTRAINT_SCHEMA = c.TABLE_SCHEMA
        AND kcu.TABLE_NAME = c.TABLE_NAME
        AND kcu.COLUMN_NAME = c.COLUMN_NAME
        AND kcu.CONSTRAINT_NAME = 'PRIMARY'
    ) AS is_primary_key_column,

    CASE
      WHEN UPPER(c.EXTRA) LIKE '%VIRTUAL GENERATED%'
        THEN 0
      ELSE 1
    END AS is_stored

  FROM INFORMATION_SCHEMA.COLUMNS c

  JOIN table_meta t
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
   AND t.TABLE_NAME = c.TABLE_NAME
),
type_facts AS (
  SELECT
    c.*,

    CASE
      WHEN data_type IN ('decimal', 'numeric') THEN
        (
          (NUMERIC_PRECISION - NUMERIC_SCALE) DIV 9
        ) * 4
        + CASE
            WHEN MOD(
              NUMERIC_PRECISION - NUMERIC_SCALE,
              9
            ) = 0 THEN 0
            WHEN MOD(
              NUMERIC_PRECISION - NUMERIC_SCALE,
              9
            ) <= 2 THEN 1
            WHEN MOD(
              NUMERIC_PRECISION - NUMERIC_SCALE,
              9
            ) <= 4 THEN 2
            WHEN MOD(
              NUMERIC_PRECISION - NUMERIC_SCALE,
              9
            ) <= 6 THEN 3
            ELSE 4
          END
        + (NUMERIC_SCALE DIV 9) * 4
        + CASE
            WHEN MOD(NUMERIC_SCALE, 9) = 0 THEN 0
            WHEN MOD(NUMERIC_SCALE, 9) <= 2 THEN 1
            WHEN MOD(NUMERIC_SCALE, 9) <= 4 THEN 2
            WHEN MOD(NUMERIC_SCALE, 9) <= 6 THEN 3
            ELSE 4
          END
      ELSE 0
    END AS decimal_bytes,

    CASE
      WHEN data_type IN (
        'char',
        'varchar',
        'binary',
        'varbinary'
      )
        THEN COALESCE(CHARACTER_OCTET_LENGTH, 0)

      WHEN data_type IN (
        'tinytext',
        'tinyblob'
      )
        THEN 255

      WHEN data_type IN (
        'text',
        'blob'
      )
        THEN 65535

      WHEN data_type IN (
        'mediumtext',
        'mediumblob'
      )
        THEN 16777215

      WHEN data_type IN (
        'longtext',
        'longblob',
        'json'
      )
        THEN 4294967295

      ELSE 0
    END AS max_payload_bytes,

    CASE
      WHEN data_type IN (
        'tinytext',
        'text',
        'mediumtext',
        'longtext',
        'tinyblob',
        'blob',
        'mediumblob',
        'longblob',
        'json'
      ) THEN 1
      ELSE 0
    END AS is_lob_like,

    CASE
      WHEN data_type = 'json' THEN 1
      ELSE 0
    END AS is_approximation,

    CASE
      WHEN data_type IN (
        'tinyint',
        'smallint',
        'mediumint',
        'int',
        'integer',
        'bigint',
        'float',
        'double',
        'real',
        'decimal',
        'numeric',
        'bit',
        'year',
        'date',
        'time',
        'datetime',
        'timestamp',
        'char',
        'varchar',
        'binary',
        'varbinary',
        'tinytext',
        'text',
        'mediumtext',
        'longtext',
        'tinyblob',
        'blob',
        'mediumblob',
        'longblob',
        'enum',
        'set',
        'json'
      ) THEN 0
      ELSE 1
    END AS is_unsupported

  FROM cols c
),
sized AS (
  SELECT
    f.*,

    CASE
      WHEN is_stored = 0 THEN 0

      WHEN data_type = 'tinyint' THEN 1
      WHEN data_type = 'smallint' THEN 2
      WHEN data_type = 'mediumint' THEN 3
      WHEN data_type IN ('int', 'integer') THEN 4
      WHEN data_type = 'bigint' THEN 8

      WHEN data_type = 'float' THEN
        IF(
          COALESCE(NUMERIC_PRECISION, 24) > 24,
          8,
          4
        )

      WHEN data_type IN ('double', 'real') THEN 8

      WHEN data_type IN ('decimal', 'numeric')
        THEN decimal_bytes

      WHEN data_type = 'bit' THEN
        (
          COALESCE(NUMERIC_PRECISION, 1) + 7
        ) DIV 8

      WHEN data_type = 'year' THEN 1
      WHEN data_type = 'date' THEN 3

      WHEN data_type = 'time' THEN
        3 + CEIL(
          COALESCE(DATETIME_PRECISION, 0) / 2
        )

      WHEN data_type = 'datetime' THEN
        5 + CEIL(
          COALESCE(DATETIME_PRECISION, 0) / 2
        )

      WHEN data_type = 'timestamp' THEN
        4 + CEIL(
          COALESCE(DATETIME_PRECISION, 0) / 2
        )

      WHEN data_type = 'binary' THEN
        COALESCE(CHARACTER_OCTET_LENGTH, 0)

      WHEN data_type = 'char'
       AND COALESCE(CHARACTER_OCTET_LENGTH, 0) < 768
        THEN COALESCE(CHARACTER_OCTET_LENGTH, 0)

      WHEN data_type = 'enum' THEN 2
      WHEN data_type = 'set' THEN 8

      ELSE 0
    END AS fixed_bytes,

    CASE
      WHEN data_type IN ('varchar', 'varbinary') THEN
        IF(max_payload_bytes > 255, 2, 1)

      WHEN data_type = 'char'
       AND max_payload_bytes >= 768
        THEN 2

      WHEN is_lob_like = 1 THEN
        IF(max_payload_bytes > 255, 2, 1)

      ELSE 0
    END AS innodb_length_bytes,

    CASE
      WHEN is_stored = 0 THEN 0

      WHEN data_type = 'tinyint' THEN 1
      WHEN data_type = 'smallint' THEN 2
      WHEN data_type = 'mediumint' THEN 3
      WHEN data_type IN ('int', 'integer') THEN 4
      WHEN data_type = 'bigint' THEN 8

      WHEN data_type = 'float' THEN
        IF(
          COALESCE(NUMERIC_PRECISION, 24) > 24,
          8,
          4
        )

      WHEN data_type IN ('double', 'real') THEN 8

      WHEN data_type IN ('decimal', 'numeric')
        THEN decimal_bytes

      WHEN data_type = 'bit' THEN
        (
          COALESCE(NUMERIC_PRECISION, 1) + 7
        ) DIV 8

      WHEN data_type = 'year' THEN 1
      WHEN data_type = 'date' THEN 3

      WHEN data_type = 'time' THEN
        3 + CEIL(
          COALESCE(DATETIME_PRECISION, 0) / 2
        )

      WHEN data_type = 'datetime' THEN
        5 + CEIL(
          COALESCE(DATETIME_PRECISION, 0) / 2
        )

      WHEN data_type = 'timestamp' THEN
        4 + CEIL(
          COALESCE(DATETIME_PRECISION, 0) / 2
        )

      WHEN data_type IN ('char', 'binary') THEN
        COALESCE(CHARACTER_OCTET_LENGTH, 0)

      WHEN data_type IN ('varchar', 'varbinary') THEN
        max_payload_bytes
        + IF(max_payload_bytes > 255, 2, 1)

      WHEN data_type IN ('tinytext', 'tinyblob')
        THEN 9

      WHEN data_type IN ('text', 'blob')
        THEN 10

      WHEN data_type IN ('mediumtext', 'mediumblob')
        THEN 11

      WHEN data_type IN (
        'longtext',
        'longblob',
        'json'
      )
        THEN 12

      WHEN data_type = 'enum' THEN 2
      WHEN data_type = 'set' THEN 8

      ELSE 0
    END AS mysql_declared_bytes

  FROM type_facts f
),
per_col AS (
  SELECT
    s.*,

    CASE
      WHEN is_stored = 0
        OR is_lob_like = 1
        THEN 0

      WHEN fixed_bytes > 0
        THEN fixed_bytes

      ELSE max_payload_bytes + innodb_length_bytes
    END AS bounded_inline_bytes,

    CASE
      WHEN is_stored = 0
        OR is_lob_like = 1
        THEN 0

      WHEN fixed_bytes > 0
        THEN fixed_bytes

      WHEN is_primary_key_column = 0
       AND data_type IN ('varchar', 'varbinary')
       AND max_payload_bytes > 40
        THEN 22

      WHEN is_primary_key_column = 0
       AND data_type = 'char'
       AND max_payload_bytes >= 768
        THEN 22

      ELSE max_payload_bytes + innodb_length_bytes
    END AS bounded_dynamic_bytes,

    CASE
      WHEN is_stored = 0
        THEN 0

      WHEN fixed_bytes > 0
        THEN fixed_bytes

      WHEN is_lob_like = 1
       AND is_primary_key_column = 0
        THEN 22

      WHEN is_primary_key_column = 0
       AND data_type IN ('varchar', 'varbinary')
       AND max_payload_bytes > 40
        THEN 22

      WHEN is_primary_key_column = 0
       AND data_type = 'char'
       AND max_payload_bytes >= 768
        THEN 22

      ELSE max_payload_bytes + innodb_length_bytes
    END AS dynamic_local_min_bytes,

    CASE
      WHEN is_stored = 0
        THEN 0

      WHEN fixed_bytes > 0
        THEN fixed_bytes

      WHEN is_lob_like = 1
       AND is_primary_key_column = 0
       AND max_payload_bytes > 768
        THEN 790

      WHEN is_lob_like = 1
        THEN max_payload_bytes + innodb_length_bytes

      WHEN is_primary_key_column = 0
       AND data_type IN ('varchar', 'varbinary')
       AND max_payload_bytes > 768
        THEN 790

      WHEN is_primary_key_column = 0
       AND data_type = 'char'
       AND max_payload_bytes > 768
        THEN 790

      ELSE max_payload_bytes + innodb_length_bytes
    END AS compact_local_min_bytes,

    CASE
      WHEN is_stored = 1
       AND is_primary_key_column = 0
       AND (
         is_lob_like = 1
         OR (
           data_type IN ('varchar', 'varbinary')
           AND max_payload_bytes > 40
         )
         OR (
           data_type = 'char'
           AND max_payload_bytes >= 768
         )
       )
        THEN 1
      ELSE 0
    END AS dynamic_externalizable,

    CASE
      WHEN is_stored = 1
       AND is_primary_key_column = 0
       AND (
         (
           is_lob_like = 1
           AND max_payload_bytes > 768
         )
         OR (
           data_type IN ('varchar', 'varbinary')
           AND max_payload_bytes > 768
         )
         OR (
           data_type = 'char'
           AND max_payload_bytes > 768
         )
       )
        THEN 1
      ELSE 0
    END AS compact_externalizable

  FROM sized s
),
per_table AS (
  SELECT
    TABLE_SCHEMA,
    TABLE_NAME,

    COUNT(*) AS defined_column_count,
    SUM(is_stored) AS stored_column_count,

    SUM(
      is_stored = 1
      AND IS_NULLABLE = 'YES'
    ) AS nullable_column_count,

    SUM(
      is_stored = 1
      AND is_primary_key_column = 1
    ) AS primary_key_column_count,

    SUM(
      is_stored = 1
      AND is_lob_like = 1
    ) AS lob_like_column_count,

    SUM(
      dynamic_externalizable
    ) AS dynamic_externalizable_column_count,

    SUM(
      compact_externalizable
    ) AS compact_externalizable_column_count,

    SUM(
      is_unsupported * is_stored
    ) AS unsupported_column_count,

    SUM(
      is_approximation * is_stored
    ) AS approximation_column_count,

    SUM(
      mysql_declared_bytes
    ) AS mysql_declared_column_bytes,

    SUM(
      bounded_inline_bytes
    ) AS bounded_inline_column_bytes,

    SUM(
      bounded_dynamic_bytes
    ) AS bounded_dynamic_column_bytes,

    SUM(
      dynamic_local_min_bytes
    ) AS dynamic_local_min_column_bytes,

    SUM(
      compact_local_min_bytes
    ) AS compact_local_min_column_bytes,

    GROUP_CONCAT(
      CASE
        WHEN is_unsupported = 1
         AND is_stored = 1
          THEN CONCAT(COLUMN_NAME, ':', data_type)
      END
      ORDER BY ORDINAL_POSITION
      SEPARATOR ', '
    ) AS unsupported_columns,

    GROUP_CONCAT(
      CASE
        WHEN is_approximation = 1
         AND is_stored = 1
          THEN CONCAT(COLUMN_NAME, ':', data_type)
      END
      ORDER BY ORDINAL_POSITION
      SEPARATOR ', '
    ) AS approximation_columns

  FROM per_col
  GROUP BY
    TABLE_SCHEMA,
    TABLE_NAME
),
calculated AS (
  SELECT
    pt.*,
    tm.ENGINE,
    tm.row_format,
    tm.has_primary_key,

    cfg.innodb_page_size,
    cfg.estimated_local_limit_bytes,
    cfg.review_local_limit_bytes,

    CEIL(
      pt.nullable_column_count / 8
    ) AS null_bitmap_bytes,

    5 + 6 + 7
    + IF(tm.has_primary_key = 1, 0, 6)
      AS record_system_bytes,

    pt.mysql_declared_column_bytes
    + CEIL(pt.nullable_column_count / 8)
      AS mysql_estimated_max_row_bytes,

    pt.bounded_inline_column_bytes
    + CEIL(pt.nullable_column_count / 8)
    + 5 + 6 + 7
    + IF(tm.has_primary_key = 1, 0, 6)
      AS innodb_bounded_inline_bytes,

    pt.dynamic_local_min_column_bytes
    + CEIL(pt.nullable_column_count / 8)
    + 5 + 6 + 7
    + IF(tm.has_primary_key = 1, 0, 6)
      AS innodb_dynamic_lower_bytes,

    pt.compact_local_min_column_bytes
    + CEIL(pt.nullable_column_count / 8)
    + 5 + 6 + 7
    + IF(tm.has_primary_key = 1, 0, 6)
      AS innodb_compact_lower_bytes

  FROM per_table pt

  JOIN table_meta tm
    ON tm.TABLE_SCHEMA = pt.TABLE_SCHEMA
   AND tm.TABLE_NAME = pt.TABLE_NAME

  CROSS JOIN config cfg
),
judged AS (
  SELECT
    c.*,

    CASE
      WHEN row_format = 'DYNAMIC'
        THEN innodb_dynamic_lower_bytes

      WHEN row_format = 'COMPACT'
        THEN innodb_compact_lower_bytes

      ELSE NULL
    END AS applicable_local_lower_bytes

  FROM calculated c
)
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  ENGINE,
  row_format,
  has_primary_key,
  innodb_page_size,

  defined_column_count,
  stored_column_count,
  nullable_column_count,
  primary_key_column_count,
  lob_like_column_count,

  dynamic_externalizable_column_count,
  compact_externalizable_column_count,

  estimated_local_limit_bytes,
  review_local_limit_bytes,

  mysql_estimated_max_row_bytes,

  65535 - mysql_estimated_max_row_bytes
    AS mysql_65535_margin_bytes,

  innodb_bounded_inline_bytes,

  innodb_dynamic_lower_bytes,

  estimated_local_limit_bytes
  - innodb_dynamic_lower_bytes
    AS dynamic_margin_bytes,

  innodb_compact_lower_bytes,

  estimated_local_limit_bytes
  - innodb_compact_lower_bytes
    AS compact_margin_bytes,

  bounded_inline_column_bytes
  - bounded_dynamic_column_bytes
    AS dynamic_saved_bounded_bytes,

  ROUND(
    100
    * (
      bounded_inline_column_bytes
      - bounded_dynamic_column_bytes
    )
    / NULLIF(bounded_inline_column_bytes, 0),
    1
  ) AS dynamic_offpage_dependency_pct,

  unsupported_column_count,
  unsupported_columns,

  approximation_column_count,
  approximation_columns,

  CASE
    WHEN ENGINE <> 'InnoDB'
      THEN 'NOT_INNODB'

    WHEN unsupported_column_count > 0
      THEN 'MANUAL_REVIEW'

    WHEN mysql_estimated_max_row_bytes > 65535
      THEN 'FAIL_MYSQL_65535'

    WHEN has_primary_key = 0
      THEN 'MANUAL_REVIEW_NO_PRIMARY_KEY'

    WHEN row_format = 'DYNAMIC'
     AND innodb_dynamic_lower_bytes
         > estimated_local_limit_bytes
      THEN 'FAIL_INNODB_LOCAL'

    WHEN row_format = 'COMPACT'
     AND innodb_compact_lower_bytes
         > estimated_local_limit_bytes
      THEN 'FAIL_INNODB_LOCAL'

    WHEN row_format NOT IN ('DYNAMIC', 'COMPACT')
      OR row_format IS NULL
      THEN 'MANUAL_REVIEW_ROW_FORMAT'

    WHEN applicable_local_lower_bytes
         > review_local_limit_bytes
      THEN 'WARNING_NEAR_LOCAL_LIMIT'

    WHEN innodb_bounded_inline_bytes
         > estimated_local_limit_bytes
      THEN 'WARNING_OFFPAGE_DEPENDENT'

    WHEN mysql_estimated_max_row_bytes > 62258
      THEN 'WARNING_NEAR_MYSQL_LIMIT'

    ELSE 'PASS_CANDIDATE'
  END AS design_verdict,

  CASE
    WHEN ENGINE <> 'InnoDB'
      THEN 'InnoDB以外はこの判定の対象外'

    WHEN unsupported_column_count > 0
      THEN CONCAT(
        '未対応型あり: ',
        COALESCE(unsupported_columns, '')
      )

    WHEN mysql_estimated_max_row_bytes > 65535
      THEN 'MySQLの65,535バイト制限を超える見込み'

    WHEN has_primary_key = 0
      THEN '明示的なPRIMARY KEYがないためクラスタ化キーを個別確認する'

    WHEN row_format = 'DYNAMIC'
     AND innodb_dynamic_lower_bytes
         > estimated_local_limit_bytes
      THEN 'DYNAMICで外部化可能な列を退避してもローカル制限を超える見込み'

    WHEN row_format = 'COMPACT'
     AND innodb_compact_lower_bytes
         > estimated_local_limit_bytes
      THEN 'COMPACTで768バイトprefixを残してもローカル制限を超える見込み'

    WHEN row_format NOT IN ('DYNAMIC', 'COMPACT')
      OR row_format IS NULL
      THEN CONCAT(
        'ROW_FORMATは個別確認が必要: ',
        COALESCE(row_format, 'NULL')
      )

    WHEN applicable_local_lower_bytes
         > review_local_limit_bytes
      THEN '外部化後の下限値がローカル制限の85%を超えている'

    WHEN innodb_bounded_inline_bytes
         > estimated_local_limit_bytes
      THEN '成立がオフページ化に依存する'

    WHEN mysql_estimated_max_row_bytes > 62258
      THEN 'MySQLの65,535バイト制限に対する余裕が5%未満'

    WHEN approximation_column_count > 0
      THEN CONCAT(
        '概算上は余裕あり。ただし近似型あり: ',
        COALESCE(approximation_columns, '')
      )

    ELSE
      '概算上は余裕あり。変更DDLは検証環境で最終確認する'
  END AS design_comment

FROM judged

ORDER BY
  CASE
    WHEN design_verdict LIKE 'FAIL%' THEN 1
    WHEN design_verdict LIKE 'MANUAL%' THEN 2
    WHEN design_verdict LIKE 'WARNING%' THEN 3
    WHEN design_verdict = 'PASS_CANDIDATE' THEN 4
    ELSE 5
  END,
  TABLE_SCHEMA,
  TABLE_NAME;

出力結果の見方

このSQLは、テーブルごとに1行の診断結果を返す。

最初にdesign_verdictとdesign_commentを確認し、必要に応じて各サイズ値とマージン値を確認する。

基本情報

キー	意味
`TABLE_SCHEMA`	対象テーブルのデータベース名
`TABLE_NAME`	対象テーブル名
`ENGINE`	ストレージエンジン
`row_format`	現在の行形式。主に`DYNAMIC`または`COMPACT`
`has_primary_key`	明示的な主キーがあれば`1`、なければ`0`
`innodb_page_size`	InnoDBのページサイズ。単位はバイト

カラム数

キー	意味
`defined_column_count`	テーブルに定義されている全カラム数
`stored_column_count`	行サイズ計算の対象とした物理保存カラム数。VIRTUAL生成列は除く
`nullable_column_count`	NULLを許可する物理保存カラム数
`primary_key_column_count`	明示的な主キーを構成するカラム数
`lob_like_column_count`	`TEXT`、`BLOB`、`JSON`系カラム数
`dynamic_externalizable_column_count`	DYNAMICでオフページ化候補としたカラム数
`compact_externalizable_column_count`	COMPACTでオフページ化候補としたカラム数

dynamic_externalizable_column_countとcompact_externalizable_column_countは、実際にオフページ化されるカラム数ではない。

実際のオフページ化は、格納する値の長さと行全体のサイズによって決まる。

判定基準

`estimated_local_limit_bytes`

InnoDBのページ内ローカルサイズについて、判定に使用する推定上限。単位はバイト。

デフォルトではinnodb_page_sizeに応じて次の値を使用する。

`innodb_page_size`	`estimated_local_limit_bytes`
`4096`	`2000`
`8192`	`4000`
`16384`	`8126`
`32768`	`16200`
`65536`	`16200`

@local_limit_overrideを指定した場合は、その値が優先される。

16KBページの8,126バイト以外は、設計判定用の近似値である。

`review_local_limit_bytes`

ローカルサイズ上限に近づいていることを検出するための警告ライン。単位はバイト。

review_local_limit_bytes
= FLOOR(estimated_local_limit_bytes × 0.85)

85%はMySQLの公式制限ではなく、本SQLで設定した設計レビュー用の警告基準である。

MySQLの65,535バイト制限

`mysql_estimated_max_row_bytes`

MySQLサーバー層の65,535バイト制限に対する概算値。単位はバイト。

主に次を合算する。

固定長型のサイズ
CHAR、BINARYの最大サイズ
VARCHAR、VARBINARYの最大サイズと長さ情報
TEXT、BLOBの9～12バイト
JSONの12バイト近似
NULLビットマップ

InnoDBの実際の物理レコードサイズではない。

`mysql_65535_margin_bytes`

65,535バイトまでの残り容量。

mysql_65535_margin_bytes
= 65535 - mysql_estimated_max_row_bytes

値	意味
正の値	上限まで余裕がある
`0`	概算上、上限と同じ
負の値	概算上、上限を超えている

InnoDBの行内サイズ

`innodb_bounded_inline_bytes`

固定長型、CHAR、VARCHAR、BINARY、VARBINARYについて、宣言最大長の値が行内にあると仮定した概算値。単位はバイト。

次も含む。

NULLビットマップ
InnoDBのレコード管理領域

TEXT、BLOB、JSONの本文は含めない。

そのため、lob_like_column_countが1以上の場合は、テーブル全体の最大行内サイズではない。

`innodb_dynamic_lower_bytes`

ROW_FORMAT=DYNAMICで、外部化可能なカラムを最大限オフページ化したと仮定した、行内サイズの下限寄りの概算値。単位はバイト。

外部化候補は、原則として1カラムあたり22バイトとして計算する。

20バイトの外部参照
+ 2バイトの長さ情報
= 22バイト

固定長部分、主キー列、短い可変長列、NULLビットマップ、InnoDBの管理領域も合算する。

`dynamic_margin_bytes`

DYNAMICの下限値から、推定ローカル上限までの余裕。

dynamic_margin_bytes
= estimated_local_limit_bytes
- innodb_dynamic_lower_bytes

値	意味
正の値	最大限外部化した場合は上限内
`0`	概算上、上限と同じ
負の値	最大限外部化しても上限を超える見込み

`innodb_compact_lower_bytes`

ROW_FORMAT=COMPACTで、外部化可能なカラムを最大限オフページ化したと仮定した、行内サイズの下限寄りの概算値。単位はバイト。

外部化候補は、原則として1カラムあたり790バイトとして計算する。

768バイトの行内prefix
+ 20バイトの外部参照
+ 2バイトの長さ情報
= 790バイト

最大サイズが768バイト以下のカラムは、全体を行内に残す前提で計算する。

`compact_margin_bytes`

COMPACTの下限値から、推定ローカル上限までの余裕。

compact_margin_bytes
= estimated_local_limit_bytes
- innodb_compact_lower_bytes

値	意味
正の値	最大限外部化した場合は上限内
`0`	概算上、上限と同じ
負の値	最大限外部化しても上限を超える見込み

DYNAMICへの依存度

`dynamic_saved_bounded_bytes`

DDLから最大サイズを有限に計算できるカラムについて、DYNAMICでオフページ化した場合に削減できる可能性がある行内バイト数。

dynamic_saved_bounded_bytes
= bounded_inline_column_bytes
- bounded_dynamic_column_bytes

TEXT、BLOB、JSONの本文、NULLビットマップ、InnoDBの管理領域は含めない。

値が大きいほど、DYNAMICのオフページ化による削減効果が大きい。

`dynamic_offpage_dependency_pct`

有限の最大サイズを計算できるカラムについて、DYNAMICによる削減可能量が占める割合。

dynamic_offpage_dependency_pct
= dynamic_saved_bounded_bytes
  / bounded_inline_column_bytes
  × 100

値が高いほど、宣言最大長ベースではDYNAMICのオフページ化に強く依存する設計と考えられる。

TEXT、BLOB、JSONの本文は計算対象外なので、テーブル全体の物理的なオフページ比率ではない。

未対応型と近似型

キー	意味
`unsupported_column_count`	SQLが対応していない物理保存カラム数
`unsupported_columns`	未対応カラムを`カラム名:型`の形式で表示
`approximation_column_count`	近似ロジックを使用したカラム数
`approximation_columns`	近似カラムを`カラム名:型`の形式で表示

未対応型がある場合、design_verdictはMANUAL_REVIEWになる。

現在のSQLでは、JSONを近似型として扱う。

総合判定

`design_verdict`

SQLが返す総合的な設計リスク判定。

値	意味
`PASS_CANDIDATE`	概算上は大きな問題を検出していない
`WARNING_OFFPAGE_DEPENDENT`	行の成立がオフページ化に依存する
`WARNING_NEAR_LOCAL_LIMIT`	外部化後の下限がローカル上限の85%を超えている
`WARNING_NEAR_MYSQL_LIMIT`	65,535バイト制限までの余裕が5%未満
`FAIL_MYSQL_65535`	MySQLの65,535バイト制限を超える見込み
`FAIL_INNODB_LOCAL`	最大限外部化してもInnoDBのローカル上限を超える見込み
`MANUAL_REVIEW_NO_PRIMARY_KEY`	明示的な主キーがなく、クラスタ化キーの確認が必要
`MANUAL_REVIEW_ROW_FORMAT`	DYNAMICまたはCOMPACT以外の行形式
`MANUAL_REVIEW`	未対応型が含まれる
`NOT_INNODB`	InnoDB以外のテーブル

CASE式は上から順番に評価され、最初に一致した判定だけが返る。

例えば、主キーがなく、かつDYNAMICの下限値が上限を超えている場合でも、先に一致するMANUAL_REVIEW_NO_PRIMARY_KEYが返る。

そのため、design_verdictだけでなく、各サイズ値とマージン値も確認する。

`design_comment`

design_verdictや計算結果に対応する補足メッセージ。

例：

MySQLの65,535バイト制限を超える見込み

成立がオフページ化に依存する

概算上は余裕あり。ただし近似型あり: payload:json

出力結果の確認順

次の順番で確認すると判断しやすい。

design_verdict
design_comment
unsupported_column_countとunsupported_columns
approximation_column_countとapproximation_columns
mysql_65535_margin_bytes
現在の行形式に対応するマージン
- DYNAMIC：dynamic_margin_bytes
- COMPACT：compact_margin_bytes
innodb_bounded_inline_bytes
dynamic_offpage_dependency_pct
has_primary_key

PASS_CANDIDATEは、DDLの成功を保証する判定ではない。

最終的な合否は、対象環境と同じ条件で実際のDDLを実行して確認する。

型別の計算例

MySQLの型ごとの物理サイズは、文字セットや宣言長、実際の値の長さなどで変化する。VARCHARは値の長さに加えて1～2バイト、ENUMは1～2バイト、SETは1、2、3、4、8バイトを使用する。(dev.mysql.com)

型	MySQL 65,535用	行内最大シナリオ	DYNAMIC下限	COMPACT下限
`INT`	4	4	4	4
`DECIMAL(10,2)`	5	5	5	5
`VARCHAR(700)` `utf8mb4`	2,802	2,802	22	790
`VARCHAR(10)` `utf8mb4`	41	41	41	41
`TEXT`	10	本文は算出対象外	22	790
`TINYTEXT`	9	本文は算出対象外	22	256
`JSON`	12の近似	本文は算出対象外	22の近似	790の近似

`VARCHAR(700) utf8mb4`

utf8mb4は1文字あたり最大4バイトなので、最大ペイロードは2,800バイトになる。

700文字 × 4バイト = 2,800バイト

最大長が255バイトを超えるため、長さ情報は2バイト。

2,800 + 2 = 2,802バイト

DYNAMICで外部化された場合は22バイト、COMPACTで外部化された場合は790バイトとして計算される。

`DECIMAL(10,2)`

DECIMALは9桁ごとに4バイトで格納され、余った桁数に応じて1～4バイトが追加される。(dev.mysql.com)

DECIMAL(10,2)の場合は、整数部8桁で4バイト、小数部2桁で1バイトとなる。

4 + 1 = 5バイト

このSQLの制限

DDLの合否を完全には保証できない

このSQLは、INFORMATION_SCHEMAから取得できる型定義を使った静的な見積もりである。

実際のInnoDBは、行全体のサイズ、各値の実長、ページサイズ、行形式などを使って、外部化する列を動的に選択する。

したがって、最終的な合否は、対象環境と同じ条件で実際のDDLを実行して確認する必要がある。

CREATE DATABASE row_size_validation;

USE row_size_validation;

SET SESSION innodb_strict_mode = ON;

CREATE TABLE validation_target (
  -- 実際に使用するカラム定義
)
ENGINE = InnoDB
ROW_FORMAT = DYNAMIC;

SHOW WARNINGS;
SHOW ERRORS;

DROP DATABASE row_size_validation;

インデックスサイズは判定していない

本SQLが対象としているのは、主にクラスタインデックスレコードのローカル行サイズである。

次の制限は別途確認する必要がある。

インデックスキーの最大長
複合インデックスのカラム数
セカンダリインデックスに含まれる主キーサイズ
プレフィックスインデックス
関数インデックス
仮想生成列に対するインデックス

DYNAMICではインデックスキープレフィックスの上限は通常3,072バイト、COMPACTでは767バイトであり、行サイズ制限とは別に評価する必要がある。(dev.mysql.com)

JSONは近似値

MySQLのJSONは内部的にバイナリ形式で保存され、メタデータなどのオーバーヘッドが存在する。

本SQLではLONGBLOBに近い型として近似しているため、approximation_columnsに表示する。(dev.mysql.com)

ENUMとSETは安全側の値

ENUMは要素数によって1または2バイト、SETは要素数によって1、2、3、4、8バイトになる。

SQLではCOLUMN_TYPEの文字列を解析せず、常に次の安全側の値を使用している。

ENUM = 2バイト
SET  = 8バイト

そのため、実際より大きく見積もる場合がある。

まとめ

MySQL / InnoDBの行サイズには、少なくとも次の2つの制限がある。

MySQLの65,535バイト制限
InnoDBのページ内ローカルサイズ制限

また、InnoDBの行内サイズは、ROW_FORMATとオフページ格納の影響を強く受ける。

そのため、単一の推定値だけで判断するのではなく、次の複数値を確認するのがよい。

mysql_estimated_max_row_bytes
innodb_bounded_inline_bytes
innodb_dynamic_lower_bytes
innodb_compact_lower_bytes
dynamic_margin_bytes
compact_margin_bytes
dynamic_offpage_dependency_pct
design_verdict

このSQLは、問題のありそうなテーブルを事前に洗い出すためのスクリーニングとして使用する。

最終的なDDLの合否は、本番と同じMySQLバージョン、ページサイズ、文字セット、照合順序、ROW_FORMAT、innodb_strict_modeを使った検証環境で、実際のDDLを実行して確認する。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up