3分間データベース講座 第7回: データベースをもっと速く！～インデックスと性能改善の基本～

登場人物：

• チャット先生（通称:先生）： オブジェクト指向マスター。最近はデータベースの世界にも詳しい。

• ボット助手（通称:ボット君）： プログラミングを始めたばかりの若手。データベースはまだ未知の世界。

ボット君： 先生！SELECT文、すごく便利ですね！でも、データが増えてくると、検索が遅くなるんじゃないかと不安です。

先生： 良いところに気づいたね、ボット君。その通り、データが増えれば増えるほど、データベースの性能は重要になる。今日のテーマは、データベースの検索速度を劇的に向上させるための仕組み、「インデックス」だ。
これは、データの信頼性と同じくらい重要な概念だよ。

1. 探したい情報を瞬時に見つける～インデックスとは～

先生： インデックスとは、データベースの検索を高速化するために作られる「索引」のことだ。
本を想像してみてほしい。本の中から特定のキーワードを探すとき、ページを最初から最後まで読むのではなく、巻末の「索引」を見て、そのキーワードが何ページにあるかを確認するよね？

先生： データベースのインデックスも同じだ。
検索したい列（カラム）にあらかじめインデックスを作成しておくと、データベースはすべての行を一つずつ調べる（フルスキャンという）のではなく、このインデックスを使って、目的のデータがどこにあるかを瞬時に見つけられるんだ。

インデックスの作成例

-- usersテーブルのname列にインデックスを作成
CREATE INDEX idx_users_name ON users(name);

-- これで、name列を使った検索が高速化される
SELECT * FROM users WHERE name = '山田 太郎';

2. インデックスのメリットとデメリット

先生： インデックスは非常に便利だが、完璧な万能薬ではない。
メリットとデメリットを理解して、適切に使い分けることが重要だ。

メリット（索引を付けると速くなること）

• 検索速度の向上: WHERE句やJOINで指定する列にインデックスがあると、データの絞り込みが非常に速くなる。

• データのソートが速い: ORDER BY句によるデータの並べ替えも効率化される。

デメリット（索引を付けると遅くなること）

• データ書き込みの遅延: INSERT、UPDATE、DELETEなどのデータ書き込み操作を行うたびに、データ本体だけでなく、インデックスも更新する必要があるため、処理が遅くなる。

• ストレージ容量の消費: インデックス自体もデータなので、ディスク容量を消費する。

ボット君： じゃあ闇雲にインデックスをたくさん作ればいいわけじゃないんですね！
検索が多い列に絞って作る必要があるってことか！

先生： その通り！闇雲にインデックスを作るのは禁物だ。
インデックスは検索速度を上げるための魔法ではない。
どの列にインデックスを作るべきか、そしてインデックスを付けすぎるとどうなるか、といったトレードオフを理解することが重要だ。

3. SQLの性能を改善する基本的な考え方

先生： インデックスの他にも、SQLの性能を改善するための基本的なテクニックがいくつかある。

• SELECT *を避ける: 必要な列だけを具体的に指定することで、ネットワーク転送量やメモリ消費を減らすことができる。

• サブクエリを避ける: 可能であれば、サブクエリ（クエリの中にクエリを書くこと）よりもJOINを使ってデータを結合した方が、多くの場合で高速に実行できる。

• インデックスが使われる条件で検索する: WHERE column = '...' のような形式はインデックスが使われやすいが、WHERE SUBSTRING(column, 1, 3) = '...' のように関数を挟むと、インデックスが使われずフルスキャンになることがある。

4. クエリ実行計画の賢い選択～ルールベース vs. コストベース～

先生： データベースは、クエリが実行されたときに「どうすれば最も効率よくデータを取得できるか」を自動的に判断する。
この判断方法には、大きく分けて2つのアプローチがあるんだ。

• ルールベース・オプティマイザ (RBO):
  事前に決められたルールに従って、実行計画を決定する。
  例えば、「WHERE句にインデックスが付いている列があれば必ず使う」といった単純なルールで判断するため、データの偏りなどは考慮しない。

• コストベース・オプティマイザ (CBO):
  データの量や偏りといった統計情報を基に、複数の実行計画を比較し、最もコスト（処理時間やCPU消費など）が低いものを選択する。
  これが現在の主流だ。

ルールベース vs. コストベースの具体的な例

先生： 例えば、ECサイトのproductsテーブルに以下の2つのインデックスがあったとしよう。

ここで、以下のようなクエリを発行したとする。

SELECT * FROM products 
　WHERE category_id = 1 
    AND price > 10000;

先生： このクエリに対して、RBOとCBOはそれぞれどう動くと思う？

