Mysql. 쇼핑몰 2. 상품 카테고리 테이블 쿼리

j_6367·2022년 3월 28일
0

쇼핑몰DB설계

목록 보기
2/2

스키마

CREATE TABLE category
(
    id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR(255) NOT NULL DEFAULT '',
    parent_id INT UNSIGNED NOT NULL DEFAULT 0,
    
    /*
     ...
    */
)

CREATE TABLE category_item
(
    category_id INT UNSIGNED NOT NULL DEFAULT 0,
    item_id     INT UNSIGNED NOT NULL DEFAULT 0,

    PRIMARY KEY (category_id, item_id)
)

필요한 쿼리

1.1 하위 카테고리 조회

1.2 카테고리 상품 조회 + 컬럼 정렬

1.3 카테고리 상품 조회 + 리뷰순(개수, 평점) 정렬

1.4 리뷰 작성시 이미 작성했던 주문인지 확인 (exists)

쿼리

1.1 하위 카테고리 조회

# 인덱스 추가
idx_1 => (parent_id)

# 쿼리
WITH RECURSIVE cte AS (
    SELECT id
         , parent_id
    FROM category
    WHERE id = :categoryId
    UNION ALL
    SELECT c.id
         , c.parent_id
    FROM category c
         INNER JOIN cte
                    ON cte.id = c.parent_id
)
SELECT *
FROM cte

부모 카테고리에서 자식을 찾아가므로 parent_id로 인덱스 추가

1.2 카테고리 상품 조회 + 컬럼 정렬

# 쿼리 1
WITH RECURSIVE cte AS (
    SELECT id
         , parent_id
    FROM category
    WHERE id = :categoryId
    UNION ALL
    SELECT c.id
         , c.parent_id
    FROM category c
         INNER JOIN cte
                    ON cte.id = c.parent_id
)
SELECT /*+
          JOIN_SUFFIX(i)
          SEMIJOIN(@subq1 MATERIALIZATION)
    */ i.id
     , i.name
     , i.price
FROM item i
WHERE i.id IN (
    SELECT /*+
              QB_NAME(subq1)
          */ ci.item_id
    FROM cte
         INNER JOIN category_item ci
                    ON ci.category_id = cte.id
)
ORDER BY i.price DESC
LIMIT 50
# 쿼리 2
WITH RECURSIVE cte AS (
    SELECT id
         , parent_id
    FROM category
    WHERE id = :categoryId
    UNION ALL
    SELECT c.id
         , c.parent_id
    FROM category c
         INNER JOIN cte
                    ON cte.id = c.parent_id
)
SELECT /*+
          JOIN_PREFIX(i)
          SEMIJOIN(@subq1 FIRSTMATCH)
    */ i.id
     , i.name
     , i.price
FROM item i
WHERE i.id IN (
    SELECT /*+
               QB_NAME(subq1)
        */ ci.item_id
    FROM cte
         INNER JOIN category_item ci
                    ON ci.category_id = cte.id
)
ORDER BY i.price DESC
LIMIT 50

상품 테이블 내에 컬럼으로 정렬하는 경우.

총 상품 중 해당하는 상품의 비율이 높을수록(ex)대카테고리) 상품테이블 풀스캔 + firtstmatch 세미조인이 유리

해당하는 상품이 별로 없을 경우 materialization 세미조인 테이블을 드라이빙, 상품 테이블을 드리븐으로 설정

# 인덱스
index idx_price (price)

# 쿼리 3
WITH RECURSIVE cte AS (
    SELECT id
         , parent_id
    FROM category
    WHERE id = :categoryId
    UNION ALL
    SELECT c.id
         , c.parent_id
    FROM category c
         INNER JOIN cte
                    ON cte.id = c.parent_id
)
SELECT /*+
          JOIN_PREFIX(i)
          SEMIJOIN(@subq1 FIRSTMATCH)
    */ i.id
     , i.name
     , i.price
FROM (
         SELECT /*+
                   JOIN_PREFIX(i)
                   SEMIJOIN(@subq1 FIRSTMATCH)
             */ i.id AS item_id
         FROM item i
         WHERE i.id IN (
             SELECT /*+
                      QB_NAME(subq1)
                 */ ci.item_id
             FROM cte
                  INNER JOIN category_item ci
                             ON ci.category_id = cte.id
         )
         ORDER BY i.price DESC
         LIMIT 50
     ) a
     INNER JOIN item i
                ON i.id = a.item_id

상품이 많고 자주 쓰이는 쿼리인 경우 테이블 풀스캔을 하지않고
인덱스 스캔 후 테이블 조인으로 처리함
인덱스 역순으로 순차적으로 읽어가며 id가 맞는 행이 50건 나오면 종료.
-> 정렬할 컬럼별로 인덱스를 각각 만들어줘야 함

1.3 상품 테이블 리뷰(개수, 평점) 정렬

WITH RECURSIVE cte AS (
    SELECT id
         , parent_id
    FROM category
    WHERE id = :categoryId
    UNION ALL
    SELECT c.id
         , c.parent_id
    FROM category c
         INNER JOIN cte
                    ON cte.id = c.parent_id
)
SELECT /*+
          JOIN_SUFFIX(i)
    */ i.id
     , i.name
     , i.price
FROM (
         SELECT /*+
                   JOIN_PREFIX(r)
                   SEMIJOIN(@subq1 FIRSTMATCH)
             */ r.item_id AS item_id
              , count(*)  AS review_count
         FROM review r
         WHERE r.item_id IN (
             SELECT /*+
                      QB_NAME(subq1)
                 */ ci.item_id
             FROM cte
                  INNER JOIN category_item ci
                             ON ci.category_id = cte.id
         )
         GROUP BY r.item_id
         ORDER BY review_count DESC
         LIMIT 50
     ) a
     INNER JOIN item i
                ON i.id = a.item_id

리뷰 테이블 기준으로 정렬하는 경우

1.2 쿼리와 동일하게 해당하는 상품 비율 높을수록 상품테이블 드라이빙으로 풀 인덱스 스캔.

아닌 경우 세미조인 구체화 테이블 드라이빙. 상품 테이블을 드리븐으로 처리

1.4 카테고리 삭제 전 확인

WITH RECURSIVE cte AS (
    SELECT id
         , parent_id
    FROM category
    WHERE id = :categoryId
    UNION ALL
    SELECT c.id
         , c.parent_id
    FROM category c
         INNER JOIN cte
                    ON cte.id = c.parent_id
)
SELECT exists(
               SELECT *
               FROM category_item ci
                    INNER JOIN cte c
                               ON c.id = ci.category_id
           ) AS is_exists

카테고리 모두 찾은 후 exists 처리해서 1건 나오면 바로 출력되도록 함

0개의 댓글