【データベース】実行計画とアナライズ - j-komatsu/myCheatSheet GitHub Wiki

実行計画とアナライズ

1. 実行計画とは？

読み方：じっこうけいかく（Execution Plan）

実行計画とは、データベースがSQLクエリをどのように処理するかを示したものです。
パフォーマンスを向上させるために、クエリの最適化やインデックスの利用状況を確認できます。

🔰 初学者向け

SQLクエリが実行されると、データベースはどのような順番で処理を行うかを決定する。
例えば、テーブルAとテーブルBを結合する場合、どのテーブルを先にスキャンするかなどが決まる。
実行計画を確認することで、クエリが遅い原因を探ることができる。

🏢 専門者向け

実行計画は、クエリオプティマイザによって決定され、異なるクエリパターンや統計情報に基づいて最適化される。
シーケンシャルスキャン、インデックススキャン、ハッシュジョイン、ネストループ など、さまざまな実行方法がある。
データベースのオプティマイザが、インデックスや結合方法、並列実行の可否を決定する。

2. アナライズとは？

読み方：アナライズ（ANALYZE）

アナライズは、データベース内のテーブルやインデックスの統計情報を収集し、クエリオプティマイザに正確なデータを提供するためのコマンドです。

🔰 初学者向け

実行計画は、統計情報をもとに最適なクエリパスを決定する。
ANALYZEを実行すると、統計情報が最新の状態に更新され、より正確な実行計画が得られる。

🏢 専門者向け

統計情報の更新は、クエリ最適化に重要な役割を果たす。
大量のデータが追加・削除された場合、ANALYZE を実行しないと誤った実行計画が作成される可能性がある。
PostgreSQL では ANALYZE、MySQL では ANALYZE TABLE で統計情報を更新できる。

3. 実行計画の読み方

以下のようなSQLを例に、実行計画の読み方を解説します。

EXPLAIN ANALYZE SELECT * FROM employees WHERE age > 30;

🔰 初学者向け

Seq Scan: シーケンシャルスキャン（全件検索）
Index Scan: インデックスを使用した検索
Nested Loop: ネストループジョイン（小規模データ向き）
Hash Join: ハッシュジョイン（大規模データ向き）

🏢 専門者向け

以下のような結果が表示されます。

Seq Scan on employees  (cost=0.00..23.10 rows=5 width=244) (actual time=0.005..0.015 rows=5 loops=1)

cost: クエリのコスト（開始コスト .. 終了コスト）
rows: 推定行数
actual time: 実際の実行時間

4. 実行計画の活用方法

🔰 初学者向け

クエリのパフォーマンスを改善するため、WHERE句でインデックスを活用する。
不必要なカラムの取得を避ける（SELECT * より SELECT col1, col2 が良い）。

🏢 専門者向け

JOINの順序を変更して最適な実行計画を得る。
インデックスヒントを利用し、オプティマイザが適切なインデックスを選択するよう誘導する。

5. 実行計画の例

以下に PostgreSQL の EXPLAIN ANALYZE を用いた例を示します。

EXPLAIN ANALYZE SELECT * FROM employees WHERE age > 30;

Seq Scan on employees  (cost=0.00..23.10 rows=5 width=244) (actual time=0.005..0.015 rows=5 loops=1)

Seq Scan が発生しているため、適切なインデックスを作成することでパフォーマンスを向上できます。

CREATE INDEX idx_age ON employees(age);
ANALYZE employees;
EXPLAIN ANALYZE SELECT * FROM employees WHERE age > 30;

6. 各種データベースでの設定・利用方法

データベース	実行計画の取得方法	統計情報の更新方法
PostgreSQL	`EXPLAIN ANALYZE`	`ANALYZE`
MySQL	`EXPLAIN`	`ANALYZE TABLE`
SQL Server	`SET SHOWPLAN_XML ON`	`UPDATE STATISTICS`
Oracle	`EXPLAIN PLAN FOR`	`DBMS_STATS.GATHER_TABLE_STATS`

7. サンプルコード

以下のSQLを実行することで、データベースの最適化を実践できます。

-- PostgreSQL の場合
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

INSERT INTO employees (name, age) VALUES
('Alice', 25), ('Bob', 40), ('Charlie', 35);

EXPLAIN ANALYZE SELECT * FROM employees WHERE age > 30;

インデックスを作成すると以下のように改善される。

CREATE INDEX idx_age ON employees(age);
ANALYZE employees;
EXPLAIN ANALYZE SELECT * FROM employees WHERE age > 30;

8. まとめ

実行計画 はSQLの実行手順を示し、クエリ最適化に役立つ。
ANALYZE を実行すると、統計情報が更新され最適なプランが選択される。
インデックス を適切に使用すると、パフォーマンス向上が期待できる。

💡 実行計画を理解し、データベースのパフォーマンスを向上させましょう！