はじめに
SQLについて最近学び始め、実際に業務で使用する際に避けるべきアンチパターンを前もって知っておくために「SQLアンチパターン」という書籍を購入しました。
さまざまなところで耳にする評価の高い本という印象があります。
アンチパターンとは
「問題の解決を意図しながらも、しばしば他の問題を生じさせてしまうような技法」のことです。
データベース論理設計アンチパターン
今回はデータベース論理設計のアンチパターンについて読みすすめるとともに、書籍中のワードや疑問点などについても調べてまとめていきたいと思います。
- ジェイウォーク(信号無視)
- ナイーブツリー(素朴な木)
ジェイウォーク(信号無視)
テーブルの論理設計時に、「多対多」という関連を表現しなければならないことがあります。 「多対多」が具体的にどういう関連なのかについては以下のQiitaを参考にしました。
https://qiita.com/ramuneru/items/db43589551dd0c00fef9
ここで取り上げられている例は、プログラミング学習サービスのユーザーとコースの関連です。
ユーザーから見るとコースを複数持つことができます。
コースから見るとユーザーを複数持つことができます。
１つ目のアンチパターンはこの交差点(多対多の関連を表現するための交差テーブル)の作成を避けるために陥りがちなアンチパターンです。
目的
この問題を解決して達成したい目標は、「複数の値を持つ属性を格納する」ことです。 列に1つのみ値を格納する場合は設計は簡単で、値に即したSQLデータ型を選択肢、値がその型の1つのインスタンスであることを表現します。 しかし、集合を列に収める場合にはどうすればよいのでしょうか。
アンチパターン-カンマ区切りフォーマット
この問題のアンチパターンは「カンマ区切りフォーマットのリストを格納する」ことです。 プロジェクトが始まって間もない頃に作成されたカラムは、時間の経過とともに複数の値を持つように変化しました。 データベースの構造に対する変更を最小限に抑えるために、あなたは対象データのデータ型をVARCHAR型に再定義し、カンマ区切りで連結して格納できるようにしました。 テーブルや列を追加せずに済み、一見うまくいったようにみえますが、この設計が原因でパフォーマンス上の問題やデータ整合性の問題が生じてしまいます。
ここでは例として、製品とアカウントの関係を取り上げます。 製品に連絡先を追加します。プロジェクトが進むにつれて、製品が複数の連絡先を保つ場合があることがわかってきました。 アカウントに対して複数の製品が紐づく1対多の関連だけでなく、製品に複数のアカウントが紐づく関連も考慮する必要があります。
問題点
特定のアカウントに関連する製品の検索
すべての外部キーが文字列連結されて1つのフィールドに格納されていると、等価性による値の比較ができなくなるためクエリを作るのが難しくなります。 何らかの文字列パターンに該当するかどうかをパターンマッチする必要があります。 パターンマッチを使用すると意図していない一致結果が返される可能性があり、インデックスを使用した場合のメリットも得られません。 また、パターンマッチ構文はデータベースエンジンによって構文が異なるため、ベンダー中立のSQLではなくなります。特定の製品に関連するアカウントの検索
カンマ区切りのリストの値を使って参照先のテーブルと結合(JOIN)するのは手間がかかり、効率的なものになりません。 正規表現を使用する場合はテーブルをスキャンしてすべての行について評価するためです。集約クエリの作成
集約クエリとは、COUNT,SUM,AVGなどの関数を使用したクエリです。 当然ですがこれらの関数はカンマ区切りリストではなく、複数の行に対して使われるように設計されています。 カンマ区切りリストにこれらを対応させること自体は可能ですが、非常にトリッキーなSQLを書くことになり、デバッグも難しくなります。特定の製品に関連するアカウントの更新
文字列連結によって、リストの末尾に新しいIDを追加することができます。 しかし、このような方法で連結した場合、リストのソート順を維持できなくなる場合があります。 また、IDをこの連結されたリストから削除する場合、SQLを2つ発行する必要があります。古いリストの取得と、新しいリストの保存です。アカウントIDの妥当性検証
IDに数値を入力したい場合でも、文字列連結のためにVARCHAR型を指定しています。 ユーザーがbananaのような無効な文字列を入力することをどうやったら防げるでしょうか。 SQLのデータ型が本来持っていた堅牢性を1つ失うことになります。区切り文字の選択
整数型ではなく文字列型の値を格納する場合、リストの個々の入力値にも区切り文字(カンマなど)が含まれることがあります。 このため、カンマを区切り文字に指定している場合には、入力した文字列としてのカンマなのか、区切り文字としてのカンマなのかが不明確になる可能性があります。(もちろんカンマ以外の文字を使用した場合でも同様の問題が発生します。)リストの長さの制限 例えば、データ型
VARCHAR(30)のカラムに格納できるエントリの数は各エントリの長さによって変わります。 エントリの長さが2文字の場合、10個のエントリを格納する事ができます。エントリの長さが6文字あった場合、格納できるのは4個となってしまいます。(カンマを含めて計算する必要があります)
アンチパターンを用いても良い場合
アプリケーションの要件によってはカンマ区切りのデータフォーマットが必要で、かつリスト内の各要素へ個別にアクセスする必要がない場合があります。 このような場合はカンマ区切りを用いることで他の場所からカンマ区切りのデータを受け取った場合にそのリストをそのまま列に格納でき、その後もそのまま取得できます。あえてバラバラにする必要はありません。
ただ、このようなケースは稀で、基本的には正規化されたデータベース構造の採用を検討するべきです。 正規化された構造のほうがアプリケーションコードの柔軟性を高め、データの整合性を保つ助けになります。
解決策: 交差テーブルを作成する
この問題の解決策は交差テーブルを作成することです。 製品テーブルにアカウントIDを保存するのではなく、新たに作成したテーブルの各行にアカウントIDを1つずつ格納します。 新たにContactsテーブルを作成することで製品とアカウントの間には「多対多」の関係が生じます。
テーブルが2つのテーブルを参照する外部キーを持つとき、そのテーブルは交差(インターセクション)テーブルと呼ばれます。 他にも、関連テーブル、結合テーブル、多対多テーブル、マッピングテーブルなどとも呼ばれます。
この解決策で、これまで上げたアンチパターンを用いた際に生じる問題がどう解決できるでしょうか。
特定のアカウントに関連する製品の検索
製品テーブルとContactsテーブルを結合することで簡単に行うことができます。 結合(JOIN)するので確かにパフォーマンスは低下します。 しかし、このクエリではアンチパターンであるカンマ区切り文字列より効果的にインデックスを使えるため、パフォーマンスは向上します。 複雑な正規表現を書く必要も、読む必要もなくなります。集約クエリの作成
製品ごとのアカウント数を数える際も単純にCOUNTが使用できます。製品の連絡先の更新
リストへの要素の追加や削除は交差テーブルへの行の挿入や削除で行えます。 各製品への参照はContactテーブルの個別の行に格納されているため、製品ごとに個別に追加や削除が行なえます。 アンチパターンでは古いリストの取得と新しいリストへの更新の2回SQLを発行する必要がありましたが、シンプルにINSERTできるようになりました。アカウントIDの妥当性検証
外部キーを用いて別テーブルの値を参照することで、入力の妥当性検証を行えます。 外部キー制約によって交差テーブルにはアカウントテーブルに存在するアカウントIDのみが格納されるようになります。 また、カラムにはINTEGERやDATEのようなSQLのデータ型を設定できるようになり、すべてのデータがこれらのデータ型であることを保証できます。区切り文字の選択
区切り文字がなくなるため入力内容に区切り文字が含まれているかどうかを気にしなくて良くなります。リストの長さの制限
各エントリは交差テーブルの個別の行に格納されているのでリストの長さはテーブルに物理的に格納できる行数と等しくなります。 エントリ数の上限を設けたい場合にはCOUNTを制限の基準にしてアプリケーション側で制御することで実現できます。その他のメリット
インデックスを用いることでパターンマッチと比べるとパフォーマンスの改善が図れます。 多くのデータベースでは外部キーとして宣言すると暗黙のうちにインデックスが作成されます。
また、交差テーブルに列を加えることで各エントリに属性を追加できます。製品に連絡先が追加された日付を記録したり、連絡先の優先順位を示す属性をつけたりできます。
ナイーブツリー(素朴な木)
コメントのスレッド機能など、再帰的な関連を持つデータに対するアンチパターンです。
アンチパターン-隣接リスト
この問題に対する解決策として多くの書籍や記事で推奨されているのは親のIDを持つ単純な方法です。 しかし、この方法は思慮が浅い素朴な解決策であるとも言えるでしょう。 親のIDをは同じテーブルにある別の行を参照します。 このような設計は隣接リストと呼ばれ、階層的なデータの格納に用いられる最も一般的な設計です。
問題点
隣接リストへのクエリ実行
あるコメントに対するすべての子孫を取得するクエリを書く際に問題になります。 階層の深さに関わらずすべての子孫を取得するクエリを実行しなければいけません。 隣接リストを使用する場合、ツリーの1つの階層が1つのJOINに対応しますが、SQLを書く際にはJOINの数を固定しなくてはならないからです。隣接リストのツリーのメンテナンス
INSERTやUPDATEは問題なく簡単に行えます。 ただし、ノードの削除は簡単ではありません。 サブツリー全体を削除したい場合、まず、すべての子孫を特定するために複数回クエリを実行し、次に外部キーの整合性制約を満たすために最下層から順番に子孫を削除する必要があります。 この操作は外部キー定義時にON DELETE CASCADE修飾子をつけることによって自動化できます。 ただし、子孫を削除する際のみで、特定のノードを削除した際にその子を昇格させたりする場合には対象ノードの作成前に子のparent_idを変更する必要があります。
アンチパターンを用いても良い場合
隣接リスト設計の長所はノードの直近の親と子を簡単に取得できることと、列の挿入が容易であることです。 データに対して必要な操作がこれらのみの場合は隣接リストは効果的に機能します。
解決策: 代替ツリーモデルを採用する
隣接リストモデルの代替となるツリーモデルを使用する。
経路列挙
隣接リストの欠点である先祖ノード取得の効率向上のために経路列挙という方法が存在します。 UNIXのパスのように再帰的なデータの経路を文字列で表現します。 例) 1/2/4, 1/2/5/6入れ子集合(Nested Set)
直近の親ではなく子孫の集合に関する情報を各ノードに格納します。 各ノードのnsleft及びnsrightと呼ばれる数値で表します。
nsleft...そのノードより下の階層にあるすべてのノードが持つ値より小さな値
nsright...そのノードより下の階層にあるすべてのノードが持つ値より大きな値
このモデルには隣接リストに劣る点があります。
個々のノードに対する挿入や削除の場合、新しいノードのnsleft値より大きいすべての左右のノードの値を再計算する必要があります。 適しているのは個々のノードの操作ではなくサブツリーに対する迅速かつ容易なクエリの実行が必要な場合です。
少し発想の転換が必要となる考え方となるので参考となるURLを貼っておきます。
https://gihyo.jp/dev/serial/01/sql_academy2/000501閉包テーブル
TreePathsテーブルを新たに定義します。それぞれが外部キーである2つの列を持ちます。 先祖/子孫関係を共有するノードの組み合わせを格納します。 各行のノードには自分自身を参照する行も追加します。 簡単なイメージはこんな感じ。
| 先祖 | 子孫 | 先祖 | 子孫 | 先祖 | 子孫 |
|---|---|---|---|---|---|
| 1 | 1 | 1 | 7 | 4 | 6 |
| 1 | 2 | 2 | 2 | 4 | 7 |
| 1 | 3 | 2 | 3 | 5 | 5 |
| 1 | 4 | 3 | 3 | 6 | 6 |
| 1 | 5 | 4 | 4 | 6 | 7 |
| 1 | 6 | 4 | 5 | 7 | 7 |
詳細は省略しますが、このモデルであれば個々のノードの追加や削除を比較的簡単に行うことができます。 デメリットとしては別にテーブルを定義する必要があることです。 また、隣接リストと比べると直近の親や子を取得することが難しくなりますが、この問題は閉包テーブルにpath_lengthのようなカラムを追加することで解決できます。自身への参照は0,子の場合は1,孫の場合は2のように値を追加することで簡単に直近の親や子を取得することができます。
参考: https://www.ritolab.com/entry/235
結局どの設計を使うべきか
| 設計 | テーブル数 | 子へのクエリ実行 | ツリーへのクエリ実行 | 挿入 | 削除 | 参照整合性維持 |
|---|---|---|---|---|---|---|
| 隣接リスト | 1 | 簡単 | 難しい | 簡単 | 簡単 | 可能 |
| 再帰クエリ | 1 | 簡単 | 簡単 | 簡単 | 簡単 | 可能 |
| 経路列挙 | 1 | 簡単 | 簡単 | 簡単 | 簡単 | 不可 |
| 入れ子集合 | 1 | 難しい | 難しい | 難しい | 難しい | 不可 |
| 閉包テーブル | 2 | 簡単 | 簡単 | 簡単 | 簡単 | 可能 |
再帰クエリはデータベースによって使用できるものと使用できないものがあります。
経路列挙はどうしても文字列の長さという上限が存在することと、文字列の操作に関してはアプリケーション側で操作するしかなく、整合性の保証ができないというデメリットがあります。
入れ子集合はどうしても構造が複雑化してしまうためデメリットが目立ちます。
閉包テーブルは多くの問題を解決しますがテーブルが新しく必要であることと、階層が深くなればなるほど多くの行数が必要となります。
構造的なメリットとリソースのデメリットのトレードオフになります。
所感
今回はアンチパターンの中からジェイウォークとナイーブツリーについてまとめました。
(THE JAYWALKを思い出しました。)
SQLで使用する各種クエリの実行が容易であることやデータ構造としてのシンプルさを保つことがアンチパターンを避けるための鍵になりそうです。
DB設計の経験がないため、あらかじめアンチパターンを勉強することで業務で使用されているDB設計の意図を汲み取りやすくなったなと読み進めるに従って感じています。
「SQLアンチパターン」についてはまた記事にしていきたいと思います。
