SQL Challenge Day #083 – Part 01

Đây là phần kiến thức nâng cao liên quan tới tối ưu database. Nếu bạn muốn học & thực chiến nhiều challenges thực tế hơn thì có thể đăng ký Khóa Học SQLDA hoặc Data Analyst BootCamp. Inbox mình Tại Đây.

Bảng SalesOrders có hàng triệu dòng, truy vấn báo cáo rất chậm, bảo trì index mất nhiều thời gian. Bạn cần triển khai Table Partitioning theo OrderDate để cải thiện hiệu suất.

Tưởng tượng bạn có một tủ sách khổng lồ với 1 triệu cuốn sách:

• Không partition
  • Tất cả sách đều chồng lên nhau trong 1 tủ duy nhất.
  • Muốn tìm sách năm 2023 → phải tìm từng cuốn trong 1 triệu cuốn → MẤT RẤT NHIỀU THỜI GIAN!
• Có partition
  • Chia tủ thành nhiều ngăn theo năm xuất bản:
    • Ngăn 1: Sách năm 2022 trở về trước
    • Ngăn 2: Sách năm 2023
    • Ngăn 3: Sách năm 2024
    • Ngăn 4: Sách năm 2025 và sau
  • Muốn tìm sách năm 2023 → CHỈ MỞ NGĂN 2 → NHANH HƠN NHIỀU LẦN!

PARTITION – chia bảng lớn thành nhiều phần nhỏ để dễ quản lý!

• TRUY VẤN NHANH HƠN: Thay vì quét toàn bộ bảng, chỉ cần quét partition liên quan.
• BẢO TRÌ DỄ DÀNG: Rebuild index từng partition thay vì rebuild toàn bộ bảng.
• XÓA DỮ LIỆU CŨ NHANH: Xóa cả partition cũ trong tích tắc thay vì DELETE từng dòng.
• TẢI DỮ LIỆU MỚI HIỆU QUẢ: Sử dụng “Partition Switching” để load dữ liệu trong giây lát

• BƯỚC 1: PARTITION FUNCTION (Quy tắc chia) → Định nghĩa CÁCH CHIA dữ liệu thành các partition → Giống như quy tắc: “Sách nào năm nào vào ngăn nào”
  • PARTITION FUNCTION là gì?
    • Là “quy tắc chia” – định nghĩa cách chia dữ liệu.
    • Dựa vào cột nào? (OrderDate).
    • Chia theo điểm nào? (2023-01-01, 2024-01-01, 2025-01-01);
  • CREATE PARTITION FUNCTION tên_function (kiểu_dữ_liệu) AS RANGE RIGHT | LEFT FOR VALUES (điểm_chia_1, điểm_chia_2, …)
    • tên_function: Tên đặt cho quy tắc này (vd: pf_SalesByYear)
    • kiểu_dữ_liệu: Kiểu của cột dùng để chia
    • RANGE RIGHT | LEFT: Cách phân loại dữ liệu
    • VALUES: Các điểm ranh giới để chia partition
    • RANGE RIGHT vs RANGE LEFT – KHÁC NHAU THẾ NÀO?
      • Giả sử điểm chia là: ‘2023-01-01’
      • RANGE RIGHT (điểm ranh giới thuộc partition BÊN PHẢI):
        • Partition 1: … đến < 2023-01-01 (không bao gồm 2023-01-01)
        • Partition 2: 2023-01-01 đến … (BAO GỒM 2023-01-01)
        • Ngày 2023-01-01 được xếp vào partition BÊN PHẢI → Dễ hiểu: “từ ngày này trở đi”
      • RANGE LEFT (điểm ranh giới thuộc partition BÊN TRÁI):
        • Partition 1: … đến <= 2023-01-01 (BAO GỒM 2023-01-01)
        • Partition 2: > 2023-01-01 đến … (không bao gồm 2023-01-01)
        • Ngày 2023-01-01 được xếp vào partition BÊN TRÁI
      • Ví dụ với quy tắc RANGE RIGHT: Điểm chia: ‘2023-01-01’, ‘2024-01-01’, ‘2025-01-01’ → Tạo ra 4 partition (số partition = số điểm chia + 1)
        • Partition 1: OrderDate < ‘2023-01-01’ → Tất cả dữ liệu năm 2022 và cũ hơn
        • Partition 2: ‘2023-01-01’ ≤ OrderDate < ‘2024-01-01’ → Tất cả dữ liệu năm 2023
        • Partition 3: ‘2024-01-01’ ≤ OrderDate < ‘2025-01-01’ → Tất cả dữ liệu năm 2024
        • Partition 4: OrderDate ≥ ‘2025-01-01’ → Tất cả dữ liệu năm 2025 và mới hơn

CREATE PARTITION FUNCTION pf_SalesByYear (DATE)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01', '2025-01-01');

SELECT 
    pf.name AS partition_function_name,
    pf.type_desc AS data_type,
    pf.boundary_value_on_right AS is_range_right,
    prv.value AS boundary_value,
    prv.boundary_id AS boundary_order
