データベースパフォーマンスチューニング完全ガイド：クエリ最適化、インデックス戦略、コネクションプール管理

リクエストフローにおけるボトルネック:
Client -> CDN -> Load Balancer -> App Server -> [Database]
                                                  ^
                                           大半の遅延がここで発生

パフォーマンス改善効果ピラミッド:

        /\
       /  \        クエリ最適化（最大効果）
      /____\
     /      \      インデックス戦略
    /________\
   /          \    スキーマ設計
  /____________\
 /              \  ハードウェア/設定チューニング
/________________\ キャッシング/読み取りレプリカ

-- PostgreSQL
EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2025-01-01'
  AND o.total > 100;

QUERY PLAN
---------------------------------------------------------------------------
Nested Loop  (cost=0.85..892.45 rows=23 width=48)
             (actual time=0.045..2.341 rows=19 loops=1)
  ->  Index Scan using idx_orders_created_at on orders o
        (cost=0.42..445.23 rows=23 width=20)
        (actual time=0.025..1.123 rows=19 loops=1)
        Filter: (total > 100)
        Rows Removed by Filter: 5
  ->  Index Scan using users_pkey on users u
        (cost=0.42..8.44 rows=1 width=36)
        (actual time=0.008..0.008 rows=1 loops=19)
Planning Time: 0.234 ms
Execution Time: 2.456 ms

-- 1. Seq Scan（順次スキャン）- テーブル全体を読み取り
-- 小さいテーブルや大部分の行を読む必要がある場合に適切
EXPLAIN ANALYZE SELECT * FROM small_table;

-- 2. Index Scan - インデックスで行の位置を見つけてテーブルにアクセス
-- 選択性が高い（少ない行を返す）クエリに適切
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- 3. Index Only Scan - インデックスだけで結果を返す（テーブルアクセス不要）
-- Covering Index使用時
EXPLAIN ANALYZE SELECT id, email FROM users WHERE email = 'user@example.com';

-- 4. Bitmap Index Scan - インデックスからビットマップを作成してテーブルにアクセス
-- 中程度の選択性のクエリに適切
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending' AND total > 100;

スキャンタイプのパフォーマンス順序（一般的）:

Index Only Scan  >  Index Scan  >  Bitmap Scan  >  Seq Scan
（最速）                                            （最遅）

-- PostgreSQL EXPLAINオプション
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE user_id = 42;

-- BUFFERS: I/O情報を含む
-- FORMAT JSON: JSON形式で出力
-- VERBOSE: 追加情報を含む
-- COSTS: コスト推定値を含む（デフォルトON）
-- TIMING: 時間情報を含む（デフォルトON）

-- MySQL EXPLAINオプション
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

-- MySQL 8.0+でツリー形式をサポート
EXPLAIN FORMAT=TREE
SELECT * FROM orders WHERE user_id = 42;

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

-- 複合インデックス（カラム順序が重要！）
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 複合インデックス活用ルール（Leftmost Prefix Rule）
-- idx_orders_user_dateは以下のクエリに活用:
-- WHERE user_id = 1                          (O)
-- WHERE user_id = 1 AND created_at > '2025'  (O)
-- WHERE created_at > '2025'                  (X) 先頭カラムなし

-- 全文検索GINインデックス
CREATE INDEX idx_articles_search
ON articles USING GIN(to_tsvector('english', title || ' ' || body));

-- JSONB GINインデックス
CREATE INDEX idx_events_data ON events USING GIN(metadata);

-- JSONB特定パスインデックス
CREATE INDEX idx_events_type ON events USING GIN((metadata -> 'type'));

-- 使用例
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgresql & tuning');

SELECT * FROM events
WHERE metadata @> '{"type": "click", "page": "/home"}';

-- BRINインデックス - 時系列データに非常に効率的
-- B-Treeより100倍以上小さいサイズ
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

-- 物理的な並び順と相関が高い場合に効果的
-- 確認方法:
SELECT correlation
FROM pg_stats
WHERE tablename = 'logs' AND attname = 'created_at';
-- correlationが1に近ければBRINが適切

-- 部分インデックス（Partial Index）- 特定条件の行のみインデックス化
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';
-- 全体行の5%のみインデックス化 -> サイズ95%節約

-- 式インデックス（Expression Index）
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));
-- WHERE LOWER(email) = 'user@example.com' クエリに活用

-- カバリングインデックス（Covering Index）
CREATE INDEX idx_orders_covering
ON orders(user_id, created_at) INCLUDE (total, status);
-- Index Only Scanが可能 -> テーブルアクセス不要

