第 19 章 パーティション化

目次

19.1 MySQL のパーティショニングの概要
19.2 パーティショニングタイプ
19.2.1 RANGE パーティショニング
19.2.2 LIST パーティショニング
19.2.3 COLUMNS パーティショニング
19.2.4 HASH パーティショニング
19.2.5 KEY パーティショニング
19.2.6 サブパーティショニング
19.2.7 MySQL パーティショニングによる NULL の扱い
19.3 パーティション管理
19.3.1 RANGE および LIST パーティションの管理
19.3.2 HASH および KEY パーティションの管理
19.3.3 パーティションとサブパーティションをテーブルと交換する
19.3.4 パーティションの保守
19.3.5 パーティションに関する情報を取得する
19.4 パーティションプルーニング
19.5 パーティション選択
19.6 パーティショニングの制約と制限
19.6.1 パーティショニングキー、主キー、および一意キー
19.6.2 ストレージエンジンに関連するパーティショニング制限
19.6.3 関数に関連するパーティショニング制限
19.6.4 パーティショニングとロック

この章では、MySQL でのユーザー定義パーティショニングの実装について説明します。使用している MySQL サーバーがパーティショニングをサポートしているかどうかを判別するには、次のように SHOW PLUGINS ステートメントの出力を確認します。

注記

MySQL の以前のバージョンには、have_partitioning 変数がありましたが、非推奨となり、MySQL 5.6.1 で削除されました。

mysql> SHOW PLUGINS;+------------+----------+----------------+---------+---------+
| Name | Status | Type | Library | License |
+------------+----------+----------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL || partition | ACTIVE | STORAGE ENGINE | NULL | GPL || ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| ndbcluster | DISABLED | STORAGE ENGINE | NULL | GPL |
+------------+----------+----------------+---------+---------+
11 rows in set (0.00 sec)

次のようなクエリーを使用して INFORMATION_SCHEMA.PLUGINS テーブルを確認することもできます。

mysql> SELECT -> PLUGIN_NAME as Name, -> PLUGIN_VERSION as Version, -> PLUGIN_STATUS as Status -> FROM INFORMATION_SCHEMA.PLUGINS -> WHERE PLUGIN_TYPE='STORAGE ENGINE';+--------------------+---------+--------+
| Name | Version | Status |
+--------------------+---------+--------+
| binlog | 1.0 | ACTIVE |
| CSV | 1.0 | ACTIVE |
| MEMORY | 1.0 | ACTIVE |
| MRG_MYISAM | 1.0 | ACTIVE |
| MyISAM | 1.0 | ACTIVE |
| PERFORMANCE_SCHEMA | 0.1 | ACTIVE |
| BLACKHOLE | 1.0 | ACTIVE |
| ARCHIVE | 3.0 | ACTIVE |
| InnoDB | 5.6 | ACTIVE || partition | 1.0 | ACTIVE |+--------------------+---------+--------+
10 rows in set (0.00 sec)

どちらの場合も、出力で partition プラグインの Status カラムに値 ACTIVE が示されていない (それぞれの例で太字で示されています) 場合、使用しているバージョンの MySQL にはパーティショニングサポートが組み込まれていません。

オラクルによって提供されている MySQL 5.6 Community バイナリには、パーティショニングのサポートが含まれています。商用の MySQL サーバーバイナリで提供されているパーティショニングサポートについては、第25章「MySQL Enterprise Editionを参照してください。

ソースから MySQL 5.6 をコンパイルする場合にパーティショニングを有効にするには、-DWITH_PARTITION_STORAGE_ENGINE オプションを指定してビルドを構成する必要があります。詳細は、セクション2.9「ソースから MySQL をインストールする」を参照してください。

使用している MySQL バイナリにパーティショニングサポートが組み込まれている場合は、それを有効にするために何も行う必要はありません (たとえば、my.cnf ファイルに特別なエントリを指定する必要はありません)。

パーティショニングサポートを無効にするには、--skip-partition オプションを指定して MySQL サーバーを起動します。その場合は、have_partitioning の値が DISABLED になります。パーティショニングサポートを無効にすると、既存のパーティション化されたテーブルの表示、およびそれらの削除は行うことができますが (ただし、これを行うことはお勧めしません)、それ以外にそれらを操作したり、データにアクセスしたりすることはできません。

パーティショニングの概要およびパーティショニングの概念については、セクション19.1「MySQL のパーティショニングの概要」を参照してください。

MySQL では、いくつかのタイプのパーティショニングおよびサブパーティショニングをサポートしています。セクション19.2「パーティショニングタイプ」およびセクション19.2.6「サブパーティショニング」を参照してください。

セクション19.3「パーティション管理」では、既存のパーティション化されたテーブルでパーティションを追加、削除および変更する方法について説明しています。

セクション19.3.4「パーティションの保守」では、パーティション化されたテーブルで使用するテーブル保守コマンドについて説明しています。

INFORMATION_SCHEMA データベースの PARTITIONS テーブルには、パーティションおよびパーティション化されたテーブルに関する情報があります。詳細は、セクション21.13「INFORMATION_SCHEMA PARTITIONS テーブル」を参照してください。このテーブルに対するクエリーのいくつかの例については、セクション19.2.7「MySQL パーティショニングによる NULL の扱い」を参照してください。

MySQL 5.6 のパーティショニングの既知の問題については、セクション19.6「パーティショニングの制約と制限」を参照してください。

また、パーティション化されたテーブルを使用して作業を行うときに、次のリソースが役に立つことがあります。

追加のリソース  MySQL のユーザー定義パーティショニングに関するその他の情報ソースには、次のものがあります。

MySQL 5.6 バイナリは、https://dev.mysql.com/downloads/mysql/5.6.htmlから入手できます。ただし、最新のパーティショニングバグ修正および機能追加が必要な場合は、Bazaar リポジトリからソースを取得できます。パーティショニングを有効にするには、-DWITH_PARTITION_STORAGE_ENGINE オプションを指定してビルドを構成する必要があります。MySQL のビルドについては、セクション2.9「ソースから MySQL をインストールする」を参照してください。パーティションを有効にした MySQL 5.6 ビルドのコンパイルで問題が発生した場合は、「MySQL Partitioning Forum」をチェックして、問題の解決策がそこにない場合は支援を要請してください。

19.1 MySQL のパーティショニングの概要

このセクションでは、MySQL 5.6 のパーティショニングの概念について説明します。

パーティショニングの制約および機能制限については、セクション19.6「パーティショニングの制約と制限」を参照してください。

SQL 標準では、データ保存の物理的な仕様に関するガイダンスはあまり提供されていません。SQL 言語自体が、それが動作するスキーマ、テーブル、行、またはカラムの基盤となるデータ構造やメディアと独立して動作するように意図されています。それにもかかわらず、ほとんどの高度なデータベース管理システムでは、ファイルシステム、ハードウェア、またはその両方について、特定のデータを格納するために使用される物理的な場所を判別する方法が開発されてきました。MySQL では、InnoDB ストレージエンジンによってテーブルスペースの認識がサポートされてきており、MySQL サーバーは、パーティショニングが導入される前から、異なるデータベースの格納に異なる物理的なディレクトリを使用するように構成できました (これを行う方法については、セクション8.11.3.1「シンボリックリンクの使用」を参照してください)。

パーティショニングはこの認識をさらに一歩進めて、必要に応じて多くの部分を設定できるルールに従って、個々のテーブルの部分をファイルシステムに配分できるようにしています。それにより、テーブルの異なる部分が別個のテーブルとして別個の場所に格納されます。データを分割するためにユーザーが選択するルールはパーティショニング関数と呼ばれ、MySQL では法、範囲セットまたは値リストに対する単純な照合、内部ハッシュ関数、または線形ハッシュ関数が使用されます。関数は、ユーザーが指定したパーティショニングタイプに従って選択され、ユーザーが指定した式の値をパラメータとして取ります。この式には、使用されるパーティショニングのタイプに応じて、カラム値、1 つ以上のカラム値を操作する関数、または 1 つ以上のカラム値のセットを指定できます。

RANGELIST、および [LINEAR] HASH パーティショニングの場合、パーティショニングカラムの値はパーティショニング関数に渡され、特定のレコードを格納すべきパーティションの番号を表す整数値が返されます。この関数は非定数および非ランダムである必要があります。クエリーを含めることはできませんが、式が NULL または次のような整数 intval を返すかぎり、MySQL で有効な SQL 式を使用できます。

-MAXVALUE <= intval <= MAXVALUE

(MAXVALUE は対象となる整数型の上限を表すために使用されます。-MAXVALUE は下限を表します。)

[LINEAR] KEYRANGE COLUMNS、および LIST COLUMNS パーティショニングの場合、パーティショニング式は 1 つ以上のカラムのリストから構成されます。

[LINEAR] KEY パーティショニングの場合、パーティショニング関数は MySQL によって提供されます。

許可されるパーティショニングカラムタイプおよびパーティショニング関数については、セクション19.2「パーティショニングタイプ」、およびパーティショニング構文の説明および追加例を示しているセクション13.1.17「CREATE TABLE 構文」を参照してください。パーティショニング関数の制約については、セクション19.6.3「関数に関連するパーティショニング制限」を参照してください。

これは「水平パーティショニング」と呼ばれます。つまり、テーブル内の異なる行を異なる物理パーティションに割り当てることができます。MySQL 5.6 は、テーブルの異なるカラムが異なる物理パーティションに割り当てられる、「垂直パーティショニング」はサポートしていません。現時点で、垂直パーティショニングを MySQL 5.6 に導入する計画はありません。

MySQL サーバーバイナリがユーザー定義パーティショニングをサポートしているかどうかの判断については、第19章「パーティション化を参照してください。

パーティション化されたテーブルを作成する場合、MySQL サーバーによってサポートされているほとんどのストレージエンジンを使用できます。MySQL パーティショニングエンジンは別のレイヤーで実行されており、それらのいずれともやり取りできます。MySQL 5.6 では、同じパーティション化されたテーブルのすべてのパーティションは同じストレージエンジンを使用する必要があります。たとえば、あるパーティションに MyISAM、別のものに InnoDB を使用することはできません。ただし、同じ MySQL サーバーまたは同じデータベース上の異なるパーティション化されたテーブルに、異なるストレージエンジンを使用することはできます。

MySQL パーティショニングは、MERGECSV、または FEDERATED ストレージエンジンで使用できません。

KEY または LINEAR KEY によるパーティショニングは NDB で使用できますが、ほかのタイプのユーザー定義パーティショニングはこのストレージエンジンを使用するテーブルでサポートされません。また、ユーザー定義パーティショニングを使用する NDB テーブルには明示的な主キーが必要であり、テーブルのパーティショニング式で参照されるカラムは主キーの一部である必要があります。ただし、ユーザーパーティション化された NDB テーブルを作成または変更するために使用される CREATE TABLE または ALTER TABLE ステートメントの PARTITION BY KEY 句または PARTITION BY LINEAR KEY 句にカラムが 1 つもリストされていない場合は、テーブルに明示的な主キーは必要ありません。詳細は、セクション18.1.6.1「MySQL Cluster での SQL 構文の不適合」を参照してください。

パーティション化されたテーブルに特定のストレージエンジンを使用するために必要なのは、パーティション化されていないテーブルの場合と同様に、[STORAGE] ENGINE オプションを使用することだけです。ただし、[STORAGE] ENGINE (およびほかのテーブルオプション) は、CREATE TABLE ステートメントでパーティショニングオプションを使用する前に指定する必要があります。次の例は、ハッシュによって 6 個のパーティションにパーティション化され、InnoDB ストレージエンジンを使用するテーブルの作成方法を示しています。

CREATE TABLE ti (id INT, amount DECIMAL(7,2), tr_date DATE) ENGINE=INNODB PARTITION BY HASH( MONTH(tr_date) ) PARTITIONS 6;

PARTITION 句に [STORAGE] ENGINE オプションを含めることはできますが、MySQL 5.6 ではこれは効果がありません。

重要

パーティショニングはテーブルのすべてのデータおよびインデックスに適用されます。データだけにパーティション化しインデックスは行わないことはできず (その逆も不可)、テーブルの一部のみをパーティション化することもできません。

各パーティションのデータおよびインデックスを特定のディレクトリに割り当てるには、パーティション化されたテーブルを作成するために使用する CREATE TABLE ステートメントの PARTITION 句に DATA DIRECTORY および INDEX DIRECTORY オプションを使用します。

Windows 上の MyISAM テーブルの個別のパーティションまたはサブパーティションでは、DATA DIRECTORY および INDEX DIRECTORY はサポートされません。これらは InnoDB テーブルの個別のパーティションおよびサブパーティションでサポートされます (すべてのプラットフォームと同様)。

また、MAX_ROWS および MIN_ROWS は、各パーティションに格納できる行のそれぞれ最大数および最小数を決定するために使用できます。MAX_ROWS オプションは、MySQL Cluster テーブルを追加パーティションで作成し、ハッシュインデックスにより大きなストレージを確保するために役立つことがあります。詳細は、DataMemory データノード構成パラメータのドキュメント、および セクション18.1.2「MySQL Cluster のノード、ノードグループ、レプリカ、およびパーティション」を参照してください。

パーティショニングのいくつかの利点を次に示します。

  • パーティショニングを使用すると、単一ディスクまたはファイルシステムパーティションに保持できるデータより多くのデータを 1 つのテーブルに格納できます。

  • 有効性を失っているデータは、多くの場合、そのデータのみが含まれているパーティションを削除することによって、パーティション化されたテーブルから簡単に削除できます。反対に、新しいデータを追加する処理は、そのデータだけを格納するための 1 つ以上の新しいパーティションを追加することによって、非常に便利になることがあります。

  • 指定された WHERE 句を満たすデータを 1 つ以上のパーティションのみに格納できることによって、検索からほかのパーティションが自動的に除外され、一部のクエリーが大幅に最適化されることがあります。パーティションはパーティション化されたテーブルが作成されたあとに変更できるため、使用頻度の高いクエリー (パーティショニングスキームが最初に設定されたときはあまり使用されていなかった) を改善するためにデータを再編成できます。一致しないパーティション (およびそれらに含まれている行) を除外するこの機能は、よくパーティションプルーニングと呼ばれます。詳細は、セクション19.4「パーティションプルーニング」を参照してください。

    また、MySQL 5.6 ではクエリーで明示的なパーティション選択がサポートされます。たとえば、SELECT * FROM t PARTITION (p0,p1) WHERE c < 5 は、パーティション p0 および p1WHERE 条件に一致する行のみを選択します。この場合、MySQL はテーブル t のほかのパーティションをチェックしません。これにより、検査するパーティションが事前にわかっているときにクエリー速度が大幅に向上することがあります。パーティション選択は、データ変更ステートメント (DELETEINSERTREPLACEUPDATELOAD DATA、および LOAD XML) でもサポートされます。詳細および例については、これらのステートメントの説明を参照してください。

通常はパーティショニングに関連付けられるその他の利点を次に示します。これらの機能は MySQL パーティショニングに現在実装されていませんが、オラクルの優先事項リストの上位にあります。

  • SUM()COUNT() などの集約関数を含むクエリーを簡単に並列化できます。そのようなクエリーの簡単な例として、SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id; を挙げられます。並列化することで、クエリーを各パーティションで同時に実行してから、すべてのパーティションで取得される結果を単に集約するだけで最終的な結果が得られることを意味します。

  • 複数のディスクにデータシークを分散することによって、クエリーのスループットが向上します。

MySQL パーティショニング開発は継続されているため、このセクションおよび章が更新されているかどうかを頻繁にチェックしてください。

19.2 パーティショニングタイプ

このセクションでは、MySQL 5.6 で使用できるパーティショニングのタイプについて説明します。これらには、次に一覧したタイプが含まれます。

  • RANGE パーティショニング  このタイプのパーティショニングは、指定された範囲に含まれるカラム値に基づいて、行をパーティションに割り当てます。セクション19.2.1「RANGE パーティショニング」を参照してください。このタイプを拡張した RANGE COLUMNS については、セクション19.2.3.1「RANGE COLUMNS パーティショニング」を参照してください。

  • LIST パーティショニング RANGE によるパーティショニングに似ていますが、別個の値のセットのいずれかに一致するカラムに基づいて、パーティションが選択されます。セクション19.2.2「LIST パーティショニング」を参照してください。このタイプを拡張した LIST COLUMNS については、セクション19.2.3.2「LIST COLUMNS パーティショニング」を参照してください。

  • HASH パーティショニング  このタイプのパーティショニングでは、テーブルに挿入される行内のカラム値を操作するユーザー定義式によって返される値に基づいて、パーティションが選択されます。関数は、負ではない整数値を返す MySQL の有効な式で構成できます。このタイプを拡張した LINEAR HASH も使用できます。セクション19.2.4「HASH パーティショニング」を参照してください。

  • KEY パーティショニング  このタイプのパーティショニングは、HASH によるパーティショニングに似ていますが、評価される 1 つ以上のカラムのみを指定し、MySQL サーバーが独自のハッシュ関数を提供します。MySQL によって提供されるハッシュ関数ではカラムデータ型に関係なく整数結果が保証されるため、これらのカラムに整数以外の値が含まれていてもかまいません。このタイプを拡張した LINEAR KEY も使用できます。セクション19.2.5「KEY パーティショニング」を参照してください。

データベースパーティショニングの非常に一般的な使用方法は、日付によってデータを分けることです。一部のデータベースシステムは、MySQL 5.6 では実装されていない、明示的な日付パーティショニングをサポートしています。ただし、MySQL で、DATETIME、または DATETIME カラムに基づいて、またはそのようなカラムを使用する式に基づいて、パーティショニングスキームを作成することは難しくありません。

KEY または LINEAR KEY でパーティショニングする場合は、DATETIME、または DATETIME カラムを、カラム値の変更を実行しないパーティショニングカラムとして使用できます。たとえば、次のテーブル作成ステートメントは MySQL で完全に有効です。

CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL
)
PARTITION BY KEY(joined)
PARTITIONS 6;

MySQL 5.6 では、RANGE COLUMNS および LIST COLUMNS パーティショニングを使用して、DATE または DATETIME カラムをパーティショニングカラムとして使用することもできます。

ただし、MySQL のほかのパーティショニングタイプでは、整数値または NULL を返すパーティショニング式が必要です。RANGELISTHASH、または LINEAR HASH による日付ベースパーティショニングを使用する場合は、次のように単純に DATETIME、または DATETIME カラムを操作してそのような値を返す関数を使用できます。

CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) ( PARTITION p0 VALUES LESS THAN (1960), PARTITION p1 VALUES LESS THAN (1970), PARTITION p2 VALUES LESS THAN (1980), PARTITION p3 VALUES LESS THAN (1990), PARTITION p4 VALUES LESS THAN MAXVALUE
);

日付を使用したパーティショニングの追加例は、この章の次のセクションにあります。

日付ベースパーティショニングのより複雑な例については、次のセクションを参照してください。

MySQL パーティショニングは、TO_DAYS()YEAR()、および TO_SECONDS() 関数で使用するために最適化されています。ただし、整数または NULL を返すほかの日時関数 (WEEKDAY()DAYOFYEAR()MONTH() など) を使用できます。そのような関数の詳細については、セクション12.7「日付および時間関数」を参照してください。

使用するパーティショニングのタイプにかかわらず、パーティションには作成時に常に 0 から始まる番号が順番に自動的に付けられることを覚えておくことが重要です。新しい行がパーティション化されたテーブルに挿入されるときは、これらのパーティション番号が正しいパーティションを識別するために使用されます。たとえば、テーブルで 4 つのパーティションが使用される場合、これらのパーティションには 012、および 3 という番号が付けられます。RANGE および LIST パーティショニングタイプの場合は、各パーティション番号のパーティションが定義されている必要があります。HASH パーティショニングの場合は、使用されるユーザー関数が 0 より大きい整数値を返す必要があります。KEY パーティショニングの場合は、MySQL サーバーが内部で使用しているハッシュ関数によって、この問題が自動的に対処されます。

パーティションの名前は通常、ほかの MySQL 識別子 (テーブル名、データベース名など) を制御するルールに従っています。ただし、パーティション名では大文字/小文字が区別されません。たとえば、次の CREATE TABLE ステートメントは、示されているように失敗します。

