서브쿼리 최적화 — 서브쿼리가 느린 이유와 해결법

-- 1. SELECT 절: 스칼라 서브쿼리 (한 행, 한 컬럼 반환)
SELECT name,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;

-- 2. FROM 절: 파생 테이블 (인라인 뷰)
SELECT sub.dept, sub.cnt
FROM (SELECT department AS dept, COUNT(*) AS cnt
      FROM employees GROUP BY department) sub;

-- 3. WHERE 절: 조건 서브쿼리
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 서브쿼리가 외부 쿼리의 값을 참조하지 않음
SELECT * FROM users
WHERE department_id IN (
    SELECT id FROM departments WHERE active = 1  -- 독립 실행
);

-- u.id를 참조하므로 users의 각 행마다 서브쿼리 실행
SELECT u.name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;
-- users가 10,000행이면 서브쿼리가 10,000번 실행

-- 느린 쿼리: 매 행마다 서브쿼리 실행
SELECT e.name, e.salary,
       (SELECT AVG(salary) FROM employees WHERE department = e.department) AS dept_avg
FROM employees e;

-- 빠른 대안: JOIN 사용
SELECT e.name, e.salary, d.dept_avg
FROM employees e
JOIN (SELECT department, AVG(salary) AS dept_avg
      FROM employees GROUP BY department) d
ON e.department = d.department;
-- 서브쿼리는 1번만 실행되고, JOIN으로 매칭

-- 파생 테이블이 실체화(Materialization)되면 임시 테이블 생성
SELECT * FROM (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
) sub
WHERE sub.total > 10000;

-- 옵티마이저가 최적화하기 어려운 형태
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- MySQL 5.5 이전에는 이 쿼리가 다음처럼 실행되었습니다:
-- for each row in users:
--     SELECT user_id FROM orders WHERE amount > 1000 AND user_id = users.id
--     상관 서브쿼리로 변환되어 매 행마다 실행!

-- 원본 쿼리
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- 옵티마이저가 내부적으로 Semi-Join으로 변환
-- (결과는 동일하지만 실행 방식이 다름)
SELECT DISTINCT users.*
FROM users
SEMI JOIN orders ON users.id = orders.user_id
WHERE orders.amount > 1000;

-- EXPLAIN에서 Semi-Join 전략 확인
EXPLAIN SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);
-- Extra: Using where; LooseScan 등

-- 원본: 파생 테이블 사용
SELECT * FROM (
    SELECT id, name, age FROM users WHERE age > 20
) sub
WHERE sub.name LIKE '김%';

-- 머지 후: 하나의 쿼리로 합쳐짐
SELECT id, name, age FROM users
WHERE age > 20 AND name LIKE '김%';

-- 머지 불가: GROUP BY가 있어서 실체화됨
SELECT * FROM (
    SELECT department, COUNT(*) AS cnt
    FROM employees
    GROUP BY department
) sub
WHERE sub.cnt > 5;

SELECT * FROM users
WHERE department_id IN (
    SELECT id FROM departments WHERE region = 'Asia'
);

-- 옵티마이저: departments의 결과를 임시 테이블에 저장하고 인덱스 생성
-- users의 각 행에 대해 임시 테이블을 인덱스로 조회

-- IN 사용
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

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

-- MySQL 8.0: 두 쿼리의 EXPLAIN 결과가 대부분 동일합니다

-- NOT IN의 NULL 함정
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
-- orders.user_id에 NULL이 있으면 결과가 빈 집합이 됩니다!

-- NOT EXISTS는 NULL에 안전
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- Before: 스칼라 서브쿼리 (매 행마다 실행)
SELECT u.name,
       (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS cnt
FROM users u;

-- After: LEFT JOIN + GROUP BY
SELECT u.name, COUNT(o.id) AS cnt
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Before: IN 서브쿼리
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

-- After: JOIN
SELECT p.*
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.active = 1;

-- Before: NOT IN
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

-- After: LEFT JOIN + IS NULL (NULL 안전)
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.user_id IS NULL;

-- 1. 집계 결과와 비교
SELECT * FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- 2. EXISTS로 존재 여부만 확인
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 3. 가독성이 중요한 복잡한 조건
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products WHERE category_id = 1)
  AND stock > (SELECT MIN(stock) FROM products WHERE featured = 1);

-- 서브쿼리 최적화 여부 확인
EXPLAIN FORMAT=TREE
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

-- optimizer_switch로 최적화 전략 확인
SHOW VARIABLES LIKE 'optimizer_switch';
-- semijoin=on, materialization=on, subquery_materialization_cost_based=on ...

-- 특정 최적화 비활성화 (디버깅용)
SET optimizer_switch='semijoin=off';

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders);
-- orders.user_id에 NULL이 하나라도 있으면 결과가 항상 빈 집합!