【ITニュース解説】🚀 Indexing, Hashing & Query Optimization in SQL (with Examples)

データベースを扱う上で、データの検索速度、つまりクエリのパフォーマンスは非常に重要だ。特にデータ量が増えてテーブルが大きくなると、必要な情報を見つけ出すのに時間がかかるようになる。これを解決し、データ取得を劇的に速くする方法として、「インデックス」と「ハッシュ」という技術がある。ここでは、これらの技術と、それらを活用したクエリ最適化の考え方について、具体的な例を交えながら見ていく。

まず、解説のために、学生の情報を管理するStudentsという名前のテーブルを作成する。このテーブルには、学生番号（roll_no）、氏名（name）、所属学科（dept）、成績評価点（cgpa）の4つの列がある。学生番号は各学生を識別する一意な値なので、主キーとして設定する。主キーに設定された列は、自動的にインデックスが作成されることが一般的だ。

Copy
1CREATE TABLE Students (
2    roll_no INT PRIMARY KEY,
3    name VARCHAR(50),
4    dept VARCHAR(10),
5    cgpa DECIMAL(3,2)
6);

このテーブルに、20件の学生データを挿入する。これにより、実際にインデックスがどのように機能するかを試すための十分なデータが用意できる。

Copy
1INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
2(101, 'Aarav', 'CSE', 8.5),
3(102, 'Diya', 'ECE', 7.9),
4(103, 'Rahul', 'MECH', 6.8),
5(104, 'Sneha', 'CSE', 8.9),
6(105, 'Karthik', 'EEE', 7.2),
7(106, 'Meera', 'CSBS', 9.1),
8(107, 'Arjun', 'CSE', 8.0),
9(108, 'Nisha', 'IT', 7.5),
10(109, 'Vikram', 'CSBS', 8.7),
11(110, 'Priya', 'ECE', 9.2),
12(111, 'Varun', 'MECH', 7.0),
13(112, 'Pooja', 'CSE', 8.6),
14(113, 'Rohit', 'EEE', 6.9),
15(114, 'Aditi', 'CSBS', 8.3),
16(115, 'Suresh', 'IT', 7.8),
17(116, 'Ishita', 'CSE', 9.0),
18(117, 'Manoj', 'ECE', 6.7),
19(118, 'Ananya', 'CSBS', 8.4),
20(119, 'Deepak', 'MECH', 7.1),
21(120, 'Krishna', 'EEE', 8.2);

この準備ができたところで、具体的なインデックスの種類を見ていこう。

最初に紹介するのは「B-Treeインデックス」だ。B-Treeは、MySQLやPostgreSQLといった多くのリレーショナルデータベース管理システム（RDBMS）で標準的に使われているインデックスの種類で、特に主キーのような一意な値を元にした正確なデータ検索（特定の学生番号の学生を探すなど）で非常に優れた性能を発揮する。B-Treeインデックスを作成すると、データベースはデータをツリー構造で効率的に整理し、目的のデータへ素早くたどり着けるようになる。

Studentsテーブルのroll_no列にB-Treeインデックスを明示的に作成するSQLは以下の通りだ。主キーには自動的にB-Treeインデックスが作成されることが多いため、この例は理解を深めるためのものだ。

Copy
1CREATE INDEX idx_rollno ON Students (roll_no);

このインデックスを作成した後、「roll_noが110の学生」を検索するクエリを実行すると、インデックスがない場合に比べてはるかに速く結果が得られる。

Copy
1SELECT *
2FROM Students
3WHERE roll_no = 110;

次に「B+ Treeインデックス」について説明する。B+ TreeもB-Treeと非常に似た構造を持つが、特にデータ範囲を指定するクエリ、例えば「成績評価点（cgpa）が8.0より大きい学生」を検索する場合などにその強みを発揮する。B+ Treeは、すべてのデータが葉ノードに集約されており、葉ノードが互いに連結されているため、範囲内のデータを連続して効率良く取得できる構造になっている。実際には、MySQLやPostgreSQLではB+ Treeインデックスも内部的にはB-Treeとして実装されることが多いが、概念的には範囲検索に適していると理解しておくと良い。

Studentsテーブルのcgpa列にB+ Treeインデックス（実質的にはB-Tree）を作成するSQLは以下のようになる。

Copy
1CREATE INDEX idx_cgpa ON Students (cgpa);

このインデックスが適用された後、「cgpaが8.0より大きい学生」を検索するクエリを実行すると、条件に合致するすべての学生が効率的に見つけられる。

Copy
1SELECT *
2FROM Students
3WHERE cgpa > 8.0;

最後に紹介するのは「ハッシュインデックス」だ。ハッシュインデックスは、特定の値と完全に一致するデータを検索する「等価検索」に最も適している。例えば、「deptが'CSBS'の学生」のように、ある列の値が完全に一致するものを探す場合に非常に高速だ。ハッシュ関数を使ってデータを格納するため、検索したい値が与えられると、直接そのデータがどこにあるかを計算して見つけ出すことができる。ただし、ハッシュインデックスはデータの範囲検索や部分一致検索には適していないという特性がある。

Studentsテーブルのdept列にハッシュインデックスを作成する（PostgreSQLの場合の）SQLは以下の通りだ。

Copy
1CREATE INDEX idx_dept ON Students USING HASH (dept);

このインデックスを作成した後、「deptが'CSBS'の学生」を検索するクエリを実行すると、該当する学生（Meera、Vikram、Aditi、Ananya）が迅速に取得される。

Copy
1SELECT *
2FROM Students
3WHERE dept = 'CSBS';

まとめると、B-Treeインデックスは、特定のプライマリキーなどを使った一意な値の正確な検索に最も適している。B+ Treeインデックス（実質的にはB-Treeインデックス）は、数値の範囲指定など、特定の範囲内のデータを検索する際に優れた性能を発揮する。そして、ハッシュインデックスは、列の値が完全に一致するデータを検索する等価検索で非常に高速に動作する。

このように、データベースのクエリパフォーマンスを最適化するためには、使用するクエリの種類や検索条件に応じて、適切な種類のインデックスを選択し、適用することが非常に重要だ。大量のデータを扱うシステムを設計・運用する際には、インデックス戦略がシステムの応答速度に大きく影響するため、これらの知識はシステムエンジニアにとって不可欠なものとなる。適切なインデックスを利用することで、ユーザーは快適にサービスを利用でき、システムは効率的に稼働するのだ。