taekwon-dev/mysql8-index

MySQL Index를 통한 조회 성능 개선기

MySQL (InnoDB) Index를 활용한 쿼리 성능 개선기

인턴에서 진행한 프로젝트 중 데이터가 100만 건 이상 쌓인 상황에서 데이터베이스 조회 성능이 서비스 이용에 차질이 있을 정도로 느려지는 것을 발견한 후, 조회 성능을 개선하기 희망하여 진행한 프로젝트입니다. 500만 건의 데이터를 구축한 뒤, 학습 내용을 기반으로 생성한 인덱스를 통해 조회 성능이 얼마나 개선 되는지 테스트를 진행했습니다.

Overview

• [ 1 ] 인덱스 적용 상황에 대한 소개

  • 게시글 리스트 조회 (무한 스크롤 방식의 Pagination 적용)

• [ 2 ] 인덱스 VS 테이블 풀 스캔 비교

  • 인덱스와 테이블 풀 스캔 방식 성능 비교를 통해 문제 상황에서 인덱스가 가지는 효용이 큰 지 검증
  • 읽어 들이는 데이터의 양이 많은 경우 인덱스의 효용이 점차 줄거나 오히려 테이블 풀 스캔 방식이 더 효율적이라는 것을 확인
  • Pagination이 적용되어 있어 페이지 단위로 20~50개의 데이터를 읽으므로 인덱스를 통한 성능 개선이 충분히 가능할 것으로 예상

• [ 3 ] OFFSET 방식의 Pagination과 인덱스의 상관 관계

  • Pagination 구현 방식 중 하나인 OFFSET의 경우, 페이지 넘버가 커질수록 불필요하게 많은 양의 데이터를 읽어 들이는 오버헤드가 있음
  • 인덱스를 통해 범위 탐색, 정렬 등 조회 시 이점이 있음에도 위 오버헤드로 인해 인덱스의 효과가 일관적이지 않는 문제를 가짐 (최소 0.07초, 최대 8.88초)

• [ 4 ] NO-OFFSET 방식의 Pagination과 인덱스의 상관관계

  • OFFSET과 달리, 페이지 넘버와 관계 없이 인덱스가 가지는 효과가 일관적임
  • 추가적으로 ICP(Index Condition Pushdown)를 통해 인덱스를 활용한 조회 과정에서 발생하는 불필요한 작업들을 생략 가능
  • 따라서 인덱스 적용 상황에서는 NO-OFFSET 방식의 Pagination이 적절하다고 판단
  • 500만 건 데이터 셋에서 최대 4.83초 소요되는 것을 평균 0.01초로 단축

• 인덱스 테스트 상세 내용 링크

  • MySQL Index를 통한 쿼리 성능 개선기

Quickstart

$ docker-compose up -d --build : 빌드 및 실행 명령어 (100만 건, 500만 건 데이터 셋 최초 구축 시 약 2~3시간 소요)

$ docker-compose up -d : 실행 명령어 

$ docker-compose down : 중지 명령어 

$ docker-compose down -v : 중지 명령어 + 볼륨 삭제

❗ 볼륨을 통해 MySQL 컨테이너 종료 시에도 영속성을 유지하고 있는데, `-v` 옵션을 통해 종료하게 되면 볼륨 삭제가 되므로 필요 시에만 적용

아래 분석 내용에서 다루는 테스트 환경은 위 명령어를 통해 구축할 수 있습니다.

이미지를 빌드하고 정상적으로 실행이 된다면 아래 과정을 통해 실행 상태의 MySQL 컨테이너에 접근해 MySQL Client를 통해 인덱스 생성 및 아래 내용에서 등장하는 여러 쿼리를 직접 테스트할 수 있습니다.

$ docker exec -it mysql_for_index /bin/bash 

$ mysql -u root -p (password : `password`)

$ use youn; (database 선택) 

$ show tables; (post_1m, post_5m 테이블 확인 가능)

DB 인덱스를 통한 쿼리 성능 개선기

