ตารางพาร์ติชันสําหรับมาตราส่วน

การตัดสินใจแบ่งพาร์ติชันเกือบจะถาวร คีย์พาร์ติชันที่เลือกไม่ดีทําให้ประสิทธิภาพแย่กว่าตารางที่ไม่ได้แบ่งพาร์ติชัน และการแบ่งพาร์ติชันตารางหลายเทราไบต์ใหม่จําเป็นต้องสร้างใหม่ทั้งหมดด้วยการหยุดทํางานหลายชั่วโมง คีย์พาร์ติชันและการจัดตําแหน่งดัชนีที่คุณเลือกระหว่างการออกแบบจะกําหนดว่าการแบ่งพาร์ติชันจะปรับปรุงระบบของคุณหรือสร้างฝันร้ายในการบํารุงรักษาที่คุณไม่สามารถเลิกทําได้อย่างง่ายดาย

การแบ่งพาร์ติชันตารางจะแบ่งตารางขนาดใหญ่ออกเป็นส่วนเล็ก ๆ ที่จัดการได้มากขึ้น (พาร์ติชัน) ในขณะที่เก็บไว้เป็นตารางตรรกะเดียว แอปพลิเคชันของคุณเห็นตารางเดียว แต่กลไกฐานข้อมูลจะจัดการเซ็กเมนต์ทางกายภาพหลายเซ็กเมนต์ที่สามารถบํารุงรักษา เก็บถาวร หรือสืบค้นได้อย่างอิสระ

ทําความเข้าใจแนวคิดการแบ่งพาร์ติชัน

การแบ่งพาร์ติชันเกี่ยวข้องกับองค์ประกอบหลักหลายประการ: ฟังก์ชันพาร์ติชัน ที่กําหนดวิธีการแบ่งข้อมูล โครงร่างพาร์ติชัน ที่แมปพาร์ติชันกับกลุ่มไฟล์และ คอลัมน์การแบ่งพาร์ติชัน ที่กําหนดว่าแต่ละแถวเป็นของพาร์ติชันใด การทําความเข้าใจแนวคิดเหล่านี้จะช่วยให้คุณออกแบบกลยุทธ์การแบ่งพาร์ติชันที่มีประสิทธิภาพ

ประเมินผลการดําเนินงานและผลประโยชน์ในการดําเนินงาน

การแบ่งพาร์ติชันให้การปรับปรุงประสิทธิภาพการสืบค้นผ่านการกําจัดพาร์ติชันที่การสืบค้นกรองตามการเข้าถึงคีย์พาร์ติชันเฉพาะพาร์ติชันที่เกี่ยวข้อง (หนึ่งเดือนแทนที่จะเป็น 120 เดือน) การประมวลผลแบบขนานที่หลายพาร์ติชันถูกประมวลผลพร้อมกันในคอร์ CPU สถิติที่เร็วขึ้นคํานวณต่อพาร์ติชันแทนที่จะเป็นทั้งตารางและดัชนีค้นหาที่พาร์ติชันที่เล็กกว่าหมายถึงต้นไม้ B ที่ตื้นกว่า

ประโยชน์ในการดําเนินงาน ได้แก่ การบํารุงรักษาแบบละเอียดซึ่งคุณสร้างดัชนีใหม่บนพาร์ติชันปัจจุบันในขณะที่พาร์ติชันเก่ายังคงออนไลน์การเก็บถาวรที่รวดเร็วโดยการเปลี่ยนพาร์ติชันเก่าเป็นตารางเก็บถาวรในไม่กี่วินาทีผ่านการดําเนินการข้อมูลเมตาความพร้อมใช้งานที่ดีขึ้นจากการบํารุงรักษาพาร์ติชันอย่างอิสระและที่เก็บข้อมูลแบบแบ่งชั้นโดยการย้ายพาร์ติชันเก่าไปยังที่เก็บข้อมูลที่ถูกกว่าและช้าลง

