샘플 데이터베이스

이 코스의 모든 Step 이 공유하는 예제 데이터베이스 shop 을 만드는 곳입니다. 가상의 온라인 쇼핑몰을 모델로 삼았고, 카테고리·고객·상품·주문·결제·후기·사원 테이블이 실제 서비스와 비슷한 모양으로 얽혀 있습니다. Step 04 의 단순 SELECT 부터 Step 21 의 파티셔닝까지, 교재에 나오는 예제 쿼리는 전부 이 데이터를 대상으로 실행합니다.

가장 중요한 특징은 재현 가능성입니다. 데이터를 만들 때 RAND() 를 전혀 쓰지 않고 나머지 연산(%)만 사용하기 때문에, 누가 몇 번을 실행하든 항상 똑같은 행이 만들어집니다. 덕분에 교재에 적힌 예제 결과가 여러분 화면의 결과와 정확히 일치합니다. 결과가 다르다면 데이터가 아니라 쿼리가 다른 것입니다.

전체 구성

파일	역할	만들어지는 것
`install.sh`	아래 SQL 들을 순서대로 실행하는 설치 스크립트	—
`01_schema.sql`	데이터베이스와 9개 테이블 DDL	`shop` 스키마
`02_seed_master.sql`	마스터 데이터 적재	tally 10,000 / 카테고리 17 / 고객 30 / 상품 40 / 사원 18
`03_seed_orders.sql`	트랜잭션 데이터 생성	주문 600 / 주문상세 약 1,200 / 결제 약 540 / 후기 약 100
`04_seed_big.sql`	대용량 테이블 생성 (선택)	`access_logs` 100만 행

테이블 관계는 다음과 같습니다.

categories ──┐
             └─< products ──< order_items >── orders >── customers
                    │                            │
                    └──< reviews >───────────────┘
                                        payments >── orders
employees (자기참조: manager_id → employee_id)
tally     (1~10000 숫자 테이블. 데이터 생성 보조용)

사용법

Docker 실습 환경(docker/docker-compose.yml)이 먼저 떠 있어야 합니다. MySQL 이 127.0.0.1:3307 에서 대기 중인 상태에서 이 디렉터리로 이동해 스크립트를 실행합니다.

cd docs/reference/mysql8/sql
chmod +x install.sh

# 스키마 + 마스터 + 주문 데이터 (약 5초)
./install.sh

# Step 15(인덱스) 이후에는 100만 행 access_logs 까지
./install.sh --big

Step 14 까지는 --big 없이도 충분합니다. 인덱스와 EXPLAIN 을 다루는 Step 15 부터 --big 이 필요하므로, 그때 다시 ./install.sh --big 을 실행하면 됩니다. 접속 정보는 MYSQL_HOST / MYSQL_PORT / MYSQL_USER / MYSQL_PASSWORD 환경 변수로 덮어쓸 수 있습니다.

파괴적 스크립트입니다

01_schema.sql 은 DROP TABLE, 02/03 은 TRUNCATE TABLE 로 시작합니다. 실습 중 만든 데이터가 있다면 전부 사라집니다. 다행히 이는 "언제든 깨끗한 초기 상태로 되돌릴 수 있다"는 뜻이기도 합니다. 실습하다 데이터를 망가뜨렸다면 ./install.sh 를 다시 돌리면 됩니다.

실습 파일

설치는 install.sh 하나로 끝나지만, 그 안에서 SQL 4개가 번호 순서대로 실행됩니다. 01_schema.sql 로 빈 테이블을 만들고 → 02_seed_master.sql 로 사람이 읽을 수 있는 고정 데이터를 넣고 → 03_seed_orders.sql 로 주문·결제·후기를 계산해서 생성한 뒤 → 필요할 때만 04_seed_big.sql 로 대용량 로그를 붙입니다. 순서 의존성이 있으므로 개별 실행할 때도 번호를 지켜야 합니다.

install.sh

전체 설치를 한 방에 끝내는 진입점입니다. set -euo pipefail 로 시작하므로 중간에 SQL 하나라도 실패하면 즉시 멈춥니다 — 절반만 적재된 애매한 상태로 넘어가지 않습니다.

접속 정보는 HOST="${MYSQL_HOST:-127.0.0.1}", PORT="${MYSQL_PORT:-3307}", USER="${MYSQL_USER:-learner}", PASS="${MYSQL_PASSWORD:-learn1234}" 로 기본값을 갖되 환경 변수로 덮어쓸 수 있게 되어 있습니다. 이 기본값은 docker/docker-compose.yml 과 맞춰져 있으니, 도커 환경을 그대로 쓴다면 아무것도 설정하지 않아도 됩니다.
HERE="$(cd "$(dirname "$0")" && pwd)" 로 스크립트 자신의 절대 경로를 구한 뒤 $MYSQL < "${HERE}/01_schema.sql" 식으로 호출합니다. 덕분에 어느 디렉터리에서 실행해도 SQL 파일을 찾아냅니다.
본격 적재 전에 SELECT VERSION() 을 한 번 날려 접속부터 확인합니다. 여기서 실패하면 컨테이너가 아직 안 떴거나 포트가 다른 것입니다.
01_schema.sql 은 DB 이름 없이 실행하고(파일 안에 CREATE DATABASE 가 있으므로), 02/03 은 $MYSQL shop < ... 처럼 shop 을 기본 DB 로 지정해 실행합니다.
if [[ "${1:-}" == "--big" ]] — 첫 번째 인자가 --big 일 때만 04_seed_big.sql 을 추가로 돌립니다. 20초~1분 걸리므로 기본에서는 제외되어 있습니다.

#!/usr/bin/env bash
# =====================================================================
# install.sh : 예제 DB 를 한 번에 세팅합니다.
#
#   ./install.sh          → 스키마 + 마스터 + 주문 데이터 (빠름, ~5초)
#   ./install.sh --big    → 위 + 100만 행 access_logs (Step 15 이후 필요)
#
# 접속 정보는 docker/docker-compose.yml 과 맞춰져 있습니다.
# =====================================================================
set -euo pipefail

HOST="${MYSQL_HOST:-127.0.0.1}"
PORT="${MYSQL_PORT:-3307}"
USER="${MYSQL_USER:-learner}"
PASS="${MYSQL_PASSWORD:-learn1234}"

HERE="$(cd "$(dirname "$0")" && pwd)"
MYSQL="mysql -h${HOST} -P${PORT} -u${USER} -p${PASS} --default-character-set=utf8mb4"

echo "▶ MySQL 접속 확인 (${HOST}:${PORT})"
$MYSQL -e "SELECT VERSION() AS mysql_version;"

echo "▶ 01_schema.sql"
$MYSQL < "${HERE}/01_schema.sql"

echo "▶ 02_seed_master.sql"
$MYSQL shop < "${HERE}/02_seed_master.sql"

echo "▶ 03_seed_orders.sql"
$MYSQL shop < "${HERE}/03_seed_orders.sql"

