MySQL 쿼리 최적화 실전 가이드: EXPLAIN 분석부터 인덱스 설계·슬로우 쿼리 튜닝까지

EXPLAIN SELECT u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
  AND o.created_at >= '2026-01-01';

-- const: 기본 키로 단일 행 조회
EXPLAIN SELECT * FROM users WHERE id = 1;
-- type: const, rows: 1

-- ref: 비유니크 인덱스 조회
EXPLAIN SELECT * FROM orders WHERE user_id = 100;
-- type: ref (user_id에 인덱스가 있는 경우)

-- ALL: 풀 스캔 - 반드시 개선 필요
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- type: ALL (함수 적용으로 인덱스 사용 불가)

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2026-01-01'
GROUP BY u.id
HAVING order_count > 5
ORDER BY order_count DESC
LIMIT 10;

-- 통계 정보 수동 갱신
ANALYZE TABLE users;
ANALYZE TABLE orders;

-- B-Tree 인덱스 생성
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

-- 이 인덱스가 효과적인 쿼리 예시
SELECT * FROM orders WHERE user_id = 100 AND created_at >= '2026-01-01';
SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC;
SELECT * FROM orders WHERE user_id IN (100, 200, 300);

-- MEMORY 엔진 테이블에서 Hash 인덱스 사용
CREATE TABLE session_cache (
    session_id VARCHAR(64) NOT NULL,
    user_id INT NOT NULL,
    data JSON,
    INDEX USING HASH (session_id)
) ENGINE = MEMORY;

-- InnoDB Adaptive Hash Index 상태 확인
SHOW ENGINE INNODB STATUS\G
-- Adaptive Hash Index 섹션에서 hit rate 확인

-- Full-text 인덱스 생성
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_content (title, body);

-- 자연어 모드 검색
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('MySQL 성능 최적화' IN NATURAL LANGUAGE MODE);

-- 불리언 모드 검색 (AND, OR, NOT 등)
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL +성능 -PostgreSQL' IN BOOLEAN MODE);

-- Spatial 인덱스 생성
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position POINT NOT NULL SRID 4326,
    SPATIAL INDEX (position)
);

-- 반경 내 검색
SELECT name, ST_Distance_Sphere(position, ST_SRID(POINT(127.0276, 37.4979), 4326)) AS distance
FROM locations
WHERE ST_Distance_Sphere(position, ST_SRID(POINT(127.0276, 37.4979), 4326)) < 1000;

-- 복합 인덱스: (a, b, c)
CREATE INDEX idx_abc ON orders (status, user_id, created_at);

-- 인덱스 활용 가능한 쿼리
SELECT * FROM orders WHERE status = 'completed';                          -- (a) 사용
SELECT * FROM orders WHERE status = 'completed' AND user_id = 100;        -- (a, b) 사용
SELECT * FROM orders WHERE status = 'completed' AND user_id = 100
  AND created_at >= '2026-01-01';                                         -- (a, b, c) 모두 사용

-- 인덱스 활용 불가능한 쿼리
SELECT * FROM orders WHERE user_id = 100;                                 -- (b)만 사용 - 인덱스 미활용
SELECT * FROM orders WHERE user_id = 100 AND created_at >= '2026-01-01';  -- (b, c) - 인덱스 미활용
SELECT * FROM orders WHERE created_at >= '2026-01-01';                    -- (c)만 사용 - 인덱스 미활용

-- 커버링 인덱스 설계
CREATE INDEX idx_covering ON orders (user_id, status, total_amount);

-- 이 쿼리는 인덱스만으로 결과 반환 가능 (Using index)
EXPLAIN SELECT user_id, status, total_amount
FROM orders
WHERE user_id = 100 AND status = 'completed';

-- 아래 쿼리는 커버링 인덱스 미적용 (SELECT *로 인해 테이블 데이터 접근 필요)
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND status = 'completed';

-- 나쁜 설계: 낮은 카디널리티 컬럼만으로 인덱스
CREATE INDEX idx_bad ON orders (status);  -- status는 5~10가지 값뿐

-- 좋은 설계: 쿼리 패턴에 맞춘 복합 인덱스
CREATE INDEX idx_good ON orders (user_id, status, created_at);

-- 정렬까지 고려한 설계
-- WHERE user_id = ? ORDER BY created_at DESC 패턴이 빈번할 때
CREATE INDEX idx_sort ON orders (user_id, created_at DESC);

-- 나쁨: 상관 서브쿼리 (각 행마다 서브쿼리 실행)
SELECT u.name, u.email,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u
WHERE u.status = 'active';

-- 좋음: LEFT JOIN + GROUP BY
SELECT u.name, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.name, u.email;

