PHP/Relacje i indeksy
Relacje i indeksy
edytujTen rozdział jest już ostatnim na naszej drodze poznawania bazy danych MySQL. Po zapoznaniu się z relacjami oraz istotą działania indeksów, powrócimy do PHP, by nauczyć się wykorzystywać tę moc w naszych skryptach.
Indeksy
edytujPodczas wykonywania polecenia SELECT serwer musi wykonać bardzo dużo operacji: wybieranie danych, sortowanie wyników itd. Ponieważ przeznaczony on jest nie tylko do pracy z tak mikroskopijnymi ilościami rekordów, z jakimi mamy do czynienia (MySQL pracuje stabilnie nawet przy dziesiątkach milionów rekordów w tabeli), bardzo ważną rolę odgrywa tu optymalizacja wszelkiego rodzaju wyszukiwania oraz sortowania. Popatrzmy: rekordy ułożone są w tabeli w takiej kolejności, w jakiej zostały dodane. Oznacza to, że jeżeli próbujemy wykonać w naszej stworzonej wcześniej bazie wyszukiwanie względem ceny, MySQL musi za każdym razem na nowo przetrząsać od początku do końca wszystko, co tam się znajduje. Kiedy produktów będzie parę tysięcy, może to trwać znacznie dłużej, niż teraz. Tutaj do akcji wkraczają indeksy. Indeks nałożony na pole A jest kopią zawartości tego pola, tyle że posortowaną i odpowiednio ułożoną. Podczas robienia wszelkiego rodzaju poszukiwań względem znajdujących się w nim wartości, MySQL może teraz gwałtownie przyspieszyć. Oto przykład, co mogą nam dać posortowane rekordy: mamy tysiąc wartości posortowanych rosnąco (są one wszystkie większe od zera, górna granica nieustalona). Powiedzmy, że chcemy wiedzieć, jakie rekordy mają wartości z zakresu od 700 do 900. Jako że są one posortowane, MySQL może od razu strzelić sobie w środek tego zbioru i sprawdzić:
- Jeżeli wartość środkowego elementu jest mniejsza od podanego zakresu, przeszukujemy tylko późniejsze rekordy.
- Jeżeli wartość jest większa od podanego zakresu - tylko wcześniejsze.
- Jeżeli mieści się w podanym zakresie - poruszamy się jednocześnie w obu kierunkach, dopóki nie wypadniemy poza zakres.
Dzięki posortowaniu, MySQL odrzuca na wejściu całe tony rekordów, które po prostu nie pasują do kryteriów z samej definicji. Także sortowanie jest prostsze - wystarczy przejechać się po indeksie; nawet nie trzeba żadnej funkcji sortującej uruchamiać. Ceną za takie udogodnienia jest zwiększenie rozmiarów bazy, gdyż sam indeks też potrzebuje trochę miejsca. Dlatego powinniśmy się zastanowić, na których polach indeks nam będzie potrzebny, a na których nie. Ba! Po ich utworzeniu będziemy się nawet mogli przekonać, czy MySQL je wykorzystuje. Wystarczy zapytanie SELECT poprzedzić słowem EXPLAIN, a otrzymamy pełną informację diagnostyczną o jego wykonywaniu. Dodajmy indeks do istniejącej już tabeli:
ALTER TABLE `produkty` ADD INDEX (`cena`);
Zapytanie ALTER służy do modyfikowania struktury już istniejących tabel, lecz nie będziemy poświęcać mu zbyt wiele czasu. Kiedy poznamy pakiet phpMyAdmin służący do zarządzania bazami danych, będzie on wykonywać takie operacje za nas automatycznie. Na razie wystarczy nam wiedzieć, że w ten sposób stworzyliśmy indeks dla pola cena w tabeli produkty. MySQL powinien teraz korzystać z niego, kiedy np. będziemy chcieli sprawdzać przedziały cenowe:
SELECT id, nazwa FROM produkty WHERE cena BETWEEN 2 AND 10; +----+-------------------------+ | id | nazwa | +----+-------------------------+ | 1 | Dlugopisy niebieskie | | 2 | Dlugopisy czerwone | | 4 | Karteczki samoprzylepne | | 3 | Zszywacze | +----+-------------------------+ 4 rows in set (0.00 sec)
A oto informacje diagnostyczne o zapytaniu:
EXPLAIN SELECT id, nazwa FROM produkty WHERE cena BETWEEN 2 AND 10; +----+-------------+----------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | produkty | range | cena | cena | 4 | NULL | 3 | Using where | +----+-------------+----------+-------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
Możemy stąd dowiedzieć się m.in., jakie klucze MySQL mógł wykorzystać (kolumna "possible_keys"), a jakich faktycznie użył (kolumna "key").
Indeksy można tworzyć także przy tworzeniu tabeli. Tak wyglądałoby polecenie utworzenia tabeli produkty, gdybyśmy od razu chcieli wprowadzić tutaj jakiś indeks:
CREATE TABLE `produkty` (
`id` int(11) NOT NULL auto_increment,
`nazwa` varchar(60) NOT NULL,
`opis` text NOT NULL,
`ilosc` smallint(6) default '0',
`cena` float NOT NULL,
`jakosc` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `cena` (`cena`)
) ENGINE=MyISAM;
Piszemy po prostu słowo key, po nim podajemy nazwę indeksu, a w nawiasie wymieniamy listę należących do niego pól (indeks może składać się z więcej, niż jednego pola, podobnie też w tabeli może być kilka indeksów).
Relacje jeden do wielu
edytujDotychczas każda tworzona tabela funkcjonowała niezależnie od innych, lecz takie podejście nie wykorzystuje nawet połowy potęgi baz danych. Prawdziwa moc tkwi w tym, że między tabelami mogą istnieć powiązania, czyli relacje, odzwierciedlające różne zależności między danymi. Pokażemy to na przykładzie internetowej biblioteki. Mamy zbiór kategorii książek zawarty w tabeli kategorie. Przechowuje on informacje o nazwie kategorii oraz ilości dostępnych tam pozycji. W prawdziwej bazie danych biblioteki oczywiście dodalibyśmy jeszcze tutaj różne ozdabiacze, w stylu opisu kategorii, możliwości wyboru ikonki, różnych dodatkowych informacji statystycznych, lecz na razie nie w tym rzecz. Oprócz tego istnieje druga tabela, ksiazki z polami nazwa, wydawnictwo, cena itp. Jednak posiada ona coś jeszcze: pole kategoria_id, czyli ID kategorii, do której dana książka jest przypisana. W ten sposób utworzyliśmy relację pomiędzy książkami a kategoriami, a ze względu na rodzaj zależności będziemy ją nazywać relacją jeden do wielu: do jednej kategorii możemy przypisać wiele książek, ale do jednej książki może być przypisana tylko jedna kategoria. Jest to najczęściej spotykany typ relacji.
Przejdźmy do utworzenia tabel:
CREATE TABLE `kategorie` (
`id` mediumint(9) NOT NULL auto_increment,
`nazwa` varchar(40) NOT NULL,
`il_ksiazek` mediumint(9) default NULL,
PRIMARY KEY (`id`),
KEY `il_ksiazek` (`il_ksiazek`)
) ENGINE=InnoDB;
INSERT INTO `kategorie` VALUES (1, 'Literatura polska', 4);
INSERT INTO `kategorie` VALUES (2, 'Literatura zagraniczna', 2);
CREATE TABLE `ksiazki` (
`id` int(11) NOT NULL auto_increment,
`nazwa` varchar(100) NOT NULL,
`wydawnictwo` varchar(50) NOT NULL,
`cena` float NOT NULL default '0',
`kategoria_id` mediumint(9) NOT NULL,
PRIMARY KEY (`id`),
KEY `kategoria_id` (`kategoria_id`)
) ENGINE=InnoDB;
INSERT INTO `ksiazki` VALUES (1, 'Hamlet', 'AAA', 6.5, 2);
INSERT INTO `ksiazki` VALUES (2, 'Makbet', 'AAA', 6.8, 2);
INSERT INTO `ksiazki` VALUES (3, 'Potop', 'BBB', 18.4, 1);
INSERT INTO `ksiazki` VALUES (4, 'Quo vadis', 'BBB', 17.99, 1);
INSERT INTO `ksiazki` VALUES (5, 'Pan Tadeusz', 'CCC', 13.78, 1);
INSERT INTO `ksiazki` VALUES (6, 'Nad Niemnem', 'CCC', 15.45, 1);
Zwróćmy uwagę na kilka rzeczy:
- W tabeli ksiazki dla pola kategoria_id jest utworzony indeks. Nakładanie indeksów na pola relacji jest bardzo dobrą praktyką i tutaj zysk wydajności związany z ich użyciem będzie najlepiej widoczny.
- Przy każdym rekordzie w tabeli ksiazki pole kategoria_id przechowuje numeryczny ID rekordu kategorii, do którego dana książka jest przypisana.
Naszą przygodę z relacjami zaczniemy od prostego przykładu: chcemy dowiedzieć się, jaka jest nazwa kategorii, do której przypisana jest książka o ID 3:
SELECT kategorie.nazwa FROM ksiazki, kategorie WHERE kategorie.id = ksiazki.kategoria_id AND ksiazki.id = 3; +-------------------+ | nazwa | +-------------------+ | Literatura polska | +-------------------+ 1 row in set (0.02 sec)
W klauzuli FROM wymieniamy aż dwie tabele - w takim wypadku nazwa każdego pola musi być poprzedzona kropką i nazwą stosownej tabeli (dodajmy, że wtedy nie możemy brać takich nazw w odwrócone apostrofy!). Istotą całego zapytania jest ten fragment: kategorie.id = ksiazki.kategoria_id - to właśnie on wiąże ze sobą kategorię z książką.
Powyższe zapytanie można nieco uprościć, stosując aliasy. Alias tworzy się w klauzuli FROM i jest to nic innego, jak skrócona nazwa tabeli, aby zapytanie było krótsze:
SELECT ka.nazwa FROM ksiazki ks, kategorie ka WHERE ka.id = ks.kategoria_id AND ks.id = 3; +-------------------+ | nazwa | +-------------------+ | Literatura polska | +-------------------+ 1 row in set (0.02 sec)
Teraz zamiast pełnej nazwy ksiazki możemy pisać ks, a zamiast kategorie - ka. Aliasy są bardzo przydatne w bardzo dużych zapytaniach, zajmujących niejednokrotnie wiele linijek. Jednak niosą one w sobie coś więcej, niż tylko poprawiają estetykę. Zagłębmy się trochę bardziej w filozofię zadeklarowania chęci użycia danej tabeli w zapytaniu. Zapis FROM ksiazki mówi, że w danym momencie przetwarzania zapytania możemy pracować maksymalnie na jednym rekordzie z podanej tabeli, do którego odnoszą się warunki podane w WHERE. Ale są sytuacje, kiedy naraz musimy pobrać dane z dwóch rekordów w jednej tabeli - aliasy są w tym wypadku konieczne, aby odróżnić jeden rekord od drugiego: FROM ksiazki ks1, ksiazki ks2. Naturalnie może się zdarzyć, że oba aliasy wskażą nam ten sam rekord, ale równie dobrze mogą być one różne, w przeciwieństwie do poprzedniej sytuacji:
SELECT ks1.nazwa AS `ks1_nazwa`, ks2.nazwa AS `ks2_nazwa` FROM ksiazki ks1, ksiazki ks2 WHERE ks1.id = 3 AND ks2.id = 4; +-----------+-----------+ | ks1_nazwa | ks2_nazwa | +-----------+-----------+ | Potop | Quo vadis | +-----------+-----------+ 1 row in set (0.00 sec)
Widać teraz bardzo wyraźnie, że zapisy ksiazki ks1 oraz ksiazki ks2 są od siebie niezależne, chyba że powiążemy je relacją (tak, możliwe jest tworzenie relacji między rekordami tej samej tabeli!). Na koniec najbardziej wymowny przykład korzystania z relacji jeden do wielu. Radzimy przyjrzeć mu się uważnie, ponieważ podobne zapytania pojawiają się naprawdę często. A mowa jest o pobraniu listy książek wraz z nazwą kategorii, do której są przypisane.
SELECT ks.id, ks.nazwa, ks.wydawnictwo, ks.cena, ka.nazwa AS `kat_nazwa` FROM ksiazki ks, kategorie ka WHERE ka.id = ks.kategoria_id ORDER BY ks.cena; +----+-------------+-------------+-------+------------------------+ | id | nazwa | wydawnictwo | cena | kat_nazwa | +----+-------------+-------------+-------+------------------------+ | 1 | Hamlet | AAA | 6.5 | Literatura zagraniczna | | 2 | Makbet | AAA | 6.8 | Literatura zagraniczna | | 5 | Pan Tadeusz | CCC | 13.78 | Literatura polska | | 6 | Nad Niemnem | CCC | 15.45 | Literatura polska | | 4 | Quo vadis | BBB | 17.99 | Literatura polska | | 3 | Potop | BBB | 18.4 | Literatura polska | +----+-------------+-------------+-------+------------------------+ 6 rows in set (0.00 sec)
Relacje wiele do wielu
edytujKolejny typ relacji, którym się zajmiemy, nosi nazwę wiele-do-wielu. Jest on dokładnie tym, co mówi nazwa. Patrząc na naszą księgarnię, możemy nim przypisać autorów do książek, co jest zgodne z intuicją: książka mogła być napisana przez wielu autorów, a jednocześnie każdy autor mógł napisać wiele książek.
Zacznijmy od stworzenia tabeli z autorami:
CREATE TABLE `autorzy` (
`id` SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`imie` VARCHAR(30) NOT NULL,
`nazwisko` VARCHAR(40) NOT NULL
) ENGINE = InnoDB;
INSERT INTO `autorzy` (`imie` , `nazwisko`) VALUES
('William', 'Shakespeare'),
('Henryk', 'Sienkiewicz'),
('Adam', 'Mickiewicz'),
('Eliza', 'Orzeszkowa'),
('Jan', 'Kowalski');
Zauważmy, że w relacji wiele-do-wielu nasze tabele nie zawierają pól-kluczy, które mogłyby je spinać. Bynajmniej nie znikają one, a wręcz przeciwnie - istnieją, tyle że w trzeciej tabeli pomocniczej:
CREATE TABLE `autorzy_to_ksiazki` (
`ksiazka_id` INT NOT NULL,
`autor_id` INT NOT NULL,
`udzial` VARCHAR(16) NOT NULL,
INDEX (`ksiazka_id`, `autor_id`)
) ENGINE = InnoDB;
INSERT INTO `autorzy_to_ksiazki` VALUES (1, 1, 'autor');
INSERT INTO `autorzy_to_ksiazki` VALUES (1, 5, 'tłumacz');
INSERT INTO `autorzy_to_ksiazki` VALUES (2, 1, 'autor');
INSERT INTO `autorzy_to_ksiazki` VALUES (2, 5, 'tłumacz');
INSERT INTO `autorzy_to_ksiazki` VALUES (3, 2, 'autor');
INSERT INTO `autorzy_to_ksiazki` VALUES (4, 2, 'autor');
INSERT INTO `autorzy_to_ksiazki` VALUES (5, 3, 'autor');
INSERT INTO `autorzy_to_ksiazki` VALUES (6, 4, 'autor');
Relacja wiele-do-wielu może przenosić dodatkowe informacje w tabeli pomocniczej, np. w tym przypadku mówi nam także, czy osoba przypisana do danej książki jest autorem, czy tłumaczem. Teraz, kiedy tabele są już gotowe, możemy pokazać parę przykładów obrazujących korzystanie z tej relacji.
Zaczniemy od prostego wybrania wszystkich autorów "Hamleta":
SELECT a.imie, a.nazwisko, ak.udzial FROM autorzy a, autorzy_to_ksiazki ak WHERE a.id=ak.autor_id AND ak.ksiazka_id=1; +---------+-------------+----------+ | imie | nazwisko | udzial | +---------+-------------+----------+ | William | Shakespeare | autor | | Jan | Kowalski | tlumacz | +---------+-------------+----------+ 2 rows in set (0.00 sec)
Autora z książką łączy nam ten warunek: a.id=ak.autor_id AND ak.ksiazka_id=1. Na liście pól pobieramy imię i nazwisko z tabeli autorzy z dołączoną informacją o udziale przy opracowywaniu akurat tej pozycji: ak.udzial. Bierzemy ją z tabeli pomocniczej.
W podobny sposób można dowiedzieć się, przy jakich książkach pracował podany autor (np. Jan Kowalski).
SELECT k.nazwa, ak.udzial FROM ksiazki k, autorzy_to_ksiazki ak WHERE k.id=ak.ksiazka_id AND ak.autor_id=5; +--------+----------+ | nazwa | udzial | +--------+----------+ | Hamlet | tlumacz | | Makbet | tlumacz | +--------+----------+ 2 rows in set (0.00 sec)
Zapytanie to działa identycznie, jak poprzednie. Podstawiliśmy do niego jedynie inną tabelę. Teraz może coś bardziej zaawansowanego. Dowiedzmy się, kto pracował dla nas jako autor i przy jakich książkach:
SELECT a.imie, a.nazwisko, k.nazwa FROM autorzy a, ksiazki k, autorzy_to_ksiazki ak WHERE a.id = ak.autor_id AND k.id = ak.ksiazka_id AND ak.udzial='autor'; +---------+-------------+-------------+ | imie | nazwisko | nazwa | +---------+-------------+-------------+ | William | Shakespeare | Hamlet | | William | Shakespeare | Makbet | | Henryk | Sienkiewicz | Potop | | Henryk | Sienkiewicz | Quo vadis | | Adam | Mickiewicz | Pan Tadeusz | | Eliza | Orzeszkowa | Nad Niemnem | +---------+-------------+-------------+ 6 rows in set (0.00 sec)
Relacje wiele-do-wielu pojawiają się w bardziej złożonych bazach danych, które muszą odzwierciedlać dużą liczbę zależności. Są bardzo wszechstronnym narzędziem i ich znajomość jest wręcz niezbędna.
Optymalizacja
edytujIm większe zapytanie z większą ilością skomplikowanych instrukcji, tym wolniej jest wykonywane. Dlatego dobra struktura bazy danych nie tylko musi być elastyczna, ale też zoptymalizowana w taki sposób, aby jak najwięcej danych dało się pobrać prostymi zapytaniami, korzystającymi z elementarnych funkcji. Brak tej cechy jest mankamentem baz projektowanych nie tylko przez początkujących, ale i wielu zaawansowanych programistów, co negatywnie rzutuje na wydajność ich aplikacji.
Pamiętajmy, że wolna przestrzeń dyskowa jest aktualnie najtańsza w historii ludzkości, a dane liczbowe zajmują śmiesznie małą jej ilość. Podstawową zasadą jest właśnie buforowanie tego, co da się buforować, a pokażemy to na przykładzie systemu newsów z możliwością komentowania. W systemie takim mamy dwie tabele: newsy oraz komentarze połączone relacją jeden-do-wielu. Zazwyczaj przy wyświetlaniu newsów pragniemy podać też, ile jest w nich komentarzy. Pobranie takich informacji jednym zapytaniem wymaga sprytnego zastosowania funkcji grupującej COUNT(), której użycie w relacjach jest obwarowane paroma ograniczeniami. Jednak to nie wszystko. Kiedy tabele zepniemy "sztywno" w warunku WHERE, na liście pokażą się nam tylko te newsy, w których ktoś już dodał jakiś komentarz! Dlatego musimy zastosować specjalną klauzulę służącą do warunkowego spinania tabel: LEFT JOIN. Jest ona użyteczna, ale dość powolna w działaniu. Ostatecznie otrzymujemy coś takiego:
SELECT n.id, n.tytul, COUNT(k.id) AS `il_komentarzy` FROM newsy n LEFT JOIN komentarze k
ON k.news_id=n.id GROUP BY k.news_id ORDER BY n.id;
Zapytanie to było wykorzystywane praktycznie na stronie WWW autora podręcznika. Działa, lecz przy większej ilości rekordów zaczynają się problemy w stylu zrywania połączenia czy nawet blokowania serwera DB. Naprawdę, nie życzymy nikomu, aby znalazł się w podobnej sytuacji, zwłaszcza że problemowi można zaradzić w bardzo prosty sposób. Dlaczego bowiem zliczać ilość komentarzy przy każdym wyświetlaniu strony? Przecież liczba ta nie zmienia się tak znowu często. Dorzućmy do tabeli newsy pole il_komentarzy. Przy dodawaniu newsa inicjujemy je wartością 0. Przy dodawaniu komentarza zwiększamy jego wartość o 1, przy usuwaniu - zmniejszamy. W ten sposób uzyskaliśmy takie zapytanie końcowe:
SELECT id, tytul, il_komentarzy FROM newsy ORDER BY id;
Wykorzystuje ono elementarną składnię, przez co może wytrzymać znacznie większe obciążenie. Tego typu optymalizacja spotykana jest w wielu zaawansowanych aplikacjach, np. systemie forów dyskusyjnych Invision Power Board, który w ten sposób zapamiętuje sobie ilości postów oraz tematów.
O kolejnym rodzaju optymalizacji już wspominaliśmy - jest to prawidłowe użycie indeksów. Zakładamy je wszędzie tam, gdzie dane będą sortowane lub wybierane według różnych kryteriów. Indeksy zawierają informacje ułożone w pewnym porządku, dlatego po ich wstawieniu w takie miejsca MySQL wykorzysta je do przyspieszenia całego procesu.
Optymalizację można przeprowadzać także z poziomu języka programowania, za pomocą którego komunikujemy się z bazą. Jeżeli nasze dane zmieniają się niezbyt często, nie ma potrzeby pobierania ich na nowo za każdym razem. Wystarczy raz pobrane zapisać gdzieś na serwerze w pliku i przy dalszych wejściach czytać właśnie z niego. Jest to jednak optymalizacja typowo programowa, dlatego zajmiemy się nią później.
Zakończenie
edytujNa tym kończymy naszą przygodę z poznawaniem języka SQL. Jeżeli pragniesz poznać go lepiej, bardzo przydatna okaże się z pewnością dokumentacja serwera MySQL. Poświęć trochę czasu na zapoznanie się z jej rozbudowaną strukturą, gdyż jest to istna kopalnia cennych informacji. Przydatne może być także analizowanie cudzych skryptów oraz własne poszukiwania z użyciem Google. Każdy sposób jest dobry, a znajomości SQL-a nigdy za wiele.