들어가며 . . .
쿼리 최적화를 아시나요? 저는 이번에 처음으로 자세히 공부해 보았습니다. 이 글을 통해 제가 경험한 시행착오를 팀 분들 및 이 글을 읽으시는 분들에게 공유하고싶습니다.
1. 쿼리 최적화란?
쿼리 최적화는 무엇일까요? 일반적인 정의는 이미 잘 알려져 있으니 제가 이번 기회에 어떻게 생각하게 되었는지 말씀 드리겠습니다.
Optimizer가 실행 계획을 잘 짜도록 유도하는 것.
Optimizer는 소위 금쪽이 입니다. 정해진 동작 방식을 가지고 실행 계획을 잘 작성하려 하지만, 일부 경우에 정말 바보같은 실행계획을 세우곤 합니다.
2. 쿼리 최적화를 잘 한 기준?
쿼리 최적화의 결과 지표로 Query Cost가 있을 수 있고 실행 시간이 있을 수 있습니다.
구글에서 제공한 실행시간에 따른 이탈률을 볼까요?
<이미지 출처: Google / SOASTA Research, 2017>
표로 간단히 볼까요?
| 시간 | 이탈률 |
|---|---|
| 1 ~ 3 sec | 32 % |
| 1 ~ 5 sec | 90 % |
| 1 ~ 6 sec | 106 % |
| 1 ~ 10 sec | 123 % |
1초 이내로 페이지가 로딩되면 너무 좋고, 3초 이내로 로딩되면 좋다고 말할 수 있을 것 같습니다. 하지만, 5초 이상으로는 이탈률이 높아서 쿼리 최적화가 필요해 보입니다.
3. 개선 과정
1. 원본
1. 원본 쿼리
SELECT HOUR(a.reservation_time) as time, COUNT(r.id) as cnt
FROM available_time a
LEFT JOIN reservation r ON a.repair_shop_id = r.repair_shop_id
AND (
(DATE(a.reservation_date) = DATE(r.departure_time) AND
TIME(a.reservation_time) = TIME(r.departure_time))
OR
(DATE(a.reservation_date) = DATE(r.arrival_time) AND
TIME(a.reservation_time) = TIME(r.arrival_time))
)
WHERE a.repair_shop_id = 1
AND a.reservation_date = '2024-02-24'
GROUP BY a.reservation_time
ORDER BY a.reservation_time;2. 성능
| 항목 | 원본 |
|---|---|
| Query Cost | 10 억 |
| 실행 시간 | 13 초 |
3. 문제점 인식
함수로 인해 칼럼의 값이 변경되면, 해당 칼럼을 Optimizer 대상으로 선정할 수 없어서 실행 계획에서 무시됩니다. (가능한 인덱스로 선정되지도 않습니다.)
2. 1차 개선
1. 개선 방향
함수가 걸리는 칼럼을 가상 칼럼으로 생성한 후, 생성한 가상 칼럼에 인덱스를 생성합니다. 이를 통해 Optimizer가 인덱스를 실행 계획에 포함하도록 유도합니다.
alter table reservation
add dep_date DATE as (DATE(departure_time)) virtual,
add index idx_dep_date (dep_date);
alter table reservation
add dep_time TIME as (TIME(departure_time)) virtual,
add index idx_dep_time (dep_time);
alter table reservation
add arr_date DATE as (DATE(arrival_time)) virtual,
add index idx_arr_date (arr_date);
alter table reservation
add arr_time TIME as (TIME(arrival_time)) virtual,
add index idx_arr_time (arr_time);2. 개선 쿼리
SELECT HOUR(a.reservation_time) AS time, COUNT(r.id) AS cnt
FROM available_time a
LEFT JOIN reservation r ON a.repair_shop_id = r.repair_shop_id
AND (
(a.reservation_date = r.dep_date AND a.reservation_time = r.dep_time)
OR
(a.reservation_date = r.arr_date AND a.reservation_time = r.arr_time)
)
WHERE a.repair_shop_id = 1 AND a.reservation_date = '2024-02-24'
GROUP BY HOUR(a.reservation_time)
ORDER BY HOUR(a.reservation_time);3. 성능
| 항목 | 원본 | 1차 개선 |
|---|---|---|
| Query Cost | 10 억 | 1000 만 |
| 실행 시간 | 13초 | 1 분 5 초 |
4. 문제점 인식
쿼리 개선을 통해 실행 계획에는 가능한 인덱스로 추가되었으나 아직 인덱스를 타지는 못하는 것으로 보입니다.
아래 요소를 개선하여 Optimizer가 최적화 대상에 포함하도록 합니다.
1. OR로 인해 selectivity가 높아져서 인덱스를 못타는 것으로 보인다.
2. Subquery는 Optimizer의 최적화 대상에서 제거된다.
3. 2차 개선
1. 개선 방향
-
Selectivity 감소
1차 개선 쿼리의 OR 연산이 탐색 범위를 넓혀서 Selectivity를 낮추어 Optimizer가 인덱스를 타도록 유도합니다.
-
Subquery 제거
Subquery에는 메타 데이터를 담고있지 않기 때문에 명시적인 제약조건이나 인덱스가 작성되어있지 않습니다. 따라서, 서브쿼리를 줄이고 UNION ALL을 사용하여 동일한 결과를 얻도록 구현했습니다.
2. 개선 쿼리
SELECT HOUR(at.reservation_time) as time, SUM(cnt) as total_cnt
FROM ( -- 프롬에는 서브쿼리 규칙이 다르다.
SELECT a.reservation_time, COUNT(r.id) as cnt
FROM available_time a
left JOIN reservation r
ON a.repair_shop_id = r.repair_shop_id
AND a.reservation_date = r.dep_date
AND a.reservation_time = r.dep_time
WHERE a.repair_shop_id = 1
AND a.reservation_date = '2024-02-24'
GROUP BY a.reservation_time
UNION ALL
SELECT a.reservation_time, COUNT(r.id) as cnt
FROM available_time a
left JOIN reservation r
ON a.repair_shop_id = r.repair_shop_id
AND a.reservation_date = r.arr_date
AND a.reservation_time = r.arr_time
WHERE a.repair_shop_id = 1
AND a.reservation_date = '2024-02-24'
GROUP BY a.reservation_time
) as at
GROUP BY HOUR(at.reservation_time)
ORDER BY time;3. 성능
| 항목 | 원본 | 1차 개선 | 2차 개선 |
|---|---|---|---|
| Query Cost | 10 억 | 1000 만 | 500 만 |
| 실행 시간 | 13초 | 1 분 5 초 | 0.3 초 |
