MySQL 성능 튜닝 실전 - 슬로우 쿼리부터 인덱스 최적화까지

들어가며

"이 API 응답이 왜 3초나 걸리죠?" 운영 중인 서비스에서 이런 질문을 받으면 심장이 철렁합니다. 로직은 단순한데 DB 쿼리만 2.8초를 잡아먹고 있고, 슬로우 쿼리 로그에는 이미 수백 건이 쌓여 있습니다. 인덱스를 추가하면 될 것 같은데, 어디에 어떤 인덱스를 걸어야 할지 막막합니다.

3~7년차 백엔드 개발자라면 이런 상황을 한두 번쯤 겪어보셨을 겁니다. JPA가 생성하는 쿼리를 믿고 있었는데 데이터가 100만 건을 넘기면서 Full Table Scan이 발생하고, 복합 인덱스를 잘못 설계해서 오히려 INSERT 성능이 떨어지는 경우도 빈번합니다. MySQL 성능 튜닝은 단순히 인덱스를 추가하는 것이 아니라, 쿼리가 실제로 어떻게 실행되는지 이해하고 체계적으로 접근하는 과정입니다.

이 글에서는 EXPLAIN ANALYZE로 실행 계획을 읽는 법부터, 인덱스 설계 원칙, 슬로우 쿼리 분석, InnoDB 버퍼 풀 튜닝, JOIN 최적화까지 실무에서 바로 적용할 수 있는 MySQL 성능 튜닝 전략을 다룹니다. 각 단계마다 Before/After 실행 계획을 비교해서 실제 효과를 확인할 수 있도록 구성했습니다.

1. EXPLAIN과 EXPLAIN ANALYZE 읽는 법

MySQL 성능 튜닝의 첫 단계는 쿼리가 실제로 어떻게 실행되는지 파악하는 것입니다. EXPLAIN은 옵티마이저의 실행 계획을 보여주고, EXPLAIN ANALYZE(MySQL 8.0.18+)는 실제 실행 결과까지 포함합니다.

EXPLAIN 기본 사용법

EXPLAIN SELECT o.order_id, o.total_amount, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'COMPLETED'
  AND o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 100;

-- 결과 예시
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | o     | ALL  | NULL          | NULL | NULL    | NULL | 982341 | Using where; Using filesort |
|  1 | SIMPLE      | c     | ALL  | PRIMARY       | NULL | NULL    | NULL |  50234 | Using where                 |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+

EXPLAIN 핵심 컬럼 해석

컬럼	의미	주의할 값
type	접근 방식	ALL(풀스캔) → 성능 문제 가능. ref, range, const가 이상적
key	실제 사용된 인덱스	NULL이면 인덱스 미사용
rows	예상 조회 행 수	실제 결과 대비 지나치게 크면 비효율
Extra	추가 정보	Using filesort, Using temporary → 개선 필요
filtered	조건 필터링 비율(%)	낮으면 인덱스가 비효율적

type 컬럼 성능 순서 (좋음 → 나쁨)

system > const > eq_ref > ref > range > index > ALL

-- const: PK 또는 UNIQUE 인덱스로 1건 조회
EXPLAIN SELECT * FROM customers WHERE id = 1;
-- type: const

-- eq_ref: JOIN에서 PK/UNIQUE로 1건 매칭
EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;
-- customers 테이블 접근: eq_ref

-- ref: 일반 인덱스로 여러 건 조회
EXPLAIN SELECT * FROM orders WHERE customer_id = 100;
-- type: ref (customer_id에 인덱스가 있을 때)

-- range: 인덱스 범위 스캔
EXPLAIN SELECT * FROM orders WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';
-- type: range

-- ALL: 풀 테이블 스캔 → 거의 항상 개선 필요
EXPLAIN SELECT * FROM orders WHERE status = 'COMPLETED';
-- type: ALL (status에 인덱스가 없을 때)

EXPLAIN ANALYZE (MySQL 8.0.18+)

EXPLAIN ANALYZE는 실제로 쿼리를 실행하고 각 단계의 실제 소요 시간, 처리 행 수를 보여줍니다. 운영 DB에서 사용 시 주의가 필요합니다.

EXPLAIN ANALYZE
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'COMPLETED'
  AND o.created_at >= '2025-01-01';

