MySQL パーティションテーブルの制限と制約の詳細な説明

ビルドを無効にする

パーティション式では、次の構成はサポートされません。

ストアドプロシージャ、ストアドファンクション、UDFS、またはプラグイン

変数またはユーザー変数を宣言する

パーティションでサポートされていない SQL 関数を参照してください。

算術演算子と論理演算子

パーティション式は +、-、* の算術演算をサポートしますが、DIV および / 演算はサポートしません (この問題はまだ存在しています。バグ #30188、バグ #33182 を参照してください)。ただし、結果は整数または NULL である必要があります (線形パーティション キーを除く。詳細については、「パーティションの種類」を参照してください)。

パーティション式はビット演算をサポートしていません: |、&、^、<<、>>、~。

HANDLER ステートメント

MySQL 5.7.1 より前のパーティション テーブルでは、HANDLER ステートメントはサポートされません。この制限は、それ以降のバージョンでは削除されました。

サーバーSQLモード

ユーザー定義のパーティションテーブルを使用する場合、パーティションテーブル作成時の SQL モードは保持されないことに注意してください。 「サーバー SQL モード」の章で説明したように、ほとんどの MySQL 関数と演算子の結果は、サーバー SQL モードに応じて変わる可能性があります。したがって、パーティション テーブルを作成した後に SQL モードを変更すると、これらのテーブルの動作に大きな変化が生じ、データの損失や破損につながる可能性があります。上記の理由から、パーティション テーブルを作成した後は、サーバーの SQL モードを変更しないことを強くお勧めします。

上記の状況を説明するために例を見てみましょう。

1. エラー処理

mysql> テーブル tn (c1 INT) を作成します。
  -> PARTITION BY LIST(1 DIV c1) (
  -> パーティション p0 の値は (NULL) です。
  -> パーティション p1 値 (1)
  -> );
  クエリは正常、影響を受けた行は 0 行 (0.05 秒)

デフォルトでは、MySQL で 0 で割った結果はエラーではなく NULL になります。

mysql> @@sql_mode を選択します。
+------------+
| @@sql_mode |
+------------+
| |
+------------+
セット内の 1 行 (0.00 秒)
 
mysql> tn 値に (NULL), (0), (1);
クエリは正常、3 行が影響を受けました (0.00 秒)
記録: 3 重複: 0 警告: 0

ただし、SQL モードを変更すると、エラーが報告されます。

mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
 
mysql> tn 値に (NULL), (0), (1);
エラー 1365 (22012): 0 による除算

2. 表の補助機能

SQL モードを変更すると、パーティション テーブルが使用できなくなる場合があります。たとえば、次のような一部のテーブルは、SQL モードが NO_UNSIGNED_SUBTRACTION の場合にのみ機能します。

mysql> @@sql_mode を選択します。
+------------+
| @@sql_mode |
+------------+
| |
+------------+
セット内の 1 行 (0.00 秒)
 
mysql> テーブル tu を作成します (c1 BIGINT UNSIGNED)
  -> 範囲によるパーティション分割(c1 - 10) (
  -> パーティション p0 の値が (-5) 未満
  -> パーティション p1 の値が (0) 未満である、
  -> パーティション p2 の値が (5) 未満である、
  -> パーティション p3 の値が (10) 未満である、
  -> パーティション p4 の値が (MAXVALUE) 未満です
  -> );
エラー 1563 (HY000): パーティション定数がパーティション関数のドメイン外です
   
mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
 
mysql> @@sql_mode を選択します。
+-------------------------+
| @@sql_mode |
+-------------------------+
| 符号なし減算なし |
+-------------------------+
セット内の 1 行 (0.00 秒)
 
mysql> テーブル tu を作成します (c1 BIGINT UNSIGNED)
  -> 範囲によるパーティション分割(c1 - 10) (
  -> パーティション p0 の値が (-5) 未満
  -> パーティション p1 の値が (0) 未満である、
  -> パーティション p2 の値が (5) 未満である、
  -> パーティション p3 の値が (10) 未満である、
  -> パーティション p4 の値が (MAXVALUE) 未満です
  -> );
   
クエリは正常、影響を受けた行は 0 行 (0.05 秒)

tu を作成した後に SQL モードを変更すると、テーブルにアクセスできなくなる可能性があります。

mysql> SET sql_mode=''; クエリは正常、0 行が影響を受けました (0.00 秒)
 
mysql> SELECT * FROM tu;
エラー 1563 (HY000): パーティション定数がパーティション関数のドメイン外です
 
mysql> tu VALUES (20) に INSERT INTO します。
エラー 1563 (HY000): パーティション定数がパーティション関数のドメイン外です

サーバー側の SQL モードも、パーティション テーブルのレプリケーションに影響します。マスター データベースとスレーブ データベース間で異なる SQL モードを使用すると、マスター データベースとスレーブ データベースでパーティション式を実行したときに結果が異なる場合があります (マスターとスレーブの切り替えは Alibaba では通常の操作ですが)。これにより、マスターとスレーブのレプリケーション プロセス中に、異なるパーティション間でデータ配分が異なる場合があります。また、パーティション テーブルの挿入がマスター データベースでは成功しても、スレーブ データベースでは失敗することもあります。上記の状況を踏まえると、最善の解決策は、プライマリ サーバーとバックアップ サーバー間の SQL モードが一貫していることを確認することです (これは、DBA が運用および保守中に注意する必要がある点です)。

パフォーマンスに関する考慮事項

パーティション操作のパフォーマンスに影響する要因は次のとおりです。

パーティション化または再パーティション化 (ALTER TABLE ... PARTITION BY ...、REORGANIZE PARTITION、REMOVE PARTITIONING など) が実行するファイル システム操作は、ファイル システムの実装によって異なります。つまり、上記の操作は、ファイル システムの種類と特性、ディスク速度、スワップ領域、オペレーティング システム上のファイル処理効率、MySQL サーバー上のファイル ハンドルに関連するオプションと変数など、オペレーティング システム上の要因によって影響を受けることになります。 large_files_support が有効になっており、open_files_limit 設定が適切であることを確認する必要があることに注意することが重要です。 MyISAM パーティション テーブルの場合、パフォーマンスを向上させるには myisam_max_sort_file_size を増やす必要があります。InnoDB テーブルの場合、innodb_file_per_table を有効にすると、パーティション分割または再パーティション分割の操作が高速になります。

パーティションの最大数も参照してください。

MyISAM とパーティション ファイル記述子

MyISAM パーティション テーブルの場合、MySQL は開いているテーブルごとにパーティションごとに 2 つのファイル記述子を使用します。つまり、パーティション化された MyISAM テーブルで操作 (特に ALTER TABLE 操作) を実行するには、パーティション化されていない同じテーブルよりも多くのファイル記述子が必要になります。

100 個のパーティションを持つ MyISAM テーブルを作成するとします。ステートメントは次のようになります。

テーブルtを作成します（c1 VARCHAR（50））
キーによるパーティション (c1) パーティション 100
エンジン=MYISAM;

つまり、この例では、KEY パーティション分割を使用していますが、パーティション タイプに関係なく、MyISAM テーブル エンジンを使用するすべてのパーティションでファイル記述子の問題が発生します。ただし、他のストレージ エンジン (InnoDB など) を使用するパーティション テーブルではこの問題は発生しません。

t を 101 個のパーティションに再分割する場合は、次のステートメントを使用します。

ALTER TABLE t PARTITION BY KEY (c1) PARTITIONS 101;

ALTER TABLE ステートメントを処理する場合、402 個のファイル記述子 (元の 100 個のパーティションに 2 個、新しい 101 個のパーティションに 2 個) が必要です。これは、テーブル データを再編成するときに、すべてのパーティション (古いパーティションと新しいパーティション) を開く必要があるためです。したがって、これらの操作を実行するときは、--open-files-limit がより大きく設定されていることを確認することをお勧めします。

テーブルロック

テーブルに対してパーティション操作を実行するプロセスは、テーブルの書き込みロックを占有しますが、読み取りには影響しません。たとえば、これらのパーティションに対する INSERT および UPDATE 操作は、パーティション操作が完了した後にのみ実行できます。

ストレージエンジン

クエリや更新操作などのパーティション操作は、通常、InnoDB や NDB よりも MyISAM エンジンの方が高速です。

インデックス; パーティションプルーニング

パーティション化されていないテーブルと同様に、パーティション化されたテーブルでもインデックスを適切に使用することでクエリ速度を大幅に向上できます。さらに、パーティション化されたテーブルとこれらのテーブルに対するクエリを設計すると、パーティション プルーニングを利用してパフォーマンスを大幅に向上させることができます。

MySQL 5.7.3 より前では、パーティション テーブルはインデックス条件プッシュダウンをサポートしていませんでしたが、それ以降のバージョンではサポートされています。

データの読み込みパフォーマンス

MySQL 5.7 では、データのロード時にバッファを使用してパフォーマンスが向上します。知っておく必要があるのは、この目的を達成するには、バッファーが各パーティションの 130 KB を占めるということです。

パーティションの最大数

パーティション テーブルがストレージ エンジンとして NDB を使用しない場合、サポートされるパーティション (サブパーティションを含む) の最大数は 8192 です。

NDB をストレージ エンジンとして使用する場合、ユーザー定義のパーティション分割の最大パーティション数は、MySQL Cluster のバージョン、データ ノード、およびその他の要因によって異なります。

非常に多数のパーティション (パーティションの最大数より少ない) を作成し、ファイルを開くときに「ストレージ エンジンからエラーが発生しました: リソース不足」などのエラーが発生する場合は、open_files_limit を増やす必要がある場合があります。ただし、open_files_limit は実際にはオペレーティング システムによって異なるため、すべてのプラットフォームで調整することが推奨されるわけではありません。巨大なパーティションや数百のパーティションを使用することが推奨されない状況もあるため、パーティションの数を増やしても必ずしも良い結果が得られるとは限りません。

クエリキャッシュをサポートしていません

パーティション テーブルはクエリ キャッシュをサポートしていないため、パーティション テーブルに対するクエリは自動的にクエリ キャッシュを回避します。つまり、パーティション テーブルのクエリ ステートメントではクエリ キャッシュは機能しません。

パーティションごとに1つのキーキャッシュ

MySQL 5.7 では、CACHE INDEX および LOAD INDEX INTO CACHE を通じて MyISAM パーティション テーブルのキー キャッシュを使用できます。キー キャッシュは 1 つ、複数、またはすべてのパーティションに対して定義できるため、1 つ、複数、またはすべてのパーティションのインデックスをキー キャッシュに事前にロードできます。

InnoDB パーティション テーブル上の外部キーはサポートされていません

InnoDB エンジンを使用するパーティション テーブルは外部キーをサポートしません。次の 2 つの具体的な状況について説明します。

InnoDB テーブルでは外部キーを使用したカスタム パーティション分割は使用できません。InnoDB テーブルですでに外部キーが使用されている場合は、パーティション分割できません。

InnoDB テーブルには、ユーザー定義のパーティション テーブルに関連付けられた外部キーを含めることはできません。また、ユーザー定義のパーティションを使用する InnoDB テーブルには、外部キーに関連付けられた列を含めることはできません。

上記の制限の範囲には、InnoDB ストレージ エンジンを使用するすべてのテーブルが含まれます。これらの制限に違反する CREATE TABLE および ALTER TABLE ステートメントは許可されません。

テーブルを変更...ORDER BY

パーティション化されたテーブルで ALTER TABLE ... ORDER BY を実行すると、各パーティションの行がソートされます。

主キーを変更する際の REPLACE ステートメントの効率

場合によっては、テーブルの主キーを変更することが必要になります。アプリケーションで REPLACE ステートメントを使用する場合、これらのステートメントの結果は大幅に変更される可能性があります。

全文索引

パーティション化されたテーブルは、パーティション化されたテーブルのストレージ エンジンが InnoDB または MyISAM であっても、フルテキスト インデックス作成や検索をサポートしません。

空間列

パーティション化されたテーブルは、ポイントやジオメトリなどの空間列をサポートしません。

一時テーブル

一時テーブルはパーティション化できません (バグ #17497)。

ログテーブル

ログ テーブルをパーティション化できません。ALTER TABLE ... PARTITION BY ... ステートメントを強制的に実行すると、エラーが報告されます。

パーティションキーのデータ型

パーティション キーは整数または整数に評価される式である必要があります。 ENUM 型になる式は使用できません。このタイプの式は NULL になる可能性があるためです。

例外が 2 つあります。

LINER パーティショニングを使用する場合、MySQL の内部ハッシュ関数がこれらの列から正しいデータ型を生成するため、TEXT または BLOBS 以外のデータ型をパーティショニング キーとして使用できます。たとえば、次の create ステートメントは有効です。

テーブル tkc (c1 CHAR) を作成します。
キーによるパーティション(c1)
パーティション4;

テーブルの作成
  ( c1 ENUM('赤', 'オレンジ', '黄', '緑', '青', '藍', '紫') )
線形キーによるパーティション(c1)
パーティション 6;

RANGE、LIST、DATE、または DATETIME 列でパーティション分割する場合は、文字列を使用できます。たとえば、次の create ステートメントは有効です。

テーブル rc を作成します (c1 INT、c2 DATE)
範囲列によるパーティション(c2) (
  パーティション p0 の値は ('1990-01-01') より小さい
  パーティション p1 の値は ('1995-01-01') より小さい
  パーティション p2 の値は ('2000-01-01') より小さい
  パーティション p3 の値は ('2005-01-01') より小さい
  パーティション p4 の値が (MAXVALUE) 未満です
);

テーブル lc を作成します (c1 INT、c2 CHAR(1))
リスト列によるパーティション(c2) (
  パーティション p0 値 IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
  パーティション p1 値 IN('b'、'e'、'h'、'k'、'n'、'q'、't'、'w'、'z')、
  パーティション p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL)
);

上記の例外はいずれも、BLOB または TEXT 列タイプには適用されません。

サブクエリ

サブクエリが整数値または NULL 値を回避する場合でも、パーティション キーをサブクエリすることはできません。

サブパーティション化の問題

サブパーティションでは、HASH または KEY パーティションを使用する必要があります。サブパーティションをサポートするのは RANGE パーティションと LIST パーティションのみです。HASH パーティションと KEY パーティションはサブパーティションをサポートしません。

SUBPARTITION BY KEY では、省略可能な PARTITION BY KEY (その場合、デフォルトでテーブルの主キーが使用されます) とは異なり、サブパーティション列を明示的に指定する必要があります。たとえば、次のようなテーブルを作成するとします。

テーブルtsを作成します（
  id INT NOT NULL AUTO_INCREMENT 主キー、
  名前 VARCHAR(30)
);

