Interview-Base Learning: Tối Ưu SQL – Table Partitioning – Challenge #01 [171225]

Challenge này là 1 câu trong 500+ Challenges của khóa học Data Analyst BootCamp mình soạn cho các bạn trong lớp thực hành, sẵn Public Free cho các bạn khác cập nhật thêm kiến thức. Nếu bạn thích học Phân Tích Dữ Liệu Thực Chiến vừa GG Meet vừa Challenges như thế này, thì có thể đăng ký khóa học Data Analyst BootCamp nhé. Inbox mình Tại Đây.

Sau khi hoàn thành câu Interview – Challenge #01 này, bạn sẽ:

• Hiểu tại sao cần partitioning và khi nào nên dùng.
• Biết cách tạo Partition Function, Partition Scheme, và Partitioned Table.
• Phân biệt được RANGE LEFT vs RANGE RIGHT.
• Hiểu quy tắc Primary Key phải chứa Partition Key.

Cho schema hiện tại của bảng Orders:

CREATE TABLE dbo.Orders
(
    OrderId         bigint          NOT NULL IDENTITY(1,1),
    OrderDate       datetime2(0)    NOT NULL,
    CustomerId      bigint          NOT NULL,
    ProductId       int             NOT NULL,
    Quantity        int             NOT NULL,
    TotalAmount     decimal(18,2)   NOT NULL,
    Status          varchar(20)     NOT NULL,
    CreatedAt       datetime2(0)    NOT NULL DEFAULT SYSDATETIME()
);

• Viết script tạo Partition Function theo tháng từ 2023-01-01 đến 2025-12-01 (sử dụng RANGE RIGHT)
• Viết script tạo Partition Scheme map tất cả partitions vào filegroup [PRIMARY]
• Viết script tạo lại bảng Orders với partitioning và clustered index phù hợp

Lấy ví dụ đơn giản cho bạn hình dung trước: Bạn là nhân viên văn phòng quản lý 500 triệu hóa đơn.

• Không có partitioning = 1 tủ hồ sơ khổng lồ
• Có partitioning = Chia thành nhiều ngăn kéo theo tháng

Partitioning là kỹ thuật chia ngang (horizontal slicing) một table logic thành nhiều phần vật lý gọi là partitions, dựa trên giá trị của một partitioning column (thường là ngày tháng).

Để partition một table, bạn cần tạo 3 thứ theo đúng thứ tự:

Partition Function trả lời câu hỏi: “Cắt data ở đâu?”

Bạn cần định nghĩa các boundary values (điểm cắt). Ví dụ: cắt vào ngày đầu tiên mỗi tháng.

CREATE PARTITION FUNCTION tên_function ( kiểu_dữ_liệu )
AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( giá_trị_1, giá_trị_2, ... );

• RANGE LEFT: Boundary thuộc về partition bên trái
• RANGE RIGHT: Boundary thuộc về partition bên phải

Trong challenge này dùng RANGE RIGHT vì:

• Boundary ‘2023-01-01’ = “Đầu tháng 1/2023” -> Partition tháng 1 chứa data từ ngày 1/1/2023

CREATE PARTITION FUNCTION pf_Demo (date)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2023-02-01', '2023-03-01');

Công thức: n boundaries → n+1 partitions

-- Tạo Partition Function theo tháng với RANGE RIGHT
CREATE PARTITION FUNCTION pf_OrdersByMonth (datetime2(0))
AS RANGE RIGHT FOR VALUES
(
    -- 2023: 12 tháng
    '2023-01-01', '2023-02-01', '2023-03-01', '2023-04-01', 
    '2023-05-01', '2023-06-01', '2023-07-01', '2023-08-01', 
    '2023-09-01', '2023-10-01', '2023-11-01', '2023-12-01',
    
    -- 2024: 12 tháng
    '2024-01-01', '2024-02-01', '2024-03-01', '2024-04-01', 
    '2024-05-01', '2024-06-01', '2024-07-01', '2024-08-01', 
    '2024-09-01', '2024-10-01', '2024-11-01', '2024-12-01',
    
    -- 2025: 12 tháng
    '2025-01-01', '2025-02-01', '2025-03-01', '2025-04-01', 
    '2025-05-01', '2025-06-01', '2025-07-01', '2025-08-01', 
    '2025-09-01', '2025-10-01', '2025-11-01', '2025-12-01'
);

• pf_OrdersByMonth: Tên function – Đặt tên gợi nhớ: pf = partition function
• datetime2(0): Data type – PHẢI khớp với column OrderDate trong table
• RANGE RIGHT: Loại range – Boundary thuộc partition bên phải
• 36 values: Boundaries – 36 boundaries → 37 partitions
• Partition 1: Data trước 2023-01-01 (catch-all cho data cũ). Partition 2-13: Tháng 1-12/2023. Partition 14-25: Tháng 1-12/2024. Partition 26-37: Tháng 1-12/2025

-- Query 1: Xem partition function đã tạo
SELECT 
    name AS TenFunction,
    fanout AS SoPartitions,  -- fanout = số partitions
    CASE boundary_value_on_right 
        WHEN 1 THEN 'RANGE RIGHT' 
        ELSE 'RANGE LEFT' 
    END AS LoaiRange
FROM sys.partition_functions
WHERE name = 'pf_OrdersByMonth';

-- Query 2: Xem chi tiết các boundary values
SELECT 
    prv.boundary_id AS STT,
    CONVERT(VARCHAR, prv.value, 120) AS BoundaryValue,
    'Partition ' + CAST(prv.boundary_id AS VARCHAR) + 
    ' chua: >= ' + CONVERT(VARCHAR, prv.value, 120) AS MoTa
FROM sys.partition_functions pf
JOIN sys.partition_range_values prv 
    ON pf.function_id = prv.function_id
WHERE pf.name = 'pf_OrdersByMonth'
ORDER BY prv.boundary_id;

Partition Scheme trả lời câu hỏi: “Mỗi partition lưu ở filegroup nào?”

Filegroup là gì? Đơn giản là thư mục logic chứa data files trong SQL Server.

• Filegroup: PRIMARY → Ổ SSD nhanh
  • → Partition mới → Truy vấn thường
• Filegroup: FG_ARCHIVE → Ổ HDD
  • → Partition cũ → Ít truy vấn

-- Cách 1: Map từng partition vào từng filegroup riêng
CREATE PARTITION SCHEME tên_scheme
AS PARTITION tên_function
TO ( filegroup_1, filegroup_2, filegroup_3, ... );

-- Cách 2: Map TẤT CẢ partitions vào 1 filegroup (đơn giản)
CREATE PARTITION SCHEME tên_scheme
AS PARTITION tên_function
ALL TO ( [tên_filegroup] );

-- Map tất cả partitions vào PRIMARY filegroup
CREATE PARTITION SCHEME ps_OrdersByMonth
AS PARTITION pf_OrdersByMonth
ALL TO ([PRIMARY]);

• ps_OrdersByMonth: Tên scheme – ps = partition scheme
• AS PARTITION pf_OrdersByMonth: Liên kết – Scheme này dùng function nào
• ALL TO ([PRIMARY]): Mapping – Tất cả 37 partitions → filegroup PRIMARY

-- Xem scheme và function liên kết
SELECT 
    ps.name AS TenScheme,
    pf.name AS TenFunction
FROM sys.partition_schemes ps
JOIN sys.partition_functions pf 
    ON ps.function_id = pf.function_id
WHERE ps.name = 'ps_OrdersByMonth';

-- Xem mapping chi tiết: Partition nào → Filegroup nào
SELECT 
    dds.destination_id AS PartitionNumber,
    fg.name AS FilegroupName
FROM sys.partition_schemes ps
JOIN sys.destination_data_spaces dds 
    ON ps.data_space_id = dds.partition_scheme_id