-- 결과 예시 (트리 형태)
-> Nested loop inner join  (cost=442891 rows=491170)
    (actual time=0.832..2847.123 rows=156234 loops=1)
    -> Filter: ((o.status = 'COMPLETED') and (o.created_at >= '2025-01-01'))
        (cost=98921 rows=491170)
        (actual time=0.621..2341.456 rows=156234 loops=1)
        -> Table scan on o  (cost=98921 rows=982341)
            (actual time=0.589..1823.234 rows=982341 loops=1)
    -> Single-row index lookup on c using PRIMARY (id=o.customer_id)
        (cost=0.60 rows=1)
        (actual time=0.003..0.003 rows=1 loops=156234)

위 결과에서 핵심 포인트를 읽어봅니다.

• Table scan on o: orders 테이블 풀스캔 발생 (982,341행 전체 읽음)
• actual time=0.589..1823.234: 첫 행까지 0.589ms, 전체 완료까지 1823ms
• rows=982341 vs rows=156234: 98만 행을 읽어서 15.6만 행만 필터 통과
• customers 테이블은 PK로 조회하므로 효율적 (Single-row index lookup)

2. 인덱스 설계 원칙

인덱스는 MySQL 성능 튜닝의 핵심입니다. 하지만 무작정 인덱스를 추가하면 INSERT/UPDATE 성능이 저하되고 디스크 공간도 낭비됩니다. 원칙에 따라 설계하는 것이 중요합니다.

B-Tree 인덱스의 동작 원리

-- B-Tree 인덱스 구조 (MySQL InnoDB의 기본 인덱스)
-- 정렬된 트리 구조로 O(log N) 탐색

--            [50]
--           /    \
--       [20,35]   [70,85]
--      / |  \    / |  \
-- [10,15][25,30][40,45] [55,65][75,80][90,95]
--   ↓     ↓     ↓       ↓     ↓     ↓
-- (실제 데이터 또는 PK 포인터)

-- B-Tree 인덱스가 효율적인 연산
-- 1. 등호(=) 조건: WHERE id = 50
-- 2. 범위 조건: WHERE id BETWEEN 20 AND 70
-- 3. 정렬: ORDER BY id
-- 4. 최솟값/최댓값: MIN(id), MAX(id)

-- B-Tree 인덱스가 사용되지 않는 경우
-- 1. LIKE '%keyword' (선행 와일드카드)
-- 2. 함수 적용: WHERE YEAR(created_at) = 2025
-- 3. 타입 불일치: WHERE varchar_col = 12345 (암묵적 형변환)
-- 4. OR 조건 (일부 경우): WHERE col1 = 'A' OR col2 = 'B'

복합 인덱스(Composite Index) 설계

복합 인덱스에서 가장 중요한 원칙은 왼쪽 접두사 규칙(Leftmost Prefix Rule)입니다. 인덱스 (A, B, C)는 A, (A,B), (A,B,C) 순서로만 활용됩니다.

-- 복합 인덱스 생성
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- 이 인덱스가 사용되는 쿼리
SELECT * FROM orders WHERE status = 'COMPLETED';                          -- O (status만 사용)
SELECT * FROM orders WHERE status = 'COMPLETED' AND created_at >= '2025-01-01'; -- O (둘 다 사용)
SELECT * FROM orders WHERE status = 'COMPLETED' ORDER BY created_at;      -- O (status 필터 + created_at 정렬)

-- 이 인덱스가 사용되지 않는 쿼리
SELECT * FROM orders WHERE created_at >= '2025-01-01';  -- X (왼쪽 접두사 없음)

-- 복합 인덱스 컬럼 순서 결정 기준
-- 1. 등호(=) 조건 컬럼을 앞에
-- 2. 범위 조건 컬럼을 뒤에
-- 3. 정렬(ORDER BY) 컬럼을 범위 다음에
-- 4. 카디널리티(고유값 수)가 높은 컬럼을 앞에

-- 예시: 주문 조회 쿼리
SELECT * FROM orders
WHERE status = 'COMPLETED'
  AND customer_id = 100
  AND created_at >= '2025-01-01'
ORDER BY created_at DESC;

