Partitioning trong SQL Server là gì? Chi tiết và củ thể

Để tạo bảng như hình trên trong SQL Server và áp dụng Partition, bạn có thể làm theo các bước sau:

Mục tiêu:#

• Tạo bảng có các cột như trong hình.

• Chia Partition dựa trên cột Thời gian hiệu lực hoặc Thời gian hết hạn (tuỳ vào yêu cầu thực tế).

Giải pháp:#

• Tạo Partition Function:
  Giả sử bạn muốn chia partition theo năm dựa trên cột Thời gian hiệu lực.

CREATE PARTITION FUNCTION PF_Year (DATETIME) AS RANGE RIGHT FOR VALUES ('2024-01-01', '2025-01-01', '2026-01-01');

Các giá trị '2024-01-01', '2025-01-01', '2026-01-01' sẽ chia bảng thành các partition theo từng năm.

• Tạo Partition Scheme:
  Ánh xạ partition vào filegroup (hoặc để mặc định).

CREATE PARTITION SCHEME PS_Year AS PARTITION PF_Year TO ([PRIMARY], [PRIMARY], [PRIMARY]);

• Tạo bảng với Partition Scheme:
  Bảng sẽ được tạo với Partition Scheme, sử dụng cột Thời gian hiệu lực làm cột phân vùng.

CREATE TABLE InsuranceRecords ( Stt INT, BienKiemSoat NVARCHAR(20), ThoiGianHieuLuc DATETIME, ThoiGianHetHan DATETIME, DoanhNghiep NVARCHAR(50), GCNBaoHiem NVARCHAR(50), NCC NVARCHAR(10), ThoiGianKiemTra DATETIME, Loai NVARCHAR(50), TrangThai NVARCHAR(20), HoatDong NVARCHAR(20) ) ON PS_Year (ThoiGianHieuLuc);

• Chèn dữ liệu:
  Dữ liệu sẽ tự động được phân bổ vào các partition dựa trên giá trị của cột Thời gian hiệu lực.

INSERT INTO InsuranceRecords VALUES (1, '30A-1234.098', '2024-11-09 10:00', '2024-12-09 10:00', 'Cty TNHH AC Logistic', 'BIC09884488', 'BIC', '2024-09-13 10:00', 'Kiểm tra tự động', 'Đã sử dụng', 'Hủy duyệt'), (2, '30A-0988.000', '2024-11-09 10:00', '2024-12-09 10:00', 'Cty TNHH AC Logistic', 'BIC09884487', 'BIC', '2024-09-13 10:00', 'Biên phòng kiểm tra', 'Đã sử dụng', 'Hủy duyệt'), (3, '30A-0988.011', '2024-11-09 10:00', '2024-12-09 10:00', 'Cty TNHH AC Logistic', 'BIC09884486', 'BIC', '2024-09-13 10:00', 'Chưa sử dụng', 'Duyệt');

• Kiểm tra phân vùng:
  Dùng câu lệnh dưới để kiểm tra bảng đã được phân vùng đúng chưa:

SELECT partition_id, rows FROM sys.partitions WHERE object_id = OBJECT_ID('InsuranceRecords');

Nếu bạn có yêu cầu tùy chỉnh thêm (như chọn cột khác để chia partition), hãy cho mình biết để điều chỉnh! 😊

Trong SQL Server, Partition không thể tự động mở rộng hoặc thêm phân vùng mà không có sự can thiệp từ người dùng. Tuy nhiên, bạn có thể tạo một giải pháp "gần như tự động" bằng cách sử dụng các Stored Procedure hoặc SQL Agent Jobs để kiểm tra và thêm phân vùng mới khi cần thiết.

Ý tưởng về Partition tự động:#

• Kiểm tra giá trị tối đa trong cột phân vùng (ví dụ: ngày tháng).

• So sánh với các giá trị ngưỡng hiện có trong Partition Function.

• Nếu cần, thêm các giá trị ngưỡng mới vào Partition Function để mở rộng phân vùng.

Cách triển khai:#

• Tạo Partition Function và Partition Scheme ban đầu
  Giả sử bạn chia partition dựa trên năm trong cột ThoiGianHieuLuc:

CREATE PARTITION FUNCTION PF_Year (DATETIME) AS RANGE RIGHT FOR VALUES ('2024-01-01', '2025-01-01'); -- Ban đầu tạo phân vùng cho 2024 và 2025

CREATE PARTITION SCHEME PS_Year AS PARTITION PF_Year TO ([PRIMARY], [PRIMARY], [PRIMARY]);

• Tạo bảng với Partition Scheme
  Tạo bảng sử dụng Partition Scheme.

CREATE TABLE InsuranceRecords ( Stt INT, BienKiemSoat NVARCHAR(20), ThoiGianHieuLuc DATETIME, ThoiGianHetHan DATETIME, DoanhNghiep NVARCHAR(50), GCNBaoHiem NVARCHAR(50), NCC NVARCHAR(10), ThoiGianKiemTra DATETIME, Loai NVARCHAR(50), TrangThai NVARCHAR(20), HoatDong NVARCHAR(20) ) ON PS_Year (ThoiGianHieuLuc);

• Viết Stored Procedure để tự động mở rộng Partition
  Procedure này kiểm tra giá trị lớn nhất trong cột ThoiGianHieuLuc và tự động thêm phân vùng mới nếu cần.

` CREATE PROCEDURE AutoExtendPartition AS BEGIN DECLARE @MaxDate DATETIME; DECLARE @NewBoundary DATETIME;

   -- Lấy giá trị lớn nhất trong cột ThoiGianHieuLuc
   SELECT @MaxDate = MAX(ThoiGianHieuLuc) FROM InsuranceRecords;

   -- Tính giá trị ngưỡng tiếp theo (năm tiếp theo)
   SET @NewBoundary = DATEADD(YEAR, 1, DATEFROMPARTS(YEAR(@MaxDate), 1, 1));

   -- Kiểm tra xem giá trị ngưỡng đã tồn tại trong Partition Function chưa
   IF NOT EXISTS (
       SELECT * FROM sys.partition_range_values
       WHERE function_id = OBJECT_ID('PF_Year')
       AND RANGE_VALUE = @NewBoundary
   )
   BEGIN
       -- Thêm giá trị ngưỡng mới vào Partition Function
       ALTER PARTITION FUNCTION PF_Year() SPLIT RANGE (@NewBoundary);

       PRINT 'Partition đã được mở rộng đến: ' + CONVERT(VARCHAR, @NewBoundary);
   END
   ELSE
   BEGIN
       PRINT 'Partition đã tồn tại cho năm: ' + CONVERT(VARCHAR, @NewBoundary);
   END

END; `

• L lên lịch tự động bằng SQL Server Agent
  Tạo một SQL Agent Job để chạy Stored Procedure này hàng ngày hoặc khi cần.
  Trong SQL Server Management Studio (SSMS), vào SQL Server Agent > Jobs > New Job.

  • Cấu hình bước Job để chạy câu lệnh:

EXEC AutoExtendPartition;

Lợi ích:#

• Dễ dàng mở rộng phân vùng khi dữ liệu tăng mà không cần can thiệp thủ công.

• Giảm nguy cơ lỗi do thiếu phân vùng.

Hạn chế:#

• Việc mở rộng Partition Function có thể tốn tài nguyên nếu dữ liệu lớn.

• Giải pháp này không hoàn toàn tự động mà vẫn cần SQL Agent Job để kích hoạt.

Nếu cần triển khai cụ thể hơn hoặc tùy chỉnh cho trường hợp khác, bạn cứ trao đổi thêm! 😊