ตัวอย่างเช่น ลองนึกภาพบริษัทที่ให้บริการทางการเงินที่มีตารางธุรกรรมขนาด 1.2 TB ที่การกรองแบบสอบถามตามวันที่ (90% ของแบบสอบถาม) จะสแกนทั้งตาราง หลังจากใช้การแบ่งพาร์ติชันรายเดือนประสิทธิภาพการสืบค้นจะดีขึ้น 10-20x ผ่านการกําจัดพาร์ติชันการสร้างดัชนีใหม่จาก 6 ชั่วโมงเป็น 20 นาทีต่อพาร์ติชันการเก็บถาวรข้อมูลเก่าจะลดลงจากการล็อกสี่ชั่วโมงเป็นวินาทีโดยใช้การสลับพาร์ติชันและต้นทุนการจัดเก็บลดลง 40% โดยการย้ายพาร์ติชันเก่าไปยังที่เก็บข้อมูลที่ถูกกว่า

ทําความเข้าใจว่าเมื่อใดควรใช้การแบ่งพาร์ติชัน

ตารางต่อไปนี้แสดงให้เห็นว่าการแบ่งพาร์ติชันช่วยได้เมื่อใดเมื่อเทียบกับการเพิ่มความซับซ้อนที่ไม่จําเป็น:

สร้างส่วนประกอบการแบ่งพาร์ติชัน

ตัวอย่างต่อไปนี้แสดงส่วนประกอบสามส่วน: ฟังก์ชันพาร์ติชัน โครงร่างพาร์ติชัน และตารางพาร์ติชัน:

-- Create partition function based on date ranges
-- Use RANGE RIGHT for datetime columns to keep same-day values together
CREATE PARTITION FUNCTION PF_OrderDate (DATETIME2)
    AS RANGE RIGHT FOR VALUES 
    ('2024-01-01', '2024-04-01', '2024-07-01', '2024-10-01');

-- Create partition scheme mapping to a single filegroup (recommended)
-- Use multiple filegroups only for tiered storage or independent backups
CREATE PARTITION SCHEME PS_OrderDate
    AS PARTITION PF_OrderDate ALL TO ([PRIMARY]);

-- Create partitioned table
-- Include partition column in primary key for clustered index alignment
CREATE TABLE Orders (
    OrderID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    CustomerID INT,
    Amount DECIMAL(10,2),
    CONSTRAINT PK_Orders PRIMARY KEY (OrderID, OrderDate)
) ON PS_OrderDate(OrderDate);

ตัวอย่างนี้สร้างพาร์ติชันรายไตรมาสสําหรับตาราง ใบสั่งซื้อ ฟังก์ชันพาร์ติชันกําหนดค่าขอบเขตสี่ค่า (มกราคม เมษายน กรกฎาคม ตุลาคม) สร้างห้าพาร์ติชัน: หนึ่งพาร์ติชันสําหรับข้อมูลก่อนปี 2024 และสี่พาร์ติชันสําหรับแต่ละไตรมาสของปี 2024 โครงร่างพาร์ติชันแมปพาร์ติชันทั้งหมดกับกลุ่มไฟล์ PRIMARY ตาราง Orders ใช้คอลัมน์ OrderDate เป็นคีย์พาร์ติชัน ซึ่งต้องรวมอยู่ในคีย์หลักสําหรับการจัดตําแหน่งดัชนีที่เหมาะสม

เลือกกลยุทธ์การแบ่งพาร์ติชัน

คีย์พาร์ติชันเป็นการตัดสินใจที่สําคัญที่สุดของคุณ เลือกไม่ดีและการแบ่งพาร์ติชันเจ็บมากกว่าช่วย คีย์พาร์ติชันในอุดมคติจะปรากฏในส่วน WHERE คําสั่งของคิวรีส่วนใหญ่สร้างพาร์ติชันที่สมดุลอย่างสมเหตุสมผลและสอดคล้องกับรูปแบบการบํารุงรักษาของคุณ

เกณฑ์การเลือกคีย์ต่อไปนี้ช่วยให้คุณเลือกคีย์พาร์ติชันที่เหมาะสม:

