数秒のクエリを数ミリ秒にする — SQLの到達作品

SQLの到達作品。データベースと索引のカード、ストップウォッチと虫眼鏡。

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つです。

  1. 特定ユーザーの投稿を出す(WHERE user_id = ?)——一覧やプロフィール画面
  2. 投稿に投稿者名を添える(JOIN users)——正規化したら必ず出てくる形
  3. 新着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だけで生成する方法

この記事でやること

  1. 練習用のデータベースと、正規化した2テーブル(users / posts)を作る
  2. INSERT ... SELECT で 100万行を生成する(手で入れない)。件数を確認する
  3. 索引を張る前に、遅いクエリを実際に流して実行時間と EXPLAIN を記録する
  4. CREATE INDEX を張り、まったく同じクエリをもう一度測る。Before/After を1枚の表にする
  5. 索引が効かない書き方を3つ再現し、書き換えで直す
  6. 索引を張ると INSERT が何倍遅くなるか、容量がどれだけ増えるかを測る
  7. 完成の判定(チェックリスト)を自分で通す。練習用データベースを消す

先に知る言葉

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

言葉意味
全表走査(フルスキャン)テーブルの全行を先頭から最後まで読むこと。EXPLAIN の type が ALL になる。行数が増えるほど遅い
索引(インデックス)列の値を並べて別に持っておく仕組み。本の巻末索引と同じで、全ページめくらずに目的の行へ跳べる
EXPLAINクエリを実行せずに、MySQL が「どう処理するつもりか」を出させる命令。実行計画とも呼ぶ
typeEXPLAIN の列。どう行を絞るか。ALL(全表走査)が最悪、ref / range は索引が効いている
rowsEXPLAIN の列。MySQL が読むつもりの行数の見積り。ここが小さいほど速い
type=ref等価(=)で索引を引いて、一致する行だけ読む状態。今回のゴール
type=range範囲(> < BETWEEN)で索引を使う状態。これも効いている側
複合索引複数列をまとめて張った索引。(a, b) の形。左端の列から順にしか使えないという規則がある
正規化同じ情報を1か所にだけ置くようテーブルを分ける設計。ここでは投稿者を users に分ける
バッファプールMySQL がよく使うデータをメモリに載せておく領域。2回目が速いのはこれのせいのことがある(測り方の注意)

① 題材とデータベースを用意する

まず、既存のデータに触らないよう、専用のデータベースを1つ作ります。

MySQL に入ります。素の Ubuntu に apt で入れた MySQL 8.0 なら、root は OS のユーザーで入れます(MySQLとSQLの基本の回で入れたものです)。

bash
$ sudo mysql

うまく入れないとき(root にパスワードを設定してある場合)は、こちらです。

bash
$ mysql -u root -p

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_行数確認.png SELECT COUNT(*) FROM posts; が 1000000 を返している画面を撮ってください。これがこの記事の出発点(100万行そろった)の証拠です。


③ 遅いクエリを作って、先に測る

ここが、この記事でいちばん大事な節です。 前回のパフォーマンスの回で繰り返した約束を、もう一度守ります。

索引を張る前に、遅い状態を測る。 測らずに索引を張ると、速くなっても「索引が効いたのか、たまたまか」が永久に分かりません。

測るとき、3つだけ約束します。 これを外すと、数字が嘘をつきます。

  1. 同じクエリを、索引の前と後で1文字も変えない。 変えると「索引のおかげか、書き換えのおかげか」が混ざる
  2. 実行時間だけで判断しない。 かならず EXPLAIN の type と rows も見る。実行時間はバッファプールに左右されるが、type と rows は作戦そのものを表す
  3. 前と後の数字を、その場でメモする。 「たぶん速くなった」ではなく、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_遅いクエリの実行計画.png EXPLAIN の結果で 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枚にまとめます。これが、めがけていた成果物です。

クエリ索引なし索引ありrowstype
WHERE user_id = 123450.61 sec0.00 sec997520 → 20ALL → ref
JOIN users WHERE user_id = 123450.60 sec0.00 sec997520 → 20ALL → ref
ORDER BY created_at DESC LIMIT 200.48 sec0.00 sec997520 → 20ALL → index

「速くなった」ではなく、この表が言えることが到達点です。 速さ(実行時間)は結果で、本質は読む行数(rows)が 997520 から 20 前後に減ったこと。type が ALL から ref / index に変わったことが、それを引き起こしています。この3行を自分の手で埋められたら、SQL コースの「索引」は身についています。

どのクエリも「変えたのは索引だけ、SQLは1文字も変えていない」点を、もう一度確かめてください。書き方を工夫したのではなく、MySQL に別の作戦を選ばせた——それが索引です。

測り方の注意(大事)。 2回目のクエリが速いのは、索引のおかげとは限りません。 MySQL は一度読んだデータをバッファプール(メモリ)に載せるので、2回目はディスクを読まずに速いことがあります。今回は type と rows が EXPLAIN で明確に変わっているので索引の効果だと言い切れますが、実行時間だけを2回比べて「速くなった」と言うのは危険です。詳しくはチューニングの深掘りに書きました。

スクリーンショットを撮ってください: 03_索引で速くなった.png EXPLAIN の 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万行の INSERT1.05 sec3.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台の中では解けません。

共通しているのは、この記事でやったこと——「先に測る」——です。 索引でも、キャッシュでも、複製でも、測らずに足したものは、効いたかどうか永久に分かりません。 それを、100万行で体に入れたのが、この到達作品でした。

本物のデータベースでやるときの注意

この型は、あなたの実際のアプリにもそのまま使えます。 ただし、練習用の bench と違って、本番には人のデータが入っています。3つだけ守ってください。

  • CREATE INDEX は、大きなテーブルだと数秒〜数分テーブルを重くすることがあります。本番で張るなら、アクセスの少ない時間帯にします
  • EXPLAIN ANALYZE は実際にクエリを走らせます。 UPDATE / DELETE に対しては打たない(本当に更新・削除されます)。測るのは SELECT だけにします
  • どの索引を張るかは、EXPLAIN で type=ALL になっている実在の遅いクエリに対してだけ。 「たぶん要る」で張らない。⑥で見たとおり、使われない索引は負債です

この記事で作った bench は使い捨てですが、身についた「測る → 張る → また測る」は、この先ずっと使えます。 それがコースの到達点です。


参考リンク


次に読む

この記事は 【第5段階】バックエンドとデータベース の 12 / 29 本目です。

記事
← 前の記事ALTER TABLE を手で打たない — ORMとマイグレーション
→ 次の記事キャッシュは消すのが一番難しい — Redisの導入

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

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

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

SQL コースの到達作品です。 100万行のテーブルで、遅いクエリを索引で速くする過程を数字で示します。

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

コメント

コメントする

目次