데이터베이스 성능을 최적화하려면 SQL 쿼리의 실행 계획(EXPLAIN)을 이해하는 것이 필수적이라고 볼 수 있습니다.
또한, 인덱스에 대한 사전 지식이 필요하므로, 인덱스가 궁금하다면 🔍클릭해서 습득하고 오시길 권장드립니다.
이제 EXPLAIN을 분석하는 방법과 성능을 개선하는 전략을 살펴보겠습니다!
1. 실행 계획(EXPLAIN)이란?
SQL 쿼리가 실행될 때 어떤 방식으로 데이터를 검색하는지를 보여주고,
이를 통해 인덱스 사용 여부, 조인 방식, 풀 테이블 스캔 여부 등을 확인이 가능합니다.
[예제]
-
SQL
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-
실행 결과
id select_type table type possible_keys key key_len rows Extra 1 SIMPLE users ALL NULL NULL NULL 1000 Using where
2. EXPLAIN 주요 항목 분석
1) type (접근 방식)
type은 쿼리가 데이터를 검색하는 방식을 나타내며, 성능을 개선하는 핵심 요소입니다.
| type 값 | 설명 | 성능 영향 |
|---|---|---|
| ALL | 전체 테이블 스캔 | 매우 느림 |
| INDEX | 인덱스 전체 스캔 | 비효율적 |
| RANGE | 특정 범위 검색 | 비교적 빠름 |
| REF | 인덱스를 통한 검색 | 최적화됨 |
| EQ_REF | 유니크 인덱스를 통한 검색 | 매우 빠름 |
| CONST | 단일 값 검색 (PK 조회) | 최고 성능 |
💡 최적의 성능을 위해 RANGE, REF, EQ_REF, CONST를 활용하는 것이 좋습니다.
2) possible_keys & key (인덱스 사용 여부)
- possible_keys: 사용할 수 있는 인덱스
- key: 실제 사용된 인덱스
EXPLAIN을 조회해 보고, 인덱스를 사용하지 않았다면 적절한 인덱스를 추가하여 성능을 개선해야 합니다.
3) rows (예상 조회 행 수)
쿼리가 몇 개의 행을 스캔할지 예측하는 값이며, 이 값이 작을수록 성능이 좋습니다!
4) Extra (추가 작업 정보)
Extra 컬럼에는 최적화가 필요한 힌트가 포함됩니다.
| Extra 값 | 설명 | 개선 방법 |
|---|---|---|
| Using where | 조건절 사용 | 인덱스 활용 가능 여부 확인 |
| Using filesort | 파일 정렬 발생 | 인덱스 추가하여 정렬 최적화 |
| Using temporary | 임시 테이블 사용 | GROUP BY 최적화 필요 |
3. 성능 개선 전략
1) 적절한 인덱스 추가
인덱스를 활용하면 ALL 스캔을 방지하고 RANGE, REF 타입으로 최적화할 수 있습니다.
[예제]
- SQL
CREATE INDEX idx_users_email ON users(email);
생성된 인덱스를 통해 WHERE email = 'test@example.com' 조회 시 인덱스를 활용한 검색이 가능합니다.2) 불필요한 SELECT * 지양하기
필요한 컬럼만 선택하면 디스크 I/O 비용을 줄이고 성능을 향상시킬 수 있습니다.
[예제]
❌ 비효율적인 쿼리
- SQL
SELECT * FROM orders WHERE user_id = 123;
✅ 최적화된 쿼리
- SQL
SELECT order_id, order_date FROM orders WHERE user_id = 123;3) 조인 최적화
조인은 필연적으로 비용이 크기 때문에 인덱스를 활용하고 EXPLAIN을 통해 조인 방식을 확인하는 것이 중요합니다.
[예제]
❌ 비효율적인 조인
- SQL
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id;
👉 users.id에 인덱스가 없으면 ALL 스캔이 발생할 수 있습니다.
✅ 인덱스 활용
- SQL
CREATE INDEX idx_users_id ON users(id);4) 서브쿼리 대신 JOIN 활용
서브쿼리는 불필요한 연산을 유발할 수 있습니다.
[예제]
❌ 서브쿼리 사용
- SQL
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total_price > 100);
✅ JOIN으로 변환
- SQL
SELECT DISTINCT u.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total_price > 100;5) LIMIT 사용하여 불필요한 데이터 조회 방지
대량의 데이터를 조회할 경우, LIMIT을 활용하여 쿼리 부담을 줄이는 것이 좋습니다.
[예제]
- SQL
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;
💡 페이징 처리 시 OFFSET 대신 WHERE 활용하는 것이 성능에 좋습니다.4. EXPLAIN ANALYZE로 실행 시간 확인
EXPLAIN ANALYZE를 사용하면 실제 실행 시간을 확인할 수 있습니다.
[예제]
- SQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
이를 통해 실제 실행 시간과 쿼리 최적화 효과를 측정할 수 있습니다.5. 정리
- EXPLAIN을 활용하면 SQL 성능을 분석하고 최적화할 수 있습니다.
- type, rows, Extra를 분석하여 병목 현상을 찾아야 합니다.
- 인덱스 추가, 조인 최적화, 불필요한 컬럼 조회 제거 등의 방법으로 성능을 개선할 수 있습니다.
- EXPLAIN ANALYZE를 통해 실제 실행 성능을 측정하고 지속적으로 개선해야 합니다.
