SQLアンチパターン メタデータトリブル

SQLアンチパターンという有名な本を前から読もう読もうと思って後回しになっておりましたが、今回一通り読むことができたので、興味深い章を一つ取り上げ自分の経験と照らし合わせまとめてみようと思います。

今回は過去在籍していた会社でも運用フローとして少なからず存在し、なおかつ当時の自分にはその解決策を知らなかったメタデータトリブルに関して取り上げてみようと思います。

メタデータトリブルとは

テーブルやカラムが無限増殖していく様子がスタートレックのトリブルがその様に似ていることから名付けられたようです。

クエリの実行速度を低下させずに増加し続けるデータに対応

過去に在籍していた会社のあるプロジェクトでは、顧客データに関しての様々な集計を取っており画面を開いて都度集計クエリを実行して集計していましたが、顧客の購入データが膨大になってくると画面を閲覧するまでに数十秒〜数分程度ほどかかっている状況で、開発側からしてもこれはユーザービリティとしてよろしくないという認識がありました。
案の定ユーザー側で瞬時に情報を確認したいとのクレームが入ることが増え、また社内でも早急な対応を要望する声が大きくなり、最終的な判断として四半期ごとのテーブルを作成し分割することでその場を凌ぐことになります。
どのようなデータベースでもデータ容量が増えるにつれてパフォーマンスが低下し将来的にもクエリの実行速度を劣化させずにデータが増加し続けるテーブルに対応できるようデータベース設計を行う必要があります。
この設計を行う際に今後の運用フローまで考慮せずに他の条件がすべて同じであれば行数が少ないテーブルへのクエリ実行のほうが、行数が多い場合よりも早く処理できるという考えにたどり着いてしまったことでそこにアンチパターンが潜んでいました。

以下、結果的にテーブル分割したことでどのような運用フローが必要になったかを記載していきます。

テーブルや列の増殖

分割対応することでその四半期ごとのテーブルを作成することになり、さらにこのテーブルは時とともにテーブル数が次第に増加していくことになります。（下記参照）

CREATE TABLE Customers_201501(...);
CREATE TABLE Customers_201502 (...);
CREATE TABLE Customers_201503 (...);

データベースへ行を挿入する際には、挿入する値に応じて、挿入先を選択しなければならなくなります。
期末が近くなってくるとCustomers_◯◯というテーブルを作成しなければなりませんでした。もし作成し忘た場合、エラーが発生してしまう状況です。最終的には年末にバッチでテーブルを追加する仕様にしましたが、その分動作が増えメンテナンスにテーブル作成が伴ってしまい、メンテナンス効率が悪くなったことには変わりがありません。

データ整合性管理の必要性

テーブルの列が全く同じなため例えば、2015年のデータを2016年に保存ができてしまいます。
これを避けるためにCHECK制約を宣言する必要がでてきます。

CREATE TABLE Customers_2019 (
 ...
 booking_date DATE CHECK (EXTRACT(YEAR FROM booking_date) = 2019),
 ...
);

ただ、自分のときはCHECK制約を追加せずに運用していたため案の定Customers_201502に入るべきデータがCustomers_201503に入ったりしていました。あるべきデータが存在しないから調査してほしいとの依頼が少なからず入るようになり、データの再整合がかなり時間を要することとなり徹夜作業になってしまったことが幾度かありました。

分割テーブル間でのデータ一意性保証の必要性

テーブルを分割した場合、主キーの値が一意であることを保証する必要がでてきます。

シーケンスオブジェクトをサポートするデータベースを使用している場合
同一のシーケンスオブジェクトを使用して、分割されたテーブルに対して主キーの値を生成できます。
ただ、当時はシーケンスオブジェクトの存在を知らず主キーを管理するためのテーブルを1つ追加で定義しました。

メタデータの同期

分割テーブルに1つのカラムを追加する際にはすべての分割テーブルにカラムを追加しなければならなくなります。
Customers_201501テーブルだけで使用するとしたとしても、UNIONを使用する場合カラム数、順番、データ型を一致させる必要があるため、すべてのテーブルにカラムを追加する必要がありました。

参照整合性の管理

親テーブルが分割されていることにより外部キーの設定が定義できなくなりデータの正確性が担保できなくなっていました。

アンチパターンを用いてもよい場合

過去データを最新データから分離するようなアーカイブが目的の場合はこのパターンを用いても良いとの記載がありました。
過去のデータに対してクエリを実行する必要性が大幅に低下する場合などです。たしかに過去のテーブルにアクセスしないのであれば分割したほうが逆に効率が良くなります。ただ、自分が経験したプロジェクトはがっつりと過去のテーブルにもアクセスが必要なためやはりアンチパターンです。

解決策：パーティショニングと正規化を行う

テーブルサイズが巨大化した場合の有効策として、水平パーティショニング、垂直パーティショニング、従属テーブルの導入などがあります。

水平パーティショニング

論理的（ユーザやアプリケーションから）は1テーブルに見えるが、物理的にテーブルを分割されます。
行を分割するルールを定義すれば、挿入時のレコードの振り分け等はデータベースで処理される為、 ユーザは１つのテーブルを扱うようにクエリの発行ができます。

CREATE TEBLE Customers(
bug_id         SERIAL PRIMARY KEY,
...
booking_date DATE
) PARTITION BY HASH ( YEAR(booking_date))
   PRATITION 4

垂直パーティショニング

水平パーティショニングがテーブルを行で分割するのに対し、垂直パーティショニングは列でテーブルを分割します。
列でのテーブルを分割をする垂直パーティショニングは、列の一部のサイズが大きい場 合や、めったに使用されない場合にメリットがあります。

①サイズが大きい列を切り離し
ワイルドカードを用いた検索にてサイズの大きいデータの取得を回避する為、別テーブルに切り離します。
②固定長と可変長とで列を分割
ストレージエンジンで検索を効率化する為、可変長の列を別テーブルに切り離します。

従属テーブルの導入

期別のカラムを持たせて強引に１レコードに収めず、プロジェクトと四半期にて１レコードになるようにすることで新たな四半期をサポートするために列の定義を加える必要がなくなります。

まとめ

メタデータトリブルに限った話ではないですが、状況的に切羽詰まった状態でもその場しのぎの対応をしてしまうと、結局どこかにしわ寄せがやってきます。当時の対応としてテーブルを分割する際に分割することで発生するデメリットを把握し、分割以外になにか手立てがないかをもっと探るべきでした。今後はアンチパターンを把握しメタデータトリブルが発生しない設計、もしくはやりとりにメタデータトリブルになっていそうな雰囲気があれば助言していこうと思います。

参考

SQLアンチパターン　メタデータトリブル
オライリー・ジャパン　Bill Karwin　著、和田 卓人、和田 省二　監訳、児島 修　訳