[트러블 슈팅] ⚠️ 게시글 검색 및 페이지네이션 슬로우 쿼리

자유 게시판 프로젝트 진행 중 검색 기능을 구현한 뒤였다. 해당 프로젝트에는 더미데이터 약 2000천만건이 들어가 있었고, 게시글 첫 페이지 조회 시에만 해도 5초 이상이 걸리며 만약 맨 마지막 페이지의 컨텐츠를 확인하기 위해서 limit 20에 offset 20000000을 주게 될 경우 타임아웃이 발생하는 에러를 마주하게 되었다....

 

이는 MySQL의 물리 디스크에 접근하여 데이터를 찾는데 그만큼 오래 걸린다는 것인데 이를 해결하기 위해서는 인덱스 생성이 필요해 보였다.

---

문제 원인 파악

원인을 파악하기 위해서 EXPLAIN 명령어로 해당 쿼리를 분석.

확인 결과 type이 ALL인 것을 확인했다. 이는 쿼리가 인덱스를 사용하지 않고 풀 테입블 스캔을 하고 있음을 의미한다.
게다가 총 1987만건의 데이터에 접근하여 데이터 추출 작업을 하는 것이다. 이는 총 데이터 2천만건의 거의 모든 데이터를 훑어보고 데이터를 추출을 하게 되므로 느려지는 것.

 

DB의 성능 최적화를 위해서는 최대한 데이터 접근 수를 낮추고, 쿼리가 인덱스에서 데이터를 찾게끔 해야 물리 디스크로의 접근이 낮춰지면서 디스크 I/O 작업 비용도 낮아질 것이다.

 

해당 쿼리는 가장 최신 게시글 20개를 불러오는 작업을 수행하는 것인데, 결과적으로 인덱스가 없는 원본 테이블에서 가장 늦게 생성된 게시글을 불러오려고하는 것인데, 성능이 안좋을 수 밖에없다. 원본 테이블은 PK를 기준으로 오름차순 정렬이기 때문에 가장 최신의 게시글을 불러온다는 건 가장 늦게 생성된 PK를 뽑아오는 작업이다.

 

그러니 최신 게시글을 불러오기 위해서 거의 모든 데이터 1987만건의 데이터를 훑어보고 데이터를 찾아오는 것이 논리적으로 맞다.

거기에 추가적으로 Extra 항목을 보면 Using filesort를 볼 수 있는데 이 또한 데이터를 찾고 나서 데이터를 반환하기 위해 정렬 작업을 별도의 메모리나 디스크 공간을 사용해서 정렬 작업을 수행하고 있다.

---

해결방안

그럼 해결책으로 생성 날짜를 기준으로 내림차순의 인덱스를 생성하면 첫 페이지 검색 시에 상위 첫 20개를 바로 꺼내와서 가져올 수 있다. type 또한 ALL이 아닌 인덱스를 사용하게 함으로써 index가 사용 될 것이고 데이터 접근도 limit 20에 맞게 20이 될 것이다.

 

정렬 작업 또한 별도의 메모리나 디스크 공간을 사용하지 않을 것이다.(인덱스에서 찾은 순차대로 그대로 뽑아오면 될 것이기에)

인덱스를 created_at desc로 생성 한 뒤에 explain으로 실행 계획을 살펴본 사진이다.

실제로 type이 index가 되면서 더 이상 ALL이 아니라 인덱스 스캔을 통해서 데이터를 가져오는 것을 확인할 수 있다. 성능 또한 향상 되었다 원래 기존의 첫 페이지 검색 시에는 응답속도가 5s 41ms 이였는데, 353ms로 단축된걸 확인할 수 있다. 대략 성능이 14배정도 빨라진걸 확인할 수 있다.

생성 날짜를 내림차순으로 정렬한 인덱스를 생성하여 첫 페이지 조회 시 가장 최신의 게시글을 빠른 시간 내에 접할 수 있게 되었지만 문제점이 존재한다.

---

인덱스 생성 후의 문제점

만약 맨 마지막 페이지의 게시글을 확인해 보지 않았다. 인덱스 생성 전에는 맨 마지막 페이지를 조회하기 위해 offset을 2천만을 넣었을 때 타임아웃이 발생해 애초에 조회 조차도 해보지 못하게 되어있었다.

 

인덱스 생성 후에는 어떻게 나올까??

1분 40초... 2분에 근접한 시간이 걸리게 된다. 물론 장족의 발전이라고 볼 수 있다. 애초에 타임아웃이 나오는 쿼리였기 때문이다. 하지만 이 상태의 커뮤니티 서비스를 누가 이용할 것인가....

 

위와 같이 발생한 이유는 explain analyze명령어로 확인할 수 있다.

 

확인 결과 옵티마이저가 인덱스를 통해 offset을 처리하여 필터링을 먼저 수행하고 필터링의 결과로 row id값을 반환하게 되는데, 인덱스가 반환한 row id 값으로 원본 테이블에서 데이터를 조회하는 작업이 발생하여 늦게 처리가 되는 것이다.

 

created_at desc 인덱스를 통해서 row id를 찾는데, 사용자가 원하는 값은 생성 날짜가 아니다. 생성 날짜를 통해 최신 게시글을 보고싶은것. 즉 게시글 데이터를 필요로 하는 것이다.

 

created_at을 통해 row id를 찾았고 이를 통해 원본 테이블에 접근하여 Post에 대한 컬럼을 뽑아서 반환해야 되는 것이다.

 

인덱스 접근 -> 필터링 -> 원본 테이블 접근 -> 데이터 조회

 

위의 작업에서 데이터 조회 작업은 실제 MySQL의 물리 디스크에 접촉해서 Post에 대한 컬럼을 찾기 시작하는데 여기서 디스크 I/O 비용이 발생하면서 작업 속도가 오래 걸리는 것을 확인했다.

 

offset이 커짐에 따라 인덱스 트리를 순회하는 것은 어쩔 수 없지만 원본 테이블로의 접근을 막아 디스크 I/O 비용을 줄이기라도 해보자 라는 마음으로 커버링 인덱스를 생성하였다.

 

데이터 추출에 필요한 컬럼들은 post_id, title, categoryId, created_at 이였고, 인덱스의 정렬 구조는 항상 첫 번째 컬럼을 기준으로 정렬 되고, 해당 SELECT 쿼리의 ORDER BY가 created_at을 기준으로 하기 때문에 created_at 을 첫 번째 조건으로 멀티컬럼 인덱스를 생성.

커버링 인덱스 사용으로 인해 원본 테이블에 접근하여 데이터를 조회하는 과정을 건너뜀으로써 모든 작업은 디스크 접근 없이 인덱스 메모리 상에서 처리되고 이로 인한 성능 저하가 최소화 될 것이라 판단 하였다.

 

인덱스 생성 전에는 offset 2천만 조건으로 조회 시에는 타임 아웃이였다, 그 후 created_at desc로 인덱스 생성 후에는 14984ms 1분 40초 정도 걸리는 시간이다.

커버링 인덱스 생성 후의 offset 2천만은?

거의 2분걸리던 작업을 3초 내외로 응답을 받을 수 있었다!!

---

이처럼 쿼리를 튜닝하여 적절한 인덱스를 생성하게 되면 비약적으로 성능이 개서되는 사례를 볼 수 있다. 단점도 있으니 무분별한 인덱스 생성은 좋지 못하다. 해당 테이블을 사용하는 쿼리들을 보고 최적의 인덱스를 생성하는게 바람직하다. 무분별한 인덱스 생성은 오히려 CUD 작업의 성능을 저하시킨다.