データベース

データベースのインデックス設計とは？パフォーマンス最適化の基礎

公開日: 2024年2月20日著者: mogura

疑問

データベースのインデックスを設計する際、どのような原則に従えばよいのでしょうか？パフォーマンス最適化の基礎について一緒に学んでいきましょう。

導入

インデックスは、データベースのパフォーマンスを大幅に向上させる重要な要素です。適切なインデックス設計により、クエリの実行時間を短縮し、システム全体のパフォーマンスを向上させることができます。

本記事では、インデックスの基本概念から、設計原則、パフォーマンス最適化まで、実践的な例とともに詳しく解説していきます。

インデックス設計のイメージ

解説

1. インデックスとは

インデックスは、データベースの検索速度を向上させるためのデータ構造です。本の索引のように、特定のデータを素早く見つけることができます。適切なインデックス設計により、クエリの実行時間を大幅に短縮できます。

インデックスの仕組み

インデックスは、テーブルの特定の列に対して作成され、その列の値と行の位置を対応付けます。クエリが実行されると、データベースはまずインデックスを検索し、該当する行の位置を特定してから、実際のデータを取得します。これにより、全件スキャン（フルテーブルスキャン）を避けることができ、検索速度が大幅に向上します。

インデックスの効果

この例では、インデックスなしの場合とありの場合の検索速度の違いを示しています。インデックスを作成することで、検索速度が大幅に向上します。

-- インデックスなしの場合: 全件スキャン（遅い）
SELECT * FROM users WHERE email = 'yamada@example.com';
-- 実行時間: 100ms（100万行の場合）

-- インデックスありの場合: インデックススキャン（速い）
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'yamada@example.com';
-- 実行時間: 1ms（100万行の場合）

2. インデックスの種類

インデックスには、B-Treeインデックス、ハッシュインデックス、全文検索インデックスなど、様々な種類があります。それぞれ異なる特性を持ち、用途に応じて適切な種類を選択することが重要です。

B-Treeインデックス（最も一般的）

B-Treeインデックスは、MySQL、PostgreSQL、SQLiteなど、ほとんどのリレーショナルデータベースで使用されています。等価検索、範囲検索、ソートなど、様々なクエリに適しています。データがソートされた状態で保存されるため、範囲検索やソートが効率的です。

ハッシュインデックス

ハッシュインデックスは、等価検索（=）に非常に高速ですが、範囲検索（<、>、BETWEEN）やソートには使用できません。MySQLのMEMORYストレージエンジンやPostgreSQLのハッシュインデックスで使用されます。

全文検索インデックス

全文検索インデックスは、テキストデータ内の単語を検索するために使用されます。MySQLのFULLTEXTインデックスやPostgreSQLのGIN/GiSTインデックスが代表例です。部分一致検索や複合語検索に適しています。

インデックスの種類と作成方法

これらの例では、様々な種類のインデックスの作成方法を示しています。B-Treeインデックスは最も一般的で、ハッシュインデックスは等価検索に、全文検索インデックスはテキスト検索に適しています。

-- B-Treeインデックスの作成（デフォルト）
CREATE INDEX idx_email ON users(email);

-- ハッシュインデックスの作成（PostgreSQL）
CREATE INDEX idx_email_hash ON users USING HASH(email);

-- 全文検索インデックスの作成（MySQL）
CREATE FULLTEXT INDEX idx_content ON posts(content);

-- 全文検索の実行（MySQL）
SELECT * FROM posts
WHERE MATCH(content) AGAINST('データベース' IN NATURAL LANGUAGE MODE);

-- 全文検索インデックスの作成（PostgreSQL）
CREATE INDEX idx_content_gin ON posts USING GIN(to_tsvector('japanese', content));

-- 全文検索の実行（PostgreSQL）
SELECT * FROM posts
WHERE to_tsvector('japanese', content) @@ to_tsquery('japanese', 'データベース');

3. インデックスの設計原則

適切なインデックス設計により、クエリのパフォーマンスを大幅に向上させることができます。WHERE句、JOIN、ORDER BYで使用されるカラムにインデックスを作成することが基本原則です。

1. WHERE句で頻繁に使用されるカラム

WHERE句で使用されるカラムにインデックスを作成することで、条件に一致する行を素早く見つけることができます。特に、等価検索（=）や範囲検索（<、>、BETWEEN）で使用されるカラムにインデックスを作成することが重要です。

2. JOINで使用されるカラム

JOINで使用されるカラムにインデックスを作成することで、結合処理が高速化されます。外部キーには通常インデックスが作成されますが、明示的に確認することが重要です。

3. ORDER BYで使用されるカラム

ORDER BYで使用されるカラムにインデックスを作成することで、ソート処理が高速化されます。インデックスは既にソートされた状態で保存されているため、追加のソート処理が不要になります。

4. カバリングインデックス

カバリングインデックスは、SELECT句で取得するカラムをすべて含むインデックスです。これにより、テーブルにアクセスせずにインデックスから直接データを取得できるため、パフォーマンスが大幅に向上します。

インデックス設計の例

これらの例では、様々な用途に応じたインデックスの作成方法を示しています。WHERE句、JOIN、ORDER BYで使用されるカラムにインデックスを作成し、カバリングインデックスを活用することで、パフォーマンスを向上させます。

-- WHERE句で使用されるカラムにインデックス
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'yamada@example.com';

-- JOINで使用されるカラムにインデックス
CREATE INDEX idx_user_id ON orders(user_id);
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.email = 'yamada@example.com';

-- ORDER BYで使用されるカラムにインデックス
CREATE INDEX idx_created_at ON posts(created_at);
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

-- カバリングインデックス（SELECT句のカラムを含む）
CREATE INDEX idx_user_covering ON users(id, name, email);
SELECT id, name, email FROM users WHERE id = 1;
-- テーブルにアクセスせずにインデックスから直接取得

4. 複合インデックスの設計

複合インデックスは、複数のカラムを組み合わせたインデックスです。カラムの順序が重要で、カーディナリティの高いカラムを先に配置することで、より効率的なインデックスを設計できます。

カラムの順序が重要

複合インデックスは、左端のカラムから順に使用されます。例えば、インデックス（A, B, C）がある場合、WHERE A = ? や WHERE A = ? AND B = ? では使用できますが、WHERE B = ? や WHERE C = ? だけでは使用できません。

カーディナリティの高いカラムを先に

カーディナリティの高いカラムを先に配置することで、インデックスの効率が向上します。例えば、ユーザーID（高カーディナリティ）とステータス（低カーディナリティ）の組み合わせでは、ユーザーIDを先に配置します。

複合インデックスの設計例

これらの例では、複合インデックスの作成方法と、カラムの順序の重要性を示しています。左端のカラムから順に使用されるため、クエリパターンに応じて適切な順序を選択することが重要です。

-- 複合インデックスの作成
CREATE INDEX idx_user_status ON orders(user_id, status);

-- このインデックスが使用されるクエリ
SELECT * FROM orders WHERE user_id = 1; -- OK（左端のカラム）
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending'; -- OK
SELECT * FROM orders WHERE status = 'pending'; -- NG（左端のカラムがない）

-- カーディナリティの高いカラムを先に
-- user_id（高カーディナリティ）を先に
CREATE INDEX idx_user_status ON orders(user_id, status);

-- カバリングインデックスとしても使用
CREATE INDEX idx_user_covering ON orders(user_id, status, created_at, total_amount);
SELECT user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 1 AND status = 'pending';
-- テーブルにアクセスせずにインデックスから直接取得

5. インデックスのパフォーマンス影響

インデックスは検索速度を向上させますが、INSERT、UPDATE、DELETEの速度を若干低下させ、ストレージを消費します。メリットとデメリットを理解し、適切なバランスを取ることが重要です。

メリット

検索速度の向上: WHERE句、JOIN、ORDER BYなどの処理が高速化されます。全件スキャンを避けることで、検索時間を大幅に短縮できます。
ソート処理の高速化: ORDER BYで使用されるカラムにインデックスがある場合、追加のソート処理が不要になります。
ユニーク制約の実現: ユニークインデックスにより、データの重複を防ぐことができます。

デメリット

ストレージの消費: インデックスは追加のストレージを消費します。特に複合インデックスや全文検索インデックスは、多くのストレージを消費する可能性があります。
更新処理の速度低下: INSERT、UPDATE、DELETEの際に、インデックスも更新する必要があるため、処理速度が若干低下します。
メンテナンスの必要性: データが増減する際に、インデックスの再構築が必要になる場合があります。

6. インデックスの最適化

インデックスの最適化は、EXPLAINで実行計画を確認し、不要なインデックスを削除することで実現できます。定期的にパフォーマンスを監視し、必要に応じてインデックスを調整することが重要です。

EXPLAINで実行計画を確認

EXPLAINを使用することで、クエリがどのように実行されるかを確認できます。インデックスが使用されているか、フルテーブルスキャンが発生していないかを確認し、必要に応じてインデックスを追加または修正します。

不要なインデックスの削除

使用されていないインデックスは、ストレージを消費し、更新処理を遅くするだけです。定期的にインデックスの使用状況を確認し、不要なインデックスを削除します。

インデックスの最適化方法

これらの例では、EXPLAINを使用して実行計画を確認し、インデックスの使用状況を確認する方法を示しています。実行計画を確認することで、インデックスが適切に使用されているかを判断できます。

-- EXPLAINで実行計画を確認（MySQL）
EXPLAIN SELECT * FROM users WHERE email = 'yamada@example.com';

-- 結果の例:
-- id | select_type | table | type  | possible_keys | key      | rows | Extra
-- 1  | SIMPLE      | users | ref   | idx_email     | idx_email| 1    | NULL
-- type: ref = インデックスが使用されている
-- type: ALL = フルテーブルスキャン（インデックスが使用されていない）

-- EXPLAINで実行計画を確認（PostgreSQL）
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'yamada@example.com';

-- インデックスの使用状況を確認（MySQL）
SELECT 
  TABLE_NAME,
  INDEX_NAME,
  SEQ_IN_INDEX,
  COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'mydb'
  AND TABLE_NAME = 'users';

-- 不要なインデックスの削除
DROP INDEX idx_unused ON users;

7. 実践的な設計例

実践的なプロジェクトでのインデックス設計例を通じて、適切なインデックス設計の方法を学びます。電子商取引システムを例に、実際のクエリパターンに基づいたインデックス設計を示します。

電子商取引システム

電子商取引システムでは、ユーザー情報、商品情報、注文情報などのテーブルがあります。ユーザーID、商品ID、注文日時など、頻繁に検索されるカラムにインデックスを作成します。また、複合インデックスを使用して、複数の条件で検索するクエリを最適化します。

電子商取引システムのインデックス設計

この例では、電子商取引システムでのインデックス設計を示しています。WHERE句、JOIN、ORDER BYで使用されるカラムにインデックスを作成し、複合インデックスとカバリングインデックスを活用しています。

-- 電子商取引システムのテーブル設計例

-- ユーザーテーブル
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 商品テーブル
CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  category_id INT NOT NULL,
  stock INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 注文テーブル
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- インデックスの設計
-- 1. WHERE句で使用されるカラム
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_product_category ON products(category_id);
CREATE INDEX idx_order_user ON orders(user_id);
CREATE INDEX idx_order_status ON orders(status);

-- 2. 複合インデックス（よく使用されるクエリパターンに基づく）
CREATE INDEX idx_order_user_status ON orders(user_id, status);
CREATE INDEX idx_order_user_date ON orders(user_id, created_at);

-- 3. ORDER BYで使用されるカラム
CREATE INDEX idx_product_created ON products(created_at);
CREATE INDEX idx_order_created ON orders(created_at);

-- 4. カバリングインデックス
CREATE INDEX idx_order_covering ON orders(user_id, status, created_at, total_amount);

-- よく使用されるクエリの例
-- ユーザーの注文一覧（ステータス別）
SELECT id, status, created_at, total_amount
FROM orders
WHERE user_id = 1 AND status = 'pending'
ORDER BY created_at DESC;
-- idx_order_user_status または idx_order_covering が使用される

-- カテゴリ別の商品一覧（価格順）
SELECT id, name, price
FROM products
WHERE category_id = 1
ORDER BY price ASC;
-- idx_product_category が使用される

8. パーティションとインデックス

パーティションは、大きなテーブルを複数の小さなテーブルに分割する機能です。パーティションとインデックスを組み合わせることで、大規模データの管理とクエリパフォーマンスを向上させることができます。

パーティションインデックス

パーティションされたテーブルでは、各パーティションごとにインデックスが作成されます。パーティションキーを含むインデックスを作成することで、パーティションプルーニング（不要なパーティションをスキップ）が可能になり、クエリパフォーマンスが向上します。

パーティションとインデックスの使用例

これらの例では、パーティションされたテーブルの作成方法と、パーティションキーを含むインデックスの作成方法を示しています。パーティションプルーニングにより、不要なパーティションをスキップしてクエリパフォーマンスが向上します。

-- パーティションされたテーブルの作成（MySQL）
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- パーティションキーを含むインデックス
CREATE INDEX idx_order_user_date ON orders(user_id, created_at);

-- パーティションプルーニングが発生するクエリ
SELECT * FROM orders
WHERE user_id = 1 AND created_at >= '2024-01-01' AND created_at < '2024-12-31';
-- p2024パーティションのみが検索される

-- パーティションされたテーブルの作成（PostgreSQL）
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2022 PARTITION OF orders
  FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE orders_2023 PARTITION OF orders
  FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

9. ベストプラクティス

インデックス設計のベストプラクティスに従うことで、効率的なデータベースを構築できます。適切なカラムにインデックスを作成し、定期的に最適化を行うことが重要です。

必要なカラムにのみインデックスを作成: WHERE句、JOIN、ORDER BYで頻繁に使用されるカラムにのみインデックスを作成します。過度なインデックスは、ストレージと更新速度に悪影響を与えます。
複合インデックスの順序を考慮: 複合インデックスでは、カラムの順序が重要です。カーディナリティの高いカラムを先に配置し、クエリパターンに応じて適切な順序を選択します。
カバリングインデックスを活用: SELECT句で取得するカラムをすべて含むカバリングインデックスを作成することで、テーブルへのアクセスを避け、パフォーマンスを向上させます。
定期的に実行計画を確認: EXPLAINを使用して実行計画を確認し、インデックスが適切に使用されているかを確認します。フルテーブルスキャンが発生している場合は、インデックスの追加を検討します。
不要なインデックスを削除: 使用されていないインデックスは、ストレージを消費し、更新処理を遅くするだけです。定期的にインデックスの使用状況を確認し、不要なインデックスを削除します。
パーティションと組み合わせる: 大規模なテーブルでは、パーティションとインデックスを組み合わせることで、クエリパフォーマンスを向上させます。パーティションキーを含むインデックスを作成します。

まとめ

データベースのインデックス設計は、パフォーマンス最適化において重要な要素です。適切なカラムにインデックスを作成し、複合インデックスの順序を考慮することで、クエリの実行時間を大幅に短縮できます。

WHERE句、JOIN、ORDER BYで使用されるカラムにインデックスを作成し、カバリングインデックスを活用することで、より効率的なクエリを実現できます。定期的に実行計画を確認し、不要なインデックスを削除することで、ストレージと更新速度のバランスを保つことが重要です。

実践的なプロジェクトでインデックスを設計し、経験を積むことで、より効率的なデータベースを構築できるようになります。