【DB初心者向け】SQLアンチパターンから学ぶ、インデックス最適化の基礎

RDMS歴1年ですが、「インデックスは頻繁にselectするカラムに貼っておいて、取得の高速化を図るものもの。ただし貼りすぎても重くなる。文字列に貼ってもしょうがない。」程度の理解でした。
名著「SQLアンチパターン」に"闇雲インデックス"という章があり、勉強になったので、情報を付加してまとめ直します。

インデックスとは、インデックスアンチパターン、解決策、カバーリングインデックスの順で進めます。

インデックスはSQLの規格に定められてない

意外だったのですが、SQLそのものはインデックスについて特に何も定めていないようです。
つまり各DBベンダー(MySQL、 PostgeSQL、Oracle etc..)がインデックス機能を独自に実装しています。
なので、インデックスの最適化を図る際は、各ベンダーの仕様を確認することが大切です。
ここでは量がとんでもなくなってしまうので、細かく各ベンダーのケースについて言及することは避けさせていただきます。
私自身がMySQLを使う機会が多いので、例としてはMySQLをあげさせていただきます。

最適化はコンテキストが大事

最適化は各アプリケーション、各テーブル、各カラムの使用状況を鑑みて考えなければいけません。
ここで書かれていることをただ闇雲に適用するのではなく、状況を踏まえて活かしていただけると幸いです。

インデックス(index)とは？

日本語に訳すと「索引」です。
簡単にいうと、データベースの検索性能を良くする機能です。

前述の通り、各ベンダーによってインデックスの実現アルゴリズムは異なりますが、
what is indexを知るために、今や消え去った電話帳ではなく、本の末尾についている索引をイメージして下さい。

索引はあいうえお順やアルファベット順に本で使われている用語が並んでいます。
本来これらの用語はあいうえお順で登場している訳ではもちろんありませんが、読者が用語を見つけやすくするためにあいうえお順で再並び替えしています。
これがインデックスです。
インデックスとは、欲している特定のカラムの情報を見つけやすくするためにあらかじめ再並び替えをしておき、再並び替えをした表から検索対象を探し出す機能です。

インデックスは、インデックスを作っておきたいカラムを予め指定します。
複数のカラムをグループ化して指定することも可能です。
これは複合インデックスと呼ばれます。少し複雑です。

対象のテーブルの保存先とは別に、もう一つインデックス対象となっているデータだけを抜き出し、元のデータの保存先を指すポインタとその対象データをセットにして、別のテーブルに保存するといったことをします。
本の索引も、あいうえお順で用語が並んでおり、その用語(=検索対象カラム)の横にはページ数(=ポインタ)が書いてあると思います。
まさにそんな感じです。

インデックステーブルの作成/更新はINSERT/UPDATE/DELETEの時に発生します。
本の索引も、改訂版が出版されるにあたり、"か"に該当する用語がなくなったらその分を詰めますよね。
それと同じです。

なので挙動の順番としては、

1. インデックステーブルで、探している用語を探しにいく
2. 見つかる
3. 用語のポインタを取得する
4. ポインタで本元のテーブルから必要なデータを取ってくる
   といった感じです。
   カバーリングインデックスが効いていると3,4を省略できます。
   カバーリングインデックスについては、最後に説明させてください。

ちなみに、この対象のカラムを再並び替えをしておくことを「インデックスを貼る」とよく言います。

アンチパターンその① 『インデックスを全く使用しない』

長くなりましてすみません。本題のアンチパターンを3つみていきます。

「インデックスを使用すると、オーバーヘッド(=余分な処理付加)がかかるので使用したくない」というのは大抵誤りです。
確かにインデックスを貼るようにすると、INSERT/UPDATE/DELETE処理が走るたびに、再並び替え処理が走るので、その分オーバーヘッドが生じます。

しかしながら、大抵のアプリケーションでは、テーブルに対するSELECTクエリ発行の方が更新回数よりもずっと多いそうです。
つまり並び替え更新処理のオーバーヘッドを取り戻す以上のメリットを享受できる傾向にあるということです。

またインデックスはUPDATE/DELETE対象行を特定する時にも活躍してくれます。

SELECTよりも明らかにINSERT/UPDATE/DELETEが多い場合を除いて、インデックスを貼った方が良いでしょう。
　　

アンチパターンその② 『インデックスを多用しすぎる』

インデックスを貼るには前述の通り、オーバーヘッドが生じます。
つまり、インデックスの必要のないカラムに対してインデックスを貼ってしまうとそれだけ無駄になります。

どんな時に無駄になるか？実例を見てみましょう、

