close
프로필 배경
프로필 로고

개발이야기

Database · 2024. 5. 10. fullscreen 넓게보기

[E-commerce] 쿼리 분석 및 인덱스 설계를 통한 성능 개선

인덱스(Index)

인덱스는 책의 목차와 비슷하다고 생각하면 편하다.

책의 목차를 보고 어디에 무엇이 있는지 바로 확인할 수 있는 것 처럼 인덱스도 바로 찾고자 하는 데이터를 찾을 수 있다.

 

목차도 "ㄱ", "ㄴ" 순으로 정렬되어 있는 것 처럼 인덱스도 정렬되어 보관이 된다.

그래서 미리 정렬된 상태에서 새로운 인덱스를 추가,삭제, 변경하는 작업은 느리지만 조회(SELECT)하는 작업은 빠르게 처리 가능하다.

 

즉, 인덱스는 데이터 저장 성능을 희생하고 데이터의 읽기 속도를 높이는 기능이다.

B-Tree

B-Tree는 칼럼의 원래 값을 변형시키지 않고 인덱스 구조체 내에서는 항상 정렬된 상태로 유지한다.
B-Tree는 트리 구조이며, 최상상위에 "루트 노드(Root node)"하나가 존재하며, 하위에 자식 노드가 붙어 있는 형태이다.
가장 하위에 존재하는 노드를 "리프 노드(Leaf node)"라 하고, 루트 노드도 아니고 리프 노드도 아닌 중간의 노드를 "브랜치 노드(Branch node)"라고 한다.

DB에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 갖고 있다.

B-Tree 인덱스 키 추가 및 삭제

B-Tree에 새로운 키값이 저장될 때, 성능 저하가 발생하는 이유

새로운 키값을 B-Tree에 저장하기 위해 리프 노드에 레코드의 키 값과 실제 데이터의 주소 정보를 저장을 하게 되는데, 만일 리프 노드가 꽉 찼을 경우 리프 노드가 분리되어야 한다. 이 작업은 상위 노드인 브랜치 노드까지 작업 영향을 미치기 때문에 쓰기 작업에 비용이 많이 든다.

B-Tree에 키 값이 삭제되는 경우는 해당 키 값이 저장된 리프 노드를 찾아 삭제 마크만 하면 된다.
삭제 마킹이 된 인덱스 키 공간은 방치 하거나 재활용할 수 있다. 
마킹하는 작업 또한 디스크 쓰기가 필요하므로 디스크I/O가 발생한다.

DB의 성능 개선은 어떻게 디스크 I/O를 줄이느냐가 관건이다.

인덱스 키 검색

인덱스를 검색하는 작업은 B-Tree의 루프 노드 -> 브랜치 노드 -> 리프 노드 순으로 이동하면서 비교 작업을 수행한다.

-> 이 과정을 "트리 탐색"이라고 한다.

B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용할 수 있다.
부등호 비교 조건에서도 인덱스를 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 사용할 수 없다.

 

인덱스 설계 기준

Case 1 쓰기, 수정, 삭제가 적은 칼럼

  • B-Tree 구조의 인덱스에서 새로운 키가 생성되거나 삭제되는 작업은 추가적으로 리프 노드가 분리되거나, 삭제된 인덱스 키를 마킹하기 위한 디스크 I/O가 필요한 작업이 필요하기 때문에 성능이 떨어진다.
  • 수정은 단순히 키 값이 변경되는 것이 아니라 먼저 키 값을 삭제 후 새로운 키 값을 추가하는 작업이기 때문에 비효율적이다.

Case 2 카디널리티가 높은 (중복도가 낮은) 칼럼

  • 카디널리티가 낮은 칼럼에 인덱스를 걸게 되면 인덱스가 탐색하는 범위가 많아져 최악의 경우 인덱스를 찾고 실제 데이터 테이블에 접근해서 가져오는 것 보다 차라리 인덱스에 접근하지 않고 테이블을 풀 스캔 하는게 나을수도 있다.
  • 즉, 최대한 인덱스로 많은 데이터를 걸러내야 최대의 조회 효율을 뽑아낼 수 있다.

