sql-optimization-patterns

Name: sql-optimization-patterns
Author: ovachiever

github.com/ovachieverデータ・データベース5644 回インストール48 週間前に更新

sql-optimization-patterns について

sql-optimization-patternsは、データベース開発者向けのSQL最適化スキルです。クエリプラン分析、インデックス設計、クエリの書き換えに加え、PostgreSQLとMySQL/MariaDBのガイドやスクリプトを含みます。

主な機能

01EXPLAINクエリプランの分析
02インデックス戦略の選択
03非効率なクエリの書き換え
04N+1クエリとページネーションへの対処
05書き込み処理のバッチ化
06スロークエリ分析スクリプト

活用シーン

01遅いSQLクエリの調査
02データベーステーブルのインデックス設計
03アプリケーションのN+1クエリ削減
04大規模データのページネーション最適化

インストール

コマンドをコピーしてターミナルで実行します。

$npx skills add https://github.com/ovachiever/droid-tings --skill sql-optimization-patterns

インストール後、Claude Code で/sql-optimization-patternsを入力して読み込まれたか確認します。

詳細

SQL 最適化パターン

体系的な最適化、適切なインデックス作成、クエリプランの分析を通じて、遅いデータベースクエリを高速な処理に変換します。

このスキルを使用する場面

実行に時間がかかるクエリのデバッグ
パフォーマンスの高いデータベーススキーマの設計
アプリケーションの応答時間の最適化
データベースの負荷とコストの削減
増加するデータセットに対するスケーラビリティの向上
EXPLAIN クエリプランの分析
効率的なインデックスの実装
N+1 クエリ問題の解決

基本概念

1. クエリ実行プラン（EXPLAIN）

EXPLAIN の出力を理解することは、最適化の基本です。

PostgreSQL EXPLAIN:

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- With actual execution stats
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';

-- Verbose output with more details
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days';

確認すべき主なメトリクス:

Seq Scan：テーブル全体のスキャン（大きなテーブルでは通常遅くなります）
Index Scan：インデックスを使用（良好）
Index Only Scan：テーブルにアクセスせずインデックスを使用（最良）
Nested Loop：結合方式（小さなデータセットに適しています）
Hash Join：結合方式（大きなデータセットに適しています）
Merge Join：結合方式（ソート済みデータに適しています）
Cost：クエリの推定コスト（低いほど良好）
Rows：返される行数の推定値
Actual Time：実際の実行時間

2. インデックス戦略

インデックスは最も強力な最適化ツールです。

インデックスの種類:

B-Tree：デフォルト。等価検索と範囲検索に適しています
Hash：等価（=）比較専用です
GIN：全文検索、配列クエリ、JSONB に使用します
GiST：幾何データ、全文検索に使用します
BRIN：相関のある非常に大きなテーブル向けのブロック範囲インデックスです

-- Standard B-Tree index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index (index subset of rows)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

-- Covering index (include additional columns)
CREATE INDEX idx_users_email_covering ON users(email)
INCLUDE (name, created_at);

-- Full-text search index
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || body));

-- JSONB index
CREATE INDEX idx_metadata ON events USING GIN(metadata);

3. クエリ最適化パターン

SELECT * を避ける:

-- Bad: Fetches unnecessary columns
SELECT * FROM users WHERE id = 123;

-- Good: Fetch only what you need
SELECT id, email, name FROM users WHERE id = 123;

WHERE 句を効率的に使う:

-- Bad: Function prevents index usage
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Good: Create functional index or use exact match
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Then:
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- Or store normalized data
SELECT * FROM users WHERE email = '[email protected]';

JOIN を最適化する:

-- Bad: Cartesian product then filter
SELECT u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id AND u.created_at > '2024-01-01';

-- Good: Filter before join
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01';

-- Better: Filter both tables
SELECT u.name, o.total
FROM (SELECT * FROM users WHERE created_at > '2024-01-01') u
JOIN orders o ON u.id = o.user_id;

最適化パターン

パターン 1: N+1 クエリを解消する

