Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy”

Mechanizmy wewnętrzne baz danych – czyli co w bazach

„piszczy”Na przykładzie SQL Server 2008

1informatyka +

Agenda

• Więzy integralności referencyjnej• Transakcje

– Poziomy izolacji transakcji

• Wyzwalacze– Rodzaje wyzwalaczy

• Procedury składowane i funkcje użytkownika• Indeksy

– Fizyczna organizacja danych w SQL Server 2008– Rodzaje indeksów (zgrupowane, niezgrupowane) – Optymalizacja zapytań i plany wykonania

• Kopie bezpieczeństwa i odtwarzanie danych• Podsumowanie

2informatyka +

Agenda







3informatyka +

Więzy integralności referencyjnej• W bazach danych często występuje konieczność zdefiniowana relacji

pomiędzy dwoma tabelami np.:– klient jest właścicielem rachunku bankowego– rachunek jest określonego typu (oszczędnościowy, rozliczeniowy…)

• Do tego celu służą więzy integralności referencyjnej • Chcemy powiązać klienta z rachunkiem bankowym:

• Wymaganie: Rachunek ma dokładnie jednego właściciela.

4informatyka +

JAK?

Więzy integralności referencyjnej• Dodajmy kilku klientów i zobaczmy ich dane:

• Rezultat:

• Mamy troje klientów o identyfikatorach 1, 2 i 3.

5informatyka +

Więzy integralności referencyjnej• Dodajmy teraz kilka rachunków i zobaczmy ich dane:

• Rezultat:

