はじめに
良いデータベース設計とは？
という議題は、アプリケーション開発をしていく上で誰もが一度は疑問にいただいた問題ではないでしょうか。
「正規化」ひとつとっても、
- 可能な限り正規化したほうが良い
- 正規化しすぎないほうが良い
など、様々な意見が交わされていると思います。
個人的にもこの問題に対する銀の弾丸的な解決策はないと思っていて、よりベターな方法を探していくしかないのかなと思っています。
その中で、今回は「データの削除・メンテナンス」というシーンから見た場合にどういう設計がより「ベター」なのかに関して思うところを書いていければと。
前提条件
下記ようなアプリケーションを開発・運用するケースで考える
普通にWebアプリケーションを作ったらだいたいこうなるとは思いますが。。。
- データの保持期限が定まっている（無限にデータを保持するわけではない）
- 不要データのクリーニングは定期的に行われる
保持するデータ
- purchases
- 購買データ
- purchase_items
- 購買商品
- purchase_item_details
- 購買商品詳細
モデル定義
class Purchase < ApplicationRecord
has_many :purchase_items, dependent: :destroy
end
class PurchaseItem < ApplicationRecord
belongs_to :purchase
has_many :purchase_item_details, dependent: :delete_all
end
class PurchaseItemDetail < ApplicationRecord
belongs_to :purchase_item
end
テーブル設計
上記のようなケースの場合、正規化を意識してテーブル設計をすると、だいたいこのような形になるのではないだろうかと思います。（id系以外の列名は適当です）
# purchases
- id
- name
- price
- created_by
- updated_by
# purchase_items
- id
- cd
- purchase_id
- created_by
- updated_by
# purhase_item_details
- id
- option1
- purchase_item_id
- created_by
- updated_by
データ削除
長い前置きの元、やっと本題です。
上記で設定したテーブルの、データ削除を行うケースについて考えます。
購買データの保存期間が、システムとして「１年」と決まっている場合、定期削除を行うプログラムを普通に書くと、下記のような形になるのではないでしょうか。
Purchase.where("created_at < YYYY/MM/DD").destroy_all
もちろん、この書き方でデータはちゃんと消えます。
ただし、該当のpurchaseが1万件…10万件…100万件と、システムが成長していくにつれて、データの削除が顕著に遅くなっていくのはわかりますでしょうか？
単純なデータ量の問題でも、created_atに適切なINDEXが貼られていない事が問題でもなく、その他にも大きな問題が隠れています。
なぜ、削除が遅いのか
原因は、
destroy_all
にあります。
Railsアプリケーションの場合、destroyを利用する事でdependentなどで記述したアソシエーションを解決できるというメリットはありますが、その分ActiveRecordのオブジェクトを作るというコストが同時に発生しています。
単一データの操作レベルであれば、取るに足らないコストですが、数百・数千万のデータを扱う場合には大きなコストとなって現れてきます。
じゃあ、先ほどの削除部分をdestroyを使わずに削除しようとして、下記に変えても、十分ではありません。
Purchase.where("created_at > YYYY/MM/DD").delete_all
当然、purhase_items、purhase_item_detailsが、消えずに残ってしまいますよね。
このままのテーブル設計で、delete_allを利用してデータ削除を利用しようとすると、
delete_purchase_ids = Purchase.where("created_at < YYYY/MM/DD").pluck(:id)
delete_purchase_item_ids = PurchaseItem.where(purchase_id: delete_purchase_ids).pluck(:id)
Purchase.where(id: delete_purchase_ids).delete_all
PurchaseItem.where(purchase_id: delete_purchase_ids).delete_all
PurchaseItemDetail.where(purchase_item_id: delete_purchase_item_ids).delete_all
という流れが必要になります。
テーブル設計時に、「綺麗に正規化」したことが原因で、データの一括操作がしにくくなっているのでは？というのが今回の観点です。（やっとここで）
では、どうするべきか
テーブル設計改善案
# purchases
- id
- name
- price
- created_by
- updated_by
# purchase_items
- id
- cd
- purchase_id
- created_by
- updated_by
# purhase_item_details
- id
- option1
- purchase_id
- purchase_item_id
- created_by
- updated_by
上記のような設計にすればどうでしょうか？
purchase_item_detailsに、purchase_idをもたせました。
もちろん、正規化的には「綺麗」ではないですね。
では、データ削除がどう変わるのか？考えてみます。
データ削除（改善案のテーブル設計Ver.）
delete_purchase_ids = Purchase.where("created_at < YYYY/MM/DD").pluck(:id)
Purchase.where(id: delete_purchase_ids).delete_all
PurchaseItem.where(purchase_id: delete_purchase_ids).delete_all
PurchaseItemDetail.where(purchase_id: delete_purchase_ids).delete_all
purchase_item_detailsが、purchase_idを持つようになったことによって、削除対象のpurchase_itemsのidを取得する必要がなくなりました。
たかが１回のSQLと思うかもしれませんが、データ量が多くなればなるほど検索コストもかかってくるため、大きな違いではないかと思います。
まとめ
「データの削除・メンテナンスをする」という視点から見た、「ベター」なデータベース設計に関して思う事を書いてみました。
実際に、私自身が運用しているサービスで同じような問題が発生し、チューニングを入れた事が記事を記載したきっかけです。
その時は、クエリで直接削除すれば1分もかからないデータ（60万レコードほど）を、子 / 孫のデータを含めて削除するのに３時間かかっても終わらないという問題が発生していました。
定期的なデータメンテナンスという観点は、データ設計時には考えられない事が多いと思います。
実際に、データのライフサイクルについて注目しながらテーブル設計を行う事で、長期的な運用・保守で問題が発生しない構造を作っていけるのではないかと思います。
ぜひ一度
- このデータはいつ作られるのか
- このデータはどれぐらいまで増えるのか
- このデータはいつ消されるのか
- 大量削除されることはあるのか
という部分にも着目してテーブル設計を行ってみていただければと思います。