【ITニュース解説】Indexing, Hashing & Query Optimization in liveSQL

データベースのパフォーマンスを劇的に向上させる強力なツールとして「インデックス」がある。インデックスは、膨大なデータの中から必要な情報を素早く見つけ出すための仕組みで、検索処理の高速化、つまり「クエリ最適化」に不可欠だ。このインデックスにはいくつかの種類があり、それぞれ得意な検索パターンがある。ここでは、具体的な学生のテーブルを例に、B-Treeインデックス、B+ Treeインデックス、ハッシュインデックスという主要なインデックスの仕組みと活用方法を解説する。

まず、解説の基礎となる「Students」という学生情報を管理するテーブルを作成する。このテーブルには、学生を識別するための固有の番号「ROLL_NO」、学生の名前「NAME」、所属する学科「DEPT」、そして成績評価の平均値である「CGPA」という4つの項目（カラム）がある。 テーブルを作成するためのSQL（Structured Query Language）は次のようになる。

Copy
1CREATE TABLE Students (
2    ROLL_NO NUMBER PRIMARY KEY,
3    NAME VARCHAR2(50),
4    DEPT VARCHAR2(20),
5    CGPA NUMBER(3,2)
6);

このCREATE TABLE文は、ROLL_NOが数値型でテーブル内で重複しない「PRIMARY KEY（主キー）」であることを示している。NAMEとDEPTはそれぞれ最大文字数が決まった文字列型、CGPAは小数点以下2桁までを持つ数値型だ。

次に、このStudentsテーブルに架空の学生データを20件挿入する。データ挿入はINSERT INTO文を使って次のように行う。

Copy
1INSERT INTO Students VALUES (101, 'Alice', 'CSBS', 8.5);
2INSERT INTO Students VALUES (102, 'Bob', 'ECE', 7.9);
3INSERT INTO Students VALUES (103, 'Charlie', 'MECH', 8.2);
4INSERT INTO Students VALUES (104, 'David', 'CIVIL', 7.0);
5INSERT INTO Students VALUES (105, 'Eva', 'CSBS', 9.0);
6INSERT INTO Students VALUES (106, 'Frank', 'EEE', 6.8);
7INSERT INTO Students VALUES (107, 'Grace', 'ECE', 8.3);
8INSERT INTO Students VALUES (108, 'Hank', 'MECH', 7.2);
9INSERT INTO Students VALUES (109, 'Ivy', 'CIVIL', 8.1);
10INSERT INTO Students VALUES (110, 'Jack', 'CSBS', 9.0);
11INSERT INTO Students VALUES (111, 'Kim', 'EEE', 7.5);
12INSERT INTO Students VALUES (112, 'Leo', 'CSBS', 9.2);
13INSERT INTO Students VALUES (113, 'Mia', 'MECH', 6.9);
14INSERT INTO Students VALUES (114, 'Nina', 'ECE', 8.7);
15INSERT INTO Students VALUES (115, 'Oscar', 'CSBS', 9.4);
16INSERT INTO Students VALUES (116, 'Paul', 'EEE', 7.8);
17INSERT INTO Students VALUES (117, 'Quinn', 'MECH', 8.0);
18INSERT INTO Students VALUES (118, 'Rose', 'CIVIL', 7.3);
19INSERT INTO Students VALUES (119, 'Sam', 'ECE', 8.8);
20INSERT INTO Students VALUES (120, 'Tina', 'CSBS', 9.1);

これらのデータは、異なる学科の学生が様々な成績を持っている状況を模している。これにより、様々な条件での検索がシミュレート可能となる。

データが準備できたところで、いよいよインデックスの作成と活用に進む。

B-Treeインデックス

B-Treeインデックスは、データベースで最も一般的に使われるインデックスの種類だ。その名前の通り、データを木の枝のように階層的に整理して格納する。この構造のおかげで、特定の値をピンポイントで探す「等値検索」や、ある範囲内のデータを探す「範囲検索」の両方に非常に高い性能を発揮する。B-Treeは、検索対象のデータが増えても検索効率が大きく低下しにくいという特徴がある。

ROLL_NOは学生を一意に識別する番号であるため、特定の学生を探す等値検索が頻繁に行われることが予想される。そこで、ROLL_NOカラムにB-Treeインデックスを作成する。Oracleデータベースでは、特別な指定がない限り、CREATE INDEX文で作成されるインデックスはデフォルトでB-Treeインデックスとなる。

Copy
1CREATE INDEX idx_rollno_btree ON Students(ROLL_NO);

