MySQLのさまざまなオブジェクトのサイズと長さの制限について話しましょう

今日は、MySQL 8.0 のさまざまなオブジェクトのサイズ、長さ、数量の制限について紹介します。

識別子の長さ制限

次の表は、さまざまな種類の識別子の最大長を示しています。

CREATE VIEW ステートメントのフィールドのエイリアスには、最大 256 文字ではなく 64 文字まで使用できます。

制約を定義するときに制約名を指定しない場合、MySQL は関連付けられたテーブルの名前に基づいて内部名を自動的に生成します。たとえば、自動的に生成される外部キーおよび CHECK 制約の名前は、テーブル名と _ibfk_ または _chk_ および数字で構成されます。テーブル名の長さが制約名の制限 (64 文字) に近い場合、他の文字を追加すると長くなりすぎてエラーが発生する可能性があります。

識別子は、テーブル定義内の識別子や、mysql データベースの権限テーブルに格納される識別子を含め、Unicode (UTF-8) を使用して格納されます。権限テーブル内の識別子フィールドのサイズは文字数です。識別子としてマルチバイト文字 (中国語など) を使用することができ、許容される長さは影響を受けません。

NDB 8.0.18 より前のリリースでは、NDB Cluster ではデータベース名とテーブル名の長さを最大 63 文字まで許可していました。この制限は NDB 8.0.18 以降では削除されました。

MySQL アカウント名のユーザー名とホスト名は識別子ではなく文字列です。権限テーブル内のこれらのフィールドの最大長については、次のセクションを参照してください。

権限テーブルの範囲フィールドの長さ制限

権限テーブルのスコープ フィールドは文字列を格納するために使用され、デフォルト値は空の文字列です。次の表は、これらのフィールドに保存できる最大文字列長を示しています。

Host および Proxied_host 文字列は、保存前に小文字に変換されます。

アクセス権限を確認する目的で、User、Proxied_user、authentication_string、Db、および Table_name 文字列の比較では大文字と小文字が区別されます。 Host、Proxied_host、Column_name、および Routine_name 文字列の比較では、大文字と小文字は区別されません。

データベースとテーブルの数の制限

MySQL ではデータベースの数に制限はありません。ただし、基盤となるファイル システムによって、1 つのデータベースに対応できるディレクトリの数が制限される場合があります。

MySQL ではテーブルの数に制限はありません。ただし、基盤となるファイル システムによって、テーブルに保存できるファイルの数が制限される場合があります。ストレージ エンジンによって特別な制限がある場合があります。InnoDB では 40 億個のテーブルを作成できます。

テーブルサイズの制限

MySQL データベース内のテーブルのサイズは、実際には MySQL の内部制限ではなく、オペレーティング システムのファイル サイズ制限によって決まります。

Windows ユーザーの場合、FAT および VFAT (FAT32) ファイル システムは実稼働環境での使用には適していません。MySQL データベースを保存するには、NTFS ファイル システムを使用することをお勧めします。

テーブルがいっぱいのエラーが発生した場合、考えられる理由は次のとおりです。

• ディスクがいっぱいです。
• ストレージ エンジンは InnoDB であり、テーブルスペース ファイルがいっぱいです。表領域の最大サイズは、表の最大サイズでもあります。詳細については、InnoDB ストレージ エンジンのサイズ制限を参照してください。一般的に、テーブルのサイズが 1 TB を超える場合にのみ、テーブルスペース ファイル間でテーブルをパーティション分割することを検討することをお勧めします。
• オペレーティング システムのファイル サイズの制限に達しました。たとえば、オペレーティング システムは最大 2 GB のファイル サイズをサポートしていますが、MyISAM ストレージ エンジンを使用すると、データ ファイルまたはインデックス ファイルがこの制限に達します。

MyISAM テーブルを使用しており、テーブルに必要なスペースが内部ポインター サイズで許可されているサイズを超えています。MyISAM では、データ ファイルとインデックス ファイルの既定サイズは最大 256 TB まで許可されていますが、この制限は最大許容サイズである 65,536 TB (2567 − 1 バイト) まで変更できます。

デフォルトの制限より大きい MyISAM テーブルが必要で、オペレーティング システムが大きなファイルをサポートしている場合、CREATE TABLE ステートメントは AVG_ROW_LENGTH および MAX_ROWS オプションをサポートします。CREATE TABLE ステートメントを参照してください。サーバーはこれらのオプションを使用して、許可するテーブルのサイズを決定します。

ポインター サイズが既存のテーブルに対して小さすぎる場合は、ALTER TABLE でオプションを変更して、テーブルの最大許容サイズを増やすことができます。ALTER TABLE ステートメントを参照してください。

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

AVG_ROW_LENGTH は、BLOB または TEXT 列を持つテーブルに対してのみ指定する必要があります。この場合、MySQL は行数のみに基づいて必要なスペースを最適化することはできません。