• Rachunki zostały utworzone (automatycznie nadane numery i daty utworzenia)• Mamy cztery rachunki. Trzy należą do naszych klientów.• Czwarty rachunek – nie wiadomo do kogo! Utraciliśmy właśnie spójność danych :-(

6informatyka +

Nie ma takiego klienta!

Więzy integralności referencyjnej• Jak zabezpieczyć się przed tego typu błędami?• Klucz obcy – kolumna lub kombinacja kolumn, która jest używana

do określenia i wymuszenia relacji pomiędzy danymi z dwóch tabel

7informatyka +

Kolumna z kluczem

podstawowym lub unikalnym

Kolumna tego samego typu!

Stworzone ograniczenie –

klucz obcy

Więzy integralności referencyjnej• Spróbujmy więc utworzyć klucz obcy na naszej tabeli rachunki:

• Nic z tego! Nie udało się utworzyć ograniczenia ze względu na istniejące dane (feralny rachunek z błędnym identyfikatorem właściciela)• Rezultat:

Msg 547, Level 16, State 0, Line 1The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Rachunki_Klienci". The conflict occurred in database "Bank", table "dbo.Klienci", column 'KlientID'.

• Jak to naprawić? Opcja WITH NOCHECK lub poprawienie błędnych danych.

8informatyka +

Więzy integralności referencyjnej• Naprawiamy błędne dane:

• Ponowne wykonanie polecenia tworzącego klucz obcy kończy się sukcesem! Od tej pory baza nie pozwoli na utworzenie rachunku dla nieistniejącego klienta:

• Rezultat:Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Rachunki_Klienci". The conflict occurred in database "Bank", table "dbo.Klienci", column 'KlientID'.

9informatyka +

Więzy integralności referencyjnej• Nie ma natomiast problemu z dodaniem rachunku dla istniejącego klienta:

• Rezultat:

10informatyka +

Luka w numeracji – ślad po nieudanej

próbie dodania rachunku

Więzy integralności referencyjnej• Przy tworzeniu klucza obcego można korzystać z opcji ON DELETE i ON UPDATE.• Służą one do określenia reakcji na usunięcie lub zmodyfikowanie wiersza (z tabeli z kluczem podstawowym) do którego odnosi się klucz obcy. Isnieją cztery warianty dla każdej :

– No action (domyślna)• Nie podejmuje żadnej akcji.

– Cascade• Usuwa/modyfikuje wiersz z kluczem obcym

– Set null• Ustawia wartość null jako wartość kolumn klucza obcego (działa jeżeli te kolumny dopuszczają wartość null!)

– Set default• Ustawia wartość domyślną dla kolumn klucza obcego (działa jeśli te kolumny maja określona wartość domyślną i spełnia ona regułę klucza lub dopuszcza wartość null.

11informatyka +

UWAGA! Bardzo wygodne i bardzo niebezpiecznie!!!

Więzy integralności referencyjnej• Usuńmy nasz klucz obcy i stwórzmy go na nowo z opcją ON DELETE CASCADE:

• Usuńmy teraz jednego klienta (posiadającego dwa rachunki):

• Pobranie listy wszystkich rachunków daje teraz rezultat:

• Usunęliśmy jeden wiersz z tabeli Klienci, a automatycznie zostały usunięte dwa rachunki należące do usuniętego klienta• Ta opcja jest bardzo niebezpieczna!!!

12informatyka +

Więzy integralności referencyjnejGarść faktów na temat kluczy obcych:• Klucz obcy może zawierać więcej niż jedna kolumnę

– Uwaga! Jeśli w takim przypadku choć jedna z kolumn ma wartość null, to pozostałe nie są sprawdzane pod kątem zgodności z regułą klucza!

• Klucz obcy może odwoływać się do tej samej tabeli (samozłączenie)– Stosowane do budowania hierarchii– Łatwe w implementacji, trudne w obsłudze– Alternatywą jest typ danych

HierarchyID lub XML.

• Klucz obcy (podobnie jak ograniczenia typu CHECK) można włączać i wyłączać za pomocą polecenia ALTER TABLE z opcją CHECK lub NOCHECK CONSTRAINT. [nie mylić z WITH CHECK/ WITH NOCHECK !]

13informatyka +

Agenda







14informatyka +

Transakcje• Dane w bazie reprezentują aktualną sytuację biznesową

– Mogą zawierać dane o zamówieniach, informacje o procesie produkcyjnym, o alokacji określonych zasobów i ich statusie itd., itp..

• Zmiany sytuacji biznesowej (stanu) powodują zmiany w danych w bazie– Pojawiają się nowe wiersze, modyfikowane są istniejące, zdarzają się

też usunięcia wierszy.

• Zmiana stanu powinna prowadzić od jednego stabilnego stanu do drugiego

• Wszelkie stany „przejściowe” spowodowane dowolnym czynnikiem są niedopuszczalne!– Zapisanie tylko części zamówienia???– Przelew bankowy wykonany połowicznie (środki pobrane, ale nie

umieszczone na docelowym rachunku) ???

15informatyka +

Transakcje• Transakcja to sekwencja logicznie powiązanych operacji

na danych, których celem jest przejście bazy danych z jednego stanu spójnego do drugiego

• Właściwości transakcji – akronim ACID– Atomicity (atomowość)

• Operacje w ramach transakcji są niepodzielne. Albo wykonają się w całości, albo wcale

– Consistency (spójność)• Baza danych jest w stanie spójnym zarówno przed rozpoczęciem transakcji

jak i po jej zakończeniu (nieważne czy transakcja zakończyła się sukcesem czy porażką)

– Isolation (odizolowanie)• Transakcje są od siebie logicznie odseparowane. Z punktu widzenia

transakcji – wykonywane sekwencyjnie

– Durability (trwałośc)• Jeżeli transakcja została zatwierdzona, to niezależnie od awarii systemu nie

może zostać cofnięta bądź utracona

16informatyka +

Transakcje• Skoro to takie ważne, to czy nie wystarczy kolejkowanie

transakcji i sekwencyjnie ich wykonywanie?• Nie. To kwestia wydajności! Takie podejście

powodowałoby drastyczny spadek wydajności wraz ze wzrostem liczby transakcji (użytkowników)

• Zależnie od specyfiki operacji wykonywanych w ramach transakcji można starać się zrównoleglać wykonywanie innych transakcji i operacji odczytu danych.

• Możliwości „zrównoleglania” operacji sterowane są poprzez mechanizm blokad (locks).

• Pojęcie poziomu izolacji odnosi się właśnie do tego zagadnienia - jakie blokady i na jaki czas są konieczne, żeby zapewnić odpowiedni poziom bezpieczeństwa dla transakcji.

17informatyka +

Transakcje• SQL Server obsługuje dwa tryby rozpoczynania

transakcji:– Jawny (explicit)

• Transakcja rozpoczyna się poleceniem BEGIN TRANSACTION

– Niejawny (implicit)• Każde pierwsze polecenie modyfikujące dane( m.in. INSERT, UPDATE,

DELETE) powoduje rozpoczęcie transakcji• Transakcję należy zakończyć jawnie (COMMIT lub ROLLBACK)• Wyłącza tryb autocommit!

• Domyślnie SQL Server działa w trybie autocommit– Każde polecenie modyfikujące dane (m.in. INSERT, UPDATE,

DELETE) powoduje rozpoczęcie transakcji.– Poprawne wykonanie polecenia powoduje automatyczne

zatwierdzenie (COMMIT) transakcji.– Błąd w trakcie wykonania polecenia powoduje automatyczne

wycofanie (ROLLBACK) transakcji

18informatyka +

Transakcje• Transakcje mogą być zagnieżdżane:

• UWAGA!– COMMIT dla transakcji zagnieżdżonej tak naprawdę nie ma żadnego efektu… jedynie zmniejsza poziom

zagnieżdżenia.– ROLLBACK powoduje wycofanie wszystkich transakcji łącznie z główną (zawierającą zagnieżdżone

pozostałe). Ustawia poziom zagnieżdżenia na 0– ROLLBACK z parametrem (nazwa punktu zapisu) wycofuje transakcje do tego punktu. Nie powoduje

zmiany poziomu zagnieżdżenia.

19informatyka +

Transakcje

• Aktualny poziom zagnieżdżenia transakcji można odczytać ze zmiennej @@TRANCOUNT

• Rozpoczynając transakcje można nadać jej nazwę. • W trakcie transakcji można tworzyć za pomocą

polecenia SAVE dodatkowe punkty zapisu (savepoint), do których będzie można wycofywać częściowo transakcję przez wywołanie polecenia ROLLBACK z parametrem – nazwą punktu zapisu.

20informatyka +

Agenda







21informatyka +

Transakcje

• Konflikty i problemy występujące przy dostępie do danych poziomu więcej niż jednej transakcji (w przykładach są to transakcje T1 i T2):

• Lost update (zgubiona modyfikacja)– T1 i T2 modyfikują wartość kolumny jedna po drugiej. Tylko

ostatnia modyfikacja (zatwierdzona transakcja) będzie widoczna w bazie.

• Dirty read (brudny odczyt) – T1 modyfikuje dane. Przed jej zatwierdzeniem, T2 odczytuje

zmodyfikowane dane i wykorzystuje je. Jeśli T1 zostanie wycofana to T2 pracuje na niepoprawnych lub nieistniejących danych – niespójność!

22informatyka +

Transakcje

• Konflikty i problemy występujące przy dostępie do danych poziomu więcej niż jednej transakcji ( w przykładach są to transakcje T1 i T2):

• Nonrepeatable Read (niepowtarzalny odczyt)– T1 odczytuje te same dane dwukrotnie w trakcie działania.

Pomiędzy jednym a drugim odczytem T2 modyfikuje te dane i zostaje zatwierdzona. W związku z tym drugi odczyt danych z poziomu T1 pobiera inne wartości niż pierwszy! Może to prowadzić do niespójności.

• Phantom reads (odczyt – widmo) – T1 modyfikuje dane z określonego zakresu i następnie pobiera je

do dalszej analizy. Pomiędzy modyfikacją a odczytem, T2 dodaje nowe wiersze do modyfikowanego przez T1 zakresu. T1 odczytuje dane i uzyskuje wiersze, których nie było przy modyfikacji.

23informatyka +

Transakcje• Standard ANSI definiuje cztery poziomy izolacji dla transakcji. Każdy z nich

cechuje się eliminowaniem szans na wystąpienie kolejnego rodzaju konfliktu: [poziom domyślny został wyróżniony]

• SQL Server posiada dwa dodatkowe poziomy izolacji (bazujące na wersjonowaniu wierszy): jeden jest implementacją poziomu READ COMMITED, drugi to poziom SNAPSHOT (funkcjonalnie zbliżony do SERIALIZABLE)

24informatyka +

Poziom izolacji

Dirty read

Nonrepeatable read

Phantom read

READ UNCOMMITED

TAK TAK TAK

READ COMMITED

NIE TAK TAK

REPEATABLE READ

NIE NIE TAK

SERIALIZABLE NIE NIE NIE

Transakcje

• Przy transakcjach warto wspomnieć o jeszcze jednym negatywnym zjawisku – zakleszczeniu.

25informatyka +

Rysowanie wykresu: - linijka - kreda

• Dwie osoby chcą narysować wykres. Potrzebne do tego są: linijka i kreda.• Pierwsza osoba sięga po kredę, druga po linijkę…• W efekcie pierwsza zaczyna czekać na linijkę, druga na kredę…• Rozwiązanie – wylosować osobę (deadlock victim), zabrać jej

linijkę lub kredę i oddać drugiej.

Transakcje• Minimalizowanie szansy na wystąpienie zakleszczenia

• Sięganie do zasobów wg tej samej kolejności!

26informatyka +

Rysowanie wykresu: - linijka - kreda

Czekam na linijkę

Chwyciłem linijkę

Teraz tylko kreda…

Transakcje• Kilka dobrych rad dotyczących transakcji• Starajmy się budować transakcje tak krótkie jak się da! Pozwala to skrócić czas aktywności blokad i poprawić wydajność• Planujmy kolejność uzyskiwania dostępu do zasobów w ramach transakcji aby unikać zakleszczeń• Mimo, iż SQL Server daje nam możliwości sterowania mechanizmem blokad – jeśli nie wiemy na 100% co robimy – lepiej nie ingerować w tę

dziedzinę. Mechanizm ten sam z siebie działa bardzo dobrze.• Dobierajmy właściwy poziom izolacji transakcji dla konkretnych operacji. Korzystanie ze zbyt wysokiego powoduje spadek wydajności

27informatyka +

Agenda







28informatyka +

Wyzwalacze• Wyzwalacz to specjalny rodzaj procedury składowanej, która jest wywoływana automatycznie w reakcji na zajście określonego zdarzenia.• Wyzwalacze to sztandarowy mechanizm pozwalający na implementowanie w bazie reguł biznesowych i zapewnienie spójności danych w

zakresie szerszym niż ograniczenia (constraints)• SQL Server posiada mechanizm wyzwalaczy dla DML (Data Manipulation Language) oraz DDL (Data Definition Language)• Korzystanie z wyzwalaczy jest przyjemne, ale muszą być one dokładnie udokumentowane! W przeciwnym razie w przypadku wystąpienia

problemów z logiką bazy bardzo trudno będzie dociec źródła problemu.

29informatyka +

Agenda







30informatyka +

Wyzwalacze DML• Wyzwalacze mogą reagować na zdarzenia: INSERT, UPDATE i DELETE• Dwa rodzaje wyzwalaczy: AFTER i INSTEAD OF• Wyzwalacze AFTER wykonują się po operacji, która spowodowała ich uruchomienie i wchodzą w skład realizowanej transakcji• Wyzwalacze INSTEAD OF wykonują się zamiast wywołującej je operacji• Można deklarować wiele wyzwalaczy na tej samej tabeli, dla tego samego zdarzenia. UWAGA! W takiej sytuacji nie mamy zbyt dużego wpływu

na kolejność wykonania wyzwalaczy. Można jedynie określić, który wykona się jako pierwszy i jako ostatni.

31informatyka +

Wyzwalacze DML• Dodajmy do naszej bazy jeszcze jedną tabelę• Będzie ona przechowywać informacje

o operacjach wykonywanych na rachunku

• Wykorzystamy wyzwalacze dozaimplementowania reguł biznesowych:– Nie można usunąć ani zmodyfikować

raz wykonanej operacji

– Minimalna kwota wypłaty z rachunku musibyć większa lub równa 10 zł

32informatyka +

Wyzwalacze DML• Na pierwszy ogień weźmy blokadę modyfikacji i usuwania wpisów w tabeli Operacje.• Zrealizujemy to za pomocą wyzwalacza INSTEAD OF:

• Dodajmy parę wpisów:

• Spróbujmy teraz usunąć operację:

• Rezultat:

33informatyka +

Wyzwalacze DML• Następny krok to implementacja drugiej reguły biznesowej – minimalna kwota wypłaty musi być większa lub równa 10 zł

• Zrealizujemy to za pomocą wyzwalacza AFTER:

• Spróbujmy wykonać wypłatę zbyt małej kwoty:

• Rezultat:

34informatyka +

Transakcja została

wycofana

Wyzwalacze DML• W kodzie wyzwalacza mamy dostęp do dwóch

specjalnych tabel : inserted i deleted• Tabela inserted zawiera listę dodawanych wierszy w

ramach wykonywanego polecenia INSERT• Tabela deleted zawiera listę wierszy usuwanych w

ramach wykonywanego polecenia DELETE• W przypadku wykonywania modyfikacji danych,

(UPDATE) tabela inserted zawiera nowe wartości wierszy, a deleted stare.

• Z tych tabel korzysta się przy tworzeniu kodu wyzwalaczy odwołującego się do modyfikowanych danych.

35informatyka +

Wyzwalacze DML• Ważne! Nie należy zakładać, że wyzwalacz będzie

wywoływany zawsze dla modyfikacji pojedynczego wiersza!

36informatyka +

TAK

NIE

Wyzwalacze DDL• Wraz z pojawieniem się SQL Servera 2005 pojawił się

nowy rodzaj wyzwalacza – wyzwalacz DDL

• Wyzwalacze DDL mogą reagować na zdarzenia - wywołania poleceń DDL (CREATE, ALTER, DROP, GRANT, DENY, REVOKE, UPDATE STATISTICS )

• Przeznaczone do wspomagania audytu zmian w strukturze bazy danych i śledzenia jej zmian

• Pozwalają też ograniczać swobodę modyfikowania struktury bazy danych lub tworzyć mechanizmy zabezpieczające przed przypadkową modyfikacją

• W kodzie wyzwalacza dostępna jest funkcja EVENTDATA() zwracająca szczegółowe informacje o zdarzeniu w formie XML

37informatyka +

Wyzwalacze DDL• Stwórzmy wyzwalacz DDL, który zablokuje wszelkie

modyfikacje tabel oraz próby ich usunięcia:

38informatyka +

Tabela testowa

Wyzwalacz DDL

Próba usunięcia

tabeli

Rezultat usuwania

Agenda







39informatyka +

Procedury składowane i funkcje użytkownika

• Procedura składowana przypomina funkcję (metodę) znaną z języków programowania:– Zawiera blok kodu, który jest wykonywany po jej wywołaniu\– Może przyjmować parametry wywołania (wejściowe oraz

wyjściowe) a także zwracać wartość (kod powrotu – wartość całkowita)

– W jej kodzie można stosować instrukcje warunkowe i pętle

• Pozwala na odcięcie aplikacji od szczegółów implementacyjnych bazy danych – tworzy warstwę abstrakcji danych

• Można nadawać uprawnienia do jej wykonania

40informatyka +


• W naszej bankowej bazie danych możemy zastosować procedurę składowaną do utworzenia rachunku dla nowego klienta.

• Wymagania biznesowe: – Klient podaje swój adres email oraz imię i nazwisko– Zostaje dla niego utworzone konto. Otrzymuje jego numer.– Klient dostaje na dzień dobry 100 zł na swoje nowe konto

• Zrealizujemy te wymagania za pomocą procedury, która przyjmie na wejściu dane klienta.

• Numer nowootwartego rachunku zostanie zwrócony jako parametr wyjściowy

41informatyka +

Procedury składowane i funkcje użytkownika• Postać procedury zakładania promocyjnego konta:

42informatyka +


• Wywołanie procedury:

• Rezultat:

• Sukces! Widać trzy udane wykonania polecenia insert oraz wygenerowany numer rachunku

43informatyka +


• Funkcje użytkownika są podobne do procedur składowanych

• Różnią się tym, że ich wywołania mogą być wykorzystane w charakterze wartości w wyrażeniach i zapytaniach.

• Funkcje występują w dwóch wariantach (zależnie od typu zwracanej wartości):– Skalarne (scalar functions)

– Tabelaryczne (mogą składać się z jednego zapytania SELECT lub z wielu wyrażeń)

• Korzystanie z funkcji skalarnej : SELECT funkcja(parametr)

• Korzystanie z funkcji tabelarycznej: SELECT * FROM funkcja(parametr)

44informatyka +


• Funkcja obliczająca saldo wskazanego rachunku:

• Wywołanie:

• Rezultat:

45informatyka +


• Funkcja tabelaryczna (inline) zwracająca n ostatnich operacji wykonanych na rachunkach:

• Wywołanie:

• Rezultat:

46informatyka +

Procedury składowane i funkcje użytkownika• Ta sama funkcja zrealizowana jako „multistatement”

• Wywołanie: • Rezultat:

47informatyka +

Agenda







48informatyka +

Agenda







49informatyka +

Fizyczna organizacja danych w SQL Server 2008

• Logicznie tabela składa się z wierszy, które składają się z kolumn.

• Jak te dane przechowywane są na dysku?• Jakie są ograniczenia przy definiowaniu tabel?• Jaki ma to wpływ na wydajność?

50informatyka +


• Podstawowa jednostka – strona (page)– Rozmiar: 8 KB (dokładnie 8060 bajtów na dane)– Jest to jednocześnie maksymalna długość wiersza

(nie licząc kolumn przechowywanych na osobnych stronach)

– Wiersz nie może być podzielony pomiędzy strony.

• Rodzaje stron– data (wszystkie dane z wyjątkiem kolumn typów: text, ntext, image,

nvarchar(max), varchar(max), varbinary(max), xml ) – index (wpisy indeksów)– text/image (text, ntext, image, nvarchar(max), varchar(max),

varbinary(max), xml oraz niemieszczące się w wierszu: varchar, nvarchar, varbinary)

– GAM, (Global Allocation Map) SGAM (Shared GAM), IAM (Index Allocation Map) – wrócimy do nich!

51informatyka +

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Fizyczna organizacja danych w SQL Server 2008• 8 KB (strona) to trochę mało…• 8 stron – 64 KB to w sam raz na jednostkę alokacji!• Jednostka ta zwana jest obszarem (extent).

• Rodzaje obszarów– Jednolite (uniform extent)

• Zawierają strony należące do jednego obiektu ( tabeli /indeksu )

– Mieszane (mixed extent)• Zawierają strony należące do więcej niż jednego obiektu

• Alokowane i odczytywane są zawsze całe obszary a nie pojedyncze strony

52informatyka +

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

……

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…


• Sterta (heap) – zbiór obszarów zawierających dane z jednej tabeli (lub partycji w przypadku tabel partycjonowanych)

• Dane nie są ze sobą powiązane w żaden sposób• Wyszukiwanie wymaga przejrzenia wszystkich stron

53informatyka +

Nagłówek

Wiersz 1

Wiersz 6

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 31

Wiersz 72

Wiersz 13

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 51

Wiersz 32

Wiersz 93

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 1

Wiersz 2

Wiersz 3

…

Nagłówek

Wiersz 4

Wiersz 98

Wiersz 62

… …

Tabela Tabela


• Tabela może składać się z jednej lub więcej partycji• Sterta jest tworzona osobno dla każdej partycji

54informatyka +

Partycja 1

Partycja 2

Partycja 3


• Skąd wiadomo które obszary są wolne, które są zajęte, do których obiektów należą obszary czy strony?

• Ze stron GAM, SGAM i IAM ;-)– GAM (Global Allocation Map) – informacje o zajętych

obszarach jednolitych (uniform)– SGAM (Shared GAM) - informacje o zajętych obszarach

mieszanych (mixed)– IAM (Index Allocation Map) – informacje o przynależności

obszarów do obiektów

55informatyka +

IAMIAM


• No dobrze, ale jak trafić do odpowiedniej strony IAM?• Każdy obiekt (tabela / indeks) ma wpisy w tabelach

systemowych dotyczące alokacji jego danych• Dostęp do tych informacji – widok sys.partitions• Każda sterta, indeks, obszar LOB mają odpowiadający im

wpis. Wpis ten zawiera wskaźnik do IAM • Wartośc kolumny index_id:

– 0 – sterta– 1 – indeks zgrupowany– 2..250 – indeksy niezgrupowane– 255 – dane LOB

56informatyka +

sys.partitions

id Index_id=0 IAM

Agenda







57informatyka +

Indeks zgrupowany

58informatyka +

sys.partitions

id Index_id=1 Root page

korzeńkorzeń

gałęziegałęzie

liścieliście

Indeks zgrupowany• Struktura drzewiasta (B-tree) – drzewo zrównoważone• Na poziomie korzenia i gałęz i – strony indeksu • Na poziomie liśc i – właściwe strony z danymi z tabeli• Dane fizycznie uporządkowane rosnąco wg klucza indeksu• Tylko jeden indeks zgrupowany dla tabeli!• Unikalność kluczy zapewniona wewnętrznie

– Jeśli w tabel i występują dwie takie same wartości k lucza, dodawana do nich jest losowa liczba i taki k lucz staje s ię wewnętrznie rozpoznawany jako unikalny

• Kiedy stosowanie jest szczególnie uzasadnione– Operowanie na zakresach danych i danych grupowanych– Pobieranie danych w określonym porządku– Zapytania korzystające z wielu kolumn tabel i– Lepsza wydajność przy dodawaniu nowych wierszy

• Na jakich kolumnach tworzyć indeks zgrupowany?– Mała długość– Wysoka selektywność (mało powtarzających s ię wartości k lucza indeksu)– Rzadko bądź wcale nie zmieniane wartości– Wartości k lucza dla kolejno dodawanych wierszy są rosnące

59informatyka +

Indeks niezgrupowany (budowany na stercie)

60informatyka +

sys.partitions


korzeń

gałęzie

liście

sterta

Indeks niezgrupowany (budowany na stercie)• Struktura drzewiasta (B-tree) – drzewo zrównoważone• Na poziomie korzenia, gałęzi i liści – strony indeksu • Liście zawierają wskaźniki do właściwych stron na stercie• Można tworzyć do 248 indeksów niezgrupowanych na tabeli• Stosowane są gdy dane wyszukiwane są według wielu kryteriów (różne zapytania)• Maksymalna długość klucza – 900 bajtów• Maksymalnie 16 kolumn w kluczu

61informatyka +

Indeks niezgrupowany (budowany na zgrupowanym)

62informatyka +

sys.partitions


korzeń

gałęzie

liście

Indeks zgrupowany

Indeks niezgrupowany (budowany na zgrupowanym)• Praktycznie wszystko tak samo jak w budowanym na stercie.• Z wyjątkiem dwóch rzeczy:

– Liście zawierają wartości klucza z indeksu zgrupowanego

– Wskaźnik zawsze ustawiony jest na korzeń indeksu zgrupowanego

• Jeśli indeks zgrupowany zostanie usunięty – niezgrupowany zostanie przebudowany (na wariant oparty o stertę)• Jeśli indeks zgrupowany zostanie utworzony – indeksy niezgrupowane zostaną także przebudowane (ze sterty na zgrupowany)

63informatyka +

Agenda







64informatyka +

Wykonywanie zapytań

• Zapytanie zostało przekazane do wykonania…co dzieje się dalej?

• Całość procesu można opisać kilkoma etapami:– Parsowanie zapytania (błędy składniowe). Efektem jest drzewo

zapytania.– Standaryzacja zapytania (drzewa). Usuwanie nadmiarowości,

standaryzowanie podzapytań itp..

– Optymalizacja zapytania .Wieloetapowy proces prowadzący do wyboru sposobu realizacji zapytania

– Kompilacja wygenerowanego planu (zapisanie w cache)– Określenie metod fizycznego dostępu do danych– Wykonanie zapytania zgodnie ze stworzonym planem

65informatyka +

Wykonywanie zapytań – optymalizacja zapytania• Optymalizacja zapytania polega na:

– Dokonaniu analizy zapytania (pod kątem kryteriów wyszukiwania oraz złączeń)

– Dobraniu indeksów, które mogą okazać się pomocne przy realizacji zapytania (kryteria wyszukiwania, kolumny wyjściowe)

– Określeniu strategii realizacji złączeń (selektywność, potrzebna pamięć)

• Generowanych jest kilka wariantów, dla każdego szacowany jest koszt wyrażony w operacjach wejścia/wyjścia (I/O) i czasie rocesora (CPU).

• Wybierany jest najtańszy wariant i przekazywany do kompilacji• Plan wykonania można podejrzeć za pomocą włączenia jednej z

opcji:– SET SHOWPLAN_TEXT ON, SET SHOWPLAN_XML ON , SET SHOWPLAN_ALL

ON

66informatyka +

Optymalizacja zapytań - wykorzystanie indeksów• Zakładamy, że zapytania będą tworzone w oparciu o tabelę:

67informatyka +

W celu zwiększenia rozmiaru wiersza i

liczby stron:)

