データベースインデックス最適化の深層理解

データベースインデックスSQL性能チューニングPostgreSQL

概要

データベースのパフォーマンスにおいて、インデックスの設計と最適化は最も重要な要素の一つです。本記事では、B-tree、Hash、Bitmap などの主要なインデックス構造の仕組みを深く理解し、PostgreSQL 16とMySQL 8.4の最新機能を活用した実践的な最適化手法を解説します。EXPLAIN PLANの詳細な分析方法から、実際のクエリパフォーマンスを劇的に改善する具体的なテクニックまで、データベースエンジニアに必要な知識を網羅的に提供します。

インデックスの基礎理論

B-treeインデックスの構造と特性

B-treeインデックスは、PostgreSQLとMySQLの両方でデフォルトのインデックスタイプとして採用されている最も汎用的なインデックス構造です。

-- PostgreSQL: B-treeインデックスの作成
CREATE INDEX idx_users_email ON users(email);

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

-- 部分インデックス（PostgreSQL特有）
CREATE INDEX idx_active_users ON users(email) 
WHERE status = 'active';

B-treeの特徴：

バランス木構造: すべてのリーフノードが同じ深さに位置
O(log n)の検索性能: データ量が倍になっても検索ステップは1つ増えるだけ
範囲検索のサポート: <、<=、=、>=、>、BETWEEN、IN、IS NULL、IS NOT NULL に対応
ソート済みデータ: ORDER BY句の高速化

-- B-treeインデックスが効果的なクエリ例
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY created_at;

Hashインデックスの特性と使用場面

Hashインデックスは等値検索に特化した高速なインデックス構造です。PostgreSQL 10以降で大幅に改善され、本番環境での使用が現実的になりました。

-- PostgreSQL: Hashインデックスの作成
CREATE INDEX idx_products_sku_hash ON products USING hash(sku);

-- MySQLではHashインデックスはメモリテーブルでのみサポート
CREATE TABLE memory_cache (
    cache_key VARCHAR(255) PRIMARY KEY,
    cache_value TEXT
) ENGINE=MEMORY;

Hashインデックスの利点：

等値検索の高速化: 特に長い文字列カラムで効果的
小さなインデックスサイズ: B-treeより約30-50%小さい
一定のアクセス時間: O(1)の理想的な性能

制限事項：

範囲検索不可
ソート操作に使用不可
PostgreSQL 10より前のバージョンではWAL非対応

Bitmapインデックスの概念と活用

PostgreSQLのBitmap Index Scanは、複数のインデックスを組み合わせて効率的なクエリ実行を可能にします。

-- 複数の単一カラムインデックスを作成
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_country ON users(country);
CREATE INDEX idx_users_age_range ON users(age_range);

-- Bitmap Index Scanが使用されるクエリ
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users
WHERE status = 'active'
  AND country = 'JP'
  AND age_range = '20-29';

PostgreSQL 16の最新インデックス機能

インデックスの並列作成

PostgreSQL 16では、インデックス作成の並列処理が強化されました。

-- 並列インデックス作成の設定
SET max_parallel_maintenance_workers = 4;
SET maintenance_work_mem = '1GB';

-- 大規模テーブルでの並列インデックス作成
CREATE INDEX CONCURRENTLY idx_large_table_parallel 
ON large_table(column1, column2);

B-tree重複排除機能

PostgreSQL 13以降で導入されたB-tree重複排除により、インデックスサイズが大幅に削減されます。

-- 重複排除を有効にしたインデックス作成（デフォルトで有効）
CREATE INDEX idx_events_user_id ON events(user_id) 
WITH (deduplicate_items = on);

-- インデックスの統計情報を確認
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE indexname = 'idx_events_user_id';

INCLUDE句によるカバリングインデックス

インデックスオンリースキャンを促進するINCLUDE句の活用：

-- カバリングインデックスの作成
CREATE INDEX idx_users_email_include 
ON users(email) 
INCLUDE (first_name, last_name, created_at);

