MySQLのEXPLAINの読み方：possible_keys、key、Extra、そしてインデックスの列の順序
誰もが読めるようにAIで翻訳しています。原文はスペイン語です。 原文を読む
DBAになりたてのころ、私は遅いクエリにずいぶん苦しみました。クエリが遅いことはわかるのに、どこを最適化できるのかを突き止める方法がわからなかったのです。やがてEXPLAINの読み方を少しずつ覚えました。EXPLAINは、クエリを実行する前に、それをどう実行するつもりかをデータベースエンジンに尋ねる方法です。この記事は、当時の私が誰かに説明してほしかったことです。
前回と違って、今回はMySQLだけの話です。MySQLから生まれたMariaDBもほぼ同じ表を返すので、違うところはそのつど書きます。
例のテーブル
5,000人の顧客による200,000件の注文です。90%が配達済み、7%が発送済み、3%が保留中で、日付は2022年から2025年にばらけています。行は再帰CTEで生成した連番から作っています。再帰CTEはテーブルをテストデータで埋める方法の記事で説明しました。
CREATE TABLE clientes (
id INT AUTO_INCREMENT PRIMARY KEY,
nombre VARCHAR(100) NOT NULL,
pais CHAR(2) NOT NULL
);
CREATE TABLE pedidos (
id INT AUTO_INCREMENT PRIMARY KEY,
cliente_id INT NOT NULL,
estado VARCHAR(20) NOT NULL,
fecha DATE NOT NULL,
total DECIMAL(10,2) NOT NULL
);
SET SESSION cte_max_recursion_depth = 200000;
INSERT INTO clientes (nombre, pais)
WITH RECURSIVE serie (n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM serie WHERE n < 5000
)
SELECT CONCAT('Cliente ', n), ELT(1 + n % 5, 'CR', 'MX', 'ES', 'AR', 'CO')
FROM serie;
INSERT INTO pedidos (cliente_id, estado, fecha, total)
WITH RECURSIVE serie (n) AS (
SELECT 1
UNION ALL
SELECT n + 1 FROM serie WHERE n < 200000
)
SELECT 1 + FLOOR(RAND() * 5000),
CASE WHEN n % 100 < 90 THEN 'entregado'
WHEN n % 100 < 97 THEN 'enviado'
ELSE 'pendiente' END,
DATE '2022-01-01' + INTERVAL FLOOR(RAND() * 1400) DAY,
ROUND(RAND() * 1000, 2)
FROM serie;MariaDBでは、SETの変数名はmax_recursive_iterationsです。
今のところ、pedidosには主キーしかありません。インデックスは途中で1つずつ作り、それぞれで実行計画の何が変わるかを見ていきます。データはランダムなので、例を試してもrowsの数字は私のものと完全には一致しません。出力はMySQL 8.0のものです。
MySQLに実行計画を尋ねる
SELECTの前にEXPLAINと書くだけです。
EXPLAIN SELECT * FROM pedidos WHERE cliente_id = 1234;+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | pedidos | NULL | ALL | NULL | NULL | NULL | NULL | 200000 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+EXPLAINはクエリを実行しません。返すのは実行計画で、クエリが読むテーブルごとに1行です。各列の意味は次のとおりです。
idとselect_type：その行がどのSELECTに属するか。サブクエリやUNIONがあるときに意味を持ち、単純なクエリでは1とSIMPLEです。table：テーブル、またはその別名。type：テーブルへのアクセス方法。よく見るものを良い順に並べると、const（キーによる1行だけ）、eq_ref（前のテーブルの1行ごとに1行）、ref（インデックスのある値を持つ行）、range（インデックスの一区間）、index（インデックス全体）、ALL（テーブル全体）です。possible_keysとkey：役に立ちそうなインデックスと、実際に選ばれたインデックス。この2つは後で節を分けて説明します。key_len：インデックスの何バイトを使うか。複数列のインデックスでは、何列使ったかがわかります。ref：インデックスを何と比較するか。定数（const）か、別のテーブルの列です。rows：調べる行数の見積もり。テーブルの統計情報から出した推定で、数えた値ではありません。filtered：そのうちWHEREを通過すると見込まれる行の割合。Extra：ほかの列に収まらない情報。絞り込むか、並べ替えるか、一時テーブルを作るか、などです。
この例では、typeがALL、keyが空、rowsが200,000です。ある顧客の40件の注文を返すために、MySQLは200,000件すべてを読むことになります。
この表ではなくテキストのツリーが返ってくる場合は、変数explain_format（MySQL 8.0.32から）がTREEになっています。私のテスト環境にある最新のMySQLは、デフォルトでそうなっています。EXPLAIN FORMAT=TRADITIONALと書けば表が返ります。
possible_keysとkey：インデックスがあることと、使われることは別
この2列は、私がいちばん理解に苦労した列です。possible_keysはオプティマイザがそのクエリに使えそうだと考えるインデックス、keyは実際に使うインデックスです。possible_keysにインデックスが10個並んでいても、keyに何も出ていなければ、そのクエリはインデックスを使いません。
keyが空になる理由は2つあります。1つ目は役に立つインデックスがない場合で、そのときは上の例のようにpossible_keysも空です。2つ目はインデックスがあるのに、オプティマイザが使わないと決めた場合です。確かめるために、日付にインデックスを作ります。
CREATE INDEX idx_fecha ON pedidos (fecha);1か月分の注文では使われます。
EXPLAIN SELECT * FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01';+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| range | idx_fecha | idx_fecha | 3 | NULL | 4399 | 100.00 | Using index condition |
+-------+---------------+-----------+---------+------+------+----------+-----------------------+1年分では使われません。
EXPLAIN SELECT * FROM pedidos
WHERE fecha >= '2024-01-01' AND fecha < '2025-01-01';+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL | idx_fecha | NULL | NULL | NULL | 200000 | 50.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+idx_fechaはpossible_keysにありますが、keyは空で、typeはまたALLです。1年分は注文の4分の1にあたります。テーブルの4分の1を、インデックスから1行ずつ探して飛び飛びに読むより、テーブル全体を順に読むほうが安くつくのです。これは不具合ではなく判断で、正しい判断のこともあります。filteredにも注目してください。実際の割合は26%なのに、50%と見積もっています。見積もりは見積もりで、外れることもあります。
いちばんよくある落とし穴は別のところにあります。インデックスがあるのに、それを使えない書き方で条件を書いてしまうことです。同じ10月の注文を、年と月で指定してみます。
EXPLAIN SELECT * FROM pedidos
WHERE YEAR(fecha) = 2025 AND MONTH(fecha) = 10;+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL | NULL | NULL | NULL | NULL | 200000 | 100.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+今度はpossible_keysが空です。インデックスは日付を順番に並べて持っていますが、その年や月を持っているわけではありません。どの日付がYEAR(fecha) = 2025を満たすかを知るには、MySQLは1行ずつ計算するしかありません。列に関数や計算をかけると、どれも同じです。DATE(fecha)、LOWER(email)、total * 1.13などです。解決策は、最初の10月の例のように、条件を列の範囲として書くことです。
Extra列
最後の列で、ほかが問題なさそうに見えるときにいちばん多くを語る列です。私がよく出会う値は次のとおりです。
Using where：行を読んだあとでWHEREを適用します。typeがALLなら、テーブル全体を読んで条件に合わない行を捨てているということです。Using index：クエリが求めるものがすべてインデックスの中にあるので、テーブルを読まずに済みます。ここに出るものとしては最良です。Using index condition：WHEREの一部を、行を取りに行く前にインデックスの中で確かめます。Using filesort：インデックスから並んだ状態で読めないので、結果を別に並べ替える必要があります。名前に惑わされないでください。ディスクに書くという意味ではありません。Using temporary：クエリを解決するために一時テーブルを作ります。典型的なのは、GROUP BYとORDER BYが別々の列を指しているときです。
10月の注文を数えるだけなら、テーブルに触れずに済みます。日付はインデックスにあり、ほかには何も要らないからです。
EXPLAIN SELECT COUNT(*) FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01';+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| range | idx_fecha | idx_fecha | 3 | NULL | 4399 | 100.00 | Using where; Using index |
+-------+---------------+-----------+---------+------+------+----------+--------------------------+ある日の注文を高い順に並べる場合、読むのは日付のインデックス経由ですが、合計金額で並べ替える必要があります。
EXPLAIN SELECT * FROM pedidos
WHERE fecha = '2024-05-10'
ORDER BY total DESC;+------+---------------+-----------+---------+-------+------+----------+----------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+---------------+-----------+---------+-------+------+----------+----------------+
| ref | idx_fecha | idx_fecha | 3 | const | 124 | 100.00 | Using filesort |
+------+---------------+-----------+---------+-------+------+----------+----------------+10月にいちばん多く使った顧客10人を出すには、その両方が要ります。顧客ごとに合計するための一時テーブルと、合計による並べ替えです。
EXPLAIN SELECT cliente_id, SUM(total) AS gastado
FROM pedidos
WHERE fecha >= '2025-10-01' AND fecha < '2025-11-01'
GROUP BY cliente_id
ORDER BY gastado DESC
LIMIT 10;+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+
| range | idx_fecha | idx_fecha | 3 | NULL | 4399 | 100.00 | Using index condition; Using temporary; Using filesort |
+-------+---------------+-----------+---------+------+------+----------+--------------------------------------------------------+どちらも、それ自体が悪いわけではありません。私が見るのは、1日に何千回も実行されるクエリや、多くの行を扱うクエリにそれが出ていないかです。
クエリが複数のテーブルを読むときは、実行計画にテーブルごとに1行ずつ、読む順番で並びます。
EXPLAIN SELECT c.nombre, p.fecha, p.total
FROM pedidos AS p
JOIN clientes AS c ON c.id = p.cliente_id
WHERE p.fecha = '2024-05-10';+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+
| table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+
| p | ref | idx_fecha | idx_fecha | 3 | const | 124 | 100.00 | NULL |
| c | eq_ref | PRIMARY | PRIMARY | 4 | tienda.p.cliente_id | 1 | 100.00 | NULL |
+-------+--------+---------------+-----------+---------+--------------------------------+------+----------+-------+まずidx_fechaでその日の注文を読み、それぞれについて主キー（eq_ref）で顧客を読みます。refが示すとおり、比較する相手はp.cliente_idです。
インデックスの列の順序
インデックスは複数の列を持てます。ある顧客の、ある状態で、ある日付の注文を探すための3列のインデックスを作ります。
CREATE INDEX idx_cliente_estado_fecha ON pedidos (cliente_id, estado, fecha);WHEREに3列すべてがあれば、インデックスを丸ごと使います。
EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND estado = 'pendiente' AND fecha = '2024-05-10';+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| ref | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89 | const,const,const | 1 | 100.00 | NULL |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+私は長いあいだ、WHEREの条件はインデックスの列と同じ順番で書かなければならず、そうしないとインデックスが効かないと思い込んでいました。それは間違いでした。オプティマイザは、条件がどこに書かれていても、それぞれを対応する列に結びつけます。順番を逆にしても、実行計画はまったく同じです。
EXPLAIN SELECT * FROM pedidos
WHERE fecha = '2024-05-10' AND estado = 'pendiente' AND cliente_id = 1234;+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+
| ref | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89 | const,const,const | 1 | 100.00 | NULL |
+------+------------------------------------+--------------------------+---------+-------------------+------+----------+-------+大事なのは、インデックスの中での列の順序です。複数列のインデックスは、まず1列目で並び、1列目の値が同じものの中では2列目で並び、以下同様です。姓と名の順に並んだ電話帳のようなものです。姓で探すことも、姓と名で探すこともできますが、名が「Ana」の人を全員探すことはできません。MySQLのマニュアルはこれを左端プレフィックスと呼んでいます。(cliente_id, estado, fecha)のインデックスは、cliente_idでの検索、cliente_idとestadoでの検索、3列すべてでの検索に使えます。cliente_idなしでestadoやfechaを探す検索には使えません。
顧客を指定せずに、今年の保留中の注文を探してみます。
EXPLAIN SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL | idx_fecha | NULL | NULL | NULL | 199728 | 4.34 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+テーブルにはestadoとfechaを含むインデックスがありますが、cliente_idから始まっているので、possible_keysにさえ出てきません。あるのはidx_fechaだけで、オプティマイザはさっきと同じ理由でそれを使いません。ほぼ1年分の注文だからです。
key_len：何列使ったか
インデックスに複数の列があるとき、key_lenはどこまで使ったかを教えてくれます。各列はインデックスの中で決まったバイト数を占めます。cliente_idはINTで4バイト、estadoはutf8mb4のVARCHAR(20)で、最大4バイトの文字が20文字と長さの2バイトで82、fechaはDATEで3です。3列合わせると、上の89になります。NULLを許す列は1バイト増えます。
顧客と状態だけで日付がなければ、最初の2列、4 + 82を使います。
EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND estado = 'pendiente';+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+
| ref | idx_cliente_estado_fecha | idx_cliente_estado_fecha | 86 | const,const | 1 | 100.00 | NULL |
+------+--------------------------+--------------------------+---------+-------------+------+----------+-------+顧客と日付で状態を飛ばすと、使えるのは1列目だけです。
EXPLAIN SELECT * FROM pedidos
WHERE cliente_id = 1234 AND fecha >= '2024-01-01';+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+
| ref | idx_fecha,idx_cliente_estado_fecha | idx_cliente_estado_fecha | 4 | const | 40 | 50.00 | Using index condition |
+------+------------------------------------+--------------------------+---------+-------+------+----------+-----------------------+keyにはidx_cliente_estado_fechaと出ているので、この列だけを見ればインデックスがすべて効いていると思うでしょう。しかしkey_lenは4で、検索に使っているのはcliente_idだけです。日付はインデックスの中で確かめられますが（Using index condition）、検索範囲を狭めるのには使えません。顧客と日付のあいだに状態があるからです。
keyに名前があっても全部読む場合
もう1つ知っておきたいケースがあります。クエリがインデックスにある列だけを求めている場合、絞り込みが1列目から始まっていなくても、MySQLはテーブルの代わりにインデックス全体を読むことがあります。
EXPLAIN SELECT cliente_id, estado, fecha FROM pedidos
WHERE estado = 'pendiente';+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+
| index | idx_cliente_estado_fecha | idx_cliente_estado_fecha | 89 | NULL | 199728 | 10.00 | Using where; Using index |
+-------+--------------------------+--------------------------+---------+------+--------+----------+--------------------------+keyには名前がありますが、typeはindexで、rowsは199,728です。インデックスの200,000件の項目を1つずつたどっています。インデックスのほうが小さいのでテーブルをたどるより速いのですが、検索ではありません。MariaDBでは同じ実行計画が、possible_keysが空でkeyに名前がある形で出てきます。これはまさにMySQLのマニュアルが説明しているケースで、インデックスは行を見つけるのには役立たないものの、求められた列をすべて含んでいるのです。だからkeyは単独で読まず、必ずtypeとrowsと合わせて読みます。
解決策
よく実行するクエリが状態と日付によるものなら、インデックスは状態から始める必要があります。
CREATE INDEX idx_estado_fecha ON pedidos (estado, fecha);EXPLAIN SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+
| type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+
| range | idx_fecha,idx_estado_fecha | idx_estado_fecha | 85 | NULL | 1321 | 100.00 | Using index condition |
+-------+----------------------------+------------------+---------+------+------+----------+-----------------------+199,728行から1,321行になり、key_lenは85です。状態の全体の82に、日付の3を足した値です。列の順序を決めるときの私のルールは、=で比較する列を先に、範囲（>=、BETWEEN）で比較する列を最後に置くことです。範囲のあとに続くインデックスの列は、もう検索範囲を狭めないからです。
ほかのエンジンでも同じか
同じです。左端プレフィックスはB-treeインデックスの性質で、B-treeはどのリレーショナルエンジンでもデフォルトのインデックスです。PostgreSQL、SQLite、Oracleのドキュメントも同じように説明しています。WHEREの条件を書く順番が影響するエンジンはありません。
年月を経て一部のエンジンが身につけたのは、1列目の値の種類がごく少ないときに、1列目を飛ばすことです。1列目の値ごとに1回ずつ、2列目で検索します。これは「スキップスキャン」（skip scan）と呼ばれ、MySQLは8.0.13から、PostgreSQLは18から、そしてOracleとSQLiteも対応しています（SQLiteではANALYZEを実行してある場合だけ）。MySQLではExtraにUsing index for skip scanと出ますが、クエリが1つのテーブルだけを読み、インデックスの列だけを求めているなど、いくつかの条件を満たすときに限られます。ここでは役に立たなかったでしょう。1列目は顧客で、5,000人もいるからです。
見積もりではなく測る
ここまでの数字はすべて見積もりです。EXPLAINはクエリを実行しません。MySQL 8.0.18からは、EXPLAIN ANALYZEがクエリを実際に実行し、見積もりの隣に実際に起きたことを並べてくれます。
EXPLAIN ANALYZE SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';-> Index range scan on pedidos using idx_estado_fecha over (estado = 'pendiente' AND '2025-01-01' <= fecha), with index condition: ((pedidos.estado = 'pendiente') and (pedidos.fecha >= DATE'2025-01-01')) (cost=595 rows=1321) (actual time=1.01..12.6 rows=1321 loops=1)最初のかっこの中のrows=1321が見積もり、2つ目のかっこの中のactual timeとrows=1321が、かかった時間（ミリ秒）と実際に読んだ行数です。2つの行数がかけ離れているときは、テーブルの統計情報が古くなっています。ANALYZE TABLE pedidosで計算し直せます。
MariaDBにはEXPLAIN ANALYZEがありません。その代わりが、10.1からある、クエリの前に付けるANALYZEです。いつもの表に、実際に起きたことを示すr_rowsとr_filteredの2列が加わって返ってきます。
ANALYZE SELECT * FROM pedidos
WHERE estado = 'pendiente' AND fecha >= '2025-01-01';どちらもクエリを実行するので、クエリと同じだけ時間がかかります。また、MariaDBのANALYZE UPDATEやANALYZE DELETEは、データを本当に変更します。
Calíopeでは
SQLエディタでクエリを書いたら、ツールバーの実行計画ビューアボタンを押すと、クエリを実行せずにサーバーへ実行計画を尋ね、ツリーとして描きます。アクセス方法ごとに色が付き、テーブルの全件スキャンは赤、インデックス全体のスキャンはオレンジ、インデックスの一区間は黄、キーによる検索は緑です。実行計画はJSONで取得するので、explain_formatの設定には左右されません。また、結果バーのインデックスボタンを押すとインデックスアドバイザが開きます。その実行計画から全件スキャンを探し、どのインデックスにも含まれない列でクエリが絞り込んでいれば、CREATE INDEXを提案します。
私が最初に見るところ
クエリが遅いとき、私は次の順番でEXPLAINを読みます。
key：空なら、インデックスはまったく使われていません。possible_keysに何かあればオプティマイザがそれを退けたということ、possible_keysも空なら、役に立つインデックスがないか、条件の書き方がインデックスを使わせていないということです。typeとrowsを合わせて：ALLやindexで行数が多ければ、keyに名前があっても、テーブルかインデックスを丸ごと読んでいます。key_len：インデックスが複数列なら、実際に何列使ったかがわかります。Extra：よく実行されるクエリにUsing temporaryやUsing filesortが出ていたら、もう一度よく見る価値があります。