• RBOの場合:
  RBOはデータの偏りを考慮しない。
  代わりに、条件文の形式を見て判断する。
  category_id = 1という等号（=）条件は、price > 10000という範囲（>）条件よりも、より厳密にデータを絞り込める可能性が高いと判断する。
  
  そのため、先に指定されているcategory_idのインデックス（インデックスA）を使ってデータを絞り込む、という計画を立てるだろう。
  

• CBOにの場合:
  CBOは、まずデータの統計情報を確認する。

  • もし、category_id = 1のデータが全体の90%を占めていたら、「この条件で絞り込んでもあまり効率が良くないな…」と判断する。
    

  • 一方で、price > 10000のデータが全体の2%しかなかったら、
    「priceのインデックス（インデックスB）を使ったほうが、圧倒的に早くデータを見つけられる！」と判断し、インデックスBを優先して使う、という計画を立てるんだ。

このように、CBOはデータの「現実の状況」を考慮して、最も賢い方法を自動的に選んでくれるんだ。

統計情報の更新がなぜ重要か？

先生： CBOは「統計情報」を頼りに最適な実行計画を立てる。
つまり、統計情報が古くなると、間違った判断をしてしまう可能性がある。

例えば、price > 10000の商品は元々2%しかなかったのに、最近で急に増えて50%になっていたとする。
古い統計情報しか持っていないCBOは、相変わらず「priceのインデックスが効率的だ」と判断し続ける。
しかし、実際にはこのインデックスを使ってもデータの絞り込み効率は悪くなってしまい、かえって遅くなる可能性があるんだ。

先生： だからこそ、データが頻繁に更新されるテーブルでは、統計情報を定期的に更新する運用が必要になる。
統計情報の更新は、日次、週次、月次など、データ更新の頻度に合わせて計画的に行うのが一般的だ。

5. 複合インデックスとWHERE句の振る舞い

先生： ここで、インデックスの中でも特に重要な「複合インデックス」と、複雑なWHERE句での動作について、さらに詳しく見ていこう。

複合インデックスは、複数の列を組み合わせたインデックスだ。

例えば、ECサイトのordersテーブルで「ユーザーID」「注文日時」「ステータス」の順で複合インデックスを作成した場合、インデックスが有効に機能するのは、WHERE句の条件がインデックスの順番に沿って、左端から連続して指定されている場合に限られる。

この原則を「左端プレフィックス（leftmost prefix）ルール」という。

ボット君： WHERE句に
　　　user_id = 123 AND order_date = '2023-10-26'
と書けば、インデックスはuser_idとorder_dateまで効くってことですね！

先生： その通り。では、少し複雑なパターンで考えてみよう。

複雑なWHERE句の例：

user_id、order_date、statusの複合インデックスがある状態で、以下のようなクエリを発行した場合、インデックスはどこまで使われると思う？

SELECT * FROM orders
WHERE user_id = 123 
　AND order_date BETWEEN '2023-10-01' AND '2023-10-31' 
  AND status = 'shipped';

先生： 結論から言うと、この場合、
インデックスはuser_idとorder_dateまでしか有効に機能しないんだ。

その理由は、
order_dateに範囲指定（BETWEEN）があるため、
データベースはuser_idとorder_dateまではインデックスを使って効率的に絞り込むことができる。

しかし、範囲指定の後ろに続くstatusの条件は、user_idとorder_dateで絞り込まれた結果に対して再度フィルタリングする必要があるため、インデックスをフル活用できないんだ。

ボット君： 範囲指定でも、インデックスの連続性が途切れてしまうんですね！
これはインデックス設計で気をつけないと...。

先生： まさにその通り。複雑なWHERE句を書く際は、どのインデックスがどう使われるかを意識することが、性能改善の鍵にろ、らなる。

まとめと次回の予告

先生： 今日のまとめだ。

• インデックス: データベースの検索速度を高速化するための索引。

• インデックス設計のトレードオフ: インデックスは検索を速くするが、書き込みを遅くし、容量を消費する。このバランスを考えることが重要だ。

• オプティマイザ: クエリの実行計画を決定する賢い仕組み。
  現在はコストベースが主流。

• 性能改善の基本: 闇雲にインデックスを作るのではなく、SELECT *を避けたり、JOINをうまく使ったりすることが大切。

• 複合インデックスのルール: 左端から連続した列にしか適用されない。
  OR句や範囲指定などが含まれると、インデックスの適用が途切れる可能性がある。

データベースの性能改善は奥が深い分野だが、今日の話はとても重要だ。
データベースのパフォーマンスがアプリケーション全体のユーザー体験に直結することも少なくないからね。

先生： 次回は、データベースにおける万が一に備える「バックアップと障害対策」について学んでいこう。

次回：第8回: DBはいつ壊れる？どう守る？～バックアップと障害対策～

#データベース #SQL #インデックス #性能改善 #クエリ最適化 #フルスキャン