인덱스를 이용하여 50만건의 데이터 빠르게 조회하기 + k6를 이용한 성능 테스트

상품 데이터가 약 50만건이 넘어가고 나면 정렬 조건을 바꿀 때마다 수 초의 시간이 걸린다. 만약 해당 쿼리 조건이 복잡하면 복잡해질수록 걸리는 시간은 더욱 길어진다. 

 

나 또한 10건 정도로 생각하고 코드를 테스트 해볼 때엔 해당 문제를 직면하지 못했으나, 50만건의 데이터를 넣은 후엔 Swagger에서 조회할 때 엄청난 시간이 걸리는 것을 확인하였다.  

 

참고로 상품 데이터는 DataInitializer를 통해 상품 CSV 파일을 Import 하였다. 

 

@Slf4j
@Component
@Profile({"data"})
@ConditionalOnProperty(name = "seed.enabled", havingValue = "true")
@RequiredArgsConstructor
public class DataInitializer implements CommandLineRunner {

    private final ProductRepository productRepository;
    private final BrandRepository brandRepository;

    @Override
    @Transactional
    public void run(String... args) throws Exception {
        final int targetCount = 500000;
        List<Product> existing = productRepository.findAll();
        if (!existing.isEmpty()) {
            return;
        }

        // 기본 브랜드 3개를 선행 생성
        brandRepository.save(Brand.builder().name("나이키").build());
        brandRepository.save(Brand.builder().name("퓨마").build());
        brandRepository.save(Brand.builder().name("아디다스").build());

        ClassPathResource resource = new ClassPathResource("ProductList.csv");
        if (!resource.exists()) {
            return;
        }

        try (BufferedReader reader = new BufferedReader(
            new InputStreamReader(resource.getInputStream(), StandardCharsets.UTF_8))) {
            reader.readLine();
            String line;
            List<String[]> templates = new ArrayList<>();
            while ((line = reader.readLine()) != null) {
                if (line.isBlank()) continue;
                String[] cols = line.split(",");
                if (cols.length < 4) continue;
                templates.add(cols);
            }

            if (templates.isEmpty()) {
                log.warn("DataInitializer: ProductList.csv has no data rows, skip.");
                return;
            }

            int saved = 0;
            int round = 0;
            while (saved < targetCount) {
                for (String[] cols : templates) {
                    if (saved >= targetCount) break;
                    Long brandId = Long.parseLong(cols[0].trim());
                    String baseName = cols[1].trim();
                    BigDecimal priceAmount = new BigDecimal(cols[2].trim());
                    int stockQuantity = Integer.parseInt(cols[3].trim());
                    Long likeCount = (cols.length >= 5 && !cols[4].isBlank())
                        ? Long.parseLong(cols[4].trim())
                        : 0L;

                    String name = baseName + " #" + (saved + 1);

                    Product product = Product.builder()
                        .brandId(brandId)
                        .name(name)
                        .price(new Money(priceAmount))
                        .stockQuantity(new Stock(stockQuantity))
                        .likeCount(likeCount)
                        .build();
                    productRepository.save(product);
                    saved++;
                }
                round++;
                if (round % 50 == 0) {
                }
            }
        } catch (Exception e) {
            throw e;
        }
    }
}

 

DB 조회 시간을 단축 시키기 위해 일반적으론 인덱스를 생성하면 되는 것으로 배웠다. 

나는 그 중에서도 단일 인덱스를 넣을 것인지 복합 인덱스를 생성할 것인지 판단하기 위해 우선 조회 쿼리를 상세하게 파해쳐보았다. 

 

최신순(LATEST) 상품 조회 쿼리

쿼리 EXPLAIN

인덱스 설계 시 주의할 점

• 자주 변경되는 컬럼에 인덱스를 남발하면 쓰기 성능 저하
• 조건 순서, 필터/정렬 조합을 고려해 복합 인덱스를 설계해야 함
• 모수가 작을 땐 오히려 인덱스 없이 Full Scan이 더 빠를 수도 있음

우선 Where 조건에서 가장 먼저 쓰이는 brand_id를 인덱스에 가장 먼저 넣어주었야 했다. 왜냐면 이 조건이 가장 강력한 카디널리티 감소 요인이기 때문이다. 즉 이 브랜드 레코드 범위로 빠르게 제한을 해주기에 row수가 상당히 줄어든다. 

 

또한 최신순은 created_at이 결정 짓는데, 이 created_at이라는 컬럼은 시간을 나타내기에 중복이 가능하여, id를 추가 정렬 기준으로 갖는다. 결국 "created_at DESC → id DESC" 를 인덱스에서 자연스럽게 제공해야 MySQL이 별도 FileSort 없이 인덱스 스캔만으로 정렬된 결과를 제공할 수 있다. 

 

단순 EXPLAIN만으로는 “실제 트래픽 상황에서 얼마나 빨라졌는지”를 검증할 수 없기에 나는 k6를 이용해 부하 테스트를 진행하였다. 

 

k6를 사용하면 VU(Virtual Users)를 이용하여 실제 유저 행동을 시뮬레이션 할 수 있다. 또한 반복 조회와 같은 시나리오 기반 테스트가 가능하며, Prometheus/Grafana를 통해 실시간으로 그래프를 볼 수 있다.  

인덱스 적용 전 쿼리 성능을 k6을 이용해 측정

 

CREATE INDEX idx_product_latest
    ON product (brand_id, created_at DESC, id DESC);

ANALYZE TABLE product;

 

인덱스 적용 후 다시 EXPLAIN 한 결과

 

나는 인덱스 적용 전과 후의 데이터를 json으로 받아 비교해보았다.

"metrics": {
    "latency_list": {
      "values": {
        "avg": 1059.0293865110245, //평균 응답 시간
        "min": 167.091,//최소 응답 시간
        "med": 476.217,
        "max": 6503.116,
        "p(90)": 2594.895,//90번째 퍼센타일
        "p(95)": 4200.921499999998
      },
      "thresholds": {
        "p(95)<25000": { //임계값 검사
          "ok": true
        }
      },
      "type": "trend",
      "contains": "default"
    },

 

그 결과 인덱스 적용 전과 후의 유의미한 차이를 보이지 않았다.

이론상 인덱스를 적용하면 RPS(초당 처리 요청 수)가 확 올라가야 한다고 배웠는데 그래프상에서 인덱스 적용 후 RPS는 유의미하게 증가하지 않았다.

 

수차례 시도 후 원하는 결과가 나오지 않아, 나는 DataGrip에서 테스트를 한 뒤 쿼리 응답 시간 측정해보았다.

인덱스 적용 전 쿼리 성능

인덱스 생성 + 인덱스 적용 후 쿼리 성능

 

참고로 동일한 쿼리를 여러 차례 반복하면 MySQL이 캐싱을 해, 정확한 비교가 안 된다는 사실을 알고 있기에 나는 측정시에 도커 컨테이너를 냈다가 다시 올려 캐싱을 초기화 시켰다. 

인덱스 전	~533ms	FileSort 발생
인덱스 후	~192ms	인덱스 스캔만으로 정렬 완료

 

그 결과 (533ms - 192ms) / 533ms ≒ 64% 개선 효과를 보였다. 

 

그렇다면 왜 k6를 통해 테스트 하였을 땐 개선 효과를 보이지 않았을까? 

 

그 것을 예측해보자면 k6는 “전체 API 응답 시간”을 측정하기 때문에 DB 조회 시간 뿐만 아니라, JPA 엔티티 매핑, 네트워크 지연,  Spring 로직 처리 등 다양한 외부 요소가 존재하기에 DB 인덱스가 해소한 병목은 전체 비율에서 매우 작게 보일 수도 있다고 생각하였다. 

 

또한 온라인에 검색해보니 특히 page_size가 작아 DB 부하가 크지 않고, RPS 또한 높지 않은 환경에서는 인덱스 개선 효과가 전체 레이턴시에 크게 드러나지 않는다고 한다. 이런 경우엔 DB 쿼리 결과만 딱 확인할 수 있는 DataGrip과 같은 환경에서 테스트 하는 것이 조금 더 유리할 수 있을 것 같아.