Index로 조회 성능을 최적화 해보자(2)

 

이번 글 주제는 DB 인덱스를 저의 개인 E-Commece 프로젝트에 사용하여 성능을 비교 해보고 적절한 인덱스를 적용해보는 과정을 정리한 글입니다.

---

 

1. 신뢰성 있는 인덱스 성능 테스트를 위해

 

데이터베이스 성능 최적화를 진행할 때, 많은 개발자들이 "인덱스를 추가하면 쿼리 성능이 향상된다"는 기본적인 개념을 떠올립니다. 하지만 단순히 인덱스를 추가하고 실행 시간을 비교하는 방식은 신뢰할 수 있는 테스트라고 볼 수 있을까?

아니라면 어떻게 하면 보다 객관적이고 신뢰성 있는 성능 테스트를 진행할 수 있는 지에 대한 정리를 하고 성능 테스트에 적용해 볼 생각입니다.

 

1-1 실험에서 고려해야 할 변수들

정확한 성능 테스트를 위해서는 실험의 변수를 명확히 정의하는 것이 중요합니다.

 

📌 1) 독립 변수 (Independent Variable)

• 우리가 직접 변경하는 실험 조건입니다.
• 이번 테스트에서는 "인덱스의 유무"가 독립 변수가 됩니다.
• 예)
  • 인덱스를 적용한 경우
  • 인덱스를 적용하지 않은 경우

📌 2) 종속 변수 (Dependent Variable)

• 독립 변수(ex. 인덱스 유무)에 따라 변화하는 결과값입니다.
• 여기서는 "쿼리의 실행 시간 , EXPLAIN 정보 등"이 종속 변수가 됩니다.
• 즉, 우리가 인덱스를 적용했을 때와 적용하지 않았을 때 실행 시간 , EXPLAIN정보가 어떻게 변하는지를 측정하게 됩니다.

📌 3) 통제 변수 (Control Variable)

• 실험 결과에 영향을 줄 수 있는 기타 변수들을 최대한 일정하게 유지해야 합니다.
• 대표적인 통제 변수:
  • 실행하는 쿼리: 동일한 쿼리를 실행해야 합니다.
  • 데이터의 상태: 동일한 데이터셋을 사용해야 합니다.
  • DBMS 설정: 캐싱, 쿼리 최적화 전략 등 동일한 환경이어야 합니다.
  • 하드웨어 환경: CPU, 메모리, 스토리지 성능이 같은 환경에서 테스트해야 합니다.

특히 DBMS는 쿼리 캐싱을 수행하기 때문에, 같은 쿼리를 반복 실행하는 방식만으로는 정확한 성능 테스트가 어려울 수 있습니다.

 

1-2 성능 테스트 진행 계획

위에서 정리한 테스트 실험에서 고려해야 할 변수들을 적용하여 테스트를 진행해보자.

 

📌  실험 설계

1. 두 개의 독립된 DBMS 환경을 준비합니다.
   • 동일한 버전의 DBMS를 실행합니다.
   • 동일한 하드웨어 환경을 유지합니다.
     

     

2. 한쪽에는 인덱스를 적용하고, 다른 한쪽에는 적용하지 않습니다.
   • 이렇게 하면 인덱스가 유일한 차이점(독립 변수)이 됩니다.
3. 양쪽 환경에 동일한 조건을 설정합니다.
   • 동일한 데이터셋을 삽입합니다.
   • 동일한 쿼리를 실행합니다.
   • 동일한 설정(DBMS 설정값, 세션 변수 등)을 유지합니다.
4. 여러 번 테스트를 수행하고 평균 값을 분석합니다.
   • 단순 실행 시간이 아닌 통계적 분석을 진행하면 더 신뢰도 높은 결론을 도출할 수 있습니다.
   • 쿼리 실행 계획(EXPLAIN ANALYZE)을 활용하여 인덱스가 어떻게 사용되는지도 함께 확인하면 좋습니다.

 

 

 2 . 테스트 실행 후 비교 분석

 

2-1 인기 상품 조회

특정 기간동안의 가장 많이 팔린 상품 3개를 조회하는 쿼리입니다. 

현재 Cache를 활용하고 있지만 Evict 된 이후에 대용량 트래픽이 발생 시 Cache stampede 현상이 발생 할 수 있어 조회 쿼리의 성능을 최적화 하기 위해 Index 설정을 고려했습니다..  

 

✅ 데이터베이스 스키마

create table product
(
    created_at datetime(6)  not null,
    id         bigint auto_increment
        primary key,
    price      bigint       not null,
    updated_at datetime(6)  not null,
    name       varchar(255) null
);

create table order_product
(
    created_at datetime(6) not null,
    id         bigint auto_increment
        primary key,
    order_id   bigint      null,
    price      bigint      not null,
    product_id bigint      null,
    quantity   bigint      not null,
    updated_at datetime(6) not null
);

 

✅ 테스트 할 쿼리

# 인기 상품 조회
SELECT
    p.id,
    p.name,
    SUM(op.quantity) AS total_quantity
FROM order_product op
JOIN product p ON p.id = op.product_id
WHERE op.created_at between '?' and '?'
GROUP BY p.id
ORDER BY total_quantity DESC
limit 3;

 

✅ 인덱스 적용 후 성능 비교

 

📌 인덱스 선택

1. (created_at);

2. (created_at , product_id);

 

 

위의 두가지 인덱스를 설정해서 진행해 본 결과 두 방법의 성능 , 속도 차이가 조회쿼리에서 범위 조건 뒤에 인덱스는 큰 의미가 없다고 판단하여 1번의 방법으로만 성능비교를 작성합니다!

 

📌 Explain 성능 비교

데이터 크기	인덱스 적용	쿼리 실행 시간(ms)	필터링된 행 수	스캔 방식	인덱스 사용 여부
100만 개	적용 전	756.0	21	Table scan	X
100만 개	적용 후	0.175	21	Index range scan	O
10만 개	적용 전	64.9	21	Table scan	X
10만 개	적용 후	0.131	21	Index range scan	O

 

📌 테스트하면서 생겼던 이슈들

1. 더미 데이터의 모수가 너무 작아도... 옵티마이저가 Full scan을 타는 문제가 발생..
2. 중복도가 현저히 낮아도 (ex. 초단위시간) 옵티마이저가 Full scan을 타는 문제 발생..

• 해결방안
  • 시간 단위별로 분포도를 균등하게 재설정하니 range scan을 탔습니다! 
  • 추후 실무에서 적용 시에도 실제 데이터를 분석하여 인덱스 설정을 진행해야겠습니다.

 

 

2-2 유저별 주문 조회 

E-commerce에서 관리자에서 주문을 조건별로 조회는 자주 사용되며 대용량의 데이터를 조회하는 부분입니다. 물론 입력(쓰기) 도 많지만 조회에서의 성능 최적화를 하는 것이 Index의 입력에서의 성능 이슈보다 좋은 효율을 가져온다고 판단하였습니다. (저의 개념적인 생각일뿐.. 실무 기반은.. 아닙니다.ㅠㅠ)   

 

✅ 데이터베이스 스키마

create table orders
(
    created_at       datetime(6)                                                     not null,
    id               bigint auto_increment
        primary key,
    issued_coupon_id bigint                                                          null,
    total_amount     bigint                                                          not null,
    updated_at       datetime(6)                                                     not null,
    user_id          bigint                                                          null,
    status           enum ('PAYMENT_COMPLETED', 'PAYMENT_FAILED', 'PAYMENT_PENDING') null,
        unique (issued_coupon_id)
);

 

✅ 테스트 할 쿼리

#적합한 인덱스 설정 순서와 맞게 쿼리 선택
select *
from orders
where status = ?      
and user_id = ?;

또는 
select *
from orders
where user_id = ?      
and status = ?;

 

✅ 인덱스 적용 후 성능 비교

 

📌 인덱스 선택

1. (status, user_id);

2. (user_id, status);

3. (user_id);

1. user_id, status 중에 무엇이 더 인덱스 설정에 적합할까? 
   • 물론 중복도는 status가 더 높을 수 있다. 하지만 " status의 중복되는 행의 개수가 많든 적든, 문제는 값의 개수가 단 3개밖에 없다는 점" 입니다. 
   • 중복이 많다고 무조건 인덱스에서 먼저 오는 것이 좋은 것이 아니라, "선택도가 얼마나 좋은가"가 더 중요할 수 있습니다. 그러므로 status의 값이 단 3개뿐이라면, 데이터를 필터링 하는 효과가 낮을 수 있다.
   • orders 테이블이 100만 건이고 , status가 단 3개이므로 만약 각 상태별로 333,333씩 데이터가 있다면 필터링을 해도 333,333개가 남아 있기 때문에 필터링의 효과가 낮습니다.
   • 그래서 user_id 와 status 중에는 user_id 가 더 적합한 인덱스 컬럼이라고 판단 가능합니다.
     
     
2. 그렇다면 (user_id) 만 하는 것과 (user_id , status) 중에는 무엇이 더 적합할까?
   
   • (user_id) 인덱스는 user_id로 인한 필터링을 해주고 status 필터링은 Using where로 필터링을 합니다.
   • 하지만, (user_id , status) 인덱스는 user_id로 필터링을 해준 이후 Using where 없이 인덱스를 통해status의 값도 필터링이 가능합니다.
   • 결과적으로 (user_id , status) 인덱스가 RANGE SCAN의 속도가 더 빨라지는 효과를 발휘할 수 있다고 판단 가능합니다.
     

 

📌 인덱스의 "선택도(Selectivity)"란?

 

선택도란 = "해당 컬럼이 전체 데이터에서 얼마나 잘 필터링 될 수 있는가"를 나타내는 의미입니다. 즉, 인덱스에서

특정 값을 찾았을 때 얼마나 빨리 원하는 데이터를 걸러낼 수 있는지를 의미합니다!

 

 

📌 Explain 성능 비교

 

[10만 데이터 기준]

인덱스 적용 방식	쿼리 실행 시간 (ms)	스캔 방식	인덱스 사용 여부
적용 전	48	Table scan	X
(status , user_id)	0.01	Index lookup	O
(user_id, status)	0.009	Index lookup	O
(user_id)	0.01	Index lookup	O

 

[100만 데이터 기준]

인덱스 적용 방식	쿼리 실행 시간 (ms)	스캔 방식	인덱스 사용 여부
적용 전	460	Table scan	X
(status , user_id)	0.09	Index lookup	O
(user_id, status)	0.05	Index lookup	O
(user_id)	0.09	Index lookup	O

 

예상했던 (user_id , status) 인덱스가 좋은 성능을 보여주는 결과를 확인 할 수 있었습니다.

ps.) 사실 인덱스끼리의 결과 차이가.. 생각보다 크진 않았습니다.

 

 

 

3. 정리 글

 

• 인덱스를 무작정 추가하는 것이 아니라, 데이터 분포와 WHERE 절 조건을 고려하여 적절한 순서로 설정 해야 좋은 효과를 나타냅니다.
• 대량의 데이터를 다룰 경우, 인덱스 최적화가 없으면 FULL SCAN이 발생하여 성능이 크게 저하될 수 있으므로, 인덱스 활용 여부를 반드시 EXPLAIN으로 확인해야 합니다!!