somemo programming etc.

プログラマ、雑記、プログラミング関係はLinkから、数式はこっちでまとめていることが多い

【mysql】検索の向上【partition】

ハードディスクのパーティションをよく聞きますが、MySQLにもパーティションという概念があります。MySQLの場合、分割する領域はテーブルの領域になります。

利点

テーブルの領域を分割することで検索範囲を明確にすることができます。ただし、正しく分割して検索時に分割した領域を指定する必要があります。

分割

例えば、以下のように分割します。

  • 運用上で一定期間だけ必要なログデータであれば、その作成日時
  • 定期的なイベント(期間はイベントのマスタで管理)のデータであれば、そのイベントのID

分割の種類には前者の期間(範囲)指定と、後者のように特定の値を指定をする方法があります。他にも種類がありますが、省略します。また、指定するカラムはPKに含まれている必要があります。

検索

検索の際には、分割の際に指定したカラムを検索条件に含める必要があります。これは、どのパーティションに属しているかが分からなくなるためです。これを指定しないと、検索が遅くなることがありますので注意しないといけません。

テーブル定義

まず、前者の種類(RANGE)は、以下のように定義します。

CREATE TABLE PARTITION_RANGE (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `create_at` datetime NOT NULL,
  PRIMARY KEY (`id`, `create_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='パーティションRANGE'
PARTITION BY RANGE (TO_DAYS(create_at))
(
PARTITION p20130101 VALUES LESS THAN (735234) COMMENT='2013-01-01' ENGINE=InnoDB,
PARTITION p20130201 VALUES LESS THAN (735265) COMMENT='2013-02-01' ENGINE=InnoDB,
PARTITION p20130301 VALUES LESS THAN (735293) COMMENT='2013-03-01' ENGINE=InnoDB,
PARTITION pMax VALUES LESS THAN MAXVALUE ENGINE=InnoDB
);

各詳細の値は、以下のSQLで調べました。パーティションで使う値が整数であるという制約のためです。ただし、5.5では整数に変換する必要がありません。また、複数のカラムを指定することができるようになっています。

select TO_DAYS('2013-01-01'), TO_DAYS('2013-02-01'), TO_DAYS('2013-03-01')
from dual

次に、後者の種類(LIST)の定義です。

CREATE TABLE PARTITION_LIST (
 `id` int unsigned NOT NULL AUTO_INCREMENT,
 `event_id` int unsigned NOT NULL,
 `create_at` datetime NOT NULL,
  PRIMARY KEY (`id`, `event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='パーティションLIST'
PARTITION BY LIST (event_id)
(
PARTITION p1 VALUES IN (1)  ENGINE=InnoDB,
PARTITION p2 VALUES IN (2)  ENGINE=InnoDB,
PARTITION p3 VALUES IN (3)  ENGINE=InnoDB
);

RANGEと違って、パーティションがなくなることがありますので、注意してください。

パーティションの利用確認

indexのときと同様に、explainを使います。ただし、少し違います。

explain partitions select * from PARTITION_RANGE

explainの後ろにpartitionsをつけます。ただのexplainの結果に加えて、partitionsカラムが追加されています。表示内容は、検索したパーティションです。上記のexplainの場合、パーティションを指定していないので、すべてのパーティションが表示されました。

このSQLにwhere句を追加し、パーティションに指定したカラム(create_at)を条件に追加すると、条件にあうパーティションのみ表示されるようになります。

パーティションの情報は以下のSQLで確認できます。

select * from INFORMATION_SCHEMA.PARTITIONS
where TABLE_NAME='PARTITION_RANGE’;

データの削除

パーティションで分割したテーブルのデータの削除は、ただdeleteするよりとても早くすむ方法があります。それは、パーティションごと削除する方法です。これは、パーティションという名の通り、データの領域も分割されているためです。また、これによりdelete時に起きるデータ領域の断片化も起きません。

デメリット

メリットをあげてきましたが、デメリットもあります。トレードオフと言ったほうがいいかもしれませんが…。

  • 指定するカラムはPKに含める(テーブル定義参照)
  • パーティションの追加を行なう必要があり、忘れるとデータが作られない
  • パーティションは1024つまで

1つ目はフレームワーク(ORM)の制約の邪魔になるかもしれません。2つ目は、自動化してカバーすれば防げます。3つ目は、これほどのパーティションを必要とするデータを扱ったことがないので分かりません…。ただし、これを含めて5.6ではパーティションに関する改善が見られます。

5.6

パーティションに関する改善は、パーティション数が増えた、同じテーブル構造同士のパーティション移動など便利な物があります。特にパーティションの移動を早く使ってみたいです。