無料スクリプト配布のPHP.TO   PHPの実用的なtips PHPマニュアル MySQLマニュアル Apacheマニュアル PostgreSQLマニュアル マニュアル検索    

第15章 パーティショニング

コンパイル時の不手際のため、MySQL 5.1.12のバイナリ配布にはNDBクラスタやパーティショニングは含まれませんでした。ご不便をお掛けし恐縮です。バージョン5.1.14.へ更新してください。ソースからコンパイルする場合には、--with-ndbcluster--with-partitionオプションとともにconfigureを実行して下さい。

この章ではMySQL 5.1 において実装される ユーザ定義パーティショニング について述べています。

パーティショニングの概要およびコンセプトについては 項15.1. 「MySQL パーティショニングの概要」 を参照してください。

MySQLは 項15.2. 「パーティショニングのタイプ」 で述べられている数種類のパーティショニングのほか、項15.2.5. 「サブパーティショニング」 で説明されているサブパーティショニングもサポートしています。

既存のパーティショニングされたテーブルへの、パーティション追加、削除、変更に関しては 項15.3. 「パーティショニング管理」 を参照してください。

パーティショニングされたテーブルと使用するテーブルメンテナンスコマンドについては、項15.3.3. 「パーティションのメンテナンス」 を参照してください。

重要バージョン 5.1.6 以前の MySQL で作成されたパーティショニングされたテーブルは 5.1.6 版以降の MySQL Server では読み取れません。加えて、INFORMATION_SCHEMA.TABLESテーブルが5.1.6サーバで存在している場合、使用不可能です。MySQL 5.1.7 以降、サーバによって適合しないパーティショニングされたテーブルの存在を知らせる警告が発せられます。

重要MySQL 5.1.5 以前で作成されたパーティショニングされたテーブルを使用している場合、MySQL 5.1.6 以降にアップグレードする前に 必ず 項C.1.11. 「Changes in release 5.1.6 (01 February 2006)」 を参照して推奨されている追加情報を入手してください。

MySQL 5.1でのパーティショニングの実装はまだ開発途中です。MySQL パーティショニングに関して判明している問題などに関しては、項15.5. 「パーティショニングの制約と制限」 を参照してください。

また、パーティショニングされたテーブルを使用して作業をこなす際に以下の情報を活用してください。

追加情報

  • MySQL パーティショニングフォーラム

    以下は MySQL のパーティショニング技術で研究・実験したいユーザのための公式ディスカッションフォーラムです。MySQL開発者等からの発表や更新を記載しています。このフォーラムはパーティショニング開発・レポートチームによってモニターされています。

  • Mikael Ronström のブログ

    MySQL パーティショニングデザイナー兼リード開発者 Mikael Ronström が MySQL のパーティショニングや MySQL クラスタに関する情報を頻繁に掲載・更新しています。

  • PlanetMySQL

    MySQL 関連のブログ。MySQL を使用しているユーザにとって有用な情報が記載されています。MySQL のパーティショニング作業を行っているユーザ等が更新するブログへのリンクが記載されていますので、頻繁にチェックするか、自身のブログを追加する場合にリンクにアクセスすることをお勧めします。

MySQL 5.1バイナリは http://dev.mysql.com/downloads/mysql/5.1.html で提供されています。ただし、最新のバグフィックスと追加情報に関しては、BitKeeper 庫からソースを取得できます。パーティショニングを有効化するには、--with-partition オプションを使用してサーバをコンパイルしてください。MySQL の構築に関する追加情報には、項2.9. 「ソースのディストリビューションを使用した MySQL のインストール」 を参照してください。パーティショニングが有効化されている MySQL 5.1 構築をコンパイルする上で問題が発生する場合は、MySQLパーティショニングフォーラムを参照し、すでに解決策が投稿されていない場合そこでヘルプを要求してください。

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

このセクションでは MySQL 5.1 パーティショニングの概要のコンセプト説明を提供しています。

パーティショニングの制限またはフィーチャーされている限界については 項15.5. 「パーティショニングの制約と制限」 を参照してください。

SQL 基準は実際のデータ保存に関するガイダンスをあまり提供していません。SQL 言語自体、スキーマ、テーブル、行、そしてカラム等の基盤となるデータ構造やメディアとは独立して作動することを目的としています。それにもかかわらず、進んでいるデータベースマネージメントシステムのほとんどが何らかの方法を使ってファイルシステム、ハードウェア、もしくはその両方において特定のデータを保存するための実際のロケーションを決定づける手段を発展させてきました。MySQL では、InnoDB ストレージエンジンはテーブルスペースの概念をサポートしてきました。MySQL サーバも、パーティショニングが可能になる前から異なる実際のディレクトリを指定して異なるデータベースの保存を行うようコンフィギャすることが可能でした。(方法に関しては、項6.6.1. 「シンボリックリンクの使用」 を参照してください)

Partitioningはこの概念を進化させたもので、必要に応じて大まかに設定したルールに従い、各々のテーブルの一部分をファイルシステム上で分布することを可能にしています。結果的に、テーブルの異なる一部分は異なるテーブルとして別々のロケーションに保存されます。データのパーティショニングをとりおこなうユーザによって選択されたルールは関数 と呼ばれ、 MySQL では係数、レンジや値のリストに対する照合、内部ハッシュファンクション、もしくはリニアハッシュファンクションになります。関数はユーザによって指定されたパーティショニングの種類によって選択され、ユーザによって提供された表現の値をパラメータとして取り入れます。この表現は整数カラム値、もしくは一つまたは複数のカラム値に対して働く関数が整数を返していることが考えられます。この表現の値はパーティショニング関数へ渡され、その特定のレコードが記憶されるべきパーティションを示す整数値を返します。この関数はコンスタントでもランダムであってもいけません。クエリを含んでいないかもしれませんが、実質MySQL上有効などのSQL表現も使用可能です。ただし、MAXVALUE (可能な最大のポジティブ整数)未満のポジティブ整数を返す表現である必要があります。パーティショニング関数の例はこの章の後部に登場するパーティショニング種類のディスカッション( 項15.2. 「パーティショニングのタイプ」 を参照してください)のほかに、パーティショニング構文の記述に含まれています(項12.1.8. 「CREATE TABLE 構文」 を参照してください)。

これは 水平パーティショニング — と称されていて、テーブル内の異なる行が異なる実際のパーティショニングに割り当てられることが有ります。MySQL 5.1 は、テーブル内の異なるカラムが実際に異なるパーティショニングに割り当てられる、垂直パーティショニング をサポートしていません。現在、垂直パーティショニングをMySQL 5.1に組み込む予定はありません。