-- 최적 인덱스: (status, customer_id, created_at)
-- status: 등호(=), 카디널리티 낮음 → 앞
-- customer_id: 등호(=), 카디널리티 높음 → 중간
-- created_at: 범위 + 정렬 → 뒤
CREATE INDEX idx_orders_optimal ON orders (status, customer_id, created_at);

커버링 인덱스(Covering Index)

커버링 인덱스는 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어, 테이블 데이터를 읽지 않고 인덱스만으로 결과를 반환하는 기법입니다. EXPLAIN의 Extra에 Using index가 표시됩니다.

-- 테이블 구조
CREATE TABLE orders (
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    total_amount DECIMAL(10,2),
    created_at DATETIME NOT NULL,
    INDEX idx_covering (status, created_at, total_amount)
);

-- 커버링 인덱스가 적용되는 쿼리
-- SELECT 컬럼이 모두 인덱스에 포함됨
SELECT status, created_at, total_amount
FROM orders
WHERE status = 'COMPLETED'
  AND created_at >= '2025-01-01';

-- EXPLAIN 결과
-- type: range
-- key: idx_covering
-- Extra: Using where; Using index  ← 커버링 인덱스!

-- 커버링 인덱스가 적용되지 않는 쿼리
-- customer_id가 인덱스에 없으므로 테이블 접근 필요
SELECT status, created_at, total_amount, customer_id
FROM orders
WHERE status = 'COMPLETED';

3. 슬로우 쿼리 로그 활성화와 분석

성능 문제를 사전에 감지하려면 슬로우 쿼리 로그를 활성화해두는 것이 필수입니다.

슬로우 쿼리 로그 설정

-- MySQL 설정 파일 (my.cnf)
[mysqld]
# 슬로우 쿼리 로그 활성화
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1          # 1초 이상 걸리는 쿼리 기록
log_queries_not_using_indexes = 1  # 인덱스 미사용 쿼리도 기록
log_slow_admin_statements = 1      # ALTER TABLE 등 관리 쿼리도 기록
min_examined_row_limit = 1000      # 1000행 이상 조회한 쿼리만 기록

-- 런타임에 동적 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- 500ms로 설정
SET GLOBAL log_queries_not_using_indexes = 'ON';

mysqldumpslow로 분석

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

# 가장 많이 발생하는 슬로우 쿼리 상위 10개
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

# 결과 예시
Count: 1523  Time=2.34s (3565s)  Lock=0.00s (0s)  Rows=156.2 (237896)
  SELECT o.order_id, c.name
  FROM orders o JOIN customers c ON o.customer_id = c.id
  WHERE o.status = 'S' AND o.created_at >= 'S'
  ORDER BY o.created_at DESC LIMIT N

# → 1523회 발생, 평균 2.34초, 총 누적 3565초 소모

Performance Schema 활용 (MySQL 8.0+)

-- 가장 오래 걸리는 쿼리 다이제스트 조회
SELECT
    DIGEST_TEXT,
    COUNT_STAR AS exec_count,
    ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
    ROUND(SUM_TIMER_WAIT / 1000000000, 2) AS total_ms,
    SUM_ROWS_EXAMINED AS rows_examined,
    SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- 인덱스 사용 현황 확인
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    COUNT_FETCH AS fetch_count,
    COUNT_INSERT AS insert_count,
    COUNT_UPDATE AS update_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'mydb'
ORDER BY COUNT_FETCH DESC;

-- 사용되지 않는 인덱스 찾기
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
  AND COUNT_FETCH = 0
  AND COUNT_INSERT = 0
  AND COUNT_UPDATE = 0
  AND OBJECT_SCHEMA = 'mydb';

4. InnoDB 버퍼 풀 튜닝

InnoDB 버퍼 풀은 MySQL에서 가장 중요한 메모리 영역입니다. 디스크 I/O를 줄이기 위해 테이블 데이터와 인덱스를 메모리에 캐싱합니다.

버퍼 풀 크기 설정

# my.cnf
[mysqld]
# 전체 시스템 메모리의 70-80%를 버퍼 풀에 할당 (전용 DB 서버 기준)
# 예: 16GB RAM → 12GB 버퍼 풀
innodb_buffer_pool_size = 12G

# 버퍼 풀 인스턴스 수 (멀티코어 환경에서 병렬 처리)
# 1GB당 1개 인스턴스, 최대 64개
innodb_buffer_pool_instances = 12

