Konrad Sztumski Politechnika Częstochowska
konrad@ gmai l. com
Streszczenie
W rozdziale przedstawiono tematykę efektywności baz danych oraz systemów informatycznych zarządzania.
1. Uwagi w stęp ne
W ostatnich latach Informatyczne Systemy Zarządzania cieszą się bardzo dużą popularnością. Stąd rosnące zainteresowanie nimi przez różne firmy. Firmy, które chcą stworzyć odpowiednie oprogramowanie muszą brać pod uwagę zarówno aktualne trendy w branży informatycznej jak i wymagania, jakie stawiają im odbiorcy oprogramowania. Bowiem wszystkie programy przeznaczone do obsługi firmy muszą umożliwiać gromadzenie i przechowywanie danych. Producenci wykorzystują bazy danych przede wszystkim do gromadzenia informacji. W prawdzie ju ż od wielu lat bazy danych wykorzystywane są w Informatycznych Systemach Zarządzania, ale stale dokonujący się postęp w dziedzinie wiedzy z informatyki, doskonalenie silników bazodanowych i osprzętu, wzrost naszych możliwości oraz umiejętności korzystania z informatyki i z oprogramowania stwarzają coraz większe możliwości przyspieszenia rozbudowy i unowocześniania oprogramowań opartych na bazie danych. Często oprogramowanie potrzebne do zarządzania przedsiębiorstwem musi umożliwiać przechowywanie bardzo dużej liczby danych, które dają się przetwarzać przez system informatyczny. Dlatego baza danych musi działać efektywnie i szybko. Chodzi o to, by użytkownikowi końcowemu zapewnić jak najbardziej sprawny dostęp do danych. Szybkość
192 K. Sztumski wykonywania poszczególnych operacji zapewnić można na kilka sposobów pokazanych na poniższym rysunku.
Rys. 1. Czynniki wpływające na szybkość działania bazy danych
Z powyższego schematu widać, że na efektywne, szybkie działanie naszego systemu bazodanowego ma wpływ kilka czynników, za które w jednakowym stopniu odpowiadają: administrator bazy, projektant i programista. Postaram się bardziej szczegółowo przybliżyć i omówić te czynniki, jakim i powinni kierować się tak projektanci, jak i programiści.
• W ybór silnika bazodanowego.
Ważne jest dobranie odpowiedniego silnika bazodanowego do naszych potrzeb
i systemu operacyjnego oraz sprzętu, jakim będziemy dysponować, ze względu na to, że różne silniki bazodanowe wykazują różne zachowania w danych zastosowaniach.
• Optymalizacja działania systemu operacyjnego.
Polega ona na wyłączeniu niepotrzebnych procesów, modułów, instalacji i odpowiedniej konfiguracji firewalli programów antywirusowych. C h o d z i o to, by zadbać o ja k najlepszą wydajność komputera, na którym zainstalowana
jest baza
i silnik bazodanowy.
• Optymalizacja ze względu na wykorzystywany hardware następuje poprzez modyfikację sprzętu (wymianę na wydajniejszy sprzęt, co wymaga w y d a tk u
na zakup nowego sprzętu), dobór architektury i parametrów pracy podzespołów, stosownych dla określonego silnika bazodanowego, system operacyjny
i przewidywaną potrzebną moc obliczeniową, która daje możliwość obsługi zakładanej przez nas ilości informacji z uwzględnieniem o d p o w ie d n ie g o
zapasu.
• Optymalizacja programistyczna.
Przekłada się ona głównie na przyspieszeniu wyświetlania zapytań w języku SQL
Budowa efektywnie działającej bazy danych dla informatycznych sy ste m ó w . 193 z bazy i na wyborze odpowiedniego środowiska programistycznego (języka programowania), zawierającego interesujące nas biblioteki i komponenty, które umożliwiają i ułatwiają tworzenie oprogramowania współpracującego z bazą danych.
• Optymalizacja analityczna.
Po określeniu wymagań, jakie stawiane są przed oprogramowaniem, tworzy się projekt bazy danych oraz relacji pomiędzy poszczególnymi polami tabel.
• Do tego wszystkiego może dojść jeszcze odpowiednie ustawienie łącza internetowego (managerów pasma) lub sieci Ethernet w zależności od architektury bazy, od wymagań określonych przez użytkownika i od wymagań samego systemu, który pracuje na naszej bazie danych lub metody pozyskiwania przez aplikację danych z bazy.
W rozdziale zajmę się jedynie sposobami optymalizacji w aspekcie analitycznym
i programistycznym.
Pierwszym krokiem przy tworzeniu bazy danych jest zebranie danych funkcjonalnych od zamawiającego przedsiębiorstwa. Na ich podstawie należy stworzyć schemat relacji pomiędzy danymi obiektami będącymi odwzorowaniem rzeczywistych procesów biznesowych na relacje pomiędzy obiektami bazy. Schemat ten nazywa się diagramem związków encji.
Projektując diagram należy zwrócić uwagę na sposoby gromadzenia danych w tabelach. Aktualnie najpopularniejszym sposobem jest stosowanie relacyjnych baz danych. Cechują się tym, że sposób dostępu do danych i łączenia ich ze sobą jest taki, żeby użytkownik nie musiał widzieć, jak te dane pobrane zostały do
komputera[l],
2. N orm alizacja danych
Normalizacja danych jest to czynność mająca na celu zmianę układu danych i relacji pomiędzy nimi na takie, które będą eliminować efekty niespójności, redundancji (nadmiarowości), relacji wieloznacznych i problemów przy aktualizacji danych. Niedoświadczony projektant może utworzyć projekt w taki sposób, iż zgromadzi wszystkie obiekty w jednej bazie.
Tab. 1. Przykładowa tabela kontrahentów
Firma A d res P rod u k t Ilość
Hortex ul. Mszczonowska 2, 02-337 Warszawa Sok jabłkowy 500 Dar Naturv Domaniewska 41/7, 02-672 Warszawa Woda 400
Tymbark Tymbark 156, 34-650 Sok wiśniowy 155
Hortex Mszczonowska 2, 02-337 Warszawa Żurek 260 Dar Naturv ul. Domaniewska 41/7, 02-672 Warszawa Woda 320
Tymbark 34- 50 Tymbark 156 Sok wiśniowy 155
194 K. Sztumski Pola powyższej tabeli zostały zaprojektowane w nieodpowiedni sposób:
Dodanie kilku zamówień danemu kontrahentowi powoduje, że dane dotyczące firtny powtarzają się w wielu miejscach, a co za tym idzie, zajmują niepotrzebnie miejsce na dysku. Aby dodać zamówienie, trzeba ciągle podawać
komplet informacji
o kontrahencie. Dane teleadresowe powinny być przetrzymywane w osobnej tabeli,
a każdy ze składników adresu powinien stanowić osobne pole. Tutaj brak powoduje, że adres kontrahenta może zostać wpisany na różne sposoby lub z błędem znakowym. Natomiast jeden standardowy uchroni nas przed błędami.
Przy aktualizacji danych kontrahenta trzeba będzie aktualizować każdą krotkę zawierającą jego dane. Usuwając dane o zamówieniu, usuwamy dane kontrahenta. Pola „produkt” i „ilość” powinny zostać przeniesione do nowej tabeli, którą możemy sobie nazwać „zam ówienie”. Zanim jednak będziemy mieli możliwość znormalizowania powyższej bazy, potrzebne będzie jeszcze wprowadzenie terminu „klucz główny” i „klucz obcy”. Klucz główny - kolumna (lub grupa kolumn), zawierająca wartości unikalne dla każdego rekordu znajdującego się w tabeli. W podanej powyżej tabeli kluczem głównym mogłaby być np. para kolumn (Nazwisko, Przedmiot). Ta para wartości jest inna w każdym wierszu. W praktyce często stosuje się inne rozwiązanie. Do tabeli dodaje się dodatkową kolumnę, w której wartości zwiększają się o jeden automatycznie wraz z każdym dodanym rekordem (zazwyczaj typ danych to autonum ber lub autoinerement, ew. kolumna musi mieć nadany taki atrybut).
Klucz obcy - klucz w tabeli, który odwołuje się do klucza głównego w innej tabeli. Klucz obcy podobnie ja k i klucz główny może składać się z kliku kolumn. Dzięki cesze unikalności klucza głównego wymienionej powyżej, mamy pewność, że klucz obcy wskaże nam dokładnie jeden rekord w innej tabeli[2].
1 Wiersz tabeli
Budowa efektywnie działającej bazy danych dla informatycznych system ów . 195 Kontrahenci
nip Firm a Ulica N r dom u N r m ieszk M iejscow ość Kod
1 Hortex Mszczonowska 2 Warszawa 02-337
2 Dar Natury Domaniewska 41 7 Warszawa 02-672
3 Tymbark Tymbark 156 Tymbark 34-650
Rys 1. Propozycja poprawnie znormalizowanych tabel
Te tabele poprawiają nam wszystkie niedogodności wymienione wcześniej. W tabeli zamówienie identyfikatorami kontrahenta i produktu są liczby, co powoduje,
że można je szybciej porównywać. Można by się pokusić jeszcze np. na rozdzielenie w tabeli „kontrahenci firmy” i „adres”, aby ułatwić edycję.
Niestety, niekoniecznie może to być potrzebne lub wpłynąć korzystnie na szybkość wyszukiwania danych w bazie. Normalizacja ma wyodrębnionych pięć postaci teoretycznych i na postawie ich oraz wymagań funkcjonalnych tworzonej bazy musimy określić potrzebny nam jej stopień.
1- Pierwsza postać normalna IN F (ang. norm al fo rm ) - postać najsłabsza;
wymaga jedynie, aby dziedziny atrybutów były elementarne (nierozkładalne, atomowe, najprostsze), np. liczby całkowite, daty, łańcuchy, a nie np. listy liczb lub zbiory dat. Rekordy w INF są stałej długości.
T Druga postać normalna 2NF - jeżeli każdy atrybut Y, który nie jest kluczem zależy funkcyjnie od klucza (a nie od podzbioru atrybutów stanowiących klucz)
~ Po wyznaczeniu wszystkich zależności funkcyjnych.
3- Trzecia postać normalna 3NF - gdy nie istnieją żadne zależności przechodnie (nie-trywialne).
Postać normalna Boyce-Codda (najmocniejsza) - zależności funkcyjne muszą tniec następującą postać: jeżeli X —* A i atrybut A nie jest zawarty w X, to Y je st kluczem lub zawiera klucz.
196 K. Sztumski 5. Czwarta postać normalna 4N F - je ż e li zawsze wtedy, kiedy zbiór atrybutów X określa wartościowo Y, to zachodzi jeden z następujących warunków: Y jest puste lub zawiera się w X , suma zbiorów X i F je st pełnym zbiorem atrybutów lub, wreszcie, ^ z a w ie ra klucz.
6. Piąta postać normalna 5NF - jeżeli nie istnieje rozkład odwracalny na zbiór mniejszych tabel[3].
W praktyce nie zawsze potrzebne będzie dojście do 5NF. Zwykle normalizacja kończy się na 3NF ze względu na koszt czasu, jaki aplikacja musi poświęcić na przeszukanie bazy.
3. D en orm alizacja
Czasami przy bardzo dużej bazie danych i ogromie wykonywanych zapytań możemy przeprowadzić proces denormalizacji. Zmiana ta zaowocuje przyspieszeniem wykonywania się tych zapytań. Realizować ją najlepiej na dwa sposoby:
• poprzez zapis w bazie danych wartości wyliczonych na podstawie innych danych z bazy;
• poprzez zapis klucza obcego nie tylko w bezpośrednio powiązanej tabeli, ale także w "sąsiednich" tabelach[2].
4. Indeksy
Zwykle przeszukując dużą bazę danych z milionami rekordów czas oczekiwania na wykonanie podzapytania byłby bardzo długi. Dane z tabel Fizycznie zapisane są w postaci pliku z danymi na dysku. Pliki te czytane są blokami danych. Chcąc wybrać z tabeli szukane przez nas dane, nasz silnik bazodanowy zacznie czytać kolejne bloki pamięci. Zastosowanie indeksów na konkretnym polu sp o w o d u je,
że informacja o bloku pamięci, w którym można znaleźć interesujące nas dane zostanie zapisana do pliku. Indeksy stosuje się głownie przy wykonywaniu zapytań SELECT wyszukujących za pomocą W HERE konkretnych pól oraz przy złączeniach tabel[4]. Zatem nasuwa się pytanie: jak stworzyć indeks? W zależności od silnika bazodanowego i typu indeksu tworzy się je na różne sposoby. Nie jestem w stanie opisać wszystkich typów i implementacji przy użyciu silników bazodanowych. Podam tylko jeden przykład. Dla zaspokojenia ciekawości najlepiej sprawdzić to w dokumentacji używanego środowiska bazodanowego.
Przykładowo w środowisku Oracle służy do tego polecenie:
Budowa efektywnie działającej bazy danych dla informatycznych systemów . 197 CREATE INDEX nazwa_indexu ON nazwa_tabeli(pola)‘
Kiedy warto stosować indeksy?
Najlepsze efekty osiąga się przy wybieraniu małej liczby rekordów z dużego zbioru. Zazwyczaj przyjmuje się, że wtedy opłaca się stosować indeksy, gdy z tabeli czytamy nie więcej niż 15% rekordów. Minusem stosowania indeksów jest do trzech razy dłuższe modyfikowanie danych. Spowodowane jest to przez
aktualizację informacji w indeksach.
5. O ptym alizacja p od zap ytań SQ L
Do przyspieszenia wykonywania zapytań w SQL trzeba tak zmodyfikować polecenie SELECT, aby pracowało na możliwie najmniejszej ilości danych. Nie używajmy raczej
SELECT * from,
tylko określmy potrzebne nam pola. Sytuacje, kiedy potrzebujemy wyświetlić cały rekord z tabeli zdarzają się bardzo rzadko.
Innym sposobem jest filtrowanie danych zawartych ju ż w bazie danych. Za sprawą normalizacji często interesujące dane mamy rozproszone w kilku
tabelach. Szybsza
i wygodniejsza jest selekcja danych z kilku tabel naraz niż wybieranie z pojedynczych
i późniejsza ich obróbka. Za pomocą poleceń specyfikującego kryteria dobom wierszy WHERE. HAVING, ORDER BY możemy otrzymać ju ż interesujące
nas dane
zjednej lub większej ilości tabel (jak w przykładzie poniżej).
SELECT firma, ilosc
PROM kontrahenci, produkty
WHERE kontrachenci.nip=zamowienie.nip;
GROUP BY firma h a v i n g ¡1ość> 5 0 0 ;
Wybieramy z dwóch tabel nazwę firmy i ilość zamówionego towaru, Szukamy tych które zamawiają powyżej 500 sztuk i sortujemy wyniki po nazwach firm.
Przedstawiłem tutaj dość proste metody, które mogą się przydać na początkowym etapie pracy z bazami danych przy ich projektowaniu i Programowaniu. Zwracam jednak uwagę na to, że opisane tu metody nie zawsze
Jest wiele rodzajów indeksów w Oracle i do poznania wszystkich odsyłam na stronę
< http://www.cs.put.poznan.pl/bbebel/sbd_2/18Indeksy.pdf> lub stronę producenta.
198 K. Sztumski znajdują zastosowanie w jakim ś konkretnym przypadku i nie zawsze uda nam się optymalizować za pomocą wszystkich wymienionych tu metod.
LITERATURA
1. Hotka D.: Oracle 9i w przykładach. MIKOM, Warszawa 2003, s. 23 2. Internet:
http://www.poradnikwebmastera.com/artykuly/bazy_danych/optymalizacja_bazy_dany ch.php
3. Forkiewicz M.: http://www.zie.pg.gda.pl/md/bazy_danych/przykIady_nonnalizacja.pdf 4. Krokiewicz M.: Optymalizacja bazy danych, SDJ 9/2008, Warszawa 2008, s. 23.
5. Internet: http://www.zie.pg.gda.pl/md/bazy_danycli/przyklady_normalizacja.pdf 6. Krokiewicz M.: Optymalizacja bazy danych, Software Developer Journal 7. Bębel B.:http://www.cs.put.poznan.pl/bbebel/sbd_2/18Indeksy.pdf