📂 목차

• DDL 심화
  • ALTER TABLE
• MySQL 데이터 타입
• NULL 타입
• Unknown 타입
• 인덱스 최적화
• 설계 단계
• 트랜잭션 제어

📚 본문

DDL 심화

ddl 을 이제 응용 관점에서 살펴보기 전에 기본적인 MySQL 이 지원하는 데이터 타입이 얼마나 있는지 보자.

ALTER TABLE

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

ALTER TABLE 은 위 문법을 따르게 된다. alter_option 을 여러개 나열시키게 된다.

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX | KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
  | DROP {CHECK | CONSTRAINT} symbol
  | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
  | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
  | ALTER [COLUMN] col_name {
        SET DEFAULT {literal | (expr)}
      | SET {VISIBLE | INVISIBLE}
      | DROP DEFAULT
    }
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST | AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE | ENABLE} KEYS
  | {DISCARD | IMPORT} TABLESPACE
  | DROP [COLUMN] col_name
  | DROP {INDEX | KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | FORCE
  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX | KEY} old_index_name TO new_index_name
  | RENAME [TO | AS] new_tbl_name
  | {WITHOUT | WITH} VALIDATION
}

ALTER table_name 문은 기본적으로 가져가고, 그 이후에 나오는 alter_option 이 핵심임을 기억하고 sql 문을 짠다.

• ADD [COLUMN] col_name column_definition [FIRST

  ALTER col_name]: 컬럼을 새로 추가

• CHANGE [COLUMN] old_col_name new_col_name [FIRST

  ALTER col_name]: 컬럼 이름 및 데이터 타입 변경 시 사용

• MODIFY [COLUMN] col_name column_definition [FIRST

  ALTER col_name]: 컬럼 이름 및 데이터 타입 변경 시 사용

• DROP [COLUMN] col_name
• ALTER [COLUMN] col_name {
  SET DEFAULT {literal | (expr)}
  | SET {VISIBLE | INVISIBLE}
  | DROP DEFAULT
  }: 기본값 가시성 설정

인덱스 수정

• ADD {INDEX

  KEY} [index_name] (key_part,…) [index_option …]

• DROP {INDEX

  KEY} index_name

• ADD {FULLTEXT

  SPATIAL} [INDEX

  KEY] …

제약조건 수정

• ADD [CONSTRAINT [symbol]] PRIMARY KEY(…)
• ADD [CONSTRAINT [symbol]] UNIQUE(…)
• ADD [CONSTRAINT [symbol]] FOREIGN KEY(…)
• DROP PRIMARY KEY
• DROP FOREIGN KEY fk_symbol

MySQL 데이터 타입

NULL 타입

null 은 의미적으로 분명하지만, 연산하는데 있어 정의가 직관적이지 않아 문제를 일으킨다.

• 산술 식(+, -, *, /) 에서 결과는 입력값이 하나라도 NULL 이면 결과도 NULL
• 비교 연산에서는 NULL 이 포함되면 unknown(알 수 없음) 값으로 처리한다. 즉 논리 리터럴에 대해 true, false, unknown 이 3가지의 논리값으로 처리된다.

Unknown 타입

• true and unknown: unknown
• false and unknown: false
• true or unknown: true
• false or unknown: unknown
• not unknown: unknown
• null = null: unknown

어떤 값인지 모르기에 unknown 으로 나오는건 당연한 의미이다. 또한 처리할 때 (‘A’, NULL), (‘A’, NULL) 은 동일한 값이기에 distinct 옵션을 쓸 때 두 튜플 중 하나만 남게 된다.

unknown 처리 예시

select name
from instructor
where salary > 10000 is unknown;

인덱스 최적화

• 인덱스 사용 시 주의사항
  • 컬럼의 좌변을 변형하면 인덱스를 사용할 수 없음
    • ❌ WHERE SUBSTRING(hire_date, 1, 4) = '2005'
    • ✅ WHERE hire_date LIKE '2005%'
• 복합 인덱스 활용
  • 자주 함께 조회되는 컬럼들을 묶어서 인덱스 생성
  • WHERE절에서 사용 빈도가 높은 컬럼 순서대로 인덱스 구성
  • 선택도가 높은(데이터 분포가 다양한) 컬럼을 앞쪽에 배치
• 인덱스 성능 관리
  • SHOW INDEX FROM 테이블명 : 인덱스 상태 확인
  • EXPLAIN 쿼리 : 실행 계획 분석
  • 불필요한 인덱스는 제거 → INSERT/UPDATE 성능 저하 방지

설계 단계

1. 요구조건 분석: 데이터 및 처리 요구 조건
2. 개념적 설계: E-R 다이어그램, DBMS 독립적
3. 논리적 설계: 정규화, 목표 DBMS에 맞는 스키마
4. 물리적 설계: 반정규화, 인덱스 설계
5. 구현: DDL로 스키마 작성

트랜잭션 제어

기본적으로 MySQL은 자동 커밋이 되는데, 트랜잭션 제어를 위해 AUTOCOMMIT 옵션을 수정하여 수동으로 커밋을 관리할 수 있다.

SELECT @@AUTOCOMMIT;

SET AUTOCOMMIT = 0;

START TRANSACTION;

INSERT INTO user (email, name, password)
VALUES ('test@test.com', '테스트', '1234');

COMMIT; -- 변경사항 확정
ROLLBACK; -- 변경사항 취소, START TRANSACTION 이전으로 돌아감

SET AUTOCOMMIT = 1;

정확히는 START TRANSACTION 이후의 INSERT, DELETE, UPDATE 등의 데이터 변경 수행만 취소하게 된다.

이때 SET AUTOCOMMIT 을 하는 것은 해당 열려있는 세션에 대해 설정이 변경되는 것이다.