Why I want to stop using range partition with timestamp column

Partition 周りで社内で説明する事が有ったのでせっかくなのでブログをかきます。注.) 僕自身はMySQLにさほど詳しくはないので、完全には鵜呑みにしない事をおすすめしますが

下記のようなテーブルがあったとします

CREATE TABLE messages (
  id int unsigned NOT NULL,
  user_id int unsigned NOT NULL,
  content varchar(255) NOT NULL DEFAULT '',
  created_at int unsigned NOT NULL  
  PRIMARY KEY (id),
  KEY `on_user_id` (user_id)
) Engine=InnoDB DEFAULT CHARSET=utf8;

データ量が多かったりして、3ヶ月以前のデータをPurgeするとか考えだすとdeleteは重いので、めんどいです。

ここで非常に強力なのが Partitioningです。もし3ヶ月以前のデータはPurgeしてよいのであれば、上記テーブルは TimestampでPartitioningするというテーブル設計がよくとられがちです。具体的には

CREATE TABLE messages (
  id int unsigned NOT NULL,
  user_id int unsigned NOT NULL,
  content varchar(255) NOT NULL DEFAULT '',
  created_at int unsigned NOT NULL  
  PRIMARY KEY (id, created_at),
  KEY `on_user_id` (user_id)
) Engine=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (created_at)
(PARTITION p201403170000 VALUES LESS THAN (1394996400) ENGINE = InnoDB,
 PARTITION p201403170400 VALUES LESS THAN (1395010800) ENGINE = InnoDB,
 ## 以下略

created_at が PK になっているのは、Partition の対象のカラムは MySQL 的に PK ないしは UNIQUE KEY に含まれてないといけないからです。

このようにする事で

一定期間以前のデータを削除したいときは Drop partitionすれば良い。Drop partitionはDrop tableのようなものなので、高速である
Partition 単位のindexは小さくなるので、データを最新順にとってくるような特定の Partition にアクセスが集中する処理は非常に高速である

といいことがたくさんあります。特に Purge がらくちんなのは大変便利で Partitionがないと生きていけないみたいな感覚によくなります。Purgeには色々負荷を押さえるための魔術が必要なケースが多く、最初に使い始めたときはこんなに楽なのか。今まで僕のPurgeの努力はなんだったのかと感動しました。

ただし、上記の設計には一つ大きな弱点があります

以下のような Query を投げる事を想定しましょう

SELECT * FROM messages
WHERE created_at < NOW()  # Partition pruningのため
ORDER BY id DESC 
LIMIT 10

これ、本来は最新のPartition から順に探索していけばいいのですが、 id と created_at の順序は同一であるという事をMySQL さんは知らないのでどうしても全ての Partition を見に行かなくてはいけず、非常に効率がわるいのです。そして、おそらくですが MySQL は全てのPartition をなめたあとに id による Merge Sort をしようとするような実装になっていると思います(勝手な想像)。

この問題に関しては、以下のようなIndexを追加する事で Indexは多少無駄になるものの、Partition pruning を聞かせる事が可能です。

ALTER TABLE messages add KEY `on_created_at` (created_at)

SELECT * FROM messages
WHERE created_at < NOW()  # Partition pruningのため
ORDER BY created_at DESC, id DESC 
LIMIT 10

ただ、これだけのためであればこのIndex 無駄ですよね。コードも複雑になりますし非常にいけてないです。また、これでは解決できない問題もあります。以下のような Query です

SELECT * FROM messages
WHERE id IN (1, 10, 100)

これも Partition pruning がきかないので、MySQLさんは全ての Partition に検索をかけにいきます。partitionが30個あったら30個のテーブルに SELECT なげるようなものです。これは一応一定の改善策があります

SELECT * FROM messages
WHERE id IN (1, 10, 100)
AND created_at >= @CRETEAED_AT_OF_ID_1 AND created_at >= @CREATED_AT_OF_ID_100
LIMIT 10

もし、各id の created_at がわかってる場合は、それらの中で最小のcreated_atと最大のcreated_at で範囲をしぼることができます。ただ、すごく古いデータとすごく新しいデータに同時にアクセスしようとすると結局効率は改善されません。

そもそもです。そもそもは id が PKなわけです。PK による ORDER BY とかにそんなにがんばりたくありません。 pk が id なのに PRIMARY KEY に created_at をいれるとかもなんか黒魔術的な感じがしていやです。黒魔術を使うとだいたいどっかでしわ寄せがくるものですし、現実的に発生してしまっています。

全てを解決する方法が有ります。id でRange Partition すればいいのです。みんな、そんな事はわかってるのですが、3ヶ月以前のデータを簡単に捨てたいんだとか思うとPartition 管理が多少めんどうになるので、二の足を踏みがちなのかなと勝手に想像しています。

でもid でrange partitionしつつ、一定時間よりも古いデータをPurgeするみたいな処理はそんなに難しくはないですね。

例として

Partitionがかわるタイミングで Batch処理でシーケンスを発行して各partitionの最も最新のcreated_at のmappingを作っておく、DROP 際はそのmappingを参照する
各Partition の最新のデータを取得して、そのpartitionの最新のcreated_atを判定し、一定期間よりも古いデータだった場合はPartition dropする

このような管理にしておけば上記のようなperformance周りの問題は全てなくなり、かつコードも簡単になり、みんなハッピーです。

もちろん自動でPartition を追加するロジックとかも必要で、こういった事は timestamp based partition よりもめんどうなのですが管理ツールは一回だけつくればいいですし、idでrange partition するべきと思います

maedamaのブログ

アプリケーションエンジニアです。最近は主に設計を担当しています。

Why I want to stop using range partition with timestamp column