📖

データベースインデックス実践：設計の正しさを実測で証明

yushi-s

2025/08/15に公開

MariaDB

パフォーマンスチューニング

tech

データベースインデックス実践：設計の正しさを実測で証明

📋 記事概要

対象読者: インデックス設計の実践的な効果を学びたい方、パフォーマンス測定に興味がある方

学習内容:

実践的なインデックス効果の測定と比較
良い設計 vs 悪い設計の性能差検証
複合インデックスとカバリングインデックスの効果
現実的なデータ分布での性能測定

主要な測定結果:

User Sessions: 221倍の高速化
Expires Range: 51倍の高速化
Complex Range: 18.6倍の性能差（Good vs Bad）
Token Search: 2.2倍の高速化

はじめに

データベースのパフォーマンス最適化において、インデックス設計は理論だけでなく、実践的な効果測定が重要です。本記事では、実際のデータを使用してインデックス設計の正しさを実測で証明します。

対象システム：セッション管理モデル

今回の題材として、以下のようなセッション管理システムを想定します：

CREATE TABLE sessions (
    id VARCHAR(36) PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(255) NOT NULL UNIQUE,
    refresh_token VARCHAR(255) NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    refresh_expires_at TIMESTAMP NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

このモデルの特徴：

複合的な検索パターン: ユーザーID、トークン、期限切れ日時など複数の検索条件
時系列データ: 期限管理による定期的なクリーンアップ処理
高頻度アクセス: 認証処理での頻繁なトークン検証
現実的なデータ分布: 一部のユーザーが多くのセッションを持つ偏り

実践的なインデックス戦略

1. 良い設計（Good Design）

最適化された複合インデックス

-- ユーザーとアクティブ状態の複合検索
INDEX idx_user_active (user_id, is_active)

-- 期限切れとアクティブ状態の複合検索
INDEX idx_expires_active (expires_at, is_active)

-- トークン検索用（適切なプレフィックス）
INDEX idx_token (token(100))

-- カバリングインデックス
INDEX idx_token_covering (token(100), user_id, expires_at, is_active)

設計のポイント

高カーディナリティ優先: user_idをis_activeより左側に配置
適切なプレフィックス: token(100)で十分な識別性を確保
カバリングインデックス: よく使用されるカラムをカバリング
現実的なクエリパターン: 実際のアプリケーションでの使用頻度を考慮

2. 悪い設計（Bad Design）

問題のあるインデックス設計

-- 低カーディナリティを左側に配置
INDEX idx_bad_order (is_active, user_id)

-- 短すぎるプレフィックス
INDEX idx_wrong_token (token(5))

-- 間違った順序
INDEX idx_wrong_expires (created_at, expires_at)

-- 過度に複雑なインデックス
INDEX idx_over_indexed (user_id, token, refresh_token, expires_at, is_active, created_at)

-- 冗長なインデックス
INDEX idx_redundant (user_id, is_active)

-- 使用頻度の低いカラム
INDEX idx_useless (updated_at)

問題点

カーディナリティの逆転: 低カーディナリティを左側に配置
不適切なプレフィックス: 識別性が不十分
過度な複雑化: メンテナンスコストの増大
冗長性: 重複するインデックス

実測結果：設計の正しさを証明

実験環境

データ量: 100,000件の現実的なテストデータ
データベース: MariaDB
測定方法: マイクロ秒精度での測定 + クエリキャッシュクリア

実験1: 基本パフォーマンス比較

測定結果（100,000件）

テストケース	No Index	Basic Index	Composite Index	最大改善率
User Sessions	6.858ms	1.286ms	0.031ms	221倍
Expires Range	24.918ms	0.528ms	0.489ms	51倍
Token Search	0.042ms	0.020ms	0.019ms	2.2倍
Expired Sessions	41.927ms	17.252ms	18.330ms	2.4倍

実行した測定クエリ

1. User Sessions（ユーザーセッション検索）

-- 測定クエリ
SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions 
WHERE user_id = 500 
AND is_active = TRUE;

-- 測定方法
SET @start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions WHERE user_id = 500 AND is_active = TRUE;
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS execution_time_microseconds;

2. Expires Range（期限範囲検索）

-- 測定クエリ
SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions 
WHERE expires_at BETWEEN NOW() 
AND DATE_ADD(NOW(), INTERVAL 24 HOUR);

-- 測定方法
SET @start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 24 HOUR);
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS execution_time_microseconds;

3. Token Search（トークン検索）

-- 測定クエリ
SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions 
WHERE token = 'token_000001_abc123';

-- 測定方法
SET @start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions WHERE token = 'token_000001_abc123';
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS execution_time_microseconds;

4. Expired Sessions（期限切れセッション検索）

-- 測定クエリ
SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions 
WHERE expires_at < NOW() 
AND is_active = TRUE;

-- 測定方法
SET @start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions WHERE expires_at < NOW() AND is_active = TRUE;
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS execution_time_microseconds;

測定のベストプラクティス

-- 測定前の準備
FLUSH QUERY CACHE;  -- クエリキャッシュをクリア
ANALYZE TABLE sessions;  -- 統計情報を更新

-- 複数回測定のためのループ
DELIMITER //
CREATE PROCEDURE measure_performance(IN iterations INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE total_time BIGINT DEFAULT 0;
    DECLARE start_time TIMESTAMP(6);
    DECLARE end_time TIMESTAMP(6);
    
    WHILE i < iterations DO
        SET start_time = NOW(6);
        SELECT SQL_NO_CACHE COUNT(*) FROM sessions WHERE user_id = 500 AND is_active = TRUE;
        SET end_time = NOW(6);
        SET total_time = total_time + TIMESTAMPDIFF(MICROSECOND, start_time, end_time);
        SET i = i + 1;
    END WHILE;
    
    SELECT total_time / iterations AS average_execution_time_microseconds;
END //
DELIMITER ;

-- 使用例
CALL measure_performance(10);

重要な発見

User Sessions: 複合インデックス (user_id, is_active) が劇的に効果的
Expires Range: 範囲検索でのインデックス効果が顕著
Token Search: 基本的なインデックスでも十分な効果
Expired Sessions: 大量データでのインデックス効果

実験2: Good Design vs Bad Design

測定結果（100,000件）

テストケース	Good Design	Bad Design	性能劣化率
Complex Range	3.596ms	67.010ms	18.6倍遅い
Recent Sessions	46.708ms	85.025ms	1.8倍遅い
Expired Sessions	16.676ms	19.397ms	1.2倍遅い

実行した測定クエリ

1. Complex Range（複合範囲検索）

-- Good Design テーブルでの測定
SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions_good_index 
WHERE expires_at BETWEEN NOW() 
AND DATE_ADD(NOW(), INTERVAL 24 HOUR) 
AND is_active = TRUE;

-- Bad Design テーブルでの測定
SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions_bad_index 
WHERE expires_at BETWEEN NOW() 
AND DATE_ADD(NOW(), INTERVAL 24 HOUR) 
AND is_active = TRUE;

-- 測定方法
SET @start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions_good_index WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 24 HOUR) AND is_active = TRUE;
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS good_design_time;

SET @start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions_bad_index WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 24 HOUR) AND is_active = TRUE;
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS bad_design_time;

2. Recent Sessions（最近のセッション検索）

-- Good Design テーブルでの測定
SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions_good_index 
WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY);

-- Bad Design テーブルでの測定
SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions_bad_index 
WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY);

-- 測定方法
SET @start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions_good_index WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY);
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS good_design_time;

SET @start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions_bad_index WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY);
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS bad_design_time;

3. Expired Sessions（期限切れセッション検索）

-- Good Design テーブルでの測定
SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions_good_index 
WHERE expires_at < NOW() 
AND is_active = TRUE;

-- Bad Design テーブルでの測定
SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions_bad_index 
WHERE expires_at < NOW() 
AND is_active = TRUE;

-- 測定方法
SET @start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions_good_index WHERE expires_at < NOW() AND is_active = TRUE;
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS good_design_time;

SET @start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions_bad_index WHERE expires_at < NOW() AND is_active = TRUE;
SET @end_time = NOW(6);
SELECT TIMESTAMPDIFF(MICROSECOND, @start_time, @end_time) AS bad_design_time;

比較測定のためのストアドプロシージャ

-- Good Design vs Bad Design の比較測定
DELIMITER //
CREATE PROCEDURE compare_designs(IN iterations INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE good_total BIGINT DEFAULT 0;
    DECLARE bad_total BIGINT DEFAULT 0;
    DECLARE start_time TIMESTAMP(6);
    DECLARE end_time TIMESTAMP(6);
    
    WHILE i < iterations DO
        -- Good Design の測定
        SET start_time = NOW(6);
        SELECT SQL_NO_CACHE COUNT(*) FROM sessions_good_index WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 24 HOUR) AND is_active = TRUE;
        SET end_time = NOW(6);
        SET good_total = good_total + TIMESTAMPDIFF(MICROSECOND, start_time, end_time);
        
        -- Bad Design の測定
        SET start_time = NOW(6);
        SELECT SQL_NO_CACHE COUNT(*) FROM sessions_bad_index WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 24 HOUR) AND is_active = TRUE;
        SET end_time = NOW(6);
        SET bad_total = bad_total + TIMESTAMPDIFF(MICROSECOND, start_time, end_time);
        
        SET i = i + 1;
    END WHILE;
    
    SELECT 
        good_total / iterations AS good_design_avg_microseconds,
        bad_total / iterations AS bad_design_avg_microseconds,
        (bad_total / iterations) / (good_total / iterations) AS performance_ratio;
END //
DELIMITER ;

-- 使用例
CALL compare_designs(10);

設計の正しさが証明された

Complex Rangeで18.6倍の性能差が出たのは、設計の優位性を明確に証明しています：

Good Design: (expires_at, is_active) の最適化された複合インデックス
Bad Design: 複雑すぎるインデックス (user_id, token, refresh_token, expires_at, is_active, created_at)

実行計画の詳細分析

EXPLAIN結果の比較

Good Design（Complex Range）

-- 実行計画の確認クエリ
EXPLAIN SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions_good_index 
WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 24 HOUR) 
AND is_active = TRUE;

-- 詳細な実行計画の取得
EXPLAIN FORMAT=JSON SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions_good_index 
WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 24 HOUR) 
AND is_active = TRUE;

結果:

access_type: range
key: idx_expires_active
rows_examined: 2,005
filtered: 100.00
Extra: Using index condition

Bad Design（Complex Range）

-- 実行計画の確認クエリ
EXPLAIN SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions_bad_index 
WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 24 HOUR) 
AND is_active = TRUE;

-- 詳細な実行計画の取得
EXPLAIN FORMAT=JSON SELECT SQL_NO_CACHE COUNT(*) 
FROM sessions_bad_index 
WHERE expires_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 24 HOUR) 
AND is_active = TRUE;

結果:

access_type: index
key: idx_over_indexed
rows_examined: 2,007
filtered: 100.00
Extra: Using index

実行計画の詳細分析クエリ

-- インデックス使用状況の確認
SHOW INDEX FROM sessions_good_index;
SHOW INDEX FROM sessions_bad_index;

-- テーブル統計情報の確認
SHOW TABLE STATUS LIKE 'sessions_good_index';
SHOW TABLE STATUS LIKE 'sessions_bad_index';

-- インデックス使用率の確認
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY,
    SUB_PART,
    PACKED,
    NULLABLE,
    INDEX_TYPE
FROM information_schema.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME IN ('sessions_good_index', 'sessions_bad_index')
ORDER BY TABLE_NAME, INDEX_NAME;

-- クエリパフォーマンスの詳細分析
SELECT 
    sql_text,
    exec_count,
    avg_timer_wait/1000000000 as avg_time_seconds,
    max_timer_wait/1000000000 as max_time_seconds,
    sum_timer_wait/1000000000 as total_time_seconds
FROM performance_schema.events_statements_summary_by_digest 
WHERE sql_text LIKE '%sessions_good_index%' 
OR sql_text LIKE '%sessions_bad_index%'
ORDER BY avg_timer_wait DESC;

重要な指標の解説

access_type: アクセス方法
- range: インデックス範囲スキャン（効率的）
- index: インデックスフルスキャン（非効率）
rows_examined: 処理行数
- 少ないほど効率的
filtered: フィルタ後の行数割合
- 100%に近いほどインデックスが効果的

現実的なデータ分布の効果

データ生成戦略

-- より現実的なユーザー分布
IF RAND() < 0.1 THEN
    SET user_id_val = FLOOR(1 + RAND() * 100); -- 10%のユーザー（1-100）
ELSE
    SET user_id_val = FLOOR(101 + RAND() * 900); -- 90%のユーザー（101-1000）
END IF;

-- より現実的な期限分布
IF RAND() < 0.3 THEN
    SET expires_hours = FLOOR(-24 + RAND() * 48); -- 30%期限切れ
ELSE
    SET expires_hours = FLOOR(1 + RAND() * 168); -- 70%将来
END IF;

効果

偏りのあるデータ: 一部のユーザーが多くのセッションを持つ
期限の多様性: 期限切れと将来のセッションが混在
現実的なクエリパターン: 実際のアプリケーションに近い条件

インデックス設計のベストプラクティス

1. 設計原則

3つのC原則

Cardinality（カーディナリティ）: 高い値を左側に配置
Column Order（カラム順序）: 等価比較を範囲比較より左側に
Coverage（カバレッジ）: よく使用されるカラムをカバリング

実践的な判断基準

-- ✅ 良い例：高カーディナリティを左側に
INDEX idx_good_order (user_id, is_active)

-- ❌ 悪い例：低カーディナリティを左側に
INDEX idx_bad_order (is_active, user_id)

2. パフォーマンス測定の重要性

測定すべき項目

実行時間: マイクロ秒精度での測定
実行計画: EXPLAINによる詳細分析
データ分布: 現実的なデータでの検証
スケーラビリティ: データ量増加時の性能変化

測定のベストプラクティス

-- クエリキャッシュをクリア
FLUSH QUERY CACHE;

-- 統計情報を更新
ANALYZE TABLE sessions;

-- マイクロ秒精度で測定
SET start_time = NOW(6);
SELECT SQL_NO_CACHE COUNT(*) FROM sessions WHERE condition;
SET end_time = NOW(6);

実践的な応用

1. 複雑なクエリの最適化

複合条件での検索

-- 最適化されたクエリ
SELECT COUNT(*) 
FROM sessions 
WHERE user_id = ? 
AND is_active = TRUE 
AND expires_at > NOW()
AND created_at > DATE_SUB(NOW(), INTERVAL 7 DAY);

推奨インデックス:

INDEX idx_user_active_expires (user_id, is_active, expires_at)

2. 時系列データの最適化

期限管理クエリ

-- 期限切れセッションの一括削除
DELETE FROM sessions 
WHERE expires_at < NOW() 
AND is_active = TRUE;

推奨インデックス:

INDEX idx_expires_active (expires_at, is_active)

3. 認証処理の最適化

トークン検証

-- トークンによる認証
SELECT user_id, expires_at 
FROM sessions 
WHERE token = ? 
AND is_active = TRUE 
AND expires_at > NOW();

推奨インデックス:

INDEX idx_token_covering (token(100), user_id, expires_at, is_active)

まとめ

本記事では、実践的なインデックス効果の測定を通じて、設計の正しさを実測で証明しました。

重要な発見

User Sessions: 221倍の高速化で複合インデックスの効果を実証
Expires Range: 51倍の高速化で範囲検索の最適化を実証
Complex Range: 18.6倍の性能差で設計の優位性を実証
現実的なデータ: 偏りのあるデータでも一貫した効果を確認

実践的な教訓

測定の重要性: 理論だけでなく実測による検証が不可欠
データ分布の考慮: 現実的なデータでの検証が重要
設計原則の遵守: 3つのC原則の効果を実証
継続的な最適化: パフォーマンス測定による継続的な改善

ソースコード

本記事で使用したSQLスクリプトとテスト環境の設定は、以下のGitHubリポジトリで公開されています：

🔗 https://github.com/YSawc/db-performance-test

リポジトリには以下の内容が含まれています：

完全なSQLスクリプト（sql_scripts_collection_fixed.sql）
devenvを使用した再現可能な開発環境
詳細な実行手順とトラブルシューティング
実際の測定結果と分析

コラボスタイル DevelopersPublication

「ワークスタイルの未来を切り拓く」を理念に掲げ、リアルとデジタルの2つの働く場に向けた事業展開に留まらず、自社が率先して新しいワークスタイルに挑戦し、発信を行うことでワークスタイルの未来を切り拓いていきます。

データベースインデックス実践：設計の正しさを実測で証明

📋 記事概要

はじめに

対象システム：セッション管理モデル

実践的なインデックス戦略

1. 良い設計（Good Design）

最適化された複合インデックス

設計のポイント

2. 悪い設計（Bad Design）

問題のあるインデックス設計

問題点

実測結果：設計の正しさを証明

実験環境

実験1: 基本パフォーマンス比較

測定結果（100,000件）

実行した測定クエリ

測定のベストプラクティス

重要な発見

実験2: Good Design vs Bad Design

測定結果（100,000件）

実行した測定クエリ

比較測定のためのストアドプロシージャ

設計の正しさが証明された

実行計画の詳細分析

EXPLAIN結果の比較

Good Design（Complex Range）

Bad Design（Complex Range）

実行計画の詳細分析クエリ

重要な指標の解説

現実的なデータ分布の効果

データ生成戦略

効果

インデックス設計のベストプラクティス

1. 設計原則

3つのC原則

実践的な判断基準

2. パフォーマンス測定の重要性

測定すべき項目

測定のベストプラクティス

実践的な応用

1. 複雑なクエリの最適化

複合条件での検索

2. 時系列データの最適化

期限管理クエリ

3. 認証処理の最適化

トークン検証

まとめ

重要な発見

実践的な教訓

ソースコード

Discussion