if [[ "${1:-}" == "--big" ]]; then
  echo "▶ 04_seed_big.sql (100만 행 생성 — 20초~1분 소요)"
  $MYSQL shop < "${HERE}/04_seed_big.sql"
fi

echo "✅ 완료. 접속:  mysql -h${HOST} -P${PORT} -u${USER} -p${PASS} shop"

01_schema.sql

shop 데이터베이스와 9개 테이블을 만드는 DDL 입니다. 가장 먼저, 그리고 스키마를 초기화하고 싶을 때마다 실행합니다. Step 02(데이터 타입)와 Step 13(제약 조건)의 내용이 실물로 들어 있으니, 그 Step 들을 읽을 때 이 파일을 함께 보면 이해가 빠릅니다.

문자셋은 DEFAULT CHARACTER SET utf8mb4 / COLLATE utf8mb4_0900_ai_ci 입니다. 한글과 이모지를 안전하게 담기 위한 MySQL 8 의 표준 선택입니다.
맨 앞의 DROP TABLE IF EXISTS 들은 FK 역순(reviews → payments → order_items → orders → products → categories → customers → employees → tally)으로, 즉 자식 테이블부터 나열되어 있습니다. 부모 테이블을 먼저 지우려 하면 외래 키 때문에 실패하기 때문입니다. 이 순서 덕분에 스크립트를 몇 번이든 다시 실행할 수 있습니다.
tally 는 1~10000 을 담는 숫자 테이블입니다. 그 자체로는 아무 의미가 없지만, 03_seed_orders.sql 에서 행을 늘리는 도구로 쓰입니다. SQL 로 데이터를 만들어낼 때 매우 자주 쓰는 기법입니다.
categories.parent_id 와 employees.manager_id 는 자기 자신을 참조하는 FK 입니다. 각각 카테고리 계층(대분류 5 + 소분류 12)과 4단계 조직도를 표현하며, Step 09 의 재귀 CTE 와 Step 07 의 SELF JOIN 실습 재료가 됩니다.
products.attrs JSON 컬럼은 Step 18 전용입니다. chk_products_price CHECK (price >= 0), chk_order_items_qty CHECK (quantity > 0), chk_reviews_rating CHECK (rating BETWEEN 1 AND 5) 같은 CHECK 제약은 MySQL 8.0.16+ 에서 실제로 동작하며 Step 13 에서 일부러 위반해 보게 됩니다.
ON DELETE 동작이 테이블마다 다릅니다. order_items/payments/reviews 는 CASCADE(부모가 지워지면 같이 삭제), categories 의 자기참조는 RESTRICT(자식이 있으면 삭제 거부)입니다. 이 차이를 Step 13 에서 직접 확인합니다.

-- =====================================================================
-- 01_schema.sql : 학습용 예제 데이터베이스 `shop` 스키마
-- ---------------------------------------------------------------------
-- 가상의 온라인 쇼핑몰입니다. 모든 Step 이 이 스키마를 공유합니다.
--
--   categories ──┐
--                └─< products ──< order_items >── orders >── customers
--                       │                            │           │
--                       └──< reviews >───────────────┘           │
--                                     └───────────────────────────┘
--   employees (자기참조: manager_id → employee_id)
--   tally     (1~10000 숫자 테이블. 데이터 생성/보조용)
--
-- 실행:  mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop < 01_schema.sql
-- =====================================================================

CREATE DATABASE IF NOT EXISTS shop
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_0900_ai_ci;

USE shop;

-- 재실행 가능하도록 자식 테이블부터 삭제 (FK 역순)
DROP TABLE IF EXISTS reviews;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS tally;

