이 사이트의 실습은 자바스크립트를 켜야 움직입니다. 아래는 같은 내용을 글로 적은 것입니다.

공부 › DB › 확장과 분산

파티셔닝

테이블을 조각내면 조회는 어느 조각을 읽나

파티셔닝은 큰 테이블 하나를 같은 서버 안에서 여러 조각(파티션)으로 나눠 저장하는 것입니다. 주문 날짜의 연도처럼 범위로 나누거나(RANGE), 회원 번호를 나눈 나머지처럼 계산으로 나눕니다(HASH). 조건을 어떻게 쓰느냐에 따라 필요한 파티션만 읽기도 하고 전부 읽기도 합니다.

실행으로 대조 · MySQL 8.4.11에서 서른두 가지 조합을 실행해 오류 코드와 EXPLAIN의 partitions를 대조

이 실습에 나오는 개념

샤딩과 파티셔닝

파티셔닝은 큰 테이블을 날짜 범위 등의 기준으로 여러 조각으로 나눠 관리하는 것이다. 샤딩은 데이터를 여러 데이터베이스 서버에 나눠 저장하는 것으로, 한 서버로 감당할 수 없는 쓰기 부하나 데이터 양을 분산한다. 샤딩을 하면 서버에 걸친 조인이나 트랜잭션이 어려워진다.

RANGE 파티션과 HASH 파티션

테이블을 파티션으로 나누는 대표적인 두 가지 방법이다. 행이 어느 파티션으로 갈지를 정하는 컬럼(또는 식)을 파티션 키라고 한다. RANGE는 파티션마다 값의 범위를 정해 두는 방식으로, 날짜처럼 이어진 범위로 조회하거나 오래된 데이터를 파티션째 지울 때 알맞다. 정해 둔 어느 범위에도 속하지 않는 값은 넣을 수 없으므로 다음 기간의 파티션을 미리 추가해야 한다. HASH는 파티션 키를 파티션 수로 나눈 나머지로 파티션을 정하는 방식으로, 행을 고르게 흩어 놓지만 범위 조건으로는 읽을 파티션을 줄이기 어렵다.

파티션 프루닝

조회 조건을 보고 해당 행이 있을 수 없는 파티션을 아예 읽지 않는 최적화다. 프루닝은 가지치기라는 뜻이다. 조건에 파티션 키 컬럼이 값이나 범위와 그대로 비교되는 형태로 들어 있어야 동작하며, MySQL에서는 EXPLAIN의 partitions 칸에서 실제로 읽는 파티션을 확인할 수 있다. 조건에 파티션 키가 없거나 파티션 키 컬럼에 함수나 계산을 씌우면 모든 파티션을 읽는다. 파티션을 나눠서 얻는 조회 성능의 이득은 대부분 이 프루닝에서 나온다.

실행 계획(EXPLAIN)

데이터베이스가 쿼리를 어떤 방법으로 실행할지 정한 계획이다. 어떤 인덱스를 쓰는지, 테이블을 어떤 순서로 조인하는지, 몇 행을 읽을 것으로 예상하는지를 보여 준다. 쿼리 앞에 EXPLAIN을 붙여 확인하며, 쿼리가 느릴 때 원인을 찾는 출발점이다.

기본 키, 유니크 키, 외래 키

기본 키(PK)는 테이블의 각 행을 유일하게 식별하는 컬럼으로 값이 중복되거나 비어 있을 수 없고 테이블에 하나만 둔다. 유니크 키는 값의 중복을 막는 제약으로 여러 개 둘 수 있다. 외래 키(FK)는 다른 테이블의 기본 키(또는 유니크 키)를 참조하는 컬럼으로, 참조 대상이 실제로 존재하도록 보장한다.

이렇게 바꿔 보세요

• 처음 설정으로 — 연도로 나눴는데 YEAR(ordered_at) = 2024로 찾으면 세 파티션을 모두 읽습니다.
• 컬럼을 그대로 비교하면 — 그 날짜가 들어 있을 파티션 하나만 읽습니다.
• 두 해에 걸친 범위 — 걸쳐 있는 두 파티션만 읽고 나머지는 건너뜁니다.
• 파티션 키가 없는 조건 — 기본 키의 첫 컬럼으로 찾는데도 모든 파티션을 읽습니다.
• 범위 밖의 날짜를 넣으면 — 받아 줄 파티션이 없어 INSERT가 오류 1526으로 실패합니다.
• 기본 키에 파티션 키가 없으면 — 테이블을 만들 때 오류 1503이 납니다.
• 해시로 나누면 — 어떤 값이든 들어갈 곳이 있지만, 범위 조회는 모든 파티션을 읽습니다.

처음 설정으로 실행하면