JOIN sys.filegroups fg 
    ON dds.data_space_id = fg.data_space_id
WHERE ps.name = 'ps_OrdersByMonth'
ORDER BY dds.destination_id;

Partitioned Table trả lời câu hỏi: “Bảng nào được partition?”

Sau khi có Function và Scheme, bạn cần tạo (hoặc tái tạo) table và gắn nó vào scheme.

CREATE TABLE tên_bảng
(
    column1 ...,
    column2 ...,
    CONSTRAINT tên_pk PRIMARY KEY CLUSTERED (columns)
)
ON tên_scheme(partition_column);  -- ← Phần quan trọng!

CREATE TABLE dbo.Orders (
    OrderId BIGINT,
    OrderDate DATETIME2(0),
    ...
    PRIMARY KEY CLUSTERED (OrderDate, OrderId)  -- Có OrderDate!
)
ON ps_OrdersByMonth(OrderDate);

Đặt Partition Key TRƯỚC trong clustered index:

PRIMARY KEY CLUSTERED (OrderDate, OrderId)

• Range queries trên OrderDate tận dụng được clustered index
• Partition elimination + index seek cùng lúc (Sẽ được học qua Challenge khác)
• Data được sắp xếp theo thời gian

CREATE TABLE dbo.Orders
(
    OrderId         bigint          NOT NULL IDENTITY(1,1),
    OrderDate       datetime2(0)    NOT NULL,
    CustomerId      bigint          NOT NULL,
    ProductId       int             NOT NULL,
    Quantity        int             NOT NULL,
    TotalAmount     decimal(18,2)   NOT NULL,
    Status          varchar(20)     NOT NULL,
    CreatedAt       datetime2(0)    NOT NULL DEFAULT SYSDATETIME(),
    
    -- Clustered PK: OrderDate đứng đầu (partition key)
    CONSTRAINT PK_Orders 
        PRIMARY KEY CLUSTERED (OrderDate, OrderId)
)
ON ps_OrdersByMonth(OrderDate);  -- Gắn vào partition scheme

• ON ps_OrdersByMonth(OrderDate): Gắn scheme – Data được partition theo OrderDate
• PRIMARY KEY (OrderDate, OrderId): Composite key – Tuân thủ quy tắc “PK chứa Partition Key”

-- Verify table đang dùng partition scheme
SELECT 
    t.name AS TenBang,
    i.name AS TenIndex,
    ps.name AS PartitionScheme,
    'Đã partition thành công!' AS TrangThai
FROM sys.tables t
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
WHERE t.name = 'Orders';

Partition Elimination = SQL Server bỏ qua các partitions không liên quan khi query

-- TEST PARTITION ELIMINATION
-- So sánh 2 cách viết query

-- Bật hiển thị số lần đọc (logical reads)
SET STATISTICS IO ON;
GO

-- QUERY A: Viết đúng cách - Có Partition Elimination
-- SQL Server chỉ đọc partition tháng 6/2024
SELECT 
    COUNT(*) AS SoOrders,
    SUM(TotalAmount) AS DoanhThu
FROM dbo.Orders
WHERE OrderDate >= '2024-06-01' 
  AND OrderDate < '2024-07-01';
GO

-- QUERY B: Viết sai cách - KHÔNG có Partition Elimination
-- SQL Server phải đọc TẤT CẢ partitions!
SELECT 
    COUNT(*) AS SoOrders,
    SUM(TotalAmount) AS DoanhThu
FROM dbo.Orders
WHERE YEAR(OrderDate) = 2024 
  AND MONTH(OrderDate) = 6;
GO

SET STATISTICS IO OFF;
GO

Kết quả:

• QUERY A: Table ‘Orders’. Scan count 37, logical reads 368.
• QUERY B: Table ‘Orders’. Scan count 37, logical reads 9645

