2009-10-21

MySQLパーティショニングについて（その1：基本知識編）

hamada
はてなブックマーク   livedoor clip

初めまして、リコーの濱田です。このたび私も本ブログを担当することになりました。今後ともよろしくお願いいたします。

本エントリではデータベースに関する技術トピックとして、MySQL 5.1 から導入された機能であるパーティショニングについて書こうと思います。少し長くなりそうなので、「基本知識編」「性能検証編」の2回に分けて書くことにします。

今回は「基本知識編」として、パーティショニングの概要と基本的な使い方について紹介します。

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

パーティショニングとは、事前に設定されたルールに従ってデータをパーティションと呼ばれる部分的なテーブルに分割する仕組みです。

データ挿入時には、設定ルールに従ってデータが該当するパーティションに自動的に振り分けられます。データ参照時には、オプティマイザがクエリから必要なパーティションを判断し、該当するパーティションのみにアクセスします。これらは MySQL の内部で行なわれるため、データの操作においてパーティショニングを意識する必要はありません。
partitioning
パーティショニングを利用することで、以下の利点が得られます。

  • 大量のデータを処理することによる性能上のボトルネックの発生を抑えられる
  • テーブルサイズに上限がある場合（MyISAMなど）でも、その上限を超える量のデータを格納できる

パーティションドテーブルの作成

それでは実際にパーティションドテーブル（パーティショニングされたテーブル）を作成してみます。パーティショニングにはいくつかの種類がありますが、ここでは RANGE パーティショニングについて説明します。

パーティションドテーブルの作成は簡単で、通常のテーブル作成文の後にパーティション設定ルールの記述を追加するだけです。以下の例は、複数クライアントのログ情報を月別に分けて格納するパーティションドテーブルの作成例です。

