회원가입   로그인
IMG-LOGO
공지사항 :

MySQL 테이블 생성

lmkfox - 2025-09-23 06:50:46 12 Views 0 Comment

1. 기본 문법

CREATE TABLE [IF NOT EXISTS] schema_name.table_name (
  column_definitions,
  table_constraints
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci
COMMENT='테이블 설명';

  • IF NOT EXISTS는 같은 이름의 테이블이 이미 있으면 오류를 피합니다.

  • ENGINE(스토리지 엔진)은 일반적으로 InnoDB 권장(트랜잭션, 외래키, MVCC 지원).

  • DEFAULT CHARSET은 utf8mb4 권장(이모지·다국어 안전).

2. 컬럼 정의 핵심 요소

컬럼 선언의 기본 형식:

col_name DATA_TYPE [NULL | NOT NULL] [DEFAULT expr] [AUTO_INCREMENT] [UNIQUE] [COMMENT '...']

주요 포인트:

  • 데이터 타입 선택: 정수(INT/BIGINT), 문자열(VARCHAR/TEXT), 날짜(DATE/DATETIME/TIMESTAMP), 소수(DECIMAL) 등.

    • 금액 등 정밀한 소수는 DECIMAL(p,s) 권장(부동소수점 FLOAT/DOUBLE는 부정확).

  • NULL vs NOT NULL: 가능하면 NOT NULL + 기본값을 사용해 불필요한 NULL을 줄이는 것이 성능·명확성에 유리.

  • DEFAULT: 상수 또는 함수(CURRENT_TIMESTAMP) 가능(버전/컬럼 타입 제약 존재).

  • AUTO_INCREMENT: 기본키에 자주 사용. BIGINT UNSIGNED AUTO_INCREMENT를 권장(확장성).

  • 열 코멘트: COMMENT '설명'으로 설명을 남겨두면 SHOW CREATE TABLE에 보입니다.

3. 키와 인덱스

  • PRIMARY KEY

PRIMARY KEY (id)

    • 테이블의 유일 식별자. InnoDB에서는 클러스터드 인덱스로 동작.

  • UNIQUE KEY

UNIQUE KEY ux_users_email (email)

  • 인덱스

INDEX idx_name (col1)
KEY idx_multi (col1, col2)

    • 복합 인덱스(Left-most rule): (a,b,c)는 a, a,b, a,b,c 같은 쿼리에서만 완전 활용.

    • 인덱스는 읽기 속도↑ 쓰기 속도↓/디스크 사용↑ — 필요한 인덱스만 설계.

  • 전문 인덱스: FULLTEXT(텍스트 검색), SPATIAL(지리공간). MySQL 버전·엔진 제약 확인.

4. 외래키(FOREIGN KEY) — 참조 무결성

  • InnoDB에서만 정상 동작.

CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE
  ON UPDATE RESTRICT

  • ON DELETE/ON UPDATE 옵션: CASCADE, SET NULL, RESTRICT, NO ACTION 등.

  • 외래키를 사용하면 데이터 무결성이 향상되지만 복잡한 배치 작업 시 제약으로 작업이 실패할 수 있으므로 설계 시 고려.

5. 생성된 컬럼(Generated Columns)

  • 식으로 계산되는 컬럼을 선언할 수 있습니다.

col2 INT GENERATED ALWAYS AS (col1 * 2) [VIRTUAL | STORED]

  • VIRTUAL: 저장하지 않고 읽을 때 계산(디스크 절약).

  • STORED: 값을 물리적으로 저장(읽기 빠름, 인덱스에 안정적으로 사용).

  • 인덱스: 생성 컬럼에 인덱스를 만들 수 있음(버전별 동작 차이가 있으므로, 인덱싱 목적이면 STORED 사용 권장).

6. 파티셔닝(Partitioning) & 임시 테이블

  • 파티셔닝: 대용량 테이블 관리를 위한 분할(예: RANGE, HASH). 설계가 잘못되면 성능 오히려 떨어질 수 있으므로 사용 전 테스트 필수.

  • 임시 테이블:

CREATE TEMPORARY TABLE temp_x (...);

  • 커넥션 단위로 존재, 연결 종료 시 자동 삭제. 세션별 격리.

7. 테이블 생성 방식 변형

  • 기본 구조만 복사

CREATE TABLE new_table LIKE existing_table;

  • 구조 + 데이터 복사

CREATE TABLE new_table AS SELECT col1, col2 FROM old_table WHERE ...;

  • 주의: CREATE ... AS SELECT는 일반적으로 인덱스·제약(FK 등)을 복사하지 않음(구조만 가져오거나 데이터만 채움).

8. 제약조건 및 체크

  • CHECK 제약은 MySQL 8.0부터 실제로 적용(이전 버전은 무시되던 시점이 있었음).

age INT CHECK (age >= 0)

9. DATETIME vs TIMESTAMP

  • TIMESTAMP: 서버/세션 타임존을 고려해 내부적으로 변환 저장(시간대 변환 기능). 일부범위 제한 존재.

  • DATETIME: 순수한 날짜/시간 값을 그대로 저장(타임존 변환 없음).

  • 둘 다 CURRENT_TIMESTAMP 기본값·ON UPDATE CURRENT_TIMESTAMP 사용 가능(버전별 차이 있으니 환경 확인).

10. 생성 예제(실무 예: users / orders)

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password_hash CHAR(60) NOT NULL,
  profile JSON DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY ux_users_email (email),
  KEY idx_users_username (username)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='회원 테이블';

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  total DECIMAL(12,2) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_orders_user_created (user_id, created_at),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

  • profile JSON 같은 경우, JSON 필드에서 자주 검색할 값은 생성 컬럼을 만들어 인덱스하는 패턴 사용.

11. 테이블 확인·관리 커맨드

  • 구조 확인

DESCRIBE table_name;
SHOW CREATE TABLE table_name;
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='db' AND TABLE_NAME='table_name';

  • 인덱스 확인

SHOW INDEX FROM table_name;

12. 권한(Privilege)

  • 테이블 생성에는 해당 스키마에 대한 CREATE 권한 필요.

GRANT CREATE ON dbname.* TO 'user'@'host';

13. ALTER / 온라인 DDL(운영 관점)

  • 테이블 생성 자체는 빠르지만 대량 데이터 삽입/색인 생성 등으로 시간이 걸릴 수 있음.

  • ALTER TABLE은 많은 경우 암묵적 커밋을 발생시키므로 운영환경에서 신중. MySQL의 ALGORITHM=INPLACE, LOCK=NONE 같이 온라인 변경을 지원하는 옵션 사용 가능(버전·옵션 지원여부 확인).

  • 대규모 변경은 pt-online-schema-change, gh-ost 같은 도구 사용 권장.

14. 설계·네이밍·베스트 프랙티스

  • 네이밍: 소문자·언더스코어(snake_case) 권장: user_profiles, order_items. 인덱스/제약 이름 규칙 일관성 유지: pk_users, ux_users_email, fk_orders_user.

  • 기본키: 대체로 BIGINT UNSIGNED AUTO_INCREMENT 사용 권장(분산 환경에서는 UUID 고려).

  • 문자셋: DB·테이블·컬럼에 utf8mb4 사용.

  • 금액: DECIMAL(p,s) 사용(절대 FLOAT 사용 금지).

  • NULL 최소화: 가능한 NOT NULL로 명시.

  • 인덱스: 쿼리 패턴 기반으로 최소한만 생성. EXPLAIN으로 검증.

  • 주석: 컬럼·테이블 COMMENT로 목적·비고 남기기.

15. 흔한 실수와 주의점

  • CREATE TABLE ... AS SELECT로 생성하면 인덱스·제약을 복사하지 않음.

  • 외래키는 MyISAM 등 비지원 엔진에서는 동작하지 않음(항상 InnoDB).

  • 대량 데이터 삽입 시 인덱스가 많으면 삽입 성능 급감 — 대량 로드 시에는 인덱스 생성 순서 고려.

  • ALTER/DDL은 트랜잭션 내에서 사용하면 의도치 않은 커밋을 유발할 수 있음.

16. 요약 체크리스트 (테이블 생성 전)

  • 목적에 맞는 데이터 타입 선택(정확도·범위 확인).

  • 기본키·유니크키·필요 인덱스 설계.

  • 문자셋(utf8mb4)·콜레이션 지정.

  • 외래키가 필요하면 InnoDB로 결정.

  • 생성 컬럼/JSON 인덱싱 필요 시 미리 설계.

  • 운영 환경이라면 온라인 DDL/마이그레이션 도구 고려.

  • SHOW CREATE TABLE로 생성 후 구조 검증.


댓글