[Database] 인덱스를 통한 쿼리 성능 개선기

개요

프로젝트가 끝나고 팀원과 함께 개선할 쿼리를 추렸다. 최근에 인덱스에 대해 공부하고 있기도 하고 개념적으로만 알고 있던 인덱스를 직접 추가하여 쿼리 성능을 개선 해보았다.

 

 

테이블

쿼리에 사용된 테이블은 다음과 같다

 

room

create table room_table (
        room_id   		  	 bigint not null,
        room_meeting_info_id     	 bigint,
        room_title  		  	 varchar(255),
        room_description  		 varchar(255),
        room_image_url  		 varchar(255),
        room_head_count  		 integer,
        room_min_age  		  	 integer,
        room_max_age  		  	 integer,
        room_min_participants            integer,
        room_max_participants	         integer,
        room_start_time  		 varchar(255),
        room_place_name  		 varchar(255),
        room_subway_line  		 varchar(255),
        room_subway_station  	         varchar(255),
        room_allowed_gender  	         enum ('UNKNOWN','MALE','FEMALE','UNION'),
        room_day_slot  		  	 enum ('WEEKDAY','WEEKEND'),
        room_status  		  	 enum ('NON_FIX','FIX'),
        room_time_slot  		 enum ('AM','PM'),
        created_at  		  	 datetime(6),
        updated_at  		  	 datetime(6)
    )

모임에 대한 정보가 들어가있다.

 

meeting_info

create table meeting_info_table (
        meeting_info_id  		  	bigint 		 not null,
        meeting_info_meeting_time 		datetime(6),
        meeting_info_people_count 		integer,
        meeting_info_line 			varchar(255),
        meeting_info_meeting_place 		varchar(255),
        meeting_info_station 			varchar(255),
        created_at 				datetime(6),
        updated_at 				datetime(6)
    )

모임이 확정되었을 때의 정보이며, room과 1:1 관계이다.

 

participant

create table participant_table (
        participant_id 	            	bigint not null,
        participant_room_id 		bigint,
        participant_user_id 		bigint,
        participant_is_leader 		bit,
        participant_last_exit 		datetime(6),
        created_at 			datetime(6),
        updated_at 			datetime(6)
    )

모임에 참여한 참여자의 정보이다. 여기에 따로 표시하지 않은 user테이블과 room은 M:N 관계이며, 그 중간 테이블이라 보면 된다.

 

모임이 약 100만개 정도 있고, 모임 참여자는 500만명, 그 중 확정된 모임은 10만개로 잡아 데이터를 삽입하였다.

room - 약 100만개

participant - 약 500만개

meeting_info - 약 10만개

테스트는 로컬 서버에서 이루어졌다.

 

 

쿼리 1.

select *
from room_table r
join meeting_info_table m
	on m.meeting_info_id=r.room_meeting_info_id
join participant_table p
	on r.room_id=p.participant_room_id
where p.participant_user_id=243121
	and m.meeting_info_meeting_time<'2023-12-31 00:00:00'
order by m.meeting_info_meeting_time desc;

튜닝할 첫 번째 쿼리는 다음과 같다.

'내가 어제까지 참여한 모임'을 조회하는 쿼리이며, 파라미터로 user id와, 오늘의 날짜가 삽입될 것이다.

 

 

실행 계획 분석

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	p	null	ALL	null	null	null	null	4579578	10	Using where; Using temporary; Using filesort
1	SIMPLE	r	null	eq_ref	PRIMARY	PRIMARY	8	p.participant_room_id	1	100	Using where
1	SIMPLE	m	null	eq_ref	PRIMARY	PRIMARY	8	r.room_meeting_info_id	1	33.33	Using where

(execution : 약 2 s, fetching : 약 50 ms)

 

옵티마이저는 인덱스가 걸려있지 않은 테이블이 있을 경우, 해당 테이블을 드라이빙 테이블로 잡는다.

실행 계획을 보면, room과 meeting_info 테이블은 pk 인덱스를 이용하지만, participant 테이블은 사용할 수 없는 인덱스가 없기에, 드라이빙 테이블로 잡은 후 500만개의 데이터를 풀 스캔 때린다.

 

