쿼리 튜닝 실전

-- 최근 실행된 SQL 중 평균 경과 시간이 긴 것
SELECT sql_id, elapsed_time/1000000 AS elapsed_sec,
       executions, buffer_gets,
       ROUND((elapsed_time/NULLIF(executions,0))/1000000, 3) AS avg_elapsed_sec,
       ROUND(buffer_gets/NULLIF(executions,0)) AS gets_per_exec,
       SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql
WHERE executions > 0
ORDER BY avg_elapsed_sec DESC
FETCH FIRST 10 ROWS ONLY;

-- Buffer Gets가 비정상적으로 많은 SQL
SELECT sql_id, buffer_gets, executions,
       ROUND(buffer_gets/NULLIF(executions,0)) AS avg_gets
FROM v$sql
WHERE buffer_gets/NULLIF(executions,0) > 100000
ORDER BY buffer_gets DESC;

-- my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1            -- 1초 초과 쿼리 기록
log_queries_not_using_indexes = 1  -- 인덱스 미사용 쿼리

-- 동적으로 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- 0.5초 초과
SET SESSION long_query_time = 0.5; -- 현재 세션에 즉시 적용

-- Performance Schema로 느린 쿼리 확인
SELECT DIGEST_TEXT,
       COUNT_STAR AS exec_count,
       ROUND(SUM_TIMER_WAIT/1000000000000, 3) AS total_sec,
       ROUND(AVG_TIMER_WAIT/1000000000000, 3) AS avg_sec,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT,
       SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0) AS rows_ratio
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

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

-- 안티패턴: 숫자로 비교 → 암묵적 형변환 위험
SELECT * FROM customers WHERE phone_number = 01012345678;
-- → DBMS와 타입 우선순위에 따라 컬럼 쪽 변환이 발생할 수 있음
-- → 컬럼이 변환되면 기존 인덱스 활용이 어려워짐

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

-- Full Table Scan 유도
SELECT /*+ FULL(e) */ * FROM employees e WHERE dept_id = 10;

-- 특정 인덱스 사용 유도
SELECT /*+ INDEX(e IDX_EMP_DEPT) */ * FROM employees e WHERE dept_id = 10;

-- 인덱스 사용 금지
SELECT /*+ NO_INDEX(e IDX_EMP_DEPT) */ * FROM employees e WHERE dept_id = 10;

-- 조인 순서 지정
SELECT /*+ LEADING(d e) USE_NL(e) */
       e.name, d.dept_name
FROM departments d
JOIN employees e ON d.id = e.dept_id;

-- 병렬 처리
SELECT /*+ PARALLEL(o, 4) */ COUNT(*) FROM orders o;

-- Index-level Optimizer Hint (MySQL 8.0.20+)
SELECT /*+ NO_INDEX(e idx_dept) */ * FROM employees e WHERE dept_id = 10;

-- Join-order Optimizer Hint (MySQL 8.0+)
SELECT /*+ JOIN_ORDER(d, e) */ * FROM departments d JOIN employees e ...;

-- 인덱스 힌트 (전통 방식)
SELECT * FROM employees USE INDEX (idx_dept) WHERE dept_id = 10;
SELECT * FROM employees FORCE INDEX (idx_dept) WHERE dept_id = 10;
SELECT * FROM employees IGNORE INDEX (idx_dept) WHERE dept_id = 10;

인덱스가 필요한 경우:
  * WHERE 조건에 자주 사용되는 컬럼
  * JOIN 조건 컬럼 (FK)
  * ORDER BY, GROUP BY 컬럼
  * 선택도(Selectivity)가 높은 컬럼
  * 여러 조건을 함께 만족시키는 복합 인덱스 후보
  * 커버링 인덱스로 테이블 접근을 줄일 수 있는 경우

인덱스가 불필요한 경우:
  * 테이블이 작아 전체 스캔 비용이 낮은 경우
  * 단독으로는 선택도가 낮고 다른 조건과도 잘 결합되지 않는 컬럼
  * INSERT/UPDATE/DELETE가 매우 빈번해 유지 비용이 큰 경우
  * 거의 사용되지 않는 쿼리 패턴만을 위한 경우

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

-- 좋은 활용: 선행 컬럼부터 순서대로 사용
WHERE dept_id = 10                              -- ✓ dept_id 사용
WHERE dept_id = 10 AND hire_date > '2024-01-01' -- ✓ dept_id + hire_date
WHERE dept_id = 10 AND hire_date > '2024-01-01' AND salary > 5000 -- ✓ 전부

-- 나쁜 활용: 선행 컬럼 건너뜀
WHERE hire_date > '2024-01-01'                  -- ✗ dept_id 없음
WHERE salary > 5000                             -- ✗ dept_id, hire_date 없음

-- 복합 인덱스 컬럼 순서 결정 원칙:
-- 1. 등치(=) 조건 컬럼을 앞에
-- 2. 범위 조건 컬럼을 뒤에
-- 3. 정렬(ORDER BY) 컬럼 고려
-- 4. 범위 조건 뒤 컬럼은 탐색 범위 축소보다 후속 필터/커버링에 쓰일 수 있음

CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    user_id INT,
    order_date DATE,
    total DECIMAL(10,2),
    PRIMARY KEY (id, order_date)  -- 모든 UNIQUE KEY는 파티션 표현식 컬럼 포함 필요
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

-- 테이블 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'EMPLOYEES');

-- 스키마 전체 통계 수집
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA');

-- 통계 확인
SELECT table_name, num_rows, last_analyzed
FROM dba_tables
WHERE owner = 'SCHEMA'
ORDER BY last_analyzed;

-- 히스토그램 생성 (데이터 분포가 편향된 컬럼)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
    'SCHEMA', 'ORDERS',
    method_opt => 'FOR COLUMNS SIZE 254 status'
);

ANALYZE TABLE employees;

-- 히스토그램 생성 (MySQL 8.0+)
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;

-- 통계 확인
SELECT * FROM information_schema.STATISTICS
WHERE TABLE_NAME = 'employees';

-- 히스토그램 확인
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, HISTOGRAM
FROM information_schema.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'orders';

-- 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');

-- 선택 기준:
-- 최신 DBMS는 IN/EXISTS를 semi join 등으로 변환하는 경우가 많음
-- NULL 처리, 중복, 실행 계획을 확인한 뒤 선택

□ 실행 계획을 확인했는가?
□ Full Table Scan이 의도적인가?
□ 적절한 인덱스가 존재하는가?
□ 복합 인덱스의 컬럼 순서가 쿼리에 맞는가?
□ WHERE 절에서 컬럼에 함수를 적용하지 않았는가?
□ 암묵적 형변환이 발생하지 않는가?
□ N+1 쿼리가 없는가?
□ 불필요한 SELECT *를 사용하지 않았는가?
□ 통계 정보가 최신인가?
□ 조인 순서와 방식이 적절한가?
□ 페이징 처리 시 OFFSET이 너무 크지 않은가?
□ 서브쿼리를 JOIN으로 변환할 수 있는가?
□ 커버링 인덱스를 활용할 수 있는가?
□ 불필요한 정렬(ORDER BY)이 있지 않은가?