크레용 서비스에서 발생한 슬로우 쿼리를 개선한 스토리입니다.
쿼리 수행 시간은 웜업을 고려해 첫 번째 쿼리 수행 시간은 평균값에서 배제했습니다.

글에서 다루는 쿼리는 '모집 프로세스 목록 조회 API'에서 발생하는 쿼리입니다.
모집(recruitment)과 프로세스(process)는 1:N 관계이며,
프로세스마다 지원(application) 수가 몇인지 함께 집계할 필요가 있습니다.

결론부터 말하자면

• 변경 전

SELECT p1_0.*,
       COUNT(a1_0.application_id) AS application_count
FROM process p1_0
         LEFT JOIN
     application a1_0 ON p1_0.process_id = a1_0.process_id AND a1_0.deleted_at IS NULL
WHERE p1_0.recruitment_id = 0xA38ED94A70B34D61B140979AD877FDBC
GROUP BY p1_0.process_id, p1_0.stage
ORDER BY p1_0.stage;

시도	1	2	3	avg
실행 시간(ms)	496	324	396	405

• 변경 후

SELECT p1_0.*,
       COUNT(a1_0.application_id) AS application_count
FROM process p1_0
         LEFT JOIN
     application a1_0 ON p1_0.process_id = a1_0.process_id AND a1_0.deleted_at IS NULL
WHERE p1_0.recruitment_id = 0xA38ED94A70B34D61B140979AD877FDBC
GROUP BY p1_0.stage, p1_0.process_id /*조건 순서 변경*/
ORDER BY p1_0.stage;

# 커버링 인덱스
CREATE INDEX idx_process_deleted ON application(process_id, deleted_at);
# 복합인덱스를 활용한 정렬 및 그룹화
CREATE INDEX idx_recruitment_stage on process(recruitment_id, stage);

시도	1	2	3	avg
실행 시간(ms)	42	34	35	37

기존 쿼리 실행 계획 분석하기

EXPLAIN
SELECT p1_0.*,
       COUNT(a1_0.application_id) AS application_count
FROM process p1_0
         LEFT JOIN
     application a1_0 ON p1_0.process_id = a1_0.process_id AND a1_0.deleted_at IS NULL
WHERE p1_0.recruitment_id = 0xA38ED94A70B34D61B140979AD877FDBC
GROUP BY p1_0.process_id, p1_0.stage
ORDER BY p1_0.stage;

id	select_type	table	type	possible_keys	key	ref	rows	filtered	Extra
1	SIMPLE	p1_0	ref	UK_recruitment	UK_recruitment	const	3	100	Using index condition; Using temporary; Using filesort
1	SIMPLE	a1_0	ref	idx_process	idx_process	crayon.p1_0.process_id	9476	100	Using where

---

temporary, filesort는 무엇이고 왜 발생할까? 🤔

1. Using temporary: 쿼리 실행 중 메모리에 임시 테이블을 만들고 데이터를 복사하여 처리. 메모리로 감당 안 될 경우 → 디스크를 사용한다 (느림)

2. Using filesort: 인덱스를 사용하지 않고 정렬

temporary table의 생성 조건

상황	설명
UNION 문	일부 예외를 제외하고 UNION 문을 처리할 때 임시 테이블 생성
파생 테이블	서브쿼리 형태의 파생 테이블(derived tables) 처리 시 생성
ORDER BY와 GROUP BY 조건이 다른 경우	ORDER BY 절과 다른 GROUP BY 절 사용 시, 또는 ORDER BY/GROUP에 첫 번째 테이블 외의 열 포함 시 생성
....	...

temporary table에 대한 자세한 내용은 공식문서를 참고하자.

---

정렬 최적화 시도 1) ORDER BY 조건 변경

그럼 다시 SQL문을 보자.

SELECT p1_0.*,
       COUNT(a1_0.application_id) AS application_count
FROM process p1_0
LEFT JOIN
     application a1_0 ON p1_0.process_id = a1_0.process_id AND a1_0.deleted_at IS NULL
WHERE p1_0.recruitment_id = 0xA38ED94A70B34D61B140979AD877FDBC
GROUP BY p1_0.process_id, p1_0.stage
ORDER BY p1_0.stage;

공식문서에서 확인했듯이, MySQL은 ORDER BY와 GROUP BY 조건이 다른 경우 temporary table을 사용한다.
그래서 해당 쿼리의 ORDER BY와 GROUP BY 조건이 일치하지 않아 temporary table과 filesort를 수행하는 것으로 분석했다.

ORDER BY 절에 stage를 걸어두긴 했지만, 서비스 데이터 특성 상 process_id로 걸어도 결과는 다르지 않다. 그러니 process_id로 정렬하고 그룹화 기준에서 stage도 제외한다면 temporary table을 사용하지 않을 것을 기대한다.