| 인덱스 적용 상황

[ 그림 1 - 인덱스 적용 상황 ]

[ 그림 1 ] 은 Int’ly 서비스의 피드 조회 페이지 중 하나다. 해당 서비스는 총 12 개의 포스트 카테고리가 있는데, 각 카테고리 별로 피드를 조회한다. 또한 피드 조회 시 무한 스크롤(infinit scroll) 방식의 페이징을 활용한다.

[ 그림 2 ] - POST Table Description

테스트 상황에서는 실제 서비스의 포스트 도메인을 그대로 활용 하지는 않았고, post 테이블은 [ 그림 2] 와 같이 구성했다. 스토리지 엔진은 InnoDB 스토리지 엔진을 사용했고, 결과적으로 인덱스는 category + created_at desc 두 개의 컬럼을 조합해서 추가했다. 지금부터 어떤 과정을 거쳐서 위와 같은 복합 인덱스(Composite Index)를 추가하게 됐는지에 대한 과정을 다룰 예정이다.

---

| 인덱스 테스트 1 : 인덱스 VS 테이블 풀 스캔

첫 번째 테스트에서 인덱스와 테이블 풀 스캔 조회 방식의 비교를 통해 얻고 싶은 것은 어떤 상황에서 인덱스를 사용해야 효율적인가 에 대한 답이었다.

테스트 데이터는 우선 100개의 레코드 기준으로, 12개의 카테고리를 랜덤 방식으로 생성 했고 해당 세트를 각각 만 번, 5만 번 반복해서 총 100만 건, 500만 건 데이터 셋을 구축했다.

[ 그림 3 ]

테스트 쿼리에 사용된 카테고리 ARTS 는 100만 건 데이터 셋에서는 10만 개, 500만 건 데이터 셋에서는 50만 개가 있는 상황이다.

[ 그림 4 ]

그리고 각각의 데이터 셋을 기준으로 인덱스가 유무에 따라 [ 그림 3 ] 쿼리를 실행한 결과가 [ 그림 4 ] 이다. 위 결과 표를 통해 알 수 있는 것은 크게 두 가지다. 먼저 인덱스를 활용했을 때가 활용하지 않았을 때보다 조회 시간이 줄어든다는 것이다. 다만, 데이터 셋이 각각 10만, 50만 개로 적지 않은 데이터를 조회하는 경우에는 성능 자체가 기대한 것 만큼 성능이 급격히 좋아지진 않는다는 것 역시 알 수 있었다. 특히 500만 건의 데이터에서 50만 건의 데이터를 조회하는 경우 100만 건 데이터 셋에서 볼 수 있는 성능 효과보다 더 낮은 효과를 본 것을 알 수 있다.

그렇다면, 왜 이런 결과가 나왔을까?

[ 그림 5 ] - 인덱스 VS 테이블 풀스캔

인덱스를 사용했음에도 기대한 효과가 나지 않은 것은 디스크 I/O 관점에서 분석할 수 있다. [ 그림 5 ] 에서 볼 수 있듯이 테이블 풀 스캔 방식은 멀티 블록 I/O 를 사용하고, 인덱스는 단일 블록 I/O 를 사용한다.

[ 그림 6 ] - 테이블 스페이스

데이터베이스에 저장되는 데이터는 [ 그림 6 ] 과 같이 계층적으로 구조화 되어 있는데, 데이터를 읽어들일 때는 블록 단위로 조회한다. 대량의 데이터를 읽어들일 때는 순차적 + 멀티 블록 단위로 데이터를 읽는다. 반면 인덱스는 랜덤 접근 방식 + 단일 블록 단위로 데이터를 읽어들인다. 이러한 정보를 통해 알 수 있는 것은, 조회할 데이터의 양이 많으면 많을 수록 테이블 풀 스캔 방식보다 인덱스 방식이 더 많은 디스크 I/O 요청을 할 수 있다는 점이다. 이 경우 디스크 I/O 가 많이 일어나 쿼리 수행 시간이 오래 걸려 결과적으로 성능이 나빠진다.

