slide1
Download
Skip this Video
Download Presentation
Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy”

Loading in 2 Seconds...

play fullscreen
1 / 81

Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy” - PowerPoint PPT Presentation


  • 110 Views
  • Uploaded on

Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy”. Na przykładzie SQL Server 2008. Agenda. Więzy integralności referencyjnej Transakcje Poziomy izolacji transakcji Wyzwalacze Rodzaje wyzwalaczy Procedury składowane i funkcje użytkownika Indeksy

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Mechanizmy wewnętrzne baz danych – czyli co w bazach „piszczy”' - idalia


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
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

informatyka +

agenda
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

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

4

wi zy integralno ci referencyjnej
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 +

5

wi zy integralno ci referencyjnej1
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 +

6

wi zy integralno ci referencyjnej2
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 +

7

wi zy integralno ci referencyjnej3
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 +

8

wi zy integralno ci referencyjnej4
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 +

9

wi zy integralno ci referencyjnej5
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 +

10

wi zy integralno ci referencyjnej6
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 +

11

wi zy integralno ci referencyjnej7
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 +

12

wi zy integralno ci referencyjnej8
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 +

13

wi zy integralno ci referencyjnej9
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 +

14

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

15

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

16

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

17

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

18

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

19

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

20

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

21

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

22

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

23

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

24

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

25

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

26

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

27

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

28

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

29

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

30

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

31

wyzwalacze dml
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 +

32

wyzwalacze dml1
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 +

33

wyzwalacze dml2
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 +

34

wyzwalacze dml3
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 +

35

wyzwalacze dml4
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 +

36

wyzwalacze dml5
Wyzwalacze DML

TAK

  • Ważne! Nie należy zakładać, że wyzwalacz będzie wywoływany zawsze dla modyfikacji pojedynczego wiersza!

NIE

informatyka +

37

wyzwalacze ddl
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 +

38

wyzwalacze ddl1
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 +

39

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

40

procedury sk adowane i funkcje u ytkownika
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 +

41

procedury sk adowane i funkcje u ytkownika1
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 +

42

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

informatyka +

43

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

informatyka +

44

procedury sk adowane i funkcje u ytkownika4
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 +

45

procedury sk adowane i funkcje u ytkownika5
Procedury składowane i funkcje użytkownika
  • Funkcja obliczająca saldo wskazanego rachunku:
  • Wywołanie:
  • Rezultat:

informatyka +

46

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

informatyka +

47

procedury sk adowane i funkcje u ytkownika7
Procedury składowane i funkcje użytkownika
  • Ta sama funkcja zrealizowana jako „multistatement”
  • Wywołanie:
  • Rezultat:

informatyka +

48

agenda7
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

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

50

fizyczna organizacja danych w sql server 2008
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 +

51

fizyczna organizacja danych w sql server 20081
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.
  • 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 +

52

fizyczna organizacja danych w sql server 20082
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

informatyka +

53

fizyczna organizacja danych w sql server 20083
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

informatyka +

54

fizyczna organizacja danych w sql server 20084
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 +

55

fizyczna organizacja danych w sql server 20085
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 +

56

fizyczna organizacja danych w sql server 20086
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

informatyka +

57

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

58

indeks zgrupowany
Indeks zgrupowany

korzeń

gałęzie

liście

informatyka +

59

indeks zgrupowany1
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
    • 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
  • 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 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 +

60

indeks niezgrupowany budowany na stercie
Indeks niezgrupowany (budowany na stercie)

korzeń

gałęzie

liście

sterta

informatyka +

61

indeks niezgrupowany budowany na stercie1
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 +

62

indeks niezgrupowany budowany na zgrupowanym
Indeks niezgrupowany (budowany na zgrupowanym)

korzeń

gałęzie

liście

Indeks zgrupowany

informatyka +

63

indeks niezgrupowany budowany na zgrupowanym1
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 +

64

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

65

wykonywanie zapyta
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 +

66

wykonywanie zapyta optymalizacja zapytania
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 +

67

optymalizacja zapyta wykorzystanie indeks w
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 +

68

wykorzystanie indeks w
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 (estimatedsubtreecost) : 2,1385

informatyka +

69

wykorzystanie indeks w1
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 +

70

wykorzystanie indeks w2
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 +

71

wykorzystanie indeks w3
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 :-)

Wcześniej było 2862 !

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

informatyka +

72

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

73

kopie bezpiecze stwa i odtwarzanie danych
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 +

74

kopie bezpiecze stwa i odtwarzanie danych1
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 +

75

kopie bezpiecze stwa i odtwarzanie danych2
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 +

76

kopie bezpiecze stwa i odtwarzanie danych3
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 +

77

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

78

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

79

slide80
KONIEC

… czy są jakieś pytania?

informatyka +

80

ad