Ch11. 물리적 모델링 실습 - 쇼핑몰 기능 확인

회원 로그인 처리

상황: 사용자가 ID로 "sejong"을 입력하고 로그인을 시도한다. 시스템은 이 ID에 해당하는 회원 정보를 데이터베이스에서 조회하여 비밀번호를 검증해야 한다.

EXPLAIN
SELECT
	member_id,
	login_id,
    password, -- 애플리케이션에서 비밀번호 검증을 위해 사용될 암호화된 비밀번호
	member_name
FROM member
WHERE login_id = 'sejong';

쿼리 결과

• table: member 테이블을 사용했다.
• type: "const".
  • "login_id" 컬럼은 "UNIQUE" 키이므로, MySQL은 이 쿼리를 최적화하는 단계에서 값을 상수("sejong")로 취급하고 단 하나의 행만 읽으면 된다는 것을 안다.
  • 성능이 매우 우수함을 의미한다.
• key: "uq_login_id".
  • "login_id" 컬럼에 설정한 "UNIQUE" 제약조건으로 인해 자동으로 생성된 인덱스를 정확하게 사용했다.
• rows: "1"
  • MySQL이 단 1개의 행만 찾으면 쿼리가 종료될 것이라고 완벽하게 예측했다.

결론: 이 실행 계획은 로그인이 얼마나 효율적으로 처리되는지를 명확히 보여준다. 전체 회원 수가 100만 명, 1000만 명이 되더라도 로그인 속도는 거의 동일하게 유지될 것이다.

 

해설 및 실무 Tip

• 로그인은 웹사이트에서 가장 빈번하게 발생하는 쿼리 중 하나이다.
  • "login_id" 컬럼에 "UNIQUE" 제약조건을 설정했기 때문에 자동으로 고유 인덱스가 생성되었고, 이 덕분에 매우 빠르게 특정 사용자를 찾아낼 수 있다.
• 매우 중요한 보안 사항
  • 데이터베이스에 저장된 "password" 값은 "pass123!"과 같은 평문이 아닌, 암호화된 해시(Hash) 값이어야 한다.
  • 실제 애플리케이션에서는 사용자에게 입력받은 비밀번호를 동일한 해시 함수로 암호화한 뒤, 데이터베이스에 저장된 해시 값과 일치하는지 비교하는 방식으로 인증을 처리한다.
  • 절대로 평문 비밀번호를 데이터베이스에 저장해서는 안 된다.

회원 주문 목록 조회

상황: "션"(member_id = 6) 회원이 마이페이지에서 자신의 주문 내역을 최신순으로 조회한다.

EXPLAIN
SELECT order_id, ordered_at, order_status, total_amount
FROM orders
WHERE member_id = 6
ORDER BY ordered_at DESC;

쿼리 결과

• table: orders 테이블을 사용했다.
• type: ref.
  • 인덱스를 사용하여 동등 비교(=) 조건으로 데이터를 찾는 효율적인 방식이다.
• key: "fk_orders_member".
  • 우리가 예상한 대로 "member_id" 컬럼의 외래 키에 자동으로 생성된 인덱스를 잘 사용하고 있다.
• rows: "2".
  • MySQL이 약 2개의 행만 읽으면 될 것이라고 예측했다. (실제 데이터 건수에 따라 달라질 수 있음)
  • 전체 주문 데이터가 아무리 많아져도, 이 인덱스 덕분에 특정 회원의 주문만 매우 빠르게 찾아낼 수 있다.
• Extra: "Using filesort".
  • WHERE 절로 데이터를 찾은 후, "ORDER BY ordered_at DESC" 정렬을 위해 별도의 정렬 작업(filesort)을 수행했다는 의미다.
  • 데이터 양이 적어서 큰 문제는 아니지만, 만약 이 기능이 매우 중요하고 회원의 주문 건수가 아주 많다면 (member_id, ordered_at) 복합 인덱스를 고려해 볼 수도 있다.
  • 하지만 지금 해당 기능은 과잉 최적화이므로 현재 상태를 유지하자.

회원 주문 목록 조회 (역정규화 효과)

