インデックスはEXPLAINで確かめる — 複合インデックスの順序と効かない書き方

この記事で扱う問題#

一覧や検索のクエリを足す PR には、たいていインデックスを足すマイグレーションが付いてきます。「status で絞り込むから、status にインデックス」「日付で検索するから、confirmed_at にインデックス」という具合です。レビューでも、マイグレーションにインデックスがあれば、それで性能の確認が済んだことにされがちです。

ところが、例えば次のことが起こり得ます。

• インデックスを張ったのに、使われていない：whereDate('confirmed_at', $date) は date(confirmed_at) = ? という SQL になります。列を関数に通しているので、confirmed_at のインデックスでは探せません
• 複合インデックスの列の順序が、クエリと合っていない：(created_at, user_id) のインデックスは、「利用者ごとに新しい順」の一覧では、ほかの利用者の注文まで新しい順にたどることになります
• 開発環境では速い：数百行のテーブルなら、どんなクエリでも一瞬で返ります。行数が少ないと、DB はインデックスを使わずに全件を読むほうを選ぶこともあり、開発環境で見た実行計画は本番の参考になりません
• インデックスが増えるだけ増える：使われていないインデックスも、INSERT や UPDATE のたびに更新されます。どれが使われているか分からないので、消すこともできません

インデックスは、張ったかどうかではなく、そのクエリで使われているかで確かめるものです。確かめる手段が EXPLAIN です。

以下、DB は MySQL 8.0 の InnoDB を前提にし、PostgreSQL で違うところは都度書きます。

判断#

確かめる単位は、クエリクラスが出す SQL#

EXPLAIN で確かめるのは、テーブルではなく、1つ1つの SQL です。読み取りを用途ごとのクエリクラスに分けていれば、確かめる SQL はクラスごとに1通りか数通りに決まります。

Eloquent や Query Builder が実際にどんな SQL を出すかは、書いたコードからは分かりにくいので、実行して取り出します。例えば tinker で、クエリクラスを呼んだときの SQL を集め、そのまま EXPLAIN に渡します（tinker の中なので、Facade やヘルパーを使っています）。

use Illuminate\Support\Facades\DB;

$db = DB::connection();
$db->enableQueryLog();

app(OrderListQuery::class)->fetch(new OrderListFilter(12345, OrderStatus::Pending), null, 20);

foreach ($db->getQueryLog() as $logged) {
    dump($logged['query']);
    dump($db->select('EXPLAIN ' . $logged['query'], $logged['bindings']));
}

確かめる環境は、行数とデータの偏りが本番に近いものにします。個人情報を消して本番のデータを写したステージングがなければ、行数と、利用者ごとの注文数の偏りくらいは本番に合わせたデータを作ります。DB が実行計画を選ぶときに使う統計情報は、大量に投入した直後には古いことがあるので、MySQL なら ANALYZE TABLE orders、PostgreSQL なら ANALYZE orders を実行してから見ます。

EXPLAIN で見る列#

MySQL の EXPLAIN の出力のうち、私が最初に見るのは次の列です。

列	見ること
key	使われたインデックス。期待したインデックスになっているか
type	読み方。ALL（全件を読む）や index（インデックスを端から端まで読む）が、件数の増えるテーブルに出ていないか。WHERE のない ORDER BY ... LIMIT は、index でも先頭の数行で止まるので問題ありません
rows	読むと見積もった行数。返す行数に比べて桁が大きすぎないか
Extra	Using filesort（読んだ後で並べ替える）、Using temporary（一時テーブルを作る）が、件数の増える読み取りに出ていないか

rows は統計情報からの見積もりで、LIMIT で途中で読むのをやめる分を反映しないことがあります。実際に何行読んだかを知りたいときは、EXPLAIN ANALYZE（MySQL 8.0.18 以降）を使います。これはクエリを実際に実行して、各段階で読んだ行数と時間を出します。実行するので、本番のプライマリではなく、レプリカかステージングで使います。PostgreSQL の EXPLAIN ANALYZE は、UPDATE や DELETE に付けるとその更新も実際に行うので、同じ条件の SELECT にして確かめます。

複合インデックスの列の順序：= の列を先に、その後に並び順・範囲#

ページネーションの記事の OrderListQuery は、次の形の SQL を出します（カーソルの条件は省略しています）。

