-- MySQL
EXPLAIN SELECT * FROM users WHERE age = 25;
EXPLAIN ANALYZE SELECT * FROM users WHERE age = 25;  -- 실제 실행 + 통계

-- PostgreSQL
EXPLAIN SELECT * FROM users WHERE age = 25;
EXPLAIN ANALYZE SELECT * FROM users WHERE age = 25;

-- Oracle
EXPLAIN PLAN FOR SELECT * FROM users WHERE age = 25;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age = 25;

-- ✅ const (최고)
SELECT * FROM users WHERE id = 123;
-- type: const (PK로 1개 조회)

-- ✅ ref (좋음)
SELECT * FROM users WHERE age = 25;
-- type: ref (Non-Unique 인덱스)

-- ⚠️ range (보통)
SELECT * FROM users WHERE age BETWEEN 20 AND 30;
-- type: range (인덱스 범위 검색)

-- ❌ ALL (나쁨)
SELECT * FROM users WHERE YEAR(created_at) = 2025;
-- type: ALL (함수로 인해 인덱스 무효화)

-- ❌ rows가 많으면 느림
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING';
-- rows: 500000 (50만 건 스캔 예상) ⚠️

-- ✅ rows가 적으면 빠름
EXPLAIN SELECT * FROM orders WHERE order_id = 12345;
-- rows: 1 (1건만 스캔) ✅

-- ✅ Using index (Covering Index)
EXPLAIN SELECT id, name FROM users WHERE age = 25;
-- Extra: Using index
-- → 인덱스만으로 처리 (빠름!)

-- ⚠️ Using filesort
EXPLAIN SELECT * FROM users ORDER BY created_at;
-- Extra: Using filesort
-- → 정렬을 위한 추가 작업 (느림)

-- ❌ Using temporary
EXPLAIN SELECT city, COUNT(*)
FROM users
GROUP BY city
ORDER BY COUNT(*) DESC;
-- Extra: Using temporary; Using filesort
-- → 임시 테이블 + 정렬 (매우 느림!)

-- EXPLAIN (예측만)
EXPLAIN SELECT * FROM users WHERE age = 25;
-- rows: 5000 (예측)

-- EXPLAIN ANALYZE (실제 실행)
EXPLAIN ANALYZE SELECT * FROM users WHERE age = 25;
-- rows: 5000 (예측), actual rows: 4823 (실제)
-- Planning Time: 0.5ms
-- Execution Time: 12.3ms

-- ⚠️ 주의! 실제로 삭제됨
EXPLAIN ANALYZE DELETE FROM users WHERE age < 18;
-- → 실제로 데이터 삭제! (롤백 필요)

-- ✅ 트랜잭션으로 보호
BEGIN;
EXPLAIN ANALYZE DELETE FROM users WHERE age < 18;
ROLLBACK;  -- 변경사항 취소

-- 데이터 대량 삽입 후 통계 업데이트
INSERT INTO users SELECT ... (100만 건 삽입)

-- ❌ 오래된 통계로 잘못된 실행 계획
EXPLAIN SELECT * FROM users WHERE age = 25;
-- rows: 100 (예전 통계 기준)

-- ✅ 통계 업데이트
ANALYZE TABLE users;  -- MySQL
VACUUM ANALYZE users; -- PostgreSQL

EXPLAIN SELECT * FROM users WHERE age = 25;
-- rows: 50000 (최신 통계 기준)
-- → 실행 계획 변경됨! (Index Scan → Full Scan)

-- Before: Full Table Scan
EXPLAIN SELECT * FROM users WHERE city = 'Seoul';
-- type: ALL

-- 인덱스 생성
CREATE INDEX idx_city ON users(city);

-- After: Index Scan
EXPLAIN SELECT * FROM users WHERE city = 'Seoul';
-- type: ref

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.age = 25;

-- Nested Loop Join 실행:
-- 1. users에서 age = 25인 Row 5,000건 가져옴
-- 2. 각 user마다 orders 테이블에서 user_id로 조회 (5,000번)
-- 3. 총 조인 연산: 5,000번

-- ✅ Nested Loop가 좋은 경우
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 123;  -- 외부 테이블 Row 1개
-- → Nested Loop Join: 매우 빠름! ✅
-- → 1번만 조인

-- ❌ Nested Loop가 나쁜 경우
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.city = o.city;  -- 인덱스 없음
-- → Nested Loop Join: 매우 느림! ❌
-- → 100만 × 100만 = 1조 번 비교

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Hash Join 실행:
-- 1. Build: users 테이블로 해시 테이블 생성 (id → name)
--    Hash Table: {123: "John", 456: "Jane", ...}
-- 2. Probe: orders 테이블을 스캔하며 해시 조회
--    order(user_id=123) → Hash[123] → "John"