-- ---------------------------------------------------------------------
-- tally : 1 ~ 10000 숫자 테이블
--   "숫자 테이블"은 SQL 로 데이터를 만들어낼 때 매우 유용한 도구입니다.
--   (예: 주문 1건당 상품 N개 생성, 날짜 채우기, 빈 구간 메우기)
-- ---------------------------------------------------------------------
CREATE TABLE tally (
  n INT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB COMMENT='보조용 숫자 테이블 1..10000';

-- ---------------------------------------------------------------------
-- categories : 상품 카테고리 (parent_id 로 자기참조 → 계층 구조)
-- ---------------------------------------------------------------------
CREATE TABLE categories (
  category_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  parent_id    INT UNSIGNED NULL COMMENT '상위 카테고리. NULL 이면 최상위',
  name         VARCHAR(50)  NOT NULL,
  sort_order   SMALLINT     NOT NULL DEFAULT 0,
  PRIMARY KEY (category_id),
  CONSTRAINT fk_categories_parent
    FOREIGN KEY (parent_id) REFERENCES categories(category_id)
    ON DELETE RESTRICT
) ENGINE=InnoDB COMMENT='상품 카테고리(계층형)';

-- ---------------------------------------------------------------------
-- customers : 고객
-- ---------------------------------------------------------------------
CREATE TABLE customers (
  customer_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email        VARCHAR(120) NOT NULL,
  name         VARCHAR(50)  NOT NULL,
  phone        VARCHAR(20)  NULL,
  grade        ENUM('BRONZE','SILVER','GOLD','VIP') NOT NULL DEFAULT 'BRONZE',
  birth_date   DATE         NULL,
  city         VARCHAR(30)  NOT NULL,
  points       INT          NOT NULL DEFAULT 0 COMMENT '적립 포인트',
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (customer_id),
  UNIQUE KEY uk_customers_email (email)
) ENGINE=InnoDB COMMENT='고객';

-- ---------------------------------------------------------------------
-- products : 상품
--   attrs 는 JSON 컬럼입니다 (Step 18 에서 집중적으로 다룹니다).
-- ---------------------------------------------------------------------
CREATE TABLE products (
  product_id   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id  INT UNSIGNED NOT NULL,
  name         VARCHAR(100) NOT NULL,
  price        DECIMAL(10,2) NOT NULL COMMENT '판매가',
  cost         DECIMAL(10,2) NOT NULL COMMENT '원가',
  stock        INT          NOT NULL DEFAULT 0,
  status       ENUM('ON_SALE','SOLD_OUT','HIDDEN') NOT NULL DEFAULT 'ON_SALE',
  attrs        JSON         NULL COMMENT '상품별 가변 속성',
  created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (product_id),
  KEY idx_products_category (category_id),
  CONSTRAINT fk_products_category
    FOREIGN KEY (category_id) REFERENCES categories(category_id),
  CONSTRAINT chk_products_price CHECK (price >= 0),
  CONSTRAINT chk_products_stock CHECK (stock >= 0)
) ENGINE=InnoDB COMMENT='상품';

-- ---------------------------------------------------------------------
-- orders : 주문 헤더
--   total_amount 는 order_items 합계로 계산되어 채워집니다.
-- ---------------------------------------------------------------------
CREATE TABLE orders (
  order_id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id   INT UNSIGNED NOT NULL,
  order_date    DATETIME     NOT NULL,
  status        ENUM('PENDING','PAID','SHIPPED','DELIVERED','CANCELLED') NOT NULL DEFAULT 'PENDING',
  total_amount  DECIMAL(12,2) NOT NULL DEFAULT 0,
  shipping_city VARCHAR(30)  NOT NULL,
  PRIMARY KEY (order_id),
  KEY idx_orders_customer (customer_id),
  CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) ENGINE=InnoDB COMMENT='주문';

-- ---------------------------------------------------------------------
-- order_items : 주문 상세 (주문 1 : N 상품)
-- ---------------------------------------------------------------------
CREATE TABLE order_items (
  order_item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id      BIGINT UNSIGNED NOT NULL,
  product_id    INT UNSIGNED NOT NULL,
  quantity      INT          NOT NULL,
  unit_price    DECIMAL(10,2) NOT NULL COMMENT '주문 시점의 가격(스냅샷)',
  PRIMARY KEY (order_item_id),
  KEY idx_order_items_order (order_id),
  KEY idx_order_items_product (product_id),
  CONSTRAINT fk_order_items_order
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
  CONSTRAINT fk_order_items_product
    FOREIGN KEY (product_id) REFERENCES products(product_id),
  CONSTRAINT chk_order_items_qty CHECK (quantity > 0)
) ENGINE=InnoDB COMMENT='주문 상세';

-- ---------------------------------------------------------------------
-- payments : 결제
-- ---------------------------------------------------------------------
CREATE TABLE payments (
  payment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id   BIGINT UNSIGNED NOT NULL,
  method     ENUM('CARD','BANK','POINT','MOBILE') NOT NULL,
  amount     DECIMAL(12,2) NOT NULL,
  status     ENUM('DONE','REFUNDED') NOT NULL DEFAULT 'DONE',
  paid_at    DATETIME NOT NULL,
  PRIMARY KEY (payment_id),
  KEY idx_payments_order (order_id),
  CONSTRAINT fk_payments_order
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='결제';

-- ---------------------------------------------------------------------
-- reviews : 상품 후기
-- ---------------------------------------------------------------------
CREATE TABLE reviews (
  review_id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  product_id  INT UNSIGNED NOT NULL,
  customer_id INT UNSIGNED NOT NULL,
  rating      TINYINT UNSIGNED NOT NULL,
  title       VARCHAR(100) NULL,
  body        TEXT NULL,
  created_at  DATETIME NOT NULL,
  PRIMARY KEY (review_id),
  KEY idx_reviews_product (product_id),
  KEY idx_reviews_customer (customer_id),
  CONSTRAINT fk_reviews_product
    FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE CASCADE,
  CONSTRAINT fk_reviews_customer
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE,
  CONSTRAINT chk_reviews_rating CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB COMMENT='상품 후기';

-- ---------------------------------------------------------------------
-- employees : 사원 (자기참조 계층 → 재귀 CTE / SELF JOIN 실습용)
-- ---------------------------------------------------------------------
CREATE TABLE employees (
  employee_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name        VARCHAR(50) NOT NULL,
  manager_id  INT UNSIGNED NULL,
  dept        VARCHAR(30) NOT NULL,
  position    VARCHAR(30) NOT NULL,
  salary      DECIMAL(10,2) NOT NULL,
  hire_date   DATE NOT NULL,
  PRIMARY KEY (employee_id),
  KEY idx_employees_manager (manager_id),
  CONSTRAINT fk_employees_manager
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
) ENGINE=InnoDB COMMENT='사원(계층형 조직)';

02_seed_master.sql

사람이 눈으로 읽을 수 있는 고정 마스터 데이터를 넣습니다. 01_schema.sql 다음에 실행하며, USE shop → SET FOREIGN_KEY_CHECKS = 0 → 전체 TRUNCATE → 재적재 순서로 진행됩니다. FK 체크를 잠시 끄는 이유는 부모/자식 관계 때문에 TRUNCATE 가 거부되는 것을 피하기 위해서이고, 적재 직전에 SET FOREIGN_KEY_CHECKS = 1 로 다시 켭니다.

tally: SET SESSION cte_max_recursion_depth = 100000 을 먼저 지정한 뒤 WITH RECURSIVE seq AS (SELECT 1 ... WHERE n < 10000) 으로 1~10000 을 만들어 넣습니다. MySQL 의 기본 재귀 한도는 1000 이라서 이 설정이 없으면 "Recursive query aborted" 에러가 납니다. 재귀 CTE 의 원리는 Step 09 에서 설명합니다.
categories: 대분류를 1~5, 소분류를 11·12·13(패션), 21·22·23(디지털) 처럼 부모 번호 + 순번으로 지어 두었습니다. parent_id 를 눈으로 따라가기 쉬우라고 일부러 이렇게 만든 것입니다.
customers: 30명. grade(BRONZE/SILVER/GOLD/VIP), city(서울·부산·대구 등), birth_date 가 골고루 섞여 있어 Step 06 의 GROUP BY 실습에 바로 쓸 수 있습니다. phone 이 NULL 인 고객(7·14·28번)과 points 가 0 인 고객(7·17·29번)이 섞여 있는데, 이는 NULL 과 0 의 차이를 체감시키기 위한 의도적 배치입니다.
products: 40개. attrs JSON 에는 카테고리마다 다른 키가 들어 있습니다(노트북은 cpu/ram_gb, 식품은 origin/organic). 22번 'USB-C 허브'와 29번 '프리미엄 라면'은 attrs 가 NULL 인데, JSON 함수가 NULL 을 어떻게 다루는지 확인하는 재료입니다. 재고 stock 이 0 이면서 status = 'SOLD_OUT' 인 상품(4·27번), HIDDEN 상태인 상품(8번)도 조건 검색 실습용입니다.
employees: 18명, 4단계 조직도(CEO → 본부장 → 팀장 → 팀원). 파일 상단 주석의 트리 그림과 데이터가 정확히 일치하므로, 재귀 CTE 결과가 맞는지 대조해 볼 수 있습니다.
마지막 SELECT 는 각 테이블 건수를 한 줄로 찍어 줍니다. categories 17 / customers 30 / products 40 / employees 18 / tally 10000 이 나오면 정상입니다.

-- =====================================================================
-- 02_seed_master.sql : 마스터 데이터 (사람이 읽을 수 있는 고정 데이터)
--   tally / categories / customers / products / employees
--   * 트랜잭션 데이터(orders 등)는 03_seed_orders.sql 에서 "생성"합니다.
-- =====================================================================
USE shop;

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE reviews;
TRUNCATE TABLE payments;
TRUNCATE TABLE order_items;
TRUNCATE TABLE orders;
TRUNCATE TABLE products;
TRUNCATE TABLE categories;
TRUNCATE TABLE customers;
TRUNCATE TABLE employees;
TRUNCATE TABLE tally;
SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- tally : 1 ~ 10000
--   재귀 CTE 로 숫자를 만들어 INSERT 합니다. (Step 09 에서 원리 설명)
-- ---------------------------------------------------------------------
SET SESSION cte_max_recursion_depth = 100000;

INSERT INTO tally (n)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT n FROM seq;

-- ---------------------------------------------------------------------
-- categories : 2단계 계층 (대분류 5 + 소분류 12)
-- ---------------------------------------------------------------------
INSERT INTO categories (category_id, parent_id, name, sort_order) VALUES
  (1, NULL, '패션',      1),
  (2, NULL, '디지털',    2),
  (3, NULL, '식품',      3),
  (4, NULL, '리빙',      4),
  (5, NULL, '도서',      5),
  -- 패션
  (11, 1, '남성의류',  1),
  (12, 1, '여성의류',  2),
  (13, 1, '신발',      3),
  -- 디지털
  (21, 2, '노트북',    1),
  (22, 2, '스마트폰',  2),
  (23, 2, '주변기기',  3),
  -- 식품
  (31, 3, '신선식품',  1),
  (32, 3, '가공식품',  2),
  -- 리빙
  (41, 4, '주방용품',  1),
  (42, 4, '가구',      2),
  -- 도서
  (51, 5, 'IT/컴퓨터', 1),
  (52, 5, '소설',      2);

-- ---------------------------------------------------------------------
-- customers : 30명
--   grade / city / birth_date 가 골고루 섞여 있어 GROUP BY 실습에 좋습니다.
--   points 는 일부러 NULL 이 아닌 0 을 섞어 두었습니다.
-- ---------------------------------------------------------------------
INSERT INTO customers (customer_id, email, name, phone, grade, birth_date, city, points, created_at) VALUES
  ( 1,'kim.minsu@example.com',   '김민수','010-1000-0001','VIP',   '1985-03-12','서울',12500,'2023-01-05 10:11:00'),
  ( 2,'lee.jieun@example.com',   '이지은','010-1000-0002','GOLD',  '1992-07-23','서울', 8300,'2023-01-18 14:02:00'),
  ( 3,'park.chulsoo@example.com','박철수','010-1000-0003','SILVER','1978-11-02','부산', 3100,'2023-02-02 09:30:00'),
  ( 4,'choi.younghee@example.com','최영희','010-1000-0004','BRONZE','2000-05-30','대구',  200,'2023-02-14 16:45:00'),
  ( 5,'jung.hoon@example.com',   '정  훈','010-1000-0005','GOLD',  '1990-01-09','인천', 7600,'2023-03-01 11:00:00'),
  ( 6,'kang.sora@example.com',   '강소라','010-1000-0006','VIP',   '1988-09-17','서울',20100,'2023-03-22 13:20:00'),
  ( 7,'yoon.daehyun@example.com','윤대현',NULL,           'BRONZE','1995-12-25','광주',    0,'2023-04-03 08:05:00'),
  ( 8,'lim.sujin@example.com',   '임수진','010-1000-0008','SILVER','1983-04-18','대전', 2450,'2023-04-19 19:41:00'),
  ( 9,'han.jiho@example.com',    '한지호','010-1000-0009','BRONZE','1999-08-08','부산',  150,'2023-05-07 12:12:00'),
  (10,'oh.seyoung@example.com',  '오세영','010-1000-0010','GOLD',  '1991-02-14','서울', 9900,'2023-05-25 15:33:00'),
  (11,'seo.jiwoo@example.com',   '서지우','010-1000-0011','BRONZE','2001-10-01','수원',  600,'2023-06-11 10:50:00'),
  (12,'shin.taemin@example.com', '신태민','010-1000-0012','SILVER','1987-06-06','인천', 4200,'2023-06-29 17:27:00'),
  (13,'jang.hyewon@example.com', '장혜원','010-1000-0013','VIP',   '1993-03-03','서울',15800,'2023-07-14 09:09:00'),
  (14,'nam.gyuri@example.com',   '남규리',NULL,           'BRONZE','1997-11-19','울산',   80,'2023-07-30 21:15:00'),
  (15,'ko.eunji@example.com',    '고은지','010-1000-0015','GOLD',  '1989-05-21','대구', 6700,'2023-08-08 08:08:00'),
  (16,'moon.jaehyun@example.com','문재현','010-1000-0016','SILVER','1994-09-30','서울', 3900,'2023-08-27 14:44:00'),
  (17,'baek.seungho@example.com','백승호','010-1000-0017','BRONZE','2002-01-15','부산',    0,'2023-09-09 11:11:00'),
  (18,'ryu.hana@example.com',    '류하나','010-1000-0018','GOLD',  '1986-08-24','인천', 8800,'2023-09-21 18:30:00'),
  (19,'song.minji@example.com',  '송민지','010-1000-0019','VIP',   '1990-12-11','서울',17300,'2023-10-02 10:02:00'),
  (20,'cho.wonjun@example.com',  '조원준','010-1000-0020','SILVER','1996-04-04','광주', 2900,'2023-10-19 16:16:00'),
  (21,'hwang.doyun@example.com', '황도윤','010-1000-0021','BRONZE','1998-07-07','대전',  340,'2023-11-05 13:13:00'),
  (22,'ahn.jisoo@example.com',   '안지수','010-1000-0022','GOLD',  '1984-02-29','서울', 7100,'2023-11-23 09:55:00'),
  (23,'noh.kanghee@example.com', '노강희','010-1000-0023','BRONZE','2003-06-18','수원',   50,'2023-12-01 20:20:00'),
  (24,'gu.sejin@example.com',    '구세진','010-1000-0024','SILVER','1981-10-27','부산', 5000,'2023-12-18 07:47:00'),
  (25,'yang.hyunwoo@example.com','양현우','010-1000-0025','VIP',   '1979-01-31','서울',22400,'2024-01-08 11:34:00'),
  (26,'bae.chaeyoung@example.com','배채영','010-1000-0026','GOLD', '1993-08-13','인천', 6100,'2024-01-27 15:05:00'),
  (27,'jeon.somin@example.com',  '전소민','010-1000-0027','BRONZE','2000-03-09','울산',  700,'2024-02-14 12:00:00'),
  (28,'sim.junho@example.com',   '심준호',NULL,           'SILVER','1990-11-11','대구', 3300,'2024-03-03 17:17:00'),
  (29,'woo.yerin@example.com',   '우예린','010-1000-0029','BRONZE','2004-09-05','서울',    0,'2024-03-25 10:26:00'),
  (30,'ha.junseo@example.com',   '하준서','010-1000-0030','GOLD',  '1992-05-16','부산', 9200,'2024-04-10 08:31:00');

-- ---------------------------------------------------------------------
-- products : 40개
--   attrs(JSON) 에는 카테고리마다 다른 속성이 들어 있습니다.
--   * 일부 상품은 attrs 가 NULL 입니다 (JSON 함수의 NULL 처리 실습용)
-- ---------------------------------------------------------------------
INSERT INTO products (product_id, category_id, name, price, cost, stock, status, attrs, created_at) VALUES
  -- 남성의류(11)
  ( 1,11,'베이직 옥스퍼드 셔츠',      39000,  18000, 120,'ON_SALE', '{"color":["white","blue"],"size":["M","L","XL"],"material":"cotton"}',            '2023-01-10 09:00:00'),
  ( 2,11,'슬림핏 치노 팬츠',          49000,  22000,  80,'ON_SALE', '{"color":["beige","navy"],"size":["28","30","32","34"],"material":"cotton"}',    '2023-01-12 09:00:00'),
  ( 3,11,'라이트 다운 재킷',         159000,  71000,  25,'ON_SALE', '{"color":["black"],"size":["M","L"],"material":"nylon","season":"winter"}',      '2023-09-01 09:00:00'),
  ( 4,11,'울 니트 스웨터',            79000,  35000,   0,'SOLD_OUT','{"color":["gray","charcoal"],"size":["M","L"],"material":"wool"}',              '2023-10-05 09:00:00'),
  -- 여성의류(12)
  ( 5,12,'플리츠 롱스커트',           59000,  26000,  60,'ON_SALE', '{"color":["black","ivory"],"size":["S","M"],"material":"polyester"}',           '2023-02-01 09:00:00'),
  ( 6,12,'실크 블라우스',            129000,  58000,  30,'ON_SALE', '{"color":["ivory"],"size":["S","M","L"],"material":"silk"}',                    '2023-03-15 09:00:00'),
  ( 7,12,'트렌치 코트',              249000, 112000,  15,'ON_SALE', '{"color":["beige"],"size":["S","M"],"material":"cotton","season":"spring"}',    '2023-03-20 09:00:00'),
  ( 8,12,'니트 가디건',               69000,  30000,  45,'HIDDEN',  '{"color":["pink","cream"],"size":["FREE"],"material":"acrylic"}',               '2023-11-11 09:00:00'),
  -- 신발(13)
  ( 9,13,'클래식 스니커즈',           89000,  40000, 200,'ON_SALE', '{"color":["white"],"size":["250","260","270","280"],"brand":"Walker"}',         '2023-01-20 09:00:00'),
  (10,13,'러닝화 에어플로우',        139000,  62000,  90,'ON_SALE', '{"color":["black","red"],"size":["255","265","275"],"brand":"Runmax"}',         '2023-04-02 09:00:00'),
  (11,13,'첼시 부츠',                189000,  85000,  20,'ON_SALE', '{"color":["brown"],"size":["260","270"],"material":"leather"}',                 '2023-10-15 09:00:00'),
  -- 노트북(21)
  (12,21,'울트라북 14 i5/16GB',     1290000, 980000,  18,'ON_SALE', '{"cpu":"i5-1340P","ram_gb":16,"ssd_gb":512,"weight_kg":1.2,"tags":["business"]}','2023-02-10 09:00:00'),
  (13,21,'울트라북 14 i7/32GB',     1790000,1350000,   9,'ON_SALE', '{"cpu":"i7-1360P","ram_gb":32,"ssd_gb":1024,"weight_kg":1.25,"tags":["business","pro"]}','2023-02-10 09:00:00'),
  (14,21,'게이밍 노트북 RTX4060',   2190000,1720000,   6,'ON_SALE', '{"cpu":"i7-13700H","ram_gb":16,"ssd_gb":1024,"gpu":"RTX4060","tags":["gaming"]}','2023-05-18 09:00:00'),
  (15,21,'보급형 노트북 15',         690000, 520000,  40,'ON_SALE', '{"cpu":"i3-1215U","ram_gb":8,"ssd_gb":256,"weight_kg":1.7,"tags":["entry"]}',   '2023-06-01 09:00:00'),
  -- 스마트폰(22)
  (16,22,'스마트폰 X20 256GB',      1150000, 890000,  50,'ON_SALE', '{"storage_gb":256,"color":["black","silver"],"5g":true,"tags":["flagship"]}',   '2023-08-01 09:00:00'),
  (17,22,'스마트폰 X20 Pro 512GB',  1490000,1150000,  22,'ON_SALE', '{"storage_gb":512,"color":["titanium"],"5g":true,"tags":["flagship","pro"]}',   '2023-08-01 09:00:00'),
  (18,22,'스마트폰 A5 128GB',        399000, 300000, 130,'ON_SALE', '{"storage_gb":128,"color":["blue","white"],"5g":false,"tags":["entry"]}',       '2023-09-12 09:00:00'),
  -- 주변기기(23)
  (19,23,'기계식 키보드 87키',       129000,  58000, 150,'ON_SALE', '{"switch":"brown","layout":"87","backlight":true}',                             '2023-03-08 09:00:00'),
  (20,23,'무선 마우스 프로',          79000,  33000, 300,'ON_SALE', '{"dpi":16000,"wireless":true,"battery_h":70}',                                  '2023-03-08 09:00:00'),
  (21,23,'27인치 4K 모니터',         459000, 350000,  35,'ON_SALE', '{"size_inch":27,"resolution":"3840x2160","panel":"IPS","hz":60}',              '2023-04-22 09:00:00'),
  (22,23,'USB-C 허브 8in1',          59000,  24000, 400,'ON_SALE', NULL,                                                                            '2023-05-05 09:00:00'),
  (23,23,'노이즈캔슬링 헤드폰',      329000, 190000,  70,'ON_SALE', '{"anc":true,"wireless":true,"battery_h":30,"tags":["audio"]}',                  '2023-07-19 09:00:00'),
  -- 신선식품(31)
  (24,31,'제주 감귤 3kg',             19900,   9000, 500,'ON_SALE', '{"origin":"제주","weight_kg":3,"organic":false}',                               '2023-11-01 09:00:00'),
  (25,31,'한우 등심 300g',            48000,  35000,  60,'ON_SALE', '{"origin":"국내산","grade":"1++","weight_g":300}',                              '2023-06-15 09:00:00'),
  (26,31,'유기농 방울토마토 1kg',      8900,   4200, 300,'ON_SALE', '{"origin":"충남","weight_kg":1,"organic":true}',                                '2023-06-15 09:00:00'),
  (27,31,'노르웨이 연어 필렛 500g',   29000,  18000,   0,'SOLD_OUT','{"origin":"노르웨이","weight_g":500,"frozen":true}',                            '2023-07-01 09:00:00'),
  -- 가공식품(32)
  (28,32,'콜드브루 원액 1L',          15900,   6800, 250,'ON_SALE', '{"caffeine_mg":180,"volume_ml":1000}',                                          '2023-02-20 09:00:00'),
  (29,32,'프리미엄 라면 5입',          6900,   3100, 800,'ON_SALE', NULL,                                                                            '2023-02-20 09:00:00'),
  (30,32,'다크초콜릿 72% 100g',        4900,   1900, 600,'ON_SALE', '{"cacao_pct":72,"weight_g":100,"vegan":true}',                                  '2023-12-01 09:00:00'),
  -- 주방용품(41)
  (31,41,'스테인리스 냄비 24cm',      89000,  41000,  55,'ON_SALE', '{"material":"stainless","diameter_cm":24,"induction":true}',                    '2023-01-25 09:00:00'),
  (32,41,'무쇠 프라이팬 28cm',       119000,  55000,  40,'ON_SALE', '{"material":"cast_iron","diameter_cm":28,"induction":true}',                    '2023-01-25 09:00:00'),
  (33,41,'전기 주전자 1.7L',          49000,  21000, 110,'ON_SALE', '{"volume_l":1.7,"power_w":2200}',                                               '2023-08-30 09:00:00'),
  -- 가구(42)
  (34,42,'원목 4인 식탁',            459000, 260000,  12,'ON_SALE', '{"material":"oak","seats":4,"width_cm":140}',                                   '2023-04-10 09:00:00'),
  (35,42,'인체공학 사무용 의자',      329000, 180000,  28,'ON_SALE', '{"adjustable":true,"lumbar":true,"max_kg":120}',                               '2023-05-30 09:00:00'),
  (36,42,'6단 책장',                 129000,  62000,  33,'ON_SALE', '{"shelves":6,"height_cm":180,"material":"mdf"}',                                '2023-09-05 09:00:00'),
  -- IT/컴퓨터 도서(51)
  (37,51,'실전 MySQL 8',              38000,  22000, 200,'ON_SALE', '{"pages":720,"isbn":"978-89-0000-001","author":"김디비"}',                      '2023-03-01 09:00:00'),
  (38,51,'모던 자바스크립트',          34000,  19000, 180,'ON_SALE', '{"pages":560,"isbn":"978-89-0000-002","author":"이프론트"}',                    '2023-03-01 09:00:00'),
  (39,51,'쿠버네티스 인 액션',         45000,  26000,  95,'ON_SALE', '{"pages":680,"isbn":"978-89-0000-003","author":"박클라우드"}',                  '2023-10-20 09:00:00'),
  -- 소설(52)
  (40,52,'여름의 끝에서',             15800,   7000, 260,'ON_SALE', '{"pages":320,"isbn":"978-89-0000-004","author":"정소설"}',                      '2023-07-07 09:00:00');

-- ---------------------------------------------------------------------
-- employees : 18명, 4단계 조직도 (재귀 CTE 실습용)
--
--   1 대표 정한별
--   ├ 2  개발본부장 김코드
--   │  ├ 5  백엔드팀장 박서버   ├ 9,10,11 팀원
--   │  └ 6  프론트팀장 최화면   ├ 12,13   팀원
--   ├ 3  영업본부장 이세일
--   │  └ 7  영업1팀장 강매출     ├ 14,15  팀원
--   └ 4  경영지원본부장 오지원
--      └ 8  인사팀장 윤사람      ├ 16,17,18 팀원
-- ---------------------------------------------------------------------
INSERT INTO employees (employee_id, name, manager_id, dept, position, salary, hire_date) VALUES
  ( 1,'정한별', NULL,'경영진',    'CEO',       15000000,'2015-01-02'),
  ( 2,'김코드',    1,'개발본부',  '본부장',     9500000,'2016-03-14'),
  ( 3,'이세일',    1,'영업본부',  '본부장',     9000000,'2016-05-02'),
  ( 4,'오지원',    1,'경영지원',  '본부장',     8800000,'2017-01-09'),
  ( 5,'박서버',    2,'개발본부',  '팀장',       7200000,'2018-02-19'),
  ( 6,'최화면',    2,'개발본부',  '팀장',       7000000,'2018-07-01'),
  ( 7,'강매출',    3,'영업본부',  '팀장',       6800000,'2018-09-03'),
  ( 8,'윤사람',    4,'경영지원',  '팀장',       6500000,'2019-01-07'),
  ( 9,'한백엔',    5,'개발본부',  '시니어',     5800000,'2019-06-17'),
  (10,'임쿼리',    5,'개발본부',  '주니어',     4200000,'2022-03-02'),
  (11,'서인덱',    5,'개발본부',  '주니어',     4000000,'2023-01-16'),
  (12,'조리액',    6,'개발본부',  '시니어',     5600000,'2020-04-06'),
  (13,'남뷰어',    6,'개발본부',  '주니어',     3900000,'2023-08-01'),
  (14,'배계약',    7,'영업본부',  '시니어',     5400000,'2019-11-04'),
  (15,'전상담',    7,'영업본부',  '주니어',     3700000,'2024-02-01'),
  (16,'황채용',    8,'경영지원',  '시니어',     5200000,'2020-09-14'),
  (17,'노총무',    8,'경영지원',  '주니어',     3600000,'2023-05-08'),
  (18,'구회계',    8,'경영지원',  '주니어',     3800000,'2022-11-21');

SELECT '02_seed_master.sql 완료' AS msg,
       (SELECT COUNT(*) FROM categories) AS categories,
       (SELECT COUNT(*) FROM customers)  AS customers,
       (SELECT COUNT(*) FROM products)   AS products,
       (SELECT COUNT(*) FROM employees)  AS employees,
       (SELECT COUNT(*) FROM tally)      AS tally;

03_seed_orders.sql

주문·주문상세·결제·후기를 계산으로 생성합니다. 이 파일의 핵심은 RAND() 를 쓰지 않는다는 점입니다. (t.n * 17) % 30 처럼 나머지 연산으로 값을 뽑기 때문에 실행할 때마다 항상 동일한 데이터가 나오고, 그래서 교재의 예제 출력과 여러분의 결과가 일치합니다.

orders 600건: FROM tally t ... WHERE t.n <= 600 으로 600행을 만들고, JOIN customers c ON c.customer_id = 1 + (t.n * 17) % 30 으로 고객을 고르게 배정합니다. 주문일은 DATE_ADD('2024-01-01', INTERVAL (t.n * 37) % 730 DAY) 로 2024-01-01 부터 730일 구간에 흩뿌리고, 시/분까지 % 24, % 60 로 채웁니다. 상태는 ELT(1 + (t.n * 7) % 10, 'DELIVERED','DELIVERED','PAID',...) — ELT 목록에 'DELIVERED' 를 4번 넣어 40% 비중을 만든 것이 요령입니다.
order_items: JOIN tally t ON t.n <= 1 + (o.order_id % 3) 이 핵심입니다. 주문 1건이 tally 와 조인되며 1~3행으로 늘어나 주문당 상품 1~3개가 생깁니다. unit_price 에는 p.price 를 그대로 복사하는데, 이는 "주문 시점 가격 스냅샷"을 흉내 낸 것입니다. 나중에 상품 가격이 바뀌어도 과거 주문 금액은 그대로여야 하기 때문입니다.
total_amount 갱신: UPDATE orders o SET o.total_amount = (SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0) FROM order_items oi WHERE oi.order_id = o.order_id) — 상관 서브쿼리 UPDATE 의 교과서적 예시이고, Step 11 에서 다시 다룹니다.
payments: WHERE o.status <> 'PENDING' AND o.total_amount > 0 이라서 PENDING 주문에는 결제가 아예 없습니다. 이것이 Step 07 의 LEFT JOIN ... IS NULL 과 Step 08 의 NOT EXISTS 실습 재료입니다. CANCELLED 주문의 결제는 IF(o.status = 'CANCELLED', 'REFUNDED', 'DONE') 로 환불 상태가 됩니다.
reviews: WHERE o.status = 'DELIVERED' AND o.order_id % 3 = 0 AND oi.order_item_id % 2 = 0 — 배송완료 주문의 일부에만 후기가 달립니다. 평점은 ELT(1 + (oi.order_item_id % 10), 5,4,5,3,4,5,2,4,5,1) 로 4~5점에 몰리는 현실적인 분포를 만듭니다.
마지막 두 SELECT 가 건수와 상태별 분포를 출력하니, 숫자가 위 설명과 맞는지 확인하고 넘어가세요.

-- =====================================================================
-- 03_seed_orders.sql : 트랜잭션 데이터 생성 (orders / order_items / payments / reviews)
-- ---------------------------------------------------------------------
-- 핵심: RAND() 를 쓰지 않고 "나머지 연산(%)"으로 값을 만듭니다.
--       → 누가 몇 번을 실행하든 항상 똑같은 데이터가 나옵니다(재현 가능).
--       → 그래서 이후 Step 들의 예제 결과가 여러분 화면과 정확히 일치합니다.
--
-- 생성량: 주문 600건 / 주문상세 약 1,200건 / 결제 약 540건 / 후기 약 100건
-- =====================================================================
USE shop;

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE reviews;
TRUNCATE TABLE payments;
TRUNCATE TABLE order_items;
TRUNCATE TABLE orders;
SET FOREIGN_KEY_CHECKS = 1;

-- ---------------------------------------------------------------------
-- 1) orders : 600건
--    - 기간: 2024-01-01 ~ 2025-12-31 (730일에 고르게 분포)
--    - 상태: DELIVERED 40% / PAID 20% / SHIPPED 20% / CANCELLED 10% / PENDING 10%
--    - total_amount 는 0 으로 넣고 3) 에서 상세 합계로 갱신합니다.
-- ---------------------------------------------------------------------
INSERT INTO orders (order_id, customer_id, order_date, status, total_amount, shipping_city)
SELECT
    t.n                                             AS order_id,
    c.customer_id,
    TIMESTAMPADD(MINUTE, (t.n * 7)  % 60,
      TIMESTAMPADD(HOUR, (t.n * 13) % 24,
        DATE_ADD('2024-01-01', INTERVAL (t.n * 37) % 730 DAY)))   AS order_date,
    ELT(1 + (t.n * 7) % 10,
        'DELIVERED','DELIVERED','PAID','SHIPPED','DELIVERED',
        'CANCELLED','PAID','DELIVERED','SHIPPED','PENDING')       AS status,
    0                                               AS total_amount,
    c.city                                          AS shipping_city
