6日目: パフォーマンス改善の鍵！インデックス設計とチューニングの基本
はじめに：なぜデータベースは遅くなるのか？
昨日までで、SQLの基本的なクエリを学び、データを自在に操作する力を手に入れました。しかし、データベースに数百万、数千万件のデータが蓄積されていくと、これまで数秒で終わっていたクエリが、突然数十秒、数分かかるようになることがあります。これは、データの量が増えることで、データベースが目的のデータを探し出すのに時間がかかるようになるためです。
6日目の今日は、データベースのパフォーマンスを改善するための最も重要な手段である「インデックス」の設計と、SQLチューニングの基本について解説します。これは、データエンジニアとして最も価値を発揮できるスキルのひとつです。
1. インデックスの仕組みを再確認する
2日目の記事で触れたように、インデックスは、書籍の「索引」のようなものです。目的のデータがどこにあるかを素早く見つけるための目次を提供し、検索処理を高速化します。
インデックスがない場合、データベースはテーブルの全データを先頭から順に読み込んで条件に合う行を探します。これをフルスキャンと呼びます。データ量が多い場合、このフルスキャンは非常に非効率です。
一方で、インデックスがある場合、データベースはまずインデックスを検索し、目的のデータが格納されている場所（行の物理的なアドレス）を特定します。これにより、必要な行だけを直接読み取ることができ、検索速度が飛躍的に向上します。
【インデックスの内部構造：B-Tree】
ほとんどのリレーショナルデータベースで使われているインデックスの構造は「B-Tree（Balanced Tree）」と呼ばれるものです。これは、木（ツリー）の形をしたデータ構造で、バランスが保たれているため、どのデータを探す場合でも検索時間がほぼ一定に保たれるという特徴があります。
B-Treeは、検索したいデータの値に応じて、どの枝に進むべきかを決定します。これにより、テーブル全体をスキャンすることなく、目的のデータが格納されている場所に素早くたどり着くことができます。
例えば、employeesテーブルのlast_name列にB-Treeインデックスを作成した場合、WHERE last_name = '田中'というクエリを実行すると、データベースはB-Treeを辿って「田中」というデータがどの行にあるかを数回のアクセスで特定できます。
2. インデックスを設計する上でのポイント
インデックスはパフォーマンス改善の特効薬ですが、闇雲に作成すれば良いわけではありません。設計にはいくつかの重要なポイントがあります。
a. インデックスを作成すべき列
-
WHERE句で頻繁に指定される列: 検索条件として使われる列にインデックスを付けるのが最も効果的です。 -
JOIN句の条件列: 複数のテーブルを結合する際に使われる外部キーの列には、インデックスを付けるべきです。 -
ORDER BYやGROUP BYで使われる列: ソートやグループ化の処理も高速化できます。 - カーディナリティ（データの多様性）が高い列: 顧客IDやメールアドレスのように、重複が少なく、値の種類が多い列はインデックスの効果が大きいです。
b. インデックスを作成すべきでない列
- カーディナリティが低い列: 性別（男、女）のような、値の種類が極端に少ない列は、インデックスの効果が薄いです。フルスキャンの方が早い場合もあります。
- 更新や挿入が頻繁な列: インデックスはデータの更新時に一緒にメンテナンスされるため、書き込み処理のオーバーヘッドが増加します。
- データ量が少ないテーブル: 数百行程度の小さなテーブルでは、インデックスによるメリットはほとんどありません。
【複合インデックスの設計】
複数の列を組み合わせてインデックスを作成する「複合インデックス」は、複数の条件で検索する場合に非常に有効です。ただし、作成する列の順序が重要になります。
例えば、WHERE last_name = '山田' AND first_name = '太郎'というクエリが多い場合、(last_name, first_name)という順序で複合インデックスを作成すると、このクエリはインデックスを効率的に利用できます。
しかし、このインデックスはWHERE first_name = '太郎'というクエリでは利用できません。なぜなら、インデックスは左から右の順序でソートされているため、last_nameという最初の列が指定されないと、インデックスを辿って検索することができないからです。
複合インデックスを設計する際は、頻繁に使われる検索条件の組み合わせと、その順序を考慮する必要があります。
3. SQLチューニングの第一歩：実行計画を理解する
クエリが遅いと感じたとき、まずやるべきことは、そのクエリがどのように実行されているかを調べることです。この「実行方法の設計図」を**実行計画（Execution Plan）**と呼びます。
実行計画を見ることで、データベースがインデックスを使っているのか、それともフルスキャンを行っているのか、複数のテーブルをどのように結合しているのか、といった詳細な情報を確認できます。
【Oracleでの実行計画の確認】
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE last_name = 'Smith';
実行計画を確認するコマンドを実行した後、DBMS_XPLAN.DISPLAYなどのパッケージを使って結果を表示します。
【MySQLでの実行計画の確認】
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';
EXPLAINコマンドを実行すると、以下のような情報が出力されます。
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 10000 | 100.00 | Using where |
-
type: データのアクセス方法を示します。ALLはフルスキャン、refやeq_refはインデックスが使われていることを意味します。 -
key: 実際に使われたインデックス名を示します。 -
rows: データベースが読み取った行数のおおよその数です。
実行計画を見ることで、**「なぜこのクエリは遅いのか」**という根本的な原因を特定できます。例えば、「インデックスがあるのに使われていない」「意図しないフルスキャンが発生している」といった問題を発見できます。
4. インデックスを活かすためのSQL記述方法
インデックスを正しく設計しても、SQLの書き方によってはインデックスが使われないことがあります。
a. ワイルドカード検索（LIKE '%...）に注意
LIKE句で検索文字列の先頭に%を付けると、インデックスが使われずフルスキャンになる可能性が高いです。
-
悪い例:
SELECT * FROM employees WHERE last_name LIKE '%田%'; -
良い例:
SELECT * FROM employees WHERE last_name LIKE '田%';
b. 型変換に注意
WHERE句で、インデックスが設定されている列に変換関数を使うと、インデックスが使われなくなることがあります。
-
悪い例:
SELECT * FROM employees WHERE TO_CHAR(hire_date, 'YYYY') = '2024'; -
良い例:
SELECT * FROM employees WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31';
c. OR演算子に注意
OR演算子で複数の条件を指定する場合、両方の条件でインデックスが使われないと、クエリ全体のパフォーマンスが低下する可能性があります。
-
悪い例:
SELECT * FROM employees WHERE last_name = 'Smith' OR salary > 100000;-
last_nameにはインデックスがあっても、salaryにインデックスがない場合、salaryの条件を満たすためにフルスキャンが発生する可能性があります。
-
5. その他、知っておきたいチューニング手法
インデックス設計以外にも、パフォーマンスを改善するための手法は多数あります。
- 統計情報の更新: データベースは、統計情報（テーブルの行数、インデックスのカーディナリティなど）を使って実行計画を立てます。データが大きく変動した場合は、統計情報を手動で更新することで、より効率的な実行計画が選択されることがあります。
- パーティショニング: 巨大なテーブルを、特定の条件（日付、地域など）で小さなテーブルに分割する手法です。これにより、クエリが特定のパーティション（分割されたテーブル）だけを対象にでき、検索効率が向上します。
- キャッシュの活用: 頻繁にアクセスされるデータをメモリにキャッシュしておくことで、ディスクI/Oを減らし、高速な応答を実現します。
- アプリケーション側のチューニング: SQLの書き方だけでなく、アプリケーションのアーキテクチャやデータベース接続プーリングの設定など、アプリケーション側でのチューニングも重要です。
まとめ：チューニングは終わりなき探求
インデックス設計とチューニングは、データベースのパフォーマンスを維持・向上させる上で不可欠なスキルです。今日解説したポイントは、その中でも最も基本となる部分です。
データベースはシステムを支える心臓部であり、そのパフォーマンスを改善する力は、AI企業のようなデータ駆動型の組織で特に重宝されます。
明日は、これまでの1週間の学習内容を総まとめし、データベースの運用・管理におけるベストプラクティスについて解説します。
- 明日（7日目）: 1週間の総復習！データベースの運用・管理におけるベストプラクティス