本線の回で、索引を張ってクエリが速くなるところは見ました。あれは、うまくいった例です。
これは深掘りです。本線の順路ではありません。 読まなくても先へは進めます。 本線の速くする前に必ず測る — パフォーマンス改善とEXPLAINの回の続きです。あの回で「
EXPLAINを打って索引を張ったらrowsが20万から1になった」ところまでやりました。この記事はその先、「張ったのに使われない」を1本かけて潰します。前回: トランザクション分離レベル — 同じSELECTが2回目で変わる
作業場所はVMの中(Ubuntu Server)です。MySQL 8.0 以降が動いていれば、それ以外の準備は要りません。練習用のテーブルはこの記事の中で作って、最後に全部消します。既存のテーブルには一切触りません。
そのあと私が実際にやったのは、こうでした。
遅いAPIがある。SQLを取り出す。WHERE に出てくる列が3つある。3つとも索引を張った。 張り終わって、もう一度叩いた。
1ミリ秒も変わりませんでした。
SHOW INDEX を打つと、索引はちゃんと3本あります。張れている。 でも速くない。このとき私は「索引は気休めなんだな」と思いかけました。 それは完全に間違いでした。
正しい言い方はこうです。
索引は「張る」と「使われる」が別のことです。 張るのは自分の仕事、使うかどうかを決めるのはデータベースの仕事です。
そして、使われるかどうかは EXPLAIN で見えます。 見えるのに、私は見ていませんでした。張ったから効いているはずだ、と思い込んでいたからです。
この記事でやるのは、その「使われなかった側」を全部わざと再現することです。効く例だけ見ていても、効かないときに自分が何をしたのか分かりません。6通りの「効かない書き方」を、同じ表に対して順番に打って、EXPLAIN を並べます。
対象レベル: 中級〜上級 / 区分: 発展
この記事でわかること
- 索引は「張る」と「使われる」が別だということ。張っただけでは何も保証されないこと。確かめる道具は
EXPLAINひとつだということ - 複合索引
(a, b)は左端から使うこと。WHERE b = ?だけではまったく使われないこと。だから列の順番が索引の性能そのものだということ - 索引は
WHEREだけでなくORDER BYにも効くこと。効いたときはUsing filesortが消えること - 索引があるのに全表走査を選ぶほうが、実際に速い場合があること。それはバグではなくオプティマイザの判断だということ
- 索引はタダではないこと。書き込みが遅くなり、ディスクを食うこと。それを張る前後で実測できること
FORCE INDEXは最後の手段であり、普段使うと将来のほうが遅くなること
この記事でやること
- 練習用のテーブルを作り、
WITH RECURSIVEで30万行を入れる(既存のテーブルには触らない) - 複合索引
(user_id, created_at)を1本だけ張り、WHEREの書き方を3通り変えてEXPLAINを読む - 索引が効かなくなる書き方を6つ順番に打ち、
typeとrowsを1枚の表にまとめる - カバリングインデックスで
Using indexを出す。EXPLAIN ANALYZEで推定と実測を比べる - 索引を張る前と後で
INSERTの所要時間とディスク使用量を測る - 使われていない索引・重複した索引を探し、統計情報を取り直して、最後に全部消す
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| 索引 / インデックス | 表とは別に持っている、探すための並べ替え済みデータ。 本の巻末の索引と同じで、本体とは別に場所を食う |
| 複合索引 | 2列以上をまとめて1本にした索引。 (user_id, created_at) のように順番付きで持つ。順番に意味がある |
| 実行計画 | データベースが「このSQLをこう実行するつもりだ」と決めた手順。EXPLAIN で見られる |
| オプティマイザ | 実行計画を選ぶ担当。 索引を使うか全部読むかを、統計情報を見て自分で決める。 人が書いた順番どおりには動かない |
| 全表走査 / フルスキャン | 表の全行を上から読むこと。EXPLAIN では type: ALL と出る |
| 選択率(カーディナリティ) | その条件で全体の何割まで絞れるか。 30万行から3行なら高い、15万行残るなら低い。低いと索引は使われない |
| カバリングインデックス | 必要な列が全部その索引に入っている状態。表の本体を一度も読まずに済む。 Extra に Using index と出る |
| filesort | 並べ替えを、その場でやったという印。 索引の順番をそのまま使えたときは出ない。「ファイルに書いた」という意味ではない |
| 統計情報 | 「この列にはだいたい何種類の値があるか」という概算のメモ。 これを見てオプティマイザが判断する。古いと判断を誤る |
練習用のデータを作る
この記事は他の回のデータに依存しません。 ここに書いてある CREATE と INSERT を打てば、そのまま最後まで進めます。
VMの中で MySQL に入ります。
sudoは「管理者としてこのコマンドを実行する」という意味です。MySQL の初期状態では、管理者ユーザーでしか入れないためこうします。
データベースを選びます。
USE appdb;
appdbが無いと言われたら、CREATE DATABASE appdb;を先に打ってからUSE appdb;します。本線でappdbを使っている場合は、そのまま使って構いません。 この記事で作る表はすべてd_ix_で始まる名前にするので、既存の表とはぶつかりません。
表を作る
CREATE TABLE d_ix_events (
id INT NOT NULL,
user_id INT NOT NULL,
status TINYINT NOT NULL,
code VARCHAR(20) NOT NULL,
title VARCHAR(60) NOT NULL,
amount INT NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id)
);
この時点で索引は主キーの1本だけです。残りは自分で張ります。 最初から張ってあると「効いた/効かない」が見えないためです。
| 列 | 何のために入れたか |
|---|---|
user_id | 1000種類。 30万行なので1人あたり300行。よく絞れる列の代表 |
status | 0か1の2種類だけ。 半分が該当する。絞れない列の代表 |
code | 数字だけの文字列(00012345 の形)。型が違う比較を試すため |
title | 日本語混じりの文字列。LIKE を試すため |
amount | 索引を張らない列。 OR の実験に使う |
created_at | 日時。複合索引の2列目と ORDER BY に使う |
30万行入れる
まず、この1行を先に打ってください。
SET SESSION cte_max_recursion_depth = 1000000;
これを忘れると、次の
INSERTが1001行目で止まります。WITH RECURSIVEは「1、2、3…」と自分を呼び出しながら行を作る書き方で、暴走を防ぐために既定では1000回で打ち切られるようになっています。ここでは30万行ほしいので、その上限を上げます。SET SESSIONは、いま繋いでいる接続の中だけの設定です。 MySQL を抜ければ元に戻ります。設定ファイルは触りません。
INSERT INTO d_ix_events (id, user_id, status, code, title, amount, created_at)
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 300000
)
SELECT
n,
(n % 1000) + 1,
n % 2,
LPAD(n, 8, '0'),
CONCAT('イベント', n),
n % 5000,
DATE_ADD('2024-01-01 00:00:00', INTERVAL n MINUTE)
FROM seq;
マシンによっては数十秒かかります。 途中で止めないでください。
終わったら件数を確認します。
SELECT COUNT(*) FROM d_ix_events;
+----------+
| COUNT(*) |
+----------+
| 300000 |
+----------+
30万と出れば成功です。 中身も1行だけ見ておきます。
SELECT * FROM d_ix_events WHERE id = 12345;
+-------+---------+--------+----------+-------------------+--------+---------------------+
| id | user_id | status | code | title | amount | created_at |
+-------+---------+--------+----------+-------------------+--------+---------------------+
| 12345 | 346 | 1 | 00012345 | イベント12345 | 2345 | 2024-01-09 13:45:00 |
+-------+---------+--------+----------+-------------------+--------+---------------------+
失敗したときの戻し方は、いつでも同じです。
DROP TABLE IF EXISTS d_ix_events;を打って、CREATEからやり直してください。この記事で作るものは全部消して構いません。
[スクリーンショット: 01_30万行の投入完了.png] SELECT COUNT(*) FROM d_ix_events; が 300000 を返している画面を撮ってください。ここが揃っていないと、以降の rows の数字が全部ずれます。
索引の中身を、使うぶんだけ知る
索引の実装そのものは、この記事では作りません。 それは別の回でやりました。
「索引がなぜ速いのか」を、30行のデータベースを自分で書いて確かめる回があります。 索引が「速い」の正体 — データベースを自作する 中身が気になったらそちらへ。この記事は使う側の話だけに絞ります。
使う側として知っておけば足りるのは、次の3つだけです。
① 索引は「並べ替えて持っている別のデータ」です。
MySQL(InnoDB) の索引は B+木という形をしています。名前は覚えなくて構いません。大事なのは性質のほうです。
| 性質 | そこから出てくる結論 |
|---|---|
| 値の順に並んでいる | = だけでなく < や BETWEEN のような範囲でも使える。 並んでいるので、途中から順に辿れる |
| 並んでいるので、並べ替えの代わりになる | ORDER BY が索引の順番と一致すれば、並べ替えそのものが要らなくなる |
| 木を根から数回たどれば目的地に着く | 件数が10倍になっても、たどる回数はほとんど増えない。 これが「増えても遅くならない」の正体 |
② 索引には「先頭から順に読む」という制約があります。
複合索引 (user_id, created_at) は、まず user_id で並べ、同じ user_id の中で created_at で並べたデータです。
これは、電話帳が「姓 → 名」で並んでいるのと同じです。
- 姓が分かれば引ける
- 姓と名が分かれば、もっと速く引ける
- 名前だけ「太郎さんを探して」と言われたら、全ページ見るしかない
この3つ目が、次の山場です。
③ 索引は本体とは別のデータなので、書くたびに二重に書きます。
だから索引を増やすと INSERT が遅くなり、ディスクを食います。 これは後半で実測します。
複合索引は、列の順番がすべて
まず、複合索引を1本だけ張ります。
CREATE INDEX idx_ix_user_created ON d_ix_events (user_id, created_at);
30万行への索引作成は数秒かかります。 張れたか見ます。
SHOW INDEX FROM d_ix_events;
+--------------+------------+---------------------+--------------+-------------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Cardinality |
+--------------+------------+---------------------+--------------+-------------+-------------+
| d_ix_events | 0 | PRIMARY | 1 | id | 299034 |
| d_ix_events | 1 | idx_ix_user_created | 1 | user_id | 996 |
| d_ix_events | 1 | idx_ix_user_created | 2 | created_at | 298187 |
+--------------+------------+---------------------+--------------+-------------+-------------+
表示される列はもっと多いので、実際の画面では横に長く出ます。 見るのは3つです。
Key_name… 索引の名前。複合索引は同じ名前が2行に分かれて出るSeq_in_index… 索引の中で何列目か。 これが「左端」の正体ですCardinality… その列のおおよその種類数。user_idが996(実際は1000)。概算なのでピッタリではありません
3つの WHERE を、同じ表に打つ
索引は1本しかありません。条件の書き方だけを変えます。
(1) 左端の列だけで絞る
EXPLAIN SELECT id, amount FROM d_ix_events WHERE user_id = 500\G
id: 1
select_type: SIMPLE
table: d_ix_events
type: ref
possible_keys: idx_ix_user_created
key: idx_ix_user_created
key_len: 4
ref: const
rows: 300
filtered: 100.00
Extra: NULL
\Gは「縦に並べて表示して」という意味です。列が多いときは;より読みやすいので、EXPLAINではこちらを使います。
type: ref、rows: 300。 30万行のうち300行だけ見るつもりだ、と言っています。索引が使われています。
(2) 左端の列と、2列目の両方で絞る
EXPLAIN SELECT id, amount FROM d_ix_events
WHERE user_id = 500 AND created_at >= '2024-06-01 00:00:00'\G
type: range
possible_keys: idx_ix_user_created
key: idx_ix_user_created
key_len: 9
ref: NULL
rows: 82
filtered: 100.00
Extra: Using index condition
rows が 300 から 82 に減りました。 そして注目すべきは key_len が 4 から 9 に増えていることです。
key_lenは「索引の何バイト目まで使ったか」です。user_idはINTで4バイト。created_atはDATETIMEで5バイト。合わせて9。 つまりkey_lenを見れば、複合索引の何列目まで効いているかが分かります。 これはrowsより正確な手がかりです。
(3) 2列目だけで絞る
EXPLAIN SELECT id, amount FROM d_ix_events
WHERE created_at >= '2024-06-01 00:00:00'\G
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 299034
filtered: 33.33
Extra: Using where
possible_keys が NULL です。 「使えそうな索引が1本もない」と言っています。索引はちゃんと張ってあるのに、候補にすら入りません。
30万行を全部読みます。
この3つを並べる
| 書き方 | key | key_len | type | rows |
|---|---|---|---|---|
WHERE user_id = 500 | idx_ix_user_created | 4 | ref | 300 |
WHERE user_id = 500 AND created_at >= … | idx_ix_user_created | 9 | range | 82 |
WHERE created_at >= … | NULL | NULL | ALL | 299034 |
これが「左端プレフィックス」です。 複合索引は、左の列から順に使う。 途中から使うことはできない。
(a, b)の索引はaの検索とa AND bの検索には効き、b単独には効きません。
[スクリーンショット: 02_複合索引の左端.png] 上の3つの EXPLAIN を続けて打った画面を撮ってください。3つ目だけ type: ALL になっているところが見えると、この記事で一番はっきりした対比です。
「じゃあ (created_at, user_id) にすればいいのでは」
両方は成立しません。 順番を逆にすれば、今度は WHERE user_id = 500 単独が効かなくなります。
決め方はこうです。
=で絞る列を左に、範囲(><BETWEEN)で絞る列を右に置く- どちらも
=なら、よく絞れるほう(種類が多いほう)を左に ORDER BYに使う列は、絞る列のあとに置く
2列目に置いた列を単独でもよく検索するなら、それは別の索引を1本張る話です。1本で全部をまかなおうとしないこと。
ORDER BY にも効く(Using filesort が消える)
索引は WHERE 専用の道具ではありません。 並べ替えにも効きます。
まず、索引の順番と違う並べ替えをします。
EXPLAIN SELECT id, created_at FROM d_ix_events
WHERE user_id = 500 ORDER BY amount\G
type: ref
key: idx_ix_user_created
rows: 300
Extra: Using filesort
Extra: Using filesort。 300行を取り出したあと、その場で並べ替えたという意味です。
filesortは「ファイルに書き出した」という意味ではありません。 名前が悪いだけで、メモリ上で済むことがほとんどです。「並べ替えを別途やった」という印だと思ってください。
次に、索引の2列目で並べ替えます。
EXPLAIN SELECT id, created_at FROM d_ix_events
WHERE user_id = 500 ORDER BY created_at\G
type: ref
key: idx_ix_user_created
rows: 300
Extra: NULL
Using filesort が消えました。
索引は user_id の中で created_at 順に並んでいるので、取り出した時点ですでに並んでいるからです。並べ替えという作業そのものが無くなりました。
300行では体感差はありません。 これが効くのは、
ORDER BY … LIMIT 20を100万行に対してやるときです。 並べ替えが必要なら、100万行を全部取り出してから並べて上位20行を返します。 索引順に並んでいるなら、先頭から20行読んだ時点で終われます。 ここが桁で変わります。
ORDER BY created_at DESC でも filesort は出ません。 索引は逆から辿ることもできるからです。ただし ORDER BY user_id ASC, created_at DESC のように向きが混ざると出ます(MySQL 8.0 では降順索引を明示的に張ることもできますが、まずは向きを揃えるほうが先です)。
索引が効かなくなる書き方を、全部試す
ここが本題です。 索引が使われない書き方には型があります。 6つ全部を、同じ表に順番に打ちます。
まず、実験用に索引を3本足します。
CREATE INDEX idx_ix_code ON d_ix_events (code);
CREATE INDEX idx_ix_title ON d_ix_events (title);
CREATE INDEX idx_ix_status ON d_ix_events (status);
これで code・title・status にも索引がある状態になりました。 つまり以下の6つは全部、索引が張ってある列に対して打っています。 それでも使われません。
① 列に関数を掛ける
EXPLAIN SELECT id FROM d_ix_events WHERE LEFT(code, 4) = '0001'\G
type: index
possible_keys: NULL
key: idx_ix_code
key_len: 82
rows: 299034
Extra: Using where; Using index
rows は30万のままです。索引を「探すため」ではなく「頭から最後まで舐めるため」に使っています(type: index)。
理由は単純です。 索引に入っているのは code の値であって、LEFT(code, 4) の値ではありません。 データベースは全行について関数を計算してみるまで、条件に合うか判断できません。
書き換えれば効きます。
EXPLAIN SELECT id FROM d_ix_events WHERE code LIKE '0001%'\G
type: range
key: idx_ix_code
rows: 10000
Extra: Using where; Using index
30万 → 約1万。 同じ結果を返す2つの書き方で、読む行数が約30倍違います(rows は推定値なので、環境によって多少前後します)。
「左辺に列だけを置く」。これが索引を活かす書き方の第一原則です。
WHERE price * 1.1 > 1000ではなくWHERE price > 1000 / 1.1。計算は右辺でやる。
② 前方一致でない LIKE
EXPLAIN SELECT id FROM d_ix_events WHERE title LIKE '%99999%'\G
type: index
key: idx_ix_title
rows: 299034
Extra: Using where; Using index
先頭にワイルドカードを置いた瞬間に終わりです。 索引は「先頭から順に並んでいる」ので、先頭が分からないと、どこから探せばいいか決められません。
前方一致なら効きます。
EXPLAIN SELECT id FROM d_ix_events WHERE title LIKE 'イベント99999%'\G
type: range
key: idx_ix_title
rows: 11
%キーワード%を索引で速くする方法はありません。 そこまで来たら、それは全文検索という別の道具の話です。本線の部分一致は索引が効かない — 全文検索とMeilisearchの回で扱っています。
③ 型が違う比較
code は VARCHAR です。 そこに数値を書きます。
EXPLAIN SELECT id FROM d_ix_events WHERE code = 12345\G
type: index
possible_keys: idx_ix_code
key: idx_ix_code
rows: 299034
Extra: Using where; Using index
possible_keys には入っているのに、rows が30万です。 そして続けてこれを打つと、理由が出ます。
SHOW WARNINGS;
+---------+------+-------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'idx_ix_code' due to type or |
| | | collation conversion on field 'code' |
+---------+------+-------------------------------------------------------------------+
「型の変換のせいで索引が使えない」と、そのまま書いてあります。
文字列と数値を比べるとき、MySQL は文字列のほうを数値に変換します。 つまり CAST(code AS DOUBLE) = 12345 を全行に対してやっているのと同じで、①と同じことが起きています。
しかも、結果は正しく返ります。 '00012345' を数値にすると 12345 なので、ちゃんと1行返る。 エラーも出ない。「動いているのに遅い」の典型です。
EXPLAIN SELECT id FROM d_ix_events WHERE code = '00012345'\G
type: ref
key: idx_ix_code
rows: 1
これはアプリから使うときに一番出ます。 JavaScript で
where('code', id)のidが数値のまま渡っている、というだけで起きます。SQL は正しく、結果も正しく、ただ読む行数が1行ではなく30万行になる。
④ OR
amount には索引を張っていません。 索引のある列と、無い列を OR で繋ぎます。
EXPLAIN SELECT id FROM d_ix_events WHERE user_id = 500 OR amount = 77\G
type: ALL
possible_keys: idx_ix_user_created
key: NULL
rows: 299034
Extra: Using where
type: ALL。 user_id の索引が使えるのに、使われません。
OR は「どちらか片方でも満たす行」なので、amount = 77 の行を漏れなく拾うには結局全部読むしかないからです。索引のある側だけ速くしても意味がありません。
片方に索引が無いだけで、全体が全表走査に落ちます。
OR を UNION に割ると効きます。
EXPLAIN SELECT id FROM d_ix_events WHERE user_id = 500
UNION
SELECT id FROM d_ix_events WHERE amount = 77\G
ただしこれは、下半分が結局 ALL です。 本当の直し方は amount にも索引を張るか、そもそもその OR が要るのかを疑うことです。
両方の列に索引があれば、MySQL は
index_mergeという手を使えることがあります(ExtraにUsing unionと出ます)。使えることがある、であって、いつもではありません。 ここでも判断するのはオプティマイザです。
⑤ NOT / <>
EXPLAIN SELECT id FROM d_ix_events WHERE code <> '00012345'\G
type: ALL
possible_keys: idx_ix_code
key: NULL
rows: 299034
Extra: Using where
「これ以外の全部」は、索引で絞れません。 30万行のうち299999行が該当するので、索引を経由するほうが遅いからです。
NOT IN も != も同じです。 これは⑥と同じ理由で、次に繋がります。
⑥ 選択率が低い(該当が多すぎる)
status は 0 か 1 の2種類しかありません。索引は張ってあります。
EXPLAIN SELECT id, amount FROM d_ix_events WHERE status = 1\G
type: ALL
possible_keys: idx_ix_status
key: NULL
rows: 299034
filtered: 50.00
Extra: Using where
索引はあるのに、使いません。
そして、これは正しい判断です。
30万行のうち15万行が該当します。索引を使うと、索引で15万件の場所を調べて、そのあと本体を15万回ランダムに読みに行くことになります。それより、頭から30万行を順に読むほうが速い。
「索引があるのに全表走査になった」は、しばしば正常です。 ディスクはバラバラの場所を1万回読むより、続いた場所を10万件読むほうが速い。 オプティマイザはこれを見積もって、わざと索引を捨てています。
同じ索引でも、絞れれば使われます。 status に片寄りを作って試します。
UPDATE d_ix_events SET status = 2 WHERE id <= 20;
EXPLAIN SELECT id, amount FROM d_ix_events WHERE status = 2\G
type: ref
key: idx_ix_status
rows: 20
索引も表も1文字も変えていません。値の偏りだけで、使う/使わないが変わりました。
索引が効くかどうかは、SQLだけでは決まりません。データの中身で決まります。 だから開発環境の10件で
EXPLAINを打っても、本番の判断は分かりません。 ここは何度も踏みました。
6つを1枚の表にする
| # | 書き方 | type | key | rows | 直し方 |
|---|---|---|---|---|---|
| ① | LEFT(code,4) = '0001' | index | idx_ix_code | 299034 | code LIKE '0001%'(列に関数を掛けない) |
| ② | title LIKE '%99999%' | index | idx_ix_title | 299034 | 前方一致にする。無理なら全文検索へ |
| ③ | code = 12345(数値) | index | idx_ix_code | 299034 | code = '00012345'(型を合わせる) |
| ④ | user_id = 500 OR amount = 77 | ALL | NULL | 299034 | 両方に索引 or UNION or 条件を見直す |
| ⑤ | code <> '00012345' | ALL | NULL | 299034 | 直せない。 該当が多すぎるので当然 |
| ⑥ | status = 1(半分が該当) | ALL | NULL | 299034 | 直さなくていい。 全表走査のほうが速い |
| ○ | user_id = 500(比較用) | ref | idx_ix_user_created | 300 | — |
[スクリーンショット: 03_索引が効かない6パターン.png] ①〜⑥のうち、type と key が読める EXPLAIN の結果を撮ってください。1枚に全部入らなくて構いません。 ③の SHOW WARNINGS; が写っていると、この記事で一番実用的な1枚になります。
①②③は「書き方の問題」なので直せます。④は設計の問題。⑤⑥は問題ですらありません。
この区別が、いちばん大事なところです。全部を ref にしようとしないでください。
カバリングインデックス — 表を読まずに済ませる
索引を使って行の場所が分かったあと、データベースはもう一仕事しています。 本体の表を読みに行って、SELECT に書いた列を取ってくる作業です。
取りたい列が全部索引の中に入っていれば、その一仕事が丸ごと消えます。
EXPLAIN SELECT user_id, created_at FROM d_ix_events WHERE user_id = 500\G
type: ref
key: idx_ix_user_created
rows: 300
Extra: Using index
Extra: Using index が出ました。
Using indexとUsing index conditionは別物です。 紛らわしいですが、Using index= 表を一度も読んでいない(これがカバリング)。Using index condition= 表は読むが、絞り込みを索引の段階で済ませた。
取りたい列を1つ増やすだけで消えます。
EXPLAIN SELECT user_id, created_at, title FROM d_ix_events WHERE user_id = 500\G
type: ref
key: idx_ix_user_created
rows: 300
Extra: NULL
title は idx_ix_user_created に入っていないので、300行ぶん本体を読みに行きます。
索引に title を足せば戻ります。
CREATE INDEX idx_ix_cover ON d_ix_events (user_id, created_at, title);
EXPLAIN SELECT user_id, created_at, title FROM d_ix_events WHERE user_id = 500\G
type: ref
key: idx_ix_cover
rows: 300
Extra: Using index
戻りました。
| 表を読むか | どんなときに効くか | |
|---|---|---|
| 普通の索引 | 読む(行の数だけ) | 返す行が少ないとき |
| カバリング | 読まない | 返す行が多いとき(1000行、1万行)に差が大きい |
SELECT *を書くと、カバリングは絶対に成立しません。 全列を索引に入れることはできないからです。 「速くしたいクエリでは列を絞る」のは、通信量の話ではなく、この話です。
ただし、これはやりすぎると害になります。
索引に列を足すと、索引そのものが太ります。 太った索引はディスクを食い、書き込みを遅くします。次の節がそれです。
EXPLAIN の読み方を、もう一段
本線の回では type と key と rows の3つを見ました。ここで全部の列を埋めます。
| 列 | 何を言っているか | どこを見るか |
|---|---|---|
type | どうやって行を見つけるつもりか。 最重要 | 下の表 |
possible_keys | 使えそうな索引の候補 | NULL なら、そもそも候補が無い=書き方の問題 |
key | 実際に選んだ索引 | possible_keys に名前があるのに key が NULL なら、「使えるけど使わないほうが速い」と判断された |
key_len | 索引の何バイト目まで使ったか | 複合索引が何列目まで効いたかが分かる |
ref | 索引と何を突き合わせたか | const なら定数、列名なら結合相手 |
rows | 読むつもりの行数(推定) | 実測ではなく見積もり。 桁が合っていれば十分 |
filtered | 読んだ行のうち、条件を通ると見込む割合(%) | rows × filtered が次の処理に渡る行数の見積もり |
Extra | その他の重要な事実 | ここに一番情報が多い |
type は、良い順に並んでいる
type | 意味 | 評価 |
|---|---|---|
const | 主キーやユニーク索引に定数を当てて、1行に確定 | 最良。 これ以上は無い |
eq_ref | 結合の相手側で、1行ずつ確定している | 最良に近い(JOIN で出る) |
ref | 索引で絞れている。複数行返る | 良い。ここを目指す |
range | 索引を使って範囲で絞っている | 良い。 BETWEEN > IN で出る |
index | 索引を頭から最後まで舐めている | 悪い。 ALL よりましなだけ |
ALL | 全表走査 | 要調査。 ただし⑥のように正しい場合もある |
indexとALLの見分けは大事です。 どちらも全部読んでいます。indexは「索引だけは読んでいる」ので、本体を読まないぶんマシというだけです。「索引の名前が出ているから効いている」は間違いです。
Extra によく出るもの
Extra | 意味 |
|---|---|
Using index | カバリング。表を読んでいない。 良い |
Using index condition | 絞り込みを索引の段階で行った。表は読む。悪くない |
Using where | 取り出したあとに条件で捨てている。ふつうに出るので、これ単体では判断材料にならない |
Using filesort | 並べ替えを別途やった。 件数が多いなら直す価値がある |
Using temporary | 一時表を作った。 GROUP BY などで出る。大きいと重い |
Impossible WHERE | 条件が絶対に成立しないので、実行すらしない(WHERE 1 = 0 など) |
EXPLAIN ANALYZE — 推定と実測を比べる
EXPLAIN は実行しません。「するつもり」を答えるだけです。 だから rows は見積もりで、外れることがあります。
MySQL 8.0.18 以降には、実際に実行して測る版があります。
EXPLAIN ANALYZE SELECT id, amount FROM d_ix_events WHERE user_id = 500\G
-> Index lookup on d_ix_events using idx_ix_user_created (user_id=500)
(cost=105 rows=300) (actual time=0.089..1.204 rows=300 loops=1)
読み方は1点だけです。
| 表示 | 意味 |
|---|---|
cost=105 rows=300 | 見積もり。 EXPLAIN の rows と同じもの |
actual time=0.089..1.204 rows=300 loops=1 | 実測。 最初の1行が返るまで0.089ミリ秒、全部返るまで1.204ミリ秒、実際に300行、1回実行した |
rows=300(推定)とactual … rows=300(実測)が近いか。ここだけ見ます。 推定300、実測15万だったら、統計情報が古いか、オプティマイザが条件を見誤っています。 変な実行計画を選ばれたときは、まずここのズレを疑います。
⑥のほうも見てみます。
EXPLAIN ANALYZE SELECT id, amount FROM d_ix_events WHERE status = 1\G
-> Filter: (d_ix_events.status = 1) (cost=30160 rows=149517)
(actual time=0.052..245.8 rows=149990 loops=1)
-> Table scan on d_ix_events (cost=30160 rows=299034)
(actual time=0.048..180.3 rows=300000 loops=1)
推定149517、実測149990。 ほぼ当たっています。「全部読む」は、この場合ちゃんと正しい判断でした。
EXPLAIN ANALYZEは本当に実行します。SELECTなら安全ですが、UPDATEやDELETEに付けると実際に書き換わります。 そこはEXPLAIN(実行しないほう)を使ってください。
索引のコスト — 張る前と後で測る
ここまでは索引の良い面だけを見てきました。代金を払う場面を、実測します。
別の表を1枚使います。
CREATE TABLE d_ix_load (
id INT NOT NULL,
user_id INT NOT NULL,
code VARCHAR(20) NOT NULL,
title VARCHAR(60) NOT NULL,
created_at DATETIME NOT NULL,
PRIMARY KEY (id)
);
索引なしで20万行入れて、時間を見ます。
SET SESSION cte_max_recursion_depth = 1000000;
INSERT INTO d_ix_load (id, user_id, code, title, created_at)
WITH RECURSIVE seq AS (
SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 200000
)
SELECT n, (n % 1000) + 1, LPAD(n, 8, '0'), CONCAT('イベント', n),
DATE_ADD('2024-01-01 00:00:00', INTERVAL n MINUTE)
FROM seq;
Query OK, 200000 rows affected (4.21 sec)
mysqlクライアントは、毎回この(4.21 sec)を出してくれます。 ストップウォッチは要りません。
サイズを見ます。
SELECT table_name,
ROUND(data_length / 1024 / 1024, 1) AS data_mb,
ROUND(index_length / 1024 / 1024, 1) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name = 'd_ix_load';
+------------+---------+----------+
| TABLE_NAME | data_mb | index_mb |
+------------+---------+----------+
| d_ix_load | 17.6 | 0.0 |
+------------+---------+----------+
index_mbが 0.0 なのは、主キーが「索引」として別勘定にならないからです。InnoDB では主キーが表の本体そのもので、data_lengthに含まれています。ここでいうindex_lengthは、あとから張った索引の合計です。
では、索引を4本張ってから、同じ量を入れ直します。
TRUNCATE TABLE d_ix_load;
CREATE INDEX idx_ld_user ON d_ix_load (user_id);
CREATE INDEX idx_ld_code ON d_ix_load (code);
CREATE INDEX idx_ld_title ON d_ix_load (title);
CREATE INDEX idx_ld_ct ON d_ix_load (created_at);
TRUNCATEは、表を空にする命令です。DELETEと違って1行ずつ消さず、作り直します。 速いかわりにWHEREは書けず、ROLLBACKもできません。 ここでは練習用の表なので使っています。
まったく同じ INSERT を打ちます。
INSERT INTO d_ix_load (id, user_id, code, title, created_at)
WITH RECURSIVE seq AS (
SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 200000
)
SELECT n, (n % 1000) + 1, LPAD(n, 8, '0'), CONCAT('イベント', n),
DATE_ADD('2024-01-01 00:00:00', INTERVAL n MINUTE)
FROM seq;
Query OK, 200000 rows affected (11.87 sec)
サイズも見ます。
SELECT table_name,
ROUND(data_length / 1024 / 1024, 1) AS data_mb,
ROUND(index_length / 1024 / 1024, 1) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'appdb' AND table_name = 'd_ix_load';
+------------+---------+----------+
| TABLE_NAME | data_mb | index_mb |
+------------+---------+----------+
| d_ix_load | 17.6 | 34.6 |
+------------+---------+----------+
並べます。
| 索引なし | 索引4本 | 倍率 | |
|---|---|---|---|
20万行の INSERT | 4.21 秒 | 11.87 秒 | 約2.8倍 |
| 本体のサイズ | 17.6 MB | 17.6 MB | 変わらない |
| 索引のサイズ | 0.0 MB | 34.6 MB | — |
| 合計 | 17.6 MB | 52.2 MB | 約3倍 |
索引のほうが本体より大きくなりました。 珍しいことではありません。 数字はマシンで変わります。倍率の桁を見てください。
[スクリーンショット: 04_索引ありなしのINSERT時間.png] 索引なしの INSERT と索引4本の INSERT の、Query OK, 200000 rows affected (… sec) の行が両方写るように撮ってください。この記事で唯一、索引の代金が数字で出ている場所です。
ここから出てくる結論は1つだけです。
索引は「とりあえず張っておく」ものではありません。 読む回数 × 速くなる量 が、書く回数 × 遅くなる量 を上回るときだけ張ります。 書き込みが多い表に索引を10本張ると、それは遅くするための作業です。
使われていない索引・重複した索引を見つける
張った索引は、放っておくと溜まります。 誰かが「念のため」張ったものが、5年後も代金を払い続けます。
使われていない索引
MySQL には、それを一覧にしてくれるビューがあります。
SELECT * FROM sys.schema_unused_indexes WHERE object_schema = 'appdb';
+---------------+-------------+----------------+
| object_schema | object_name | index_name |
+---------------+-------------+----------------+
| appdb | d_ix_load | idx_ld_title |
| appdb | d_ix_load | idx_ld_ct |
| appdb | d_ix_events | idx_ix_cover |
+---------------+-------------+----------------+
これは「一度も使われていない」ではなく、「MySQL を起動してから一度も使われていない」です。 月1回のバッチでしか使わない索引は、ここに出ます。 出たからといって、すぐ消してはいけません。 最低でも「一番長い周期の処理が1周する期間」動かしてから見てください。
何も返らなければ、それは「全部使われている」ではなく、sys スキーマや performance_schema が無効な可能性もあります。 そのときは次で確かめます。
SELECT @@performance_schema;
+----------------------+
| @@performance_schema |
+----------------------+
| 1 |
+----------------------+
1 なら有効です。
重複した索引
(user_id) と (user_id, created_at) の両方がある状態は、(user_id) が無駄です。
複合索引は左端から使えるので、(user_id, created_at) は WHERE user_id = ? にもそのまま使えます。 つまり (user_id) 単独の索引は何もできることが増えていないのに、代金だけ払っています。
確かめます。 d_ix_load に、いま (user_id) があります。ここに複合索引を足します。
CREATE INDEX idx_ld_user_ct ON d_ix_load (user_id, created_at);
この状態で、(user_id) 単独の索引を落としても、実行計画は変わりません。
EXPLAIN SELECT id FROM d_ix_load WHERE user_id = 500\G
type: ref
key: idx_ld_user
rows: 200
DROP INDEX idx_ld_user ON d_ix_load;
EXPLAIN SELECT id FROM d_ix_load WHERE user_id = 500\G
type: ref
key: idx_ld_user_ct
rows: 200
key の名前が変わっただけで、type も rows も同じです。 索引を1本減らして、性能は落ちませんでした。
見つけ方の規則は1行です。 ある索引の列並びが、別の索引の先頭部分と完全に一致するなら、短いほうは要りません。
(a)と(a, b)→(a)は不要。(a, b)と(b, a)→ どちらも必要かもしれない(別物です)。
sys には、これを探すビューもあります。
SELECT * FROM sys.schema_redundant_indexes WHERE table_schema = 'appdb';
統計情報 — 古いと変な計画になる
オプティマイザは、実際のデータを数えて判断しているわけではありません。 「この列にはだいたい何種類の値がある」という概算のメモを見ています。それが統計情報です。
さっき SHOW INDEX で見た Cardinality が、それです。
SELECT index_name, seq_in_index, column_name, cardinality
FROM information_schema.statistics
WHERE table_schema = 'appdb' AND table_name = 'd_ix_events'
ORDER BY index_name, seq_in_index;
+---------------------+--------------+-------------+-------------+
| INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | CARDINALITY |
+---------------------+--------------+-------------+-------------+
| PRIMARY | 1 | id | 299034 |
| idx_ix_code | 1 | code | 298187 |
| idx_ix_status | 1 | status | 2 |
| idx_ix_title | 1 | title | 292736 |
| idx_ix_user_created | 1 | user_id | 996 |
| idx_ix_user_created | 2 | created_at | 298187 |
+---------------------+--------------+-------------+-------------+
status が 2、user_id が 996。 実際は3種類と1000種類なので、ズレています。 これはサンプリングして推測しているからで、正常です。
問題は、これが大きくズレたときです。
- 大量に
INSERT/DELETEした直後 - データの偏りが変わったとき(さっき
status = 2を作ったような場合)
取り直す命令はこれです。
ANALYZE TABLE d_ix_events;
+--------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| appdb.d_ix_events | analyze | status | OK |
+--------------------+---------+----------+----------+
ANALYZE TABLEは統計を取り直すだけで、データも索引も作り直しません。 大きな表でも短時間で終わります。OPTIMIZE TABLEは別物です。 あちらは表を作り直すので、大きな表では長時間ロックがかかります。 混同しないでください。
普段は MySQL が自動で取り直しています。 手で打つのは、「実行計画が明らかにおかしいとき」の最初の一手としてです。それで直るなら、原因は統計情報でした。
ヒントは最後の手段
「使ってほしい索引を、名指しで指定する」書き方があります。
EXPLAIN SELECT id, amount FROM d_ix_events FORCE INDEX (idx_ix_status)
WHERE status = 1\G
type: ref
key: idx_ix_status
rows: 149517
さっき ALL を選んだクエリが、索引を使うようになりました。 実際に測ります。
SELECT COUNT(*) FROM d_ix_events WHERE status = 1;
1 row in set (0.06 sec)
SELECT COUNT(*) FROM d_ix_events FORCE INDEX (idx_ix_status) WHERE status = 1;
1 row in set (0.31 sec)
強制したほうが5倍遅くなりました。 オプティマイザの判断のほうが正しかった、ということです。
そのうえで、なぜ普段使わないのか。理由は3つです。
| 理由 | 中身 |
|---|---|
| データが変われば正解も変わる | いま正しくても、半年後には間違いです。 オプティマイザは毎回計算し直しますが、FORCE INDEX は固定されます |
| 索引名を消せなくなる | ヒントに書いた索引を誰かが消すと、そのクエリはエラーで落ちます。 SQLと索引名が固く結びつきます |
| 本当の原因が隠れる | 索引が使われないのには理由があります。 ⑥のように「使わないほうが速い」か、①③のように「書き方が悪い」か。ヒントはどちらの場合も、調べるのをやめさせます |
FORCE INDEXを書きたくなったら、順番はこうです。 1.EXPLAIN ANALYZEで推定と実測がズレていないか見る 2. ズレていたらANALYZE TABLEで統計を取り直す 3. それでも直らず、かつ実測でヒントのほうが速いと確認できたときだけ書く 4. 書いたら、なぜそうしたかをコメントで残す
私が実際に FORCE INDEX を残したことは、まだ一度もありません。 2番で直りました。
つまずいたところ
遠回りした順に書きます。
1. 索引を3本張って、1ミリ秒も変わらなかった
冒頭に書いた話です。WHERE に出てくる列に片っ端から索引を張りました。 実際に遅かったのは LIKE '%…%' の行で、索引では絶対に直らないほうでした。
EXPLAIN を先に打っていれば、張る前に分かっていました。 張るのは30秒、EXPLAIN は1秒です。順番が逆でした。
2. possible_keys に名前があるのを「効いている」と読んだ
possible_keys は候補で、key が実際に使ったものです。 候補に名前があって key が NULL なのは、「使えるけど、使わないほうが速いと判断した」という意味でした。この2列を並べて読むまで、半年かかりました。
3. type: index を「索引を使っているから良い」と読んだ
これが一番長く間違えていました。 index は索引を頭から最後まで舐めているという意味で、ALL と大差ありません。rows を見ればすぐ分かったのに、type の文字列だけ見ていました。
type は文字を読むのではなく、const / eq_ref / ref / range が良くて index / ALL は悪い、と覚え直しました。
4. アプリからだけ遅くて、mysql から打つと速かった
同じSQLのはずなのに、Node.js から呼ぶと10倍遅い。 原因は③でした。code に数値をそのまま渡していただけです。手で打つときは自然と引用符を付けるので、再現しませんでした。
アプリのログにSQLを出して、値の型まで見るまで気づけませんでした。 いまはEXPLAIN はアプリが投げる形そのままで打っています。
5. status に索引を張って、使われないのを不具合だと思った
「索引を張ったのに ALL のまま」を、MySQL のバグだと思って調べ回りました。 半分が該当する条件では、全表走査のほうが速い。正しい動作でした。
索引が効かない場面には「直せるもの」と「直す必要がないもの」がある、と分かってからは、時間の使い方が変わりました。
6. 練習中に「使われていない索引」を消して、月次バッチを落とした
sys.schema_unused_indexes に出た索引を、その日のうちに消しました。 それは月末のバッチだけが使う索引で、MySQL を再起動してから2日しか経っていませんでした。
「使われていない」は「起動してから使われていない」です。 ここに書いてあるとおりでした。読んでいたのに、急いでいて飛ばしました。
7. 索引を張りすぎて INSERT が遅くなり、原因が分からなくなった
遅いのは SELECT だと思い込んでいたので、INSERT を疑うのに時間がかかりました。 索引は SELECT を速くするもの、という覚え方だと、代金を払っている場所が視界に入りません。
この記事で INSERT の時間を測ったのは、そのためです。
後片づけ
練習用の表を全部消します。 d_ix_ で始まるものだけです。
まず、何があるか見ます。
SHOW TABLES LIKE 'd\_ix\_%';
_はLIKEでは「任意の1文字」という意味を持つので、そのまま書くとdXixのようなものにも当たります。\_と書いて、文字としての下線を指定しています。
+---------------------------+
| Tables_in_appdb (d_ix_%) |
+---------------------------+
| d_ix_events |
| d_ix_load |
+---------------------------+
消します。索引は表と一緒に消えるので、DROP INDEX を個別に打つ必要はありません。
DROP TABLE IF EXISTS d_ix_events;
DROP TABLE IF EXISTS d_ix_load;
消えたことと、他の表が残っていることを確認します。
SHOW TABLES;
+-----------------+
| Tables_in_appdb |
+-----------------+
| knex_migrations |
| tasks |
+-----------------+
d_ix_ で始まる表が1つも無ければ成功です。 表示される中身は、あなたがここまでに作ったものによって変わります。d_ix_ が消えていることだけ見てください。
MySQL を抜けます。
EXIT;
cte_max_recursion_depth は元に戻ります。 SET SESSION は接続中だけの設定なので、抜けた時点で既定値です。設定ファイルは触っていません。
もし途中で appdb 自体をこの記事のために新規作成したなら、それも消して構いません。本線で使っている場合は消さないでください。
-- 本線で appdb を使っているなら、これは打たないこと
DROP DATABASE appdb;
まとめ
この記事で理解できたことを、6行にします。
- 索引は「張る」と「使われる」が別。 張るのは自分、使うか決めるのはオプティマイザ。確かめる方法は
EXPLAINひとつしかない - 複合索引は左端から使う。
(a, b)はaとa AND bに効き、b単独にはまったく効かない。 どこまで効いたかはkey_lenで分かる - 索引は
ORDER BYにも効く。 効いたときはUsing filesortが消える。 効くのはLIMITを付けたときに桁で出る - 索引が使われない書き方は6つ。 ①列に関数 ②前方一致でない
LIKE③型違い ④OR⑤NOT⑥該当が多すぎる。
①②③は直せる。④は設計の話。⑤⑥は直す必要がない
- カバリング(
Using index)は表を読まない。 ただし列を足すほど索引は太る。INSERTは2.8倍遅くなり、サイズは3倍になった(実測) FORCE INDEXは最後の手段。 先にEXPLAIN ANALYZEで推定と実測を比べ、ズレていたらANALYZE TABLE。たいていそこで直る
この6つは、全部1つのことの言い換えです。
索引は、絞り込めるときにだけ意味がある。
そこに戻れば、「索引を張ったのに速くならない」は、もう不思議ではなくなります。 絞り込めていないか、絞り込めない書き方をしているか、絞り込む必要がないか。3つのどれかです。
参考リンク
- roadmap.sh — SQL
- roadmap.sh — Backend
- MySQL 公式ドキュメント
- PostgreSQL — EXPLAIN の使い方
- PostgreSQL — インデックス
- SQLite — クエリプランナの解説
- Use The Index, Luke — 索引の解説サイト
次に読む
この記事は 【深掘り】SQL の 14 / 17 本目です。
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- 複合索引の列順と左端プレフィックス
- 索引が効かない書き方 / カバリング / 統計情報
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント