인덱스 설계 - 실습

물리적 모델링에서 가장 중요한 작업 중 하나가 인덱스 설계다. 단순히 PK만 잡는 것이 아니라, 자주 사용하는 조회 패턴에 맞춰 인덱스를 설계해야 실제 서비스에서 빠른 응답을 얻을 수 있다.

2-1. 기본 키와 외래 키 설계

먼저 각 테이블에 기본 키(PK)와 외래 키(FK)를 정의한다. 아래는 각 테이블의 핵심 키 구조이다.

member 테이블

CREATE TABLE member (
  member_id   BIGINT       NOT NULL AUTO_INCREMENT,
  login_id    VARCHAR(50)  NOT NULL,
  password    VARCHAR(255) NOT NULL,
  member_name VARCHAR(100) NOT NULL,
  email       VARCHAR(100) NOT NULL,
  created_at  DATETIME     NOT NULL,
  updated_at  DATETIME     NOT NULL,
  PRIMARY KEY (member_id),
  UNIQUE KEY uq_login_id (login_id)
);

member_id를 PK로 사용한다.
로그인에 사용하는 login_id는 UNIQUE 제약을 걸어 중복을 막고, 자동으로 인덱스를 얻는다. 로그인 쿼리의 핵심이다.

product 테이블

CREATE TABLE product (
  product_id      BIGINT       NOT NULL AUTO_INCREMENT,
  product_name    VARCHAR(200) NOT NULL,
  price           INT          NOT NULL,
  stock_quantity  INT          NOT NULL,
  created_at      DATETIME     NOT NULL,
  updated_at      DATETIME     NOT NULL,
  PRIMARY KEY (product_id),
  KEY idx_product_name (product_name)
);

product_id가 PK다.
상품 검색을 위해 product_name에 인덱스를 둔다.

orders 테이블

CREATE TABLE orders (
  order_id      BIGINT       NOT NULL AUTO_INCREMENT,
  member_id     BIGINT       NOT NULL,
  total_amount  INT          NOT NULL,
  order_status  VARCHAR(20)  NOT NULL,
  ordered_at    DATETIME     NOT NULL,
  created_at    DATETIME     NOT NULL,
  updated_at    DATETIME     NOT NULL,
  PRIMARY KEY (order_id),
  KEY idx_orders_member_id (member_id),
  KEY idx_order_status_ordered_at (order_status, ordered_at),
  CONSTRAINT fk_orders_member
    FOREIGN KEY (member_id) REFERENCES member(member_id)
);

member_id 외래 키에 인덱스를 두어 회원별 주문 목록 조회를 빠르게 만든다.
관리자 화면에서 자주 사용하는 패턴인 WHERE order_status = ? AND ordered_at BETWEEN ...을 위해 (order_status, ordered_at) 복합 인덱스를 만든다.

order_item 테이블

CREATE TABLE order_item (
  order_item_id   BIGINT       NOT NULL AUTO_INCREMENT,
  order_id        BIGINT       NOT NULL,
  product_id      BIGINT       NOT NULL,
  product_name    VARCHAR(200) NOT NULL, -- 역정규화: 주문 시점의 상품명
  order_price     INT          NOT NULL, -- 역정규화: 주문 시점의 상품 가격
  order_quantity  INT          NOT NULL,
  created_at      DATETIME     NOT NULL,
  updated_at      DATETIME     NOT NULL,
  PRIMARY KEY (order_item_id),
  KEY idx_order_item_order_id (order_id),
  KEY idx_order_item_product_id (product_id),
  CONSTRAINT fk_order_item_orders
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
  CONSTRAINT fk_order_item_product
    FOREIGN KEY (product_id) REFERENCES product(product_id)
);

주문 상세는 주문 번호(order_id) 기준으로 자주 조회하므로, order_id에 인덱스를 둔다.
상품 기준 통계 조회를 위해 product_id에도 인덱스를 둔다.
product_name, order_price는 역정규화 컬럼으로, 뒤에서 자세히 본다.