パーティショニングのサポートはMySQL 5.1の-max版に含まれています(つまり、5.1 -maxバイナリは--with-partitionで構築されます。MySQL バイナリがパーティショニングサポートで構築されている場合、有効化するための追加作業は必要ありません(例えば、my.cnfファイルへの特殊エントリーは要求されません。)ユーザのMySQLサーバがパーティショニングをサポートしているか否かは、以下のようなSHOW VARIABLESコマンドを使用して確認することができます。

mysql> SHOW VARIABLES LIKE '%partition%';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| have_partitioning | YES   |
+-------------------+-------+
1 row in set (0.00 sec)

妥当な SHOW VARIABLES の出力上に上記のように YES 値が have_partitioning 変数と現れない場合、ユーザの MySQL はパーティショニングをサポートしていません。

MySQL 5.1.6以前では、この変数はhave_partition_engineと名づけられていました。(Bug#16718)

パーティショニングされたテーブルを作成するには、MySQLサーバにサポートされるほとんどの記憶エンジンを使用することができます。MySQLパーティショニングエンジンは別の層で作動しており、これらのどのエンジンとも対話できます。MySQL 5.1では、同じパーティショニングされたテーブルのパーティショニングは同じ記憶エンジンを使用していなければいけません。例えば、MyISAMを1つのパーティションに使い、別のパーティションにInnoDBを使用することはできません。ただし、同じMySQLサーバもしくは同じデータベース上で異なるパーティショニングされたテーブルに異なる記憶エンジンを使用することを阻むものはありません。

:MySQL パーティショニングはMERGEもしくはCSV記憶エンジンと使うことはできません。MySQL 5.1.15に始まり、FEDERATEDテーブルもパーティショニングできません (Bug#22451)。MySQL 5.1.6より前では、BLACKHOLE記憶エンジンを使用してパーティショニングされたテーブルを作ることは不可能でした (Bug#14524)。KEY を使用してのパーティショニングは NDBCluster 記憶エンジンを使用することでサポートされていますが、MySQL 5.1 内のクラスタテーブルでは他の種類のユーザによって定義されるパーティショニングはサポートされません。

パーティショニングテーブルに特定の記憶エンジンを使用するには、非パーティショニングされたテーブルでそうするように[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.1 では効果がありません。)

:パーティショニングはテーブルの全データとインデックスに対して適用されます。インデックスのみ、またはvice versaテーブルの1部分のみをパーティショニングすることはできません。

各パーティションのデータやインデックスは、パーティショニングされたテーブル作成のために使用されるCREATE TABLEステートメントの partitioning 節のDATA DIRECTORYINDEX DIRECTORYオプションを使用して、特定のディレクトリに割り当てることができます。それに加え、MAX_ROWSMIN_ROWSは各パーティショニングに記憶可能な最大と最低の数値を割り出すことができます。これらのオプションについては、項15.3. 「パーティショニング管理」 を参照してください。

パーティショニングの利点にはこういったものが含まれます。

  • 1つのディスクもしくはファイルシステムのパーティションで記憶できる量よりも多くのデータをテーブルで記憶することができます。

  • 有用性を失うデータはそのデータのみを含んでいるパーティションを消去することでテーブルから取り除くことができます。また逆に、新しいデータを追加するプロセスは、そのデータ特有のパーティションを用意することで大きく援助することができるケースがあります。

  • ある特定のWHERE節を満たすデータがが1つまたは1つ以上のパーティションのみで記憶されることによって、いくつかのクエリは最適化されます。これにより、検索から残りのパーティションを除外します。パーティショニングされたテーブル作成後にパーティショニングを変更できるため、パーティショニングスキームが最初に設定された当初は現れなかった頻繁なクエリを促進させるためにデータを再編成することができます。この機能は パーティションの刈り込み とも呼ばれており、MySQL 5.1.6で実装されました。追加情報に関しては 項15.4. 「パーティションの刈り込み」 を参照してください。

他のパーティショニングによるベネフィットが次のリストの記述されています。これらの特徴は現MySQLパーティショニングには実装されていませんが、優先事項のトップにあります。

  • SUM()COUNT() といった集約関数を含むクエリは、簡単に並列化させることができます。そのようなクエリの単純な例:SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;.「並列化」とは、各パーティションで同時にクエリを作動させることが可能で、且つ最終結果は全パーティションの結果の合計として取得することができるという意味を含んでいます。

  • 複数のディスク上でデータシークを広めるために、さらに優れたクエリ処理能力を取得できます。

パーティショニングの開発は続いていますので、引き続きこのセクションと章をチェックしてください。

15.2. パーティショニングのタイプ

このセクションではMySQL 5.1で提供されているパーティショニングのタイプについて記述されています。これらは:

  • RANGE パーティショニング:あるレンジに当てはまるカラム値に対するパーティションに行を割り当てます。項15.2.1. 「RANGE パーティショニング」 を参照してください。

  • LIST パーティショニング:レンジによるパーティショニングと似ていますが、離散的値と合致するカラムに対してパーティションが選択されます。項15.2.2. 「LIST パーティショニング」 を参照してください。

  • HASH パーティショニング:テーブルに挿入される行のカラム値に作用する、ユーザによって定義された表現が返す値に対してパーティションが選択されます。関数は、MySQL内で非ネガティブ整数値を生み出す有効な表現で構成されます。項15.2.3. 「HASH パーティショニング」 を参照してください。

  • KEY パーティショニング:ハッシュによるパーティショニングと似ていますが、評価されるひとつか1つ以上のカラムが提供され、MySQL サーバは自身のハッシュ関数を提供している。MySQL に提供されるハッシュ関数はカラムデータタイプに左右されない整数の値を約束するため、これらのカラムは整数値以外の値を含むことができます。項15.2.4. 「KEY パーティショニング」 を参照してください。

非常に一般的なデータベースパーティショニングは、日付によってデータを分けることで行われます。いくつかのデータベースシステムは明確なデータパーティショニングをサポートしています。これは、MySQL 5.1では実装されません。ただし、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 の他のタイプのパーティショニングは整数値もしくは 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() 関数での使用に対して最適化されています。ただし、整数や NULL を返す日付・時間関数を使用できます。たとえば、WEEKDAY()DAYOFYEAR()、または MONTH() を使用することができます。詳細については、項11.5. 「日付時刻関数」 を参照してください。

— は、使用されているパーティショニングの種類によらず、— は重要で、パーティショニングは常に 0 に始まり自動的且つシーケンスにしたがって作成されます。新しい行がパーティショニングされたテーブルに挿入された時、正しいパーティションを識別するのに使用されるのはこれらのパーティション番号です。例えば、ユーザのテーブルが4つのパーティションを使用している場合、これらのパーティションには 012、そして 3 と番号付けされます。RANGELIST パーティショニング型に関しては、各パーティション番号ごとにパーティションが定義されていることが必要です。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

これは mypartMyPart の違いをMySQLが察知できないために失敗します。

このテーブルのパーティションの数を指定する時、それは正の値であって、ゼロで始まらない、ゼロではない整数文字であり、0.8E+016-2 は整数として成立しても使用することはできません。(MySQL 5.1.12からは、小数点の分数は切り捨てられるのではなく、完全に使用不可能となりました。)

続くセクションでは、各パーティショニング型を作成するためのあらゆる構文を提供しているわけではありません。追加情報は、項12.1.8. 「CREATE TABLE 構文」を参照してください。

15.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
);

このテーブルは、ユーザのニーズによってさまざまな方法でレンジによるパーティショニングを行うことができます。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)
);

このパーティショニングスキーマでは、p0 パーティションに記憶されるのは店舗1-5で働く店員を含む行で、p1 パーティションに記憶されるのは店舗6-10の店員になります。各パーティションは小から大まで順番どおりにパーティションが分けられていることに注目してください。これは PARTITION BY RANGE 構文の要求です。switch ... case、C、Javaなどと同義であると考えていいでしょう。

(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
);

MAXVALUE はありうる最高の整数値を表しています。store_id カラム値が16かそれ以上のものは、 (定義される最高値) p3 パーティションに記憶されます。いずれ将来、— 25、30、もしくはさらに店舗が増えた時、— ALTER TABLE ステートメントを使用して21-25、26-30の店舗のために新しいパーティションを作成することができます。(方法に関しては、項15.3. 「パーティショニング管理」 を参照してください。)

同様に、雇用者コードに合わせてテーブルをパーティションすることができます。—それは、job_codeカラム値によるレンジで可能になります。例えば、— 二桁雇用コードがストア店員、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つのうち1つの DATE カラムの表現を使用することができます。例えば、社員が会社を辞めた年度ごとにパーティショニングするとします。それは、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 で記憶されています。

レンジパーティショニングは以下のような時に特に有用です。

  • old」 データを呼び出したい、もしくは削除したい。上記で記されているパーティショニングされたスキーマを使用している場合、ALTER TABLE employees DROP PARTITION p0; 1991より前に辞めた全ての従業員に関する行を削除することができます。 (詳細については、項12.1.2. 「ALTER TABLE 構文」項15.3. 「パーティショニング管理」, を参照して下さい。)行の非常に多いテーブルに関しては、DELETE FROM employees WHERE YEAR(separated) <= 1990; といったような DELETE クエリを使用して効率よく作業を行うことができます。

  • 日付や時間の値、もしくは他のシリーズからなる値を含むカラムを使用したい場合。

  • テーブルパーティショニングのために使用されるカラムに直接従属するクエリを頻繁に使用する。例えば、SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id; といったクエリを実行する場合、MySQLは p2 パーティションのみスキャンする必要があると断定します。これは残りのパーティションが WHERE 節を充たすレコードを含むことができないからです。これがどのように達成されるかについては、項15.4. 「パーティションの刈り込み」 を参照してください。

