19.6 パーティショニングの制約と制限

[+/-]

19.6.1 パーティショニングキー、主キー、および一意キー

19.6.2 ストレージエンジンに関連するパーティショニング制限

19.6.3 関数に関連するパーティショニング制限

19.6.4 パーティショニングとロック

このセクションでは、MySQL パーティショニングサポートでの現在の制約と制限について説明します。

禁止されている構造体  次の構造体はパーティショニング式で許可されません。

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

• 宣言された変数またはユーザー変数。

パーティショニング式で許可される SQL 関数のリストについては、セクション19.6.3「関数に関連するパーティショニング制限」を参照してください。

算術および論理演算子  算術演算子 +、-、および * の使用は、パーティショニング式で許可されます。ただし、結果は整数値または NULL である必要があります (この章のほかの場所で説明しているように、[LINEAR] KEY パーティショニングの場合を除きます。詳細は、セクション19.2「パーティショニングタイプ」を参照してください)。

DIV 演算子もサポートされますが、/ 演算子は許可されません。(Bug #30188、Bug #33182)。

ビット演算子 |、&、^、<<、>>、および ~ はパーティショニング式では許可されません。

HANDLER ステートメント  MySQL 5.6 では、HANDLER ステートメントはパーティション化されたテーブルでサポートされません。

サーバー SQL モード  ユーザー定義パーティショニングを使用するテーブルは、それらが作成された時点で有効だった SQL モードを保持しません。セクション5.1.7「サーバー SQL モード」で説明したように、多くの MySQL 関数および演算子の結果はサーバー SQL モードに従って変更されることがあります。このため、パーティション化されたテーブルの作成後の任意の時点に SQL モードを変更すると、そのようなテーブルの動作が大きく変わることがあり、データの破損または損失が発生しやすくなることがあります。これらの理由により、パーティション化されたテーブルを作成したあとにサーバー SQL モードを決して変更しないことが強く推奨されています。

例  次の例は、サーバー SQL モードを変更したことによる、パーティション化されたテーブルの動作の変化をいくつか示しています。

1. エラー処理  次のように、パーティショニング式が column DIV 0、column MOD 0 などのいずれかであるパーティション化されたテーブルを作成するとします。

   mysql> CREATE TABLE tn (c1 INT)
       ->     PARTITION BY LIST(1 DIV c1) (
       ->       PARTITION p0 VALUES IN (NULL),
       ->       PARTITION p1 VALUES IN (1)
       -> );
   Query OK, 0 rows affected (0.05 sec)

   ゼロで除算した結果に対する MySQL のデフォルト動作は、エラーを発生させずに NULL を返すことです。

   mysql> SELECT @@sql_mode;
   +------------+
   | @@sql_mode |
   +------------+
   |            |
   +------------+
   1 row in set (0.00 sec)
   
   
   mysql> INSERT INTO tn VALUES (NULL), (0), (1);
   Query OK, 3 rows affected (0.00 sec)
   Records: 3  Duplicates: 0  Warnings: 0

   ただし、ゼロによる除算をエラーとして扱い、厳密なエラー処理を適用するようにサーバー SQL モードを変更すると、次のように同じ INSERT ステートメントが失敗します。

   mysql> SET sql_mode='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> INSERT INTO tn VALUES (NULL), (0), (1);
   ERROR 1365 (22012): Division by 0

2. テーブルアクセス可能性  サーバー SQL モードを変更することによって、パーティション化されたテーブルが使用できなくなることがあります。次の CREATE TABLE ステートメントは、NO_UNSIGNED_SUBTRACTION モードが有効である場合にのみ、正常に実行できます。

   mysql> SELECT @@sql_mode;
   +------------+
   | @@sql_mode |
   +------------+
   |            |
   +------------+
   1 row in set (0.00 sec)
   
   mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
       ->   PARTITION BY RANGE(c1 - 10) (
       ->     PARTITION p0 VALUES LESS THAN (-5),
       ->     PARTITION p1 VALUES LESS THAN (0),
       ->     PARTITION p2 VALUES LESS THAN (5),
       ->     PARTITION p3 VALUES LESS THAN (10),
       ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
       -> );
   ERROR 1563 (HY000): Partition constant is out of partition function domain
   
   mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> SELECT @@sql_mode;
   +-------------------------+
   | @@sql_mode              |
   +-------------------------+
   | NO_UNSIGNED_SUBTRACTION |
   +-------------------------+
   1 row in set (0.00 sec)
   
   mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED)
       ->   PARTITION BY RANGE(c1 - 10) (
       ->     PARTITION p0 VALUES LESS THAN (-5),
       ->     PARTITION p1 VALUES LESS THAN (0),
       ->     PARTITION p2 VALUES LESS THAN (5),
       ->     PARTITION p3 VALUES LESS THAN (10),
       ->     PARTITION p4 VALUES LESS THAN (MAXVALUE)
       -> );
   Query OK, 0 rows affected (0.05 sec)

   tu を作成したあとに NO_UNSIGNED_SUBTRACTION サーバー SQL モードを削除すると、このテーブルにアクセスできなくなる可能性があります。

   mysql> SET sql_mode='';
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> SELECT * FROM tu;
   ERROR 1563 (HY000): Partition constant is out of partition function domain
   mysql> INSERT INTO tu VALUES (20);
   ERROR 1563 (HY000): Partition constant is out of partition function domain

