はじめに

本エントリーは某社内で実施するSQLアンチパターン勉強会向けの資料となります。

本エントリーで書籍「SQL アンチパターン」をベースに学習を進めます。書籍上でのサンプルコードはMySQLですが、本エントリーでのサンプルコードはT-SQLに置き換えて解説します。

メタデータトリブルとは

どのようなデータベースクエリでもデータの容量が増加してきた場合、パフォーマンスは低下します。
行数が多いテーブルに対してクエリを投げるより、行数の少ないテーブルに対して行った方が早く処理を完了できることから、「すべてのテーブルの行は少ない方がよい」という誤った考えを導くと危険があります。
その結果、以下の二つのアンチパターンに陥ってしまいます。

行数の多いテーブルを複数のテーブルに分割する
列を複数列に分割する

テーブルの増殖

今回使用するテーブルは、以下のものを年ごとに作成したものとする。

CREATE TABLE Bugs ( 
   bug_id        BIGINT identity, 
   date_reported     DATE NOT NULL,
   summary           VARCHAR(80), 
   description       VARCHAR(1000), 
   solution          VARCHAR(1000), 
   reported_by       BIGINT NOT NULL, 
   assigned_to       BIGINT, 
   verified_by       BIGINT, 
   status            VARCHAR(20) NOT NULL DEFAULT 'NEW', 
   priority          VARCHAR(20), 
   hours             NUMERIC(9,2),   

   CONSTRAINT PK_Bugs PRIMARY KEY CLUSTERED (bug_id), 
   FOREIGN KEY (reported_by) REFERENCES Accounts(account_id), 
   FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id), 
   FOREIGN KEY (verified_by) REFERENCES Accounts(account_id), 
   FOREIGN KEY (status) REFERENCES BugStatus(status) 
 );

データを複数のテーブルに分割するときは、どの行がそのテーブルに属しているか定義が必要になります。

CREATE TABLE Bugs_2016(...);
CREATE TABLE Bugs_2017 (...);
CREATE TABLE Bugs_2018 (...);

作成したテーブルに対してデータを挿入する。

INSERT INTO Bugs_2017 (..., data_reported, ...) VALUES (..., '2017-06-01', ...);

2018年になった場合、最初の段階でテーブルを作成していないため、以下のSQLはエラーとなる。

INSERT INTO Bugs_2018 (..., data_reported, ...) VALUES (..., '2018-01-01', ...);

このように、新しくデータを追加したい場合は、テーブルの作成が伴ってくる。

データの整合性

2018年のデータを2017年に登録できてしまう。
CHECK制約をつけることで回避可能となっている。

CREATE TABLE Bugs_2017 (
 bug_id, 
 data_reported CHECK (EXTRACT(YEAR FROM date_reported) = 2017),
 etc...
);

新たにテーブルを作成する際は、CHECK制約の値の修正が必要となる。

データの同期

データの日付の更新を行う場合、以下のSQL文では不正なデータを作成してしまう。

UPDATE Bugs_2018
SET date_reported = '2017-12-27'
WHERE bug_id = 1;

そのため、対象テーブルから行を削除したのちデータを挿入する必要がある。

INSERT INTO Bugs_2017 (bug_id, date_reported, ...)
  SELECT bug_id, date_reported, ...
  FROM Bugs_2018
  WHERE bug_id = 1;

DELETE FROM Bugs_2017 WHERE bug_id = 1;

一意性の保証

テーブルを分割した場合、主キーの値が一意であることを保証する必要がある。
以下の場合によって対応が異なる。

シーケンスオブジェクトをサポートするデータベースを使用している場合
同一のシーケンスオブジェクトを使用して、分割されたテーブルに対して主キーの値を生成できる。
シーケンスオブジェクトがなく、テーブルごとにID生成のみをサポートするデータベースを使用している場合
主キーを作成するためにテーブルを定義する必要がある。

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

複数のテーブルを参照する際は、以下のSQLを使用する。

