[DB] 페이징 조회 쿼리 성능 개선 - MySQL

* 개요

https://cyr0331.tistory.com/99

[DB] Index 실습(대용량 데이터 처리)

지난 게시글을 통해 Index 실습을 진행하였다.

인덱스를 생성함으로써 약 700만개의 데이터를 조회하는 시간을 17초에서 0.02초로 줄일 수 있었다.

그런데 과연 인덱스를 사용함으로써 문제는 전부 다 해결되었을까?

 

* 뒷 페이지 조회

문제가 전부 해결됐는지 확인하기 위해 이번엔 50000페이지를 조회해보도록 하겠다.

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

뒷 페이지를 조회하니, 3.14초나 소요가 된다.

쿼리 플랜을 확인해보자.

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

이전에 생성했던 인덱스가 잘 적용된 것을 확인할 수 있다. 그런데도 문제가 생긴다.

3초 정도가 대수냐고 할 수도 있지만, 실제 서비스에서는 3초의 시간도 사용자에게 불편하게 느껴질 것이다.

또한 50000페이지보다 더 뒷 페이지로 가면 갈 수록, 소요 시간은 더 늘어날 것이다.

어떻게 해결해야 할까? 인덱스에 대해 조금 더 깊게 알아보자.

 

* MySQL의 엔진 및 인덱스의 종류

MySQL의 기본 스토리지 엔진은 InnoDB이다.

- 스토리지 엔진 : DB의 데이터 저장 및 관리 장치

그리고, InnoDB는 테이블마다 Clustered Index를 자동 생성한다.

 

* MySQL의 Clustered Index란?

클러스터드 인덱스는 PK를 기준으로 정렬된 인덱스이다.

Primary Index(주 인덱스)라고도 불리고, 일반적으로 PK에 생성된다.

클러스터드 인덱스는 leaf node의 값으로 행 데이터(row data)를 가진다.

article 테이블에는 article_id(PK)를 기준으로 하는 클러스터드 인덱스가 생성되어 있고, 데이터를 가진다.

PK를 통한 조회는 자동으로 생성된 클러스터드 인덱스로 수행되는 것이다.

 

 

* MySQL의 Clustered Index를 이용한 조회

그럼 위에서 생성했던 인덱스 idx_board_id_article_id가 아니라 PK 조건을 걸어서 조회를 해보자.

article_id의 값은 임의로 지정했다.

select * from article where article_id = 155628446000697344;

매우 빠르게 조회된 것을 확인할 수 있다. 쿼리 플랜을 확인해보자.

explain select * from article where article_id = 155628446000697344;

별도의 인덱스를 생성한 것이 없는데, key를 보니 PRIMARY라고 되어있다.

그럼 내가 생성한 인덱스는 무슨 인덱스인가?

 

* Secondary Index란?

내가 생성한 인덱스는 Secondary Index이다. Non-Clustered Index라고도 부른다.

세컨더리 인덱스의 leaf node는 다음 데이터를 가지고 있다.

• 인덱스 컬럼 데이터
• 데이터에 접근하기 위한 포인터

여기서 데이터는 클러스터드 인덱스가 갖고 있다.

세컨더리 인덱스는 인덱스에 지정한 컬럼을 key로 가진다.

그리고 leaf node의 데이터는 데이터에 접근하기 위한 포인터(PK)를 가진다.

내가 생성했던 board_id, article_id 인덱스에 이 것을 대입해보면,

article_id는 인덱스 컬럼 데이터이면서, 데이터에 접근하기 위한 포인터이다.

세컨더리 인덱스를 이용한 데이터 조회는 인덱스 트리를 2번 타고 있는 것이다.

1. 세컨더리 인덱스에서 데이터에 접근하기 위한 포인터를 찾는다.
2. 클러스터드 인덱스에서 데이터를 찾는다.

페이징 쿼리가 저 절차대로 수행되고 있는지 분석해보자.

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

 

1. (board_id, article_id)에 생성된 세컨더리 인덱스에서 article_id를 찾는다.
2. 클러스터드 인덱스에서 article 데이터를 찾는다.
3. offset 1499970을 만날 때까지 반복하며 skip한다.
4. limit 30개를 추출한다.

이 과정을 확인하면 데이터는 offset 1499970부터 30개의 데이터만 필요한데,

offset 1499970을 만날 때까지 반복하며 데이터에 접근하고 있는 것이다.

이 비효율적인 과정을 생략할 수는 없을까?

 

* board_id, article_id만 추출

내가 만든 세컨더리 인덱스는 board_id와 article_id를 포함한다.

세컨더리 인덱스에서 필요한 30건에 대해서 article_id만 먼저 추출하고,

그 30건에 대해서만 클러스터드 인덱스에 접근하면 되지 않을까?

article_id는 클러스터드 인덱스에 접근하지 않아도 가져올 수 있을 것 같다.

쿼리에서 전체를 추출하는 것이 아니라, board_id와 article_id만 추출하도록 수행해보자.

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

전체 데이터를 조회할 때는 약 3초가 소요됐었는데, board_id와 article_id만 추출하는 것은 0.4초가 소요되었다.

쿼리 플랜을 확인해보니, Extra = Using index가 추가되었다. 인덱스만 사용해서 데이터를 조회했음을 알 수 있다.

이 인덱스는 무엇일까?

 

* Covering Index

이렇게 인덱스의 데이터만으로 조회를 수행할 수 있는 인덱스를 Covering Index라고 한다.

커버링 인덱스는 인덱스만으로 쿼리의 모든 데이터를 처리할 수 있는 인덱스이다.

클러스터드 인덱스의 데이터를 읽지 않고, 세컨더리 인덱스에 포함된 정보만으로 조회가 가능한 인덱스를 의미한다.

그럼 이제 추출된 30건의 article_id에 대해서 클러스터드 인덱스에 접근하면 된다.

 

* 해결 방법

30건의 article_id를 의 서브 쿼리의 결과로 만들고, article 테이블과 join 해보자.

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

50000번 페이지에서 30개의 게시글을 추출하기 위해, 약 3초 가량 걸리던 쿼리가 0.2초로 개선되었다.

커버링 인덱스를 활용하여 offset 1499970에서부터 클러스터드 인덱스에 접근하게 된 것이다.

쿼리 플랜을 확인해보자.

article_id를 추출하기 위한 서브 쿼리 과정에서 파생(DERIVED) 테이블이 생기지만, 이 과정에서 커버링 인덱스가 사용되었다. 작은 규모의 파생 테이블과 조인하여 30건에 대해서만 클러스터드 인덱스에서 데이터를 가져오기 때문에 빠르게 처리할 수 있다.