SELECT id, status, created_at FROM orders
WHERE user_id = ? AND status = ?
ORDER BY created_at DESC, id DESC
LIMIT 21

記事のコメントには、インデックスは (user_id, created_at, id) で、status で絞るなら (user_id, status, created_at, id) も検討する、と書きました。どちらを張るかは、列の順序の原則から考えます。

• 先頭には、= で比べる列を置く。ここでは user_id と status
• その後に、並び順の列を ORDER BY と同じ順序で置く。ここでは created_at、id。カーソルの条件（created_at の範囲）も、並び順と同じ列なので、この位置で範囲を狭めるのに使われます
• 範囲の条件（<・>・BETWEEN）の列も、= の列の後に置く。範囲の条件で使った列より後ろの列は、探す範囲を狭めるのには使われず、その列の順序で並んでいるとも限らなくなります

範囲の列と並び順の列が違うときは、どちらかを諦めることになります。例えば WHERE user_id = ? AND total_amount >= ? ORDER BY created_at なら、(user_id, created_at, total_amount) は並べ替えが要らない代わりに total_amount で範囲を狭められず、(user_id, total_amount, created_at) は範囲で絞れる代わりに Using filesort が残ります。どちらが速いかは、範囲の条件でどれだけ行が減るかで決まるので、ステージングで両方を張って EXPLAIN ANALYZE で比べ、本番に残すのは片方にします。

(user_id, created_at, id) しかない場合、DB はこのインデックスで利用者の注文を新しい順にたどり、1行ずつ表の行を読んで status を確かめ、21行そろったところで止めます。並べ替えは要りません。ただし、絞り込む状態が珍しいもの（例えば、未確定の注文がほとんどない利用者の pending）だと、21行見つかるまで、その利用者の注文をほとんど全部読むことになります。EXPLAIN では key も type も問題がないように見え、EXPLAIN ANALYZE で読んだ行数を見て初めて分かる、という種類の遅さです。

(user_id, status, created_at, id) を張れば、利用者と状態で範囲が決まり、その中を新しい順に21行読むだけで済みます。一方で、status で絞らない一覧では、このインデックスは user_id で絞るところまでしか使えず、並べ替えには使えません（Extra に Using filesort が出ます）。user_id の次が status なので、status を決めないと created_at の順に並んでいないからです。状態で絞る一覧と絞らない一覧の両方があるなら、インデックスは2本とも要ります。

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    public function up(): void
    {
        Schema::table('orders', function (Blueprint $table): void {
            // OrderListQuery で status を指定したときのため。status を指定しない一覧は (user_id, created_at, id) を使う
            $table->index(['user_id', 'status', 'created_at', 'id'], 'orders_user_status_created_id_index');
        });
    }

    public function down(): void
    {
        Schema::table('orders', function (Blueprint $table): void {
            $table->dropIndex('orders_user_status_created_id_index');
        });
    }
};

インデックスのコメントには、どのクエリのために張ったかを書きます。後で消してよいかを判断するとき、手がかりがそれしかないことが多いからです。

InnoDB のセカンダリインデックスには、末尾に主キーが暗黙に含まれるので、id を書かなくても並び順に使われることがあります。私は、並び順の一部として意図して使っていることを示すために、明示しています。PostgreSQL にはこの仕組みがないので、明示しないと id の分の並べ替えが残ります。

インデックスが効かない書き方#

EXPLAIN を見て期待したインデックスが使われていないとき、原因はインデックスの側ではなく、クエリの書き方にあることがよくあります。例えば、管理画面のダッシュボードに出す「指定した日に確定した注文の件数」を考えます。(status, confirmed_at) のインデックスがあるとします。

// date(confirmed_at) = ? になり、confirmed_at の範囲で探せない。インデックスの中の確定済みの行をすべて読んで日付を確かめる
$this->db->table('orders')
    ->where('status', OrderStatus::Confirmed->value)
    ->whereDate('confirmed_at', $day->format('Y-m-d'))
    ->count();

列を関数に通さず、範囲の条件に書き直します。

use DateTimeImmutable;
use DateTimeZone;
use Illuminate\Database\ConnectionInterface;

final class ConfirmedOrderCountQuery
{
    public function __construct(private ConnectionInterface $db)
    {
    }

