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

####とある開発現場でのテーブル分割、列分割
年ごとの営業収益を管理したい！

```sql: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)
);
```
年ごとに列分割をしてしまったケース。

```sql: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) バグ報告の日時を使って、データを年ごとに分割

```sql:Tribble/anti/create-table.sql
CREATE TABLE Bugs_2008 (...);
CREATE TABLE Bugs_2009 (...);
CREATE TABLE Bugs_2010 (...);
```
```sql:Tribble/anti/insert.sql
INSERT INTO Bugs_2010 (..., data_reported, ...) VALUES (..., '2010-06-01', ...);
```
2011年1月1日になりました。

```sql: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制約を宣言することにより回避できるが、、、

```sql: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日に報告されたものだった。

```sql:Tribble/anti/update.sql
UPDATE Bugs_2010
SET date_reported = '2009-12-27'
WHERE bug_id = 1;
```
単純にUPDATEを行うと、Bugs_2010テーブルにデータ不正が生じてしまう。
Bugs_2009テーブルにデータを挿入し、Bugs_2010テーブルからさ削除を行わなければならない。

```sql: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テーブルであるかのようにクエリを実行する。

```sql: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テーブルを参照する場合.
親テーブルが複数に分割されている為、従属テーブルで外部キーを定義できない。

```sql: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)
);
```

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

```sql: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) 列を年毎に分割。

```sql:Tribble/anti/create.sql
CREATE TABLE Bugs (
  bugs_2008 INT,
  bugs_2009 INT,
  bugs_2010 INT
);
```


## アンチパターンの見つけ方
テーブル(または列)を分割している可能性がある発言に注意

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

　

## アンチパターンを用いてもよい場合
**過去データを最新のデータから分離するようなアーカイブが目的の場合**

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

## 解決策：パーティショニングと正規化を行う
####水平パーティショニング(シャーディング)の使用
論理的には1テーブルだが、物理的には行でテーブルが分割される。

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

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