MySQLで履歴テーブルに月ごとのPartitioning設定を行う

2021年12月16日 16:55

こんにちは。最近SHIFTに入社した三輪です。SHIFT社内のプロダクト開発に従事しております。

SHIFTの基盤開発ではユーザーのログイン履歴を保存する機能を開発しています。ログインごとの履歴を保存するのでデータ量は膨大なものになるため、過去のデータは定期的に削除する必要があります。

RDBを使う場合、Deleteだと1行ずつレコードを消していくことになるので、膨大な作業時間がかかりそう…。ということで、Partitioning機能を使って簡単にデータ削除を出来る設計を行います。

Partitioningとは和訳すると分割という意味で、その名の通りデータを分割して保存する機能です。

Partitionで調べるとハードディスクのPartitionとDBのPartitionが出てきますが、ここで解説するのはDBのPartitionの方です。
ハードディスクのPartitioningは下記の記事が詳しいです。
パーティションとは

Partitioningとは

特定のカラムを分割キー(パーティション化キー)として、その値によりテーブルを物理的に複数のセグメントに分割(Partitioning)することで、クエリやメンテナンス作業のパフォーマンスを向上させる技術です。利点としては下記2つ。

・SQLでアクセスする物理領域を限定し、処理を高速化する(WHERE句を使う際に有効)
・パーティション単位で管理作業(DROPやTRUNCATE)を行える

テーブルをPartitioningすると実態としては物理的に分割された別テーブルになりますが、論理的には1つのテーブルに見えるので、アプリケーション側からは普段通り使うことができます。

要するに、例えば全レコードが億単位になることが予想されるようなログテーブルを作る際、処理性能を考慮して「202201_logs」「202202_logs」のような月ごとのテーブルを作らなくても、Partitioningを使えば見た目は1つのログテーブルだが、実態としては別テーブルにしてくれるということです。

Partitioningの種類

主なDBでは下記の種類がサポートされています。それぞれ特徴があるので、ケースバイケースでどの種類でPartitioningするか考える必要があります。

ログイン履歴テーブルのPartitioning

さて、今回は履歴テーブルなので、レンジパーティションを使って実装を進めます。ログイン履歴テーブルはこんな感じです。
使用しているDBはMySQL5.7系です。

CREATE TABLE `login_histories`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`, `created_at`) USING BTREE,
  INDEX `idx_user_id`(`user_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

パーティションキーはcreated_at、MySQLではパーティションキーはプライマリキーに含んでおく必要があるので、プライマリキーはidとcreated_atの複合キーです。また、データ数が多くなるのでuser_idはインデックスだけ張り、外部キー参照はしていません。

では、created_atをパーティションキーとして、月ごとにパーティション設定を行います。レンジパーティションの場合、MySQLではパーティションキーは範囲を指定する必要があるので、今回はパーティションキーを整数値化するために YEAR(created_at)×100 + MONTH(created_at) とします。

※MySQL 5.7 では、date や datetime 型などをそのままキーとしてとることができるのですが、バージョンやDBによって使えるカラム型がまちまちなので、汎用的に使える方法として、整数値に変換した例を示します。

パーティションを設定するALTER文は下記。

ALTER TABLE `login_histories`
Partition BY RANGE (YEAR(created_at)*100 + MONTH(created_at))
(
  Partition p202201 VALUES LESS THAN (202202),
  Partition p202202 VALUES LESS THAN (202203),
  Partition p202203 VALUES LESS THAN (202203),
  Partition p999999 VALUES LESS THAN (MAXVALUE) -- デフォルト値設定
);

データ削除

Partitioningしたテーブルのデータを削除する場合、DELETE文を流すのではなくDROP PARTITIONしてやればOKです。

ALTER TABLE `login_histories` DROP PARTITION p202201;

WHERE句で絞り込みをかけて1行ずつDELETEするのではなく、1つのパーティションをそのまま削除するだけなので非常に高速です。

まとめ

今までDBはRailsのActiveRecordを介してしか触ってこなかったので、初めてPartitioningの概念を知って実装しました。

DBの機能を使うとアプリケーションの処理速度を改善することができるので、バックエンドエンジニアとして実装ケースによって細かく機能を使いこなせるようになりたいです。

＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

執筆者プロフィール：Taiga Miwa
受託会社で2年ほど勤務し、Railsを使ったWebアプリの新規開発を主に担当。Rails, React, Vue, GraphQLなどモダンな技術を使って開発を行っていた。最近SHIFTにジョインし、アーキテクト目指して修行中。得意技はRubyと魚捌き。

■SHIFTについて
私たちはソフトウェアテスト(第三者検証)のプロ集団です。

■資料ダウンロード／動画視聴ページはこちらから

■メディア掲載情報　

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/

最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。

ソフトウェア品質のプロがお伺いします。