mysql> CREATE TABLE t2 (val INT) -> PARTITION BY LIST(val)( -> PARTITION mypart VALUES IN (1,3,5), -> PARTITION MyPart VALUES IN (2,4,6) -> );ERROR 1488 (HY000): Duplicate partition name mypart

失敗は、MySQL がパーティション名 mypartMyPart の違いを認識できないために発生します。

テーブルのパーティション番号を指定するときは、先行ゼロなしのゼロ以外の正の整数リテラルとして表現する必要があり、0.8E+016-2 などの式であってはいけません (これが整数値に評価されるとしても)。小数は許可されません。

以降のセクションでは、各パーティションタイプの作成に使用できるすべての形式の構文を提供しているわけではありません。この情報については、セクション13.1.17「CREATE TABLE 構文」も参照してみてください。

19.2.1 RANGE パーティショニング

範囲によってパーティション化されるテーブルは、各パーティションに含まれる行のパーティショニング式値が指定された範囲に収まるようにパーティション化されます。範囲は、連続しているけれども重複しないものであるべきで、VALUES LESS THAN 演算子を使用して定義されます。次のいくつかの例では、20 のビデオ店で構成されるチェーン (1 から 20 までの番号が付けられている) の従業員レコードを保持する、次のようなテーブルを作成していると想定してください。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL
);
注記

ここで使用する employees テーブルには主キーまたは一意キーがありません。これらの例はここでの説明のためのもので、実際のテーブルは主キー、一意キー、またはその両方を備えている可能性がきわめて高く、パーティショニングカラムに利用できる選択肢はこれらのキー (ある場合) に使用されるカラムに依存します。これらの事項については、セクション19.6.1「パーティショニングキー、主キー、および一意キー」を参照してください。

このテーブルは、必要に応じていくつかの方法で、範囲によるパーティション化を実行できます。1 つの方法は、store_id カラムを使用することです。たとえば、次のように PARTITION BY RANGE 句を追加することで、テーブルを 4 つのパーティションに分割することを決定できます。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN (21)
);

このパーティショニングスキームでは、店舗 1 から店舗 5 で働いている従業員に対応するすべての行がパーティション p0 に格納され、店舗 6 から店舗 10 の従業員がパーティション p1 に格納されます (以下同様)。各パーティションは、もっとも小さい値からもっとも大きい値の順で定義されます。これは PARTITION BY RANGE 構文の要件です。これは、C または Java の一連の if ... elseif ... ステートメントに似ていると考えることができます。

データ (72, 'Michael', 'Widenius', '1998-06-25', NULL, 13) が含まれている新しい行がパーティション p2 に挿入されることは簡単に判断できますが、このチェーンに 21 番目の店舗が追加されたらどうなるでしょうか。このスキームでは、store_id が 20 よりも大きい行に対応するルールがなく、サーバーはどこに置くべきかがわからないため、エラーになります。これが発生しないようにするには、すべての状況に対応するVALUES LESS THAN 句を CREATE TABLE ステートメントで使用して、明示的に指定されている最大値を超えるすべての値に備えます。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11), PARTITION p2 VALUES LESS THAN (16), PARTITION p3 VALUES LESS THAN MAXVALUE);
注記

一致する値が見つからないときにエラーを回避するための別の方法は、INSERT ステートメントの一部として IGNORE キーワードを使用することです。例については、セクション19.2.2「LIST パーティショニング」を参照してください。また、IGNORE の一般的な情報については、セクション13.2.5「INSERT 構文」を参照してください。

MAXVALUE は、可能な最大整数値よりも確実に大きな整数値を表します (数学用語では、上限です)。これによって、store_id カラム値が 16 (定義されている最大値) 以上である行は、パーティション p3 に格納されます。将来のある時点で、店舗の数が 25、30、またはそれ以上に増加したときは、ALTER TABLE ステートメントを使用して、店舗 21-25、26-30 などのための新しいパーティションを追加できます (これを行う方法の詳細については、セクション19.3「パーティション管理」を参照してください)。

同様に、従業員ジョブコードに基づいて (つまり、job_code カラム値の範囲に基づいて) テーブルをパーティション化できます。たとえば、正規 (インストア) 従業員に 2 桁のジョブコード、オフィスおよびサポート従業員に 3 桁のコード、および管理職に 4 桁のコードが使用されると想定すると、次のステートメントを使用してパーティション化されたテーブルを作成できます。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL
)
PARTITION BY RANGE (job_code) ( PARTITION p0 VALUES LESS THAN (100), PARTITION p1 VALUES LESS THAN (1000), PARTITION p2 VALUES LESS THAN (10000)
);

この例では、インストア従業員に関連するすべての行はパーティション p0、オフィスおよびサポートスタッフに関連するものは p1、および管理職に関連するものはパーティション p2 に格納されます。

VALUES LESS THAN 句に式を使用することもできます。ただし、MySQL が式の戻り値を LESS THAN (<) 比較の一部として評価できる必要があります。

店舗番号に従ってテーブルデータを分割するのではなく、代わりに 2 つの DATE カラムのうちの 1 つに基づく式を使用できます。たとえば、各従業員が会社を退職した年 (つまり、YEAR(separated) の値) に基づいてパーティション化するとします。そのようなパーティショニングスキームを実装する CREATE TABLE ステートメントの例を次に示します。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT
)
PARTITION BY RANGE ( YEAR(separated) ) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996), PARTITION p2 VALUES LESS THAN (2001), PARTITION p3 VALUES LESS THAN MAXVALUE
);

このスキームでは、1991 年より前に退職したすべての従業員の場合、行はパーティション p0 に格納されます。1991 年から 1995 年までに退職した人は p1、1996 年から 2000 年までに退職した人は p2、および 2000 年よりあとに退職した従業員は p3 に格納されます。

次の例に示すように、UNIX_TIMESTAMP() 関数を使用して、TIMESTAMP カラムの値に基づいて、RANGE によってテーブルをパーティション化することもできます。

CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ), PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ), PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ), PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ), PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ), PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ), PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ), PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

TIMESTAMP 値を含むほかの式は許可されません(Bug #42849 を参照してください)。

次の条件の 1 つ以上が true のときは、RANGE パーティショニングが特に役立ちます。

  • 古いデータを削除したい、またはする必要がある。直前のパーティショニングスキームを使用している場合は、単純に ALTER TABLE employees DROP PARTITION p0; を使用して 1991 年より前に会社を退職した従業員に関連するすべての行を削除できます。(詳細は、セクション13.1.7「ALTER TABLE 構文」およびセクション19.3「パーティション管理」を参照してください)。テーブルに多数の行がある場合、これは DELETE FROM employees WHERE YEAR(separated) <= 1990; などの DELETE クエリーを実行するよりもはるかに効率的な場合があります。

  • 日付または時間値、または何らかのほかの一連値から生じる値が含まれるカラムを使用したい。

  • テーブルのパーティショニングに使用されるカラムに直接依存するクエリーを頻繁に実行する。たとえば、EXPLAIN PARTITIONS SELECT COUNT(*) FROM employees WHERE separated BETWEEN '2000-01-01' AND '2000-12-31' GROUP BY store_id; などのクエリーを実行する場合、MySQL はパーティション p2 のみをスキャンする必要であることをすばやく判断できます (残りのパーティションに WHERE 句を満たすレコードが含まれるはずがないため)。これがどのように実現されるかについての詳細は、セクション19.4「パーティションプルーニング」を参照してください。

このタイプのパーティショニングのバリアントが RANGE COLUMNS パーティショニングです。RANGE COLUMNS によるパーティショニングでは、複数のカラムを使用してパーティショニング範囲を定義できます (パーティション内での行の配置、およびパーティションプルーニングを実行するときに特定のパーティションの包含または除外を判断する際に適用されます)。詳細は、セクション19.2.3.1「RANGE COLUMNS パーティショニング」を参照してください。

時間間隔に基づくパーティショニングスキーム  MySQL 5.6 で時間の範囲または間隔に基づいてパーティショニングスキームを実装する場合は、2 つの方法があります。

  1. 次のように、RANGE によってテーブルをパーティション化し、パーティショニング式に DATETIME、または DATETIME カラムを操作して整数値を返す関数を使用します。

    CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL
    )
    PARTITION BY RANGE( YEAR(joined) ) ( PARTITION p0 VALUES LESS THAN (1960), PARTITION p1 VALUES LESS THAN (1970), PARTITION p2 VALUES LESS THAN (1980), PARTITION p3 VALUES LESS THAN (1990), PARTITION p4 VALUES LESS THAN MAXVALUE
    );

    MySQL 5.6 では、次の例に示すように UNIX_TIMESTAMP() 関数を使用して、TIMESTAMP カラムの値に基づいて RANGE によってテーブルをパーティション化することもできます。

    CREATE TABLE quarterly_report_status ( report_id INT NOT NULL, report_status VARCHAR(20) NOT NULL, report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
    PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) ( PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ), PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00') ), PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00') ), PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00') ), PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00') ), PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00') ), PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00') ), PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00') ), PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ), PARTITION p9 VALUES LESS THAN (MAXVALUE)
    );

    MySQL 5.6 では、TIMESTAMP 値を含むほかの式は許可されません。(Bug #42849 を参照してください)。

    注記

    MySQL 5.6 では、LIST によってパーティション化されるテーブルのパーティショニング式として UNIX_TIMESTAMP(timestamp_column) を使用することもできます。ただし、このようにするのは通常は実用的ではありません。

  2. DATE または DATETIME カラムをパーティショニングカラムとして使用して、RANGE COLUMNS によってテーブルをパーティション化します。たとえば、次のように joined カラムを直接使用して members テーブルを定義できます。

    CREATE TABLE members ( firstname VARCHAR(25) NOT NULL, lastname VARCHAR(25) NOT NULL, username VARCHAR(16) NOT NULL, email VARCHAR(35), joined DATE NOT NULL
    )
    PARTITION BY RANGE COLUMNS(joined) ( PARTITION p0 VALUES LESS THAN ('1960-01-01'), PARTITION p1 VALUES LESS THAN ('1970-01-01'), PARTITION p2 VALUES LESS THAN ('1980-01-01'), PARTITION p3 VALUES LESS THAN ('1990-01-01'), PARTITION p4 VALUES LESS THAN MAXVALUE
    );
注記

DATE または DATETIME 以外の日付または時間型を使用してパーティショニングカラムを使用することは、RANGE COLUMNS ではサポートされません。

19.2.2 LIST パーティショニング

MySQL の LIST パーティショニングは、多くの点で RANGE パーティショニングに似ています。RANGE によるパーティショニングと同様に、各パーティションを明示的に定義する必要があります。2 つのタイプのパーティショニングの主な違いは、LIST パーティショニングでは、各パーティションが、連続する値の範囲のセットのいずれかではなく、値リストのセットのいずれかに含まれるカラム値のメンバーシップに基づいて定義および選択されることです。これを行うには、PARTITION BY LIST (expr) を使用します。ここで、expr はカラム値またはカラム値に基づく式で、整数値を返し、VALUES IN (value_list) で各パーティションを定義します。ここで、value_list はカンマで区切られた整数のリストです。

注記

MySQL 5.6 では、LIST によってパーティション化するときに、整数 (および NULL も可。セクション19.2.7「MySQL パーティショニングによる NULL の扱い」を参照してください) のリストに対してのみ照合できます。

ただし、LIST COLUMN パーティショニングを使用するときは、ほかのカラムタイプを値リストで使用できます (これについては、このセクションで後述します)。

範囲で定義されるパーティションの場合と異なり、リストパーティションは特定の順序で宣言する必要はありません。構文についての詳細は、セクション13.1.17「CREATE TABLE 構文」を参照してください。

以降の例では、パーティション化するテーブルの基本定義が、次に示す CREATE TABLE ステートメントによって提供されることを想定します。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT
);

(これは、セクション19.2.1「RANGE パーティショニング」の例の出発点として使用したものと同じテーブルです。)

次の表に示すように、20 のビデオ店があり、それらが 4 つのフランチャイズに分類されていると想定します。

地域店舗 ID 番号
3、5、6、9、17
1、2、10、11、19、20
西4、12、13、14、18
中央7、8、15、16

同じ地域に属する店舗の行が同じパーティションに格納されるようにこのテーブルをパーティション化するには、次のような CREATE TABLE ステートメントを使用できます。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT
)
PARTITION BY LIST(store_id) ( PARTITION pNorth VALUES IN (3,5,6,9,17), PARTITION pEast VALUES IN (1,2,10,11,19,20), PARTITION pWest VALUES IN (4,12,13,14,18), PARTITION pCentral VALUES IN (7,8,15,16)
);

これにより、特定の地域に関連する従業員レコードをテーブルで簡単に追加または削除できるようになります。たとえば、西地域の全店舗が別の会社に売却されたとします。MySQL 5.6 では、その地域の店舗で働いていた従業員に関連するすべての行をクエリー ALTER TABLE employees TRUNCATE PARTITION pWest を使用して削除できます。これは、同等の DELETE ステートメント DELETE FROM employees WHERE store_id IN (4,12,13,14,18); よりもはるかに効率的に実行できます(ALTER TABLE employees DROP PARTITION pWest を使用してもこれらのすべての行が削除されますが、テーブルの定義からパーティション pWest も削除されるため、ALTER TABLE ... ADD PARTITION ステートメントを使用してテーブルの元のパーティショニングスキームをリストアする必要があります)。

RANGE パーティショニングと同様に、LIST パーティショニングとハッシュまたはキーによるパーティショニングを組み合わせることによって、複合パーティショニング (サブパーティショニング) を生成できます。セクション19.2.6「サブパーティショニング」を参照してください。

RANGE パーティショニングの場合と異なり、MAXVALUE などのすべての状況に対応するものはありません。パーティショニング式で予期されるすべての値を PARTITION ... VALUES IN (...) 句で指定してください。一致しないパーティショニングカラム値が含まれている INSERT ステートメントは、次の例に示すように、エラーで失敗します。

mysql> CREATE TABLE h2 ( -> c1 INT, -> c2 INT -> ) -> PARTITION BY LIST(c1) ( -> PARTITION p0 VALUES IN (1, 4, 7), -> PARTITION p1 VALUES IN (2, 5, 8) -> );Query OK, 0 rows affected (0.11 sec)
mysql> INSERT INTO h2 VALUES (3, 5);ERROR 1525 (HY000): Table has no partition for value 3

単一 INSERT ステートメントを使用して複数の行を挿入するときの動作は、テーブルがトランザクションストレージエンジンを使用するかどうかによって変わります。InnoDB テーブルの場合、ステートメントは単一トランザクションと見なされ、一致しない値があるときはステートメントが完全に失敗し、行は挿入されません。MyISAM などの非トランザクションストレージエンジンを使用するテーブルの場合、一致しない値が含まれている行の前にある行は挿入されますが、あとにある行はされません。

このタイプのエラーは、IGNORE キーワードを使用することで無視させることができます。そうした場合、一致しないパーティショニングカラム値が含まれる行は挿入されませんが、一致する値を持つ行は挿入されてエラーが報告されません。

mysql> TRUNCATE h2;Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM h2;Empty set (0.00 sec)
mysql> INSERT IGNORE INTO h2 VALUES (2, 5), (6, 10), (7, 5), (3, 1), (1, 9);Query OK, 3 rows affected (0.00 sec)
Records: 5 Duplicates: 2 Warnings: 0
mysql> SELECT * FROM h2;+------+------+
| c1 | c2 |
+------+------+
| 7 | 5 |
| 1 | 9 |
| 2 | 5 |
+------+------+
3 rows in set (0.00 sec)

MySQL 5.6 は LIST COLUMNS パーティショニングのサポートを提供します。これは、LIST パーティショニングのバリアントで、パーティショニングカラムに整数型以外の型のカラムを使用したり、複数のカラムをパーティショニングキーとして使用したりできます。詳細は、セクション19.2.3.2「LIST COLUMNS パーティショニング」を参照してください。

19.2.3 COLUMNS パーティショニング

次の 2 つのセクションでは、RANGE および LIST パーティショニングのバリアントである COLUMNS パーティショニングについて説明します。COLUMNS パーティショニングでは、パーティショニングキーに複数のカラムを使用できます。これらのすべてのカラムが、パーティションに行を配置するため、およびパーティションプルーニングでどのパーティションで一致する行をチェックするかを判断するという両方の目的のために考慮されます。

また、RANGE COLUMNS パーティショニングおよび LIST COLUMNS パーティショニングの両方が、値範囲またはリストメンバーの定義のために整数以外のカラムの使用をサポートします。許可されるデータ型を次のリストに示します。

  • すべての整数型: TINYINTSMALLINTMEDIUMINTINT (INTEGER)、および BIGINT(これは、RANGE および LIST によるパーティショニングと同じです)。

    ほかの数値データ型 (DECIMALFLOAT など) はパーティショニングカラムとしてサポートされません。

  • DATE および DATETIME

    日付または時間に関連するほかのデータ型を使用するカラムは、パーティショニングカラムとしてサポートされません。

  • 次の文字列型: CHARVARCHARBINARY、および VARBINARY

    TEXT カラムおよび BLOB カラムはパーティショニングカラムとしてサポートされません。

次の 2 つのセクションでの RANGE COLUMNS および LIST COLUMNS パーティショニングの説明では、MySQL 5.1 以降でサポートされる範囲およびリストに基づくパーティショニングをすでに理解していることを想定しています。これらについての詳細は、セクション19.2.1「RANGE パーティショニング」およびセクション19.2.2「LIST パーティショニング」をそれぞれ参照してください。

19.2.3.1 RANGE COLUMNS パーティショニング

RANGE COLUMNS パーティショニングは RANGE パーティショニングに似ていますが、複数のカラム値に基づく範囲を使用してパーティションを定義できます。また、整数型以外の型のカラムを使用して範囲を定義できます。

RANGE COLUMNS パーティショニングは、次の点で RANGE パーティショニングと大きく異なります。

  • RANGE COLUMNS は式を受け入れません (カラムの名前のみ)。

  • RANGE COLUMNS は 1 つ以上のカラムのリストを受け入れます。

    RANGE COLUMNS パーティションは、スカラー値の比較ではなく、タプル (カラム値のリスト) の比較に基づきます。RANGE COLUMNS パーティションでの行の配置も、タプルの比較に基づきます。これについては、このセクションで後述します。

  • RANGE COLUMNS パーティショニングカラムは整数カラムに制限されません。文字列、DATE、および DATETIME カラムもパーティショニングカラムとして使用できます。(詳細は、セクション19.2.3「COLUMNS パーティショニング」を参照してください)。

RANGE COLUMNS によってパーティション化されたテーブルを作成するための基本構文を次に示します。

CREATE TABLE table_namePARTITIONED BY RANGE COLUMNS(column_list) ( PARTITION partition_name VALUES LESS THAN (value_list)[, PARTITION partition_name VALUES LESS THAN (value_list)][, ...]
) column_list: column_name[, column_name][, ...]value_list: value[, value][, ...]
注記

パーティション化されたテーブルを作成するときに使用できる CREATE TABLE オプションをすべて示しているわけではありません。詳細は、セクション13.1.17「CREATE TABLE 構文」を参照してください。

前述の構文で、column_list は 1 つ以上のカラムのリスト (パーティショニングカラムリストと呼ばれることもあります)、value_list は値のリスト (つまり、パーティション定義値リストです) です。value_list は各パーティション定義に指定する必要があり、各 value_list には column_list のカラムと同じ数の値が必要です。一般的に、COLUMNS 句に N 個のカラムを使用する場合は、各 VALUES LESS THAN 句にも N 個の値のリストを指定する必要があります。

パーティショニングカラムリストおよび各パーティションを定義する値リスト内の要素は、同じ順序で指定する必要があります。また、値リスト内の各要素は、カラムリスト内の対応する要素と同じデータ型である必要があります。ただし、パーティショニングカラムリストおよび値リスト内のカラム名の順序は、CREATE TABLE ステートメントの主要部内のテーブルカラム定義の順序と同じである必要はありません。RANGE によってパーティション化されるテーブルと同様に、MAXVALUE を使用して、指定されたカラムに挿入される正当な値より確実に大きな値を表すことができます。これらの点をすべて説明するために役立つ CREATE TABLE ステートメントの例を次に示します。

