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

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

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

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

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

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

-MAXVALUE <= intval <= MAXVALUE

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

[LINEAR] KEY、RANGE 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 パーティショニングは、MERGE、CSV、または 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 および p1 の WHERE 条件に一致する行のみを選択します。この場合、MySQL はテーブル t のほかのパーティションをチェックしません。これにより、検査するパーティションが事前にわかっているときにクエリー速度が大幅に向上することがあります。パーティション選択は、データ変更ステートメント (DELETE、INSERT、REPLACE、UPDATE、LOAD DATA、および LOAD XML) でもサポートされます。詳細および例については、これらのステートメントの説明を参照してください。

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

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

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

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