쿼리 개선기

삼성SW아카데미 11기에선 현재 3개의 프로젝트 중 첫번째 프로젝트인 공통프로젝트를 진행중입니다.

저희 팀은 도서 인프라 취약지역 사람들을 위한 도서 공유 서비스를 기획하고 개발중입니다.

메인 기능이기도 한 등록도서를 조회하는 기능에서 쿼리 성능이 저하되는 상황이 발생했습니다.

문제 해결 과정에 대한 이해를 돕기 위해 간단한 ERD를 그려보았습니다.

테이블에는 2백만건의 더미 데이터를 삽입하고 테스트를 진행했습니다.

 

간략한 ERD

---

문제의 쿼리 - Offset 페이징

offset 페이징 방식은 offset 사이즈를 건너뛰어 limit 사이즈만큼의 데이터를 가져오는 방식을 뜻합니다.

정확히 말하자면 offset 사이즈만큼을 건너뛰는 것이 아니라 offset 사이즈만큼 앞에서부터 읽어가며 필요한 데이터를 찾아가는 것입니다. 따라서 데이터의 크기가 많아지면 많아질 수록 쿼리의 성능은 저하됩니다.

offset 만큼 데이터를 읽는 것도 성능 저하의 이슈가 되지만, 더 큰 문제는 count 쿼리입니다.

현재의 페이지 정보를 나타내기 위해선 전체 데이터의 수를 알아야되고, 그러기 위해서는 count 쿼리로 테이블의 전체 데이터 수를 알아내야 합니다.

이 과정에서 userbook의 PK에 대한 인덱스 풀 스캔이 일어나며 성능 저하가 발생하게 됩니다.

select *
from userbook, users, book
where userbook.book_isbn = book.isbn
and userbook.user_id = users.id
and userbook.area_code = "5219039000"
order by userbook.id
limit 20
offset 300;

select count(*)
from userbook, users, book
where userbook.book_isbn = book.isbn
and userbook.user_id = users.id
and userbook.area_code = "5219039000"
order by userbook.id;

 

데이터베이스 2백만건이 있는 상황에서 offset 방식의 페이징 처리의 쿼리 실행시간 : 4.625s

---

개선하기 위한 쿼리 - NoOffset 페이징

NoOffset 페이징이란 말 그대로 offset을 사용하지 않는 페이징 처리 방식입니다.

offset 없이 페이징 처리를 어떻게 하나요 ?

조회한 리스트의 마지막 ID를 기준으로 다음에 가져와야할 페이지를 찾는 것이 NoOffset의 개념입니다.
예를 들어 사용자가 자신의 지역에 등록된 ID를 기준으로 정렬된 도서 중 49번 ID를 가진 도서를 읽었다면 다음 읽어야할 도서의 ID는 49번보다 큰 도서가 되겠죠.
따라서 NoOffset 은 데이터를 읽고 필요한 데이터를 찾는 과정없이 인덱스를 타고 원하는 데이터에 바로 접근하는 것이 가능해집니다.
또한 페이징 처리에 필요한 select count 쿼리가 불필요해지게 되는 것입니다.

 

NoOffset 방식으로 Offset 페이징과 동일한 데이터를 조회하는 쿼리입니다.

select *
from userbook, users, book
where userbook.book_isbn = book.isbn
and userbook.user_id = users.id
and userbook.area_code = "5219039000"
and userbook.id > 11624625
order by userbook.id
limit 20;

동일한 환경에서 NoOffset 페이징 방식의 쿼리 실행시간 : 0.328s

 

페이징 처리만 바꾸었을 뿐인데 약 10배의 성능 개선이되었음을 알 수 있습니다.

하지만 아직도 쿼리가 빠르다고 할 수 없는 속도입니다.

---

기존의 쿼리 실행 계획

기존 쿼리의 실행 계획

 

현재 쿼리의 문제점은 쿼리의 실행 계획을 통해 알 수 있었습니다.

바로 book 테이블에 대해 테이블 풀 스캔이 일어나고 있었습니다.

userbook의 실행계획을 보시면 filtered가 0.02가 나오는 것을 확인할 수 있습니다.

하지만 book 테이블에선 인덱스 적용이 되지 않아 테이블 풀 스캔이 일어났습니다.

따라서 Nested Loop Join에서 연산해야할 양이 많아지기에 쿼리의 속도가 느릴 수 밖에 없었습니다.

 

book 테이블에 인덱스 적용이 안되었던 이유

💡 기존에 생성된 인덱스는 다음과 같습니다.

create index idx_userbook_book_user_areacode on userbook(book_isbn, user_id, area_code);

테이블 내 카디널리티가 높은 순서대로 인덱스를 생성했습니다.
book_isbn 컬럼은 조인 컬럼으로만 사용되고, 추가적인 조건이 없으므로 이 컬럼에 대한 인덱스는 쿼리 성능에 큰 기여를 하지 않습니다. 결과적으로, book 테이블이 풀 스캔되어 조인하게 됩니다.
단일 인덱스의 경우에는 카니털리티가 높은 순서대로 인덱스를 생성하는 것이 효율적이지만 복합 인덱스의 경우에는 인덱스 컬럼은 앞의 컬럼에 의존적이게 됩니다.
따라서 쿼리에서 중요한 조건 중 하나인 area_code를 기준으로 인덱스를 생성하는 것이 Nested Loop Join의 연산양을 줄일 수 있는 방법입니다.

 

---

개선

컬럼들의 순서를 조정하여 인덱스를 다시 생성했습니다.

create index idx_userbook_areacode_bookisbn_users_userbookid on userbook (area_code, book_isbn, user_id, id);

 

area_code 컬럼이 첫 번째로 위치하여, 해당 조건에 맞는 레코드 검색에 인덱스가 사용되었습니다.

그 결과, 테이블 풀 스캔이 발생하지 않고, 필요한 레코드만 효율적으로 검색할 수 있었습니다.

기존 4.625초가 소요되던 작업을 페이징 처리 변경 및 인덱스 최적화를 통해 0.113초로 단축하여,

약 40 배 성능이 개선되었습니다.

느낀 점

글로만 배운 인덱스의 단점이었던 거 같습니다. 카디널리티가 높은 것이 인덱스가 되어야 효율이 높다라고 들었었고, 복합 인덱스에도 똑같이 적용한 것이 성능 저하를 일으킨 원인이었습니다.

이번 쿼리 개선을 통해 인덱스와 조인 연산에 대해 깊이 있는 이해를 가질 수 있는 시간이었습니다.

---

참고자료

• 1. 페이징 성능 개선하기 - No Offset 사용하기
• [DB] 데이터베이스 NESTED LOOPS JOIN (중첩 루프 조인)에 대하여