mysql> CREATE TABLE rcx ( -> a INT, -> b INT, -> c CHAR(3), -> d INT -> ) -> PARTITION BY RANGE COLUMNS(a,d,c) ( -> PARTITION p0 VALUES LESS THAN (5,10,'ggg'), -> PARTITION p1 VALUES LESS THAN (10,20,'mmmm'), -> PARTITION p2 VALUES LESS THAN (15,30,'sss'), -> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) -> );Query OK, 0 rows affected (0.15 sec)

テーブル rcx にはカラム abc、および d が含まれています。COLUMNS 句に指定されたパーティショニングカラムリストには、これらのカラムのうちの 3 つが ad、および c の順に使用されています。パーティションを定義するために使用される各値リストには、同じ順序で 3 つの値が含まれます。つまり、各値リストタプルの形式は、カラム ad、および c が (この順序で) 使用するデータ型に対応する、(INT, INT, CHAR(3)) です。

パーティションに行がどのように配置されるかは、挿入される行のタプル (COLUMNS 句内のカラムリストに一致) とVALUES LESS THAN 句に使用されるタプル (テーブルのパーティションを定義) を比較することによって判断されます。スカラー値ではなくタプル (つまり、値のリストまたはセット) を比較するため、RANGE COLUMNS パーティションで使用される VALUES LESS THAN のセマンティクスは、単純な RANGE パーティションの場合とは若干異なります。RANGE パーティショニングでは、VALUES LESS THAN 内の制限値と等しい式値を生成する行は、対応するパーティションに配置されません。ただし、RANGE COLUMNS パーティショニングを使用するときは、パーティショニングカラムリストの最初の要素が VALUES LESS THAN 値リスト内の最初の要素と値が等しい行が、対応するパーティションに配置されることがあります。

次のステートメントによって作成されるパーティション化された RANGE テーブルを検討します。

CREATE TABLE r1 ( a INT, b INT
)
PARTITION BY RANGE (a) ( PARTITION p0 VALUES LESS THAN (5), PARTITION p1 VALUES LESS THAN (MAXVALUE)
);

各行の a のカラム値が 5 である 3 つの行をこのテーブルに挿入する場合、各行の a カラム値が 5 以上であるため、3 行がすべてパーティション p1 に格納されます。これは、INFORMATION_SCHEMA.PARTITIONS テーブルに対して適切なクエリーを実行することによって確認できます。

mysql> INSERT INTO r1 VALUES (5,10), (5,11), (5,12);Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT PARTITION_NAME,TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'r1';+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 3 |
+----------------+------------+
2 rows in set (0.00 sec)

ここで、次のように作成される、カラム a および b の両方が COLUMNS 句で参照される、RANGE COLUMNS パーティショニングを使用する同様のテーブル rc1 を検討します。

CREATE TABLE rc1 ( a INT, b INT
)
PARTITION BY RANGE COLUMNS(a, b) ( PARTITION p0 VALUES LESS THAN (5, 12), PARTITION p3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

r1 に挿入したのとまったく同じ行を rc1 に挿入した場合、行の配分はかなり異なります。

mysql> INSERT INTO rc1 VALUES (5,10), (5,11), (5,12);Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT PARTITION_NAME,TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'rc1';+--------------+----------------+------------+
| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
+--------------+----------------+------------+
| p | p0 | 2 |
| p | p1 | 1 |
+--------------+----------------+------------+
2 rows in set (0.00 sec)

これは、スカラー値ではなく行を比較しているためです。挿入された行値と、テーブル rc1 のパーティション p0 を定義するために使用された VALUES THAN LESS THAN 句の行制限値とを次のように比較できます。

mysql> SELECT (5,10) < (5,12), (5,11) < (5,12), (5,12) < (5,12);+-----------------+-----------------+-----------------+
| (5,10) < (5,12) | (5,11) < (5,12) | (5,12) < (5,12) |
+-----------------+-----------------+-----------------+
| 1 | 1 | 0 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

2 つのタプル (5,10) および (5,11)(5,12) より小さいと評価されるため、パーティション p0 に格納されています。5 は 5 以上、12 は 12 以上であるため、(5,12)(5,12) 以上と見なされ、パーティション p1 に格納されています。

前の例の SELECT ステートメントは、次のように明示的な行コンストラクタを使用して記述することもできました。

SELECT ROW(5,10) < ROW(5,12), ROW(5,11) < ROW(5,12), ROW(5,12) < ROW(5,12);

MySQL で行コンストラクタを使用する方法についての詳細は、セクション13.2.10.5「行サブクエリー」を参照してください。

単一パーティショニングカラムのみを使用して RANGE COLUMNS によってパーティション化されたテーブルの場合、パーティションへの行の格納は RANGE によってパーティション化された同等のテーブルの場合と同じです。次の CREATE TABLE ステートメントでは、1 つのパーティショニングカラムを使用して RANGE COLUMNS によってパーティション化されるテーブルが作成されます。

CREATE TABLE rx ( a INT, b INT
)
PARTITION BY RANGE COLUMNS (a) ( PARTITION p0 VALUES LESS THAN (5), PARTITION p1 VALUES LESS THAN (MAXVALUE)
); 

このテーブルに行 (5,10)(5,11)、および (5,12) を挿入する場合、それらの配置は、前に作成して移入したテーブル r の場合と同じです。

mysql> INSERT INTO rx VALUES (5,10), (5,11), (5,12);Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT PARTITION_NAME,TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'rx';+--------------+----------------+------------+
| TABLE_SCHEMA | PARTITION_NAME | TABLE_ROWS |
+--------------+----------------+------------+
| p | p0 | 0 |
| p | p1 | 3 |
+--------------+----------------+------------+
2 rows in set (0.00 sec)

1 つ以上のカラムの制限値が連続するパーティション定義で繰り返される、RANGE COLUMNS によってパーティション化されるテーブルを作成することもできます。これを行うには、パーティションを定義するために使用されるカラム値のタプルが厳密にしだいに増加する必要があります。たとえば、次の各 CREATE TABLE ステートメントは有効です。

CREATE TABLE rc2 ( a INT, b INT
)
PARTITION BY RANGE COLUMNS(a,b) ( PARTITION p0 VALUES LESS THAN (0,10), PARTITION p1 VALUES LESS THAN (10,20), PARTITION p2 VALUES LESS THAN (10,30), PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE) );
CREATE TABLE rc3 ( a INT, b INT
)
PARTITION BY RANGE COLUMNS(a,b) ( PARTITION p0 VALUES LESS THAN (0,10), PARTITION p1 VALUES LESS THAN (10,20), PARTITION p2 VALUES LESS THAN (10,30), PARTITION p3 VALUES LESS THAN (10,35), PARTITION p4 VALUES LESS THAN (20,40), PARTITION p5 VALUES LESS THAN (MAXVALUE,MAXVALUE) );

次のステートメントも成功しますが、カラム b の制限値がパーティション p0 で 25 およびパーティション p1 で 20、カラム c の制限値がパーティション p1 で 100 およびパーティション p2 で 50 であるため、一見したところではそうでないように見えるかもしれません。

CREATE TABLE rc4 ( a INT, b INT, c INT
)
PARTITION BY RANGE COLUMNS(a,b,c) ( PARTITION p0 VALUES LESS THAN (0,25,50), PARTITION p1 VALUES LESS THAN (10,20,100), PARTITION p2 VALUES LESS THAN (10,30,50) PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) );

RANGE COLUMNS によってパーティション化されるテーブルを設計するときは、次のように mysql クライアントを使用して目的のタプルを比較することで、いつでも連続するパーティション定義をテストできます。

mysql> SELECT (0,25,50) < (10,20,100), (10,20,100) < (10,30,50);+-------------------------+--------------------------+
| (0,25,50) < (10,20,100) | (10,20,100) < (10,30,50) |
+-------------------------+--------------------------+
| 1 | 1 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

CREATE TABLE ステートメントに含まれるパーティション定義が、厳密にしだいに増加しない順序である場合は、次の例に示すようにエラーで失敗します。

mysql> CREATE TABLE rcf ( -> a INT, -> b INT, -> c INT -> ) -> PARTITION BY RANGE COLUMNS(a,b,c) ( -> PARTITION p0 VALUES LESS THAN (0,25,50), -> PARTITION p1 VALUES LESS THAN (20,20,100), -> PARTITION p2 VALUES LESS THAN (10,30,50), -> PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE) -> );ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

そのようなエラーが発生するときは、それらのカラムリストの小なり比較を作成することで、無効なパーティション定義を推定できます。この場合、問題はパーティション p2 の定義にあります。次に示すように、それを定義するために使用されるタプルが、パーティション p3 を定義するために使用されるタプル以上であるためです。

mysql> SELECT (0,25,50) < (20,20,100), (20,20,100) < (10,30,50);+-------------------------+--------------------------+
| (0,25,50) < (20,20,100) | (20,20,100) < (10,30,50) |
+-------------------------+--------------------------+
| 1 | 0 |
+-------------------------+--------------------------+
1 row in set (0.00 sec)

RANGE COLUMNS を使用するときは、複数の VALUES LESS THAN 句内の同じカラムに MAXVALUE を指定することもできます。ただし、それ以外の場合は、連続するパーティション定義内の個々のカラムの制限値はしだいに増加するべきであり、MAXVALUE をすべてのカラム値の上限として使用するパーティションは 1 つだけ定義するべきであり、このパーティション定義は PARTITION ... VALUES LESS THAN 句のリストの最後に指定するべきです。また、複数のパーティション定義の最初のカラムの制限値として MAXVALUE を使用することはできません。

前述したように、RANGE COLUMNS パーティショニングでは、整数以外のカラムをパーティショニングカラムとして使用することもできます(これらの完全な一覧については、セクション19.2.3「COLUMNS パーティショニング」を参照してください)。次のステートメントを使用して作成された employees という名前のテーブル (パーティション化されていません) を検討します。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL
);

RANGE COLUMNS パーティショニングを使用して、次のように従業員の姓に基づいて各行を 4 つのパーティションのいずれかに格納する、このテーブルのバージョンを作成できます。

CREATE TABLE employees_by_lname ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL
)
PARTITION BY RANGE COLUMNS (lname) ( PARTITION p0 VALUES LESS THAN ('g'), PARTITION p1 VALUES LESS THAN ('m'), PARTITION p2 VALUES LESS THAN ('t'), PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

または、次の ALTER TABLE ステートメントを実行することで、前に作成した employees テーブルをこのスキームを使用してパーティション化できます。

ALTER TABLE employees PARTITION BY RANGE COLUMNS (lname) ( PARTITION p0 VALUES LESS THAN ('g'), PARTITION p1 VALUES LESS THAN ('m'), PARTITION p2 VALUES LESS THAN ('t'), PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
注記

文字セットおよび照合順序が異なるとソート順序が異なるため、文字列カラムをパーティショニングカラムとして使用するときに、使用している文字セットおよび照合順序が、指定された行が RANGE COLUMNS によってパーティション化されるテーブルのどのパーティションに格納されるかに影響することがあります。また、そのようなテーブルが作成されたあとに指定されたデータベース、テーブル、またはカラムの文字セットまたは照合順序を変更すると、行がどのように配分されるかが変わることがあります。たとえば、大文字/小文字が区別される照合順序を使用するときは、'and''Andersen' の前に置かれますが、大文字/小文字が区別されない照合順序を使用するときは、その逆が true になります。

MySQL が文字セットおよび照合順序をどのように扱うかについては、セクション10.1「文字セットのサポート」を参照してください。

同様に、employees テーブルを、各行がいくつかの 10 年間 (その間に対応する従業員が雇用された) ベースのパーティションのいずれかに格納されるように、次のような ALTER TABLE ステートメントを使用してパーティション化できます。

ALTER TABLE employees PARTITION BY RANGE COLUMNS (hired) ( PARTITION p0 VALUES LESS THAN ('1970-01-01'), PARTITION p1 VALUES LESS THAN ('1980-01-01'), PARTITION p2 VALUES LESS THAN ('1990-01-01'), PARTITION p3 VALUES LESS THAN ('2000-01-01'), PARTITION p4 VALUES LESS THAN ('2010-01-01'), PARTITION p5 VALUES LESS THAN (MAXVALUE)
);

PARTITION BY RANGE COLUMNS 構文の詳細については、セクション13.1.17「CREATE TABLE 構文」を参照してください。

19.2.3.2 LIST COLUMNS パーティショニング

MySQL 5.6 は LIST COLUMNS パーティショニングのサポートを提供します。これは LIST パーティショニングのバリアントで、複数のカラムをパーティションキーとして使用でき、整数型以外のデータ型のカラムをパーティショニングカラムとして使用できます。文字列型、DATE、および DATETIME カラムを使用できます(COLUMNS パーティショニングカラムに許可されるデータ型の詳細については、セクション19.2.3「COLUMNS パーティショニング」を参照してください)。

ある会社の顧客が 12 の都市に存在し、販売およびマーケティングのために、それらを次の表に示すように 3 つの都市で構成される 4 つの地域に分類すると想定します。

地域都市
1Oskarshamn、Högsby、Mönsterås
2Vimmerby、Hultsfred、Västervik
3Nässjö、Eksjö、Vetlanda
4Uppvidinge、Alvesta、Växjo

LIST COLUMNS パーティショニングでは、ここで示すように、顧客が所在する都市の名前に基づいてこれらの地域に対応する 4 つのパーティションのいずれかに行を割り当てる、顧客データのテーブルを作成できます。

CREATE TABLE customers_1 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE
)
PARTITION BY LIST COLUMNS(city) ( PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'), PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'), PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'), PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);

RANGE COLUMNS によるパーティショニングのように、COLUMNS() 句で式を使用してカラム値を整数に変換する必要はありません(実際、カラム名ではなく式を使用することは COLUMNS() では許可されません)。

DATE および DATETIME カラムを使用することもでき、次の例では、前に示した customers_1 テーブルと同じ名前およびカラムを使用していますが、renewal カラムに基づく LIST COLUMNS パーティショニングを使用して、顧客のアカウントの更新がスケジュールされている 2010 年 2 月の週に応じて、4 つのパーティションのいずれかに行が格納されることを示しています。

CREATE TABLE customers_2 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE
)
PARTITION BY LIST COLUMNS(renewal) ( PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03', '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'), PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10', '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'), PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17', '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'), PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24', '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);

これは機能しますが、関係する日付の数が非常に多くなってきた場合に、定義および保守が面倒になります。そのような場合は通常、RANGE または RANGE COLUMNS パーティショニングを代わりに使用するほうが現実的です。この場合、パーティショニングキーとして使用するカラムは DATE カラムであるため、次に示すように RANGE COLUMNS パーティショニングを使用します。

CREATE TABLE customers_3 ( first_name VARCHAR(25), last_name VARCHAR(25), street_1 VARCHAR(30), street_2 VARCHAR(30), city VARCHAR(15), renewal DATE
)
PARTITION BY RANGE COLUMNS(renewal) ( PARTITION pWeek_1 VALUES LESS THAN('2010-02-09'), PARTITION pWeek_2 VALUES LESS THAN('2010-02-15'), PARTITION pWeek_3 VALUES LESS THAN('2010-02-22'), PARTITION pWeek_4 VALUES LESS THAN('2010-03-01')
);

詳細は、セクション19.2.3.1「RANGE COLUMNS パーティショニング」を参照してください。

また (RANGE COLUMNS パーティショニングと同様に)、COLUMNS() 句で複数のカラムを使用できます。

PARTITION BY LIST COLUMNS() 構文についての詳細は、セクション13.1.17「CREATE TABLE 構文」を参照してください。

19.2.4 HASH パーティショニング

HASH によるパーティショニングは、事前に決められた数のパーティションにデータを均等に配分するために主に使用されます。RANGE または LIST パーティショニングでは、指定されたカラム値またはカラム値セットがどのパーティションに格納されるかを明示的に指定する必要があります。HASH パーティショニングでは MySQL がこれを自動的に行うため、必要なことは、ハッシュされるカラム値またはカラム値に基づく式、およびパーティション化されたテーブルがいくつのパーティションに分割されるかを指定することだけです。

HASH パーティショニングを使用してテーブルをパーティション化する場合は、CREATE TABLE ステートメントに PARTITION BY HASH (expr) 句を付加する必要があります。ここで、expr は整数を返す式です。これには、型が MySQL の整数型のいずれかであるカラムの名前を単純に指定できます。また、これのあとにはほとんどの場合 PARTITIONS num 句を続けます。ここで、num はテーブルがいくつのパーティションに分割されるかを表す正の整数です。

たとえば、次のステートメントは store_id カラムにハッシュを使用し、4 つのパーティションに分割されたテーブルを作成します。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

PARTITIONS 句を含めない場合、パーティションの数はデフォルトで 1 となります。

PARTITIONS キーワードを使用する場合、そのあとに数を指定しないと構文エラーになります。

整数を返す SQL 式を expr に使用することもできます。たとえば、従業員が雇用された年度に基づいてパーティション化するとします。これは、次のように行うことができます。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

expr は、定数以外のランダムではない整数値 (つまり、変化するけれども決定論的であるべき) を返す必要があり、セクション19.6「パーティショニングの制約と制限」で説明されている禁止された構造体を含んでいてはいけません。また、この式は行が挿入または更新 (または場合によっては削除) されるたびに評価されるべきです。これは、非常に複雑な式がパフォーマンスの問題を起こすことがあることを意味します (特に、一度に多くの行に影響する操作 (バッチ挿入など) を実行するとき)。

もっとも効率的なハッシュ関数は、単一テーブルカラムに実行され、その値がカラム値に対して比例的に増加または減少するもので、これによってパーティションの範囲をプルーニングできます。つまり、式がそのベースのカラムの値に対してより密接に変化するほど、MySQL は式を HASH パーティショニングにより効率的に使用できます。

たとえば、date_colDATE 型のカラムである場合、式 TO_DAYS(date_col)date_col の値に正比例すると表現されます。date_col の値が変わるたびに、式の値が一定の方法で変化するためです。date_col に対する式 YEAR(date_col) の変化は、TO_DAYS(date_col) ほど比例的ではありません。date_col のあらゆる変化に対して YEAR(date_col) が同等に変化するとはかぎらないためです。それでも、YEAR(date_col) はハッシュ関数の良い候補の 1 つです。date_col の一部と正比例し、date_col の変化によって YEAR(date_col) で比例的でない変化が発生することがないためです。

比較のために、型が INT である int_col という名前のカラムがあるとします。式 POW(5-int_col,3) + 6 を検討してみてください。これは、int_col の値が変化したときに、式の値に比例的に変化することが保証されないため、ハッシュ関数の良い候選択肢ではありません。int_col の値が一定量で変化したときに、式の値の変化量が大きくなる可能性があります。たとえば、int_col5 から 6 に変化すると、式の値が -1 に変化しますが、int_col の値が 6 から 7 に変化すると、式の値が -7 に変化します。

つまり、カラム値と式の値のグラフが、等式 y=cx (ここで、c はゼロでない何らかの定数) によって描かれるような直線に近くなるほど、その式はハッシュにより適切になります。これは、式が非直線的であるほど、パーティションに対するデータの配分が不均衡になる傾向があることに関係しています。

理論上は、複数のカラム値を使用する式をプルーニングすることもできますが、そのような式のどれが適しているかを判断するのがかなり難しく、時間がかかることがあります。このため、複数のカラムを含むハッシュ式を使用することはあまり推奨されていません。

PARTITION BY HASH が使用された場合、MySQL はユーザー関数の結果の法に基づいて、num パーティションのうちのどのパーティションを使用するかを判断します。つまり、式 expr の場合、レコードが格納されるパーティションは、パーティション番号 N です (ここで、N = MOD(expr, num))。テーブル t1 が次のように 4 つのパーティションを持つように定義されているとします。

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4;

t1col3 値が '2005-09-15' であるレコードを挿入した場合、それが格納されるパーティションは次のように判断されます。

MOD(YEAR('2005-09-01'),4)
= MOD(2005,4)
= 1