-- インデックスオンリースキャンが可能なクエリ
EXPLAIN (ANALYZE, BUFFERS)
SELECT email, first_name, last_name, created_at
FROM users
WHERE email = '[email protected]';

MySQL 8.4の最新最適化機能

不可視インデックス

MySQL 8.0で導入された不可視インデックスにより、安全なインデックス管理が可能になりました。

-- 不可視インデックスの作成
CREATE INDEX idx_test INVISIBLE ON orders(total_amount);

-- インデックスの可視性を変更
ALTER TABLE orders ALTER INDEX idx_test VISIBLE;

-- オプティマイザヒントで不可視インデックスを使用
SELECT /*+ USE_INDEX(orders idx_test) */ *
FROM orders
WHERE total_amount > 1000;

降順インデックス

MySQL 8.0以降では、真の降順インデックスがサポートされています。

-- 降順インデックスの作成
CREATE INDEX idx_logs_timestamp_desc ON logs(timestamp DESC);

-- 複合インデックスでの昇順・降順の組み合わせ
CREATE INDEX idx_sales_date_amount ON sales(sale_date ASC, amount DESC);

関数インデックス

式インデックスによる高度な最適化：

-- JSON関数を使用したインデックス
CREATE INDEX idx_json_email 
ON users((JSON_UNQUOTE(JSON_EXTRACT(data, '$.email'))));

-- 計算式を使用したインデックス
CREATE INDEX idx_total_with_tax 
ON orders((price * 1.1));

EXPLAIN PLANの詳細分析

PostgreSQLのEXPLAIN ANALYZE

-- 詳細な実行計画の取得
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT 
    o.order_id,
    o.order_date,
    c.customer_name,
    SUM(oi.quantity * oi.unit_price) as total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY o.order_id, o.order_date, c.customer_name
HAVING SUM(oi.quantity * oi.unit_price) > 1000
ORDER BY total DESC
LIMIT 100;

実行計画の重要な指標：

Actual Time: 実際の実行時間（ミリ秒）
Rows: 処理された行数
Loops: ノードの実行回数
Buffers: 読み込まれたページ数
I/O Timings: ディスクI/Oの時間

MySQLのEXPLAIN FORMAT=TREE

MySQL 8.0.16以降の新しいEXPLAIN形式：

-- ツリー形式での実行計画表示
EXPLAIN FORMAT=TREE
SELECT 
    c.customer_name,
    COUNT(o.order_id) as order_count,
    AVG(o.total_amount) as avg_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'Japan'
  AND o.order_date >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
GROUP BY c.customer_id
HAVING order_count > 10;

-- JSON形式での詳細情報取得
EXPLAIN FORMAT=JSON
SELECT ...;

高度なインデックス設計戦略

複合インデックスの最適な順序

-- カーディナリティとクエリパターンを分析
WITH index_stats AS (
    SELECT 
        attname as column_name,
        n_distinct,
        correlation
    FROM pg_stats
    WHERE tablename = 'orders'
)
SELECT * FROM index_stats;

-- 最適な複合インデックスの作成
-- 1. 等値条件のカラムを先に
-- 2. カーディナリティの高い順に
-- 3. 範囲条件のカラムは最後に
CREATE INDEX idx_optimal ON orders(
    status,           -- 等値条件、低カーディナリティ
    customer_id,      -- 等値条件、高カーディナリティ  
    created_at        -- 範囲条件
);

パーティションテーブルでのインデックス戦略

