テーブル設計の指針
特定の値以外入れて欲しくないカラムはCHECK制約を使う。
例えば、削除フラグなどで1、2のみしか入って欲しくない場合などは使えます。
なお、MySQLではCHECK制約は対応していません。（8.0.16以降では使えるようになりました。）
外部キー制約を使う。
たまに「アプリ側でバリデーションを行えば良い」という考えの人もいますが、DB側でちゃんと制限しましょう。アプリのバグとかで想定外の値が混入する可能性もあるためです。
日付データの持たせ方
DATETIME型で時間まで持たせる方法
基本的にこれで統一するのが一般的です。
文字列で持たせる方法
これも一つの手ですが、妥当性を確実にチェックできないので、13月だとか、32日だとか登録されるはずがないデータまで登録できてしまう可能性があるので使わない方が良いです。
DATE型で時間をもたせない方法
その場合は、基本的にHH:MM:SSは全て0で登録されることになります。
デメリット
もし誤ってNOW関数などで現在の日付などを登録してしまうと時間情報まで入ってしまうので、selectでの検索結果がおかしくなる可能性がある。
アンチパターン
用途が分かりにくいカラムがある。
- カラム名が省略系
- 他のカラムの内容に依存して値の意味が変わる可能性がある。
- カラムから取得した文字列をプログラムで分解する必要がある。
- マジックナンバーが使われている。
- 自由項目や予備項目などがある。
巨大なテーブル
NULL値が入ったカラム
もし、他の項目がNULL以外の値が入るのに、NULLが入るカラムがあるのであれば、それは別テーブルに切り出すべき。
間違っても、unknownや9999を入れるようにしてはいけない。
適切な型を使う。
TEXT型やLOB型はあまり使ってはいけない。
何でもかんでもVARCHARでもいけない。
テーブル間の関係が分かりにくい。
- 外部キー制約がない。
- キーとなるカラムの名前に一貫性がない。
複数の目的のテーブル
基本的に「複数の目的に使われるテーブル」はアンチパターンになります。
例
「一般ユーザー」と「管理者ユーザー」を一つのテーブルで管理する。
見分け方
- データの属性によって入る値が変わる。
- NULLが入るようなカラムがある。
メリット
- モデルとしてもシンプルになる。
- パフォーマンス面でも良い。
「複数の意味を含んだID」のアンチパターン
以下のようなデータ管理方法は管理カラムの数が減るので良さそうに見えますが、基本的にはアンチパターンになります。
- 「一般ユーザー」か「管理者ユーザー」かを「ユーザーIDの先頭1桁目」で判断する。
- 「100(市ID)121(町ID)」などのようにIDの中に複数の意味を持たせる。
こういったIDは「意味を含んだID」、「スマートID」、「論理ID」と呼んだりします。
デメリット
プログラムが複雑化する。
プログラムで状態を調べるためにいちいちsubstringなどで整形する関数を作らないといけない（カラム直接確認で済ませれない）
集計がしにくい。
あとで、集計が必要になった場合にcountもgroup byも使えずにSQL関数で一旦加工する必要がある。
substrなどのSQL関数を使ってしまうと関数を実行するまでRDBMSでは結果がわからないのでインデックスが効きません。よってフルテーブルスキャンになるので非常に処理速度も遅くなります。開発面や運用面でのコストも上がってしまいます。
UPDATE文を使っている。
DELETE、INSERT文の対で実行するのが良い。
UPDATEはデータ不整合が混入しやすい動作。ロジックもシンプルになる。
条件によって更新するというUPDATE文を使う。
また、UPDATE文を仮に使うとしても条件の違いによって何度もUPDATE文を使っていることはデメリットになります。
条件の違いによって何度もUPDATE文を発行してしまうのは以下のデメリットがあるのでアンチパターンになります。
- インデックスが効かないケースもあり性能が悪くなる。
- 前のUPDATE内容が後続のUPDATE内容に影響を与えてしまう。
そういう場合は、以下のようにCASE文を使って一括でUPDATE文を実行しましょう。
|
1 2 3 4 5 |
UPDATE SET 更新値 = CASE WHEN 条件1 THEN 値1 WHEN 条件2 THEN 値2 ENDな |
なお、条件と値の組み合わせをSQLの中にハードコーディングして埋め込んでしまうのは保守しずらくなるためそれらを定義した「パタメータテーブル」や事前に結果を集計しておく「テンポラリテーブル」を使うようにすると良いです。
「テンポラリテーブル（集計結果）」と「パラメータテーブル」をJOINして、条件に合うものは更新する、合わないものはそのままみたいな感じの設計にすると良いでしょう。
テンポラリテーブル
例えば、当月の集計結果などの情報のためだけのテーブルを作っておきます。
なお、集計の際のSELECT文にはロックがかかってしまうため、テンポラリテーブル（集計結果）を作らずに1文でUPDATEすることは基本的にできません。
パラメータテーブル
以下のようなテーブルです。例ではランクを決めるにあたっての条件を記載したりします。これを使うことで設定データを変えるだけでコードには変更が及ばないため保守作業コストを下げることが可能です。
| 現ランク | UPランク | DOWNランク | UP条件 | DOWN条件 |
|---|---|---|---|---|
| A | NULL | B | NULL | 50 |
| B | A | C | 50 | 20 |
| C | B | D | 20 | 10 |
| D | C | NULL | 10 | NULL |
「子テーブルが複数の親テーブルを持つ」ような設計(ポリモーフィック)
子テーブルのあるカラムの値によって紐づく親テーブルが変わると言う設計ですね。
デメリット
外部キーが使えない。
アプリ側としてはJOINする対象がデータを取り出してみるまでわからなくなってしまいます。そのため、親テーブルの両方をJOINしてからNULLの場所によって対象データを判断すると言うような運用が行われかねません。これは非常に非効率なクエリです。
外部キーが使えないので参照整合性は担保することもできません。
ページャーのアンチパターン（LIMIT、OFFSET）
アプリケーションにページャーを導入する場合はORDER BYの「LIMIT」と「OFFSET」を利用すると思います。実はこれも何にも考えずに使っているとパフォーマンスの落とし穴があります。
OFFSET
どこからデータ取得を開始するかの値です。
この値が大きくなればなるほどデータを取り出す件数が増えてしまうので注意が必要になります。
解決策
OFFSETは廃止してwhere句で事前に絞り込む
OFFSETは廃止して、where句で「前ページ最後の行のid以上」と言う条件を付け加えてあげます。そうすることでOFFSETの値が肥大化するたびに検索対象の行数が増大してしまうと言う現象を抑えることができます。
使えないケース
ただし、この手法は以下のケースでは使えません。
ORDER BY句の結果がidの順番とは関係なく、またはUNIQUEな値ではない。
例えば、nameなどのユニークではない値の場合など。
UNIONや、GROUP BYやHAVINGが使いたい場合
GROUP BYなどではidが使えない。また、これらには「ORDER BY句狙いのインデックス」が使えないため遅くなる。
制約をかけすぎる：外部キー制約が生み出すデッドロック
外部キー制約の子テーブルを更新すると、親テーブルの共有ロックを自動的に取るのでデッドロックの温床となる。
その対策としては、排他ロックを持たせることが良いが、排他ロックは正しく順番を持たせるためにパフォーマンスのボトルネックとなってしまう。
multirow insert(バルクインサート)を使う。
複数のINSERT文を一括実行できる機能です。
構文
|
1 |
INSERT INTO テーブル名 (列1, 列2、・・・) VALUES('値1', '値2'・・・),('値3', '値4'・・・), |
メリット
- パフォーマンスが良い。
デメリット
- サポートしていないDBMSもあります。
- エラーが発生した時にどの行でエラーが出たのかが分かりにくい。
ループの中で単純なSQLを呼び出す。
SELECTで情報を取得して、ループの中で単純なINSERT文を何度も呼び出すみたいな処理を書いた経験は誰でもあるのではないでしょうか？これは非常に分かりやすい書き方なのでよく採用されますが、いくつかの問題点を含んでいます。
問題点
パフォーマンスが悪くなる。
一括でSQLを実行する場合に比べて圧倒的に性能は悪くなります。ネットワークアクセスが増えるためです。
また、一回で取得するデータ量も少量になるので、I/O負荷を並列に処理しずらくなります。
ケース
数百行程度のループであればそれほど問題になることはないと思います。ただ、数百万回だったり、数千万回だったりのループが発生する場合はこれが顕著になります。
この記事へのコメントはありません。