データベース物理設計のアンチパターン

逃げ恥の最終話を観終わりました。一言で言うと""最高""、さらにもう一言言えるとするならば""最高""です。

引き続き、 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 ('可愛い', 'かっこいい', 'おもしろい', '演技がうまい'))
);

enumを使う場合(MySQLの例)。

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

これだと問題があるで!って著者が言ってます。

  • 行削除時にファイルを削除しないとゴミファイルができる
  • トランザクションが分かれる
  • データベースのバックアップツールでバックアップされない
  • SQLのアクセス権限が適用されない

解決策

必要に応じてBLOB型を採用する

person_id name portrait_image
1 ガッキー (バイナリデータ)
2 星野源 (バイナリデータ)
3 かばお (バイナリデータ)

これで、上記の問題は解決されます。って言ってます。著者が。 選択肢があることを念頭に置いて、どっちがいいかを適切に検討せよってかんじ。

12. Index Shotgun

目的

パフォーマンスを最適化する

アンチパターン

闇雲にインデックスを使用する

「なんか遅いし、とりあえずインデックスはってみるか〜」ってやるやつですね。あるある。あかんで。更新系処理やvacuumが重くなります。

あとは、インデックスがあるのにそれを使えないクエリを書いちゃうやつ。 order by の指定順とか、 like の後方一致とか、気にせなあかんで。

解決策

「MENTOR」の原則に基づいて効果的なインデックス管理を行う

説明しよう!MENTORとは!!!

Measure

遅くて、かつ実行頻度が高いクエリを特定しよう!!!MySQLPostgreSQLなら、スロークエリが記録されるぞ!!いろんなツールもあるぞ!!

Explain

遅いクエリの実行計画をみよう!!

Nominate

Sequential Scan しているところをみつけよう!!インデックスをはると速くなるかもしれないぞ!!!

Test

インデックスを作成して、クエリを実行してみよう!!再び実行計画をみると、 Index Scanをするようになったか、それによって速くなったかが確認できるぞ!!

Optimise

インデックスをいいかんじにメモリにのせよう!!インデックスは使用頻度が高いので、キャッシュメモリに格納されやすいぞ!!キャッシュに割りあてるシステムメモリの量の設定値はだいたいデフォルトで低めになっているので、調整しよう!!

Rebuild

インデックスをメンテナンスしよう!!vacuumとかanalyzeにもコストがかかるので、運用の中で適切な実行頻度を設定しよう!!

まとめ

前回の論理設計のところでは、リレーショナルモデルかくあるべきみたいなのがちらついていましたが、今回は物理設計なので、けっこう実装依存なところも多いかもしれません。個人的に印象部会のは 31 Flavors で、自分が最近お仕事でenumを使って、まさにここで挙がっていたようなキツみをかんじていたので、耳が痛かったです。あとは、インデックスはりまくり状態はなりがちだと思うので、意志と意図をもって判断したいところですね。ガッキーが最高。