김현준 7주차 서블릿 카페 학습 일지

페이징 성능 올리기(커버링 인덱스, 복합 인덱스)

문제 상황

이번 JSP 카페의 미션 중 페이지네이션이 있었는데 이 때의 상황을 바탕으로 쿼리의 성능에 대해 알아보겠다.

초기 DDL
CREATE TABLE IF NOT EXISTS articles (
article_id BIGINT PRIMARY KEY AUTO_INCREMENT, 
writer_id VARCHAR(255), 
title VARCHAR(255), 
contents TEXT, 
reply_count BIGINT, 
is_deleted BOOLEAN, 
created_at TIMESTAMP, 
modified_at TIMESTAMP, 
INDEX idx_article_is_deleted (is_deleted), 
INDEX articles_created_at_index (created_at), 
INDEX articles_modified_at_index (modified_at), 
INDEX articles_reply_count_index (reply_count), 
INDEX articles_title_index (title), 
INDEX articles_writer_id_index (writer_id));

SQL 쿼리
SELECT article_id, title, modified_at, writer_id, reply_count FROM articles 
WHERE is_deleted = false ORDER BY created_at DESC LIMIT ? OFFSET ?

초기 DDL을 보면 contents를 제외한 모든 컬럼에 인덱스를 걸었다. 그런데 생각해보니 페이징 쿼리에서 결국 가져와야 하는 쿼리가 페이지 사이즈 만큼 뿐이므로 INDEX를 굳이 모든 쿼리에 붙일 필요가 없다고 생각을 했다. 성능엔 차이가 없었는데 filesort를 타는 모습을 발견했다.

// 인덱스를 2개만 걸어줌!
INDEX idx_article_is_deleted (is_deleted), 
INDEX articles_created_at_index (created_at)

// 쿼리
SELECT article_id, title, modified_at, writer_id, reply_count
FROM articles
WHERE is_deleted = false
ORDER BY created_at DESC
LIMIT 15 OFFSET 30000;

// 실행 계획
1,SIMPLE,articles,,ref,idx_article_is_deleted,idx_article_is_deleted,2,const,248337,100,Using filesort
actual time - 997.0

물론 OFFSET을 10000정도로 하면 33.8 정도의 시간 밖에 걸리지 않는다. 하지만 뒤에서의 속도가 너무 느렸다.

30000정도에선 왜 filesort를 탈까?

이유는 아래와 같다.

단일 인덱스만 있는 경우: is_deleted와 created_at에 각각 단일 인덱스가 있다면, MySQL 옵티마이저는 다음과 같은 선택을 할 수 있다. a. is_deleted 인덱스를 사용하여 false 조건을 만족하는 레코드를 찾는다. b. 그 다음 created_at으로 정렬을 수행한다 (filesort). 또는 a. created_at 인덱스를 사용하여 정렬된 순서로 데이터를 읽는다. b. 각 레코드에 대해 is_deleted = false 조건을 확인한다 (필터링).

옵티마이저는 이 두 방법 중 더 효율적이라고 판단되는 것을 선택한다.

OFFSET의 영향: OFFSET 값에 따라 옵티마이저의 선택이 달라질 수 있다.
- OFFSET 33333의 경우: 많은 양의 데이터를 스킵해야 하므로, 옵티마이저는 filesort를 사용하는 것이 더 효율적이라고 판단할 수 있다.
- OFFSET 10000의 경우: 상대적으로 적은 양의 데이터를 스킵하므로, created_at 인덱스를 사용하여 정렬된 순서로 데이터를 읽고 is_deleted 조건을 필터링하는 것이 더 효율적이라고 판단할 수 있다.
비용 기반 최적화: MySQL 옵티마이저는 통계 정보를 바탕으로 각 실행 계획의 비용을 추정한다. OFFSET 값이 작을 때는 인덱스를 사용하는 것이 유리하지만, 값이 커질수록 filesort를 사용하는 것이 더 효율적이라고 판단할 수 있다.
데이터 분포: is_deleted = false인 레코드의 비율에 따라서도 실행 계획이 달라질 수 있다. 이 비율이 높다면 created_at 인덱스를 사용하는 것이 유리할 수 있다.