インデックス設計チェックリスト:

必須確認:
  [x] WHERE句で頻繁に使用されるカラムにインデックス
  [x] JOIN条件カラムにインデックス
  [x] ORDER BYカラムがインデックスに含まれる
  [x] 複合インデックスのカラム順序を最適化
  [x] 選択性（Selectivity）が高いカラムを優先

注意事項:
  [x] 書き込みパフォーマンス vs 読み取りパフォーマンスのトレードオフ
  [x] 使用されていないインデックスを削除
  [x] インデックスサイズを監視
  [x] インデックスブロートを管理（PostgreSQL）

-- pg_stat_statementsの有効化（postgresql.conf）
-- shared_preload_libraries = 'pg_stat_statements'

-- 拡張のインストール
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 遅いクエリTop 10（総時間基準）
SELECT
  substring(query, 1, 80) AS short_query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- 平均実行時間が長いクエリ
SELECT
  substring(query, 1, 100) AS short_query,
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  rows / NULLIF(calls, 0) AS avg_rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 10;

# my.cnf - Slow Query Log設定
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1.0             # 1秒以上のクエリを記録
log_queries_not_using_indexes = 1 # インデックス未使用クエリを記録
min_examined_row_limit = 100      # 最低100行以上検査したクエリ

# mysqldumpslowでスロークエリ分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# pt-query-digest（Percona Toolkit）
pt-query-digest /var/log/mysql/slow-query.log \
  --limit 10 \
  --order-by query_time:sum

-- PostgreSQL: リアルタイムの長時間クエリ確認
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 seconds'
  AND state != 'idle'
ORDER BY duration DESC;

-- ロック待ちクエリの確認
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.database IS NOT DISTINCT FROM bl.database
  AND kl.relation IS NOT DISTINCT FROM bl.relation
JOIN pg_stat_activity blocking ON kl.pid = blocking.pid
WHERE NOT bl.granted AND kl.granted;

# N+1問題の例（Python/SQLAlchemy）

# BAD: N+1クエリ発生
users = session.query(User).all()  # 1回のクエリ
for user in users:
    print(user.orders)  # N回の追加クエリ（ユーザーごとに1回）

# 実際に実行されるSQL:
# SELECT * FROM users;                    -- 1回
# SELECT * FROM orders WHERE user_id = 1;  -- +1
# SELECT * FROM orders WHERE user_id = 2;  -- +1
# SELECT * FROM orders WHERE user_id = 3;  -- +1
# ...（N人のユーザー）

# 解決1: Eager Loading（JOIN）
users = session.query(User).options(
    joinedload(User.orders)
).all()
# SQL: SELECT * FROM users LEFT JOIN orders ON ...  -- 1回のクエリ

# 解決2: Subquery Loading
users = session.query(User).options(
    subqueryload(User.orders)
).all()
# SQL: SELECT * FROM users;                         -- 1回
# SQL: SELECT * FROM orders WHERE user_id IN (...);  -- 1回

# 解決3: selectinload（SQLAlchemy推奨）
users = session.query(User).options(
    selectinload(User.orders)
).all()

// Node.js（Prisma）- N+1解決
// BAD: N+1
const users = await prisma.user.findMany();
for (const user of users) {
  const orders = await prisma.order.findMany({
    where: { userId: user.id }
  });
}

// GOOD: includeで解決
const users = await prisma.user.findMany({
  include: { orders: true }
});

// GOOD: 必要なフィールドのみ選択
const users = await prisma.user.findMany({
  select: {
    id: true,
    name: true,
    orders: {
      select: { id: true, total: true },
      where: { total: { gt: 100 } }
    }
  }
});

// Java（JPA/Hibernate）
// BAD: LAZYローディングでN+1発生
@Entity
public class User {
    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)
    private List<Order> orders;
}

// GOOD 1: JPQL JOIN FETCH
@Query("SELECT u FROM User u JOIN FETCH u.orders")
List<User> findAllWithOrders();

// GOOD 2: EntityGraph
@EntityGraph(attributePaths = {"orders"})
@Query("SELECT u FROM User u")
List<User> findAllWithOrders();

// GOOD 3: BatchSize（Hibernate）
@Entity
public class User {
    @OneToMany(mappedBy = "user")
    @BatchSize(size = 100)
    private List<Order> orders;
}

# Spring Boot - Hibernate N+1検出
# application.yml
spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
        session.events.log.LOG_QUERIES_SLOWER_THAN_MS: 25

