逃げ恥の最終話を観終わりました。一言で言うと""最高""、さらにもう一言言えるとするならば""最高""です。
引き続き、 O'Reilly Japan - SQLアンチパターン の中身をヨーグルトくらいの柔らかさに噛み砕くシリーズ。
前回は、 「Ⅰ部 データベース論理設計のアンチパターン」でした。今回は「Ⅱ部 データベース物理設計のアンチパターン」です。4個のアンチパターンが紹介されています。
9. Rounding Errors
目的
小数値を使用する
アンチパターン
FLOAT データ型を使用する
浮動小数点数を使用するからには、丸め誤差が避けられません。
以下はガッキーの可愛さを示す数値を FLOAT型のcuteness列に持っている場合です。
select cuteness from Actors where name = 'ガッキー'; --> 59.95 select cuteness * 1000000000 from Actors where name = 'ガッキー'; --> 59950000762.939
59950000000 かと思いきや実は違った、というやつです。 「ガッキーの可愛さってどれくらいだっけ？」というようにざっくり確認する分には問題がないのですが、「ガッキーの可愛さは59.95」という厳密な確認が問題になります。
select * from Actors where cuteness = 59.95; --> 一致する行なし
解決策
NUMERIC データ型を利用する
NUMERICとかDESCIMALを使いましょう。これらは精度を指定でいます。
-- 第一引数 : 精度。整数部の桁数。 -- 第二引数 : スケール。小数部の桁数。 alter table Actors add column cuteness numeric(9, 2);
10. 31 Flavors
目的
列を特定の値に限定する
俳優の情報が一覧化されているテーブルがあり、そこに俳優の特徴を表す列があるとします。その列は「可愛い」「かっこいい」「おもしろい」「演技がうまい」という4通りの属性をとり得るとします。
アンチパターン
限定する値を列定義で指定する
check制約を使う場合。
create table Actors ( -- 他の列・・・ feature varchar(20) check ( feature in ('可愛い', 'かっこいい', 'おもしろい', '演技がうまい')) );
create table Actors ( -- 他の列・・・ feature enum('可愛い', 'かっこいい', 'おもしろい', '演技がうまい'), );
これには以下のような問題があります。
- 取りうる値を知るためにはシステムカタログを参照する(複雑なクエリになる)必要がある
- 追加・削除する構文がなく、新しい値を追加する場合に新たに作成する必要がある
- そのためには、既存の取りうる値を取得する必要がある
解決策
限定する値をデータで指定する
以下のようなテーブルを作成し、 Actors.feature の外部キーに ActorFeatures.feature を指定すれば良いです。
ActorFeatures
| feature |
|---|
| 可愛い |
| かっこいい |
| おもしろい |
| 演技がうまい |
11. Phantom Files
目的
画像をはじめとする大容量メディアファイルを格納する
アンチパターン
物理ファイルの仕様を必須と思い込む
画像などのファイルは、外部のファイルシステムに置いて、ファイルパスやidで紐付ける、というのがわりと一般的です。
Persons
| person_id | name | portrait_image_id |
|---|---|---|
| 1 | ガッキー | 30 |
| 2 | 星野源 | 8429 |
| 3 | かばお | 494 |
これだと問題があるで！って著者が言ってます。
解決策
必要に応じてBLOB型を採用する
| person_id | name | portrait_image |
|---|---|---|
| 1 | ガッキー | (バイナリデータ) |
| 2 | 星野源 | (バイナリデータ) |
| 3 | かばお | (バイナリデータ) |
これで、上記の問題は解決されます。って言ってます。著者が。 選択肢があることを念頭に置いて、どっちがいいかを適切に検討せよってかんじ。
12. Index Shotgun
目的
パフォーマンスを最適化する
アンチパターン
闇雲にインデックスを使用する
「なんか遅いし、とりあえずインデックスはってみるか〜」ってやるやつですね。あるある。あかんで。更新系処理やvacuumが重くなります。
あとは、インデックスがあるのにそれを使えないクエリを書いちゃうやつ。 order by の指定順とか、 like の後方一致とか、気にせなあかんで。
解決策
「MENTOR」の原則に基づいて効果的なインデックス管理を行う
説明しよう！MENTORとは！！！
Measure
遅くて、かつ実行頻度が高いクエリを特定しよう！！！MySQLとPostgreSQLなら、スロークエリが記録されるぞ！！いろんなツールもあるぞ！！
Explain
遅いクエリの実行計画をみよう！！
Nominate
Sequential Scan しているところをみつけよう！！インデックスをはると速くなるかもしれないぞ！！！
Test
インデックスを作成して、クエリを実行してみよう！！再び実行計画をみると、 Index Scanをするようになったか、それによって速くなったかが確認できるぞ！！
Optimise
インデックスをいいかんじにメモリにのせよう！！インデックスは使用頻度が高いので、キャッシュメモリに格納されやすいぞ！！キャッシュに割りあてるシステムメモリの量の設定値はだいたいデフォルトで低めになっているので、調整しよう！！
Rebuild
インデックスをメンテナンスしよう！！vacuumとかanalyzeにもコストがかかるので、運用の中で適切な実行頻度を設定しよう！！
まとめ
前回の論理設計のところでは、リレーショナルモデルかくあるべきみたいなのがちらついていましたが、今回は物理設計なので、けっこう実装依存なところも多いかもしれません。個人的に印象部会のは 31 Flavors で、自分が最近お仕事でenumを使って、まさにここで挙がっていたようなキツみをかんじていたので、耳が痛かったです。あとは、インデックスはりまくり状態はなりがちだと思うので、意志と意図をもって判断したいところですね。ガッキーが最高。