FROM sys.partition_functions pf
LEFT JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id
WHERE pf.name = 'pf_SalesByYear'
ORDER BY prv.boundary_id;

partition_function_name	data_type	is_range_right	boundary_value	boundary_order
pf_SalesByYear	RANGE	1	2023-01-01 00:00:00.000	1
pf_SalesByYear	RANGE	1	2024-01-01 00:00:00.000	2
pf_SalesByYear	RANGE	1	2025-01-01 00:00:00.000	3

• partition_function_name: Tên function vừa tạo.
• data_type: Kiểu dữ liệu (DATE).
• is_range_right: 1 = RANGE RIGHT, 0 = RANGE LEFT
• boundary_value: Các điểm ranh giới
• boundary_order: Thứ tự của điểm ranh giới

• BƯỚC 2: TẠO PARTITION SCHEME (Sơ đồ lưu trữ)
  • PARTITION SCHEME là gì?
    • Là “sơ đồ lưu trữ” – chỉ định partition nào lưu ở đâu.
    • Ánh xạ các partition vào các FILEGROUP trong database.
  • FILEGROUP là gì?
    • FILEGROUP là “nhóm file vật lý” trên ổ đĩa nơi SQL Server lưu dữ liệu.
    • Mỗi database có ít nhất 1 filegroup: [PRIMARY]
    • Trong môi trường thực tế:
      • Có thể tạo nhiều filegroup khác nhau.
      • Mỗi filegroup có thể nằm trên ổ đĩa vật lý khác nhau.
      • Tối ưu I/O bằng cách phân tán dữ liệu.
    • CREATE PARTITION SCHEME tên_scheme AS PARTITION tên_function ALL TO (filegroup) | TO (filegroup1, filegroup2, …)
      • ALL TO (filegroup): Tất cả partition lưu vào 1 filegroup
      • TO (fg1, fg2, fg3, fg4): Mỗi partition vào filegroup riêng
    • FILEGROUP1, FILEGROUP2… ở đâu ra?
      • filegroup1, filegroup2, FG2022, FG2023… → Chúng chưa tồn tại! → Bạn phải TỰ TẠO chúng trước khi dùng!
      • SQL Server MẶC ĐỊNH chỉ có 1 filegroup: [PRIMARY] → Khi cài SQL Server, nó tự động tạo [PRIMARY] → Đó là filegroup DUY NHẤT bạn có từ đầu!
      • Muốn dùng nhiều filegroup → PHẢI TỰ TẠO
        • ALTER DATABASE YourDB ADD FILEGROUP [FG2022];
        • ALTER DATABASE YourDB ADD FILEGROUP [FG2023];
        • ALTER DATABASE YourDB ADD FILE (NAME = ‘Data2022’, FILENAME = ‘D:\Data\2022.ndf’, SIZE = 100MB) TO FILEGROUP [FG2022];
        • ALTER DATABASE YourDB ADD FILE (NAME = ‘Data2023’, FILENAME = ‘E:\Data\2023.ndf’, SIZE = 100MB) TO FILEGROUP [FG2023];

CREATE PARTITION SCHEME ps_SalesByYear
AS PARTITION pf_SalesByYear
ALL TO ([PRIMARY]);

Ánh xạ từ Partition Function: pf_SalesByYear → Tất cả 4 partition lưu vào filegroup: [PRIMARY]

SELECT 
    ps.name AS partition_scheme_name,
    pf.name AS partition_function_name,
    fg.name AS filegroup_name,
    dds.destination_id AS partition_number
FROM sys.partition_schemes ps
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
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_SalesByYear'
ORDER BY dds.destination_id;

partition_scheme_name	partition_function_name	filegroup_name	partition_number
ps_SalesByYear	pf_SalesByYear	PRIMARY	1
ps_SalesByYear	pf_SalesByYear	PRIMARY	2
ps_SalesByYear	pf_SalesByYear	PRIMARY	3
ps_SalesByYear	pf_SalesByYear	PRIMARY	4
ps_SalesByYear	pf_SalesByYear	PRIMARY	5

• partition_scheme_name: Tên scheme vừa tạo
• partition_function_name: Function mà scheme này sử dụng
• filegroup_name: Nơi lưu trữ vật lý (PRIMARY)
• partition_number: Số thứ tự partition (1, 2, 3, 4)

• Tại sao cần Partition Scheme, không dùng Function trực tiếp?
  • Function chỉ định nghĩa CÁCH CHIA
  • Scheme định nghĩa NƠI LƯU – cần cả 2 mới đủ!      
• Bảng cũ có dữ liệu rồi, làm sao partition?
  • Không thể partition bảng cũ trực tiếp
  • Phải: Tạo bảng mới → Copy dữ liệu → Đổi tên bảng
• Partition ảnh hưởng đến query như thế nào?
  • SQL Server tự động áp dụng “Partition Elimination”
  • Chỉ quét partition cần thiết, bỏ qua partition không liên quan → Cải thiện hiệu suất ĐÁNG KỂ!