SELECT p1_0.*,
       COUNT(a1_0.application_id) AS application_count
FROM process p1_0
LEFT JOIN
     application a1_0 ON p1_0.process_id = a1_0.process_id AND a1_0.deleted_at IS NULL
WHERE p1_0.recruitment_id = 0xA38ED94A70B34D61B140979AD877FDBC
GROUP BY p1_0.process_id
ORDER BY p1_0.process_id;

id	select_type	table	type	possible_keys	key	ref	rows	filtered	Extra
1	SIMPLE	p1_0	ref	PRIMARY,UK_recruitment	UK_recruitment	const	3	100	Using index condition
1	SIMPLE	a1_0	ref	idx_process	idx_process	crayon.p1_0.process_id	9476	100	Using where

GROUP BY와 ORDER BY 조건을 통일시키니 예상대로 Using temporary; Using filesort는 발생하지 않는다.

✋ 그런데 잠깐 !!!

크레용은 계획이 있다.

process 순서도 사용자의 마음대로 커스텀 가능할 것이다 !

이것이 우리가 stage 필드를 사용했던 이유다.
그러니 ORDER BY를 변경하지 않는 조건 아래에서 해결책을 찾아야 한다.

정렬 최적화 시도 2) GROUP BY 조건 순서 변경

SELECT p1_0.*,
       COUNT(a1_0.application_id) AS application_count
FROM process p1_0
LEFT JOIN
     application a1_0 ON p1_0.process_id = a1_0.process_id AND a1_0.deleted_at IS NULL
WHERE p1_0.recruitment_id = 0xA38ED94A70B34D61B140979AD877FDBC
GROUP BY p1_0.stage, p1_0.process_id
ORDER BY p1_0.stage;

id	select_type	table	type	possible_keys	key	ref	rows	filtered	Extra
1	SIMPLE	p1_0	ref	UK_recruitment	UK_recruitment	const	3	100	Using index condition; Using temporary; Using filesort
1	SIMPLE	a1_0	ref	idx_process	idx_process	crayon.p1_0.process_id	9476	100	Using where

GROUP BY 순서를 변경했지만 여전히 Using temporary; Using filesort; 이 발생한다. 🤔

-> Sort: p1_0.stage, p1_0.process_id  (actual time=235..235 rows=3 loops=1)
    -> Table scan on <temporary>  (actual time=235..235 rows=3 loops=1)
        -> Aggregate using temporary table  (actual time=235..235 rows=3 loops=1)
            -> Nested loop left join  (cost=5515 rows=28431) (actual time=0.716..130 rows=102685 loops=1)
                -> Index lookup on p1_0 using UK_recruitment (recruitment_id = 0xa38ed94a70b34d61b140979ad877fdbc), with index condition: (p1_0.recruitment_id = 0xa38ed94a70b34d61b140979ad877fdbc)  (cost=0.8 rows=3) (actual time=0.269..0.274 rows=3 loops=1)
                -> Filter: (a1_0.deleted_at is null)  (cost=1206 rows=9477) (actual time=0.156..41.7 rows=34228 loops=3)
                    -> Index lookup on a1_0 using idx_process (process_id = p1_0.process_id)  (cost=1206 rows=9477) (actual time=0.155..40 rows=34228 loops=3)

실행계획을 분석해보니 recruitment_id 인덱스를 사용해서 process를 찾고 임시테이블을 사용해 stage를 정렬하는 순서로 쿼리를 처리한다.
GROUP BY 후, 결과를 ORDER BY 조건대로 정렬하기 위해 임시 테이블을 생성하는 것이다.

---

정렬 최적화 시도 3) 복합 인덱스 : 정렬에 최적화된 필터링 결과 유도하기

그렇다면 WHERE 조건에 따라 필터링한 결과가 ORDER BY 조건에 부합한다면 별도로 정렬할 필요가 없을 것 같다.

어떻게 필터링 결과를 내가 원하는 방향으로 유도할 수 있을까?

실마리는 복합 인덱스의 저장 방식에 있다.
복합 인덱스는 순서가 중요한데, 데이터 정렬이 앞선 컬럼에 의존하여 정렬되기 때문이다.
즉, recruitment_id와 stage로 복합 인덱스를 구성하는 경우 recruitment_id로 정렬하고 그 다음으로 stage를 기준으로 정렬하는 것이다.

이런 복합 인덱스의 특징을 활용해 별도의 정렬이 필요없도록 데이터를 필터링하겠다.

CREATE INDEX idx_recruitment_stage on process(recruitment_id, stage);

