아이돔 쿼리 성능 테스트를 하면서 1000건 이상의 데이터를 조회할 때 filesort가 발생하여 데이터를 하나씩 읽는 것을 확인했습니다. 이는 성능 저하를 일으키는 것 뿐만 아니라 리소스 소모와 애플리케이션이 지연을 발생시켜 응답 속도가 느려질 수 있는 병목 현상을 발생시킬 수 있습니다. 이러한 문제를 해결하기 위해 인덱스에 대해 알아보고, 해결 방법들을 찾아보고 적용하여 성능 개선을 하려고 합니다.
0. B-Tree 인덱스와 인덱스가 필요한 이유
B-Tree란?
이진 트리를 확장하여 N개의 자식을 가질 수 있도록 만든 것이다. 좌우 자식 간의 균형이 맞지 않을 경우에는 매우 비효율적이기 때문에, 항상 균형을 맞춘다는 의미에서 균형 트리라고도 불린다.
B-Tree의 특징
- 자녀 노드의 최대 개수를 늘리기 위해서 부모 노드의 key를 하나 이상 저장합니다.
- 부모 노드의 key들을 오름차순으로 정렬합니다.
- 정렬된 순서에 따라 자녀 노드들의 key값의 범위가 결정됩니다.
- 이런 방식을 사용하면 자녀 노드의 최대 개수를 입맛에 맞게 결정해서 사용할 수 있습니다.
- 최대 몇 개의 자녀 노드를 가질 것인지가 B-Tree를 사용할 때 중요한 파라미터입니다.
- M : 각 노드의 최대 자녀 노드 수, 최대 M개의 자녀를 가질 수 있는 B-Tree는 M차 B-Tree라 부릅니다.
- M-1 : 각 노드의 최대 key 수
- M/2 : 각 노드의 최소 자녀 노드 수(반올림, root node와 leaf node 제외)
- M/2 - 1 : 각 노드의 최소 키 수(root node 제외)
B-Tree 인덱스 구조
특수한 부분이 아니라면 B-Tree 인덱스를 사용하면 됩니다. 각각의 DBMS는 B-Tree를 변형(B+Tree 또는 B*-Tree 등)하여 사용하기도 합니다.
인덱스는 페이지 단위로 저장되며, 인덱스 키를 바탕으로 항상 정렬된 상태를 유지합니다. 정렬된 인덱스 키를 따라서 리프 노드에 도달하면 (인덱스 키, PK)쌍으로 저장되어 있습니다.
페이지란?
디스크와 메모리(버퍼플)에 데이터를 읽고 쓰는 최소 작업 단위입니다. 일반적인 인덱스를 포함해 PK와 테이블 등은 모두 페이지 단위로 관리됩니다. 따라서 만약 쿼리를 통해 1개의 레코드를 읽고 싶더라도 결국 하나의 블록으로 읽어야 합니다.
그래서 페이지에 저장되는 개별 데이터의 크기를 최대한 적게하여, 1개의 페이지에 많은 데이터들을 저장할 수 있도록 하는 것이 상당히 중요합니다. 페이지에 저장되는 데이터의 크기가 클수록,
1 .디스크 I/O가 많아질 수 있습니다.
2. 메모리에 캐싱할 수 있는 페이지의 수가 줄어들 수 있습니다.
인덱스가 필요한 이유
인덱스를 통해 데이터를 조회하는 것은,
1. 인덱스를 통해 PK를 찾는다.
2. PK를 통해 레코드를 찾는다.
2가지 작업이 수행되는 것입니다.
이러한 이유로 옵티마이저는 인덱스를 통해 레코드 1건을 읽는 것이 테이블을 통해 직접 읽는 것 보다 4 ~ 5배 정도 비용이 더 많이 들 것입니다. 하지만 DBMS는 우리가 원하는 레코드가 어디있는지 모르기 때문에, 모든 테이블을 뒤져서 레코드를 찾아야합니다. 이는 엄청난 디스크 읽기 작업이 필요하므로 상당히 느립니다.
하지만 인덱스를 사용한다면 인덱스를 통해 PK를 찾고, PK를 통해 레코드를 저장된 위치에서 바로 가져올 수 있으므로 디스크 읽기가 줄어들게 됩니다. 그렇기 때문에 레코드를 찾는 속도가 훨씬 빠르며, 이것이 인덱스를 사용하는 이유입니다.
반면에 인덱스를 타지 않는 것이 효율적일 수도 있습니다. 인덱스를 통해 레코드 1건을 읽는 것이 4~5배 정도 비싸기 때문에, 읽어야 할 레코드의 건수가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않는 것이 효율적입니다. 이런 경우 옵티마이저는 인덱스를 이용하지 않고 테이블 전체를 읽어서 처리합니다.
정렬 처리 방법
인덱스를 정렬 순서대로만 읽을 수 있는 것은 아닙니다. 인덱스가 오름차순으로 생성되어 있어도 내림차순으로 읽는 것이 가능하며, 인덱스를 읽는 방향은 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정됩니다.
- Ascending index : 작은 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스입니다.
- Descending index : 큰 값의 인덱스 키가 B-Tree의 왼쪽으로 정렬된 인덱스입니다.
- Forward index scan(Forward scan) : 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 왼쪽 페이지부터 오른쪽으로 스캔합니다.
- Backward index scan(Backward scan) : 인덱스 키의 크고 작음에 관계없이 인덱스 리프 노드의 오른쪽 페이지부터 왼쪽으로 스캔합니다.
Descending index
MySQL 8.0부터는 Descending index 기능이 추가되어 역순으로 정렬되는 인덱스(Descending index)를 생성할 수 있게 되었습니다. 이를 통해 필요에 따라 적절히 정순(ORDER BY ASC)과 역순(ORDER BY DESC)을 혼합하여 정렬하는 작업을 인덱스로 이용할 수 있게 되었습니다.
CREATE TABLE tb_wow (
uid BIGINT PRIMARY KEY,
age SMALLINT,
score SMALLINT,
INDEX ix_score_age (score DESC, age ASC)
);
SELECT * FROM tb_wow ORDER BY score ASC, age DESC;
SELECT * FROM tb_wow ORDER BY score DESC, age ASC ;Backward index scan이 느린 이유
MySQL 서버의 InnoDB 스토리지 엔진에서는 Forward 및 Backward index scan이 페이지 간의 양방향 연결 고리(Double linked list)를 통해 이루어집니다. 이를 통해 Forward와 Backward 간의 차이점은 단순히 스캔 방향의 차이에 불과합니다.
-
페이지 잠금이 Forward index scan에 적합한 구조
- InnoDB의 페이지 잠금 방식은 Forward index scan을 중심으로 구현되어 있습니다. Forward index scan으로 인덱스 리프 페이지를 읽을 때, 페이지 잠금을 획득할 때 Forward scan 순서대로 잠금을 설정하고, 처리가 완료되면 잠금을 해제합니다.
- InnoDB의 B-Tree 리프 페이지는 Double linked list로 연결되어 있기 때문에 어느 방향에서든 이동하는데에는 차이가 없습니다. 하지만 InnoDB 스토리지 엔진에서는 페이지 잠금 과정에서 데드락을 방지하기 위해서 B-Tree의 왼쪽에서 오른쪽 순서(Forward)로만 잠금을 획득하도록 합니다. 따라서 Forward index scan에서는 다음 페이지 잠금 획득이 간단하지만, Backward index scan에서 이전 페이지 잠금을 획득하는 과정은 상당히 복잡합니다.
-
페이지 내에서 인덱스 레코드는 단방향으로만 연결된 구조(Forward single linked link)
InnoDB 스토리지 엔진이 특정 레코드를 검색할 때, B-Tree를 사용하여 검색 대상 레코드가 저장된 페이지까지 도달합니다. 그러나 해당 페이지 내에는 여러 레코드가 저장되어 있습니다. 일반적으로 16K 크기의 인덱스 페이지에는 약 600개 이상의 레코드가 저장될 수 있습니다. 이 많은 레코드를 하나씩 비교하는 것은 레코드 검색을 상당히 느리게 만들 수 있습니다. 따라서 InnoDB 스토리지 엔진은 각 페이지에서 순차적으로 정렬된 레코드를 4~8개 정도씩 묶어 대표 키를 선정합니다. 이 대표 키들을 별도의 리스트로 관리하는데, 이를 페이지 디렉토리라고 합니다.
1. filesort란
MySQL에서 ORDER BY 또는 GROUP BY와 같은 정렬 작업을 처리하기 위해 사용하는 정렬 메커니즘 중 하나입니다. MySQL이 인덱스를 사용하여 데이터를 정렬할 수 없을 때, Filesort라는 방식으로 정렬을 처리합니다.
파일 정렬 작업에서는 정렬 연산을 수행하기 위해 추가적인 메모리 공간이 필요합니다. 이를 위해 sort buffer라는 메모리 공간을 사용하며, MySQL 서버에서는 이를 위해 sort_buffer_size라는 시스템 변수를 설정할 수 있습니다. 이 변수는 정렬을 수행하는 데 사용할 수 있는 최대 메모리 크기를 결정하며, 정렬 작업의 성능과 메모리 사용량을 조절하는 데 도움이 됩니다. 이 sort buffer는 가변적으로 메모리를 할당받아 작업을 수행합니다.
filesort의 문제점
정렬에 할당된 메모리 공간보다 더 큰 공간이 필요할 경우 임시 디스크 파일을 사용하게 되는데, 이는 filesort의 속도를 저하시키는 원인 중 하나가 됩니다.
메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시 디스크에 기록해 둔 후 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 저장합니다. 이와 같이 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 합니다. 이 작업들이 모드 디스크의 쓰기와 읽기를 발생시키며, 레코드 건수가 많을수록 반복 작업의 횟수가 많아집니다. 또한, 소트 버퍼는 여러 클라이언트가 공유해서 사용할 수 있는 영역이 아니기 때문에 커넥션이 많을수록, 정렬 작업이 많으면 많을수록 소트 버퍼로 소비되는 메모리 공간이 커짐을 의미합니다.
리눅스 계열의 운영체제에서는 너무 큰 sort_buffer_size를 사용하는 경우, 큰 메모리 공간 할당 때문에 성능이 훨씬 떨어질 수도 있습니다
정렬 처리 방법
| 정렬 처리 방법 | 실행 계획의 Extra 컬럼 내용 |
|---|---|
| 인덱스를 사용한 정렬 | 별도 표기 없음 |
| 조인에서 드라이빙 테이블만 정렬 | "Using Filesort" 메시지가 표기 됨 |
| 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 | "Using temporary; Using Filesort" 메시지가 표시됨 |
1. 인덱스를 이용한 정렬
실제로 추가적인 정렬 작업을 수행하지 않고 인덱스의 순서를 활용하여 데이터에 접근합니다. 이는 인덱스의 값이 이미 정렬되어 있기 때문에 가능합니다. B-Tree 인덱스는 키 값으로 정렬되어 있으며, 이를 통해 인덱스를 통한 정렬된 접근이 가능합니다.
조인이 네스티드-루프 방식으로 실행될 때에도 드라이빙 테이블의 인덱스 읽기 순서가 유지됩니다. 하지만 조인 버퍼가 사용될 경우에는 정렬 순서가 흐트러질 수 있으므로 주의가 필요합니다. 이러한 특성을 고려하여 쿼리의 실행 계획을 작성하고 최적화해야 합니다.
2. 조인의 드라이빙 테이블만 정렬
첫 번째 테이블의 레코드를 먼저 정렬한 후에 조인을 실행하는 것은 정렬의 차선택이 될 수 있습니다. 이렇게 처리하면 조인에서 첫 번째로 읽히는 테이블의 컬럼만으로 ORDER BY 절을 작성해야 합니다. 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고, 그 결과와 드리븐 테이블을 조인합니다.
3. 임시 테이블을 이용한 정렬
쿼리가 단일 테이블에서 SELECT하여 정렬하는 경우에는 임시 테이블이 필요하지 않습니다. 그러나 2개 이상의 테이블을 조인하고 그 결과를 정렬해야 하는 경우에는 임시 테이블이 필요할 수 있습니다. 이 방법은 정렬해야 할 레코드가 가장 많기 때문에 가장 느린 정렬 방법입니다.
ORDER BY 절의 정렬 기준 컬럼은 드라이빙 테이블이 아닌 드리븐 테이블에 있는 컬럼입니다.
인덱스를 사용한 정렬 방식은 LIMIT로 제한된 건수 만큼만 읽으면서 바로바로 클라이언트로 결과를 전송해줄 수 있습니다. 그러나 인덱스를 사용하지 못하는 경우의 처리는 필요한 모든 레코드를 디스크로부터 읽어서 정렬한 후에야 비로소 LIMIT로 제한된 건수만큼 잘라서 클라이언트로 전송해줄 수 있습니다.
성능
tb_test1 테이블의 레코드가 100건이고, tb_test2 테이블 레코드가 1000건이며, 두 테이블의 조인 결과는 전체 1000건이라고 가정하고 정렬 처리 방법별로 읽어야 하는 레코드 건수와 정렬을 수행해야 하는 레코드 건수 비교한 표입니다.
 |  |
