[MySQL] 순위 조회 쿼리 성능 개선하기 (인덱스, 실행계획)

안녕하세요. 오늘은 순위 조회 쿼리를 개선한 이야기를 해보겠습니다.

 

초기 순위 페이지 조회 쿼리

---

 

그림1. 순위 페이지 (출처: https://sir.kr/g5_plugin/1353)

많은 서비스에서 이런 순위 페이지를 제공하고 있습니다. 어떻게 구현할 수 있을까요? (포인트는 전부 다르다고 가정하겠습니다.)

간단하게 데이터베이스의 SELECT 문과 정렬 기능을 이용해 구현할 수 있습니다.

 

쿼리는 다음과 같습니다.

SELECT nickname, point FROM member ORDER BY point DESC;

 

만약 데이터가 많아지면 어떻게 될까요? 1,000만 건의 데이터를 준비하고 실행해봤습니다. 4.807초가 소요됐습니다.

그림2. 순위 조회 쿼리 실행 결과

순위 페이지를 조회하는데 5.643초가 소요된다면 사용자가 얼마나 불편할까요? 데이터가 더 많아지면 더 오래 걸릴 것입니다. 이 문제를 해결하기 위해 페이징, No Offset 구조, 인덱스를 적용해보겠습니다. 글의 모든 예제는 MySQL로 작성하겠습니다.

 

1. 페이징 적용하기

---

기존의 방식은 1,000만 건의 데이터를 모두 조회합니다. 이렇게 많은 데이터는 묶음 단위로 끊어서 보여주는 것이 성능적으로나 시각적으로더욱 좋은 방식입니다. 게임 사이트의 랭킹 페이지, 커뮤니티 사이트의 게시물 목록, 은행 거래 내역 등 대부분의 서비스에서 데이터는 페이징 방식으로 제공됩니다.

그림3. 페이징 방식 (출처: https://jojoldu.tistory.com/528)

 

MySQL의 OFFSET, LIMIT 키워드를 활용하여 페이징 방식의 조회 기능을 구현할 수 있습니다. 쿼리는 다음과 같습니다.

SELECT id, nickname, point FROM member ORDER BY point DESC LIMIT XXX OFFSET YYY;

 

XXXX번째 레코드부터 YYY개의 데이터를 조회하는 쿼리입니다. 예를 들어, XXX = 100, YYY = 1000 이라면 1000번째 레코드부터 100개의 데이터를 조회하겠다는 의미입니다.

쿼리를 실행해보겠습니다. 실행 결과 3.166초가 소요됐습니다. 여전히 느린 것 같습니다.

그림4. 순위 조회 쿼리 "페이징 방식" 실행 결과

 

실행계획을 분석해봤습니다.

-> Limit/Offset: 100/1000 row(s)  (cost=998739 rows=100) (actual time=1954..1954 rows=100 loops=1)
    -> Sort: `member`.`point` DESC, limit input to 1100 row(s) per chunk  (cost=998739 rows=9.74e+6) (actual time=1954..1954 rows=1100 loops=1)
        -> Table scan on member  (cost=998739 rows=9.74e+6) (actual time=0.347..1432 rows=10e+6 loops=1)

 

-> Table scan on member (... rows=10e+6) 구문에서 전체 데이터(1,000만개)를 스캔하고 있음을 알 수 있었습니다.

현재의 쿼리는 다음과 같이 동작합니다. 예를 들어 OFFSET = 9,999,993, LIMIT = 8 일 때의 동작을 살펴보겠습니다.

 

1. 데이터 전체를 스캔합니다.

그림5. OFFSET 방식 - 데이터 스캔

 

2. 스캔한 데이터를 정렬합니다.

그림6. OFFSET 방식 - 데이터 정렬

 

3. OFFSET(=9,999,993) 번째 데이터부터 LIMIT(=8)개의 데이터를 추출합니다.

그림7. OFFSET 방식 - 데이터 추출

 

단지 LIMIT(=8)개의 데이터가 필요함에도 불구하고, 전체 데이터를 스캔했습니다. ORDER BY point 때문입니다. point 컬럼을 기준으로 내림차순된 상태에서 LIMIT 갯수의 데이터를 추출해야되다 보니, 정렬을 위해 전체 데이터가 필요한 것입니다. 만약 데이터가 항상 point 컬럼을 기준으로 정렬되있으면 전체 데이터를 스캔할 필요가 없을 것입니다. 이를 가능하게 해줄 인덱스를 적용해보겠습니다.

 

2. 인덱스 적용하기

---

인덱스란 책의 맨 끝에 있는 찾아보기(또는 "색인")와 비슷한 개념입니다. 책의 마지막에 있는 "찾아보기"가 인덱스에 비유된다면 책의 내용은 데이터 파일에 해당한다고 볼 수 있습니다. 책의 찾아보기를 통해 알아낼 수 있는 페이지 번호는 데이터 파일에 저장된 레코드의 주소에 비유될 것입니다.

 

DBMS 인덱스 특징 중 하나는 원하는 내용을 빠르게 찾도록 도와준다는 것입니다. 인덱스를 이분탐색에 활용하여 데이터를 찾기 때문입니다. 그리고 책의 "찾아보기"와 DBMS 인덱스의 공통점 가운데 중요한 것이 바로 정렬입니다. 아래 그림은 인덱스(=friends_name_asc)에서 Name 컬럼이 Zack인 데이터를 찾는 과정을 보여줍니다. Name을 기준으로 정렬된 것을 확인할 수 있습니다.

그림8. 인덱스 이분탐색 (출처: https://dataschool.com/sql-optimization/how-indexing-works/)

 

장점만 존재하는 것은 아닙니다. 인덱스가 많은 테이블은 INSERT, UPDATE, DELETE 문장의 처리가 느려집니다. 원본 테이블뿐만 아니라 인덱스에도 변경사항을 반영해야하기 때문입니다. 결론적으로 DBMS에서 인덱스는 데이터 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능입니다.

 

MySQL에서는 CREATE INDEX 키워드를 사용하여 인덱스를 생성할 수 있습니다.

CREATE INDEX point_desc_index ON member(point DESC);

 

point 컬럼을 인덱스 키로 설정하였습니다. 따라서 인덱스의 리프노드는 point 컬럼을 기준으로 내림차순 정렬됩니다. 인덱스 자체가 이미 정렬되어 있기 때문에 별도의 정렬 작업 없이 읽기만 하면 됩니다.

그림9. 인덱스 - point 컬럼을 기준으로 내림차순 정렬된 모습

 

다시 한 번 쿼리를 실행해보겠습니다. 실행 결과 0.0057초가 소요됐습니다.

그림10. 순위 조회 쿼리 "인덱스 방식" 실행 결과

굉장히 빨라졌습니다. 실행계획을 확인해보면 예전처럼 전체 데이터를 스캔하지 않습니다. 1,100개의 행을 스캔하고, 그 중에서 100개의 데이터를 추려냅니다.

그림11. 순위 조회 쿼리 "인덱스 방식" 실행 계획

 

하지만 여전히 문제점이 남아있습니다. OFFSET 값을 크게 설정하고 실행해보겠습니다.

SELECT id, nickname, point FROM member ORDER BY point DESC LIMIT 100 OFFSET 3000000;

 

실행 결과, 4.681초가 소요됐습니다.

그림12. 순위 조회 쿼리 "인덱스 방식" OFFSET 높을 때 실행 결과

 

OFFSET 값이 1000일 때 비해 많이 느려진 모습입니다. 실행 계획을 분석해봤습니다.

-> Limit/Offset: 100/3000000 row(s)  (cost=998320 rows=100) (actual time=5447..5447 rows=100 loops=1)
    -> Sort: `member`.`point` DESC, limit input to 3000100 row(s) per chunk  (cost=998320 rows=9.74e+6) (actual time=5303..5393 rows=3e+6 loops=1)
        -> Table scan on member  (cost=998320 rows=9.74e+6) (actual time=0.274..1450 rows=10e+6 loops=1)

->Table scan on member (actual ... rows=10e+6) 에서 확인되듯, 전체 데이터를 풀 스캔하고 있습니다.

 

OFFSET 값을 크게 했을 뿐인데 왜 그럴까요?

 

바로 OFFSET 때문입니다. OFFSET 은 처음부터 모든 행을 읽도록 만드는데요. 예를 들어, OFFSET 10000, LIMIT 100 이라 하면, 10,000개의 행을 읽고, 그 후로 100개를 더 읽게 됩니다. 최종적으로 10,200개의 행을 읽는 셈입니다. OFFSET 값이 커지다 못해 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않게 됩니다.

일반적인 DBMS의 옵티마이저에서는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업으로 예측하기 때문입니다. 대략적인 이유는 풀 테이블 스캔은 순차 I/O를 사용하고, 인덱스 스캔은 랜덤 I/O 작업이기 때문입니다.

• 랜덤 I/O: 읽을 데이터 마다 디스크 헤더를 움직이는 방식
• 순차 I/O: 읽을 데이터들이 연속적으로 위치해있어 디스크 헤더를 한 번만 움직이면서 모든 데이터를 읽는 방식

OFFSET 값이 3,000,000 으로 인덱스를 통해 읽어야 할 레코드의 건수가 전체 테이블 레코드(=10,000,000개)의 20~25%를 넘어섰습니다. 따라서 인덱스를 이용하지 않고, 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하게 됩니다. OFFSET 방식을 WHERE 방식으로 변경하면 테이블 풀 스캔을 방지할 수 있습니다. WHERE 방식을 알아보겠습니다.

 

3. No Offset 방식 적용하기

---

No Offset 방식(WHERE 방식)을 그림으로 나타내면 다음과 같습니다.

그림13. No Offset 방식 원리

클라이언트와 서버는 다음 방식으로 동작합니다.

1. 클라이언트는 첫번째 순위 페이지를 조회합니다.
   • 서버는 인덱스에서 point 컬럼이 큰 순서대로 원하는 갯수만큼 읽어들이는 쿼리를 수행합니다.
2. 클라이언트가 두번째 혹은 그 이상의 순위 페이지를 조회합니다.
   • 클라이언트는 이전에 읽어들인 데이터 중 가장 낮은 point 컬럼 값을 알고 있을 겁니다. (위 그림에선 980)
   • 클라이언트는 새로운 페이지를 조회할 때 해당 값(=980)을 서버에게 전달합니다.
   • 서버는 해당 값보다 point 컬럼이 낮은 레코드를 원하는 개수(=10개)만큼 추려냅니다.

쿼리는 다음과 같습니다.

SELECT id, nickname, point FROM member WHERE point < YYY ORDER BY point DESC LIMIT XXX;

 

실행 결과, 0.0019초가 소요됐습니다.

그림14. 순위 조회 쿼리 "No Offset 방식" 실행 결과

 

No Offset 방식일 때(그림12. 4.681 소요)에 비해 대략 2,500배 가량 빨라졌습니다. 실행 계획을 분석해봤습니다.

-> Limit: 100 row(s)  (cost=950548 rows=100) (actual time=0.0832..0.449 rows=100 loops=1)
    -> Index range scan on member using point_desc_index over (7000101 < point < NULL), with index condition: (`member`.`point` < 7000101)  (cost=950548 rows=4.63e+6) (actual time=0.0815..0.432 rows=100 loops=1)

 

테이블 풀스캔을 하지 않습니다. point 컬럼이 700,101 보다 낮은 인덱스를 100개만 추려냅니다.

동작 방식을 그림으로 표현하면 다음과 같습니다.

그림15. 인덱스 스캔 동작 과정

 

이와 같이 시작해야할 위치를 찾고, 해당 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는 것을 인덱스 스캔(index scan)이라고 합니다. 인덱스 스캔의 결과는 레코드가 아닌 인덱스입니다. 읽어들인 인덱스의 프라이머리키를 이용해 최종적으로 레코드를 읽어 옵니다.

 

이번 글에서는 페이징 방식 ~ No Offset 방식을 차례대로 적용해보며 OFFSET, 인덱스의 동작과정을 살펴봤습니다. 틀린 점이 있다면 댓글 남겨주시면 감사하겠습니다.

 

참고 자료

---

• Real MySQL 8.0 (백은빈, 이성욱 지음)
• https://jojoldu.tistory.com/528
• https://www.youtube.com/watch?v=bqkcoSm_rCs
• https://www.youtube.com/watch?v=H_u28u0usjA
• https://www.youtube.com/watch?v=liPSnc6Wzfk