#Step 25 — 최종 프로젝트

학습 목표

• 실무형 비즈니스 요구 12개를 SQL 로 답한다 (윈도우/CTE/JOIN/서브쿼리 총동원)
• 느린 쿼리 3개를 진단하고 튜닝한다 (EXPLAIN ANALYZE 로 전후 비교)
• 새 도메인("쿠폰") 스키마를 제약·동시성까지 고려해 설계한다
• 이 코스 이후 무엇을 공부할지 로드맵을 잡는다

선행 스텝: Step 04 ~ 24 전부 예상 소요: 180분+

이 스텝은 문제와 정답을 분리했습니다.

• problems.md — 문제 (먼저 스스로 푸세요)
• solutions.md — 정답 + 해설 + 실측 결과
• slow-queries.sql — Part 2 튜닝 실습용 100만 행 테이블/쿼리 (전문은 아래 실습 파일 섹션에 있습니다)

#구성

#Part 1 — 비즈니스 리포트 12문제 (110점)

매출 리포트, 카테고리 순위, RFM 세그먼트, 전년 대비 성장률, 재구매율, 이탈 고객, 장바구니 상품 조합, 코호트 리텐션, 등급별 AOV, 결제수단 분포, 리뷰-판매 상관, 누적 매출.

이 문제들은 실제 데이터 분석/그로스 팀이 매주 뽑는 리포트의 축소판입니다. 윈도우 함수(RANK, NTILE, LAG, SUM() OVER)와 CTE 를 자유자재로 쓰게 됩니다.

#Part 2 — 느린 쿼리 튜닝 3문제 (45점)

1. 함수로 감싼 날짜 — DATE(logged_at)='...' → sargable 범위로 (실측 128ms → 0.5ms)
2. 앞쪽 와일드카드 LIKE — LIKE '%detail%' 는 왜 인덱스를 못 쓰나, 그리고 인덱스가 답이 아닐 때
3. 상관 서브쿼리 — 행마다 반복 실행 → JOIN + GROUP BY

#Part 3 — 스키마 설계 (30점)

"쿠폰" 도메인. 정액/정률 할인, 유효기간, 발급 한도, 중복 발급 방지, 사용 추적을 제약(UNIQUE/CHECK/FK)과 트랜잭션으로 설계합니다.

총 200점. 160점 이상이면 완주.

#시작하는 법

# 1) 리포트 문제는 learner 계정으로 (읽기 위주)
mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop

# 2) 튜닝 실습 테이블 준비 (s25_logs, 100만 행)
mysql -h127.0.0.1 -P3307 -uroot -proot1234 shop -t < slow-queries.sql

# 3) 다 풀었으면 solutions.md 와 대조

규칙: 공용 테이블은 읽기만. 새 테이블은 s25_ 접두사. 끝나면 DROP TABLE s25_*.

#채점 기준 (요약)

#이후 학습 로드맵

이 코스는 MySQL 8.0 의 실무 핵심을 다뤘습니다. 다음은 여기서 뻗어나갈 방향입니다.

#1) MySQL 8.4 LTS / 9.x 변경점

• 8.4 (LTS, 2024): mysql_native_password 기본 비활성화, 여러 시스템 변수 기본값 변경, 그룹 복제 개선. 프로덕션은 8.0 → 8.4 LTS 로 넘어가는 흐름.
• 9.x (Innovation): mysql_native_password 제거, JavaScript 저장 프로시저(Enterprise), 벡터 타입/함수(VECTOR, AI 워크로드) 도입 등. Innovation 릴리스는 최신 기능이 빠르게 들어오지만 짧은 지원 주기라 학습용/신기능 검증용.
• 업그레이드 전 항상 릴리스 노트의 "Deprecated/Removed" 섹션을 확인하는 습관.

#2) 확장성(Scale) — 한 대로 안 될 때

• 읽기 확장: 이 코스에서 만든 레플리카를 늘려 읽기 부하 분산(Step 23).
• 샤딩(Sharding): 데이터를 여러 서버에 수평 분할. 샤드 키 선택이 전부(잘못 고르면 크로스-샤드 조인 지옥). 애플리케이션 레벨 샤딩의 복잡도를 먼저 이해할 것.
• ProxySQL: MySQL 앞단의 프록시. 읽기/쓰기 분리(쓰기는 소스, 읽기는 레플리카로 자동 라우팅), 커넥션 풀링, 쿼리 캐싱/라우팅 규칙, 쿼리 재작성. 운영에서 매우 흔함.
• Vitess: YouTube 가 만든 MySQL 수평 확장 플랫폼(현 CNCF). 샤딩을 애플리케이션에 거의 투명하게 만들어 줌. PlanetScale 등이 기반으로 사용. 대규모 MySQL 의 사실상 표준 중 하나.