FROM tally t
JOIN customers c ON c.customer_id = 1 + (t.n * 17) % 30
WHERE t.n <= 600;

-- ---------------------------------------------------------------------
-- 2) order_items : 주문 1건당 1~3개 상품
--    tally 를 CROSS JOIN 해서 "행을 늘리는" 전형적인 기법입니다.
-- ---------------------------------------------------------------------
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
    o.order_id,
    p.product_id,
    1 + (o.order_id + t.n) % 3                      AS quantity,
    p.price                                         AS unit_price
FROM orders o
JOIN tally t
      ON t.n <= 1 + (o.order_id % 3)                -- 주문당 상품 개수 1~3
JOIN products p
      ON p.product_id = 1 + (o.order_id * 7 + t.n * 13) % 40
ORDER BY o.order_id, t.n;

-- ---------------------------------------------------------------------
-- 3) orders.total_amount 갱신 (상세 합계)
--    상관 서브쿼리를 이용한 UPDATE. Step 11 에서 다시 다룹니다.
-- ---------------------------------------------------------------------
UPDATE orders o
SET o.total_amount = (
    SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
    FROM order_items oi
    WHERE oi.order_id = o.order_id
);

-- ---------------------------------------------------------------------
-- 4) payments
--    - PAID / SHIPPED / DELIVERED : 정상 결제 1건 (status = DONE)
--    - CANCELLED                  : 결제 후 환불 1건 (status = REFUNDED)
--    - PENDING                    : 결제 없음  ← LEFT JOIN / NOT EXISTS 실습 재료
-- ---------------------------------------------------------------------
INSERT INTO payments (order_id, method, amount, status, paid_at)
SELECT
    o.order_id,
    ELT(1 + (o.order_id % 4), 'CARD','BANK','POINT','MOBILE')     AS method,
    o.total_amount,
    IF(o.status = 'CANCELLED', 'REFUNDED', 'DONE')                AS status,
    TIMESTAMPADD(MINUTE, 5 + (o.order_id % 120), o.order_date)    AS paid_at