15.2.2. LIST パーティショニング

MySQLのLISTパーティショニングは多くの点でRANGEパーティショニングに似ています。RANGE によるパーティショニング同様、各パーティションは明確に定義されていなければいけません。決定的な違いは、リストパーティショニングでは、隣接する値のレンジ内のセットの1つとしてではなく、各パーティションは1セットの値のリストの中のカラム値メンバーシップによって定義・選択されます。これは PARTITION BYLIST (expr) によって実行されます。その時 expr はカラム値もしくは返される整数値に基づいたカラム値や表現であり、VALUES IN (value_list)value_list はカンマによって分けられた整数のリストになります。

:MySQL 5.1では、LIST によるパーティショニングを行う時、整数のリストのみに対して照合(そして NULL項15.2.6. 「MySQLパーティショニングの NULL 値の取り扱い」 を参照してください)をすることが可能です。

パーティショニングがRANGEによって定義されたケースとは異なり、リストパーティショニングは特定の順番で宣言される必要はありません。さらに詳しい構文に関する情報については、項12.1.8. 「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
);

(これは 項15.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)
);

これによって、テーブルから特定の地域の店舗の従業員情報を簡単に追加・削除することが可能になります。例えば、西地域の全店舗が別の会社に売られたとします。その地域で雇用されている従業員を示す全ての行は ALTER TABLE employees DROP PARTITION pWest; クエリを使用して削除することができ、これは同等の DELETE ステートメントの DELETE FROM employees WHERE store_id IN (4,12,13,14,18); よりはるかに効率よく実行することができます。

RANGEHASH パーティショニングのように、NULL か整数ではない値を持つカラムでテーブルをパーティショニングする場合、そのような値を返すカラムに基づいてパーティショニング表現を使用する必要があります。例えば、以下のように従業員データを含むテーブルが記されていたとします。

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 CHAR(1),
    store_id INT
);

この employees テーブルのバージョンでは、雇用コードは数値ではなく文字になります。各文字が特定の職種に対応しており、同様の職種についている、もしくは同じ職場の従業員は同じパーティションに含まれるよう、以下のスキーマでテーブルをパーティショニングしたいとします。

職種・職場雇用コード
管理D, M, O, P
営業B, L, S
技術A, E, G, I, T
事務K, N, Y
サポートC, F, J, R, V
割り振りなし

値のリストでキャラクタ値がしようできないため、これらを整数もしくは NULL 等に変換する必要があります。このため、ASCII() 関数をカラム値に使用することができます。加えて、— 異なる時間帯、ロケーションでの異なるアプリケーションの使用により、— これらのコードは大文字、もしくは小文字になりえ、「割り振られていない」 を示す「」 値は、NULL、空の文字列、もしくはスペースを表しているかもしれません。このスキーマを実装しているパーティショニングされたテーブルが以下に示されています。

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 CHAR(1),
    store_id INT
) 
PARTITION BY LIST(ASCII( UCASE(job_code) )) (
    PARTITION management VALUES IN(68, 77, 79, 80),
    PARTITION sales VALUES IN(66, 76, 83),
    PARTITION technical VALUES IN(65, 69, 71, 73, 84),
    PARTITION clerical VALUES IN(75, 78, 89),
    PARTITION support VALUES IN(67, 70, 74, 82, 86),
    PARTITION unassigned VALUES IN(NULL, 0, 32)
);

評価式がパーティションのリストに与える値として許可されていないため、照合される文字に関しては文字列ではなくASCIIコードをリストしなければいけません。ASCII(NULL)NULL を返すことに注意してください。

重要もしリストに含まれないカラム値(もしくはパーティショニング表現が返す値)に行を挿入しようとした場合、INSERT クエリはエラーを表示し、失敗に終わります。例えば、先ほど概要の説明がされた LIST リストパーティショニングスキーマでは、このクエリは失敗します。

INSERT INTO employees VALUES 
    (224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 'Q', 21);

失敗は、81 (大文字 'Q' ASCII のアスキーコード)がパーティションを定義する値のリストに含まれていないためおこります。値のリストに含まれない値を承認する、VALUES LESS THAN(MAXVALUE) と同義の パーティションリストの 「キャッチオール」 定義は存在しません。つまり、照合する全ての値は、値のリスト中に存在していなければいけません。

RANGE パーティショニングと同様に、LIST パーティショニングとキー、ハッシュパーティショニングを合わせることで合成パーティショニングを生成できます(サブパーティショニング)。項15.2.5. 「サブパーティショニング」 を参照してください。

15.2.3. HASH パーティショニング

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 キーワードの続きに数字が使用されていない場合、構文エラーとなります。

expr に対して整数を返すSQL表現を使用することもできます。例えば、雇用年度に対してパーティショニングを行いたいとします。以下のようにできます。

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;

非定数、非ランダム整数値を返すという条件をクリアしていれば、MySQLで有効な expr に対してどの関数でも表現でも使用することができます。(言い換えれば、多様性があっても、断言的でなければいけません。)ただし、行が挿入もしくは更新(削除)されるたび、この表現が評価されます。これは、特に多数の行に影響を及ぼすオペレーション(例えばバッチ挿入)を実行する際、きわめて複雑な表現はパフォーマンス問題を浮上させる可能性があります。

最も効率的なハッシュ関数では、単一のテーブルカラムに対して実行され、その値がカラム値に比例して増大、減少します。これにより、パーティションのレンジを 「pruning」 することができます。つまり、表現が基となるカラムの値に対して比例すればするほど、MySQLはその表現をハッシュパーティショニングにその分だけ効率よく用いることができます。

たとえば、date_colDATE のカラム型である場合、表現 TO_DAYS(date_col)date_col の値に対して直接比例します。これは date_col の値が変更されるたびに、表現の値が一定の割合で変更されるからです。YEAR(date_col) 表現の date_col に対する変化は、TO_DAYS(date_col) に対する変化と比べて直接的ではありません。これは、date_col 内の変化の全てが YEAR(date_col) に対して同等の変更を促すとは限らないからです。それでも、YEAR(date_col) はハッシュ関数の優れた候補となります。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 という変化をもたらします。

言い換えると、, グラフのカラム値に対して versus 表現の値が y=nx この時 n は 0 以外の定数という条件のもと直線をなぞるほど、その表現はハッシュにふさわしいものになります。これは、表現が非直線状的であればあるほど、パーティション内で割り振られるデータが不均衡になりがちであることと関係しています。

セオリーでは、「刈り込み」は1つ以上のカラム値を含む表現にも使用できますが、ふさわしい表現の特定は難しい上に、多くの時間を要します。このため、複数のカラムに対してハッシュ表現を使用することは推奨できません。

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;

t1 にレコードを挿入し、col3 値が '2005-09-15' の場合、それが記憶されるパーティションは以下の様に断定されます。

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

MySQL 5.1 は HASH パーティショニングの変形である、linear hashing もサポートしており、これはパーティショニングされたテーブルに新しい行を配置する際にさらに複雑なアルゴリズムを使用します。詳細については、 項15.2.3.1. 「LINEAR HASH パーティショニング」 を参照してください。

レコードが挿入もしくは更新されるたびに、ユーザ関数は評価されます。それは — 状況によって、— レコードが消去される際に評価されることもあります。

:パーティショニングされるテーブルに UNIQUE キーがある場合、HASH ユーザ関数や KEYcolumn_list に対してアーギュメントとして提供されたカラムは、そのキーの一部出なければいけません。例外:この制限は NDBCluster 記憶エンジンを使用しているテーブルには当てはまりません。

15.2.3.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 よりも大きい二乗を探してください。この値を 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' を持つ t1 に2つのレコードを挿入したいとします。最初のパーティション番号は以下のように決定されます。

V = POWER(2, CEILING( LOG(2,7) )) = 8
N = YEAR('2003-04-14') & (8 - 1)
   = 2003 & 7
   = 3