CREATE TABLE logs (
    id INT NOT NULL AUTO_INCREMENT,
    client_name VARCHAR(32) NOT NULL,
    log_data VARCHAR(1024) NOT NULL,
    logged_at DATETIME NOT NULL,
    PRIMARY KEY(id, logged_at),
    INDEX(client_name)
) ENGINE=MyISAM
PARTITION BY RANGE( TO_DAYS(logged_at) ) (
    PARTITION p200910 VALUES LESS THAN ( TO_DAYS('2009-11-01') ),
    PARTITION p200911 VALUES LESS THAN ( TO_DAYS('2009-12-01') ),
    PARTITION p200912 VALUES LESS THAN ( TO_DAYS('2010-01-01') ),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

PARTITION BY RANGE 以下がパーティション設定ルールです。上記の例では p200910、p200911、p200912、pmax という名前の4つのパーティションが作成され、TO_DAYS(logged_at) の値を基にデータが各パーティションに振り分けられる設定になっています。例えば、logged_at が ‘2009-11-01′ より小さいデータは p200910 に格納されるといった具合です。ここで、振り分けに利用される表現（上記例では TO_DAYS(logged_at) ）を「パーティショニング表現」と呼びます。

パーティション設定ルールの最後の行の “VALUES LESS THAN MAXVALUE” は「キャッチオール」節と呼ばれるものです。MAXVALUE は表現可能な最大整数値を表すので、logged_at が ‘2010-01-01′ 以上となるデータは全て pmax に格納されます。これが無いと、logged_at が ‘2010-01-01′ 以上となるデータを格納する場合にエラーが起こります。

なお、パーティションドテーブルの作成に際してはいくつかの制約があります。以下に代表的なものを挙げておきます。

  • パーティショニング表現に用いられるカラムはテーブル内に存在するプライマリキー（またはユニークキー）の一部でなければならない
    • プライマリキー（またはユニークキー）が存在しない場合は例外
  • パーティション表現は連続した整数値をとるものでなければならない
    • 日付データを利用する場合、TO_DAYS() 関数などを使って整数値に直す必要がある
  • 作成できるパーティションの上限は1テーブルにつき1024個

パーティションの追加

既存のパーティションドテーブルから更にパーティションを追加する方法は2つあります。

  • ADD PARTITION を使って新規パーティションを追加する方法
  • REORGANIZE PARTITION を使って既存のパーティションを再分割する方法

ただし、既存のパーティションドテーブルに「キャッチオール」節が存在する場合は後者の方法しか選択肢がありません。

以下にそれぞれの方法について説明します。

ADD PARTITION を使う方法

既存のパーティションドテーブルにパーティションを新規追加するには以下のようにします。

ALTER TABLE logs ADD PARTITION (
    PARTITION p201001 VALUES LESS THAN ( TO_DAYS('2010-02-01') )
);

注意点として、RANGE パーティショニングされているテーブルにおいて、ADD PARTITION は既存パーティションの「後」にしかパーティション追加ができません（「前」や「間」はNGです）。そのため、「キャッチオール」節（MAXVALUE）が既に存在する場合は（MAXVALUE より後の値は存在し得ないため）、この方法が使えません。

REORGANIZE PARTITION を使う方法

既存のパーティションを再分割するには以下のようにします。この例では、pmax を p201001 と、（新しい） pmax に再分割しています。

ALTER TABLE logs REORGANIZE PARTITION pmax INTO (
    PARTITION p201001 VALUES LESS THAN ( TO_DAYS('2010-02-01') ),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

REORGANIZE PARTITION を行なうと、既存のデータが再分割後のパーティションに適切に振り分けられます。例えば上記の例では、旧 pmax に格納されていたデータのうち logged_at が ‘2009-02-01′ より小さいものは p201001 に、’2009-02-01′ 以上のものは 新 pmax に格納されます。この再振り分け処理により、既存データが大量にある場合は REORGANIZE PARTITION の実行にそれなりの時間がかかります。

REORGANIZE PARTITION は、既存パーティションの結合にも利用できます。例えば、p200910 と p200911 を結合したい場合は以下のようにします。

ALTER TABLE logs REORGANIZE PARTITION p200910, p200911 INTO (
    PARTITION p200910_11 VALUES LESS THAN ( TO_DAYS('2009-12-01') )
);

さらに、既存の複数パーティションを新しい複数パーティションに再構成することもできます。

ALTER TABLE logs REORGANIZE PARTITION p200910, p200911, p200911, pmax INTO (
    PARTITION p200909_10 VALUES LESS THAN ( TO_DAYS('2009-11-01') ),
    PARTITION p200911_12 VALUES LESS THAN ( TO_DAYS('2010-01-01') ),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

パーティションの削除

パーティションドテーブルから特定のパーティションを削除するには、以下のようにしてパーティションごと DROP します。

ALTER TABLE logs DROP PARTITION p200912;

上記の例では、パーティション p200912 および p200912 に格納されているデータが全て削除されます。また、上記例の実行前に p200910、p200911、p200912、pmax のパーティションがあった場合、p200912 が削除されたことで logged_at が ‘2009-12-01′ 以上となるデータは全て pmax に格納されるようになります。

パーティションの削除は内部的にテーブルの DROP とほぼ同じであるため、（ストレージエンジンが MyISAM や InnoDB の場合であれば）格納されているデータ件数によらず一瞬で処理が完了します。

パーティショニングの解除

パーティションドテーブルからパーティションを取り除き、通常のテーブルにするには以下のようにします。

ALTER TABLE logs REMOVE PARTITIONING;

DROP PARTITION とは異なり、各パーティションに格納されているデータは削除されません。各パーティションのデータを一つのテーブルにマージするため、既存データが大量にある場合は REMOVE PARTITIONING の実行にそれなりの時間がかかります。

まとめ

本エントリでは、MySQL 5.1 から導入された機能であるパーティショニングについて概要を説明し、RANGE パーティショニングによるパーティションドテーブルの作成、パーティションの追加・削除、パーティショニングの解除方法について具体例を交えて説明しました。RANGE パーティショニング以外のパーティショニング方法などパーティショニングについてより深く知りたい方は、MySQL 5.1 リファレンスマニュアル :: 15 パーティショニング を一読されると良いと思います。

次回のエントリでは、パーティションドテーブルに対して実際にデータを挿入・参照することでパーティショニングの性能面を検証してみようと思います。お楽しみに！

Tags: , ,

TrackBack URI

コメント