-- ✅ Hash Join이 좋은 경우
SELECT u.name, o.total
FROM users u  -- 10만 건
JOIN orders o ON u.id = o.user_id  -- 100만 건
-- → Hash Join: 빠름! ✅
-- → 시간: O(100,000 + 1,000,000) = 110만

-- ❌ Hash Join이 나쁜 경우 (메모리 부족)
SELECT u.name, o.total
FROM users u  -- 1억 건 (해시 테이블이 메모리 초과!)
JOIN orders o ON u.id = o.user_id
-- → Hash Join 시도 → 디스크 사용 → 느림! ⚠️

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY u.id;

-- Merge Join 실행:
-- 1. users를 id로 정렬: [1, 2, 3, ...]
-- 2. orders를 user_id로 정렬: [1, 1, 2, 3, ...]
-- 3. 두 테이블을 순차적으로 병합

-- ✅ Merge Join이 좋은 경우 (이미 정렬된 인덱스)
CREATE INDEX idx_id ON users(id);
CREATE INDEX idx_user_id ON orders(user_id);

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY u.id;
-- → 정렬 불필요 (인덱스 사용)
-- → Merge Join: 매우 빠름! ✅

-- MySQL
SELECT /*+ NO_HASH_JOIN(users, orders) */ u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;
-- → Hash Join 비활성화

-- PostgreSQL
SET enable_hashjoin = off;
SET enable_mergejoin = off;
-- → Nested Loop만 사용

-- Oracle
SELECT /*+ USE_NL(u o) */ u.name, o.total
FROM users u, orders o
WHERE u.id = o.user_id;
-- → Nested Loop 강제

// ❌ N+1 문제 발생
List<User> users = userRepository.findAll();  // 1번 쿼리

for (User user : users) {
    List<Order> orders = user.getOrders();    // N번 쿼리 (각 user마다)
    System.out.println(orders.size());
}

// 실행된 쿼리:
// 1. SELECT * FROM users;  (100명)
// 2. SELECT * FROM orders WHERE user_id = 1;
// 3. SELECT * FROM orders WHERE user_id = 2;
// ...
// 101. SELECT * FROM orders WHERE user_id = 100;
// → 총 101번 쿼리! ⚠️

-- 1번째 쿼리
SELECT * FROM users;
-- 100 rows

-- 2번째 쿼리
SELECT * FROM orders WHERE user_id = 1;
-- 10 rows

-- 3번째 쿼리
SELECT * FROM orders WHERE user_id = 2;
-- 5 rows

-- ...

-- 101번째 쿼리
SELECT * FROM orders WHERE user_id = 100;
-- 8 rows

-- → 총 101번 쿼리 실행 (DB 부하!)

// ✅ Fetch Join 사용
@Query("SELECT u FROM User u JOIN FETCH u.orders")
List<User> findAllWithOrders();

List<User> users = userRepository.findAllWithOrders();  // 1번 쿼리

for (User user : users) {
    List<Order> orders = user.getOrders();  // 추가 쿼리 없음!
    System.out.println(orders.size());
}

// 실행된 쿼리:
// SELECT u.*, o.*
// FROM users u
// JOIN orders o ON u.id = o.user_id;
// → 총 1번 쿼리! ✅

-- ⚠️ 여러 컬렉션 JOIN 시 데이터 중복
SELECT u.*, o.*, r.*
FROM users u
JOIN orders o ON u.id = o.user_id      -- 10개
JOIN reviews r ON u.id = r.user_id;    -- 5개

-- 결과:
-- user1의 Row 수: 10 × 5 = 50개 (중복!) ⚠️

// ✅ Batch Size 설정
@Entity
public class User {
    @OneToMany(mappedBy = "user")
    @BatchSize(size = 100)  // 100개씩 배치 로딩
    private List<Order> orders;
}

List<User> users = userRepository.findAll();  // 1번 쿼리

for (User user : users) {
    List<Order> orders = user.getOrders();    // 100명마다 1번 쿼리
    System.out.println(orders.size());
}

// 실행된 쿼리:
// 1. SELECT * FROM users;  (100명)
// 2. SELECT * FROM orders WHERE user_id IN (1, 2, ..., 100);  (100명 분)
// → 총 2번 쿼리! ✅

-- 1번째 쿼리
SELECT * FROM users;

-- 2번째 쿼리 (IN 절로 배치 조회)
SELECT * FROM orders
WHERE user_id IN (1, 2, 3, ..., 100);
-- → 100명의 주문을 한 번에 가져옴

-- ✅ Subquery로 집계
SELECT
    u.id,
    u.name,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- → 1번 쿼리로 완료 ✅
-- → orders 테이블은 서브쿼리로 인덱스 사용

