はじめに
検索条件に使うカラムへ、とりあえずインデックスを付ける。
それで速くなることもありますが、インデックス設計としては不十分です。
同じテーブルでも、絞り込み方や並び順が違えば、必要なインデックスは変わります。
インデックスは、テーブルを速くするためのものではありません。
特定のクエリに、効率のよいアクセス経路を与えるためのものです。
TL;DR
インデックス設計は、カラム一覧ではなくクエリから始めます。
見るべきなのはWHEREだけではありません。
JOIN、ORDER BY、取得件数、実行頻度まで含めて、そのクエリがデータへどうアクセスするかを見ます。
1. インデックスはテーブルではなくクエリに対応する
例えば、記事一覧を取得する次のクエリを考えます。
SELECT
id,
title,
created_at
FROM
articles
WHERE
company_id = ?
AND status = 'published'
ORDER BY
created_at DESC
LIMIT 20;
このクエリでは、company_id、status、created_atを使っています。
それぞれへ単独インデックスを付ける方法もあります。
CREATE INDEX idx_articles_company_id
ON articles (company_id);
CREATE INDEX idx_articles_status
ON articles (status);
CREATE INDEX idx_articles_created_at
ON articles (created_at);
ただし、このクエリが必要としているのは、3つのカラムを別々に検索することではありません。
この一連の処理に合うアクセス経路を作る必要があります。
候補の一つが、次の複合インデックスです。
CREATE INDEX idx_articles_company_status_created
ON articles (
company_id,
status,
created_at DESC
);
このインデックスは、articlesテーブル全体を漠然と速くするためのものではありません。
先ほどの一覧取得クエリに合わせて設計しています。
同じカラムでも、クエリが違えば必要な形は変わる
次のクエリもcreated_atを使います。
SELECT
COUNT(*)
FROM
articles
WHERE
created_at >= ?;
しかし、先ほどの一覧取得とはアクセスの仕方が違います。
一覧取得では、絞り込みと並び替えを組み合わせて少数件を返します。
期間集計では、created_atを起点に広い範囲を読みます。
使っているカラムが同じでも、クエリが求めるアクセス経路は異なります。
したがって、確認すべきなのは、
このカラムにインデックスがあるか
ではなく、
このクエリが必要とする順序でデータへたどり着けるか
です。
複合インデックスの順序もクエリから決める
次の2つは、同じカラムを含んでいても別のインデックスです。
(company_id, status, created_at)
(created_at, status, company_id)
先ほどの一覧クエリでは、company_idとstatusが等価条件で、created_atが並び替えに使われています。
複合インデックスの順序は、カラムの重要度で決めるものではありません。
クエリがどの条件で範囲を絞り、どの順序でデータを必要とするかから考えます。
2. インデックスを増やすほど速くなるわけではない
インデックスは検索用の追加構造です。
読み取りを速くできる一方で、データを書き込むたびにインデックスも更新されます。
そのため、対象のSELECTが速くなっただけでは、インデックスを残す理由として十分ではありません。
そのクエリがどれだけ実行されるのか。
書き込みへどれだけ影響するのか。
ディスクやメモリの消費に見合うのか。
読み取りの改善と維持コストを合わせて判断します。
「念のため」のインデックスは役割が重なりやすい
遅いクエリが出るたびにインデックスを追加すると、似た構成のものが増えていきます。
INDEX (company_id)
INDEX (company_id, status)
INDEX (company_id, status, created_at)
すべて別のインデックスですが、先頭部分の役割は重なっています。
(company_id)
(company_id, status)
(company_id, status, created_at)
^^^^^^^^^^
先頭部分が重なる
長い複合インデックスが、短いインデックスの役割を一部代替できる場合もあります。
ただし、別のクエリでは短いインデックスの方が適しているかもしれません。
定義だけを見て機械的に消すのではなく、それぞれがどのクエリに使われているかを確認します。
重要なのはインデックスの本数ではありません。
このインデックスは、どのクエリのためにあるのか。
これを説明できる状態にしておくことです。
3. インデックスは設計しただけでは使われない
インデックスを作成しても、データベースが必ず使うとは限りません。
オプティマイザは、データ量や分布、取得件数などから、実行コストが小さいと考えた経路を選びます。
全件走査の方が安いと判断されれば、追加したインデックスが選ばれないこともあります。
つまり、インデックスを作ることと、そのインデックスが有効に使われることは別です。
EXPLAINで期待した経路になっているか確認する
MySQLでは、EXPLAINを使って実行計画を確認できます。
EXPLAIN
SELECT
id,
title,
created_at
FROM
articles
WHERE
company_id = 10
AND status = 'published'
ORDER BY
created_at DESC
LIMIT 20;
見るべきなのは、インデックス名が表示されたかどうかだけではありません。
インデックスが使われていても、読み取る行数が十分に減っていなければ、期待した効果は得られません。
また、絞り込みは速くなっていても、ORDER BYのために別のソートが発生している可能性もあります。
実行計画は、設計したアクセス経路が実際に採用されたかを確認するために使います。
実行計画の改善と、性能の改善は分けて見る
EXPLAINは、データベースがどのように処理する予定かを示します。
しかし、最終的に知りたいのは、実際の負荷が減ったかです。
数ミリ秒の改善でも、1秒間に何百回も実行されるクエリなら大きな効果になります。
反対に、ほとんど実行されないクエリのために、書き込みコストの高いインデックスを追加する価値は低いかもしれません。
インデックスの効果は、1回の速さだけでなく、システム全体へ与える影響で判断します。
4. MENTORでインデックスを管理する
インデックス設計は、DDLを一度追加して終わる作業ではありません。
改善対象を測定し、実行計画を読み、候補を試し、残すか判断する流れです。
MENTORで重要なのは、インデックスを追加する前に改善対象を特定し、追加後も効果を検証することです。
まず、改善対象のクエリを決める
インデックス候補を考える前に、どのクエリを改善するのかを決めます。
一度だけ極端に遅いクエリより、少し遅くて大量に実行されるクエリの方が、システムへの影響が大きいこともあります。
実行時間 × 実行回数 = システムへの影響
スロークエリログや監視データから、処理時間、実行回数、読み取り行数を確認します。
改善対象が決まらなければ、インデックスの効果も判断できません。
クエリのアクセスパターンを分解する
対象のクエリが決まったら、データへどのようにアクセスしているかを見ます。
SELECT
id,
title,
created_at
FROM
articles
WHERE
company_id = ?
AND status = 'published'
ORDER BY
created_at DESC
LIMIT 20;
このうち、どこまでをインデックスに任せたいのかを決めます。
絞り込みだけを速くするのか。
並び替えも避けたいのか。
必要な列までインデックスへ含め、テーブル本体へのアクセスを減らしたいのか。
目的が曖昧なままでは、必要以上に大きなインデックスを作りやすくなります。
候補を作り、変更前後を比較する
候補となるインデックスを追加したら、変更前後で実行計画と性能を比較します。
期待したインデックスが使われないなら、カラム順、検索条件、データ分布を見直します。
インデックスが使われても性能が変わらないなら、ボトルネックが別の場所にある可能性があります。
判断基準は、DDLを追加できたかではありません。
改善仮説を、実行計画と実測で確認できたかです。
5. 使われないインデックスを残し続けない
インデックスは追加されやすい一方で、削除されにくいものです。
しかし、利用されていないインデックスも、書き込み時の更新と保存領域を必要とします。
役割を説明できないインデックスは、まず削除候補として扱います。
そのうえで、実際の利用状況と、ほかのインデックスとの重複を確認します。
削除も仮説として検証する
不要に見えるインデックスでも、いきなり本番環境から削除するのは危険です。
対象クエリや実行計画への影響を確認します。
追加と削除は、どちらもインデックス設計です。
クエリに対する仮説を立て、測定して判断します。
おわりに
インデックスは、検索に使いそうなカラムへ付けるものではありません。
特定のクエリが、必要なデータへ効率よくたどり着くために設計するものです。
クエリが変われば、必要なインデックスも変わります。
以前は必要だったインデックスが、使われなくなることもあります。
このインデックスは、どのクエリを、どのように速くするために存在するのか。
この問いに答えられる状態を保つことが、クエリ駆動のインデックス管理です。