    /** 日本時間のその日のうちに確定した注文の件数。confirmed_at は日本時間の DATETIME で保存している前提 */
    public function countOn(DateTimeImmutable $day): int
    {
        $start = $day->setTimezone(new DateTimeZone('Asia/Tokyo'))->setTime(0, 0);
        $end = $start->modify('+1 day');

        // インデックスは (status, confirmed_at)。= の status の後に、範囲の confirmed_at
        return $this->db->table('orders')
            ->where('status', OrderStatus::Confirmed->value)
            ->where('confirmed_at', '>=', $start->format('Y-m-d H:i:s'))
            ->where('confirmed_at', '<', $end->format('Y-m-d H:i:s'))
            ->count();
    }
}

終わりを「翌日の0時より前」にしているのは、23:59:59 までにすると、小数秒を持つ列で 23:59:59.5 の行が漏れるからです。confirmed_at を UTC で保存しているなら、$start と $end も UTC に直してから条件に入れます。このクエリは status と confirmed_at しか使わないので、表の行を読まずにインデックスだけで数えられます（Extra に Using index と出ます）。

ほかに、私がレビューでよく見るのは次の書き方です。

• 文字列の列を数値と比べる：MySQL では、sku が文字列の列のとき WHERE sku = 12345 と書くと、列の側が数値に変換されて比べられ、sku のインデックスでは探せません。$request->integer('sku') のような値をそのまま渡すと起きます。条件に渡す値の型を、列の型に合わせます
• 先頭が % の LIKE：LIKE '%山田%' は、通常の B-tree のインデックスでは探せません。部分一致が本当に要件なら、全文検索や検索エンジンを使うかの判断になります
• 別の列どうしの OR：WHERE user_id = ? OR email = ? は、どちらか一方のインデックスだけでは探せません。両方の列にインデックスがあれば、MySQL は2つを組み合わせて使うことがあり、EXPLAIN の type に index_merge、Extra に Using union(...) と出ます。それも出ずに ALL なら、UNION に分けるかを考えます

MySQL 8.0.13 以降の関数インデックスや PostgreSQL の式インデックスで、式自体にインデックスを張る方法もあります。ただ、インデックスの式とクエリの式がそろっていないと使われず、後から読む人が気づきにくいので、範囲の条件に書き直せるなら書き直します。

レビューで見る点#

レビューの観点のうち「性能」に当たる部分で、インデックスについては次のことを確かめます。

• 件数が増えるテーブルへの新しいクエリには、EXPLAIN の結果を PR に付けてもらう：SQL、本番に近いデータで見た EXPLAIN、期待したインデックス。key が期待どおりか、type が ALL でないか、Using filesort が出ていないかを見ます
• インデックスを足すマイグレーションには、どのクエリのためかを書いてもらう：列の順序が、そのクエリの = の列を先に、その後に並び順・範囲の列を置いた形になっているか。既存のインデックスの先頭部分と同じだけのインデックス（(user_id, created_at, id) があるのに (user_id) がある、など）になっていないか。MySQL では foreignId()->constrained() の外部キーのためにインデックスが自動で作られるので、足した覚えがなくてもあることが多いです
• 大きなテーブルにインデックスを足すときの、ロック：MySQL 8.0 の InnoDB では、多くの場合、書き込みを止めずに足せますが、始まりと終わりのメタデータロックが長いトランザクションを待つと、後から来たクエリが詰まります。PostgreSQL の普通の CREATE INDEX は作り終えるまで書き込みを止めるので、CREATE INDEX CONCURRENTLY を DB::statement() で実行し、マイグレーションの $withinTransaction を false にします。途中で失敗すると無効な（INVALID）インデックスが残るので、消してから作り直します
• 使われていないインデックスを探す：MySQL なら sys.schema_unused_indexes、PostgreSQL なら pg_stat_user_indexes の idx_scan が 0 のものが候補です。MySQL の集計はサーバを再起動すると消え、PostgreSQL の集計もクラッシュからの復旧や pg_stat_reset() で消えるので、月末だけ走る集計のようなクエリが、まだ一度も走っていないだけのこともあります。また、どちらもサーバごとの集計なので、リードレプリカに読み取りを流しているなら、レプリカでも確かめます。プライマリで 0 でも、レプリカで使われていることがあります。候補を見つけたら、インデックスのコメントに書いたクエリがまだあるかを確かめます
• インデックスを消すときは、戻せる段階を挟む：MySQL 8.0 なら、まず ALTER TABLE orders ALTER INDEX orders_user_status_created_id_index INVISIBLE で DB から見えなくし、遅くなるクエリがないかをしばらく見てから消します。見えなくしたインデックスも書き込みのたびに更新されているので、問題があれば VISIBLE に戻すだけで元に戻ります。一度消したインデックスを作り直すのは、大きなテーブルでは時間がかかります。PostgreSQL には見えなくする仕組みがないので、消す前に idx_scan が増えていないことを、より長い期間で確かめます