このインデックスが作成されると、ROLL_NOを使って学生を検索するSQLクエリは劇的に高速になる。例えば、ROLL_NOが110の学生を探す場合、インデックスがない場合は20件全てのデータを一つずつ見ていく必要があるが、インデックスがあれば辞書で単語を探すように効率的に目的のデータにたどり着くことができる。

Copy
1SELECT * FROM Students WHERE ROLL_NO = 110;

このクエリを実行すると、ROLL_NOが110の学生（Jack, CSBS, 9.0）の情報を素早く取得できる。B-Treeインデックスは、このような主キーやユニークなIDなど、特定の値を指定して検索する際に特に有効なのだ。

B+ Treeインデックス

B+ Treeインデックスは、B-Treeインデックスの一種であり、特に範囲検索の性能をさらに高めることに特化した構造を持っている。B+ Treeでは、データの本体（リーフノードと呼ばれる最下層のノード）が全て連結されたリストとして保持されているため、一度目的のデータ範囲の始点を見つけ出すと、その後のデータを連続的に読み進めることができる。これにより、特定の範囲のデータを効率よく取得することが可能となる。

Oracleデータベースの通常のB-Treeインデックスは、内部的にはB+ Treeの特性も持ち合わせており、範囲スキャン（特定の範囲のデータを検索すること）においてB+ Treeのように振る舞う。CGPA（成績）のような数値データは、「CGPAが8.0より大きい学生」や「CGPAが7.0から9.0の範囲の学生」といった範囲検索で使われることが多いだろう。そこで、CGPAカラムにインデックスを作成する。

Copy
1CREATE INDEX idx_cgpa_bplus ON Students(CGPA);

このインデックスが作成されると、CGPAを使った範囲検索の性能が向上する。

Copy
1SELECT * FROM Students WHERE CGPA > 8.0;

このクエリは、CGPAが8.0を超える全ての学生の情報を効率的に取得する。B+ Treeインデックス（またはB+ Treeのように振る舞うB-Treeインデックス）は、数値の大小比較や日付の期間指定など、連続したデータの範囲を検索する際に非常に強力なツールとなる。

ハッシュインデックス（模擬）

ハッシュインデックスは、データを「ハッシュ関数」と呼ばれる特殊な計算式に通して得られる「ハッシュ値」に基づいてデータを格納するインデックスだ。ハッシュ関数は、入力されたデータから常に同じハッシュ値を生成し、異なる入力からは異なるハッシュ値を生成する（理想的には）。このハッシュ値を利用することで、特定の値を検索する「等値検索」を極めて高速に実行できる。

ハッシュインデックスは等値検索には非常に強いが、データの順序を保たないため、B-TreeやB+ Treeが得意とする範囲検索には向かないという特徴がある。

Oracleデータベースは、PostgreSQLのようにUSING HASHという直接的な構文でハッシュインデックスを作成することはできない。しかし、DEPT（学科名）のようなカラムは「'CSBS'という学科の学生」のように、特定の文字列と完全に一致するデータを検索する等値検索が主な用途となる。このような場合、通常のインデックス（B-Tree）をDEPTカラムに作成すると、データベースの内部処理でハッシュルックアップ（ハッシュ検索のような高速な処理）に似た効率で等値検索が行われることがある。

Copy
1CREATE INDEX idx_dept_hash ON Students(DEPT);

このインデックスがDEPTカラムに作成されると、特定の学科名の学生を検索するクエリは高速化される。

Copy
1SELECT * FROM Students WHERE DEPT = 'CSBS';

このクエリを実行すると、「CSBS」学科に所属する学生の情報を非常に素早く取得できる。ハッシュインデックス（またはそれに類する動作をするインデックス）は、学科名、商品コード、カテゴリ名など、特定の文字列や数値に完全に一致するデータを頻繁に検索する場合に最適な選択肢となる。

まとめると、B-Treeインデックスは、ROLL_NOのようなユニークなIDや数値の等値検索、およびある程度の範囲検索に優れている。B+ Treeインデックスは、CGPAのような数値範囲や日付範囲など、連続したデータの範囲検索に最も適している。そして、ハッシュインデックス（またはその挙動を模したインデックス）は、DEPTのような文字列の完全一致検索に特化しており、非常に高い性能を発揮する。データベースのクエリを最適化するには、どのような検索が頻繁に行われるかを理解し、それに合った適切なインデックスを選択することが重要だ。これにより、データベースアプリケーションのレスポンス速度を大幅に向上させることができる。