FROM orders o
WHERE o.status <> 'PENDING'
  AND o.total_amount > 0;

-- ---------------------------------------------------------------------
-- 5) reviews
--    배송완료(DELIVERED) 주문 중 일부에만 후기가 달립니다.
--    평점은 4~5점에 몰리도록(현실적인 분포) 만들었습니다.
-- ---------------------------------------------------------------------
INSERT INTO reviews (product_id, customer_id, rating, title, body, created_at)
SELECT
    oi.product_id,
    o.customer_id,
    ELT(1 + (oi.order_item_id % 10), 5,4,5,3,4,5,2,4,5,1)         AS rating,
    CONCAT(p.name, ' 후기')                                       AS title,
    ELT(1 + (oi.order_item_id % 5),
        '배송도 빠르고 품질도 좋아요. 재구매 의사 있습니다.',
        '가격 대비 만족스럽습니다.',
        '사진이랑 조금 다르지만 무난해요.',
        '기대보다 별로였어요. 아쉽습니다.',
        '완전 강추합니다! 주변에도 추천했어요.')                   AS body,
    DATE_ADD(o.order_date, INTERVAL 7 DAY)                        AS created_at
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p     ON p.product_id = oi.product_id
WHERE o.status = 'DELIVERED'
  AND o.order_id % 3 = 0
  AND oi.order_item_id % 2 = 0;