|---|
어느 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만듭니다. 가능하다면 인덱스를 사용한 정렬로 유도하고, 그렇지 못하다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유동하는 것도 좋은 튜닝 방법이라고 할 수 있습니다.
| 인덱스 이용 vs Filesort 이용
| 장점 | 단점 | |
|---|---|---|
| 인덱스 이용 | INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠르다. | INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다.인덱스 때문에 디스크 공간이 더 많이 필요하다. 인덱스의 개수가 늘어날수록 InnoDB의 버퍼 풀을 위한 메모리가 많이 필요하다. |
| Filesort 이용 | 인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀐다.정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다. | 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느려진다. |
2. 해결 방법
일반적으로는 WHERE 절에 대한 인덱스 설계가 주로 고려되지만, 실제로는 쿼리 전체에 대한 인덱스 설계가 필요합니다. 인덱스는 데이터를 효율적으로 찾는 방법이지만, 이를 최적화하여 실제 데이터에 접근하지 않고도 데이터를 검색할 수 있습니다.
커버링 인덱스
쿼리를 수행하는 데 필요한 모든 데이터를 포함하는 인덱스로, 데이터베이스 성능을 향상시키는데 유용합니다. SELECT / WHERE / GROUP BY / ORDER BY 등에 활용되는 모든 컬럼이 인덱스의 구성 요소인 경우를 말합니다.
커버링 인덱스가 빠른 이유
일반적으로 인덱스를 이용해 조회되는 쿼리에서 가장 큰 성능 저하를 일으키는 부분은 인덱스를 검색하고 대상이 되는 row의 나머지 컬럼값을 데이터 블록에서 읽을 때 입니다.
페이징 쿼리와 무관하게 인덱스를 탔음에도 느린 쿼리의 경우 select절 항목 때문입니다. 이를테면 커버링 인덱스를 태우지 않은 일반적인 조회 쿼리는 order by, offset ~ limit 을 수행할때도 데이터 블록으로 접근하게 됩니다.
select id, book_no, book_type, name
from book
where name like '200%'
order by id desc
limit 10 offset 10000;1번에서 사용된 기존의 페이징 쿼리는 select에서 사용된 book_no, book_type이 인덱스(idx_book_1(name))에 포함되지 않기 때문에 커버링 인덱스가 될 수가 없습니다.
클러스터 인덱스(PK)인 id는 모든 인덱스에 자동 포함됩니다.
쿼리에서 오래걸리는 페이징 작업까지는 커버링 인덱스로 빠르게 처리후, 마지막 필요한 컬럼들만 별도로 가져오는 형태를 사용합니다.
CREATE TABLE tb_wow (
uid BIGINT PRIMARY KEY,
age SMALLINT,
score SMALLINT,
INDEX ix_score_age (score DESC, age ASC)
);
SELECT * FROM tb_wow ORDER BY score ASC, age DESC;
SELECT * FROM tb_wow ORDER BY score DESC, age ASC ;3. 적용
아이돔에서는 생성, 수정, 삭제보다 조회하는 쿼리를 더 많이 사용하기 때문에 손해를 보더라도 커버링 인덱스를 사용하기로 결정했습니다.
| 문제가 있는 코드
카테고리별로 게시글을 조회하고 createdAt을 내림차순으로 정렬하기 위해 모든 데이터를 하니씩 읽으면서 가상 테이블을 생성했습니다. 그러나 이 작업 중에 filesort가 발생하는 것을 발견했습니다. 이를 해결하기 위해 커버링 인덱스를 사용하여 쿼리의 성능을 향상시키는 방법을 고려했습니다.
| 커버링 인덱스 적용하기
MySQL
게시글을 가져오는 쿼리에서 filesort를 제거하기 위해 사용된 필드들인 dorm_category, is_deleted, 그리고 created_at에 커버링 인덱스를 설정했습니다. 또한, 요구 사항에 따라 created_at을 내림차순으로 정렬해야 했기 때문에 인덱스 생성 시 created_at 뒤에 desc를 추가했습니다. 이를 통해 파일 정렬 문제를 해결할 수 있었습니다.
- id : SQL문이 실행되는 순서를 의미합니다. 2개 행의 id가 같다면 이것은 조인이 된 것 입니다.
- select_type
- SIMPLE : 단순한 SELECT문입니다.
- PRIMARY : 외부쿼리 또는 UNION이 포함되는 경우 1번째 SELECT문입니다.
- SUBQUERY : SELECT / WHERE에 작성된 서브쿼리입니다.
- DERIVED : FROM에 작성된 서브쿼리입니다.
- UNION : UNION 또는 UNION ALL로 합쳐진 SELECT문 입니다.
- type
- system : 0개 또는 1개의 데이터만 테이블에 존재하는 경우입니다.
- const : 단 1개의 데이터만 조회하는 경우입니다.
- eq_ref : 조인이 될 때, 드리븐 테이블의 PK 또는 고유 인덱스로 단 1개의 데이터만 조회하는 경우입니다.
- ref : eq_ref와 같지만 2개 이상의 데이터를 조회하는 경우입니다.
- index : Index Full Scan
- range : Index Range Scan
- all : Table Full Scan
- key
- 옵티마이저가 실제로 선택한 인덱스를 의미합니다.
- rows
- SQL문을 수행하기 위해 접근한 데이터의 모든 행 수를 의미합니다.
- extra
- Distinct : 중복을 제거하는 경우입니다.
- Using where : WHERE로 필터링한 경우입니다.
- Using Temporary : 데이터 중간 결과를 임시 테이블을 생성한 경우입니다. (보통 DISTINCT / GROUP BY / ORDER BY 가 포함되면 임시 테이블 생성)
- Using index : 커버링 인덱스를 사용한 경우입니다.
- Using filesort : 데이터를 정렬한 경우입니다.
Querydsl
@Override
public Page<Post> findPostsByDormCategoryAndIsDeletedFalse(
DormCategory dormCategory, Pageable pageable) {
// 1) 커버링 인덱스로 대상 조회
List<Long> ids = queryFactory
.select(post.id)
.from(post)
.where(post.dormCategory.eq(dormCategory)
.and(post.isDeleted.eq(false)))
.orderBy(post.createdAt.desc())
.offset(pageable.getOffset())
.limit(pageable.getPageSize())
.fetch();
List<Post> posts = queryFactory
.selectFrom(post)
.where(post.id.in(ids))
.orderBy(post.createdAt.desc())
.fetch();
return new PageImpl<>(posts, pageable, ids.size());
}- select에는 id만 포함하여 커버링 인덱스를 활용해 빠르게 조회합니다.
- 1의 결과를 가지고 select절을 조회합니다.
- where id in()밖에 없기 때문에 정렬된 상태로 조회되지 않으므로 .orderBy(post.createdAt.desc())를 추가합니다.
| 성능 테스트
- 커버링 인덱스 적용 전
- 커버링 인덱스 적용 후
MySQL에서 커버링 인덱스를 적용하기 전의 실행 시간은 0.474초이고, 적용 후에는 0.0018초입니다. 이는 0.4722초나 감소하여 약 99.581% 개선되었습니다.
실제 어플리케이션에서의 총 요청 응답 시간은 커버링 인덱스를 적용하기 전 941ms였으며, 적용 후에는 487ms로 감소하여 454ms만큼 감소했습니다. 이는 약 48.27% 개선되었습니다.
| 단점
-
많은 인덱스가 필요합니다.
모든 쿼리 항목을 인덱스에 포함해야 하므로, 느린 쿼리가 발생할 때마다 새로운 인덱스가 생성될 수 있습니다.
-
인덱스 크기가 증가합니다.
인덱스는 결국 데이터이기 때문에, 너무 많은 컬럼이 포함되면 인덱스의 크기가 커질 수 있습니다. 특히, where절 이외에도 order by, group by, having 등에 사용되는 컬럼이 인덱스에 포함되면 인덱스 크기가 더욱 커집니다.
-
데이터 양이 증가하고 페이지 번호가 뒤로 갈수록 성능이 저하될 수 있습니다.
시작 지점을 기준으로한 NoOffset 방식과 비교했을 때, 커버링 인덱스 방식은 데이터 양과 페이지 번호가 증가함에 따라 성능이 저하될 수 있습니다. 특히 테이블의 크기가 계속해서 증가하면 NoOffset 방식에 비해 성능 차이가 더 크게 나타날 수 있습니다.
참고 자료
