Akademia SQL - Część 16: Partycjonowanie tabel – informacje podstawowe

Autor: Damian Widera

Opublikowano: 2 czerwca 2009

Zawartość strony

	Informacje podstawowe
	Laboratorium
	Referencje

Informacje podstawowe

Uwaga: Partycjonowanie tabel dostępne jest tylko w edycjach Enterprise i Developer SQL Server (oraz w wersjach ewaluacyjnych).

Partycjonowanie obiektów bazy danych pozwala na zwiększenie wydajności, rozumianej jako szybkość dostępu do danych oraz ułatwia zarządzanie. Partycjonowanie tabeli może być osiągnięte na kilka sposobów i generalnie polega na podzieleniu na mniejsze części. Nie jest wymagane jednak, aby podział skutkował fizycznym rozłożeniem tabeli pomiędzy kilka fizycznych plików rozmieszczonych na fizyczne dyski twarde. W ten sposób można znacząco zwiększyć wydajność samej aplikacji, ponieważ dane odczytywane są równocześnie przy udziale wielu głowic dysku twardego.

Jednym ze sposobów podziału tabeli na partycje, który jest tematem tej lekcji, jest wykorzystanie podziału horyzontalnego, który polega na podziale tabeli względem pewnego klucza nałożonego na wiersze tabeli. Na przykład tabela zawierająca 100 milionów wierszy może zostać podzielona na 12 partycji (mniejszych tabel) reprezentujących kolejne miesiące w roku. Jeżeli zapytanie wymaga danych z określonego miesiąca, to będzie odwoływało się do określonej części (partycji) tabeli, a nie do całego zbioru. Najistotniejszym pytaniem, na które należy odpowiedzieć, jest w jaki sposób wyznaczyć klucz partycjonowania tabeli. Odpowiedź na to pytanie wymaga przeprowadzenia analizy biznesowej, ponieważ jako klucz partycjonowania powinny zostać wybrane te kolumny, które pozwolą odwoływać się do jak najmniejszej liczby partycji.

Typowym zastosowaniem partycjonowania tabeli jest podział ze względu na „wiek” danych. Można wyobrazić sobie sytuację, w której tabela zawiera dane z ostatnich dwóch lat, ale tylko dane z ostatniego miesiąca są najczęściej potrzebne. Można więc w taki sposób podzielić tabelę, aby w jednej partycji znajdowały się dane tylko z ostatniego miesiąca, a pozostałe dane znajdą się na innej lub innych partycjach.

Proces partycjonowania tabeli składa się z szeregu kroków, które zostaną praktycznie omówione w laboratorium do lekcji. Pierwszym krokiem jest utworzenie odpowiedniej liczby grup plików. Do każdej grupy plików zostanie następnie wstawiony plik, który zawierać będzie partycje tabeli. Następnie należy wykonać funkcję partycjonującą, która określi sposób podziału tabeli. Funkcja ta zawiera informacje o typie klucza partycjonującego (np. int) oraz wartości tego klucza. W funkcji tej definiuje sie również warunki brzegowe podziału:

CREATE PARTITION FUNCTION funkcja_SalesOrderDetail(int)

AS RANGE RIGHT

FOR VALUES(50000,60000,70000)

W pokazanym powyżej przypadku kluczem partycjonowania będzie kolumna o typie int, a do kolejnych partycji trafia przedziały wartości:

• Od 0 do 49999 do pierwszej partycji,
• Od 50000 do 59999 do drugiej partycji,
• Od 60000 do 69999 do trzeciej partycji,
• Od 70000 do czwartej partycji.

Słowo kluczowe RIGHT w definicji funkcji oznacza, że wartość brzegowa (50000, 60000 i 70000) trafią do kolejnej partycji.

Kolejnym krokiem jest wykonanie schematu partycjonowania, w którym należy powiązać funkcję partycjonującą i grupy plików, w których będą przechowywane kolejne partycje tabeli.

Ostatnim krokiem jest utworzenie definicji tabeli za pomocą znanej składni CREATE TABLE i wskazanie w frazie ON schematu partycjonowania a nie grupy plików, jak ma to miejsce dla tabel niepartycjonowanych.

Tworzenie partycjonowanej tabeli jest możliwe tylko dla użytkownika, która posiada szereg uprawnień w SQL Server. Użytkownik taki musi posiadać uprawnienia ALTER ANY DATASPACE, CONTROL lub ALTER dla bazy danych, w której będzie partycjonowana tabela, CONTROL SERVER lub ALTER ANY DATABASE na poziomie uprawnien serwera. W praktyce takie uprawnienia mają członkowie serwerowej roli sysadmin i bazodanowych ról db_owner i db_ddladmin.

 Do początku strony

Laboratorium

Celem laboratorium jest praktyczne zapoznanie się z mechanizmem partycjonowania, co pozwoli na wykorzystanie go w wielu praktycznych zastosowaniach. Do wykonania przykładów potrzebna będzie baza danych AdventureWorks2008, którą można pobrać z witryny www.codeplex.com.

1. W tym zadaniu zostanie krok po kroku przedstawiony sposób na utworzenie partycjonowanej tabeli. Tabela, która zostanie podzielona na partycje, to kopia istniejącej tabeli Sales.SalesOrderDetail, którą utworzymy w dalszej części laboratorium. Duże tabele dzięki mechanizmowi partycjonowania moga być przechowywane w wielu plikach, co w wielu przypadkach ułatwi i przyspieszy dostęp danych. Utworzona w tym przykładzie funkcja partycjonująca podzieli tabele na partycje horyzontalnie, tzn. po wiersach, wykorzystując podany klucz.

2. Otwórz SQL Server Management Studio (SSMS) i połącz się z serwerem baz danych, na którym zainstalowana jest baza danych AdventureWorks2008. Pamiętaj, że do wykonania tego ćwiczenia musisz mieć uprawnienia, które zostały opisane w części teoretycznej.

3. Pierwszym krokiem jest utworzenie grup plików. Należy rozważyc wszystkie uwarunkowania biznesowe oraz znać dobrze dane, ich przyrost oraz rozkład zanim zostanie podjęta decyzja o liczbie grup plików, które mają być utworzone. W naszym przypadku utworzone zostaną cztery grupy plików. Aby wykonać ten krok otwórz nowe okno zapytania, wpisz i uruchom poniższy kod:

USE [master]

GO

ALTER DATABASE [AdventureWorks2008] ADD FILEGROUP [grupa_1]

GO

ALTER DATABASE [AdventureWorks2008] ADD FILEGROUP [grupa_2]

GO

ALTER DATABASE [AdventureWorks2008] ADD FILEGROUP [grupa_3]

GO

ALTER DATABASE [AdventureWorks2008] ADD FILEGROUP [grupa_4]

GO

4. Następnie do każdej nowej grupy plików należy wstawić pliki. W praktyce, każdy plik zostałby umieszczony na osobnym dysku, aby zwiekszyć szybkośc odczytu i zapisu danych, jednak dla celów demonstarcyjnych pliki zostaną utworzone w jednym katalogu:

ALTER DATABASE [AdventureWorks2008] ADD FILE

