🚀 Step 19: パフォーマンスチューニング実践

実際の遅いクエリを改善しよう!

📋 このステップで学ぶこと

実際の遅いクエリを改善する手順
ベンチマークの正しい取り方
キャッシュの理解と活用
パーティショニングの基礎
データベース設定の最適化
総合的な最適化の実践

🎯 1. 実際の遅いクエリを改善

1-1. ケーススタディ：売上レポートの改善

実際のビジネスシーンで遭遇する「遅いクエリ」を、段階的に改善していきましょう。

⚠️ 問題のあるクエリ（実行時間: 45秒）

「商品別の注文件数と売上金額」を集計するレポートです。

※横にスクロールできます

— 問題のクエリ：実行に45秒かかっている
SELECT 
    p.product_name,
    COUNT(*) as order_count,
    SUM(od.quantity * od.unit_price) as total_sales
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
WHERE o.order_date >= ‘2024-01-01’
GROUP BY p.product_id, p.product_name
ORDER BY total_sales DESC;

クエリの内容:

products、order_details、orders の3テーブルを結合
2024年以降の注文だけを対象
商品ごとに注文件数と売上を集計
売上順に並べ替え

1-2. ステップ1：実行計画を確認する

まず、なぜ遅いのかを実行計画で確認します。

※横にスクロールできます

EXPLAIN QUERY PLAN
SELECT 
    p.product_name,
    COUNT(*) as order_count,
    SUM(od.quantity * od.unit_price) as total_sales
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
WHERE o.order_date >= ‘2024-01-01’
GROUP BY p.product_id, p.product_name
ORDER BY total_sales DESC;

実行計画の結果:

