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)
# 인덱스 추가
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
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건 나오면 종료.
-> 정렬할 컬럼별로 인덱스를 각각 만들어줘야 함
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 쿼리와 동일하게 해당하는 상품 비율 높을수록 상품테이블 드라이빙으로 풀 인덱스 스캔.
아닌 경우 세미조인 구체화 테이블 드라이빙. 상품 테이블을 드리븐으로 처리
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건 나오면 바로 출력되도록 함