MyISAM テーブルのデフォルトのサイズ制限を変更するには、内部行ポインターに使用されるバイト数を設定する myisam_data_pointer_size を設定します。MAX_ROWS オプションを指定しない場合は、この値を使用して新しいテーブルのポインター サイズを設定します。myisam_data_pointer_size の値は 2 から 7 までです。たとえば、動的ストレージ形式を使用するテーブルの場合、値 4 では最大 4GB のテーブルが許可され、値 6 では最大 256TB のテーブルが許可されます。固定ストレージ形式を使用するテーブルでは、最大データ長が大きくなります。ストレージ形式の特性については、「MyISAM テーブル ストレージ形式」を参照してください。

次のステートメントを使用して、最大データ サイズとインデックス サイズを確認できます。

db_name から 'tbl_name' のようなテーブルステータスを表示します。

myisamchk -dv /path/to/table-index-file を使用することもできます。SHOW ステートメント、または myisamchk — MyISAM テーブルメンテナンスユーティリティを参照してください。

MyISAM テーブルのファイル サイズ制限を回避する他の方法は次のとおりです。

大きなテーブルが読み取り専用である場合は、myisampack を使用して圧縮できます。myisampack は通常、テーブルを少なくとも 50% 圧縮するため、実質的にテーブルを大きくすることができます。myisampack は複数のテーブルを 1 つのテーブルにマージすることもできます。「myisampack — 圧縮された読み取り専用の MyISAM テーブルの生成」を参照してください。

MySQL には、単一の MERGE テーブルと同じ構造を持つ MyISAM テーブルのコレクションを処理できる MERGE ライブラリが含まれています。MERGE ストレージ エンジンを参照してください。

MEMORY (HEAP) ストレージ エンジンを使用している場合は、max_heap_table_size システム変数の値を増やす必要があります。「サーバー システム変数」を参照してください。

フィールド数と行サイズの制限

フィールド数量制限

MySQL の各テーブルには最大 4096 個のフィールドが含まれますが、実際のフィールド数の制限はこの値よりも小さくなります。この制限はいくつかの要因によって異なります。

1. データ行の最大サイズによってフィールドの数 (および可能なサイズ) が制限されます。これは、すべてのフィールドの合計長さがこのサイズを超えることができないためです。次のセクションを参照してください。
2. 各フィールドのストレージ要件によって、フィールドの数が制限されます。特定のデータ型のストレージ要件は、ストレージ エンジン、ストレージ形式、文字セットなどの要因によって異なります。詳細については、公式ドキュメントを参照してください。
3. ストレージ エンジンは、テーブル内の列の数に追加の制限を課す場合があります。たとえば、InnoDB テーブルでは最大 1017 列が許可されます。その他のストレージ エンジン関連の制限については、公式ドキュメントを参照してください。
4. 関数インデックスの実装では、非表示の仮想計算ストレージ列関数が利用されるため、各関数インデックスもフィールド番号を占有します。

データ行のサイズ制限

データ行のサイズ制限は、次の要因によって決まります。

• ストレージ エンジンがより大きなストレージをサポートできる場合でも、MySQL テーブル内の各データ行の内部ストレージ制限は 65535 バイトです。 BLOB 型と TEXT 型は、実際の内容が別々に保存されるため、9 ～ 12 バイトしか占有しません。
• InnoDB テーブル データ行 (データベース ページにローカルに保存されるデータ) の最大サイズは、innodb_page_size の半分よりわずかに小さくなります (4 KB、8 KB、16 KB、32 KB)。たとえば、デフォルトの 16 KB ページ サイズ構成の場合、データ行の最大サイズは 8 KB よりわずかに小さくなります。 64KB のデータ ページの場合、最大データ行サイズは 16KB よりわずかに小さくなります。
  行内の可変長フィールドが InnoDB データ行のサイズ制限を超える場合、InnoDB はオフページ ストレージを使用して、データ行が InnoDB データ行のサイズ制限を満たすまで、一部の可変長フィールドを保存します。オフページに保存される可変長フィールドの場合、ローカルに保存されるデータの内容はデータ行の形式によって異なります。詳細については、「InnoDB データ行形式」を参照してください。
• 異なるストレージ エンジンでは異なるページ ヘッダーとフッター データが使用されるため、データ行に使用できる実際のストレージ領域に影響します。

データ行のサイズ制限の例

次の InnoDB および MyISAM の例は、MySQL の最大行サイズ制限である 65535 バイトを示しています。この制限はストレージ エンジンとは無関係であり、ストレージ エンジンがより大きなデータ行をサポートできる場合でも従う必要があります。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000)、d VARCHAR(10000)、e VARCHAR(10000)、
       f VARCHAR(10000)、g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
エラー1118 (42000): 行サイズが大きすぎます。使用されている行の最大サイズは
テーブルタイプは、BLOBを除いて65535です。これにはストレージオーバーヘッドが含まれます。
マニュアルを確認してください。いくつかの列をTEXTまたはBLOBに変更する必要があります。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000)、d VARCHAR(10000)、e VARCHAR(10000)、
       f VARCHAR(10000)、g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
エラー1118 (42000): 行サイズが大きすぎます。使用されている行の最大サイズは
テーブルタイプは、BLOBを除いて65535です。これにはストレージオーバーヘッドが含まれます。
マニュアルを確認してください。いくつかの列をTEXTまたはBLOBに変更する必要があります。