• Nie ma żadnych indeksów na tabeli Klienci • Zapytanie, którym się zajmiemy jest proste:

Wykorzystanie indeksów

• Pierwsze wykonanie zapytania – plan wykonania

68informatyka +

Brak indeksów – skanowanie sterty

• Koszt zapytania (estimated subtree cost) : 2,1385

Pierwsze wykonanie: strony pobierane z

dysku Kolejne wykonania:

strony znajdują się w cache

Wykorzystanie indeksów• Stwórzmy najpierw indeks zgrupowany na kolumnie ID.• Zrealizujemy to przez utworzenie klucza podstawowego na tej kolumnie (prowadzi to do utworzenia indeksu)

69informatyka +

• Wykonanie naszego zapytania po utworzeniu indeksu przebiega według planu:

• Koszt zapytania pozostał bez zmian : 2,1385

Stworzyliśmy indeks zgrupowany, więc nie ma już

sterty.

Wykorzystanie indeksów• Spróbujmy teraz popracować nad wydajnością• Stwórzmy indeks niezgrupowany na kolumnie, której używamy jako kryterium wyszukiwania

70informatyka +

• Skoro istnieje indeks na kolumnie Nazwisko, to powinien zostać użyty do wyszukiwania? Sprawdźmy…

• Dlaczego?• Bo na wyjściu zapytania mamy jeszcze kolumnę Imie!• Optymalizator stwierdził, iż nie warto korzystać z indeksu