MySQL 5.6 は、パーティション化されたテーブルに挿入される新しい行の配置を判断するために、より複雑なアルゴリズムを使用する線形ハッシュと呼ばれる、HASH パーティショニングのバリアントもサポートします。このアルゴリズムについては、セクション19.2.4.1「LINEAR HASH パーティショニング」を参照してください。

ユーザー関数は、レコードが挿入または更新されるたびに評価されます。状況によっては、レコードが削除されるときにも評価されることがあります。

注記

パーティション化されるテーブルに UNIQUE キーがある場合、HASH ユーザー関数または KEYcolumn_list に引数として指定するカラムは、そのキーの一部である必要があります。

19.2.4.1 LINEAR HASH パーティショニング

MySQL は線形ハッシュもサポートしています。通常のハッシュと異なるところは、線形ハッシュは線形二乗アルゴリズムを使用し、通常のハッシュはハッシュ関数の値の法を使用することです。

構文的には、リニアハッシュパーティショニングと通常のハッシュの唯一の違いは、次に示すように、PARTITION BY 句に LINEAR キーワードが追加されていることです。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT
)
PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

expr の場合、線形ハッシュが使用されるときにレコードが格納されるパーティションは、num パーティションのうちのバーティション番号 N です。ここで、N は次のアルゴリズムに従って導出されます。

  1. num よりも大きい次の 2 の累乗を見つけます。この値を V と呼ぶことにします。これは次のように計算できます。

    V = POWER(2, CEILING(LOG(2, num)))

    (num が 13 であるとします。その場合、LOG(2,13) は 3.7004397181411 です。CEILING(3.7004397181411) は 4、V = POWER(2,4) は 16 です。)

  2. N = F(column_list) & (V - 1) を設定します。

  3. N >= num の間:

    • V = CEIL(V / 2) を設定します

    • N = N & (V - 1) を設定します

線形ハッシュパーティショニングを使用し、6 個のパーティションを持つテーブル t1 を次のステートメントを使用して作成するとします。

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR HASH( YEAR(col3) ) PARTITIONS 6;

col3 カラムの値が '2003-04-14' および '1998-10-19' である 2 つのレコードを t1 に挿入するとします。これらの 1 番目のパーティション番号は次のように決定されます。

V = POWER(2, CEILING( LOG(2,6) )) = 8N = YEAR('2003-04-14') & (8 - 1) = 2003 & 7 = 3
(3 >= 6 is FALSE: record stored in partition #3)

2 番目のレコードが格納されるパーティションの番号は、次のように計算されます。

V = 8N = YEAR('1998-10-19') & (8-1) = 1998 & 7 = 6
(6 >= 6 is TRUE: additional step required)N = 6 & CEILING(8 / 2) = 6 & 3 = 2
(2 >= 6 is FALSE: record stored in partition #2)

線形ハッシュによるパーティショニングの利点は、パーティションの追加、削除、マージ、および分割の速度が向上することです。これは、非常に大量 (テラバイト) のデータが含まれるテーブルを扱うときに利点になることがあります。欠点は、通常のハッシュパーティショニングを使用して獲得される配分と比べて、データがパーティションに均等に配分される可能性が低いことです。

19.2.5 KEY パーティショニング

キーによるパーティショニングはハッシュによるパーティショニングと似ていますが、ハッシュパーティショニングはユーザー定義の式を使用し、キーパーティショニング用のハッシュ関数は MySQL サーバーによって提供されます。MySQL Cluster はこのために MD5() を使用します。ほかのストレージエンジンを使用するテーブルの場合、サーバーは PASSWORD() と同じアルゴリズムに基づく独自の内部ハッシュ関数を使用します。

CREATE TABLE ... PARTITION BY KEY の構文規則は、ハッシュによってパーティション化されたテーブルを作成する場合のものと似ています。主な違いを次に示します。

  • HASH ではなく KEY が使用されます。

  • KEY は、0 個以上のカラム名のリストのみを取ります。パーティショニングキーとして使用されるカラムは、テーブルの主キーの一部またはすべてを構成している必要があります (テーブルにそれがある場合)。パーティショニングキーとしてカラム名を指定しない場合は、テーブルの主キーが使用されます (ある場合)。たとえば、次の CREATE TABLE ステートメントは MySQL 5.6 で有効です。

    CREATE TABLE k1 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20)
    )
    PARTITION BY KEY()
    PARTITIONS 2;

    主キーはないけれども一意キーはある場合は、パーティショニングキーに一意キーが使用されます。

    CREATE TABLE k1 ( id INT NOT NULL, name VARCHAR(20), UNIQUE KEY (id)
    )
    PARTITION BY KEY()
    PARTITIONS 2;

    ただし、一意キーカラムが NOT NULL として定義されていない場合、前のステートメントは失敗します。

    どちらの場合も、パーティショニングキーは id カラムです。ただし、SHOW CREATE TABLE の出力や INFORMATION_SCHEMA.PARTITIONS テーブルの PARTITION_EXPRESSION カラムには表示されません。

    ほかのパーティショニングタイプの場合と異なり、KEY によるパーティショニングに使用されるカラムは、整数または NULL 値に制限されません。たとえば、次の CREATE TABLE ステートメントは有効です。

    CREATE TABLE tm1 ( s1 CHAR(32) PRIMARY KEY
    )
    PARTITION BY KEY(s1)
    PARTITIONS 10;

    ほかのパーティショニングタイプが指定された場合、前のステートメントは有効でなくなります(この場合、s1 はテーブルの主キーであるため、単純に PARTITION BY KEY() を使用することも有効であり、PARTITION BY KEY(s1) と同じ効果があります)。

    この問題の詳細については、セクション19.6「パーティショニングの制約と制限」を参照してください。

    注記

    NDB ストレージエンジンを使用するテーブルは、テーブルの主キーをパーティショニングキーとして使用して、KEY によって暗黙的にパーティション化されます。MySQL Cluster テーブルに明示的な主キーがない場合は、NDB ストレージエンジンによって各 MySQL Cluster テーブルに生成される隠し主キーが、パーティショニングキーとして使用されます。

    NDB テーブルに明示的なパーティショニングスキームを定義する場合は、テーブルに明示的な主キーが必要であり、パーティショニング式に使用されるカラムがこのキーの一部である必要があります。ただし、テーブルがのパーティショニング式を使用する (つまり、カラム参照なしの PARTITION BY KEY()) 場合、明示的な主キーは必要ありません。

    このパーティショニングは、ndb_desc ユーティリティー (-p オプション付き) を使用して確認できます。

    重要

    キーによってパーティション化されたテーブルの場合は、ALTER TABLE DROP PRIMARY KEY を実行できません。それを実行すると次のエラーが生成されます: ERROR 1466 (HY000): Field in list of fields for partition function not found in table。これは、KEY によってパーティション化された MySQL Cluster テーブルの場合は問題ではありません。そのような場合は、隠し主キーをテーブルの新しいパーティショニングキーとして使用してテーブルが再編成されます。第18章「MySQL Cluster NDB 7.3 および MySQL Cluster NDB 7.4を参照してください。

リニアキーによってテーブルをパーティション化することもできます。次に、単純な例を示します。

CREATE TABLE tk ( col1 INT NOT NULL, col2 CHAR(5), col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

LINEAR を使用することは、HASH パーティショニングの場合と同じ効果が KEY パーティショニングにあり、パーティショニング番号は法演算ではなく二乗アルゴリズムを使用して導出されます。このアルゴリズムの説明およびその影響については、セクション19.2.4.1「LINEAR HASH パーティショニング」を参照してください。

19.2.6 サブパーティショニング

サブパーティショニング (複合パーティショニングとも呼ばれます) は、パーティション化されたテーブルの各パーティションをさらに分割することです。次の CREATE TABLE ステートメントを検討します。

CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) SUBPARTITIONS 2 ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE );

テーブル ts には 3 つの RANGE パーティションがあります。これらの各パーティション (p0p1、および p2) は、さらに 2 つのサブパーティションに分割されます。実際には、テーブル全体が 3 * 2 = 6 パーティションに分割されます。ただし、PARTITION BY RANGE 句のアクションによって、これらの最初の 2 つには purchased カラムで値が 1990 より小さいレコードのみが格納されます。

MySQL 5.6 では、RANGE または LIST によってパーティション化されたテーブルをサブパーティション化できます。サブパーティショニングには、HASH または KEY パーティショニングを使用できます。これは、複合パーティショニングとも呼ばれます。

注記

SUBPARTITION BY HASH および SUBPARTITION BY KEY は通常それぞれ、PARTITION BY HASH および PARTITION BY KEY と同じ構文規則に従います。これの例外は、SUBPARTITION BY KEY は現在 (PARTITION BY KEY と異なり) デフォルトカラムをサポートしないことで、この目的に使用されるカラムを指定する必要があります (テーブルに明示的な主キーがある場合でも)。これは既知の問題であり、対処中です。詳細および例については サブパーティションに関する問題を参照してください。

SUBPARTITION 句を使用して個々のサブパーティションのオプションを指定することによって、サブパーティションを明示的に定義することもできます。たとえば、前の例と同じテーブル ts をより冗長な形式で作成するには、次のようにします。

CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4, SUBPARTITION s5 ) );

構文に関するいくつかの注意事項を次に示します。

  • 各パーティションには、同じ数のサブパーティションが必要です。

  • パーティション化されたテーブルのパーティションに SUBPARTITION を使用してサブパーティションを明示的に定義する場合は、それらのすべてを定義する必要があります。言い換えると、次のステートメントは失敗します。

    CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2, SUBPARTITION s3 ) );

    このステートメントは、SUBPARTITIONS 2 句を含んでいた場合でも失敗します。

  • SUBPARTITION 句には、(少なくとも) サブパーティションの名前が含まれている必要があります。それ以外は、サブパーティションに適切なオプションを設定するか、またはそのオプションのデフォルト設定を想定します。

  • サブパーティション名はテーブル全体で一意である必要があります。たとえば、次の CREATE TABLE ステートメントは MySQL 5.6 で有効です。

    CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0, SUBPARTITION s1 ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2, SUBPARTITION s3 ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4, SUBPARTITION s5 ) );

サブパーティションは、非常に大きいテーブルで、データおよびインデックスを多数のディスクに分散するために使用できます。/disk0/disk1/disk2 などとしてマウントされた 6 個のディスクがあるとします。ここで次の例を検討します。

CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0 DATA DIRECTORY = '/disk0/data' INDEX DIRECTORY = '/disk0/idx', SUBPARTITION s1 DATA DIRECTORY = '/disk1/data' INDEX DIRECTORY = '/disk1/idx' ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s2 DATA DIRECTORY = '/disk2/data' INDEX DIRECTORY = '/disk2/idx', SUBPARTITION s3 DATA DIRECTORY = '/disk3/data' INDEX DIRECTORY = '/disk3/idx' ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s4 DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx', SUBPARTITION s5 DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx' ) );

この場合、各 RANGE のデータおよびインデックスに個別のディスクが使用されます。ほかにも多数のバリエーションが考えられます。別の例を次に示します。

CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE(YEAR(purchased)) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ( SUBPARTITION s0a DATA DIRECTORY = '/disk0' INDEX DIRECTORY = '/disk1', SUBPARTITION s0b DATA DIRECTORY = '/disk2' INDEX DIRECTORY = '/disk3' ), PARTITION p1 VALUES LESS THAN (2000) ( SUBPARTITION s1a DATA DIRECTORY = '/disk4/data' INDEX DIRECTORY = '/disk4/idx', SUBPARTITION s1b DATA DIRECTORY = '/disk5/data' INDEX DIRECTORY = '/disk5/idx' ), PARTITION p2 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2a, SUBPARTITION s2b ) );

この場合は、次のように格納されます。

  • purchased 日付が 1990 年より前の行には大きな領域が使用されるため、4 つに分割され、パーティション p0 を構成する 2 つの各サブパーティション (s0a および s0b) のデータおよびインデックスに個別のディスクが専用に割り当てられます。言い換えると、次のようになります。

    • サブパーティション s0a のデータは /disk0 に格納されます。

    • サブパーティション s0a のインデックスは、/disk1 に格納されます。

    • サブパーティション s0b のデータは、/disk2 に格納されます。

    • サブパーティション s0b のインデックスは、/disk3 に格納されます。

  • 1990 年から 1999 年までの日付 (パーティション p1) が含まれている行は、1990 年より前の行ほどの領域を必要としません。これらは、p0 に格納されるレガシーレコードの場合の 4 つのディスクではなく、2 つのディスク (/disk4 および /disk5) に分割されます。

    • p1 の最初のサブパーティション (s1a) に属するデータおよびインデックスは、/disk4 (データは /disk4/data、およびインデックスは /disk4/idx) に格納されます。

    • p1 の 2 番目のサブパーティション (s1b) に属するデータおよびインデックスは、/disk5 (データは /disk5/data、およびインデックスは /disk5/idx) に格納されます。

  • 2000 年から現在までの日付 (パーティション p2) を反映する行には、前の 2 つの範囲で必要とされたほどの領域は使用されません。現在のところ、これらのすべてをデフォルトの場所に格納しても問題ありません。

    将来、2000 年から始まる 10 年間の購入数が、デフォルトの場所では十分な領域を提供できないほど増えたときには、ALTER TABLE ... REORGANIZE PARTITION ステートメントを使用して対応する行を移動できます。これを行う方法については、セクション19.3「パーティション管理」を参照してください。

NO_DIR_IN_CREATE サーバー SQL モードが有効である場合、DATA DIRECTORY および INDEX DIRECTORY オプションはパーティション定義で許可されません。MySQL 5.6 では、これらのオプションはサブパーティションを定義するときにも許可されません (Bug #42954)。

19.2.7 MySQL パーティショニングによる NULL の扱い

MySQL のパーティショニングには、パーティショニング式の値 (カラム値またはユーザー定義式の値にかかわらず) として NULL を拒否する手段はありません。式の値として NULL を使用することは許可されていますが (そうでない場合は整数を返す必要がある)、NULL は数値でないことを認識することは重要です。MySQL のパーティショニング実装は、ORDER BY のように、NULL でない値より小さい値として NULL を扱います。

これは、NULL の扱いは各タイプのパーティショニングで異なり、これに準備していない場合は予期しない動作になる可能性があることを意味します。このような状況があるので、このセクションでは、各 MySQL パーティショニングタイプが、行をどのパーティションに格納するべきかを判断するときに NULL 値をどのように扱うかを説明し、それぞれの例を示します。

RANGE パーティショニングでの NULL の扱い  パーティションを判断するために使用されるカラム値が NULL である行を、RANGE によってパーティション化されたテーブルに挿入した場合、行はもっとも低いパーティションに挿入されます。p という名前のデータベースに、次のように作成された 2 つのテーブルがあるとします。

mysql> CREATE TABLE t1 ( -> c1 INT, -> c2 VARCHAR(20) -> ) -> PARTITION BY RANGE(c1) ( -> PARTITION p0 VALUES LESS THAN (0), -> PARTITION p1 VALUES LESS THAN (10), -> PARTITION p2 VALUES LESS THAN MAXVALUE -> );Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE t2 ( -> c1 INT, -> c2 VARCHAR(20) -> ) -> PARTITION BY RANGE(c1) ( -> PARTITION p0 VALUES LESS THAN (-5), -> PARTITION p1 VALUES LESS THAN (0), -> PARTITION p2 VALUES LESS THAN (10), -> PARTITION p3 VALUES LESS THAN MAXVALUE -> );Query OK, 0 rows affected (0.09 sec)

これらの 2 つの CREATE TABLE ステートメントによって作成されたパーティションについては、次のクエリーを INFORMATION_SCHEMA データベース内の PARTITIONS テーブルに対して使用することで確認できます。

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| t1 | p0 | 0 | 0 | 0 |
| t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 |
| t2 | p0 | 0 | 0 | 0 |
| t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.00 sec)

(このテーブルについての詳細は、セクション21.13「INFORMATION_SCHEMA PARTITIONS テーブル」を参照してください。)ここで、これらの各テーブルのパーティショニングキーとして使用されるカラムに NULL が含まれる単一行を移入し、2 つの SELECT ステートメントを使用してこれらの行が挿入されたことを確認します。

mysql> INSERT INTO t1 VALUES (NULL, 'mothra');Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO t2 VALUES (NULL, 'mothra');Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM t1;+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t2;+------+--------+
| id | name |
+------+--------+
| NULL | mothra |
+------+--------+
1 row in set (0.00 sec)

挿入された行を格納するためにどのパーティションが使用されたかについては、前のクエリーを INFORMATION_SCHEMA.PARTITIONS に対して再実行して出力を検査することで確認できます。

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 't_';+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+| t1 | p0 | 1 | 20 | 20 || t1 | p1 | 0 | 0 | 0 |
| t1 | p2 | 0 | 0 | 0 || t2 | p0 | 1 | 20 | 20 || t2 | p1 | 0 | 0 | 0 |
| t2 | p2 | 0 | 0 | 0 |
| t2 | p3 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)

これらの行が各テーブルのもっとも低いパーティションに格納されたことについては、これらのパーティションを削除してから SELECT ステートメントを再実行することで確認できます。

mysql> ALTER TABLE t1 DROP PARTITION p0;Query OK, 0 rows affected (0.16 sec)
mysql> ALTER TABLE t2 DROP PARTITION p0;Query OK, 0 rows affected (0.16 sec)
mysql> SELECT * FROM t1;Empty set (0.00 sec)
mysql> SELECT * FROM t2;Empty set (0.00 sec)

(ALTER TABLE ... DROP PARTITION の詳細については、セクション13.1.7「ALTER TABLE 構文」を参照してください。)

SQL 関数を使用するパーティショニング式の場合も、NULL はこのように扱われます。次のような CREATE TABLE ステートメントを使用してテーブルを定義するとします。

CREATE TABLE tndate ( id INT, dt DATE
)
PARTITION BY RANGE( YEAR(dt) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE
);

ほかの MySQL 関数と同様に、YEAR(NULL)NULL を返します。dt カラム値が NULL である行は、パーティショニング式がほかの値より小さい値に評価されたかのように扱われ、パーティション p0 に挿入されます。

LIST パーティショニングでの NULL の扱い LIST によってパーティション化されたテーブルで NULL 値が許可されるのは、NULL が含まれている値リストを使用していずれかのパーティションが定義されている場合のみです。これとは逆に、LIST によってパーティション化されたテーブルが、値リストで NULL を明示的に使用していない場合は、次の例のようにパーティショニング式で NULL 値に評価される行を拒否します。

mysql> CREATE TABLE ts1 ( -> c1 INT, -> c2 VARCHAR(20) -> ) -> PARTITION BY LIST(c1) ( -> PARTITION p0 VALUES IN (0, 3, 6), -> PARTITION p1 VALUES IN (1, 4, 7), -> PARTITION p2 VALUES IN (2, 5, 8) -> );Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO ts1 VALUES (9, 'mothra');ERROR 1504 (HY000): Table has no partition for value 9mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');ERROR 1504 (HY000): Table has no partition for value NULL

ts1 に挿入できるのは、c1 値が 0 以上 8 以下の行のみです。NULL は、数値 9 と同様にこの範囲を外れます。NULL が含まれる値リストを持つテーブル ts2 およびts3 は次のように作成できます。

mysql> CREATE TABLE ts2 ( -> c1 INT, -> c2 VARCHAR(20) -> ) -> PARTITION BY LIST(c1) ( -> PARTITION p0 VALUES IN (0, 3, 6), -> PARTITION p1 VALUES IN (1, 4, 7), -> PARTITION p2 VALUES IN (2, 5, 8), -> PARTITION p3 VALUES IN (NULL) -> );
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE ts3 ( -> c1 INT, -> c2 VARCHAR(20) -> ) -> PARTITION BY LIST(c1) ( -> PARTITION p0 VALUES IN (0, 3, 6), -> PARTITION p1 VALUES IN (1, 4, 7, NULL), -> PARTITION p2 VALUES IN (2, 5, 8) -> );
Query OK, 0 rows affected (0.01 sec)

