【ITニュース解説】🚀 Indexing, Hashing & Query Optimization in SQL (Tutorial Example on Students Table)

データベースシステムにおいて、データを効率的に検索し、必要な情報を素早く取り出すことは非常に重要である。もしテーブルに何千、何百万ものデータが格納されている場合、毎回すべてのデータを最初から最後まで順番に調べていては、データの取得に膨大な時間がかかってしまう。このような問題を解決し、データベースの検索性能を劇的に向上させる技術が「インデックス」と「ハッシュ」だ。これらは、まるで分厚い教科書の巻末にある索引や、辞書の特定の単語を素早く見つけるための仕組みのように機能する。データベースに適切なインデックスを設定することで、特定のデータを探す際にすべての行をスキャンするのではなく、必要なデータに直接「ジャンプ」できるようになるのだ。

インデックスにはいくつかの種類があり、それぞれ得意な検索パターンがある。今回紹介するのは主に三つのタイプだ。一つ目は「B-Treeインデックス」で、これは特定の値をぴったり一致させる「等値検索」や、「〜より大きい」「〜より小さい」といった範囲を指定する「範囲検索」に非常に優れている。例えば、特定の学生の学籍番号を検索したり、ある点数範囲内の学生を検索したりする場合に有効だ。二つ目は「B+ Treeインデックス」で、これはB-Treeをさらに発展させたもので、特にデータが連続して並んでいる場合や、広範囲のデータを効率的に検索するのに適している。すべてのデータが木の「葉」の部分に格納される構造のため、順番にデータを読み進めるようなクエリで高い性能を発揮する。そして三つ目は「ハッシュインデックス」だ。これは「ハッシュ関数」という特殊な計算を使って、検索したい値に対応するデータの位置を瞬時に割り出すことができるため、等値検索においては非常に高速な性能を誇る。ただし、範囲検索には適していないという特徴がある。

これらのインデックスがどのように機能し、データベースの性能にどう影響するかを具体的に見ていこう。まず、チュートリアルでは「College」というデータベースの中に「Students」というテーブルを作成する。このテーブルは、学生の情報を管理するためのもので、学籍番号（roll_no）、名前（name）、所属学科（dept）、成績（cgpa）といった項目を持つ。学籍番号は各学生を一意に識別するための「主キー」として設定され、このテーブルを使って様々なクエリとインデックスの効果を試していく。

テーブルを作成したら、次にサンプルとして20件の学生データを挿入する。これらのデータは、様々な学科や成績の学生が含まれるように工夫されており、等値検索や範囲検索のデモンストレーションに適している。

最初のインデックスとして、学籍番号（roll_no）の列に「B-Treeインデックス」を作成する。B-Treeインデックスは、学籍番号のような一意の識別子に対する等値検索に最適であり、学籍番号を指定して特定の学生の情報を検索する際に、データベースはすべての行をスキャンすることなく、このインデックスを使って素早く該当するデータを見つけ出すことができる。これにより、検索速度は格段に向上する。

次に、成績（cgpa）の列に「B+ Treeインデックス」を作成する。B+ Treeインデックスは、特に成績が「8.0より大きい」といった範囲を指定するクエリに対して非常に有効だ。このインデックスを利用することで、データベースは特定のCGPA値の範囲に該当するレコードのみを効率的に探し出し、テーブル全体をスキャンする手間を省くことができる。これは、連続したデータや範囲検索に強いB+ Treeの特性によるものだ。

さらに、所属学科（dept）の列には「ハッシュインデックス」を作成する。ハッシュインデックスは、特定の学科（例えば「CSBS」）の学生を検索するような等値検索において、驚くほど高速な検索性能を発揮する。ハッシュ関数によって、検索したい学科名がどのデータの位置に対応するかを瞬時に計算し、直接そのデータにアクセスするため、非常に効率が良いのだ。ただし、先にも述べたように、「CSEまたはCSBSの学生」といった範囲検索や、「CSEより学籍番号が大きい」といった比較を伴う検索には向いていないことに注意が必要だ。

これらのインデックスを作成した後、実際にクエリの性能がどのように変化したかを確認するために、「EXPLAIN」というコマンドを使ってクエリの実行計画をテストする。EXPLAINコマンドは、データベースがどのようにクエリを実行するか、どのような順序で、どのインデックスを使うかといった詳細な計画を表示してくれる。インデックスを作成する前は、通常「フルテーブルスキャン」（全行スキャン）が行われていることが示されるが、インデックスを作成した後は、データベースが適切に作成されたインデックスを利用していることが確認できる。これにより、データ検索が高速化されている様子を具体的に理解できるだろう。

まとめると、インデックスとハッシュは、データベースのクエリ性能を最適化するために不可欠なツールである。B-Treeインデックスは個別のレコード検索や範囲検索に役立ち、B+ Treeインデックスは広範囲のデータを効率的に取得するのに優れている。そして、ハッシュインデックスは等値検索において非常に高速な検索を提供する。適切な種類のインデックスを、適切な列に設定することで、データベースの検索時間を劇的に短縮し、大量のデータを扱うシステムでも快適な操作性を実現できる。データベース設計を行う際には、どのようなクエリが頻繁に実行されるかを考慮し、それらのクエリパターンに最適なインデックスを選択することが、システム全体のパフォーマンスを最大化する鍵となる。