-> Sort: m.meeting_info_meeting_time DESC  (actual time=4676..4676 rows=3 loops=1)
    -> Stream results  (cost=1.47e+6 rows=152637) (actual time=369..4675 rows=3 loops=1)
        -> Nested loop inner join  (cost=1.47e+6 rows=152637) (actual time=369..4674 rows=3 loops=1)
            -> Nested loop inner join  (cost=969615 rows=457958) (actual time=368..4673 rows=5 loops=1)
                -> Filter: ((p.participant_user_id = 243121) and (p.participant_room_id is not null))  (cost=466240 rows=457958) (actual time=366..4667 rows=12 loops=1)
                    ★-> Table scan on p  (cost=466240 rows=4.58e+6) (actual time=9.31..4443 rows=5e+6 loops=1)
                -> Filter: (r.room_meeting_info_id is not null)  (cost=0.999 rows=1) (actual time=0.526..0.526 rows=0.417 loops=12)
                    -> Single-row index lookup on r using PRIMARY (room_id=p.participant_room_id)  (cost=0.999 rows=1) (actual time=0.524..0.524 rows=1 loops=12)
            -> Filter: (m.meeting_info_meeting_time < TIMESTAMP'2023-12-31 00:00:00')  (cost=0.993 rows=0.333) (actual time=0.295..0.295 rows=0.6 loops=5)
                -> Single-row index lookup on m using PRIMARY (meeting_info_id=r.room_meeting_info_id)  (cost=0.993 rows=1) (actual time=0.292..0.292 rows=1 loops=5)

participant 테이블에 대한 실행 분석은 다음과 같다.

1. participant 테이블을 풀 스캔 하며,
2. p.participant_user_id = 243121 조건을 통해서 필터링하며,
3. 동시에 participant_room_id가 null이 아닌 것을 골라내어 드라이빙 테이블로 사용한다.

 

결론 - participant 테이블의 user_id와 room_id에 대한 복합 컬럼 인덱스 생성이 필요하다!

 

 

인덱스 생성

create index participant_table_index on participant_table (participant_user_id, participant_room_id);

인덱스 생성 후, 다시 한번 실행 계획을 분석해보자

 

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	p	null	ref	participant_table_index	participant_table_index	9	const	12	100	Using index condition; Using temporary; Using filesort
1	SIMPLE	r	null	eq_ref	PRIMARY	PRIMARY	8	p.participant_room_id	1	100	Using where
1	SIMPLE	m	null	eq_ref	PRIMARY	PRIMARY	8	r.room_meeting_info_id	1	33.33	Using where

(execution: 약 10 ms, fetching: 약 70 ms)

 

우리가 생성한 인덱스가 잘 사용된 것을 볼 수 있으며, 쿼리 실행 시간도 압도적으로 줄어든 것을 볼 수 있다.

 

-> Sort: m.meeting_info_meeting_time DESC  (actual time=15.1..15.1 rows=3 loops=1)
    -> Stream results  (cost=39.6 rows=4) (actual time=6.39..15 rows=3 loops=1)
        -> Nested loop inner join  (cost=39.6 rows=4) (actual time=6.37..15 rows=3 loops=1)
            -> Nested loop inner join  (cost=26.4 rows=12) (actual time=5.89..12.3 rows=5 loops=1)
                ★-> Index lookup on p using participant_table_index (participant_user_id=243121), with index condition: (p.participant_room_id is not null)  (cost=13.2 rows=12) (actual time=3.38..3.4 rows=12 loops=1)
                -> Filter: (r.room_meeting_info_id is not null)  (cost=1.01 rows=1) (actual time=0.739..0.739 rows=0.417 loops=12)
                    -> Single-row index lookup on r using PRIMARY (room_id=p.participant_room_id)  (cost=1.01 rows=1) (actual time=0.738..0.738 rows=1 loops=12)
            -> Filter: (m.meeting_info_meeting_time < TIMESTAMP'2023-12-31 00:00:00')  (cost=1 rows=0.333) (actual time=0.535..0.535 rows=0.6 loops=5)
                -> Single-row index lookup on m using PRIMARY (meeting_info_id=r.room_meeting_info_id)  (cost=1 rows=1) (actual time=0.532..0.532 rows=1 loops=5)

조건에 일치하는 데이터 12개만을 인덱스에서 갖고오는 것을 볼 수 있다.

 

 

추가적인 개선 고민

