문제 상황

현재 레코드 수는 아래 그림과 같이 약 150만개 이고, 매 1시간마다 800 ~ 1200개의 레코드가 추가되는 테이블을 사용하고 있다.

위 요구사항을 해결하기 위한 로직을 처리하는데 아래와 같이 12초 ~ 13초의 시간이 소요되어 이를 개선하고자 한다.

해결 과정

우선 슬로우 쿼리가 발생하는 이유를 찾기 위해 실행 계획을 통해 알아보기로 한다.

개선 전의 실행 계획

EXPLAIN ANALYZE
SELECT *
FROM news
WHERE created_at BETWEEN '2023-07-07 10:00:00' AND '2023-07-07 11:00:00' AND title LIKE '%입력값%';

위와 같은 쿼리를 날렸을 때의 결과는 아래와 같다.

Table scan on news: (news.created_at between '2023-07-07 10:00:00' and '2023-07-07 11:00:00')
(cost=83282.06 rows=242017) (actual time=5.829..19139.414 rows=305977 loops=1)

개선하기 전의 실행 계획을 살펴보면 해당 쿼리는 5.8초 ~ 19139.414 초가 걸릴 수 있다.

DBMS에 인덱스 추가해보기

MySQL에서 인덱스를 추가하는 방법은 단순하다.

ALTER TABLE tablename ADD INDEX indexname (column1);

테이블에 들어있는 데이터 용량에 따라 수분이 걸릴 수도 있다.

위 인덱스가 제대로 생성되었는지 확인하려면 아래와 같은 쿼리를 보내면 된다.

SHOW INDEX FROM tablename;

DBMS에 인덱스를 생성했으니 애플리케이션 단에서도 매핑을 시켜보자.

Spring Boot, JPA 환경에서 인덱스를 적용해보기

  
@Table(
    name = "news",
    indexes = [
        Index(name = "news_index", columnList = "created_at")
    ]
)
@Entity
class News() { ... }

위 @Table 애노테이션으로 인덱스를 지정하고, 인덱스의 이름을 명시하여 DBMS에 생성된 인덱스와 매핑할 수 있다.

Index를 적용한 후 실행 계획

with index condition: (news.created_at between '2023-07-07 10:00:00' and '2023-07-07 11:00:00')
(cost=2088.81 rows=2027) (actual time=2.678..57.552 rows=2027 loops=1)

인덱스를 적용했을 땐 2.678초 ~ 57.552 초가 걸릴 수 있다고 한다.

가장 빠르게 쿼리된 상황에만 보더라도 약 50%가 성능 개선된 것으로 볼 수 있다.

그럼 인덱스를 쓰는게 무작정 좋은 것인지는 조금 더 따져봐야한다.

Index의 구조

MySQL에서는 B-Tree(Balanced-Tree), HashMap으로 구조화되어있다. DBMS의 종류에 따라 다르겠지만 최근에는 Fractal-Tree과 로그 기반의 Merge-Tree으로 구조화 되는 경우도 있다.

루트 노드, 브랜치 노드, 리프 노드가 트리 형태로 이어져 있으며 각 노드는 16KB인 페이지다.

리프 노드는 실제 데이터 레코드를 찾아가기 위한 주소를 가지고 있다.

Index 노드 삽입 과정

저장될 키 값을 이용해 B-Tree에 적절한 삽입점을 검색한다.

저장 위치가 결정되면 레코드의 키 값과 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다.

리프 노드가 꽉 차서 저장할 수 없을 때는 리프 노드가 분리 되어야 한다. 그리고 이 때 상위 브랜치 노드까지 처리 범위가 넓어진다.

위 이유로 인해 B-Tree는 상대적으로 쓰기 작업(새로운 키 추가)에 비용이 많이 든다.

인덱스를 지정할 때 고려해야할 점 - 삽입/변경/삭제의 빈도

만약 컬럼이 50가지 100가지 등등 많은 데이터를 보유하고 있다면 매 레코드를 추가할 때 마다 해당 컬럼에 대한 인덱스를 모두 추가하는 부가적인 리소스가 발생하게 되어 데이터 삽입이 매우 느리게 될 수 있다.
- 인덱스의 내부 구현이 LinkedList와 유사한 B-Tree방식으로 구현되어있기 때문이다.
  - 책의 내용이 1000페이지인데 500페이지 쯤에서 내용을 추가해야한다고 해보자
  - 이 상황에선 당연하게도 페이지의 내용을 추가하는 것과 더불어 인덱스를 관리하는 목차 또한 모두 고쳐야한다.
- 따라서 삽입/변경/삭제 등이 자주 일어나는 테이블에는 적절한 방법이 아니다.

인덱스를 지정할 때 고려해야할 점 - 인덱스를 적용할 테이블의 크기

DBMS에도 인덱스를 관리하기 위한 추가적인 저장공간 리소스가 소모된다.
- 인덱스는 일반적으로 10% ~ 20%의 공간을 추가로 요구한다.

결론적으로 인덱스는 대용량 테이블에 적용한다면 성능 개선을 기대할 수 있지만

그렇지 않다면 오히려 리소스를 더 사용하고, 조회 성능 까지도 느리게 만들 수 있다.

인덱스를 지정할 때 고려해야할 점 - Cardinality

Cardinality, Selectivity 두 용어는 거의 같은 의미로 사용되고, 모든 인덱스 Key 값 중 유니크한 값의 수를 말한다.

인덱스 키 값 중 중복된 값이 많아지면 많아질수록 Cardinality는 낮아진다.

인덱스는 Cardinality가 높을수록 검색 대상이 줄어들기 때문에 빠른 성능을 나타낼 수 있다.

하지만 Cardinality가 낮더라도 정렬, 그룹화 등을 위해 인덱스를 만드는 것이 더 나을 수도 있다.

인덱스는 항상 검색에만 사용되는 것이 아닌 것을 인지해야한다.

또한 여러 컬럼을 Index로 지정할 때는 카디널리가 높은 순으로 인덱스를 생성하는 것이 더 효율적이다.

숏스 - 150만건이 담긴 Table내 BETWEEN 조건 질의시 성능 개선 과정