Optymalizacja bazy danych

Wiele stron napisanych w PHP lub innym języku który jest uruchamiany po stronie serwera korzysta z baz danych. 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żesz stwierdzić (lub dostaniesz taką informację np. od swojego Administratora Serwera), że strony się wolno ładują i serwer jest mocno obciążony, lub że brakuje miejsca na dysku. 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 wydaje się zakup wydajniejszego serwera czy większego dysku, ew. kolejnych serwerów czy dysków. Do rozwiązanie ma jednak tą podstawową wadę, iż jest dość drogie. Znacznie tańsze jest wykonanie optymalizacji samej bazy danych - tą metodą można uzyskać niejednokrotnie lepsze rezultaty, które znacznie oddalą w czasie kolejne problemy z wydajnością czy miejscem na dysku.

W tym artykule omówię następujące zagadnienia:

• normalizacja bazy danych, czyli jak poprawnie zaprojektować tabele;
• indeksy i dlaczego ich stosowanie jest tak istotne dla kwestii wydajności;
• wykonywanie operacji na bazie danych i w bazie danych;
• praca z dużymi bazami danych i wieloma zapytaniami na sekundę, czyli kiedy denormalizacja bazy danych jest koniecznością.

Normalizacja bazy danych

Podstawowym błędem popełnianym przez wielu niedoświadczonych projektantów baz danych jest wrzucenie wszystkich danych do jednej tabeli. Zaprojektowana tabela zawierająca informacje o studentach i ich ocenach z przedmiotów może wyglądać następująco:

Tak zaprojektowane rozwiązanie ma wiele wad:

• te same dane powtarzają się w wielu miejscach (np. dane adresowe), przez co niepotrzebnie zajmują miejsce na dysku ;
• przy wpisywaniu danych zawsze musimy wpisać komplet danych, przez co dane mogą być niepoprawne (np. literówki);
• w przypadku gdy zajdzie konieczność aktualizacji danych, potrzeba będzie je zmienić w wielu miejscach (np. zmiana adresu po przeprowadzce);
• może się zdarzyć sytuacja że pewne dane które powinny być różne, będą nierozróżnialne (np. w bazie są tylko imiona i nazwiska osób, i trafi się dwóch Janów Kowalskich);
• 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 Kowalski. 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 Jana Kowalskiego może być zapisany następująco:

• ul. Fiołkowa 1/5, 12-345 Fiołkowo (wersja poprawna)
• ul. Fiolkowa 1/5, 12-345 Fiolkowo (bez polskich znaków)
• ul. Fiołkowa 1/5. 12-345 Fiołkowo (kropka zamiast przecinka)
• ul. Fiołkowa 1/5 12-345 Fiołkowo (brak przecinka)
• 12-345 Fiołkowo, ul. Fiołkowa 1/5 (zamieniona ulica i miejscowość)
• ul. Fiołkowa 1/5, Fiołkowo, 12-345 (kod na końcu)
• u. Fiołowa 1/5, 12-345 Fiłkowo (brakujące literki)
• ul. Fiołkowa 1 m.5, 12-345 Fiołkowo (inaczej zapisany nr mieszkania)

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:

Po modyfikacji tabeli w ten sposób nie będzie już żadnych problemów z wyszukiwaniem dowolnych danych, np. osób o nazwisku Mucha zamieszkałych w Portach Mniejszych. Wciąż jednak jest problem z danymi które się powtarzają. W tym celu właśnie dokonuje się normalizacji bazy danych. Teoria baz danych wyróżnia 5 tzw. postaci normalnych. Baza jak powyżej jest w 1-szej postaci normalnej. Każda kolejna postać normalna jest "ulepszeniem" postaci normalnych o niższych numerach. Od strony teoretycznej jest to wytłumaczone m.in. w materiałach do wykładu Bazy Danych autora Roberta Chwastka w rozdziale "Normalizacja relacji". Jest to opis teoretyczny, który dla wielu osób może być jednak niestrawny (pamiętaj jednak że jeżeli chcesz być dobrym informatykiem, musisz zaprzyjaźnić się z matematyką!). Dlatego też ja tutaj podam praktyczne wskazówki co należy zrobić.

Zanim to jednak zrobię, muszę wprowadzić kilka pojęć:

• Klucz główny - kolumna (lub grupa kolumn), których wartości są unikalne dla każdego rekordu znajdującego się w tabeli. W powyższej 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 się zwiększają o 1 automatycznie wraz z każdym dodanym rekordem (zazwyczaj typ danych to autonumber lub autoincrement, ew. kolumna musi mieć nadany taki atrybut);
• Klucz obcy - klucz w tabeli, który odwołuje się do klucza głównego w innej tabeli. Podobnie jak klucz główny, może składać się z kliku kolumn. Dzięki wymienionej powyżej cesze unikalności klucza głównego, mamy pewność że klucz obcy będzie wskazywał na dokładnie jeden rekord w innej tabeli.

Aby podzielić tabelę na kilka mniejszych, warto przyjrzeć się które dane się w niej powtarzają, i przenieść je do osobnej tabeli. W naszym przypadku są to dane osobowe i adresowe studentów. Zatem przenieśmy je do osobnej tabeli, i dodatkowo nadajmy każdemu studentowi unikalny identyfikator:

Po takiej zmianie dane osobowe i adresowe studentów są już tylko w jednym miejscu, przez co wymienione powyżej problemy już ich nie dotyczą. Dostęp do danych o ocenach studenta także jest prosty - wystarczy sprawdzić jakie jest IdStudenta, i odszukać wiersze z takim Id w tabeli Oceny. Analogicznie można też w drugą stronę, czyli sprawdzić komu została wystawiona dana ocena.

Podany sposób można (a nawet w praktyce trzeba) zastosować wiele razy, aby dokonać dalszej optymalizacji bazy danych. W naszym przypadku można zauważyć że nazwa przedmiotu się powtarza. Ją także warto wydzielić do osobnej tabeli, także z tego względu iż lista przedmiotów jest zazwyczaj niezmienna, co najwyżej mogą dojść nowe. Dodatkową zaletą posiadania tabeli z nazwami przedmiotów jest też to iż możliwe staje się wyświetlenie listy z której można wybrać przedmiot, zamiast pola tekstowego do wpisania jego nazwy. (W rzeczywistych systemach tabele zawierające tego typu dane, które są zwykle niezmienne, nazywają się "tabelami słownikowymi".) Wydzielmy zatem nazwy przedmiotów do osobnej tabeli - po tej modyfikacji tabela Studenci pozostanie taka sama, zmieni się natomiast tabela Oceny:

Zmiana tego typu ma jeszcze tą zaletę, iż identyfikator przedmiotu jest liczbą, a co za tym idzie, porównywanie liczb jest szybsze niż porównywanie ciągów znaków (aby np. odszukać oceny z matematyki, należy raz odszukać odpowiednie IdPrzemiotu, a potem już tylko porównywać liczby).

W podany sposób można także wydzielić nazwy miejscowości do innej tabeli. Do rozważenia mogą być także imiona (to może się przydać, jeżeli potrzebować będziesz kiedyś dokonać odmiany przez przypadki, np. Anna - Anno. To może być przydatne np. do tworzenia powitań w e-mailach, np. "Droga Anno...").

W pewnych przypadkach (np. w przypadku imion, czy nazw miejscowości) możesz zdecydować że chcesz je nadal przechowywać w tabeli, zamiast kluczy obcych prowadzących do innej tabeli. W takim przypadku tworzenie tabeli słownikowej też ma sens, ze względu na to iż użytkownik będzie mógł sobie wybrać wartość z listy zamiast wpisywać ją za każdym razem. W ten sposób można znacznie ograniczyć ilość np. literówek we wprowadzanych danych.

Podczas zmiany struktury bazy danych warto też zwrócić uwagę na pola, które zawierają wartości mogące zostać wyliczone na podstawie zawartości innych pól (np. suma kwot na fakturze, czy średnia ocen). Warto się zastanowić czy warto trzymać te wartości w bazie danych (zapisany jest gotowy wynik), czy też raczej lepiej będzie wyliczać je gdy będą potrzebne (oszczędność miejsca na dysku). O tym problemie wspomnę jeszcze na końcu artykułu, w części poświęconej denormalizacji bazy danych jako sposobowi na poprawę wydajności.

Stosowanie indeksów

W bibliotekach istnieją specjalne katalogi książek, służące do szybkiego odszukania wybranej pozycji. Mogą one być poukładane na różne sposoby, np. alfabetycznie wg autora lub tytułu książki. Po odszukaniu książki w katalogu, możesz odczytać nr regału i półki gdzie się książka znajduje, przez co musisz już tylko przejrzeć ksiązki na jednej półce (np. 30 sztuk) aby znaleźć tą potrzebną, zamiast wszystkich zgromadzonych książek (np. 30 tysięcy).