QUERY PLAN
|--SCAN order_details
|--SEARCH orders USING INDEX sqlite_autoindex_orders_1
`--SEARCH products USING INTEGER PRIMARY KEY

1-3. ステップ2：問題点を特定する

🔍 発見した問題点

SCAN order_details → 全件スキャンが発生している
order_date での絞り込みが効いていない
order_detailsが最初にスキャンされるため、無駄なデータを読んでいる

問題の原因は、order_detailsテーブルが全件スキャンされていることです。100万件のorder_detailsがあれば、100万件全てを読んでしまいます。

1-4. ステップ3：インデックスを追加する

問題を解決するため、必要なインデックスを追加します。

※横にスクロールできます

— 1. ordersのorder_dateにインデックス
— 理由: WHERE句で日付絞り込みに使うため
CREATE INDEX idx_orders_date ON orders(order_date);

— 2. order_detailsのorder_idにインデックス
— 理由: JOINで結合キーとして使うため
CREATE INDEX idx_order_details_order ON order_details(order_id);

— 3. 統計情報を更新
— 理由: オプティマイザが新しいインデックスを認識するため
ANALYZE;

各インデックスの役割:

インデックス	目的
idx_orders_date	WHERE o.order_date >= ‘2024-01-01’ を高速化
idx_order_details_order	JOIN on od.order_id = o.order_id を高速化

1-5. ステップ4：改善効果を確認する

インデックス追加後、再度実行して効果を確認します。

※横にスクロールできます

— タイマーをONにして実行時間を計測
.timer on

— 同じクエリを再実行
SELECT 
    p.product_name,
    COUNT(*) as order_count,
    SUM(od.quantity * od.unit_price) as total_sales
FROM products p
JOIN order_details od ON p.product_id = od.product_id
JOIN orders o ON od.order_id = o.order_id
WHERE o.order_date >= ‘2024-01-01’
GROUP BY p.product_id, p.product_name
ORDER BY total_sales DESC;

結果:

Run Time: real 2.300 user 2.100 sys 0.200

45秒 → 2.3秒（約20倍の改善!）

1-6. ステップ5：さらに最適化する

まだ改善の余地があります。複合インデックスとカバリングインデックスを使ってさらに高速化しましょう。

※横にスクロールできます

— 1. ordersの複合インデックス
— 理由: order_dateで絞り込み、order_idでJOINを同時に最適化
CREATE INDEX idx_orders_date_id ON orders(order_date, order_id);

— 2. order_detailsのカバリングインデックス
— 理由: 必要な列を全て含めることで、テーブル本体へのアクセスを省略
CREATE INDEX idx_order_details_full 
ON order_details(order_id, product_id, quantity, unit_price);

— 3. 統計情報を更新
ANALYZE;

💡 カバリングインデックスとは

SELECT句で必要な列を全てインデックスに含めることで、テーブル本体を読まずにインデックスだけで結果を返せる状態です。ディスクアクセスが減り、高速になります。

再度実行して効果を確認します。

※横にスクロールできます

.timer on
SELECT …  — 同じクエリを実行

結果:

Run Time: real 0.800 user 0.750 sys 0.050

2.3秒 → 0.8秒（さらに約3倍の改善!）

1-7. 最終結果のまとめ

✅ 改善結果のまとめ

段階	実行時間	改善率
改善前	45秒	–
インデックス追加後	2.3秒	20倍
複合インデックス後	0.8秒	56倍

最終結果: 45秒 → 0.8秒（56倍の改善!）

📊 2. ベンチマークの取り方

2-1. なぜ正確なベンチマークが重要か

パフォーマンスを改善するには、正確な測定が不可欠です。感覚で「速くなった気がする」ではなく、数値で証明することが大切です。

💡 医者で例えると…

「熱がある」ではなく「38.5度ある」と正確に測定するのと同じです。
「速くなった」ではなく「45秒→0.8秒になった」と数値で示します。

2-2. ベンチマークの5原則

📌 ベンチマークの5原則

複数回実行して平均を取る（1回だけでは信頼性が低い）
キャッシュをクリアしてから測定（2回目以降は速くなるため）
本番と同じデータ量でテスト（データ量で速度は大きく変わる）
他の処理がない状態で測定（負荷がない状態で計測）
改善前後を同じ条件で比較（条件を揃える）

2-3. SQLiteでのベンチマーク方法

SQLiteでは .timer on コマンドで実行時間を計測できます。

※横にスクロールできます

— タイマーをONにする
.timer on

— クエリを実行（1回目）
SELECT * FROM customers WHERE prefecture = ‘東京都’;
— Run Time: real 0.123 user 0.100 sys 0.023

— クエリを実行（2回目）
SELECT * FROM customers WHERE prefecture = ‘東京都’;
— Run Time: real 0.125 user 0.102 sys 0.023

— クエリを実行（3回目）
SELECT * FROM customers WHERE prefecture = ‘東京都’;
— Run Time: real 0.120 user 0.098 sys 0.022

— 平均: 約0.123秒

実行時間の読み方:

項目	意味
real	実際にかかった時間（これが最も重要）
user	CPU処理時間（ユーザーモード）
sys	CPU処理時間（システムモード）

2-4. キャッシュをクリアする方法

2回目以降の実行は、データがキャッシュに残っているため速くなります。正確な測定には、キャッシュをクリアする必要があります。

※横にスクロールできます

— 方法1: SQLiteを閉じて再起動
.quit
— シェルから再度起動
sqlite3 database.db

— 方法2: 別の大きなクエリを実行してキャッシュを追い出す
SELECT COUNT(*) FROM large_table;

— 方法3: キャッシュサイズを一時的に小さくする
PRAGMA cache_size = -1;
— クエリを実行
PRAGMA cache_size = -2000;  — 元に戻す

2-5. 1回目の測定を捨てる理由

なぜ1回目を捨てるのか:

コールドスタート：接続直後は初期化処理が含まれる
クエリプラン：初回はクエリの解析・最適化が入る
キャッシュ：初回はディスクから読み込む

2回目以降の方が「通常の使用状況」に近いため、1回目は「ウォームアップ」として捨てます。

2-6. Pythonでの自動測定

より正確な測定には、Pythonなどのプログラムで自動化すると便利です。

※横にスクロールできます

# Python でのベンチマーク例
import time
import sqlite3

# データベースに接続
conn = sqlite3.connect(‘database.db’)
cursor = conn.cursor()

def benchmark(query, iterations=5):
    “””クエリを複数回実行して平均時間を計測”””
    times = []
    
    for i in range(iterations):
        start = time.time()       # 開始時刻
        cursor.execute(query)     # クエリ実行
        cursor.fetchall()         # 結果を全て取得
        end = time.time()         # 終了時刻
        times.append(end – start) # 実行時間を記録
    
    # 結果を表示
    avg_time = sum(times) / len(times)
    print(f”平均: {avg_time:.4f}秒”)
    print(f”最速: {min(times):.4f}秒”)
    print(f”最遅: {max(times):.4f}秒”)
    return avg_time

# 改善前を測定
print(“=== 改善前 ===”)
before = benchmark(
    “SELECT * FROM customers WHERE prefecture = ‘東京都'”
)

# インデックスを作成
cursor.execute(
    “CREATE INDEX IF NOT EXISTS idx_prefecture ON customers(prefecture)”
)
conn.commit()

# 改善後を測定
print(“\n=== 改善後 ===”)
after = benchmark(
    “SELECT * FROM customers WHERE prefecture = ‘東京都'”
)

# 改善率を計算
improvement = before / after
print(f”\n改善率: {improvement:.1f}倍”)

💡 ベンチマークのコツ

ウォームアップ：1回目の結果を捨てる
外れ値：極端に遅い結果は除外（他の処理の影響かも）
環境：他のプロセスを停止してから測定
記録：結果をファイルに保存して後で比較

💾 3. キャッシュの理解と活用

3-1. キャッシュとは何か

キャッシュとは、一度読んだデータをメモリに保存しておき、2回目以降は高速に取り出せる仕組みです。

💡 図書館で例えると…

倉庫（ディスク）：本を探すのに時間がかかる
机の上（キャッシュ）：よく使う本を置いておけば、すぐ取れる

データベースも同じで、よく使うデータをメモリに置いておくことで高速化します。

3-2. キャッシュの効果を確認する

同じクエリを2回実行すると、2回目はキャッシュの効果で高速になります。

※横にスクロールできます

.timer on

— 1回目（キャッシュなし = ディスクから読む）
SELECT * FROM customers WHERE prefecture = ‘東京都’;
— Run Time: real 0.500 user 0.400 sys 0.100

— 2回目（キャッシュあり = メモリから読む）
SELECT * FROM customers WHERE prefecture = ‘東京都’;
— Run Time: real 0.005 user 0.004 sys 0.001
— 100倍速い!

キャッシュの効果:

状態	データの場所	速度
1回目（キャッシュなし）	ディスク	遅い（0.5秒）
2回目（キャッシュあり）	メモリ	速い（0.005秒）

速度差は100〜1000倍になることも!

3-3. SQLiteのキャッシュサイズを設定する

SQLiteでは、PRAGMA cache_size でキャッシュサイズを設定できます。

※横にスクロールできます

— 現在のキャッシュサイズを確認
PRAGMA cache_size;
— -2000（デフォルト: 約2MB）

— キャッシュサイズを増やす（10MB）
— 負の数はKB単位、正の数はページ数
PRAGMA cache_size = -10000;  — 10MB

— 設定を確認
PRAGMA cache_size;
— -10000

cache_sizeの指定方法:

値	意味	例
負の数	KB単位	-10000 = 10MB
正の数	ページ数	10000 = 10000ページ（約40MB）

3-4. アプリケーションレベルのキャッシュ

データベースのキャッシュとは別に、アプリケーション側でもキャッシュを実装できます。

※横にスクロールできます

# Python での簡易キャッシュ実装
cache = {}  # キャッシュ用の辞書

def get_customer(customer_id):
    “””顧客情報を取得（キャッシュ対応）”””
    
    # 1. キャッシュにあるかチェック
    if customer_id in cache:
        print(“キャッシュヒット!”)
        return cache[customer_id]
    
    # 2. キャッシュにない場合はDBから取得
    print(“DBから取得”)
    cursor.execute(
        “SELECT * FROM customers WHERE customer_id = ?”,
        (customer_id,)
    )
    result = cursor.fetchone()
    
    # 3. キャッシュに保存
    cache[customer_id] = result
    
    return result

# 使用例
customer = get_customer(101)  # → “DBから取得”
customer = get_customer(101)  # → “キャッシュヒット!”
customer = get_customer(102)  # → “DBから取得”
customer = get_customer(101)  # → “キャッシュヒット!”

3-5. キャッシュの有効期限を設定する

キャッシュは古いデータを返してしまう危険性があります。有効期限を設定しましょう。

※横にスクロールできます

# Python での有効期限付きキャッシュ
import time

cache = {}  # {key: (value, timestamp)}
CACHE_TTL = 300  # 有効期限: 5分（300秒）

def get_customer_with_ttl(customer_id):
    “””有効期限付きキャッシュ”””
    
    # 1. キャッシュにあるかチェック
    if customer_id in cache:
        value, timestamp = cache[customer_id]
        
        # 有効期限内かチェック
        if time.time() – timestamp < CACHE_TTL:
            print("キャッシュヒット!")
            return value
        else:
            print("キャッシュ期限切れ")
    
    # 2. DBから取得
    print("DBから取得")
    cursor.execute(
        "SELECT * FROM customers WHERE customer_id = ?",
        (customer_id,)
    )
    result = cursor.fetchone()
    
    # 3. キャッシュに保存（タイムスタンプ付き）
    cache[customer_id] = (result, time.time())
    
    return result

3-6. キャッシュ戦略の考え方

📌 キャッシュすべきデータ

頻繁に読まれるデータ（商品情報、マスタデータ）
更新が少ないデータ（カテゴリ、都道府県など）
計算コストが高いデータ（集計結果、レポート）

⚠️ キャッシュの注意点

古いデータ：更新が反映されない可能性
メモリ不足：キャッシュが大きすぎると問題に
一貫性：データ更新時にキャッシュもクリアする必要

有効期限の目安:

データの種類	有効期限
商品情報	1時間〜1日
ユーザー情報	5分〜30分
在庫情報	1分〜5分
静的コンテンツ	1週間〜1ヶ月

📂 4. パーティショニングの基礎

4-1. パーティショニングとは

パーティショニングとは、大きなテーブルを複数の小さなテーブルに分割する技術です。

💡 本棚で例えると…

パーティションなし：1つの大きな本棚に全ての本（探すのに時間がかかる）
パーティションあり：年ごとの本棚に分ける（2024年の本は2024年の棚だけ探す）

4-2. パーティショニングのメリット

✅ パーティショニングのメリット

検索が速くなる：必要なパーティションだけを読む
削除が速くなる：パーティション単位で一括削除
メンテナンスが楽：部分的にVACUUMやREINDEX
バックアップが楽：必要な部分だけバックアップ

4-3. SQLiteでの手動パーティショニング

SQLiteには自動パーティショニング機能がないため、手動でテーブルを分割します。

※横にスクロールできます

— 年ごとにテーブルを分割
— 2022年の注文
CREATE TABLE orders_2022 (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    amount INTEGER
);

— 2023年の注文
CREATE TABLE orders_2023 (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    amount INTEGER
);

— 2024年の注文
CREATE TABLE orders_2024 (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    amount INTEGER
);

4-4. VIEWで統合する

分割したテーブルを、VIEWで1つのテーブルのように見せることができます。

※横にスクロールできます

— 全ての注文を見るVIEWを作成
CREATE VIEW orders_all AS
SELECT * FROM orders_2022
UNION ALL
SELECT * FROM orders_2023
UNION ALL
SELECT * FROM orders_2024;

クエリの解説:

CREATE VIEW → 仮想テーブル（VIEW）を作成
UNION ALL → 複数のSELECT結果を縦に結合
アプリケーションからは orders_all として透過的にアクセス

使い方は通常のテーブルと同じです。

※横にスクロールできます

— 2024年以降のデータを検索
SELECT * FROM orders_all 
WHERE order_date >= ‘2024-01-01’;
— 内部的には orders_2024 だけが読まれる!

4-5. 古いデータの削除が高速に

パーティショニングの大きなメリットは、古いデータの削除が一瞬で終わることです。

※横にスクロールできます

— DELETEで削除（遅い）
DELETE FROM orders WHERE order_date < '2022-01-01';
-- 100万行削除 → 数分〜数十分かかる

-- テーブルごと削除（一瞬）
DROP TABLE orders_2021;
-- 100万行でも一瞬で完了!

💡 なぜDROP TABLEは速いのか

DELETEは1行ずつ削除してログを書くのに対し、DROP TABLEはファイルを削除するだけなので一瞬で終わります。

4-6. 新しいパーティションを追加する

年が変わったら、新しいパーティションを追加してVIEWを更新します。

※横にスクロールできます

— 1. 新しいテーブルを作成
CREATE TABLE orders_2025 (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date TEXT,
    amount INTEGER
);

— 2. 古いVIEWを削除
DROP VIEW orders_all;

— 3. 新しいVIEWを作成（2021を削除、2025を追加）
CREATE VIEW orders_all AS
SELECT * FROM orders_2022
UNION ALL
SELECT * FROM orders_2023
UNION ALL
SELECT * FROM orders_2024
UNION ALL
SELECT * FROM orders_2025;

4-7. 月単位のパーティショニング

データ量が非常に多い場合は、月単位でパーティショニングすることもあります。

※横にスクロールできます

— 月ごとにテーブルを分割
CREATE TABLE orders_2024_01 (…);  — 2024年1月
CREATE TABLE orders_2024_02 (…);  — 2024年2月
CREATE TABLE orders_2024_03 (…);  — 2024年3月
— …

— 3月のデータだけを検索
SELECT * FROM orders_2024_03 
WHERE customer_id = 101;
— 1ヶ月分だけを検索（1年分より12倍速い!）

4-8. パーティショニングの設計指針

📌 パーティショニングの設計指針

項目	考慮点
分割基準	日付、地域、カテゴリなど、よく使う検索条件で分割
粒度	年単位、月単位、日単位（データ量に応じて決定）
管理	自動化スクリプトで運用（手動は手間がかかる）
アクセス	VIEWで透過的にアクセス

パーティショニングを使うべき場合:

数百万件以上のデータ
時系列データ（ログ、注文、アクセス履歴など）
古いデータを定期的に削除する場合
特定期間だけを頻繁に検索する場合

⚙️ 5. データベース設定の最適化

5-1. SQLiteの主要な設定項目

SQLiteには、パフォーマンスに影響する様々な設定があります。適切に設定することで、大幅な高速化が可能です。

主要な設定項目:

設定	効果
journal_mode	トランザクションログの方式
synchronous	ディスク書き込みの同期レベル
cache_size	メモリキャッシュのサイズ
temp_store	一時ファイルの保存場所

5-2. WALモードに変更する（最重要）

WAL（Write-Ahead Logging）モードは、SQLiteで最も効果的な高速化設定です。

※横にスクロールできます

— 現在のジャーナルモードを確認
PRAGMA journal_mode;
— delete（デフォルト）

— WALモードに変更
PRAGMA journal_mode = WAL;
— wal

— 設定を確認
PRAGMA journal_mode;
— wal

✅ WALモードのメリット

読み込みと書き込みが同時に可能（並列処理が改善）
書き込みが高速（ログを追記するだけ）
ロック競合が減る（同時アクセスに強い）
クラッシュ復旧が高速

⚠️ WALモードの注意点

ファイルが増える：-wal、-shm ファイルが作られる
NFS非対応：ネットワークファイルシステムでは使用不可
設定の永続化：一度設定すれば保持される

5-3. 同期モードを変更する

synchronous設定は、ディスク書き込みの安全性と速度のトレードオフを調整します。

※横にスクロールできます

— 現在の同期モードを確認
PRAGMA synchronous;
— 2（FULL = デフォルト）

— NORMALモードに変更（高速化、やや危険）
PRAGMA synchronous = NORMAL;
— 1

— 設定を確認
PRAGMA synchronous;
— 1

同期モードの種類:

モード	値	速度	安全性
FULL	2	遅い	最も安全
NORMAL	1	普通	ほぼ安全（推奨）
OFF	0	最速	危険（電源断でデータ破損の可能性）

5-4. キャッシュサイズを増やす

メモリに余裕がある場合は、キャッシュサイズを増やすことで高速化できます。

※横にスクロールできます

— 現在のキャッシュサイズを確認
PRAGMA cache_size;
— -2000（デフォルト: 約2MB）

— キャッシュサイズを64MBに増やす
— 負の数はKB単位
PRAGMA cache_size = -64000;

— 設定を確認
PRAGMA cache_size;
— -64000

5-5. 一時ファイルをメモリに配置

ソート処理などで使われる一時ファイルをメモリに配置すると、高速化できます。

※横にスクロールできます

— 一時ファイルの場所を確認
PRAGMA temp_store;
— 0（DEFAULT = ファイル）

— メモリに変更
PRAGMA temp_store = MEMORY;

— 設定を確認
PRAGMA temp_store;
— 2（MEMORY）

5-6. 推奨設定のまとめ

以下は、一般的な用途での推奨設定です。

※横にスクロールできます

— SQLiteの推奨設定（接続時に実行）

— 1. WALモードを有効化（最重要）
PRAGMA journal_mode = WAL;

— 2. 同期モードをNORMALに（高速化）
PRAGMA synchronous = NORMAL;

— 3. キャッシュサイズを増やす（64MB）
PRAGMA cache_size = -64000;

— 4. 一時ファイルをメモリに
PRAGMA temp_store = MEMORY;

— 5. 外部キー制約を有効化
PRAGMA foreign_keys = ON;

📌 Pythonで接続時に設定する例

※横にスクロールできます

# Pythonでの接続と設定
import sqlite3

# 接続
conn = sqlite3.connect(
    ‘database.db’,
    timeout=30,              # タイムアウト30秒
    check_same_thread=False  # マルチスレッド対応
)

# 推奨設定を適用
conn.execute(“PRAGMA journal_mode = WAL”)
conn.execute(“PRAGMA synchronous = NORMAL”)
conn.execute(“PRAGMA cache_size = -64000”)
conn.execute(“PRAGMA temp_store = MEMORY”)
conn.execute(“PRAGMA foreign_keys = ON”)

5-7. 大量挿入の最適化

大量のデータを挿入する場合、トランザクションでまとめると劇的に高速化できます。

※横にスクロールできます

— ❌ 遅い方法（1件ずつコミット）
— 100,000回のINSERTで100,000回のディスク書き込み!
INSERT INTO customers (name, email) VALUES (‘田中’, ‘tanaka@example.com’);
— 自動コミット
INSERT INTO customers (name, email) VALUES (‘佐藤’, ‘sato@example.com’);
— 自動コミット
— 繰り返し…

※横にスクロールできます

— ✅ 速い方法（トランザクションでまとめる）
BEGIN TRANSACTION;

INSERT INTO customers (name, email) VALUES (‘田中’, ‘tanaka@example.com’);
INSERT INTO customers (name, email) VALUES (‘佐藤’, ‘sato@example.com’);
INSERT INTO customers (name, email) VALUES (‘鈴木’, ‘suzuki@example.com’);
— 繰り返し…

COMMIT;
— 1回のディスク書き込みで完了!

トランザクションの効果:

方法	10万件の挿入時間
自動コミット（1件ずつ）	5〜10分
トランザクション（まとめて）	数秒

100〜1000倍の改善!

5-8. 複数行INSERTの活用

SQLiteでは、1つのINSERT文で複数行を挿入できます。

※横にスクロールできます

— ❌ 遅い（1行ずつ）
INSERT INTO customers (name, email) VALUES (‘田中’, ‘tanaka@example.com’);
INSERT INTO customers (name, email) VALUES (‘佐藤’, ‘sato@example.com’);
INSERT INTO customers (name, email) VALUES (‘鈴木’, ‘suzuki@example.com’);

— ✅ 速い（複数行を1つのINSERTで）
INSERT INTO customers (name, email) VALUES 
    (‘田中’, ‘tanaka@example.com’),
    (‘佐藤’, ‘sato@example.com’),
    (‘鈴木’, ‘suzuki@example.com’);

🎯 6. 総合的な最適化の実践

6-1. ケーススタディ：ECサイトの注文管理

実際のビジネスシナリオで、これまで学んだ全ての技術を組み合わせて最適化してみましょう。

⚠️ 現状の問題

注文データ：500万件
月次レポート生成：5分かかる
顧客ページの読み込み：3秒かかる
商品ページの読み込み：0.5秒かかる

6-2. 解決策1：パーティショニングで月次レポートを高速化

500万件の注文データを年月ごとに分割します。

※横にスクロールできます

— 年月ごとにテーブル分割
CREATE TABLE orders_2024_01 (…);
CREATE TABLE orders_2024_02 (…);
CREATE TABLE orders_2024_03 (…);
— …

— VIEWで統合
CREATE VIEW orders AS
SELECT * FROM orders_2024_01
UNION ALL SELECT * FROM orders_2024_02
UNION ALL SELECT * FROM orders_2024_03
— …

✅ 効果

月次レポートの生成時間：5分 → 30秒（10倍改善）
1ヶ月分のデータだけを読むため、高速になります。

6-3. 解決策2：サマリーテーブルで顧客ページを高速化

顧客ごとの集計結果を事前に計算して保存します。

※横にスクロールできます

— 顧客サマリーテーブルを作成
CREATE TABLE customer_summary (
    customer_id INTEGER PRIMARY KEY,
    total_orders INTEGER,        — 総注文件数
    total_amount INTEGER,        — 総注文金額
    last_order_date TEXT,        — 最終注文日
    updated_at TEXT              — 更新日時
);

夜間バッチで定期的に更新します。

※横にスクロールできます

— 夜間バッチで更新（毎日深夜に実行）
INSERT OR REPLACE INTO customer_summary
SELECT 
    customer_id,
    COUNT(*) as total_orders,
    SUM(amount) as total_amount,
    MAX(order_date) as last_order_date,
    datetime(‘now’) as updated_at
FROM orders
GROUP BY customer_id;

顧客ページでは、サマリーテーブルを参照します。

※横にスクロールできます

— 顧客ページでの表示（高速）
SELECT * FROM customer_summary 
WHERE customer_id = 101;

✅ 効果

顧客ページの読み込み：3秒 → 0.01秒（300倍改善!）
500万件のordersを毎回集計する代わりに、事前計算済みのデータを取得するだけ。

6-4. 解決策3：インデックス最適化

必要なインデックスを追加し、不要なインデックスを削除します。

※横にスクロールできます

— 必要なインデックスを追加
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_status ON orders(status);

— 不要なインデックスを削除
— （ほとんど使われないインデックス）
DROP INDEX idx_orders_amount;

✅ 効果

更新処理が20%高速化
インデックスを減らすことで、INSERT/UPDATEの負荷が軽減されます。

6-5. 解決策4：キャッシュ活用

よく見られる商品情報をアプリケーションキャッシュに保存します。

※横にスクロールできます

# Python での商品キャッシュ
import time

product_cache = {}
CACHE_TTL = 3600  # 1時間

def get_product(product_id):
    “””商品情報を取得（キャッシュ対応）”””
    
    # キャッシュチェック
    if product_id in product_cache:
        value, timestamp = product_cache[product_id]
        if time.time() – timestamp < CACHE_TTL:
            return value  # キャッシュから返す
    
    # DBから取得
    cursor.execute(
        "SELECT * FROM products WHERE product_id = ?",
        (product_id,)
    )
    product = cursor.fetchone()
    
    # キャッシュに保存
    product_cache[product_id] = (product, time.time())
    
    return product

✅ 効果

商品ページ：0.5秒 → 0.05秒（10倍改善）
2回目以降のアクセスは、DBアクセスなしでキャッシュから返します。

6-6. 総合的な改善結果

✅ 総合的な改善結果

処理	改善前	改善後	改善率
月次レポート	5分	30秒	10倍
顧客ページ	3秒	0.01秒	300倍
商品ページ	0.5秒	0.05秒	10倍

6-7. 適用した技術のまとめ

使用した最適化テクニック:

パーティショニング：大量データの分割
サマリーテーブル：事前計算で高速化
インデックス最適化：必要なものだけ残す
アプリケーションキャッシュ：頻繁なアクセスを高速化
データベース設定：WALモード、キャッシュサイズ

📝 練習問題

パフォーマンスチューニングの実践問題に挑戦しましょう!

問題 1基本

ベンチマークの基本

ベンチマークを取る時に「1回目の結果を捨てる」のはなぜですか?

解答:

1回目は以下の理由で通常より遅くなるためです：

コールドスタート：接続直後は初期化処理が含まれる
クエリプラン：初回はクエリの解析・最適化処理が入る
キャッシュなし：初回はディスクから読み込む

2回目以降の方が「通常の使用状況」に近いため、1回目は「ウォームアップ」として除外します。

問題 2基本

WALモードのメリット

WALモードの主なメリットを3つ挙げてください。

解答:

読み込みと書き込みが同時に可能（並列処理が改善）
書き込みが高速（ログを追記するだけ）
ロック競合が減る（同時アクセスに強い）

設定方法: PRAGMA journal_mode = WAL;

問題 3基本

キャッシュの効果

同じクエリを2回実行すると、2回目が速くなるのはなぜですか?

解答:

1回目はディスクからデータを読むのに対し、2回目はメモリにキャッシュされたデータを読むからです。

ディスク読み込み：遅い（ミリ秒単位）
メモリ読み込み：速い（マイクロ秒単位）

この差は100〜1000倍になることもあります。

問題 4応用

トランザクションの効果

10万件のINSERTを行う場合、トランザクションでまとめると何倍速くなりますか?

解答: 100〜1000倍

理由:

自動コミット：10万回のディスク書き込み → 5〜10分
トランザクション：1回のディスク書き込み → 数秒

※横にスクロールできます

— ✅ 速い方法
BEGIN TRANSACTION;
INSERT INTO customers (…) VALUES (…);
INSERT INTO customers (…) VALUES (…);
— 10万件繰り返し
COMMIT;  — 1回のディスク書き込み

問題 5応用

パーティショニングの効果

500万件の注文データを年ごとに分割した場合、2024年のデータだけを検索すると何倍速くなる可能性がありますか?

解答: 約5倍（5年分のデータがある場合）

理由:

パーティションなし：500万件全てをスキャン
パーティションあり：2024年分の約100万件だけをスキャン

分割数に応じて、読み込むデータ量が1/N（Nは分割数）になります。

問題 6応用

サマリーテーブルの活用

「顧客ページで注文件数と合計金額を表示する」場合、毎回500万件の注文データから集計するのは遅いです。どう改善しますか?

解答: サマリーテーブルを使う

※横にスクロールできます

— 1. サマリーテーブルを作成
CREATE TABLE customer_summary (
    customer_id INTEGER PRIMARY KEY,
    total_orders INTEGER,
    total_amount INTEGER,
    last_order_date TEXT,
    updated_at TEXT
);

— 2. 夜間バッチで事前計算
INSERT OR REPLACE INTO customer_summary
SELECT 
    customer_id,
    COUNT(*),
    SUM(amount),
    MAX(order_date),
    datetime(‘now’)
FROM orders
GROUP BY customer_id;

— 3. 顧客ページでは高速に取得
SELECT * FROM customer_summary WHERE customer_id = 101;

毎回500万件を集計する代わりに、事前計算済みのデータを1行取得するだけ。300倍以上の高速化が可能です。

問題 7チャレンジ

最適な設定の適用

SQLiteの接続時に適用すべき推奨設定を、PRAGMAコマンドで5つ書いてください。

※横にスクロールできます

— 1. WALモードを有効化（最重要）
PRAGMA journal_mode = WAL;

— 2. 同期モードをNORMALに（高速化）
PRAGMA synchronous = NORMAL;

— 3. キャッシュサイズを増やす（64MB）
PRAGMA cache_size = -64000;

— 4. 一時ファイルをメモリに
PRAGMA temp_store = MEMORY;

— 5. 外部キー制約を有効化
PRAGMA foreign_keys = ON;

問題 8チャレンジ

総合的な最適化

以下のクエリが45秒かかっています。改善策を3つ以上挙げてください。
SELECT p.product_name, COUNT(*), SUM(od.quantity * od.unit_price) FROM products p JOIN order_details od ON p.product_id = od.product_id JOIN orders o ON od.order_id = o.order_id WHERE o.order_date >= '2024-01-01' GROUP BY p.product_id, p.product_name ORDER BY 3 DESC;

改善策:

order_dateにインデックスを追加
CREATE INDEX idx_orders_date ON orders(order_date);
order_detailsのorder_idにインデックスを追加
CREATE INDEX idx_order_details_order ON order_details(order_id);
複合インデックスを作成
CREATE INDEX idx_orders_date_id ON orders(order_date, order_id);
カバリングインデックスを作成
CREATE INDEX idx_order_details_full ON order_details(order_id, product_id, quantity, unit_price);
統計情報を更新
ANALYZE;

これらの改善で、45秒 → 0.8秒（56倍改善）が期待できます。

📝 Step 19 のまとめ

✅ 学んだこと

実際のクエリを診断・改善する手順
ベンチマークの正しい取り方（5原則）
キャッシュの理解と活用（DB/アプリケーション）
パーティショニングで大量データを扱う
データベース設定の最適化（WALモード、キャッシュサイズ）
総合的な最適化の実践

📌 チューニングの手順

測定：現状のパフォーマンスを記録
分析：実行計画でボトルネックを特定
改善：インデックス、クエリ、設定を最適化
検証：改善効果を数値で確認
監視：継続的にパフォーマンスを監視

💡 最適化の優先順位

クエリの改善：N+1問題、不要なDISTINCT
インデックス：適切なインデックスを作成
DB設定：WALモード、キャッシュサイズ
キャッシュ：アプリケーションレベル
パーティショニング：大量データの場合
サマリーテーブル：頻繁な集計の場合

❓ よくある質問

Q1: どのくらい改善すれば十分ですか?

目安として、ユーザー向けの処理は0.5秒以内を目指しましょう。ユーザーは1秒以上待つとストレスを感じます。ただし、複雑なレポートなど時間がかかる処理は、非同期で実行するのも手です。

Q2: WALモードのデメリットは?

ファイルが増えることと、ネットワークファイルシステム（NFS）では使えないことです。ただし、ほとんどの場合でメリットの方が大きいので、WALモードを推奨します。

Q3: キャッシュの有効期限はどう決める?

更新頻度で決めます：

商品情報：1時間〜1日
ユーザー情報：5分〜30分
在庫情報：1分〜5分
静的コンテンツ：1週間〜1ヶ月

Q4: パーティショニングはいつ使うべき?

数百万件以上のデータで、時系列での検索や削除が多い場合に有効です。また、古いデータを定期的に削除する場合は、パーティション単位で削除できるので非常に便利です。

Q5: トランザクションを使うと遅くなる?

逆です。トランザクションを使う方が速いです。特に大量のINSERT/UPDATEでは、トランザクションを使わないと1件ごとにディスクに書き込むため、100〜1000倍遅くなります。

Q6: 本番環境でベンチマークを取っても大丈夫?

推奨しません。本番環境では他のユーザーの処理が動いているため、正確な測定ができません。必ずテスト環境で、本番と同じデータ量で測定してください。

🎓 次のステップでは

Step 20: 総合演習プロジェクトでは、これまで学んだ全ての技術を使って、実際のビジネスシナリオに基づいた総合的な演習を行います!

📝

学習メモ

SQL応用・パフォーマンス最適化 - Step 19

📋 過去のメモ一覧 ▼