サーバー SQL モードは、パーティション化されたテーブルのレプリケーションにも影響します。マスターとスレーブで SQL モードが異なると、パーティショニング式が違って評価されることがあります。これにより、指定されたテーブルのマスターのコピーとスレーブのコピーでパーティション間のデータ配分が違ってくることがあり、マスターで成功するパーティション化されたテーブルへの挿入がスレーブで失敗することさえあります。最適な結果を得るために、マスターとスレーブとで常に同じサーバー SQL モードを使用してください。

パフォーマンス考慮事項  パーティショニング操作のパフォーマンスへの影響の一部を次のリストに示します。

• ファイルシステム操作  パーティショニングおよび再パーティショニング操作 (PARTITION BY ...、REORGANIZE PARTITIONS、または REMOVE PARTITIONING を指定した ALTER TABLE など) は、それらの実装のファイルシステム操作に依存します。これは、これらの操作の速度が、ファイルシステムのタイプと特性、ディスク速度、スワップ領域、オペレーティングシステムによるファイル処理効率、ファイル処理に関連する MySQL サーバーのオプションと変数などの要因に影響されることを意味します。特に、large_files_support が有効になっていて、open_files_limit が適切に設定されていることを確認してください。MyISAM ストレージエンジンを使用するパーティション化されたテーブルの場合、myisam_max_sort_file_size を増やすとパフォーマンスが向上することがあります。innodb_file_per_table を有効にすることで、InnoDB テーブルを使用するパーティショニングおよび再パーティショニング操作の効率が向上することがあります。

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

• テーブルロック  テーブルに対してパーティショニング操作を実行する処理は、テーブルに対して書き込みロックを設定します。そのようなテーブルからの読み取りは比較的影響を受けません。保留中の INSERT および UPDATE 操作は、パーティショニング操作が完了するとすぐに実行されます。

• ストレージエンジン  パーティショニング操作、クエリー、および更新操作は通常、InnoDB または NDB テーブルより MyISAM テーブルで高速である傾向があります。

• インデックス、パーティションプルーニング  パーティション化されていないテーブルと同様に、インデックスを適切に使用することで、パーティション化されたテーブルに対する照会速度が大幅に向上することがあります。また、パーティション化されたテーブルおよびこれらのテーブルに対するクエリーをパーティションプルーニングの利点を活用するように設計することで、パフォーマンスが劇的に向上することがあります。詳細は、セクション19.4「パーティションプルーニング」を参照してください。

  インデックスコンディションプッシュダウンは、パーティション化されたテーブルではサポートされません。セクション8.2.1.6「インデックスコンディションプッシュダウンの最適化」を参照してください。

• LOAD DATA のパフォーマンス  MySQL 5.6 では、LOAD DATA はパフォーマンスを向上させるためにバッファリングを使用します。これを実現するために、バッファーがパーティションごとに 130K バイトメモリーを使用することを認識してください。

パーティションの最大数  MySQL 5.6.7 より前は、NDB ストレージエンジンを使用しないテーブルで可能な最大パーティション数は 1024 でした。MySQL 5.6.7 以降は、この制限は 8192 パーティションに増えています。MySQL Server バージョンにかかわらず、この最大数にはサブパーティションが含まれます。

