커버링 인덱스를 이용한 최적화
이번에 사이드 프로젝트를 진행하며 쿼리 실행계획을 보며 쿼리 최적화를 진행하였다.
우선 테이블을 보자.
create table account_book
(
id bigint auto_increment primary key,
title varchar(255) not null,
type varchar(255) not null,
amount int not null,
created_at datetime default CURRENT_TIMESTAMP null,
updated_at datetime default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP,
created_by varchar(255) null,
updated_by varchar(255) null,
end_date datetime null,
memo varchar(255) null,
user_id bigint null,
frequency varchar(255) null,
month int null,
day int null,
occurred_at date not null,
category_id bigint null,
constraint account_book_category_id_fk
foreign key (category_id) references monchall.category (id),
constraint account_book_user_entity_id_fk
foreign key (user_id) references monchall.user_entity (id)
);위와 같은 테이블을 생성하였다. user_id를 이용해 user_entity 테이블과 연관관계를 가지고 있고 category_id를 이용해 category 테이블과 연관관계를 가지고 있다.
여기서 찾으려고 하는 데이터는 user_id와 type이 일치하고 occurred_at으로 내림차순 정렬했을 때 10개의 데이터를 가지고 오려고 한다.
select a.occurred_at,
a.id,
a.title,
a.category_id,
a.amount,
a.updated_at,
a.memo,
a.end_date,
a.frequency,
a.month,
a.day
from account_book as a
join category c on a.category_id = c.id
where user_id = ?
and type = 'SPEND'
order by occurred_at desc
limit 10 offset ?;type의 경우 'SPEND'와 'INCOME' 두 가지가 있다.
현재 account_book에는 occurred_at은 2024년 4월 1일 ~ 2025년 4월 27일까지 날짜로 총 22만 개의 레코드가 있다. 쿼리의 성능 테스트를 위해 극단적인 offset을 사용했다. 참고로 밑에서 실행하는 모든 쿼리는 각각 10회 실행한 결과다.
explain analyze
select a.occurred_at,
a.id,
a.title,
a.category_id,
a.amount,
a.updated_at,
a.memo,
a.end_date,
a.frequency,
a.month,
a.day
from account_book as a
join category c on a.category_id = c.id
where user_id = 3
and type = 'SPEND'
order by occurred_at desc
limit 10 offset 100000;-> Limit/Offset: 10/100000 row(s) (cost=44648 rows=10) (actual time=420..420 rows=10 loops=1)
-> Nested loop inner join (cost=44648 rows=218859) (actual time=284..416 rows=100010 loops=1)
-> Sort: a.occurred_at DESC (cost=22215 rows=218859) (actual time=284..303 rows=100010 loops=1)
-> Filter: ((a.`type` = 'SPEND') and (a.user_id = 3) and (a.category_id is not null)) (cost=22215 rows=218859) (actual time=8.8..206 rows=120000 loops=1)
-> Table scan on a (cost=22215 rows=218859) (actual time=0.0666..161 rows=220006 loops=1)
-> Single-row covering index lookup on c using PRIMARY (id=a.category_id) (cost=0.25 rows=1) (actual time=913e-6..949e-6 rows=1 loops=100010)
위 결과를 정리해 보자.
| 항목 | 정보 |
| 테이블 스캔 건수 | 220,006 |
| 필터링 후 건수 | 120,000 |
| 정렬 대상 건수 | 100,010 |
| 최종 가져온 row | 10 |
| 정렬 소요 시간 | 284 ~ 416 ms |
| 전체 수행 시간 | 420 ms |
우선 a테이블 (account_book)은 테이블 스캔을 하고 있다. 이후 where 조건에 따라 필터링을 하고 있다. 그리고 occurred_at 컬럼 기준으로 내림차순 정렬을 하고 10만 개를 읽고 그중 10개를 잘라서 가져오고 있다. category 테이블은 커버링 인덱스를 이용해 가져오고 있다.
읽지 않아도 되는 데이터까지 전부 읽고 있다. 그리고 offset으로 인해 10만 건의 데이터를 가져오고 limit으로 10건을 가져오고 있다. 사실상 99.99%가 버려지고 있는 것이다. 현재는 무한 스크롤이 아닌 페이징을 하고 있어 커서 기반 페이지네이션은 제한이 된다. offset과 limit은 유지한 채 개선을 해보자.
인덱스
현재는 생성된 인덱스가 없어서 테이블 스캔을 통해 모든 데이터를 가져오고 있다. 테이블의 모든 컬럼을 상대로 SELECT 한다고 했을 때 인덱스를 어떻게 만들어 볼 수 있을까?
우선 복합 인덱스를 생성할 때 순서가 중요하다. 뒤에 명시된 컬럼은 앞에 명시된 컬럼에 의존하게 된다. 예를 들어 a, b 컬럼을 묶어 인덱스를 만든다면 a컬럼을 기준으로 정렬 후 b컬럼을 정렬하게 된다.
인덱스를 생성할 때, 우선 user_id를 가장 먼저 정렬 기준으로 삼기로 했다. 이는 WHERE 절에서 user_id를 기준으로 데이터를 조회하고 있기 때문에, 가장 먼저 필터링이 이뤄지는 컬럼을 인덱스의 선두에 두는 것이 효율적이라고 판단했기 때문이다.
그다음으로는 type 컬럼을 인덱스에 추가했다. type은 'SPEND'와 'INCOME' 두 가지 값만 가지는 컬럼인데, 만약 user_id보다 먼저 정렬하게 되면 상대적으로 많은 레코드에 접근해야 하므로 효율이 떨어질 수 있다. 따라서 선택도가 더 높은 user_id 뒤에 배치하는 것이 좋다고 생각했다.
마지막으로, 정렬 조건에 사용되는 occurred_at 컬럼을 인덱스에 추가했다. 만약 type과 occurred_at의 순서를 바꾸어 날짜 기준으로 먼저 정렬하게 되면, 시간 순서대로는 정렬되겠지만 INCOME과 SPEND 데이터가 뒤섞여 원하는 결과를 얻기 어렵다.
이러한 이유로 최종적으로 user_id -> type -> occurred_at 순서로 인덱스를 생성했다.
CREATE INDEX user_idx_occurred_at_type ON account_book (user_id, type, occurred_at desc);위와 같이 인덱스를 생성하고 같은 쿼리로 다시 실행계획을 보자.
-> Limit/Offset: 10/100000 row(s) (cost=50229 rows=10) (actual time=307..307 rows=10 loops=1)
-> Nested loop inner join (cost=50229 rows=109427) (actual time=0.0997..303 rows=100010 loops=1)
-> Filter: (a.category_id is not null) (cost=11930 rows=109427) (actual time=0.0919..192 rows=100010 loops=1)
-> Index lookup on a using user_idx_occurred_at_type (user_id=3, type='SPEND') (cost=11930 rows=109427) (actual time=0.0908..183 rows=100010 loops=1)
-> Single-row covering index lookup on c using PRIMARY (id=a.category_id) (cost=0.25 rows=1) (actual time=902e-6..938e-6 rows=1 loops=100010)| 항목 | 정보 |
| 테이블 스캔 건수 | 109,427 |
| 필터링 후 건수 | 100,010 |
| 정렬 대상 건수 | |
| 최종 가져온 row | 10 |
| 정렬 소요 시간 | |
| 전체 수행 시간 | 307 ms |
인덱스를 생성한 결과, 실행 계획에서 Index Lookup을 통해 새로 만든 인덱스를 사용하는 것을 확인할 수 있었다. 이를 통해 쿼리 수행 시간이 약 100ms 정도 단축되었으며, 테이블 스캔 건수 또한 절반 수준으로 줄어드는 효과를 얻었다.
또한 이번 실행 계획에서는 별도의 정렬(Sort) 작업이 발생하지 않았다.
그 이유는 인덱스 컬럼 순서에 있다. occurred_at이 인덱스의 마지막 컬럼으로 설정되어 있어, 인덱스를 탐색하는 시점에 이미 원하는 정렬 순서대로 데이터가 조회되기 때문이다.
따라서 별도의 추가 정렬 없이 바로 결과를 반환할 수 있었고, 이는 전체 쿼리 성능 개선에도 크게 기여했다.
하지만 여전히 대부분의 데이터가 버려지고 10개의 데이터를 가져오고 있다. 조금 더 개선해 보자.
커버링 인덱스
커버링 인덱스란 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있어, 테이블을 추가 조회하지 않고 인덱스만으로 결과를 얻을 수 있는 것을 말한다. 이번에는 커버링 인덱스를 활용해 쿼리 성능을 개선해 보자.
초기에는 굉장히 단순한 SQL을 이용했다.
select a.occurred_at,
a.id,
a.title,
a.category_id,
a.amount,
a.updated_at,
a.memo,
a.end_date,
a.frequency,
a.month,
a.day
from account_book as a
join category c on a.category_id = c.id
where user_id = ?
and type = ?
order by occurred_at desc
limit 10 offset ?;위 쿼리에서 필요한 모든 컬럼을 포함시켜 인덱스를 만든다면 인덱스의 크기가 굉장히 커질 것이다. 또한 크기와 상관없이 만들려고 하면 에러가 뜬다.
Specified key was too long; max key length is 3072 byte
생성하려고 하는 인덱스 길이의 최대를 초과했다고 나온다. 그래서 select를 제외한 나머지 부분에 대해 우선 수행한다.
select a.occurred_at,
a.id,
a.title,
a.category_id,
a.amount,
a.updated_at,
a.memo,
a.end_date,
a.frequency,
a.month,
a.day
from account_book as a
join (select id
from account_book as ab force index (account_book_cover)
where user_id = ?
and type = ?
order by ab.occurred_at desc
limit 10 offset ?) as temp
on temp.id = a.id
join category c on a.category_id = c.id;이러한 형태로 바뀐다는 것이다. 이 쿼리에서 join 하는 부분에 커버링 인덱스가 사용된다.
CREATE INDEX account_book_cover ON account_book (user_id, type, occurred_at desc, id);
위와 같이 join 안 서브쿼리에 사용되는 모든 컬럼을 포함시켜 인덱스를 만들었다. 이제 다시 쿼리를 실행해 보자.
-> Nested loop inner join (cost=74633 rows=10) (actual time=47.1..47.2 rows=10 loops=1)
-> Nested loop inner join (cost=49629 rows=10) (actual time=47.1..47.2 rows=10 loops=1)
-> Table scan on temp (cost=24623..24626 rows=10) (actual time=47.1..47.1 rows=10 loops=1)
-> Materialize (cost=24623..24623 rows=10) (actual time=47.1..47.1 rows=10 loops=1)
-> Limit/Offset: 10/100000 row(s) (cost=24622 rows=10) (actual time=47.1..47.1 rows=10 loops=1)
-> Covering index lookup on ab using account_book_cover (user_id=3, type='SPEND') (cost=24622 rows=109427) (actual time=0.0323..43.2 rows=100010 loops=1)
-> Filter: (a.category_id is not null) (cost=0.25 rows=1) (actual time=0.00435..0.00445 rows=1 loops=10)
-> Single-row index lookup on a using PRIMARY (id=temp.id) (cost=0.25 rows=1) (actual time=0.00406..0.0041 rows=1 loops=10)
-> Single-row covering index lookup on c using PRIMARY (id=a.category_id) (cost=0.25 rows=1) (actual time=0.00159..0.00163 rows=1 loops=10)| 항목 | 정보 |
| 테이블 스캔 건수 | 100,010 |
| 필터링 후 건수 | 10 |
| 정렬 대상 건수 | |
| 최종 가져온 row | 10 |
| 정렬 소요 시간 | |
| 전체 수행 시간 | 47.1 |
account_book_cover 인덱스를 사용하고 있다. 커버링 인덱스를 활용했기 때문에, 테이블을 내려가지 않고 인덱스만으로 필요한 데이터를 처리하게 된다. 그 결과, 불필요한 디스크 I/O를 줄일 수 있다.
다만, offset이 10만이기 때문에 원하는 10건을 찾기 위해선 10만 건 이상의 데이터를 스캔하는 비용은 피할 수 없다. 하지만 커버링 인덱스를 사용함으로써 성능을 최적화할 수 있었다. 실행 계획에서 Covering index lookup이 나타나는 부분을 보면, 인덱스만으로 처리가 완료되었음을 확인할 수 있다. 이는 디스크 I/O를 발생시키지 않고, 인덱스만으로 데이터를 읽은 것을 의미한다. 또한, Nested loop inner join으로 조인하는 작업이 진행되지만, 조인 대상이 10건으로 소량이기 때문에 조인 비용은 매우 적다. 결론적으로, Offset이 큰 경우에도 커버링 인덱스를 활용하면 성능을 크게 개선할 수 있다는 점을 확인할 수 있었다.
이번 포스트에서는 커버링 인덱스를 활용하여 쿼리 성능을 최적화하는 방법을 살펴보았다. 커버링 인덱스는 인덱스만으로 필요한 데이터를 모두 읽을 수 있기 때문에, 디스크 I/O를 최소화하고 성능을 개선하는 데 큰 도움이 된다. 또한, Offset 값이 큰 경우에도 커버링 인덱스를 통해 불필요한 테이블 접근을 줄일 수 있다는 것을 확인했다. 실제 실행 계획을 분석한 결과, 커버링 인덱스를 사용함으로써 쿼리의 응답 시간이 빨라졌고, 조인 비용 또한 최소화되었다.
다만, 커버링 인덱스만으로 모든 상황에서 성능이 최적화되는 것은 아니다. 특히 Offset이 매우 큰 경우, 여전히 많은 데이터를 스캔해야 하므로 쿼리 설계와 인덱스 설계를 적절히 병행하는 것이 중요할 것이다.
참고