[성능 개선] 특정 시장에서 최소 가격 상품 5개 조회 API Index(인덱스) 사용하여 조회 성능 개선 + ngrinder로 성능테스트

DB 성능 개선할 때 ‘SQL 튜닝’을 가장 먼저 해야 하는 이유

1. SQL 튜닝을 제외한 나머지 방법은 추가적인 시스템을 구축해야 한다. 따라서 금전적, 시간적 비용이 추가적으로 발생한다. 조금 더 복잡해진 시스템 구조로 인해 관리 비용이 늘어난다. 그에 비해 SQL 튜닝은 기존의 시스템 변경 없이 성능을 개선할 수 있다.
   
   
2. 근본적인 문제를 해결하는 방법이 SQL 튜닝일 가능성이 높다. SQL 자체가 비효율적으로 작성됐다면 아무리 시스템적으로 성능을 개선한다고 하더라도 한계가 있다. 하지만 SQL 튜닝을 통해 기본적으로 성능을 향상시킨다면, 시스템적인 성능 개선이 필요없거나 훨씬 간단한 개선으로 큰 성능 개선 효과를 얻을 수 있다.

⭐️ 인덱스(Index)는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료 구조를 뜻한다.

쉽게 이야기하면, 인덱스(Index) : 데이터를 빨리 찾기 위해 특정 컬럼을 기준으로 미리 정렬해놓은 표

1. 100만건 더미데이터 생성

-- 높은 재귀(반복) 횟수를 허용하도록 설정
SET SESSION cte_max_recursion_depth = 1000000;

-- 더미 데이터 삽입 쿼리
INSERT INTO item (create_time, update_time, item_category, item_detail, item_name, item_sell_status, price, stock_number, shop_no)
WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 250000 -- 
),
-- 각 카테고리별로 상품 이름을 고정하여 생성
fruit_names AS (
    SELECT '과일' AS item_category, ELT(FLOOR(RAND() * 8) + 1, '복숭아', '천도복숭아', '키위', '망고', '체리', '수박', '샤인머스캣', '블루베리') AS item_name FROM cte
),
vegetable_names AS (
    SELECT '채소' AS item_category, ELT(FLOOR(RAND() * 5) + 1, '양배추', '상추', '깻잎', '열무', '무') AS item_name FROM cte
),
meat_names AS (
    SELECT '육류' AS item_category, ELT(FLOOR(RAND() * 3) + 1, '소고기', '삼겹살', '목살') AS item_name FROM cte
),
fish_names AS (
    SELECT '생선' AS item_category, ELT(FLOOR(RAND() * 3) + 1, '고등어', '연어', '게') AS item_name FROM cte
),
-- 각 카테고리별로 만들어진 상품 이름을 합침
item_names AS (
    SELECT * FROM fruit_names
    UNION ALL
    SELECT * FROM vegetable_names
    UNION ALL
    SELECT * FROM meat_names
    UNION ALL
    SELECT * FROM fish_names
)
SELECT 
    NOW() - INTERVAL FLOOR(RAND() * 30) DAY AS create_time, -- Random date within the last month.
    NOW() AS update_time, -- Current timestamp as update_time
    item_names.item_category,
    '' AS item_detail, 
    item_names.item_name, 
    'SELL' AS item_sell_status, 
    FLOOR(RAND() * 47001) + 3000 AS price, -- Random price between 3,000 and 50,000 KRW
    FLOOR(RAND() * 50) + 1 AS stock_number, -- Random stock quantity between 1 and 50
    FLOOR(RAND() * 6) + 1 AS shop_no -- Random shop_no between 1 and 6
FROM 
    item_names;

2. 상점 고유번호와 상품 이름을 이용해 상품 조회(searchItemsByMarketNoAndItemName 쿼리 조회)

select
        i1_0.item_category,
        i1_0.item_name,
        i1_0.price,
        m1_0.market_name,
        s1_0.shop_name,
    from
        item i1_0 
    join
        shop s1_0 
            on s1_0.shop_no=i1_0.shop_no 
    join
        market m1_0 
            on m1_0.market_no=s1_0.market_no 
    where
        m1_0.market_no=1 
        and i1_0.item_name='상추' 
    order by
        i1_0.price 
    limit
        5

평균 1300ms

3. 가격(ptice) 인덱스 생성

CREATE INDEX idx_price ON item (price);

평균 34ms

4. mysql profiling 기능을 통해 인덱스 활용 전과 후를 명확하게 비교하여 검증

총 : 1.238392 총 : 0.004111

1.238 → 0.004 으로 해당 쿼리문의 수행 시간 단축

인덱스 반영 후 99.67% 시간 단축

---

부하테스트

• 상품 이름이 '복숭아' 인 데이터 조회

• GET /api/[marketNo]/items/rank

• 인덱스 적용하기 전

  • Vuser(가상 사용자) : 100
  • Duration : 1분
    

• 인덱스(idx_price) 적용 후

  • Vuser(가상 사용자) : 100
  • Duration : 1분
    

• 수치 변화
  • TPS (Transactions Per Second) : { 2.2 } → { 1,967.7 } (약 894배, 89,340% 성능 향상)
  • 최대 TPS (Peak TPS) : { 5 } → { 2,841 }
  • 평균 테스트 시간 (Mean Test Time) : { 24,728.66 }ms → { 49.15 }ms (99.80% 개선)
  • 실행된 테스트 수 (Executed Tests) : { 144 } → { 110,473 }