만약 역정규화를 하지 않았다면 다음과 같은 복잡하고 비효율적인 쿼리를 실행해야 한다.

-- 역정규화를 하지 않았다면 매번 실행해야 했을 복잡한 쿼리
SELECT
    o.order_id,
    o.ordered_at,
    o.order_status,
	SUM(oi.order_price * oi.order_quantity) AS calculated_total_amount
FROM orders o
JOIN order_item oi ON o.order_id = oi.order_id
WHERE o.member_id = 6
GROUP BY o.order_id, o.ordered_at, o.order_status
ORDER BY o.ordered_at DESC;

• 위 쿼리는 주문 목록에 표시될 모든 주문 건에 대해 order_item 테이블을 "JOIN" 하고 "GROUP BY"를 통해 합계를 계산한다.
• 주문 내역 조회는 사용자가 매우 빈번하게 사용하는 기능이므로, 매번 이렇게 복잡한 연산을 수행하는 것은 데이터베이스에 큰 부하를 준다.
• orders 테이블에 "total_amount"를 역정규화함으로써, 우리는 "JOIN"과 집계 연산을 피하고 단순히 orders 테이블만 읽어서 매우 빠르고 효율적으로 주문 목록을 표시할 수 있다.

 

이것이 바로 읽기 성능을 최적화하기 위한 역정규화의 강력한 효과다.

상품명 검색

EXPLAIN
SELECT product_id, product_name, product_price
FROM product
WHERE product_name LIKE 'JPA%';

쿼리 결과

• type: "range".
  • 인덱스를 사용하여 특정 범위의 데이터를 검색했다는 의미다.
  • "LIKE 'JPA%'"는 "JPA"로 시작하는 모든 문자열 범위를 검색하므로 range 타입으로 동작한다.
• key: "idx_product_name".
  • 우리가 상품명 검색을 위해 "product_name" 컬럼에 생성한 "idx_product_name" 인덱스가 정확하게 사용되었다.
• rows: "1".
  • MySQL이 약 1개의 행만 스캔하면 된다고 예측했다.
  • 인덱스가 없었다면 모든 상품 데이터를 스캔해야 했을 것이다.
• Extra: "Using index condition".
  • 인덱스를 통해 데이터를 필터링하여 불필요한 데이터 접근을 줄였다는 긍정적인 신호다.

관리자 주문 조회(복합 인덱스)

상황: 관리자가 어드민 페이지에서 "2025년 9월에 취소(CANCELED)된 주문"을 조회한다.

EXPLAIN
SELECT order_id, member_id, ordered_at, order_status
FROM orders
WHERE order_status = 'CANCELED'
AND ordered_at BETWEEN '2025-09-01 00:00:00' AND '2025-09-30 23:59:59';

쿼리 결과

• type: "range".
  • "order_status"는 등호(=) 조건이지만, "ordered_at"이 범위(BETWEEN) 조건이므로 최종적으로 "range" 타입으로 동작했다.
• key: "idx_order_status_ordered_at".
  • 우리가 관리자 조회 성능을 위해 (order_status, ordered_at) 순서로 만든 복합 인덱스가 정확히 사용되었다.
• rows: "1".
  • MySQL은 인덱스를 사용해 "order_status"가 "CANCELED"인 것들 중에서 "ordered_at"이 조건에 맞는 데이터만 효율적으로 찾아 약 "1"건만 읽으면 된다고 예측했다.
  • 이 복합 인덱스가 없었다면 훨씬 많은 데이터를 스캔해야 했을 것이다.

주문 상세 조회(역정규화 효과)

상황: 한 회원이 자신의 주문 상세 내역을 본다. "네이트" 회원의 주문(order_id= 3)에 어떤 상품들이 있었는지 확인한다.

-- product 테이블과 JOIN 할 필요가 없다.
SELECT order_item_id, product_name, order_price, order_quantity
FROM order_item
WHERE order_id = 3;

 

• 이 쿼리는 "order_id"가 order_item 테이블의 외래 키이므로 자동으로 생성된 인덱스를 사용해 매우 빠르게 동작한다.
  • 여기서 핵심은 "EXPLAIN" 결과보다도 SQL 쿼리 자체에 있다.
• 만약 우리가 "product_name"을 역정규화하지 않았다면, 상품명을 가져오기 위해 다음과 같이 항상 product 테이블과 "JOIN"을 해야만 했을 것이다.

-- 역정규화를 하지 않았다면 매번 실행해야 했을 쿼리
SELECT
    oi.order_item_id,
    p.product_name,
    oi.order_price,
    oi.order_quantity
FROM order_item oi
JOIN product p ON oi.product_id = p.product_id
WHERE oi.order_id = 3;

• 주문 상세 조회는 매우 빈번한 기능이다. 역정규화를 통해 이 "JOIN" 연산을 제거함으로써 우리는 데이터베이스의 부하를 크게 줄이고 서비스의 응답 속도를 향상시키는, 최적화를 수행한 것이다.

월별 매출 통계 리포트

상황: 경영진 보고를 위해 월별 총 주문 건수와 매출액을 집계해야 한다.

SELECT
	DATE_FORMAT(ordered_at, '%Y-%m') AS `year_month`,
	COUNT(order_id) AS total_orders,
	SUM(total_amount) AS total_sales
FROM orders
WHERE order_status NOT IN ('CANCELED') -- 취소된 주문은 매출에서 제외
GROUP BY `year_month`
ORDER BY `year_month`;

쿼리 결과

• "DATE_FORMAT" 함수를 사용하면 "DATETIME" 형식의 데이터를 "년-월" 또는 "일자" 등 원하는 형식으로 바꿔서 그룹핑할 수 있다.
• 참고로 WHERE 절에서 취소된 주문을 제외하는 것은 정확한 매출 통계를 위한 필수 조건이다.

베스트셀러 상품 TOP 5

상황: 2025년 8월 ~ 9월에 가장 많이 팔린 상품 5개를 판매량 순으로 확인하여 마케팅에 활용하고 싶다.

-- 판매량 기준 TOP 5
SELECT
	oi.product_name,
	SUM(oi.order_quantity) AS total_quantity_sold,
	SUM(oi.order_price * oi.order_quantity) AS total_sales_amount
FROM order_item oi
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_status NOT IN ('CANCELED')
AND o.ordered_at BETWEEN '2025-08-01 00:00:00' AND '2025-09-30 23:59:59'
GROUP BY oi.product_name
ORDER BY total_quantity_sold DESC, total_sales_amount DESC
LIMIT 5;

쿼리 결과

• 여러 테이블을 조인하여 복합적인 분석을 수행하는 쿼리이다.
• order_item의 판매량(order_quantity)과 판매가(order_price)를 SUM 함수로 집계한다.
• product 테이블과 조인할 필요 없이 역정규화된 "product_name"을 바로 사용해서 더 효율적이다.

VIP 고객 식별

상황: 총 구매액이 가장 높은 상위 2명의 고객에게 감사의 의미로 쿠폰을 발송하려고 한다.

SELECT
	m.member_id,
	m.member_name,
	m.email,
	SUM(o.total_amount) AS total_purchase_amount,
	COUNT(o.order_id) AS total_order_count
FROM member m
JOIN orders o ON m.member_id = o.member_id
WHERE o.order_status NOT IN ('CANCELED')
GROUP BY m.member_id, m.member_name, m.email
ORDER BY total_purchase_amount DESC
LIMIT 2;

쿼리 결과

• 고객(member)과 주문(orders) 테이블을 조인하여 고객별로 총 구매액을 집계한다.
• CRM(고객 관계 관리)에서 가장 기본적이면서도 중요한 분석 기법이다.

재고 부족 상품 확인

상황: 재고가 50개 미만으로 남은 상품 목록을 확인하여 재주문 프로세스를 시작해야 한다.

SELECT
	product_id,
	product_name,
	stock_quantity
FROM product
WHERE  stock_quantity < 50
ORDER BY stock_quantity ASC;

쿼리 결과

• 재고 관리 시스템의 핵심 기능이다.
• 스케줄러를 이용해 이 쿼리를 주기적으로 실행하고, 결과가 있을 경우 담당자에게 알림을 보내는 방식으로 자동화할 수 있다