[MSSQL] 슬라이딩 윈도우:테이블 파티셔닝 기법

이번에는 슬라이딩 윈도 알고리즘을 통한 테이블 파티셔닝 슬라이딩 윈도 기법에 대해 포스트 할 예정이다.



로그성 데이터와같이 데이터가 계속 쌓이는 테이블의 경우 기간이 늘어날수록 데이터는 점점 방대해진다.



테이블의 행 데이터가 매우 많은 대용량 데이터베이스의 경우에 INSERT, UPDATE, SELECT 등의 작업은 갈수록 느려지게 마련이다.



이럴 경우, 테이블을 분할하는 것이 시스템 성능에 큰 도움이 된다.

 

---

● 윈도우 슬라이딩 프로세스

윈도 슬라이딩을 사용하는 상황은 무엇이 있을까? 아래 그림과 같이 6개월간의 데이터들이 모여있다고 하자.

1. SysEndTime 테이블은 최근 6개월의 데이터만 보관한다.
2. 6개월 초과된 데이터는 오래된 데이터를 모아두는 테이블에 보관한다.

최근 6개월의 데이터를 보관하기 위해서는 6개의 파티션이 필요하다.
=> 6개의 파티션이 생기면 총 7개의 공간이 생성된다. ( 벽이 6개가 있으면 공간은 7개이다. )
1~6번의 공간에는 최근 6개월 데이터가 보관되어있다

** 왜 6개월 데이터를 보관하는데 7개의 공간이 필요할까?**

이 스케줄러는 2/17일 0시 0분 0초 000에 실행이 된다.


이 스케줄러가 실행되는데에도 처리시간이 어느 정도 걸리는데 이 처리 도중에 INSERT 되는 공간을 미리 만들어 두는 것이다. 공간이 6개라면 데이터의 손실이 일어난다.

아래 그림이 윈도우 슬라이딩의 핵심 시퀀스이다.

1. 먼저 1번의 공간을 StagingTable테이블로 오래된 데이터를 모두 옮긴다.
2. 1번 공간과 2번 공간을 합친다. 그럼 2번 공간이 1번이 되고 3번 공간이 2번 공간이 되며 앞으로 당겨진다.
3. 마지막으로 6번 공간에는 2/16일 23시 59분 59초 999까지의 데이터가 모여있고 3/16일이라는 벽을 만들면서 6번 공간을 SPLIT 한다.
4. 기존 6번 공간이 나누어지면서 7번(6번) 공간이 생긴다.

위와 같은 프로세스가 윈도 슬라이딩 기법이다.

실습을 통해 알아보자

---

● 테이블 파티셔닝 순서

1. 파일 그룹으로 분리한 데이터베이스 생성 또는 데이터베이스에 파일그룹 추가
2. 파티션 함수 (Partition Function)를 정의 : 파티션 함수는 테이블 또는 인덱스를 분할하는 방법을 정의한다.
3. 파티션 구성표 (Partition Schema)를 정의 : 파티션 구성표는 파티션 함수에 의해 생성된 파티션을 파일 그룹별로 나눠주는 역할을 한다.
4. 테이블 정의 시에 파티션 구성표를 적용 (파티션 함수가 아닌 구성표를 적용한다.)
5. 데이터 입력 또는 대량 데이터 로드
6. 자동으로 범위에 따라서 파일 그룹에 분할되어 저장됨.

---

● 실습

로그인 로그 테이블(LOGIN_SESSION)을 대상으로 같은 파일 그룹에 RegDate를 기준으로 파티션을 나눠 3개월의 데이터를 보존(해당 월 포함)하는 실습을 진행.

1. 파일 그룹으로 분리한 데이터베이스 생성 또는 데이터베이스에 파일그룹 추가

-- TESTDB 데이터베이스에 "PARTITION_FILEGROUP" 파일그룹 생성
ALTER DATABASE TESTDB
  ADD FILEGROUP PARTITION_FILEGROUP

-- "PARTITION_FILEGROUP" 파일그룹이 사용할 파일을 생성
ALTER DATABASE TESTDB
  ADD FILE
      (
    NAME = 'PARTITION_FILEGROUP'
    ,FILENAME = '/var/opt/mssql/data/PARTITION_FILEGROUP.ndf'
    , SIZE = 102400KB  -- 초기SIZE
    , MAXSIZE = UNLIMITED -- 어느 SIZE까지 증가를 허용
    , FILEGROWTH = 102400KB -- 파일의 증가시 얼마씩 증가
      )
   TO FILEGROUP PARTITION_FILEGROUP

---

2. 파티션 함수(Partition Function)를 정의 : 파티션 함수는 테이블 또는 인덱스를 분할하는 방법을 정의한다.

• 3개월의 데이터를 보존하기 위해서는 파티션이 총 3개, 파티션 공간이 4개가 필요하다.
  • 만약 4/30일에서 5/1일로 넘어가 5/1 00시 00분 00초에 스케줄러가 돌아간다고 가정을 하자.
    스케줄러가 돌아가는 5월 1일 0시 0분 0초에 2월, 3월, 4월의 데이터와 5월 0시 0분 0초에 쌓이는 데이터도 실시간으로 보관이 되어야 하므로 총 4개의 파티션 공간이 필요하다. ( 스케줄러가 0.000초 만에 끝나는 게 아니기 때문에.... )

-- PFN_ThreeMonth_Partition(datetime) datetime을 매개변수로 가지는 파티션 함수를 생성해준다.
-- 파티션이 가지는 범위을 경계값도 설정해준다.
CREATE PARTITION FUNCTION PFN_ThreeMonth_Partition (DATETIME)
AS
 RANGE RIGHT FOR VALUES
 (
     '2021-03-01 00:00:00'
    ,'2021-04-01 00:00:00'
    ,'2021-05-01 00:00:00'
 )
 -- 파티션 함수를 호출해보면 4월 30일 11시 40초가 몇번째 파티션인지 출력해준다.
 SELECT $Partition.PFN_ThreeMonth_Partition('2021-04-30 11:00:40')

 

> 파티션 함수에서 RANGE를 정의할 때 **LEFT**, **RIGHT**를 설정해주는데 아래의 사진이 설명해준다.

 

 

---

3. 파티션 구성표(Partition Schema)를 정의 : 파티션 구성표는 파티션 함수에 의해 생성된 파티션을 파일 그룹별로 나눠주는 역할을 한다.

파티션을 파일 그룹에 매핑하는 법은 다양하다. 1. 만들어진 파티션들을 모두 같은 파일그룹에 매핑 2. 만들어진 파티션들을 각각 다른 파일그룹에 매핑 3. 만들어진 파티션들을 **몇개는 같은 파일그룹 ** 나머지는 **각각 다른 파일 그룹**에 매핑
`주의할 점. 파티션보다 적은 파일 그룹을 매핑하면 에러 발생.!!! ` `(ex. 파티션 공간 4개 => 파일그룹 3개만 매핑 )`

-- 모든 파티션을 같은 파일그룹에 매핑하는 파티션 구성표
-- ALL 키워드를 통해 모든 파티션을 "PARTITION_FILEGROUP"에 매핑한다.
CREATE PARTITION SCHEME SCH_ThreeMonth_Partition
AS PARTITION PFN_ThreeMonth_Partition ALL TO ( PARTITION_FILEGROUP )

-- 파티션 공간이 3개라고 가정하고 각 공간별로 다른 파일그룹을 매핑하는 파티션 구성표 
-- 파티션 개수보다 적은 파일그룹을 매핑 시 에러 발생!!! 
-- 더 많이 매핑은 가능 ( 더 많이 매핑했다면, 초과된 파일 그룹은 NEXT USED에서 사용될 파일 그룹으로 자동 지정됨)
CREATE PARTITION SCHEME SCH_ThreeMonth_Partition 
    AS PARTITION PFN_ThreeMonth_Partition 
    TO ( FILEGORUP1, FILEGROUP2, FILEGROUP3 )

