SQL Tuning Report: 조인 순서 및 인덱스 활용 개선¶
1. Analysis Overview¶
대상 쿼리¶
EXPLAIN
SELECT 매핑.사원번호, 부서.부서번호
FROM 부서사원_매핑 매핑, 부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.시작일자 >= '2002-03-01';
실행 계획 (EXPLAIN) 결과¶
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | 부서 | index | PRIMARY | UI_부서명 | 122 | NULL | 9 | Using index |
| 1 | SIMPLE | 매핑 | ALL | I_부서번호 | NULL | NULL | NULL | 331143 | Using where; Using join buffer (flat, BNL join) |
데이터 현황 통계¶
부서사원_매핑전체 건수: 331,603건부서전체 건수: 9건시작일자 >= '2002-03-01'조건 만족 건수: 1,341건 (전체의 약 0.4%)
2. Performance Diagnosis¶
⚠️ 주요 문제점¶
- 비효율적인 조인 순서: 데이터 건수가 매우 적은
부서(9건)를 드라이빙 테이블로 선택했으나, 드리븐 테이블인매핑에 적절한 인덱스 조인이 발생하지 않고 있습니다. - Access Type: ALL (Full Table Scan):
매핑테이블에서 약 33만 건의 데이터를 전수 조사하고 있습니다. 특히BNL join(Block Nested Loop) 방식이 사용되어,부서테이블의 각 행에 대해매핑테이블을 반복적으로 풀 스캔하는 부하가 발생합니다. - 인덱스 미활용:
매핑.시작일자조건은 전체 데이터의 0.4%만 추출하는 매우 변별력(Selectivity)이 높은 조건임에도 불구하고, 이를 처리할 인덱스가 없어 활용되지 못하고 있습니다.
3. Optimization Strategy¶
💡 인덱스 전략 및 쿼리 제안¶
가장 효과적인 방법은 변별력이 높은 시작일자 컬럼을 선두로 하는 인덱스를 생성하여 Index Range Scan을 유도하고, 조인 순서를 변경하는 것입니다.
추천 인덱스 생성¶
최적화된 조인 유도¶
인덱스 생성 후에는 옵티마이저가 자동으로 매핑 테이블을 드라이빙 테이블로 선택할 가능성이 높습니다. (전체 33만 건 중 1,341건만 읽으면 되기 때문)
4. Expected Impact¶
수정 후 예상되는 실행 계획의 변화는 다음과 같습니다.
| 지표 | 개선 전 | 개선 후 (예상) | 비고 |
|---|---|---|---|
| 드라이빙 테이블 | 부서 (9행) | 매핑 (1,341행) | 필터링 효율이 좋은 테이블이 선행 |
| type | ALL | range | 인덱스 범위 스캔 활용 |
| key | NULL | I_매핑_시작일자_부서번호 | 신규 생성 인덱스 활용 |
| rows | 331,143 | 1,341 | 읽기 대상 행 수가 약 99.6% 감소 |
| Extra | Using join buffer (BNL) | Using index condition | 불필요한 조인 버퍼 제거 및 인덱스 활용 |
💡 인덱스 없이 힌트를 통한 조인 순서 고정 (STRAIGHT_JOIN)¶
SELECT STRAIGHT_JOIN 매핑.사원번호, 부서.부서번호
FROM 부서사원_매핑 매핑, 부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.시작일자 >= '2002-03-01';
[STRAIGHT_JOIN 적용 시 실행 계획] | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | | 1 | SIMPLE | 매핑 | ALL | I_부서번호 | NULL | NULL | NULL | 331,143 | Using where | | 1 | SIMPLE | 부서 | eq_ref | PRIMARY | PRIMARY | 12 | tuning.매핑.부서번호 | 1 | |
요약¶
- 변별력 중심 인덱싱: 데이터 분포도가 좁은(0.4%)
시작일자컬럼에 인덱스를 부여하는 것이 성능 개선의 핵심입니다. - Full Scan 제거: BNL 조인을 제거하고 NL(Nested Loop) 조인 또는 해시 조인 시 인덱스를 타도록 유도하여 IO 부하를 획기적으로 줄일 수 있습니다.
- 인덱스 생성이 어려울 경우, STRAIGHT_JOIN 힌트를 사용해 상대적으로 대용량인 매핑 테이블을 랜덤 액세스 없이 테이블 풀 스캔으로 처리하여 성능 개선.