NDB ストレージエンジンを使用するテーブルのユーザー定義パーティションの最大数は、使用されている MySQL Cluster ソフトウェアのバージョン、データノードの数、およびその他の要因に応じて決まります。詳細は、NDB とユーザー定義のパーティション化を参照してください。

多数のパーティション (ただし、最大数より少ない) を持つテーブルを作成するときに、Got error ... from storage engine: Out of resources when opening fileなどのエラーメッセージが表示される場合は、open_files_limit システム変数の値を増やすことによってこの問題に対処できることがあります。ただし、これはオペレーティングシステムによって異なるため、すべてのプラットフォームで可能または推奨されるとはかぎりません。詳細は、セクションB.5.2.18「'File' が見つかりません、および同様のエラー」を参照してください。場合によっては、多数の (数百の) パーティションを使用することがほかの問題のために推奨されないこともあり、より多くのパーティションを使用することが自動的に良い結果となるとはかぎりません。

ファイルシステム操作も参照してください。

クエリーキャッシュがサポートされない  クエリーキャッシュはパーティション化されたテーブルではサポートされません。MySQL 5.6.5 以降は、クエリーキャッシュはパーティション化されたテーブルを使用するクエリーで自動的に無効になり、そのようなクエリーで有効にすることはできません。(Bug #53775)

パーティションごとのキーキャッシュ  MySQL 5.6 では、CACHE INDEX および LOAD INDEX INTO CACHE ステートメントを使用することで、キーキャッシュがパーティション化された MyISAM テーブルでサポートされます。キーキャッシュは 1 つ、複数、またはすべてのパーティションに定義でき、1 つ、複数、またはすべてのパーティションのインデックスをキーキャッシュにプリロードできます。

パーティション化された InnoDB テーブルで外部キーがサポートされない  InnoDB ストレージエンジンを使用するパーティション化されたテーブルでは、外部キーはサポートされません。これは具体的には、次の 2 つの記述が true であることを意味します。

1. ユーザー定義パーティショニングを使用する InnoDB テーブルの定義には、外部キー参照を含めることはできません。定義に外部キー参照が含まれる InnoDB テーブルはパーティション化できません。

2. InnoDB テーブル定義に、ユーザーパーティション化されたテーブルへの外部キー参照を含めることはできません。ユーザー定義パーティショニングを持つ InnoDB テーブルに、外部キーによって参照されるカラムを含めることはできません。

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

ALTER TABLE ... ORDER BY  パーティション化されたテーブルに ALTER TABLE ... ORDER BY column ステートメントを実行すると、各パーティション内でのみ行が並べ替えられます。

主キーを変更することによる REPLACE ステートメントへの影響  テーブルの主キーを変更することが望ましい場合があります (セクション19.6.1「パーティショニングキー、主キー、および一意キー」を参照してください)。REPLACE ステートメントを使用するアプリケーションでこれを行うと、これらのステートメントの結果が大きく変わることがあることを認識してください。詳細および例については、セクション13.2.8「REPLACE 構文」を参照してください。

FULLTEXT インデックス  パーティション化されたテーブルは、InnoDB または MyISAM ストレージエンジンを使用するパーティション化されたテーブルでも、FULLTEXT インデックスまたは検索をサポートしません。

空間カラム  POINT、GEOMETRY などの空間データ型を持つカラムは、パーティション化されたテーブルで使用できません。

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

ログテーブル  ログテーブルをパーティション化することはできません。そのようなテーブルに ALTER TABLE ... PARTITION BY ... ステートメントを実行すると、エラーで失敗します。

パーティショニングキーのデータ型  パーティショニングキーは、整数カラム、または整数に解決される式である必要があります。ENUM カラムを使用する式は使用できません。カラムまたは式値は NULL でもかまいません(セクション19.2.7「MySQL パーティショニングによる NULL の扱い」を参照してください)。

この制約には 2 つの例外があります。

1. [LINEAR] KEY によってパーティショニングするときは、TEXT または BLOB 以外の有効な MySQL データ型のカラムをパーティショニングキーとして使用できます。MySQL の内部キーハッシュ関数によって、これらの型から正しいデータ型が生成されるためです。たとえば、次の 2 つの CREATE TABLE ステートメントは有効です。

   CREATE TABLE tkc (c1 CHAR)
   PARTITION BY KEY(c1)
   PARTITIONS 4;
   
   CREATE TABLE tke 
       ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') ) 
   PARTITION BY LINEAR KEY(c1) 
   PARTITIONS 6;

2. RANGE COLUMNS または LIST COLUMNS によってパーティショニングするときは、文字列、DATE、および DATETIME カラムを使用できます。たとえば、次の各 CREATE TABLE ステートメントは有効です。

   CREATE TABLE rc (c1 INT, c2 DATE)
   PARTITION BY RANGE COLUMNS(c2) (
       PARTITION p0 VALUES LESS THAN('1990-01-01'),
       PARTITION p1 VALUES LESS THAN('1995-01-01'),
       PARTITION p2 VALUES LESS THAN('2000-01-01'),
       PARTITION p3 VALUES LESS THAN('2005-01-01'),
       PARTITION p4 VALUES LESS THAN(MAXVALUE)
   );
   
   CREATE TABLE lc (c1 INT, c2 CHAR(1))
   PARTITION BY LIST COLUMNS(c2) (
       PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'),
       PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'),
       PARTITION 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 の場合は省略できます (その場合、テーブルの主キーカラムがデフォルトで使用されます)、次のステートメントによって作成されたテーブルがあるとします。

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
);

次のようなステートメントを使用することで、KEY によってパーティション化された、同じカラムを持つテーブルを作成できます。

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
)
PARTITION BY KEY() 
PARTITIONS 4;