• รูปแบบคิวรี: 80%+ ของคิวรีกรองตามคอลัมน์นี้
• การกระจายข้อมูล: การกระจายที่สม่ําเสมอข้ามพาร์ติชัน (ไม่มีพาร์ติชันเดียวที่มีข้อมูล 90%)
• การจัดตําแหน่งการบํารุงรักษา: ตรงกับรูปแบบการเก็บถาวร/การล้างข้อมูล (คอลัมน์วันที่สําหรับการเก็บถาวรตามเวลา)
• ความเสถียร: ค่าไม่เปลี่ยนแปลงหลังจากแทรก (หลีกเลี่ยงการแบ่งพาร์ติชันบนคอลัมน์ที่ปรับปรุงได้)

ทําความเข้าใจการแบ่งช่วง

การแบ่งช่วงจะแบ่งข้อมูลตามช่วงค่า ซึ่งส่วนใหญ่เป็นวันที่ แต่ละพาร์ติชันมีช่วงเฉพาะ (ข้อมูลเดือนมกราคมข้อมูลเดือนกุมภาพันธ์ ฯลฯ ) นี่เป็นกลยุทธ์ที่ใช้กันอย่างแพร่หลายมากที่สุด

นี่คือจุดที่การแบ่งพาร์ติชันช่วงทํางานได้ดีที่สุด:

• ข้อมูลอนุกรมเวลา (คําสั่งซื้อ บันทึก ธุรกรรม)
• ข้อมูลตามลําดับ (หมายเลขใบแจ้งหนี้ รหัสใบสั่ง)
• ช่วงตัวเลข (แถบเงินเดือน ระดับราคา)

ตารางต่อไปนี้แสดงรูปแบบพาร์ติชันทั่วไป:

ตัวอย่างเช่น แพลตฟอร์มอีคอมเมิร์ซที่แบ่งพาร์ติชันคําสั่งซื้อรายเดือนช่วยให้การสืบค้นเดือนปัจจุบันเข้าถึงหนึ่งพาร์ติชัน รายงานรายไตรมาสเพื่อเข้าถึง 3 พาร์ติชัน และการวิเคราะห์สิ้นปีเพื่อใช้ 12 พาร์ติชันในขณะที่กําจัดปีเก่า

คุณสามารถสร้างพาร์ติชันช่วงได้โดยการกําหนดขอบเขตในฟังก์ชันพาร์ติชัน:

-- RANGE RIGHT creates 5 partitions: <100000, 100000-199999, 200000-299999, 300000-399999, >=400000
CREATE PARTITION FUNCTION PF_InvoiceNumber (INT)
    AS RANGE RIGHT FOR VALUES 
    (100000, 200000, 300000, 400000);

พาร์ติชันตามค่าตามหมวดหมู่

คุณสามารถใช้ RANGE การแบ่งพาร์ติชันด้วยสตริงหรือค่าตามหมวดหมู่ เช่น ภูมิภาค ฟังก์ชันพาร์ติชันจะวางค่าตามลําดับการจัดเรียง วิธีการนี้ใช้ได้กับการกระจายทางภูมิศาสตร์ ระบบหลายผู้เช่า หรือข้อมูลแผนกที่คิวรีมักจะกรองตามประเภท

ตัวอย่างต่อไปนี้แบ่งพาร์ติชันข้อมูลตามภูมิภาค:

-- Partition by region
CREATE PARTITION FUNCTION PF_Region (NVARCHAR(50))
    AS RANGE LEFT FOR VALUES ('East', 'North', 'South', 'West');

CREATE PARTITION SCHEME PS_Region
    AS PARTITION PF_Region ALL TO ([PRIMARY]);

CREATE TABLE RegionalData (
    DataID INT NOT NULL,
    Region NVARCHAR(50) NOT NULL,
    Value DECIMAL(10,2),
    CONSTRAINT PK_RegionalData PRIMARY KEY (DataID, Region)
) ON PS_Region(Region);

