[Database] MySQL 에서 Index 를 설계하고 추가하는 방법

이전에 올렸던 미니 e-commerce 프로젝트에서 제공하는 API 의 조회성능을 올려서 서비스를 좀더 향상시키고자 한다. 바로 이전 포스트에서 Redis 를 활용한 원격 캐싱 서버를 활용해 조회 성능을 높였는데, DB 인덱스를 추가하는 방법으로도 조회 성능을 높일 수 있다.

 

 현재 서비스를 상용으로 올려서 운영중인 프로젝트가 아니기 때문에, 어떤 쿼리가 가장 많이 수행되고 있는지는 정확한 파악이 어렵다. 하지만 비즈니스적으로 자주 조회될 쿼리가 무엇인지는 추측이 가능하고, 테스트 코드를 실행시켰을 때 어떤 쿼리가 수행되는지 정도는 파악이 가능하다.

 

 현재 제공하는 기능에서 사용하는 쿼리와 적용해볼 인덱스, 그리고 성능 향상점은 아래에 적어 놓았다. 



 

테스트 데이터

랜덤 값으로 생성한 100만개의 데이터를 각각의 테이블에 넣고 실행 하였다.

DDL 은 아래와 같다.

create table balance
(
    id      bigint not null
        primary key,
    balance bigint null,
    user_id bigint null,
    version bigint null
);


create table user_coupon
(
    id        bigint not null
        primary key,
    coupon_id bigint null,
    user_id   bigint null
);

create table orders
(
    id              bigint  not null
        primary key,
    amount          bigint  null,
    discount_amount bigint  null,
    status          tinyint null,
    user_coupon_id  bigint  null,
    user_id         bigint  null
);

create table product
(
    id          bigint       not null
        primary key,
    description varchar(255) null,
    name        varchar(255) null,
    price       bigint       null,
    stock       bigint       null
);



create table order_product
(
    id           bigint       not null
        primary key,
    order_id     bigint       null,
    price        bigint       null,
    product_id   bigint       null,
    product_name varchar(255) null,
    quantity     bigint       null,
);

 

유저의 잔액을 조회

select * from hhplus.balance where user_id = 506066;

인덱스 적용전

- 실행 계획

full scan 을 하는 모습이다

- 실행 시간: 2s 84ms

 

인덱스 적용

특정 유저의 잔액을 조회하는 쿼리는 매우 자주 실행되고, user_id 에 대한 인덱스만 넣으면 됨으로 아래와 같이 인덱스를 추가해 준다.

create index user_id_index on hhplus.balance(user_id);

 

인덱스 적용 후

- 실행 계획

설정한 Index 가 정상적으로 적용되어 조회비용이 많이 절감되었다

- 실행 시간 : 51ms

 

 

유저가 쿠폰을 가지고 있는지 조회

# 유저가 특정 쿠폰을 가지고 있는지 조회
select * from hhplus.user_coupon where user_id = 505011 and coupon_id = 100333;
# 유저의 쿠폰을 모두 조회
select * from hhplus.user_coupon where user_id = 505011;

 

인덱스 적용 전

- 실행 계획

user_id, coupon_id 로 조회시의 full scan

user_id 로 조회시의 full scan

- 실행 시간 : 1s 380ms, 1s 610ms

 

인덱스 적용

 주문서 생성 시에 유저에게 할당된 쿠폰을 조회하게 되는데, user_id 와 coupon_id 를 추가할 수 있다. 복합 키 구성 시에는 먼저 그룹을 크게 나눌 수 있는 범위부터 작은 순으로 나누는게 좋기 때문에, 카디널리티가 낮은 것 순으로 가는게 좋다. 하나의 쿠폰을 여러 유저에게 주는 테이블이기 때문에 카디널리티는 coupon_id 쪽이 더 높아, coupon_id 를 먼저 추가하는게 좋을 것 같다.

 

 하지만, 현재 서비스에서 제공하는 기능에서는 coupon_id 부터 인덱스를 타야할 상황은 적을 것 같다. 특정 쿠폰을 어떤 유저들이 받았는지 조회하는 기능(대시보드) 이 있으면 말이 달라지겠지만, 1) 유저가 어떤 쿠폰을 가지고 있는지 2) 특정 쿠폰을 유저가 가지고 있는지 에 대한 기능이 우선순위가 높으므로 user_id, coupon_id 순으로 인덱스를 걸었다. 

 만약 쿠폰이 어떤 유저들에게 부여되었는지 조회하는 쿼리가 필요한 요구사항이 추가된다면 coupon_id 를 먼저 확인하는 인덱스 추가가 필요해 보인다.

create index user_id_coupon_id_index on hhplus.user_coupon(user_id, coupon_id);

인덱스 적용 후

- 쿼리 실행 계획

user_id 로 조회시의 index scan

user_id, coupon_id 로 조회시의 index scan

- 쿼리 실행 시간 : 25ms, 18ms

 

 

인기 상품 조회

SELECT 
    product.id, 
    product.description, 
    product.name, 
    product.price, 
    product.stock, 
    SUM(order_product.quantity) AS total_quantity
FROM order_product
JOIN orders 
    ON orders.id = order_product.order_id
JOIN product 
    ON product.id = order_product.product_id
WHERE orders.status = 1
GROUP BY product.id
ORDER BY total_quantity DESC
LIMIT 100;

 

인덱스 적용 전

- 쿼리 실행 계획

- 쿼리 실행 시간 : 4s 712ms

 

인덱스 적용

이번엔 여러개의 Join 이 들어가다 보니 쿼리 실행 계획이 좀 복잡한 편이다. orders 와 product 의 PK 와 order_product 의 order_id, product_id 를 조인하는 부분에서는 디폴트로 생성된 PK 에 대한 인덱스가 있기 때문에 index 를 타고 있는 모습이다.

하지만 여기서 orders.status = 1 인 경우에는 인덱스가 존재하지 않기 때문에 오래 걸리고 있다. status 에 대한 인덱스를 걸어주면 성능 개선이 일어날 것으로 기대를 하고 인덱스를 적용해 보자!

 

인덱스 적용 후

- 쿼리 실행 계획

 

- 쿼리 실행 시간 : 4s 871ms

오히려 늘어난, 거의 성능에 영향을 주지 않는 모습이다.

 

사실 주어진 DB 에서 status 는 0 아니면 1이 대부분이므로, 카디널리티가 매우 낮은 상태이다. 그렇기 때문에 인덱스의 효과를 거의 보지 못하고 있다. 게다가 쿼리의 핵심이 되는 부분인 total_quantity 를 SUM() 함수로 계산해서 정렬을 하고 있기 때문에, 해당 컬럼은 임시 데이터로 생성되면서 인덱스를 탈수가 없다. 따라서 해당 쿼리에 대한 최적화는 이정도가 최선인 것으로 보인다.

 

해당 기능은 쿼리로 적용시키기 보다는 배치나 스케쥴러로 캐싱을 시켜놓는 편이 좋지 않을까 싶다.

 

 

결론

DB 인덱스를 적용함으로써 Full Scan 이 일어나던 부분은 Index Scan 을 하게 됨으로써 비용이 많이 줄어드는 모습을 볼 수 있었다. 하지만 데이터의 분포도에 따라 index 의 효율이 일어나지 않는 부분도 있고, 쿼리 중 임시 데이터로 생성되는 부분은 인덱스를 탈수 없는 형태로 변환되기 때문에 인덱스를 걸어도 의미가 없는 모습을 볼 수 있었다.

 

이처럼 인덱스는 무작정 적용시키기 보다는 예상되는 (또는 관측되는) 데이터의 경향을 보고 잘 적용하여야 한다. 인덱스로 성능 개선을 못하는 부분은 캐싱이나 배치 처리를 하는 방식으로 해결하는것도 좋은 방법으로 보인다.