이번에는 생애 처음으로 토이 프로젝트의 쿼리를 튜닝해보았습니다.
프로젝트에서 제일 많이 호출되는 쿼리를 튜닝했는데 그 경험을 적어보겠습니다.
쿼리 튜닝 환경
- M1 air
- RAM 16GB, SSD 512GB
- Docker 컨테이너 Mysql 8.x 버전
다대다 관계 튜닝하기
project라는 테이블과 member라는 테이블은 다대다 관계다.
중간 관계 테이블로 project_member라는 테이블이 존재한다.
테스트 데이터는 MySQL 프로시저를 활용해서 주입했으며 데이터 갯수는 아래와 같다.
- member: 200만
- project: 500만
- project_member: 1400만
먼저 쿼리를 살펴보자. 내가 참여한 모든 프로젝트를 조회하는 쿼리다.
디스코드 사이드 바에서 내가 참여한 모든 채팅방을 보여줄 때 사용하는 쿼리라고 보면 되겠다.
select
p.id,
p.title,
p.content
from member as m
join project_member as pm on m.id = pm.member_id
join project as p on p.id = pm.project_id
where m.id = ? and p.status = ? and pm.status = ? and m.status = ? ;해당 쿼리를 튜닝하지 않고 실행했을 때 5.9초가 걸렸다.
5.9초는 매우 긴 시간이라고 생각한다. 반드시 튜닝이 필요..!!
튜닝을 위해 explain 키워드를 사용해 실행 계획을 확인했다.
type이 all이라고 써 있는데 이건 모든 테이블의 데이터를 풀 스캔하는 것이다.
rows를 봐도 어마어마한 값이 적혀있는 것을 알 수 있다.
단순히 인덱스를 걸기 전에 최근에 튜닝 공부를 하고 어떻게 수정해야 하는지 생각해보았다.
현재 흐름
현재 모든 테이블은 pk에 대해 클러스터링 인덱스다.
- member 테이블에서는 pk인 id를 사용해 굉장히 빠르게 데이터를 찾는다.
- member 테이블의 id를 활용해 project_member에서 데이터를 찾아야 하는데, 정렬되어 있지 않다. 따라서 모든 테이블을 풀스캔하는 것이다. 여기가 문제다.
- project 테이블도 마찬가지로 pk인 id를 사용해 굉장히 빠르게 데이터를 찾는다.
이렇게 머릿속으로 스캔의 흐름을 그려보았다.
project_member 테이블에서 member_id를 가지고 정렬을 시켜야 한다고 생각했으므로 다음과 같이 인덱스를 추가했다.
CREATE INDEX pm_index ON project_member(member_id);그런 다음 이제 실행 계획을 확인해보았다.
pm_index를 사용하는 것을 확인할 수 있다.
그런 다음 시간 초를 재보았다.
SET @start_time = NOW(6);
select
p.id,
p.title,
p.content
from member as m
join project_member as pm on m.id = pm.member_id
join project as p on p.id = pm.project_id
where m.id = ? and p.status = ? and pm.status = ? and m.status = ? ;
SET @end_time = NOW(6);
SELECT TIMEDIFF(@end_time, @start_time) AS execution_time;
다음과 같이 시간이 0.1초로 줄어든 것을 확인할 수 있다.
이렇게 성공적으로 쿼리 튜닝을 진행했다.
쿼리 튜닝 예제가 아니라 본인 프로젝트의 쿼리를 튜닝을 진행한 것은 처음이다.
프로젝트의 더 많은 쿼리를 튜닝해봐야겠다.
