sqlアンチパターンと解決策-入門編-

はじめに

sqlアンチパターンの読書メモです。
書籍では25ものアンチパターンを取り扱っていましたが、今回はとりわけ初心者でも理解に難しくなく、すぐ実践できそうなものをピックアップしてまとめました。
※独断と偏見です。これは要らないとかあったほうがいいとかあれば意見下さい

目次

• 1章 信号無視(ジェイウォーク)
• 9章 丸め誤差(ラウンディングエラー)
• 7章 複数列属性(マルチカラムアトリビュート)
• 10章 31のフレーバー
• 15章 ランダムセレクション
• 19章 パスワード

1章 信号無視(ジェイウォーク)

-状況-
ブログ記事とそれに紐づくタグ(暮らし、学び、テクノロジー、国際みたいなものを複数紐付けられる)をうまいこと格納させたい

-アンチパターン-
カンマ区切りのフォーマットで格納する

id	title	tag_ids
1	sqlアンチパターン感想	1,2,4

のような形。

懸念事項

• リストの長さを決められない

• バリデーションが難しい
  「1,2,hoge」のような、無効な値が紛れ込んでしまうことをdb側で防げないのはよくない。

• sqlがいちいち複雑になってしまう
  例えば「記事に紐づくタグ名もjoinして取得したい」時はこう書くことになる。

SELECT * FORM articles AS a 
INNER JOIN tags AS t ON a.tag_id REGEXP '[[:<:]]' || t.id || '[[:>:]]'
WHERE a.id = 1;

本来なら数字の一致不一致のチェックだけで済むところを、正規表現を使って文字列処理のパターンマッチを行わなければならず、必要以上にに複雑になる。
また「既存のtag_idから特定のタグを削除したい」といった処理を書くときも同様に。

　- 更新対象のカラムを取得　
　- アプリケーション側でデータの中を変更

$value_to_remove = "2";
$contact_list = split(",", $row['tag_id's]);
$key_to_remove = array_search($value_to_remove, $cotact_list);
unset($contact_list['key_to_remove']);
$contact_list = join(",", $contact_list);

　- 保存
という手順を踏まなければならない。
更新やタグ数のカウントなども同様。

• インデックスを使うメリットもなくなる

など。

-解決策-
交差点テーブルを使う

[tags_relation]

id	article_id	tag_id
1	1	1
2	1	2
3	1	4

[Tags]

id	tag_name
1	くらし
2	学び
3	テクノロジー

上記の「記事に紐づくタグ名もjoinして取得したい」時も以下で事足りる。

SELECT * FORM articles AS a
INNER JOIN tags_relations AS tr ON a.id = tr.article_id
INNER JOIN tags AS t ON tr.tag_id = t.id
WHERE a.id = 1;

「既存のtag_idから特定のタグを削除したい」時も以下で事足りる。

DELETE FROM tags_relations WHERE article_id=1 AND tag_id=2;

9章 丸め誤差(ラウンディングエラー)

-状況-
小数点を使った計算を正しく行いたい

-アンチパターン-
Float型を使う
小数点をサポートする型としてFloat型を使うのは一般的とされているらしいが、円周率のような終わらない数を格納するために丸め誤差は避けられない。

懸念事項

例えば
SELECT hourly_rate FROM Accounts WHERE account_id=123;
で
結果：59.95
を取得したとする。
しかしこのhourly_rateがfload型で指定されている場合、格納されている値が必ずしも59.95とは限らない。

10億倍すると差異がわかる。
SELECT hourly_rate * 1000000000 FROM Accounts WHERE account_id=123;
結果：59950000762.939

ここまで細かくしないと出てこない誤差なんて気にしない、あったとしても問題ないと思われがち。
しかしFloat型の列の値と等価比較などを行った際には、一致しているように見えても一致していないと見なされてしまうことがある。
sumなども誤差が積もって正しい計算結果が出なくなることがある。

-解決策-
Numeric型またはDecimal型を使う
これらのデータ型は列の定義に指定した精度(整数の桁数)とスケール(小数の桁数)で数値を格納する。
例えば精度に9、スケールを2で指定すると、

１２３４５６７８９.12　　→　　格納できる
1234567891.12  →　格納できない
123456789.123  →　　格納できない