# 버퍼 풀 덤프/복원 (재시작 시 워밍업 시간 절약)
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

버퍼 풀 히트율 확인

-- 버퍼 풀 히트율 계산 (99% 이상이 이상적)
SHOW GLOBAL 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.85% → 양호
-- 95% 미만이면 버퍼 풀 크기 증가 필요

기타 InnoDB 튜닝 파라미터

[mysqld]
# 리두 로그 크기 (쓰기 성능에 영향)
innodb_redo_log_capacity = 4G          # MySQL 8.0.30+

# I/O 용량 설정 (SSD 기준)
innodb_io_capacity = 2000              # 일반 I/O
innodb_io_capacity_max = 4000          # 최대 I/O

# 플러시 설정
innodb_flush_log_at_trx_commit = 1     # 1: 안전 (기본값), 2: 성능 우선
innodb_flush_method = O_DIRECT         # OS 캐시 바이패스 (Linux)

# 동시 스레드
innodb_read_io_threads = 8
innodb_write_io_threads = 8

5. 쿼리 캐시와 MySQL 8.0 이후 변화

MySQL 5.7까지는 쿼리 캐시(Query Cache)가 있었지만, MySQL 8.0에서 완전히 제거되었습니다.

쿼리 캐시가 제거된 이유

• 테이블 단위 무효화: 테이블에 한 행만 변경되어도 해당 테이블의 모든 캐시가 무효화
• 글로벌 뮤텍스 경합: 쿼리 캐시 접근 시 글로벌 뮤텍스 잠금으로 동시성 저하
• 쓰기가 빈번한 워크로드에서 오히려 성능 저하

MySQL 8.0 이후 대안

-- 방법 1: 애플리케이션 레벨 캐시 (Redis/Memcached)
-- Spring Boot + Redis 캐시 예시

@Service
public class OrderService {

    @Cacheable(value = "orders", key = "#customerId + '_' + #status",
              unless = "#result.isEmpty()")
    public List<Order> getOrders(Long customerId, String status) {
        return orderRepository.findByCustomerIdAndStatus(customerId, status);
    }

    @CacheEvict(value = "orders", allEntries = true)
    public Order createOrder(OrderRequest request) {
        // 주문 생성 시 캐시 무효화
        return orderRepository.save(toEntity(request));
    }
}

-- 방법 2: MySQL의 인덱스 + 버퍼 풀 최적화
-- 쿼리 캐시 대신 인덱스를 잘 설계하면 버퍼 풀 내에서 빠르게 조회

-- 방법 3: ProxySQL 쿼리 캐싱
-- ProxySQL의 쿼리 캐시 기능으로 애플리케이션 변경 없이 캐싱
-- proxysql.cnf
mysql_query_rules:
- rule_id: 1
  match_pattern: "^SELECT .* FROM orders WHERE status"
  cache_ttl: 5000  # 5초 캐시

6. 파티셔닝

대용량 테이블에서 조회 성능을 높이기 위해 파티셔닝을 활용할 수 있습니다. 특히 시간 기반 데이터나 로그성 데이터에 효과적입니다.

RANGE 파티셔닝 (가장 많이 사용)

-- 월별 파티셔닝
CREATE TABLE order_logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    action VARCHAR(50) NOT NULL,
    detail TEXT,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503),
    PARTITION p202503 VALUES LESS THAN (202504),
    PARTITION p202504 VALUES LESS THAN (202505),
    PARTITION p202505 VALUES LESS THAN (202506),
    PARTITION p202506 VALUES LESS THAN (202507),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 파티션 프루닝 확인
EXPLAIN SELECT * FROM order_logs
WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01';
-- partitions: p202503  ← 해당 파티션만 스캔

-- 파티션 관리: 오래된 데이터 삭제
ALTER TABLE order_logs DROP PARTITION p202501;

