SQLチューニング入門：DBMSの内部処理とパフォーマンス最適化
SQL文のパフォーマンスを向上させるには、DBMSの内部処理を理解することが不可欠です。
本記事では、オプティマイザの仕組みや統計情報、実行計画の確認方法などを、アウトプットをかねて実例・コマンド付きでまとめています。
1. DBMSにおけるSQLクエリ処理の流れ
DBMSはSQL文を以下のステップで処理します：
-
パーサ（Parser）
SQLの構文解析と正当性チェックを行う。 -
オプティマイザ（Optimizer）
実行方法の候補を作り、コストを比較して最適な実行計画を選定。 -
実行計画（プラン）
選ばれた実行方法の詳細なプラン。 -
実行エンジン（Executor）
実際にプランを元にデータを読み取り、結果を返す。
┌───────┐
│ クエリ │
└──┬────┘
↓
┌─────────────┐
│ パーサ │
└────┬────────┘
↓
┌─────────────┐
│ オプティマイザ│
└────┬────────┘
↓
┌─────────────┐
│ 実行計画(プラン)│
└────┬────────┘
↓
┌─────────────┐
│ 実行エンジン │
└─────────────┘
2. オプティマイザと付き合う方法
SQLのパフォーマンスを左右する大きな要因のひとつが、**オプティマイザ（Optimizer）**の働きです。
オプティマイザは、SQL文の構文を解析したあと、最適な実行方法（実行計画）を決定します。
オプティマイザの判断基準
オプティマイザは、以下のような情報をもとにコストを計算し、最適なプランを選びます。
- 各テーブルの行数（件数）
- 各列の値のバラつき（カーディナリティ）
- インデックスの有無
- NULLの比率
- 統計情報の更新状況
これらの情報は「統計情報」としてDBMS内部に保持されています。
3. 統計情報を更新するには
統計情報が古くなっていると、オプティマイザは誤った実行計画を選んでしまうことがあります。
そのため、テーブルのデータ量が大きく変わったタイミングで、統計情報を更新することが重要です。
各DBMSの統計情報更新コマンド
| DBMS | 統計情報更新コマンド |
|---|---|
| Oracle | EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE'); |
| Microsoft SQL Server | UPDATE STATISTICS TableName; |
| DB2 | RUNSTATS ON TABLE TableName; |
| MySQL | ANALYZE TABLE TableName; |
4. 実行計画を確認する
SQL文がどのように実行されるかは、**実行計画（Execution Plan）**を確認することでわかります。
各DBMSで実行計画を表示するためのコマンドは以下のとおりです。
各DBMSの実行計画確認コマンド
| DBMS | 実行計画コマンド |
|---|---|
| Oracle | EXPLAIN PLAN FOR SELECT * FROM users; |
| Microsoft SQL Server |
SET SHOWPLAN_ALL ON; → SELECT * FROM users;
|
| DB2 | EXPLAIN PLAN FOR SELECT * FROM users; |
| PostgreSQL | EXPLAIN ANALYZE SELECT * FROM users; |
| MySQL | EXPLAIN SELECT * FROM users; |
PostgreSQLの例
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'example@example.com';
出力例
Index Scan using index_users_on_email on users
(cost=0.29..8.30 rows=1 width=244)
(actual time=0.035..0.036 rows=1 loops=1)
5. チューニング例：インデックスの有無による差
SQLの実行速度は、インデックスの有無によって大きく異なります。ここでは具体例を使って、インデックスの効果を見てみましょう。
インデックスがない場合のクエリ
以下のように customer_id を指定して orders テーブルを検索するクエリがあります。
SELECT * FROM orders WHERE customer_id = 123;
Seq Scan on orders
インデックスを追加した場合
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
再実行時の実行計画
Index Scan using idx_orders_customer_id on orders
→ 該当行のみピンポイントでアクセスできるため高速化！
まとめ
- SQLのパフォーマンスは、オプティマイザが選ぶ実行計画によって大きく左右される。
- 実行計画の中身を読めるようになることで、ボトルネックの特定が可能になる。
- インデックス、統計情報、クエリの書き方を総合的に見直すことが大切。