Partisjonstabeller for skala

13 minutter

Delingsbeslutninger er nesten permanente. En dårlig valgt partisjonsnøkkel gjør ytelsen dårligere enn en upartisjonert tabell, og å repartisjonere en multi-terabyte tabell krever at den bygges helt på nytt med timer med nedetid. Partisjonsnøkkelen og indeksjusteringen du velger under designet avgjør om partisjonering forbedrer systemet ditt eller skaper vedlikeholdsmareritt du ikke enkelt kan angre.

Tabellpartisjonering deler store tabeller inn i mindre, mer håndterbare deler (partisjoner) samtidig som de holdes som én logisk tabell. Applikasjonen din ser én tabell, men databasemotoren håndterer flere fysiske segmenter som kan vedlikeholdes uavhengig, arkiveres eller forespørres.

Forstå partisjoneringskonsepter

Partisjonering involverer flere nøkkelkomponenter: en partisjonsfunksjon som definerer hvordan data deles, et partisjonsskjema som kartlegger partisjoner til filgrupper, og partisjoneringskolonnen som bestemmer hvilken partisjon hver rad tilhører. Å forstå disse konseptene hjelper deg å utforme en effektiv oppdelingsstrategi.

Evaluere ytelse og driftsmessige fordeler

Partisjonering gir forbedringer i spørringsytelsen gjennom partisjonseliminering der spørringer filtrerer etter partisjon. Nøkkeltilgang kun relevante partisjoner (én måned i stedet for 120 måneder), parallell prosessering der flere partisjoner behandles samtidig på tvers av CPU-kjerner, raskere statistikk beregnet per partisjon i stedet for hele tabellen, og indekssøk der mindre partisjoner betyr grunnere B-trær.

Driftsfordeler inkluderer granulært vedlikehold hvor du bygger opp indekser på den nåværende partisjonen mens eldre partisjoner forblir online, rask arkivering ved å bytte gamle partisjoner til arkivtabeller på sekunder gjennom metadataoperasjoner, bedre tilgjengelighet ved å vedlikeholde partisjoner uavhengig, og lagdelt lagring ved å flytte eldre partisjoner til billigere, tregere lagring.

For eksempel, tenk deg et finansielt tjenesteselskap med en transaksjonstabell på 1,2 TB hvor spørringer filtrert etter dato (90% av spørringer) skanner hele tabellen. Etter å ha implementert månedlig partisjonering, forbedres spørringsytelsen 10–20 ganger gjennom partisjonseliminering, indeksombygging går fra 6 timer til 20 minutter per partisjon, arkivering av gamle data reduseres fra fire timers låser til sekunder ved partisjonsbytte, og lagringskostnadene reduseres med 40% ved å flytte eldre partisjoner til billigere lagring.

Forstå når du skal bruke partisjonering

Tabellen nedenfor viser når partisjonering hjelper versus når det legger til unødvendig kompleksitet:

Scenario	Bruke partisjonering?	Hvorfor
Spørringer filtreres på en spesifikk kolonne (dato, region) 80%+ av tiden	Ja	Partisjonseliminering får kun tilgang til relevante partisjoner
Regelmessig arkivering av gamle data (månedlig, kvartalsvis)	Ja	Bytt partisjoner ut på sekunder i stedet for `DELETE` operasjoner
Trenger å bygge opp indekser kun basert på nylige data	Ja	Bygg opp nåværende partisjon på nytt mens eldre partisjoner forblir online
Store bord (multi-TB) med behov for trinnvis lagring	Ja	Flytt eldre partisjoner til billigere lagring
De fleste spørringer skanner hele tabellen eller filteret på ulike kolonner	Nei	Alle partisjoner skannet—dårligere ytelse enn upartisjonert
Enkeltradsoppslag eller skanninger med små rekkevidder er vanlige	Nei	Partisjonering legger til overhead uten fordeler
Ingen klar kolonne samsvarer med spørringsmønstre	Nei	Kan ikke velge en effektiv partisjonsnøkkel

Lag partisjoneringskomponenter

Følgende eksempel viser de tre komponentene: partisjonsfunksjon, partisjonsskjema og oppdelt 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);

Dette eksempelet lager kvartalsvise partisjoner for en Ordretabell . Partisjonsfunksjonen definerer fire grenseverdier (januar, april, juli, oktober) og skaper fem partisjoner: én for data før 2024, og fire for hvert kvartal i 2024. Partisjonsskjemaet kartlegger alle partisjoner til PRIMARY-filgruppen. Orders-tabellen bruker kolonnen OrderDate som partisjonsnøkkel, som må inkluderes i primærnøkkelen for korrekt indeksjustering.

Velg partisjoneringsstrategier

Partisjonsnøkkelen er ditt viktigste valg. Velg dårlig, og deling skader mer enn det hjelper. Den ideelle partisjonsnøkkelen vises i klausulen WHERE i de fleste spørringer, skaper rimelig balanserte partisjoner og samsvarer med vedlikeholdsmønstrene dine.

Følgende nøkkelutvelgelseskriterier hjelper deg å velge riktig partisjonsnøkkel:

Spørringsmønstre: 80%+ av spørringer filtreres etter denne kolonnen
Datafordeling: Jevn fordeling på tvers av partisjoner (ingen enkelt partisjon med 90% data)
Vedlikeholdsjustering: Matcher arkiv-/rensingsmønstre (datokolonner for tidsbasert arkivering)
Stabilitet: Verdien endres ikke etter INSERT (unngå partisjonering på oppdaterbare kolonner)

Forstå avstandspartisjonering