次の MyISAM の例では、フィールドの 1 つを TEXT に変更します。これにより、BLOB フィールドと TEXT フィールドはデータ行のサイズで 9 ～ 12 バイトしか占めないため、65535 バイトの制限を超えないようにします。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000)、d VARCHAR(10000)、e VARCHAR(10000)、
       f VARCHAR(10000)、g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
クエリは正常、影響を受けた行は 0 行 (0.02 秒)

次の InnoDB テーブルは、フィールドが TEXT に変更されて 65535 バイトの制限を超えないようにし、InnoDB オフページ ストレージによって InnoDB データ行のサイズ制限を超えないようにできるため、正常に作成できます。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000)、d VARCHAR(10000)、e VARCHAR(10000)、
       f VARCHAR(10000)、g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
クエリは正常、影響を受けた行は 0 行 (0.02 秒)

可変長フィールドのストレージには長さ情報が含まれており、これもデータ行のサイズに計算されます。たとえば、VARCHAR(255) CHARACTER SET utf8mb3 フィールドでは、データの長さを格納するために 2 バイトが必要なので、各値は最大 767 バイトを占める可能性があります。

次のステートメントは、フィールドに 32765 + 2 バイトと 32766 + 2 バイトが必要であり、65535 バイトの制限を満たすため、テーブル t1 を正常に作成します。

mysql> テーブル t1 を作成します
       (c1 VARCHAR(32765) NULLではありません、c2 ​​VARCHAR(32766) NULLではありません)
       エンジン = InnoDB 文字セット latin1;
クエリは正常、影響を受けた行は 0 行 (0.02 秒)

次のステートメントは、フィールドの長さが 65535 バイトの制限を超えていませんが、レコード長に 2 バイトを追加した後は制限を超えるため、テーブル t2 の作成に失敗します。

mysql> テーブル t2 を作成します
       (c1 VARCHAR(65535) NULLではありません)
       エンジン = InnoDB 文字セット latin1;
エラー1118 (42000): 行サイズが大きすぎます。使用されている行の最大サイズは
テーブルタイプは、BLOBを除いて65535です。これにはストレージオーバーヘッドが含まれます。
マニュアルを確認してください。いくつかの列をTEXTまたはBLOBに変更する必要があります。
列の長さを 65,533 以下に減らすと、ステートメントは成功します。

mysql> テーブル t2 を作成します
       (c1 VARCHAR(65533) NULLではありません)
       エンジン = InnoDB 文字セット latin1;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

MyISAM テーブルの場合、null 可能フィールドには、データが null であるかどうかを記録するための追加のスペースが必要です。各 null 許容フィールドには 1 ビットの追加が必要であり、これはバイトに切り上げられます。

次のステートメントは、MyISAM が null 許容フィールドを格納するために追加のスペースを必要とし、データ行のサイズが 65535 バイトを超えるため、テーブル t3 の作成に失敗します。

mysql> テーブル t3 を作成します
       (c1 VARCHAR(32765) NULL、c2 VARCHAR(32766) NULL)
       エンジン = MyISAM 文字セット latin1;
エラー1118 (42000): 行サイズが大きすぎます。使用されている行の最大サイズは
テーブルタイプは、BLOBを除いて65535です。これにはストレージオーバーヘッドが含まれます。
マニュアルを確認してください。いくつかの列をTEXTまたはBLOBに変更する必要があります。
InnoDB NULL 列のストレージの詳細については、「InnoDB 行形式」を参照してください。

InnoDB は、行サイズ (データベース ページ内に格納されるローカル データ) をデータベース ページのサイズの半分未満に制限します。次のステートメントは、すべてのフィールドの長さが InnoDB ページのデータ行サイズの制限である 16 KB を超えているため失敗します。

mysql> テーブル t4 を作成します (
       c1 CHAR(255)、c2 CHAR(255)、c3 CHAR(255)、
       c4 CHAR(255)、c5 CHAR(255)、c6 CHAR(255)、
       c7 文字(255)、c8 文字(255)、c9 文字(255)、
       c10 文字(255)、c11 文字(255)、c12 文字(255)、
       c13 文字(255)、c14 文字(255)、c15 文字(255)、
       c16 文字(255)、c17 文字(255)、c18 文字(255)、
       c19 文字(255)、c20 文字(255)、c21 文字(255)、
       c22 文字(255)、c23 文字(255)、c24 文字(255)、
       c25 文字(255)、c26 文字(255)、c27 文字(255)、
       c28 文字(255)、c29 文字(255)、c30 文字(255)、
       c31 文字(255)、c32 文字(255)、c33 文字(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
エラー 1118 (42000): 行サイズが大きすぎます (> 8126)。一部の列を TEXT または BLOB に変更すると解決する場合があります。
現在の行形式では、0 バイトの BLOB プレフィックスがインラインで格納されます。

要約する

これで、MySQL のさまざまなオブジェクトのサイズと長さの制限に関するこの記事は終了です。MySQL オブジェクトのサイズと長さの制限の詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。