問題：N+1 クエリのアンチパターン

# Bad: Executes N+1 queries
users = db.query("SELECT * FROM users LIMIT 10")
for user in users:
    orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
    # Process orders

解決策：JOIN またはバッチ読み込みを使う

-- Solution 1: JOIN
SELECT
    u.id, u.name,
    o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.id IN (1, 2, 3, 4, 5);

-- Solution 2: Batch query
SELECT * FROM orders
WHERE user_id IN (1, 2, 3, 4, 5);

# Good: Single query with JOIN or batch load
# Using JOIN
results = db.query("""
    SELECT u.id, u.name, o.id as order_id, o.total
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id IN (1, 2, 3, 4, 5)
""")

# Or batch load
users = db.query("SELECT * FROM users LIMIT 10")
user_ids = [u.id for u in users]
orders = db.query(
    "SELECT * FROM orders WHERE user_id IN (?)",
    user_ids
)
# Group orders by user_id
orders_by_user = {}
for order in orders:
    orders_by_user.setdefault(order.user_id, []).append(order)

パターン 2: ページネーションを最適化する

非推奨：大きなテーブルでの OFFSET

-- Slow for large offsets
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;  -- Very slow!

推奨：カーソルベースのページネーション

-- Much faster: Use cursor (last seen ID)
SELECT * FROM users
WHERE created_at < '2024-01-15 10:30:00'  -- Last cursor
ORDER BY created_at DESC
LIMIT 20;

-- With composite sorting
SELECT * FROM users
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Requires index
CREATE INDEX idx_users_cursor ON users(created_at DESC, id DESC);

パターン 3: 集計を効率化する

COUNT クエリを最適化する:

-- Bad: Counts all rows
SELECT COUNT(*) FROM orders;  -- Slow on large tables

-- Good: Use estimates for approximate counts
SELECT reltuples::bigint AS estimate
FROM pg_class
WHERE relname = 'orders';

-- Good: Filter before counting
SELECT COUNT(*) FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';

-- Better: Use index-only scan
CREATE INDEX idx_orders_created ON orders(created_at);
SELECT COUNT(*) FROM orders
WHERE created_at > NOW() - INTERVAL '7 days';

GROUP BY を最適化する:

-- Bad: Group by then filter
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 10;

-- Better: Filter first, then group (if possible)
SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING COUNT(*) > 10;

-- Best: Use covering index
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

パターン 4: サブクエリを最適化する

相関サブクエリを変換する:

-- Bad: Correlated subquery (runs for each row)
SELECT u.name, u.email,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count
FROM users u;

-- Good: JOIN with aggregation
SELECT u.name, u.email, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name, u.email;