// Lazy Loading (기본값)
@Entity
public class User {
    @OneToMany(fetch = FetchType.LAZY)  // 기본값
    private List<Order> orders;
}

User user = userRepository.findById(1);  // orders는 로딩 안 됨
user.getOrders().size();  // 이 시점에 쿼리 실행

// Eager Loading
@Entity
public class User {
    @OneToMany(fetch = FetchType.EAGER)
    private List<Order> orders;
}

User user = userRepository.findById(1);  // orders도 함께 로딩
// → 사용하지 않아도 항상 로딩 (비효율) ⚠️

-- ❌ Correlated Subquery (느림)
SELECT u.name,
       (SELECT COUNT(*)
        FROM orders o
        WHERE o.user_id = u.id) AS order_count
FROM users u;

-- 실행:
-- users의 각 Row마다 서브쿼리 실행 (N번)
-- → users 100만 건 → 서브쿼리 100만 번 실행! ⚠️

-- ✅ JOIN 사용 (빠름)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 실행:
-- 1번의 JOIN + GROUP BY
-- → 훨씬 빠름! ✅

-- ⚠️ IN Subquery (성능 주의)
SELECT *
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- 실행 계획:
-- Subquery가 먼저 실행 → 결과를 IN 절로 비교
-- → orders 테이블 전체 스캔 가능 ⚠️

-- ✅ EXISTS 사용
SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.total > 1000
);

-- 장점:
-- 1. 첫 번째 매칭되는 Row만 찾으면 중단 (Short-circuit)
-- 2. 인덱스 활용 가능

-- ✅ JOIN 사용
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total > 1000;

-- 또는 (중복 제거 필요 없으면)
SELECT u.*
FROM users u
WHERE u.id IN (
    SELECT DISTINCT user_id
    FROM orders
    WHERE total > 1000
);

-- ❌ SELECT 절의 Scalar Subquery (느림)
SELECT
    u.id,
    u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count,
    (SELECT SUM(total) FROM orders WHERE user_id = u.id) AS total_amount,
    (SELECT MAX(created_at) FROM orders WHERE user_id = u.id) AS last_order_date
FROM users u;

-- 문제:
-- 각 컬럼마다 서브쿼리 실행
-- → users 100만 건 × 3개 서브쿼리 = 300만 번 실행! ⚠️

-- ✅ JOIN으로 한 번에 집계
SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    SUM(o.total) AS total_amount,
    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;

-- 성능:
-- 서브쿼리: 300만 번 실행 (300초)
-- JOIN: 1번 실행 (3초)
-- → 100배 빠름! ✅

-- ✅ WITH 절 사용
WITH high_value_users AS (
    SELECT user_id, SUM(total) AS total_amount
    FROM orders
    GROUP BY user_id
    HAVING SUM(total) > 10000
),
recent_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= '2025-01-01'
    GROUP BY user_id
)
SELECT
    u.name,
    h.total_amount,
    r.order_count
FROM users u
JOIN high_value_users h ON u.id = h.user_id
LEFT JOIN recent_orders r ON u.id = r.user_id;

-- 장점:
-- 1. 가독성 향상
-- 2. 재사용 가능
-- 3. 디버깅 쉬움

-- PostgreSQL 12+: CTE는 기본적으로 Materialized (물리적 저장)
WITH temp AS (
    SELECT * FROM large_table WHERE ...
)
SELECT * FROM temp WHERE ...;
-- → temp 결과가 메모리/디스크에 저장됨

-- ✅ Inline으로 최적화 (PostgreSQL)
WITH temp AS NOT MATERIALIZED (
    SELECT * FROM large_table WHERE ...
)
SELECT * FROM temp WHERE ...;
-- → 서브쿼리처럼 최적화됨

-- MySQL Slow Query Log
-- Time: 30.5s
-- Rows examined: 5,000,000
SELECT
    o.id,
    o.total,
    u.name,
    u.email,
    (SELECT COUNT(*) FROM order_items WHERE order_id = o.id) AS item_count,
    (SELECT SUM(quantity) FROM order_items WHERE order_id = o.id) AS total_quantity
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PENDING'
  AND o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

EXPLAIN ANALYZE SELECT ...;

-- ✅ 1. 인덱스 생성
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
-- → Full Scan 제거
-- → ORDER BY 인덱스 활용 (filesort 제거)

-- ✅ 2. Subquery를 JOIN으로 변환
SELECT
    o.id,
    o.total,
    u.name,
    u.email,
    COUNT(oi.id) AS item_count,
    SUM(oi.quantity) AS total_quantity
FROM orders o
JOIN users u ON o.user_id = u.id
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'PENDING'
  AND o.created_at >= '2025-01-01'
GROUP BY o.id, o.total, u.name, u.email
ORDER BY o.created_at DESC
LIMIT 100;

EXPLAIN ANALYZE SELECT ...;