第7章 インデックスとパフォーマンス

7.1 インデックスとは何か — 本の索引のたとえ

分厚い技術書で「トランザクション」の説明ページを探すとき、あなたは1ページ目から順にめくりません。巻末の索引で「と」の欄を見て「トランザクション …… 152ページ」を見つけ、そのページに直行するはずです。

データベースのインデックスはまさにこの索引です。インデックスがない列での検索は、DBMSがテーブルの全行を先頭から順に調べるしかありません。これをフルスキャンと呼びます。6行のcompany.dbなら一瞬ですが、100万行の顧客テーブルで毎回全ページをめくっていたら、システムは使いものになりません。

インデックスの実体は、多くのRDBMSでB木(Bツリー)という木構造です。「値の大小で枝分かれする案内板」を数段たどるだけで目的の行にたどり着けます。

salaryのインデックス(イメージ)

              [ 300000 | 360000 ]           ← 案内板: どの枝に進むか
             /         |         \
   [280000, 295000] [300000, 320000, 350000] [360000, 380000, 400000]
        │                  │                       │
     行の場所           行の場所                行の場所

「salary = 380000 の行は?」→ 案内板で右の枝へ → 2回の分岐で発見
フルスキャンなら10万行を全部確認、B木なら数段の分岐で到達

行数が10倍・100倍になっても、たどる段数はわずかしか増えない——これがB木の強みです。10万行から1行を探すのに、フルスキャンは10万回の確認、B木なら十数回で済みます。

7.2 効果を測ってみる — 10万行とEXPLAIN QUERY PLAN

体感するには大量データが必要です。sqlite3だけで完結する WITH RECURSIVE(自分自身を参照して行を生成する書き方。ここでは「10万行を作る呪文」と思ってください)で、10万人の社員テーブルを作ります。

$ sqlite3 perf.db
sqlite> CREATE TABLE big_employees (
   ...>   id INTEGER PRIMARY KEY,
   ...>   name TEXT NOT NULL,
   ...>   salary INTEGER NOT NULL
   ...> );
sqlite> WITH RECURSIVE seq(n) AS (
   ...>   SELECT 1
   ...>   UNION ALL
   ...>   SELECT n + 1 FROM seq WHERE n < 100000
   ...> )
   ...> INSERT INTO big_employees (id, name, salary)
   ...> SELECT n, 'emp' || n, 200000 + (n * 7) % 300000 FROM seq;
sqlite> SELECT count(*) FROM big_employees;
100000

次に、DBMSが「どんな作戦でこのクエリを実行するつもりか」を教えてくれる EXPLAIN QUERY PLAN を使います。実行計画と呼ばれるこの情報こそ、性能チューニングの羅針盤です。

sqlite> .timer on
sqlite> EXPLAIN QUERY PLAN SELECT * FROM big_employees WHERE salary = 350000;
QUERY PLAN
`--SCAN big_employees
sqlite> SELECT count(*) FROM big_employees WHERE salary = 350000;
1
Run Time: real 0.013 ...

SCAN という表示が「全行を順に調べます」というフルスキャン宣言です。ではsalary列にインデックスを作ってみます。CREATE INDEX 索引名 ON テーブル(列) です。

sqlite> CREATE INDEX idx_big_employees_salary ON big_employees(salary);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM big_employees WHERE salary = 350000;
QUERY PLAN
`--SEARCH big_employees USING INDEX idx_big_employees_salary (salary=?)
sqlite> SELECT count(*) FROM big_employees WHERE salary = 350000;
1
Run Time: real 0.000 ...

表示が SCAN から SEARCH ... USING INDEX に変わりました。「全部めくる」から「索引で直行する」への作戦変更です。実行時間も1桁以上縮んでいます。10万行でこの差なら、1000万行では文字どおり千倍級の差になります。SCANかSEARCHか——実行計画を読むときは、まずこの一語を見てください。

7.3 インデックスが効かないパターン

せっかく貼ったインデックスが使われない、という落とし穴が3つあります。共通する原因は「索引に載っている形のまま比較していない」ことです。書名の索引があっても「書名を逆から読んだ結果」では引けないのと同じです。

①は特に頻出です。インデックスに載っているのは hire_date の値そのものであって、「substrを通した後の値」ではありません。列を関数で加工した瞬間、DBMSは全行に関数を適用して確かめるしかなくなり、SCANに転落します。加工するなら比較する側(右辺)を加工し、インデックス列は裸で使う——これが合言葉です。

②の中間一致は、索引が「先頭の文字から順に並んでいる」ことを思い出せば納得できます。「『花子』を含む名前」は、五十音順の索引では探しようがないのです。③は比較のために全行で型変換が走るのが原因で、①の変種と言えます。

7.4 インデックスのコスト — タダではない