Case 3 where 절에 자주 사용되는 칼럼

  • `WHERE` 절에서 자주 사용되는 칼럼에 인덱스를 생성하면, 해당 조건으로 데이터를 필터링하여 찾기 때문에 풀 테이블 스캔을 피할 수 있다. (=효율적으로 데이터를 찾을 수 있다.)

Case 4 집계 쿼리에 자주 사용되는 칼럼

  • 집계 함수(SUM, AVG, MAX, MIN 등등)에 사용되는 칼럼에 인덱스를 걸면, 연산을 빠르게 수행할 수 있다. 
    e.g. 월별, 연별로 상품의 매출을 집계하는 경우 (product_id와 sales_date 칼럼에 인덱스를 생성)
SELECT product_id, COUNT(*)
FROM sales
WHERE sales_date BETWEEN '2024-04-01' AND '2024-04-30'
GROUP BY product_id;


sales_date 인덱스로 24년 4월 한달간 데이터만 가져와 product_id 인덱스 값을 기준으로 그룹 하여 집계한다.
만일 인덱스가 존재하지 않다면 `sales` 테이블을 풀 스캔해야 하므로, 성능 저하가 발생할 수 있다.

여러 컬럼으로 인덱스를 구성할 경우 순서

  • 카디널리티가 높은 순 -> 낮은 순으로 구성

인덱스 조회 시 주의사항

  • 여러 컬럼으로 인덱스 구성 시 첫번째 인덱스 조건은 조회 조건에 포함되어야 한다
  • 컬럼 값을 연산(or 가공)하지 않고 그대로 사용해야 인덱스가 유효하다.
    예를 들어 quantity 컬럼을 인덱스 걸었을 때, `where quantity * 2 < 100;` 이 쿼리는 인덱스를 사용하지 못하지만 `where quantity < 100 * 2;` 이 쿼리는 인덱스를 사용한다.
  • LIKE로 뒷부분이 일치하는 것을 찾는 쿼리는 인덱스를 사용하지 못한다.
    e.g. '%완료' -> 인덱스 X, '완료%' -> 인덱스 O

 

현재 시나리오에서 실행되는 쿼리 분석 및 인덱스 설계

우선 각 테이블의 PK값은 클러스터드 인덱스로 별도의 인덱스를 설정해줄 필요가 없음.

User

  • 유저 테이블
{
    id            BIGINT  (PK)
    name          VARCHAR
    address       VARCHAR
    phone_number  VARCHAR
    point         BIGINT
    created_at    DATETIME
    updated_at    DATETIME
}
  • 사용자 조회 쿼리
SELECT *
FROM users
WHERE id = {userId}
  • 포인트 업데이트 쿼리
UPDATE users
SET point = {point}
WHERE id = {userId}

 

users 테이블 인덱스 설계

  • 사용자 조회 시 user 테이블의 pk인 id 값으로 조회하기 때문에 별도의 인덱스 설정을 하지 않음.
  • 포인트 업데이트는 주문 결제시 마다 일어나는 수정 쿼리이고 조회 조건으로 사용되지 않기 때문에 point에 인덱스를 거는것은 비효율적이라고 판단하여 인덱스를 설정하지 않음. (Case1, 3번에 부합하지 않음)

Product

  • 상품 테이블
{
    id             BIGINT
    name           VARCHAR
    price          BIGINT
    description    VARCHAR
    stock_quantity BIGINT
    created_at     DATETIME
    updated_at     DATETIME
}
  • 단일 상품 조회 쿼리
SELECT *
FROM products
WHERE id = {productId}
  • 상품 리스트 조회 쿼리
SELECT * 
FROM products
  • 상품 재고 업데이트 쿼리
UPDATE products
SET stock_quantity = {stockQuantity}
WHERE id = {productId}
  • 최근 3일간 인기 판매 상품 조회 쿼리
SELECT *
FROM products AS p
JOIN order_items AS oi ON p.id = oi.product_id
JOIN orders AS o ON o.id = oi.order_id
WHERE o.order_status = {orderStatus} AND o.ordered_at BETWEEN {startDate} AND {endDate}
GROUP BY p.id
ORDER BY SUM(oi.quantity) DESC
LIMIT 5

 

