MySQLでのVARCHARサイズ変更を行うALTER TABLEについて

これは何

MySQLにはVARCHARの文字数を拡張するDDLをオンラインで行うための最適化が存在する。

一方でこの最適化は内部のバイナリ表現に依存した限定的な最適化であり、意図せずオフラインDDLとして実行される可能性がある。

本ページではこの最適化の仕組みと安全にサイズ変更を行うための対策について解説する。

結論

結論1

MySQL 5.6以前では文字数が何文字だろうとVARCHARサイズ変更に関する最適化は行われないのでオフラインDDLになる。

そのため変更したい場合は停止メンテ等を行うかpt-online-schema-change等の利用を検討すること。

結論2

MySQL 5.7以降ではVARCHARサイズとCHARSETに依存してオンラインで拡張可能なサイズが変わるので、将来の拡張を考えた上でサイズを検討しよう。

結論3

以下のように ALGORITHM=INPLACE, LOCK=NONE をDDL末尾につけることで、DDLをオンラインで実行できるか検証できる。(algorithm, lockについては MySQLでカラム追加などのalter table中にクエリがブロックされるかなどについてのメモ - だいたいよくわからないブログ を参照)

エラーがでたらオフラインDDLになってしまうため停止メンテ等を行うかpt-online-schema-changeの利用を検討すること。

ALTER TABLE table_name CHANGE COLUMN colum_name colum_name VARCHAR(63), ALGORITHM=INPLACE, LOCK=NONE;

背景

MySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations によるとMySQL 5.7以降では

Extending VARCHAR column size は In Place=yes, Rebuilds Table=no, Permits Concurrent DML=yes, Only Modifies Metadata=yes となっている。

つまりread/writeはブロックされず、DDL自体も高速に終了することが期待できる。

しかし The number of length bytes required by a VARCHAR column must remain the same. といった記載もあり、対象範囲が自明ではない。以下ではこれらの挙動について解説する

前提: VARCHARについて

VARCHAR は 1 バイトまたは 2 バイトのlength prefixが付いたデータとして格納される。length prefixは値に含まれるバイト数を示す。 ( つまり${byte数prefix}${実際の文字列} という表現になる)

MySQL :: MySQL 5.7 Reference Manual :: 11.3.2 The CHAR and VARCHAR Types

このとき文字列のバイト数に依存して、length prefixのbyte数も以下のように変化する。

  • 1~255byteの場合は1byte (00~FF)
  • 256byte以上の場合は2byteで表現される。(0100~FFFF)
  • ※ MySQLの１行のサイズ上限があるため65535byteより大きなVARCHARはそもそも定義することができないのでlength prefixが3byteになることはない

また DDLで指定する VARCHAR(255)などの括弧内の数字はbyte数ではなく文字数なので、VARCHAR(255)のlength prefixが1byteであるかどうかは文字コードに依存する。(latin1なら1文字1byteなのでVARCHART(255)のlength prefixは1byte、utf8mb4なら1文字4byteなのでVARCHART(255)のlength prefixは2byteになる)

確認内容

MySQL 5.6

MySQL 5.6のドキュメントには Extending VARCHAR column size についての記載はない。

see: MySQL :: MySQL 8.0 Reference Manual :: 17.12.1 Online DDL Operations

実際に下記のDDLで検証したところサイズの拡大・縮小ともにALGORITHM=INPLACEではエラーとなり、ALGORITHM=COPYが要求された。

ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

ここから考えると、MySQL 5.6ではVARCHARに関する最適化はなく表中の Changing the column data type 相当のオフラインDDLになっていると想定される。

-- size=2にセット
ALTER TABLE foo MODIFY COLUMN foo_id VARCHAR(2);

-- size=2 to 3 => エラーになる
ALTER TABLE foo CHANGE COLUMN foo_id foo_id VARCHAR(3), ALGORITHM=INPLACE, LOCK=NONE;

-- size=2 to 1 => エラーになる
ALTER TABLE foo CHANGE COLUMN foo_id foo_id VARCHAR(1), ALGORITHM=INPLACE, LOCK=NONE;

MySQL 5.7

MySQL 5.7ではMySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operationsにあるとおり、VARCHARのサイズ拡張はオンラインDDLとして実行可能であるという記載がある。

しかし、この最適化には以下のような注意点がある。

  • カラムサイズの縮小に関しては対応されていない。（縮小の場合はオフラインDDLになる）
  • length prefixのbyte数は同じである必要がある（異なる場合はオフラインDDLになる）
カラムサイズの縮小について

サイズの縮小時に最適化されない旨は上記の公式ドキュメント内に記載がある

Decreasing VARCHAR size using in-place ALTER TABLE is not supported. Decreasing VARCHAR size requires a table copy (ALGORITHM=COPY).

実際に MySQL 5.7で同様の構文でサイズを縮小しようとしたところMySQL 5.6と同様のエラーが発生することが確認できた。

length prefixのサイズ制限について

前提の項で挙げたとおりVARCHARのbyte数が1~255の場合と 256以上の場合ではlength prefixのバイト数が異なる。

そのため VARCHAR(1) => VARCHAR(2)の場合はオンラインDDLになるが、VARCHAR(3) => VARCHAR(1000)の場合はオフラインDDLになってしまうといった問題が発生しうる。

さらにこれはバイト数での話であり、「文字数を意味するVARCHAR(N)のN」について考えたい場合は文字コードについても考える必要がある。

具体的には以下のようにオンラインDDLで実行されるかのしきい値が変わる。

  • CHARSET = latin1 のときは1文字1byteなので
    • VARCHAR(1)~VARCHAR(255)はlength prefixのサイズが1
    • VARCHAR(256)~はlength prefixのサイズが2
  • CHARSET = utf8mb4 のときは1文字4byteなので
    • VARCHAR(1)~VARCHAR(63)はlength prefixのサイズが1
    • VARCHAR(64)~はlength prefixのサイズが2

安全なマイグレーションの手引き

まず不用意にオフラインDDLになることをさけるため、 LOCK=none を付与することをおすすめする。

read/writeがブロックされるalter tableになっていたら実行前にエラーになってくれる。

またMySQL 8.xのドキュメントを見てもVARCHARのサイズ拡張に対応したアルゴリズムはINPLACEだけなので、 ALGORITHM=INPLACE もつけてよいだろう。(将来的になにか素敵なアルゴリズムが出た場合は見直す必要が出るかもしれないが、とはいえ現状でもブロックなしでtableコピーもないので気にするほどではない・・はず）

MySQL :: MySQL 8.0 Reference Manual :: 17.12.1 Online DDL Operations

エラーになった場合は諦めるか停止メンテ、あるいはpt-online-schema-change等の利用を検討するのが良いだろう。