なぜそう判断したか#

「検索に使う列には全部インデックス」にしない理由#

1つのテーブルを読むとき、DB が探すのに使うインデックスは、基本的に1本です（複数のインデックスを組み合わせる仕組みもありますが、並び順までは満たせません）。user_id・status・created_at に1本ずつ張っても、「利用者と状態で絞って新しい順」というクエリを1本で満たすインデックスはありません。どれか1本で探し、残りは1行ずつ確かめ、最後に並べ替えることになります。

インデックスは、列ではなくクエリの形に合わせて作るものです。列ごとに張ると、本数のわりに効くものが少なく、書き込みのたびに更新する手間だけが増えます。

列の順序を、値の種類の多さで決めない理由#

複合インデックスの順序は「値の種類が多い（絞り込める）列を先に」と説明されることがあります。私は、それより先に、クエリの中で = で比べる列か、範囲や並び順に使う列かで決めています。status は値が3〜4種類しかありませんが、= で比べる列なので、並び順の created_at より前に置かないと、status で絞った一覧を順にたどれません。値の種類の多さで迷うのは、= で比べる列どうしの順序を決めるときくらいで、その場合も、ほかのクエリと先頭の列を共有できるかのほうを重く見ます。

EXPLAIN の結果を、CI のテストにしない理由#

「このクエリはこのインデックスを使う」ことを、テストで確かめたくなることがあります。私は CI には入れていません。テストの DB の行数は少なく、DB は行数が少ないと全件を読むほうを選ぶので、本番では使われるインデックスが、テストでは使われないことがあるからです。逆に、テストで使われていても、本番のデータの偏りでは別の実行計画になることもあります。

テストは「将来の変更で何を壊してはいけないか」を書き残すものですが、実行計画はデータの量と偏りで変わるもので、コードの変更だけでは決まりません。そのため、確かめる場所を2つに分けています。クエリを足すときは PR のレビューで EXPLAIN を見る。リリースの後は、MySQL のスロークエリログや PostgreSQL の pg_stat_statements で、遅くなったクエリを見つける。データが増えて実行計画が変わるのは、リリースの後に起きることだからです。

どこから設計過剰になるか#

• 件数の増えないテーブル：都道府県や設定のマスタのように数百行で止まるテーブルは、全件を読んでも速いので、EXPLAIN を見る必要はありません。インデックスも、一意制約のためのもの以外は要りません
• すべての PR で EXPLAIN を求める：確かめるのは、件数の増えるテーブルへの新しいクエリと、既存のクエリの条件や並び順を変えた場合に限ります。全部に求めると、結果を貼るだけの作業になり、誰も読まなくなります
• すべてのクエリをインデックスだけで返せるようにする：SELECT する列まで全部インデックスに入れると、インデックスが大きくなり、列を足すたびに作り直すことになります。件数を数えるクエリのように、もともと少ない列で済むもの以外では狙いません
• 夜間のバッチで1日1回だけ走る重いクエリ：ほかの処理と時間が重ならず、終わる時間に余裕があるなら、そのためだけのインデックスを足して書き込みを遅くするより、全件を読むままにしておくほうがよいこともあります

まとめ#

• インデックスは「張ったか」ではなく、そのクエリで使われているかを、本番に近いデータで EXPLAIN を見て確かめる。読んだ行数まで見るときは EXPLAIN ANALYZE を、レプリカかステージングで使う
• 複合インデックスは、= で比べる列を先に、その後に並び順・範囲の列を置き、クエリの形に合わせて作る（並び順と範囲の列が違えば、どちらかを諦める）。列を関数に通す・型を合わせない・先頭が % の LIKE は、インデックスを効かなくする
• レビューでは、新しいクエリに EXPLAIN の結果と、インデックスに「どのクエリのためか」を求める。インデックスを消すときは、見えなくする段階を挟んで戻せるようにする