niezgrupowanego, skoro i tak trzeba pobrać strony danych, żeby uzyskać wartości z tej kolumny

• Koszt zapytania ciągle bez zmian : 2,1385

Nic z tego! Nasz indeks nie został wykorzystany

Wykorzystanie indeksów

• Zróbmy w końcu coś co przyniesie efekt!• Wiemy dlaczego nasz indeks był nieprzydatny• Uczyńmy go przydatnym! Dodajmy kolumnę Imie do indeksu

71informatyka +

• Wykonajmy kolejny raz nasze zapytanie

Sukces :-)

Wcześniej było 2862 !

• Koszt wykonania: 0,0453• Wcześniej było: 2,1385

Agenda







72informatyka +

Kopie bezpieczeństwa i odtwarzanie danych• Baza danych może być skonfigurowana do pracy w trzech

trybach:– Simple Recovery– Bulk logged Recovery (nieomawiany w ramach wykładu)– Full Recovery

• Zależnie od wybranego trybu mamy różne możliwości wykonywania kopii zapasowych.

• W trybie Simple Recovery można wykonywać jedynie pełne i różnicowe kopie zapasowe. Pozwala to w razie awarii odtworzyć stan bazy do stanu na chwilę wykonania ostatniej kopii zapasowej.

• W trybie Full Recovery można dodatkowo wykonywać kopie logu transakcji. Pozwala to praktycznie na odtworzenie stanu bazy bezpośrednio sprzed awarii

73informatyka +

Kopie bezpieczeństwa i odtwarzanie danych

• Wykonanie kopii zapasowej odbywa się za pomocą polecenia BACKUP:– BACKUP DATABASE – kopia zapasowa całej bazy danych– BACKUP LOG – kopia zapasowa logu transakcji– BACKUP FILE – kopia zapasowa pliku wchodzącego w skład

bazy danych

• Polecenie BACKUP DATABASE wykonuje domyślnie pełną kopię bazy danych

• Wywołane z opcją WITH DIFFERENTIAL – wykonuje kopię różnicową (zmiany danych od ostatniej kopii pełnej)

• Opcja ta zadziała pod warunkiem, że wcześniej wykonaliśmy kopię pełną!

74informatyka +

Kopie bezpieczeństwa i odtwarzanie danych• Odtwarzanie bazy z kopii zapasowej realizowane jest za

pomocą polecenia RESTORE• Posiada ono takie same warianty jak polecenie BACKUP

(DATABASE, LOG, FILE)• W przypadku konieczności odtworzenia stanu z kilku

kolejnych kopii (kopia pełna, kopia różnicowa oraz log transakcji) można wykorzystać opcję NORECOVERY, która powoduje , że baza utrzymywana jest w stanie niespójności i pozwala na odtwarzanie kolejnych kopii.