#3) MySQL vs PostgreSQL — 언제 무엇을

"둘 다 훌륭한 DB 이고, 팀의 운영 역량과 워크로드 성격이 선택을 좌우한다"가 정답입니다. 종교전쟁 하지 마세요.

#4) 더 깊이 파고들 주제

• 격리 수준과 락을 깊게(팬텀/갭 락, 데드락 실전 디버깅) — Step 19 의 심화
• 옵티마이저 힌트와 통계, 히스토그램(ANALYZE TABLE ... UPDATE HISTOGRAM)
• 파티셔닝 + 아카이빙 자동화를 이벤트/외부 스케줄러로 (Step 20 + 21)
• 관측성: Percona Monitoring and Management(PMM), Prometheus + mysqld_exporter, pt-query-digest(Percona Toolkit)
• 온라인 스키마 변경: pt-online-schema-change, gh-ost(대형 테이블 무중단 ALTER)

#코스를 마치며

Step 04 의 SELECT 1 부터 여기 파티셔닝·복제·튜닝까지 왔습니다. SQL 은 문법이 아니라 "데이터로 질문에 답하는 사고방식" 입니다. 이제 그 도구를 손에 넣었습니다.

남은 건 실전에서 계속 EXPLAIN 을 읽고, 느린 쿼리를 잡고, 스키마를 고민하는 것뿐입니다. 수고하셨습니다. 🎓

← Step 24 — 모니터링과 튜닝

#실습 파일

이 스텝에서 실제로 실행하는 스크립트는 slow-queries.sql 하나입니다. Part 1(비즈니스 리포트)과 Part 3(스키마 설계)은 기존 shop 데이터베이스와 백지 상태에서 진행하고, Part 2(느린 쿼리 튜닝)에 들어가기 직전에 이 스크립트를 한 번 실행해 100만 행짜리 놀이터 테이블을 만듭니다. 그 뒤 problems.md 의 튜닝 문제를 풀고 solutions.md 의 튜닝안과 EXPLAIN ANALYZE 결과를 대조하면 됩니다.

#slow-queries.sql

위 "시작하는 법" 2번 단계에서 mysql -h127.0.0.1 -P3307 -uroot -proot1234 shop -t < slow-queries.sql 로 실행하는 파일입니다. Part 2 의 느린 쿼리 3종을 직접 눈으로 확인하기 위한 준비 + 실행 스크립트입니다.

