Help us understand the problem. What is going on with this article?

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした