[WIP]SQLアンチパターン-12章 インデックスショットガン-まとめ

インデックスはただ貼れば良いということではない

結論としてはこうなります。
自分はインデックスについてきちんと分かっていなかったのですが、
調べてみるとインデックスって色々な種類があります。（インデックスで一般的によく使われているのが、B木インデックスというものです。）
本も読んだりして、万能なインデックスなんて存在しないのだな、という認識に至りました。
DBの設計、取り出すデータの種類、アプリケーションレベルでのデータ扱いのロジック、実際に使用するクエリ構文によって有効なインデックスって変わるのだなと。

今回はそのインデックスについて、実際インデックスってなんなんですか？
それを少しでもわかりやすく書ければと思います。

（まだ十分に把握していない知識が多いので、今は必要な知識を全ては書けていません。それだけは分かっています。笑）

インデックスってそもそもなに？

このページが簡単に理解できるのかなと思います。
インデックスについて

ここも多分知っておくべき情報がいくつかあると思います。（筆者はまだ全部読みきっていませんのであとで読みます）

DBのインデックスについて調べてまとめた

ある値を検索しようとなった時に、
その値がテーブルの何行目に格納されているのか、
それを示しているのがインデックスです。
そしてそのインデックスもまたテーブル構造で表されるので
DBにテーブルが1つ増えたような感じになるんですかね。

インデックスを貼りすぎるとどうなるのか

(自分、インデックスを貼るという言い方がまだしっくりきていないです)
インデックスって要は1つのデータファイルです。
メモリ上に保存されることもあります。
（例えば使うかわからないけどとりあえず作っておこう！みたいなテンションで）インデックスファイルを作成しすぎるとどうなるでしょうか。
サーバー内の容量が圧迫されてしまうだけになります。SQL文を回す上でも余計なオーバーヘッドが出てきてしまうこともあります。

クエリとインデックスの正しい関係について

例えば、以下のようなテーブルがあったとしましょう

服(Clothes)テーブル

インデックスを作成する際は下記のようなコードになります。

CREATE INDEX MakerName ON Clothes(maker, name);

このインデックスはmakerとnameが両方ある複合インデックスになります。

下記のように検索方法したらどうなるでしょうか
- 名前がタートルネックの服を探したい

この場合、クエリはこうなります

SELECT * FROM Clothes WHERE 'name' = 'タートルネック';

このクエリを回す際に、先ほど作成した複合インデックスは使われません。
まずそもそも複合インデックスとはつまりどういうことか、認識を改める必要が自分にはありました。
複合インデックスはあくまで両方のデータを扱って検索した場合に使われます。
今回は検索するための条件としてnameしか使っていないので、インデックスは利用されません。

また、

SELECT * FROM Clothes WHERE 'name' = 'タートルネック' AND maker = 'アディダス';

としても、これも実はインデックスは利用されません。
なぜなら作成した複合インデックスは、最初にmaker、次にnameという順番だからです。
複合インデックスでは定義の順番が実は重要ということになります。

インデックスが有効なケースを知ろう

まず、単純にインデックスを貼っただけではパフォーマンスが上がるということには繋がりません。
例えば、データの数が10個とか、そのくらいの数だとインデックスを作成したところで劇的にパフォーマンスが上がるということはありません。どのくらいの数からインデックスを作成することでパフォーマンスが上がるのかは私にもわかりませんが・・・。

つまりインデックスが有効かどうか、必要なのかどうか、知る手段を知る必要があります。
その中で使われるのがEXPLAIN構文です。

EXPLAIN構文について知ろう

EXPLAIN構文は、簡単にいうと、あるクエリを叩いた時に、
- そのクエリはどのような検索方法を使っているか(単純にWHERE構文なのか、インデックスを使っているのか）
- インデックスが有効であろうカラムはあるのか（あったらどれなのか）
- どのようなインデックスの種類を使われているのか

などがわかるようになります。

このEXPLAIN構文を使って、最適なインデックスなどを見つけて行くのが良さそうですね。
下記のような感じになります。

EXPLAIN SELECT * FROM Clothes WHERE 'name' = 'タートルネック';

MySQLのEXPLAIN
ここのページではパラメータが表す意味が記載されているので参考にどうぞ。

インデックスが意味をなさないってどういう時？

• そもそも検索で条件として利用されないカラムでインデックスを作成している
• もともと登録している（つまり重複してインデックスを作成している）（主キーは自動的にインデックス作成されることが多いらしいので気をつけましょう）
• 長い文字列に対するインデックス（それに対応するインデックスの種類もあるみたいです）
• 複合インデックスと検索条件で利用するカラムの順番が違う
• データの数が少ない（インデックスで絞っても意味のないくらいのパフォーマンスの改善しか見込めない）

これって、例えばnameにタートルネックであるデータの数が10件しかない時と10000件ある時とで、だいぶ変わってきます。条件の組み合わせ条件によっては、makerでソートをしてからnameを調べて検索する、といったことも考えられます。
この際、検索する値としてnameが10件しかなく、かつmakerが10000件あったとしたら、まずはnameから複合インデックスを定義して、クエリもそれに合わせて作成するのが良いです。
欲しいデータとして該当する行が1行しかない時に

1. makerで探して10000件 => 該当の行を見つける
2. nameで探して10件 => 該当の行を見つける

では明らかに2の方法で探したほうがコストが少なくて楽です。
これを選択性が良いと表します。（間違っていたらすいません。）
インデックスを作成して、クエリを回す際はこの選択性を考える必要があるんですね・・・。

執筆途中