prodcuts 테이블 인덱스 설계

  • PK값인 id 를 제외하고 다른 칼럼은 조회 조건으로 사용되지 않기 때문에 인덱스를 설정하지 않음
  • 최근 3일간 인기 판매 상품을 효율적으로 조회하기 위해서는 join하는 order 테이블 또는 order_item 테이블의 인덱스를 설정

Stock

  • 재고 테이블
{
    id             BIGINT
    product_id.    BIGINT
    stock_quantity BIGINT
    created_at     DATETIME
    updated_at     DATETIME
}
  • 재고 조회 쿼리 (id 기반)
SELECT *
FROM stocks
WHERE id = {stockId}
  • 재고 조회 쿼리 (product_id 기반)
SELECT *
FROM stocks
WHERE product_id = {productId}
  • 재고 수량 업데이트 쿼리
UPDATE stocks
SET stock_quantity = {stockQuantity}
WHERE id = {stockId}

 

stocks 테이블 인덱스 설계

  • product_id 칼럼 인덱스 : 만일 상품이 자주 등록되고 삭제가 되는 서비스 라면 product_id에 인덱스를 거는건 비효율적으로 판단이 되지만, 그렇지 않다면 product_id 를 기반으로 stock을 조회하는 빈도가 많을 것으로 생각되어 product_id에 인덱스를 걸어 조회 성능을 향상시킬 수 있을 것 같다.
    상품 등록과 삭제 작업보다 조회하는 작업이 더 빈번할 것으로 판단되고, 카디널리티도 높기 때문에 product_id에 인덱스 설정(Case 2, 3 부합)
  • stock_quantity 칼럼 인덱스 : 재고를 수정하는 쿼리에는 사용되지만 조회 조건에는 사용되지 않기 때문에 인덱스를 설정하는 건 비효율적으로 판단 (Case 1, 3 부합하지 않음)

Order

  • 주문 테이블
{
    id             BIGINT
    user_id        BIGINT
    pay_amount     BIGINT
    receiver_name  VARCHAR
    address        VARCHAR
    phone_number   VARCHAR
    order_status   VARCHAR
    order_date     DATETIME
    created_at     DATETIME
    updated_at     DATETIME
}
  • 주문 조회 쿼리
SELECT *
FROM orders
WHERE id = {orderId}
  • 주문 생성 쿼리
