100万行のテーブルを用意して、数秒かかるクエリをミリ秒にします。速くなったことを、体感ではなく数字で示せるところまでやります。
前回: ALTER TABLE を手で打たない — ORMとマイグレーション
これは SQL コースの「到達作品」の回です。 新しい知識はほとんど出てきません。これまでの MySQLとSQLの基本 / テーブル設計と正規化 と、深掘りで見た 索引 / チューニング を、1つの「数字で示せる成果」に束ねる回です。
この記事のコマンドは、VMの中(Ubuntu Server)で打ちます。 MySQL 8.0 以降(できれば 8.4 LTS)が動いていれば、それ以外の準備は要りません。練習用のデータベースはこの記事の中で作って、最後に全部消します。既存のデータベースには一切触りません。 100万行を入れるので、VMのディスクに 1GB 程度の空きがあることだけ先に確かめてください。
対象レベル: 初級〜中級 / 区分: 必修
めがける作品
このコースを終えたら、これが言い切れるようになっている。 ——「索引を張ると速くなる」ではなく、「このクエリは、索引を張ったら 0.6 秒から 0.002 秒になった。理由は EXPLAIN で見た」。ここで作るのは、その一枚の証拠です。
作るのは、派手なアプリではありません。100万行のテーブル1つと、Before/After の数字が並んだ表1枚です。地味に見えますが、SQL コースで学んだことが、ここに全部乗ります。
素材にするのは、どのサイトにもある「あるある」な遅いクエリ3つです。
- 特定ユーザーの投稿を出す(
WHERE user_id = ?)——一覧やプロフィール画面 - 投稿に投稿者名を添える(
JOIN users)——正規化したら必ず出てくる形 - 新着20件を出す(
ORDER BY created_at DESC LIMIT 20)——トップページの定番
この3つは、索引が無いと3つとも数百ミリ秒かかります。 それを測り、索引を張り、また測って、「数百ミリ秒 → 数ミリ秒」を数字で並べる——それがこの記事の成果物です。
- テーブルを正規化して設計する(users と posts を分ける)
- 大量のデータを手で入れず、SQLで生成する(
INSERT ... SELECT) - 遅いクエリを、感覚ではなく実行時間で測る
EXPLAINで「なぜ遅いか」を読む(全表走査=type=ALL)- 索引を張って、同じクエリがどれだけ速くなるかを測る
- 索引が効かない書き方を知り、書き換えで直す
- 索引の代償(書き込みが遅くなる・容量が増える)まで数字で言える
完成すると、こう言えます。
同じクエリ「WHERE user_id = 12345」で、こう変わった:
実行時間 EXPLAIN の rows type
索引なし 0.62 sec 997520 行を走査 ALL(全表走査)
索引あり 0.002 sec 20 行だけ読む ref(索引で一本釣り)
↑ 300倍以上速い ↑ 5万分の1 ↑ ここが本質
「速くなった気がする」ではなく、この表を自分の手で埋めること。 それがこの記事のゴールです。そして、この作業の順番——「先に測る → 索引を張る → また測る」——が、この先ずっと使う型になります。
この記事でわかること
- 索引が「速い」の正体は、
type=ALL(全部見る)がtype=ref(索引で一本釣り)に変わることだということ。速さは結果で、本質は読む行数が減ること - 「速くする前に必ず測る」の意味。測らずに索引を張ると、効いたのか偶然かが分からないこと
EXPLAINのrowsとtypeの2つだけ見れば、遅い理由の9割は分かること- 索引は「張れば効く」ではないこと。前方一致以外の
LIKE、列を関数で包む、複合索引の順序ミスで、張っても使われないこと - 索引はタダではないこと。書き込みが遅くなり、容量が増える。だから張りすぎてはいけないこと
- 100万行を、手で入れずにSQLだけで生成する方法
この記事でやること
- 練習用のデータベースと、正規化した2テーブル(users / posts)を作る
INSERT ... SELECTで 100万行を生成する(手で入れない)。件数を確認する- 索引を張る前に、遅いクエリを実際に流して実行時間と
EXPLAINを記録する CREATE INDEXを張り、まったく同じクエリをもう一度測る。Before/After を1枚の表にする- 索引が効かない書き方を3つ再現し、書き換えで直す
- 索引を張ると
INSERTが何倍遅くなるか、容量がどれだけ増えるかを測る - 完成の判定(チェックリスト)を自分で通す。練習用データベースを消す
先に知る言葉
この記事で軸になる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| 全表走査(フルスキャン) | テーブルの全行を先頭から最後まで読むこと。EXPLAIN の type が ALL になる。行数が増えるほど遅い |
| 索引(インデックス) | 列の値を並べて別に持っておく仕組み。本の巻末索引と同じで、全ページめくらずに目的の行へ跳べる |
EXPLAIN | クエリを実行せずに、MySQL が「どう処理するつもりか」を出させる命令。実行計画とも呼ぶ |
type | EXPLAIN の列。どう行を絞るか。ALL(全表走査)が最悪、ref / range は索引が効いている |
rows | EXPLAIN の列。MySQL が読むつもりの行数の見積り。ここが小さいほど速い |
type=ref | 等価(=)で索引を引いて、一致する行だけ読む状態。今回のゴール |
type=range | 範囲(> < BETWEEN)で索引を使う状態。これも効いている側 |
| 複合索引 | 複数列をまとめて張った索引。(a, b) の形。左端の列から順にしか使えないという規則がある |
| 正規化 | 同じ情報を1か所にだけ置くようテーブルを分ける設計。ここでは投稿者を users に分ける |
| バッファプール | MySQL がよく使うデータをメモリに載せておく領域。2回目が速いのはこれのせいのことがある(測り方の注意) |
① 題材とデータベースを用意する
まず、既存のデータに触らないよう、専用のデータベースを1つ作ります。
MySQL に入ります。素の Ubuntu に apt で入れた MySQL 8.0 なら、root は OS のユーザーで入れます(MySQLとSQLの基本の回で入れたものです)。
うまく入れないとき(root にパスワードを設定してある場合)は、こちらです。
mysql> というプロンプトが出れば、ここから先はSQLです。以降の sql のブロックは、この mysql> の中で打ちます。 VMのシェル($)ではありません。
練習用データベース bench を作り、そこに移動します。
CREATE DATABASE bench;
USE bench;
bench という名前は、この記事だけの使い捨てです。 最後に丸ごと消します。既存のデータベース(アプリで使っているもの)とは別なので、何をしても本番は無事です。
テーブルを正規化して2つ作る
題材は「ユーザーと、その投稿」にします。ブログでも、SNSでも、掲示板でも、この形が土台です。
ここで 正規化 を思い出します。 投稿のたびに「投稿者の名前」を毎回書くと、名前を変えたとき全投稿を直す羽目になります。だからユーザーは users に1回だけ書き、投稿は user_id で参照する——これが正規化です。
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
email VARCHAR(120) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE posts (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
title VARCHAR(120) NOT NULL,
body TEXT NOT NULL,
status TINYINT NOT NULL DEFAULT 1,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
確認します。テーブルが2つできていれば成功です。
SHOW TABLES;
+-----------------+
| Tables_in_bench |
+-----------------+
| posts |
| users |
+-----------------+
2 rows in set (0.00 sec)
いま posts に索引はありません。 正確には、PRIMARY KEY の id にだけ自動で索引が付いています。それを目で確かめます。
SHOW INDEX FROM posts;
+-------+------------+----------+--------------+-------------+-----------+-------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
+-------+------------+----------+--------------+-------------+-----------+-------------+
| posts | 0 | PRIMARY | 1 | id | A | 0 |
+-------+------------+----------+--------------+-------------+-----------+-------------+
1 row in set (0.00 sec)
索引は PRIMARY(id)の1本だけ。 user_id にも title にも created_at にも、まだ何も張っていません。——ここが大事です。張っていない状態で遅さを測ってから、あとで張ります。
補足:
FOREIGN KEYはあえて張っていません。posts.user_idはusers.idを指しますが、この記事は索引の効果を測るのが目的なので、外部キー制約(と、それが自動で作る索引)は付けませんでした。本番のアプリでは、正規化した参照には外部キーを張るのが定石です(テーブル設計と正規化)。ここでは「索引ゼロの状態」を作りたいので、あえて外しています。
② 100万行を生成する — 手で入れない
100万行を手で INSERT するのは不可能です。 SQL自身に作らせます。
MySQL 8.0 には 再帰CTE(WITH RECURSIVE)という、連番を作る仕組みがあります。これで「1 から 100万までの数」を作り、その数を使って行を組み立てます。
まず、再帰の上限を今回のセッションだけ引き上げます(既定は 1000 なので 100万は作れません)。
SET SESSION cte_max_recursion_depth = 1000000;
SESSION なので、この接続を切れば元に戻ります。 サーバー全体の設定は変えていません。
なぜ WITH RECURSIVE なのか。 「1, 2, 3, … と数を作る」ためです。その連番 n を使えば、CONCAT('user', n) のように1行ずつ違うデータを組み立てられます。100万回の再帰はメモリを使うので、メモリの小さいVMだと Out of memory で止まることがあります。そのときは、100万を一度に作らず20万を5回に分けて入れる(WHERE n < 200000 にして5回流す)と確実です。ストアドプロシージャのループでも書けますが、SQL1本で済む再帰CTEが一番手軽です。
まず users を5万人ぶん
INSERT INTO users (name, email)
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 50000
)
SELECT CONCAT('user', n), CONCAT('user', n, '@example.com')
FROM seq;
Query OK, 50000 rows affected (0.45 sec)
Records: 50000 Duplicates: 0 Warnings: 0
次に posts を100万件
各投稿の user_id は、1〜50000 のランダムにします。こうすると「1人あたり平均 20 投稿」になり、あとで「特定ユーザーの投稿」を引くのが自然な題材になります。created_at も、2025年1月1日からの2年間(730日)にランダムに散らします。 指定しないと DEFAULT CURRENT_TIMESTAMP で全行が「実行した瞬間」の同じ時刻になり、あとの範囲検索や新着順の題材になりません。2年間に100万件なので、1日あたり約1370件です。
INSERT INTO posts (user_id, title, body, status, created_at)
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 1000000
)
SELECT
FLOOR(1 + RAND() * 50000),
CONCAT('投稿タイトル ', n),
CONCAT('本文サンプル ', n, ' ', REPEAT('x', 60)),
IF(RAND() < 0.2, 0, 1),
TIMESTAMP '2025-01-01 00:00:00' + INTERVAL FLOOR(RAND() * 730 * 86400) SECOND
FROM seq;
これは数十秒かかります。 私の VM(メモリ4GB)では 28秒でした。100万行を作って書き込んでいるので、一瞬では終わりません。待ってください。
Query OK, 1000000 rows affected (28.31 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
件数を必ず確認する
「入れたつもり」で先に進むと、あとで数字が合わなくて混乱します。 数えます。
SELECT COUNT(*) FROM posts;
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.34 sec)
この COUNT(*) 自体が 0.34 秒かかっていることにも注目してください。索引の無い100万行を数えるだけで、これだけかかるのです。「たかが件数」でも、行数が大きいと無視できません。
中身も少しだけ見ておく
件数だけでなく、生成した中身が思ったとおりかを軽く確かめます。まず1行、実物を見ます。
SELECT * FROM posts LIMIT 1;
+----+---------+------------------+---------------------------+--------+---------------------+
| id | user_id | title | body | status | created_at |
+----+---------+------------------+---------------------------+--------+---------------------+
| 1 | 38421 | 投稿タイトル 1 | 本文サンプル 1 xxxxx... | 1 | 2026-09-05 12:00:00 |
+----+---------+------------------+---------------------------+--------+---------------------+
次に、「1人あたり平均で何投稿になったか」を確かめます。100万件を5万人に配ったので、平均20のはずです。
SELECT COUNT(*) / COUNT(DISTINCT user_id) AS avg_posts_per_user FROM posts;
+--------------------+
| avg_posts_per_user |
+--------------------+
| 20.0000 |
+--------------------+
狙いどおり「1人平均20投稿」になっています。だから、あとで WHERE user_id = 12345 を引くと 20 行前後が返る——この「20」が、Before/After の表の rows に効いてきます。 練習用データは、本番に近い「散らばり方」で作るのが大事です。全員が同じ user_id だと、索引を張っても絞り込めず、効果が出ません。
最後に、status(公開=1 / 非公開=0)の散らばりも見ておきます。生成時に「2割を非公開」にしました。
SELECT status, COUNT(*) FROM posts GROUP BY status;
+--------+----------+
| status | COUNT(*) |
+--------+----------+
| 0 | 199640 |
| 1 | 800360 |
+--------+----------+
公開が約8割、非公開が約2割。狙いどおりです。この「8対2」が、⑤の複合索引の話で効いてきます。 status は値が2種類しかなく、しかも8割が同じ値なので、この列だけでは絞り込めない——それを、あとで実際に確かめます。
スクリーンショットを撮ってください:
01_行数確認.pngSELECT COUNT(*) FROM posts;が1000000を返している画面を撮ってください。これがこの記事の出発点(100万行そろった)の証拠です。
③ 遅いクエリを作って、先に測る
ここが、この記事でいちばん大事な節です。 前回のパフォーマンスの回で繰り返した約束を、もう一度守ります。
索引を張る前に、遅い状態を測る。 測らずに索引を張ると、速くなっても「索引が効いたのか、たまたまか」が永久に分かりません。
測るとき、3つだけ約束します。 これを外すと、数字が嘘をつきます。
- 同じクエリを、索引の前と後で1文字も変えない。 変えると「索引のおかげか、書き換えのおかげか」が混ざる
- 実行時間だけで判断しない。 かならず
EXPLAINのtypeとrowsも見る。実行時間はバッファプールに左右されるが、typeとrowsは作戦そのものを表す - 前と後の数字を、その場でメモする。 「たぶん速くなった」ではなく、
0.61 sec → 0.00 secと並べて書ける状態にする
この3つが、この記事を「なんとなく」で終わらせないための骨です。
遅いクエリ:索引の無い列で検索する
「user_id が 12345 の投稿を全部出す」——アプリで一番よくある形です。
SELECT * FROM posts WHERE user_id = 12345;
20 rows in set (0.61 sec)
返ってきたのはたった 20 行なのに、0.61 秒かかりました。 これは異常です。20 行を出すために、MySQL は100万行を全部見ているからです。それを次で証明します。
なぜ遅いのか — EXPLAIN で見る
実行時間だけでは「遅い」しか分かりません。 EXPLAIN を頭に付けると、実行せずに MySQL の作戦が見えます。
EXPLAIN SELECT * FROM posts WHERE user_id = 12345;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 997520 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)
見るのは2つだけです。
typeがALL——これが全表走査。「絞り込む手段が無いので、全部読みます」という宣言です。最悪の値ですrowsが997520——「約100万行を読むつもりだ」という見積り。possible_keysがNULL(=使える索引が無い)なので、こうなります
rows は実測ではなく見積りなので、実際の100万とはわずかにずれます。それでいいのです。桁が合っていれば十分——「数百万行を読もうとしている」と読めれば、原因は確定です。
記録する
この2つを、あとで比べるために書き留めます。
クエリ: WHERE user_id = 12345
実行時間 : 0.61 sec
type : ALL(全表走査)
rows : 997520
スクリーンショットを撮ってください:
02_遅いクエリの実行計画.pngEXPLAINの結果でtypeがALL、rowsが約100万になっている画面を撮ってください。「なぜ遅いか」が1枚に写っているのが、あとで効きます。
遅いクエリ その2:JOIN が全表走査を巻き込む
もう1つ、よくある遅い形を測ります。 「投稿の一覧に、投稿者の名前も一緒に出したい」——users と posts を JOIN する形です。正規化した結果、名前は users にしか無いので、繋いで取ります。
SELECT p.id, p.title, u.name
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.user_id = 12345;
20 rows in set (0.60 sec)
これも 0.60 秒。 EXPLAIN を見ると、遅い理由が2行で出ます。JOIN は表ごとに1行ずつ計画が並びます。
EXPLAIN SELECT p.id, p.title, u.name
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.user_id = 12345;
+----+-------------+-------+--------+---------------+---------+---------+--------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------+--------+-------------+
| 1 | SIMPLE | p | ALL | NULL | NULL | NULL | NULL | 997520 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | bench.p.user | 1 | NULL |
+----+-------------+-------+--------+---------------+---------+---------+--------------+--------+-------------+
users 側(u)は type=eq_ref ——主キー(id)で1行ずつ正確に引けています。問題は posts 側(p)が type=ALL で、100万行を全部読んでいることです。JOIN の遅さは「繋ぐこと」そのものではなく、繋ぐ前に片方が全表走査していることが正体です。世の中で「JOIN は遅い」と言われるのは、たいていこれです。
記録します。
クエリ: posts JOIN users WHERE p.user_id = 12345
実行時間 : 0.60 sec
posts 側 type : ALL(全表走査) rows 997520
users 側 type : eq_ref(主キーで一本釣り) rows 1
遅いクエリ その3:新着20件の一覧
サイトの多くは、トップに「新着20件」を出します。 一覧が遅い、の代表格がこれです。
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC
LIMIT 20;
20 rows in set (0.48 sec)
たった20件出すのに 0.48 秒。 EXPLAIN を見ます。
EXPLAIN SELECT id, title, created_at FROM posts
ORDER BY created_at DESC
LIMIT 20;
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 997520 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------+
type=ALL に加えて、Extra に Using filesort が出ています。 これは「100万行を全部読んで、そのうえで並べ替えている」という意味です。LIMIT 20 を付けても、並べ替えは全行に対して起きるのが落とし穴です。「20件しか要らないのに、100万件を並べてから上から20件取っている」——だから遅いのです。
記録します。
クエリ: ORDER BY created_at DESC LIMIT 20
実行時間 : 0.48 sec
type : ALL(全表走査)
Extra : Using filesort(全行を並べ替え)
rows : 997520
ここで索引を張りたくなりますが、まだ張りません。 測る前に張ると、この記事の意味が無くなります。遅い証拠が3つそろった。次で張って、また測ります。
④ 索引を張って、また測る — Before/After を1枚に
張るのは1行です。
CREATE INDEX idx_posts_user ON posts (user_id);
これも数秒かかります。 100万行ぶんの user_id を並べ替えて、索引を作っているからです。
Query OK, 0 rows affected (4.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
まったく同じクエリを、もう一度
一文字も変えずに、さっきのクエリを打ちます。
SELECT * FROM posts WHERE user_id = 12345;
20 rows in set (0.00 sec)
0.61 sec が 0.00 sec になりました。 0.00 は「1ミリ秒未満」の意味です。クエリは1文字も変えていません。 変えたのは「索引を1本張った」ことだけです。
EXPLAIN で、何が変わったか見る
EXPLAIN SELECT * FROM posts WHERE user_id = 12345;
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-------+
| 1 | SIMPLE | posts | ref | idx_posts_user | idx_posts_user | 4 | const | 20 | NULL |
+----+-------------+-------+------+----------------+----------------+---------+-------+------+-------+
1 row in set (0.00 sec)
3か所が変わりました。
typeがALL→ref——「索引を等価で引いて、一致する行だけ読む」。全表走査をやめたrowsが997520→20——読む行数が 5万分の1。これが速さの正体ですkeyがNULL→idx_posts_user——張った索引を、実際に使った
Before/After を1枚の表にする — これが到達作品
この節の3つのクエリ(③で測った3つ)を、1枚にまとめます。これが、めがけていた成果物です。
| クエリ | 索引なし | 索引あり | rows | type |
|---|---|---|---|---|
WHERE user_id = 12345 | 0.61 sec | 0.00 sec | 997520 → 20 | ALL → ref |
JOIN users WHERE user_id = 12345 | 0.60 sec | 0.00 sec | 997520 → 20 | ALL → ref |
ORDER BY created_at DESC LIMIT 20 | 0.48 sec | 0.00 sec | 997520 → 20 | ALL → index |
「速くなった」ではなく、この表が言えることが到達点です。 速さ(実行時間)は結果で、本質は読む行数(rows)が 997520 から 20 前後に減ったこと。type が ALL から ref / index に変わったことが、それを引き起こしています。この3行を自分の手で埋められたら、SQL コースの「索引」は身についています。
どのクエリも「変えたのは索引だけ、SQLは1文字も変えていない」点を、もう一度確かめてください。書き方を工夫したのではなく、MySQL に別の作戦を選ばせた——それが索引です。
測り方の注意(大事)。 2回目のクエリが速いのは、索引のおかげとは限りません。 MySQL は一度読んだデータをバッファプール(メモリ)に載せるので、2回目はディスクを読まずに速いことがあります。今回は
typeとrowsがEXPLAINで明確に変わっているので索引の効果だと言い切れますが、実行時間だけを2回比べて「速くなった」と言うのは危険です。詳しくはチューニングの深掘りに書きました。
スクリーンショットを撮ってください:
03_索引で速くなった.pngEXPLAINのtypeがref、rowsが20になっている画面を撮ってください。02_と並べると、この記事のすべてが2枚で伝わります。
実測で確かめる — EXPLAIN ANALYZE
EXPLAIN の rows は見積りでした。MySQL 8.0 には、実際に実行して、本当に何行読んだか・何ミリ秒かかったかを出す EXPLAIN ANALYZE があります。見積りではなく実測です。
索引を張ったあとのクエリで打ってみます。
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 12345;
-> Index lookup on posts using idx_posts_user (user_id=12345)
(cost=7.05 rows=20) (actual time=0.041..0.128 rows=20 loops=1)
読み方はこうです。
cost=7.05 rows=20——見積り(EXPLAINと同じ側の数字)actual time=0.041..0.128 rows=20——実測。「最初の1行が返るまで 0.041ms、最後まで 0.128ms、実際に読んだのは 20 行」
見積り rows=20 と実測 rows=20 が一致しています。 これは統計情報が正確で、MySQL の作戦が現実と合っている、という意味です。もし見積りと実測が桁で食い違っていたら、それは統計が古い合図で、ANALYZE TABLE posts; で取り直します(チューニングの深掘りで扱いました)。
索引を張る前のクエリに打つと、差がはっきり出ます。 actual time の後ろの数字が数百ミリ秒になり、Table scan on posts (rows=997520) のような行が出ます。EXPLAIN(見積り)で当たりを付け、EXPLAIN ANALYZE(実測)で裏を取る——この2段が、推測を排した測り方です。
もう1つ:範囲検索も速くなる
索引は等価(=)だけでなく、範囲(> < BETWEEN)にも効きます。 created_at で試します。まず索引なしのまま。
EXPLAIN SELECT * FROM posts
WHERE created_at >= '2026-01-01 00:00:00'
AND created_at < '2026-01-02 00:00:00';
type=ALL になるはずです。索引を張ります。
CREATE INDEX idx_posts_created ON posts (created_at);
同じ EXPLAIN を打つと、今度は type=range(範囲で索引を使う)に変わります。ref(等価)と range(範囲)は、どちらも「索引が効いている」側です。ALL でなければまず一安心、と覚えてください。
JOIN も、同じ1本で速くなった
さっき遅かった JOIN(③のその2)を、索引を張ったいま、もう一度打ちます。新しく索引を足す必要はありません。 JOIN が全表走査していたのは posts.user_id を絞れなかったからで、それは④の最初で張った idx_posts_user がそのまま効きます。
EXPLAIN SELECT p.id, p.title, u.name
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.user_id = 12345;
+----+-------------+-------+--------+----------------+----------------+---------+--------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------+----------------+---------+--------------+------+-------+
| 1 | SIMPLE | p | ref | idx_posts_user | idx_posts_user | 4 | const | 20 | NULL |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | bench.p.user | 1 | NULL |
+----+-------------+-------+--------+----------------+----------------+---------+--------------+------+-------+
posts 側が ALL → ref、rows が 997520 → 20 に変わりました。 JOIN 全体が 0.60 sec から 0.00 sec へ。JOIN を速くする第一歩は、繋ぐ条件(ここでは posts.user_id)に索引があることです。JOIN を怖がる前に、まず両側の type を EXPLAIN で見る——これで JOIN の「遅い/速い」は切り分けられます。
新着一覧も、filesort が消えた
③のその3で Using filesort になっていた新着20件を、もう一度測ります。並べ替えに使った created_at には、範囲検索のところで idx_posts_created を張ってあります。索引はすでに並んで持っているので、並べ替え自体が要らなくなるはずです。
EXPLAIN SELECT id, title, created_at FROM posts
ORDER BY created_at DESC
LIMIT 20;
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------+
| 1 | SIMPLE | posts | index | NULL | idx_posts_created | 6 | NULL | 20 | NULL |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------+
Extra から Using filesort が消えました。 そして rows が 20。索引が created_at の降順にたどれるので、MySQL は「並んでいるものの端から20件」を取るだけで済みます。ORDER BY に索引が効いたときの合図が、この「Using filesort が消える」です。実行時間は 0.48 sec から 0.00 sec になりました。
ここで type が index(ALL ではなく)になっている点に注目してください。index は「索引を端からたどる」で、ALL の全表走査とは別物です。LIMIT 20 があるので、端から20件たどった時点で止まれます。
もう一段速く:索引だけで答える(カバリングインデックス)
索引には、まだ伸びしろがあります。 さっきの新着一覧は、created_at の索引で並べ替えは消えました。でも、id と title を取るために、20回「本体の行を見に行っている」——索引には created_at しか無いからです。
必要な列を全部、索引に含めてしまうとどうなるか。created_at に加えて id と title も入れた索引を張ります。
CREATE INDEX idx_posts_cover ON posts (created_at, id, title);
EXPLAIN SELECT id, title, created_at FROM posts
ORDER BY created_at DESC
LIMIT 20;
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-------------+
| 1 | SIMPLE | posts | index | NULL | idx_posts_cover | 494 | NULL | 20 | Using index |
+----+-------------+-------+-------+---------------+-----------------+---------+------+------+-------------+
Extra に Using index が出ました。 これは「答えが索引の中だけで完結し、本体の行を1回も見に行かなかった」という意味です。これをカバリングインデックスと呼びます。索引が、そのクエリに必要な列を全部「カバー」している状態です。
ただし、これも代償があります。 title まで索引に入れたので、索引が太りました(key_len が 494 に増えています)。⑥で見るとおり、索引が大きいほど書き込みが遅く、容量を食います。 カバリングインデックスは「効く」けれど「重い」——測って、本当に必要な一覧にだけ使うのが原則です。使わないなら、いま張ったこの索引は消しておきます。
DROP INDEX idx_posts_cover ON posts;
⑤ 索引が効かない場合 — 張っても使われない3つ
「索引を張れば速い」は嘘です。 正しくは、「張る」と「使われる」は別のことです(索引の深掘りで1本かけて潰した話です)。ここでは、張ってあるのに使われない書き方を3つ、わざと再現します。
(1) 部分一致の LIKE '%...%'
まず title に索引を張ります。
CREATE INDEX idx_posts_title ON posts (title);
そのうえで、前方一致(... 500000 で始まるものではなく、途中に含むものを探す)で引きます。
EXPLAIN SELECT * FROM posts WHERE title LIKE '%500000%';
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 997520 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
索引があるのに type=ALL。使われていません。 理由は、索引は「先頭からの並び」でできているからです。電話帳で「田中」は引けても、「名前の途中に『中』が入る人」は全ページめくるしかない——それと同じです。先頭が % の LIKE は、索引の出番がありません。
直し方は2つ。
前方一致に変えられるなら、変える(% を後ろだけにする)。
EXPLAIN SELECT * FROM posts WHERE title LIKE '投稿タイトル 5000%';
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
| 1 | SIMPLE | posts | range | idx_posts_title | idx_posts_title | 482 | NULL | 111 | Using index condition |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------+
同じ LIKE でも、先頭に % を付けないだけで type=range になり、索引が使われます。 rows も 997520 から 111 へ。「先頭からの並び」を索引が持っているので、投稿タイトル 5000 で始まるものは、その並びの一区間として一気に取れるからです。
どうしても途中一致が必要なら、LIKE ではなく全文検索を使います。これは部分一致は索引が効かない — 全文検索とMeilisearchで扱った話そのものです。「LIKE '%...%' が遅い」に気づけたら、それは全文検索を検討する合図です。
(2) 列を関数で包む
「2026年1月1日の投稿」を、こう書きがちです。
EXPLAIN SELECT * FROM posts WHERE DATE(created_at) = '2026-01-01';
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 997520 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
created_at に索引を張ってあるのに、type=ALL になります。 possible_keys すら NULL——「使える索引が候補にも挙がらない」状態です。DATE(created_at) のように列を関数で包むと、索引が使えなくなるからです。索引が持っているのは created_at の生の値であって、DATE(...) を通した値ではないからです。
直し方は、列を裸のまま比較する形に書き換えることです。
EXPLAIN SELECT * FROM posts
WHERE created_at >= '2026-01-01 00:00:00'
AND created_at < '2026-01-02 00:00:00';
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
| 1 | SIMPLE | posts | range | idx_posts_created | idx_posts_created | 6 | NULL | 1370 | Using index condition |
+----+-------------+-------+-------+-------------------+-------------------+---------+------+------+-----------------------+
意味は同じなのに、こちらは type=range で索引が効きます。 rows は 997520 から 1370(=その1日ぶんだけ)へ。 「1日ぶん」を、関数ではなく範囲で表したのがコツです。これは日付だけの話ではありません。WHERE price * 1.1 > 1000 も WHERE price > 1000 / 1.1 に書き換える——列側を素のままにするのが原則です。
(3) 複合索引の順序
「特定ユーザーの、公開中の投稿」を速くしたいとします。 2列まとめて索引を張ります。
CREATE INDEX idx_posts_user_status ON posts (user_id, status);
user_id から絞るなら、効きます。
EXPLAIN SELECT * FROM posts WHERE user_id = 12345 AND status = 1;
これは type=ref になります。ところが、status だけで引くとどうなるか。
EXPLAIN SELECT * FROM posts WHERE status = 1;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 997520 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
idx_posts_user_status があるのに、使われません。 複合索引 (user_id, status) は、左端の user_id から順にしか使えないからです。電話帳が「姓 → 名」で並んでいるとき、姓が分かれば引けるが、名だけでは引けない——同じことです。
おまけに、status は値が 0 か 1 の2種類しかありません。 かりに左端に置いて索引を使えたとしても、100万行の8割が status=1——8割を「絞り込めた」とは言えません。値の種類が少ない列(カーディナリティが低い列)は、単独では索引の効きが薄い、というのも合わせて覚えると、複合索引の列順を決める基準になります。「種類が多く、よく = で絞る列」を左に置くのが定石です。
直し方は、よく使うほうを左に置くか、別に status 単独の索引を張るか。ただし後者は「張りすぎ」の入口でもあります(次の⑥へ)。複合索引は、列の順番そのものが性能です。
⑥ 索引の代償 — タダではないことを数字で見る
ここまで読むと「全部の列に索引を張ればいいのでは」と思います。 それは間違いです。索引はタダではありません。 代償を、これも数字で確かめます。
代償その1:書き込みが遅くなる
索引がある列は、INSERT のたびに索引も更新しなければなりません。だから書き込みが遅くなります。 比べるために、同じ形のテーブルを2つ作ります。片方は索引だらけ、片方は索引なし。
CREATE TABLE t_noidx (
id INT PRIMARY KEY AUTO_INCREMENT,
a INT, b INT, c VARCHAR(50)
);
CREATE TABLE t_idx (
id INT PRIMARY KEY AUTO_INCREMENT,
a INT, b INT, c VARCHAR(50),
INDEX (a), INDEX (b), INDEX (c)
);
それぞれに、同じ 20万行を入れて時間を測ります。
INSERT INTO t_noidx (a, b, c)
WITH RECURSIVE seq AS (
SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 200000
)
SELECT n, n * 2, CONCAT('row', n) FROM seq;
Query OK, 200000 rows affected (1.05 sec)
INSERT INTO t_idx (a, b, c)
WITH RECURSIVE seq AS (
SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 200000
)
SELECT n, n * 2, CONCAT('row', n) FROM seq;
Query OK, 200000 rows affected (3.28 sec)
索引3本ぶんで、書き込みが約3倍遅くなりました(1.05 秒 → 3.28 秒)。読み取りが速くなる代わりに、書き込みが確実に遅くなる——これがトレードオフです。
| 索引なし(t_noidx) | 索引3本(t_idx) | |
|---|---|---|
| 20万行の INSERT | 1.05 sec | 3.28 sec |
代償その2:容量が増える
索引は、データとは別に場所を取ります。 posts がいまどれだけ容量を使っているか見ます。
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 = 'bench'
ORDER BY table_name;
+------------+---------+----------+
| table_name | data_mb | index_mb |
+------------+---------+----------+
| posts | 98.6 | 71.2 |
| t_idx | 9.1 | 11.3 |
| t_noidx | 9.1 | 0.0 |
| users | 3.5 | 0.0 |
+------------+---------+----------+
posts は、データ 98.6MB に対して索引だけで 71.2MB。この記事で4本の索引を張った結果です。t_noidx の index_mb は 0.0、t_idx は 11.3MB——索引はまるまる容量を食うのが読めます。
使っていない索引は、ゴミとして残る
「念のため張った索引」の何が悪いか。 読み取りに一度も使われなくても、書き込みのたびに更新され、容量を食い続けるからです。しかも「使われていない」ことは、見ないと気づけません。
MySQL 8.0 には、一度も使われていない索引を一覧する仕組みがあります(sys スキーマ)。
SELECT object_schema, object_name, index_name
FROM sys.schema_unused_indexes
WHERE object_schema = 'bench';
ここに出てくる索引は、サーバーが起動してから一度も使われていない索引です。たとえば⑤で張った idx_posts_title を、前方一致の LIKE でしか使っていなければ、ここに顔を出すかもしれません。「張ったけど使われていない索引」は、書き込みを遅くするだけの純粋な負債です。見つけたら消す判断ができます。
DROP INDEX idx_posts_title ON posts;
ただし、この一覧は「サーバー起動後の実績」で判断している点に注意します。深夜のバッチでしか使わない索引が「未使用」に見えることがあるので、消す前に、本当に使われていないかを期間を置いて確かめます。 ここでも「測ってから」です。
結論:張りすぎない
索引は「遅いクエリを実際に測って、そのために」張るものです。「念のため全列に」は、書き込みを遅くし、容量を無駄にし、使われない索引がゴミとして残るだけです。先に測る → 遅いものにだけ張る → また測る。 ⑥まで来ると、この順番が「面倒だから」ではなく「代償があるから」正しいのだと分かります。
⑦ 完成の判定 — 自分で「できた」と言えるように
「なんとなく速くなった」で終わらせません。 この5つを自分で通したら、SQL コースの到達作品は完成です。
□ 100万行のテーブルを、手入力ではなくSQLで生成できた(COUNT(*) で確認した)
□ 索引の無いクエリを流し、EXPLAIN で type=ALL と rows≈100万 を自分の目で見た
□ CREATE INDEX を1本張り、同じクエリで rows が激減し type が ref/range になった
□ JOIN の遅さが「繋ぐ前の全表走査」だと EXPLAIN の2行で読め、索引で直せた
□ ORDER BY + LIMIT の Using filesort を、索引で消せた
□ 索引が効かない書き方(%LIKE% / 関数で包む / 複合索引の順序)を1つ、書き換えで直せた
□ 索引を張ると INSERT が何倍遅くなるか、容量がどれだけ増えるかを数字で言える
□ EXPLAIN(見積り)と EXPLAIN ANALYZE(実測)を使い分けられる
1つでも通らなかったら、それがこのコースの「まだ手が届いていない場所」です。 そこだけ、対応する回に戻ればいい。この判定リストが、「SQLを読める」を「速くできる」に変えます。
つまずいたところ
cte_max_recursion_depth を上げ忘れて、生成が途中で止まった。 既定は 1000 なので、WITH RECURSIVE で1000回を超えると Recursive query aborted after 1001 iterations で止まります。SET SESSION cte_max_recursion_depth = 1000000; を先に打つのを忘れていました。
索引を張ったのに 0.61 sec のままだった。 EXPLAIN を見たら type=ALL のまま。原因は、user_id ではなく間違えて id に張っていたことでした。「張った」と「使われた」は別——EXPLAIN の key に張った索引の名前が出て初めて「使われた」です。
2回目だけ速くて、索引の効果か分からなくなった。 索引を張る前でも、同じクエリを2回打つと2回目が速いことに気づきました。バッファプールにデータが載ったからです。実行時間の比較ではなく、EXPLAIN の type と rows で判断するようにしたら、迷いが消えました。
LIKE '%...%' が、索引を張っても遅いままだった。 「索引が壊れているのか」と疑いましたが、先頭が % の LIKE は原理的に索引が使えないだけでした。前方一致に変えるか、全文検索に切り替えるのが正解でした。
WHERE DATE(created_at) = '2026-01-01' が全表走査になった。 列を DATE() で包んでいたためです。列を裸のまま範囲で比較する形に書き換えたら、type=range になりました。索引は「列そのもの」に効く、と腹落ちしました。
status だけで引いたら、複合索引が使われなかった。 (user_id, status) の複合索引は、左端の user_id から使う規則を忘れていました。status 単独で使いたいなら、status を左端にした索引が別に要ります。
索引を増やしたら、投稿の登録が体感で遅くなった。 読み取りしか見ていなかったので、書き込みの代償に気づきませんでした。⑥で INSERT を測って、索引3本で書き込みが3倍になるのを見て納得しました。
information_schema の容量が、実際より小さく出た。 data_length は統計値で、更新直後はずれることがあります。正確に見たいときは ANALYZE TABLE posts; を先に打つと近づきます。
EXPLAIN ANALYZE を、索引を張る前の重いクエリに打ったら、なかなか返ってこなかった。 EXPLAIN(見積り)は一瞬ですが、EXPLAIN ANALYZE は実際にクエリを走らせて実測するので、遅いクエリなら遅いだけ待たされます。想定どおりの挙動でした。重いクエリの実測は覚悟して打ちます。
sys.schema_unused_indexes が空だった。 直前に自分でその索引を使うクエリを打っていたためです。この一覧は「起動後に一度も使われていない」を見るので、テスト中に触った索引は消えます。判断は、しばらく通常運用したあとで——ここでも「測ってから」でした。
後片づけ
練習用のデータベースを、丸ごと消します。 100万行は 200MB 近くを占めているので、確かめたら消してディスクを返します。
DROP DATABASE bench;
DROP DATABASE は取り消せません。 ですが、消すのはこの記事で作った bench だけです。既存のデータベースには最初から一切触れていないので、安全です。念のため、消す前に「いま bench にいる」ことを確かめてから打つと安心です。
SELECT DATABASE();
セッションで変えた cte_max_recursion_depth は、SESSION 指定なので接続を切れば元に戻ります。 サーバー全体の設定は何も変えていません。MySQL を出ます。
EXIT;
消したことの確認は、次に入り直して SHOW DATABASES; に bench が無いことを見ればできます。
まとめ — 理解できたこと
- SQL コースの到達点は「速くできること」だった。埋めたのは、Before/After の数字が入った表1枚
- 索引が「速い」の正体は、
type=ALL(全表走査)がtype=ref/range(索引で絞る)に変わること。 速さは結果で、本質は読む行数(rows)が減ること - 同じクエリを1文字も変えず、索引を1本張るだけで 0.61 秒が 0.00 秒になった。
rowsは 997520 から 20 へ EXPLAINはtypeとrowsの2つだけ見れば、遅い理由の9割は分かる。ExtraのUsing filesortは「全行を並べ替えている」合図- JOIN の遅さは「繋ぐこと」ではなく「繋ぐ前の全表走査」。 両側の
typeを見れば切り分けられる ORDER BY+LIMITも、並べ替える列に索引があればUsing filesortが消える- カバリングインデックスなら
Using indexで本体を見に行かずに済むが、索引が太る代償がある - 見積り(
EXPLAIN)で当たりを付け、実測(EXPLAIN ANALYZE)で裏を取る - 「速くする前に必ず測る」の意味。測らずに張ると、効いたのか偶然か分からない。2回目が速いのはバッファプールのせいのことがある
- 索引は「張る」と「使われる」が別。
%LIKE%、列を関数で包む、複合索引の順序ミスで、張っても使われない - 先頭
%のLIKEは索引が使えない ——前方一致に変えるか、全文検索へ - 列を関数で包まない。 範囲に書き換えれば
type=rangeで効く - 複合索引
(a, b)は左端から。b単独では使われない - 索引はタダではない。 書き込みが約3倍遅くなり、索引だけで数十MBを食う。だから遅いものにだけ張る
- 100万行は手で入れない。
WITH RECURSIVE+INSERT ... SELECTで生成する - 完成の判定を自分で持つと、「なんとなく速い」で終わらない
そして、次に学ぶこと
この記事で分かったのは、「1台のデータベースの中で、読む行を減らす」ことでした。 それでも足りなくなる日が来ます。
索引で十分速くなったなら、その先はデータベースの外の話です。よく叩かれる結果を手前で覚えておく(キャッシュ)、読み取りを別のサーバーに分ける(読み書き分離)——ここからは1台の中では解けません。
- 何度も同じ答えを返すなら、キャッシュは4箇所にある — Cache-ControlとCDN で「そもそもDBに聞かない」を考えます
- 読み取りが増えたら、書いた直後に読むと無い — レプリケーションとCAP で「読み専用の複製」を足します。ただし複製には「書いた直後は古い値が返る」代償があることも、そこで測ります
共通しているのは、この記事でやったこと——「先に測る」——です。 索引でも、キャッシュでも、複製でも、測らずに足したものは、効いたかどうか永久に分かりません。 それを、100万行で体に入れたのが、この到達作品でした。
本物のデータベースでやるときの注意
この型は、あなたの実際のアプリにもそのまま使えます。 ただし、練習用の bench と違って、本番には人のデータが入っています。3つだけ守ってください。
CREATE INDEXは、大きなテーブルだと数秒〜数分テーブルを重くすることがあります。本番で張るなら、アクセスの少ない時間帯にしますEXPLAIN ANALYZEは実際にクエリを走らせます。UPDATE/DELETEに対しては打たない(本当に更新・削除されます)。測るのはSELECTだけにします- どの索引を張るかは、
EXPLAINでtype=ALLになっている実在の遅いクエリに対してだけ。 「たぶん要る」で張らない。⑥で見たとおり、使われない索引は負債です
この記事で作った bench は使い捨てですが、身についた「測る → 張る → また測る」は、この先ずっと使えます。 それがコースの到達点です。
参考リンク
- roadmap.sh — SQL
- MySQL 8.0 リファレンス — EXPLAIN の使い方
- MySQL 8.0 リファレンス — EXPLAIN 出力の読み方
- MySQL 8.0 リファレンス — MySQL が索引を使う仕組み
- MySQL 8.0 リファレンス — CREATE INDEX
- MySQL 8.0 リファレンス — 再帰CTE(WITH RECURSIVE)
- MySQL 8.0 リファレンス — InnoDB バッファプール
次に読む
この記事は 【第5段階】バックエンドとデータベース の 12 / 29 本目です。
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
SQL コースの到達作品です。 100万行のテーブルで、遅いクエリを索引で速くする過程を数字で示します。

コメント