Mechanizmy_wewnetrzn..

advertisement
Mechanizmy wewnętrzne baz danych –
czyli co w bazach
„piszczy”
Na przykładzie SQL Server 2008
informatyka +
1
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
informatyka +
2
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
informatyka +
3
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:
JAK
?
• Wymaganie: Rachunek ma dokładnie jednego właściciela.
informatyka +
4
Więzy integralności referencyjnej
• Dodajmy kilku klientów i zobaczmy ich dane:
• Rezultat:
• Mamy troje klientów o identyfikatorach 1, 2 i 3.
informatyka +
5
Więzy integralności referencyjnej
• Dodajmy teraz kilka rachunków i zobaczmy ich dane:
• Rezultat:
Nie ma
takiego
klienta!
• 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 :-(
informatyka +
6
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
Kolumna z
kluczem
podstawowym
lub unikalnym
Stworzone
ograniczenie –
klucz obcy
informatyka +
Kolumna tego
samego typu!
7
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 1
The 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.
informatyka +
8
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'.
informatyka +
9
Więzy integralności referencyjnej
• Nie ma natomiast problemu z dodaniem rachunku
dla istniejącego klienta:
Luka w numeracji –
ślad po nieudanej
próbie dodania
rachunku
• Rezultat:
informatyka +
10
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 :
UWAGA! Bardzo
– No action (domyślna)
• Nie podejmuje żadnej akcji.
wygodne i bardzo
niebezpiecznie!!!
– 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.
informatyka +
11
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!!!
informatyka +
12
Więzy integralności referencyjnej
Garść 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 !]
informatyka +
13
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
informatyka +
14
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) ???
informatyka +
15
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
informatyka +
16
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.
informatyka +
17
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
informatyka +
18
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.
informatyka +
19
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.
informatyka +
20
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
informatyka +
21
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ść!
informatyka +
22
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.
informatyka +
23
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]
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
• 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)
informatyka +
24
Transakcje
• Przy transakcjach warto wspomnieć o jeszcze jednym
negatywnym zjawisku – zakleszczeniu.
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.
informatyka +
25
Transakcje
• Minimalizowanie szansy na wystąpienie zakleszczenia
Czekam
na linijkę
Rysowanie wykresu:
- linijka
- kreda
Chwyciłem
linijkę 
Teraz tylko
kreda…
• Sięganie do zasobów wg tej samej kolejności!
informatyka +
26
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
informatyka +
27
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
informatyka +
28
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.
informatyka +
29
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
informatyka +
30
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.
informatyka +
31
Wyzwalacze DML
• Dodajmy do naszej bazy jeszcze jedną tabelę
• Będzie ona przechowywać informacje
o operacjach wykonywanych na
rachunku
• Wykorzystamy wyzwalacze do
zaimplementowania reguł biznesowych:
– Nie można usunąć ani zmodyfikować
raz wykonanej operacji
– Minimalna kwota wypłaty z rachunku musi
być większa lub równa 10 zł
informatyka +
32
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:
informatyka +
33
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:
Transakcja
została
wycofana
• Rezultat:
informatyka +
34
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.
informatyka +
35
Wyzwalacze DML
• Ważne! Nie należy zakładać, że wyzwalacz będzie
wywoływany zawsze dla modyfikacji pojedynczego
wiersza!
TAK
NIE
informatyka +
36
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
informatyka +
37
Wyzwalacze DDL
• Stwórzmy wyzwalacz DDL, który zablokuje wszelkie
modyfikacje tabel oraz próby ich usunięcia:
Tabela
testowa
Wyzwalacz
DDL
Próba
usunięcia
tabeli
Rezultat
usuwania
informatyka +
38
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
informatyka +
39
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
informatyka +
40
Procedury składowane i funkcje użytkownika
• 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
informatyka +
41
Procedury składowane i funkcje użytkownika
• Postać procedury zakładania promocyjnego konta:
informatyka +
42
Procedury składowane i funkcje użytkownika
• Wywołanie procedury:
• Rezultat:
• Sukces! Widać trzy udane wykonania polecenia insert
oraz wygenerowany numer rachunku
informatyka +
43
Procedury składowane i funkcje użytkownika
• 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)
informatyka +
44
Procedury składowane i funkcje użytkownika
• Funkcja obliczająca saldo wskazanego rachunku:
• Wywołanie:
• Rezultat:
informatyka +
45
Procedury składowane i funkcje użytkownika
• Funkcja tabelaryczna (inline) zwracająca n ostatnich
operacji wykonanych na rachunkach:
• Wywołanie:
• Rezultat:
informatyka +
46
Procedury składowane i funkcje użytkownika
• Ta sama funkcja zrealizowana jako „multistatement”
• Wywołanie:
• Rezultat:
informatyka +
47
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
informatyka +
48
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
informatyka +
49
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ść?
informatyka +
50
Fizyczna organizacja danych w SQL Server 2008
• 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.
Nagłówek
Wiersz 1
Wiersz 2
Wiersz 3
…
• 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!
informatyka +
51
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 )
•
Nagłówek
Nagłówek
– Mieszane (mixed extent)
Nagłówek
Wiersz 1
Nagłówek
• Zawierają strony należące do więcej niż jednego obiektu
Wiersz
1
Nagłówek
Wiersz
1
Nagłówek
Wiersz
Wiersz
1 22
Nagłówek
Wiersz
Alokowane i odczytywane są zawsze
Wiersz
1 2
Nagłówek
Wiersz
Wiersz
1
Wiersz
Wiersz
2 33
Wiersz
1
Wiersz
całe obszary a nie pojedyncze strony
Wiersz
2 3
Wiersz
1Wiersz
Wiersz
2
…3
Wiersz
Wiersz
2
…
Wiersz
Wiersz
2… 3 3
Wiersz
…3
Wiersz
…
Wiersz
… 3
…
…
informatyka +
52
Fizyczna organizacja danych w SQL Server 2008
• 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
Nagłówek
Nagłówek
Nagłówek
Nagłówek
Nagłówek
Nagłówek
Nagłówek
Nagłówek
Nagłówek
Nagłówek
Nagłówek
Nagłówek
Wiersz 1
Wiersz 1
Wiersz 1
Nagłówek
Nagłówek
Nagłówek
Wiersz 1
Nagłówek
Wiersz
1
Wiersz
1
Wiersz
1
Nagłówek
Nagłówek
Nagłówek
Wiersz
1
Nagłówek
Wiersz
1 2
Wiersz
1 2
Wiersz
1 2
Nagłówek
Nagłówek
Nagłówek
Wiersz
1
Wiersz
Wiersz
Wiersz
Nagłówek
Wiersz
1 2
Wiersz
1 2
Wiersz
1 2
Wiersz
Nagłówek
Nagłówek
Nagłówek
Wiersz
1 22
Wiersz
Wiersz
Wiersz
Nagłówek
Wiersz
1
Wiersz
1
Wiersz
1
Wiersz
Nagłówek
Wiersz
1 2
Wiersz
2 3 Nagłówek
Wiersz
2 3 Nagłówek
Wiersz
2 3 Nagłówek
Wiersz
1Wiersz
Wiersz
1Wiersz
Wiersz
1Wiersz
Wiersz
Wiersz
1
Wiersz
2
Wiersz
2
Wiersz
2
Wiersz
Wiersz
1Wiersz
Wiersz
1Wiersz
Wiersz
1Wiersz
Wiersz
2 33
3 Wiersz
3 Wiersz
3
Wiersz
1
…
Wiersz
2
Wiersz
2
Wiersz
2
Wiersz
Wiersz
1
31
51
Wiersz
2 3
Wiersz
Wiersz
Wiersz
Wiersz
4Wiersz
Wiersz
2… 3 3
Wiersz
2… 3 3
Wiersz
2… 3 3
Wiersz
2
Wiersz
Wiersz
Wiersz
…
Wiersz
2… 3
Wiersz
2… 3
Wiersz
2… 3
Wiersz
Wiersz
2… 3 3
Wiersz
Wiersz
Wiersz
Wiersz
6
Wiersz
72
Wiersz
32
Wiersz
…
…
…
Wiersz
98
Wiersz
Wiersz
Wiersz
…3
Wiersz
… 33
… 33
… 33
Wiersz
Wiersz
Wiersz
…
Wiersz
…
…
…
Wiersz
Wiersz
Wiersz
… 623
Wiersz
… 3
… 13
… 93
…
…
…
…
…
…
…
…
…
informatyka +
…
53
Fizyczna organizacja danych w SQL Server 2008
• Tabela może składać się z jednej lub więcej partycji
• Sterta jest tworzona osobno dla każdej partycji
Tabela
Partycja 1
Partycja 3
Partycja 2
informatyka +
54
Fizyczna organizacja danych w SQL Server 2008
• 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
IAM
informatyka +
55
Fizyczna organizacja danych w SQL Server 2008
• 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
sys.partitions
id
Index_id=0
IAM
informatyka +
56
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
informatyka +
57
Indeks zgrupowany
sys.partitions
id
Index_id=1
Root page
korzeń
gałęzie
liście
informatyka +
58
Indeks zgrupowany
•
•
•
•
•
•
Struktura drzewiasta (B-tree) – drzewo zrównoważone
Na poziomie korzenia i gałęzi – strony indeksu
Na poziomie liści – 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
–
•
Kiedy stosowanie jest szczególnie uzasadnione
–
–
–
–
•
Jeśli w tabeli występują dwie takie same wartości klucza, dodawana do nich jest losowa
liczba i taki klucz staje się wewnętrznie rozpoznawany jako unikalny
Operowanie na zakresach danych i danych grupowanych
Pobieranie danych w określonym porządku
Zapytania korzystające z wielu kolumn tabeli
Lepsza wydajność przy dodawaniu nowych wierszy
Na jakich kolumnach tworzyć indeks zgrupowany?
–
–
–
–
Mała długość
Wysoka selektywność (mało powtarzających się wartości klucza indeksu)
Rzadko bądź wcale nie zmieniane wartości
Wartości klucza dla kolejno dodawanych wierszy są rosnące
informatyka +
59
Indeks niezgrupowany (budowany na stercie)
sys.partitions
id
Index_id=2
Root page
korzeń
gałęzie
liście
sterta
informatyka +
60
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
informatyka +
61
Indeks niezgrupowany (budowany na zgrupowanym)
sys.partitions
id
Index_id=2
Root page
korzeń
gałęzie
liście
Indeks
zgrupowany
informatyka +
62
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)
informatyka +
63
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
informatyka +
64
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
informatyka +
65
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
informatyka +
66
Optymalizacja zapytań - wykorzystanie indeksów
• Zakładamy, że zapytania będą tworzone w oparciu o tabelę:
W celu zwiększenia
rozmiaru wiersza i
liczby stron:)
• Nie ma żadnych indeksów na tabeli Klienci
• Zapytanie, którym się zajmiemy jest proste:
informatyka +
67
Wykorzystanie indeksów
• Pierwsze wykonanie zapytania – plan wykonania
Brak indeksów –
skanowanie sterty
Pierwsze wykonanie:
strony pobierane z
dysku
Kolejne wykonania:
strony znajdują się w
cache
• Koszt zapytania (estimated subtree cost) : 2,1385
informatyka +
68
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)
• Wykonanie naszego zapytania po utworzeniu indeksu
przebiega według planu:
Stworzyliśmy indeks
zgrupowany, więc nie ma już
sterty.
• Koszt zapytania pozostał bez zmian : 2,1385
informatyka +
69
Wykorzystanie indeksów
• Spróbujmy teraz popracować nad wydajnością
• Stwórzmy indeks niezgrupowany na kolumnie, której
używamy jako kryterium wyszukiwania
• Skoro istnieje indeks na kolumnie Nazwisko, to powinien
zostać użyty do wyszukiwania? Sprawdźmy…
Nic z tego! Nasz indeks nie
został wykorzystany
• 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
informatyka +
70
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
• Wykonajmy kolejny raz nasze zapytanie
Sukces :-)
• Koszt wykonania: 0,0453
• Wcześniej było: 2,1385
informatyka +
Wcześniej
było 2862 !
71
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
informatyka +
72
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
informatyka +
73
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ą!
informatyka +
74
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)
informatyka +
75
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!
informatyka +
76
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
informatyka +
77
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!
informatyka +
78
KONIEC
… czy są jakieś pytania?
informatyka +
79
Download