페이징 처리 성능 개선(2) - Covering Index

학습 목적

이전에 적용했던 noOffset방식은 성능 자체는 가장 좋지만 더보기 버튼을 눌렀을 때 동작하는 무한 스크롤 방식이 아니거나 group by등으로 기준을 잡을 key가 중복이 되는 경우에는 사용할 수가 없습니다. 

 

따라서 이번에는 noOffset을 이용하지 못하는 경우에 대비하기 위해 커버링 인덱스를 사용하여 성능 개선을 확인 해보고자 합니다! DB에는 item이 2000만개 존재합니다.

커버링 인덱스란 쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스
를 이야기합니다. 즉, select, where, order by, limit, group by 등에서 사용되는 모든 컬럼이 Index 컬럼
안에 다 포함된 경우입니다.

인덱스 vs 커버링 인덱스

• 인덱스를 탔음에도 느리다면 select절 때문입니다. order by, offset ~ limit절을 실행할 때에도 데이터 블록에 접근하기 때문입니다.
• 커버링 인덱스를 적용한다면 데이터 블록 접근없이 인덱스만을 이용해 where, order by, offset ~ limit절을 실행한 뒤에 다 걸러진 id값만을 가지고 실제 데이터 블록에 접근합니다.

 

실제 사용 예시

select절까지 포함하게 되면 너무 많은 컬럼이 인덱스에 포함되기 때문에 실제로 커버링 인덱스를 태울 때는 select를 제외한 나머지만 우선으로 진행한 뒤 실제 테이블과 조인하여 가져옵니다. 아래와 같은 쿼리를

SELECT *  // select 과정에서 실제 테이블에 접근
FROM items
WHERE 조건문
ORDER BY id DESC
OFFSET 페이지번호
LIMIT 페이지사이즈

다음과 같이 이용하는 형태입니다.

SELECT  * // 2. 실제 테이블과 join을 통해 
FROM  items as i
JOIN (SELECT id // 1. index만을 이용해 원하는 조건의 id를 획득  
        FROM items
        WHERE 조건문
        ORDER BY id DESC
        OFFSET 페이지번호
        LIMIT 페이지사이즈) as temp on temp.id = i.id

 

 

DB쿼리 작성 및 성능 확인

응답해야 하는 api스펙은 id, title, minimum_price, thumbnail_image_file_name, item_status, expire_at이며 인덱스 적용 전 후와 커버링 인덱스 적용 순서로 확인해볼 것입니다. 
(복합 인덱스는 index는 deleted, item_status, expire_at 순서로 구성되어있습니다.)

 

1. 인덱스 적용 X

select id, title, minimum_price, thumbnail_image_file_name, item_status, expire_at from item
where item_status = "BIDDING"  and deleted = false
order by expire_at desc limit 1000000, 10;

2. 인덱스 적용 O

create index deleted_itemStatus_expireAt ON item(deleted, item_status, expire_at);

select id, title, minimum_price, thumbnail_image_file_name, item_status, expire_at from item
where  deleted = false and item_status = "BIDDING" 
order by expire_at desc limit 1000000, 10;

3. 커버링 인덱스 적용

select i.id , title, minimum_price, thumbnail_image_file_name, item_status, expire_at 
from item as i join
(select id from item
where  deleted = false and item_status = "BIDDING" 
order by expire_at desc limit 1000000, 10) as s
on i.id = s.id;

다음에는 실제 QueryDSL을 이용해서 앞선 쿼리들을 구현해보겠습니다