[ 그림 7 ] - I/O 요청 시 프로세스 상태

실행 중인 프로세스에서 디스크 I/O (= 입출력 요청)이 있으면, 해당 프로세스는 대기 상태가 되고 입출력 관리자가 해당 요청을 모두 처리하고 완료 신호인 인터럽트를 일으키면 입출력 요청을 했던 프로세스는 다시 준비 큐에 들어가 실행 상태가 되기를 다시 기다린다. 이후 다시 실행 상태가 되면 다음 작업을 수행하게 된다. 이러한 방식으로 처리가 되므로 디스크 I/O 가 빈번히 일어나면 성능이 나빠지게 된다.

이번 테스트를 통해 읽어들이는 데이터의 양이 많은 경우 인덱스 활용이 능사가 아님을 직접 눈으로 확인할 수 있었다. 하지만 내가 인덱스를 적용할 상황에서는 페이징을 통해 소량의 데이터를 조회 하므로 인덱스의 효과를 누릴 수 있을 것을 기대할 수 있었다. 어떤 결과가 있었는지 다음 테스트를 통해 확인해보자.

---

| 인덱스 테스트 2 - 페이징 (OFFSET vs NO OFFSET)

인덱스 테스트 1 - 인덱스 vs 테이블 풀 스캔 을 통해서 많은 데이터를 읽어 들일 때 인덱스를 통해 얻을 수 있는 효용이 크지 않다는 것을 알 수 있었다. 그렇다면, 상대적으로 소량의 데이터를 읽어들이는 페이징의 경우의 인덱스 활용은 어땠을까?

테스트 데이터 셋은 위와 동일하게 진행했고, 500만 건의 데이터 셋을 활용했다. 페이징을 처리 시 OFFSET 을 활용하는 방식과 그렇지 않은 NO OFFSET 방식 두 가지 상황을 비교하여 테스트를 진행했다.

🔖 OFFSET 방식

[ 그림 8 ]

OFFSET 방식의 경우 [ 그림 8 ] 의 쿼리를 통해 테스트를 진행했다. 한 페이지 사이즈는 실제 프로젝트에서 사용한 20 값으로 고정했고, OFFSET 값은 각각 1000, 10000, 100000, 300000 으로 설정한 뒤 결과 값을 비교해봤다.

[ 그림 9 ] - OFFSET 방식 테스트 결과

테스트 결과는 [ 그림 9 ] 와 같이 나왔다. OFFSET 방식은 OFFSET 값에 따라 인덱스 사용 유무에 따른 성능이 극명하게 나뉘는 경향을 보였다. OFFSET 값이 상대적으로 적은 1000, 10000 의 경우 인덱스 사용 후 조회 성능이 월등히 좋아진 것을 확인할 수 있었는데, OFFSET 값이 커질 수록 인덱스 활용 시 얻는 효용이 줄어든 것 역시 동시에 확인할 수 있었다.

[ 그림 10 ] - OFFSET 의 데이터 조회 방식

[ 그림 9 ] 와 같은 결과가 도출된 것은 OFFSET 이 포함된 쿼리가 어떻게 데이터를 조회하는지를 기반으로 분석해볼 수 있다. (인덱스 없다고 가정하고) OFFSET 이 포함된 경우, [ 그림 10 ] 과 같이 먼저 OFFSET 값까지 데이터를 읽어들이고, 해당 값에 도달한 다음 LIMIT 사이즈만큼 데이터를 추가로 조회한 뒤 LIMIT 사이즈 만큼 조회한 대상만 결과 값으로 반환하고, OFFSET 값까지 도달하는 과정에서 읽은 데이터는 버린다. 따라서 OFFSET 값이 작은 경우에는 오버헤드가 적지만 OFFSET 값이 커질수록 불필요하게 읽어야 하는 데이터가 많아져 조회성능이 나빠진다.