というふうにできる。
正確な値を格納することは難しいが、格納されたデータ同士の計算は正しく行える。

7章 複数列属性(マルチカラムアトリビュート)

-状況-
複数の値を持つ属性を格納したい
例）連絡先の電話番号を格納する場合。固定電話、携帯電話、fax、仕事用電話番号...など人によって持っている番号の種類や数が様々。

-アンチパターン-
値ごとに列を定義する

account_id	tel1	tel2	tel3
1	090-1111-2222	null	null
2	03-1111-2222	080-2222-3333	090-1111-2222
3	123-4444-5555	080-1111-2222	null

懸念事項

以下の時に処理を書くのが大変になってしまう。

• 電話番号の検索
  三つのカラムで検索をかけなければいけなくなる。

• 値の追加や削除
  どの列が空いているか確認できない。確認するためにアプリ側でソース書かなければいけなくなる。

• 一意性の保証
  複数の列に同じ値が格納されるのを防ぐことが難しい。

• そもそも三列で足りるのか
  途中でtel4というカラムを追加したら、このテーブルを扱うアプリケーションの全てのsqlをチェックしなければならない。新たな不具合を誘発してしまう。

-解決策-
従属テーブルを作る
信号無視(ジェイウォーク)パターンでも見た通り。

[Account]

id	name	phone_numbers_id
1	山田太郎	1

[Phone_numbers]

id	account_id	tel	kind
1	1	090-1111-2222	携帯番号

アンチパターンで上げた懸念事項は解消され、且つ電話番号の種類もわかりやすく格納できる。

10章 31のフレーバー

-状況-
選択肢がいくつかに限定される値を格納したい
例）顧客管理システムの敬称カラムには元々Mr,Mrs,Ms,Dr,Revしか入らない予定だったが、仏支社ができたことにより新たに仏人用敬称(M,Mme,Mlle)を格納できるようにすることになった。

-アンチパターン-
CHECK制約で列を特定の値に限定する

CREATE TABLE client (
  ...
  salutation VARCHAR(4)
    CHECK (salutation IN ('Mr', 'Mrs', 'Ms', 'Dr', 'Rev')),
);

懸念事項

• 新しい選択肢を追加するたびに、db定義を変えなければいけなくなる
  理想を言えば、テーブル定義の変更は頻繁に行うべきではない。アプリケーションへの影響範囲が大きいため、変更するたびに広い範囲のテストを行わなければいけなくなってしまうため。

• 選択肢がわかりにくい
  例えば入力フォームを作成しているフロントエンジニアにとって、選択肢を調べるためにdb定義書を見に行かなければならなくなる。

• 値の廃止が困難
  廃止した値が格納されないようにテーブル定義を変えたとして、既存のデータはどうするの？という話。

• 移植が困難
  CHECK制約は各種データベース製品間で仕様が統一されていないため、移植するとなった時に書き換え作業を行わなければいけなくなってしまう。

-解決策-
参照テーブルを作成する

[client]

id	client_name	salutation_id
1	Anne Jacqueline Hathaway	２

[salutation_status]

id	salutation
1	Mr
2	Mrs
3	Ms

Tip：ちなみに一つ前の電話番号格納テーブルを見てください。
お気づきでしょうか。
あのPhone_numbersテーブルにもまだアンチパターンが存在します。
これも適切な形に直します。

[Phone_numbers]

id	account_id	tel	kind
1	1	090-1111-2222	携帯番号

　　　↓

[Phone_numbers]

id	account_id	tel	kind_id
1	1	090-1111-2222	2

[Phone_Kinds]

id	kind
1	固定電話
2	携帯電話
3	Fax

ランダムセレクション

-状況-
ランダムにデータを取得したい時

-アンチパターン-
ランダムにソートして最初の行を取得する。ランダムに生成した値を主キー値にして検索かける

SELECT * FROM Articles ORDER BY RAND() LIMIT 1;

懸念事項

• インデックスのメリットを得られないため、パフォーマンスが悪くなる
  非決定性をもつRAND関数によってソートを行うとはつまりそういうこと。

• 必要な行が最初の一行だった場合、ソートする労力のほとんどが無駄になる