1. CREATE TABLE … PARTITION BY RANGE (YEAR(ordered_at)): orders 테이블을 주문 날짜의 연도에 따라 세 조각(p2023, p2024, p2025)으로 나눠 만듭니다. 이 조각 하나하나가 파티션입니다. 쿼리를 쓰는 쪽에서는 여전히 orders 테이블 하나로 보이고, 세 파티션은 모두 같은 MySQL 서버 안에 있습니다.
2. 주문 300건을 넣어 둡니다. 넣는 쪽은 파티션을 지정하지 않습니다. MySQL이 행마다 주문 날짜의 연도를 보고 들어갈 파티션을 고릅니다. 파티션별 행 수는 p2023 121건, p2024 122건, p2025 57건입니다.
3. INSERT … VALUES (1001, 7, '2025-06-01', 5000): 주문 날짜의 연도가 2025이므로 "2026 미만"을 받는 p2025 파티션에 들어갑니다.
4. EXPLAIN SELECT * FROM orders WHERE YEAR(ordered_at) = 2024: 조건이 ordered_at 컬럼 자체가 아니라 YEAR(ordered_at)에 걸려 있습니다. 파티션을 나눈 식과 똑같은데도, MySQL은 컬럼에 함수를 씌운 조건으로는 파티션을 골라내지 못해 세 파티션을 모두 읽습니다. ordered_at >= '2024-01-01' AND ordered_at < '2025-01-01'처럼 컬럼을 그대로 두고 범위로 쓰면 p2024 파티션만 읽습니다. EXPLAIN의 partitions 칸: p2023,p2024,p2025.

실행 결과

• 파티셔닝은 서버 한 대 안에서 테이블 하나를 나눠 저장하는 것입니다. 데이터를 여러 서버에 나누는 샤딩과 달리 조인과 트랜잭션, 쿼리는 그대로 쓸 수 있습니다. 대신 서버 한 대의 한계를 넘게 해 주지는 않습니다. 한 번에 읽고 지울 범위를 줄이는 것이 목적입니다.
• 파티션 키 컬럼에 함수나 계산을 씌우면 프루닝이 되지 않습니다. 인덱스가 걸린 컬럼에 함수를 씌우면 인덱스를 못 쓰는 것과 같은 이치입니다. 컬럼은 그대로 두고 비교할 값 쪽을 바꿉니다.
• RANGE 파티션의 큰 장점은 오래된 데이터를 통째로 지울 수 있다는 것입니다. ALTER TABLE orders DROP PARTITION p2023은 그 파티션의 행을 하나씩 지우지 않고 조각째로 없앱니다.

코드

CREATE TABLE orders (
  id BIGINT NOT NULL,
  member_id INT NOT NULL,
  ordered_at DATE NOT NULL,
  amount INT NOT NULL,
  PRIMARY KEY (id, ordered_at)
)
PARTITION BY RANGE (YEAR(ordered_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026)
);

-- 주문 300건이 들어 있는 상태에서 한 건을 더 넣는다
-- 컬럼 순서: id, member_id, ordered_at, amount
INSERT INTO orders VALUES (1001, 7, '2025-06-01', 5000);

-- 이 조회가 어느 파티션을 읽는지는 EXPLAIN의 partitions 칸에 나온다
EXPLAIN SELECT * FROM orders WHERE YEAR(ordered_at) = 2024;

이 모형의 전제

서른두 가지 조합 모두를 이 PC의 MySQL 8.4.11(InnoDB)에서 화면과 같은 문장으로 실행해 대조했습니다. 조합마다 테이블을 새로 만들어 CREATE TABLE의 성공 여부와 오류 코드, 주문 300건이 파티션마다 들어간 건수(SELECT COUNT(*) … PARTITION (이름)), 새 행이 들어간 파티션 또는 INSERT의 오류 코드, EXPLAIN의 partitions 칸이 화면과 일치하는 것을 확인했습니다. 화면에 적은 오류 메시지도 실제 메시지입니다. 설명에 쓴 다음 사실도 실행으로 확인했습니다. 유니크 키에 파티션 키가 없어도 오류 1503이 납니다. 기본 키와 유니크 키가 모두 없는 테이블은 제약에 걸리지 않습니다. 날짜 조건을 컬럼의 범위(>= 와 <)로 쓰면 p2024만 읽습니다. HASH에서 BETWEEN 5 AND 6은 p1, p2만 읽습니다. ADD PARTITION으로 2026년 파티션을 추가한 뒤에는 같은 INSERT가 성공합니다. DROP PARTITION p2023은 그 파티션의 121행을 없앱니다. 기본 키가 (id, ordered_at)일 때 id가 같고 날짜만 다른 두 행이 들어갑니다. 어느 파티션을 읽는지는 조건에서 정해지며 행 수나 통계와 무관하지만, 파티션 안에서 인덱스를 쓰는지와 예상 행 수는 데이터에 따라 달라지므로 결과로 삼지 않았습니다. 파티션 키와 기본 키의 제약, 프루닝이 되는 조건의 형태는 MySQL 8.4 참조 설명서 26.6.1 Partitioning Keys, Primary Keys, and Unique Keys와 26.4 Partition Pruning의 설명과도 맞습니다. 파티션마다 인덱스를 따로 가진다는 것은, 파티션 셋이 InnoDB 안에서 별도의 테이블과 데이터 파일로 저장되고 인덱스 통계(mysql.innodb_index_stats)에도 파티션마다 PRIMARY 인덱스가 따로 올라 있는 것을 보고 확인했습니다. LIST와 KEY 파티션, 서브파티션, 파티션 수가 아주 많을 때의 비용은 다루지 않았습니다. 다른 DB 제품은 파티션 키가 없는 유니크 인덱스를 허용하기도 하므로, 이 제약은 MySQL 기준입니다.

앞 실습: 샤딩 · 다음 실습: Redis의 한 줄 처리