id	select_type	table	type	possible_keys	key	ref	rows	filtered
1	SIMPLE	p1_0	ref	UK_recruitment,idx_recruitment_stage	idx_recruitment_stage	const	3	100
1	SIMPLE	a1_0	ref	idx_process	idx_process	crayon.p1_0.process_id	9476	100

예상대로 복합 인덱스를 추가했더니 using temporary, using filesort가 발생하지 않는다!

-> Group aggregate: count(a1_0.application_id)  (cost=12065 rows=45.6) (actual time=50.1..131 rows=3 loops=1)
    -> Nested loop left join  (cost=5515 rows=28431) (actual time=0.648..126 rows=102685 loops=1)
        -> Index lookup on p1_0 using idx_recruitment_stage (recruitment_id = 0xa38ed94a70b34d61b140979ad877fdbc), with index condition: (p1_0.recruitment_id = 0xa38ed94a70b34d61b140979ad877fdbc)  (cost=0.8 rows=3) (actual time=0.215..0.222 rows=3 loops=1)
        -> Filter: (a1_0.deleted_at is null)  (cost=1206 rows=9477) (actual time=0.159..40.2 rows=34228 loops=3)
            -> Index lookup on a1_0 using idx_process (process_id = p1_0.process_id)  (cost=1206 rows=9477) (actual time=0.158..38.4 rows=34228 loops=3)

실제로 idx_recruitment_stage 인덱스로 조회하고 다시 정렬하지 않는 것을 확인할 수 있다.

그럼 개선 후 성능은 어떻게 되었을까?

시도	1	2	3	avg
실행 시간(ms)	214	258	198	223

평균으로 비교해보자면 약 45% (405ms → 223ms) 개선되었다! 👀

---

커버링 인덱스 사용하기 (COUNT 할 때 다른 데이터가 왜 필요해?)

다음으로 조인문을 살펴보겠다.

LEFT JOIN application a1_0 
	ON p1_0.process_id = a1_0.process_id 
    AND a1_0.deleted_at IS NULL

COUNT(a1_0.application_id)의 경우 컬럼이 NULL인지 아닌지만 판단하면 되기 때문에 실제 컬럼 값을 읽을 필요가 없다. 즉, 값 자체가 아닌 단순 존재 여부만 확인하면 된다. 그리고 MySQL은 인덱스의 리프 노드에 항상 PK를 포함하고 있다.

process_id와 deleted_at 복합 인덱스를 생성하게 되면 인덱스만으로 아래 조건을 모두 처리할 수 있다.

• process_id 조건 처리
• deleted_at IS NULL 조건 처리
• COUNT(application_id)의 NULL 체크는 PK에서 자동 처리
  

즉, 실제 데이터 접근 행위 없이 인덱스에 있는 컬럼값들로만 쿼리를 완성하는 커버링 인덱스를 적용할 수 있을 것이다.

CREATE INDEX idx_process_deleted ON application(process_id, deleted_at);

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p1_0	null	ref	UK_recruitment,idx_recruitment_stage	idx_recruitment_stage	17	const	3	100	Using index condition
1	SIMPLE	a1_0	null	ref	idx_process,idx_process_deleted	idx_process_deleted	18	crayon.p1_0.process_id,const	9476	100	Using where; Using index

-> Group aggregate: count(a1_0.application_id)  (cost=9516 rows=45.6) (actual time=23.4..54.6 rows=3 loops=1)
    -> Nested loop left join  (cost=2965 rows=28431) (actual time=0.143..49 rows=102685 loops=1)
        -> Index lookup on p1_0 using idx_recruitment_stage (recruitment_id = 0xa38ed94a70b34d61b140979ad877fdbc), with index condition: (p1_0.recruitment_id = 0xa38ed94a70b34d61b140979ad877fdbc)  (cost=0.8 rows=3) (actual time=0.0567..0.0621 rows=3 loops=1)
        -> Filter: (a1_0.deleted_at is null)  (cost=356 rows=9477) (actual time=0.0305..14.6 rows=34228 loops=3)
            -> Covering index lookup on a1_0 using idx_process_deleted (process_id = p1_0.process_id, deleted_at = NULL)  (cost=356 rows=9477) (actual time=0.0301..12.8 rows=34228 loops=3)

마지막 줄에 커버링 인덱스를 사용했다는 것을 확인할 수 있다!

시도	1	2	3	avg
실행 시간(ms)	171	101	145	139

실행 시간이 처음 쿼리에 비해 약 65% (405ms → 139ms) 감소했다!

---

참고

• https://dev.mysql.com/doc/refman/8.4/en/internal-temporary-tables.html
• https://jojoldu.tistory.com/476