-- 또는: 파생 테이블(Derived Table) 사용
SELECT u.name, u.email, COALESCE(oc.cnt, 0) AS order_count
FROM users u
LEFT JOIN (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders
    GROUP BY user_id
) oc ON u.id = oc.user_id
WHERE u.status = 'active';

-- 나쁨: OR 조건으로 인한 인덱스 활용 어려움
SELECT * FROM products
WHERE category_id = 10 OR brand_id = 20;

-- 좋음: UNION ALL로 분리 (각 조건에 대해 인덱스 활용 가능)
SELECT * FROM products WHERE category_id = 10
UNION ALL
SELECT * FROM products WHERE brand_id = 20 AND category_id != 10;

-- 나쁨: 인덱스 컬럼에 함수 적용
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 좋음: 범위 조건으로 재작성
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- MySQL 8.0+: 함수 기반 인덱스 (Expression Index)
CREATE INDEX idx_email_lower ON users ((LOWER(email)));
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

-- 나쁨: phone_number가 VARCHAR인데 숫자로 비교
SELECT * FROM users WHERE phone_number = 01012345678;
-- MySQL이 phone_number를 숫자로 변환 -> 인덱스 미사용

-- 좋음: 타입 일치
SELECT * FROM users WHERE phone_number = '01012345678';

-- USE INDEX: 특정 인덱스 사용 권장 (옵티마이저가 무시할 수 있음)
SELECT * FROM orders USE INDEX (idx_user_created)
WHERE user_id = 100 AND created_at >= '2026-01-01';

-- FORCE INDEX: 특정 인덱스 강제 사용 (풀 스캔 대신 반드시 인덱스 사용)
SELECT * FROM orders FORCE INDEX (idx_user_created)
WHERE user_id = 100 AND created_at >= '2026-01-01';

-- IGNORE INDEX: 특정 인덱스 무시
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE status = 'completed' AND user_id = 100;

-- JOIN 순서 고정
SELECT /*+ JOIN_ORDER(u, o) */
  u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- 특정 테이블 인덱스 지정
SELECT /*+ INDEX(o idx_user_created) */
  o.*
FROM orders o
WHERE o.user_id = 100;

-- Hash Join 강제
SELECT /*+ HASH_JOIN(u, o) */
  u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- 병렬 쿼리 실행 (MySQL 8.0.14+)
SELECT /*+ SET_VAR(innodb_parallel_read_threads=4) */
  COUNT(*) FROM large_table;

-- 현재 설정 확인
SHOW VARIABLES LIKE 'optimizer_switch';

-- 세션 레벨에서 특정 최적화 비활성화
SET SESSION optimizer_switch = 'index_merge_intersection=off';
SET SESSION optimizer_switch = 'derived_merge=off';
SET SESSION optimizer_switch = 'batched_key_access=on';

-- MySQL 8.0.31+: 히스토그램 기반 최적화
ANALYZE TABLE orders UPDATE HISTOGRAM ON user_id, status WITH 100 BUCKETS;

-- 슬로우 쿼리 로그 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1초 이상 걸리는 쿼리 기록
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 인덱스 미사용 쿼리도 기록
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

-- my.cnf에 영구 설정
-- [mysqld]
-- slow_query_log = 1
-- long_query_time = 1
-- log_queries_not_using_indexes = 1
-- slow_query_log_file = /var/log/mysql/slow-query.log
-- min_examined_row_limit = 1000

# 가장 느린 쿼리 Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# 가장 빈번한 슬로우 쿼리 Top 10
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

# 특정 패턴 필터링
mysqldumpslow -s t -t 10 -g "orders" /var/log/mysql/slow-query.log

# 슬로우 로그 분석
pt-query-digest /var/log/mysql/slow-query.log

# 특정 시간 범위 분석
pt-query-digest --since="2026-03-10 00:00:00" --until="2026-03-11 00:00:00" \
  /var/log/mysql/slow-query.log

# 결과를 파일로 저장
pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow-query-report.txt

-- 현재 버퍼 풀 상태 확인
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 버퍼 풀 적중률 계산
SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_rate;
-- 99% 이상이 바람직

# my.cnf 설정 예시
[mysqld]
innodb_buffer_pool_size = 32G
innodb_buffer_pool_instances = 8    # 버퍼 풀 크기 / 인스턴스 = 4GB씩
innodb_buffer_pool_chunk_size = 1G  # 온라인 리사이징 단위
innodb_log_file_size = 4G
innodb_log_buffer_size = 64M
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

-- 셧다운 시 버퍼 풀 덤프, 시작 시 로드
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
SET GLOBAL innodb_buffer_pool_load_at_startup = ON;

-- 수동 덤프/로드
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