次のステートメントを使用して、同じ列を使用してパーティション テーブル (KEY でパーティション化) を作成することもできます。

テーブルtsを作成します（
  id INT NOT NULL AUTO_INCREMENT 主キー、
  名前 VARCHAR(30)
）
キーによるパーティション()
パーティション4;

前の文は実際には次の文と同じです。

テーブルtsを作成します（
  id INT NOT NULL AUTO_INCREMENT 主キー、
  名前 VARCHAR(30)
）
キーによるパーティション(id)
パーティション4;
 

ただし、デフォルト列をサブパーティション列として使用してサブパーティション化されたテーブルを作成しようとすると、次のステートメントは失敗し、ステートメントを成功させるには以下に示すように指定する必要があります (バグ #51470 として知られています)。

mysql> テーブル ts を作成します (
  -> id INT NOT NULL AUTO_INCREMENT 主キー、
  -> 名前 VARCHAR(30)
  -> )
  -> 範囲によるパーティション分割(id)
  -> キーによるサブパーティション()
  -> サブパーティション 4
  -> (
  -> パーティション p0 の値が (100) 未満
  -> パーティション p1 の値が (MAXVALUE) 未満です
  -> );
   
エラー1064（42000）：SQL構文にエラーがあります。マニュアルを確認してください。
')の近くで使用する正しい構文については、MySQLサーバーのバージョンに対応しています。
 
mysql> テーブル ts を作成します (
  -> id INT NOT NULL AUTO_INCREMENT 主キー、
  -> 名前 VARCHAR(30)
  -> )
  -> 範囲によるパーティション分割(id)
  -> キー(id)によるサブパーティション
  -> サブパーティション 4
  -> (
  -> パーティション p0 の値が (100) 未満
  -> パーティション p1 の値が (MAXVALUE) 未満です
  -> );
   
クエリは正常、影響を受けた行は 0 行 (0.07 秒)

データ辞書とインデックス辞書のオプション

パーティション化されたテーブルのデータ ディクショナリとインデックス ディクショナリには、次の制約が適用されます。

テーブルレベルのデータ辞書とインデックス辞書は無視されます (バグ #32091)

Windows システムでは、MyISAM パーティション テーブルは、個々のパーティションまたはサブパーティションのデータ ディクショナリおよびインデックス ディクショナリ オプションをサポートしません。ただし、InnoDB パーティション テーブルの独立したパーティションまたはサブパーティションのデータ ディクショナリはサポートされます。

パーティションテーブルの修復と再構築

パーティション テーブルは、CHECK TABLE、OPTIMIZE TABLE、ANALYZE TABLE、および REPAIR TABLE ステートメントをサポートします。

あるいは、ALTER TABLE ... REBUILD PARTITION を使用して、パーティション化されたテーブル上の 1 つ以上のパーティションを再構築することもできます。また、ALTER TABLE ... REORGANIZE PARTITION を使用してパーティションを再構築することもできます。

MySQL 5.7.2 以降、サブパーティションは ANALYZE、CHECK、OPTIMIZE、REPAIR、および TRUNCATE 操作をサポートします。 REBUILD 構文は MySQL 5.7.5 より前のバージョンで導入されましたが、機能しません (Bug #19075411、Bug #73130 を参照)。

mysqlcheck、myisamchk、および myisampack 操作は、パーティション化されたテーブルではサポートされていません。

エクスポートオプション

MySQL バージョン 5.7.4 より前では、InnoDB パーティション テーブルに対する FLUSH TABLES ステートメントのエクスポート オプションはサポートされていません (バグ #16943907)。

参考文献

https://dev.mysql.com/doc/refman/5.7/en/パーティション制限.html

https://www.slideshare.net/datacharmer/mysql-partitions-tutorial/34-Partition_pruning_unpartitioned_tableexplain_partitions

https://www.percona.com/blog/2010/12/11/mysql-partitioning-can-save-you-or-kill-you/