Na samym wstępie projektu chciałbym przybliżyć definicję bazy danych. Składa się ona z różnych elementów. Najważniejszymi z nich jest rekord podzielony na kilka pól, w których są przechowywane informacje poszczególnych kategorii. Na przykład w książce adresowej każdy rekord to zbiór informacji na temat jednej osoby. Składa się ona z kilku pól przechowujących takie informacje, jak: imię, nazwisko, adres, numer telefonu. W każdym polu zapisywane są dane oddzielonej kategorii. Dzięki temu komputerowe bazy danych umożliwiają szybkie sortowanie rekordów według poszczególnych kategorii lub wyszukiwanie informacji w obrębie tylko wybranych pól.

Jest to bardzo wygodny sposób przechowywania, dostępu i operowania na zbiorach danych - od bardzo małych (kilka rekordów w jednej tabeli) do baz danych zawierających setki tabel i miliony rekordów. Większość baz danych ma tą cechę, iż zazwyczaj dopisywanie są do niej nowe dane, natomiast usuwanych danych jest bardzo niewiele. Z tego też powodu w pewnym momencie możemy stwierdzić, że nasza baza danych bardzo wolno się ładuje . Z tego też względu warto zadbać o odpowiednie zaprojektowanie bazy danych oraz operacji wykonywanych na niej, tak aby jak najmniej obciążały one serwer, oraz aby dane nie zajmowały aż tyle cennego miejsca. Najprostszym rozwiązaniem byłoby zakupienie wydajniejszego serwera bądź większego dysku. To rozwiązanie ma jednak jedną a jakże ważną wadę – koszty. Zatem musimy znaleźć nieco tańsze i lepsze rozwiązanie. Jednym z takich jest wykonanie optymalizacji naszej bazy danych.

Normalizacja danych

Błędem popełnianym przez wielu niedoświadczonych, rokujących projektantów baz danych jest wrzucanie danych do jednej tabeli. Poniżej przedstawiam przykładowo zaprojektowaną tabelę:

Oceny studentów

Nasza zaprojektowana tabela ma bardzo wiele wad:

• te same dane powtarzają się w wielu miejscach (np. adres), przez co niepotrzebnie zajmują miejsce na dysku;
• gdy zajdzie konieczność aktualizacji danych, potrzeba będzie je zmienić w wielu miejscach (np. zmiana adresu po przeprowadzce);
• nie ma możliwości wpisania tylko części danych (np. tylko danych osobowych);
• przy usuwaniu oceny usuniemy też przy okazji dane o studencie.

Dodatkowym problemem jest to, że dane o studencie (imię i nazwisko, oraz adres) nie są rozdzielone na wiele kolumn, co może w przyszłości spowodować problemy z wyszukiwaniem określonych danych, np. osób o nazwisku Czerw. Dodatkowo ze względu na błędy podczas wpisywania danych, czy stosowanie różnych sposobów zapisu np. adresu, przez co automatyczny podział tych danych może okazać się niemożliwy. Przykładowo adres Łukasza Czerwa może być zapisany następująco:

• ul. Przemysłowa 1/5, 39-510 Rzeszów (wersja poprawna)
• ul. Przemyslowa 1/5, 39-510 Rzeszow (bez polskich znaków)
• ul. Przemysłowa 1/5. 39-510 Rzeszów (kropka zamiast przecinka)
• ul. Przemysłowa 1/5 39-510 Rzeszów (brak przecinka)
• 39-510 Rzeszów ul. Przemysłowa 1/5 (zamieniona ulica i miejscowość)

To jest tylko mały przykład tego jak można zapisać w różny sposób te same dane, w praktyce uniemożliwiając ich automatyczną analizę. Szczególnie dotkliwie można to odczuć gdy kiedyś będzie trzeba to zrobić - wtedy bez przeglądania wszystkich danych i ręcznego ich poprawiania się nie obejdzie. Z tego też powodu warto już na etapie projektowania bazy danych zadbać o odpowiednie rozdzielenie tych danych na kolumny. Z tego powodu nasza tabela mogłaby wyglądać następująco:

Studenci

Modyfikując tabelę w ten sposób nie będzie już żadnych problemów z wyszukiwaniem dowolnych danych, np., osób o nazwisku Michalski zamieszkały w Krośnie. Wciąż jednak istnieje problem z danymi, które się powtarzają. W tym celu właśnie dokonuje się normalizacji danych.

Zanim jednak przejdę do normalizacji, chciałbym przybliżyć dwa bardzo ważne pojęcia związane z bazami danych:

• Klucz główny - kolumna (lub grupa kolumn), których wartości są unikalne dla każdego rekordu znajdującego się w tabeli.
• Klucz obcy - klucz w tabeli, który odwołuje się do klucza głównego w innej tabeli.

Aby podzielić naszą tabelę na kilka mniejszych, połączonych pewnymi relacjami, warto przyjrzeć się, które dane się powtarzają. W naszym przykładzie powtarzającymi danymi są dane osobowe studentów.

Studenci

Oceny

Po takiej zmianie dane osobowe i adresowe już się nie powtarzają. A dostęp do danych o ocenach studenta również jest bardzo prosty – sprawdzamy IdStudenta. Niniejszy sposób możemy stosować wielokrotnie, aby dokonać dalszej optymalizacji. Możemy zauważyć, że w tabeli Oceny nazwa przedmiotu się powtarza. Warto ją wydzielić do osobnej tabeli, również ze względu iż lista przedmiotów jest przeważnie niezmienna, co najwyżej dojdą nowe. Wydzielmy zatem nazwy przedmiotów do osobnej tabeli. Po tej modyfikacji utworzyły się następujące tabele:

Studenci

Przedmioty

Oceny

Zmiana tego typu ma pewną szczególną zaletę, IDPrzedmiotu jest liczbą, więc porównywanie liczb jest zdecydowanie szybsze aniżeli porównywanie ciągów znaków.

Podczas zmiany struktury bazy danych warto się zastanowić czy takie wartości jak średnia ocen, itp. powinny się znaleźć w bazie, czy też raczej lepiej będzie wyliczać je gdy będą one faktycznie potrzebne.

Zastosowanie indeksów

W bibliotece mamy do czynienia ze specjalnymi katalogami książek, które służą do bardzo szybkiego wyszukania wybranej pozycji. Mogą być one poukładane na różne sposoby – alfabetycznie według autora lub tytułu książki. Po odszukaniu książki w katalogu, w łatwy sposób możemy odczytać numer regału i półki gdzie ona się znajduje.

W bardzo podobny sposób działa zasada indeksów w bazie danych. Pozwalają one na szybkie odszukanie wymaganych danych, co może doprowadzić do tego, że zapytania będą się wykonywać znacznie szybciej.

Indeksy możemy podzielić na zakładane na jedną kolumnę lub na wiele kolumn równocześnie. Innym podziałem jest podział na indeksy unikalne (każda wartość w indeksie z indeksem musi być inna – stosujemy je do zapewnienia unikalności kluczy głównych), oraz tzw. „normalne” (innymi słowy nie unikalne – wartości w indeksie mogą się powtarzać). Komendy do tworzenia indeksów mogą się różnić w zależności na jakim oprogramowaniu baz danych pracujemy.

Chciałbym przedstawić kilka przykładów komend dla MySQL. Na każdym przykładzie pokażę jak utworzyć dwa rodzaje indeksów: unikalny i zwykły. W poniżej podanych przykładach tabela ma 2 kolumny typu INT – o nazwach column1 i column2. Nasze indeksy nazywają się odpowiednio: unikalny i zwykly. Podawanie nazw indeksów nie jest obowiązkowe – baza danych sama utworzy nazwy. Jednakże warto je podawać, żeby łatwiej się zorientować, który indeks do czego służy.

Tworzenie indeksów przy tworzeniu tabeli

CREATE TABLE nazwa_tabeli (

column1 INT,

column2 INT,

UNIQUE unikalny (column1),

INDEX zwykly (column2)

)

Tworzenie indeksów w istniejącej tabeli

ALTER TABLE nazwa_tabeli

ADD UNIQUE unikalny (column1),

ADD INDEX zwykly (column2)

CREATE INDEX indeks_imie ON osoby (imie)

CREATE INDEX indeks_nazw ON osoby (nazwisko)

Istnieje także opcja utworzenia indeksów na kilku kolumnach jednocześnie

