Létrehozhat particionált táblát vagy indexet az SQL Server 2016-ban az SQL Server Management Studio vagy a Transact-SQL használatával. A particionált táblákban és indexekben lévő adatok vízszintesen blokkokra vannak osztva, amelyek az adatbázisban több fájlcsoport között szétoszthatók. A particionálás javíthatja a nagy táblák és indexek kezelhetőségét és méretezhetőségét.

Vagy az index általában négy lépésből áll:

Hozzon létre egy fájlcsoportot vagy fájlcsoportokat és a megfelelő fájlokat, amelyek partíciókat tartalmaznak a particionálási séma szerint.

Hozzon létre egy partíciós függvényt, amely egy adott oszlop elemértékei alapján leképezi a tábla- vagy indexsorokat partíciókra.

Hozzon létre egy partíciós sémát, amely leképezi a particionált tábla vagy index partícióit új fájlcsoportokra.

Hozzon létre vagy módosítson egy táblát vagy indexet, és adjon meg egy partíciós sémát tárolási helyként.

Ebben a részben

Mielőtt elkezdené, hajtsa végre a következő lépéseket.

Korlátozások

Biztonság

Hozzon létre egy particionált táblát vagy indexet a következő eszközökkel:

SQL Server Management Studio

Korlátozások

Egy függvény és partíciós séma hatóköre arra az adatbázisra korlátozódik, amelyben létrehozták. A particionáló függvények az adatbázison belüli többi függvénytől külön névtérben találhatók.

Ha a partíciós függvény bármely sorában null partíciós oszlop található, akkor ezek a sorok a bal szélső partícióba kerülnek. Ha azonban a null értéket adjuk meg határértékként, és a RIGHT opciót adjuk meg, a bal szélső partíció üresen marad, és a null értékek a második partícióba kerülnek.

Biztonság

Engedélyek

A particionált tábla létrehozásához CREATE TABLE engedély szükséges az adatbázisban és ALTER engedély a sémán, amelyben a tábla létrejön. A particionált index létrehozásához ALTER jogosultság szükséges ahhoz a táblához vagy nézethez, amelyen az index készül. Particionált tábla vagy index létrehozásához a következő további engedélyek bármelyike ​​szükséges:

BÁRMELY ADATTERÜLET-engedélyt MÓDOSÍTS. Ez az engedély alapértelmezés szerint a rögzített kiszolgálói szerepkör tagjaihoz van hozzárendelve sysadminés előre meghatározott adatbázis-szerepek db_tulajdonosÉs db_ddladmin.

CONTROL vagy ALTER jogosultság azon az adatbázison, amelyben a függvény és a partíciós séma létrejön.

CONTROL SERVER vagy BÁRMELY ADATBÁZIS engedély MÓDOSÍTÁSA azon az adatbázis-kiszolgálón, amelyen a függvény és a partíciós séma létrejött.

teljes lépésről lépésre utasításokat Ebben az eljárásban egy vagy több fájlcsoportot, megfelelő fájlokat és táblázatot hozhat létre. A következő példában ezekre az objektumokra hivatkozunk a particionált tábla létrehozásakor.

Új fájlcsoportok létrehozása particionált táblához

Hozzon létre egy particionált táblát

A particionáláshoz kattintson a jobb gombbal a táblára, és válassza ki Tárolásés kattintson Szakasz létrehozása...

BAN BEN Szakaszvarázsló Az oldalon Üdvözöljük a Szakaszvarázslóban kattintson További.

Az oldalon Partícióoszlop kiválasztása a rácsban válassza ki azt az oszlopot, amellyel a táblát particionálni kívánja. a rácsban Elérhető particionáló oszlopok csak a particionálható adattípusú oszlopok jelennek meg. Ha egy számított oszlopot választ ki particionáló oszlopként, akkor azt állandóvá kell tennie.

A particionáló oszlop és az értéktartomány kiválasztását elsősorban az határozza meg, hogy milyen mértékben kell az adatokat logikailag csoportosítani. Például az adatokat logikai csoportokra oszthatja az év hónapja vagy negyedéve szerint. Az ütemezett adatlekérdezések határozzák meg, hogy az ilyen logikai csoportosítás megfelelő-e a táblapartíciók kezeléséhez. Bármilyen típusú adat használható particionáló oszlopként, kivéve szöveg, ntext, kép, xml, időbélyeg, varchar (max), nvarchar(max), varbináris(max), adattípus-álnevek és CLR felhasználó által definiált adattípusok.

A tábla elhelyezése a kiválasztott particionált táblával
Lehetővé teszi, hogy kiválasszon egy particionált táblát, amely kapcsolódó adatokat tartalmaz, és ezzel a táblával particionálással csatlakozik. A particionált táblák, amelyeket particionáló oszlopok egyesítenek, általában hatékonyabbak a lekérdezésekben.

Tárolás igazítása a nem egyedi és egyedi indexek indexelt particionáló oszloppal
Igazítja az összes indexet egy táblán, amely ugyanazzal a sémával particionálva van. Egy tábla és indexei összehangolásával a partíciók hatékonyabban mozgathatók a particionált táblákba és onnan, mivel az adatok particionálása ugyanazzal az algoritmussal történik.

A particionáló oszlop és a többi oszlop kiválasztása után kattintson a gombra További.

Az oldalon Partícionálási funkció kiválasztása fejezetben Válassza ki a szakaszolási funkciót kattintson a vagy gombra. Választáskor Hozzon létre egy szakaszoló függvényt adjon meg egy függvénynevet. Ha az opció ki van választva Meglévő funkció szakaszolás, majd válassza ki a listából a particionáláshoz használt függvény nevét. Vegye figyelembe, hogy ha nincs más particionáló függvény az adatbázisban, a Meglévő particionálási funkció elérhetetlen lesz.

További.

Az oldalon Particionálási séma kiválasztása fejezetben Válasszon particionálási sémát kattintson a vagy gombra. Választáskor Hozzon létre partíciós sémátírjon be egy sémanevet. Ha az opció ki van választva Meglévő particionálási séma, majd válassza ki a listából a használni kívánt sémanevet. Ha nincs más partíciós séma az adatbázisban, a paraméter Meglévő particionálási séma elérhetetlen lesz.

Az oldal kitöltése után kattintson a gombra További.

Az oldalon Partícióleképezés fejezetben Hatótávolság válassza ki Bal szegély vagy Jobb szegély az összes létrehozott fájlcsoportba beillesztendő legnagyobb vagy legkisebb határérték kiválasztásához. A partíciók létrehozásakor határértékként megadott fájlcsoportok számán kívül mindig meg kell adni egy további fájlcsoportot.

a rácsban Fájlcsoportok kiválasztása és határértékek megadása mezőben Fájlcsoport válassza ki a fájlcsoportot, amelybe az adatokat particionálja. fejezetben Határ minden fájlcsoporthoz adjon meg egy határértéket. Ha nincs megadva határérték, a partíciófüggvény a teljes táblát vagy indexet egyetlen partícióra képezi le a partíciófüggvény nevével.

Ezen az oldalon a következő további lehetőségek állnak rendelkezésre:

Állíts fel határokat...
Párbeszédpanel megnyitása Határértékek beállítása, ahol kiválaszthat töréspontokat és dátumtartományokat a partíciókhoz. Ez a lehetőség csak akkor érhető el, ha olyan particionáló oszlopot választ ki, amely a következő adattípusok egyikét tartalmazza: dátum, dátum idő, kis randevú, dátum és idő 2 vagy datetime offset.

Tárolási besorolás
A szükséges sorok számának becslése és elérhető tér a partíciókhoz megadott minden fájlcsoport tárolására. Ezek az értékek csak olvashatók a rácsban.

A párbeszédpanelen Határértékek beállítása A következő további beállításokat állíthatja be:

kezdő dátum
Válassza ki a partíciótartomány értékeinek kezdő dátumát.

lejárati dátum
Válassza ki a partíciótartomány értékeinek befejezési dátumát. Választáskor Bal szegély Az oldalon Partícióleképezés ez a dátum lesz az utolsó érték minden fájlcsoportnál és partíciónál. Választáskor Jobb szegély Az oldalon Partícióleképezés ez a dátum lesz az utolsó előtti első érték fájlcsoport.

időintervallum
Válassza ki a dátum-részletezettséget vagy a tartományérték lépését az egyes szakaszokhoz.

Az oldal kitöltése után kattintson a gombra További.

Az oldalon Kimeneti paraméter kiválasztása adja meg, hogyan kell feltölteni a particionált táblát. Válassza ki Szkript létrehozása SQL-szkript létrehozásához a varázsló előző oldalain található adatok alapján. Válassza ki Azonnal fussúj particionált tábla létrehozásához, miután befejezte az összes többi varázslóoldalt. Válassza ki Menetrend egy új particionált tábla előzetes létrehozásához rendelkezésre álló idő a jövőben.

Választáskor Szkript létrehozása V Szkript paraméterei a következő lehetőségek lesznek elérhetőek:

A szkriptet fájlba írja ki
Szkript létrehozása SQL fájlként. Írja be a mezőbe a fájl nevét és helyét Fájl név vagy kattintson Felülvizsgálat a párbeszédpanel megnyitásához Script fájl helye. fejezetben Mentés másként válassza ki Szöveg Unicode-ban vagy ANSI szöveg.

Szkript kimenete a vágólapra
A szkript mentése a vágólapra.

Szkript megjelenítése új lekérdező ablakban
A szkript egy új lekérdezésszerkesztő ablakban jön létre. Ez az opció alapértelmezés szerint ki van választva.

Választáskor Menetrend kattintson Változás ütemezés.

1. A párbeszédpanelen Hozzon létre egy munkabeosztást mezőben Név adjon meg egy nevet a munkabeosztásnak.

   Listázott Ütemezés típusa válassza ki az ütemezés típusát:

   • Automatikus indítás az SQL Server Agent indításakor

     Futtassa, amikor a processzorok tétlenek

     ismétlődő. Válassza ezt a lehetőséget, ha az új particionált tábla rendszeresen frissül új adatokkal.

     egyszer. Ez az opció alapértelmezés szerint ki van választva.

Jelölje be vagy törölje a pipát Beleértve az ütemezés engedélyezéséhez vagy letiltásához.

Választáskor ismétlődő:

