MySQL 10 min read

MySQLのインデックス作成と確認 ― CREATE INDEXとSHOW INDEX、作ったのに使われないとき

※この記事にはアフィリエイト広告(プロモーション)が含まれます。

この記事の出力は MySQL 8.4.11(Ubuntu 26.04)で採りました。表 t は 5万行です。

まず見る(結論)

CREATE INDEX idx_cust ON t (cust_id);        -- 作成
SHOW INDEX FROM t;                           -- 確認
EXPLAIN SELECT * FROM t WHERE cust_id = 123; -- 使われたか(key 列)
DROP INDEX idx_cust ON t;                    -- 削除

ALTER TABLE t ADD INDEX idx_cust (cust_id); も同じ操作です。

作成して使われたかを見る

EXPLAIN SELECT * FROM t WHERE cust_id = 123;   -- 作成前
| type | possible_keys | key  | rows  | Extra       |
| ALL  | NULL          | NULL | 48941 | Using where |

CREATE INDEX idx_cust ON t (cust_id);

EXPLAIN SELECT * FROM t WHERE cust_id = 123;   -- 作成後
| type | possible_keys | key      | rows | Extra |
| ref  | idx_cust      | idx_cust |   10 | NULL  |

(EXPLAIN の出力は列を抜粋)type が ALL は全行読み、key に名前が出ていればそのインデックスを使っています。possible_keys に出ていても key が NULL なら、候補にはしたが使わなかったという意味です。各列の読み方は EXPLAIN の記事にあります。

一覧を確認する

SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE, CARDINALITY, IS_VISIBLE
  FROM information_schema.STATISTICS
 WHERE TABLE_SCHEMA = 'fsv' AND TABLE_NAME = 't'
 ORDER BY INDEX_NAME, SEQ_IN_INDEX;

+------------+--------------+-------------+------------+-------------+------------+
| INDEX_NAME | SEQ_IN_INDEX | COLUMN_NAME | NON_UNIQUE | CARDINALITY | IS_VISIBLE |
+------------+--------------+-------------+------------+-------------+------------+
| idx_cust   |            1 | cust_id     |          1 |        4995 | YES        |
| PRIMARY    |            1 | id          |          0 |       48941 | YES        |
+------------+--------------+-------------+------------+-------------+------------+

SHOW INDEX FROM t でも同じ内容が出ます(列が多いので \G を付けると読みやすい)。複合インデックスは1列ごとに1行で、SEQ_IN_INDEX が列の順番です。CARDINALITY は値の種類数の見積もりです。

複合インデックスは列の順番で効き方が変わる

(status, created) の順で作ったインデックスです。

WHERE key type
status = 1 AND created >= '2026-02-04' idx_st_cr range(347行)
status = 1 idx_st_cr ref
created >= '2026-02-04' NULL(possible_keys も NULL) ALL

先頭の列 status を条件に含まないと使われません。created だけで検索するなら、created が先頭のインデックスを別に作ります。

status = 1 AND created >= '2026-01-10' のように該当行が全体の半分近くになる条件では、possible_keys に出ていても key は NULL で全行読みになりました。全行読みのほうが速いとオプティマイザーが判断した結果で、作り方の問題ではありません。

作ったのに使われない書き方

WHERE key 書き直し
code = 123(code は VARCHAR) NULL code = '00000123' と文字列で渡す → idx_code
DATE(created) = '2026-01-15' NULL created >= '2026-01-15' AND created < '2026-01-16' → idx_created
code LIKE '%0123' NULL 前方一致 LIKE '0000012%' なら idx_code

文字列の列を数値と比べると、行ごとに数値へ変換して比べるのでインデックスを使えません。アプリから数値型のまま値を渡している場合に起きます。

列に関数をかけたまま検索したいなら、8.0.13 以降は式に対するインデックスを作れます。式を二重の括弧で囲みます。

CREATE INDEX idx_dcreated ON t ((DATE(created)));   -- 通る。DATE(created) = ... で使われた
CREATE INDEX idx_dcreated2 ON t (DATE(created));    -- ERROR 1064

作成時のエラー

エラー 原因 対処
ERROR 1061 Duplicate key name 'idx_cust' 同じ名前のインデックスがある 名前を変える。中身が同じなら作らない
ERROR 1170 BLOB/TEXT column 'memo' used in key specification without a key length TEXT 列に長さを指定していない (memo(20)) のように先頭の文字数を指定
ERROR 1071 Specified key was too long; max key length is 3072 bytes VARCHAR(1000) の utf8mb4 は最大 4000 バイト (note(768))(768×4=3072 バイト)で通った
ERROR 1062 Duplicate entry '1' for key 't.ux_cust' UNIQUE インデックスを、重複のある列に作った 重複を先に片付ける(ERROR 1062 の記事)

名前を変えて同じ列にもう1つ作ると、エラーにはならず警告だけが出ます。

Warning | 1831 | Duplicate index 'idx_b' defined on the table 'fsv.t'. This is deprecated and will be disallowed in a future release.

重複したインデックスは更新のたびに両方が書き換わるだけなので、SHOW INDEX で見つけたら片方を消します。

削除する前に試す(不可視インデックス)

ALTER TABLE t ALTER INDEX idx_cust INVISIBLE;
ALTER TABLE t ALTER INDEX idx_cust VISIBLE;

INVISIBLE にすると、インデックスは残したままオプティマイザーが使わなくなり、EXPLAIN の key は NULL になりました。主キーは ERROR 3522 A primary key index cannot be invisible で不可視にできません。

大きな表にインデックスを作ると時間がかかります。作成中に更新が止まらないかは ALTER TABLE の記事のメタデータロックの節を見てください。

関連: MySQLのEXPLAINの見方 / MySQLのスロークエリログを有効にする / MySQLのALTER TABLE / PostgreSQLのインデックス作成

MySQL をもっと深く知るための本

MySQL徹底入門 第5版 MySQL 8.4 LTS対応

翔泳社・2025年6月・MySQL 8.4 LTS 対応

MySQL 8.4 に対応した入門書。日本MySQLユーザ会のメンバーが執筆している。

Amazonで見る

MySQL運用・管理[実践]入門 〜安全かつ高速にデータを扱う内部構造・動作原理を学ぶ

技術評論社・2024年5月

内部構造と動作原理から、運用・管理の考え方を学ぶ。

Amazonで見る

Amazonのアソシエイトとして、FIRE SE は適格販売により収入を得ています。