FK는 공짜가 아니다

-- ─────────────────────────────────────────────────────────
-- Step 0. 격리 DB + 숫자 생성기 (이걸 교차조인해서 대량 행 생성)
-- ─────────────────────────────────────────────────────────
DROP DATABASE IF EXISTS fk_bench;
CREATE DATABASE fk_bench;
USE fk_bench;
SET SESSION cte_max_recursion_depth = 2000;

CREATE TABLE nums (n INT PRIMARY KEY);
INSERT INTO nums (n)
WITH RECURSIVE seq AS (SELECT 0 AS n UNION ALL SELECT n+1 FROM seq WHERE n < 999)
SELECT n FROM seq;   -- 0..999

-- ─────────────────────────────────────────────────────────
-- Step 1. 부모(차원) 테이블 6종 + 적재
-- ─────────────────────────────────────────────────────────
-- users 100만
CREATE TABLE users (
  id BIGINT PRIMARY KEY, email VARCHAR(120) NOT NULL, name VARCHAR(50) NOT NULL,
  phone VARCHAR(20) NOT NULL, status VARCHAR(20) NOT NULL, point INT NOT NULL,
  created_at DATETIME(6) NOT NULL
) ENGINE=InnoDB;
INSERT INTO users (id, email, name, phone, status, point, created_at)
SELECT a.n*1000+b.n, CONCAT('user', a.n*1000+b.n, '@loopers.com'),
       CONCAT('사용자', a.n*1000+b.n), CONCAT('010', LPAD(FLOOR(RAND()*1e8),8,'0')),
       ELT(1+FLOOR(RAND()*3),'ACTIVE','DORMANT','BANNED'),
       FLOOR(RAND()*100000), NOW(6) - INTERVAL FLOOR(RAND()*1000) DAY
FROM nums a CROSS JOIN nums b;

-- products 100만
CREATE TABLE products (
  id BIGINT PRIMARY KEY, name VARCHAR(120) NOT NULL, price DECIMAL(12,2) NOT NULL,
  stock INT NOT NULL, status VARCHAR(20) NOT NULL, created_at DATETIME(6) NOT NULL
) ENGINE=InnoDB;
INSERT INTO products (id, name, price, stock, status, created_at)
SELECT a.n*1000+b.n, CONCAT('상품-', a.n*1000+b.n), ROUND(RAND()*490000+1000,2),
       FLOOR(RAND()*1000), ELT(1+FLOOR(RAND()*2),'ON_SALE','SOLD_OUT'),
       NOW(6) - INTERVAL FLOOR(RAND()*500) DAY
FROM nums a CROSS JOIN nums b;

-- addresses 100만
CREATE TABLE addresses (
  id BIGINT PRIMARY KEY, zipcode VARCHAR(10) NOT NULL, line1 VARCHAR(200) NOT NULL,
  line2 VARCHAR(100), recipient VARCHAR(50) NOT NULL, created_at DATETIME(6) NOT NULL
) ENGINE=InnoDB;
INSERT INTO addresses (id, zipcode, line1, line2, recipient, created_at)
SELECT a.n*1000+b.n, LPAD(FLOOR(RAND()*100000),5,'0'),
       CONCAT('서울시 ', a.n*1000+b.n, '로'), CONCAT(FLOOR(RAND()*1000),'호'),
       CONCAT('받는이', a.n*1000+b.n), NOW(6)
FROM nums a CROSS JOIN nums b;

-- merchants 10만
CREATE TABLE merchants (
  id BIGINT PRIMARY KEY, name VARCHAR(100) NOT NULL, region VARCHAR(30) NOT NULL,
  created_at DATETIME(6) NOT NULL
) ENGINE=InnoDB;
INSERT INTO merchants (id, name, region, created_at)
SELECT a.n*100+b.n, CONCAT('판매자-', a.n*100+b.n),
       ELT(1+FLOOR(RAND()*5),'SEOUL','BUSAN','DAEGU','INCHEON','GWANGJU'), NOW(6)
FROM nums a CROSS JOIN nums b WHERE b.n < 100;

-- coupons 10만
CREATE TABLE coupons (
  id BIGINT PRIMARY KEY, code VARCHAR(30) NOT NULL, discount_rate INT NOT NULL,
  valid_until DATE NOT NULL
) ENGINE=InnoDB;
INSERT INTO coupons (id, code, discount_rate, valid_until)
SELECT a.n*100+b.n, CONCAT('CPN', LPAD(a.n*100+b.n,8,'0')),
       ELT(1+FLOOR(RAND()*4),5,10,15,20), DATE_ADD(CURDATE(), INTERVAL FLOOR(RAND()*365) DAY)