1. fejezetben Frekvencia a listán Teljesített adja meg a végrehajtás gyakoriságát:

   • Választáskor Napi mezőben Fut minden Adja meg, hogy a feladat milyen gyakran futjon újra napokon belül.

     Választáskor Heti mezőben Fut minden Adja meg, hogy a munka milyen gyakran kerül átütemezésre hetekben. Válassza ki a hét azon napját vagy napjait, amikor a munkaütemezés fut.

     Választáskor Havi kattintson Nap vagy Határozott.

     • Választáskor Nap adja meg annak a hónapnak a dátumát, amikor a munkaütemezésnek futnia kell, és adja meg, hogy a munkaütemezés hányszor futjon újra hónapokban. Ha például azt szeretné, hogy a munkaütemezés minden második hónap 15-én fusson, válassza a lehetőséget Napés az első mezőbe írja be a „15”-et, a másodikba a „2”-t. Felhívjuk figyelmét, hogy a második mezőben megadott szám nem haladhatja meg a „99”-et.

       Választáskor Határozott válasszon egy adott napot a hétnek abban a hónapban, amelyben futni kívánja a munkaütemezést, és adja meg, hogy a munkaütemezés milyen gyakran futjon újra hónapok múlva. Ha például azt szeretné, hogy a munkaütemezés minden második hónap hét utolsó napján fusson, válassza a lehetőséget Nap, válassza ki utolsó az első listában és munkanap a második listában, majd írja be a „2”-t a második mezőbe. Te is választhatsz első, második, harmadik vagy negyedik, valamint a hét adott napjai (például vasárnap vagy szerda) az első két listában. Kérjük, vegye figyelembe, hogy az utolsó mezőben megadott szám nem haladhatja meg a „99”-et.

A terepen Hányszor egy nap adja meg, hogy milyen gyakran kell újra végrehajtani a munkaütemezést a munkaütemezés megkezdésének napján:

• Választáskor Hajtsa végre egyszer adjon meg egy adott napszakot a munkaütemezés terepen történő futtatásához Hajtsa végre egyszer. Adja meg a napszakot: óra, perc és másodperc.

  Választáskor Fut minden a mezőben adja meg a feladat végrehajtásának gyakoriságát a kiválasztott napon Frekvencia. Ha például azt szeretné, hogy a munkaütemezés 2 óránként fusson a munkaütemezés megkezdésének napján, válassza a lehetőséget Fut minden., írja be a „2”-t az első mezőbe, majd válasszon a listából néz. Ebből a listából is választhat percekÉs másodpercig. Felhívjuk figyelmét, hogy az első mezőben megadott szám nem haladhatja meg a „100-at”.

  A terepen Kezdd adja meg a munkaütemezés indításának időpontját. A terepen vége a adja meg a munka átütemezésének befejezésének idejét. Adja meg a napszakot: óra, perc és másodperc.

fejezetben Időtartam, területen kezdő dátum adja meg a munkaütemezés kezdő dátumát. Válassza ki lejárati dátum vagy Nincs befejezési dátum a feladat ütemezésének befejezési dátumának megadásához. Választáskor lejárati dátum adja meg a munkaütemezés befejezési dátumát.

Érték kiválasztásakor egyszer V Egyszeri végrehajtás mezőben dátum adja meg a munkarend kezdési dátumát. A terepen Idő adja meg a munkarend kezdési időpontját. Adja meg a napszakot: óra, perc és másodperc.

fejezetben Összegzés V Leírás Ellenőrizze, hogy az összes munkaütemezési beállítás helyes-e.

Kattintson a gombra rendben.

Az oldal kitöltése után kattintson a gombra További.

Az oldalon Összefoglaló megtekintése fejezetben A kiválasztott opciók megtekintése Bontsa ki az összes elérhető lehetőséget, hogy megbizonyosodjon arról, hogy az összes szakaszbeállítás helyes. Ha minden beállítás helyes, nyomja meg a gombot Kész.

oldal Teljesítmény A Partíció létrehozása varázsló a Partíció létrehozása varázsló műveleteivel kapcsolatos állapotinformációk nyomon követésére szolgál. A varázslóban kiválasztott műveletektől függően a folyamatoldal egy vagy több műveletet tartalmazhat. A felső mezőben látható a varázsló általános állapota, valamint a kapott állapot-, figyelmeztetés- és hibaüzenetek száma.

Az oldalon Teljesítmény A szakaszkészítő varázsló a következő lehetőségeket kínálja:

Intelligencia
Információ az eseményről, állapotról és a varázsló műveletei eredményeként visszaküldött üzenetekről.

Akció
Meghatározza az egyes műveletek típusát és nevét.

Állapot
Azt jelzi, hogy a varázslóművelet összességében adott értéket Sikeresen vagy Hiba.

Üzenet
Bármilyen hiba vagy figyelmeztető üzenet a folyamatból.

Jelentés
Hozzon létre egy jelentést, amely tartalmazza a Partícióvarázsló eredményeit. Elérhető opciók: Jelentés megtekintése, Jelentés mentése fájlba, És Jelentés küldése e-mailben.

Jelentés megtekintése
Párbeszédpanel megnyitása Jelentés megtekintése, amely szöveges jelentést tartalmaz a partíciók varázsló létrehozásáról.

Jelentés másolása a vágólapra
A varázsló jelentésének eredményeit a vágólapra másolja.

Jelentés küldése e-mailben
A varázsló állapotjelentésének eredményeit egy e-mail üzenetbe másolja.