パーティショニングの値リストを定義するときに、NULL をほかの値と同様に扱うことができます (そうすべきです)。たとえば、VALUES IN (NULL) および VALUES IN (1, 4, 7, NULL) は両方とも有効であり、VALUES IN (1, NULL, 4, 7)VALUES IN (NULL, 1, 4, 7) なども同様です。カラム c1NULL である行をテーブル ts2 および ts3 にそれぞれ挿入できます。

mysql> INSERT INTO ts2 VALUES (NULL, 'mothra');Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO ts3 VALUES (NULL, 'mothra');Query OK, 1 row affected (0.00 sec)

INFORMATION_SCHEMA.PARTITIONS に対して適切なクエリーを発行することによって、先ほど挿入した行を格納するためにどのパーティションが使用されたかを確認できます (前の例と同様に、パーティション化されたテーブルが p データベースに作成されたことを想定しています)。

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'ts_';+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| ts2 | p0 | 0 | 0 | 0 |
| ts2 | p1 | 0 | 0 | 0 |
| ts2 | p2 | 0 | 0 | 0 || ts2 | p3 | 1 | 20 | 20 || ts3 | p0 | 0 | 0 | 0 || ts3 | p1 | 1 | 20 | 20 || ts3 | p2 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
7 rows in set (0.01 sec)

このセクションですでに示したように、行を格納するためにどのパーティションが使用されたかについては、それらのパーティションを削除してから SELECT を実行することで確認できます。

HASH および KEY パーティショニングでの NULL の扱い HASH または KEY によってパーティション化されたテーブルの場合、NULL の扱いは少し異なります。これらの場合、NULL 値を返すパーティショニング式は、戻り値がゼロであったかのように扱われます。この動作については、HASH によってパーティション化されたテーブルを作成して該当する値が含まれるレコードを挿入することで、ファイルシステムにどのような影響があるかを検査することで確認できます。次のステートメントを使用して作成されたテーブル th (これも p データベース内) があるとします。

mysql> CREATE TABLE th ( -> c1 INT, -> c2 VARCHAR(20) -> ) -> PARTITION BY HASH(c1) -> PARTITIONS 2;Query OK, 0 rows affected (0.00 sec)

このテーブルに属するパーティションは、次のクエリーを使用して表示できます。

mysql> SELECT TABLE_NAME,PARTITION_NAME,TABLE_ROWS,AVG_ROW_LENGTH,DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';
+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+
| th | p0 | 0 | 0 | 0 |
| th | p1 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

各パーティションの TABLE_ROWS は 0 です。ここで次に示すように、c1 カラム値が NULL および 0 である 2 つの行を th に挿入し、それらの行が挿入されたことを確認します。

mysql> INSERT INTO th VALUES (NULL, 'mothra'), (0, 'gigan');Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM th;+------+---------+
| c1 | c2 |
+------+---------+
| NULL | mothra |
+------+---------+
| 0 | gigan |
+------+---------+
2 rows in set (0.01 sec)

任意の整数 N について、NULL MOD N の値は常に NULL であることを思い出してください。HASH または KEY によってパーティション化されたテーブルの場合、この結果は正しいパーティションを判別するために 0 として扱われます。INFORMATION_SCHEMA.PARTITIONS テーブルを再度確認すると、両方の行がパーティション p0 に挿入されたことがわかります。

mysql> SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH > FROM INFORMATION_SCHEMA.PARTITIONS > WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME ='th';+------------+----------------+------------+----------------+-------------+
| TABLE_NAME | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+------------+----------------+------------+----------------+-------------+| th | p0 | 2 | 20 | 20 || th | p1 | 0 | 0 | 0 |
+------------+----------------+------------+----------------+-------------+
2 rows in set (0.00 sec)

テーブルの定義で PARTITION BY HASH の代わりに PARTITION BY KEY を使用してこの例を繰り返すと、このタイプのパーティショニングでも NULL が 0 のように扱われることを簡単に確認できます。

19.3 パーティション管理

MySQL 5.6 は、パーティション化されたテーブルを変更するためにいくつかの方法を提供しています。既存のパーティションを追加、削除、再定義、マージ、または分割できます。これらのすべてのアクションは、ALTER TABLE ステートメントのパーティショニング拡張を使用して実行できます。パーティション化されたテーブルおよびパーティションに関する情報を取得する方法もあります。以降のセクションでは次のトピックについて説明します。

注記

MySQL 5.6 では、パーティション化されたテーブルのすべてのパーティションに同じ数のサブパーティションが必要であり、テーブルが作成されたあとにサブパーティショニングを変更することはできません。

テーブルのパーティショニングスキームを変更するために必要なことは、partition_options 句付きで ALTER TABLE ステートメントを使用することだけです。この句の構文は、パーティション化されたテーブルを作成する CREATE TABLE で使用されるものと同じで、必ずキーワード PARTITION BY で始まります。次の CREATE TABLE ステートメントを使用して範囲によってパーティション化されたテーブルがあるとします。

CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (2005) );

このテーブルをキーによるパーティション化でパーティション化し直して、キーをベースとする id カラム値を使用する 2 つのパーティションに分割するために、次のステートメントを使用できます。

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

これは、テーブルを削除してから CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2; を使用して再作成する場合と同じ効果を、テーブルの構造に対して持ちます。

ALTER TABLE ... ENGINE = ... は、テーブルによって使用されるストレージエンジンのみを変更し、テーブルのパーティショニングスキームはそのままにします。テーブルのパーティショニングを削除するには、ALTER TABLE ... REMOVE PARTITIONING を使用します。セクション13.1.7「ALTER TABLE 構文」を参照してください。

重要

ALTER TABLE ステートメントに使用できるのは、単一の PARTITION BYADD PARTITIONDROP PARTITIONREORGANIZE PARTITION、または COALESCE PARTITION 句のみです。たとえば、あるパーティションを削除して、テーブルの残りのパーティションを再編成する場合は、2 つの別々の ALTER TABLE ステートメントでそうする必要があります (DROP PARTITION を使用するものと REORGANIZE PARTITIONS を使用するもの)。

MySQL 5.6 では、ALTER TABLE ... TRUNCATE PARTITION を使用して、選択した 1 つ以上のパーティションからすべての行を削除できます。

19.3.1 RANGE および LIST パーティションの管理

RANGE および LIST パーティションは、パーティションの追加と削除がどのように処理されるかに関してはよく似ています。このため、このセクションでは両方のタイプのパーティショニングの管理について説明します。ハッシュまたはキーによってパーティション化されたテーブルの管理については、セクション19.3.2「HASH および KEY パーティションの管理」を参照してください。RANGE または LIST パーティションの削除は追加よりも単純なので、これを最初に説明します。

RANGE または LIST によってパーティション化されたテーブルからパーティションを削除する操作は、DROP PARTITION 句付きで ALTER TABLE ステートメントを使用することで実行できます。非常に基本的な例ですが、次の CREATE TABLE および INSERT ステートメントを使用して、範囲によってパーティション化されるテーブルをすでに作成済みで、10 件のレコードを移入しているとします。

mysql> CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE) -> PARTITION BY RANGE( YEAR(purchased) ) ( -> PARTITION p0 VALUES LESS THAN (1990), -> PARTITION p1 VALUES LESS THAN (1995), -> PARTITION p2 VALUES LESS THAN (2000), -> PARTITION p3 VALUES LESS THAN (2005) -> );Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO tr VALUES -> (1, 'desk organiser', '2003-10-15'), -> (2, 'CD player', '1993-11-05'), -> (3, 'TV set', '1996-03-10'), -> (4, 'bookcase', '1982-01-10'), -> (5, 'exercise bike', '2004-05-09'), -> (6, 'sofa', '1987-06-05'), -> (7, 'popcorn maker', '2001-11-22'), -> (8, 'aquarium', '1992-08-04'), -> (9, 'study desk', '1984-09-16'), -> (10, 'lava lamp', '1998-12-25');Query OK, 10 rows affected (0.01 sec)

パーティション p2 に挿入されているはずの項目を以下のように確認できます。

mysql> SELECT * FROM tr -> WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';+------+-----------+------------+
| id | name | purchased |
+------+-----------+------------+
| 3 | TV set | 1996-03-10 |
| 10 | lava lamp | 1998-12-25 |
+------+-----------+------------+
2 rows in set (0.00 sec)

p2 という名前のパーティションを削除するには、次のコマンドを実行します。

mysql> ALTER TABLE tr DROP PARTITION p2;Query OK, 0 rows affected (0.03 sec)
注記

NDB ストレージエンジンは ALTER TABLE ... DROP PARTITION をサポートしません。ただし、この章で説明されている ALTER TABLE へのほかのパーティショニング関連拡張はサポートしています。

パーティションを削除すると、そのパーティションに格納されていたすべてのデータも削除されることを覚えておくことは非常に重要です。前の SELECT クエリーを再実行することで、これが本当であることを確認できます。

mysql> SELECT * FROM tr WHERE purchased -> BETWEEN '1995-01-01' AND '1999-12-31';Empty set (0.00 sec)

このため、テーブルに対して ALTER TABLE ... DROP PARTITION を実行するには、そのテーブルの DROP 権限が必要です。

テーブル定義およびそのパーティショニングスキームを保持したまま、すべてのパーティションからすべてのデータを削除する場合は、TRUNCATE TABLE ステートメントを使用してください。(セクション13.1.33「TRUNCATE TABLE 構文」を参照してください)。

データを失うことなくテーブルのパーティショニングを変更する場合は、代わりに ALTER TABLE ... REORGANIZE PARTITION を使用してください。REORGANIZE PARTITION については、後続の説明またはセクション13.1.7「ALTER TABLE 構文」を参照してください。

ここで SHOW CREATE TABLE ステートメントを実行すると、テーブルのパーティショニング構成がどのように変更されたかを確認できます。