CREATE TABLE Bugs (
 id SERIAL PRIMARY KEY,
 date_reported DATE,
 content VARCHAR(80), 
 status CARCHAER(10),
 working_hours NUMERIX(9,2),
 INDEX (id),
 INDES (content),
 INDEX(working_hours),
 INDEX(id, date_reported, status)
);

・無駄1 primary keyにインデックスを貼っている
ほとんどのデータベースは主キーに自動でインデックスを貼ってくれるので不要です。

・無駄2 長い文字列にのカラムにインデックスを貼っている
長い文字列のカラムにインデックスを貼ってしまうと、インデックスのサイズが大きくなってしまいます。
加えて、長い文字列に対して検索をかけることはほぼないはずです。
Qiitaの本文に対して完全一致条件で検索しにいくことはまずないですよね。
LIKEを使った部分一致ではインデックスは使用されません。全件スキャンします。
したがって長い文字列のカラムに対するインデックスは不要です。

・無駄3 ほとんど検索されないカラムにインデックスを貼っている
working_hoursで絞りたいことはまずないので、不要です。

・無駄４ 適切でない複合インデックスに対してインデックスを貼っている
複合インデックスは有益ですが、多くの場合はそれほど使われない傾向にあるようです。
また複合インデックスは順序がとても大事なので、間違った指定をしているとまったく不要になります。
複合インデックスは長くなるので、他のページに説明をdelegateさせてください。

(あとで追記するかもしれません)

アンチパターンその③ 『インデックスを活用しないクエリを発行する』

インデックスを貼っているつもりで全然使えていないパターンです。
これはインデックスの貼り方を学ぶことと、実際にテストをしてちゃんとインデックスが効いているかを確かめる他ありません。

間違えの例を一つあげると、
CREATE INDEX UserSearch ON Users(last_name, first_name);

これに対し、
SELECT * FROM Users ORDER BY first_name, last_name;
をしても意味がありません。
何故ならばインデックスはlast_name -> first_nameで貼られているからです。
SELECT * FROM Users ORDER BY last_name, first_name;
なら意味があります。

解決策としてのMENTOR

以下はSQLアンチパターンに書かれているものですが、フレームワークとして的を得ているのでサマリます。

① Measure(測定)
まずは真実と現状値を知るために計測しましょう。
ありがちな罠としては、アプリが重い原因はクエリではなく、他の要因だったというものです。
htmlのキャッシュを使わないレンダリングとか。

測定する時はキャッシュをオフにすることを忘れずに。

② Explain(解析)& ③ Nominate(指名/特定)
遅いクエリを特定したら、何故遅くなっているのかを解析し、特定しましょう。
今回のテーマでいえば、インデックスを正しく使えていないのではないか？ということを解析しましょう。

各データベースはクエリ実行企画(QEP)と呼ばれるクエリ最適化機能を持っています。
例えばMySQLであればExplain句がそれに当たります。
これらを利用して、インデックスの使用可否を調べてみましょう。

Explain句を使用して自分で解析していく以外にも、各データベースがツールを提供しています。
例によってMySQLであれば、 Enterprise Query Analyzer がそれに当たります。
(私はExplain句は使ったことがありますが、ツールはありませんのでyoutubeにdelegateします。)

(後日詳細を書くかもしれません)

④ Test(テスト)
実際にリリースして改善具合を経過観察しましょう。
ではなく、再び改善後のプロファイリングし、どれほど改善したのかをリリース前に追いましょう。

⑤ Optimize(最適化)
インデックスはキャッシュメモリに格納されやすいです。
そしてデータベースのキャッシュ容量は少なく設定されがちです。
キャッシュメモリを見直してみてもいいでしょう。

また良く使われるデータはあらかじめキャッシュしておくのも手の一つです。

⑥ Rebuild(再構築)
定期的にメンテナンスをしましょう。

おまけ　カバーリングインデックスとは?

クエリに必要な列が全てインデックスに含まれている場合、本元のテーブルからデータを読み込む必要はありません。
そのため本体のインデックスには不要なカラムをインデックス対象にすることをカバーリングインデックスといいます。

例えばUserテーブルからfirst_nameとlast_nameで検索し、hitしたユーザーの性別(gender)と生年月日(birthtday)を取得したいとします。
普通に考えればfirst_nameとlast_nameにインデックスを貼ればいいのですが、ついでにgenderとbirthdateもインデックスの対象に入れてしまいます。
するとクエリはインデックステーブルだけを検索対象とするので、速度が向上するというわけです。
もちろん使いどことは考える必要があります。

おわり

何か間違いがあったらご指摘いただけると大変ありがたいです。