はじめに
データベースには大量のデータが蓄積されていきます。データが増加するにつれて自然に発生する問題の一つは、検索速度に関する課題です。インデックスは、テーブルの操作速度を向上させる重要なデータ構造であり、DBメモリ内に一定のスペースを使用して保存されます。効果的なインデックス管理は、データベースのパフォーマンスを最適化するために不可欠です。
インデックスの特徴
- WHERE句に使用するカラムの効率化: インデックスは、主に検索条件に頻繁に使用されるカラムに設定されます。
- 単一および複合インデックス: インデックスは、1つのカラムまたは複数のカラムを組み合わせて設定できます。単一インデックスを複数設定したり、複数のカラムを組み合わせて複合インデックスを設定することができます。
-
過剰なインデックスは非効率的: インデックスを多く設定したからといって常に検索速度が向上するわけではありません。インデックスは追加のストレージを必要とし、データの更新時にインデックスも同時に更新されるため、パフォーマンスに悪影響を与える可能性があります。
検索時に頻繁に使用され、固有の値が多いカラムにインデックスを設定することが推奨されます。
- 更新および削除操作: WHERE句に適切に設定されたインデックスを使用すると検索パフォーマンスは向上しますが、更新および削除操作自体の速度が速くなるわけではありません。ただし、条件に合致するレコードを迅速に見つけることができるため、全体の作業効率は向上する可能性があります。
-
適切なインデックス数: テーブルあたり3～5個のインデックスが適当であり、以下の基準を考慮する必要があります。
- カーディナリティ: カラムが持つ固有の値の数が多いほど、インデックスの効率が高まります。
- 選択度: 特定のフィールド値を指定したときに選択されるレコード数をテーブル全体のレコード数で割った値であり、選択度が高いほどインデックス効率は低くなります。
- 利用度: インデックスが実際のクエリでどの程度頻繁に使用されるかを確認することが重要です。
- 重複度: 重複が少ない値を持つカラムにインデックスを設定することが効果的です。
また、インデックスを設計する際には、クエリパターンの分析とともにカーディナリティ、選択度、利用度、重複度を考慮し、複合インデックスを設定する際には頻繁に使用されるカラムを前方に配置することが重要です。インデックスを多く設定したからといって常に検索速度が向上するわけではなく、読み取り作業と書き込み作業の比率を考慮して適切なインデックス数を維持することが重要です。
インデックスが設定されたカラムを数式や関数で変形したり、クエリ内でデータ型の変換が発生したりすると、インデックスを活用できなくなります。たとえば、'20220112'のような値をTO_DATE('20220112','YYYYMMDD')に変換して使用する場合、インデックスが無視される可能性があります。このような場合には、Functional Based Indexの使用も検討できます。たとえば、年月日カラムから日付部分のみでインデックスを作成したい場合は、SUBSTR(カラム名, 7, 2)のように設定することができます。ただし、InsertやUpdate時にインデックスが更新されるため、IOパフォーマンスに影響を与える可能性がある点に注意が必要です。
また、NOT演算子やLIKE演算子を使用する場合、そしてOR条件を使用する場合、インデックスを効果的に活用できないことがあります。たとえば、LIKE '%pattern'のようにパターンの先頭部分にワイルドカード（%）がある場合、インデックスを使用できません。このような場合、クエリをUNION ALLで分割することが有効です。コード性カラムで否定形の条件を使用する必要がある場合は、code != '03'の代わりにcode IN ('01', '02', '04')のようにクエリを記述することで、データの分布に応じてインデックスを効果的に活用できるようにすることが推奨されます。
実際の例
Memo Entity
import lombok.*;
import javax.persistence.*;
@Entity
@Table(name="tbl_memo")
@ToString
@Getter
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Memo {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long mno;
@Column(length = 200, nullable = false)
private String memoText;
}
10万件のデータ挿入後のSELECTテスト
mysql> select count(*) from tbl_memo;
+----------+
| count(*) |
+----------+
| 100191 |
+----------+
1 row in set (0.01 sec)
テスト用サンプルデータの挿入
@Test
public void testInsertDummies(){
// 101 -> 100000個までダミーデータを生成
IntStream.rangeClosed(101,100000).forEach(i -> {
Memo memo = Memo.builder().memoText("Sample..."+i).build();
memoRepository.save(memo);
});
}
- インデックスの追加
CREATE INDEX <インデックス名> ON <テーブル名> (カラム名1, カラム名2...);
例:
CREATE INDEX memo_text ON tbl_memo (memo_text);
- インデックスの確認
SHOW INDEX FROM tbl_memo;
- インデックスの使用状況を確認
EXPLAIN SELECT * FROM tbl_memo WHERE memo_text = "Sample...2000";
- LIKE演算子使用時のインデックス利用状況を確認
EXPLAIN SELECT * FROM tbl_memo WHERE memo_text LIKE "Sample%";
(インデックスを適用しても、多くの行が対象となるため、インデックスの影響が小さいことが確認できます。)
インデックス設計時の考慮事項
インデックスを設計する際には、次の事項を考慮する必要があります。
- クエリパターンの分析: 頻繁に使用されるクエリのWHERE句、JOIN条件、ORDER BYなどを分析し、インデックスが必要なカラムを特定します。
- カーディナリティの考慮: 固有の値が多いカラムにインデックスを設定すると、検索効率が向上します。
- 複合インデックスの順序: 複合インデックスを設定する際には、頻繁に使用されるカラムを前方に配置することが推奨されます。
- インデックスの維持コスト: インデックスはデータの更新時に追加のコストが発生するため、読み取り作業と書き込み作業の比率を考慮して、適切なインデックス数を維持することが重要です。
- カバリングインデックス: 必要なすべてのカラムをインデックスに含めることで、クエリ実行時にテーブルを追加で読み込む必要がなくなります。これにより、検索パフォーマンスが大幅に向上します。
インデックスのモニタリングとメンテナンス
インデックスは一度設定したら終わりではなく、データベース
の使用パターンの変化に応じて定期的にモニタリングし、再設計する必要があります。
-
インデックス使用状況の確認:
SHOW INDEXコマンドを使用して、現在のインデックスの状態を確認し、実際のクエリでどの程度使用されているかをモニタリングします。 - 不要なインデックスの削除: 使用頻度が低い、またはパフォーマンスに寄与しないインデックスは削除し、ストレージを節約し、データの更新パフォーマンスを向上させます。
- インデックスの再構成: インデックスが断片化している場合、再構成を行うことでパフォーマンスを回復させることができます。
- クエリの最適化: クエリ自体を最適化し、インデックスをより効果的に活用できるよう調整します。
結論
インデックスは、データベースのパフォーマンスを最適化するための重要な要素の一つです。適切なインデックス設計により、検索パフォーマンスを大幅に向上させることができますが、過剰なインデックス設定は逆にパフォーマンスを低下させる可能性があります。したがって、クエリパターンを綿密に分析し、インデックスの効果を継続的にモニタリングし、必要に応じて調整することが重要です。MySQLが提供するさまざまなインデックスの種類や機能を理解し、適切に活用することで、効率的なデータベース運用を実現できます。
追加参考資料
この投稿が、DBインデックスの概念とその活用に関する理解を深めるのに役立つことを願っています。追加の質問やご意見がありましたら、コメントでお知らせください！