mysql> SHOW CREATE TABLE tr\G*************************** 1. row *************************** Table: tr
Create Table: CREATE TABLE `tr` ( `id` int(11) default NULL, `name` varchar(50) default NULL, `purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE ( YEAR(purchased) ) ( PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.01 sec)

purchased カラム値が '1995-01-01' から '2004-12-31' まで (両端を含む) の新しい行を変更済みテーブルに挿入すると、それらの行はパーティション p3 に格納されます。このことを次のようにして確認できます。

mysql> INSERT INTO tr VALUES (11, 'pencil holder', '1995-07-12');Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM tr WHERE purchased -> BETWEEN '1995-01-01' AND '2004-12-31';+------+----------------+------------+
| id | name | purchased |
+------+----------------+------------+
| 11 | pencil holder | 1995-07-12 |
| 1 | desk organiser | 2003-10-15 |
| 5 | exercise bike | 2004-05-09 |
| 7 | popcorn maker | 2001-11-22 |
+------+----------------+------------+
4 rows in set (0.00 sec)
mysql> ALTER TABLE tr DROP PARTITION p3;Query OK, 0 rows affected (0.03 sec)
mysql> SELECT * FROM tr WHERE purchased -> BETWEEN '1995-01-01' AND '2004-12-31';Empty set (0.00 sec)

ALTER TABLE ... DROP PARTITION の結果としてテーブルから削除された行数は、同等の DELETE クエリーとは異なり、サーバーから報告されません。

LIST パーティションを削除する場合は、RANGE パーティションの削除に使用するものとまったく同じ ALTER TABLE ... DROP PARTITION 構文を使用します。ただし、この操作が持つ影響について、このテーブルをあとで使用する際に重要な違いが 1 つあります。このテーブルには、削除したパーティションを定義する値リストに含まれていた値を持つ行を挿入できなくなります。(例については、セクション19.2.2「LIST パーティショニング」を参照してください)。

すでにパーティション化されたテーブルに新しい範囲またはリストパーティションを追加するには、ALTER TABLE ... ADD PARTITION ステートメントを使用します。RANGE によってパーティション化されたテーブルの場合は、これを使用して、既存のパーティションのリストの最後に新しい範囲を追加できます。次のように定義された、組織のメンバーシップデータが含まれるパーティション化されたテーブルがあるとします。

CREATE TABLE members ( id INT, fname VARCHAR(25), lname VARCHAR(25), dob DATE
)
PARTITION BY RANGE( YEAR(dob) ) ( PARTITION p0 VALUES LESS THAN (1970), PARTITION p1 VALUES LESS THAN (1980), PARTITION p2 VALUES LESS THAN (1990)
);

さらに、メンバーの最少年齢は 16 歳であるとします。カレンダーが 2005 年の終わりに近づいて、1990 年に生まれたメンバー (さらに、来年以降はそれよりあとのメンバー) をまもなく受け入れることに気付きます。次のように members テーブルを変更することで、1990 年から 1999 年までに生まれた新しいメンバーを受け入れることができます。

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

範囲によってパーティション化されたテーブルで ADD PARTITION を使用するときは、パーティションリストの上端にのみ新しいパーティションを追加できます。この方法で新しいパーティションを既存のパーティションの間または前に追加しようとすると、次のようにエラーになります。

mysql> ALTER TABLE members > ADD PARTITION ( > PARTITION n VALUES LESS THAN (1960));ERROR 1463 (HY000): VALUES LESS THAN value must be strictly » increasing for each partition

この問題は、次のように最初のパーティションを 2 つに再編成し、それらの間の範囲を分割することで回避できます。

ALTER TABLE members REORGANIZE PARTITION p0 INTO ( PARTITION n0 VALUES LESS THAN (1960), PARTITION n1 VALUES LESS THAN (1970)
);

SHOW CREATE TABLE を使用することで、ALTER TABLE ステートメントによって意図した効果が得られたことを確認できます。

mysql> SHOW CREATE TABLE members\G*************************** 1. row *************************** Table: members
Create Table: CREATE TABLE `members` ( `id` int(11) DEFAULT NULL, `fname` varchar(25) DEFAULT NULL, `lname` varchar(25) DEFAULT NULL, `dob` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

セクション13.1.7.1「ALTER TABLE パーティション操作」も参照してください。

ALTER TABLE ... ADD PARTITION を使用して、LIST によってパーティション化されたテーブルに新しいパーティションを追加することもできます。次の CREATE TABLE ステートメントを使用してテーブル tt が定義されているとします。

CREATE TABLE tt ( id INT, data INT
)
PARTITION BY LIST(data) ( PARTITION p0 VALUES IN (5, 10, 15), PARTITION p1 VALUES IN (6, 12, 18)
);

data カラム値が 714、および 21 である行を格納する新しいパーティションを次のように追加できます。

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES IN (7, 14, 21));

既存のパーティションの値リストにすでに含まれている値を含む新しい LIST パーティションは、追加できません。これを試みるとエラーになります。

mysql> ALTER TABLE tt ADD PARTITION  > (PARTITION np VALUES IN (4, 8, 12));ERROR 1465 (HY000): Multiple definition of same constant » in list partitioning

data カラム値が 12 である行がパーティション p1 にすでに割り当てられているため、値リストに 12 が含まれる新しいパーティションをテーブル tt に作成することはできません。これを実現するために、p1 を削除し、np を追加してから、定義を変更した新しい p1 を追加できます。ただし、すでに説明したように、これによって p1 に格納されていたすべてのデータが失われるので、これが実際にやりたいことでないことが多いです。別の解決策になる可能性があるのが、CREATE TABLE ... SELECT ... を使用して、新しいパーティショニング付きでテーブルのコピーを作成し、データをそこにコピーしてから、古いテーブルを削除して新しいテーブルを名前変更することですが、これは大量のデータを扱うときに非常に時間がかかる可能性があります。高可用性が要求される状況では実行できない可能性もあります。

次のように単一 ALTER TABLE ... ADD PARTITION ステートメントで複数のパーティションを追加できます。

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, hired DATE NOT NULL
)
PARTITION BY RANGE( YEAR(hired) ) ( PARTITION p1 VALUES LESS THAN (1991), PARTITION p2 VALUES LESS THAN (1996), PARTITION p3 VALUES LESS THAN (2001), PARTITION p4 VALUES LESS THAN (2005)
);
ALTER TABLE employees ADD PARTITION ( PARTITION p5 VALUES LESS THAN (2010), PARTITION p6 VALUES LESS THAN MAXVALUE
);

ありがたいことに、MySQL のパーティショニング実装は、データを失うことなくパーティショニングを再定義する方法を提供しています。まず、RANGE パーティショニングを使用するいくつかの簡単な例を見てみましょう。次のように定義された members テーブルを思い出してください。

mysql> SHOW CREATE TABLE members\G*************************** 1. row *************************** Table: members
Create Table: CREATE TABLE `members` ( `id` int(11) default NULL, `fname` varchar(25) default NULL, `lname` varchar(25) default NULL, `dob` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE ( YEAR(dob) ) ( PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM. PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
)

1960 年より前に生まれたメンバーを表すすべての行を別のパーティションに移動するとします。すでに説明したように、これは ALTER TABLE ... ADD PARTITION を使用して行うことはできません。ただし、ALTER TABLE への別のパーティション関連拡張を使用して、これを行うことができます。

ALTER TABLE members REORGANIZE PARTITION p0 INTO ( PARTITION s0 VALUES LESS THAN (1960), PARTITION s1 VALUES LESS THAN (1970)
);

実際には、このコマンドはパーティション p0 を 2 つの新しいパーティション s0 および s1 に分割します。さらに、p0 に格納されていたデータを 2つの PARTITION ... VALUES ... 句に示されているルールに従って新しいパーティションに移動する結果、s0 には YEAR(dob) が 1960 より小さいレコードのみが含まれ、s1 には YEAR(dob) が 1960 以上で 1970 より小さい行が含まれます。

REORGANIZE PARTITION 句を使用して、隣接するパーティションをマージすることもできます。次のように members テーブルを以前のパーティショニングに戻すことができます。

ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO ( PARTITION p0 VALUES LESS THAN (1970)
);

REORGANIZE PARTITION を使用してパーティションを分割またはマージしてもデータは失われません。上記のステートメントを実行すると、MySQL はパーティション s0 および s1 に格納されていたすべてのレコードをパーティション p0 に移動します。

REORGANIZE PARTITION の一般的な構文を次に示します。

ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions);

ここで、tbl_name はパーティション化されたテーブルの名前、partition_list は変更する 1 つ以上の既存のパーティションの名前のカンマ区切りのリストです。partition_definitions は、CREATE TABLE で使用される partition_definitions リスト (セクション13.1.17「CREATE TABLE 構文」を参照してください) と同じ規則に従う、新しいパーティション定義のカンマ区切りのリストです。REORGANIZE PARTITION を使用するときは、複数のパーティションを 1 つにマージする、または 1 つのパーティションを多数に分割する以外のこともできます。たとえば、次のように members テーブルの 4 つのパーティションすべてを 2 つに再編成できます。

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO ( PARTITION m0 VALUES LESS THAN (1980), PARTITION m1 VALUES LESS THAN (2000)
);

LIST によってパーティション化されたテーブルで REORGANIZE PARTITION を使用することもできます。リストによってパーティション化された tt テーブルに新しいパーティションを追加する操作が、既存のパーティションのいずれかの値リストにすでに存在する値が新しいパーティションに含まれていることが原因で失敗する問題に戻ります。これは、競合しない値のみが含まれるパーティションを追加してから、新しいパーティションと既存のものを再編成して既存のものに格納されていた値が新しいものに移動するようにすることで、対処できます。

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8));
ALTER TABLE tt REORGANIZE PARTITION p1,np INTO ( PARTITION p1 VALUES IN (6, 18), PARTITION np VALUES in (4, 8, 12)
);

RANGE または LIST によってパーティション化されたテーブルをパーティション化し直すために ALTER TABLE ... REORGANIZE PARTITION を使用するときに注意すべき、いくつかの重要点を次に示します。

  • 新しいパーティショニングスキームを決定するために使用される PARTITION 句には、CREATE TABLE ステートメントで使用されるものと同じ規則が適用されます。

    もっとも重要なことは、新しいパーティショニングスキームには、重複する範囲 (RANGE によってパーティション化されたテーブルに適用される) または値のセット (LIST によってパーティション化されたテーブルを再編成するとき) を指定できないことを覚えておくべきです。

  • partition_definitions リストのパーティションの組み合わせは、partition_list に指定されたパーティションの組み合わせの範囲または値セット全体と同じであるべきです。

    たとえば、このセクションの例として使用されている members テーブルでは、パーティション p1 および p2 が 1980 年から 1999 年までを範囲としています。このため、これら 2 つのパーティションを再編成する場合は、全体として同じ年範囲を含むべきです。

  • RANGE によってパーティション化されたテーブルの場合は、隣接するパーティションのみを再編成できます。範囲パーティションを飛ばすことはできません。

    たとえば、このセクションの例として使用されている members テーブルを、ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ... で始まるステートメントを使用して再編成することはできません。p0 は 1970 年より前の年を範囲とし、p2 は 1990 年から 1999 年まで (両端を含む) を範囲としていて、この 2 つは隣接するパーティションではないためです。

  • REORGANIZE PARTITION を使用して、テーブルのパーティショニングタイプを変更することはできません。つまり、たとえば、RANGE パーティションを HASH パーティションに変更することはできません (逆も不可)。このコマンドを使用してパーティショニング式またはカラムを変更することもできません。これらの作業をテーブルを削除および再作成することなく実現するために、ALTER TABLE ... PARTITION BY ... を使用できます。例:

    ALTER TABLE members PARTITION BY HASH( YEAR(dob) ) PARTITIONS 8;

19.3.2 HASH および KEY パーティションの管理

ハッシュまたはキーによってパーティション化されたテーブルは、パーティショニングセットアップで変更に関して互いによく似ていますが、範囲またはリストによってパーティション化されたテーブルとはいくつかの点で異なります。このため、このセクションではハッシュまたはキーによってパーティション化されたテーブルの変更についてのみ取り上げます。範囲またはリストによってパーティション化されたテーブルのパーティションを追加および削除することについては、セクション19.3.1「RANGE および LIST パーティションの管理」を参照してください。

HASH または KEY によってパーティション化されたテーブルから、RANGE または LIST によってパーティション化されたテーブルと同じ方法でパーティションを削除することはできません。ただし、ALTER TABLE ... COALESCE PARTITION ステートメントを使用して HASH または KEY のパーティションをマージすることはできます。クライアントに関するデータが含まれている、12 個のパーティションに分割されたテーブルがあるとします。clients テーブルは次のように定義されています。

CREATE TABLE clients ( id INT, fname VARCHAR(30), lname VARCHAR(30), signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;

パーティションの数を 12 個から 8 個に減らす場合は、次の ALTER TABLE コマンドを実行します。

mysql> ALTER TABLE clients COALESCE PARTITION 4;Query OK, 0 rows affected (0.02 sec)

COALESCE は、HASHKEYLINEAR HASH、または LINEAR KEY によってパーティション化されたテーブルで同等に適切に動作します。次の例は前の例と似ていますが、テーブルが LINEAR KEY によってパーティション化されている点のみが異なります。

mysql> CREATE TABLE clients_lk ( -> id INT, -> fname VARCHAR(30), -> lname VARCHAR(30), -> signed DATE -> ) -> PARTITION BY LINEAR KEY(signed) -> PARTITIONS 12;Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

COALESCE PARTITION のあとの数値は、残りにマージするパーティションの数です。つまり、テーブルから削除するパーティションの数です。

テーブルに含まれているものより多くのパーティションを削除しようとすると、次のようなエラーになります。

mysql> ALTER TABLE clients COALESCE PARTITION 18;ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead

clients テーブルのパーティションの数を 12 個から 18 個に増やす場合は、次のように ALTER TABLE ... ADD PARTITION を使用します。

ALTER TABLE clients ADD PARTITION PARTITIONS 6;

19.3.3 パーティションとサブパーティションをテーブルと交換する

MySQL 5.6 では、ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt を使用して、テーブルパーティションまたはサブパーティションをテーブルと交換できます。ここで、pt はパーティション化されたテーブル、p はパーティション化されていないテーブル nt と交換する pt のパーティションまたはサブパーティションです (次の記述が true である場合)。

  1. テーブル nt 自体はパーティション化されていない。

  2. テーブル nt は一時テーブルではない。

  3. テーブル pt および nt の構造はそれ以外の点で同じである。

  4. テーブル nt は外部キー参照を含まず、ほかのどのテーブルも nt を参照する外部キーを持たない。

  5. nt 内に p のパーティション定義の境界の外に存在する行がない。

ALTER TABLE ステートメントに通常必要な ALTERINSERT、および CREATE 権限に加えて、ALTER TABLE ... EXCHANGE PARTITION を実行するための DROP 権限が必要です。

ALTER TABLE ... EXCHANGE PARTITION の次の影響も考慮してください。

  • ALTER TABLE ... EXCHANGE PARTITION を実行しても、パーティション化されたテーブルまたは交換されるテーブルに対するトリガーは呼び出されません。

  • 交換されるテーブル内の AUTO_INCREMENT カラムがリセットされます。

  • IGNORE キーワードは、ALTER TABLE ... EXCHANGE PARTITION と一緒に使用された場合、効果を持つません。

ALTER TABLE ... EXCHANGE PARTITION ステートメントの完全な構文を次に示します。ここで、pt はパーティション化されたテーブル、p は交換されるパーティションまたはサブパーティション、ntp と交換されるパーティション化されていないテーブルです。

ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt;

単一 ALTER TABLE EXCHANGE PARTITION ステートメントでは、1 つのパーティションまたはサブパーティションのみを 1 つのパーティション化されていないテーブルのみと交換できます。複数のパーティションまたはサブパーティションを交換するには、複数の ALTER TABLE EXCHANGE PARTITION ステートメントを使用してください。EXCHANGE PARTITION は、ほかの ALTER TABLE オプションと組み合わせることはできません。パーティション化されたテーブルによって使用されるパーティショニングおよび (該当する場合) サブパーティショニングには、MySQL 5.6 でサポートされる任意のタイプを選択できます。

パーティションをパーティション化されていないテーブルと交換する

次の SQL ステートメントを使用して、パーティション化されたテーブル e が作成および移入されているとします。

CREATE TABLE e ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30)
) PARTITION BY RANGE (id) ( PARTITION p0 VALUES LESS THAN (50), PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (150), PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO e VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black");

ここで、e2 という名前の、e のパーティション化されていないコピーを作成します。これは、mysql クライアントを使用して次のように行うことができます。

mysql> CREATE TABLE e2 LIKE e;Query OK, 0 rows affected (1.34 sec)
mysql> ALTER TABLE e2 REMOVE PARTITIONING;Query OK, 0 rows affected (0.90 sec)
Records: 0 Duplicates: 0 Warnings: 0

テーブル e のどのパーティションに行が含まれるかは、次のように INFORMATION_SCHEMA.PARTITIONS テーブルを照会することで確認できます。

mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'e';+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1 |
| p1 | 0 |
| p2 | 0 |
| p3 | 3 |
+----------------+------------+
4 rows in set (0.00 sec)
注記

パーティション化された InnoDB テーブルの場合、INFORMATION_SCHEMA.PARTITIONS テーブルの TABLE_ROWS カラムに示される行数は、SQL 最適化で使用される見積もり値であり、常に正確とはかぎりません。

テーブル e 内のパーティション p0 をテーブルe2 と交換するには、次のような ALTER TABLE ステートメントを使用できます。

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;Query OK, 0 rows affected (0.28 sec)

より正確に言うと、ここで発行したステートメントによって、パーティションで見つかる行がテーブルで見つかるものと交換されます。これがどのように行われたかは、前のように INFORMATION_SCHEMA.PARTITIONS テーブルを照会することで観察できます。パーティション p0 で以前は見つかったテーブル行が存在しなくなっています。

mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'e';+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 0 |
| p1 | 0 |
| p2 | 0 |
| p3 | 3 |
+----------------+------------+
4 rows in set (0.00 sec)

テーブル e2 を照会すると、見つからない行がそこで見つかります。

mysql> SELECT * FROM e2;+----+-------+-------+
| id | fname | lname |
+----+-------+-------+
| 16 | Frank | White |
+----+-------+-------+
1 row in set (0.00 sec)

パーティションと交換されるテーブルは、必ずしも空である必要はありません。これを実証するために、まず新しい行をテーブル e に挿入してから、この行がパーティション p0 に格納されていることを確認します (50 より小さい id カラム値を選択し、これをあとで PARTITIONS テーブルを照会することで確認します)。

mysql> INSERT INTO e VALUES (41, "Michael", "Green");
Query OK, 1 row affected (0.05 sec)
mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'e';
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1 |
| p1 | 0 |
| p2 | 0 |
| p3 | 3 |
+----------------+------------+
4 rows in set (0.00 sec) 

ここで、前と同じ ALTER TABLE ステートメントを使用して、ふたたびパーティション p0 をテーブル e2 と交換します。

mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;Query OK, 0 rows affected (0.28 sec)

次のクエリーの出力は、ALTER TABLE ステートメントを発行する前に、パーティション p0 に格納されていたテーブル行およびテーブル e2 に格納されていたテーブル行の配置が切り替わったことを示しています。

mysql> SELECT * FROM e;+------+-------+-------+
| id | fname | lname |
+------+-------+-------+
| 16 | Frank | White |
| 1669 | Jim | Smith |
| 337 | Mary | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
4 rows in set (0.00 sec)
mysql> SELECT PARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'e';+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p0 | 1 |
| p1 | 0 |
| p2 | 0 |
| p3 | 3 |
+----------------+------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM e2;+----+---------+-------+
| id | fname | lname |
+----+---------+-------+
| 41 | Michael | Green |
+----+---------+-------+
1 row in set (0.00 sec)

一致しない行

ALTER TABLE ... EXCHANGE PARTITION ステートメントを発行する前にパーティション化されていないテーブルで見つかる行は、それらがターゲットパーティションに格納されるために必要な条件を満たしている必要があり、そうでない場合はステートメントが失敗することを覚えておいてください。これがどのように発生するかを確認するために、まずテーブルe のパーティション p0 のパーティション定義の境界外の行を、e2 に挿入します。たとえば、id カラム値が大きすぎる行を挿入してから、テーブルをパーティションとふたたび交換してみてください。

mysql> INSERT INTO e2 VALUES (51, "Ellen", "McDonald");Query OK, 1 row affected (0.08 sec)
mysql> ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;ERROR 1707 (HY000): Found row that does not match the partition

IGNORE キーワードは受け入れられますが、次に示すように EXCHANGE PARTITION で使用されるときは効果がありません。

mysql> ALTER IGNORE TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;ERROR 1707 (HY000): Found row that does not match the partition

サブパーティションをパーティション化されていないテーブルと交換する

ALTER TABLE ... EXCHANGE PARTITION ステートメントを使用して、サブパーティション化されたテーブルのサブパーティション (セクション19.2.6「サブパーティショニング」を参照してください) をパーティション化されていないテーブルと交換することもできます。次の例では、まず RANGE によってパーティション化され、KEY によってサブパーティション化されたテーブル es を作成し、テーブル e と同様にこのテーブルに移入してから、このテーブルの空のパーティション化されていないコピー es2 を作成します。

mysql> CREATE TABLE es ( -> id INT NOT NULL, -> fname VARCHAR(30), -> lname VARCHAR(30) -> ) -> PARTITION BY RANGE (id) -> SUBPARTITION BY KEY (lname) -> SUBPARTITIONS 2 ( -> PARTITION p0 VALUES LESS THAN (50), -> PARTITION p1 VALUES LESS THAN (100), -> PARTITION p2 VALUES LESS THAN (150), -> PARTITION p3 VALUES LESS THAN (MAXVALUE) -> );Query OK, 0 rows affected (2.76 sec)
mysql> INSERT INTO es VALUES -> (1669, "Jim", "Smith"), -> (337, "Mary", "Jones"), -> (16, "Frank", "White"), -> (2005, "Linda", "Black");Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE es2 LIKE es;Query OK, 0 rows affected (1.27 sec)
mysql> ALTER TABLE es2 REMOVE PARTITIONING;Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0

テーブル es を作成するときにサブパーティションの名前を明示的に指定しなかったけれども、PARTITIONS テーブルから選択するときに、次のように INFORMATION_SCHEMA からそのテーブルの SUBPARTITION_NAME を取り込むことで、それらに生成された名前を取得できます。

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'es';+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0 | p0sp0 | 1 |
| p0 | p0sp1 | 0 |
| p1 | p1sp0 | 0 |
| p1 | p1sp1 | 0 |
| p2 | p2sp0 | 0 |
| p2 | p2sp1 | 0 |
| p3 | p3sp0 | 3 |
| p3 | p3sp1 | 0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)

次の ALTER TABLE ステートメントは、テーブル es のサブパーティション p3sp0 をパーティション化されていないテーブル es2 と交換します。

mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;Query OK, 0 rows affected (0.29 sec)

次のクエリーを発行することで、それらの行が交換されたことを確認できます。

mysql> SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS -> FROM INFORMATION_SCHEMA.PARTITIONS -> WHERE TABLE_NAME = 'es';+----------------+-------------------+------------+
| PARTITION_NAME | SUBPARTITION_NAME | TABLE_ROWS |
+----------------+-------------------+------------+
| p0 | p0sp0 | 1 |
| p0 | p0sp1 | 0 |
| p1 | p1sp0 | 0 |
| p1 | p1sp1 | 0 |
| p2 | p2sp0 | 0 |
| p2 | p2sp1 | 0 |
| p3 | p3sp0 | 0 |
| p3 | p3sp1 | 0 |
+----------------+-------------------+------------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM es2;+------+-------+-------+
| id | fname | lname |
+------+-------+-------+
| 1669 | Jim | Smith |
| 337 | Mary | Jones |
| 2005 | Linda | Black |
+------+-------+-------+
3 rows in set (0.00 sec)

テーブルがサブパーティション化されている場合、次に示すように、パーティション化されていないテーブルと交換できるのは、テーブルのパーティション全体ではなくサブパーティションのみです。

mysql> ALTER TABLE es EXCHANGE PARTITION p3 WITH TABLE es2;ERROR 1704 (HY000): Subpartitioned table, use subpartition instead of partition

MySQL によって使用されるテーブル構造の比較は非常に厳密です。カラムの数、順序、名前、および型、さらにパーティション化されたテーブルとパーティション化されていないテーブルのインデックスが、正確に一致する必要があります。また、両方のテーブルが同じストレージエンジンを使用している必要があります。

mysql> CREATE TABLE es3 LIKE e;Query OK, 0 rows affected (1.31 sec)
mysql> ALTER TABLE es3 REMOVE PARTITIONING;Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE es3\G*************************** 1. row *************************** Table: es3
Create Table: CREATE TABLE `es3` ( `id` int(11) NOT NULL, `fname` varchar(30) DEFAULT NULL, `lname` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> ALTER TABLE es3 ENGINE = MyISAM;Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es3;ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

19.3.4 パーティションの保守

いくつかのテーブルおよびパーティション保守タスクは、MySQL 5.6 のパーティション化されたテーブルでそのような目的を実現するための SQL ステートメントを使用して実行できます。

パーティション化されたテーブルのテーブル保守は、パーティション化されたテーブルでサポートされる CHECK TABLEOPTIMIZE TABLEANALYZE TABLE、および REPAIR TABLE ステートメントを使用して実現できます。

次のリストで説明しているように、ALTER TABLE へのいくつかの拡張を使用して、1 つ以上のパーティションに対してこのタイプの操作を直接実行できます。

  • パーティションの再構築  パーティションを再構築します。これは、パーティションに格納されているすべてのレコードを削除してからそれらを再度挿入することと同じ効果があります。これはデフラグに役立つことがあります。

    例:

    ALTER TABLE t1 REBUILD PARTITION p0, p1;
  • パーティションの最適化  パーティションから多数の行を削除した場合、または可変長行を持つ (つまり、VARCHARBLOB、または TEXT カラムを持つ) パーティション化されたテーブルに多くの変更を行なった場合は、ALTER TABLE ... OPTIMIZE PARTITION を使用して、未使用領域を解放したりパーティションデータファイルをデフラグしたりできます。

    例:

    ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

    指定されたパーティションに OPTIMIZE PARTITION を使用することは、そのパーティションに CHECK PARTITIONANALYZE PARTITION、および REPAIR PARTITION を実行することと同等です。

    一部の MySQL ストレージエンジン (InnoDB を含む) は、パーティションごとの最適化をサポートしません。これらの場合は、ALTER TABLE ... OPTIMIZE PARTITION がテーブル全体を再構築されます。MySQL 5.6.9 以降では、そのようなテーブルでこのステートメントを実行すると、テーブル全体が再構築および分析され、該当する警告が発行されます (Bug #11751825、Bug #42822)。この問題を回避するには、代わりに ALTER TABLE ... REBUILD PARTITION および ALTER TABLE ... ANALYZE PARTITION を使用してください。

  • パーティションの分析  これは、パーティションのキー分布を読み取って格納します。

    例:

    ALTER TABLE t1 ANALYZE PARTITION p3;
  • パーティションの修復  これは、破損したパーティションを修復します。

    例:

    ALTER TABLE t1 REPAIR PARTITION p0,p1;
  • パーティションのチェック  パーティション化されていないテーブルで CHECK TABLE を使用できるのとほぼ同様に、パーティションのエラーをチェックできます。

    例:

    ALTER TABLE trb3 CHECK PARTITION p1;

    このコマンドは、テーブル t1 のパーティション p1 のデータまたはインデックスが破損しているかどうかを通知します。その場合は、ALTER TABLE ... REPAIR PARTITION を使用してパーティションを修復してください。

上記のリストの各ステートメントでは、パーティション名のリストの代わりにキーワード ALL もサポートされます。ALL を使用すると、テーブル内のすべてのパーティションにステートメントが作用します。

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

MySQL 5.6 では、ALTER TABLE ... TRUNCATE PARTITION を使用してパーティションを切り捨てることもできます。このステートメントは、TRUNCATE TABLE がテーブルからすべての行を削除するのとほぼ同様に、1 つ以上のパーティションからすべての行を削除するために使用できます。

ALTER TABLE ... TRUNCATE PARTITION ALL はテーブル内のすべてのパーティションを切り捨てます。

ANALYZECHECKOPTIMIZEREBUILDREPAIR、および TRUNCATE 操作は、サブパーティションではサポートされません。

19.3.5 パーティションに関する情報を取得する

このセクションでは、既存のパーティションに関する情報を取得する方法 (いくつかの方法が可能) について説明します。そのような情報を取得する方法には次のものが含まれます。

  • SHOW CREATE TABLE ステートメントを使用して、パーティション化されたテーブルの作成に使用されたパーティショニング句を表示する。

  • SHOW TABLE STATUS ステートメントを使用して、テーブルがパーティション化されているかどうかを判別する。

  • INFORMATION_SCHEMA.PARTITIONS テーブルを照会する。

  • EXPLAIN PARTITIONS SELECT ステートメントを使用して、指定された SELECT によってどのパーティションが使用されているかを確認する。

この章のほかの場所でも説明しているように、SHOW CREATE TABLE の出力にはパーティション化されたテーブルの作成に使用された PARTITION BY 句が含まれます。例:

mysql> SHOW CREATE TABLE trb3\G*************************** 1. row *************************** Table: trb3
Create Table: CREATE TABLE `trb3` ( `id` int(11) default NULL, `name` varchar(50) default NULL, `purchased` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (YEAR(purchased)) ( PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM, PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM, PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM
)
1 row in set (0.00 sec)

パーティション化されたテーブルに対する SHOW TABLE STATUS の出力は、Create_options カラムに文字列 partitioned が含まれることを除いて、パーティション化されていないテーブルの場合と同じです。Engine カラムには、テーブルのすべてのパーティションによって使用されるストレージエンジンの名前が含まれます。(このステートメントについての詳細は、セクション13.7.5.37「SHOW TABLE STATUS 構文」を参照してください)。

パーティションに関する情報は、PARTITIONS テーブルを含む INFORMATION_SCHEMA からも取得できます。セクション21.13「INFORMATION_SCHEMA PARTITIONS テーブル」を参照してください。

指定された SELECT クエリーでパーティション化されたテーブルのどのパーティションが使用されるかは、EXPLAIN PARTITIONS を使用して判別できます。PARTITIONS キーワードは、partitions カラム (どのパーティションからのレコードがクエリーで照合されるかをリストする) を EXPLAIN の出力に追加します。

テーブル trb1 が次のように作成されて移入されているとします。

CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (3), PARTITION p1 VALUES LESS THAN (7), PARTITION p2 VALUES LESS THAN (9), PARTITION p3 VALUES LESS THAN (11) );
INSERT INTO trb1 VALUES (1, 'desk organiser', '2003-10-15'), (2, 'CD player', '1993-11-05'), (3, 'TV set', '1996-03-10'), (4, 'bookcase', '1982-01-10'), (5, 'exercise bike', '2004-05-09'), (6, 'sofa', '1987-06-05'), (7, 'popcorn maker', '2001-11-22'), (8, 'aquarium', '1992-08-04'), (9, 'study desk', '1984-09-16'), (10, 'lava lamp', '1998-12-25');

SELECT * FROM trb1; などのクエリーでどのパーティションが使用されるかを次のように確認できます。

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: trb1 partitions: p0,p1,p2,p3 type: ALL
possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using filesort

この場合、4 つのパーティションがすべて検索されます。ただし、次のようにパーティショニングキーを使用する制限条件をクエリーに追加すると、一致する値が含まれているパーティションのみがスキャンされることがわかります。

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: trb1 partitions: p0,p1 type: ALL
possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using where

EXPLAIN PARTITIONS では、普通の EXPLAIN SELECT ステートメントと同様に、使用されているキーおよび使用可能なキーに関する情報が表示されます。

mysql> ALTER TABLE trb1 ADD PRIMARY KEY (id);Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: trb1 partitions: p0,p1 type: range
possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 7 Extra: Using where

EXPLAIN PARTITIONS に関する次の制約および制限に注目してください。

  • EXTENDED および PARTITIONS キーワードは、同一 EXPLAIN ... SELECT ステートメント内で一緒に使用できません。そうしようとすると構文エラーになります。

  • クエリーを検査するためにパーティション化されていないテーブルで EXPLAIN PARTITIONS を使用すると、エラーは発生しませんが、partitions カラムの値は常に NULL となります。

EXPLAIN PARTITIONS 出力の rows カラムには、テーブルの合計行数が表示されます。

セクション13.8.2「EXPLAIN 構文」も参照してください。

19.4 パーティションプルーニング

このセクションでは、パーティションプルーニングと呼ばれる最適化について説明します。パーティションプルーニングの背後の中心概念は比較的単純で、一致する値がないパーティションはスキャンしないと表現できます。次のステートメントによって定義されたパーティション化されたテーブル t1 があるとします。

CREATE TABLE t1 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL
)
PARTITION BY RANGE( region_code ) ( PARTITION p0 VALUES LESS THAN (64), PARTITION p1 VALUES LESS THAN (128), PARTITION p2 VALUES LESS THAN (192), PARTITION p3 VALUES LESS THAN MAXVALUE
);

次のような SELECT ステートメントから結果を取得するとします。

SELECT fname, lname, region_code, dob FROM t1 WHERE region_code > 125 AND region_code < 130;

返すべき行がパーティション p0 にも p3 にもないことは簡単にわかります。つまり、パーティション p1 および p2 のみを検索して一致する行を見つける必要があります。そうすることにより、一致する行を検索するために消費する時間と労力が、テーブル内のすべてのパーティションのスキャンに必要なものより少なくて済みます。必要のないパーティションをこのように省くことをプルーニングといいます。オプティマイザがこのクエリーの実行でパーティションプルーニングを使用できるときは、同じカラム定義およびデータが含まれているパーティション化されていないテーブルで同じクエリーを実行するよりも、速度が大幅に向上することがあります。

WHERE 条件を次の 2 つのケースのいずれかにまとめられるとき、オプティマイザはプルーニングを実行できます。

  • partition_column = constant

  • partition_column IN (constant1, constant2, ..., constantN)

最初のケースで、オプティマイザは指定された値についてパーティショニング式を単純に評価し、どのパーティションに値が含まれるかを判別して、このパーティションのみをスキャンします。多くの場合、この等号を別の算術比較 (<><=>=、および <> を含む) に置き換えることができます。WHERE 句で BETWEEN を使用するクエリーも、パーティションプルーニングの利点を活用できます。このセクションの後続の例を参照してください。

2 番目のケースで、オプティマイザはリスト内の各値についてパーティショニング式を評価して、一致するパーティションのリストを作成してから、このパーティションリストのパーティションのみをスキャンします。

MySQL は、パーティションプルーニングを SELECTDELETE、および UPDATE ステートメントに適用できます。INSERT ステートメントは現在のところ、プルーニングできません。

短い範囲にもプルーニングを適用できます (オプティマイザが同等の値リストに変換できるもの)。たとえば、前の例では、WHERE 句を WHERE region_code IN (126, 127, 128, 129) に変換できます。これにより、オプティマイザは、リスト内の最初の 3 つの値はパーティション p1 に見つかり、残りの 3 つの値はパーティション p2 に見つかり、ほかのパーティションには関連する値は含まれないため一致する行を検索する必要がないと判断できます。

MySQL 5.6 のオプティマイザは、RANGE COLUMNS または LIST COLUMNS パーティショニングを使用するテーブルの複数のカラムで、前述のタイプの比較を使用する WHERE 条件のプルーニングを実行することもできます。

このタイプの最適化は、パーティショニング式が同一性や範囲で構成されていてそれを同一性のセットにまとめられるとき、またはパーティショニング式が増減する関係を表すときに適用できます。パーティショニング式が YEAR() または TO_DAYS() 関数を使用するとき、DATE カラムまたは DATETIME カラムでパーティション化されるテーブルにプルーニングを適用することもできます。また、MySQL 5.6 では、パーティショニング式が TO_SECONDS() 関数を使用するとき、そのようなテーブルにプルーニングを適用できます。

テーブル t2 が次のように定義されて、DATE カラムでパーティション化されるとします。

CREATE TABLE t2 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL
)
PARTITION BY RANGE( YEAR(dob) ) ( PARTITION d0 VALUES LESS THAN (1970), PARTITION d1 VALUES LESS THAN (1975), PARTITION d2 VALUES LESS THAN (1980), PARTITION d3 VALUES LESS THAN (1985), PARTITION d4 VALUES LESS THAN (1990), PARTITION d5 VALUES LESS THAN (2000), PARTITION d6 VALUES LESS THAN (2005), PARTITION d7 VALUES LESS THAN MAXVALUE
);

t2 を使用する次のステートメントでは、パーティションプルーニングを使用できます。

SELECT * FROM t2 WHERE dob = '1982-06-23';
UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';
DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

最後のステートメントの場合、オプティマイザは次のようにも動作できます。

  1. 範囲の下限が含まれるパーティションを見つけます

    YEAR('1984-06-21') は値 1984 を返し、それはパーティション d3 に見つかります。

  2. 範囲の上限が含まれるパーティションを見つけます

    YEAR('1999-06-21')1999 と評価され、それはパーティション d5 に見つかります。

  3. これらの 2 つのパーティションおよびそれらの間にある可能性のあるパーティションのみをスキャンします

    この場合、これはパーティション d3d4、および d5 のみがスキャンされることを意味します。残りのパーティションは安全に無視できます (そして無視されます)。

重要

パーティション化されたテーブルに対するステートメントの WHERE 条件で参照される無効な DATE および DATETIME 値は、NULL として扱われます。これは、SELECT * FROM partitioned_table WHERE date_column < '2008-12-00' などのクエリーは値を返さないことを意味します (Bug #40972 を参照してください)。

ここまで、RANGE パーティショニングを使用する例のみを見てきましたが、プルーニングはほかのパーティショニングタイプにも適用できます。

次に示すテーブル t3 のように、パーティショニング式が増加または減少している LIST によってパーティション化されたテーブルがあるとします。(この例では、簡単にするために region_code カラムが値 1 から 10 まで (両端を含む) に制限されると想定します)。

CREATE TABLE t3 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL
)
PARTITION BY LIST(region_code) ( PARTITION r0 VALUES IN (1, 3), PARTITION r1 VALUES IN (2, 5, 8), PARTITION r2 VALUES IN (4, 9), PARTITION r3 VALUES IN (6, 7, 10)
);

SELECT * FROM t3 WHERE region_code BETWEEN 1 AND 3 などのステートメントの場合、オプティマイザはどのパーティションで値 1、2、および 3 が見つかるかを判別して (r0 および r1)、残りのもの (r2 および r3) をスキップします。

HASH または [LINEAR] KEY によってパーティション化されたテーブルの場合も、パーティショニング式で使用されるカラムに対して WHERE 句が単純な = 関係を使用しているときは、パーティションプルーニングを適用できます。次のように作成されたテーブルがあるとします。

CREATE TABLE t4 ( fname VARCHAR(50) NOT NULL, lname VARCHAR(50) NOT NULL, region_code TINYINT UNSIGNED NOT NULL, dob DATE NOT NULL
)
PARTITION BY KEY(region_code)
PARTITIONS 8;

カラム値を定数と比較するステートメントはプルーニングできます。

UPDATE t4 WHERE region_code = 7;

プルーニングは短い範囲にも適用できます。オプティマイザがそのような条件を IN 関係に変換できるためです。たとえば、前に定義したものと同じテーブル t4 を使用して、次のようなクエリーをプルーニングできます。

SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6;
SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5;

どちらの場合も、WHERE 句はオプティマイザによって WHERE region_code IN (3, 4, 5) に変換されます。

重要

この最適化は、範囲サイズがパーティションの数より小さい場合にのみ使用されます。次のステートメントがあるとします。

DELETE FROM t4 WHERE region_code BETWEEN 4 AND 12;

WHERE 句の範囲は 9 個の値 (4、5、6、7、8、9、10、11、12) ですが、t4 のパーティションは 8 個だけです。これはこの DELETE をプルーニングできないことを意味します。

HASH または [LINEAR] KEY によってパーティション化されたテーブルの場合、プルーニングを使用できるのは整数カラムに対してのみです。たとえば、dobDATE カラムであるため、次のステートメントにはプルーニングを使用できません。

SELECT * FROM t4 WHERE dob >= '2001-04-14' AND dob <= '2005-10-15';

ただし、このテーブルが年値を INT カラムに格納する場合は、WHERE year_col >= 2001 AND year_col <= 2005 を持つクエリーをプルーニングできます。

MySQL 5.6.8 以降では、自動パーティショニングを提供するストレージエンジン (MySQL Cluster によって使用される NDB ストレージエンジン (現在のところ、MySQL 5.6 ではサポートされません) など) を使用するすべてのテーブルで、パーティションプルーニングは無効です(Bug #14672885)。MySQL 5.6.10 以降では、明示的にパーティション化されている場合は、そのようなテーブルをプルーニングできます(Bug #14827952)。

19.5 パーティション選択

MySQL 5.6 は、ステートメントを実行するときに、指定された WHERE 条件と一致する行をチェックすべきパーティションおよびサブパーティションの明示的選択をサポートします。パーティション選択は、特定のパーティションのみで一致がチェックされる点でパーティションプルーニングと似ていますが、2 つの重要な点で異なります。

  1. チェックされるパーティションは、パーティションプルーニングと異なり (自動)、ステートメントの発行者が指定します。

  2. パーティションプルーニングはクエリーのみに適用されますが、明示的なパーティション選択はクエリーおよびいくつかの DML ステートメントの両方でサポートされます。

明示的なパーティション選択をサポートする SQL ステートメントを次に一覧します。

  • SELECT

  • DELETE

  • INSERT

  • REPLACE

  • UPDATE

  • LOAD DATA

  • LOAD XML

このセクションの残りの部分では、上記に一覧したステートメントに一般的に適用される明示的パーティション選択について説明し、いくつかの例を示します。

明示的パーティション選択は、PARTITION オプションを使用して実装されます。サポートされるすべてのステートメントについて、このオプションは次のような構文を使用します。

 PARTITION (partition_names) partition_names: partition_name, ...

このオプションは常に、パーティションが属するテーブルの名前の後ろに続けます。partition_names は、使用されるパーティションまたはサブパーティションのカンマ区切りのリストです。このリスト内の各名前は、指定されたテーブルの既存のパーティションまたはサブパーティションの名前である必要があります。パーティションまたはサブパーティションが見つからない場合、ステートメントはエラー (partition 'partition_name' doesn't exist) で失敗します。partition_names に指定するパーティションまたはサブパーティションは、任意の順序でリストでき、重複していてもかまいません。

PARTITION オプションを使用すると、リストされたパーティションおよびサブパーティションのみで一致する行がチェックされます。このオプションを SELECT ステートメントで使用すると、指定したパーティションに属する行を判別できます。次のようなステートメントを使用して作成および移入された、employees という名前のパーティション化されたテーブルがあるとします。

SET @@SQL_MODE = '';
CREATE TABLE employees ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, fname VARCHAR(25) NOT NULL, lname VARCHAR(25) NOT NULL, store_id INT NOT NULL, department_id INT NOT NULL
) PARTITION BY RANGE(id) ( PARTITION p0 VALUES LESS THAN (5), PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (15), PARTITION p3 VALUES LESS THAN MAXVALUE
);
INSERT INTO employees VALUES ('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2), ('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4), ('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3), ('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1), ('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4), ('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2), ('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3), ('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2), ('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);

どの行がパーティション p1 に格納されているかは次のように確認できます。

mysql> SELECT * FROM employees PARTITION (p1);+----+-------+--------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+--------+----------+---------------+
| 5 | Mary | Jones | 1 | 1 |
| 6 | Linda | Black | 2 | 3 |
| 7 | Ed | Jones | 2 | 1 |
| 8 | June | Wilson | 3 | 1 |
| 9 | Andy | Smith | 1 | 3 |
+----+-------+--------+----------+---------------+
5 rows in set (0.00 sec)

この結果は、クエリー SELECT * FROM employees WHERE id BETWEEN 5 AND 9 によって取得されるものと同じです。

複数のパーティションからの行を取得するには、それらの名前をカンマ区切りのリストとして指定します。たとえば、SELECT * FROM employees PARTITION (p1, p2) はパーティション p1 および p2 からのすべての行を返し、残りのパーティションからの行を除外します。

パーティション化されたテーブルに対する有効なクエリーは、PARTITION オプションを使用して、結果を 1 つ以上の目的のパーティションに制限するように書き直すことができます。WHERE 条件、ORDER BY オプション、LIMIT オプションなどを使用できます。集約関数を HAVING および GROUP BY オプション付きで使用することもできます。次の各クエリーは、前に定義した employees テーブルで実行するときに、有効な結果を生成します。

mysql> SELECT * FROM employees PARTITION (p0, p2) -> WHERE lname LIKE 'S%';+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 4 | Jim | Smith | 2 | 4 |
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
2 rows in set (0.00 sec)
mysql> SELECT id, CONCAT(fname, ' ', lname) AS name -> FROM employees PARTITION (p0) ORDER BY lname;+----+----------------+
| id | name |
+----+----------------+
| 3 | Ellen Johnson |
| 4 | Jim Smith |
| 1 | Bob Taylor |
| 2 | Frank Williams |
+----+----------------+
4 rows in set (0.06 sec)
mysql> SELECT store_id, COUNT(department_id) AS c -> FROM employees PARTITION (p1,p2,p3) -> GROUP BY store_id HAVING c > 4;+---+----------+
| c | store_id |
+---+----------+
| 5 | 2 |
| 5 | 3 |
+---+----------+
2 rows in set (0.00 sec)

パーティション選択を使用するステートメントは、MySQL 5.6 でサポートされるパーティショニングタイプを使用するテーブルで使用できます。テーブルが [LINEAR] HASH または [LINEAR] KEY パーティショニングを使用して作成されているけれども、パーティションの名前が指定されていない場合は、MySQL はパーティションに p0p1p2、...、pN-1 という名前を自動的に付けます。ここで、N はパーティションの数です。明示的に名前が付けられていないサブパーティションの場合、MySQL は各パーティション pX 内のサブパーティションに pXsp0pXsp1pXsp2、...、pXspM-1 という名前を自動的に割り当てます。ここで、M はサブパーティションの数です。このテーブルで SELECT (または明示的パーティション選択が許可されるほかの SQL ステートメント) を実行するときは、次のようにこれらの生成された名前を PARTITION オプションで使用できます。

mysql> CREATE TABLE employees_sub ( -> id INT NOT NULL AUTO_INCREMENT, -> fname VARCHAR(25) NOT NULL, -> lname VARCHAR(25) NOT NULL, -> store_id INT NOT NULL, -> department_id INT NOT NULL, -> PRIMARY KEY pk (id, lname) -> ) -> PARTITION BY RANGE(id) -> SUBPARTITION BY KEY (lname) -> SUBPARTITIONS 2 ( -> PARTITION p0 VALUES LESS THAN (5), -> PARTITION p1 VALUES LESS THAN (10), -> PARTITION p2 VALUES LESS THAN (15), -> PARTITION p3 VALUES LESS THAN MAXVALUE -> );Query OK, 0 rows affected (1.14 sec)
mysql> INSERT INTO employees_sub # re-use data in employees table -> SELECT * FROM employees;Query OK, 18 rows affected (0.09 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> SELECT id, CONCAT(fname, ' ', lname) AS name -> FROM employees_sub PARTITION (p2sp1);+----+---------------+
| id | name |
+----+---------------+
| 10 | Lou Waters |
| 14 | Fred Goldberg |
+----+---------------+
2 rows in set (0.00 sec)

次のように PARTITION オプションを INSERT ... SELECT ステートメントの SELECT 部分に使用することもできます。

mysql> CREATE TABLE employees_copy LIKE employees;Query OK, 0 rows affected (0.28 sec)
mysql> INSERT INTO employees_copy -> SELECT * FROM employees PARTITION (p2);Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM employees_copy;+----+--------+----------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+--------+----------+----------+---------------+
| 10 | Lou | Waters | 2 | 4 |
| 11 | Jill | Stone | 1 | 4 |
| 12 | Roger | White | 3 | 2 |
| 13 | Howard | Andrews | 1 | 2 |
| 14 | Fred | Goldberg | 3 | 3 |
+----+--------+----------+----------+---------------+
5 rows in set (0.00 sec)

パーティション選択は結合と一緒に使用することもできます。次のステートメントを使用して 2 つのテーブルを作成して移入するとします。

CREATE TABLE stores ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, city VARCHAR(30) NOT NULL
) PARTITION BY HASH(id) PARTITIONS 2;
INSERT INTO stores VALUES ('', 'Nambucca'), ('', 'Uranga'), ('', 'Bellingen'), ('', 'Grafton');
CREATE TABLE departments ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL
) PARTITION BY KEY(id) PARTITIONS 2;
INSERT INTO departments VALUES ('', 'Sales'), ('', 'Customer Service'), ('', 'Delivery'), ('', 'Accounting');

任意またはすべてのテーブルからのパーティション (またはサブパーティション、あるいはその両方) を結合で明示的に選択できます(指定されたテーブルからのパーティションを選択するために使用する PARTITION オプションは、テーブル名の直後、かつほかのすべてのオプション (テーブルエイリアスを含む) の前に指定します)。たとえば、次のクエリーは都市 Nambucca および Bellingen (stores テーブルのパーティション p0) のいずれかの店舗の販売部門または配送部門 (departments テーブルのパーティション p1) で働いているすべての従業員の、名前、従業員 ID、部門、および都市を取得します。

mysql> SELECT -> e.id AS 'Employee ID', CONCAT(e.fname, ' ', e.lname) AS Name, -> s.city AS City, d.name AS department -> FROM employees AS e -> JOIN stores PARTITION (p1) AS s ON e.store_id=s.id -> JOIN departments PARTITION (p0) AS d ON e.department_id=d.id -> ORDER BY e.lname;+-------------+---------------+-----------+------------+
| Employee ID | Name | City | department |
+-------------+---------------+-----------+------------+
| 14 | Fred Goldberg | Bellingen | Delivery |
| 5 | Mary Jones | Nambucca | Sales |
| 17 | Mark Morgan | Bellingen | Delivery |
| 9 | Andy Smith | Nambucca | Delivery |
| 8 | June Wilson | Bellingen | Sales |
+-------------+---------------+-----------+------------+
5 rows in set (0.00 sec)

MySQL での結合の一般情報については、セクション13.2.9.2「JOIN 構文」を参照してください。

DELETE ステートメントで PARTITION オプションを使用すると、オプションにリストされているパーティション (およびサブパーティション (ある場合)) でのみ削除される行がチェックされます。次のようにほかのパーティションは無視されます。

mysql> SELECT * FROM employees WHERE fname LIKE 'j%';+----+-------+--------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+--------+----------+---------------+
| 4 | Jim | Smith | 2 | 4 |
| 8 | June | Wilson | 3 | 1 |
| 11 | Jill | Stone | 1 | 4 |
+----+-------+--------+----------+---------------+
3 rows in set (0.00 sec)
mysql> DELETE FROM employees PARTITION (p0, p1) -> WHERE fname LIKE 'j%';Query OK, 2 rows affected (0.09 sec)
mysql> SELECT * FROM employees WHERE fname LIKE 'j%';+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

WHERE 条件と一致するパーティション p0 および p1 内の 2 つの行のみが削除されました。SELECT を 2 回目に実行したときの結果から確認できるように、WHERE 条件に一致する 1 行がテーブルに残っていますが、別のパーティション (p2) にあります。

明示的パーティション選択を使用する UPDATE ステートメントも同様に動作します。次のステートメントを実行することによって確認できるように、PARTITION オプションによって参照されるパーティション内の行のみが、更新される行を判別するときに考慮されます。

mysql> UPDATE employees PARTITION (p0)  -> SET store_id = 2 WHERE fname = 'Jill';Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql> SELECT * FROM employees WHERE fname = 'Jill';+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 1 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)
mysql> UPDATE employees PARTITION (p2) -> SET store_id = 2 WHERE fname = 'Jill';Query OK, 1 row affected (0.09 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM employees WHERE fname = 'Jill';+----+-------+-------+----------+---------------+
| id | fname | lname | store_id | department_id |
+----+-------+-------+----------+---------------+
| 11 | Jill | Stone | 2 | 4 |
+----+-------+-------+----------+---------------+
1 row in set (0.00 sec)

同様に、DELETE 付きで PARTITION を使用すると、パーティションリストに指定されたパーティション内の行のみが削除をチェックされます。

行を挿入するステートメントの動作は、適切なパーティションが見つからないとステートメントが失敗する点が異なります。これは、次のように INSERT および REPLACE ステートメントの両方に当てはまります。

mysql> INSERT INTO employees PARTITION (p2) VALUES (20, 'Jan', 'Jones', 1, 3);ERROR 1729 (HY000): Found a row not matching the given partition setmysql> INSERT INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 1, 3);Query OK, 1 row affected (0.07 sec)
mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, 'Jan', 'Jones', 3, 2);ERROR 1729 (HY000): Found a row not matching the given partition setmysql> REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);
Query OK, 2 rows affected (0.09 sec)

InnoDB ストレージエンジンを使用するパーティション化されたテーブルに複数の行を書き込むステートメントの場合、VALUES に続くリスト内の行を partition_names リストに指定されたパーティションのいずれかに書き込むことができないときは、ステートメント全体が失敗し、一行も書き込まれません。これについては、次の例 (employees テーブルを再使用) の INSERT ステートメントで示されています。

mysql> ALTER TABLE employees -> REORGANIZE PARTITION p3 INTO ( -> PARTITION p3 VALUES LESS THAN (20), -> PARTITION p4 VALUES LESS THAN (25), -> PARTITION p5 VALUES LESS THAN MAXVALUE -> );Query OK, 6 rows affected (2.09 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE employees\G*************************** 1. row *************************** Table: employees
Create Table: CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fname` varchar(25) NOT NULL, `lname` varchar(25) NOT NULL, `store_id` int(11) NOT NULL, `department_id` int(11) NOT NULL, PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> INSERT INTO employees PARTITION (p3, p4) VALUES -> (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);ERROR 1729 (HY000): Found a row not matching the given partition set
mysql> INSERT INTO employees PARTITION (p3, p4. p5) VALUES -> (24, 'Tim', 'Greene', 3, 1), (26, 'Linda', 'Mills', 2, 1);Query OK, 2 rows affected (0.06 sec)
Records: 2 Duplicates: 0 Warnings: 0

前述のことは、複数の行を書き込む INSERT および REPLACE ステートメントの両方に当てはまります。

MySQL 5.6.10 以降では、自動パーティショニングを提供するストレージエンジン (NDB など) を使用するテーブルの場合、パーティション選択は無効になります。(Bug #14827952)。

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

このセクションでは、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 0column 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 domainmysql> 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 domainmysql> 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 インデックスまたは検索をサポートしません。

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

一時テーブル  一時テーブルはパーティション化できません (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 TABLEOPTIMIZE TABLEANALYZE TABLE、および REPAIR TABLE がパーティション化されたテーブルでサポートされます。

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

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

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

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

このセクションでは、パーティショニングキーと主キーおよび一意キーとの関係について説明します。この関係を制御するルールは次のように表現できます。パーティション化されたテーブルのパーティショニング式で使用されるすべてのカラムは、テーブルが持つことができるすべての一意キーの一部である必要があります。

つまり、テーブルのすべての一意キーは、テーブルのパーティショニング式内のすべてのカラムを使用する必要があります(これには、テーブルの主キーも含まれます (自明で一意キーであるため)。この点については、このセクションで後述します)。たとえば、次の各テーブル作成ステートメントは無効です。

CREATE TABLE t1 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1), UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

どちらの場合も、記述されたテーブルには、パーティショニング式に使用されているすべてのカラムを含んでいない一意キーが少なくとも 1 つあります。

次の各ステートメントは有効で、対応する無効なテーブル作成ステートメントを機能させる 1 つの方法を示しています。

CREATE TABLE t1 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t2 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

次の例は、そのような場合に生成されるエラーを示しています。

mysql> CREATE TABLE t3 ( -> col1 INT NOT NULL, -> col2 DATE NOT NULL, -> col3 INT NOT NULL, -> col4 INT NOT NULL, -> UNIQUE KEY (col1, col2), -> UNIQUE KEY (col3) -> ) -> PARTITION BY HASH(col1 + col3) -> PARTITIONS 4;ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

この CREATE TABLE ステートメントは、指定されたパーティショニングキーに col1 および col3 が含まれているけれども、これらのカラムのいずれもテーブルの両方の一意キーの一部でないために、失敗します。無効なテーブル定義の考えられる解決策の 1 つを次に示します。

mysql> CREATE TABLE t3 ( -> col1 INT NOT NULL, -> col2 DATE NOT NULL, -> col3 INT NOT NULL, -> col4 INT NOT NULL, -> UNIQUE KEY (col1, col2, col3), -> UNIQUE KEY (col3) -> ) -> PARTITION BY HASH(col3) -> PARTITIONS 4;Query OK, 0 rows affected (0.05 sec)

この場合、指定されたパーティショニングキー col3 は両方の一意キーの一部であるため、このテーブル作成ステートメントは成功します。

次のテーブルは、両方の一意キーに属するカラムをパーティショニングキーに含めることができないため、パーティション化できません。

CREATE TABLE t4 ( col1 INT NOT NULL, col2 INT NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, UNIQUE KEY (col1, col3), UNIQUE KEY (col2, col4)
);

すべての主キーは自明で一意キーであるため、この制約にはテーブルの主キーも含まれます (ある場合)。たとえば、次の 2 つのステートメントは無効です。

CREATE TABLE t5 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;
CREATE TABLE t6 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY(col1, col3), UNIQUE KEY(col2)
)
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;

どちらの場合も、パーティショニング式で参照されるすべてのカラムが主キーに含まれていません。ただし、次の 2 つのステートメントは両方とも有効です。

CREATE TABLE t7 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY(col1, col2)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
CREATE TABLE t8 ( col1 INT NOT NULL, col2 DATE NOT NULL, col3 INT NOT NULL, col4 INT NOT NULL, PRIMARY KEY(col1, col2, col4), UNIQUE KEY(col2, col1)
)
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;

テーブルに一意キーがない場合 (主キーがない場合を含む) はこの制約は適用されず、カラム型がパーティショニングタイプと互換性があるかぎり、パーティショニング式に任意のカラムを使用できます。

同じ理由で、テーブルのパーティショニング式で使用されるすべてのカラムが一意キーに含まれている場合を除き、パーティション化されたテーブルにあとから一意キーを追加することはできません。次のように作成されたパーティション化されたテーブルがあるとします。

mysql> CREATE TABLE t_no_pk (c1 INT, c2 INT) -> PARTITION BY RANGE(c1) ( -> PARTITION p0 VALUES LESS THAN (10), -> PARTITION p1 VALUES LESS THAN (20), -> PARTITION p2 VALUES LESS THAN (30), -> PARTITION p3 VALUES LESS THAN (40) -> );Query OK, 0 rows affected (0.12 sec)

次のいずれかの ALTER TABLE ステートメントを使用することで、t_no_pk に主キーを追加できます。

# possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
# use another possible PK
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
# drop this PK
mysql> ALTER TABLE t_no_pk DROP PRIMARY KEY;Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

ただし、次のステートメントは失敗します。c1 は、パーティショニングキーの一部ですが、指定された主キーの一部ではないためです。

# fails with error 1503
mysql> ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

t_no_pk のパーティショニング式は c1 のみであるため、c2 のみに一意キーを追加しようとすると失敗します。ただし、c1 および c2 の両方を使用する一意キーは追加できます。

これらのルールは、既存のパーティション化されていないテーブルを ALTER TABLE ... PARTITION BY を使用してパーティション化するときにも適用されます。次のように作成されたテーブル np_pk があるとします。

mysql> CREATE TABLE np_pk ( -> id INT NOT NULL AUTO_INCREMENT, -> name VARCHAR(50), -> added DATE, -> PRIMARY KEY (id) -> );Query OK, 0 rows affected (0.08 sec)

次の ALTER TABLE ステートメントは、added カラムがテーブルの一意キーの一部でないため、エラーで失敗します。

mysql> ALTER TABLE np_pk -> PARTITION BY HASH( TO_DAYS(added) ) -> PARTITIONS 4;ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

ただし、次に示すように、パーティショニングカラムに id カラムを使用する次のステートメントは有効です。

mysql> ALTER TABLE np_pk -> PARTITION BY HASH(id) -> PARTITIONS 4;Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

np_pk の場合、パーティショニング式の一部として使用できる唯一のカラムは id です。このテーブルをパーティショニング式でほかのカラムを使用してパーティション化する場合は、目的のカラムを主キーに追加するか、主キーをすべて削除することによって、まずテーブルを変更する必要があります。

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

次の制限は、テーブルのユーザー定義パーティショニングでストレージエンジンを使用する際に適用されます。

MERGE ストレージエンジン  ユーザー定義パーティショニングと MERGE ストレージエンジンは互換性を持ちません。MERGE ストレージエンジンを使用するテーブルはパーティション化できません。パーティション化されたテーブルはマージできません。

FEDERATED ストレージエンジン FEDERATED テーブルのパーティショニングはサポートされません。パーティション化された FEDERATED テーブルを作成することはできません。

CSV ストレージエンジン CSV ストレージエンジンを使用するパーティション化されたテーブルはサポートされません。パーティション化された CSV テーブルを作成することはできません。

InnoDB ストレージエンジン InnoDB 外部キーと MySQL パーティショニングは互換性を持ちません。パーティション化された InnoDB テーブルは、外部キー参照を持つことができず、外部キーによって参照されるカラムを持つこともできません。外部キーを持っていたり、外部キーによって参照されたりする InnoDB テーブルはパーティション化できません。

また、ALTER TABLE ... OPTIMIZE PARTITION は、InnoDB ストレージエンジンを使用するパーティション化されたテーブルで正しく動作しません。そのようなテーブルの場合は、代わりに ALTER TABLE ... REBUILD PARTITION および ALTER TABLE ... ANALYZE PARTITION を使用してください。詳細は、セクション13.1.7.1「ALTER TABLE パーティション操作」を参照してください。

NDB ストレージエンジン (MySQL Cluster) KEY (LINEAR KEY を含む) によるパーティショニングは、NDB ストレージエンジンでサポートされる唯一のパーティショニングタイプです。MySQL Cluster NDB 7.3 では、[LINEAR] KEY 以外のパーティショニングタイプを使用して MySQL Cluster テーブルを作成することはできず、それを試みるとエラーで失敗します。

また、NDB テーブルに定義できる最大パーティション数は、クラスター内のデータノードとノードグループの数、使用している MySQL Cluster ソフトウェアのバージョン、およびその他の要因に依存します。詳細は、NDB とユーザー定義のパーティション化を参照してください。

ユーザーパーティション化された NDB テーブルが次の 2 つの要件のいずれかまたは両方を満たさなくなる CREATE TABLE ステートメントおよび ALTER TABLE ステートメントは許可されず、エラーで失敗します。

  1. テーブルに明示的な主キーが存在する必要があります。

  2. テーブルのパーティショニング式に指定されたすべてのカラムが主キーの一部である必要があります。

例外  ユーザーパーティション化された NDB テーブルが空のカラムリストを使用して (つまり、PARTITION BY KEY() または PARTITION BY LINEAR KEY() を使用して) 作成された場合、明示的な主キーは必要ありません。

パーティション化されたテーブルをアップグレードする  アップグレードを実行するときに、KEY によってパーティション化され、NDB 以外のストレージエンジンを使用するテーブルは、ダンプしてリロードする必要があります。

すべてのパーティションに同じストレージエンジン  パーティション化されたテーブルのすべてのパーティションは、同じストレージエンジンを使用する必要があり、それがテーブルが全体として使用する同じストレージエンジンである必要があります。また、テーブルレベルでエンジンを指定しない場合は、パーティション化されたテーブルを作成または変更するときに次のいずれかを行う必要があります。

  • 任意のパーティションまたはサブパーティションにエンジンを指定しない

  • すべてのパーティションまたはサブパーティションにエンジンを指定する

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

このセクションでは特に、パーティショニング式で使用される関数に関連する、MySQL パーティショニングの制限について説明します。

次の表に示されている MySQL 関数のみがパーティショニング式で許可されます。

ABS()CEILING() (CEILING() および FLOOR()を参照してください)DAY()
DAYOFMONTH()DAYOFWEEK()DAYOFYEAR()
DATEDIFF()EXTRACT() (WEEK 指定子付きの EXTRACT() 関数を参照してください)FLOOR() (CEILING() および FLOOR()を参照してください)
HOUR()MICROSECOND()MINUTE()
MOD()MONTH()QUARTER()
SECOND()TIME_TO_SEC()TO_DAYS()
TO_SECONDS()UNIX_TIMESTAMP() (TIMESTAMP カラムで、MySQL 5.6.1 から許可され、MySQL 5.6.3 から完全にサポートされています)WEEKDAY()
YEAR() YEARWEEK()

MySQL 5.6 では、TO_DAYS()TO_SECONDS()、および YEAR() 関数で範囲最適化を使用できます。また、MySQL 5.6.3 以降では、UNIX_TIMESTAMP() はパーティショニング式で単調として扱われます。詳細は、セクション19.4「パーティションプルーニング」を参照してください。

CEILING() および FLOOR()  これらの各関数は、正確な数値型 (INT 型または DECIMAL 型のいずれかなど) の引数を渡された場合にのみ整数を返します。これはたとえば、次の CREATE TABLE ステートメントがここで示すようにエラーで失敗することを意味します。

mysql> CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )( -> PARTITION p0 VALUES IN (1,3,5), -> PARTITION p1 VALUES IN (2,4,6) -> );ERROR 1490 (HY000): The PARTITION function returns the wrong type