-- PostgreSQL: パーティションテーブルの作成
CREATE TABLE sales (
    id BIGSERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (sale_date);

-- パーティションの作成
CREATE TABLE sales_2024_q1 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- グローバルインデックスの作成
CREATE INDEX idx_sales_amount ON sales(amount);

インデックスメンテナンス戦略

-- PostgreSQL: インデックスの肥大化チェック
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    indexrelid::regclass as index_oid,
    100 * (pg_relation_size(indexrelid) - pg_relation_size(indexrelid, 'fsm')) 
        / pg_relation_size(indexrelid) as bloat_ratio
FROM pg_stat_user_indexes
WHERE pg_relation_size(indexrelid) > 1024 * 1024  -- 1MB以上
ORDER BY bloat_ratio DESC;

-- オンラインでのインデックス再構築
CREATE INDEX CONCURRENTLY idx_new ON table_name(column);
DROP INDEX CONCURRENTLY idx_old;
ALTER INDEX idx_new RENAME TO idx_old;

実践的な最適化事例

ケース1: 全文検索の最適化

-- PostgreSQL: GINインデックスを使用した全文検索
CREATE INDEX idx_articles_fts ON articles 
USING gin(to_tsvector('japanese', title || ' ' || content));

-- 高速な全文検索クエリ
SELECT 
    id,
    title,
    ts_rank(
        to_tsvector('japanese', title || ' ' || content),
        plainto_tsquery('japanese', 'データベース インデックス')
    ) as rank
FROM articles
WHERE to_tsvector('japanese', title || ' ' || content) 
    @@ plainto_tsquery('japanese', 'データベース インデックス')
ORDER BY rank DESC
LIMIT 20;

ケース2: 時系列データの最適化

-- BRINインデックスを使用した時系列データの最適化
CREATE INDEX idx_logs_timestamp_brin ON logs 
USING brin(timestamp) 
WITH (pages_per_range = 128);

-- パーティショニングとの組み合わせ
CREATE TABLE logs_2024_01 PARTITION OF logs
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 時系列クエリの最適化
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    date_trunc('hour', timestamp) as hour,
    COUNT(*) as log_count,
    AVG(response_time) as avg_response_time
FROM logs
WHERE timestamp >= '2024-01-01'
  AND timestamp < '2024-02-01'
GROUP BY hour
ORDER BY hour;

ケース3: 地理空間データの最適化

-- PostGIS拡張を使用した地理空間インデックス
CREATE EXTENSION IF NOT EXISTS postgis;

-- GiSTインデックスの作成
CREATE INDEX idx_locations_geom ON locations 
USING gist(geom);

-- 近傍検索の最適化
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    id,
    name,
    ST_Distance(geom, ST_MakePoint(139.7, 35.7)::geography) as distance
FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(139.7, 35.7)::geography, 5000)
ORDER BY distance
LIMIT 10;

パフォーマンス監視とトラブルシューティング

インデックス使用状況の監視

-- PostgreSQL: 未使用インデックスの検出
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_toast%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- MySQL: インデックス統計の確認
SELECT 
    table_schema,
    table_name,
    index_name,
    cardinality,
    seq_in_index,
    column_name
FROM information_schema.statistics
WHERE table_schema = 'your_database'
ORDER BY table_name, index_name, seq_in_index;

スロークエリの分析と改善

-- PostgreSQL: pg_stat_statementsを使用した分析
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT 
    query,
    calls,
    total_exec_time / 1000 as total_sec,
    mean_exec_time / 1000 as mean_sec,
    stddev_exec_time / 1000 as stddev_sec,
    rows
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY mean_exec_time DESC
LIMIT 20;

まとめ

データベースインデックスの最適化は、単なる技術的な作業ではなく、アプリケーションのパフォーマンスと直結する重要な設計活動です。B-tree、Hash、Bitmapといった各インデックスタイプの特性を理解し、EXPLAIN PLANを活用した科学的なアプローチにより、クエリ性能を劇的に改善できます。

PostgreSQL 16とMySQL 8.4の最新機能を活用することで、より高度な最適化が可能になりました。継続的なモニタリングと適切なメンテナンスにより、データベースのパフォーマンスを長期的に維持することが重要です。

本記事で紹介した技術とベストプラクティスを実践することで、スケーラブルで高性能なデータベースシステムの構築が可能になります。データの成長に合わせて、インデックス戦略も進化させていくことが、持続可能なシステム運用の鍵となります。

概要