OFFSET 값이 작은 경우 인덱스를 통한 조회 성능이 월등히 좋아진 것을 볼 수 있는데, 이는 인덱스 사용 유무에 따라 바뀌는 실행 계획 을 통해 분석할 수 있다.

[ 그림 11 - 인덱스 적용 X ]

[ 그림 12 - 인덱스 적용 O ]

[ 그림 11, 12 ] 는 실행 계획 결과 중 분석에 의미 있는 부분 위주로 잘라서 가져온 것으로 [ 그림 11 ] 은 인덱스 적용 전, [ 그림 12 ] 는 인덱스 적용 후의 실행 계획이다.

LIMIT 20 OFFSET 1000 또는 10000 조건에서 인덱스 활용 시 좋은 조회 성능이 나온 이유로 첫 번째는 rows 컬럼의 값을 들 수 있다. 실행 계획의 rows 컬럼은 쿼리 처리 과정에서 얼마나 많은 레코드를 읽고 비교할 지를 예측한 값이다. 인덱스 적용 전에는 데이터 셋 500만 건에 거의 육박하는 450만 건이 잡혀 있는데, 인덱스 적용 후에는 약 100만 건으로 줄어든 것을 볼 수 있다. 물론 rows 컬럼 값이 정확한 값이 도출된 것은 아니지만 인덱스 적용 후 확실히 적은 스캔 범위를 가진다는 것을 알 수 있다.

두번째로 Extra 컬럼의 상태를 통해서도 이유를 추정해볼 수 있다. 인덱스 사용 전에는 Using where, Using filesort, 이는 스토리지 엔진에서 읽어온 데이터를 대상으로 where 절 조건에 맞는 데이터를 필터링 하는 과정과 order by 절 의 정렬 조건을 위해 별도로 정렬 처리가 진행 됐음을 의미한다. 반면, 인덱스 적용 후에는 두 과정이 생략될 수 있는데 이는 B-TREE 기반의 인덱스 자료구조는 category -> created_at desc 순서대로 정렬이 되어 있고, 이를 기반으로 별도의 필터링 작업과 정렬 작업을 생략할 수 있다.

그렇다면, 인덱스를 통해 이러한 이점이 있음에도 OFFSET 값이 크면 인덱스 효용이 작아지는 이유는 어떻게 분석할 수 있을까? 이는 위에서 언급한 것 처럼 인덱스를 통해 스캔 범위가 줄고, 필터링과 정렬 작업이 생략될 수 있어도 결국 읽어야 하는 데이터 자체가 많아지기 때문이다. 다시 말해, 인덱스를 통해 오버헤드를 줄일 수 있음에도 읽어야 하는 데이터의 양이 많아지는 구조라면 인덱스의 효과가 크지 않다는 것이고, 사실 이 부분은 테스트 1 - 인덱스 vs 테이블 풀 스캔 에서도 확인한 내용과 같은 맥락이다.

---

🔖 NO OFFSET 방식

[ 그림 13 ]

NO OFFSET 방식의 경우 [ 그림 13 ] 의 쿼리를 통해 테스트를 진행했다. 한 페이지 사이즈는 실제 프로젝트에서 사용한 20 값으로 고정했고, 위 쿼리에서 ?에 해당하는 값은 마지막으로 조회한 포스트의 ID (= PK) 를 기준으로 각각 천, 만, 10만, 30만, 50만, 100만, 300만, 500만으로 각각 설정한 뒤 결과 값을 비교해봤다. 참고로 ?에 해당하는 값이 마지막으로 조회한 ID 인 이유는 피드를 조회할 때 최신 순서로 조회하는 상황을 가정했기 때문이다.

[ 그림 14 - NO OFFSET 방식 테스트 결과 ]

테스트 결과는 [ 그림 14 ] 와 같이 나왔다. NO OFFSET 방식은 category + created_at desc 인덱스 생성 유무에 따라서 비교한 것은 OFFSET 방식과 동일하지만, 인덱스 생성을 하지 않은 경우에도 클러스터링 인덱스(Clustering Index)가 활용되므로 결과적으로 두 경우 모두 인덱스를 사용하는 상황이다. 이는 이 글의 서두에 언급한 것과 같이 InnoDB 스토리지 엔진을 사용하고 있고, InnoDB 스토리지 엔진은 PK에 해당하는 컬럼을 기본적으로 인덱스로 지정하기 때문이다. 따라서 이번 테스트는 사실상 클러스터링 인덱스를 사용한 경우와 세컨더리 인덱스를 사용하는 경우를 비교하는 것과 같았다.

[ 1 ] 클러스터링 인덱스를 사용하는 경우는 마지막 조회 ID 값이 커질수록 성능이 좋지 않다.

(세컨더리 인덱스를 생성하지 않은 상태)

[ 2 ] 세컨더리 인덱스를 사용하는 경우는 마지막 조회 ID 값이 커져도 좋은 성능을 보인다.

(마지막 조회 ID 값이 작은 경우에는 세컨더리 인덱스가 있어도 클러스터링 인덱스를 사용한다)

테스트 결과를 정리하면 위와 같은데, 지금부터 어떤 이유로 위와 같은 결과가 나왔는지 알아보자.

[ 그림 15 ] - 클러스터링 인덱스가 활용된 경우 실행 계획

[ 그림 15 ] 는 세컨더리 인덱스를 따로 생성하지 않은 상태에서 클러스터링 인덱스가 활용된 실행 계획이다. type 컬럼에 표기된 range 는 인덱스 범위 탐색을 의미하는데, 클러스터링 인덱스의 경우 범위 탐색 처리가 매우 효율적이다. 이는 클러스터링 인덱스의 리프 노드는 연결 리스트를 통해 상호 연결되어 있고, 리프 노드에는 모든 컬럼이 저장되어 있다는 점에 기인한다. 이러한 특성에도 불구하고 마지막 조회 ID 값이 커질수록 성능이 점차 나빠진 이유는 뭘까?

[ 그림 16 - 1 ] - 마지막 조회 ID < 100,000 (실제 쿼리 조건에 맞는 데이터 수 : 10,000)

[ 그림 16 - 2 ] - 마지막 조회 ID < 500,000 (실제 쿼리 조건에 맞는 데이터 수 : 50,000)

[ 그림 16 - 3 ] - 마지막 조회 ID < 1,000,000 (실제 쿼리 조건에 맞는 데이터 수 : 100,000)

처음에는 10만, 50만, 100만 각각의 실행 계획에서 rows 컬럼에 주목했었다. rows 컬럼은 쿼리를 수행하기 위해 얼마나 많은 레코드를 읽고 비교해야 하는지를 예측한 값이다. 마지막 조회 ID 값이 커질수록 rows 컬럼 값이 마찬가지로 커졌기 때문에 더 많은 레코드를 읽어들이는 과정에서 디스크 I/O가 많이 일어나서 성능이 나빠진 것이 아닐까? 라는 생각을 했었다.

하지만 InnoDB 스토리지 엔진에서 지원하는 클러스터링 인덱스는 리프 노드가 데이터 페이지(블록)이고, 버퍼 풀(Buffer Pool)에 캐싱되어 있는 경우 실제 디스크 I/O가 일어나지 않다는 점에서 이 부분은 큰 상관이 없다고 생각이 들었다. 이후 주목한 점은 Extra 컬럼의 Using where와 Using filesort 였다.

[ 그림 17 - 1 ] - 카테고리, ID, 정렬 조건 → 3.73 sec 소요

[ 그림 17 - 2 ] - ID 조건 → 0.00 sec 소요

[ 그림 17 - 3 ] - 카테고리, ID 조건 → 0.00 sec 소요

[ 그림 17 - 4 ] - ID, 정렬 조건 → 2.94 sec 소요