INSERT INTO order (user_id, pay_amount, receiver_name, address, phone_number, status, order_date, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  • 주문 상태 업데이트 쿼리
UPDATE orders
SET order_tatus = {orderStatus}
WHERE id = {orderId}


order 테이블 인덱스 설계

  • user_id 칼럼 인덱스 : 현재 쿼리에는 없지만 user_id 기반으로 사용자별 주문 내역을 조회하는 경우 카디널리티가 매우 높기 때문에 인덱스 설정하는것이 좋아보이나, (Case 2, 3에 부합) 이커머스 서비스 특성 상 주문이 생성되는 빈도가 매우 높기 때문에 인덱스를 설정하는 건 비효율적이라고 생각이 된다. (Case 1에 부합하지 않음)
    만일 주문 내역을 조회하는 비즈니스가 중요하다면 user_id에 인덱스를 설정하는것도 고려해볼만 하다. (Case 1에 부합하지 않음)
  • order_status 칼럼과 ordered_at 칼럼은 최근 3일간 인기 판매 상품 조회 쿼리를 위해 Join하여 조회 조건으로 사용된다. 인기 판매 상품 쿼리는 빈번하기 때문에 두 칼럼을 인덱스로 설정하는 것이 조회 성능을 향상시킬 수 있다고 판단
    두 칼럼의 인덱스 순서는 카디널리티가 더 높은 ordered_at 을 첫 번째 인덱스로 사용하여 주어진 날짜 범위에 필터링을 먼저 하고, order_status 인덱스는 이 범위 내에서 필터링하여 조회 성능을 높일 수 있을것으로 판단 (Case2, 3에 부합) 

 

OrderItem

  • 주문 아이템 테이블
{
    id             BIGINT
    order_id       BIGINT
    product_id     BIGINT
    product_name   VARCHAR
    unit_price     BIGINT
    total_price    BIGINT
    quantity       BIGINT
    status         VARCHAR
    created_at     DATETIME
    updated_at     DATETIME
}
  • 주문 아이템 생성 쿼리
INSERT INTO order_items (order_id, product_id, product_name, unit_price, total_price, quantity, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  • 주문 상품 조회 쿼리
SELECT *
FROM order_items
WHERE order_id = {orderId}
  • 주문 아이템 상태 업데이트 쿼리
UPDATE order_items
SET status = {status}
WHERE id = {orderItemId}


order_item 테이블 인덱스 설계

  • order_id 칼럼 인덱스 : order_id 기반으로 조회 쿼리가 많이 발생 하지만 주문 생성이 빈번하기 때문에 order_id를 인덱스로 설정하는 건 비효율적이라고 판단 (Case 1에 부합하지 않음)
  • 나머지 칼럼은 조회 조건으로 사용되지 않을 뿐더러 주문 아이템이 생성 쿼리가 빈번하기 때문에 인덱스를 설정하지 않음

 

Payment

  • 결제 테이블
{
    id             BIGINT
    order_id       BIGINT
    pay_amount     BIGINT
    payment_method VARCHAR
    created_at     DATETIME
    updated_at     DATETIME
}
  • 결제 생성 쿼리
INSERT INTO payments (order_id, pay_amount, payment_method, created_at, updated_at)  
values (?, ?, ?, ?, ?);


payment 테이블 인덱스 설계

  • 주문과 마찬가지로 결제 내역 조회 쿼리가 많이 발생할것으로 예상되지만 결제 생성이 빈번한것으로 판단되어 해당 테이블에는 인덱스를 설정하지 않음

Cart

  • 장바구니 테이블
{
    id             BIGINT
    user_id        BIGINT
    created_at     DATETIME
    updated_at     DATETIME
}
  • 장바구니 조회 쿼리
SELECT *
FROM carts
WHERE user_id = {userId}
  • 장바구니 생성 쿼리
INSERT INTO carts (user_id, created_at, updated_at)  
VALUES (?, ?, ?);


cart 테이블 인덱스 설계

  • user_id 칼럼 인덱스 : user_id 기반으로 조회 작업이 빈번하고, 카디널리티가 높다. 그리고 장바구니는 동일 사용자에 대해 한번 생성되면 다시 생성되거나 삭제되지 않기 때문에 인덱스 설정 (Case 2, 3 부합)

CartItem

  • 장바구니 아이템 테이블
{
    id            BIGINT
    cart_id       BIGINT
    product_id    BIGINT
    quantity      BIGINT
    deleted       BIT
    created_at    DATETIME
    updated_at    DATETIME
}
  • 장바구니 아이템 조회 (cart_id 기반) 쿼리
SELECT *
FROM cart_items
WHERE cart_id = {cartId}
  • 장바구니 아이템 조회 (cart_id, product_id 기반) 쿼리
SELECT *
FROM cart_items
WHERE cart_id = {cartId} AND product_id = {productId}
  • 장바구니 아이템 생성 쿼리
INSERT INTO cart_items (cart_id, product_id, quantity, deleted, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?);
  • 장바구니 아이템 삭제 여부 업데이트 쿼리
UPDATE order_items
SET deleted = {deleted}
WHERE id = {orderItemId}

cart_item 테이블 인덱스 설계

  • cart_item은 장바구니 조회 시 장바구니에 담긴 상품을 cart_id, product_id 등등 조회 쿼리가 많지만  장바구니에 아이템을 추가하는 작업은 빈번하기 때문에 인덱스 설정은 비효율적을 판단 (Case 1에 부합하지 않음)

 

성능 개선 확인

order테이블에 인덱스를 설정(ordered_at, order_status)하고 인기 판매 상품 조회 쿼리의 성능 개선정도를 확인해 보기 위해 order 데이터 10,000개 orderItem 데이터 10,000개의 더미 데이터를 삽입하고 인기 상품 쿼리를 explain 해봤다.

 

인덱스 적용 전 EXPLAIN

-> Limit: 5 row(s)  (actual time=47.7..47.7 rows=5 loops=1)
    -> Sort: SUM(oi.quantity) DESC, limit input to 5 row(s) per chunk  (actual time=47.7..47.7 rows=5 loops=1)
        -> Table scan on <temporary>  (actual time=47.4..47.5 rows=362 loops=1)
            -> Aggregate using temporary table  (actual time=47.4..47.4 rows=362 loops=1)
                -> Nested loop inner join  (cost=7791 rows=485) (actual time=0.381..45.1 rows=667 loops=1)
                    -> Nested loop inner join  (cost=4393 rows=9709) (actual time=0.198..23.2 rows=10036 loops=1)
                        -> Filter: ((oi.product_id is not null) and (oi.order_id is not null))  (cost=995 rows=9709) (actual time=0.176..8 rows=10036 loops=1)
                            -> Table scan on oi  (cost=995 rows=9709) (actual time=0.173..4.72 rows=10036 loops=1)
                        -> Single-row index lookup on p using PRIMARY (id=oi.product_id)  (cost=0.25 rows=1) (actual time=0.0012..0.00124 rows=1 loops=10036)
                    -> Filter: ((o.order_status = 'PAID') and (o.ordered_at between '2024-05-06' and '2024-05-09'))  (cost=0.25 rows=0.05) (actual time=0.00206..0.00207 rows=0.0665 loops=10036)
                        -> Single-row index lookup on o using PRIMARY (id=oi.order_id)  (cost=0.25 rows=1) (actual time=0.00174..0.00177 rows=0.746 loops=10036)

 

인덱스 적용 후 EXPLAIN

-> Limit: 5 row(s)  (actual time=41.1..41.1 rows=5 loops=1)
    -> Sort: SUM(oi.quantity) DESC, limit input to 5 row(s) per chunk  (actual time=41.1..41.1 rows=5 loops=1)
        -> Table scan on <temporary>  (actual time=40.9..41 rows=362 loops=1)
            -> Aggregate using temporary table  (actual time=40.9..40.9 rows=362 loops=1)
                -> Nested loop inner join  (cost=7791 rows=809) (actual time=0.236..38.8 rows=667 loops=1)
                    -> Nested loop inner join  (cost=4393 rows=9709) (actual time=0.116..18 rows=10036 loops=1)
                        -> Filter: ((oi.product_id is not null) and (oi.order_id is not null))  (cost=995 rows=9709) (actual time=0.0953..5.24 rows=10036 loops=1)
                            -> Table scan on oi  (cost=995 rows=9709) (actual time=0.0937..4.33 rows=10036 loops=1)
                        -> Single-row index lookup on p using PRIMARY (id=oi.product_id)  (cost=0.25 rows=1) (actual time=0.00108..0.00111 rows=1 loops=10036)
                    -> Filter: ((o.order_status = 'PAID') and (o.ordered_at between '2024-05-06' and '2024-05-09'))  (cost=0.25 rows=0.0833) (actual time=0.00197..0.00198 rows=0.0665 loops=10036)
                        -> Single-row index lookup on o using PRIMARY (id=oi.order_id)  (cost=0.25 rows=1) (actual time=0.00168..0.0017 rows=0.746 loops=10036)

 

 

인덱스 적용 전과 후의 비교

총 실행 시간

  • 인덱스 적용 전: actual time=47.7..47.7
  • 인덱스 적용 후: actual time=41.1..41.1

인덱스 적용 후 쿼리의 총 실행 시간이 약간 단축되었다.    

Nested loop inner join

  • 인덱스 적용 전: actual time=0.381..45.1
  • 인덱스 적용 후: actual time=0.236..38.8

인덱스 적용 후 내부 조인의 실행 시간이 감소 하였다.

 

 

'Database' 카테고리의 다른 글

Connection을 미리 생성하는 이유  (0) 2024.05.21
[E-commerce] 캐시를 통한 애플리케이션 성능 개선  (1) 2024.05.10
[E-commerce] 동시성 문제 해결하기 (비관적 락, 네임드 락, 분산 락)  (0) 2024.05.01
함수적 종속성 (FD, Functional Dependency)  (0) 2024.04.14
[OpenSearch] OpenSearchClient와 OpenSearch Query DSL 이용하기  (0) 2023.07.29
Database 관련 글
더 보기

티스토리툴바