[MySQL] 표준 프로세스대로 개발하기 (2)

2탄입니당

 

1탄 보고 오시죵

https://uchive.tistory.com/47

[MySQL] 표준 프로세스대로 개발하기 (1)

 

 

최종목표:

디비 개발하는 프로세스에 따라 개발을 해보겠습니다.

나라, 상점, 고객, 브랜드, 상품 테이블을 구성하고 인덱싱 후, 더미데이터를 생성해 보겠습니다.

 

📍요구사항 → 개념 설계(ERD) → 논리 설계 → 물리 설계 → 구현 → 테스트 → 운영

 

 

-- 나라 테이블
CREATE TABLE country (
    country_id INT AUTO_INCREMENT PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL,
    country_code CHAR(2) NOT NULL
);

-- 지점 테이블
CREATE TABLE shop (
    shop_id INT AUTO_INCREMENT PRIMARY KEY,
    shop_name VARCHAR(255) NOT NULL,
    country_id INT NOT NULL,
    FOREIGN KEY (country_id) REFERENCES country(country_id)
);

-- 브랜드 테이블
CREATE TABLE brand (
    brand_id INT AUTO_INCREMENT PRIMARY KEY,
    brand_name VARCHAR(255) NOT NULL
);

-- 상품 테이블
CREATE TABLE goods (
    goods_id INT AUTO_INCREMENT PRIMARY KEY,
    goods_name VARCHAR(255) NOT NULL,
    brand_id INT NOT NULL,
    FOREIGN KEY (brand_id) REFERENCES brand(brand_id)
);

-- 고객 테이블
CREATE TABLE customer (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

-- 주문_상품 테이블 (구매 테이블에 해당)
CREATE TABLE purchase_goods (
    purchase_goods_id INT AUTO_INCREMENT PRIMARY KEY, -- PK 추가 (구매 상세 ID)
    quantity INT NOT NULL DEFAULT 1,                  -- 상품 개수
    customer_id INT NOT NULL,
    goods_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
);

 

위처럼 물리적 설계를 해보았는데요.

 

 

 

‼️ 여기서 잠깐

오늘 배운 점을 적용해보자면 데이터가 수천만 건 이상 일때,

인덱싱을 해야 쿼리 성능으로 이어진다는 점입니다.

 

📌 인덱스 고려해야 할 컬럼

상품 테이블(goods)

• good_id 는 Pk이므로 클러스트 인덱스 존재
• goods_name은 자주 검색될 것으로 예상돼 보조 인덱스 필요(검색용 논클러스터 인덱스 or FULLTEXT 인덱스)

CREATE INDEX idx_goods_name ON goods(goods_name);

 

고객(customer)

• customer_id 는 PK이므로  클러스터 인덱스 
• email 은 UNIQUE 제약조건이므로 자동 논클러스터 인덱스 생성 

주문_상품(purchase_goods)

• purchase_goods_id 는 PK이무로 클러스터 인덱스 
• goods_id, customer_id 는  조인/검색에 따라 논클러스터 인덱스 추가

CREATE INDEX idx_purchase_customer ON purchase_goods(customer_id);
CREATE INDEX idx_purchase_goods ON purchase_goods(goods_id);
-- 복합검색이 잦다면
CREATE INDEX idx_purchase_customer_goods ON purchase_goods(customer_id, goods_id);

 

 

결론은 아래와 같습니당

1. PK = 클러스터 인덱스는 무조건 있음 (정렬 저장)
2. 검색 패턴(WHERE, JOIN, ORDER BY)에 맞춰 논클러스터 인덱스 추가
3. LIKE 검색 대비
접두 검색 (LIKE '노트북%') → 인덱스 활용 가능포함 검색 (LIKE '노트%') → 인덱스 활용 불가 → FULLTEXT 고려
4. 인덱스 과잉 생성 금지

 

 

 

3탄에서 만나용