昨年は数ミリ秒かかっていたクエリが、今日では数分かかる場合があります。これは単にテーブルが大きくなったためです。 SQL クエリの最適化 は、巧妙なトリックではなく、反復可能なプロセスに重点を置いています。つまり、データベースが何を行っているかを調べ、無駄な作業を削除し、それが役に立ったかどうかを確認します。

SQLite 3.50 で生成された 100 万行に対して、以下の各手法をテストしました。タイミングは、メモリ内データベースを使用して 1 台のラップトップから複数回実行したうちの最良の値です。これらはご使用のシステムと一致せず、PostgreSQL、MySQL、SQL Server にはそれぞれ独自のオプティマイザーとプラン形式があります。それぞれの改善の規模とその背後にある原則に注目してください。

一目でわかる結果

テストのセットアップ

CREATE TABLE customers (
  id INTEGER PRIMARY KEY, name TEXT, city TEXT
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer_id INT, status TEXT,
  created TEXT, total INT, note TEXT
);
-- 50,000 customers and 1,000,000 orders with random values

シンプルな最適化プロセス

1. 重要なクエリを見つけます。 遅いクエリのログまたはモニタリングを使用します。 1 分間に 1,000 回実行される 200 ミリ秒のクエリは、10 秒の月次レポートよりも重要な場合があります。
2. 計画を読んでください。 データベースが実際に何をしているのかを確認してください。
3. 1 つ変更します。 インデックスを追加するか、クエリの一部を書き換えます。
4. 現実的なデータに基づいて 再測定。 100 個のテスト行に関する計画では、100 万個についてはほとんどわかりません。
5. 時間をかけて観察してください。 データが増加し、統計が変化すると、計画が変更される可能性があります。

実行計画を読む

実行プランは、クエリを実行するためのデータベースの段階的な戦略であり、使用するインデックスやテーブルを結合する順序なども含まれます。クエリを参照するには、クエリにプレフィックスを付けます。

PostgreSQL の EXPLAIN ANALYZE は実際にクエリを実行するため、UPDATE または DELETE と一緒に使用する場合は注意してください。最初に探すべきものは次のとおりです。

フルスキャンは自動的に問題になるわけではありません。クエリに小さなテーブルの大部分が必要な場合は、スキャンが最も速い選択肢になります。 PostgreSQL の EXPLAIN ANALYZE では、推定行と実際の行も比較します。大きな違いは、多くの場合、テーブル統計が古いことを意味します。

適切なインデックスを追加する

このクエリは 1 人の顧客の注文を検索します。

SELECT id, total FROM orders WHERE customer_id = 123;

インデックスを追加する前の計画はフル スキャンでした。

SCAN orders
-- 20 rows in 73 ms

次に、フィルター列にインデックスを追加しました。

CREATE INDEX idx_orders_customer ON orders(customer_id);

SEARCH orders USING INDEX idx_orders_customer (customer_id=?)
-- 20 rows in 0.01 ms

データベースがそれらを見つけるために 100 万行を読み取る必要がなくなったため、同じ 20 行が何千倍も速く返されます。この記事の他のテクニックでは、これ以上の勝利をもたらしたものはありません。特に結果を数行に絞り込む場合、最初の候補としては、WHERE、JOIN、および ORDER BY で使用される列が適しています。 SQL インデックス のガイドでは、その仕組みについて説明しています。

インデックス付き列を関数内でラップしないでください

created のインデックスを使用して 3 月の注文を検索する 2 つの方法を次に示します。

-- Function on the column
SELECT id FROM orders WHERE substr(created, 1, 7) = '2026-03';
-- SCAN orders USING COVERING INDEX idx_orders_created
-- 84,961 rows in 96 ms

-- Plain range
SELECT id FROM orders
WHERE created >= '2026-03-01' AND created < '2026-04-01';
-- SEARCH orders USING COVERING INDEX idx_orders_created (created>? AND created<?)
-- 84,961 rows in 33 ms

どちらも同じ行を返しますが、最初の行は約 3 倍の時間がかかりました。この計画はその理由を示しています。列に関数を使用すると、データベースは並べ替えられたインデックス内の位置にジャンプできないため、すべてのエントリを SCAN 実行する必要があります。範囲バージョンは SEARCH 正しい場所にまっすぐ移動できます。このようにインデックスを使用できる条件は、sargable と呼ばれます。

次の一般的なパターンでも同じ問題が発生します。

最後のものも確認しました。最初の文字からソートされたインデックスは最後の一致には役に立たないため、先頭のワイルドカード SCAN customers が生成されます。

複合インデックス

クエリが 2 つの列でフィルタリングする場合、単一列のインデックスはジョブの一部のみを実行します。

SELECT id FROM orders
WHERE status = 'paid'
  AND created >= '2026-03-01' AND created < '2026-03-08';

-- Using only the created index: 6,198 rows in 19 ms

CREATE INDEX idx_status_created ON orders(status, created);

-- SEARCH orders USING COVERING INDEX idx_status_created
--   (status=? AND created>? AND created<?)
-- 6,198 rows in 2.2 ms

