데이터베이스 기초 정리1 (DDL, 제약조건, 인덱스, 실행계획)

📌 DDL 명령어 요약

1. DDL이란?

테이블과 같은 구조를 정의하는데 사용되는 명령어이다. (생성, 변경, 삭제 등)

• CREATE TABLE: 테이블 생성
• ALTER TABLE: 테이블 구조 변경
• DROP TABLE: 테이블 삭제
• RENAME: 이름 변경
• TRUNCATE: 테이블의 모든 데이터 삭제
• COMMENT: 테이블에 설명 추가

 

---

📌 MySQL 데이터 타입

1. 분류 타입 설명

숫자형	INT, BIGINT, DECIMAL	정수, 실수
문자형	VARCHAR, CHAR, TEXT	문자열
날짜형	DATE, TIME, DATETIME, TIMESTAMP	날짜와 시간
기타	JSON, BLOB	JSON 데이터, 바이너리

 

---

📌 CREATE TABLE

• CREATE TABLE은 "데이터 구조(타입) + 규칙(제약조건)"을 함께 정의하는 작업이다.
• 데이터베이스에서 테이블을 생성할 때는 다음과 같은 구조를 사용하며,
  • 각 컬럼에는 데이터 타입과 제약조건을 함께 정의하여 데이터의 형태와 무결성을 관리할 수 있다.

CREATE TABLE 테이블이름 (
    필드이름(컬럼이름)1 필드타입1 [제약조건],
    필드이름2 필드타입2 [제약조건],
    ...
    [테이블 제약조건]
);

1. 제약조건 (Constraints)

• 제약조건은 데이터의 정합성과 규칙을 보장하기 위해 사용된다.
• 특히 PRIMARY KEY는 테이블서 각 행을 구분하는 가장 중요한 컬럼이다.

제약조건	설명
NOT NULL	NULL을 허용하지 않음
UNIQUE	중복된 값을 허용하지 않음
PRIMARY KEY	NOT NULL + UNIQUE
FOREIGN KEY	다른 테이블 참조
DEFAULT	기본값 설정
CHECK	값의 범위 제한
AUTO_INCREMENT	자동 증가

 

2. 데이터 타입

• 데이터 타입은 컬럼에 저장될 값의 형태를 결정한다.

타입	설명	예시	사용 예
INT	일반 정수	1, 100	나이, 수량
INT UNSIGNED	음수 없는 정수	0, 100	ID, PK
BIGINT	매우 큰 정수	999999999999	로그 ID
DECIMAL(p,s)	정확한 소수	1234.56	금액, 급여
FLOAT	부동소수점 (오차 가능)	3.14	통계값
DOUBLE	더 정밀한 실수	3.141592	과학 계산

 
 

3 테이블 생성 예시 쿼리

CREATE TABLE employees (
    employee_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(20),
    last_name VARCHAR(25) NOT NULL,
    hire_date DATE NOT NULL,
    job_id VARCHAR(10) NOT NULL,
    salary DECIMAL(8, 2) NOT NULL CHECK(salary > 0),
    manager_id INT(11) UNSIGNED,
    PRIMARY KEY (employee_id),
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id),
);

 

---

📌 인덱스

1. 인덱스란?

• 데이터 검색 속도를 향상시키기 위한 자료구조로 특정 컬럼의 값을 빠르게 찾을 수 있도록 도와준다.
  • 인덱스가 없으면 행 전체를 전부 확인, 인덱스가 있으면 일부만 조회
  • 단, 인덱스를 쓰려면 컬럼변형 없이 그대로 사용
    • ex> WHERE SUBSTRING(date, 1, 4) = '2005' (X)
    • ex> WHERE date LIKE '2005%' (O)
• 테이블 데이터와 별도로 생성되는 구조라서 SELECT 조회 결과에 직접 보이진 않는다.
• 기본적으로 오름차순(ASC) 기준으로 정렬된 구조가 생성되며, 필요하면 내림차순(DESC) 으로 생성할 수도 있다.

내가 헷갈려서 정리해본 인덱스와 파티셔닝 차이

1. 인덱스 : 인덱스에서 위치 찾고 → 해당 row 바로 접근 ⇒ “빠르게 찾는다”
   • 특정 값 조회 많을 때
   • WHERE 자주 쓰는 컬럼
2. 파티셔닝 : 데이터 들어있는 파티션만 조회” → 다른 파티션은 아예 안 봄 ⇒ “검색 범위를 줄인다”
   • 데이터가 엄청 많을 때 (수백만~수억)
   • 날짜 기준 조회 많을 때 (로그, 뉴스, 거래 데이터)

개념 핵심

인덱스	데이터를 빠르게 “찾는 기술”
파티셔닝	데이터를 “나눠서 적게 보게 하는 기술”

 

2. 생성과 삭제

-- 인덱스 생성
CREATE INDEX employees_hire_date_idx 
ON employees(hire_date);

-- 복합 인덱스
CREATE INDEX emp_dept_sal_idx 
ON employees(department_id, salary);

-- 인덱스 확인
SHOW INDEX FROM employees;

-- 인덱스 삭제
ALTER TABLE employees 
DROP INDEX employees_hire_date_idx;

 
 

3. 실행 계획 확인 (EXPLAIN)

-- 인덱스 생성
CREATE INDEX emp_hire_idx ON employees(hire_date);

-- 인덱스 사용 후
EXPLAIN SELECT * FROM employees 
WHERE hire_date = '2005-01-01';

1. 인덱스 생성 전
   • type: ALL 전체 테이블 스캔
   • key: NULL 인덱스 안 씀
2. 인덱스 생성 후

• type: ref 특정 값 검색
• key: emp_hire_idx 👉 인덱스 사용!

---

 

💡 EXPLAIN 결과 컬럼 정리

컬럼명	설명
id	쿼리 실행 순서
select_type	쿼리 유형
table	조회 테이블
type ⭐	데이터 조회 방식 (성능 핵심)
possible_keys	사용 가능한 인덱스 후보
key ⭐	실제 사용한 인덱스
key_len	사용한 인덱스 길이
ref	비교 기준 컬럼/값
rows ⭐	읽을 것으로 예상되는 행 수
filtered	조건에 맞는 비율 (%)
Extra	추가 실행 정보

 
 

⭐ type: 결과값 정리

값	의미
ALL	전체 스캔 (최악)
index	인덱스 전체 스캔
range	범위 검색
ref	특정 값 검색
const	PK로 1건 조회 (최고)

 

 

 

데이터베이스 기초 정리2 (설계, 트랜잭션, ALTER TABLE)