부하를 적절하게 축소하기 (1)

1. DB Query Optimization

다량의 트래픽이 유입되는 경우 데이터의 조회 쿼리 성능은 인덱스와 함께 하느냐, 아니냐에 따라서 성능은 하늘과 땅 차이.

 

인덱스는 조회 성능을 높일 수 있지만 다음과 같은 사항들이 고려되어 설계되어야 함.

• 한번에 찾을 수 있는 값 - 데이터 중복이 적은 컬럼
• 인덱스 재정렬 최소화 - 데이터 삽입, 수정이 적은 컬럼
• 인덱스의 목적은 검색 - 조회에 자주 사용되는 컬럼
• 너무 많지 않은 인덱스 (약 3~4개) - 인덱스 또한 공간을 차지함

인덱스 Column의 기준

인덱스 대상 컬럼의 핵심은 높은 카디널리티(Cardinality)

 - 카디널리티: 데이터의 중복 수치

사용자 테이블 (이름, 성별, 나이, 계좌번호, .. ) 있다.
만약 인덱스 설정을 통해 사용자 조회 성능을 높이려고 할 때,
(1) 나이 → 연령대가 많이 차이나지 않으므로 많은 데이터를 걸러낼 수 없음. 
(2) 성별 → 50% 의 데이터만 걸러낼 수 있으므로 성별 기준 전체 조회가 아니면 유의미하지 않음.
(3) 이름, 계좌번호 → 중복값이 거의 없으므로 검색 조건과 일치하지 않는 데이터 대부분을 걸러낼 수 있음.

 

여러 컬럼을 조합한 Index

• 여러 컬럼으로 이루어진 인덱스에서는 일반적으로 카디널리티가 높은 순으로 배치

CREATE INDEX IDX_P_STATUS ON ORDER_ITEM (product_id, order_status)

 

테이블 예시

• 반드시 첫번째 인덱스 조건은 조회 조건 중 하나에 포함되어야 한다.
• 복합 Index의 경우 조건 순서와 일치시킬수록 높은 성능을 가진다.

index를 이용한 조회 유의사항

1. 인덱스 컬럼의 값과 타입을 그대로 사용할 것 (인덱스는 컬럼의 값만 알고 있음)

## price 컬럼에 Index 가 적용된 경우

# 올바른 인덱스 사용
where price > 10000 / 100; // price 컬럼에 대한 Index Search

## 잘못된 인덱스 사용
where price * 100 > 10000; // price * 100 에 대한 Index X

 

2. LIKE, BETWEEN, <, > 등 범위 조건의 컬럼은 Index가 적용되나 그 뒤 컬럼은 Index 적용 x

## Index(product_id, ordered_at, count, is_abroad) is_abroad : 해외 배송 여부
where product_id = 1 and is_abroad = 1 and ordered_at > '2024-01-01'
## product_id = Index
## ordered_at = Index
## is_abroad = Index X


# NOTE: datetime, id 처럼 카디널리티가 높은(데이터중복도가 낮은) 필드 이후에는
#       복합인덱스를 추가해도 의미 없는 인덱스가 될 확률이 매우 높음

 

3. AND는 ROW를 줄이지만 OR은 비교를 위해 ROW를 늘리므로 Full-Scan 발생 확률이 높다.

 - WHERE 절에서 OR 연상을 사용할 때는 이를 고려해야한다.

 

4. =, IN은 다음 컬럼도 인덱스를 사용한다.

 - IN 은 = 연산을 여러번 수행한 것이므로 다음 컬럼도 인덱스를 태울 수 있음.

 

Covering Index

• 앞서 배운 것처럼 조회하고자 하는 모든 컬럼이 조회 대상, 조회 조건에 포함된다면 데이터를 찾기 위해 ROW 에 접근할 필요가 없으므로 높은 성능의 조회를 달성할 수 있음

인덱스는 Read 연산의 효율을 높이지만 CUD 연산에서는 오버헤드가 발생할 수 있다.

 

Query 실행 계획 보는법

---

2. 이커머스 시나리오에서의 복잡한 Query

이커머스 시나리오에서 자주 조회하는 쿼리, 복잡한 쿼리를 가진 인기 상품 통계의 성능을 개선해보고자 한다.

 

2.1. 인기 상품 통계 선정 이유

• 이커머스 서비스에서 제공하는 기능 중 상위 상품 목록 조회 API 응답 속도가 785ms

• 상위 상품 목록 조회 API 응답 속도가 느린 원인 중 하나가 인기 상품 통계 쿼리 수행 시간 775ms

인기 상품 통계 쿼리 수행시간

• 상위 상품 목록 조회 API의 응답 속도의 98%를 인기 상품 통계 쿼리가 차지

---

2.2. 인기 상품 통계 쿼리

• 인기 상품 통계 쿼리는 최근 3일 동안 가장 많이 판매 상품 5개를 추출하는 통계 쿼리

인기 상품 통계쿼리

인기 상품 통계쿼리 실행계획

---

2.3. 쿼리 실행 계획 (Explain) 및 원인

• 위 사진의 쿼리 실행 계획에서 tb_order_item AS oi1_0 테이블 행의 type 컬럼의 값이 ALL 출력되고 있다.
• type 컬럼의 값이 ALL이라는 것은 풀 테이블 스캔이 발생하고 있다는 것으로 현재 테이블에 존재하는 398,025 행을 순차적으로 읽기 때문에 쿼리의 속도가 느린것이 원인

---

3. 인덱스 추가

3.1. 해당 Query에서 사용되는 해당 컬럼에 Index 추가하는 타당한 이유 작성

• 인기 상품 통계 쿼리의 WHERE 절, JOIN 절, 및 GROUP BY 에 포함되는 product_id, reg_date, order_id 컬럼들을 인덱스 생성에 선정하여 쿼리 성능을 향상 시키려 한다.
• 카디널리티가 높은 순으로 product_id > reg_date > order_id 컬럼 순으로 정렬하여 인덱스를 생성

-- tb_order_item 테이블에 복합 인덱스 추가 (product_id 포함)
CREATE INDEX idx_tb_order_item_product_id_reg_date_order_id ON tb_order_item (product_id, reg_date, order_id);

 

 

3.2. 쿼리 실행 계획 (Explain)

인덱스 추가 후 인기 상품 통계 쿼리의 실행계획

 

3.3. 상위 상품 목록 조회 API 전/후 속도 비교

• 인덱스 추가 후 API 응답과 쿼리 수행시간 캡처 화면

API 응답 시간

인기 상품 통계 쿼리 수행시간

인덱스 적용 전

• API 응답 시간: 785ms 
• 쿼리 수행 시간: 775ms

인덱스 적용 후

• API 응답 시간: 467ms 
• 쿼리 수행 시간: 461ms

성능 개선 차이 = 적용 전 평균 소요시간 - 적용 후 평균 소요시간

성능 개선 차이 = 785 - 467 = 318

성능 개선 백분율 = (성능 개선 차이 / 적용 전 소요시간) * 100

성능 개선 백분율 = (318 / 785) * 100 = 40.50955414%

 

인덱스 추가를 통해 상위 상품 목록 조회에 대한 소요 시간을 785ms -> 467ms 응답 시간을 단축해보았다. 

---

4. 더 개선이 필요한 부분

1. 다른 기능들에서 수행되는 쿼리 개선과 카디널리티 계산해보기 (상품 목록 조회, 장바구니 목록 조회)