Ha végzett az opciók kiválasztásával, nyomja meg a gombot Bezárás.

A Partíció létrehozása varázsló létrehoz egy függvényt és egy partíciós sémát, majd alkalmazza a partíciót a megadott táblára. A tábla particionálásának ellenőrzéséhez az Object Explorer alkalmazásban kattintson a jobb gombbal a táblára, és válassza ki Tulajdonságok. Menj az oldalra tárolás. Az oldal információkat jelenít meg, beleértve a partíciófüggvény nevét, a sémát és a partíciók számát.

Hozzon létre egy particionált táblát

BAN BEN objektum böngésző csatlakozzon az adatbázismotor egy példányához.

A szabványos panelen válassza a lehetőséget Kérelem létrehozása.

Másolja a következő példát a lekérdező ablakba, és kattintson a gombra Fuss. A következő példa a fájlcsoportok létrehozását, a funkcionalitást és a particionálási sémákat mutatja be. Egy új tábla jön létre, amikor partíciós sémát ad meg tárolási helyként.

HASZNÁLATA AdventureWorks2012; MEGY -- Négy új fájlcsoportot ad az AdventureWorks2012 adatbázishoz ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test1fg; MEGY ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test2fg; MEGY ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test3fg; MEGY ALTER DATABASE AdventureWorks2012 ADD FILEGROUP test4fg; -- Minden fájlcsoporthoz hozzáad egy fájlt. ALTER DATABASE AdventureWorks2012 ADD FILE(NAME=test1dat1, FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t1dat1.ndf", MÉRET = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) A FÁJLCSOPORTBA test1fg; ALTER DATABASE AdventureWorks2012 ADD FILE(NAME=test2dat2, FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t2dat2.ndf", MÉRET = 5 MB, MAXSIZE = 100 MB, FÁJLGYÁRTÁS = 5 MB) A FILEGROUP teszt2fg; MEGY ALTER DATABASE AdventureWorks2012 ADD FILE(NAME=test3dat3, FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t3dat3.ndf", MÉRET = 5 MB, MAXSIZE = 100 MB, FÁJLGYÁRTÁS = 5 MB) A FÁJLCSOPORTBA test3fg; MEGY ALTER DATABASE AdventureWorks2012 ADD FILE(NAME=test4dat4, FILENAME= "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\t4dat4.ndf", MÉRET = 5 MB, MAXSIZE = 100 MB, FILEGROWTH = 5 MB) A FÁJLGROUPBA test4fg; MEGY -- Létrehoz egy myRangePF1 nevű partíciós függvényt, amely egy táblát négy partícióra particionál PARTÍCIÓ FUNKCIÓ LÉTREHOZÁSA myRangePF1 (int ) AZ ÉRTÉKEKHEZ MÁRTARTOTT TARTOMÁNYKÉNT (1 , 100 , 1000 ); MEGY -- Létrehoz egy myRangePS1 nevű partíciós sémát, amely a myRangePF1-et alkalmazza a fent létrehozott négy fájlcsoportra A myRangePS1 PARTÍCIÓS SÉMA LÉTREHOZÁSA myRangePF1 TO PARTÍCIÓKÉNT (test1fg, test2fg, test3fg, test4fg); MEGY -- Létrehoz egy PartitionTable nevű particionált táblát, amely a myRangePS1-et használja a col1 particionálásához TÁBLÁZAT LÉTREHOZÁSA PartitionTable (col1 int PRIMARY KEY , col2 char (10 )) ON myRangePS1 (col1) ; MEGY

Tábla particionálás meghatározása

A következő lekérdezés egy vagy több sort ad vissza, ha a PartitionTable particionálva van. Ha a tábla nincs particionálva, nem ad vissza sort.

Határértékek meghatározása particionált tábla számára

A következő lekérdezés a PartitionTable minden partíciójának határértékeit adja vissza.

SELECT t .name AS TableName, i .name AS IndexName, p .partition_number, p .partition_id, i .data_space_id, f .function_id, f .type_desc, r.boundary_id, r.value AS BoundaryValue FROM sys .tables AS t JOIN sys .indexes AS i ON t .object_id = i .object_id JOIN sys .partitions AS p ON i .object_id = p .object_id AND i .index_id = p .index_id JOIN sys .partition_schemes AS s s ON_data dataINss_id_id_data . .partition_functions AS f ON s.function_id = f .function_id LEFT JOIN sys .partíció_tartomány_értékei AS r ON f .function_id = r.function_id and r.boundary_id = p .partíció_szám WHERE t .name = "Partíciótábla" ÉS i .type<= 1 ORDER BY p .partition_number;

Partícionált tábla particionáló oszlopának meghatározása

A következő lekérdezés egy tábla particionáló oszlopának nevét adja vissza. PartitionTable.

KIVÁLASZTÁS t. AS ObjectID , t .name AS TableName , ic.column_id AS Partitioning ColumnID , c .name AS PartitioningColumnName FROM sys .tables AS t JOIN sys .indexes AS i ON t . = én . És én .<= 1 -- clustered index or a heap JOIN sys .partition_schemes AS ps ON ps.data_space_id = i .data_space_id JOIN sys .index_columns AS ic ON ic. = i . AND ic.index_id = i .index_id AND ic.partition_ordinal >= 1 -- mert 0 = nem particionáló oszlop JOIN sys .columns AS c ON t . = c. ÉS ic.column_id = c .column_id WHERE t .name = "PartitionTable" ; MEGY

További információkért lásd.

Jó estét/délutánt/reggelt kedves habralyudi! Folytatjuk a blog fejlesztését és kiegészítését kedvenc, nyílt forráskódú rdbms-emről, a Postgresql-ről. Csodával határos módon az történt, hogy a mai téma témája itt soha nem került elő. Azt kell mondanom, hogy a postgresql-ben történő particionálás nagyon jól le van írva a dokumentációban, de ez megállít?).

Bevezetés

Általánosságban elmondható, hogy a particionálás alatt általában nem valamiféle technológiát értünk, hanem sokkal inkább az adatbázis-tervezés olyan megközelítéseként, amely jóval azelőtt jelent meg, hogy a DBMS elkezdte volna támogatni az ún. particionált táblák. Az ötlet nagyon egyszerű - az asztalt több kisebb méretű részre osztani. Két alfaja van - vízszintes és függőleges metszet.

Vízszintes szakaszolás

A táblázat egyes részei a különböző sorokat tartalmazzák. Tegyük fel, hogy van egy absztrakt alkalmazás - LOGS - naplótáblázata. Részekre bonthatjuk – egy a 2009. januári naplókhoz, egy a 2009. februári naplókhoz, és így tovább.

Függőleges szakaszolás

A táblázat egyes részei a különböző oszlopokat tartalmazzák. A függőleges particionálás (ha valóban indokolt) alkalmazását valamivel nehezebb megtalálni, mint a vízszintes particionálást. Szférikus lóként ezt a lehetőséget javaslom megfontolni: a HÍREK táblában vannak azonosító, SHORTTEXT, LONGTEXT oszlopok, és a LONGTEXT mezőt jóval ritkábban használjuk, mint az első kettőt. Ebben az esetben célszerű a HÍREK táblát oszlopokra bontani (két táblát hozzon létre a RÖVIDSZÖVEG és a HOSSZÚSZÖVEG számára, amelyeket elsődleges kulcsok kapcsolnak össze + hozzon létre egy NEWS nézetet, amely mindkét oszlopot tartalmazza). Így amikor csak a hír leírására van szükségünk, a DBMS-nek nem kell a hír teljes szövegét lemezről olvasnia.

Particionálás támogatása a modern DBMS-ben

A legtöbb modern DBMS támogatja a tábla particionálását ilyen vagy olyan formában.

• Jóslat- támogatja a particionálást a 8-as verziótól kezdve. Egyrészt nagyon egyszerű a szekciókkal való munka (egyáltalán nem kell rajtuk gondolkodni, úgy dolgozol, mint egy normál asztalnál*), másrészt viszont minden nagyon rugalmas. A szakaszok "alpartíciókra" oszthatók, törölhetők, feloszthatók, átvihetők. A particionált tábla indexelésének különféle lehetőségei támogatottak (globális index, particionált index). Link a teljes leíráshoz.
• Microsoft SQL Server- nemrég (2005-ben) jelent meg a particionálás támogatása. Használatból az első benyomás az, hogy „Na végre!! :)”, a második pedig: „Működik, úgy tűnik, minden rendben van”. Dokumentáció az msdn-n
• MySQL- támogatja az 5.1-es verzió óta.
• Stb…

*- Hazudok persze, vannak szabványos nehézségek - időben új részt létrehozni, a régit kidobni stb., de valahogy mégis minden egyszerű és világos.

Partícionálás Postgresql-ben

A postgresql-ben lévő particionálási táblák megvalósítása némileg eltér a többi adatbázistól. A particionálás a tábla öröklődésén alapul (ez a postgresql egyedi jellemzője). Vagyis rendelkeznünk kell egy főtáblával (főtáblával), és ennek szakaszai leszármazott táblák lesznek. Megfontoljuk a szakaszolást egy valósághoz közeli feladat példáján.

A probléma megfogalmazása

Az adatbázis az oldal/oldalak látogatóiról szóló adatok gyűjtésére és elemzésére szolgál. Az adatmennyiség elég nagy a particionáláshoz. Az elemzés során a legtöbb esetben az utolsó nap adatait használják fel.
1. Hozza létre a főtáblát:

CREATE TABLE analytics.events

user_id UUID NEM NULL ,
event_type_id SMALLINT NOT NULL ,
event_time TIMESTAMP DEFAULT most() NOT NULL ,
url VARCHAR(1024) NOT NULL ,
hivatkozó VARCHAR(1024),
ip INET NOT NULL
);

2. Naponként az event_time mező szerint fogunk particionálni. Minden naphoz új részt hozunk létre. A szakaszokat a szabály szerint nevezzük el: analytics.events_DDMMYYYY. Íme egy példaszakasz 2010. január 1-jére.

TÁBLÁZAT LÉTREHOZÁSA analytics.events_01012010
event_id BIGINT DEFAULT nextval("analytics.seq_events" ) ELSŐDLEGES KULCS ,
CHECK(esemény_idő >= IDŐBÉLYEG "2010-01-01 00:00:00" ÉS eseményidő< TIMESTAMP "2010-01-02 00:00:00" )
) ÖRÖKSÉGE(analytics.events);

* Ezt a forráskódot a Source Code Highlighter kiemelte.

Szakasz létrehozásakor kifejezetten beállítjuk az event_id mezőt (PRIMARY KEY nem öröklődik), és létrehozunk egy ELLENŐRZÉSI KORLÁTOZÁST az event_time mezőben, hogy ne szúrjunk be túl sokat.

3. Hozzon létre egy indexet az event_time mezőben. Egy tábla particionálásakor feltételezzük, hogy az események táblára vonatkozó lekérdezések többsége az event_time mezőben lévő feltételt fogja használni, így egy index ebben a mezőben sokat segít nekünk.

CREATE INDEX events_01012010_event_time_idx ON analytics.events_01012010 HASZNÁLATA btree(event_time);

* Ezt a forráskódot a Source Code Highlighter kiemelte.

4. Szeretnénk elérni, hogy a főtáblába beillesztve az adatok a neki szánt részbe kerüljenek. Ehhez a következő trükköt tesszük – létrehozunk egy triggert, amely szabályozza az adatfolyamokat.

FUNKCIÓ LÉTREHOZÁSA VAGY CSERÉJE analytics.events_insert_trigger()
VISSZAJÁRA TRIGGERT $$-ként
KEZDŐDIK
IF (ÚJ .event_time >= TIMESTAMP "2010-01-01 00:00:00" ÉS
ÚJ .event_time< TIMESTAMP "2010-01-02 00:00:00" ) THEN
INSERT INTO analytics.events_01012010 ÉRTÉKEK (ÚJ .*);
MÁS
KIVÉTEL EMELÉSE "A dátum % kívül esik a tartományon. Az analytics.events_insert_trigger javítása", NEW .event_time;
VÉGE HA ;
RETURN NULL ;
VÉGE ;
$$
NYELV plpgsql;

* Ezt a forráskódot a Source Code Highlighter kiemelte.

CREATE TRIGGER events_before_insert
ELŐTT BEHELYEZNI AZ analytics.events
MINDEN SOR VÉGREHAJTÁSA analytics.events_insert_trigger();

* Ezt a forráskódot a Source Code Highlighter kiemelte.

5. Minden készen áll, most van egy particionált analytics.events táblázatunk. Elkezdhetjük dühösen elemezni az adatait. A CHECK megszorításokat egyébként nem csak azért hoztuk létre, hogy megvédjük a szakaszokat a helytelen adatoktól. A Postgresql használhatja őket lekérdezési terv összeállításakor (azonban az event_time élő indexnél ez minimális nyereséget ad), elég a constraint_exclusion direktívát használni:

SET constraint_exclusion = be ;
SELECT * FROM analytics.events WHERE eseményidő > CURRENT_DATE ;

* Ezt a forráskódot a Source Code Highlighter kiemelte.

Az első rész vége

Szóval mi van nálunk? Lássuk pontról pontra:
1. Az eseménytáblázat szekciókra bontva egyszerűbbé és gyorsabbá válik az elmúlt nap rendelkezésre álló adatainak elemzése.
2. Az a rémület, amikor ráébredtem, hogy mindezt valahogy támogatni kell, időben szakaszokat létrehozni, nem felejtve el ennek megfelelően megváltoztatni a triggert.

Arról, hogy mennyire könnyű és gondtalan dolgozni particionált táblákkal, a második részben fogom elmondani.

UPD1: A particionálást felváltotta a particionálás
UPD2:
Az egyik olvasó megjegyzése alapján, akinek sajnos nincs fiókja Habrén:
Az öröklődéshez számos tervezési szempont kapcsolódik. A partíciók nem öröklik az elsődleges kulcsot és az idegen kulcsokat az oszlopukban. Ez azt jelenti, hogy partíció létrehozásakor kifejezetten létre kell hozni az ELSŐDLEGES KULCS és az IDEGEN KULCSOT a partíció oszlopaiban. Magamról megjegyzem, hogy egy IDEGEN KULCS létrehozása egy particionált tábla oszlopain nem a legjobb módszer. A legtöbb esetben a particionált tábla egy "ténytábla", és maga is a tábla "dimenziójára" utal.

A nagy táblákon végzett munka során folyamatosan problémákkal találkozunk azok karbantartási, frissítési teljesítményével. A particionálás az egyik legproduktívabb és legkényelmesebb megoldás a felmerülő problémákra.
Általánosságban elmondható, hogy a particionálás egy tábla vagy index blokkokra történő particionálása. A particionálási beállítástól függően a blokkok különböző méretűek lehetnek, és különböző fájlcsoportokban és fájlokban tárolhatók.
A felosztásnak vannak előnyei és hátrányai is.
Az előnyök jól le vannak írva a Microsoft webhelyén, íme egy részlet:

« A nagy táblák vagy indexek particionálása a következő kezelhetőségi és teljesítménybeli előnyöket nyújthatja.

• Ez lehetővé teszi az adatok részhalmazainak gyors és hatékony átvitelét és elérését, miközben megőrzi az adatkészlet integritását. Például egy olyan művelet, mint például az adatok betöltése az OLTP-ről egy OLAP-rendszerbe, másodperceket vesz igénybe, nem pedig perceket és órákat, mint a particionálatlan adatok esetében.
• A karbantartási műveletek gyorsabban végrehajthatók egy vagy több résszel. A műveletek hatékonyabbak, mert csak az adatok részhalmazain hajtják végre őket, nem pedig a teljes táblán. Például tömörítheti az adatokat egy vagy több partícióba, vagy újraépíthet egy vagy több indexpartíciót.
• A hardverkonfiguráción gyakran futtatott lekérdezések alapján javíthatja a lekérdezések teljesítményét. Például a lekérdezésoptimalizáló gyorsabban tud equi-join lekérdezéseket végrehajtani két vagy több particionált tábla között, ha ezek a táblák ugyanazokkal a partíciós oszlopokkal rendelkeznek, mivel maguk a partíciók is összekapcsolhatók.

Az SQL Server I/O-műveleteihez szükséges adatok rendezésekor az adatok először partíció szerint vannak rendezve. Az SQL Server egyszerre csak egy lemezt tud elérni, ami ronthatja a teljesítményt. Az adatok rendezésének felgyorsítása érdekében ajánlatos az adatfájlokat szakaszokra osztani több merevlemezen egy RAID létrehozásával. Így annak ellenére, hogy az adatokat partíció szerint rendezi, az SQL Server egyidejűleg képes lesz elérni az egyes partíciók összes merevlemezét.
A teljesítményt úgy is javíthatja, hogy a teljes tábla helyett partíció szinten alkalmaz zárolásokat. Ez csökkentheti a tábla zárolási ütközésének számát».

A hátrányok közé tartozik a particionált táblák kezelésének és karbantartásának nehézsége.

Nem fogunk foglalkozni a particionálás megvalósításával, mivel ez a probléma nagyon jól le van írva a Microsoft webhelyén.

Ehelyett megpróbálunk módot mutatni a particionált táblák működésének optimalizálására, vagy inkább megmutatjuk az optimális (szerintünk) módot az adatok frissítésére bármely időszakra.

A particionált tábla nagy előnye ezeknek a partícióknak a fizikai szétválasztása. Ez a tulajdonság lehetővé teszi, hogy szakaszokat cseréljünk egymás között vagy bármely más táblával.
Normál adatfrissítéskor, csúszó ablakkal (például egy hónapig), a következő lépéseket kell végrehajtanunk:

1. Keresse meg a szükséges sorokat egy nagy táblázatban;
2. Törölje a talált sorokat a táblázatból és az indexből;
3. Szúrjon be új sorokat a táblázatba, frissítse az indexet.

Amikor sorok milliárdjait tároljuk egy táblázatban, ezek a műveletek meglehetősen hosszú időt vesznek igénybe, de szinte egyetlen műveletre korlátozhatjuk magunkat: egyszerűen cseréljük ki a kívánt részt egy előre elkészített táblázatra (vagy szakaszra). Ebben az esetben nem kell sorokat törölnünk vagy beszúrnunk, és az indexet is frissítenünk kell a teljes nagy táblán.

Térjünk át a szavakról a tettekre, és mutassuk meg, hogyan kell ezt megvalósítani.

1. Először állítson be egy particionált táblát a fent említett cikkben leírtak szerint.
2. Elkészítjük a cseréhez szükséges táblákat.

Az adatok frissítéséhez szükségünk van a céltábla mini másolatára. Ez egy mini-másolat, mert olyan adatokat fog tárolni, amelyeket hozzá kell adni a céltáblához, pl. adatok mindössze 1 hónapig. Szüksége lesz egy harmadik üres táblára is az adatcsere megvalósításához. Miért van rá szükség - később elmagyarázom.

A minipéldányra és a csereasztalra szigorú feltételek vonatkoznak:

• Mindkét táblának léteznie kell a SWITCH utasítás használata előtt. A váltási művelet végrehajtása előtt mind a táblának, ahonnan a partíciót áthelyezik (a forrástábla), mind a partíciót fogadó táblának (a céltábla) léteznie kell az adatbázisban.
• A cél szakasznak léteznie kell, és üresnek kell lennie. Ha egy táblát partícióként adunk hozzá egy már meglévő particionált táblához, vagy ha egy partíciót áthelyezünk egyik particionált táblából a másikba, akkor a célpartíciónak léteznie kell, és üresnek kell lennie.
• A particionálatlan céltáblának léteznie kell, és üresnek kell lennie. Ha a partíció egyetlen nem particionált táblát kíván alkotni, akkor az új partíciót fogadó táblának léteznie kell, és üres, nem particionált táblának kell lennie.
• A szakaszoknak ugyanabból az oszlopból kell származniuk. Ha egy partíciót egyik particionált tábláról egy másikra váltanak, akkor mindkét táblát ugyanabban az oszlopban kell particionálni.
• A forrás- és céltábláknak ugyanabban a fájlcsoportban kell lenniük. Az ALTER TABLE...SWITCH utasítás forrás- és céltábláit ugyanabban a fájlcsoportban kell tárolni, valamint a nagy értékű oszlopokat. Minden releváns indexet, indexpartíciót vagy indexelt partíció nézetet szintén ugyanabban a fájlcsoportban kell tárolni. Ez azonban eltérhet a megfelelő táblák vagy más releváns indexek fájlcsoportjától.

Hadd magyarázzam el a korlátokat a példánkkal:

1. A táblázat bélyegképét ugyanabban az oszlopban kell particionálni, mint a célt. Ha a bélyegkép nem egy particionált tábla, akkor azt ugyanabban a fájlcsoportban kell tárolni, mint a lecserélendő partíciót.

2. A cseretáblának üresnek kell lennie, és ugyanabban az oszlopban kell particionálni, vagy ugyanabban a fájlcsoportban kell tárolni.

3. Megvalósítjuk a cserét.

Most a következőkkel rendelkezünk:
Táblázat minden időre vonatkozó adatokkal (továbbiakban Table_A)
Táblázat 1 hónapra vonatkozó adatokkal (továbbiakban_B táblázat)
Üres táblázat (továbbiakban Table_C)

Először is meg kell találnunk, hogy melyik szekcióban tároljuk az adatokat.
Ezt megtudhatja, ha megkérdezi:

KIVÁLASZTÁS
számít(*) mint
, $PARTÍCIÓ.(dt) as
, rang() felett (sorrend: $PARTITION.(dt))
dbo-tól. (nolock)
csoportosítás $PARTITION szerint.(dt)

Ebben a lekérdezésben olyan szakaszokat kapunk, amelyek információsorokat tartalmaznak. A szám nem számolható – ezt azért tettük, hogy ellenőrizzük az adatcsere szükségességét. A Rank-t azért használjuk, hogy egy ciklusban menjünk, és egy eljárásban több szakaszt frissíthessünk.

Amint megtudtuk, hogy mely szekciókban tároljuk az adatokat, azok felcserélhetők. Tegyük fel, hogy az adatok az 1. partícióban vannak tárolva.

Ezután a következő műveleteket kell végrehajtania:
Cserélje ki a céltábla partícióit a cserélni kívánt táblával.
ALTER TABLE . AZ 1. PARTÍCIÓT VÁLTÁSRA . PARTÍCIÓ 1
Most a következőkkel rendelkezünk:
A céltáblázatban nem maradt adat a számunkra szükséges részben, pl. szakasz üres
Cserélje ki a partíciókat a céltábláról és a miniatűrről
ALTER TABLE . AZ 1. PARTÍCIÓT VÁLTÁSRA . PARTÍCIÓ 1
Most a következőkkel rendelkezünk:
A havi adatok megjelentek a céltáblázatban, és a minipéldány már üres
Táblázat törlése vagy törlése cseréhez.

Ha van egy fürtözött index a táblán, akkor ez sem probléma. Mind a 3, ugyanabban az oszlopban particionált táblában létre kell hozni. Partícióváltáskor az index automatikusan frissül, újraépítés nélkül.

Ebben a cikkben bemutatom a lekérdezés-végrehajtási tervek sajátosságait a particionált táblák elérésekor. Vegye figyelembe, hogy nagy különbség van a particionált táblák (amelyek csak az SQL Server 2005-ben váltak elérhetővé) és a particionált nézetek között (amelyek az SQL Server 2000-ben is elérhetők voltak, és még mindig elérhetők az SQL Server 2005-ben és újabb verziókban). A particionált nézetek lekérdezési terveinek jellemzőit egy másik cikkben mutatom be.

Táblázat nézet

Hozzunk létre egy egyszerű particionált táblát:

hozza létre a pf(int) partíciós függvényt értékek tartományaként (0, 10, 100)

ps partíciós séma létrehozása pf all partícióként a ()

t (a int, b int) tábla létrehozása a ps(a)-n

Ez a szkript létrehoz egy táblát négy partícióval. Az SQL Server értékeket rendelt mind a négy partíció azonosítójához, ahogy az a táblázatban látható:

PtnId	értékeket
1	t.a<= 0
2	0 < t.a <= 10
3	10 < t.a <= 100
4	100 < t.a

Most pedig nézzünk meg egy lekérdezési tervet, amely arra kényszerítené az optimalizálót, hogy táblázatvizsgálatot használjon:

……|–Állandó pásztázás(ÉRTÉKEK:(((1)),(2)),((3)),(4)))
…….|–Table Scan(OBJECT:([t]))

A fenti tervben az SQL Server kifejezetten megadja az összes partícióazonosítót a "Constant Scan" utasításban, amely megvalósítja a táblavizsgálatot, és adatokat szolgáltat a beágyazott hurkok csatlakozási utasításához. Emlékezzünk itt arra, hogy a beágyazott hurkok összekapcsolási utasítása a belső táblán (ebben az esetben egy teljes táblavizsgálaton) egyszer végigfut a külső tábla minden egyes értékénél (esetünkben "Állandó vizsgálat"). Így négyszer szkenneljük át a táblázatot; minden szakaszazonosítóhoz egyszer.

Azt is meg kell jegyezni, hogy a beágyazott hurokillesztés kifejezetten azt mutatja, hogy a külső tábla annak az oszlopnak az értékei, ahol a partícióazonosítók vannak tárolva. Bár ez nem látható azonnal a végrehajtási terv szöveges nézetében (sajnos néha nem vesszük észre ezt az információt), a táblavizsgálat egy oszlopot használ a partícióazonosítókkal, amelyek a vizsgálat végrehajtásához és annak meghatározásához, hogy melyik partíciót vizsgálják. Ez az információ mindig elérhető a grafikus végrehajtási tervben (meg kell nézni a táblanézet operátor tulajdonságait), valamint a lekérdezés végrehajtási tervének XML-ábrázolásában: