쿼리 튜닝 실전

-- 튜닝 태스크 생성
DECLARE
  task_name VARCHAR2(100);
BEGIN
  task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
    sql_id => 'abc123def456',
    scope  => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE
  );
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name);
END;

-- 권고안 확인
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('task_name')
FROM DUAL;
-- → 인덱스 생성 제안, SQL 프로파일, 구조 변경 등

Buffer Gets(논리적 읽기) / 반환 행
  → 1행 반환에 10,000 Gets → 비효율적
  → 1행 반환에 5 Gets → 효율적

Rows Examined / Rows Sent
  → MySQL에서 검사한 행 vs 반환한 행
  → 1000행 검사, 1행 반환 → 인덱스 재검토 필요

비효율 기준
  * Gets per Row > 1000 → 인덱스 필요
  * Full Table Scan + 대규모 테이블 → 인덱스 필요
  * 동일 SQL 반복 실행 → 캐싱 또는 배치로 전환

-- 안티패턴: N+1
-- 쿼리 1: SELECT id FROM orders WHERE user_id = 1;  → 100건
-- 쿼리 2~101:
--   SELECT * FROM order_items WHERE order_id = 1;
--   SELECT * FROM order_items WHERE order_id = 2;
--   ...
--   SELECT * FROM order_items WHERE order_id = 100;
-- → 총 101번 쿼리 실행!

-- 개선 1: JOIN으로 한 번에
SELECT o.id, o.order_date, oi.product_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 1;

-- 개선 2: IN 절 (ORM에서 자주 사용)
SELECT * FROM order_items
WHERE order_id IN (1, 2, 3, ..., 100);
-- → 2번의 쿼리로 해결

-- 안티패턴: 컬럼에 함수 적용 → 인덱스 사용 불가
SELECT * FROM employees
WHERE UPPER(name) = 'KIM';           -- name 인덱스 사용 불가
WHERE TO_CHAR(hire_date, 'YYYY') = '2024'; -- hire_date 인덱스 사용 불가
WHERE salary + 1000 > 50000;         -- salary 인덱스 사용 불가

-- 개선 1: 조건을 변형하여 컬럼에 함수 제거
WHERE hire_date >= DATE '2024-01-01'
  AND hire_date < DATE '2025-01-01';
WHERE salary > 49000;

-- 개선 2: 함수 기반 인덱스 (Oracle)
CREATE INDEX idx_emp_upper_name ON employees(UPPER(name));
SELECT * FROM employees WHERE UPPER(name) = 'KIM';  -- 인덱스 사용 가능

-- phone_number는 VARCHAR 타입, 인덱스 있음

-- 안티패턴: 숫자로 비교 → 암묵적 형변환 → 인덱스 무효
SELECT * FROM customers WHERE phone_number = 01012345678;
-- → DB가 내부적으로 TO_NUMBER(phone_number) 수행
-- → 인덱스 사용 불가!

-- 개선: 타입을 일치시킴
SELECT * FROM customers WHERE phone_number = '01012345678';
-- → 인덱스 정상 사용

1. 힌트는 최후의 수단
   → 먼저: 인덱스 생성, 통계 갱신, 쿼리 구조 변경
   → 그래도 안 될 때: 힌트 사용

2. 힌트를 사용하면 유지보수 비용 증가
   → 데이터 분포 변경 시 힌트가 오히려 역효과
   → 주석으로 힌트 사용 이유 문서화

3. 힌트 문법 오류 시 무시됨 (에러 아님!)
   → /*+ INDX(e IDX_EMP) */ → 오타! 힌트 무시됨
   → 실행 계획으로 힌트 적용 여부 확인 필수

-- 인덱스: (dept_id, name, salary)

-- 커버링 인덱스: 인덱스만으로 결과 반환 (테이블 접근 불필요)
SELECT name, salary FROM employees WHERE dept_id = 10;
-- → 인덱스에 name, salary가 모두 포함
-- → 실행 계획: INDEX RANGE SCAN (테이블 접근 없음)
-- → Oracle: "TABLE ACCESS" 없음, MySQL Extra: "Using index"

CREATE TABLE orders (
    id         NUMBER,
    user_id    NUMBER,
    order_date DATE,
    total      NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p2025 VALUES LESS THAN (DATE '2026-01-01'),
    PARTITION p_max VALUES LESS THAN (MAXVALUE)
);

-- Partition Pruning: 해당 파티션만 스캔
SELECT * FROM orders
WHERE order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31';
-- → p2024 파티션만 읽음

-- 파티션 추가
ALTER TABLE orders ADD PARTITION p2026
    VALUES LESS THAN (DATE '2027-01-01');

-- 오래된 파티션 삭제 (아카이빙)
ALTER TABLE orders DROP PARTITION p2023;
-- → DELETE보다 훨씬 빠름 (데이터 파일 자체를 삭제)

-- OFFSET이 클수록 느려짐
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 100000;
-- → 100,010행을 읽고 100,000행을 버림! (비효율)

-- OFFSET 0:    10행 읽기     → 빠름
-- OFFSET 1000: 1,010행 읽기  → 보통
-- OFFSET 100000: 100,010행 읽기 → 매우 느림

-- 방법: 마지막으로 조회한 행의 키를 기준으로 다음 페이지 조회
-- 첫 페이지
SELECT * FROM orders ORDER BY id LIMIT 10;
-- → 마지막 행의 id = 1050

-- 다음 페이지 (OFFSET 대신 WHERE 조건)
SELECT * FROM orders WHERE id > 1050 ORDER BY id LIMIT 10;
-- → 인덱스 사용, 항상 10행만 읽음
-- → 페이지가 깊어져도 성능 동일!

-- 복합 정렬의 커서 페이징
SELECT * FROM orders
WHERE (created_at, id) > ('2024-01-15', 1050)
ORDER BY created_at, id
LIMIT 10;

-- 큰 테이블에서 OFFSET 페이징이 필요할 때
-- 안티패턴: 모든 컬럼을 OFFSET으로 가져옴
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 50000;

-- 개선: PK만 먼저 가져오고, JOIN으로 상세 데이터 조회
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 50000
) sub ON o.id = sub.id;
-- → 서브쿼리에서 커버링 인덱스 사용 가능

-- 안티패턴: 상관 서브쿼리 (행마다 서브쿼리 실행)
SELECT e.name, e.salary,
       (SELECT d.dept_name FROM departments d WHERE d.id = e.dept_id) AS dept_name
FROM employees e;
-- → employees 행 수만큼 서브쿼리 반복 실행

-- 개선: JOIN
SELECT e.name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;
-- → 단일 조인으로 해결

-- IN (서브쿼리 결과를 모두 가져와서 비교)
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE status = 'ACTIVE');

-- EXISTS (조건 만족하는 첫 행 발견 시 즉시 종료)
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.status = 'ACTIVE');

-- 선택 기준:
-- 서브쿼리 결과가 작으면 → IN이 유리
-- 외부 쿼리 결과가 작으면 → EXISTS가 유리
-- MySQL 최신 버전은 옵티마이저가 자동 변환하는 경우 많음