第7章 — データベースキャッシュ: 最後の砦

-- shared_buffers の設定
ALTER SYSTEM SET shared_buffers = '4GB';  -- 16GB RAM の場合
 
-- キャッシュヒット率の確認
SELECT 
  sum(blks_hit) AS cache_hits,
  sum(blks_read) AS disk_reads,
  round(sum(blks_hit) / 
    (sum(blks_hit) + sum(blks_read))::numeric * 100, 2) 
    AS hit_ratio
FROM pg_stat_database;

-- 大きなソートが必要なクエリの前に
SET work_mem = '256MB';
SELECT * FROM products ORDER BY popularity DESC LIMIT 1000;
RESET work_mem;

-- バッファプールサイズの設定
SET GLOBAL innodb_buffer_pool_size = 12884901888;  -- 12GB
 
-- ヒット率の確認
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

# ❌ N+1: 商品100件 → 101クエリ
products = Product.all
products.each do |p|
  puts p.category.name  # 各商品で1クエリ
end
 
# ✅ Eager Loading: 2クエリ
products = Product.includes(:category).all
products.each do |p|
  puts p.category.name  # キャッシュ済み
end

CREATE MATERIALIZED VIEW popular_products AS
SELECT p.id, p.name, p.price, 
       COUNT(oi.id) as order_count
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.id
ORDER BY order_count DESC;
 
-- 定期的にリフレッシュ
REFRESH MATERIALIZED VIEW CONCURRENTLY popular_products;