-- 신규 파티션 추가
ALTER TABLE order_logs REORGANIZE PARTITION p_future INTO (
    PARTITION p202507 VALUES LESS THAN (202508),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

파티셔닝 주의사항

• 파티션 키는 반드시 PK/UNIQUE KEY에 포함되어야 합니다
• 파티션 프루닝이 동작하려면 WHERE 절에 파티션 키 조건이 포함되어야 합니다
• 파티션 수가 너무 많으면(1000개 이상) 오히려 성능 저하
• JOIN이 빈번한 테이블보다는 로그성/이력성 테이블에 적합

7. JOIN 최적화와 Subquery vs JOIN

MySQL에서 JOIN 성능은 쿼리 전체 성능에 큰 영향을 미칩니다. 올바른 JOIN 전략과 Subquery/JOIN 선택이 중요합니다.

JOIN 최적화 원칙

-- 원칙 1: 작은 결과 집합을 먼저 필터링
-- Bad: 큰 테이블 먼저 JOIN
SELECT o.*, p.name
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.created_at >= '2025-01-01';
-- orders 전체를 JOIN 후 필터링

-- Good: MySQL 옵티마이저가 자동으로 최적화하지만, 인덱스가 핵심
-- orders.created_at에 인덱스 + orders.product_id에 인덱스
CREATE INDEX idx_orders_created ON orders (created_at);
CREATE INDEX idx_orders_product ON orders (product_id);

-- 원칙 2: JOIN 컬럼에 인덱스 필수
-- JOIN ON 절의 컬럼에 인덱스가 없으면 Nested Loop에서 풀스캔 발생
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- 원칙 3: 같은 타입과 문자셋으로 JOIN
-- Bad: 타입 불일치로 인덱스 사용 불가
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id;  -- customer_id: VARCHAR, id: BIGINT → 암묵적 변환!

-- Good: 동일 타입으로 설계
-- 둘 다 BIGINT 또는 동일 타입 사용

Subquery vs JOIN 비교

-- Case 1: IN + Subquery → JOIN으로 변환
-- Subquery (MySQL 8.0에서는 옵티마이저가 자동 변환하기도 함)
SELECT * FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE grade = 'VIP'
);

-- JOIN (명시적, 더 예측 가능한 실행 계획)
SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.grade = 'VIP';

-- Case 2: EXISTS는 Subquery가 효율적인 경우
-- 존재 여부만 확인할 때 EXISTS가 효율적
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
      AND o.created_at >= '2025-01-01'
);
-- EXISTS는 한 건만 찾으면 멈추므로 효율적

-- Case 3: 상관 서브쿼리 → JOIN으로 변환
-- Bad: 상관 서브쿼리 (행마다 서브쿼리 실행)
SELECT o.*,
    (SELECT COUNT(*) FROM order_items oi WHERE oi.order_id = o.order_id) AS item_count
FROM orders o
WHERE o.status = 'COMPLETED';

-- Good: JOIN + GROUP BY
SELECT o.*, COALESCE(oi_count.cnt, 0) AS item_count
FROM orders o
LEFT JOIN (
    SELECT order_id, COUNT(*) AS cnt
    FROM order_items
    GROUP BY order_id
) oi_count ON o.order_id = oi_count.order_id
WHERE o.status = 'COMPLETED';

8. 실전 Before/After 실행 계획 비교

실제 운영 환경에서 발생할 수 있는 슬로우 쿼리를 개선하는 과정을 단계별로 살펴봅니다.

Case: 주문 목록 조회 API (2.8초 → 12ms)

-- 문제 쿼리
SELECT o.order_id, o.total_amount, o.created_at,
       c.name AS customer_name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'COMPLETED'
  AND o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

-- Before: EXPLAIN 결과
-- orders: type=ALL, rows=982341, Extra=Using where; Using filesort
-- customers: type=eq_ref, rows=1
-- 실행 시간: 2.8초

-- Step 1: 복합 인덱스 추가
CREATE INDEX idx_orders_status_created ON orders (status, created_at);

-- After Step 1: EXPLAIN 결과
-- orders: type=range, key=idx_orders_status_created, rows=156234
-- Extra=Using index condition; Using filesort (여전히 filesort)
-- 실행 시간: 320ms

-- Step 2: 정렬을 인덱스로 처리 (내림차순 인덱스)
DROP INDEX idx_orders_status_created ON orders;
CREATE INDEX idx_orders_status_created_desc ON orders (status, created_at DESC);

-- After Step 2: EXPLAIN 결과
-- orders: type=range, key=idx_orders_status_created_desc, rows=156234
-- Extra=Using index condition (filesort 제거!)
-- 실행 시간: 45ms

