WOJSKOWA AKADEMIA TECHNICZNA

LABOLATORIUM Z PRZEDMIOTU

SYSTEMY BAZ DANYCH

Temat: Optymalizacja

Prowadzący: mgr inż. Katarzyna WITAN

Wykonał: Mateusz SUCHOCKI

Marcin WARAKSA

Data: 24.05.2012r.

Zadanie projektowe:

1. Sprecyzować zakres tematyczny dla bazy danych i opracować jej schemat:

   1. Maksymalnie 5 tabel

   2. Każda tabela powinna zawierać minimum 700 rekordów

2. Opracować strukturę dla powyższej bazy danych i przeprowadzić działania optymalizacyjne:

   1. Normalizacji (1 przykład)

   2. Denormalizacji (1 przykład)

   3. Rozmieszczenie indeksów (2 przykład)

3. Opracować zapytania SQL i przeprowadzić ich optymalizację w kontekście:

   1. Użycia indeksów (2 przykład)

   2. Restrukturyzacji zapytania (2 przykład)

4. Opracować zapytania zawierające poniższe argumenty wyszukiwania, oraz zaproponować ich wydajniejsze odpowiedniki:

   1. Like(1 przykład)

   2. Between(1 przykład)

   3. In(1 przykład)

Rozwiązanie

1. Model bazy danych

Pierwszy etap zadania projektowego polegał na zdefiniowaniu zakresu tematycznego bazy danych jak również opracowaniu jej schematu.

Zaprojektowana baza danych zawiera dane na temat zwierząt przebywających w zoo oraz dane o przydzielonych im opiekunach, karmie jaką powinny być karmione, oraz wybiegu do jakiego są przydzielone. Baza składa się z pięciu tabel:

• ZWIERZE –zawiera dane na temat zwierząt (gromada, gatunek, imię, płeć itp.)

• OPIEKUN- zawiera dane na temat opiekunów (imię, nazwisko, itp.)

• WYBIEG- zawiera dane na temat dostępnych wybiegów (miejsce, typ, wielkość)

• KARMA- zawiera dane na temat karmy dla zwierząt (nazwa, rodzaj)

• ZWIERZ_KARMA- zawiera dane o ilości danej kramy przydzielonej dla konkretnego zwierzęcia

Na poniższym rysunku został przedstawiony schemat zaprojektowanej bazy danych „zooDB”:

Rysunek 1.1 Schemat bazy danych „zooDB”

1. Wypełnienie bazy

Kolejnym etapem projektu było wypełnienie utworzonej bazy danych „zooDB” przykładowymi danymi. Każda tabela z bazy „zooDB” zawiera po 1000 rekordów. Do wygenerowania losowych danych posłużono się narzędziem ze strony www.generatordanych.testerzy.pl, które dzięki prostemu interfejsowi umożliwia prostą generacje danych w formacie SQL.

Na poniższym rysunku przedstawiony został wynik zapytania SQL, który obrazuje liczbę rekordów dla poszczególnych tabel.

Rysunek 2.1 Wynik zapytania SQL dla liczby rekordów poszczególnych tabel

1. Zmiana struktury bazy danych

   1. Normalizacja

Następnym etapem było wykonanie procesu normalizacji na wytworzonej bazie danych. Głównym celem procesu normalizacji jest eliminacja powtarzających się danych w relacyjnej bazie danych, co zwiększa gwarancję jej prawidłowego działania.

Tak więc dla naszego schematu został wykonany proces normalizacji poprzez rozbicie tabeli „ZWIERZE”, która zawierała dane redundantne. Dane te odnoszą się do systematyki zwierząt, która może się powtarzać dla danego gatunku zwierzęcia. Po wykonaniu tej operacji powstała nowa tabela „SYSTEMATYKA_ZWIERZ”, zawierająca dane na temat systematyki zwierząt.

Poniższy rysunek przedstawia schemat bazy danych „zoo_normalizacjaDB”, która powstała na skutek procesu normalizacji bazy danych „zooDB”.

Rysunek 3.1 Schemat bazy danych po procesie normalizacji

1. Denormalizacja

Ta faza realizacji zadania polegała na wykonaniu procesu odwrotnego do procesu normalizacji czyli denormalizacji bazy danych. Proces ten jest odwrotny do procesu normalizacji tak więc denormalizacja będzie polegała na połączeniu tabel w taki sposób, aby przechowywane dane w tabeli były redundantne. Pozwala to na przyśpieszenie wykonania operacji poprzez unikanie kosztownych operacji łączenia tabel.

W zaprojektowanym schemacie bazy danych „zooDB” dane na temat wybiegu z tabeli „WYBIEG” zostały dodane do tabeli „ZWIERZE”, w ten sposób została wykonana kontrolowana nadmierność danych w bazie.