SELECT b.status, COUNT(*) AS count_per_status FROM (
  SELECT * FROM Bugs_2016
  UNION
  SELECT * FROM Bugs_2017
  UNION
  SELECT * FROM Bugs_2018 ) AS b
GROUP BY b.stats;

テーブルの個数が増えた場合、クエリの書き方を修正する必要が出てくる。

メタデータの同期

テーブルに対して新規に列を追加する際は、一つのテーブルにのみ追加される。
SQLの中でUNIONを使用している場合、結合するテーブルのカラムの数、順番、データ型が一致していないといけない。
そのため、SQLの書き換えが必要となる。

ALTER TABLE Bugs_2017 ADD COLUMN hours NUMERIC(9,2)

参照整合性の管理

従属テーブルがBugsテーブルを参照する場合、親テーブルが分割されていることにより外部キーの設定が定義できない。

SELECT * FROM Acounts as a 
INNER JOIN(
 SELECT * FROM Bugs_2016
 UNION ALL
 SELECT * FROM Bugs_2017
 UNION ALL
 SELECT * FROM Bugs_2018
) t ON a.account_id = t.reported_by

ある人が報告したすべてのバクを報告年にかかわらず検索を行いたい場合のSQLとなっている。
年数が増えた場合は結合するテーブルがどんどん増えていくこととなる。

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

テーブルだけでなく、テーブル列もメタデータトリブルになることがある。

例)メトリクスの承継を別々の列に格納するようなテーブル

CREATE TABLE ProjectHistory (
 bugs_fixed_2016   INT,
 bugs_fixed_2017   INT,
 bugs_fixed_2018   INT
)

年数が増えた場合に列の追加が必要となる。

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

以下の発言がある場合、アンチパターンの兆しである。

じゃあ、～ごとにテーブル(または列)を作る必要があるんだね
→ある列の値を使用してテーブルを分割している場合がある。
このデータベースがサポートしているテーブル(または列)の最大数は？
→必要な数を十分に上限にするための設計を初期段階でしているはずである。心配している場合は、設計を再検討する必要がある。
今朝アプリケーションが新規データの追加に失敗した理由がわかった。新しい年のデータを格納するためのテーブルを作成し忘れていた
→メタデータトリブルを用いた場合に生じる失敗の代表例である。
複数テーブルを一回で検索するためのクエリの実行方法は？全部のテーブルの列が共通しているんだけど
→同一構造を持つ複数のテーブルを頻繁に検索する場合は、一つのテーブルにデータをまとめておく。行を区別するための属性列を1つ追加しておくこと。
テーブル名のパラメータをどうやって渡せばいい？年が同的に付加されるテーブル名にクエリを実行する必要がある
→データが一つのテーブルに格納していれば問題ない。

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

このパターンを用いてもよい場合は、過去データを最新データから分離するようなアーカイブが目的の場合である。

現在のデータと過去のデータを合わせてクエリ実行する必要がない場合、
過去データは互換性を持つテーブルに対してコピーし、最新データを保存するテーブルから削除する。

解決策

テーブルのサイズが巨大化した際、手作業でテーブルを分解するのではなく、以下の3つの方法がある。

パーティショニング
従属テーブルの導入

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

パーティショニングの機能を用いることで簡単にテーブルの分割を行うことができる。
SQL serverでは、水平パーティショニング・垂直パーティショニングという分類ではなく、パーティショニングとして定義されている。
内容についてはパーティションテーブルとパーティションインデックスの作成を参照してください。

従属テーブルの導入

CREATE TABLE ProjectHistory (
 project_id   BIGINT,
 year         SMALLINT,
 bugs_fixed   INT,
 PRIMARY KEY(project_id,year),
 FOREIGN KEY(project_id) REFERENCES Project(project_id)
)

上記のSQLは、プロジェクトごとに年別の列を持たせて強引に1行にするのではなく、プロジェクトと組み合わせるごとに一行となるようにテーブルを定義している。
「マルチカラムアトリビュート」と同様の解決方法となる。詳細については、以下のリンクを参照してください。

「第七回マルチカラムアトリビュート」

最後に

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

SQLアンチパターン勉強会 第八回：メタデータトリブル