따라서 다르게 쿼리를 타는 것이다. 그래서 나는 복합 인덱스를 사용하기로 했다.

//복합 인덱스 사용
CREATE INDEX idx_is_deleted_created_at ON articles (is_deleted, created_at);

//위와 동일한 쿼리를 날렸을 때 실행 계획
1,SIMPLE,articles,,ref,"idx_article_is_deleted,idx_is_deleted_created_at",idx_is_deleted_created_at,2,const,248337,100,Backward index scan

actual time - 86.0

속도가 굉장히 빨라진 모습이다. 여기서 Backward index scan은 index를 역순으로 타는 것인데 기본적으로 index는 오름차순이기 때문에 created_at에서 desc로 정렬하기 때문에 역순으로 타는 것이 빠르다고 판단해 역순으로 타게 된다.

복합 인덱스를 썼기 때문에 속도가 빨라진 이유는 다음과 같다.

단일 인덱스 스캔:
- 복합 인덱스를 사용하면 하나의 인덱스 스캔으로 WHERE 조건과 ORDER BY를 모두 처리할 수 있다.
- 단일 인덱스들을 사용할 때와 달리, 두 개의 인덱스를 오가며 데이터를 처리할 필요가 없다.
정렬 회피:
- 복합 인덱스는 이미 (is_deleted, created_at) 순으로 정렬되어 있어, 별도의 filesort 작업이 필요 없다.
- 이는 특히 대량의 데이터를 다룰 때 큰 성능 향상을 가져온다.
효율적인 OFFSET 처리:
- 복합 인덱스를 사용하면 OFFSET 값이 큰 경우에도 효율적으로 처리할 수 있다.
- 인덱스에서 직접 필요한 위치로 이동할 수 있어, 많은 양의 데이터를 스킵하는 데 드는 비용이 줄어든다.
일관된 성능:
- 데이터량이나 OFFSET 값에 관계없이 일관된 성능을 제공한다.
I/O 감소:
- 필요한 데이터를 찾기 위해 접근해야 하는 데이터 페이지의 수가 줄어든다.

따라서 복합 인덱스를 고려해서 사용해야 한다.

커버링 인덱스

Non Clustered Key (일반적인 인덱스) 에는 인덱스 컬럼의 값들과 Clustered Key (PK) 의 값이 포함되어 있음
Clustered Key 만이 실제 테이블의 row 위치를 알고 있음

쿼리를 충족시키는 데 필요한 모든 데이터를 갖고 있는 인덱스를 커버링 인덱스 (Covering Index 혹은 Covered Index) 라고한다.

위의 상황에서 커버링 인덱스를 적용시킬 수 있다.

// 커버링 인덱스 쿼리
SELECT a.article_id, title, modified_at, writer_id, reply_count
FROM articles as a
         JOIN (select article_id
               from articles
               where is_deleted = false
               order by created_at desc
               limit 15 offset 30000) as b on a.article_id = b.article_id;
               
// 실행 계획
1,PRIMARY,<derived2>,,ALL,,,,,30015,100,
1,PRIMARY,a,,eq_ref,PRIMARY,PRIMARY,8,b.article_id,1,100,
2,DERIVED,articles,,ref,"idx_article_is_deleted,idx_is_deleted_created_at",idx_is_deleted_created_at,2,const,248337,100,Backward index scan; Using index
-- 2의 extra 옵션에서 Using index를 통해 커버링 인덱스를 타는 모습을 볼 수 있다.

actual time - 16.1

JOIN 절 안에서의 서브쿼리에서 인덱스를 이용해서 데이터를 가져오고 이를 비교해서 가져와 실제 실행 시간이 86.0 → 16.1로 줄어들었다. 물론 서브 쿼리와 서브 쿼리 결과를 메모리를 사용해 임시 테이블로 저장하기 때문에 cost는 크지만 실행 시간이 더 빨랐다. cost는 약 1.3배정도 더 나온 것 같다. 15개의 데이터를 임시 테이블로 저장하기에 현재는 문제가 없지만 limit 쿼리가 커지면 메모리가 부족할 수 있기에 이를 잘 써야할 것 같다.