-- ---------------------------------------------------------------------
-- 검증
-- ---------------------------------------------------------------------
SELECT '03_seed_orders.sql 완료' AS msg,
       (SELECT COUNT(*) FROM orders)      AS orders,
       (SELECT COUNT(*) FROM order_items) AS order_items,
       (SELECT COUNT(*) FROM payments)    AS payments,
       (SELECT COUNT(*) FROM reviews)     AS reviews;

SELECT status, COUNT(*) AS cnt, FORMAT(SUM(total_amount),0) AS sum_amount
FROM orders GROUP BY status ORDER BY cnt DESC;

04_seed_big.sql

access_logs 테이블에 100만 행을 만듭니다. ./install.sh --big 을 줄 때만 실행되며, Step 15(인덱스) 이후에 필요합니다. 600건짜리 orders 로는 인덱스 효과가 보이지 않기 때문입니다 — 행이 적으면 옵티마이저가 풀스캔을 고르는 게 실제로 더 빠르고, 그래서 EXPLAIN 을 봐도 배울 게 없습니다.

테이블 정의에 보조 인덱스가 하나도 없습니다. PRIMARY KEY (log_id) 뿐입니다. 이것은 실수가 아니라 의도입니다. Step 15/16 에서 인덱스 없는 상태의 느린 쿼리를 먼저 측정하고, 직접 인덱스를 붙여 개선 폭을 눈으로 확인하기 위한 출발점입니다.
100만 행을 한 번에 넣으면 언두 로그가 비대해지므로 CREATE PROCEDURE gen_access_logs(IN total_rows INT, IN batch_size INT) 를 만들어 CALL gen_access_logs(1000000, 100000) — 10만 행씩 10배치로 나눠 넣고 배치마다 COMMIT 합니다. 저장 프로시저와 DELIMITER $$ 문법은 Step 20 에서 자세히 다룹니다.
여기서도 재귀 CTE 로 행을 만들므로 SET SESSION cte_max_recursion_depth = 2000000 을 미리 올려 둡니다. 실제로 재귀가 도는 깊이는 한 배치인 batch_size(=100,000) 만큼이므로, 이 값이 batch_size 보다 작으면 "Recursive query aborted" 로 실패합니다. MySQL 기본값 1000 으로는 어림도 없어서 반드시 올려야 하고, 2000000 은 배치 크기를 키워도 되도록 넉넉히 잡아 둔 것입니다.
로그 값은 전부 나머지 연산으로 만듭니다. status_code 는 ELT(...) 목록에 200 을 15개, 나머지(304/400/404/500/503)를 5개 넣어 정상 응답 75% 의 현실적인 분포를 만듭니다. logged_at 은 TIMESTAMPADD(SECOND, ((done + i) * 29) % 63072000, '2024-01-01 00:00:00') 로 2024-01-01 부터 2년(63,072,000초) 구간에 퍼집니다 — Step 21 의 날짜 파티셔닝 실습이 여기에 기댑니다.
생성이 끝나면 DROP PROCEDURE gen_access_logs 로 프로시저를 정리하고 ANALYZE TABLE access_logs 로 통계를 갱신합니다. 통계가 오래되면 옵티마이저가 엉뚱한 실행 계획을 고르는데, 그 의미는 Step 16 에서 설명합니다.
소요 시간 20초~1분입니다. 멈춘 게 아니니 기다리세요. 끝나면 information_schema.tables 를 조회해 데이터/인덱스 크기를 MB 단위로 보여 줍니다. 인덱스를 붙이기 전이라 index_mb 가 0 에 가깝다는 점을 기억해 두고, Step 15 이후에 다시 조회해 비교해 보세요.