FROM nums a CROSS JOIN nums b WHERE b.n < 100;

-- categories 1천
CREATE TABLE categories (id BIGINT PRIMARY KEY, name VARCHAR(50) NOT NULL) ENGINE=InnoDB;
INSERT INTO categories (id, name) SELECT n, CONCAT('카테고리-', n) FROM nums;

-- ─────────────────────────────────────────────────────────
-- Step 2. 거래(자식) 테이블 — 17컬럼 + FK컬럼6 + 인덱스6 (FK 제약은 아직 없음)
-- ─────────────────────────────────────────────────────────
CREATE TABLE transactions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL, product_id BIGINT NOT NULL, merchant_id BIGINT NOT NULL,
  category_id BIGINT NOT NULL, address_id BIGINT NOT NULL, coupon_id BIGINT NOT NULL,
  order_no VARCHAR(40) NOT NULL, quantity INT NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL, discount_amount DECIMAL(12,2) NOT NULL,
  total_amount DECIMAL(12,2) NOT NULL, payment_method VARCHAR(20) NOT NULL,
  status VARCHAR(20) NOT NULL, memo VARCHAR(255),
  created_at DATETIME(6) NOT NULL, updated_at DATETIME(6) NOT NULL,
  KEY k_user(user_id), KEY k_product(product_id), KEY k_merchant(merchant_id),
  KEY k_category(category_id), KEY k_address(address_id), KEY k_coupon(coupon_id)
) ENGINE=InnoDB;

-- 측정용 INSERT (30만 건) — Step 3~7에서 매번 이 블록을 재사용
INSERT INTO transactions
  (user_id, product_id, merchant_id, category_id, address_id, coupon_id,
   order_no, quantity, unit_price, discount_amount, total_amount,
   payment_method, status, memo, created_at, updated_at)
SELECT FLOOR(RAND()*1000000), FLOOR(RAND()*1000000), FLOOR(RAND()*100000),
       FLOOR(RAND()*1000), FLOOR(RAND()*1000000), FLOOR(RAND()*100000),
       CONCAT('ORD-', LPAD(a.n*1000+b.n,12,'0')), 1+FLOOR(RAND()*5),
       ROUND(RAND()*100000,2), ROUND(RAND()*5000,2), ROUND(RAND()*100000,2),
       ELT(1+FLOOR(RAND()*3),'CARD','BANK','POINT'),
       ELT(1+FLOOR(RAND()*4),'PAID','PENDING','CANCELLED','REFUNDED'),
       CONCAT('주문메모 ', a.n*1000+b.n), NOW(6), NOW(6)
FROM nums a CROSS JOIN nums b WHERE a.n < 300;

-- ─────────────────────────────────────────────────────────
-- Step 3~7. FK를 0→1→2→4→6개로 늘려가며 매 단계 측정
--   각 단계: TRUNCATE → (FK 추가) → 위 '측정용 INSERT' 실행 → 시간 기록
--   ※ 캐시 워밍 위해 한 번 더 돌려 두 번째 값 사용
-- ─────────────────────────────────────────────────────────
-- [FK 0개] 기준선: 위 측정용 INSERT 그대로 실행

-- [FK 1개]
TRUNCATE transactions;
ALTER TABLE transactions ADD CONSTRAINT fk_user     FOREIGN KEY (user_id)     REFERENCES users(id);
-- → 측정용 INSERT 실행

-- [FK 2개]
TRUNCATE transactions;
ALTER TABLE transactions ADD CONSTRAINT fk_product  FOREIGN KEY (product_id)  REFERENCES products(id);
-- → 측정용 INSERT 실행

-- [FK 4개]
TRUNCATE transactions;
ALTER TABLE transactions ADD CONSTRAINT fk_merchant FOREIGN KEY (merchant_id) REFERENCES merchants(id);
ALTER TABLE transactions ADD CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(id);
-- → 측정용 INSERT 실행

-- [FK 6개]
TRUNCATE transactions;
ALTER TABLE transactions ADD CONSTRAINT fk_address  FOREIGN KEY (address_id)  REFERENCES addresses(id);
ALTER TABLE transactions ADD CONSTRAINT fk_coupon   FOREIGN KEY (coupon_id)   REFERENCES coupons(id);
-- → 측정용 INSERT 실행