Na rysunku poniżej został umieszczony schemat bazy danych „zoo_denormalizacjaDB” , która powstała po przeprowadzeniu procesu denormalizacji na bazie danych „zooDB”.

Rysunek 3.2 Schemat bazy danych po procesie denormalizacji

1. Rozmieszczenie indeksów

Domyślnie przy tworzeniu bazy danych dla każdego klucza głównego oraz kluczy obcych tworzone są indeksy. Dzięki ustawionym indeksom możliwe jest szybsze wykonywanie niektórych zapytań na bazie danych. Tak więc jednym z ważniejszych etapów konstruowania struktury bazy danych jest rozmieszczenie dodatkowych indeksów.

W zaprojektowanej bazie danych „zooDB” zakłada się, iż zapytania często będą się odwoływać do nazwisk opiekunów. Z tego powodu dobrym rozwiązaniem jest umieszczenie indeksu „OPIEKUN_NAZWISKO” na kolumnie „NAZWISKO” w tabeli „OPIEKUN”. Usprawni to przeszukiwanie bazy danych pod kątem nazwisk.

Rysunek 3.3 Obraz przedstawiający ustawione indeksy na tabeli „OPIEKUN”

Jednym z założeń jest to, że w bazie danych będą częste operacje wyszukiwania zwierząt po gatunku. Tak więc podobnie jak w poprzednim przypadku ustawiony został indeks „ZWIERZE_GATUNEK” na kolumnie „GATUNEK” w tabeli „ZWIERZE”.

Rysunek 3.4 Obraz przedstawiający ustawione indeksy na tabeli „ZWIERZE”

1. Zapytania SQL

   1. Użycie indeksów

Na poniższym rysunku widoczne jest zaprojektowane zapytanie oraz query plan wykorzystywany po jego uruchomieniu.

Rysunek 4.1 Zaprojektowane zapytanie oraz plan jego wykonania.

Sortowanie nazwisk na końcu zapytania spowalnia wykonywanie zapytania. W celu optymalizacji wykonywania zapytania stworzony został indeks w tabeli „OPIEKUN” w kolumnie „NAZWISKO”, rysunek 4.2.

Rysunek 4.2 Zaprojektowane zapytanie oraz plan jego wykonania po dodaniu indeksu.

Plan wykonania zapytania znacząco się skrócił, ponieważ wyniki zapytania nie muszą być sortowane (po nazwiskach opiekunów), wykorzystywane są w tym celu indeksy.

Kolejne zaprojektowane zapytanie wykorzystuje tabele „ZWIERZE” i „ZWIERZ_KARMA”, na końcu zapytania występuje wyrażenie warunkowe które wymusza znalezienie w kolumnie „ILOŚĆ” w tabeli „ZWIERZ_KARMA” wartości większych od 70. Przeszukiwanie tabeli znacząco zwiększa czas wykonania się zapytania. Na poniższym rysunku widoczne jest zapytanie oraz plan jego wykonania.

Rys 4.3 Zaprojektowane zapytanie oraz plan jego wykonania.

Dodanie indeksu w tabeli „ZWIERZ_KARMA” na kolumnie „ILOŚĆ” znacząco przyspiesza wykonanie zapytania, ponieważ dzięki indeksom porównywanie wartości nie wymaga przeszukania całej tabeli.

Rysunek 4.4 Zaprojektowane zapytanie oraz plan jego wykonania po dodaniu indeksu.

Na rysunku 4.4 widoczne jest zaprojektowane zapytanie oraz plan jego wykonania. Należy zwrócić uwagę, że w końcowym etapie wykonania zapytania pojawił się napis „Scan zk using index ZWIERZ_KARMA_ILOSC”. Oznacza to że zapytanie wykorzystuje dodany wcześniej indeks.

1. Restrukturyzacja treści zapytania

Kolejnym zadaniem było opracowanie dwóch zapytań oraz przeprowadzenie ich optymalizacji poprzez ich restrukturyzację. Pierwsze zapytanie przed restrukturyzacją powoduje wylistowanie wszystkich informacji o zwierzętach, które nie są z gromady ssaków. Wyglądało ono następująco:

SELECT *

FROM ZWIERZE Z

WHERE Z.GROMADA NOT IN ('ssaki')

Po wyrażeniu SELECT występuje gwiazdka, co powoduje pobranie nazw kolumn z informacji o bazie, zajmuje to niepotrzebnie czas. Lepszym rozwiązaniem jest wypisanie wszystkich potrzebnych nazw kolumn. Warunek WHERE zawiera wyrażenie NOT IN, można go zastąpić wydajniejszym wyrażeniem EXISTS. W ten sposób zrestrukturyzowano zapytanie, wygląda ono następująco:

SELECT Z.GROMADA, Z.ID_ZWIERZ, Z.GATUNEK, Z.IMIE, Z.DATA_UR, Z.PLEC, Z.PLEC, Z.CECHA_GAT, Z.ID_WYBIEGU, Z.ID_OPIEKUNA

FROM ZWIERZE Z

WHERE NOT EXISTS

( SELECT ZW.GROMADA

FROM ZWIERZE ZW

WHERE ZW.GROMADA = 'ssaki' AND Z.ID_ZWIERZ = ZW.ID_ZWIERZ)

Wywołanie drugiego opracowanego zapytania powoduje wylistowanie pracowników oraz liczby zwierząt którymi się opiekują, przedstawia się ono następująco:

SELECT DISTINCT(O.NAZWISKO) 'NAZWISKO OPIEKUNA',

(SELECT COUNT(*)

FROM OPIEKUN OP, ZWIERZE Z

WHERE OP.ID_OPIEKUNA=Z.ID_OPIEKUNA

AND OP.ID_OPIEKUNA=O.ID_OPIEKUNA)

FROM OPIEKUN O

Występuje w nim funkcja COUNT() z argumentem „*”, wydajniejsze okazuje się być wywołanie jej z argumentem „1”. Do pogrupowania wyników użyto funkcji DISTINCT, jednak efektywniejszym rozwiązaniem jest użycie wyrażenia GROUP BY. Po restrukturyzacji zapytanie wygląda następująco:

SELECT O.NAZWISKO 'NAZWISKO OPIEKUNA',

(SELECT COUNT(1)

FROM OPIEKUN OP, ZWIERZE Z

WHERE OP.ID_OPIEKUNA=Z.ID_OPIEKUNA

AND OP.ID_OPIEKUNA=O.ID_OPIEKUNA)

FROM OPIEKUN O

GROUP BY O.ID_OPIEKUNA, O.NAZWISKO

1. Zapytania z argumentami LIKE, BEETWEN, IN i ich wydajniejsze odpowiedniki

   1. LIKE

Zapytanie wykorzystujące funkcje „LIKE” którego wynikiem jest imię, gatunek i płeć zwierząt, których nazwa gatunku zaczyna się od liter „KROKO” :

SELECT Z.IMIE, Z.GATUNEK, Z.PLEC

FROM ZWIERZE Z

WHERE GATUNEK LIKE'KROKO%'

Wykorzystanie funkcji LIKE okazuje się być wolniejsze niż użycie funkcji SUBSTR, która wycina podane ciągi znaków z rekordów wskazanej kolumny:

SELECT Z.IMIE, Z.GATUNEK, Z.PLEC

FROM ZWIERZE Z

WHERE SUBSTR(Z.GATUNEK,1,5)='KROKO'

1. BEETWEN

Zapytanie wykorzystujące funkcje „BEETWEN”, którego wynikiem jest gromada, gatunek zwierząt, których numer wybiegu zawiera się w przedziale od 100 do 400.

SELECT Z.GROMADA, Z.GATUNEK, Z.IMIE, W.ID_WYBIEGU

FROM ZWIERZE Z, WYBIEG W

WHERE Z.ID_WYBIEGU = W.ID_WYBIEGU AND W.ID_WYBIEGU BETWEEN 100 AND 400

Wydajniejszym odpowiednikiem jest użycie dwóch warunków porównania (>=, <=) zamiast wyrażenia BETWEEN:

SELECT Z.GROMADA, Z.GATUNEK, Z.IMIE, W.ID_WYBIEGU

FROM ZWIERZE Z, WYBIEG W

WHERE Z.ID_WYBIEGU = W.ID_WYBIEGU AND W.ID_WYBIEGU >=100 AND W.ID_WYBIEGU <=400

1. In

Zapytanie wykorzystujące funkcje IN, którego wynikiem jest lista pracowników którzy są opiekunami dla przynajmniej jednego zwierzaka:

SELECT O.ID_OPIEKUNA, O.IMIE, O.NAZWISKO

FROM OPIEKUN O

WHERE O.ID_OPIEKUNA IN

( SELECT Z.ID_OPIEKUNA

FROM ZWIERZE Z

WHERE Z.ID_OPIEKUNA = O.ID_OPIEKUNA)

Zamiast operatora IN wydajniejsze jest użycie operatora EXISTS. Zmienione zapytanie wygląda następująco:

SELECT O.ID_OPIEKUNA, O.IMIE, O.NAZWISKO

FROM OPIEKUN O

WHERE EXISTS

( SELECT Z.ID_OPIEKUNA

FROM ZWIERZE Z

WHERE Z.ID_OPIEKUNA = O.ID_OPIEKUNA)