(NAME = N'AdventureWorks2008_D1',

FILENAME = N'C:\Temp\AdventureWorks2008_D1.ndf',

SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [grupa_1]

GO

ALTER DATABASE [AdventureWorks2008] ADD FILE

(NAME = N'AdventureWorks2008_D2',

FILENAME = N'C:\Temp\AdventureWorks2008_D2.ndf',

SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [grupa_2]

GO

ALTER DATABASE [AdventureWorks2008] ADD FILE

(NAME = N'AdventureWorks2008_D3',

FILENAME = N'C:\Temp\AdventureWorks2008_D3.ndf',

SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [grupa_3]

GO

ALTER DATABASE [AdventureWorks2008] ADD FILE

(NAME = N'AdventureWorks2008_D4',

FILENAME = N'C:\Temp\AdventureWorks2008_D4.ndf',

SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [grupa_4]

GO

Uwaga! Każdy plik został utworzony z początkowym rozmiarem 2MB oraz z włączoną opcją automatycznego powiększania o 1MB. Takie rozwiązanie w praktyce nie jest stosowane i zostało użyte tylko w celach demonstracyjnych.

Zwróć uwagę, że pliki zostały utworzone w katalogu C:\Temp. Będziesz musiał to zmienić, jeśli okaże się, że nie masz uprawnień, aby w tym miejscu tworzyć pliki.

5. Po utworzeniu grup plików i wstawieniu plików należy zdefiniować funkcje partycjonującą, która określa sposób podziału tabeli na partycje. Na tym etapie nie wskazuje się, która kolumna będzie kluczem partycjonowania, wiadomo jedynie jakiego typu jest ten klucz (w tym wypadku – int). W definicji funkcji partycjonującej nie podaje się również nazwy tabeli.

Aby utworzyć funkcję partycjonującą wykonaj poniższy kod:

USE AdventureWorks2008

GO

CREATE PARTITION FUNCTION funkcja_SalesOrderDetail(int)

AS RANGE RIGHT

FOR VALUES(50000,60000,70000)

GO

6. Ostatnim krokiem przed podziałem tabeli na partycje jest utworzenie schematu partycjonowania, który wykorzysta funkcje partycjonującą wykonaną w poprzednim punkcie. Tak, jak w przypadku funkcji, podczas tworzenia schematu partycjonowania nie trzeba podawać nazwy tabeli w jego definicji.

USE AdventureWorks2008

GO

CREATE PARTITION SCHEME schemat_SalesOrderDetail

AS PARTITION funkcja_SalesOrderDetail

TO (grupa_1,grupa_2,grupa_3,grupa_4)

GO

7. Obiekty, które zostały wykonane, czyli funkcja i schemat partycjonowania, można odnaleźć w SSMS w Eksploratorze obiektów . Należy wybrać bazę danych AdventureWorks2008 a następnie przejść do sekcji Storage:

Rysunek 1: Sekcja Storage.

8. Po utworzeniu wszystkich wymaganych obiektów można przystąpić do utworzenia partycjonowanej tabeli. Definicja tabeli znajduje się poniżej. Należy w niej podać nazwę schematu, względem którego zostanie podzielona na partycje. Nazwę te wskazuje się we frazie ON nazwa_schematu(nazwa_klucza).

USE [AdventureWorks2008]

GO

CREATE TABLE [Sales].[Kopia_SalesOrderDetail](

    [SalesOrderID] [int] NOT NULL,

    [SalesOrderDetailID] [int] IDENTITY(1,1) NOT NULL,

    [CarrierTrackingNumber] [nvarchar](25) NULL,

    [OrderQty] [smallint] NOT NULL,

    [ProductID] [int] NOT NULL,

    [SpecialOfferID] [int] NOT NULL,

    [UnitPrice] [money] NOT NULL,

    [UnitPriceDiscount] [money] NOT NULL,

    [LineTotal]  AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),

    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,

    [ModifiedDate] [datetime] NOT NULL,

     CONSTRAINT [PK_SalesOrderDetail_SalesOrderDetailID] PRIMARY KEY CLUSTERED 

    (

        [SalesOrderID] ASC,

        [SalesOrderDetailID] ASC

    ) 

)ON schemat_SalesOrderDetail([SalesOrderID])

9. Po utworzeniu tabeli i podzieleniu jej na partycje można wypełnic ją danymi. Ponieważ wykonana w poprzednim punkcie tabela jest kopią tabeli Sales.SalesOrderDetail to najprościej będzie wypełnić ją już istniejącymi danymi:

USE AdventureWorks2008

GO

INSERT INTO [Sales].[Kopia_SalesOrderDetail]

(

   [SalesOrderID]

   ,[CarrierTrackingNumber]

   ,[OrderQty]

   ,[ProductID]

   ,[SpecialOfferID]

   ,[UnitPrice]

   ,[UnitPriceDiscount]

   ,rowguid

   ,[ModifiedDate]

)

SELECT [SalesOrderID]

      ,[CarrierTrackingNumber]

      ,[OrderQty]

      ,[ProductID]

      ,[SpecialOfferID]

      ,[UnitPrice]

      ,[UnitPriceDiscount]

      ,NEWID()

      ,[ModifiedDate]

  FROM [Sales].[SalesOrderDetail]

GO

10. Po wypełnieniu tablicy danymi można sprawdzić, do których partycji dane zostały faktycznie zapisane. Wykonaj poniższe zapytanie, które wyświetla informację, w której partycji znajduje się dany wiersz:

USE AdventureWorks2008

GO

SELECT 

    SalesOrderDetailId,

    [SalesOrderID],

    $PARTITION.funkcja_SalesOrderDetail ([SalesOrderID]) AS [Numer partycji]

FROM Sales.Kopia_SalesOrderDetail

GO

Wynik wykonania zapytanie jest nastepujący:

Rysunek 2: Wynik zapytania.

11. Poniższe zapytanie wyświetla informację o ilości wierszy w danej partycji. Pozwala to ocenić, czy funkcja partycjonująca została wybrana poprawnie, czy może należy ją zmodyfikować. W idealnym przypadku partycje powinny być zapełnione równą ilością elementów, ale w praktycje jest to trudne do osiągnięcia:

SELECT 

    $PARTITION.funkcja_SalesOrderDetail ([SalesOrderID]) AS [Numer partycji],

    COUNT(*) AS [Liczba elementów]

FROM Sales.Kopia_SalesOrderDetail

GROUP BY $PARTITION.funkcja_SalesOrderDetail ([SalesOrderID])

ORDER BY  [Numer partycji]

GO

Po uruchomieniu zapytania otrzymano nastepujace wyniki:

Numer partycji Liczba elementów

-------------- ----------------

1              30708

2              42107

3              33312

4              15190



(4 row(s) affected)

 Do początku strony

Referencje

Dodatkowe informacje na temat partycjonowania tabel oraz innych możliwości, które SQL Server 2008 daje z zakresu partycjonowania, można znaleźć w następujących miejscach:

[1] Witryna SQL Server 2008 Jumpstart

[2] Dokumentacja Books Online

[3] „Serwer SQL 2008. Administracja i programowanie”, Wydawnictwo Helion SA

Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) Od 8 lat zajmuje się projektowaniem, tworzeniem i wdrażaniem aplikacji wykorzystujących platformę .NET, SQL Server oraz Oracle. Obecnie pracuje jako project manager dla LGBS Polska. Pracował także jako trener, programista, administrator baz danych, twórca domumentacji oraz analityk biznesowy. Aktywnie współpracuje z polskim oddziałem Microsoft publikując atykuły, webcasty oraz porady z zakresu SQL Server na stronach TechNet. Jest współautorem książki „Serwer SQL 2008. Administracja i programowanie”. Speaker na wielu konferencjach, m.in. Microsoft Heroes Happen Here, C2C, European PASS Conference, Microsoft Technology Summit, Energy Launch, TechED. Od 2004 r. posiada certyfikaty firmy Microsoft: MCT, MCITP–DBA oraz MCSD.NET. Jest współtwórcą oraz liderem jednej z najwiekszych grup pasjonatów SQL Server w Polsce – Śląskiej Regionalnej Grupy Microsoft (PLSSUG Katowice). Od listopada 2008 jest prezesem Polish SQL Server Users Group (PLSSUG) w Polsce. W styczniu 2009 nagrodzony tytułem MVP w kategorii SQL Server.

Do początku strony