はじめに
こんにちは。小川です。
以前から気になっていた「SQLアンチパターン」という本を読みました。
とても良い本で今まで気づかずにやっていたことがSQLアンチパターンなんだと知ることができました。
勉強になったので、学んだことを言語化して量が多いのでいくつかの記事に分けて紹介していこうと思います。
今回は第Ⅰ部のDB論理設計のアンチパターンについて記載します。
この記事は「SQLアンチパターン」の気づき（DB論理設計2）の続きになります。
7章.マルチカラムアトリビュート（複数列属性）
アンチパターン
複数の値を持つ属性を格納するために、複数の列を定義する。
例)
CREATE TABLE Bugs (
bug_id SERIAL PRIMARY KEY,
description VARCHAR(1000)
);
↓ バグテーブルにタグ機能を追加する
CREATE TABLE Bugs (
bug_id SERIAL PRIMARY KEY,
description VARCHAR(1000),
tag1 VARCHAR(20),
tag2 VARCHAR(20),
tag3 VARCHAR(20)
);
上記のように１つのテーブルに複数のタグをつけられるようにする
デメリット
- 値の検索や追加、削除が困難になる
- 一意性の保証ができなくなる
- 増加する値の処理を行う必要がある
→タグが3列では足りなくなった場合、新たに列を追加する作業が必要になる
解決策：従属テーブルを作成する
バグテーブルにタグ列を追加するのではなく、新しくタグテーブル(従属テーブル)を作成してバグテーブルに紐づける。
CREATE TABLE Tags (
bug_id BIGINT UNSIGNED NOT NULL,
tag VARCHAR(20),
PRIMARY KEY (bug_id, tag),
FOREIGN KEY (bug_id) REFERNCES Bugs(bug_id)
);
8章.メタデータトリブル（メタデータ大増殖）
アンチパターン
どのようなデータベースクエリでもデータの容量が増加してきた場合、パフォーマンスは低下します。
そのため私たちは行数が少ないテーブルへのクエリ実行の方が、行数が多い場合よりも早く処理できることを知っています。
しかしこれは、求めている処理が何かにかかわらず「すべてのテーブルの行は少ない方がいい」という誤った考えを導きます。
その結果以下の2つのアンチパターンに陥ってしまいます。
- 行数の多いテーブルを、複数のテーブルに分割する
- 行を複数列に分割する
例）年ごとにテーブルを分ける
CREATE TABLE Bugs_2016 (...);
CREATE TABLE Bugs_2017 (...);
CREATE TABLE Bugs_2018 (...);
デメリット
- データベースへ行を挿入する際に、挿入する値に応じて、挿入先テーブルを選択しなくてはならない
- データの整合性を管理する必要がある
→テーブル名に関連するデータが自動的に入るように制限する方法がないので、各テーブルでCHECK制約などを設ける必要がある - アプリケーション側で処理を間違えていた場合、データを同期させる必要がある
- 分割されたすべてのテーブルの間で、主キーが一意であることを保障する必要がある
- テーブルをまたいだクエリの実行に手間がかかる
- メタデータの同期
→列の追加が必要になった際、全てのテーブルに対する操作が必要になる
解決策：パーティショニングと正規化を行う
1．水平パーティショニングの使用
水平パーティショニングとは行を分割するいくつかのルールを定義して、分割する方法です。
テーブル作成時にパーティション分割を行うことで、物理的には分割されているがあたかも１つのテーブルを扱うかのように見えます。
例）
CREATE TABLE Bugs (
bug_id SERIAL PRIMARY KEY,
-- 他の列
date_reported DATE
) PARTITION BY HASH (YEAR(date_reported) )
PARTITIONS 4;
この例では「PARTITIONS 4」と記述しているため、物理的なテーブル数が4つに固定されています。
データベースが４年を超える期間のデータを扱う場合は、どれか1つのパーティションに2年以上の期間のデータが格納されることになります。
2．垂直パーティショニングの使用
水平パーティショニングは行で分割するのに対し、垂直パーティショニングは列で分割を行います。
3．従属テーブルの導入
7章のマルチカラムアトリビュートと同様の解決策となっています
まとめ
今回は第Ⅰ部のDB論理設計のアンチパターンについて一部まとめてみました。
この記事はこの本を読んで、私がこういう理解をしましたということを言語化してみました。
とても勉強になる本なので皆様もぜひ読んでみてください。
以上、小川でした。