(3 >= 6 is FALSE: record stored in partition #3)

2番目のレコードが記憶されているパーティションの番号は以下の様に計算されます。

V = 8
N = YEAR('1998-10-19') & (8-1)
  = 1998 & 7
  = 6

(6 >= 6 is TRUE: additional step required)

N = 6 & CEILING(8 / 2 - 1) 
  = 6 & 3
  = 2

(2 >= 6 is FALSE: record stored in partition #2)

リニアハッシュによるパーティショニングの利点は、パーティションの追加、削除、結合、そして分裂のスピードアップが図れることです。これは、大量のデータ(テラバイト級)を含むテーブルを取り扱う際に、効果的です。欠点は、通常のハッシュパーティショニングを使用した時に比べデータがパーティションの間で不均等に割り振られていることがあります。

15.2.4. KEY パーティショニング

キーによるパーティショニングはハッシュによるパーティショニングと似ていますが、ハッシュパーティショニングがユーザによって定義された表現を使用するところ、キーパーティショニングのハッシュ関数は MySQL サーバによって提供されます。MySQL クラスタはこのために MD5() を使用します。他のストレージエンジンを使用しているテーブルには、サーバは自身の PASSWORD() アルゴリズムに基づいた内部ハッシュ関数を使用します。

CREATE TABLE ... PARTITION BY KEY の構文ルールは、ハッシュによってパーティショニングされたテーブルを作成するものと類似しています。最大の相違点は

  • HASH よりも KEY が使用されることにあります。

  • KEY は 1 以上のカラム名のみとります。MySQL 5.1.5に始まり、パーティショニングキーとして使用されるカラムはテーブルのプライマリキーの一部もしくは前部を構成しなければいけません。これは、テーブルにプライマリキーがある場合のみです。

    MySQL 5.1.6に始まり、KEY は 0 以上のカラム名をとります。パーティショニングキーとしてカラム名が特定されていない場合、存在する場合に限ってテーブルのプライマリキーが使用されます。たとえば、以下の CREATE TABLE ステートメントはMySQL 5.1.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 TABLEINFORMATION_SCHEMA.PARTITIONS テーブルの PARTITION_EXPRESSION カラムで記されていなくても同じです。

    他のパーティショニングタイプと違い、KEY によるパーティショニングに使用されたカラムは整数や NULL 値に制限されません。例えば、以下の CREATE TABLE ステートメントは有効です。

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

    他のパーティショニングの種類が指定された場合、先のステートメントは有効 ではありません。(:この場合、単純に PARTITION BY KEY() を使用すれば、有効である上 PARTITION BY KEY(s1) と同等の効果となります。これは、s1 がテーブルのプライマリキーとなるからです。

    この件の詳細については 項15.5. 「パーティショニングの制約と制限」 を参照してください。

    :MySQL 5.1.6に始まり、NDB Cluster ストレージエンジンを使用しているテーブルは、テーブルのプライマリキーをパーティショニングキーとして、KEY によって暗黙にパーティショニングされています。クラスタテーブルに明確にプライマリキーがない場合、各クラスタテーブルの NDB ストレージエンジンによって生成された 「hidden」 プライマリキーがパーティショニングキーとして使用されます。

    重要NDB Cluster 以外のMySQLストレージ エンジンを使用しているキーパーティショニングされたテーブルは、ALTER TABLE DROP PRIMARY KEY を実行することができません。なぜなら、実行した場合は以下のエラーテキストが現れるからです:ERROR 1466 (HY000): Field in list of fields for partition function not found in table。これは KEY によってパーティショニングされたMySQLクラスタテーブルにとっては問題になりません。その場合、「hidden」プライマリキーをテーブルの新しいパーティショニングキーとしてテーブルが再構築されます。章 14. MySQL Cluster を参照してください。

リニアキーを使用してテーブルをパーティショニングすることもできます。ここに単純な例を記します。

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

LINEAR を使用するのは KEY パーティショニングに対しても HASH パーティショニングに対しても同様の効果をもたらします。この時パーティショニングはモジュロ算術よりも二乗アルゴリズムを使用してパーティショニング番号が派生されます。アルゴリズムとその意味合いについては、 項15.2.3.1. 「LINEAR HASH パーティショニング」 を参照してください。

15.2.5. サブパーティショニング

サブパーティショニング、— もしくは 複合パーティショニング — はパーティショニングされたテーブルのパーティションをさらに分けることを指します。例えば、以下の 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.1 では、RANGELIST によってパーティショニングされたテーブルをさらにサブパーティショニングすることが可能です。サブパーティショニングは HASH または 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 節は(最低でも) サブパーティションの名称を含んでいなければいけません。でなければ、サブパーティションに要求どおりのオプションを設定するか、そのオプションのデフォルト設定にもどします。

  • MySQL 5.1.7 以前では、サブパーティションの名称は各パーティション内ではユニークである必要がありましたが、テーブル全体の中でユニークである必要はありませんでした。MySQL 5.1.8 に始まり、サブパーティション名称はテーブル全体においてユニークであることが必要になりました。たとえば、以下の CREATE TABLE ステートメントはMySQL 5.1.8以降有効です。

    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
            )
        );
    

    (以前のステートメントは MySQL 5.1.8. 以前でも有効です。)

サブパーティショニングはデータやインデックスを複数のディスク上分布するために特に大きなテーブルと使用することができます。例えば、/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つに分けられています。これは別のディスクをデータと各サブパーティション専用に割り当て、(s0as0b) p0 パーティションを作成する。言い換えると:

    • サブパーティション s0a のデータは /disk0 に記憶されています。

    • サブパーティション s0a のインデックスは /disk01 に記憶されています。

    • サブパーティション 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年から始まった購入のデータ量がデフォルト位置内で支えきれなくなった時、ALTER TABLE ... REORGANIZE PARTITION ステートメントを使用してそれらの行は移動させることができます。詳細については、項15.3. 「パーティショニング管理」 を参照してください。

15.2.6. MySQLパーティショニングの NULL 値の取り扱い

MySQLでのパーティションは、カラム値であろうと、ユーザによって提供された表現であろうと、NULL をパーティショニング表現の値として禁じるようなことは一切しません。NULL 値を、整数を生み出す表現の値として使用することが許可されていますが、NULL は数値でないことを覚えておいてください。MySQL5.1.8より、パーティションは NULL を全ての非 NULL 値より少ないものと認めます。これは、ORDER BY でも同じです。

これにより、NULL の取り扱いは異なるパーティショニングの種類によって、予期せぬ事態を招くことがあります。これにより、この章では各MySQLのパーティショニングのタイプが、行が記憶されるパーティションを選択するさい、どのように NULL 値を取り扱うかを紹介し、例を取り上げます。

パーティションを判定するカラム値が NULL となるよう RANGE によりパーティショニングされたテーブルに行を挿入した場合、 行は最も低いパーティションに挿入されます。例えば、以下の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 t1 (
    ->     c1 INT, 
    ->     c2 VARCHAR(20)
    -> ) 
    -> PARTITION BY RANGE(c1) ( 
    ->     PARTITION p0 VALUES LESS THAN (-5), 
    ->     PARTITION p1 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> 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)

どのパーティションに行が記憶されているかは、ファイルシステムを検査し、パーティションと対応している .MYD ファイルのサイズを比較することで割り出すことができます。

/var/lib/mysql/test> ls -l *.MYD
-rw-rw----  1 mysql mysql 20 2006-03-10 03:27 t1#P#p0.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 03:17 t1#P#p1.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 03:17 t1#P#p2.MYD
-rw-rw----  1 mysql mysql 20 2006-03-10 03:27 t2#P#p0.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 03:17 t2#P#p1.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 03:17 t2#P#p2.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 03:17 t2#P#p3.MYD

(パーティションのファイルは table_name#P#partition_name.extension フォーマットにより名づけられます。t1#P#p0.MYD はテーブル t1 のパーティション p0 データが記憶されているところです。: MySQL 5.1.5以前には、これらのファイルはそれぞれ t1_p0.MYDt2_p0.MYD と名づけられていました。この変化が更新に対してどういう影響を及ぼすかは、項C.1.11. 「Changes in release 5.1.6 (01 February 2006)」 とバグ#13437を参照してください。)

これらの行が各テーブルの最も低いパーティションに記憶されていたことを証明するには、これらのパーティションを削除し、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 に関する情報については、項12.1.2. 「ALTER TABLE 構文」 を参照してください。)

これはSQL関数を使用するパーティショニング表現に対しても同様です。例えば、以下のようなテーブルがあるとします。

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 を返します。NULLdt カラム値を持つ行は、パーティショニング表現がそれ以外の値よりも少ない値に評価されたかのように扱われるため、 p0 パーティションに挿入される。

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 9

mysql> INSERT INTO ts1 VALUES (NULL, 'mothra');
ERROR 1504 (HY000): Table has no partition for value NULL

唯一 0 から 8 の間に c1 の値が含まれる行が ts1 に挿入可能です。NULL9 の様に、このレンジ外に位置します。NULL を含む値リストを持つテーブル ts2ts3 を、以下のとおり作成することができます。

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)、以下同様)。テーブル ts2ts3 両方に NULL を持つ行をカラム c1 に挿入することができます。

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)

ファイルシステムを検査することで、テーブル ts2 のパーティション p3 にはこれらステートメントの最初のものが挿入され、テーブル ts3 のパーティション p1 には2番目のステートメントが挿入されたことを確認することができます。

/var/lib/mysql/test> ls -l ts2*.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 10:35 ts2#P#p0.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 10:35 ts2#P#p1.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 10:35 ts2#P#p2.MYD
-rw-rw----  1 mysql mysql 20 2006-03-10 10:35 ts2#P#p3.MYD

/var/lib/mysql/test> ls -l ts3*.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 10:36 ts3#P#p0.MYD
-rw-rw----  1 mysql mysql 20 2006-03-10 10:36 ts3#P#p1.MYD
-rw-rw----  1 mysql mysql  0 2006-03-10 10:36 ts3#P#p2.MYD

新しい例のとおり、これらファイルをリストするため、ユニックスオペレーティングシステム上で bash シェルを使用します。この件に関しては、ユーザのプラットフォームが提供するものを使用してください。たとえば、WindowsのOS上でDOSシェルを使用している場合、最後のリストと等価のものは C:\Program Files\MySQL\MySQL Server 5.1\data\test ディレクトリ内の dir ts3*.MYD コマンドを起動することで取得できる可能性があります。

このセクションの前部で紹介したとおり、削除し、SELECT を実行することで値の記憶に使用されたパーティションを確認することができます。.

NULLHASHKEY によってパーティショニングされたテーブルとは同様に取り扱われます。こういったケースでは、NULL 値を生み出すパーティショニング表現は返される値が0であるかのように扱われます。この動作を確認するには、HASH によってパーティショニングされたテーブルを作成し、適当な値を含むレコードで実装させることでファイルシステムへの影響を調べることができます。たとえば、以下のステートメントで、test データベース内のテーブル th が作成されたとします。

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

Linux上でのMySQLのRPMインストールを想定すると、このステートメントは2つの .MYD ファイルを /var/lib/mysql/test につくり、それは bash シェルで以下の様に現れます。

/var/lib/mysql/test> ls th*.MYD -l
-rw-rw----  1 mysql mysql 0 2005-11-04 18:41 th#P#p0.MYD
-rw-rw----  1 mysql mysql 0 2005-11-04 18:41 th#P#p1.MYD

各ファイルのサイズが0 バイトであることに注目してください。では、c1 カラム値が NULL の行を th に挿入して、その行が挿入されたことを認証してください。

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

mysql> SELECT * FROM th;
+------+---------+
| c1   | c2      |
+------+---------+
| NULL | mothra  |
+------+---------+
1 row in set (0.01 sec)

どの整数 N にとっても、NULL MOD N の値は常に NULL になります。HASHKEY によってパーティショニングされたテーブルに関しては、この結果は正しいパーティションを 0 として確定するために扱われます。システムシェルに戻ると、(このため bash を使用します)、再度データファイルをリストすることで、値が最初のパーティションに挿入されたことを確認できます。(デフォルトで p0 と名づけられる)

var/lib/mysql/test> ls *.MYD -l
-rw-rw----  1 mysql mysql 20 2005-11-04 18:44 th#P#p0.MYD
-rw-rw----  1 mysql mysql  0 2005-11-04 18:41 th#P#p1.MYD

他のデータファイルに影響することなく(ディスク上でサイズを増大)、th#P#p0.MYD ファイルのみ、INSERT ステートメントが改良したことが確認できます。

重要MySQL 5.1.8以前では、RANGE パーティショニングは、配置の断定に関して、パーティショニング表現値 NULL をゼロとして扱いました。(これを回避する方法は、ヌルを許容しないテーブルをデザインすることで、通常はカラムを NOT NULL と宣言することで実行しました)。この前期の動作による RANGE パーティショニングスキーマがある場合、MySQL 5.1.8以降にアップグレードする時再実装しなければいけません。

15.3. パーティショニング管理

MySQL 5.1 はパーティショニングされたテーブルの改良方法をいくつか提供しています。存在するパーティションを追加、削除、再定義、結合、そして分離させることができます。これらのアクションの全てが ALTER TABLE コマンドのパーティショニング拡張によって行うことができます。 (構文の定義については項12.1.2. 「ALTER TABLE 構文」 を参照してください)パーティショニングされたテーブルやパーティションそれ自体の情報を取得する方法もあります。続くセクションでこれらのトピックを紹介します。

:MySQL 5.1 では、パーティショニングされたテーブルのパーティションは全て同じ数のサブパーティションを持たなければいけません。また、一度テーブルが作成されてからサブパーティションを変更することは不可能です。

ステートメント ALTER TABLE ... PARTITION BY ... は MySQL 5.1.6 より稼動しています。MySQL 5.1 では、構文的には認められていてもステートメント事態は効果がありませんでした。

テーブルのパーティショニングスキーマを変更するには、ALTER TABLE コマンドを partition_options 節と共に使用することのみがが要求されます。この節は 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 カラム値を用い、以下のステートメントを使用してください。

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

これはテーブルを削除し CREATE TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2; を使用して再作成するのと同様の効果をテーブルの構成にもたらします。

重要MySQL 5.1.7 と MySQL 5.1 のりりーすでは、ALTER TABLE ... ENGINE = ... は影響されたテーブルの全てのパーティションを取り除く動作をしました。 MySQL 5.1.8 に始まり、このステートメントはテーブルに使用されるストレージエンジンだけを変更し、テーブルのパーティショニングスキーマは保たれます。MySQL 5.1.8以降はテーブルのパーティションを取り除くには、ALTER TABLE ... REMOVE PARTITIONING を使用してください。詳細については項12.1.2. 「ALTER TABLE 構文」 を参照してください。

15.3.1. RANGELIST パーティションの管理

パーティションの追加や削除が行われる点に関しては、レンジパーティショニングもリストパーティショニングもよく似ています。このため、これらのパーティションの管理をこのセクションで紹介します。ハッシュやキーでパーティショニングされたテーブルの使用については、項15.3.2. 「HASHKEY パーティションの管理」 を参照してください。RANGELIST パーティションの削除は追加よりも単純なので、先にこちらを紹介します。

RANGELIST によってパーティショニングされたテーブルからパーティションを削除するには、ALTER TABLE ステートメントを DROP PARTITION 節と使用することで達成できます。ここに単純な例を記します。すでにレンジで作成され、CREATE TABLEINSERT ステートメントを使用して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)

注:MySQL 5.1 では、NDBCLUSTER ストレージ エンジンは 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)

これにより、MySQL 5.1.10 では ALTER TABLE ...DROP PARTITION を実行する前にテーブルの DROP 権限があります。

テーブルの定義とパーティショニングスキーマを保持したままパーティションからデータを削除したい場合、TRUNCATE TABLE コマンドを実行してください。(詳しくは 項12.2.9. 「TRUNCATE 構文」 をご確認ください。)

データを 失わずに、テーブルのパーティションを変更する場合、ALTER TABLE ...REORGANIZE PARTITION を代わりに使用してください。REORGANIZE PARTITION に関する情報については、以下か 項12.1.2. 「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)

'1995-01-01''2004-12-31' 間の purchased カラム値を変更されたテーブルに行挿入する時、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つ重大な違いがあります。テーブルに、削除されたパーティションを定義する値リストに含まれていた値を、行挿入することができません。(項15.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 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2000));

重要レンジによりパーティショニングされたテーブルでは、パーティションのリストに新しいパーティションを追加するため、ADD PARTITION を使用することができます。このようにして、存在するパーティションのまえ、もしくは間に新しいパーティションを追加すると、以下のようなエラー表示となります。

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

同じように、LIST によってパーティショニングされたテーブルに新しいパーティションを追加することができます。例えば、以下の様に定義されたテーブルでは:

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 に割り振られているため、テーブル tt12 を値リストに含む新しいパーティションを作成することはできません。これを達成するには、p1 を削除し、np を追加してから、改良された定義の新しい p1 を作成する必要があります。ただし、以前述べたとおり、p1 に記憶れた全てのデータの損失につながります。— 大抵、ユーザの意思とはかけ離れた結果となります。また、別の解決法で、新しいパーティションを含んだテーブルのコピーを作成し、かつデータを CREATE TABLE ...SELECT ... を使用して書き込み、古いテーブルを削除し新しいテーブルの名前をつけなおすことができますが、量の多いデータを取り扱っている時など、非常に多くの時間を要することがあります。加えて、これは高い有効性が求められている状況では、あまり推奨できる手段ではありません。

MySQL 5.1.6二始まり、以下の様に1つの 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つの新しいパーティション s0s1 に分けます。p0 に記憶されていたデータを2つの PARTITION ... VALUES ... 節で記されるルールによってデータが移動させられます。これにより、s0YEAR(dob) が1960未満の、そして s1YEAR(dob) 1960 以上、1970 未満のレコードを含む行が存在します。

REORGANIZE PARTITION 節を使用して隣接するパーティションを結合することができます。以下の様に members テーブルを以前のパーティションに戻すことができます。

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

REORGANIZE PARTITION を使用してパーティションを分離、結合することでデータが失われることはありません。上記のステートメントを実行する際、MySQLは s0s1 に記憶されていたパーティションを p0 に移動させます。

REORGANIZE PARTITION の一般的な構文は

ALTER TABLE tbl_name 
    REORGANIZE PARTITION partition_list 
    INTO (partition_definitions);

ここでは、tbl_name はパーティショニングされたテーブルの名前で、partition_list はカンマによって分けられた、変更するべき存在するパーティションの名前です。partition_definitions はカンマによって分けられた、新しいパーティションの定義のリストで、CREATE TABLE で使用される partition_definitions リストと同様のルールに従います。(項12.1.8. 「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)
);

REORGANIZE PARTITIONLIST によってパーティショニングされたテーブルと使用することもできます。それでは、リストによってパーティショニングされたテーブル 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)
);

RANGELIST を使用してパーティショニングされたテーブルを再度パーティショニングする際に使用される ALTER TABLE ... REORGANIZE PARTITION に関する重要なポイントは、以下のとおりです。

  • 新しいパーティショニングスキーマを決定するのに使用される PARTITION 節は CREATE TABLE ステートメントで使用されているものに対して同じルールが適用されます。

    さらに重要なのは、新しいパーティショニングスキーマには重複するレンジや値のセットがあってはならないことです(RANGELIST によってパーティショニングされたテーブルを再編成する際に適用する)。

    :MySQL 5.1.4以前では、INTO 節内で存在するパーティションの名前を、それらのパーティションが再定義、もしくは削除されていても再利用することはできませんでした。詳細については、項C.1.13. 「Changes in release 5.1.4 (21 December 2005)」 を参照してください。

  • partition_definitions リストに含まれるパーティションのコンビネーションは、partition_list で名づけられている結合されたパーティションと同じレンジ、値のセットになります。

    たとえば、このセクションで例として使用されている members テーブル では、パーティション p1p2 は合わせて1980から1999の期間をカバーしているということになります。よって、これらパーティションのどの再編成も、最終的には同じ期間をカバーすることになります。

  • RANGE によりパーティショニングされたテーブルに関しては、隣接するパーティションのみ再編成することができます。レンジパーティションを飛び越すことはできません。

    たとえば、このセクションで使用されている members テーブルを ALTER TABLE members REORGANIZE PARTITION p0,p2 INTO ... で始まるステートメントに再編成することはできません。なぜなら、p0 1970 以前の年度をカバーしており、p2 は1990から1999をカバーするため、この二つは隣接するパーティションにはなりえません。

  • REORGANIZE PARTITION を使用してテーブルのパーティショニングのタイプを変更することはできません。それは、RANGE パーティショニングを HASH パーティショニングや、その逆 vice versa もまた不可能ということになります。また、このコマンドを使用してパーティショニング表現やカラムを変更することができます。両方のタスクを、テーブルを削除もしくは再作成せずに行う場合、ALTER TABLE ...PARTITION BY ... を使用することができます。例:

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

15.3.2. HASHKEY パーティションの管理

ハッシュやキーによりパーティショニングされたテーブルの変更はパーティショニングの設定を行う際と手順が似ており、レンジやリストによりパーティショニングされたテーブルとは違いがあります。そのため、このセクションはハッシュやキーのみによりパーティショニングされたテーブルの変更に関するトピックに絞って説明をおこないます。レンジやリストによりパーティショニングされたテーブルの追加や削除については、項15.3.1. 「RANGELIST パーティションの管理」 を参照してください。

HASHKEY によりパーティショニングされたテーブルを RANGELIST によりパーティショニングされたテーブルと同じように削除することはできません。ただし、HASHKEY パーティショニングは ALTER TABLE ...COALESCE PARTITIONコマンドを使って結合することはできます。例えば、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)

COALESCEHASH, KEYLINEAR 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;

15.3.3. パーティションのメンテナンス

MySQL では複数のパーティショニングメンテナンスタスクを行うことができます。5.1.MySQLでは CHECK TABLEOPTIMIZE TABLEANALYZE TABLE、そして REPAIR TABLEのようなコマンドをパーティショニングされたテーブル用にサポートされてはいません。 代わりに、MySQL5.1.5で実装された ALTER TABLE の拡張のいくつかを使用することができます。これらはこのような種類のオペレーションを直接1つ、もしくは複数のパーティションに実行するのに使用できます。以下のリストを参照してください。

  • REBUILD PARTITIONパーティションを再構築します。パーティションに記憶されているレコードを削除し、再度挿入するのと同じ効果があります。これはデフラグメンテーションのために有効に使えます。

    例:

    ALTER TABLE t1 REBUILD PARTITION p0, p1;
    
  • OPTIMIZE PARTITIONもしパーティションから多くの行を削除もしくは異なる長さの行を含むパーティショニングされたテーブルに変更を加えた場合、(つまり、VARCHARBLOB、またはTEXTカラムを含む)ALTER TABLE ...OPTIMIZE PARTITIONを使用してパーティションのデータファイルをデフラグすることによって使用されていないスペースを再利用できます。

    例:

    ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;
    

    OPTIMIZE PARTITION をあるパーティションで使用することは CHECK PARTITIONANALYZE PARTITION、そして REPAIR PARTITION をそのパーティションで作動させることと同義です。

  • ANALYZE PARTITION:これはパーティションのキーの分散を読み込み・記憶します。

    例:

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

    例:

    ALTER TABLE t1 REPAIR PARTITION p0,p1;
    
  • CHECK PARTITIONCHECK TABLE をパーティショニングされていないテーブルでチェックのため使用できるように、パーティションのエラーをチェックすることができます。

    例:

    ALTER TABLE trb3 CHECK PARTITION p1;
    

    このコマンドはテーブル t1 のデータやインデックスが p1 破壊されているかを知らせます。この場合、ALTER TABLE ...REPAIR PARTITIONを使用してパーティションを修復することができます。

mysqlcheckmyisamchk ユーティリティを使用してこれらのタスクを達成することができます。これらは、テーブルをパーティショニングすることによって別々の .MYI ファイルに生成されています。項7.11. 「mysqlcheck — テーブル メンテナンスと修復プログラム」 を参照してください。

15.3.4. パーティション情報の取得

このセクションでは、存在するパーティションの情報を取得するいくつかの方法を紹介します。これらは:

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

  • 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)

:MySQL 5.1の最初のリリースでは、PARTITIONS 節は、HASHKEY によってパーティショニングされたテーブルでは表示されませんでした。この問題は MySQL 5.1.6. で修正されました。

SHOW TABLE STATUS はパーティショニングされたテーブルにおいても動作します。MySQL 5.1.9に始まり、パーティショニングされていないテーブルと出力は同じですが、Create_options カラムに partitioned 文字列を含んでいます。MySQL 5.1.8 以前では、Engine カラムには必ず PARTITION の値が含まれていました。MySQL 5.1.9 に始まり、このカラムはテーブルに使用される全てのパーティションのストレージエンジンの名前を含んでいます。(このコマンドの詳細については、項12.5.4.27. 「SHOW TABLE STATUS 構文」 を参照してください。)

パーティションの情報は PARTITIONS テーブルが含まれる、INFORMATION_SCHEMA からも取得できます。項21.19. 「INFORMATION_SCHEMA PARTITIONS テーブル」 を参照してください。

MySQL 5.1.5 に始まり、EXPLAIN PARTITIONS を使用して、パーティショニングされたテーブルのどのパーティションが SELECT と関係しているかを判定することができます。PARTITIONS キーワードはクエリと照合されるれレコードを記したパーティションを表示する、EXPLAIN の出力結果に partitions カラムを追加します。

例えば、以下の様に定義・実装されたテーブル 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 の制限や規制について注意してください。

  • EXTENDEDPARTITIONS キーワードを、同じ EXPLAIN ... SELECT ステートメントで使用することはできません。これを試みると、構文エラーが発生します。

  • もし EXPLAIN PARTITIONS がパーティショニングされていないテーブルに対するクエリを診断する場合、エラーは発生しませんが、partitions カラムの値は常に NULL となります。

項6.2.1. 「EXPLAINを使用して、クエリを最適化する」 も参照してください。

15.4. パーティションの刈り込み

このセクションでは パーティションの刈り込み を紹介します。この最適化は、MySQL 5.1.6.でパーティショニングされたテーブル用に実装されています。

パーティション刈り込みのコンセプトは単純です。「合致する値が存在し得ないパーティションはスキャンしない」 というものです。例えば、以下のステートメントに定義されたパーティショニングされたテーブル 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 fname, lname, postcode, dob
    FROM t1 
    WHERE region_code > 125 AND region_code < 130; 

これを見れば、返されるはずの行が p0p3 のどちらかのパーティションに含まれていることが容易に理解できます。つまり、p1p2 パーティションのみを検索する必要があるということがわかります。そうすることによって、テーブル内のパーティションをスキャンするよりも、一致する行を探すことに時間を費やすことができます。この不必要なパーティションを 「省く」 ことを、刈り込み といいます。オプティマイザがパーティションの刈り込みをクエリの実行に使用できると、パーティショニングされていないテーブルに含まれる同じカラム定義やデータに対して行うことに比べると一段速く、クエリの実行ができます。

WHERE 状態が以下の2つになる場合、クエリオプティマイザは刈り込みを実行することができます。

  • partition_column = constant

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

ケース1の場合、オプティマイザは単純に与えられた値のパーティショニング表現を評価し、どのパーティションにその値があるかを判定、そしてそのパーティションのみを検索します。ケース2の場合、オプティマイザはリストに含まれる各値に対してパーティショニング表現を評価し一致するパーティションのリストを作成、そしてそのリストにあるパーティションだけを検索します。

刈り込みはショートレンジにも適用できます。この際、オプティマイザは値が等価のリストに変換できます。例えば、前の例では、WHERE 節は WHERE region_code IN (125, 126, 127, 128, 129, 130) に変換できます。この後、オプティマイザはリストに含まれる最初の3つの値が p1 パーティションに含まれると断定、残りの3つの値が p2 そして、他のパーティションには一致する値が含まれないと判断できるため、残りの検索を省くことができます。

この類の最適化はパーティショニング表現が等価もしくはレンジにより構成され、等価のセットに縮小できる場合、あるいはパーティショニング表現が増減する関係を表している場合に適用できます。刈り込みは DATEDATETIME カラムでパーティショニングされたテーブルに対しても適用することができます。この時、パーティショニング表現は YEAR() または TO_DAYS() 関数を使用しています。(:将来的にリリースされるMySQLには刈り込みサポートを追加する予定があります。これは、DATEDATETIME 値、整数を返し、増減に対して追加関数を行う形になります。)例えば、以下の様に定義されたテーブル t2DATE カラムでパーティショニングされていたとします。

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'; 

SELECT * FROM t2 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';

SELECT * FROM t2 WHERE YEAR(dob) 
         IN (1979, 1980, 1983, 1985, 1986, 1988);

SELECT * FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'

最後のクエリに関しては、オプティマイザは以下の様に作動します。

  1. レンジの下辺を含むパーティションを検索することができます。.

    YEAR('1984-06-21')1984 の値を生み出し、d3 パーティションで発見されます。

  2. レンジの上辺を含むパーティションを検索することができます。.

    YEAR('21.06.99')1999 の値を生み出し、d5 パーティションで発見されます。

  3. これら2つのパーティションと、それらの間にあるパーティションのみを検索します。.

    この場合、パーティション d3d4d5 のみが検索されます。残りのパーティションは安全に無視することができます。(無視されます。)

これまで、RANGE パーティショニングを含む例のみを挙げましたが、刈り込みは他のパーティショニングのタイプでも利用することができます。

LIST によってパーティショニングされたテーブルを検討します。パーティショニング表現が増減を繰り返している、以下のようなテーブル t3 を検証してみましょう。(この例では、詳細を省くため 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の値がどのパーティションに含まれているかを判定します。(r0r1) 残りの(r2r3 はスキップします)。

HASHKEY を使用してパーティショニングされているテーブルに関しては、パーティションの刈り込みは 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;

このようなクエリは全て刈り込みの対象となります。

SELECT * FROM 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) に変更されます。重要この最適化はレンジの規模がパーティションの数よりも小さい場合のみ使用されます。このクエリを検討してください。

SELECT * FROM t4 WHERE region_code BETWEEN 4 AND 8;

WHERE 節のレンジは5つの値ををカバーします(4, 5, 6, 7, 8)。しかし、t4 には4つのパーティションしか存在しません。これは、以前のクエリを刈り込みできないことを意味しています。

刈り込みは HASHKEY によってパーティショニングされたテーブルの整数カラムにのみ使用できます。例えば、テーブル t4 のクエリは、dobDATE カラムであるため、刈り込みを使用することができません。

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

ただし、テーブルが INT カラム内で年度を示す値を記憶している場合、WHERE year_col >= 2001 AND year_col <= 2005 を含むクエリは刈り込みされます。

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