Partition trong SQL Server hữu ích nhất khi bạn làm việc với bảng rất lớn (thường là từ hàng triệu đến hàng tỷ hàng), nơi mà hiệu năng truy vấn, bảo trì, và quản lý dữ liệu trở thành một vấn đề quan trọng. Tuy nhiên, quyết định sử dụng Partition không chỉ phụ thuộc vào kích thước dữ liệu mà còn vào cách dữ liệu được truy vấn và quản lý.

Các trường hợp nên sử dụng Partition:#

• Dữ liệu lớn (Big Data): Khi bảng có hàng triệu hoặc hàng tỷ bản ghi và tốc độ truy vấn bắt đầu chậm lại.

  • Nếu bảng chứa dữ liệu lịch sử, ví dụ: log giao dịch, nhật ký, hoặc dữ liệu thời gian thực.

• Truy vấn thường xuyên sử dụng điều kiện lọc (WHERE) trên cột phân vùng: Ví dụ: Bạn thường xuyên truy vấn dữ liệu theo ngày tháng (thời gian hiệu lực, thời gian hết hạn) hoặc theo phân nhóm cụ thể.

  • Partition giúp giảm phạm vi dữ liệu cần xử lý (Partition Elimination).

  Ví dụ: SELECT * FROM InsuranceRecords WHERE ThoiGianHieuLuc BETWEEN '2024-01-01' AND '2024-12-31'; Với Partition, SQL Server chỉ quét các partition chứa dữ liệu phù hợp thay vì toàn bộ bảng.

• Cải thiện hiệu năng bảo trì: Khi bạn cần xóa, thêm, hoặc di chuyển dữ liệu theo từng nhóm.

  • Ví dụ: Bạn muốn xóa dữ liệu cũ hơn 5 năm. Với Partition, bạn có thể dùng lệnh SWITCH hoặc TRUNCATE thay vì xóa từng hàng: ALTER TABLE InsuranceRecords SWITCH PARTITION 1 TO ArchiveTable;

• Yêu cầu phân tán dữ liệu trên nhiều ổ đĩa: Nếu bạn sử dụng nhiều filegroup trên các ổ đĩa vật lý khác nhau để tăng hiệu năng I/O.

Khi nào KHÔNG cần Partition?#

• Dữ liệu nhỏ: Nếu bảng chỉ có vài nghìn đến vài triệu bản ghi và truy vấn không gặp vấn đề về hiệu năng.

  • Partition có thể làm phức tạp hệ thống không cần thiết.

• Truy vấn không tận dụng Partition Elimination: Nếu hầu hết các truy vấn cần quét toàn bộ bảng thay vì một partition cụ thể.

• Không có yêu cầu quản lý dữ liệu phức tạp: Nếu bạn không cần xóa dữ liệu cũ thường xuyên hoặc không có yêu cầu di chuyển dữ liệu.

Quy tắc chung:#

• Dữ liệu trên 10 triệu bản ghi: Cân nhắc Partition nếu bạn gặp vấn đề về hiệu năng.

• Dữ liệu trên 100 triệu bản ghi: Partition thường là cần thiết, đặc biệt khi dữ liệu được truy vấn theo nhóm (ví dụ: theo ngày, tháng, năm).

• Dữ liệu dưới 1 triệu bản ghi: Thường không cần Partition, trừ khi bạn đang chuẩn bị cho việc mở rộng dữ liệu trong tương lai.

Kịch bản cụ thể:#

Với bảng InsuranceRecords trong hình, nếu:

• Dữ liệu của bạn dự kiến sẽ vượt qua 100 triệu hàng (ví dụ: dữ liệu bảo hiểm của nhiều năm), và các truy vấn thường lọc theo ThoiGianHieuLuc hoặc DoanhNghiep, thì Partition sẽ mang lại hiệu quả rõ rệt.

• Nếu dữ liệu nhỏ (vài triệu bản ghi), bạn nên tối ưu hóa bằng chỉ mục thay vì Partition.

Nếu bạn muốn kiểm tra hoặc tối ưu hóa dữ liệu cụ thể của bảng này, hãy chia sẻ thêm chi tiết nhé! 😊