8章 Metadata Tribble(メタデータ大増殖) #勉強会

とある開発現場でのテーブル分割、列分割

年ごとの営業収益を管理したい！

Tribble/anti/create.sql

CREATE TABLE Customers (
  customer_id NUMBER(9) PRIMARY KEY,
  revenue2002 NUMBER(9,2),
  revenue2003 NUMBER(9,2),
  revenue2004 NUMBER(9,2)
);

年ごとに列分割をしてしまったケース。

Tribble/anti/create.sql

CREATE TABLE Customers_Revenue2002 (
  customer_id NUMBER(9) PRIMARY KEY,
  revenue NUMBER(9,2)
);
CREATE TABLE Customers_Revenue2003 (
  customer_id NUMBER(9) PRIMARY KEY,
  revenue NUMBER(9,2)
);
CREATE TABLE Customers_Revenue2004 (
  customer_id NUMBER(9) PRIMARY KEY,
  revenue NUMBER(9,2)
);

年ごとにテーブル分割をしてしまったケース。

さまざまな問題が発生！!

目的

スケーラビリティを高める

データ容量が増えるにつれてパフォーマンスは低下。
indexを使うことで改善するが、いつかはクエリの実行速度に影響が出る。

⇒ データが増加し続けるテーブルに対して、クエリの実行速度を劣化させずにテーブルの構造を設計すること。

アンチパターン

テーブルや列をコピー(大増殖)する

トリブル...
アメリカのSFテレビドラマ『スタートレック』シリーズに登場する、架空の動物。
強い繁殖力を持つ...

以下、アンチパターンの紹介

1.テーブルの増殖

ex) バグ報告の日時を使って、データを年ごとに分割

Tribble/anti/create-table.sql

CREATE TABLE Bugs_2008 (...);
CREATE TABLE Bugs_2009 (...);
CREATE TABLE Bugs_2010 (...);

Tribble/anti/insert.sql

INSERT INTO Bugs_2010 (..., data_reported, ...) VALUES (..., '2010-06-01', ...);

2011年1月1日になりました。

Tribble/anti/insert.sql

INSERT INTO Bugs_2011 (..., data_reported, ...) VALUES (..., '2011-01-01', ...);

2011年のテーブル作成を忘れていた為、エラー！
新しいデータのために、新たなテーブルを作成しなければなりません。
　

2.データの整合性を管理する

ex)2010年のデータが2009年のテーブルに登録できてしまう。
[TABLE: Bugs_2009]

bug_id	date_reported	comment
1	2009-06-01	...
2	2010-01-01	2009年のtableに2010年のデータが登録できるバグ!?...

CHECK制約を宣言することにより回避できるが、、、

Tribble/anti/create.sql

CREATE TABLE Bugs_2009 (
 bug_id,
 date_reported DATE CHECK (EXTRACT(YEAR FROM date_reported) = 2009),
 comment
);

新たにBugs_2010年のテーブルを作成するときに、CHECK制約の値の修正を忘れないようにしなければならない。
修正を誤ると、受け入れるべき行を拒絶するテーブルを作成することになってしまう。

CHECK制約：値が条件に反していないかチェックすることができる
EXTRACT:日付の部分抽出　

3.データの同期

ex)日付の変更。2010年1月3日に報告されたデータが、2009年12月27日に報告されたものだった。

Tribble/anti/update.sql

UPDATE Bugs_2010
SET date_reported = '2009-12-27'
WHERE bug_id = 1;

単純にUPDATEを行うと、Bugs_2010テーブルにデータ不正が生じてしまう。
Bugs_2009テーブルにデータを挿入し、Bugs_2010テーブルからさ削除を行わなければならない。

Tribble/anti/select_insert.sql

INSERT INTO Bugs_2009 (bug_id, date_reported, ...)
  SELECT bug_id, date_reported, ...
  FROM Bugs_2010
  WHERE bug_id = 1;

DELETE FROM Bugs_2010 WHERE bug_id = 1;

4.一意性の保障

分割されたすべてのテーブルの間で、主キーが一意であることを保障する必要がある。
行をテーブルから別のテーブルへ移動させる場合に、主キーの一意性が保障されていないと移動できない。

シーケンスオブジェクトをサポートするDBの場合、同一のシーケンスオブジェクトを使用する。
シーケンスオブジェクトがサポートされていない場合は、主キーを生成する為のテーブルを1つ定義しなければならない。

5.テーブルをまたいだクエリ実行

ex)報告された年にかかわらず、すべての未完了のバグの合計を算出したい。
UNIONを使用してあたかも1テーブルであるかのようにクエリを実行する。