WEEK 指定子付きの EXTRACT() 関数 EXTRACT() 関数によって返される値は、EXTRACT(WEEK FROM col) として使用されるときに、default_week_format システム変数の値に依存します。このため、MySQL 5.6.2 以降では、EXTRACT() は、単位を WEEK として指定するときに、パーティショニング関数として許可されなくなりました (Bug #54483)。

これらの関数の戻り型についての詳細は、セクション12.6.2「数学関数」およびセクション11.2「数値型」を参照してください。

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

MySQL 5.6.5 以前では、DML または DDL ステートメントを実行するときにテーブルレベルロックを実際に実行する MyISAM などのストレージエンジンの場合、パーティション化されたテーブルに影響するそのようなステートメントがテーブルに全体としてロックを適用していました。つまり、ステートメントが完了するまですべてのパーティションがロックされました。MySQL 5.6.6 はパーティションロックプルーニングを実装し、これによって多くの場合に不必要なロックが排除されます。MySQL 5.6.6 以降では、パーティション化された MyISAM テーブルに対して読み取りまたは更新を行うほとんどのステートメントで、影響を受けるパーティションのみがロックされます。たとえば、MySQL 5.6.6 より前は、パーティション化 MyISAM テーブルからの SELECT でテーブル全体がロックされました。MySQL 5.6.6 以降は、SELECT ステートメントの WHERE 条件を満たす行を実際に含むパーティションのみがロックされます。これには、パーティション化された MyISAM テーブルに対する同時操作の速度および効率を向上させる効果があります。この改善は、多く (32 以上) のパーティションを持つ MyISAM テーブルを操作するときに特に顕著になります。

この動作の変更は、行レベルロックを使用し、パーティションプルーニングの前にロックを実際に実行しない (または実行する必要がない)、InnoDB などのストレージエンジンを使用するパーティション化されたテーブルに影響するステートメントには影響しません。

次のいくつかの段落では、テーブルレベルロックを使用するストレージエンジンを使用するテーブルに対する、さまざまな MySQL ステートメントへのパーティションロックプルーニングの影響について説明します。

DML ステートメントへの影響

SELECT ステートメント (union または join を含むものを含む) が、実際に読み取る必要があるパーティションのみをロックするようになります。これは SELECT ... PARTITION にも適用されます。

UPDATE は、パーティショニングカラムが更新されないテーブルにのみロックをプルーニングします。

REPLACE および INSERT は、挿入または交換される行を持つパーティションのみをロックするようになります。ただし、AUTO_INCREMENT 値がパーティショニングカラム用に生成される場合は、すべてのパーティションがロックされます。

INSERT ... ON DUPLICATE KEY UPDATE は、パーティショニングカラムが更新されないかぎりプルーニングされます。

INSERT ... SELECT は、読み取る必要があるソーステーブル内のパーティションのみをロックするようになります (ターゲットテーブル内のすべてのパーティションはロックされます)。

注記

INSERT DELAYED はパーティション化されたテーブルではサポートされません。

LOAD DATA ステートメントによってパーティション化されたテーブルに適用されるロックはプルーニングできません。

パーティション化されたテーブルのパーティショニングカラムを使用する BEFORE INSERT または BEFORE UPDATE トリガーが存在する場合は、このテーブルを更新する INSERT および UPDATE ステートメントでのロックをプルーニングできないことを意味します (トリガーがその値を変更する可能性があるため)。テーブルのパーティショニングカラムに対する BEFORE INSERT トリガーが存在する場合は、INSERT または REPLACE によって設定されるロックをプルーニングできないことを意味します (BEFORE INSERT トリガーが、行が挿入される前に行のパーティショニングカラムを変更し、それによって行が本来のものとは異なるパーティションに強制的に挿入される可能性があるため)。パーティショニングカラムでの BEFORE UPDATE トリガーは、UPDATE または INSERT ... ON DUPLICATE KEY UPDATE によって適用されるロックをプルーニングできないことを意味します。

影響を受ける DDL ステートメント

CREATE VIEW でロックが行われなくなります。

ALTER TABLE ... EXCHANGE PARTITION がロックをプルーニングするようになります。交換されるテーブルおよび交換されるパーティションのみがロックされます。

ALTER TABLE ... TRUNCATE PARTITION がロックがプルーニングするようになります。空にされるパーティションのみがロックされます。

ALTER TABLE ステートメントは引き続きテーブルレベルでメタデータロックを行います。

その他のステートメント

LOCK TABLES はパーティションロックをプルーニングできません。

CALL stored_procedure(expr) はロックプルーニングをサポートしますが、expr を評価する際はしません。

DO および SET ステートメントはパーティショニングロックプルーニングをサポートしません。

関連キーワード:  パーティション,PARTITION,テーブル,TABLE,VALUES,パーティショニング,NULL,THAN,LESS,