Khi query chạy trên bảng Partitioned, SQL Server thường báo cáo Scan count bằng tổng số partitions có trong bảng (ở đây là 37), ngay cả khi nó chỉ thực sự chui vào 1 partition để lấy dữ liệu.

• Nó giống như việc bạn đi vào một khách sạn có 37 phòng.
  • Query A (Tối ưu): Bạn biết khách ở phòng 19, bạn đi thẳng vào phòng 19. Nhưng báo cáo vẫn ghi: “Phạm vi tìm kiếm: Khách sạn 37 phòng”.
  • Query B (Chậm): Bạn không biết khách ở đâu, bạn phải mở cửa cả 37 phòng để tìm. Báo cáo vẫn ghi: “Phạm vi tìm kiếm: Khách sạn 37 phòng”.

Logical Reads đếm số lượng trang dữ liệu (pages) thực tế mà SQL Server phải bốc từ bộ nhớ ra để đọc.

• Query B (Quét hết): 9,645 reads. (Nghĩa là phải đọc hết sạch dữ liệu trong 37 phòng).
• Query A (Partitioning): 368 reads. (Chỉ đọc dữ liệu của 1 phòng).
• Chênh lệch: 9645 / 368 ≈ 26 lần.

Partition Elimination ĐÃ HOẠT ĐỘNG THÀNH CÔNG. Query đã tối ưu có hiệu quả.

Tạo nonclustered index để optimize query sau:

SELECT * FROM Orders 
WHERE CustomerId = 500 
  AND OrderDate >= '2024-01-01';

Trước khi Index: Table ‘Orders’. Scan count 37, logical reads 6475

Index cần aligned với partition scheme

CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders (CustomerId, OrderDate)
ON ps_OrdersByMonth(OrderDate);  -- Aligned với partition scheme

Sau khi Index: Table ‘Orders’. Scan count 37, logical reads 215

• Index được tạo ON ps_OrdersByMonth(OrderDate) → Aligned index
• Mỗi partition của table có partition tương ứng của index
• Query sẽ tận dụng cả partition elimination VÀ index seek

SET NOCOUNT ON;

DECLARE @BatchSize INT = 10000;
DECLARE @TotalRows INT = 2000000;
DECLARE @CurrentBatch INT = 1;
DECLARE @StartTime DATETIME = GETDATE();

WHILE @CurrentBatch * @BatchSize <= @TotalRows
BEGIN
    INSERT INTO dbo.Orders (OrderDate, CustomerId, ProductId, Quantity, TotalAmount, Status)
    SELECT TOP (@BatchSize)
        DATEADD(DAY, ABS(CHECKSUM(NEWID())) % 1095, '2023-01-01'),
        (ABS(CHECKSUM(NEWID())) % 10000) + 1,
        (ABS(CHECKSUM(NEWID())) % 100) + 1,
        (ABS(CHECKSUM(NEWID())) % 20) + 1,
        CAST((ABS(CHECKSUM(NEWID())) % 999000 + 1000) / 100.0 AS DECIMAL(18,2)),
        CASE ABS(CHECKSUM(NEWID())) % 5
            WHEN 0 THEN 'Pending'
            WHEN 1 THEN 'Processing'
            WHEN 2 THEN 'Shipped'
            WHEN 3 THEN 'Delivered'
            ELSE 'Cancelled'
        END
    FROM sys.all_objects a CROSS JOIN sys.all_objects b;
    
    IF (@CurrentBatch * @BatchSize) % 100000 = 0
        PRINT '   ✓ Đã insert: ' + FORMAT(@CurrentBatch * @BatchSize, 'N0') + ' / 1,000,000 (' 
              + CAST((@CurrentBatch * @BatchSize * 100 / @TotalRows) AS VARCHAR) + '%)';
    
    SET @CurrentBatch = @CurrentBatch + 1;
END

DECLARE @TotalInserted INT;
SELECT @TotalInserted = COUNT(*) FROM dbo.Orders;
GO