*실습에서는 같은 파일 그룹에 매핑하는 방법을 사용한다. 

---

4. 테이블 정의 시에 파티션 구성표를 적용 (파티션 함수가 아닌 구성표를 적용한다. )

-- LOGIN_SESSION 테이블 생성시 파티션 구성표를 적용한다.
-- 만약 테이블 생성시 INDEX를 생성한다면 파티셔닝 KEY(REG_DATE)가 포함되어야한다.
CREATE TABLE LOGIN_SESSION
(
  SESSION_ID INT
  , REG_DATE DATETIME NOT NULL
) ON SCH_ThreeMonth_Partition(REG_DATE)

-- 파티셔닝 KEY를 포함하여 파티셔닝 인덱스 생성 FILEFACTOR는 90설정
-- 파티션 구성표에 적용될 값 = REG_DATE
CREATE CLUSTERED INDEX CL1_REG_DATE ON dbo.LOGIN_SESSION(SESSION_ID, REG_DATE)
WITH FILEFACTOR = 90 ON SCH_ThreeMonth_Partition(REG_DATE)

---

5. 데이터 입력 또는 대량 데이터 로드

-- 첫번째 파티션 데이터
INSERT INTO NUMBER_TABLE VALUES (1      , '2020-02-11 11:48:01')
INSERT INTO NUMBER_TABLE VALUES (2    , '2020-02-17 11:48:02')
INSERT INTO NUMBER_TABLE VALUES (3    , '2020-02-21 11:48:03')
INSERT INTO NUMBER_TABLE VALUES (4    , '2020-02-30 11:48:01')

--두번째 파티션 데이터
INSERT INTO NUMBER_TABLE VALUES (5    , '2020-03-07 11:48:02')
INSERT INTO NUMBER_TABLE VALUES (6    , '2020-03-21 11:48:03')

--세번째 파티션 데이터
INSERT INTO NUMBER_TABLE VALUES (7    , '2020-04-10 11:48:01')
INSERT INTO NUMBER_TABLE VALUES (8    , '2020-04-18 11:48:01')
INSERT INTO NUMBER_TABLE VALUES (9    , '2020-04-29 11:48:02')

-- 스케줄러 실행시 들어온 로그데이터
--네번째 파티션 데이터
INSERT INTO NUMBER_TABLE VALUES (10    , '2020-05-01 00:00:01')

6. 자동으로 범위에 따라서 파일 그룹에 분할되어 저장됨

SELECT * FROM LOGIN_SESSION

 

---

●실습 최종 코드

-- 스위치 테이블을 비워준다.
 TRUNCATE TABLE LOGIN_SESSION_SWITCH

-- 무조건 SWITCH 테이블은 비어있어야 스위칭이 가능하다.
-- LOGIN_SESSION 테이블의 파티션 1번 내용을 LOGIN_SESSION_SWITCH테이블로 스위칭한다.
 ALTER TABLE dbo.LOGIN_SESSION
  SWITCH PARTITION 1      
 TO LOGIN_SESSION_SWITCH


-- 첫 경계값을 삭제하고 파티션1과 파티션2를 합병한다.
ALTER PARTITION FUNCTION PFN_ThreeMonth_Partition() MERGE RANGE('2020-03-01 00:00:00')


-- NEXT USED 파티션의 파일그룹을 "PARTITION_FILEGROUP"로 지정한다.
 ALTER PARTITION SCHEME SCH_ThreeMonth_Partition       
 NEXT USED PARTITION_FILEGROUP;    


 -- 경계값을 추가하여 파티션4번을 분할(SPLIT)한다. 
 -- 분할되어 추가된 파티션은 13번줄에서 NEXT USED로 설정된 "PARTITION_FILEGROUP"파일그룹에 매핑.
ALTER PARTITION FUNCTION PFN_ThreeMonth_Partition()       
SPLIT RANGE ('2021-06-01 00:00:00');      

 -- 스위치 테이블을 비워준다.
 TRUNCATE TABLE LOGIN_SESSION_SWITCH