ใช้การแบ่งพาร์ติชันดัชนี

เมื่อคุณแบ่งพาร์ติชันตาราง ดัชนีสามารถจัดแนวหรือไม่จัดแนวได้ ดัชนีที่จัดแนวจะใช้โครงร่างพาร์ติชันเดียวกันกับตาราง ในขณะที่ดัชนีที่ไม่จัดแนวจะใช้การแบ่งพาร์ติชันที่แตกต่างกันหรือไม่มีการแบ่งพาร์ติชัน ตามค่าเริ่มต้น ดัชนีที่ไม่ใช่คลัสเตอร์บนตารางที่แบ่งพาร์ติชันจะสืบทอดโครงร่างพาร์ติชันของตาราง

ทําความเข้าใจดัชนีที่สอดคล้องกันและไม่สอดคล้องกัน

ดัชนีที่จัดแนว จะใช้ฟังก์ชันพาร์ติชันเดียวกันกับตาราง พาร์ติชันดัชนีแต่ละพาร์ติชันตรงกับพาร์ติชันตารางทําให้สามารถสลับพาร์ติชันได้อย่างรวดเร็วการบํารุงรักษาที่ง่ายขึ้นและการกําจัดพาร์ติชันที่ดีขึ้น

ดัชนีที่ไม่จัดแนว ใช้การแบ่งพาร์ติชันที่แตกต่างกันหรือไม่มีการแบ่งพาร์ติชัน ไม่สามารถใช้การสลับพาร์ติชันและไม่ได้รับการสนับสนุนบนตารางที่มีพาร์ติชันมากกว่า 1,000 พาร์ติชัน

ใช้ดัชนีที่จัดแนวเมื่อคุณต้องการสลับพาร์ติชันสําหรับการเก็บถาวร ต้องการสร้างพาร์ติชันเฉพาะใหม่อย่างอิสระ หรือเมื่อรูปแบบคิวรีกรองบนคีย์พาร์ติชัน

ตัวอย่างเช่น ลองนึกภาพตาราง Orders ที่แบ่งพาร์ติชันโดย OrderDate ที่มีดัชนีที่ไม่ใช่คลัสเตอร์บน CustomerID การใช้การแบ่งพาร์ติชันที่สอดคล้องกับโครงร่าง OrderDate เดียวกันช่วยให้สามารถเก็บถาวรเดือนเก่าได้โดยการสลับพาร์ติชันสร้างดัชนีปัจจุบันใหม่อย่างอิสระและวางพาร์ติชันเก่าโดยไม่ส่งผลกระทบต่อทั้งตาราง

คุณสามารถสร้างดัชนีที่แบ่งพาร์ติชันโดยใช้โครงร่างพาร์ติชันเดียวกันกับตารางฐาน:

-- Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX IX_Orders_Customer
    ON Orders(CustomerID)
    ON PS_OrderDate(OrderDate);

-- Create partitioned columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_SalesData_CS
    ON SalesData(Revenue, Region)
    ON PS_SalesDate(SaleDate);

จัดการการทํางานของพาร์ติชัน

หลังจากสร้างตารางที่แบ่งพาร์ติชันแล้วคุณต้องจัดการเมื่อเวลาผ่านไป การดําเนินการทั่วไป ได้แก่ การสืบค้นข้อมูลเมตาของพาร์ติชันการเพิ่มพาร์ติชันใหม่เมื่อข้อมูลเติบโตและการลบพาร์ติชันเก่าระหว่างการเก็บถาวร การดําเนินการเหล่านี้ใช้ $PARTITION ฟังก์ชันและ ALTER PARTITION FUNCTION คําสั่ง

ข้อมูลพาร์ติชันแบบสอบถาม

คุณสามารถดูข้อมูลพาร์ติชันได้โดยใช้ $PARTITION ฟังก์ชัน ตัวอย่างมีดังนี้:

-- View partition information
SELECT 
    $PARTITION.PF_OrderDate(OrderDate) AS PartitionNumber,
    MIN(OrderDate) AS MinDate,
    MAX(OrderDate) AS MaxDate,
    COUNT(*) AS RowCount
