MySQL Partition 설정, 사용법

MySQL의 Partition (파티션) 기능은 대용량 테이블을 물리적으로 분할하여 성능 향상, 관리 용이성 등을 얻기 위해 사용. 하지만 모든 상황에서 무조건 유리한 것은 아니며, 파티션이 적절한 시점과 구조를 이해하고 사용해야 효과적.

---

✅ 파티션(Partition)이란?

• 하나의 테이블을 여러 물리적 파티션(파일 블록)으로 나눔
• 쿼리 성능 향상 가능 (파티션 프루닝 등)
• 파티션 단위로 백업, 삭제, 유지 관리 가능

---

✅ 기본 전제 조건

1. 파티션 키로 사용할 컬럼은 PRIMARY KEY 또는 UNIQUE 제약조건과 일치해야 함
   예: PRIMARY KEY(id)가 있으면 id가 반드시 파티션 키에 포함되어야 함.
2. 파티션은 테이블 생성 시 설정하거나, 테이블을 변경하여 설정 가능 (ALTER TABLE)

---

✅ 1. 파티션 테이블 생성 예시

🎯 예: RANGE 파티션 (날짜 범위 기준)

CREATE TABLE sales (
  id INT NOT NULL,
  sale_date DATE NOT NULL,
  amount DECIMAL(10, 2),
  PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p2019 VALUES LESS THAN (2020),
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

• sale_date의 연도 기준으로 파티션 나눔.
• pmax는 나머지 모든 값 처리.

---

🎯 예: LIST 파티션 (정해진 값 목록 기준)

CREATE TABLE users (
  id INT NOT NULL,
  region VARCHAR(10) NOT NULL,
  name VARCHAR(100),
  PRIMARY KEY (id, region)
)
PARTITION BY LIST COLUMNS(region) (
  PARTITION east VALUES IN ('Seoul', 'Busan'),
  PARTITION west VALUES IN ('Gwangju', 'Jeonju'),
  PARTITION etc VALUES IN ('Others')
);

• 문자열 또는 정수 리스트에 따라 파티션 분리

---

🎯 예: HASH 파티션 (균등 분산)

CREATE TABLE logs (
  id INT NOT NULL,
  message TEXT,
  created_at DATETIME,
  PRIMARY KEY (id)
)
PARTITION BY HASH(id) PARTITIONS 4;

• id % 4로 자동 분할 → 균등하게 분산됨

---

✅ 2. 파티션 쿼리 예시

파티션은 자동 처리되지만, WHERE 절에 파티션 키 조건이 있어야 최적화됨 (파티션 프루닝).

SELECT * FROM sales WHERE sale_date >= '2020-01-01' AND sale_date < '2021-01-01';

이 쿼리는 p2020만 읽습니다 → 빠름.

하지만 아래는 모든 파티션을 스캔합니다:

SELECT * FROM sales WHERE amount > 1000;

✅ 반드시 파티션 키가 WHERE 절에 있어야 "파티션 프루닝" 최적화가 적용됩니다.

---

✅ 3. 기존 테이블에 파티션 추가 (ALTER TABLE)

MySQL에서는 기존 테이블에 직접 PARTITION을 추가하는 건 불가능합니다. 대신 다음 방법으로 복사해야 합니다:

-- 기존 테이블 구조 확인
SHOW CREATE TABLE old_table;

-- 새로운 파티션 테이블 생성
CREATE TABLE new_table ( ... ) PARTITION BY ...;

-- 데이터 복사
INSERT INTO new_table SELECT * FROM old_table;

-- 기존 테이블 제거, 이름 변경
RENAME TABLE old_table TO backup_table, new_table TO old_table;

---

✅ 4. 파티션 관리 (삭제, 추가 등)

-- 파티션 삭제
ALTER TABLE sales DROP PARTITION p2019;

-- 파티션 추가
ALTER TABLE sales
ADD PARTITION (
  PARTITION p2022 VALUES LESS THAN (2023)
);

⚠️ 주의: 파티션 삭제는 해당 파티션의 데이터도 함께 삭제합니다!

---

✅ 5. 파티션 확인

SELECT table_name, partition_name, subpartition_name,
       table_rows, avg_row_length
FROM information_schema.partitions
WHERE table_name = 'sales';

 

---

🔥 장점

• 파티션 단위로 쿼리 최적화
• 대용량 테이블 관리 편리
• 일부 파티션만 백업/삭제 가능

⚠️ 주의점

 

제한사항	설명
외래키(FK) 불가	파티션 테이블은 FK 지원 안함
Fulltext, Spatial index 불가	파티션 테이블은 제한 있음
일부 제약조건 제약	PRIMARY KEY/UNIQUE 키에 파티션 키가 포함되어야 함
쿼리가 파티션 키를 안 쓰면 → 느림	무조건 빨라지는 것은 아님

 

---

🧩 요약

 

파티션 종류	사용 조건	예시
RANGE	날짜/정수 범위	YEAR(date)
LIST	정해진 목록	region IN (...)
HASH	균등 분산	HASH(id)
KEY	자동 해시	KEY(user_id)