-- Better: Use window functions
SELECT DISTINCT ON (u.id)
    u.name, u.email,
    COUNT(o.id) OVER (PARTITION BY u.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

明確さを高めるために CTE を使う:

-- Using Common Table Expressions
WITH recent_users AS (
    SELECT id, name, email
    FROM users
    WHERE created_at > NOW() - INTERVAL '30 days'
),
user_order_counts AS (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT ru.name, ru.email, COALESCE(uoc.order_count, 0) as orders
FROM recent_users ru
LEFT JOIN user_order_counts uoc ON ru.id = uoc.user_id;

パターン 5: バッチ処理

INSERT をバッチ処理する:

-- Bad: Multiple individual inserts
INSERT INTO users (name, email) VALUES ('Alice', '[email protected]');
INSERT INTO users (name, email) VALUES ('Bob', '[email protected]');
INSERT INTO users (name, email) VALUES ('Carol', '[email protected]');

-- Good: Batch insert
INSERT INTO users (name, email) VALUES
    ('Alice', '[email protected]'),
    ('Bob', '[email protected]'),
    ('Carol', '[email protected]');

-- Better: Use COPY for bulk inserts (PostgreSQL)
COPY users (name, email) FROM '/tmp/users.csv' CSV HEADER;

UPDATE をバッチ処理する:

-- Bad: Update in loop
UPDATE users SET status = 'active' WHERE id = 1;
UPDATE users SET status = 'active' WHERE id = 2;
-- ... repeat for many IDs

-- Good: Single UPDATE with IN clause
UPDATE users
SET status = 'active'
WHERE id IN (1, 2, 3, 4, 5, ...);

-- Better: Use temporary table for large batches
CREATE TEMP TABLE temp_user_updates (id INT, new_status VARCHAR);
INSERT INTO temp_user_updates VALUES (1, 'active'), (2, 'active'), ...;

UPDATE users u
SET status = t.new_status
FROM temp_user_updates t
WHERE u.id = t.id;

高度なテクニック

マテリアライズドビュー

コストの高いクエリを事前に計算します。

-- Create materialized view
CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
    u.id,
    u.name,
    COUNT(o.id) as total_orders,
    SUM(o.total) as total_spent,
    MAX(o.created_at) as last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Add index to materialized view
CREATE INDEX idx_user_summary_spent ON user_order_summary(total_spent DESC);

-- Refresh materialized view
REFRESH MATERIALIZED VIEW user_order_summary;

-- Concurrent refresh (PostgreSQL)
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;

-- Query materialized view (very fast)
SELECT * FROM user_order_summary
WHERE total_spent > 1000
ORDER BY total_spent DESC;

パーティショニング

大きなテーブルを分割してパフォーマンスを向上させます。

-- Range partitioning by date (PostgreSQL)
CREATE TABLE orders (
    id SERIAL,
    user_id INT,
    total DECIMAL,
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');

-- Queries automatically use appropriate partition
SELECT * FROM orders
WHERE created_at BETWEEN '2024-02-01' AND '2024-02-28';
-- Only scans orders_2024_q1 partition

クエリヒントと最適化

-- Force index usage (MySQL)
SELECT * FROM users
USE INDEX (idx_users_email)
WHERE email = '[email protected]';

-- Parallel query (PostgreSQL)
SET max_parallel_workers_per_gather = 4;
SELECT * FROM large_table WHERE condition;

-- Join hints (PostgreSQL)
SET enable_nestloop = OFF;  -- Force hash or merge join

ベストプラクティス

インデックスは選択的に作成する：インデックスが多すぎると書き込みが遅くなります
クエリのパフォーマンスを監視する：スロークエリログを使います
統計情報を最新の状態に保つ：ANALYZE を定期的に実行します
適切なデータ型を使う：小さい型ほどパフォーマンスが向上します
慎重に正規化する：正規化とパフォーマンスのバランスを取ります
頻繁にアクセスされるデータをキャッシュする：アプリケーションレベルのキャッシュを使います
コネクションプーリング：データベース接続を再利用します
定期的なメンテナンス：VACUUM、ANALYZE、インデックスの再構築を行います

-- Update statistics
ANALYZE users;
ANALYZE VERBOSE orders;

-- Vacuum (PostgreSQL)
VACUUM ANALYZE users;
VACUUM FULL users;  -- Reclaim space (locks table)

-- Reindex
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

よくある落とし穴

過剰なインデックス作成：インデックスごとに INSERT/UPDATE/DELETE が遅くなります
未使用のインデックス：容量を浪費し、書き込みを遅くします
インデックスの不足：クエリが遅くなり、テーブル全体のスキャンが発生します
暗黙的な型変換：インデックスの使用を妨げます
OR 条件：インデックスを効率的に使用できません
先頭にワイルドカードがある LIKE：LIKE '%abc' はインデックスを使用できません
WHERE 句内の関数：関数インデックスがない限り、インデックスの使用を妨げます

クエリの監視

-- Find slow queries (PostgreSQL)
SELECT query, calls, total_time, mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- Find missing indexes (PostgreSQL)
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / seq_scan AS avg_seq_tup_read
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY seq_tup_read DESC
LIMIT 10;

-- Find unused indexes (PostgreSQL)
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

リソース

references/postgres-optimization-guide.md：PostgreSQL 固有の最適化
references/mysql-optimization-guide.md：MySQL/MariaDB の最適化
references/query-plan-analysis.md：EXPLAIN プランの詳細な分析
assets/index-strategy-checklist.md：インデックスを作成するタイミングと方法
assets/query-optimization-checklist.md：最適化の手順ガイド
scripts/analyze-slow-queries.sql：データベース内の遅いクエリを特定します
scripts/index-recommendations.sql：インデックスの推奨案を生成します

Markdown 対応

ログインしてコメント

基本情報

作者: ovachiever
リポジトリ: ovachiever/droid-tings
カテゴリ: データ・データベース
インストール数: 44
GitHub スター: 56

問題を報告

おすすめの Skills

すべての Skills

hyperframes-core

github.com/heygen-com

930.7k

HyperFrames コンポジションコントラクト — レンダリング可能なプロジェクトを構築します。コンポジション構造、data-* タイミング属性、class="clip"、トラック、サブコンポジション、変数、フレームワーク所有のメディア再生、決定論的レンダリングルール、検証に使用します。Tailwind プロジェクトおよび STORYBOARD.md / SCRIPT.md プラン形式もカバーします。コンポジション HTML を作成する前にお読みください。

lark-base

open.feishu.cn

830.7k

Feishu Base を操作します。テーブル、フィールド、レコード、ビューの作成、統計、数式/lookup、フォーム、ダッシュボード、アプリモード（BaseApp/AppMode のページとコンポーネント）、Workspace ディレクトリ、ワークフロー、ロール権限、テンプレートセンター（Base テンプレートのカテゴリ/一覧/検索）に対応します。Base/多次元テーブル/bitable、BaseApp/AppMode、/base/ または /app/ リンクがある場合に使用します。BaseApp に lark-apps は使用しません。ファイルのインポート/エクスポートは lark-drive、認証/認可は lark-shared に振り分けます。

lark-sheets

open.feishu.cn

829.3k

Feishu スプレッドシート：スプレッドシートの作成と操作に対応します。ワークシートと行/列構造（追加/削除/結合/サイズ/非表示/固定/グループ化）、セルの読み書き（値/数式/スタイル/コメント/セル画像）、範囲のコピー/移動/並べ替え/入力、検索と置換、一括更新、グラフ、ピボットテーブル、条件付き書式、フィルターとフィルタービュー、ドロップダウンリスト、スパークライン、浮動画像などのオブジェクトの作成と管理に対応します。数式の検証、過去バージョンへの復元、ローカルの Excel/CSV と Feishu スプレッドシート間のインポート/エクスポートも可能です。スプレッドシートの作成/編集、集計と可視化、書式設定、数式計算（Excel 数式の移行を含む）、財務モデリング（DCF、三表、予算、Sensitivity など）に使用します。多次元テーブル（Base/bitable）には lark-base を使用してください。クラウドストレージ（Drive/クラウドストレージ）内のスプレッドシートファイルを名前やキーワードで検索する場合は、まず lark-drive の drive +search でリソースを特定してください。doubao.com の /sheets/ URL/token が提示された場合も、このスキルを直接使用し、ドメインが Feishu でないことを理由に WebFetch にフォールバックしないでください。振り分けはドメインではなく URL パスの形式と token に基づきます。

remotion-best-practices

github.com/remotion-dev

708.9k

Remotion を使ってプログラム的にビデオを構築するためのベストプラクティスとドメイン知識（React/TypeScript でのビデオ、MP4 にレンダリング）。Remotion コードを書いたり編集したりするときはいつでも使用してください—ビデオプロジェクトのスカッフォルディング、interpolate/spring と useCurrentFrame() でのアニメーション、シーンとトランジションのシーケンス処理、字幕、オーディオ、GIF/Lottie/3D、チャート、ビジュアルエフェクト、calculateMetadata による動的な期間、またはレンダリング。Remotion API に手を出す前に相談してください。そのコンポーネントとプロップは進化し続けているからです（例：@remotion/media）。