오늘의 주제는 “SQL 최적화” 입니다.
이번에는 기존에 작성했던 쿼리 튜닝 게시글과 다르게
아래 문제를 바탕으로 작성된 쿼리를 튜닝해보는 시간을 가져보려 합니다.
테이블은 총 2개이며, 각 테이블의 정보는 다음과 같습니다.
📌 테이블 구성
작업목록
| 컬럼명 | 데이터 타입 | 설명 |
|---|---|---|
| 작업ID | VARCHAR(10) | 작업 식별자 (PK) |
| 작업구분코드 | VARCHAR(3) | 작업 구분 |
| 작업명 | VARCHAR(100) | 작업 이름 |
| 등록자 | VARCHAR(50) | 등록자 정보 |
▶ 테이블의 총 레코드는 1,000만 건
▶ 작업구분코드의 분포도는 아래를 참고
- AAL: 40%
- CCL: 55%
- CML: 5%
▶ 인덱스 구성
- 작업목록_PK: 작업ID
- 작업목록_X1: 작업구분코드
작업실행로그
| 컬럼명 | 데이터 타입 | 설명 |
|---|---|---|
| 작업로그ID | VARCHAR(10) | 작업로그 식별자 (PK) |
| 작업ID | VARCHAR(10) | 작업 식별자 |
| 작업일시 | DATETIME | 작업 실행 일시 |
▶ 하루 평균 1만 개의 작업실행로그 생성
▶ 총 레코드는 1억 5,000만 건이며, 최초 생성 이후 별도의 데이터 삭제는 없었음
▶ 작업목록 테이블에 등록된 작업의 실행로그만 관리하며, 작업ID는 Not Null
▶ 인덱스 구성
- 작업실행로그_PK: 작업로그ID
- 작업실행로그_X1: 작업ID
- 작업실행로그_X2: 작업일시
❓ 실전 문제
Q1. 2025년 05월 01일자에 실행된 작업중 작업구분코드가 'CCL'인 작업의 작업명, 등록자, 마지막 작업일시를 구하는 쿼리를 작성하시오.
select c.작업명, c.등록자, a.max_time as 마지막작업시간
from (
select 작업ID
, MAX(작업일시) as max_time
from 작업실행로그
group by 작업ID
) a
join 작업실행로그 b on a.작업ID = b.작업ID
and a.max_time = b.작업일시
join 작업목록 c on a.작업ID = c.작업ID
where c.작업구분코드 = 'CCL'
and DATE_FORMAT(b.작업일시, '%y-%m-%d') = '2025-05-01'
;😭 아쉬운 점
위와 같은 문제를 작성한 쿼리를 보았을 때 여러분은 어떤 생각이 드시나요?
제가 가장 먼저 든 생각은 과연 결과값이 나올 수 있을까? 였습니다.
물론 하염없이 기다리다 보면 결과는 나오겠지만, 수행시간에 초점을 둔다면 "No" 입니다.
제가 생각했을 때 위의 쿼리 문제점은 아래와 같이 나열해 볼 수 있을 것 같습니다.
1️⃣ 인라인뷰를 사용한 불필요한 GROUP BY
▶ 작성자가 문제를 너무 복잡하게 생각한 탓일까요? 단순하게 작업실행로그와 작업목록 테이블간의 조인만으로 해결할 수 있어 보이는데, 작성자의 경우 작업실행로그 테이블의 모든 작업ID의 마지막 작업일시를 구했습니다. 물론 이렇게 모든 작업ID와 각 작업의 마지막 작업일시를 미리 구한다면 작업목록에서 데이터를 빠르게 가져올 수 있다고 생각했을 수 있겠습니다.
다만, 작성자가 작업실행로그 테이블에 얼마나 많은 작업ID가 존재하는지 파악하지 못한게 조금 아쉽다고 볼 수 있을 것 같습니다. 집계 함수의 경우 정렬, 조인등의 작업보다는 리소스가 적게 발생하기 때문에 여기서의 문제점은 없겠으나 작업목록 테이블이 1,000만 건이라는 것을 생각하면 확실히 좋은 방법은 아니겠죠?
2️⃣ 불필요한 JOIN
▶ 앞서 인라인뷰를 통해 각각의 작업ID별 마지막 작업일시를 구했는데도 불필요하게 작업실행로그 테이블과 다시 한번 조인을 하도록 작성되어 있습니다.
인라인뷰에서는 1,000만 건의 데이터를 가지고 있으므로 수많은 데이터와 조인을 시도하게 될텐데요, NL 조인만을 지원하는 MySQL 의 경우 다음과 같은 조인 방식은 엄청난 리소스를 사용하게 됩니다.
3️⃣ 인덱스 컬럼 가공
▷ 제가 엔지니어들에게 가장 많이 강조하는 것 중 하나가 바로 "인덱스 컬럼을 가공하지 마세요." 입니다. 많은 엔지니어들이 DATE형 컬럼에 작성의 편의성을 위해 DATE_FORMAT()과 같은 함수로 가공하여 작성합니다.
ex) DATE_FORMAT(작업일시, '%y-%m-%d') = '2001-01-19'
그러나 이와 같이 작성하게 되면 인덱스를 사용할 수 없게 되는 문제가 발생합니다.
위 쿼리에서도 [작업일시] 구성의 작업실행로그_X2 인덱스가 존재하지만 조건절 컬럼을 가공했기 때문에 효과적으로 인덱스를 사용하지 못하게 됩니다.
그러면 위와 같은 문제를 풀기 위해 효율적인 SQL문은 어떻게 작성해야 할까요?
저는 아래와 같이 작성하여 위 문제들을 해결해 보았습니다.
✅ 최적화된 쿼리
select b.작업명
, b.등록자
, max(a.작업일시) as 마지막작업시간
from 작업실행로그 a
join 작업목록 b on a.작업ID = b.작업ID
where a.작업일시 >= '2025-05-01'
and a.작업일시 < '2025-05-02'
and b.작업구분코드 = 'CCL'
group by b.작업명
, b.등록자
;📈 개선 효과
위와 같이 쿼리를 작성하게 되면
작업실행로그 테이블에서는 [작업실행로그_X2] 인덱스를 사용해 해당 기간 동안의 데이터를 빠르게 Range Scan 할 수 있게 되고,
작업목록 테이블에서는 [작업목록_PK] 인덱스를 사용해 데이터를 빠르게 가져와 작업구분코드가 'CCL'인 데이터를 필터링해 데이터를 처리할 수 있게 됩니다.
위와 같이 쿼리를 작성하게 되면 작업실행로그 테이블에서는 [작업실행로그_X2] 인덱스를 사용해 해당 기간 동안의 데이터를 빠르게 Range Scan 할 수 있게 되고,
작업목록 테이블에서는 [작업목록_PK] 인덱스를 사용해 데이터를 빠르게 가져와 작업구분코드가 'CCL' 인 데이터를 필터링해 데이터를 처리할 수 있게 됩니다.
💬 Q&A
Q. 왜 작업구분코드로 구성된 작업목록_X1 인덱스를 사용하지 않고 작업목록_PK 인덱스를 사용했나요?
A. 작업목록에서 작업구분코드가 'CCL'인 데이터의 분포도는 약 55% 입니다.
이 때문에 옵티마이저는 작업목록_X1 인덱스가 아닌 작업목록_PK 인덱스를 사용했을 가능성이 높겠네요.
💡 핵심 포인트
▶ 인덱스 컬럼은 절대 가공하지 않기 - 함수 사용 시 인덱스 활용 불가
▶ 필요한 데이터만 먼저 필터링 - 전체 데이터 집계 지양
▶ 불필요한 서브쿼리/인라인뷰 제거 - 쿼리 구조 단순화
▶ 데이터 분포도 고려 - 인덱스 선택에 영향