このセクションでは現在MySQLパーティショニングサポートに課せられている制約と制限を紹介します。

  • MySQL 5.1.12 より、以下の生成子はパーティショニング表現で許可されていません。

    • 入れ子関数コール(例えば、func1( func2(col_name) ))といった生成子を指します。

    • 記憶された関数、ストアド プロシージャ、UDF、プラグイン。

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

  • MySQL 5.1.12 より、以下特定の MySQL 関数はパーティショニング表現で許容されていません。

    • GREATEST()

    • ISNULL()

    • LEAST()

    • CASE()

    • IFNULL()

    • NULLIF()

    • BIT_LENGTH()

    • CHAR_LENGTH()

    • CHARACTER_LENGTH()

    • FIND_IN_SET()

    • INSTR()

    • LENGTH()

    • LOCATE()

    • OCTET_LENGTH()

    • POSITION()

    • STRCMP()

    • CRC32()

    • ROUND()

    • SIGN()

    • DATEDIFF()

    • PERIOD_ADD()

    • PERIOD_DIFF()

    • TIMESTAMPDIFF()

    • UNIX_TIMESTAMP()

    • WEEK()

    • CAST()

    • CONVERT()

    • BIT_COUNT()

    • INET_ATON()

  • +×、そして / といった数的演算子はパーティショニング表現で許容されています。ただし、結果は整数値もしくは NULL でなければいけません。 ([LINEAR] KEY パーティショニングは例外となります。— 詳細については項15.2. 「パーティショニングのタイプ」 を参照してください)。

    MySQL 5.1.12 にはじまり、|&^, <<>> そして ~ といったビット演算子はパーティショニング表現では許容されていません。

  • MySQL 5.1.12 より、以下特定の MySQL 関数のみがパーティショニング表現で許容されています。

    • ABS()

    • ASCII()

    • CEILING()

    • DAY()

    • DAYOFMONTH()

    • DAYOFWEEK()

    • DAYOFYEAR()

    • EXTRACT()

    • FLOOR()

    • HOUR()

    • MICROSECOND()

    • MINUTE()

    • MOD()

    • MONTH()

    • ORD()

    • QUARTER()

    • SECOND()

    • TIME_TO_SEC()

    • TO_DAYS()

    • WEEKDAY()

    • WEEKOFYEAR()

    • YEAR()

    • YEARWEEK()

  • 重要サーバ SQL モード次第で、いくつもの MySQL 関数や演算子の結果が変更される可能性に注意してください。このため、パーティショニングされたテーブルを作成したあとモードを変更することは推奨できません。項4.2.6. 「SQL モード」 を参照してください。

  • ASCII()ORD() といった関数を使用して文字列を(たとえば CHAR あるいは VARCHAR カラム)整数に変換するのは、文字列が8-ビットキャラクタセットを使用している時のみ可能です。文字列に使用される照合順序は関連キャラクタセットのどの照合順序でも可能です。ただし、latin1_german2_cilatin2_czech_cs、そして cp1250_czech_cs などの照合順序は1対複数の変換を擁するため、使用は不可能です。

  • パーティションの最大数は 1024 になります。これはサブパーティションを含めた値です。

    もし、多くのパーティションを使用してテーブルを作成する場合(しかし上記の最大数よりも少ない場合)、以下のエラーメッセージが発生します。Got error 24 from storage engine これは、open_files_limit システム変数の値を増加させなければいけないという意味です。項B.1.2.17. 「'File' Not Found and Similar Errors」 を参照してください。

  • パーティショニングされたテーブルは外部キーをサポートしません。これは、InnoDBストレージエンジンを使用しているテーブルも含みます。

  • パーティショニングされたテーブルは FULLTEXT をサポートしません。これは、MyISAM ストレージエンジンを使用しているテーブルも含みます。

  • パーティショニングされたテーブルは GEOMETRY カラムをサポートしません。

  • MySQL 5.1.8 以降、テンポラリテーブルはパーティショニングできません。(Bug#17497)

  • MERGE ストレージ エンジンを使用しているテーブルはパーティショニングできません。

    FEDERATED テーブルのパーティショニングはサポートされていません。MySQL 5.1.15 からは、パーティショニングされた FEDERATED テーブルを作成する事は不可能になりました。将来的に、この制限をMySQLから取り除く方向で開発を進めています。

    CSV ストレージ エンジンを使ったパーティショニングはサポートされません。MySQL 5.1.12 からは、パーティショニングされた CSV テーブルを作成する事は不可能になりました。

    MySQL 5.1.6 以前では、BLACKHOLE ストレージエンジンを使用しているテーブルはパーティショニング不可能でした。

    KEY (あるいは LINEAR KEY)によるパーティショニングが、NDB ストレージエンジンでサポートされる唯一のパーティショニングです。MySQL 5.1.12 で始まり、[LINEAR] KEY以外のパーティショニングのタイプを使用してクラスタテーブルを作成するのが不可能になりました。実行するとエラーが発生します。

  • アップグレードを実行中 KEY によりパーティショニングされ、NDBCLUSTER 以外のストレージエンジンを使用しているテーブルは、いったんダンプしてからリストアする必要があります。

  • テーブルのパーティションとサブパーティションすべてが(後者の場合、存在していれば)同じストレージ エンジンを使用していなければいけません。将来的に、この制限をMySQLから取り除く方向で開発を進めています。

  • パーティショニングキーは整数カラム、もしくは整数に帰結する表現でなければいけません。カラム、もしくは表現値は NULL となりえます。(詳しくは 項15.2.6. 「MySQLパーティショニングの NULL 値の取り扱い」 をご確認ください。)

    この規制にたいする唯一の例外は[LINEAR] KEY — を 使用してパーティショニングする際に発生します。この時、パーティショニングキーとして他タイプのカラムを使用することができるのは、— MySQL の内部キーハッシュ関数はこれらの型から正しいデータ型を生成するからです。例えば、以下の CREATE TABLE ステートメントは有効です。

    CREATE TABLE tkc (c1 CHAR)
    PARTITION BY KEY(c1)
    PARTITIONS 4;
    

    この例外は BLOBTEXT カラム型に 適用されません

  • サブクエリが整数値もしくは NULL に帰結するとしても、サブクエリをパーティショニングキーとして利用することは出来ません。

  • パーティショニングされたテーブルのパーティショニング表現に使用される全てのカラムはテーブル内に存在する全てのユニークキーの一部でなければいけない。.言い換えると、テーブル内のユニークキー全てはテーブルパーティショニング表現の全てのカラムを使用しなければいけない。たとえば、以下のテーブル作成ステートメントは無効です。

    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;
    
    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;
    

    各ケースで、挙げられたテーブルはパーティショニング表現で使用されているカラム全てを含まないユニークキーを、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;
    
    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;
    

    各プライマリキーは定義によるとユニークキーなので、この規制は、テーブルにそれが含まれる場合、テーブルのプライマリキーに対しても働きます。例えば、下記の2ステートメントは無効です

    CREATE TABLE t4 (
        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 t5 (
        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 t6 (
        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 t7 (
        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;
    

    テーブルにユニークキーがない場合、— これはプライマリキーが内場合も含む — この規制は適用されません。カラム型がパーティショニングのタイプと適合している限り、パーティショニング表現内のどのカラムも使用することが可能です。

    同じ理由で、後からパーティショニングされたテーブルにユニークキーを追加することはできません。ただし、テーブルのパーティショニング表現内に含まれる全カラムがキーに含まれている場合、これは可能となります。以下にパーティショニングされたテーブルの定義が記されているとします。

    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)
        );
    

    次の ALTER TABLE ステートメントのどちらかを使用して、t_no_pk にプライマリキーを付け加えることができます。

    #  possible PK
    ALTER TABLE t_no_pk ADD PRIMARY KEY(c1);
    
    #  also a possible PK
    ALTER TABLE t_no_pk ADD PRIMARY KEY(c1, c2);
    

    ただし、次のステートメントは失敗します。なぜなら、c1 はパーティショニングキーの一部であっても、プライマリキーの一部ではないからです。

    #  fails with ERROR 1482
    ALTER TABLE t_no_pk ADD PRIMARY KEY(c2);
    

    t_no_pk はパーティショニング表現に c1 しかないため、c2 にのみユニークキーを追加使用としても、失敗に終わります。ただし、c1c2 を両方使用するユニークキーを追加することは可能です。

    これらのルールは、ALTER TABLE ... PARTITION BY を使用してパーティショニングしたいテーブルにも適用されます。以下 np_pk と定義されたテーブルを検討してください。

    CREATE TABLE np_pk (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(50),
        added DATE,
        PRIMARY KEY (id)
    );
    

    続く ALTER TABLE ステートメントはエラーで失敗します。これは added カラムがテーブル内のユニークキーの一部ではないからです。

    ALTER TABLE np_pk
        PARTITION BY HASH( TO_DAYS(added) )
        PARTITIONS 4;
    

    このステートメントは、有効になります。

    ALTER TABLE np_pk
        PARTITION BY HASH(id)
        PARTITIONS 4;
    

    np_pk の場合、パーティショニング表現の一部として使用できるカラムは id になります。パーティショニング表現内の他のカラムを使用してこのテーブルをパーティショニングしたい場合、まず必要なカラムをプライマリキーに追加するか、プライマリキー自体を破棄することでテーブルを改良しなければいけません。

    将来的に、この制限をMySQLから取り除く方向で開発を進めています。

  • サブパーティショニングは HASHKEY パーティショニングに限定されます。HASHKEY パーティショニングに対してサブパーティショニングを行うことはできません。


powered by SEO.CUG.NET