前のステートメントは、次のように記述されているかのように扱われます (テーブルの主キーカラムがパーティショニングカラムとして使用されます)。

CREATE TABLE ts (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30)
)
PARTITION BY KEY(id) 
PARTITIONS 4;

ただし、次のステートメントは、デフォルトカラムをサブパーティショニングカラムとして使用するサブパーティション化されたテーブルを作成しようとするため失敗します。このステートメントが成功するには次のようにカラムを指定する必要があります。

mysql> CREATE TABLE ts (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30)
    -> )
    -> PARTITION BY RANGE(id)
    -> SUBPARTITION BY KEY()
    -> SUBPARTITIONS 4
    -> (
    ->     PARTITION p0 VALUES LESS THAN (100),
    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use near ')

mysql> CREATE TABLE ts (
    ->     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(30)
    -> )
    -> PARTITION BY RANGE(id)
    -> SUBPARTITION BY KEY(id)
    -> SUBPARTITIONS 4
    -> (
    ->     PARTITION p0 VALUES LESS THAN (100),
    ->     PARTITION p1 VALUES LESS THAN (MAXVALUE)
    -> );
Query OK, 0 rows affected (0.07 sec)

これは既知の問題です (Bug #51470 を参照してください)。

DELAYED オプションがサポートされない  INSERT DELAYED を使用してパーティション化されたテーブルに行を挿入することはサポートされません。これを試みるとエラーで失敗します。

DATA DIRECTORY および INDEX DIRECTORY オプション  DATA DIRECTORY および INDEX DIRECTORY は、パーティション化されたテーブルで使用するときに次の制限が適用されます。

• テーブルレベル DATA DIRECTORY および INDEX DIRECTORY オプションは無視されます (Bug #32091 を参照してください)。

• Windows では、DATA DIRECTORY および INDEX DIRECTORY オプションは、MyISAM テーブルの個々のパーティションまたはサブパーティションでサポートされません (Bug #30459)。ただし、InnoDB テーブルの個々のパーティションまたはサブパーティションには DATA DIRECTORY を使用できます。

パーティション化されたテーブルを修復および再構築する  ステートメント CHECK TABLE、OPTIMIZE TABLE、ANALYZE TABLE、および REPAIR TABLE がパーティション化されたテーブルでサポートされます。

また、ALTER TABLE ... REBUILD PARTITION を使用することで、パーティション化されたテーブルの 1 つ以上のパーティションを再構築できます。ALTER TABLE ... REORGANIZE PARTITION でもパーティションが再構築されます。これら 2 つのステートメントの詳細については、セクション13.1.7「ALTER TABLE 構文」を参照してください。

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

FOR EXPORT オプション (FLUSH TABLES)  FLUSH TABLES ステートメントの FOR EXPORT オプションは、MySQL 5.6.16 以前のパーティション化された InnoDB テーブルでサポートされません (Bug #16943907)。