서론
현재 프로젝트가 끝나고 리팩토링을 하고 있다.
쿼리에 인덱스가 하나도 적용되지 않은 상태라 쿼리 튜닝을 하기로 했다.
많은 쿼리중에 사용 비중이 높은 쿼리들을 정리해봤고 순차적으로 인덱스를 적용해보기로 했다. 물론 하나의 인덱스가 두개 이상의 쿼리에서 쓰일 수 있는 방향도 고려해서 적용하기로 했다!
그 중에서 이번에 인덱스 적용을 해보게 된 쿼리는 join + where이 적용된 쿼리이다.
이번 포스팅에서 인덱스 적용 과정과 적용 전 후의 실행계획, 성능 차이를 분석해보고자 한다!
프로젝트 배경(테이블, 쿼리)
쿼리
쿼리를 먼저 소개하자면 다음과 같다.
select u.user_id, u.user_nickname, u.user_age_group, u.user_profile_imamge_url, p.participant_is_leader, u.user_manner_score
from participant_table as p
join user_table as u
on p.participant_user_id = u.user_id
where p.participant_room_id = ?;테이블
이 쿼리에서 쓰이는 테이블은 두개로 다음과 같다.
- Participant_Table
create table participant_table
(
participant_is_leader bit null,
created_at datetime(6) null,
participant_id bigint auto_increment primary key,
participant_last_exit datetime(6) null,
participant_room_id bigint null,
participant_user_id bigint null,
updated_at datetime(6) null
);- User_Table
create table user_table
(
user_birth int null,
user_is_joined bit null,
user_manner_score double null,
user_role tinyint null,
user_signal int null,
user_id bigint auto_increment primary key,
user_email varchar(255) null,
user_line varchar(255) null,
user_name varchar(255) null,
user_nickname varchar(255) null,
user_profile_imamge_url varchar(255) null,
user_provider varchar(255) null,
user_provider_id varchar(255) null,
user_station varchar(255) null,
user_age_group enum ('UNDER_CHILDREN', 'CHILDREN', 'TEENAGER', 'TWENTY', 'THIRTY', 'FORTY', 'FIFTY', 'SIXTY', 'SEVENTY', 'EIGHTY', 'NINETY') null,
user_gender enum ('MALE', 'FEMALE', 'UNION') null,
check (`user_role` between 0 and 0)
);TMI로 설명을 덧붙이자면 특정 User가 Room에 참가하게 되면 Participant 데이터가 생긴다.
User는 여러 Room에 참가할 수 있기 때문에 그 특성을 고려하여 설계하기로 했다.
즉, User 데이터 수가 Participant 데이터 수보다 적다.
(애초에 테이블 설계도 User와 Participant가 일대다 관계로 설계했다.)
인덱스 적용
테스트 데이터는 Participant 테이블에 100만건, User 테이블에 10만건을 넣어놓았다.
첫번째 실행 계획(INDEX ❌)
이 쿼리는 join과 where절을 통해 조회하는 쿼리이다.
순차적으로 본다면 두 테이블의 join을 먼저 한 후 거기서 where조건을 통해 필터링 과정을 거친다.
인덱스를 적용해보기 전, 실행계획을 먼저 살펴보자
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | p | null | ALL | null | null | null | null | 1004280 | 10 | Using where |
| 1 | SIMPLE | u | null | eq_ref | PRIMARY | PRIMARY | 8 | board_signal.p.participant_user_id | 1 | 100 | null |
실행계획을 통해 중요한 것들에 대해 쿼리를 분석해보자.
- participant 테이블의 type이 ALL로 드라이빙 테이블이다.
- join on절에 user테이블에만 인덱스가 있어 user테이블이 드리븐 테이블이 됐다.
- 해당 쿼리에서 participant 테이블에 대한 인덱스는 없으므로 ref는 null이다.
- rows 값을 보면 풀 테이블 스캔이 진행되었다.
- where 조건을 통해 데이터의 약 10%가 필터링이 되었다.
- 즉, 90%의 데이터는 불필요하게 읽은 데이터다. (스토리지 엔진에서 가져온 데이터에 대해 10%만 반환되었다.)
- user 테이블은 조인 시에 PK 인덱스를 탔다.
이 실행계획을 통해 우린 participant 테이블에 대한 인덱스가 필요함을 알 수 있다.
우선, 쿼리를 다시 보고 결과값과 실행시간을 파악하자.
select u.user_id, u.user_nickname, u.user_age_group, u.user_profile_imamge_url, p.participant_is_leader, u.user_manner_score
from participant_table as p
join user_table as u
on p.participant_user_id = u.user_id
where p.participant_room_id = 13;(execution : 334ms, fetching : 19ms)의 결과를 얻었다.
두번째 실행 계획(조인 컬럼 INDEX)
이제 순차적으로 생각하여 인덱스를 적용해보자면 join시 on 절에 대한 인덱스를 생각해볼 수 있다.
우선 그 부분에 대해서만 인덱스를 적용해보고 실행 계획을 살펴보자.
CREATE INDEX idx_participant_user_id ON participant_table (participant_user_id);실행계획을 보기 전 생각해보자면 이제 조인 칼럼 두개 모두에 인덱스가 생성되어있다.
드라이빙 테이블이 되는 기준을 생각해보면 인덱스가 두개 모두에 존재할 경우 테이블의 데이터가 적은 쪽이 드라이빙 테이블이 된다. 데이터가 적은 쪽을 먼저 액세스 해야 탐색 횟수가 적기 때문이다‼️
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | u | null | ALL | PRIMARY | null | null | null | 144599 | 100 | null |
| 1 | SIMPLE | p | null | ref | idx_participant_user_id | idx_participant_user_id | 9 | board_signal.u.user_id | 1 | 10 | Using where |
바로 이전의 실행계획과 뭐가 달라졌는 지 비교해보자.
- 드라이빙 테이블이 user 테이블로 바뀌었다.
- 인덱스가 조인 칼럼에 모두 있어 데이터가 적은쪽이 선택되었다.
- user 컬럼에 대한 조건은 없기에 user 테이블을 풀스캔하였다.
- participant_user_id에 인덱스가 생성되어 적용이 되었다.
- where 조건에 있는 컬럼에는 인덱스가 적용되지 않아 필터링 비율이 여전히 그대로다.
이번엔 (execution : 161ms, fetching : 14ms)의 결과를 얻었다.
실행 시간 자체만 보자면 조인 컬럼에 인덱스를 적용하기 전과 훨씬 빨라졌다.
하지만 실행계획을 보니 더 개선할 수 있는 부분이 존재한다.
where 조건을 통해 필터링이 된 결과는 아직도 불필요한 90%를 추가적으로 더 읽어오고 있다.
where 조건에 있는 컬럼에도 인덱스를 적용시켜 디스크 I/O를 줄여보자.
세번째 실행계획(조인컬럼 INDEX, WHERE 조건 INDEX)
이제 인덱스를 추가해보자. 참고로 복합 컬럼 인덱스는 적용할 수 없다.
select로 여러 컬럼 값을 가져오기 때문에 join을 먼저 실행하고 where 조건을 거치기 때문에 복합 컬럼 인덱스 적용 시, where 조건에 있는 컬럼은 인덱스를 활용하지 못할 것 이다.
이제 where 조건에 있는 컬럼에 대해 인덱스를 추가 생성 후 실행계획을 이전과 비교해보자.
CREATE INDEX idx_participant_room_id ON participant_table (participant_room_id);| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | p | null | ref | idx_participant_room_id,idx_participant_user_id | idx_participant_room_id | 9 | const | 27 | 100 | Using where |
| 1 | SIMPLE | u | null | eq_ref | PRIMARY | PRIMARY | 8 | board_signal.p.participant_user_id | 1 | 100 | null |
- 드라이빙 테이블이 다시 participant가 되었다.
- where 조건 컬럼에도 인덱스가 적용 되었다.
- 즉, 불필요한 레코드를 미리 필터링 할 수 있고 옵티마이저는 이를 알고있다.
- 필터링이 되면 user_table의 데이터수보다 더 적은 데이터셋이 추출되어 participant가 드라이빙 테이블이 된다.
- 사용된 인덱스 키를 보면 participant_room_id 인덱스만 사용되었다‼️
- 즉, 해당 인덱스로도 충분히 결과를 최적화할 수 있기에 옵티마이저가 user_id에 대한 인덱스는 사용하지 않은 것이다.
- rows가 27로 이전과 현저히 줄었다.
- 필터링이 100이 되었다. 불필요한 데이터를 읽어오지 않는다.
- 인덱스를 통해 미리 필터링이 가능했다.
결과는 (execution : 5ms, fetching : 11ms) 이다.
실행시간이 인덱스를 아예 적용해보기 전보다 압도적으로 줄어든 것을 알 수 있다!
하지만 짚고 넘어가야할 부분이 있는데 실행계획에서 알 수 있듯이 participant_user_id에 대한 인덱스는 사용되지 않았다. 즉, 해당 인덱스는 이 쿼리에서 필요가 없는 것이다. 그러므로 불필요 인덱스는 제거해주자.
DROP INDEX idx_participant_user_id ON participant_table ;쿼리를 개선해보았으니 api 부하 테스트를 통해 어느 정도의 성능 개선이 이루어졌는 지 테스트를 해봐야 한다
성능 테스트
테스트 환경
- 테스트 툴 : JMeter
- 8CPU
- Memory : 16GB
- 동시 요청 스레드 : 1000
- 루프 카운트 : 5
인덱스 적용 전
인덱스를 하나도 적용하지 않았을 때의 테스트 결과다.
28TPS 로 아쉬운 성능이다.😓
인덱스 적용 후
적용 후에는 약 280TPS 로 10배 가까이 개선이 되었다!
결론
현재 프로젝트에 있는 쿼리(join + where)에 대한 성능 튜닝을 해보았다.
해당 쿼리는 자주 쓰이는 쿼리라 의미 있는 개선 경험이었다😃
또한 테스트 전에는 두 컬럼에 대한 인덱스가 모두 생성되어야 한다고 생각했지만 실제로는 WHERE 조건에 있는 컬럼에 대한 인덱스만 최적화하는데 사용되었다.
생각 없이 그냥 인덱스 적용에 대해서만 생각했다면 불필요한 인덱스를 둘 뻔 했다.
인덱스 적용을 하면서 결론만 보자면 간단한 적용일 수 있지만 인덱스 생성을 하면서 어떤 변화가 이루어지고 어떤 원리로 성능이 개선되는 지 파악해보는 것이 중요하다고 생각한다. 단순히 인덱스 적용에만 초점을 두면 쿼리가 점점 더 복잡해지면 인덱스를 어떻게 활용해야할 지 전혀 감이 안 잡힐수도 있고 적용하더라도 적용 전보다 더 비효율적인 설계를 할 수도 있다. 그렇기에 실행계획을 분석하고 옵티마이저가 어떻게 작동을 하는 지 살펴보는 것은 필수적이라고 생각이 들었다.
