アイスタイル Advent Calendar 2019、9日目の担当はDBAのsuzukitoです。
リレーショナルデータベースの話をします。
もしサンタクロースが大人にも来てくれるなら、指定条件を満たしたデータが自動的に消えてくれるデータベースが欲しいです。
そうです。
運用中のデータベースで不要になったデータ消すのは一苦労なのです。
弊社が運営するサイトアットコスメ（＠ｃｏｓｍｅ） は20年以上運用しているため、それなりに大量のデータがたまっています。
サイトには10年以上動いているコードもありますが、データ量が増える事に対する配慮がないコードは珍しくありません。
みなさんはデータ量を意識して開発してますか？
1万行のデータでは調子よく動くコードも、1,000万行を相手にすると、まともに動かなくなったりするのはよくある話です。
OLTP向けデータベースでは、データサイズがコンパクトであることはパフォーマンス上、非常に有利です。
データサイズをコンパクトな状態で維持するために、データを消しやすく設計しておくことは意外に重要なのです。
データを消しやすくするために
考慮点を列挙してみます。
- パーティションを活用する
- 消せるデータと消せないデータは一緒にしない
- 削除用のインデックスを定義しておく
- 過去データを集約する
- データに期限を設定する
パーティションを活用する
パーティションはテーブル内を行方向に分割して保存する機能です。
パーティションの利点はいくつかありますが、最大の利点はパーティション単位で一括でデータの削除ができるところです。
例えば、日付列を使ってパーティションを作れば、月単位のデータ削除が簡単にできるようになります。
データを削除する場合、通常はDELETEを使います。
DELETEは、ロールバックされた場合にすべてを元に戻さなくてはならないので、ログに記録を行います。
1,000万行データを削除するならば、1,000万行のログが生成されることになるので、けっこう負荷が高い処理になりますし、時間もかかります。
MySQLの例になりますが、パーティションが設定されたテーブルは、パーティション単位でファイルが作られます。
パーティションの削除は行単位の削除ではなく、パーティションファイルの削除になるため、非常に高速にデータの削除が可能です。
- MySQL :: MySQL 5.6 リファレンスマニュアル :: 19 パーティション化
- PostgreSQL 11.5文書 :: 5.10. テーブルのパーティショニング
- パーティション テーブルとパーティション インデックス – SQL Server | Microsoft Docs
消せるデータと消せないデータは一緒にしない
パーティションを活用したとしても、同じパーティション内に削除できるデータと削除できないデータが混在していると意味がありません。
例えば、ポイント付与履歴を管理するテーブルがあったとします。
ログインポイント、キャンペーン参加ポイント、初回ログインポイントなど、いろんな種類のポイントがあるとします。
- ログインポイントの付与は、1日1回限り
- キャンペーン参加ポイントの付与は、同一キャンペーン中1回限り
- 初回ログインポイントは、同一アカウント1回限り
付与済みかどうかの判断は、そのデータの存在有無で判定するとします。
すると、このテーブルには削除可能になるタイミングがバラバラのものが入っていることになるので、月別パーティションでは削除できなくなってしまいます。
初回ログインポイント付与履歴にいたっては永遠に存在する必要があるため、このデータが含まれているパーティションは永遠に削除できません。
データを効率よく削除できるようにするためには、データの保存期間に応じて入れ物を分ける事が必要なのです。
削除用のインデックスを定義しておく
データのバリエーションが豊富で、保存期間に応じたパーティションが作れない場合は、削除対象検索用のインデックスを定義しておいた方がよいです。忘れがちですけど、DELETEだってインデックスがあれば速くなります。
DELETEは排他ロックをかけるのでSELECTと競合します。
DELETEが速ければ、アプリケーションのパフォーマンスも向上するのです。
余談ですが、運用中のデータベースで、いきなり広い範囲のデータをDELETEしようとすると、排他ロックがとれずにDELETEが失敗しがちです。狭い範囲を指定して、ループしながらこまめに消していきましょう。
過去データを集約する
明細データを集計すれば合計が得られます。
が、常に集計する前提でアプリケーション作ってしまうと、これまた、永遠にデータが削除できません。
明細テーブルと集計済みテーブルを分けて用意し。一定期間を経過した過去の明細データを集計し、集計済みテーブルに入れ。明細を削除可能にしておくとよいでしょう。
データに期限を設定する
サービス設計時にデータの表示期限を設計しておかないと、これまた、永遠にデータが消せないサービスになってしまいます。
例えば、「この画面では過去2年分のデータが表示される」など、仕様に盛り込んでおくことをおすすめします。
サービス規約上、過去データの表示期限がないため、データを消すためには先に規約の変更が必要だというサービスが、うちにも実際にあったりします。
まとめ
「データの削除」って観点から考慮すべき点をまとめてみました。
データが増え過ぎちゃってから対処をするのはとっても大変なので、事前に対策をしておきたいですね。
そんな弊社では、大量のデータに立ち向かうエンジニアを絶賛募集中です！
募集職種はこちらをどうぞ。
笑いの絶えない明るい職場です。