Tribble/anti/select_union.sql

SELECT b.status, COUNT(*) AS count_per_status FROM (
  SELECT * FROM Bugs_2008
  UNION
  SELECT * FROM Bugs_2009
  UNION
  SELECT * FROM Bugs_2010 ) AS b
GROUP BY b.stats;

しかし、
Bugs_2011などのテーブルが増えるにつれ、アプリケーションコード(クエリ)を修正しなければならない。

6.メタデータの同期

ex) カラムの追加が発生した場合。

ALTER文は変更対象の1テーブルにしか適用されません。
上記のようなUNIONを使ったクエリの場合に問題が生じる。(取得する列が同じ必要がある)

全テーブルにALTER文を実行する or not...

7.参照整合性の管理

ex) Commentsなどの従属テーブルがBugsテーブルを参照する場合.
親テーブルが複数に分割されている為、従属テーブルで外部キーを定義できない。

Tribble/anti/create.sql

CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  bugs_id BIGINT UNSIGNED NOT NULL,
  FOREIGN KEY (bugs_id) REFERENCES Bugs_????(bug_id)
);

従属テーブルと親テーブルを結合して参照する場合も問題が生じる。

Tribble/anti/select.sql

SELECT * FROM Accounts a 
INNER JOIN (
  SELECT * FROM Bugs_2008
  UNION ALL
  SELECT * FROM Bugs_2009
  UNION ALL
  SELECT * FROM Bugs_2010
) t ON a.account_id = t.reported_by;

どのBugs_????と紐づくのかが分からない。
Bugs_2011のテーブルが増えた場合にクエリの修正が必要。

8.メタデータトリブル列の特定

テーブル分割だけではなく、列もメタデータトリブルになる場合がある。
ex) 列を年毎に分割。

Tribble/anti/create.sql

CREATE TABLE Bugs (
  bugs_2008 INT,
  bugs_2009 INT,
  bugs_2010 INT
);

アンチパターンの見つけ方

テーブル(または列)を分割している可能性がある発言に注意

じゃあ、～ごとにテーブル(または列)を作る必要があるんだね
このデータベースがサポートしているテーブル(または列)の最大数は？
新しい年のデータを格納するためのテーブルを作成し忘れていたんだ
複数テーブルを1回で検索するためのクエリの実行方法は？全部のテーブルの列は共通しているんだけど
テーブル名のパラメータをどうやって渡せばいい？年が動的に付加されるテーブル名にクエリを実行する必要があるんだ

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

過去データを最新のデータから分離するようなアーカイブが目的の場合

現在のデータと過去のデータとを合わせてクエリ実行する必要が無い場合、
過去データを別の場所にコピーし、テーブルから削除するのは適切な処理といえる。
　

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

水平パーティショニング(シャーディング)の使用

論理的には1テーブルだが、物理的には行でテーブルが分割される。

テーブル分割の欠点に悩まされることなく、テーブル分割するメリットを得られる。
行を分割するルールを定義すれば、あとはデータベースが必要な作業を行ってくれる。
ただし、パーティショニングされたデータをまたいでのselectはパフォーマンスが落ちる。

Tribble/anti/create.sql

CREATE TABLE Bugs (
  bug_id SERIAL PRIMARY KEY,
  date_reported DATE
) PARTITION BY HASH ( YEAR(date_reported) )
  PATITIONS 4;

date_reportedの年ごとにパーティションを設定。
物理テーブルの数は4つに固定。

この他にもさまざまなルールを設けることが可能。
　

垂直パーティショニングの使用

論理的には1テーブルだが、物理的には列でテーブルが分割される。

列の一部のサイズが大きい場合(BLOBやTEXTなど)にメリットがある。
めったに使用されない列の場合にメリットがある。

無駄に大きいデータや使わないデータを参照しない。

従属テーブルの導入

7章「マルチカラムアトリビュート」の解決策と同様。
解決策は、従属テーブルの導入。

project_id(PK)	year(PK)	bugs_fixed
1	2009	12345
2	2010	54321

年ごとにテーブルを分割するのではなく！
年ごとのカラムを追加するのではなく！
project_idと年の組み合わせごとに1行となるようにテーブルを定義する。
　

最後に、
データにメタデータを増殖させないように気をつけましょう。

増殖し続ける履歴データなどは、
過去○ヶ月のみ参照可能。
といったようにアプリケーション仕様を定義することにより、パーティショニングが可能となる。
無理な場合は、DBシャーディングを考慮する。