MySQL EXPLAINの読み方｜複合インデックスの列順を実測で比べる

EXPLAINにUsing filesortが残っている。インデックスの列順を変えると消えたけれど、それで速くなったと言えるでしょうか。

複合インデックスは、条件で読む範囲を狭める案と、必要な順に読んで早く止める案を比べて選びます。EXPLAINのkeyだけで決めず、同じ結果が返ることを確かめ、EXPLAIN ANALYZEで実際に通った行数を見ます。

この記事ではMySQL 8.4.11の隔離環境で、10万行の注文データに二つの索引を別々に作りました。遅いSELECTが特定できた後の比較が対象です。対象SQLを探す段階なら、先にスロークエリログの取り方を確認してください。

同じSELECTに、二つのインデックスを試す

調べるのは「テナント3・ステータス1・金額9,000以上の注文を、新しい順に20件」です。金額で絞ることと日付順に読むことを、どちらも一つの索引で済ませたい場面です。

SELECT id, created_at, amount
FROM orders_demo
WHERE tenant_id = 3 AND status = 1 AND amount >= 9000
ORDER BY created_at DESC
LIMIT 20;

候補	列順	狙い
A：ix_filter	tenant_id, status, amount, created_at	金額の範囲まで絞ってから並べる
B：ix_order	tenant_id, status, created_at, amount	日付の新しい順に読み、金額の条件を満たす20件で止める

どちらにも使いどころがあります。「等価条件、範囲条件、ORDER BYの順」と覚えて終わると、Bの選択肢を見落とします。

検証用データを作る

以下は新しく用意した検証用MySQLで実行してください。既存の業務DBへ流す手順ではありません。explain_labが既にあれば、CREATE DATABASEで止まります。金額と作成日時が単純に連動しない合成データを10万行作ります。

