SQL：インデックスとパフォーマンス

idx_categoryが作成される

CREATE INDEX idx_category ON products(category); と書く。

CREATE INDEX idx_category ON products(category);

「どのテーブルの」「どの列に」「何という名前で」を指定する。作成しただけでは何も変わらないが、以降その列での検索が速くなる。

解答 == B

「走査する」という意味の英単語。

B

SCANは「端から端まで見る」という意味。インデックスが無い列で検索すると、この方式になる。行数が少なければ問題にならないが、数百万件になると致命的に遅くなる。

idx_categoryが削除される

DROP INDEX idx_category; と書く。

DROP INDEX idx_category;

インデックスの削除はテーブル自体には影響しない。データは残ったまま、索引だけが無くなり、検索速度が元に戻る。

____ QUERY PLAN
SELECT * FROM products WHERE price > 10000;

実行計画が表示される

「説明する」という意味の7文字の英単語。

EXPLAIN

EXPLAIN QUERY PLAN を先頭に付けるだけで、そのSQLが実際にはどう実行されるかが分かる。デバッグや性能調査の第一歩。

解答 == B

products.id での検索は最初から速い。

B

PRIMARY KEY には自動でインデックスが付く。だから id での検索は何もしなくても速い。自分で作る必要があるのは、それ以外の列で頻繁に検索する場合。

インデックス作成後、SEARCHが使われる

CREATE INDEXの後に、別のSQL文としてEXPLAIN QUERY PLANを書く。

CREATE INDEX idx_price ON products(price);

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE price = 3200;

作成した直後から効果が反映される。実行計画がSCANからSEARCHに変わっていれば、インデックスが正しく使われている証拠。

CREATE ____ INDEX idx_name ON products(name);

idx_nameが作成される

「一意」を意味する6文字のキーワード（SQL15で学んだ制約と同じ単語）。

UNIQUE

UNIQUE INDEXは索引と重複禁止を同時に実現する。SQL15のUNIQUE制約は、内部的にはこの仕組みで動いている。

解答 == B

インデックスは検索用の索引。データが書き込まれるたびに、索引も更新する必要がある。

B

インデックスはタダではない。書き込みのたびに索引の更新コストがかかるので、「よく検索される列」にだけ絞って作るのが基本方針。全列に付けるのは逆効果になりうる。

インデックスが一覧に表示される

CREATE INDEXのあと、SELECT name FROM sqlite_master WHERE type='index'; で確認する。

CREATE INDEX idx_category ON products(category);

SELECT name, tbl_name FROM sqlite_master WHERE type='index';

sqlite_master はデータベースの構造そのものを保存しているテーブル。作成したテーブルやインデックスの一覧を、ここから確認できる。

インデックス作成後の実行計画が表示される

作成 → EXPLAIN QUERY PLAN の順で2本書く。

CREATE INDEX idx_stock ON products(stock);

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE stock < 10;

範囲検索（< や BETWEEN）にもインデックスは効く。完全一致だけでなく、大小比較でも索引を使って絞り込みができる。

idx_areaが作成される

CREATE INDEX idx_area ON customers(area); と書く。

CREATE INDEX idx_area ON customers(area);

「エリア別の顧客一覧」を頻繁に検索するなら、この列にインデックスを作る価値がある。SQL09〜11で何度も使ったWHERE c.area='東京'のような検索が、これで速くなる。

インデックス作成後の実行計画が表示される

CREATE INDEX idx_orders_customer ON orders(customer_id); のあと、EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id=1; を書く。

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

JOINのON句で使われる列は、インデックスの最有力候補。SQL09〜11で書いた ON o.customer_id = c.id のような結合が、大量データでは大きく速くなる。

1回目はSCAN、2回目はSEARCHになる

EXPLAIN QUERY PLAN → CREATE INDEX → EXPLAIN QUERY PLAN の順に3本書く。

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE name = 'マウス';

CREATE INDEX idx_name ON products(name);

EXPLAIN QUERY PLAN
SELECT * FROM products WHERE name = 'マウス';

同じクエリでも、インデックスの有無で実行計画がSCANからSEARCHに変わるのを自分の目で確認できた。これがインデックスの効果を体感する最も直接的な方法。

2つのインデックスが作成され、件数が確認できる

2本のCREATE INDEXのあと、COUNT(*)で数える。

CREATE INDEX idx_price ON products(price);

CREATE INDEX idx_stock ON products(stock);

SELECT COUNT(*) AS インデックス数
FROM sqlite_master
WHERE type='index' AND tbl_name='products';

1つのテーブルに複数のインデックスを持たせられる。ただしD08で学んだとおり、増やすほど書き込みコストも増える。本当に必要な列だけに絞る判断が実務では求められる。

削除後、idx_categoryが一覧に無い

最初にCREATE INDEXしてから、DROP INDEXし、最後にSELECTで確認する。

CREATE INDEX idx_category ON products(category);

DROP INDEX idx_category;

SELECT name FROM sqlite_master
WHERE type='index' AND name='idx_category';

使われなくなったインデックスは削除するのが正しい運用。残しておいても検索は速くならず、書き込みコストだけが無駄にかかり続ける。