CREATE INDEX indeks_imie_nazw ON osoby (imie, nazwisko)

Jeden indeks nałożony na dwie kolumny jednocześnie

SELECT * FROM osoby WHERE imie='Jan' AND nazwisko='Kowalski'

Jednakże pomiędzy tymi dwoma metodami tworzenia indeksów istnieje zasadnicza różnica, gdy zapytanie wybierające dane posiada warunki odwołujące się do kilku kolumn z indeksami. Przykładowo możemy użyć takiego zapytania:

W przypadku gdy tabela posiada dwa indeksy, każdy na pojedynczej kolumnie, baza danych wykona to zapytanie następująco:

• Wyszuka wszystkie rekordy - imie = Jan;
• Wyszuka wszystkie rekordy - nazwisko = Kowalski;
• Obliczy część wspólną zbiorów rekordów z pierwszego i drugiego kroku, i zwróci ją jako wynik zapytania.

W przypadku, gdy natomiast na tabeli jest nałożony indeks na kolumnach (imie, nazwisko), baza danych może wyszukać potrzebne dane w jednym kroku, równocześnie sprawdzając wartości w polach imie i nazwisko.

Warto także wiedzieć, iż indeks nałożony na kilku kolumnach zostanie użyty nie tylko, gdy warunek zawiera wszystkie kolumny indeksu, ale również dla warunków zawierających dowolną ilość kolumn z lewej strony indeksu. Np. indeks na kolumnach (imie, nazwisko, miejscowosc) zostanie użyty dla następujących kombinacji kolumn występujących w warunku wyszukiwania:

• imie, nazwisko, miejscowosc
• imie, nazwisko
• imie

Kolejność warunków nie ma tu żadnego znaczenia - baza danych sama je sobie odpowiednio przestawi aby pasowały do indeksu.

Należy pamiętać także że każdy indeks zajmuje pewną ilość miejsca na dysku, zatem nie warto tworzyć indeksów na każdej kolumnie w bazie. Zamiast tego należy przeanalizować zapytania które są wykonywane, i na podstawie tego podjąć decyzję gdzie i jakie indeksy utworzyć. Na pewno indeksy należy utworzyć dla kluczy głównych (unikalne) oraz dla kluczy obcych, gdyż po tych polach zazwyczaj często się wyszukuje.

Optymalizacja zapytań

Aby przyśpieszyć wykonywanie zapytań, należy je zmodyfikować tak aby pracowały na jak najmniejszej ilości danych. Pierwszą rzeczą którą warto zrobić jest przerobienie wszystkich zapytań typu SELECT * na zapytania zawierające listę kolumn, które tak naprawdę są tylko potrzebne. Zatem zamiast czegoś takiego:

SELECT * FROM osoby WHERE imie='Jan' AND nazwisko='Kowalski'

należy zastosować coś takiego:

SELECT id_osoby, imie, nazwisko FROM osoby WHERE imie='Jan' AND nazwisko='Kowalski'

W ten sposób wybieramy tylko dane z trzech kolumn, zamiast np. z dziesięciu.

Drugą możliwością optymalizacji to filtrowanie wybieranych danych już w bazie danych, za pomocą klauzuli WHERE. Należy pamiętać także, że instrukcja SELECT może równocześnie wybierać dane z wielu tabel, np.:

SELECT imie, nazwisko, przedmiot, ocena

FROM studenci, oceny

WHERE studenci.id_studenta = oceny.id_studenta

AND imie='Jan' AND nazwisko='Kowalski'

Jest to znacznie lepsze niż niezależne wybranie danych z kilku tabel, i ich późniejsze przetwarzanie w skrypcie przed wyświetleniem.

SELECT COUNT(*) FROM osoby WHERE imie='Jan' AND nazwisko='Kowalski'

Należy pamiętać także że baza danych może dokonywać różnych obliczeń. Do najprostszych należy obliczanie liczby rekordów. Zatem zamiast pobierania wszystkich rekordów i późniejszego ich liczenia w skrypcie, warto poprosić bazę aby podała gotowy wynik:

Możliwe są także bardziej złożone obliczenia, zwłaszcza gdy użyjemy możliwości grupowania rekordów w zapytaniach.