Na podobnej zasadzie działają indeksy w bazie danych - pozwalają na szybkie odszukanie wymaganych danych, przez co zapytania będą się wykonywać znacznie szybciej. Podam jeden przykład z którym miałem osobiście do czynienia: zapytanie wybierało dane z ponad 10 tabel, z których największa miała nieco ponad milion rekordów. Pracowało ono ponad 5 minut. Po założeniu indeksu na tej największej tabeli, czas wykonywania zapytania skrócił się do pół minuty (przyśpieszenie ponad 10 razy). Zatem jest o co walczyć :).

Indeksy mogą być różnych typów. Można je podzielić na indeksy zakładane na jedną kolumnę, lub na wiele kolumn równocześnie (to jest coś innego niż posiadanie wielu indeksów na pojedynczych kolumnach - więcej szczegółów później). Innym podziałem jest podział na indeksy unikalne (każda wartość w indeksie z indeksem musi być inna; jest to stosowane np. do zapewnienia unikalności kluczy głównych), oraz "normalne" (czyli nie unikalne - wartości w indeksie mogą się powtarzać. To jest najczęstszy przypadek).

Komenda do tworzenia indeksów może się różnić w zależności od bazy danych której używasz, dlatego odsyłam do dokumentacji. Poniżej podaję kilka przykładów komend dla MySQL. W każdym z przykładów pokażę jak utworzyć dwa rodzaje indeksów: unikalny i zwykły. W poniższych przykładach tabela ma dwie kolumny typu INT, o nazwach kolumna1 i kolumna2. Indeksy nazywają się odpowiednio indeks_unikalny i indeks_zwykly. Nazwy indeksów nie są obowiązkowe - jeżeli ich nie podasz, baza danych sama sobie utworzy nazwy. Warto jednak je nadawać, gdyż później łatwiej się zorientować który indeks do czego służy.

Tworzenie indeksów przy tworzeniu tabeli:

CREATE TABLE nazwa_tabeli (
kolumna1 INT,
kolumna2 INT,
UNIQUE indeks_unikalny (kolumna1),
INDEX indeks_zwykly (kolumna2)
)

Tworzenie indeksów w istniejącej tabeli:

ALTER TABLE nazwa_tabeli
ADD UNIQUE indeks_unikalny (kolumna1),
ADD INDEX indeks_zwykly (kolumna2)

Tworzenie indeksów w istniejącej tabeli (inna wersja; komenda tworzy tylko jeden indeks na raz, zatem należy wydać dwie komendy):

CREATE UNIQUE INDEX indeks_unikalny ON nazwa_tabeli (kolumna1)
CREATE INDEX indeks_zwykly ON nazwa_tabeli (kolumna2)

Tak jak pokazałem, w pojedynczej tabeli może istnieć wiele indeksów. Istnieje także możliwość utworzenia indeksów na kilku kolumnach jednocześnie. Porównaj zatem dwa przykłady:

Dwa indeksy, każdy na jednej kolumnie:

CREATE INDEX indeks_imie ON osoby (imie)
CREATE INDEX indeks_nazw ON osoby (nazwisko)

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

CREATE INDEX indeks_imie_nazw ON osoby (imie, nazwisko)

Pomiędzy tymi indeksami istnieje zasadnicza różnica, która objawia się w momencie gdy zapytanie wybierające dane posiada warunki odwołujące się do kilku kolumn z indeksami. Na przykład można użyć takiego zapytania:

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

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

1. Wyszuka wszystkie rekordy gdzie imie = Jan;
2. Wyszuka wszystkie rekordy gdzie nazwisko = Kowalski;
3. 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.

Jeżeli przewidujesz że będziesz używał(a) zapytań wyszukujących tylko po imieniu, tylko po nazwisku, lub po imieniu i nazwisku, możesz stworzyć następujące indeksy aby obsłużyć wszystkie przypadki:

CREATE INDEX indeks_imie_nazw ON osoby (imie, nazwisko)
CREATE INDEX indeks_nazw ON osoby (nazwisko)

Pamiętaj 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. Co więcej, jeżeli chcesz pole oznaczyć jako klucz obcy, baza danych może wymagać indeksu na nim (zobacz do dokumentacji bazy danych aby poznać więcej szczegółów).

Optymalizacja zapytań i operacje wykonywane w bazie danych

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 wybierzesz tylko dane z trzech kolumn, zamiast z np. dwudziestu.

