쇼핑몰DB 1. 리뷰
스키마
CREATE TABLE review
(
id INT PRIMARY KEY,
order_item_id INT NOT NULL DEFAULT 0 COMMENT '주문',
member_id INT NOT NULL DEFAULT 0 COMMENT '리뷰 작성 회원',
item_id INT NOT NULL DEFAULT 0 COMMENT '상품',
contents TEXT NULL,
score TINYINT NOT NULL DEFAULT 0 COMMENT '별점 1~5점',
/*
....
*/
)
CREATE TABLE review_reply
(
id INT PRIMARY KEY,
review_id INT NOT NULL DEFAULT 0,
/*
....
*/
index idx_review_id (review_id)
)필요한 쿼리
속도가 중요한 쿼리 (유저용)
1.1 최신순 상품페이지 리뷰
1.2 최신순 상품페이지 리뷰 + 별점 필터링
1.3 리뷰 작성시 이미 작성했던 주문인지 확인 (exists)
중요도 낮은 쿼리 (쇼핑몰 관리자용)
2.1 특정 회원이 쓴 리뷰 목록
2.2 댓글이 없는 리뷰 목록
2.3 별점별 리뷰 목록
쿼리
1.1 최신순 상품페이지 리뷰
# 인덱스 추가
idx_1 => (item_id)
# 쿼리
SELECT a.*
, m.name AS member_name
FROM (
SELECT r.*
FROM review r
WHERE item_id = :itemId
ORDER BY id DESC
LIMIT :offset, :limit
) a
INNER JOIN member m
ON m.id = a.member_id
ORDER BY a.id DESC동일한 item_id 기준 id로 정렬되어 있어서 역순으로 20건만 읽으면 된다.
페이징 쿼리를 서브쿼리로 감싸지 않으면 2페이지부터 필요없는 행에서도 member 조인이 생긴다.
1.2 최신순 상품페이지 리뷰 + 별점 필터링
# 인덱스 수정 전
idx_1 => (item)
# 수정 후
idx_1 => (item_id, score)
# 쿼리
SELECT a.*
, m.name AS member_name
FROM (
SELECT r.*
FROM review r
WHERE item_id = :itemId
AND score = :score
ORDER BY id DESC
LIMIT :offset, :count
) a
INNER JOIN member m
ON m.id = a.member_id
ORDER BY a.id DESC동일한 item_id, score 기준 id로 정렬되어 있어서 역순으로 20건만 읽으면 된다.
하지만 인덱스 변경시 위 1.1의 쿼리에서 동일 item_id 기준 id로 정렬되어 있지 않아서
item_id를 전체 정렬해야 한다. (동일 item_id 행을 모두 읽어야한다)
# 인덱스 수정 전
idx_1 => (item_id, score)
# 인덱스 수정 후
idx_1 => (item_id, id, score)
# 쿼리
SELECT a.*
, m.name AS member_name
FROM (
SELECT r.*
FROM review r
WHERE item_id = :itemId
AND score = :score
ORDER BY id DESC
LIMIT :offset, :count
) a
INNER JOIN member m
ON m.id = a.member_id
ORDER BY a.id DESC1.2의 속도가 변경 전 (item_id, score)만큼 빠르지 않지만 1.1의 쿼리 속도는 (item_id)와 동일하게 유지된다.
서브쿼리(a)에서 인덱스만 읽어서 필요한 id 가져온 후 전체 데이터 가져오기 위해 join한다.
→ 최선의 경우 인덱스 20건 데이터 20건 (뒤에서부터 원하는 score가 20건 연속으로 있는 경우)
→ 최악의 경우 인덱스 item_id : itemId 전체, 데이터 20건 (원하는 score가 앞쪽에 있는 경우)
score가 1~5점 동일한 갯수만큼 분포되어 있다면
인덱스에서 약 100건, 실제 데이터 20건 읽어서 완료할 수 있어 충분히 빠르다.
기타
innodb에선 모든 인덱스 끝에 pk가 추가된다.
인덱스 중간에 pk를 추가할 경우 끝에 추가로 붙지 않는다.
1.3 리뷰 작성시 이미 작성했던 주문인지 확인 (exists)
# 인덱스 추가
idx_2 => (order_item_id)
# 쿼리
SELECT exists(
SELECT *
FROM review
WHERE order_item_id = :orderItemId
) AS is_exists인덱스만 읽어서 처리 가능하다
2.1 특정 회원이 쓴 리뷰 목록
# 쿼리
SELECT a.id
, a.contents
, a.score
, a.member_id
, rr.reply_count
FROM (
SELECT r.id
, r.contents
, r.score
, r.member_id
FROM orders o
INNER JOIN order_item oi
ON o.id = oi.order_id
INNER JOIN review r
ON oi.id = r.order_item_id
WHERE o.member_id = :memberId
ORDER BY r.id DESC
LIMIT :offset, :count
) a
LEFT OUTER JOIN LATERAL (
SELECT count(*) AS reply_count
FROM review_reply rr
WHERE rr.review_id = a.id
) rr
ON TRUE
ORDER BY a.id DESC부분범위처리는 안됨.
해당 회원의 전체 리뷰 읽은 후 정렬하여 조회
2.2 댓글이 없는 리뷰 목록
# 쿼리
SELECT r.*
, 0 AS reply_count
FROM review r
WHERE r.id NOT IN(
SELECT rr.review_id
FROM review_reply rr
)
ORDER BY r.id DESC
LIMIT :offset, :limitPK 역순으로 읽으면서 만족하는 행 20건 찾으면 종료
2.3 별점별 리뷰 목록
# 필요한 인덱스
idx_3 => (score)
# 쿼리
SELECT a.*
, rr.reply_count
FROM (
SELECT *
FROM review
WHERE score = :score
ORDER BY id DESC
LIMIT :offset, :count
) a
LEFT OUTER JOIN LATERAL (
SELECT count(*) AS reply_count
FROM review_reply rr
WHERE rr.review_id = a.id
) rr
ON TRUE
ORDER BY a.id DESC동일한 score 기준 id로 정렬 되어 있어서 필요한 20건만 읽으면 된다.
인덱스 없이 PK 기준으로 역순으로 읽어도 괜찮을듯함
페이징쿼리는 서브쿼리 후 조인해서 비효율이 없도록함
결과
CREATE TABLE review
(
id INT PRIMARY KEY,
order_item_id INT NOT NULL DEFAULT 0 COMMENT '주문',
member_id INT NOT NULL DEFAULT 0 COMMENT '리뷰 작성 회원',
item_id INT NOT NULL DEFAULT 0 COMMENT '상품',
contents TEXT NULL,
score TINYINT NOT NULL DEFAULT 0 COMMENT '별점 1~5점',
/*
....
*/
# idx_1
INDEX idx_item (item_id, id, score)
# idx_2
UNIQUE INDEX udx_order_item (order_item_id)
)