-- 新しく作った検証用MySQLだけで実行する。既存DBがあればCREATEで停止する。
CREATE DATABASE explain_lab;
USE explain_lab;
CREATE TABLE orders_demo (
  id BIGINT UNSIGNED NOT NULL,
  tenant_id INT NOT NULL,
  status TINYINT NOT NULL,
  amount INT NOT NULL,
  created_at DATETIME NOT NULL,
  memo VARCHAR(180) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO orders_demo
WITH digits AS (
  SELECT 0 AS d UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
  UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
), numbers AS (
  SELECT a.d + b.d * 10 + c.d * 100 + d.d * 1000 + e.d * 10000 AS n
  FROM digits a CROSS JOIN digits b CROSS JOIN digits c
  CROSS JOIN digits d CROSS JOIN digits e
)
SELECT n + 1, MOD(n, 10) + 1, MOD(FLOOR(n / 10), 4),
       MOD(FLOOR(n / 10) * 7919 + 13, 10000),
       DATE_ADD('2025-01-01 00:00:00', INTERVAL FLOOR(n / 10) HOUR),
       REPEAT('x', 160)
FROM numbers;
ANALYZE TABLE orders_demo;
SELECT COUNT(*) AS total_rows FROM orders_demo;

同じmysql接続で続ければ、USEで選んだDBが維持されます。別の接続から最初のSELECTだけを実行するときは、先にUSE explain_lab;を実行してください。今回の条件内ではcreated_atが一意なので、同じ日時の行の順序に左右されず結果を比較できます。

追加前・A・Bの計画を順に記録する

まず追加前に、次の二つを実行します。通常のEXPLAINは計画を示し、EXPLAIN ANALYZEはSELECTそのものを実行して実測を返します。重いSQLを本番で試す前に、実行範囲や負荷を判断してください。

USE explain_lab;
EXPLAIN FORMAT=TRADITIONAL
SELECT id, created_at, amount
FROM orders_demo
WHERE tenant_id = 3 AND status = 1 AND amount >= 9000
ORDER BY created_at DESC
LIMIT 20;

-- ANALYZEは対象SELECTを実行する。今回の隔離データだけで比較する。
EXPLAIN ANALYZE
SELECT id, created_at, amount
FROM orders_demo
WHERE tenant_id = 3 AND status = 1 AND amount >= 9000
ORDER BY created_at DESC
LIMIT 20;

次にAを作り、同じEXPLAINとSELECTを再実行します。

USE explain_lab;
CREATE INDEX ix_filter
  ON orders_demo (tenant_id, status, amount, created_at);
ANALYZE TABLE orders_demo;

Aの記録を保存したらAを削除し、Bだけを作って再実行します。二つを同時に置くと、比較したい方が選ばれるとは限りません。ここでは索引を強制するヒントも使っていません。

USE explain_lab;
-- AとBを同時に置かず、同じデータで別々に比較する検証用の手順。
DROP INDEX ix_filter ON orders_demo;
CREATE INDEX ix_order
  ON orders_demo (tenant_id, status, created_at, amount);
ANALYZE TABLE orders_demo;

amount >= 9000	追加前	A：金額を先にする	B：日付を先にする
type / key	ALL / NULL	range / ix_filter	ref / ix_order
key_len	NULL	9	5
EXPLAINのrows（推定）	104,315	250	2,500
末端の走査処理が返した行数（実測）	100,000	250	190
SELECTの結果	20行	同じ20行	同じ20行
Using filesort	あり	あり	なし

この実測では、Bは190行を調べたところで金額条件に合う20行がそろい、LIMITで止まりました。推定rowsの2,500を、そのまま実際に読んだ行数として扱わないのがポイントです。

Aでは、金額で絞れた後にも並べ替えが必要

Aの先頭二列は等価条件で固定され、amountの範囲へ進めます。ただし、その中では金額ごとにcreated_atが並んでいます。金額が異なる注文全体を新しい順に返すには、別途並べ替える必要があります。

今回のAは250行まで狭めた後に並べ替えました。Using filesortは、この追加の並べ替えを表します。名前だけで「ディスクに書き出した」「索引が役に立たなかった」とは判断できません。

Bでは、日付順に読みながら金額を調べる

Bもtenant_idとstatusを固定します。その範囲をcreated_atの逆順に読めるので、ExtraにはBackward index scanが現れました。amountは各行を読みながら条件に合うか確認します。

Bのkey_lenは5で、今回のNOT NULLなINTとTINYINTの計4＋1バイトに対応します。しかしcreated_atが無意味なわけではありません。検索開始位置を絞る部分と、並び順に使う部分は違います。key_lenが短い＝残りの列は一切使われないとは読めません。

条件に合う注文が少ないと、Bは多く読む

次に、同じSELECTの金額条件だけをamount >= 9995へ変えます。AとBをそれぞれ単独で作った状態で比べると、結果はどちらも同じ1行でした。

金額の下限	Aの走査行数	Bの走査行数	返した行数
9,000	250	190	20
9,995	1	2,500	1

Aは金額の範囲を直接狭めるため、対象の1行だけを読みました。Bは日付順に調べても20件そろわず、tenant_idとstatusが一致する2,500行を最後まで読みます。並べ替えがないことより、条件に合う行へどれだけ少ない読み取りで届くかが効く場面です。

記録したルート処理の終了時刻は、9,000の条件でAが約0.062ms、Bが約0.067msでした。9,995ではAが約0.016ms、Bが約0.341msです。単発の小さな合成データの測定なので、速度差の保証には使えません。読み取り量と、その差が生まれる理由を比較材料にしてください。

本番に近い検証では、よく使う条件だけでなく「ほとんど該当しない値」「件数の多いテナント」「LIMITの変更」も候補に入れます。一つの条件で速くなっても、同じ画面の別条件で負担が増えることがあります。

EXPLAINの列を、今回の判断に結びつける

列・表示	何を見るか	今回の読み方
type	行へのアクセス方法	ALLは全表走査、rangeは範囲、refは同じキー値の候補を読む。名前の順位だけで採用しない
possible_keys / key	探索の候補 / 実際に選んだ索引	keyが付いた後も行数を見る。候補一覧にない索引がカバリング目的で選ばれる場合もある
key_len	探索に用いるキー部分の長さ	Aは9、Bは5。型やNULL許容も関係するので列数そのものではない
rows / filtered	調べる行数の推定 / 条件を通る割合の推定	残る行数の目安はrows × filtered ÷ 100。filteredは捨てる割合ではない
Using index	必要な値を索引で賄える	単に「索引を使った」という意味ではない。今回はAもBも表示された
Using filesort	索引の順序だけでは済まない並べ替え	有無と、並べ替えへ渡る行数を合わせて見る

各列の詳細はMySQL公式のEXPLAIN出力説明でも確認できます。今回のInnoDBではセカンダリインデックスに主キー値も含まれるため、SELECTのidを賄えます。InnoDBの索引構造を踏まえると、列定義にidを重ねて足す必要がない理由が分かります。

TREEは下の走査から、Filter、Limitへ追う

Bで金額9,995を指定した記録から、実測部分を抜き出すと次のようになります。

Limit: 20 row(s)                  actual rows=1    loops=1
  Filter: amount >= 9995          actual rows=1    loops=1
    Covering index lookup (逆順) actual rows=2500 loops=1

下から2,500行が渡され、金額の条件を通ったのは1行です。LIMITの20は上限なので、結果が1行でも矛盾しません。TREEのrowsはその処理が返した行数で、上のLimitのrowsだけを見ても走査量は分かりません。

actual time=a..bは最初の行までと処理を終えるまでの時間をミリ秒で示します。子の処理を含むので、親子の時間を足し合わせないでください。loopsが複数なら、時間は1ループあたりの平均です。詳しくはEXPLAIN ANALYZEの公式説明を参照してください。今回の記録はすべてloops=1です。

列順を決める前に、他の使い方と追加コストも見る

複合インデックスは、通常、左端からの組み合わせを手掛かりに探索します。たとえば(tenant_id, status, amount)なら、tenant_idだけ、tenant_idとstatus、さらにamountという使い方を考えられます。WHERE句に条件を書く順番と、インデックスの列順は別物です。

先頭列の条件がないSQLは、同じように狭い範囲を探索できると決めつけず、実際の計画を確認します。例外的なアクセス方法もあるため「先頭条件がなければ索引は絶対使われない」とまで覚える必要はありません。複合インデックスの左端からの利用と、ORDER BYに索引を使う条件を分けて考えると整理できます。

範囲条件より後ろの列も、今回のAのcreated_atのように、取得する値を索引内で賄うためには役立ちます。探索範囲を狭められないことと、その列に役割がないことは同じではありません。

採用前には、次の四点を記録しておきます。

• 同じ条件で、返る値と必要な順序が変わっていないか。
• 代表的な条件と、該当件数が少ない条件の両方で、実測の走査行数がどう変わったか。
• 既存索引と先頭列が重複していないか。他のSELECTが必要とする列順ではないか。
• 読み取りの改善に対し、INSERT・UPDATE時の索引更新と保存容量の負担が見合うか。

小さい表や大部分を返すSQLなら、全表走査が選ばれること自体を不具合と決める必要はありません。索引を増やすことが目的にならないようにします。本番へ追加するDDLと実行時の注意点は、MySQLのDDLと安全なスキーマ変更で確認してください。

手元のSQLで迷ったら、まず「範囲を狭める案」と「順序を使って早く止める案」を一つずつ書き出してみてください。同じ結果を返す二案の、走査行数と条件ごとの変化を比べるところから選べます。