Partitionstabeller för skalning

  • 13 minuter

Partitioneringsbeslut är nästan permanenta. En dåligt vald partitionsnyckel gör prestandan sämre än en opartitionerad tabell, och om du partitionerar en tabell med flera terabyte måste du återskapa den helt med timmar av stilleståndstid. Partitionsnyckeln och indexjusteringen som du väljer under designen avgör om partitionering förbättrar systemet eller skapar underhållsmardrömmar som du inte enkelt kan ångra.

Tabellpartitionering delar upp stora tabeller i mindre, mer hanterbara delar (partitioner) samtidigt som de bevaras som en enda logisk tabell. Programmet ser en tabell, men databasmotorn hanterar flera fysiska segment som kan underhållas, arkiveras eller frågas oberoende av varandra.

Förstå partitioneringsbegrepp

Partitionering omfattar flera nyckelkomponenter: en partitionsfunktion som definierar hur data delas upp, ett partitionsschema som mappar partitioner till filgrupper och partitioneringskolumnen som avgör vilken partition varje rad tillhör. Genom att förstå dessa begrepp kan du utforma en effektiv partitioneringsstrategi.

Utvärdera prestanda och driftfördelar

Partitionering ger förbättringar av frågeprestanda genom partitionseliminering där frågor filtrerar efter partitionsnyckelåtkomst endast relevanta partitioner (en månad i stället för 120 månader), parallell bearbetning där flera partitioner bearbetas samtidigt över CPU-kärnor, snabbare statistik beräknad per partition i stället för hela tabellen och index söker där mindre partitioner innebär grundare B-träd.

Driftfördelarna omfattar detaljerat underhåll där du återskapar index på den aktuella partitionen medan äldre partitioner förblir online, snabb arkivering genom att byta gamla partitioner till arkivtabeller på några sekunder genom metadataåtgärder, förbättrad tillgänglighet från att underhålla partitioner oberoende av varandra och nivåindelad lagring genom att flytta äldre partitioner till billigare och långsammare lagring.

Anta till exempel att ett företag för finansiella tjänster med en transaktionstabell på 1,2 TB där frågor som filtreras efter datum (90% frågor) genomsöker hela tabellen. När du har implementerat månatlig partitionering förbättras frågeprestanda 10–20 gånger genom partitionseliminering, indexombyggnaderna går från 6 timmar till 20 minuter per partition, arkivering av gamla data minskar från fyra timmars lås till sekunder med partitionsväxling och lagringskostnaderna minskar med 40% genom att flytta äldre partitioner till billigare lagring.

Förstå när du ska använda partitionering

Följande tabell visar när partitionering hjälper jämfört med när den lägger till onödig komplexitet:

Scenario Vill du använda partitionering? Varför
Frågor filtrerar på en specifik kolumn (datum, region) 80%+ av tiden Ja Partitionseliminering får endast åtkomst till relevanta partitioner
Regelbunden arkivering av gamla data (månadsvis, kvartalsvis) Ja Byt ut partitioner på några sekunder i stället för DELETE åtgärder
Behöver bara återskapa index på senaste data Ja Återskapa den aktuella partitionen medan äldre partitioner är online
Stora tabeller (flera TB) med nivåindelade lagringsbehov Ja Flytta äldre partitioner till billigare lagring
De flesta frågor söker igenom hela tabellen eller filtrerar på olika kolumner Nej. Alla partitioner genomsöks – sämre prestanda än opartitionerad
Sökningar med en rad eller genomsökningar med små intervall är vanliga Nej. Partitionering lägger till omkostnader utan fördelar
Ingen tydlig kolumn överensstämmer med frågemönster Nej. Det går inte att välja en effektiv partitionsnyckel

Skapa partitioneringskomponenter

I följande exempel visas de tre komponenterna: partitionsfunktion, partitionsschema och partitionerad tabell:

-- 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);

I det här exemplet skapas kvartalspartitioner för en ordertabell . Partitionsfunktionen definierar fyra gränsvärden (januari, april, juli, oktober) som skapar fem partitioner: en för data före 2024 och fyra för varje kvartal 2024. Partitionsschemat mappar alla partitioner till DEN PRIMÄRA filgruppen. Tabellen Beställningar använder kolumnen OrderDate som partitionsnyckel, som måste ingå i primärnyckeln för korrekt indexjustering.

Välj partitioneringsstrategier

Partitionsnyckeln är ditt viktigaste beslut. Välj dåligt, och partitionering gör mer ont än hjälper. Den idealiska partitionsnyckeln WHERE visas i satsen för de flesta frågor, skapar rimligt balanserade partitioner och överensstämmer med dina underhållsmönster.

Följande kriterier för val av nyckel hjälper dig att välja rätt partitionsnyckel:

  • Frågemönster: 80%+ av frågor filtrerar efter den här kolumnen
  • Datadistribution: Jämn distribution mellan partitioner (ingen enskild partition med 90% data)
  • Underhållsjustering: Matchar arkiverings-/rensningsmönster (datumkolumner för tidsbaserad arkivering)
  • Stabilitet: Värdet ändras inte efter INSERT (undvik partitionering på uppdateringsbara kolumner)

Förstå intervallpartitionering