logging:
  level:
    org.hibernate.SQL: DEBUG
    org.hibernate.stat: DEBUG

# Django - django-debug-toolbarまたはnplusone
# settings.py
INSTALLED_APPS = [
    'nplusone.ext.django',
]

NPLUSONE_RAISE = True  # 開発環境でN+1検出時にエラーを発生

コネクションプールなし:
Request -> [新規接続作成 30ms] -> [クエリ 5ms] -> [接続切断] = 35ms

コネクションプール使用:
Request -> [プールから既存接続取得 0.1ms] -> [クエリ 5ms] -> [プールに返却] = 5.1ms

# application.yml - HikariCP最適設定
spring:
  datasource:
    hikari:
      # プールサイズ公式: connections = (core_count * 2) + spinning_disks
      # 4コアSSDサーバー: (4 * 2) + 0 = 8
      maximum-pool-size: 10
      minimum-idle: 5

      # コネクションタイムアウト（プールからの最大待機時間）
      connection-timeout: 30000   # 30秒

      # アイドルコネクション削除時間
      idle-timeout: 600000        # 10分

      # コネクション最大存続時間
      max-lifetime: 1800000       # 30分

      # コネクション検証クエリ
      connection-test-query: "SELECT 1"

      # コネクションリーク検出
      leak-detection-threshold: 60000  # 60秒

      # プール名（モニタリング用）
      pool-name: "MyApp-HikariPool"

# pgbouncer.ini
[databases]
myapp = host=localhost port=5432 dbname=myapp

[pgbouncer]
# プーリングモード
# session: セッション維持（デフォルト）
# transaction: トランザクション単位（推奨）
# statement: ステートメント単位（制限的）
pool_mode = transaction

# プールサイズ
default_pool_size = 20
min_pool_size = 5
reserve_pool_size = 5

# 接続制限
max_client_conn = 200
max_db_connections = 50

# タイムアウト
server_idle_timeout = 600
client_idle_timeout = 300
query_timeout = 30
client_login_timeout = 60

# 認証
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# ログ
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1
stats_period = 60

# 管理インターフェース
listen_addr = 0.0.0.0
listen_port = 6432
admin_users = pgbouncer

-- PgBouncerステータス確認
SHOW pools;
SHOW stats;
SHOW servers;
SHOW clients;

