인덱스를 통한 받은 편지 조회 개선하기

Boltter 서비스는 익명으로 편지를 주고받을 수 있는 서비스입니다. 작성된 편지는 여러 사람들에게 추천되기 때문에 하나의 편지는 많은 사용자에게 전송될 수 있습니다. 이러한 도메인 규칙에 의해 편지 작성 요청보다 편지를 조회하는 요청이 더 많다고 판단하여, 개발 기간이 종료되었지만 받은 편지 조회 성능을 개선해보고자 합니다.

실제 서비스에서는 사용자와 편지를 주고받는 데이터의 양이 적기 때문에 조회 요청에 대한 응답이 빠르게 이루어집니다. 따라서, 테스트를 위한 데이터를 삽입하여 편지 조회 성능을 개선해보도록 하겠습니다.

• 주고받은 내역이 저장된 테이블의 row는 약 380만 건으로 설정하였습니다.
• 다른 팀원이 개발한 파트라 기존 기능을 최대한 유지하며 기능을 개선하였습니다.

📌 기존 내용

기존에 받은 편지 조회 시 실행되는 쿼리를 확인하기 위해, show-sql=true 로 설정하여 쿼리를 확인하였습니다.

select lbe1_0.letter_id,
			case when (lbe1_0.letter_type=?) then le1_0.title 
					 when (lbe1_0.letter_type=?) then rle1_0.title
					 else 'Unknown Title' end,
			case when (lbe1_0.letter_type=?) then le1_0.label 
					 when (lbe1_0.letter_type=?) then rle1_0.label
					 else 'Unknown Label' end,
					 lbe1_0.letter_type,lbe1_0.box_type,lbe1_0.created_at 
from letter_box lbe1_0
left join letters le1_0 on lbe1_0.letter_id=le1_0.id and lbe1_0.letter_type=?
left join reply_letters rle1_0 on lbe1_0.letter_id=rle1_0.id and lbe1_0.letter_type=?
where lbe1_0.user_id=? and lbe1_0.box_type=?
order by lbe1_0.created_at desc
limit ?,?

// 페이지네이션을 위한 count
select lbe1_0.id from letter_box lbe1_0
where lbe1_0.user_id=? and lbe1_0.box_type=?

letter_box 테이블은 사용자별로 보낸/받은 편지 내역을 저장합니다. 따라서 사용자가 받은 편지를 조회하는 작업은 letter_box 테이블에서 특정 유저의 받은 편지를 가져와야 합니다. 우선 개선 없이 요청했을 때 소요되는 시간을 확인해보겠습니다.

받은 편지들을 조회하는 데에 1.02s가 소요됩니다. 추가로 Jmeter를 통해 초당 100회의 요청에 대한 테스트를 진행해보겠습니다.

최소 1초에서 최대 27초로 평균 14초나 소요되고 있습니다. 위 쿼리 explain을 사용하여 실행계획을 확인해보겠습니다.

explain
select lb.letter_id,
			case when (lb.letter_type='LETTER') then l.title 
					 when (lb.letter_type='REPLY_LETTER') then rl.title
					 else 'Unknown Title' end,
			case when (lb.letter_type='LETTER') then l.label 
					 when (lb.letter_type='REPLY_LETTER') then rl.label
					 else 'Unknown Label' end,
					 lb.letter_type,lb.box_type,lb.created_at 
from letter_box as lb
left join letters as l on lb.letter_id=l.id and lb.letter_type='LETTER'
left join reply_letters rl on lb.letter_id=rl.id and lb.letter_type='REPLY_LETTER'
where lb.user_id=1 and lb.box_type='RECEIVE'
order by lb.created_at desc
limit 0,10;

결과는 다음과 같습니다.

실행 계획을 보면 letters와 reply_letters 는 PK를 기반으로 효율적으로 조인을 수행하지만, letter_box 테이블에 대해 풀 테이블 스캔을 수행한 것을 확인할 수 있습니다. 그리고 전체 데이터 중, 3.33%가 조건에 부합할 것으로 예측하였습니다. 즉, 불필요한 읽기 작업이 상당히 많이 발생하게 됩니다.

📌 인덱스 적용하기

위 받은 편지 조회 요청의 where절은 다음과 같습니다.

where lb.user_id=1 and lb.box_type='RECEIVE'

즉, 조회를 요청한 유저가 주고받은 편지 중, 받은 편지에 대한 조회를 요청하였습니다. letter_box에 대해 조회 요청은 특정 사용자가 받은/보낸 편지입니다. 따라서 다중 컬럼 인덱스를 적용하고 user_id, box_type 순으로 인덱스를 적용하겠습니다.

CREATE INDEX idx_user_box_type ON letter_box (user_id, box_type);

위와 같은 순서로 인덱스를 적용한 이유는 특정 유저에 대한 주고받은 내역에 대해 먼저 접근하는 것이 효율적이기 때문입니다. 인덱스에서 다중 컬럼 인덱스는 n번째 인덱스는 n-1번째 인덱스에 의존하여 정렬됩니다. 설정한 인덱스를 예를 들면, box_type은 user_id에 의존하여 정렬되어 있고 user_id가 똑같은 레코드에서만 의미가 있습니다. 따라서 다중 컬럼 인덱스 내에서 컬럼의 위치는 상당히 중요합니다.

인덱스를 적용한 후의 실행계획은 다음과 같습니다.

그렇다면 이후의 받은 편지 조회 요청에 소요되는 시간은 어떨까요?

letter_box 테이블에 대해 탐색하는 row 수가 상당히 많이 줄었습니다. 실제 조건에 부합하는 row는 193건이고 불필요한 읽기 작업이 상당히 많았음을 나타냅니다.

다음은 Jmeter를 통한 초 당 100회 요청에 대한 테스트 결과입니다.

이전과 비교해 상당한 성능 차이가 있었음을 알 수 있습니다. Postman을 통한 API 응답 시간은 1.02s -> 0.016s까지 감소하였습니다. Jmeter를 통한 테스트 결과는 상당한 차이가 발생했습니다.

테스트를 위한 삽입한 데이터 양이 상당히 많았지만, 이를 감안하더라도 조회에 있어서 큰 성능 차이가 발생했습니다. 그렇다면 인덱스를 설정하는 것이 무조건 좋은걸까요?

DBMS에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능입니다. 즉, 인덱스를 추가하는 작업은 저장 속도를 어디까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빠르게 만들어야 하느냐에 따라 결정해야 합니다. SELECT 문장의 WHERE 조건에 사용되는 컬럼이라고 전부 인덱스로 생성하면 저장 성능이 떨어지고 인덱스 크기가 비대해져 역효과를 일으킬 수 있습니다. 따라서 이러한 부분을 고려하여 인덱스를 도입해야 합니다.