[SQL] 파티셔닝 대용량 데이터 처리하기

안녕하세요.

오늘은 SQL로 대용량 데이터를 처리하는데 용이한 파티셔닝에 대해 간단하게 알아보겠습니다.

 

 SQL 데이터 파티셔닝하기

▣ 파티셔닝이란?

하나의 큰 테이블을 논리 또는 물리적으로 여러 개의 작은 조각(파티션)으로 나누는 기술

 

 - 대용량 데이터베이스(DB)에서 성능을 향상시키고 관리를 용이하게 하는 기술로, 데이터와 인덱스의 양을 최소화하여 조회 기능을 향상시킬 수 있음

 - 행을 기준으로 나누는 수평 파티셔닝과 열을 기준으로 나누는 수직 파티셔닝이 있음

 

▣ SQL 실습해보기

 - 먼저, 파티션 함수 및 스키마를 생성하여 파티션된 테이블(Orders)*을 생성

 * Column 설명

• OrderID: 주문 고유 식별자
• CustomerID: 고객 식별자
• OrderDate: 주문 날짜, 파티셔닝의 기준이 되는 열
• TotalAmount: 주문 총액

 - OrderDate 열 기준으로 파티셔닝되어 있으며, '23년과 '24년 초의 분기별로 파티션을 나눔

-- 1. 파티션 함수 생성
CREATE PARTITION FUNCTION PF_OrderDate (datetime2)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-04-01', '2023-07-01', '2023-10-01', '2024-01-01');

-- 2. 파티션 스키마 생성
CREATE PARTITION SCHEME PS_OrderDate
AS PARTITION PF_OrderDate ALL TO ([PRIMARY]);

-- 3. 파티션된 테이블 생성
CREATE TABLE Orders (
    OrderID int IDENTITY(1,1) PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderDate datetime2 NOT NULL,
    TotalAmount decimal(10,2) NOT NULL
) ON PS_OrderDate(OrderDate);

 

 - 데이터를 날짜에 따라 적절한 파티션에 분산시켜 저장하고 '23년 2분기부터 3분기까지의 값을 조회하는 쿼리 실행

-- 4. 데이터 삽입
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount) VALUES 
(1001, '2023-02-15', 150.00),
(1002, '2023-05-20', 200.00),
(1003, '2023-08-10', 300.00),
(1004, '2023-11-30', 250.00),
(1005, '2024-01-05', 180.00);

-- 5. 파티션을 활용한 쿼리
SELECT * FROM Orders
WHERE OrderDate >= '2023-04-01' AND OrderDate < '2023-10-01';

 

 - OrderDate가 '23년 2, 3분기에 해당하는 row 조회 결과

OrderID  CustomerID  OrderDate               TotalAmount
-------  ----------  ----------------------  -----------
2        1002        2023-05-20 00:00:00.00  200.00
3        1003        2023-08-10 00:00:00.00  300.00

 

 - 특정 조회 결과 말고, 해당 데이터의 파티셔닝을 볼 수 있는 방법은 아래와 같음

 - sys.partitions로 SQL server의 시스템 뷰를 생성 및 JOIN을 통해 Orders 테이블의 파티션 번호별 결과를 조회할 수 있음

SELECT 
    p.partition_number,
    p.rows,
    CASE
        WHEN p.partition_number = 1 THEN 'Less than 2023-01-01'
        ELSE CONVERT(varchar, rv.value, 23)
    END AS boundary_value
FROM sys.partitions p
JOIN sys.tables t ON p.object_id = t.object_id
LEFT JOIN sys.partition_range_values rv ON p.partition_number = rv.boundary_id
WHERE t.name = 'Orders' AND p.index_id <= 1
ORDER BY p.partition_number;

partition_number  rows  boundary_value
----------------  ----  ---------------
1                 0     Less than 2023-01-01
2                 1     2023-01-01
3                 1     2023-04-01
4                 1     2023-07-01
5                 1     2023-10-01
6                 1     2024-01-01