FROM Orders
GROUP BY $PARTITION.PF_OrderDate(OrderDate)
ORDER BY PartitionNumber;

เพิ่มขอบเขตพาร์ติชันใหม่

คุณสามารถแยกพาร์ติชันเพื่อเพิ่มค่าขอบเขตใหม่ได้โดยใช้ ALTER PARTITION FUNCTION. ตัวอย่างมีดังนี้:

-- Split partition to add new boundary
ALTER PARTITION FUNCTION PF_OrderDate()
    SPLIT RANGE ('2024-11-01');

คําสั่งนี้เพิ่มค่าขอบเขตใหม่ (1 พฤศจิกายน 2024) ให้กับฟังก์ชันพาร์ติชัน โดยแยกพาร์ติชันที่มีอยู่ออกเป็นสองพาร์ติชัน พาร์ติชันที่มีวันที่ตั้งแต่เดือนตุลาคมถึงธันวาคมจะกลายเป็นสองพาร์ติชัน: พาร์ติชันหนึ่งสําหรับเดือนตุลาคมและอีกพาร์ติชันหนึ่งสําหรับเดือนพฤศจิกายนถึงธันวาคม

เก็บถาวรและลบพาร์ติชัน

คุณสามารถรวมพาร์ติชันเพื่อเก็บถาวรข้อมูลเก่าได้โดยใช้ ALTER PARTITION FUNCTIONMERGE RANGE. ตัวอย่างมีดังนี้:

-- Merge partitions to archive old data
ALTER PARTITION FUNCTION PF_OrderDate()
    MERGE RANGE ('2023-12-31');

ใช้แนวทางปฏิบัติที่ดีที่สุดสําหรับการแบ่งพาร์ติชัน

การปฏิบัติตามแนวทางปฏิบัติที่ดีที่สุดช่วยให้คุณหลีกเลี่ยงข้อผิดพลาดทั่วไปในการแบ่งพาร์ติชันที่ยากต่อการแก้ไขหลังจากการใช้งาน:

• จัดแนวดัชนีด้วยพาร์ติชันตาราง: ใช้โครงร่างพาร์ติชันเดียวกันสําหรับตารางและดัชนีเพื่อเปิดใช้งานการสลับและบํารุงรักษาพาร์ติชัน
• ตรวจสอบการกระจายข้อมูล: ตรวจสอบสถิติพาร์ติชันเป็นประจําเพื่อระบุพาร์ติชันที่ไม่สมดุลและตรวจสอบการกําจัดพาร์ติชัน
• การจัดการพาร์ติชันอัตโนมัติ: กําหนดเวลางานเพื่อเพิ่มพาร์ติชันใหม่ก่อนที่จะถึงขอบเขตและเก็บถาวรพาร์ติชันเก่า
• หลีกเลี่ยงการแบ่งพาร์ติชันมากเกินไป: กําหนดเป้าหมายหลายล้านแถวต่อพาร์ติชัน ไม่ใช่หลายพันพาร์ติชันสร้างค่าใช้จ่าย
• รวมคีย์พาร์ติชันในคีย์หลัก: จําเป็นสําหรับการจัดตําแหน่งดัชนีแบบคลัสเตอร์บนโครงร่างพาร์ติชัน

การแบ่งพาร์ติชันต้องมีการวางแผนอย่างรอบคอบ คีย์พาร์ติชันและการจัดตําแหน่งดัชนีที่คุณเลือกจะเป็นตัวกําหนดว่าคุณได้รับประสิทธิภาพหรือสร้างความซับซ้อน เมื่อนําไปใช้อย่างถูกต้องการแบ่งพาร์ติชันจะเปลี่ยนการจัดการตารางขนาดใหญ่ผ่านการสืบค้นที่เร็วขึ้นการเก็บถาวรที่มีประสิทธิภาพและการบํารุงรักษาที่ง่ายขึ้น