MySQLで履歴テーブルに月ごとのPartitioning設定を行う
こんにちは。最近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の機能を使うとアプリケーションの処理速度を改善することができるので、バックエンドエンジニアとして実装ケースによって細かく機能を使いこなせるようになりたいです。
__________________________________
■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/