• 놀이터 테이블 생성: USE shop; 뒤에 DROP TABLE IF EXISTS s25_logs; → CREATE TABLE s25_logs LIKE access_logs; 로 구조를 복제하고, INSERT INTO s25_logs SELECT * FROM access_logs; 로 100만 행을 그대로 채웁니다. 파일 주석대로 이 테이블은 보조 인덱스가 없는 상태로 시작합니다(원본 access_logs 자체가 PK 외에 인덱스가 없기 때문입니다 — Step 15 에서 직접 붙여 보던 그 테이블입니다). 덕분에 튜닝 실습에서 인덱스를 마음껏 추가/삭제해도 공용 access_logs 는 전혀 건드리지 않습니다. 스텝 규칙인 "공용 테이블은 읽기만, 새 테이블은 s25_ 접두사"를 그대로 따르는 구조입니다.
• ANALYZE TABLE s25_logs; 로 통계를 갱신합니다. 방금 대량 INSERT 한 테이블은 통계가 어긋나 있어 옵티마이저가 엉뚱한 실행 계획을 고를 수 있고, 그러면 튜닝 전후 비교가 오염됩니다. 이 한 줄이 있어야 측정이 공정해집니다.
• 느린 쿼리 #1 — EXPLAIN ANALYZE SELECT COUNT(*) FROM s25_logs WHERE DATE(logged_at) = '2024-06-15';. 컬럼을 DATE() 함수로 감싸면 조건이 sargable 하지 않게 되어, 인덱스를 새로 만들어 줘도 쓸 수 없습니다(비교 대상이 컬럼 값이 아니라 함수 결과이기 때문). 그래서 100만 행 전부에 함수를 적용하는 풀스캔이 됩니다(실측 128ms, 매치 2,979행). 해법은 두 가지를 함께 하는 것입니다 — ALTER TABLE s25_logs ADD INDEX idx_logged_at (logged_at); 로 인덱스를 만들고, 조건을 logged_at >= '2024-06-15' AND logged_at < '2024-06-16' 라는 반열린 범위로 재작성해 커버링 인덱스 레인지 스캔을 태웁니다(0.5ms, 결과 행 수는 동일).
• 느린 쿼리 #2 — EXPLAIN ANALYZE SELECT COUNT(*) FROM s25_logs WHERE path LIKE '%detail%';. 앞쪽 와일드카드는 B-트리 인덱스의 접두사 정렬을 이용할 수 없어 스캔 시작 지점을 잡지 못합니다. 게다가 detail 이 들어간 행이 전체의 25%(약 25만 행) 라 선택도가 낮아, 설령 path 에 인덱스를 걸어도 극적인 이득이 없습니다. "인덱스=만능"이 아니라는 것을 체감하는 문제이니, 직접 인덱스를 추가해 보고 그래도 안 빨라지는 것까지 확인하세요(해설의 대안: 정확 매치 IN, FULLTEXT, 접두 검색 LIKE 'detail%').
• 느린 쿼리 #3 — SELECT 절 안의 상관 서브쿼리((SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id AND o.status <> 'CANCELLED')). customers 의 행마다 서브쿼리가 반복 실행되고(실행 계획에 loops=30 으로 찍힙니다), 그 결과인 order_count 로 정렬하기 때문에 LIMIT 5 가 있어도 전체 고객을 다 계산해야 합니다. 지금 customers 는 30명뿐이라 체감 차이는 작지만, 패턴 자체가 데이터가 커지는 순간 치명적입니다. orders 를 LEFT JOIN + GROUP BY 로 한 번에 집계하는 형태로 바꾸는 것이 정답입니다(status <> 'CANCELLED' 는 WHERE 가 아니라 ON 절에).
• 주의: 맨 앞의 DROP TABLE IF EXISTS s25_logs; 때문에 재실행하면 놀이터 테이블이 초기화됩니다(직접 추가한 인덱스도 함께 사라집니다). 처음부터 다시 측정하고 싶을 때는 오히려 편리합니다. 실습이 끝나면 파일 마지막 주석대로 DROP TABLE IF EXISTS s25_logs; 로 정리하세요. 100만 행이 디스크에 그대로 남아 있으면 다른 실습에 부담이 됩니다.

-- =====================================================================
-- Step 25 — 최종 프로젝트 : slow-queries.sql
-- ---------------------------------------------------------------------
-- Part 2(느린 쿼리 튜닝) 실습용 놀이터입니다.
-- 100만 행 s25_logs 를 만들어 "느린 버전"을 직접 돌려보고,
-- solutions.md 의 튜닝안과 EXPLAIN ANALYZE 로 비교하세요.
--
-- 실행: mysql -h127.0.0.1 -P3307 -uroot -proot1234 shop -t < slow-queries.sql
-- =====================================================================
USE shop;

-- 놀이터 테이블 (access_logs 복제, 보조 인덱스 없음)
DROP TABLE IF EXISTS s25_logs;
CREATE TABLE s25_logs LIKE access_logs;
INSERT INTO s25_logs SELECT * FROM access_logs;
ANALYZE TABLE s25_logs;

-- ---------------------------------------------------------------------
-- 느린 쿼리 #1 : 날짜 컬럼을 함수로 감쌌다 (인덱스가 있어도 못 쓴다)
--   "2024-06-15 하루치 접근 로그 수"
-- ---------------------------------------------------------------------
EXPLAIN ANALYZE
SELECT COUNT(*) FROM s25_logs WHERE DATE(logged_at) = '2024-06-15';

-- ---------------------------------------------------------------------
-- 느린 쿼리 #2 : 앞쪽 와일드카드 LIKE (인덱스 사용 불가 + 낮은 선택도)
--   "path 에 'detail' 이 들어간 로그 수"
-- ---------------------------------------------------------------------
EXPLAIN ANALYZE
SELECT COUNT(*) FROM s25_logs WHERE path LIKE '%detail%';

-- ---------------------------------------------------------------------
-- 느린 쿼리 #3 : 상관 서브쿼리 (행마다 서브쿼리 반복 실행)
--   "고객별 주문 수 상위 5명"
-- ---------------------------------------------------------------------
EXPLAIN ANALYZE
SELECT c.name,
       (SELECT COUNT(*) FROM orders o
        WHERE o.customer_id = c.customer_id AND o.status <> 'CANCELLED') AS order_count
FROM customers c
ORDER BY order_count DESC
LIMIT 5;

-- 튜닝 후 비교는 solutions.md 참고. 끝나면 정리:
-- DROP TABLE IF EXISTS s25_logs;