Secondary Index 예제

복합 인덱스

 select * from post where board_id = 1 
 order by post_id desc limit 100 offset 99999900;

오늘의 문제에서 알아본 보조 인덱스를 조금 더 파려고 합니다.

위 쿼리는 게시판 id가 1인 모든 게시물을 최신 순, 내림차순으로 조회합니다.

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

Extra                       
Using where; Using filesort 

실행계획을 보면 Using filesort. 인덱스가 없기에 내부적으로 정렬이 들어갔다는 뜻입니다.

성능을 높이기 위해서 인덱스를 걸어줍니다.

create index idx_board_id_post_id on post(board_id asc, post_id desc)

where절에 사용하는 board_id와 post_id까지 복합인덱스로 걸어줍니다.

board_id에만 걸 경우 ordey by에서 post_id를 위한 정렬이 결국 발생하기 때문입니다.

mysql> explain select * from post where board_id = 1 order by post_id desc limit 30 offset 90;

idx_board_id_post_id 

possible_keys. 인덱스를 활용한 모습입니다.

커버링 인덱스

select * from post where board_id = 1 order by post_id  
desc limit 30 offset 1499790;


30 rows in set (2.44 sec)

offset을 늘려서 조회해보도록 합니다. 약 1200만개 데이터에서 2초 중반대 시간이 걸리는거 보면 문제가 있는 듯 합니다.

mysql> explain select * from article where board_id = 1 order by post_id des
c limit 30 offset 1499790;

idx_board_id_post_id

여전히 인덱스를 활용하고있습니다만, 무엇이 문제일까요... 이전 포스팅에서 정리한 보조 인덱스의 특성을 생각하면서 과정을 다시 봅시다.

select * from post where board_id = 1 order by post_id  
desc limit 30 offset 1499790;

1. 보조인덱스(idx_board_id_post_id)에서 실제 데이터 접근을 위한 키, 포인터(PK) post_id를 탐색
2. post_id로 Clustered Index에서 post 데이터 탐색
3. offset 1499790을 만날 때 까지 반복해서 스킵한다.
4. limit 30개로 추출한다.

즉, 반복해서 스킵하는 과정이 모든 칼럼을 대상으로 했기 때문에 오래걸린것입니다.

그렇다면 스킵하는 과정을 인덱스가 적용된 칼럼에 제한(커버링인덱스)해서 탐색하고 해당 칼럼을 기준으로 다른 칼럼들을 조인한다면 최적화가 가능할 듯 합니다.

select * from (
    select post_id from post
    where board_id = 1
    order by post_id desc
    limit 30 offset 1499970
) s left join post on s.post_id = post.post_id;

또 다른 방법

사실 offset 자체가 앞에서 부터 스킵하는 특성 때문에 offset 크기가 늘어날수록 성능은 안좋아 질 것입니다.

여기서 가장 먼저 떠오른 방법이 cursor 방식(무한 스크롤)이네요. 꼭 페이지번호 방식으로 페이징 해야할 경우가 아니라면 바로 고려할 수 있는 대안입니다.

해당 내용은 쿠케 님 강의를 추가로 참고했습니다. 말씀하시길 애초에 offset이 성능이 안좋아질정도로 조회하려는 사용자가 정상적인 유저일지 (데이터 수집용 어뷰저)생각해보고

제한하는 정책(offset 제한)을 두던가, 시간단위로 테이블을 분리하는 방법도 고려할 수 있다고 하십니다.