はじめに
「なんとなく速くなるらしいから、とりあえず貼っておけ」...
データベースのインデックスに対する認識、もしかしてそこで止まっていませんか？
↑ちなみに私はこの状態でした笑
上記の認識が間違っているわけではありませんが、ただ設定するだけでは意味がなく、使い方を間違えるとSQLの実行速度が改善しないどころか、データベース全体に無駄な負荷をかけてしまう「足かせ」になりかねません。
この記事では、私がインデックスについて学ぶ中で「なるほど！」と納得できた、 「正しい使い方」 と、知らず知らずのうちに作っているかもしれない 「効果の薄いインデックス」 についてまとめてみました。
※勉強の備忘をかねているため、間違っている箇所や修正した方がいい箇所等ございましたら、教えていただけると幸いです。
そもそも「インデックス」とはなに？
MySQL公式ドキュメントより引用:
インデックスは特定のカラム値のある行をすばやく見つけるために使用されます。 インデックスがないと、MySQL は関連する行を見つけるために、先頭行から始めてテーブル全体を読み取る必要があります。
テーブルが大きいほど、このコストが大きくなります。 テーブルに問題のカラムのインデックスが含まれている場合、MySQL はすべてのデータを調べる必要なく、データファイルの途中のシークする位置をすばやく特定できます。
これはすべての行を順次読み取るよりはるかに高速です。
公式ドキュメントの説明では少しイメージがつきにくいと思いますが、
インデックスとは名前の通り書籍の 索引 のようなものです。
書籍で特定のページを開きたい場合に、１ページ目から順番にめくって調べるより、目次を確認して開きたいページを見つけるほうが 圧倒的に効率が良い ですよね！
データベースのインデックスも👆の例と同様で、特定のレコードを絞り込む際（主にWHERE句など）1に、インデックスを使用することで処理の高速化ができる！
また、単にデータを「絞り込む」だけでなく、
-
JOINする際に、結合対象の行を効率的に見つける。 -
ORDER BYで指定した順序で、ソート処理なしに結果を取得する。 -
GROUP BYで指定した項目で、効率的にグループ化処理を行う。 -
MIN()やMAX()で、最小値や最大値を素早く取得する。
といった様々な場面で、インデックスはクエリの実行速度向上に貢献します。
インデックスの種類
MYSQLでインデックスを設定した際は、デフォルトでB-treeインデックスが設定されます。
インデックスにも、様々な種類がありますが、B-treeインデックスは汎用性が高く、多くの場合で有効な選択肢となります。
B-treeインデックスの詳細な仕組み自体は、こちらの記事が詳しく解説されており、参考になりました。仕組みの詳細が気になる方は、ぜひご参照ください。
B-treeインデックス入門
※以降の説明は、主にこのB-treeインデックスに関わる説明です。
そもそもどのカラムにインデックスを設定すればいいの?
インデックス自体の説明でも若干書かれている内容ではありますが、
検索条件 (WHERE句)、結合条件 (JOIN句)、並べ替え (ORDER BY句)、グループ化 (GROUP BY句) など、クエリの性能に影響を与えるカラムにインデックスを設定することを検討しましょう！
インデックスを設定するカラムを選ぶ際は、 「そのカラムがクエリの性能にとって重要な操作（頻繁な検索、大規模な結合、大きな結果セットの並べ替えなど）で使われているか」 を基準に考えると良いと思います！
効果の薄いインデックスとその使い方
インデックスショットガン
これは有名なアンチパターンではありますが、
「インデックスを貼ったら処理が早くなる？なら、全部のカラムにインデックスを設定してしまえ！」
という考えで全てのカラムにインデックスを設定しまうことです。(👇の画像のような状態のテーブル)
そもそも、インデックスはINSERTやUPDATEなどレコードの追加やカラムの変更が行われると、インデックス自体の更新を行います。データが変更されたなら索引も変更しないと意味がないですからね。
つまり、意味のないカラムにまでインデックスを設定しているとインデックス自体の更新が原因で、かえって書き込み処理のパフォーマンスを低下させる可能性があります。！
インデックスを貼って処理の高速化を行おうとしているのに、処理が遅くなってしまっては本末転倒ですよね。
インデックスショットガンによるパフォーマンス低下については、こちらの記事が詳しく検証されており、大変参考になりました！
インデックスショットガンで書き込み性能はどれくらい劣化するのか
WHERE句でのカラムに対する関数適用
インデックスを設定したカラムに対して、WHERE句で関数を使用した条件を指定すると、インデックスが効果的に利用されない場合があります。
これは、関数によってカラムの値が変換されてしまうと、元のインデックスを構造を使った効率的な検索(INDEX SEEK)ができず、テーブル全体を検索する(FULL SCAN)を実行してしまう可能性があります。
--今日一日の購入履歴を抽出する想定。
/*インデックスを設定したカラムを関数で変換した場合の例
この場合、purchase_datetimeカラムがDATE関数で変換されるため、
インデックスが効果的に利用されない（`FULL SCAN`になる可能性が高い）*/
SELECT purchase_datetime,item_id FROM purchase_History
WHERE DATE(purchase_datetime) = CURDATE(); -- 日付部分だけで比較
/*インデックスを設定したカラムを関数で変換しない場合の例
この場合、purchase_datetimeカラムに直接条件が適用されるため、
インデックスが効果的に利用される（INDEX SEEKになる可能性が高い）*/
SELECT purchase_datetime, item_id
FROM purchase_History
WHERE purchase_datetime >= CURDATE() -- 今日の0時0分0秒以上
AND purchase_datetime < CURDATE() + INTERVAL 1 DAY; -- 明日の0時0分0秒未満
文字列の部分一致検索(LIKE)
LIKEを使用した文字列の部分一致検索は、検索のパターンによってはインデックスの利用可否が変わります。
前方一致検索 (LIKE 'str%')
インデックスは 通常、効果的に使用されます。
MySQLのB-treeインデックスは、データの先頭からソートされています。
前方一致検索では、検索文字列の先頭部分が固定されているため、インデックスを辿って効率的に該当する行を見つけることができます。
中間一致検索 (LIKE '%str%')
インデックスは 通常、利用されません。
検索文字列が、カラム内の文字列のどの位置に出現するかが不定なため、インデックスを使って効率的に絞り込むことが難しいためです。
多くの場合、テーブル全体をスキャンする（FULL SCAN）必要が生じます。
後方一致検索 (LIKE '%str')
インデックスは 通常、利用されません。
中間一致検索と同様に、検索文字列がカラム内の文字列の末尾に一致するかどうかを判断するために、インデックスを効率的に使用することが難しいです。
多くの場合、テーブル全体をスキャンする（FULL SCAN）必要が生じます。
/*前方一致検索の例
インデックスが効果的に利用される（INDEX SEEKになる可能性が高い）*/
SELECT user_id,user_name FROM users
WHERE user_name LIKE '田中%' ; --user_nameが「田中」から始まるレコードのみを抽出
/*中間一致検索の例
インデックスが効果的に利用されない（`FULL SCAN`になる可能性が高い）*/
SELECT user_id,user_name FROM users
WHERE user_name LIKE '%中%' ; --user_nameに「中」が含むレコードのみを抽出
/*後方一致検索の例
インデックスが効果的に利用されない（`FULL SCAN`になる可能性が高い）*/
SELECT user_id,user_name FROM users
WHERE user_name LIKE '%太郎' ; --user_nameに「太郎」で終わるレコードのみを抽出
終わりに
今回紹介した内容はインデックスの世界のほんの一部ですが、
インデックスを設定すべきカラムの選び方やクエリの書き方によって、インデックスが有効に活用されるか変わることを、私自身改めて学ぶことができました！
最後まで読んでいただき、ありがとうございます！
参考
・MYSQL公式ドキュメント
インデックスについて
・書籍
達人に学ぶDB設計徹底指南書
SQLアンチパターン
・記事
B-treeインデックス入門
インデックスショットガンで書き込み性能はどれくらい劣化するのか
-
インデックスを使用するかどうかはデータベースの
オプティマイザが決定することであり、インデックスが設定されていれば必ず使用するとは限らない。 ↩