-> Sort: m.meeting_info_meeting_time DESC  (actual time=15.1..15.1 rows=3 loops=1)
    -> Stream results  (cost=39.6 rows=4) (actual time=6.39..15 rows=3 loops=1)
        -> Nested loop inner join  (cost=39.6 rows=4) (actual time=6.37..15 rows=3 loops=1)
            -> Nested loop inner join  (cost=26.4 rows=12) (actual time=5.89..12.3 rows=5 loops=1)
                -> Index lookup on p using participant_table_index (participant_user_id=243121), with index condition: (p.participant_room_id is not null)  (cost=13.2 rows=12) (actual time=3.38..3.4 rows=12 loops=1)
                ★-> Filter: (r.room_meeting_info_id is not null)  (cost=1.01 rows=1) (actual time=0.739..0.739 rows=0.417 loops=12)
                    ★-> Single-row index lookup on r using PRIMARY (room_id=p.participant_room_id)  (cost=1.01 rows=1) (actual time=0.738..0.738 rows=1 loops=12)
            ★-> Filter: (m.meeting_info_meeting_time < TIMESTAMP'2023-12-31 00:00:00')  (cost=1 rows=0.333) (actual time=0.535..0.535 rows=0.6 loops=5)
                ★-> Single-row index lookup on m using PRIMARY (meeting_info_id=r.room_meeting_info_id)  (cost=1 rows=1) (actual time=0.532..0.532 rows=1 loops=5)

위에서 봤던 실행 분석 중, room 테이블과 meeting_info 테이블을 필터링 하는 부분이다.

두 테이블 모두 pk 인덱스를 사용하여, pk 조건에 대해 필터링 후 추가적인 필터링을 한다.

room 테이블과 meeting_info테이블 각각 pk를 포함하는 복합 인덱스를 만들면 성능이 향상될지 궁금하여 인덱스를 추가 생성 후 실행해보았다.

 

 

추가 인덱스 생성

create index participant_table_index on participant_table (participant_user_id, participant_room_id);
create index meeting_info_table_index on meeting_info_table (meeting_info_id, meeting_info_meeting_time desc);

room 테이블과 meeting_info 테이블의 각각 pk를 포함한 복합 컬럼 인덱스를 생성하였다.

실행 계획(explain)은 큰 변화가 없었다.

 

-> Sort: m.meeting_info_meeting_time DESC  (actual time=0.42..0.42 rows=3 loops=1)
    -> Stream results  (cost=33.5 rows=12) (actual time=0.255..0.395 rows=3 loops=1)
        -> Nested loop inner join  (cost=33.5 rows=12) (actual time=0.247..0.379 rows=3 loops=1)
            -> Nested loop inner join  (cost=23.3 rows=12) (actual time=0.218..0.312 rows=5 loops=1)
                -> Index lookup on p using participant_table_index (participant_user_id=243121), with index condition: (p.participant_room_id is not null)  (cost=13.2 rows=12) (actual time=0.179..0.181 rows=12 loops=1)
                ★-> Index lookup on r using room_table_index (room_id=p.participant_room_id), with index condition: (r.room_meeting_info_id is not null)  (cost=0.753 rows=1) (actual time=0.0105..0.0107 rows=0.417 loops=12)
            ★-> Index lookup on m using meeting_info_table_index (meeting_info_id=r.room_meeting_info_id), with index condition: (m.meeting_info_meeting_time < TIMESTAMP'2023-12-31 00:00:00')  (cost=0.549 rows=1) (actual time=0.0125..0.0129 rows=0.6 loops=5)

(execution: 약 9 ms, fetching: 약 100 ms)

 

실행 분석(explain analyze)은 변화가 있었는데, 쿼리에 있는 조건 필터링이 전부 인덱스에서 이루어졌다!

하지만 결과는 큰 변화 없었다..! 오히려 fetching 시간은 늘어났다

 

 

결과에 대한 생각

이미 participant 테이블 인덱스를 생성함으로써, where 조건에 부합하는 12개의 레코드를 드라이빙 테이블로 정리하였다.

드라이빙 테이블이 애초에 12개로 정리되었기에, 조인 테이블에 대한 인덱스를 걸어도 큰 효과가 없었던 것 같다.

 

또한 전부 인덱스를 통해 스캔하기에, 테이블 스캔을 통해 시퀀셜 액세스 했을 때보다 미세하게 fetching시간이 늘어난 것 같다..!

 

추가로 생성한 인덱스는 성능 개선은 미비하고, 공간만 추가적으로 차지한다고 판단하여 다시 삭제한다.

 

 

쿼리 2.

select r.room_id,
       r.room_title,
       r.room_image_url,
       r.room_head_count
from room_table r
join participant_table p
	on r.room_id = p.participant_room_id
left join meeting_info_table m
	on m.meeting_info_id = r.room_meeting_info_id
where p.participant_user_id = 243121
	and (m.meeting_info_meeting_time >= '2023-12-31 00:00:00' or m.meeting_info_meeting_time is null)
order by p.created_at desc;

튜닝할 두 번째 쿼리이며, '채팅방 목록'을 조회한다.

채팅방 목록 조회 기능인데 시간 조건이 걸린 이유는 오프라인 만남 시간이 지나면, 자동으로 채팅방을 삭제하기 위해서이다.

 