Druga możliwość optymalizacji to filtrowanie wybieranych danych już w bazie danych, za pomocą klauzuli WHERE. Pamiętaj 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.

Pamiętaj 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:

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

Możliwe są także bardziej złożone obliczenia, zwłaszcza gdy użyjemy możliwości grupowania rekordów w zapytaniach. Np. można policzyć ile osób nosi poszczególne nazwiska:

SELECT nazwisko, COUNT(id_osoby) FROM osoby GROUP BY nazwisko

Często warto także poprosić bazę danych o posortowanie rekordów - nie trzeba będzie tego robić specjalnie przed wyświetleniem:

SELECT nazwisko, COUNT(id_osoby) FROM osoby
GROUP BY nazwisko ORDER BY nazwisko

Można także policzyć średnią ocen dla każdej osoby:

SELECT imie, nazwisko, AVG(ocena)
FROM studenci, oceny
WHERE studenci.id_studenta = oceny.id_studenta
GROUP BY imie, nazwisko

Możliwe jest nawet także wybranie osób które mają średnią ocen większą od 4.0, i posortowanie ich wg tej średniej malejąco (czyli na początku ci z najwyższą średnią):

SELECT imie, nazwisko, AVG(ocena)
FROM studenci, oceny
WHERE studenci.id_studenta = oceny.id_studenta
GROUP BY imie, nazwisko
HAVING AVG(ocena) > 4.0
ORDER BY AVG(ocena) DESC

Jeżeli niektóre z podanych powyżej przykładów to jeszcze dla Ciebie czarna magia, to oznacza iż musisz nadrobić zaległości w języku SQL. Polecam do tego np. materiały do wykładu Bazy Danych autora Roberta Chwastka - co prawda opisują one bazę danych Oracle, ale wiele z zawartych tam informacji przyda się też np. do MySQL'a (zwłaszcza rozdziały poświęcone warunkom i wyrażeniom języka SQL (rozdział 4), oraz standardowym funkcjom (rozdział 5).

Procedury składowane w bazie danych

Dodatkową możliwością dostępną w bazach danych (MySQL od wersji 5.0, PostgreSQL, MS SQL, Oracle) jest możliwość pisania procedur i funkcji, które są przechowywane i wykonywane w bazie danych. Dzięki temu można osiągnąć dalszy wzrost wydajności, gdyż taka procedura (lub funkcja) będzie mieć bezpośredni dostęp do danych, zatem wyeliminowane zostaną opóźnienia związane z przesyłaniem danych poza bazę danych. Polecam przejrzeć dokumentację do używanej bazy danych aby dowiedzieć się więcej na ten temat.

Denormalizacja bazy danych metodą zwiększenia wydajności

W przypadku dużych baz danych na których wykonywana jest ogromna ilość zapytań, warto pomyśleć o częściowej denormalizacji, aby zwiększyć wydajność. Można tego dokonać na dwa podstawowe 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.

Wartości które mogą zostać wyliczone i zapisane w bazie danych to np. suma kwot na fakturze czy średnia ocen. Warto przeanalizować dane i zapytania, aby znaleźć takie wartości które są często obliczane, a obliczenia nie są trywialne. Są to potencjalni kandydaci do optymalizacji tą metodą. Co prawda baza zajmie więcej miejsca na dysku oraz jej modyfikacje będą wolniejsze, ale sa też zalety: jeżeli większość zapytań tylko odczytuje dane, to zapisany gotowy wynik pozwoli zaoszczędzić czas który wcześniej był poświęcany na obliczenia.

Drugą metodą optymalizacji przez denormalizację jest dodanie dodatkowych kluczy obcych. Zobacz na przykładowe tabele dla forum dyskusyjnego:

Tabele te zawierają kolejno kategorie (fora można grupować w kategorie), fora, topiki (czyli inaczej wątki dyskusji) i odpowiedzi do nich. Zwróć uwagę że w tabeli Topic umieszczony jest dodatkowo klucz obcy CAT_ID, a w tabeli Reply znajdują się klucze obce CAT_ID i FORUM_ID. Dzięki takiemu zabiegowi można szybko wyszukać np. wątki dyskusji i odpowiedzi w ramach określonej kategorii, bez konieczności odwoływania się do tabeli Forum. Gdyby tych nadmiarowych kluczy nie było, trzeba byłoby ją dołączyć gdyż łączy ona tabelę Category z tabelą Topic.

Wykop tą stronę! >>>