Go to Qiita Advent Calendar Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@yosukenaruse

SQLインデックス完全ガイド：パフォーマンス最適化の基礎から実践まで

Posted at

SQLインデックス完全ガイド：パフォーマンス最適化の基礎から実践まで

はじめに

SQLインデックスは、データベースのパフォーマンスを大幅に向上させる重要な技術です。適切なインデックスの設計と運用により、クエリの実行時間を短縮し、システム全体の効率性を向上させることができます。この記事では、インデックスの基本概念から実践的な最適化テクニックまで詳しく解説します。

インデックスとは

インデックスの基本概念

インデックスは、データベース内のデータを効率的に検索するためのデータ構造です。本の索引と同様に、必要な情報を素早く見つけるための「道しるべ」として機能します。

インデックスの必要性

-- インデックスなしの場合：全件スキャン
SELECT * FROM users WHERE email = 'user@example.com';
-- 実行時間：O(n) - テーブル全体をスキャン

-- インデックスありの場合：インデックススキャン
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
-- 実行時間：O(log n) - インデックスを使用した高速検索

インデックスの種類

1. B-treeインデックス（最も一般的）

B-treeインデックスは、最も一般的で汎用的なインデックスです。

-- 基本的なB-treeインデックス
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_products_price ON products(price);

特徴：

  • 等価比較（=）、範囲比較（<, >, <=, >=）、LIKE検索に対応
  • ソート順序を保持
  • 一意性制約の実装に使用

2. ハッシュインデックス

ハッシュインデックスは、等価比較のみに特化したインデックスです。

-- PostgreSQLでのハッシュインデックス
CREATE INDEX idx_users_email_hash ON users USING hash(email);

特徴：

  • 等価比較（=）のみに最適
  • 範囲比較には使用不可
  • メモリ使用量が少ない

3. GiSTインデックス（PostgreSQL）

GiST（Generalized Search Tree）インデックスは、複雑なデータ型に使用されます。

-- 地理空間データ用
CREATE INDEX idx_locations_geom ON locations USING gist(geometry);

-- 全文検索用
CREATE INDEX idx_documents_content ON documents USING gist(to_tsvector('english', content));

4. GINインデックス（PostgreSQL）

GIN（Generalized Inverted Index）インデックスは、配列やJSONデータに使用されます。

-- 配列データ用
CREATE INDEX idx_products_tags ON products USING gin(tags);

-- JSONデータ用
CREATE INDEX idx_users_profile ON users USING gin(profile);

5. BRINインデックス（PostgreSQL）

BRIN（Block Range INdex）インデックスは、大きなテーブルの範囲検索に最適です。

-- 時系列データ用
CREATE INDEX idx_logs_timestamp ON logs USING brin(timestamp);

インデックスの作成方法

基本的なインデックス作成

-- 単一列インデックス
CREATE INDEX idx_users_email ON users(email);

-- 複合インデックス
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 一意インデックス
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- 部分インデックス（条件付きインデックス）
CREATE INDEX idx_orders_active ON orders(order_date) WHERE status = 'active';

インデックスの命名規則

-- 推奨される命名規則
CREATE INDEX idx_[テーブル名]_[列名] ON [テーブル名]([列名]);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 複合インデックスの場合
CREATE INDEX idx_[テーブル名]_[列名1]_[列名2] ON [テーブル名]([列名1], [列名2]);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

複合インデックスの設計

複合インデックスの基本

-- サンプルテーブル
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    status VARCHAR(20),
    amount DECIMAL(10,2)
);

-- 複合インデックス
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

列の順序の重要性

-- 効率的なクエリ（インデックスが使用される）
SELECT * FROM orders WHERE customer_id = 123 AND order_date >= '2024-01-01';

-- 非効率的なクエリ（インデックスが使用されない可能性）
SELECT * FROM orders WHERE order_date >= '2024-01-01' AND customer_id = 123;

複合インデックスの最適化

-- よく使用されるクエリパターンに基づく設計
-- パターン1: customer_id + order_date
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- パターン2: status + order_date
CREATE INDEX idx_orders_status_date ON orders(status, order_date);

-- パターン3: customer_id + status + order_date
CREATE INDEX idx_orders_customer_status_date ON orders(customer_id, status, order_date);

インデックスの効果測定

EXPLAINを使用した分析

-- インデックスなしのクエリ
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE customer_id = 123;

-- 結果例：
-- Seq Scan on orders  (cost=0.00..1000.00 rows=10 width=...)
--   Filter: (customer_id = 123)

-- インデックスありのクエリ
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders WHERE customer_id = 123;

-- 結果例：
-- Index Scan using idx_orders_customer_id on orders  (cost=0.29..8.31 rows=10 width=...)
--   Index Cond: (customer_id = 123)

パフォーマンス比較

-- 実行時間の測定
\timing on

-- インデックスなし
SELECT COUNT(*) FROM orders WHERE customer_id = 123;

-- インデックスあり
SELECT COUNT(*) FROM orders WHERE customer_id = 123;

\timing off

インデックスの最適化テクニック

1. 選択性の高い列を優先

-- 選択性の高い列（一意性が高い）
CREATE INDEX idx_users_email ON users(email);  -- 高選択性

-- 選択性の低い列（一意性が低い）
CREATE INDEX idx_users_gender ON users(gender);  -- 低選択性（避けるべき）

2. WHERE句でよく使用される列

-- よく使用されるWHERE句の列にインデックスを作成
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_date_range ON orders(order_date) WHERE order_date >= '2024-01-01';

3. JOINで使用される列

-- JOINで使用される外部キーにインデックスを作成
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

4. ORDER BYで使用される列

-- ORDER BYで使用される列にインデックスを作成
CREATE INDEX idx_orders_date_desc ON orders(order_date DESC);
CREATE INDEX idx_products_price_name ON products(price, name);

5. GROUP BYで使用される列

-- GROUP BYで使用される列にインデックスを作成
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

インデックスのメンテナンス

インデックスの状態確認

-- インデックスの一覧表示
SELECT 
    schemaname,
    tablename,
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

-- インデックスの使用状況
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

インデックスの再構築

-- インデックスの再構築（PostgreSQL）
REINDEX INDEX idx_orders_customer_id;

-- テーブル全体のインデックス再構築
REINDEX TABLE orders;

-- データベース全体のインデックス再構築
REINDEX DATABASE mydatabase;

統計情報の更新

-- テーブルの統計情報を更新
ANALYZE orders;
ANALYZE users;

-- 特定の列の統計情報を更新
ANALYZE orders(customer_id, order_date);

インデックスの落とし穴

1. 過度なインデックス

-- 避けるべき：過度なインデックス
CREATE INDEX idx_users_id ON users(id);  -- 主キーには不要
CREATE INDEX idx_users_created_at ON users(created_at);  -- 使用頻度が低い
CREATE INDEX idx_users_updated_at ON users(updated_at);  -- 使用頻度が低い

2. 不適切な列の選択

-- 避けるべき：選択性の低い列
CREATE INDEX idx_users_gender ON users(gender);  -- 選択性が低い
CREATE INDEX idx_orders_status ON orders(status);  -- 選択性が低い場合

3. 不要な複合インデックス

-- 避けるべき：使用されない複合インデックス
CREATE INDEX idx_orders_customer_status_date ON orders(customer_id, status, order_date);
-- 実際には customer_id + order_date のみ使用される場合

実践的なインデックス設計例

例1：ECサイトの注文テーブル

-- 注文テーブルの設計
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date TIMESTAMP NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    shipping_address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 推奨インデックス
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);
CREATE INDEX idx_orders_status_date ON orders(status, order_date);

例2：ブログシステムの記事テーブル

-- 記事テーブルの設計
CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    author_id INTEGER NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    published_at TIMESTAMP,
    status VARCHAR(20) DEFAULT 'draft',
    tags TEXT[],
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 推奨インデックス
CREATE INDEX idx_articles_author_id ON articles(author_id);
CREATE INDEX idx_articles_published_at ON articles(published_at);
CREATE INDEX idx_articles_status ON articles(status);
CREATE INDEX idx_articles_author_status ON articles(author_id, status);
CREATE INDEX idx_articles_tags ON articles USING gin(tags);
CREATE INDEX idx_articles_content_search ON articles USING gin(to_tsvector('english', content));

例3：ログテーブル

-- ログテーブルの設計
CREATE TABLE access_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id INTEGER,
    ip_address INET,
    user_agent TEXT,
    request_path VARCHAR(500),
    response_time INTEGER,
    status_code INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 推奨インデックス
CREATE INDEX idx_logs_created_at ON logs(created_at);
CREATE INDEX idx_logs_user_id ON logs(user_id);
CREATE INDEX idx_logs_status_code ON logs(status_code);
CREATE INDEX idx_logs_user_date ON logs(user_id, created_at);
CREATE INDEX idx_logs_status_date ON logs(status_code, created_at);

パフォーマンス監視とチューニング

スロークエリの特定

-- スロークエリの特定（pg_stat_statements拡張が必要）
SELECT 
    query,
    mean_time,
    calls,
    total_time
FROM pg_stat_statements
WHERE mean_time > 1000  -- 1秒以上
ORDER BY mean_time DESC
LIMIT 10;

インデックス使用率の監視

-- 使用されていないインデックスの特定
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

インデックスサイズの監視

-- インデックスのサイズ確認
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

まとめ

SQLインデックスは、データベースパフォーマンスの最適化において最も重要な要素の一つです。

主要なポイント

  1. 適切なインデックス設計: クエリパターンに基づいた設計
  2. 複合インデックスの活用: 列の順序を考慮した設計
  3. 定期的なメンテナンス: 統計情報の更新とインデックスの再構築
  4. パフォーマンス監視: 実行計画とインデックス使用率の監視

ベストプラクティス

  1. 選択性の高い列を優先: 一意性の高い列にインデックスを作成
  2. WHERE句、JOIN、ORDER BY、GROUP BYを考慮: 実際のクエリパターンに基づく設計
  3. 過度なインデックスを避ける: 書き込みパフォーマンスへの影響を考慮
  4. 定期的な見直し: 使用されていないインデックスの削除

適切なインデックス設計により、データベースのパフォーマンスを大幅に向上させることができます。

参考資料

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?