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

1. Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy” Na przykładzie SQL Server 2008 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 + 2

3. 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

4. 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. JAK? informatyka + 4

5. 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

6. 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 :-( Nie ma takiego klienta! informatyka + 6

7. 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 Stworzone ograniczenie – klucz obcy Kolumna tego samego typu! Kolumna z kluczem podstawowym lub unikalnym informatyka + 7

8. 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

9. 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

10. Więzy integralności referencyjnej Luka w numeracji – ślad po nieudanej próbie dodania rachunku • Nie ma natomiast problemu z dodaniem rachunku dla istniejącego klienta: • Rezultat: informatyka + 10

11. 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 • Setnull • Ustawia wartość null jako wartość kolumn klucza obcego (działa jeżeli te kolumny dopuszczają wartość null!) • Setdefault • 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. UWAGA! Bardzo wygodne i bardzo niebezpiecznie!!! informatyka + 11

12. 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

13. 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

14. 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

15. 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

16. 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

17. 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

18. 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

19. 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

20. 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

21. 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

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): • 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

23. 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

24. 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) informatyka + 24

25. 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

26. Transakcje Chwyciłem linijkę  Teraz tylko kreda… • Minimalizowanie szansy na wystąpienie zakleszczenia • Sięganie do zasobów wg tej samej kolejności! Czekam na linijkę Rysowanie wykresu: - linijka - kreda informatyka + 26

27. 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

28. 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

29. 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

30. 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

31. 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

32. Wyzwalacze DML • Dodajmy do naszej bazy jeszcze jedną tabelę • Będzie ona przechowywać informacjeo 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ł informatyka + 32

33. 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

34. 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: Transakcja została wycofana informatyka + 34

35. 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

36. Wyzwalacze DML TAK • Ważne! Nie należy zakładać, że wyzwalacz będzie wywoływany zawsze dla modyfikacji pojedynczego wiersza! NIE informatyka + 36

37. 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

38. 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

39. 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

40. 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

41. 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

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

43. Procedury składowane i funkcje użytkownika • Wywołanie procedury: • Rezultat: • Sukces! Widać trzy udane wykonania polecenia insert oraz wygenerowany numer rachunku informatyka + 43

44. 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

45. Procedury składowane i funkcje użytkownika • Funkcja obliczająca saldo wskazanego rachunku: • Wywołanie: • Rezultat: informatyka + 45

46. Procedury składowane i funkcje użytkownika • Funkcja tabelaryczna (inline) zwracająca n ostatnich operacji wykonanych na rachunkach: • Wywołanie: • Rezultat: informatyka + 46

47. Procedury składowane i funkcje użytkownika • Ta sama funkcja zrealizowana jako „multistatement” • Wywołanie: • Rezultat: informatyka + 47

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 + 48

49. 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

50. 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