Edited at

8章 Metadata Tribble(メタデータ大増殖)

More than 3 years have passed since last update.


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

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


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シャーディングを考慮する。