なぜ気軽にテーブルにカラムを足してはいけないのか
はじめに
データベース（RDB）の設計で深く考えずにテーブルにカラムを追加してしまうことはありませんか？テーブルの追加よりもアプリケーション側での変更が少ないので、心理的ハードルが低いこともあるでしょう。しかし、そうしてカラム追加していくうちに取り返しのつかないことになるかもしれません。
この記事では大きく以下の3つに分けて、なぜ気軽にテーブルにカラムを足してはいけないのかを説明します。
- そもそもデータベース設計がちゃんとできていないことの兆候である
- 変更のコストが大きい
- インデックスの設計が難しくなる
そもそもデータベース設計がちゃんとできていないことの兆候である
気軽にカラム追加して行き着く先はテーブルのカラム数が多くなることですが、カラムが多すぎるテーブルはDatabase Smell[1]（データベースの設計上の悪い兆候）の1つであるとされています。
カラムを気軽に増やしてしまう場合、以下の問題があることが多いです。
- 正規化ができていない
- 複数エンティティを混在させてしまう
それぞれについて詳しく見ていきましょう。
正規化ができていない
正規化はデータベース設計の基本中の基本です。その正規化ができていないことの代表例であり、カラム追加の最も初歩的な失敗として 「マルチカラムアトリビュート」があります。これは書籍SQLアンチパターン[2]での呼び名で、列持ちテーブルとも呼ばれます。以下のようなテーブル設計のことを言います。
連絡先テーブル
| user_id | phone1 | phone2 | phone3 |
|---|---|---|---|
| 1 | 090-xxxx-xxxx | 080-xxxx-xxxx | NULL |
| 2 | 070-xxxx-xxxx | NULL | NULL |
| 3 | 090-xxxx-xxxx | 050-xxxx-xxxx | 03-xxxx-xxxx |
最初は1つの電話番号しか必要なかったのに、2つ目、3つ目と必要になるたびにカラムを追加してしまった例です。この設計には、以下の問題があります。
- 特定の電話番号検索するには全てのカラムを検索する必要がある
- 一意性の保証ができない
- 更新時にどのカラムを更新するかのロジックが必要になる
では、どのように設計すればよいかというと、電話番号を別テーブルに分割し、1対Nの関係を持たせることです。このように設計することで、上記の問題は解決されます。
連絡先テーブル
| id | user_id | priority | phone |
|---|---|---|---|
| 1 | 1 | 1 | 090-xxxx-xxxx |
| 2 | 1 | 2 | 080-xxxx-xxxx |
| 3 | 2 | 1 | 070-xxxx-xxxx |
| 4 | 3 | 1 | 090-xxxx-xxxx |
| 5 | 3 | 2 | 050-xxxx-xxxx |
| 6 | 3 | 3 | 03-xxxx-xxxx |
データベース設計の学習のためによく参照される、達人に学ぶdb設計徹底指南書[3]の第一版の紙面のほとんどを使って正規化について説明されていました。そのくらい、データベース設計において、正規化は重要かつ基本的なことです。パフォーマンスの問題などで正規化を崩すことが必要な時もありますが、最初の設計としては正規化するところから始めることをお勧めします。
複数エンティティを混在させてしまう
カラムが増えてしまうもう1つの原因として、複数のエンティティ（データモデル内で個別に識別可能な要素やオブジェクトを表す概念）が1つのテーブルに混在してしまうことがあります。例えば最初に以下のようなテーブルがあったとします。このテーブルは注文エンティティを表現したテーブルです。
注文テーブル
| id | user_id | product_id | ordered_on |
|---|---|---|---|
| 1 | 1 | 101 | 2024-01-01 |
| 2 | 2 | 102 | 2024-01-02 |
| 3 | 1 | 103 | 2024-01-03 |
ところが、今までは注文の情報しか必要なかったところに、ユーザー向けの画面上に発送日の列だけ足して、入力できるようにしたいという要望が入ったとします。この時に特に考えずユーザー向けの画面同様に、データベースのテーブルにもカラムを追加してしまうと以下のようになります。
注文テーブル
| id | user_id | product_id | ordered_on | shipped_on |
|---|---|---|---|---|
| 1 | 1 | 101 | 2024-01-01 | 2024-01-05 |
| 2 | 2 | 102 | 2024-01-02 | NULL |
| 3 | 1 | 103 | 2024-01-03 | NULL |
この設計は、注文エンティティと発送エンティティの一部が混在してしまっている状態です。発送エンティティが混在しているのであとから発送に関する情報をもっと入れたいとなったときに、ここに追加し続けてしまうことになるでしょう。この場合も発送は別のテーブルとして切り出すのが適切です。
よくある失敗パターンとしては安直に日時(xxx_at / xxx_on)やフラグ(xxx_flag)やステータス(xxx_status)のカラムを追加することです。これらを追加したくなったときには他のエンティティではないかと疑いましょう。
変更のコストが大きい
アプリケーションのリファクタリングと比べて、データベースのリファクタリングはコスト（時間・工数・リスク）が大きいです。影響範囲がどの程度あるか、既存データがどの程度あるか、ダウンタイムがどの程度許容されるか、などを考慮する必要があります。データベースのリファクタリングはアプリケーションのリファクタリングよりもコストが高いことを理解しておきましょう。
いい話をすることで有名なそーだいさん[4]が最近のブログでとても良いことを言っていました。
データベースリファクタリングは影響範囲も広く、時間がかかる。 だからこそ一番必要なのはやり切るぞ！という覚悟である。
そーだいさんが書いている通り、覚悟が必要です。気軽に追加したカラムを後から削除するのはとても大変です。
具体的にどのようなことにコストがかかるのかを見ていきましょう。
移行時に負荷・ロックの発生により長時間のダウンタイムが発生する可能性がある
ALTER TABLEは多くのケースでロックを取ります。レコード数が多いとき、ALTER TABLEの実行時間が長くなり、数時間そのテーブルに対する書き込みができなくなる場合もあります。新テーブルへのINSERTにした場合もレコード数が多い場合、実行時間が長くかかったり、データベースのCPUを圧迫する可能性があります。事前にテスト環境でデータ移行のリハーサルを行い、実行時間やデータベースの負荷を確認しておくことが重要です。
利用しているRDBMSでどのようなロックを取るかは意識して運用すると安全です。
例えば、PostgreSQLだと、ALTER TABLE がどのようなロックを取るかは以下の記事が参考になります。
Dual Write期間を設ける可能性がある
データ移行が大規模で、リスクが大きい場合、アプリケーション側で新旧両方のテーブルに書き込みむDual Writeを行うというプラクティスがあります。これにより、アプリケーションのロールバックが可能になり、データ移行のリスクを減らすことができます。しかし、この場合、アプリケーション内部での複雑性が増し、コードの変更にかかるコストが増大します。リリースも複数段階に分けて行う必要があり、スケジュール管理も複雑になります。
変更した時よりも時間が経つほど移行のコストは大きくなる
移行自体のコスト
レコード数が大きくなると、上述したロックを取る時間やリソースの逼迫に大きく影響します。場合によっては8時間以上かかることもあるので、「一晩のメンテだけで済むと思ってたら翌営業日の朝になっても終らない」なんてこともありえます。
本番環境でデータ移行を実施するときに「postgresql alter table 終わらない」というようなGoogle検索をしてしまうことがないようにしましょう。
影響範囲を調べるコスト
カラムを別テーブルに移動させる際に、アプリケーションの影響範囲を調査する必要があります。この時、カラムに依存していなくても、テーブルへの依存があると、カラムに依存がないかどうかを確認する必要が出てきます。時間が経つほど、テーブルに対する依存が増えるため、カラムの依存調査にかかる手間が増えます。
また、データベースに入っているデータそのものはアプリケーションコードのようにGitを使って差分管理することはもちろんできないので、全く予期せぬ値が入っていることもありえます。保証してくれるのはデータベースのスキーマ定義のみです。今のアプリケーションコードでは入りえない値が過去の期間だけ存在したバグによって混入することもありえます。
システムとしてどう使われるかも、時間の推移とユーザーの増加によって変わりえます。もともと想定していたのとは違う使われ方をしていたり、想定しない値が入っている可能性もあります。
いずれにせよ、昔からある膨大なレコードに対しては事前に入念な検証が必要になります。
インデックスの設計が難しくなる
一般的に、カラムが少ないほどインデックス設計は楽です。逆にカラム数が増えるほど、設計は難しいものになりやすいです。
インデックスは１つのテーブルに対してスキャンする際には1つしか使われません。そのため、複数のカラムに対してインデックスを使った検索を行いたい場合は、複合インデックスを使います。しかし、複合インデックスも全てのカラムにつければいいわけではありません。一般的に複合インデックスは左方一致する条件でしか利用されません（左から順番にしか使われない）。
例えば、以下のようなテーブルとインデックスがあるとします。
書籍テーブル
| id | name | category_id | publisher_id |
|---|---|---|---|
| 1 | A | 1 | 1 |
| 2 | B | 2 | 2 |
| 3 | C | 3 | 3 |
インデックス
CREATE INDEX books_index ON books (category_id, publisher_id)
このインデックスはbooksテーブルに対して、category_idとpublisher_idでの複合インデックです。
複合インデックスはインデックス定義の左側から一致しているカラムが検索条件に含まれる場合、検索時に利用されます。
以下のようなクエリは検索時にインデックスが利用されます。
-- 複合インデックスで指定されているすべての列を含む
SELECT * FROM books WHERE category_id = 1 and publisher_id = 1
-- 複合インデックスで指定されている左側の列を含む
SELECT * FROM books WHERE category_id = 1
しかし、以下のような検索をする際にはこのインデックスは利用されません。インデックスの1番左に定義されているカラムが利用されていないからです。
-- 複合インデックスで指定されている右側の列のみを含む
SELECT * FROM books WHERE publisher_id = 1
しかし、むやみやたらにインデックスを増やすと更新時のパフォーマンスが大きく劣化します。SQLアンチパターンで説明されているインデックスショットガンという問題です。
気軽にカラムを増やすと後でパフォーマンスチューニングをする際にも足枷になりやすいことがわかります。
慣れるより習う （習うより慣れよではない）
データベース設計の失敗は時間が経ってから気づくことが多いです。そのため、自分で失敗した内容から学ばずに、異動や転職などで新しい環境に行って同じ失敗を繰り返してしまうこともあるでしょう。逆に、他の人が失敗した内容を見て学ぶことはできます。しかし、問題が大きくなってしまった後では理想の状態を描くことが難しいのも確かです。
そーだいさんも「DBの問題は忘れた頃にやってくると」おっしゃっていますが、まさにその通りです。
学習のためにいい本や資料はたくさんあります。他の方の書かれた記事ですが、大変見事にまとまっています。
終わりに
カラムの追加はデータベースの操作の中でも基本的な操作ですが、さまざまな問題を起こしうることをお分かりいただけたでしょうか？
カラム追加が適切な場合ももちろんありますが、適切なデータモデリング・テーブル設計が行われた上で初めて判断できることです。適切にデータベース設計ができるようになるまでは経験と学習が必要です。
上で貼った記事は網羅的な分、膨大なので個人的なおすすめの書籍を載せてこの記事の結びとします。
-
Refactoring Databases: Evolutionary Database Design の 2.4 Database Smellsに記載されています。 ↩︎
-
達人に学ぶDB設計徹底指南書 は第２版で大幅に加筆されており、内容も刷新されています。購入する場合は第２版をお勧めします。
達人に学ぶDB設計徹底指南書 第2版 ↩︎ -
そーだいさんは今回のテーマにも関連のある素晴らしい本を書かれています。こちらもおすすめです。失敗から学ぶ RDBの正しい歩き方 ↩︎
Discussion