列の順序は非常に重要です。 (status, created) のインデックスは、姓、名の順に並べ替えられた電話帳のように機能します。姓のみを検索することはできますが、名のみを検索することはできません。 status に対するクエリだけでも、このインデックスを使用できます。一般に、created のみでフィルタリングすると、それを効率的に使用できません。経験則として、最初に等価性をテストする列を配置し、その後に範囲列を配置します。

この計画には インデックスをカバー とも記載されています。これは、インデックスがクエリに必要なすべてを保持しているため、テーブル自体を読み取る必要がないことを意味します。必要な列のみを選択すると、この可能性が高くなります。これが SELECT * を避けるもう 1 つの理由です。

ページネーションの高速化

深いページに OFFSET を使用すると、データベースはスキップされたすべての行を通過する必要があるため、隠れたコストがかかります。

-- Page deep into the results
SELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 500000;
-- 8.1 ms

-- Keyset pagination: continue from the last id seen
SELECT id FROM orders WHERE id > 500000 ORDER BY id LIMIT 20;
-- 0.01 ms

キーセットのページネーションは、どれだけ深く進んでも高速なままです。また、新しいデータが追加されたときにページ間で行が移動することも避けられます。その代償として、ユーザーは 5,000 ページに直接ジャンプすることができませんが、これは実際のニーズであることはほとんどありません。幅の広い行があるテーブル、またはスキップする行が多いテーブルでは、ギャップがテストの場合よりもはるかに大きくなる可能性があります。

より無駄のないクエリを作成する

• 必要な列のみを選択してください。 SELECT * はより多くのデータを送信するため、インデックスがクエリをカバーできなくなる可能性があります。
• 早期にフィルタリングします。 グループ化する前に行を削除するには WHERE を使用し、集計の条件にのみ HAVING を使用します。 SQL GROUP BY および HAVING を参照してください。
• 数行しか必要ない場合は、LIMIT を使用します。
• 重複を削除する必要がない場合は、UNION ALLを推奨します。 UNION vs UNION ALL テストでは、何が節約できるかを示しています。
• 不必要な DISTINCT は避けてください。 重複を生成する結合が隠蔽されることがよくあります。
• 存在チェックには EXISTS を使用します。SQL サブクエリのガイドで説明されているように、最初の一致で停止する可能性があります。
• N+1 クエリを監視します。 ループ内の行ごとに 1 つのクエリがあり、通常はアプリケーション コードによって引き起こされ、実際の問題となることがよくあります。単一の結合またはバッチ クエリで、数百回のラウンド トリップを置き換えることができます。

結合します

結合する列にインデックスを付けます。ある都市の顧客を対象に総支出レポートを実行しました。その計画はまさに期待どおりのものでした。

SELECT c.name, SUM(o.total)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Goa'
GROUP BY c.id;

-- SCAN c
-- SEARCH o USING COVERING INDEX idx_cover (customer_id=?)
-- 9,900 rows in 34 ms

顧客をスキャンし、インデックスを使用して各顧客の注文を見つけます。 orders.customer_id にインデックスがないと、データベースは顧客ごとにさらに多くの作業を実行する必要があります。外部キーのインデックスは、最も一般的で価値のある追加機能の 1 つです。基本の理解を深めたい場合は、SQL 結合の説明を参照してください。

インデックスにコストがかかる場合

インデックスは無料ではないため、列ごとにインデックスを追加しないでください。

• 書き込みが遅くなります。 挿入、更新、削除のたびに、各インデックスも更新する必要があります。
• ストレージとメモリの追加。 大きなテーブルではインデックスが大きくなる可能性があります。
• 非選択列にはほとんど利点がありません。 ほとんどの行が一致する場合、true または false 列のインデックスは役に立たない可能性があります。
• 冗長性。 (a, b) にすでにインデックスがある場合、(a) のインデックスは多くの場合不要です。

使用状況統計を定期的に確認し、使用していないインデックスを削除します。また、オプティマイザは適切なプランを選択するためにそれらに依存するため、ANALYZE またはデータベースと同等のものを使用して統計を最新に保ちます。遅い部分はクエリではなく競合である場合があります。ロックを保持したまま長時間実行される トランザクション により、高速なクエリが遅く見えることがあります。

よくある間違い

• 測定せずに最適化する。 自信に満ちた推測の多くは、間違っていることが判明します。
• 小さなデータを使用したテスト。 テーブルが大きくなるにつれて、計画とタイミングは劇的に変化します。
• 別のデータベースのヒントを信頼する。 あるエンジンに対するアドバイスは、別のエンジンには当てはまらない場合があります。
• 盲目的にインデックスを追加します。それぞれのインデックスは書き込み速度を低下させます。
• 最初の実行の効果を無視します。 キャッシュにより 2 回目の実行が高速になるため、数回実行します。

まとめ

遅いクエリのほとんどには、インデックスの欠落、列の関数、フェッチされるデータが多すぎるなどの通常の原因があります。今週最も遅い実際のクエリを取り上げ、EXPLAIN を実行して、大きなテーブルのフル スキャンを探します。 1 つの点を修正し、再度測定し、変更した点をメモしておきます。

よくある質問

学び続ける