-- =====================================================================
-- 04_seed_big.sql : 대용량 테이블 생성 (인덱스 / EXPLAIN / 파티셔닝 실습용)
-- ---------------------------------------------------------------------
-- shop 의 600건짜리 orders 로는 인덱스 효과가 눈에 보이지 않습니다.
-- (행이 적으면 옵티마이저가 그냥 풀스캔을 고르는 게 실제로 더 빠릅니다)
--
-- 그래서 100만 행짜리 access_logs 를 만듭니다.
--   * 처음에는 "인덱스가 하나도 없는" 상태로 둡니다. Step 15/16 에서 직접 붙입니다.
--   * 소요 시간: 노트북 기준 20초 ~ 1분. 커피 한 잔 하고 오세요.
--
-- 실행:  mysql -h127.0.0.1 -P3307 -ulearner -plearn1234 shop < 04_seed_big.sql
-- =====================================================================
USE shop;

DROP TABLE IF EXISTS access_logs;

CREATE TABLE access_logs (
  log_id      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  path        VARCHAR(100) NOT NULL,
  method      ENUM('GET','POST','PUT','DELETE') NOT NULL,
  status_code SMALLINT NOT NULL,
  duration_ms INT NOT NULL,
  user_agent  VARCHAR(60) NOT NULL,
  logged_at   DATETIME NOT NULL,
  PRIMARY KEY (log_id)
  -- 보조 인덱스 없음! Step 15 에서 직접 만들어 봅니다.
) ENGINE=InnoDB COMMENT='접근 로그(대용량 실습용)';

SET SESSION cte_max_recursion_depth = 2000000;

-- 100만 행을 한 번에 만들면 언두 로그가 커지므로 10만 행씩 10번 나눠 넣습니다.
-- (한 번에 넣고 싶다면 아래 프로시저 대신 seq 조건을 1000000 으로 바꾸세요)
DROP PROCEDURE IF EXISTS gen_access_logs;
DELIMITER $$
CREATE PROCEDURE gen_access_logs(IN total_rows INT, IN batch_size INT)
BEGIN
  DECLARE done INT DEFAULT 0;

  WHILE done < total_rows DO
    INSERT INTO access_logs (customer_id, path, method, status_code, duration_ms, user_agent, logged_at)
    WITH RECURSIVE seq AS (
      SELECT 1 AS i
      UNION ALL
      SELECT i + 1 FROM seq WHERE i < batch_size
    )
    SELECT
      1 + ((done + i) * 7)  % 30                                                    AS customer_id,
      ELT(1 + ((done + i) * 3) % 8,
          '/', '/products', '/products/detail', '/cart', '/orders',
          '/orders/detail', '/mypage', '/search')                                   AS path,
      ELT(1 + ((done + i) * 11) % 10, 'GET','GET','GET','GET','GET','GET','GET','POST','POST','PUT') AS method,
      ELT(1 + ((done + i) * 13) % 20,
          200,200,200,200,200,200,200,200,200,200,
          200,200,200,200,200,304,400,404,500,503)                                  AS status_code,
      1 + ((done + i) * 31) % 3000                                                  AS duration_ms,
      ELT(1 + ((done + i) * 5) % 4, 'Chrome/120','Safari/17','Edge/120','Bot/1.0')  AS user_agent,
      TIMESTAMPADD(SECOND, ((done + i) * 29) % 63072000, '2024-01-01 00:00:00')     AS logged_at
    FROM seq;

    SET done = done + batch_size;
    COMMIT;
  END WHILE;
END$$
DELIMITER ;

-- 100만 행 (10만 x 10 배치)
CALL gen_access_logs(1000000, 100000);

DROP PROCEDURE gen_access_logs;

-- 옵티마이저가 최신 통계로 판단하도록 통계 갱신 (Step 16 에서 의미 설명)
ANALYZE TABLE access_logs;

SELECT '04_seed_big.sql 완료' AS msg, COUNT(*) AS rows_generated FROM access_logs;

SELECT
  table_name,
  ROUND(data_length  / 1024 / 1024, 1) AS data_mb,
  ROUND(index_length / 1024 / 1024, 1) AS index_mb,
  table_rows AS approx_rows
FROM information_schema.tables
WHERE table_schema = 'shop' AND table_name = 'access_logs';

#샘플 데이터베이스

#전체 구성

#사용법

#실습 파일

#install.sh

#01_schema.sql

#02_seed_master.sql

#03_seed_orders.sql

#04_seed_big.sql