-- 로드 진행 상태 확인
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

-- MySQL 8.4: EXPLAIN 개선 예시
EXPLAIN FORMAT=TREE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Invisible Index 활용 (인덱스 삭제 전 테스트)
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;
-- 성능 테스트 후 문제가 없으면 삭제
-- DROP INDEX idx_status ON orders;
-- 문제가 발생하면 다시 활성화
ALTER TABLE orders ALTER INDEX idx_status VISIBLE;

-- 문제 상황: VARCHAR 컬럼에 숫자 비교
-- account_no 컬럼이 VARCHAR(20)인데 애플리케이션에서 숫자로 전달
EXPLAIN SELECT * FROM accounts WHERE account_no = 123456;
-- type: ALL (전체 테이블 스캔!)

-- 원인: MySQL이 account_no 컬럼을 숫자로 변환하므로 인덱스 사용 불가
-- 해결: 타입 일치시키기
EXPLAIN SELECT * FROM accounts WHERE account_no = '123456';
-- type: ref (인덱스 사용)

-- 문제 상황: 인덱스가 있지만 옵티마이저가 풀 스캔을 선택
-- status 컬럼에 인덱스가 있으나 'active'가 전체의 90%를 차지
EXPLAIN SELECT * FROM users WHERE status = 'active';
-- type: ALL (옵티마이저가 풀 스캔이 더 효율적이라고 판단)

-- 원인: 카디널리티가 낮아 인덱스 효율이 떨어짐
-- 해결 1: 다른 조건과 복합 인덱스 구성
CREATE INDEX idx_status_created ON users (status, created_at);

-- 해결 2: 특정 값에 대해서만 인덱스가 유용한 경우
-- MySQL은 Partial Index를 직접 지원하지 않으므로, 쿼리 패턴 변경 고려

-- 문제 상황: 인덱스 (created_at, user_id)로 생성
-- 빈번한 쿼리: WHERE user_id = ? AND created_at >= ?
EXPLAIN SELECT * FROM orders
WHERE user_id = 100 AND created_at >= '2026-01-01';
-- 인덱스를 비효율적으로 사용 (range scan on created_at first)

-- 해결: 등호 조건 컬럼을 앞에 배치
DROP INDEX idx_created_user ON orders;
CREATE INDEX idx_user_created ON orders (user_id, created_at);
-- type: range (효율적인 범위 스캔)

-- 문제 상황: 500만 건 DELETE 후 쿼리 성능 저하
-- 원인: 인덱스 페이지에 빈 공간이 많아짐 (단편화)

-- 진단
SELECT
  TABLE_NAME,
  INDEX_LENGTH,
  DATA_LENGTH,
  DATA_FREE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'orders';

-- 해결: 인덱스 재구축
ALTER TABLE orders ENGINE=InnoDB;  -- 테이블 + 인덱스 재구축
-- 또는
OPTIMIZE TABLE orders;

-- 사용되지 않는 인덱스 찾기
SELECT
  s.TABLE_SCHEMA,
  s.TABLE_NAME,
  s.INDEX_NAME,
  s.COLUMN_NAME
FROM INFORMATION_SCHEMA.STATISTICS s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage p
  ON s.TABLE_SCHEMA = p.OBJECT_SCHEMA
  AND s.TABLE_NAME = p.OBJECT_NAME
  AND s.INDEX_NAME = p.INDEX_NAME
WHERE p.COUNT_STAR = 0
  AND s.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
  AND s.INDEX_NAME != 'PRIMARY'
ORDER BY s.TABLE_SCHEMA, s.TABLE_NAME;

-- 중복 인덱스 찾기 (sys 스키마 활용)
SELECT * FROM sys.schema_redundant_indexes;

-- 인덱스 추가 시 잠금 모드 확인
ALTER TABLE orders ADD INDEX idx_new (col1, col2), ALGORITHM=INPLACE, LOCK=NONE;

-- pt-online-schema-change 사용 (대용량 테이블에 권장)
-- 원본 테이블을 트리거로 동기화하며 스키마 변경

pt-online-schema-change \
  --alter "ADD INDEX idx_new (col1, col2)" \
  --execute \
  D=mydb,t=orders

-- 히스토그램 생성
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON total_amount WITH 254 BUCKETS;

-- 히스토그램 확인
SELECT
  SCHEMA_NAME,
  TABLE_NAME,
  COLUMN_NAME,
  JSON_EXTRACT(HISTOGRAM, '$.histogram-type') AS histogram_type,
  JSON_EXTRACT(HISTOGRAM, '$.number-of-buckets-specified') AS buckets
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;

-- 히스토그램 삭제
ANALYZE TABLE orders DROP HISTOGRAM ON status;