[MSSQL] Partition Table(파티션 테이블) 생성 방법

`MSSQL` 에서 `파티션 테이블`을 생성해보겠습니다 !

 

결재문서 테이블 데이터가 너무 많아 속도 개선 방안을 찾던 중, 파티션 테이블을 만들면 된다는 방법을 찾아 속도 개선을 진행하려고 했지만,, 쿼리를 다 만들고 실행시키려고 하니 

SQL Server Enterprise Edition에서만 파티션 함수를 만들 수 있습니다. SQL Server Enterprise Edition에서만 분할을 지원합니다.

 

이런 에러가 발생하여 저는 적용시키지 못하였습니다 ㅜㅜ 눈물 줄줄 ..

나중에 MYSQL로 이관하면 다시 적용해볼려고 합니다.

혹시 MSSQL 사용하시는 분들은 SQL Server Enterprise Edition인지 확인하시고 적용해주시면 되겠습니다.

 

---

파티션 테이블 만들게 된 계기

 

결재문서 테이블에 테이터가 너무 방대하여 해당 데이터 조회 속도를 개선하려고 하였습니다.

고려해보았던 방식으로는 파티셔닝, 연도별 테이블 분리, 아카이브 테이블 생성 이렇게 3가지가 있습니다.

방식	특징	적합한 상황
파티셔닝	논리적으로 데이터를 나누어 성능 향상
연도별 테이블 분리	물리적으로 테이블을 분리하여 성능 최적화	데이터 양이 많고, 연도별로 데이터가 완전히 분리 가능할 경우
아카이브 테이블	오래된 데이터를 별도 보관	과거 데이터를 자주 조회하지 않고, 최신 데이터 위주로 사용할 경우

 

제가 맡은 시스템은 결재문서를 기간을 선택하여 조회하는 일이 많고, 오래된 데이터를 조회하는 일이 종종 있습니다. 해당 특성을 고려하여 방식 선별을 진행하였습니다.

 

먼저, 특정 연도만 조회하지 않고 2024.02.14~2025.02.14 처럼 여러 연도를 조회하는 일이 많기 때문에 연도별로 테이블을 분리하면 `union` 이 자주 발생하기 때문에 비효율적이라 생각하였습니다.

더불어, 과거 데이터를 조회하기 때문에 아카이브 테이블도 적합하지 않다고 생각하였습니다.

 

-> 그래서 파티셔닝 방식을 사용하면 여러 연도를 조회할 때 자동으로 필요한 파티션만 검색하게 되어 효율적일 것이라 판단하여 파티션 테이블을 생성하기로 하였습니다.

---

파티션 테이블 생성 쿼리

파티션 테이블을 생성하는 쿼리를 살펴봅시다.

 

---

1.  파티션 함수 생성

-- 1. 파티션 함수 생성
CREATE PARTITION FUNCTION pf_appr_end_date(DATETIME2)
AS RANGE RIGHT FOR VALUES (
	'2010-01-01', '2011-01-01', '2012-01-01',
    '2013-01-01', '2014-01-01', '2015-01-01', '2016-01-01',
    '2017-01-01', '2018-01-01', '2019-01-01', '2020-01-01',
    '2021-01-01', '2022-01-01', '2023-01-01', '2024-01-01',
    '2025-01-01'
);

 

기존 테이블을 파티션 테이블로 변환하기 위해 새로운 파티션 함수를 생성해줍니다.

 

 

RANGE LEFT VS RANGE RIGHT

 

`RANGE LEFT`

CREATE PARTITION FUNCTION pf_appr_end_date(DATETIME2)
AS RANGE LEFT FOR VALUES ('2020-01-01', '2021-01-01');

 

경계값(VALUES)이 왼쪽 파티션에 포함됩니다.

 

• 첫 번째 파티션: 최소값 ~ 2020-01-01
• 두 번째 파티션: 2020-01-02 ~ 2021-01-01
• 세 번째 파티션: 2021-01-02 ~ ∞ (다음 값이 있으면 그 이전까지)

2019-12-31	첫 번째 파티션
2020-01-01	첫 번째 파티션 (경계값 포함) ✅
2020-01-02	두 번째 파티션
2021-01-01	두 번째 파티션 (경계값 포함) ✅
2021-01-02	세 번째 파티션

 

 

`RANGE RIGHT`

CREATE PARTITION FUNCTION pf_appr_end_date(DATETIME2)
AS RANGE RIGHT FOR VALUES ('2020-01-01', '2021-01-01');

 

경계값(VALUES)이 오른쪽 파티션에 포함됩니다.

• 첫 번째 파티션: 최소값 ~ 2019-12-31
• 두 번째 파티션: 2020-01-01 ~ 2020-12-31
• 세 번째 파티션: 2021-01-01 ~ ∞ (다음 값이 있으면 그 이전까지)

2019-12-31	첫 번째 파티션
2020-01-01	두 번째 파티션 (경계값 포함) ✅
2020-12-31	두 번째 파티션
2021-01-01	세 번째 파티션 (경계값 포함) ✅
2021-01-02	세 번째 파티션

 

---

2.  파티션 스키마 생성 

-- 2. 파티션 스키마 생성
CREATE PARTITION SCHEME ps_appr_end_date
AS PARTITION pf_appr_end_date
TO (FileGroup1, FileGroup2, FileGroup3, [PRIMARY]);

 

파티션 스키마를 생성하여, 파티션 함수에 의해 분할된 데이터를 특정 파일 그룹에 저장하도록 설정하는 작업을 수행합니다.

 

---

3.  파티션 테이블 생성 

-- 3. 파티셔닝된 테이블 생성
CREATE TABLE appr_partitioned (
	id nvarchar(54) COLLATE Korean_Wansung_CI_AS NOT NULL,
	desc nvarchar(100) COLLATE Korean_Wansung_CI_AS NULL,
    CONSTRAINT PK_appr_id PRIMARY KEY NONCLUSTERED (id)
) ON ps_appr_req_date(appr_req_date);

파티션 스키마를 이용하여 파티션 테이블을 만들어줍니다.

 

---

4.  기존 테이블 데이터 마이그레이션

-- 4. 기존 테이블 데이터 마이그레이션
INSERT INTO appr_partitioned
SELECT * FROM appr;

 

저는 기존 테이블이 존재하였기 때문에 데이터도 마이그레이션 해주었습니다.

 

 

혹시 틀린점이 있다면 댓글로 알려주세요!