-- PostgreSQL接続数確認
SELECT count(*) AS total_connections,
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       count(*) FILTER (WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_stat_activity
WHERE backend_type = 'client backend';

-- 最大接続数の確認
SHOW max_connections;

-- アンチパターン1: SELECT *
-- BAD
SELECT * FROM users WHERE id = 1;
-- GOOD
SELECT id, name, email FROM users WHERE id = 1;

-- アンチパターン2: 関数使用によるインデックス無効化
-- BAD（インデックス使用不可）
SELECT * FROM users WHERE YEAR(created_at) = 2025;
-- GOOD（インデックス使用可能）
SELECT * FROM users
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';

-- アンチパターン3: LIKEの先頭ワイルドカード
-- BAD（インデックス使用不可）
SELECT * FROM products WHERE name LIKE '%phone%';
-- GOOD（全文検索インデックス活用）
SELECT * FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('phone');

-- アンチパターン4: ORの代わりにUNION ALL
-- BAD（非効率なインデックス使用）
SELECT * FROM orders WHERE user_id = 1 OR status = 'pending';
-- GOOD
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND user_id != 1;

-- アンチパターン5: 大きなIN句
-- BAD
SELECT * FROM products WHERE id IN (1, 2, 3, ..., 10000);
-- GOOD（一時テーブルまたはANY使用）
SELECT * FROM products WHERE id = ANY(ARRAY[1, 2, 3, ...]);
-- または一時テーブルJOIN

-- アンチパターン6: 不必要なDISTINCT
-- BAD
SELECT DISTINCT u.name FROM users u JOIN orders o ON u.id = o.user_id;
-- GOOD（EXISTS使用）
SELECT u.name FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- アンチパターン7: COUNT(*) vs EXISTS
-- BAD（全行カウント）
SELECT CASE WHEN COUNT(*) > 0 THEN true ELSE false END
FROM orders WHERE user_id = 1;
-- GOOD（最初の行のみ確認）
SELECT EXISTS(SELECT 1 FROM orders WHERE user_id = 1);

-- アンチパターン8: 暗黙的な型変換
-- BAD（文字列カラムに数値比較 -> インデックス無効）
SELECT * FROM users WHERE phone = 01012345678;
-- GOOD
SELECT * FROM users WHERE phone = '01012345678';

-- アンチパターン9: OFFSETページネーション
-- BAD（大きなOFFSETは遅い）
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- GOOD（キーセットページネーション）
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

-- アンチパターン10: 過度なサブクエリ
-- BAD
SELECT * FROM users WHERE id IN (
  SELECT user_id FROM orders WHERE total > (
    SELECT AVG(total) FROM orders
  )
);
-- GOOD（CTEまたはJOIN）
WITH avg_total AS (SELECT AVG(total) AS avg_val FROM orders)
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
CROSS JOIN avg_total
WHERE o.total > avg_total.avg_val;

-- JOIN順序の最適化（小さいテーブルを先に）
-- PostgreSQLは自動最適化するが、ヒントが必要な場合:

-- テーブルサイズ確認
SELECT relname, reltuples::bigint AS row_count
FROM pg_class
WHERE relname IN ('users', 'orders', 'products')
ORDER BY reltuples DESC;

-- JOINでインデックスがあるカラムを使用
-- GOOD
SELECT u.name, o.total
FROM orders o
JOIN users u ON u.id = o.user_id  -- users.idはPK（インデックス）
WHERE o.status = 'completed';

-- Lateral Joinの活用（上位N件パターン）
SELECT u.name, recent_orders.*
FROM users u
CROSS JOIN LATERAL (
  SELECT id, total, created_at
  FROM orders
  WHERE user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) recent_orders;

-- Rangeパーティショニング（時系列データに最適）
CREATE TABLE orders (
  id bigserial,
  user_id bigint NOT NULL,
  total decimal(10,2),
  status varchar(20),
  created_at timestamp NOT NULL
) PARTITION BY RANGE (created_at);

-- 月別パーティション作成
CREATE TABLE orders_2025_01
  PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders_2025_02
  PARTITION OF orders
  FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- 自動パーティション作成（pg_partman活用）
-- CREATE EXTENSION pg_partman;
SELECT partman.create_parent(
  'public.orders',
  'created_at',
  'native',
  'monthly'
);

-- Listパーティショニング（カテゴリ別分割）
CREATE TABLE products (
  id bigserial,
  name varchar(255),
  category varchar(50),
  price decimal(10,2)
) PARTITION BY LIST (category);

CREATE TABLE products_electronics
  PARTITION OF products
  FOR VALUES IN ('electronics', 'computers', 'phones');

CREATE TABLE products_clothing
  PARTITION OF products
  FOR VALUES IN ('clothing', 'shoes', 'accessories');

-- Hashパーティショニング（均等分配）
CREATE TABLE sessions (
  id uuid,
  user_id bigint,
  data jsonb,
  expires_at timestamp
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_p0
  PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE sessions_p1
  PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE sessions_p2
  PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE sessions_p3
  PARTITION OF sessions
  FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- MySQL Rangeパーティショニング
CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  total DECIMAL(10,2),
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

書き込み/読み取り分離アーキテクチャ:

       書き込みリクエスト      読み取りリクエスト
          |                       |
     [Primary]     ->      [Replica 1]
          |                 [Replica 2]
          |                 [Replica 3]
     非同期レプリケーション

# Spring Boot - 読み取り/書き込み分離
spring:
  datasource:
    primary:
      url: jdbc:postgresql://primary:5432/myapp
      username: app_write
    replica:
      url: jdbc:postgresql://replica:5432/myapp
      username: app_read

# Django - 読み取り/書き込みルーター
# settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'primary.db.example.com',
        'NAME': 'myapp',
    },
    'replica': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'replica.db.example.com',
        'NAME': 'myapp',
    }
}

DATABASE_ROUTERS = ['myapp.routers.ReadReplicaRouter']

# routers.py
class ReadReplicaRouter:
    def db_for_read(self, model, **hints):
        return 'replica'

    def db_for_write(self, model, **hints):
        return 'default'

# Redisキャッシングパターン
import redis
import json
from functools import wraps

r = redis.Redis(host='localhost', port=6379, db=0)

# Cache-Aside（Lazy Loading）パターン
def cache_aside(key_prefix, ttl=3600):
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            cache_key = f"{key_prefix}:{args}:{kwargs}"

            # 1. キャッシュから検索
            cached = r.get(cache_key)
            if cached:
                return json.loads(cached)

            # 2. DBから検索
            result = func(*args, **kwargs)

            # 3. キャッシュに保存
            r.setex(cache_key, ttl, json.dumps(result))

            return result
        return wrapper
    return decorator