매우 단순한 접근이지만 가장 직접적으로 어떤 요인으로 인해 성능에 부정적인 영향을 주었는지 바로 알 수 있었다. Using filesort, 다시 말해 포스트를 최순 순서로 정렬하는 과정에서 많은 오버헤드가 발생한 것이다. 그리고 이 지점에서 세컨더리 인덱스 활용 시 성능이 일관되게 좋을 수 있었는지에 대해서도 알 수 있었다. 인덱스 자료 구조 (B-TREE) 특성상 데이터를 정렬하는 특징이 있기 때문에 정렬 작업을 생략할 수 있기 때문이다. 정렬 작업이 생략 되었는지, 세컨더리 인덱스를 활용 했을 때의 실행 계획을 확인해보자.

[ 그림 18 ] - 세컨더리 인덱스 사용 쿼리 실행 계획

세컨더리 인덱스 활용 시 실행 계획을 보면 Using where, Using filesort 모두 표시되지 않은 것을 확인할 수 있다. 이는 스토리지 엔진을 통해 데이터를 읽고 별도로 MySQL 엔진에서 필터링 작업과 정렬 작업을 수행하지 않았다는 것을 의미한다. 그런데 Extra 컬럼에 Using Index Condition이 표시된 것 역시 확인할 수 있었다. 해당 표시는 인덱스 컨디션 푸시다운(Index Condition Pushdown)이 적용된 것을 의미했는데, 관련 내용은 아래와 같다.

Index Condition Pushdown (ICP) is an optimization for the case where MySQL retrieves rows from a table using an index. (→) ICP는 인덱스를 통해 테이블의 레코드를 조회할 때 사용하는 최적화 전략이다.

Without ICP, the storage engine traverses the index to locate rows in the base table and returns them to the MySQL server which evaluates the WHERE condition for the rows. (→) ICP를 사용하지 않는 경우, 스토리지 엔진은 인덱스를 통해 레코드에 접근해 이를 MySQL 서버에 전달하고 MySQL 서버는 전달 받은 데이터에 WHERE 절의 조건을 활용해 필터링 작업을 진행한다.

With ICP enabled, and if parts of the WHERE condition can be evaluated by using only columns from the index, the MySQL server pushes this part of the WHERE condition down to the storage engine. (→) 반면 ICP를 사용하는 경우, WHERE 절의 일부 조건이 인덱스를 활용해 필터링이 가능한 경우 MySQL 서버는 WHERE 절의 해당 조건을 스토리지 엔진에 전달한다.

The storage engine then evaluates the pushed index condition by using the index entry and only if this is satisfied is the row read from the table. (→) 스토리지 엔진은 MySQL 서버로부터 전달 받은 WHERE 절 조건을 활용함으로써 인덱스를 통해 테이블 레코드에 접근할 때 조건에 맞는 경우에만 접근하는 방식으로 처리한다.

ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine. (→) ICP는 위 과정을 통해 스토리지 엔진이 테이블 레코드에 접근하는 횟수와 MySQL 서버가 스토리지 엔진에 핸들러 API 콜을 요청하는 횟수를 줄일 수 있다.

위 내용의 원문은 MySQL 8.0 Reference 에서 ICP Optimization 에 대한 부분에서 가지고 왔다. 다시 테스트 상황으로 돌아가서, ICP가 적용될 수 있었던 배경을 정리해보자.

[ 그림 19 ]

category + created_at desc 두 컬럼이 조합된 인덱스는 세컨더리 인덱스로, InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스는 클러스터링 인덱스를 포함하고 있다. 따라서 [ 그림 19 ] 쿼리의 WHERE 조건에 해당하는 = category , id < ? 들은 모두 세컨더리 인덱스 컬럼을 통해 필터링이 가능하다고 볼 수 있다. 다시 말해 위에서 설명한 ICP가 일어나는 조건에 부합하고, 이러한 조건에 의해 실행 계획에 Using Index Condition 이 등장한 것이었다. 또한 MySQL 8.0 Reference 의 ICP 설명 중, InnoDB 테이블의 경우 세컨더리 인덱스를 사용한 경우에만 ICP 가 적용된다는 점이 있었다. 이 조건 역시 테스트 상황과 맞물리는 지점이다.

