환경: MySql
예전에 거래내역의 데이터를 파티셔닝 해서 조회한 적이 있는데 직접 설정했던 게 아니라서 구체적으로 알아본다.
파티셔닝 (Partitioning)
파티셔닝은 하나의 데이터베이스 내에서 데이터를 논리적으로 나누는 방법. 특정 컬럼을 기준으로 데이터를 여러 파티션으로 분할하여 성능을 향상시킬 수 있다.
파티셔닝의 특징:
- 단일 데이터베이스 인스턴스 내에서 분할
- 쿼리 성능 향상: 데이터를 작은 블록으로 나누어 특정 파티션만 조회할 수 있어 성능이 향상
- 트랜잭션 처리: 트랜잭션의 일관성을 유지할 수 있음
- 관리 용이성: 데이터를 분할해도 동일한 데이터베이스 인스턴스를 사용하므로 관리가 상대적으로 단순
파티셔닝을 선택할 때:
- 단일 서버에서 성능을 향상시키고 싶을 때
- 데이터가 일정한 기준으로 나누어지고, 쿼리가 특정 범위 (예: 날짜, 지역 등)로 자주 조회될 때
- 트랜잭션 일관성 및 데이터 무결성을 유지해야 할 때
예시:
- 날짜 기반 파티셔닝: order_date가 날짜 범위에 따라 파티셔닝되어, 특정 날짜 범위만 조회하면 해당 파티션만 스캔하여 빠른 성능을 제공
파티션 생성
CREATE TABLE orders (
id INT NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10, 2),
PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
PARTITION p202301 VALUES LESS THAN (202302),
PARTITION p202302 VALUES LESS THAN (202303),
PARTITION p202303 VALUES LESS THAN (202304),
PARTITION pMax VALUES LESS THAN MAXVALUE
);
PARTITION BY RANGE는 각 파티션의 VALUES LESS THAN 조건에 따라 데이터를 할당
MySQL에서는 파티션 조건이 겹치지 않도록 설계되어 있으므로, 데이터가 특정 파티션에만 할당됨
- YEAR(order_date) * 100 + MONTH(order_date) 값이 202302 미만인 데이터가 이 파티션에 들어감
- 예: 2023년 1월의 데이터(202301), 2022년 12월 이전의 데이터
파티션 확인 가능?
MySQL에서는 내부적으로 파티션이 관리되지만, 사용자 관점에서는 단일 테이블로만 동작하며 클라이언트에서 개별 파티션을 노출하지 않는다. 파티션 정보를 조회하거나 특정 파티션만 쿼리 하는 기능은 있지만, 파티션에 직접 들어가서 작업하는 방식은 지원되지 않는다.
하지만 오라클에서는 파티션이 클라이언트나 관리 도구에서 명시적으로 노출되며, 개별 파티션에 대해 직접 접근하고 작업할 수 있다.
내부 동작 방식
물리적 파일 분리
- MySQL은 각 파티션을 내부적으로 별도의 파일 형태로 저장
- 데이터 파일(.ibd 파일, InnoDB 기준)이 각 파티션별로 생성
- 이로 인해 파티셔닝 된 테이블은 디스크 I/O 및 데이터 관리를 효율적으로 수행
논리적 테이블
- 사용자는 하나의 테이블로 모든 데이터를 다룸
- SQL 문장에서 특정 파티션을 명시적으로 참조할 필요가 없음
- MySQL은 파티션 조건에 따라 자동으로 적절한 파티션에 데이터를 삽입하거나 조회
플로우
1. 삽입 시 MySQL은 파티션 조건을 평가하여 해당 파티션에 데이터를 저장
2. 조회 시에도 사용자는 특정 파티션을 신경 쓰지 않아도 됨. MySQL이 자동으로 필요한 파티션만 읽음(파티션 프루닝).
3. 필요하다면 특정 파티션만 직접 조회할 수도 있음
INSERT INTO orders (id, order_date, amount) VALUES (1, '2023-01-15', 100.00);
-- 이 데이터는 p202301 파티션에 저장됨
SELECT * FROM orders WHERE order_date = '2023-01-15';
-- MySQL은 p202301 파티션만 스캔
SELECT * FROM orders PARTITION (p202301);
-- 명시적인 조회도 가능..
조회 시 파티션 프루닝
MySQL의 파티션 프루닝(Partition Pruning)은 쿼리 실행 시 WHERE 절의 조건에 따라 필요한 파티션만 읽도록 최적화하는 기법
파티션 프루닝이 작동하는 조건
- WHERE 절에 파티션 키가 포함될 것
- MySQL은 WHERE 절에 파티션 키(파티셔닝 기준이 되는 컬럼)가 있을 때만 프루닝을 수행
- 상수 또는 단순 연산 사용
- MySQL은 조건이 상수 값이거나 단순 연산으로 평가될 수 있을 때만 프루닝을 적용
- 예를 들어, order_date = '2023-01-15'는 가능하지만 order_date = NOW()는 모든 파티션을 탐색
- 범위 조건
- BETWEEN, <, >, = 등의 조건도 프루닝이 가능
- IN 조건
그럼 아래와 같이 여러 월에 걸쳐진 데이터도 프루닝이 적용될까?
SELECT * FROM orders WHERE order_date >= '2023-02-01' AND order_date < '2023-03-01';
mysql의 경우 가능, oracle의 경우 불가능..
1. mysql
- 위 쿼리는 order_date 컬럼이 파티션 키이므로 프루닝이 가능
- >=, <, BETWEEN과 같은 범위 조건은 프루닝이 지원
- -> p202301과 p202302 파티션만 검색
- MySQL에서 프루닝 여부를 확인하려면 EXPLAIN PARTITIONS를 사용
- 여기서 partitions 열에 p202301, p202302와 같은 값이 표시되면 프루닝이 성공적으로 적용된 것
EXPLAIN PARTITIONS SELECT * FROM orders WHERE order_date >= '2023-01-15' AND order_date < '2023-02-21';
id | partitions | type | possible_keys | key | key_len | ref | rows | Extra
---|---------------|-------|---------------|------|---------|------|-------|------
1 | p202301,p202302 | ALL | NULL | NULL | NULL | NULL | 1000 |
단, 아래와 같은 쿼리는 프루닝 불가
WHERE YEAR(order_date) = 2023 AND MONTH(order_date) = 1; -- 프루닝 불가
2. Oracle
- 조건이 파티션의 경계값과 비교 가능해야 한다.
- order_date= TO_DATE('2024-01-15', 'YYYY-MM-DD')
- order_date BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-01-31', 'YYYY-MM-DD')
- order_date >= TO_DATE('2023-01-15', 'YYYY-MM-DD') AND order_date < TO_DATE('2023-02-10', 'YYYY-MM-DD');
- 조건이 파티션 경계와 명확히 매칭되므로 Oracle은 p202301과 p202302 두 개의 파티션만 읽습니다.
- 파티션 키에 함수나 연산을 적용하면 Oracle이 프루닝을 할 수 없다.
SELECT * FROM sales WHERE TRUNC(order_date) = TO_DATE('2024-01-15', 'YYYY-MM-DD');
-- 파티션 프루닝 작동하지 않음
- 파티션 키에 연산을 하는 것은 위험하다. 모든 파티션을 스캔할 수 있으니 확인해야한다.
SELECT * FROM orders
WHERE TRUNC(order_date) >= TO_DATE('2023-01-15', 'YYYY-MM-DD')
AND TRUNC(order_date) < TO_DATE('2023-02-21', 'YYYY-MM-DD');
EXPLAIN PLAN FOR
SELECT * FROM orders
WHERE order_date >= TO_DATE('2023-01-15', 'YYYY-MM-DD')
AND order_date < TO_DATE('2023-02-21', 'YYYY-MM-DD');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
--------------
PARTITION START | PARTITION STOP
-----------------|---------------
202301 | 202302
