MySQL 성능 최적화

6 min read

MySQL 성능 최적화

MySQL의 내부 동작 원리와 성능 최적화 기법을 다룹니다.

성능 최적화 경험

일반적인 최적화 접근법

  1. 슬로우 쿼리 로그 분석: 실행 시간이 긴 쿼리 식별
  2. EXPLAIN 분석: 실행 계획 확인
  3. 인덱스 추가/수정: 적절한 인덱스 설계
  4. 쿼리 리팩토링: 비효율적인 쿼리 개선

실제 사례

-- Before: Full Table Scan (2.3초)
SELECT * FROM orders
WHERE DATE(created_at) = '2024-01-15';
 
-- After: Index Range Scan (0.02초)
SELECT * FROM orders
WHERE created_at >= '2024-01-15 00:00:00'
  AND created_at < '2024-01-16 00:00:00';

FULLTEXT vs LIKE 성능

LIKE ‘%keyword%’ 성능 한계

-- 인덱스 사용 불가, Full Table Scan
SELECT * FROM articles WHERE content LIKE '%검색어%';
  • 100만 건 기준: 약 3-5초
  • 1000만 건 기준: 약 30-50초

FULLTEXT 인덱스 활용

-- FULLTEXT 인덱스 생성
ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);
 
-- FULLTEXT 검색
SELECT * FROM articles
WHERE MATCH(content) AGAINST('검색어' IN BOOLEAN MODE);
  • 100만 건 기준: 약 0.1-0.3초
  • 주의사항: 한글은 ngram 파서 필요

쿼리 분석 방법

EXPLAIN 활용

EXPLAIN SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | u     | ref  | PRIMARY,idx_s | idx_s| 4       | const|  100 | Using where |
|  1 | SIMPLE      | o     | ref  | idx_user      | idx_u| 4       | u.id | 1000 | NULL        |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

EXPLAIN ANALYZE (MySQL 8.0+)

EXPLAIN ANALYZE SELECT ...
  • 실제 실행 시간 측정
  • 예상 vs 실제 행 수 비교

분석 시 중요 지표

EXPLAIN 핵심 컬럼

컬럼의미좋은 값
type접근 방식const > eq_ref > ref > range > index > ALL
key사용된 인덱스NULL이 아닌 값
rows예상 스캔 행 수낮을수록 좋음
Extra추가 정보Using index (커버링)

나쁜 신호

  • type: ALL - Full Table Scan
  • Extra: Using filesort - 정렬을 위한 추가 작업
  • Extra: Using temporary - 임시 테이블 사용

성능과 I/O 지표

모니터링 지표

-- InnoDB 버퍼 풀 히트율
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
 
-- 디스크 I/O
SHOW STATUS LIKE 'Innodb_data_%';
 
-- 슬로우 쿼리 수
SHOW STATUS LIKE 'Slow_queries';

핵심 지표

  • Buffer Pool Hit Ratio: 99% 이상 권장
  • Disk Reads/s: 낮을수록 좋음
  • QPS (Queries Per Second): 처리량 지표

해시 조인 vs NL 조인

Nested Loop Join

For each row in outer_table:
    For each row in inner_table:
        If join_condition matches:
            Return combined row

적합한 경우:

  • 작은 결과 집합
  • 인덱스가 잘 구성된 경우
  • OLTP 워크로드

Hash Join (MySQL 8.0.18+)

1. Build phase: 작은 테이블로 해시 테이블 생성
2. Probe phase: 큰 테이블 스캔하며 해시 테이블 조회

적합한 경우:

  • 큰 결과 집합
  • 인덱스가 없거나 비효율적인 경우
  • OLAP 워크로드
-- 해시 조인 힌트
SELECT /*+ HASH_JOIN(t1, t2) */ *
FROM t1 JOIN t2 ON t1.id = t2.t1_id;

데이터 저장 구조

InnoDB 저장 계층

┌─────────────────────────────────────┐
│           Tablespace                │
│  ┌───────────────────────────────┐  │
│  │          Segment              │  │
│  │  ┌─────────────────────────┐  │  │
│  │  │        Extent           │  │  │
│  │  │  ┌───┬───┬───┬───┬───┐ │  │  │
│  │  │  │ P │ P │ P │ P │ P │ │  │  │
│  │  │  │ a │ a │ a │ a │ a │ │  │  │
│  │  │  │ g │ g │ g │ g │ g │ │  │  │
│  │  │  │ e │ e │ e │ e │ e │ │  │  │
│  │  │  └───┴───┴───┴───┴───┘ │  │  │
│  │  └─────────────────────────┘  │  │
│  └───────────────────────────────┘  │
└─────────────────────────────────────┘
  • Page: 16KB (기본), I/O 최소 단위
  • Extent: 64개 페이지 (1MB)
  • Segment: 테이블스페이스 내 논리적 구조

인덱스 구조

B+Tree 인덱스

        ┌───────────────┐
        │   Root Node   │
        │  [10] [20]    │
        └───────────────┘
       /       |        \
┌─────────┐ ┌─────────┐ ┌─────────┐
│ [3][7]  │ │[12][17] │ │[25][30] │
└─────────┘ └─────────┘ └─────────┘
    |           |           |
┌───────┐  ┌───────┐  ┌───────┐
│ Leaf  │→│ Leaf  │→│ Leaf  │
│ Nodes │  │ Nodes │  │ Nodes │
└───────┘  └───────┘  └───────┘

특징:

  • 리프 노드에만 데이터 저장
  • 리프 노드 간 연결 (범위 검색 효율)
  • 균형 트리 (O(log N) 검색)

Clustered vs Secondary Index

Clustered Index (Primary Key):

  • 리프 노드에 실제 행 데이터 저장
  • 테이블당 하나만 가능

Secondary Index:

  • 리프 노드에 Primary Key 값 저장
  • Primary Key로 실제 데이터 조회 (더블 룩업)

범위 탐색 방법

Index Range Scan

SELECT * FROM users WHERE age BETWEEN 20 AND 30;

동작 과정:

  1. B+Tree에서 시작점(age=20) 찾기: O(log N)
  2. 리프 노드 순차 스캔: 연결 리스트 따라가며
  3. 종료점(age=30) 도달 시 종료

복합 인덱스에서의 범위 탐색

-- INDEX (status, created_at)
SELECT * FROM orders
WHERE status = 'completed'
  AND created_at BETWEEN '2024-01-01' AND '2024-01-31';

주의: 범위 조건 이후 컬럼은 인덱스 활용 불가

-- INDEX (a, b, c)
WHERE a = 1 AND b > 10 AND c = 100  -- c는 인덱스 사용 못함

Backlinks