Táblák particionálása dedikált SQL-készletben
Javaslatok és példák a táblapartíciók dedikált SQL-készletben való használatára.
Mik azok a táblapartíciók?
A táblapartíciók lehetővé teszik az adatok kisebb adatcsoportokra való felosztását. A legtöbb esetben a táblapartíciók egy dátumoszlopban jönnek létre. A particionálás minden dedikált SQL-készlettábla-típus esetében támogatott; beleértve a fürtözött oszlopcentrikus adattárat, a fürtözött indexet és a halommemória értékét. A particionálás minden terjesztési típus esetében támogatott, beleértve a kivonatot és a ciklikus időszeletelést is.
A particionálás előnyös lehet az adatkarbantartás és a lekérdezési teljesítmény szempontjából. Az, hogy mindkettő előnyös-e, vagy csak egy, attól függ, hogy az adatok hogyan töltődnek-e be, és hogy ugyanaz az oszlop használható-e mindkét célra, mivel a particionálás csak egy oszlopon végezhető el.
A betöltendő előnyök
A dedikált SQL-készlet particionálásának elsődleges előnye az adatok betöltése hatékonyságának és teljesítményének javítása partíciótörlés, váltás és egyesítés használatával. A legtöbb esetben az adatok particionálása egy olyan dátumoszlopon történik, amely szorosan kapcsolódik ahhoz a sorrendhez, amelyben az adatok betöltődnek az SQL-készletbe. A partíciók adatok karbantartására való használatának egyik legnagyobb előnye a tranzakciónaplózás elkerülése. Bár az adatok egyszerű beszúrása, frissítése vagy törlése lehet a legegyszerűbb módszer, kis gondolkodással és erőfeszítéssel a particionálás használata a betöltési folyamat során jelentősen javíthatja a teljesítményt.
A partícióváltással gyorsan eltávolíthat vagy lecserélhet egy táblaszakaszt. Előfordulhat például, hogy egy értékesítési ténytábla csak az elmúlt 36 hónap adatait tartalmazza. Minden hónap végén az értékesítési adatok legrégebbi hónapja törlődik a táblából. Ezek az adatok törölhetők egy törlési utasítással a legrégebbi hónap adatainak törléséhez.
A nagy mennyiségű adat törlési utasítással történő törlése azonban túl sok időt vehet igénybe, valamint olyan nagy tranzakciók kockázatát is okozhatja, amelyek hiba esetén hosszú időt vesznek igénybe a visszaállításhoz. Optimálisabb módszer az adatok legrégebbi partíciójának elvetése. Ahol az egyes sorok törlése több órát is igénybe vehet, a teljes partíció törlése másodperceket vehet igénybe.
A lekérdezések előnyei
A particionálás a lekérdezési teljesítmény javítására is használható. Egy olyan lekérdezés, amely szűrőt alkalmaz a particionált adatokra, csak a megfelelő partíciókra korlátozhatja a vizsgálatot. Ez a szűrési módszer elkerülheti a teljes táblavizsgálatot, és csak az adatok egy kisebb részhalmazát vizsgálhatja. A fürtözött oszlopcentrikus indexek bevezetésével a predikátumeliminációs teljesítmény előnyei kevésbé előnyösek, de bizonyos esetekben előnyös lehet a lekérdezések számára.
Ha például az értékesítési ténytábla 36 hónapra van particionálva az értékesítési dátum mező használatával, akkor az értékesítési dátumra szűrő lekérdezések kihagyhatják a szűrőnek nem megfelelő partíciók keresését.
Partícióméretezés
Bár a particionálás egyes forgatókönyvek teljesítményének javítására használható, a túl sok partícióval rendelkező tábla létrehozása bizonyos körülmények között ronthatja a teljesítményt. Ezek az aggodalmak különösen a fürtözött oszlopcentrikus táblákra érvényesek.
Ahhoz, hogy a particionálás hasznos legyen, fontos tisztában lenni azzal, hogy mikor érdemes particionálást használni, és hogy hány partíciót kell létrehozni. Nincs nehéz, gyors szabály arról, hogy hány partíció túl sok, az adatoktól és az egyidejűleg betöltött partíciók számától függ. A sikeres particionálási sémák általában több tíz-száz partícióval, nem ezresekkel vannak elszenvedve.
A fürtözött oszlopcentrikus táblák partícióinak létrehozásakor fontos figyelembe venni, hogy hány sor tartozik az egyes partíciókhoz. A fürtözött oszlopcentrikus táblák optimális tömörítéséhez és teljesítményéhez eloszlásonként és partíciónként legalább 1 millió sorra van szükség. A partíciók létrehozása előtt a dedikált SQL-készlet már 60 eloszlásra osztja az egyes táblákat.
A táblákhoz hozzáadott particionálások a színfalak mögött létrehozott disztribúciókon kívül vannak. Ebben a példában, ha az értékesítési ténytábla 36 havi partíciót tartalmazott, és mivel egy dedikált SQL-készlet 60 disztribúcióval rendelkezik, akkor az értékesítési ténytáblának havonta 60 millió sort, azaz 2,1 milliárd sort kell tartalmaznia, ha minden hónap ki van töltve. Ha egy tábla kevesebb sort tartalmaz, mint a javasolt minimális számú sor partíciónként, érdemes lehet kevesebb partíciót használni a partíciónkénti sorok számának növelése érdekében.
További információt az Indexelés című cikkben talál, amely olyan lekérdezéseket tartalmaz, amelyek képesek felmérni a fürt oszlopcentrikus indexeinek minőségét.
A SQL Server szintaktikai eltérései
A dedikált SQL-készlet a SQL Server-nél egyszerűbb partíciók meghatározásának módját mutatja be. A particionálási függvények és sémák nem használhatók dedikált SQL-készletben, mivel azok SQL Server vannak. Ehelyett mindössze azonosítania kell a particionált oszlopot és a határpontokat.
Bár a particionálás szintaxisa kissé eltérhet a SQL Server, az alapvető fogalmak megegyeznek. SQL Server és dedikált SQL-készlet táblánként egy partícióoszlopot támogat, amely lehet tartományos partíció. További információ a particionálásról: Particionált táblák és indexek.
Az alábbi példa a CREATE TABLE utasítás használatával particionálja a FactInternetSales táblát az OrderDateKey oszlopban:
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101,20020101
,20030101,20040101,20050101
)
)
);
Partíciók migrálása SQL Server
SQL Server partíciódefiníciók dedikált SQL-készletbe való migrálásához egyszerűen:
- Távolítsa el a SQL Server partíciósémát.
- Adja hozzá a partíciófüggvény definícióját a CREATE TABLE táblához.
Ha particionált táblát migrál egy SQL Server-példányból, az alábbi SQL segíthet az egyes partíciók sorainak számának megállapításában. Ne feledje, hogy ha ugyanazt a particionálási részletességet használja a dedikált SQL-készletben, a partíciónkénti sorok száma 60-zal csökken.
-- Partition information for a SQL Server Database
SELECT s.[name] AS [schema_name]
, t.[name] AS [table_name]
, i.[name] AS [index_name]
, p.[partition_number] AS [partition_number]
, SUM(a.[used_pages]*8.0) AS [partition_size_kb]
, SUM(a.[used_pages]*8.0)/1024 AS [partition_size_mb]
, SUM(a.[used_pages]*8.0)/1048576 AS [partition_size_gb]
, p.[rows] AS [partition_row_count]
, rv.[value] AS [partition_boundary_value]
, p.[data_compression_desc] AS [partition_compression_desc]
FROM sys.schemas s
JOIN sys.tables t ON t.[schema_id] = s.[schema_id]
JOIN sys.partitions p ON p.[object_id] = t.[object_id]
JOIN sys.allocation_units a ON a.[container_id] = p.[partition_id]
JOIN sys.indexes i ON i.[object_id] = p.[object_id]
AND i.[index_id] = p.[index_id]
JOIN sys.data_spaces ds ON ds.[data_space_id] = i.[data_space_id]
LEFT JOIN sys.partition_schemes ps ON ps.[data_space_id] = ds.[data_space_id]
LEFT JOIN sys.partition_functions pf ON pf.[function_id] = ps.[function_id]
LEFT JOIN sys.partition_range_values rv ON rv.[function_id] = pf.[function_id]
AND rv.[boundary_id] = p.[partition_number]
WHERE p.[index_id] <=1
GROUP BY s.[name]
, t.[name]
, i.[name]
, p.[partition_number]
, p.[rows]
, rv.[value]
, p.[data_compression_desc];
Partícióváltás
A dedikált SQL-készlet támogatja a partíciófelosztást, az egyesítést és a váltást. A függvények mindegyike az ALTER TABLE utasítással lesz végrehajtva.
Ha partíciókat szeretne váltani két tábla között, meg kell győződnie arról, hogy a partíciók igazodnak a saját határaikhoz, és hogy a tábladefiníciók megegyeznek. Mivel a tábla értéktartományának kényszerítéséhez nem érhetők el ellenőrzési korlátozások, a forrástáblának ugyanazokat a partícióhatárokat kell tartalmaznia, mint a céltáblának. Ha a partíció határai nem egyeznek meg, akkor a partíciókapcsoló meghiúsul, mivel a partíció metaadatai nem lesznek szinkronizálva.
A partíció felosztásához a megfelelő partíciónak (nem feltétlenül a teljes táblának) üresnek kell lennie, ha a tábla fürtözött oszlopcentrikus indexet (CCI) tartalmaz. Az ugyanabban a táblában lévő más partíciók tartalmazhatnak adatokat. Az adatokat tartalmazó partíciók nem oszthatók fel, ezért a következő hibaüzenet jelenik meg: ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete. Az adatokat tartalmazó partíciók felosztásának kerülő megoldásaként tekintse meg az adatokat tartalmazó partíció felosztását ismertető cikket.
Adatokat tartalmazó partíció felosztása
A már adatokat tartalmazó partíciók felosztásának leghatékonyabb módja egy CTAS utasítás használata. Ha a particionált tábla fürtözött oszlopcentrikus, akkor a táblapartíciónak üresnek kell lennie, mielőtt felosztható lenne.
Az alábbi példa egy particionált oszlopcentrikus táblát hoz létre. Minden partícióba egy sort szúr be:
CREATE TABLE [dbo].[FactInternetSales]
(
[ProductKey] int NOT NULL
, [OrderDateKey] int NOT NULL
, [CustomerKey] int NOT NULL
, [PromotionKey] int NOT NULL
, [SalesOrderNumber] nvarchar(20) NOT NULL
, [OrderQuantity] smallint NOT NULL
, [UnitPrice] money NOT NULL
, [SalesAmount] money NOT NULL
)
WITH
( CLUSTERED COLUMNSTORE INDEX
, DISTRIBUTION = HASH([ProductKey])
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
);
INSERT INTO dbo.FactInternetSales
VALUES (1,19990101,1,1,1,1,1,1);
INSERT INTO dbo.FactInternetSales
VALUES (1,20000101,1,1,1,1,1,1);
A következő lekérdezés a katalógusnézettel keresi meg a sys.partitions sorok számát:
SELECT QUOTENAME(s.[name])+'.'+QUOTENAME(t.[name]) as Table_name
, i.[name] as Index_name
, p.partition_number as Partition_nmbr
, p.[rows] as Row_count
, p.[data_compression_desc] as Data_Compression_desc
FROM sys.partitions p
JOIN sys.tables t ON p.[object_id] = t.[object_id]
JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
JOIN sys.indexes i ON p.[object_id] = i.[object_Id]
AND p.[index_Id] = i.[index_Id]
WHERE t.[name] = 'FactInternetSales';
A következő felosztási parancs hibaüzenetet kap:
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Msg 35346, Level 15, State 1, Line 44
SPLIT clause of ALTER PARTITION statement failed because the partition is not empty. Only empty partitions can be split in when a columnstore index exists on the table. Consider disabling the columnstore index before issuing the ALTER PARTITION statement, then rebuilding the columnstore index after ALTER PARTITION is complete.
CTAS Az adatok tárolására azonban létrehozhat egy új táblát.
CREATE TABLE dbo.FactInternetSales_20000101
WITH ( DISTRIBUTION = HASH(ProductKey)
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101
)
)
)
AS
SELECT *
FROM FactInternetSales
WHERE 1=2;
A partícióhatárok igazítása esetén engedélyezve van egy kapcsoló. Ezzel a forrástáblát üres partícióval hagyja el, amelyet később feloszthat.
ALTER TABLE FactInternetSales SWITCH PARTITION 2 TO FactInternetSales_20000101 PARTITION 2;
ALTER TABLE FactInternetSales SPLIT RANGE (20010101);
Mindössze annyit kell hagynia, hogy az adatokat az új partícióhatárokhoz igazítja a paranccsal CTAS, majd visszakapcsolhatja az adatokat a főtáblába.
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales_20000101]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101;
ALTER TABLE dbo.FactInternetSales_20000101_20010101 SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2;
Miután elvégezte az adatok áthelyezését, érdemes frissíteni a céltábla statisztikáit. A statisztikák frissítése biztosítja, hogy a statisztikák pontosan tükrözzék az adatok új eloszlását a saját partícióikon.
UPDATE STATISTICS [dbo].[FactInternetSales];
Végül az adatok áthelyezésére szolgáló egyszeri partíciókapcsoló esetén elvetheti a partíciókapcsolóhoz létrehozott táblákat, FactInternetSales_20000101_20010101 és FactInternetSales_20000101a következőt: . Másik lehetőségként érdemes lehet üres táblákat tartani a normál, automatizált partíciókapcsolókhoz.
Új adatok betöltése egy lépésben adatokat tartalmazó partíciókba
Az adatok partíciókba való betöltése partícióváltással kényelmes módszer az új adatok a felhasználók számára nem látható táblákba való előkészítésére. Az elfoglalt rendszerek esetében kihívást jelenthet a partícióváltáshoz kapcsolódó zárolási versengés kezelése.
Egy partíció ALTER TABLE meglévő adatainak törléséhez az adatok kiváltásához használt adatokra volt szükség. Ezután egy másikra ALTER TABLE volt szükség az új adatok közötti váltáshoz.
A dedikált SQL-készletben a TRUNCATE_TARGET beállítás támogatott a ALTER TABLE parancsban. A TRUNCATE_TARGET paranccsal felülírhatja a ALTER TABLE partíció meglévő adatait új adatokkal. Az alábbi példa egy új tábla meglévő adatokkal való létrehozására, új adatok beszúrására, majd az összes adat céltáblába való visszaállítására, a meglévő adatok felülírására használható CTAS .
CREATE TABLE [dbo].[FactInternetSales_NewSales]
WITH ( DISTRIBUTION = HASH([ProductKey])
, CLUSTERED COLUMNSTORE INDEX
, PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES
(20000101,20010101
)
)
)
AS
SELECT *
FROM [dbo].[FactInternetSales]
WHERE [OrderDateKey] >= 20000101
AND [OrderDateKey] < 20010101
;
INSERT INTO dbo.FactInternetSales_NewSales
VALUES (1,20000101,2,2,2,2,2,2);
ALTER TABLE dbo.FactInternetSales_NewSales SWITCH PARTITION 2 TO dbo.FactInternetSales PARTITION 2 WITH (TRUNCATE_TARGET = ON);
Táblaparticionálási forrásvezérlő
Megjegyzés
Ha a forrásvezérlő eszköz nincs úgy konfigurálva, hogy figyelmen kívül hagyja a partíciós sémákat, a tábla sémájának módosítása a partíciók frissítéséhez egy tábla elvetését és újbóli létrehozását okozhatja az üzembe helyezés részeként, ami elérhetetlen lehet. Szükség lehet egy egyéni megoldásra egy ilyen módosítás implementálásához az alábbiakban leírtak szerint. Ellenőrizze, hogy a folyamatos integrációs/folyamatos üzembe helyezési (CI/CD) eszköz lehetővé teszi-e ezt. Az SQL Server Data Tools (SSDT) területen keresse meg a Speciális közzétételi beállítások "Partíciós sémák figyelmen kívül hagyása" lehetőséget, hogy elkerülje a létrehozott szkripteket, amelyek egy tábla elvetését és újbóli létrehozását okozzák.
Ez a példa egy üres tábla partíciósémáinak frissítésekor hasznos. A partícióváltozások adatokkal rendelkező táblán történő folyamatos üzembe helyezéséhez kövesse az Adatokat tartalmazó partíció felosztása az üzembe helyezés mellett című cikk lépéseit, hogy ideiglenesen áthelyezhesse az adatokat az egyes partíciókból a partíció FELOSZTÁSI TARTOMÁNYA alkalmazása előtt. Erre azért van szükség, mert a CI/CD eszköz nem tudja, hogy mely partíciók rendelkeznek adatokkal.
Ha el szeretné kerülni, hogy a tábladefiníció rozsdásodjon a verziókövetési rendszerben, érdemes megfontolnia a következő megközelítést:
A tábla létrehozása particionált táblaként, de partícióértékek nélkül
CREATE TABLE [dbo].[FactInternetSales] ( [ProductKey] int NOT NULL , [OrderDateKey] int NOT NULL , [CustomerKey] int NOT NULL , [PromotionKey] int NOT NULL , [SalesOrderNumber] nvarchar(20) NOT NULL , [OrderQuantity] smallint NOT NULL , [UnitPrice] money NOT NULL , [SalesAmount] money NOT NULL ) WITH ( CLUSTERED COLUMNSTORE INDEX , DISTRIBUTION = HASH([ProductKey]) , PARTITION ( [OrderDateKey] RANGE RIGHT FOR VALUES () ) );SPLITa tábla az üzembe helyezési folyamat részeként:-- Create a table containing the partition boundaries CREATE TABLE #partitions WITH ( LOCATION = USER_DB , DISTRIBUTION = HASH(ptn_no) ) AS SELECT ptn_no , ROW_NUMBER() OVER (ORDER BY (ptn_no)) as seq_no FROM ( SELECT CAST(20000101 AS INT) ptn_no UNION ALL SELECT CAST(20010101 AS INT) UNION ALL SELECT CAST(20020101 AS INT) UNION ALL SELECT CAST(20030101 AS INT) UNION ALL SELECT CAST(20040101 AS INT) ) a; -- Iterate over the partition boundaries and split the table DECLARE @c INT = (SELECT COUNT(*) FROM #partitions) , @i INT = 1 --iterator for while loop , @q NVARCHAR(4000) --query , @p NVARCHAR(20) = N'' --partition_number , @s NVARCHAR(128) = N'dbo' --schema , @t NVARCHAR(128) = N'FactInternetSales' --table; WHILE @i <= @c BEGIN SET @p = (SELECT ptn_no FROM #partitions WHERE seq_no = @i); SET @q = (SELECT N'ALTER TABLE '+@s+N'.'+@t+N' SPLIT RANGE ('+@p+N');'); -- PRINT @q; EXECUTE sp_executesql @q; SET @i+=1; END -- Code clean-up DROP TABLE #partitions;
Ezzel a megközelítéssel a forrásvezérlőben lévő kód statikus marad, és a particionálási határértékek dinamikusak lehetnek; idővel fejlődik az SQL-készlettel.
Következő lépések
A táblák fejlesztésével kapcsolatos további információkért tekintse meg a Táblázat áttekintése című cikket.
Visszajelzés
Hamarosan elérhető: 2024-ben fokozatosan kivezetjük a GitHub-problémákat a tartalom visszajelzési mechanizmusaként, és lecseréljük egy új visszajelzési rendszerre. További információ: https://aka.ms/ContentUserFeedback.
Visszajelzés küldése és megtekintése a következőhöz: