はじめに
開発をしていく上で、SQLの非効率な書き方やDB設計のアンチパターンを知っておきたいと思いました。
著書「SQLアンチパターン」を読んで、各部から印象に残った章の内容を抜粋してまとめていきます。
読みづらいところなどあるかと思いますが、少しでも参考になれば幸いです。
SQLアンチパターンの要約一覧です。
他の章についても要約してます！
II部 データベース物理設計のアンチパターン
12章 インデックスショットガン（闇雲インデックス）
インデックスとは
書籍の索引のようなもので、データベース内の特定の列（または複数の列）に対して作成されるデータ構造です。この索引を利用することで、データベースのレコードを高速に検索することができます。
アンチパターン：闇雲にインデックスを使用する
インデックスを理解しないまま、使うか否かの判断をすると以下3つのいずれかが起こります。
- インデックスを全く定義しないか、少ししかインデックスを定義しなくなってしまう
- インデックスを多く定義しすぎるか、役に立たないインデックスを定義してしまう
- インデックスを活用しないクエリを実行してしまう
インデックスを全く定義しない
インデックス更新によってオーバーヘッドが生じると、多くのデータベース関連の書物や記事には書かれています。
一部の開発者たちは、そのオーバーヘッドを排除しようとして、インデックスそのものを使用しなければ良いと考えます。
しかし、通常のアプリケーションでは、テーブルに対するクエリ発行回数の方が、テーブルの更新回数よりも何百倍も多いものです。インデックスを用いてクエリを実行するたびに、インデックス維持のために生じたオーバーヘッドを取り戻しているのです。
例えば、bug_id列に設定したインデックスは以下のステートメントで役立ちます。
UPDATE Bugs SET status = 'FIXED' WHERE bug_id = 1234;
インデックスを定義していない列で値を検索するステートメントでは、一致する行を探すためにテーブル全体を検索しなければなりません。
UPDATE Bugs SET status = 'OBSOLETE' WHERE date_reported < '2000-01-01';
インデックスを多く定義しすぎる
インデックスのメリットを得られるのは、インデックスを使うクエリを実行する時です。使用されないインデックスを作るメリットはありません。
CREATE TABLE Bugs (
bug_id SERIAL PRIMARY KEY,
date_reported DATE NOT NULL,
summary VARCHAR(80) NOT NULL,
status VARCHAR(10) NOT NULL,
hours NUMERIC(9,2),
INDEX(bug_id), -- ①
INDEX(summary), -- ②
INDEX(hours), -- ③
INDEX(bug_id, date_reported, status) -- ④
);
①bug_id
ほとんどのデータベースは、主キーのインデックスを自動的に作成するため、明示的にインデックスを定義するのは冗長です。
②summary
VARCHAR(80)などの長い文字列を格納するデータ型へのインデックスは、よりコンパクトなデータ型のインデックスと比べてサイズが大きくなります。
③hours
同様に、この列の特定の値を検索することは、ほとんどないと考えられます。
④bug_id, date_reported, status
これはいわゆる複合インデックスですが、多くの場合は冗長であったり、使用頻度が極めて低くなりがちです。また、複合インデックスでは列の順序が重要で、検索条件、結合条件、ソート順において、列を定義した順（左から右）に使わなければなりません。
解決策：「MENTOR」の原則に基づいて効果的なインデックス管理を行う
MENTORとは、最適なインデックス作成のためのチェックリストです。
以下の頭文字をとったものです。
Measure （測定）
Explain （解析）
Nominate （指名）
Test （テスト）
Optimize （最適化）
Rebuild （再構築）
Measure(測定)
情報がなければ、情報に基づく判断はできません。ほとんどのデータベースでは、SQLのクエリ実行時間を記録する方法があります。これによって、最大のコストがかかっている操作を識別します。
（例）
- Microsoft SQL Server：SQLのトレース機能と、トレース結果のレポートと分析のためのツールがあります
- MySQLとPostgreSQL：指定された閾値より実行時間が長くかかったクエリを記録できます。また、MySQLでは、この機能はスロークエリログと呼ばれます。スロークエリログの設定パラメータ
long_query_timeのデフォルト値は10秒です
また、実行に最も時間がかかるクエリであっても、実行頻度が低い場合には、アプリケーションにとって最大のコストであるとは限りません。他の単純なクエリの実行頻度が高いために、実行時間に大きく影響している場合もあります。
そのような場合には、実行頻度が高いクエリの最適化に注目すれば、労力に見合ったメリットを得やすくなるでしょう。
Explain(解析)
最もコストがかかるクエリを特定した後は、クエリの処理が遅くなっている原因を解析します。
データベースは、クエリ実行計画（Query Execution Plan: QEP）と呼ばれるクエリ最適化機能によって、クエリ実行にどのインデックスを使うか判断しています。
以下は各データベース製品におけるQEPの分析結果のレポート取得方法をまとめた表です。
| データベース | QEPレポート機能 |
|---|---|
| IBM DB2 | EXPLAIN, db2explnコマンドまたはVisual Explain |
| Microsoft AQL Server | SET SHOWPLAN_XMLまたはDisplay Execution Plan |
| MySQL | EXPLAIN |
| Oracle | EXPLAIN PLAN |
| PostgreSQL | EXPLAIN |
| SQLite | EXPLAIN |
一般的なQEPは、クエリに関連するテーブル、オプティマイザーによるインデックスの選択理由、テーブルへのアクセスの順番などを示します。
Nominate(指名)
クエリのQEPを読んで、クエリがインデックスを使わないでテーブルにアクセスしている箇所を探します。
一部のデータベースには、クエリのトレース統計値を収集し、多くの変更を提案してくれるツールがあります。
- IBM DB2 Design Advisor
- Microsoft SQL Server Database Engine Tuning Advisor
- MySQL Enterprise Query Analyzer
- Oracle SQL Tuning Advisor
Test(テスト)
インデックスの作成後、再びクエリのプロファイリング(データベース内のデータの特性や品質を詳細に調査し、分析するプロセス)を行います。
大事なのは、変更が効果をもたらしたことを確認してから作業を終了することです。
例えば、自分の仕事ぶりを上長に報告する時、
「パフォーマンスの問題を改善するためにあらゆる手段を講じましたが、その効果についてはしばらく様子を見なければなりません」というより、
「使用頻度の高いテーブルに新たなインデックスを1つ作成すべきだと判断し、重要なクエリ複数でパフォーマンスを38%改善しました」と報告する方が良いはずです。
Optimize(最適化)
インデックスは使用頻度の高いデータ構造であるため、キャッシュメモリに格納されやすくなります。メモリ上のインデックスにアクセスすることで、ディスクI/Oを伴う読み込みよりもはるかにパフォーマンスを改善できます。
使用頻度の高いデータやインデックスのキャッシュへの格納をデータベースに依存するのではなく、インデックスをあらかじめキャッシュメモリにロードしておくことでメリットが得られる場合もあります。例えばMySQLでは、LOAD INDEX INTO CACHEステートメントを使用します。
Rebuild(再構築)
インデックスはバランスが取れている時に最も効果的です。長期にわたって行の更新や削除を行うことで、インデックスは次第に不均衡になっていきます。
では、インデックスを再構築する頻度はどれぐらいが適切なのでしょうか？
それは、不均衡が生じるかもしれないテーブルに対する変更の頻度によって異なります。
また、テーブルのサイズや、インデックスの最適化によってそのテーブルが得られるメリットの頻度によっても異なります。
そして最も重要なルールは、推測のみに基づいて、闇雲にインデックスをつけてはならないということです。
まとめ
- インデックスを理解しないまま使用すると、以下のいずれかの問題が発生する
- インデックスを全く定義しないか、少ししかインデックスを定義しなくなってしまう
- インデックスを多く定義しすぎる
- インデックスを活用しないクエリを実行してしまう
- 「MENTOR」の原則に基づいて効果的なインデックス管理を行うことが大切
所感
今回は時間の都合で抜粋してまとめました。ですが正直言って、他の章の内容も良かったので、テーマを一つに絞って書くのは難しかったです。
（個人的に、11章の「ファントムファイル（幻のファイル）」はもう一度読み返したい）
ただそれよりも重要なのは、「実務や実践の場で活かす」ことだと思っています。今後DB設計や実装をしていく中で、アンチパターンについて思い出しながら理解を深めていければと思います。
参考記事
SQL トレース (SQL Trace)
(MySQL 8.0 リファレンスマニュアル) 5.4.5 スロークエリーログ
データプロファイリングとは？データマイニングとの違いやメリット、手法を解説