なぜ遅いのか：インデックスとEXPLAIN

全表走査

どんなクエリでも前に EXPLAIN QUERY PLAN を付ければ、実行はされず、計画が返ります。

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE name = 'Quill Helmet';

SCAN products

SCAN は、データベースがテーブルの全行を読んで1件ずつ判定したという意味です。products.name にはインデックスがないので、これ以上の手はありません。400行なら誰も気づきません。気にする理由は、このコストがテーブルに正比例して伸びることです。同じ計画は1,000万行なら1,000万行を、クエリを実行するたびに読みます。

索引検索

price にはインデックスがあります。

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE price > 2000;

SEARCH products USING INDEX idx_products_price (price>?)

SEARCH が見たい言葉です。データベースはインデックスに行き、価格が2000を超える位置へ直接飛び、そこから先だけを読みました。答えの外にある行には触れていません。他のエンジンは同じ区別を別の言葉で表します。PostgreSQL は Seq Scan と Index Scan、MySQL の EXPLAIN は type: ALL と type: range、SQL Server は Table Scan と Index Seek です。

結合の計画

EXPLAIN QUERY PLAN
SELECT o.order_number, c.last_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'delivered';

SEARCH o USING INDEX idx_orders_status (status=?)
SEARCH c USING INTEGER PRIMARY KEY (rowid=?)

ループとして読んでください。ステータスのインデックスで配達済みの注文を見つけ、その1件ごとに主キーで顧客を引く。どちらも検索で、健全な結合はこう見えます。疑うべきなのは内側の SCAN です。それは外側の1行ごとにテーブルを丸ごと読むという意味で、開発環境では速かった結合が本番で何分もかかる典型的な理由です。外部キーの列にはインデックスを。SQLite や MySQL を含むほとんどのデータベースは、勝手には作ってくれません。

インデックスの失い方

orders.ordered_at にはインデックスがあります。このクエリはその列で絞り込んでいるのに、全表を読みます。

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE substr(ordered_at, 1, 4) = '2025';

SCAN orders

インデックスが持っているのは ordered_at であって、substr(ordered_at, 1, 4) ではありません。列を関数で包んだ時点で、保存されている並び順は何も教えてくれなくなり、判定のために全行で式を計算するしかなくなります。WHERE YEAR(created_at) = 2025 も WHERE UPPER(email) = ... も WHERE price * 1.1 > 100 も同じです。

比較の相手に裸の列が向くように書き直します。

EXPLAIN QUERY PLAN
SELECT * FROM orders
WHERE ordered_at >= '2025-01-01'
  AND ordered_at <  '2026-01-01';

SEARCH orders USING INDEX idx_orders_ordered_at (ordered_at>? AND ordered_at<?)

同じ行が返り、今度はインデックスが働いています。一般則はインデックスの列は比較の片側に単独で置き、計算はもう片側へ移すこと。日付に対する関数を半開区間に直すのが、その圧倒的に多い実例です。

ORDER BY はただにできる

EXPLAIN QUERY PLAN
SELECT * FROM products ORDER BY price DESC LIMIT 20;

SCAN products USING INDEX idx_products_price

3つめの形です。検索ではありませんが、順序のない走査でもありません。クエリは価格順の行をほしがっていて、インデックスはすでに価格順に並んでいるので、データベースはインデックスをたどって20行で止まります。並べ替えは一度も起きません。このインデックスがなければ400行を読み、並べ替え、380行を捨てることになります。

インデックスが WHERE と同じくらい ORDER BY ... LIMIT の役に立つのはこのためで、「Xの上位20件」型のクエリを確かめる価値があるのもこのためです。適切なインデックスがあれば触るのは20行、なければテーブル全体です。

短いチェックリスト

• 絞り込みと結合に使う列にはインデックスを。特に外部キーは、自動で付くことがまずありません。
• WHERE の中でインデックス列を関数に入れないこと。
• できる場面では LIKE '%abc%' より LIKE 'abc%' を。先頭のワイルドカードはインデックスを使えません。
• インデックスはただではありません。書き込みのたびに更新されるので、実際に問い合わせる列にだけ付けてください。
• 推測せず測ること。変更の前後で EXPLAIN を実行し、2つの計画を比べてください。

この先は

基本コースはここまでです。方言のページでは、ページング、upsert、文字列と日付の扱いという3つの作業を MySQL、PostgreSQL、SQL Server、Oracle、SQLite で並べています。この3つは、動いているクエリを別のエンジンに持っていくとき、書き直さないと動かないほど構文が違う領域だからです。