Intervalloppdeling deler data basert på verdiintervaller—oftest datoer. Hver partisjon inneholder et spesifikt område (januardata, februardata, osv.). Dette er den mest brukte strategien.

Her fungerer range-partitionering best:

Tidsseriedata (ordre, logger, transaksjoner)
Sekvensielle data (fakturanumre, ordre-ID-er)
Numeriske intervaller (lønnsbånd, prisnivåer)

Følgende tabell viser vanlige partisjonsmønstre:

Mønster	Når bør man bruke
Daglig	Systemer med høyt volum, kort opphold
Ukentlig	Middels volum, 6-12 måneders retensjon
Månedlig	Vanligst er balanser på antall partisjoner og størrelse
Kvartalsvise	Lavere volum, flerårig beholdning
Årlig	Arkivscenarier, langsiktige historiske data

For eksempel gjør en e-handelsplattform som partisjonerer ordre månedlig at nåværende månedssøk treffer én partisjon, kvartalsrapporter får tilgang til 3 partisjoner, og årsanalyse bruker 12 partisjoner samtidig som eldre år elimineres.

Du kan lage intervallpartisjoner ved å definere grenser i partisjonsfunksjonen:

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

Oppdeling etter kategoriske verdier

Du kan bruke RANGE partisjonering med streng- eller kategoriverdier som regioner. Partisjonsfunksjonen plasserer verdier basert på sorteringsrekkefølge. Denne tilnærmingen fungerer for geografisk distribusjon, multitenant-systemer eller avdelingsdata hvor spørringer ofte filtreres etter kategori.

Følgende eksempel deler data etter 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);

Implementer indekspartisjonering

Når du partisjonerer en tabell, kan indekser være justert eller ikke-justert. Justerte indekser bruker samme partisjonsskjema som tabellen, mens ikke-justerte indekser bruker annen partisjonering eller ingen partisjonering. Som standard arver ikke-klyngede indekser på partisjonerte tabeller tabellens partisjonsskjema.

Forstå justerte versus ikke-justerte indekser

Alignerte indekser bruker samme partisjonsfunksjon som tabellen. Hver indekspartisjon matcher en tabellpartisjon, noe som muliggjør rask partisjonsbytte, enklere vedlikehold og bedre eliminering av partisjoner.

Ikke-justerte indekser bruker annen partisjonering eller ingen partisjonering. De kan ikke bruke partisjonsbytte og støttes ikke på tabeller med mer enn 1 000 partisjoner.

Bruk justerte indekser når du trenger partisjonsbytte for arkivering, vil bygge opp spesifikke partisjoner uavhengig, eller når spørringsmønstre filtrerer på partisjonsnøkkelen.

For eksempel, tenk deg en ordretabell delt opp av OrderDate med en ikke-klynget indeks på CustomerID. Ved å bruke justert partisjonering med samme OrderDate-skjema kan man arkivere gamle måneder ved å bytte partisjoner, bygge opp nåværende indekser uavhengig og fjerne gamle partisjoner uten å påvirke hele tabellen.

Du kan lage partisjonerte indekser ved å bruke samme partisjonsskjema som basistabellen:

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

Administrer partisjonsoperasjoner

Etter å ha laget partisjonerte tabeller, må du administrere dem over tid. Vanlige operasjoner inkluderer å spørre partisjonsmetadata, legge til nye partisjoner etter hvert som data vokser, og fjerne gamle partisjoner under arkivering. Disse operasjonene bruker $PARTITION funksjonen og ALTER PARTITION FUNCTION setningen.

Spør informasjon om partisjoner

Du kan se partisjonsinformasjon ved å bruke funksjonen $PARTITION . Her er et eksempel:

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

Legg til ny partisjonsgrense

Du kan splitte partisjoner for å legge til nye grenseverdier ved å bruke ALTER PARTITION FUNCTION. Her er et eksempel:

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

Denne setningen legger til en ny grenseverdi (1. november 2024) til partisjonsfunksjonen, og deler en eksisterende partisjon i to partisjoner. Delingen som inneholder datoer fra oktober til desember blir nå to delinger: en for oktober og en annen for november til desember.

Arkiver og fjern partisjon

Du kan slå sammen partisjoner for å arkivere gamle data ved å bruke ALTER PARTITION FUNCTION med MERGE RANGE. Her er et eksempel:

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

Bruk beste praksis for partisjonering

Følgende beste praksis hjelper deg å unngå vanlige partisjoneringsfeil som er vanskelige å rette etter implementering:

Juster indekser med tabellpartisjoner: Bruk samme partisjonsskjema for tabeller og indekser for å muliggjøre partisjonsbytte og vedlikehold
Overvåk datadistribusjon: Sjekk partisjonsstatistikk regelmessig for å identifisere ubalanserte partisjoner og verifisere partisjonseliminering
Automatiser partisjonshåndtering: Planlegg jobber for å legge til nye partisjoner før de når grenser og arkiver gamle partisjoner
Unngå over-partisjonering: Sikt på millioner av rader per partisjon, ikke tusenvis – for mange partisjoner skaper overhead
Inkluder partisjonsnøkkel i primærnøkkelen: Kreves for klynget indeksjustering på partisjonsskjemaet

Oppdeling krever nøye planlegging. Partisjonsnøkkelen og indeksjusteringen du velger avgjør om du får ytelse eller skaper kompleksitet. Når det implementeres riktig, transformerer partisjonering håndtering av store tabeller gjennom raskere spørringer, effektiv arkivering og forenklet vedlikehold.

Tilbakemeldinger

Var denne siden nyttig?