少量のデータに対して実行する分には問題ないが、本番稼動後データが徐々に増えてきたら大変。

-解決策-
offsetを使う

案1 1から最大値の主キー値をランダムに指定し、selectする
×欠番があってはならない。(論理削除、物理削除)

案2 案1に加え、欠番があった場合次の有効値をselectする
▲欠番の一つ上のキー値が選択される可能性が高くなるため、値が均等に選ばれなくなる。

SELECT a.* FROM Articles as a1
INNER JOIN (
  SELECT CEIL(RAND() * (SELECT MAX(id) FROM Articles)) AS article_id
) AS a2 ON a1.id >= a2.article_id
ORDER BY a1.article_id
LIMIT 1;

案3 全てのキー値のリストを受け取り、ランダムに一つ選択してselectする
▲データベースから全ての対象idを受け取るとサイズが非常の大きくなってしまう可能性もある
▲クエリを2回実行しなければいけなくなる

案4 Offset句を使う
Offset 取得を開始する位置を指定できる。

SELECT * FROM Articles LIMIT 1 OFFSET X ;

X：データの行数をカウントし、0と行数までの間の乱数。
例えばX=3だったとすると、最初の三件分のデータは取得しないで4件目からデータを取ってくる、といったことができる。

これは案1,2,3全ての弱点をカバーしている。

案5 各データベースの固有の機能を利用する
メジャーなdb製品には、ランダムに値を取得するための独自の解決策が実装されているのでそれを使う。

例えばOracleの場合SAMPLE句というものでできる。

SELECT * FROM (SELECT * FROM Articles SAMPLE (1)　ORDER BY dbms_random.value) WHERE ROWNUM = 1:

Tip：ところがどっこい、私の場合メインで使うのはmysqlです。
ではmysqlはどうしているのかとググってみたら、なかなかそれらしきものが出てこない...
それどころか結構order by rand()で満足している人も多くてびっくり。
悩ましい。アプリケーション側でもコードを書かなければいけなくなるけども、やっぱりoffset句が無難な選択肢のように思えます。

パスワード

-状況-
ユーザーidに対応したパスワードを格納したい

-アンチパターン-
パスワードを直に格納する

懸念事項

• パスワードの挿入に使うsqlや、ユーザーとパスワードが一致するか確認するためのsqlを攻撃者に読み取られたら大変

-解決策-
パスワードをハッシュ化 + ソルト

パスワードをネットワークの上で直にやりとりすることは危険。論外。

ハッシュ化してソルトを加える。

• ハッシュ
  ハッシュは基本的に不可逆とされているが、SHA-1やMD5など強度の不十分性が証明されたものもある。使おうとしているハッシュの規格には気をつける。
  パスワードの格納には最低でもSHA-256を使うことが望ましい。

• ソルト
  不可逆とはいえSHA1などの例にもあるように、時間と労力をかければ解読されてしまうこともありうる。また辞書攻撃の対策としても、暗号化前のパスワードにソルト(各パスワードごとに異なる無意味な文字列)を付けるようにする。

不可逆ということは、ユーザーが忘れてしまった場合、元のパスワードは永遠に失われる。
リカバリーしようとするのではなく、リセットし、問い合わせが来たら一時パスワードを送付するようにする。

おわりに

仮に理解が追いついていなくても、形だけでもやってはいけないものだと知ったほうがいいものもあると思います。特にdb設計に関しては。

それから物事に例外は付き物です。
例えば9章丸め誤差の部分でアンチパターンとして出てきたFloat型ですが、Numeric型ではなくこちらを使った方がいいパターンもあります。
そういった例外に関しても本には詳しく書いてあったので、db触る人はぜひ一読してみてください。
（逆にやっぱり例外なんてなくて等しく交差点テーブルにした方がいいんだなぁとか、それにすることの大切さもより実感できたりもしました。）

参考書籍

『sqlアンチパターン』
Bill Karwin　著、和田 卓人、和田 省二　監訳、児島 修　訳　O`REILLY

よく名前が上がっているsqlの良本です。
少しでもdb触るエンジニアには読んでほしい...テーブル設計は変えられないから...
わたしもがんばります。