はじめに
今回も引き続き「SQLアンチパターン」を読み進めていきます。
今回から第二部「データベース物理設計のアンチパターン」についてまとめます。
ラウンディングエラー(丸め誤差)
目的: 整数の代わりに小数値を使用する
小数を扱いたい場合、かつ計算の精度が求められるパターンを想定。
アンチパターン: FLOATデータ型を用いる
問題点
「10進数で記述できるすべての値を、２進数として格納できるわけではない」
丸め誤差が発生して計算の精度に問題が発生する。
また、和を計算するより積を計算する場合に誤差累積が大きくなり問題となる。
丸め誤差 参考: https://gihyo.jp/dev/serial/01/java-calculation/0012
解決策
- NUMERICデータ型を使用する。
- DECIMALデータ型を使用する。
上記のようなデータ型は列定義に指定した精度で数値を格納できる。
NUMERIC型とDECIMAL型は同一の振る舞いをするシノニム。
参考: MySQL公式 https://dev.mysql.com/doc/refman/5.6/ja/fixed-point-types.html
FLOATを用いる際は概数として扱う。
サーティワンのフレーバー(31のフレーバー)
目的: 列を特定の値に限定する
列に格納できる値を限定された値に制限する。
Enum型のように扱いたい場合。
MySQLの場合はENUMが存在する。
参考: https://dev.mysql.com/doc/refman/5.6/ja/enum.html
その他RDBMSの場合はCHECK制約を定義するなどして特定の値に限定する事ができる。
アンチパターン: 限定する値を列定義で指定する
有効なデータ値を列定義時に指定する。つまり、メタデータに有効なデータ値を設定する。
問題点
- 有効なデータがなにかをアプリケーション側から調べることが難しい
- 値の追加、及び廃止や移植が困難になる
解決策
- 参照テーブルを作成する。
作成した参照テーブルを参照するようデータに外部キー制約をつけることで列に入る値を制限する。
この形にしておくと値の追加、廃止、移植が容易になる。
ファントムファイル(幻のファイル)
目的: 画像などの大容量メディアファイルを格納する
多くのエンジニアが取る方法は以下の二通りある。 1. 画像のバイナリデータをBLOB型として格納する 2. ファイルシステムにファイルとして格納し、ファイルパスをVARCHARとしてデータベースに格納する
アンチパターン: 物理ファイルの使用を必須と思いこむ
先程あげたパターンの2つめのパターンがアンチパターンとして紹介されている。
問題点
- ファイルの削除時の問題
画像がデータベースの外にある場合、データベースで画像へのパスを含む行を削除しても、そのパスの指定先のファイルは自動的に削除されるわけではない。 アプリケーション側での対応が必要となる。 - トランザクション分離の問題
トランザクション分離については以前記事でも少し触れました。
参考:https://taisei-miyaji.hatenadiary.com/entry/2022/08/05/234907
ここで問題とされているのは、ロールバック時にファイルが復元されない点。 データベースの行削除はロールバック対象なのでもとに戻るが、ファイルはロールバックの対象ではないのでもとに戻らない。 - データベースのバックアップツール使用時における問題
大抵のデータベース製品では以下のようにクライアントツールを提供している。
これらツールのサポートを得られないので復旧したデータと画像を紐付ける処理を自分で用意する必要がある。
| RDBMS | バックアップツール |
|---|---|
| MySQL | mysqldump |
| Oracle | rman |
| PostgreSQL | pg_dump |
| SQLite | .dump |
SQLアクセス権限における問題
SQLにはGRANTやREVOKEなどを用いてテーブルや列へのアクセスを管理する機能がありますが、データベース内で文字列で指定された外部ファイルを対象にすることはできない。ファイルはSQLデータ型ではない
データベースにファイルへのパスを格納する場合、その文字列が正当なパス名であることをSQLデータ型で検証することはできない。 指定したパスにファイルが存在することも検証できず、ファイルの移動や削除が起きてもデータベース内の文字列は自動的に更新されない。
アンチパターンを使う場合のメリット
- データベースの容量を減らせる
- データベースのバックアップが短時間で終了し、容量を抑えることができる。
- 画像ファイルがデータベース外にあればプレビューや編集が容易になる。
解決策: 必要に応じてBLOB型を使用する
これまで挙げたような問題に該当する場合は、データベースの内部に画像ファイルを格納することを考えるべき。
脳死で外部に格納するというエンジニアの一般論にただ従うのではなく、双方の方法の問題点を検討した上で選択する。
インデックスショットガン(闇雲インデックス)
目的: パフォーマンスを最適化する
データベースを扱うエンジニアにとって最大のテーマであるパフォーマンス。
その向上のためにインデックスを効果的に使用することが重要だが、いつ、どのように使用するべきかについて理解していない場合にアンチパターンに陥る事がある。
インデックスについては以前記事にしたのでよければこちらも参考にしてください。
参考: https://taisei-miyaji.hatenadiary.com/entry/2022/06/16/084952
アンチパターン: 闇雲にインデックスを使用する
インデックスを使用するか否かの判断をインデックスの仕組みを理解しないまま行うと以下のようなミスが発生する。
- インデックスを全く定義しないか、少ししかインデックスを定義しなくなってしまう
- インデックスを多く定義しすぎるか、役立たないインデックスを定義してしまう
- インデックスを活用しないクエリを実行してしまう
問題点
インデックスを全く定義しない
インデックスの更新によってデータベースにオーバーヘッドが発生するが、そのオーバーヘッドを避けるためにインデックスの使用を避けようとしてしまう。
インデックスにはオーバーヘッドを正当化するだけのメリットがあることを理解する必要がある。
ちなみにインデックスはANSI SQL規格では言及されておらず、実装は各データベース製品が独自に行なっている。インデックスを多く定義しすぎる
主キーには自動的にインデックスが作成されるのでインデックスを明示的に貼るのは冗長。
summaryのような長い文字列を格納するカラムにインデックスを貼ったとしても、検索やソートを実行することは考えにくい。
解決策: MENTORの原則に基づいて効果的なインデックス管理を行う
- Measure (測定) クエリ実行時間等の測定
- Explain (解析) クエリの処理が遅くなっている原因の解析
- Nominate (指名) クエリ実行計画を読んでクエリがインデックスを使用しないでテーブルにアクセスしている箇所を作成
- Test (テスト) インデックスの作成後、その効果を確認する
- Optimize (最適化) キャッシュに割り当てるシステムメモリ量の適切な設定等を行う
- Rebuild (再構築) インデックスのメンテナンスを実施する
所感
今回はデータベース物理設計のアンチパターンについてまとめました。
論理設計のときと同様、SQLで用意されている機能をできるだけ利用し、アプリケーション側での操作や制約をしないことが多く挙げられている印象です。
また、物理設計においてはデータベースの仕組みやコンピュータの仕組みを理解した上で、クエリのパフォーマンスについて意識するパターンが多いなと感じました。
個人的にはSQLクエリに対する理解度は課題に感じているので引き続き勉強していきたいと思います。