-- Step 3: 커버링 인덱스로 테이블 접근 최소화
DROP INDEX idx_orders_status_created_desc ON orders;
CREATE INDEX idx_orders_covering ON orders (
    status, created_at DESC, order_id, total_amount, customer_id
);

-- After Step 3: EXPLAIN 결과
-- orders: type=range, key=idx_orders_covering, rows=20
-- Extra=Using where; Using index (커버링 인덱스!)
-- 실행 시간: 12ms

성능 개선 요약

단계	변경사항	실행 시간	개선율
Before	인덱스 없음	2,800ms	-
Step 1	복합 인덱스 추가	320ms	88% 개선
Step 2	내림차순 인덱스 (filesort 제거)	45ms	98% 개선
Step 3	커버링 인덱스	12ms	99.6% 개선

9. 추가 튜닝 팁

페이지네이션 최적화 (No Offset)

-- Bad: OFFSET이 커지면 느려짐
SELECT * FROM orders
WHERE status = 'COMPLETED'
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;  -- 100,000행을 읽고 버림

-- Good: Keyset Pagination (커서 기반)
SELECT * FROM orders
WHERE status = 'COMPLETED'
  AND created_at < '2025-03-15 14:30:00'  -- 이전 페이지 마지막 값
ORDER BY created_at DESC
LIMIT 20;  -- 인덱스로 바로 위치 이동

COUNT(*) 최적화

-- Bad: 전체 COUNT (대용량 테이블에서 느림)
SELECT COUNT(*) FROM orders WHERE status = 'COMPLETED';
-- InnoDB는 MVCC 때문에 정확한 COUNT에 풀 인덱스 스캔 필요

-- Good 1: 근사치 사용
SELECT TABLE_ROWS
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

-- Good 2: 캐시된 카운트 유지
-- 별도 counter 테이블이나 Redis에 카운트 캐싱
-- 주문 생성/상태 변경 시 카운트 업데이트

-- Good 3: 조건부 COUNT에 커버링 인덱스 활용
CREATE INDEX idx_orders_status ON orders (status);
SELECT COUNT(*) FROM orders WHERE status = 'COMPLETED';
-- Using index (커버링 인덱스로 인덱스만 스캔)

SELECT * 지양

-- Bad: 불필요한 컬럼까지 모두 조회
SELECT * FROM orders WHERE status = 'COMPLETED' LIMIT 100;
-- 큰 TEXT/BLOB 컬럼이 있으면 버퍼 풀 낭비

-- Good: 필요한 컬럼만 명시
SELECT order_id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'COMPLETED'
LIMIT 100;
-- 커버링 인덱스 활용 가능성 증가

마치며

MySQL 성능 튜닝은 한 번에 끝나는 작업이 아니라, 데이터가 증가하고 트래픽 패턴이 변하면서 지속적으로 관리해야 하는 과정입니다. 이 글에서 다룬 내용을 정리합니다.

• EXPLAIN ANALYZE로 시작: 감이 아닌 데이터로 병목을 찾습니다. type=ALL, Using filesort, Using temporary가 보이면 개선 포인트입니다.
• 인덱스 설계는 쿼리 기반으로: 왼쪽 접두사 규칙을 지키고, 등호 조건을 앞에, 범위 조건을 뒤에 배치합니다. 커버링 인덱스로 테이블 접근을 최소화합니다.
• 슬로우 쿼리 로그는 항상 켜두세요: 문제가 발생한 뒤 찾는 것보다 미리 모니터링하는 것이 훨씬 효과적입니다.
• InnoDB 버퍼 풀은 충분히 할당: 전용 DB 서버라면 메모리의 70~80%를 버퍼 풀에 할당하고, 히트율 99% 이상을 유지합니다.
• JOIN은 인덱스가 핵심: JOIN 컬럼에 인덱스가 없으면 풀스캔이 발생합니다. 타입과 문자셋을 통일합니다.

성능 튜닝에서 가장 중요한 것은 "측정하고 개선하는 사이클"입니다. 감으로 인덱스를 추가하지 말고, EXPLAIN으로 현재 상태를 확인하고, 변경 후 반드시 효과를 검증하세요. 이 습관 하나만으로도 대부분의 성능 문제를 체계적으로 해결할 수 있습니다.