ICP에 대한 설명 중, ICP를 통해 스토리지 엔진이 테이블 레코드에 접근하는 횟수를 줄일 수 있다고 했는데 이는 어떤 의미를 가지는지도 한 번 생각해봤다. 세컨더리 인덱스를 활용하는 경우 리프 노드에 PK 값을 가지고 있고 이를 기준으로 클러스터링 인덱스를 통해 데이터 블록에 접근할 수 있다. 다시 말해 두 개의 인덱스 구조(B-TREE)를 모두 수직적 탐색을 했을 때 데이터 블록에 접근할 수 있다는 것이다. 이러한 구조에서 ICP가 적용되지 않으면, 스토리지 엔진은 세컨더리 인덱스의 리프 노드에 저장된 PK 값이 [ 그림 19 ] 쿼리 조건에 부합하지 않더라도 해당 PK를 기준으로 클러스터링 인덱스를 수직적 탐색을 해서 데이터 블록에 접근해서 읽은 뒤 MySQL 엔진에 읽은 데이터를 전달하게 된다. 이 경우 읽은 데이터 목록에는 쿼리 조건에 맞지 않은 데이터를 필터링 하는데 이를 MySQL 엔진이 수행한다. 따라서 이 경우 불필요한 데이터 블록에 접근하는 수가 많아지고, MySQL 엔진이 스토리지 엔진에 API 요청을 통해 필터링 작업 요청을 또 보내야 한다.

이와 반대로, ICP 최적화가 적용되면 세컨더리 인덱스의 리프 노드에 저장된 PK 값이 쿼리 조건에 부합하지 않으면 클러스터링 인덱스를 타지 않고 다음 PK 값을 판단하면 된다. 따라서 스토리지 엔진은 데이터 블록에 접근하는 횟수가 줄어들고, MySQL 엔진 역시 스토리지 엔진에 대한 API 요청 수를 줄일 수 있다는 점에서 효율적이다.

[ 1 ] 클러스터링 인덱스를 사용하는 경우는 마지막 조회 ID 값이 커질수록 성능이 좋지 않다.

(세컨더리 인덱스를 생성하지 않은 상태)

[ 2 ] 세컨더리 인덱스를 사용하는 경우는 마지막 조회 ID 값이 커져도 좋은 성능을 보인다.

(마지막 조회 ID 값이 작은 경우에는 세컨더리 인덱스가 있어도 클러스터링 인덱스를 사용한다)

따라서 NO OFFSET 방식에서 테스트 결과에 대한 분석을 최종 정리하면 다음과 같다. [ 1 ] 의 경우 마지막 조회 ID 값이 클수록 해당 조건에 부합하는 레코드 수가 많아지는데, 이를 정렬하는 과정에서 소요되는 시간으로 인해 성능이 좋지 않았다. 반면, 세컨더리 인덱스를 사용하는 경우에는 마지막 조회 ID 값에 관계 없이 일관적으로 좋은 성능을 보였는데 이는 세컨더리 인덱스를 통해 정렬 작업을 생략할 수 있을 뿐만 아니라 ICP를 통한 최적화를 통해 세컨더리 인덱스가 가지는 오버헤드를 줄일 수 있었다는 점에서 이유를 찾을 수 있었다.

---

| 관련 포스팅

• Index [1] — 인덱스 사용 배경, 인덱스와 디스크 I/O
• Index [2] — 인덱스 자료 구조 (Hash Table)
• Index [3] — 인덱스 자료 구조(B+Tree)
• Index [4] — Index(B+Tree) 특징 및 Index 사용 관련 고려사항

| Reference

• ️Real MySQL 8.0

• ️친절한 SQL 튜닝

• https://jojoldu.tistory.com/528?category=637935

• ️https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html