インデックスとEXPLAIN — 張ったのに使われない

インデックスとEXPLAIN。見出し付きのファイル棚と検索カード、実行計画の書類。

本線の回で、索引を張ってクエリが速くなるところは見ました。あれは、うまくいった例です。

これは深掘りです。本線の順路ではありません。 読まなくても先へは進めます。 本線の速くする前に必ず測る — パフォーマンス改善と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 は最後の手段であり、普段使うと将来のほうが遅くなること

この記事でやること

  1. 練習用のテーブルを作り、WITH RECURSIVE で30万行を入れる(既存のテーブルには触らない)
  2. 複合索引 (user_id, created_at) を1本だけ張り、WHERE の書き方を3通り変えて EXPLAIN を読む
  3. 索引が効かなくなる書き方を6つ順番に打ち、type と rows を1枚の表にまとめる
  4. カバリングインデックスで Using index を出す。EXPLAIN ANALYZE で推定と実測を比べる
  5. 索引を張る前と後で INSERT の所要時間とディスク使用量を測る
  6. 使われていない索引・重複した索引を探し、統計情報を取り直して、最後に全部消す

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
索引 / インデックス表とは別に持っている、探すための並べ替え済みデータ。 本の巻末の索引と同じで、本体とは別に場所を食う
複合索引2列以上をまとめて1本にした索引。 (user_id, created_at) のように順番付きで持つ。順番に意味がある
実行計画データベースが「このSQLをこう実行するつもりだ」と決めた手順。EXPLAIN で見られる
オプティマイザ実行計画を選ぶ担当。 索引を使うか全部読むかを、統計情報を見て自分で決める。 人が書いた順番どおりには動かない
全表走査 / フルスキャン表の全行を上から読むこと。EXPLAIN では type: ALL と出る
選択率(カーディナリティ)その条件で全体の何割まで絞れるか。 30万行から3行なら高い、15万行残るなら低い。低いと索引は使われない
カバリングインデックス必要な列が全部その索引に入っている状態。表の本体を一度も読まずに済む。 Extra に Using index と出る
filesort並べ替えを、その場でやったという印。 索引の順番をそのまま使えたときは出ない。「ファイルに書いた」という意味ではない
統計情報「この列にはだいたい何種類の値があるか」という概算のメモ。 これを見てオプティマイザが判断する。古いと判断を誤る

練習用のデータを作る

この記事は他の回のデータに依存しません。 ここに書いてある CREATE と INSERT を打てば、そのまま最後まで進めます。

VMの中で MySQL に入ります。

bash
$ sudo 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_id1000種類。 30万行なので1人あたり300行。よく絞れる列の代表
status0か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つを並べる

書き方keykey_lentyperows
WHERE user_id = 500idx_ix_user_created4ref300
WHERE user_id = 500 AND created_at >= …idx_ix_user_created9range82
WHERE created_at >= …NULLNULLALL299034

これが「左端プレフィックス」です。 複合索引は、左の列から順に使う。 途中から使うことはできない。 (a, b) の索引は a の検索と a AND b の検索には効き、b 単独には効きません。

[スクリーンショット: 02_複合索引の左端.png] 上の3つの EXPLAIN を続けて打った画面を撮ってください。3つ目だけ type: ALL になっているところが見えると、この記事で一番はっきりした対比です。

「じゃあ (created_at, user_id) にすればいいのでは」

両方は成立しません。 順番を逆にすれば、今度は WHERE user_id = 500 単独が効かなくなります。

決め方はこうです。

  1. = で絞る列を左に、範囲(> < BETWEEN)で絞る列を右に置く
  2. どちらも = なら、よく絞れるほう(種類が多いほう)を左に
  3. 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枚の表にする

#書き方typekeyrows直し方
①LEFT(code,4) = '0001'indexidx_ix_code299034code LIKE '0001%'(列に関数を掛けない)
②title LIKE '%99999%'indexidx_ix_title299034前方一致にする。無理なら全文検索へ
③code = 12345(数値)indexidx_ix_code299034code = '00012345'(型を合わせる)
④user_id = 500 OR amount = 77ALLNULL299034両方に索引 or UNION or 条件を見直す
⑤code <> '00012345'ALLNULL299034直せない。 該当が多すぎるので当然
⑥status = 1(半分が該当)ALLNULL299034直さなくていい。 全表走査のほうが速い
○user_id = 500(比較用)refidx_ix_user_created300—

[スクリーンショット: 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万行の INSERT4.21 秒11.87 秒約2.8倍
本体のサイズ17.6 MB17.6 MB変わらない
索引のサイズ0.0 MB34.6 MB—
合計17.6 MB52.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行にします。

  1. 索引は「張る」と「使われる」が別。 張るのは自分、使うか決めるのはオプティマイザ。確かめる方法は EXPLAIN ひとつしかない
  2. 複合索引は左端から使う。 (a, b) は a と a AND b に効き、b 単独にはまったく効かない。 どこまで効いたかは key_len で分かる
  3. 索引は ORDER BY にも効く。 効いたときは Using filesort が消える。 効くのは LIMIT を付けたときに桁で出る
  4. 索引が使われない書き方は6つ。 ①列に関数 ②前方一致でない LIKE ③型違い ④OR ⑤NOT ⑥該当が多すぎる。

①②③は直せる。④は設計の話。⑤⑥は直す必要がない

  1. カバリング(Using index)は表を読まない。 ただし列を足すほど索引は太る。INSERT は2.8倍遅くなり、サイズは3倍になった(実測)
  2. FORCE INDEX は最後の手段。 先に EXPLAIN ANALYZE で推定と実測を比べ、ズレていたら ANALYZE TABLE。たいていそこで直る

この6つは、全部1つのことの言い換えです。

索引は、絞り込めるときにだけ意味がある。

そこに戻れば、「索引を張ったのに速くならない」は、もう不思議ではなくなります。 絞り込めていないか、絞り込めない書き方をしているか、絞り込む必要がないか。3つのどれかです。

参考リンク


次に読む

この記事は 【深掘り】SQL の 14 / 17 本目です。

記事
← 前の記事トランザクション分離レベル — 同じSELECTが2回目で変わる
→ 次の記事スロークエリの特定と書き換え — 遅いのはどのSQLか

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›SQL — Indexes
  • 複合索引の列順と左端プレフィックス
  • 索引が効かない書き方 / カバリング / 統計情報

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次