첫 번째 쿼리와 조인은 거의 동일하며, where절 조건도 거의 비슷한 것을 볼 수 있다.

 

 

실행 계획 분석

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	p	null	ref	participant_table_index	participant_table_index	9	const	12	100	Using index condition; Using filesort
1	SIMPLE	r	null	eq_ref	PRIMARY	PRIMARY	8	p.participant_room_id	1	100	Using where
1	SIMPLE	m	null	eq_ref	PRIMARY	PRIMARY	8	r.room_meeting_info_id	1	33.33	Using where

(execution : 약 11 ms, fetching : 약 110 ms)

 

조인 조건과 where절 조건이 동일하기에 첫 번째 쿼리에서 생성한 인덱스를 그대로 이용한다.

어부지리로 함께 튜닝이 되었다..ㅎㅎ

참고로 인덱스를 생성하지 않았을 때 실행 시간은 약 1 s 400 ms 였다.

 

 

추가 개선점

-> Filter: ((m.meeting_info_meeting_time >= TIMESTAMP'2023-12-31 00:00:00') or (m.meeting_info_meeting_time is null))  (cost=41.4 rows=12) (actual time=3.1..6.07 rows=9 loops=1)
    -> Nested loop left join  (cost=41.4 rows=12) (actual time=3.09..6.04 rows=12 loops=1)
        -> Nested loop inner join  (cost=26.4 rows=12) (actual time=3.09..4.97 rows=12 loops=1)
            ★-> Sort: p.created_at DESC  (cost=13.2 rows=12) (actual time=2.75..2.76 rows=12 loops=1)
                -> Index lookup on p using participant_table_index (participant_user_id=243121), with index condition: (p.participant_room_id is not null)  (cost=13.2 rows=12) (actual time=2.72..2.72 rows=12 loops=1)
            -> Single-row index lookup on r using PRIMARY (room_id=p.participant_room_id)  (cost=1.01 rows=1) (actual time=0.184..0.184 rows=1 loops=12)
        -> Single-row index lookup on m using PRIMARY (meeting_info_id=r.room_meeting_info_id)  (cost=1 rows=1) (actual time=0.0885..0.0885 rows=0.417 loops=12)

 

실행 계획과 실행 분석을 보면서 눈에 띈점은 participant 테이블의 created_at에 대해 소트 버퍼에서 정렬을 하고 있다는 것이다!

 

기왕 인덱스를 사용하고 있고, 인덱스의 정렬을 이용할 조건이 된다면 추가적인 메모리를 사용하지 않고 인덱스의 정렬을 이용하면 좋을 것 같다는 생각이 들어 인덱스 컬럼을 추가하기로 한다.

 

이 때 인덱스의 컬럼은 (user_id, created_at, room_id) 순으로 하였다.

(user_id, room_id, created_at) 순으로 인덱스를 생성하였을 때, room_id는 인덱스 컨디션 푸쉬다운 대상 컬럼으로, 그 뒤에 created_at의 정렬 순서가 의미가 없어지게 되어 소트 버퍼에서 한번 더 정렬하게 된다.

또한 인덱스를 바꾼다고 해서, 첫 번째 쿼리의 실행 계획에 영향이 가지 않는 것을 확인하였다.

 

 

인덱스 수정

create index participant_table_index on participant_table (participant_user_id, created_at desc, participant_room_id);

인덱스의 컬럼을 추가하였다.

 

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	p	null	ref	participant_table_index	participant_table_index	9	const	12	100	Using where; Using index
1	SIMPLE	r	null	eq_ref	PRIMARY	PRIMARY	8	p.participant_room_id	1	100	Using where
1	SIMPLE	m	null	eq_ref	PRIMARY	PRIMARY	8	r.room_meeting_info_id	1	33.33	Using where

(execution: 약 8 ms, fetching: 약 100 ms)

 

Using filesort가 없어진 것을 볼 수 있다.

 

 

정리

최근에 인덱스에 대해 공부하고, 스터디도 참여하게 되어 개념적으로 많이 접했찌만 그래도 참 낯설게 느껴졌다. 하지만 직접 해보면서 부딪혀보니 재미를 많이 느낀 주제여서 이것저것 해보며 공부가 정말 많이 되었다.

또한 인덱스를 하나 생성한 것 만으로 정말 엄청난 성능 향상이 있어서 신기하기도 하였다.

 

복잡한 서비스가 아니기에 어렵지는 않았지만, 다음에 더 복잡한 쿼리를 마주하게 되면 기대하는 마음으로 할 수 있을 것 같다.