Intervallpartitionering delar upp data baserat på värdeintervall – oftast datum. Varje partition innehåller ett visst intervall (januaridata, februaridata osv.). Detta är den mest använda strategin.

Här fungerar intervallpartitionering bäst:

  • Tidsseriedata (beställningar, loggar, transaktioner)
  • Sekventiella data (fakturanummer, order-ID: er)
  • Numeriska intervall (löneband, prisnivåer)

I följande tabell visas vanliga partitionsmönster:

Mönster När du ska använda
Varje dag System med stora volymer, kort kvarhållning
Weekly Medelhög volym, kvarhållning mellan 6 och 12 månader
Månadsvis Vanligast är att balansera partitionsantal och storlek
Kvartalsvis Lägre volym, kvarhållning under flera år
Årligen Arkivscenarier, långsiktiga historiska data

Till exempel kan en e-handelsplattform som partitionerar beställningar varje månad möjliggöra att aktuella förfrågningar för månaden når en partition, kvartalsrapporter kan komma åt 3 partitioner, och årsanalys kan använda 12 partitioner samtidigt som äldre års partitioner exkluderas.

Du kan skapa intervallpartitioner genom att definiera gränser i partitionsfunktionen:

-- 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);

Partitionera efter kategoriska värden

Du kan använda RANGE partitionering med strängvärden eller kategoriska värden som regioner. Partitionsfunktionen placerar värden baserat på sorteringsordning. Den här metoden fungerar för geografisk distribution, system med flera klientorganisationer eller avdelningsdata där frågor ofta filtreras efter kategori.

I följande exempel partitioneras data efter region:

-- 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);

Implementera partitionering av index

När du partitionerar en tabell kan index vara justerade eller icke-justerade. Justerade index använder samma partitionsschema som tabellen, medan icke-justerade index använder annat partitionsschema eller ingen partitionering. Som standard ärver icke-grupperade index på partitionerade tabeller tabellens partitionsschema.

Förstå justerade jämfört med ojusterade index

Alignerade index använder samma partitionsfunktion som tabellen. Varje indexpartition matchar en tabellpartition, vilket möjliggör snabb partitionsväxling, förenklat underhåll och bättre partitionseliminering.

Icke-justerade index använder antingen olika partitionering eller ingen partitionering alls. De kan inte använda partitionsväxling och stöds inte i tabeller med fler än 1 000 partitioner.

Använd justerade index när du behöver partitionsväxling för arkivering, vill återskapa specifika partitioner oberoende av varandra eller när frågemönster filtrerar på partitionsnyckeln.

Tänk dig till exempel en ordertabell som partitionerats av OrderDate med ett icke-grupperat index i CustomerID. Genom att använda justerad partitionering med samma OrderDate-schema kan du arkivera gamla månader genom att växla partitioner, återskapa aktuella index oberoende av varandra och ta bort gamla partitioner utan att påverka hela tabellen.

Du kan skapa partitionerade index med samma partitionsschema som bastabellen:

-- 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);

Hantera partitionsåtgärder

När du har skapat partitionerade tabeller måste du hantera dem över tid. Vanliga åtgärder är att fråga efter partitionsmetadata, lägga till nya partitioner när data växer och ta bort gamla partitioner under arkivering. Dessa åtgärder använder $PARTITION-funktionen och ALTER PARTITION FUNCTION-instruktionen.

Information om sökfrågepartition

Du kan visa partitionsinformation med hjälp $PARTITION av funktionen . Här är ett exempel:

-- 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;

Lägg till ny partitionsgräns

Du kan dela partitioner för att lägga till nya gränsvärden med hjälp ALTER PARTITION FUNCTIONav . Här är ett exempel:

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

Den här instruktionen lägger till ett nytt gränsvärde (1 november 2024) till partitionsfunktionen och delar upp en befintlig partition i två partitioner. Partitionen som innehåller datum från oktober till december blir nu två partitioner: en för oktober och en annan för november till december.

Arkivera och ta bort partition

Du kan slå samman partitioner för att arkivera gamla data med hjälp av tillsammans med . Här är ett exempel:

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

Tillämpa metodtips för partitionering

Följande metodtips hjälper dig att undvika vanliga partitioneringsmisstag som är svåra att korrigera efter implementeringen:

  • Justera index med tabellpartitioner: Använd samma partitionsschema för tabeller och index för att aktivera partitionsväxling och underhåll
  • Övervaka datadistribution: Kontrollera partitionsstatistik regelbundet för att identifiera obalanserade partitioner och verifiera partitionseliminering
  • Automatisera partitionshantering: Schemalägg jobb för att lägga till nya partitioner innan du når gränser och arkiverar gamla partitioner
  • Undvik överpartitionering: Rikta in sig på miljontals rader per partition, inte tusentals – för många partitioner skapar omkostnader
  • Inkludera partitionsnyckel i primärnyckel: Krävs för klustrad indexjustering i partitionsschemat

Partitionering kräver noggrann planering. Den partitionsnyckel och indexjustering du väljer avgör om du får prestanda eller skapar komplexitet. När partitioneringen implementeras korrekt omvandlas stor tabellhantering genom snabbare frågor, effektiv arkivering och förenklat underhåll.