대규모 데이터에서 페이징 조회 쿼리 튜닝해보기 - 인덱스 사용법

최근 대규모 시스템 설계에 대한 스터디를 진행하면서, DB에서 데이터를 조회해올 때 어떤 방법들을 사용하여 효율적으로 튜닝할 수 있는지 학습하였고, 해당 내용을 포스팅니다. 이번 포스팅에서는 인덱스가 조회쿼리에 얼마나 큰 영향을 줄 수있는지 확인해보도록 합니다.

 

1. 실행 환경

저는 mysql:latest 버전을 사용하여 docker로 구동시켰습니다. 포스팅 기준 9.3.0-1 버전임으로 참고 부탁드립니다.

article 이라는 테이블을 생성하였고, 해당 테이블에는 사전에 1200만건의 가짜 데이터를 삽입해두었습니다.

CREATE TABLE `article` (
  `article_id` bigint NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` varchar(3000) NOT NULL,
  `board_id` bigint NOT NULL,
  `writer_id` bigint NOT NULL,
  `created_at` datetime NOT NULL,
  `modified_at` datetime NOT NULL,
  PRIMARY KEY (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

count 조회만 했음에도 2.06초 이상이 걸리는것을 아래와 같이 확인할 수 있습니다.

 

---

2. 페이징 조회 쿼리 및 explain 확인

limit과 offset을 사용한 페이징 쿼리를 실행해봅니다.

select * from article where board_id=1 order by created_at desc limit 30 offset 90;

1200만건이라는 크지 않은 규모의 데이터임에도 페이징 조회 쿼리 한번에 3.4초 이상이 걸림을 확인할 수 있습니다.

똑같은 조회쿼리 앞에 explain 키워드를 입력하면 해당 쿼리의 실행계획을 확인할 수 있습니다.

explain select * from article where board_id=1 order by created_at desc limit 30 offset 90;

type이 ALL로  표기되어있는데, 이는 테이블 전체를 읽는 풀스캔이 이뤄진다는 뜻이고, Extra의 Using filesort는 데이터가 메모리에서 정렬을 수행할 수 없을 정도가 되기 때문에 파일디스크에서 데이터를 정렬함을 의미합니다. 때문에 단순한 조회임에도 3.4초라는 시간이 걸리는 것입니다.

 

---

3. Index 생성 후 페이징 조회 쿼리 비교

인덱스를 생성해봅니다. board_id와 article_id를 기준으로 생성합니다.

create index indx_board_id_article_id on article(board_id asc, article_id desc);

기존에 1200만건이라는 데이터를 삽입해두었기 때문에 단순한 인덱스 생성에도 13.8초라는 시간이 걸립니다. 현업에서 운영하는 테이블에 인덱스를 추가 및 수정할때는 서비스 무중단 플랜을 잘 세워둔 후 작업하는 것을 권장합니다.

 

이제 explain 명령을 통해 인덱스 생성 전과 같은 쿼리플랜을 출력해봅니다. order by만 article_id로 바꿔줍니다.

explain select * from article where board_id=1 order by article_id desc limit 30 offset 90;

type이 ref로 바뀌었고, possible_keys와 key 값에 우리가 추가한 인덱스가 보이는 것이 확인됩니다.

같은 내용의 쿼리가 기존 인덱스 적용 전에 3.4초에서 0.1초로 3.3초 이상이 줄어든 것을 확인할 수 있습니다.

 

---

4. offset 기준 페이지네이션 조회쿼리의 단점 파악 - 인덱스에 대한 이해

이제 offset을 1499970으로 주고 조회를 다시 시작해봅니다.

select * from article where board_id=1 order by article_id desc limit 30 offset 1499970;

아래처럼 쿼리 실행 시간이 1.9초가 넘어가는 것을 확인할 수 있습니다. 여기서 인덱스에 대한 이해가 필요합니다.

 

Mysql의 기본 스토리지 엔진은 InnoDB

Mysql에서 사용하는 기본 스토리지 엔진은 InnoDB입니다. 해당 엔진은 테이블마다 Clustered Index를 자동으로 생성하게 되는데 이는 PrimaryKey를 기준으로 생성되기 때문에 Primary key를 이용한 조회는 자동으로 생성된 Clustered Index로 수행되는 것을 의미합니다.

 

때문에 별도로 인덱스 생성한 것이 아님에도 불구하고 PrimaryKey에 자동으로 생성된 Clustered Index가 사용된 것을 아래와 같이 확인할 수 있었습니다.

 

위에서 별도로 생성한 인덱스는 Secondary Index(보조 인덱스) 혹은 Non-Clustered Index라고 불리는데, Clustered Index와는 다르게 leaf node에 직접적인 데이터를 담고있는 것이 아니라, 인덱스 컬럼 데이터 및 데이터에 접근하기 위한 포인터를 가지게 됩니다.

Clustered Index와 Secondary Index를 한눈에 비교해봅니다.

	Clustered Index	Secondary Index
생성	테이블의 Primary Key로 자동생성	테이블의 컬럼으로 직접 생성
데이터	행 데이터(row data)	데이터에 접근하기 위한 포인터, 인덱스 컬럼 데이터
개수	테이블당 1개	테이블당 여러 개

 

결론적으로, Secondary Index를 이용한 데이터 조회는 Secondary Index에서 데이터에 접근하기 위한 포인터를 찾은 후, Clustered Index에서 데이터를 찾는 인덱스 트리를 두 번 타게되는 것입니다. 이 개념을 바탕으로 페이징 쿼리를 다시 살펴보겠습니다.

select * from article where board_id=1 order by article_id desc limit 30 offset 1499970;

board_id, article_id에 생성된 Secondary Index에서 article_id를 찾은 후, Clustered Index에서 article 데이터를 찾으며 offset 1499970을 만날 때 까지 반복하며 skip을 하고 limit 30개를 추출하는 내용입니다.

 

데이터는 offset 1499970부터 30개만 필요한데, 해당 offset을 만날 때 까지 데이터에 접근하고 있는 비효율적이고 무의마한 과정이 생긴다는 것 입니다.

 

저희가 만든 Secondary Index는 board_id와 article_id를 포함하고 있습니다. 그렇다면 Secondary Index에서 필요한 30건에 대해서 article_id만 먼저 추출하고 그 30건에 대해서만 Clustered Index에 접근하면 충분하지 않을까 라는 생각을 하게됩니다.

 

조회 컬럼의 지정 - Covering Index

Select 해오는 컬럼 자체를 board_id, article_id 2개만 추출하도록 바꿔서 쿼리를 수행해봅니다. 

select board_id,article_id from article where board_id=1 order by article_id desc limit 30 offset 1499970;

전체 데이터를 가져올 때는 약 1.n초 이상이 소요되었는데, board_id 및 article_id만 추출하는 것은 0.3초로 소요시간이 많이 줄어들었습니다.

explain을 사용해 쿼리 플랜을 살펴보면 인덱스는 동일하게 사용되었는데, Extra=Using index 내용이 추가되었음을 확인하게 됩니다. 인덱스만 사용해서 데이터를 조회했음을 의미하는데 이렇게 인덱스의 데이터만으로 조회를 수행할 수 있는 인덱스를 Covering Index라고 합니다.

 

Covering Index란 인덱스만으로 쿼리의 모든 데이터를 처리할 수 있는 인덱스로, Clustered Index를 읽지 않고 Secondary Index가 포함된 정보만으로 쿼리 사용이 가능하게 하는 인덱스를 뜻합니다. 용어가 너무 어렵게 느껴진다면 이렇게 생각하면 편합니다. 우리가 인덱스를 생성할 때 지정한 컬럼들이 Covering Index가 된다고 생각하면 말이죠.

 

이제 추출된 30건의 article_id에 대해서만 Clustered Index에 접근하게 쿼리를 수정하면 됩니다.

select * from (
 select article_id from article where board_id = 1 order by article_id desc limit 30 offset 1499970
) t left join article on t.article_id = article.article_id;

이렇게 원하는 offset까지 계속 스킵된 시간을 줄여서 2초가 넘어가던 쿼리를 다시한번 0.2초대로 단축시키는 것이 가능해집니다. 여기서 끝일까요???

 

---

5. 대용량 대이터 기준 마지막 페이지 조회

위에서 사용한 offset 1499970은 대략 5000번째 페이지를 뜻합니다. 하지만 이게 더 뒤로가서 30000번째 페이징이 되었을 경우는 또 어떨지 조회해봅니다.

select * from ( select article_id from article where board_id = 1 order by article_id desc limit 30 offset 8999970 ) t left join article on t.article_id = article.article_id;

또 다시 쿼리 수행 시간이 1초 이상이 넘어가게 됩니다. Covering Index를 가지고 Clustered Index에서 데이터를 가져오는건 똑같은데, 뒷 페이지로 갈 수록 속도가 느려지는 현상은 여전합니다. 왜 그럴까 생각해봅시다.

 

아무리 article_id (Covering Index) 추출을 위해 Secondary Index만 조회된다고 하더라도, offset 만큼 Index Scan이 필요하게 되는 것입니다.즉, Clustered Index의 데이터에 직접 접근하지 않아도 offset이 늘어날 수록 느려질 수 밖에 없는 상황인 것입니다.

 

이를 해결하기 위한 방법은 다양합니다. 게시글을 1년 단위로 테이블을 분리하여 개별 테이블의 크기를 작게 만들어 각 단위에 대해 전체 게시글 수를 관리하는 방법도 있고, offset을 인덱스 페이지 단위 skip하는 것이 아니라, 1년 동안 작성된 게시글 수 단위로 skip하는 여러가지 검색 조건을 더 넣을수도 있습니다.

 

혹은, 300,000 번 페이지를 조회하는건 일반 사용자가 아닌 데이터 수집을 목적으로 하는 크롤링 혹은 어뷰저일 수 있으니 서비스 운영 정책을 새우는 방법(게시글 목록 조회는 최근 기준으로 10,000번 페이지까지 제한하는 등) 도 있습니다.