「そんなに速くなるなら全列に貼ればいい」と思うかもしれません。しかしインデックスは書き込みのたびに更新される別冊です。本の本文に1ページ足したら索引も直すように、INSERT・UPDATE・DELETEのたびに、その表に貼られたすべてのインデックスの木構造を更新する作業が発生します。

• インデックス1本ごとに、書き込みは少しずつ遅くなる(10本貼れば10冊の索引を毎回直す)
• ディスク容量も消費する(インデックスはデータの複製を含む実体ファイル)
• めったに検索されない列の索引は、維持費だけ払って一度も引かれない索引になる

つまりインデックスは「読み取りを速くするために、書き込みの速度と容量を差し出す」交換です。貼る基準は「WHERE・JOIN・ORDER BYで頻繁に使う列」に絞ること。ログのように書き込みが主役のテーブルでは、特に慎重に選びます。

複合インデックスと列順の初歩

2列以上をまとめた複合インデックスも作れます。このとき列の順番が重要です。

CREATE INDEX idx_emp_dept_salary ON big_employees(dept_id, salary);

これは「部署ごとに給与順で並んだ索引」です。電話帳が「姓→名」の順に並んでいるのと同じで、WHERE dept_id = 20 AND salary >= 300000 には抜群に効きますが、WHERE salary >= 300000 だけ(2列目だけ)の検索には効きません。姓が分からないまま名だけで電話帳は引けないからです。複合インデックスは「先頭の列から順に使われる」——初歩としてこれだけ覚えてください。

7.5 遅いクエリとの付き合い方

実務で「画面が遅い」と言われたとき、腕の見せどころは修正の速さではなく手順の正しさです。

1. 計測する — どのクエリが何秒かかっているかを数字で特定する(.timer on、本番ではDBMSのスロークエリログ)。「たぶんこれが遅い」で直し始めない。
2. 実行計画を見る — 遅いと分かったクエリに EXPLAIN QUERY PLAN。SCANになっていないか、7.3節のパターンを踏んでいないか。
3. 改善して再計測する — インデックス追加やWHERE句の書き直しをして、①と同じ方法で効果を数字で確認する。

推測で直さない、計測で直す。当てずっぽうの変更は、効かないだけでなく「複合インデックスの列順を壊す」「書き込みを遅くする」など状況を悪化させることさえあります。

N+1問題 — クエリは速いのにシステムが遅い

最後に、1本1本のクエリは速いのに全体が遅くなる有名なパターンを紹介します。「社員100人の一覧に部署名を添えて表示する」処理を、プログラム側のループでこう書いてしまうケースです。

-- 社員一覧を1回取得(1本)
SELECT id, name, dept_id FROM employees;
-- その後、ループで社員ごとに部署名を1件ずつ取得(100本)
SELECT name FROM departments WHERE id = 10;
SELECT name FROM departments WHERE id = 20;
-- ... これが人数分続く

一覧取得の1本+行数N本で「N+1問題」と呼びます。1本あたり数ミリ秒でも、往復100回ぶんの通信と処理が積み重なって画面全体は重くなります。解決はシンプルで、JOIN(第4章)を使って1本のSQLにまとめることです。

SELECT e.id, e.name, d.name AS dept_name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;

N+1はプログラムからDBを呼ぶときに無意識に書いてしまいがちで、Python連携を学ぶ第8章でループの中にSELECTを書きそうになる場面として再登場します。「ループの中のSELECTは黄色信号」と覚えておいてください。

まとめ

• インデックスは本の索引。なければ全行を調べるフルスキャン(SCAN)、あればB木を数段たどるだけ(SEARCH)で行に到達する
• EXPLAIN QUERY PLANで実行計画を確認する。見るべき第一のポイントは「SCANかSEARCH ... USING INDEXか」
• 列に関数を適用・中間一致LIKE・型の不一致ではインデックスは使われない。「インデックス列は裸で使う」が合言葉
• インデックスは書き込み速度と容量を差し出す交換。よく検索する列に絞って貼り、複合インデックスは先頭の列から順に効く
• 遅いクエリは「計測→実行計画→改善→再計測」の順で直す。推測で直さない。ループ内SELECT(N+1問題)はJOINでまとめる
• 重いクエリは可用性の侵害でありDoS攻撃の入口。性能対策はセキュリティ対策でもある

練習問題

sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM big_employees WHERE name = 'emp77777';
QUERY PLAN
`--SCAN big_employees
sqlite> CREATE INDEX idx_big_employees_name ON big_employees(name);
sqlite> EXPLAIN QUERY PLAN
   ...> SELECT * FROM big_employees WHERE name = 'emp77777';
QUERY PLAN
`--SEARCH big_employees USING INDEX idx_big_employees_name (name=?)

SELECT * FROM employees
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

sqlite> SELECT id, name, hire_date FROM employees
   ...> WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';
4|高橋 美咲|2020-04-01