[이음] 프로젝트에 쿼리의 실행계획을 분석하며 최적화 해보기

게시글의 댓글 조회

 

 

기존의 쿼리는

SELECT c.* from comments c WHERE c.post_id = :postId and c.is_deleted = false ORDER BY c.created_at asc 입니다.

 

댓글을 조회할 게시글의 id 값으로 한번 필터링한 후 삭제 여부가 false인 것들을 조회해 생성일자 오름차순으로 조회했습니다.

 

 

 

 

 

 

explain으로 실행계획을 분석했을 때 개선할 수 있는 점을 발견했습니다.

각각의 칼럼별로 간단히 결과를 하나하나 분석해보겠습니다.

id select_type table partitions type possible_keys key key_len ref rows filtered Extra

 

 

id: 쿼리 내부의 SELECT 단위 번호.

select_type: select 문이 어떤 형태인지 나타냄

• SIMPLE: 서브쿼리 없는 단일 SELECT

table: 이 row가 설명하는 테이블 이름

partitions: 어떤 파티션을 사용했는지

type(★): 테이블 접근 방법

• ALL = 풀 테이블 스캔
• INDEX=인덱스 풀 스캔
• ref, eq_ref=key lookup
• range=범위 스캔

possible_keys: 옵티마이저가 사용할 수 있다고 판단한 후보 인덱스 목록

• null: 인덱스를 전혀 사용할 수 없는 상태

key: 실제로 선택된 인덱스

key_len: 사용한 인덱스의 바이트 길이

ref: 인덱스 조회에 사용된 값

rows: 옵티마이저가 예측한 스캔 row 수

• 실제가 아니라 추정치 기반이라 통계 기반 의존
• rows가 높으면 느린 쿼리 가능성 높음

filtered: 조건을 통과한 row 비율

Extra(★): 쿼리가 어떤 추가 작업을 수행하는지 보여주는 매우 중요한 정보

• Using where: WHERE 조건을 통해 row 필터링 중
• Using index: 커버링 인덱스 → 테이블 접근 필요X
• Using temporary: 임시 테이블 생성
• Using filesort: ORDER BY 수행 시 파일 정렬

explain 을 통해 현재 해당 쿼리는 인덱스를 전혀 타지 못하고, ORDER BY 문 때문에 내부적으로 filesort 를 진행중인 매우 비효율적인 쿼리를 발견했습니다.

 

이를 개선해봅시다!

기존에 인덱스가 전혀 없던 Comment 엔터티에 인덱스를 먼저 생성합니다.

 

 

 

 

 

 

인덱스는 where문과 orderby 에서 사용하는 오름차순 순서대로 선행 칼럼을 postId로 뒤이어 isDeleted와 createdAt 오름차순으로 생성합니다.

 

결과는?

 

 

 

 

 

 

 

불필요한 filesort 도 진행하지 않고 새로 생성한 idx_post_id_is_deleted_created_at 인덱스도 key 커럼을 통해 잘 타고 있음을 확인할 수 있습니다.

explain을 통해 프로젝트의 기존의 슬로우 쿼리를 발견하고 인덱스를 타도록 개선할 수 있었습니다.

 

 

 

 

게시글의 좋아요 여부 조회

 

 

 

해당 쿼리는 Like 엔터티가 Unique 제약 조건으로 인덱스가 걸려 있기 때문에 const type으로 조회가 완료되어 조회 성능은 좋은 편으로 조회에는 문제가 없는 쿼리로 생각되었습니다.

 

 

 

 

 

현재 상태가 진행중인 게시글, 완료 상태인 게시글 조회

 

 

 

 

 

 

select * from posts p where p.is_completed = false and p.is_deleted = false order by p.created_at desc limit ?

이제 실행계획을 출력해보겠습니다.

 

 

 

 

인덱스 풀스캔을 하고 있고 idx_created_at 이라는 인덱스를 타고 있는걸 확인할 수 있습니다.

Posts 엔터티에는 생성일 내림차순으로 인덱스가 생성되어 있기 때문에 이걸 옵티마이저가 선택한 것 같습니다.

 

 

 

 

 

더 개선할 수 있는 부분이 없는지 수행 시간을 확인해보겠습니다.

 

 

 

 

실제 걸린 시간은 0.8초 정도 이대로는 사용하기 어렵습니다. 더미 데이터가 적은데도 이정도 시간이라면 실제 운영환경에서는 더 느려질 것이기 때문입니다.

 

개선할 수 있는 부분으로 인덱스 풀 스캔을 범위 스캔을 하도록 개선해볼 수 있다고 생각했습니다.

범위 스캔을 위해 인덱스를 수정 해보겠습니다.

 

 

 

 

 

기존 type이 index 던 것을 ref로 동등 비교를 통해 여러 non-unique 인덱스를 읽는 방식으로 개선할 수 있었습니다.

 

 

 

 

실제 응답 속도도 0.8초대 이던 것을 0.09초대로 개선할 수 있었습니다.

 

 

 

 

다음으로 완료상태인 게시글 조회 쿼리에 대해서도 실행계획을 확인해 보겠습니다.

이 쿼리는 select * from posts p where p.id < 810000000000000029 and p.is_completed = true and p.is_deleted = false order by p.created_at desc limit 10;

 

로 무한스크롤을 위한 쿼리였습니다.

 

 

 

 

 

 

범위 스캔을 잘 타고 있고 key = primary로 클러스터링 인덱스를 활용했습니다.

다만 Using filesort 로 내부적으로 정렬을 수행하고 있으므로 이 부분을 병목지점으로 생각하고

제거해보기로 했습니다.

created_at 에서 생성일 순으로 정렬된 인덱스가 없기 때문에 내부적으로 filesort 가 일어난 것으로 보였고, 완료 상태인 게시글로 먼저 필터링을 해주는게 수 만건의 id로 필터링 하는 것 보다 효과적으로 쿼리할 수 있을 것이라고 생각했습니다.

따라서, 보조 인덱스를 is_completed를 선행 칼럼으로 is_completed, id, created_at 순서로 생성했습니다.

 

 

 

 

WHERE 절에 컬럼을 명시하더라도, ORDER BY 에서 컬럼의 순서가 잘못되거나 특정 컬럼이 누락되면 동일한 이유로 정렬에 인덱스를 사용할 수 없다.(WHERE 절에서는 당연히 컬럼 순서가 바뀌어도 무관하다.)

참고로, WHERE 절이 동등비교가 아닐 때에도 정렬 시 인덱스를 사용할 수 없으므로, 그럴 땐 차라리 ORDER BY 조건에 명시적으로 해당 칼럼을 넣어주는 것이 더 나을 수 있다.

 

위 내용을 기억하고 쿼리문을 수정해주었습니다.

 

// 이전 쿼리
explain select * from posts p where p.id < 810000000000000029 and p.is_completed = true and p.is_deleted = false order by p.created_at desc limit 10;
// 수정한 쿼리
explain select * from posts p where p.id < 810000000000000029 and p.is_completed = true and p.is_deleted = false order by p.id, p.created_at desc limit 10;

 

 

 

 

id값을

ORDER BY

에서 명시적으로 컬럼을 넣어줌으로 써 정렬된 인덱스를 사용할 수 있으므로

Using filesort

를 제거할 수 있었습니다.

 

 

 

 

수정 전 0.09초가 걸리던 쿼리를

 

 

쿼리 튜닝 후 0.06초 까지 단축할 수 있었습니다.

 

 

잠깐 범위 스캔에 대해 알아 보겠습니다.

 

RANGE가 나온 순간, 그 뒤 컬럼(c)은 ORDER BY에 절대 쓸 수 없습니다.

실무에서도 이것 때문에 정렬이 깨지고 filesort가 터지게됩니다.

 

즉,

✔ 동등 조건은 인덱스를 계속 이어서 탈 수 있음

✔ 범위 조건은 해당 컬럼까지만 인덱스를 사용하고 종료

✔ 그 뒤 컬럼은 인덱스 정렬 최적화 불가능

✔ ORDER BY는 RANGE가 나오기 전 컬럼까지만 인덱스로 정렬 가능

 

"넓은 범위"가 왜 정렬 최적화를 못하게 할까?

복합 인덱스는 이렇게 정렬되어 있습니다:

(A ASC, B ASC, C ASC)

즉:

• 먼저 A 기준으로 group
• 그 안에서 B 기준으로 정렬
• 그 안에서 C 기준으로 정렬

그런데 RANGE는 이렇게 넓습니다

B < 100

→ B 값 1,2,3, ..., 99 전부 포함

문제:

• 각 B 그룹 내부에만 C가 정렬됨
• B 전체를 통합하면 C 정렬 순서가 깨짐

즉:

B=1 → C 순서: 1,2,10
B=2 → C 순서: 3,4,8
B=3 → C 순서: 0,9,11
...

이 값들을 통째로 가져오면 C는 전혀 정렬되어 있지 않습니다.

그래서 MySQL은 이렇게 판단합니다:

“이건 ORDER BY C를 인덱스 정렬로 해결할 수 없네”

→ filesort 수행

실제 MySQL 내부 규칙

MySQL 공식 문서에 다음 규칙이 있습니다.

복합 인덱스에서 RANGE 조건이 등장하면, 그 컬럼 이후는 정렬 최적화(ORDER BY)를 할 수 없음.

즉:

• “스캔”(next pointer로 읽기)은 가능
• “정렬된 순서 유지”는 불가능

 

 

 

내가 댓글 단 게시글 조회

 

 

해당 쿼리는 게시글과 댓글을 조인하는 쿼리기 때문에 2가지 쿼리 시퀀스가 나왔는데, 게시글을 가져오는 쿼리는 PRIMARY 로 클러스터드 인덱스를 타서 성능에 문제가 없을것으로 판단했습니다.

문제는 인덱스를 전혀 타지 못하고 내부적으로 임시 테이블을 생성하고 filesort 를 수행하는 comment 쪽이 문제였습니다.

 

쿼리는 다음과 같았습니다.

select distinct p.* from posts p left join comments c on p.id = c.post_id where c.user_id = 773227704805082357 and p.is_deleted = false order by p.created_at desc;

 

 

 

 

 

수행 시간 또한 1.86초 라는 프로덕트에 사용할 수 없는 시간대가 걸렸습니다.

역시 조인이 되는 곳에는 인덱스를 활용해주는 것이 좋다는 것을 조인 쿼리를 분석할 때 마다 느낄 수 있습니다.

정렬 대상이 “JOIN 결과물”이기 때문에 거의 항상 filesort가 발생하고 있었습니다. 게다가 distinct 는 내부적으로 Using temporary 를 사용하게될 문제점도 있어서 가능한 distinct 키워드는 제거하고 쿼리를 수정하는 쪽으로 방향을 잡았습니다.

이 쿼리가 문제인 이유는 다음과 같았습니다.

comments를 where 절에서 user_id값으로 먼저 탐색하기 때문에 posts의 순서대로 정렬을 다시 거쳐야 했기 때문에 Using filesort 와 Using temporary 가 발생했던 것 이었습니다.

 

이를 개선하기위해

 

1. posts 먼저 읽기
2. 인덱스 (is_deleted, created_at DESC, id)로 스캔
3. 그 순서대로 comments 조인
4. 추가 정렬 없이 바로 리턴

 

 

최종적으로 수정한 쿼리는 다음과 같습니다.

post_id + isDeleted + userId 복합 인덱스를 추가해서 댓글 필터링 조건 조회 시 인덱스를 탈 수 있도록 수정했습니다.

select p.* from posts p left join comments c on p.id = c.post_id where c.user_id = 773227704805082357 and c.is_deleted = false and p.is_deleted = false order by p.created_at desc;

 

 

 

 

 

 

수행 시간도 이전 1.86초에서 0.05초로 수행 시간이 짧아진 것을 확인할 수 있습니다!

하지만, 이 쿼리는 조금 더 개선이 필요할 것 같습니다.

아직 filesort 를 진행하고 있는데 더 최적화 할 수 있는 부분에 대해 찾아봐야겠습니다.

 

글 읽어주셔서 감사합니다!