• Ostatnie polecenie odtworzenia bazy powinno być wywołane z opcją RECOVERY (domyślna), żeby baza wróciła do stanu stabilnego (wycofanie niezatwierdzonych transakcji sprzed awarii)

75informatyka +

Kopie bezpieczeństwa i odtwarzanie danych• Wykonywanie kopii zapasowych i ich odtwarzanie można

wykonywać także z poziomu narzędzia SQL Server Management Studio.

• Dodatkowo tworząc tzw. Maintenance Plan można stworzyć harmonogram wykonywania kopii zapasowych, który będzie realizowany automatycznie.

• Istnieją także narzędzia produkowane przez inne firmy, które pozwalają planować i realizować strategie wykonywania kopii zapasowych baz danych.

• Najważniejsze jednak jest sensowne zaplanowanie strategii wykonywania kopii zapasowych.

– Powinna zapewnić możliwość odtworzenia danych z założoną dokładnością– Powinna zapewnić akceptowalny czas odtworzenia bazy i przywrócenia

gotowości do pracy– Powinna być skrupulatnie realizowana– Powinna zawierać dokładnie opisane procedury odtwarzania danych po

awarii!

76informatyka +

Agenda







77informatyka +

Podsumowanie• Baza danych to nie tylko zbiór tabel• Istnieje wiele mechanizmów wewnątrz bazy danych,

które służą zapewnieniu spójności danych, definiowaniu różnego rodzaju ograniczeń, implementowaniu złożonej logiki aplikacji itp.

• Warto te mechanizmy stosować, gdyż takie podejście skutkuje zwykle wyższą wydajnością aplikacji oraz wyższym poziomem bezpieczeństwa danych.

• Możliwości drzemiące w mechanizmach bazy danych są wystarczające, żeby projektować bazy „hermetyczne” i „idiotoodporne” w postaci czarnej skrzynki, która udostępnia na zewnątrz tylko listę operacji (procedur składowanych)

• Warto zapoznać się z tymi mechanizmami praktycznie!

78informatyka +

KONIEC

79informatyka +

… czy są jakieś pytania?

Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy”

Documents

Transcript of Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy”