[DataBase]간단한 E-Commerce 프로젝트를 통해 알아보는 Indexing 🔍(Index로 성능 개선해보기2)

nana·2024년 11월 14일
Index
0

DataBase

목록 보기
6/8

0. 전 테스트에서 간과한 것.

그냥 몇 만건의 데이터를 넣어놓고 api에서 여러번 조회 부하테스트를 넣는걸로 테스트가 가능할거라 생각했다.

전에 한게 아까워서 그대로 두는게 절대아니고^..^ 나름의 유의미한 데이터가 되지않을까..하여 놔둔다.

👉🏻적은 데이터가 있을 때에는 Index Seek 을 통해 찾는 것 보다 Full Scan이 더 빠를 수 있기 때문에 이번엔 찐 dummy data를 통해 인덱스를 내가 생각한 대로 타는지 실행계획을 통해 파악해 보고자 한다.

  • DB : MySQL 8.0

1. Dummy Data 생성

인덱스가 제대로 동작하는지 보려면 최소 100만건 이상의 데이터는 존재해야한다. 


USE ecommerce;

-- 테이블 초기화
TRUNCATE TABLE order_product;
TRUNCATE TABLE customer_order;
TRUNCATE TABLE product_inventory;
TRUNCATE TABLE product;
TRUNCATE TABLE cart;
TRUNCATE TABLE customer;

-- 1. customer 테이블에 1,000명의 고객 데이터 생성
INSERT INTO customer (balance)
WITH RECURSIVE cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 1000
)
SELECT FLOOR(RAND() * 10000) AS balance
FROM cte;

-- 2. product 테이블에 1,000,000개의 제품 데이터 생성
SET SESSION cte_max_recursion_depth = 10000000;


-- product 테이블에 1,000,000개의 제품 데이터 생성
INSERT INTO product (product_nm, price, category)
WITH RECURSIVE cte (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM cte WHERE n <= 1000000
)
SELECT
    CONCAT('Product', LPAD(n, 7, '0')) AS product_nm,
    FLOOR(RAND() * 1000) AS price,
    CASE WHEN n % 4 = 0 THEN 'food'
         WHEN n % 4 = 1 THEN 'clothes'
         WHEN n % 4 = 2 THEN 'elec'
         ELSE 'etc' END AS category
FROM cte;

-- product_inventory에 각 product의 재고 데이터를 생성
INSERT INTO product_inventory (product_id, amount)
SELECT product_id, FLOOR(RAND() * 100)
FROM product;

-- customer_order에 주문 데이터 생성
INSERT INTO customer_order (customer_id)
SELECT customer_id FROM customer ORDER BY RAND() LIMIT 1000000;

-- order_product에 각 주문마다 임의의 제품 추가
INSERT INTO order_product (order_id, product_id, amount, price)
SELECT
    customer_order.order_id,
    product.product_id,
    FLOOR(RAND() * 5) + 1 AS amount,
    product.price
FROM
    customer_order
        JOIN
    product ON RAND() < 0.01;  -- 일부 제품을 임의로 추가

1000명의 고객과 100만건의 상품, 주문 데이터를 생성했다.

2. 인덱스 생성하기.

2-1. 재고가 있는 상품 전체 조회

SELECT p.product_id, p.product_nm, p.price, p.category, PI.amount 
FROM product p 
INNER JOIN product_inventory PI 
ON p.product_id = PI.product_id
WHERE PI.amount > 0

product의 product_id는 PK이기 때문에 인덱스가 기본적으로 걸려있다.
조인하는 컬럼인 product_inventory의 product_id와 amount가 조회하는 쿼리에 사용되었고 product와 product_inventory는 1:1관계이므로 Cardinality가 높을 것으로 예상된다.

2-1-1. 인덱스 없을 때 조회

1) product_inventory테이블은 Full Scan을 했고, product테이블은 eq_ref를 했다.

🎲 실행계획 type

  • eq_ref :
    조인이 수행될 때 드리븐 테이블의 데이터에 접근하며, 고유 인덱스 또는 기본키로 단 1건의 데이터를 조회하는 방식.
    현재 테이블의 각 행에 대해 다른 테이블에서 단 하나의 행만이 조인될 것을 의미.
  • ALL :
    테이블의 처음부터 끝까지 읽는 full-scan방식.
    활용할 인덱스가 없거나 인덱스를 활용하는게 오히려 비효율적이라고 옵티마이저가 판단할 경우 선택됨.

2) key
옵티마이저가 SQL문을 최적화 하고자 사용한 기본키(PK) 또는 인덱스 명을 의미한다.
어느 인덱스로 데이터를 검색했는지 확인할 수 있기에 해당 정보를 통해 비효율적인 인덱스 사용이나 인덱스 사용하지 않았을 경우 SQL튜닝을 고려할 수 있다.

해당 쿼리에서는 product의 PK를 사용하고product_inventory는 key가 사용되지 않았다.

  • 조회 건 수 : 1,980,054 건
  • 쿼리를 실행 : 0.031s
  • 전체 조회 : 네트워크 전송을 포함 약 80.032s
    약 200만건을 조회하는데 80초 정도 걸렸다.

2-1-2. 인덱스 걸고 조회

1) product_inventory의 product_id와 amount로 걸면 Cardinality가 높아 적절한 인덱스일 것으로 예상했다. 

CREATE INDEX idx_product_id ON PRODUCT_INVENTORY (product_id);
CREATE INDEX idx_amount ON product_inventory (amount);


  • product_inventory의 type : ALL
  • product_inventory의 key : null

인덱스를 생성했으나 사용되지 않았다.
옵티마이저가 full scan이 더 성능이 좋다고 판단했기 때문이다.

2) 이번엔 select에 사용된 조건인 "amount"와 "product_id"의 복합인덱스를 생성해서 비교해보았다. 

CREATE INDEX idx_product_inventory_amount_product_id ON PRODUCT_INVENTORY(amount, product_id);

  • product_inventory의 type : ALL ➡️ range
  • product_inventory의 key : null ➡️ idx_product_inventory_amount_product_id

이번엔 내가 생성한 인덱스가 정상적으로 타는 것을 볼 수 있다.

🎲 filtered

  • 스토리지 엔진 MySQL 엔진으로 가져온 데이터를 대상으로 필터 조건에 따라 필터링 된 건수를 백분율로 계산해 보여주는 항목.
  • 예를 들어 where 사원번호 between 1 and 10 조건으로 100건의 데이터 중 10건의 데이터가 필터링 되었다면 filtered의 값은 10(%)가 될 것이다.

🎲 extra
SQL문을 어떻게 수행 할 것 인지에 대한 추가 정보를 보여주는 항목
세미콜론; 으로 구분해서 여러 정보를 나열한다.
이건 종류가 너무 많으니 여기에서 봐주시길!


인덱스를 탔더니 조회 시간이 줄어들었다.

💡왜 복합인덱스에서 성능이 나온걸까?
product_id는 어차피 1:1관계에 있으므로 인덱스를 거는 것 만으로 크게 성능을 향상시킬 수 없다.
where절에 amount가 있으므로 product_id 인덱스를 찾았을 때 amount까지 필요한 데이터로 가져올 수 있기 때문에 성능개선을 확실히 느낄 수 있다.

2-2. 판매 랭킹 조회

explain SELECT op.product_id, SUM(op.amount)
FROM order_product op 
WHERE op.reg_date >=  '2024-11-10'
GROUP BY op.product_id  
ORDER BY SUM(op.amount) DESC

주문일 최근3일간의 데이터를 집계하여 상위 5개의 상품을 보여주는 쿼리이다.
전체를 조회해서 상위 5개를 뽑아내는건 소스 내에서 한다.

2-2-1. 인덱스 없음


찾은 행: 1,999,281 쿼리: 00:02:19.0 (+ 0.719 초 네트워크)

2-2-2. product_id 인덱스 추가

CREATE INDEX idx_product_id ON order_product (product_id);

2-2-3. reg_date 인덱스 추가

CREATE INDEX idx_createdAt ON order_product (reg_date);

2-2-4. reg_date, product_id 인덱스 추가

CREATE INDEX idx_createdAt_product_id ON order_product (reg_date, product_id);

👉🏻 가장 필터가 많이 된 것은 product_id에서 였다.

이 부분이 너무 이상해서 SOS를 했다 ㅠ

쩝..해 봤는데도 안되는군요 ㅠㅠ 그치만..
다시 해보는걸로...

2-3. 주문 정보 조회

SELECT * 
FROM customer_order o 
INNER JOIN order_product op 
ON o.order_id = op.order_id
WHERE o.order_id = 13

주문 정보로 조회하기 때문에 order_product에 order_id로 인덱스를 걸어주면 성능향상을 기대할 수 있다.

2-3-1. 인덱스 적용 전

5건을 조회하는데 16 s 892 ms이 걸렸다.
pk로 적용된 customer_order의 order_id를 제외하고는 조인에 영향을 준 컬럼이 없다.
filtered도 10만 걸러주었고 rows도 전체 row를 검색하고있었다.

2-3-2. Order_id 인덱스 추가

sql CREATE INDEX idx_orderId ON ORDER_PRODUCT (order_id)

⏰ 16 s 892 ms ➡️ 76 ms
실행계획을 봐도 정확히 order_id를 통한 조인이 되었다.
filtered도 100%로 향상되었다.

https://catsbi.oopy.io/c6410158-5165-4145-9332-58896020f7cc
https://velog.io/@juhyeon1114/MySQL-%EC%8B%A4%ED%96%89-%EA%B3%84%ED%9A%8D-%EC%95%8C%EC%95%84%EB%B3%B4%EA%B8%B0

profile
nana
BackEnd Developer, 기록의 힘을 믿습니다.
이전 포스트

[DataBase]간단한 E-Commerce 프로젝트를 통해 알아보는 Indexing 🔍(Index로 성능 개선해보기.)

다음 포스트

Database Connection Pool(데이터베이스 커넥션 풀)을 사용하지 않으면 일어나는 일!!?

0개의 댓글