인덱스 만들었다고 안심하면 안되는 이유 | 페이징 쿼리 성능 개선

본 포스팅은 인프런 강의

스프링부트로 직접 만들면서 배우는 대규모 시스템 설계 - 게시판

섹션 2 수강  후 해당 내용을 정리하여 작성하였습니다. 

---

 

아래와 같이 게시물을 저장하는 article 테이블이 있다. 

article_id는 auto_increment로 생성된다고 가정한다.

 

아래 쿼리를 통해 특정 게시판의 최신 게시물 5개를 조회할 수 있다. 

# 쿼리1 : 특정게시판 최신 게시물 5개 조회 쿼리
SELECT * 
FROM article 
WHERE board_id = 1
ORDER BY article_id DESC  /* auto_increment로 생성하였기 때문에 created_at으로 정렬한거와 동일하다.  */
LIMIT 5;

 

조건문 및 정렬 성능을 개선하기 위해

board_id asc, article_id desc로 구성된

idx_board_id_article_id 인덱스를 생성한다.

---

게시물이 약 150만개 있는 데이터베이스에서

쿼리1을 실행하였을 때 

0.029초가 걸렸다. 

 

꽤 빠른 시간안에 실행이 되었다. 

그러면 offset을 1,500,000으로 설정하면 어떻게 될까? 

# 쿼리2: OFFSET이 매우 큰 쿼리1
SELECT * 
FROM article 
WHERE board_id = 1
ORDER BY article_id DESC 
LIMIT 5
OFFSET 1500000

 

약 11초가 걸렸다.

 

성능을 개선하기 위해 인덱스까지 추가하였는데

11초라니...! 너무 느리다

 

실행계획을 한번 살펴보자.

인덱스를 잘 타고 있는 것을 알 수 있다.

근데 왜이렇게 느린걸까? 

---

그 이유는 인덱스의 종류와 관련있다. 

인덱스는 Clustered Index와 Non-clustered Index로 나눌 수 있는데, 

Clustered Index는 말단노드에 모든 데이터를 가지고 있는 인덱스이고, 

Non-Clustered Index는 말단 노드에 인덱스 데이터만 가지고 있는 인덱스이다. 

 

Non-Clustered Index를 통해 접근할 때, 인덱스 컬럼 이외의 데이터가 필요한 경우

Clustered Index를 통해 해당 데이터를 조회하는 과정이 필요하다 

즉, 인덱스 트리를 두번 타게 된다. 

이 과정에서 시간이 오래걸리는 것이다. 

 

이 문제를 해결하기 위한 쿼리는 다음과 같다

# 쿼리3 : 성능개선한 쿼리2
SELECT * 
FROM (
	SELECT article_id
	FROM article
	WHERE board_id = 1
	ORDER BY article_id DESC
	LIMIT 5
	offset 1514000
) a
INNER JOIN article b ON a.article_id = b.article_id

 

idx_board_id_article_id 인덱스를 통해 article_id 목록을 조회한 후

article_id를 사용해 전체 데이터를 조회한다.

 

쿼리 3의 실행시간은 0.736초로 

쿼리2보다 약 93% 빨라졌다. 

---

인덱스가 있어도 인덱스 컬럼 이외의 데이터가 필요한 경우

서브쿼리에서 pk만 조회한 후 join하는 것이 속도 측면에서 더 빠를 수 있다.

---

번외로 쿼리2와 쿼리3의 cost를 비교해보았다. 

 

쿼리2의 cost는 약 10만, 쿼리3의 cost 는 약 70만으로

튜닝전의 쿼리가 7배 낮다...! 

 

이에 대해 챗지피티한테 물어보았다

 

나는 cost가 성능개선의 절대적인 지표인줄 알았는데

cost가 낮아도 속도가 오래걸릴 수 있다는걸 처음 알았다.