@cache_aside("user", ttl=1800)
def get_user_profile(user_id):
    return db.query("SELECT * FROM users WHERE id = %s", [user_id])

# Write-Throughパターン
def update_user(user_id, data):
    # 1. DB更新
    db.execute("UPDATE users SET name=%s WHERE id=%s", [data['name'], user_id])

    # 2. キャッシュ更新（または削除）
    cache_key = f"user:{user_id}"
    r.delete(cache_key)  # Cache Invalidation
    # または
    r.setex(cache_key, 1800, json.dumps(data))  # Cache Update

キャッシング戦略比較:

Cache-Aside:
  メリット: 最も一般的、柔軟
  デメリット: 初回リクエストはキャッシュミス

Write-Through:
  メリット: キャッシュが常に最新
  デメリット: 書き込み遅延の増加

Write-Behind（Write-Back）:
  メリット: 最高の書き込みパフォーマンス
  デメリット: データ損失リスク

Read-Through:
  メリット: キャッシュロジックが分離
  デメリット: 実装の複雑さ

-- 手動VACUUM
VACUUM (VERBOSE) orders;

-- VACUUM FULL（テーブル再書き込み - ロック発生！）
VACUUM FULL orders;

-- 統計更新
ANALYZE orders;

-- VACUUM + ANALYZE同時実行
VACUUM ANALYZE orders;

# postgresql.conf - autovacuum設定
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 60

# テーブルの20%変更時にautovacuum実行
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 50

# テーブルの10%変更時にautoanalyze実行
autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50

# autovacuum速度調整（I/O負荷）
autovacuum_vacuum_cost_delay = 2ms
autovacuum_vacuum_cost_limit = 200

-- HOT Update条件:
-- 1. 更新されたカラムにインデックスがない
-- 2. 新しい行が同じページに保存できる

-- HOT比率の確認
SELECT
  relname,
  n_tup_upd,
  n_tup_hot_upd,
  round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS hot_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC;

-- HOT比率を高めるには:
-- 1. 頻繁に更新されるカラムのインデックスを最小化
-- 2. fillfactorを下げて同じページにスペースを確保
ALTER TABLE orders SET (fillfactor = 80);

-- テーブル別I/O統計
SELECT
  relname,
  seq_scan,
  idx_scan,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_live_tup,
  n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- インデックス使用率
SELECT
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan;

-- 未使用インデックス（削除候補）
SELECT
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

-- キャッシュヒット率
SELECT
  sum(heap_blks_read) AS heap_read,
  sum(heap_blks_hit) AS heap_hit,
  round(100.0 * sum(heap_blks_hit) / NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 2) AS hit_pct
FROM pg_statio_user_tables;
-- 99%以上が理想的

# my.cnf - InnoDB最適化
[mysqld]
# Buffer Poolサイズ（全メモリの70~80%）
innodb_buffer_pool_size = 8G

# Buffer Poolインスタンス（8G以上で分割）
innodb_buffer_pool_instances = 8

# ログファイルサイズ
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M

# I/Oスレッド
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# 同時実行性
innodb_thread_concurrency = 0  # 自動

# フラッシュ方法
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1  # 安全（2: パフォーマンス優先）

# 一時テーブル
tmp_table_size = 256M
max_heap_table_size = 256M

-- Buffer Poolステータス確認
SHOW ENGINE INNODB STATUS\G

-- Buffer Poolヒット率
SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_ratio;
-- 99%以上が理想的

MySQL 8.0 Query Cache代替:

1. アプリケーションレベルキャッシング（Redis/Memcached）
2. ProxySQL クエリキャッシュ
3. MySQL Routerキャッシング
4. InnoDB Buffer Pool最適化

-- MySQL 8.0 Performance Schema活用
-- スロークエリ分析
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT/1000000000, 2) AS total_ms,
  ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- インデックスヒント（オプティマイザが間違った選択をする場合）
SELECT * FROM orders USE INDEX (idx_orders_user_date)
WHERE user_id = 1 AND created_at > '2025-01-01';

SELECT * FROM orders FORCE INDEX (idx_orders_status)
WHERE status = 'pending';

-- MySQL 8.0オプティマイザヒント
SELECT /*+ NO_INDEX(orders idx_orders_status) */
  * FROM orders WHERE user_id = 1;

SELECT /*+ JOIN_ORDER(users, orders) */
  u.name, o.total
FROM users u JOIN orders o ON u.id = o.user_id;