W niniejszej notce chciałbym przedstawić kilka problemów i zagadnień, które wpływają na czas wykonywania zapytań SQL oraz z którymi będziesz musiał się niejednokrotnie zmierzyć w walce wręcz, budując aplikacje internetowe!
Wychodzę z założenia, iż każdy zna już podstawy języka SQL, miał z nim do czynienia we wcześniejszych projektach i potrafi pisać zapytania do bazy danych. Nie będę także opisywał konkretnego silnika (Oracle, MySQL, DB2), a porady dotyczą samego SQL – deklaratywnego języka do obsługi relacyjnych systemów baz danych.
Przejdę więc do konkretów. Oto główne czynniki wpływające na długość wykonywania zapytań na bazie danych:
1. Całkowita ilość danych do pobrania
To chyba oczywiste, że im więcej danych musisz przejrzeć tym więcej czasu Ci to zajmie. Także w przypadku danych jest to oczywiste. Zbyt dużego pola do popisu przy optymalizacji takich zapytań nie masz. Przede wszystkim pomocne tutaj okaże się odpowiednie indeksowanie oraz szybkie maszyny, na których jest przechowywana i przetwarzana baza danych.
2. Ilość ludzi aktualnie pobierających dane
Temat współbieżności i przepustowości jest bardzo obszernym i skomplikowanym. Niemniej jednak w wielkich platformach internetowych jest to dość często spotykany problem, z którym trzeba sobie mimo wszystko radzić. Nie możesz bowiem dopuścić, żeby dochodziło do konkurowania o zasoby różnych klientów, blokad czy innych, mniej oczywistych efektów ubocznych (które mogą doprowadzić do głównego grzechu bazodanowca – niespójności danych).
W tym przypadku radzić można sobie na kilka sposób, m. in. poprzez tworzenie kolejek danych do zapisu w odpowiedniej kolejności, czy wprowadzanie celowych blokad (COMMIT), aby uniknąć nadpisywania lub usuwania danych wprowadzanych w tym samym czasie. Innym sposobem może być partycjonowanie danych. Jeszcze innym rozwiązaniem, tym razem na poziomie oprogramowania, jest udoskonalenie współbieżności w określonym języku programowania.
3. Kryteria dotyczące pobierania danych
Od tego jak bardzo skomplikowany jest warunek przy selekcji danych zależy czas pobierania właściwych danych. Mam tutaj na myśli warunek WHERE, podczas którego korzystamy z wbudowanych funkcji SQL (takich jak operacje na dacie lub czasie, operacje matematyczne, czy logiczne). Znaczący wpływ na szybkość określania danych ma również odpowiednie indeksowanie kolumn, jak i właściwe budowanie odpowiednich zapytań (tak by wykorzystywały stworzone indeksy).
Przy indeksach należy dodać, iż jest to dość luksusowe rozwiązanie. Choć zazwyczaj powoduje dużo szybsze dostanie się do pożądanych danych to kosztuje sporo miejsca fizycznego na dysku serwera. Nierzadko bywa, iż indeks tabeli jest większy niż rzeczywiste dane (zajmuje większą przestrzeń dyskową).
Podczas definiowania filtrów w klauzuli WHERE lub HAVING pamiętaj, by najpierw odrzucać zbiory danych, których nie potrzebujesz. Im szybciej pozbędziesz się większych ilości danych z zapytania (by nie przetwarzać tysięcy rekordów przez kilka warunków), tym szybciej wykona się to zapytanie.
4. Rozmiar zbioru wynikowego do zwrócenia
Nie zawsze jest to jasne, jednak im większy zbiór danych jest do zwrócenia klientowi, tym czas wykonania polecenia SQL również się wydłuża (punkt ten dotyczy także modyfikacji i usuwaniu rekordów z tabel).
Dodatkowo dochodzi do tego fakt, iż dane te najczęściej muszą być przesłane do klienta zdalnego (mowa o technologii Web). Im więcej danych do przesłania, tym więcej czasu serwer potrzebuje na ich odebranie, jak i obrobienie oraz dalsze przesłanie do klienta (przeglądarki).
Rozwiązaniem jest tutaj lepsze porcjowanie wyników i pobieranie jedynie wymaganych rekordów, nic ponadto. Gorzej w przypadku programów wsadowych, które zazwyczaj przetwarzają bardzo duże ilości danych, lecz i tutaj znajdą się dedykowane rozwiązania.
Jako programista musisz także pamiętać, iż użytkownik nie lubi czekać. To znaczy, że ilość danych zwracanych do użytkownika końcowego powinna być proporcjonalna do czasu, w jaki uzyskał dostęp do tych danych. Nie możesz pozwolić na sytuacje takie jak np.: w wyniku wyszukiwania została wykonana operacja na bazie danych trwająca kilkanaście (kilkadziesiąt) sekund, by na końcu oznajmić użytkownikowi: ‘Niestety, lecz dla Twojego zapytania nie odnaleziono żadnych produktów’. Użytkownik może poczuć się zirytowany długim okresem wyczekiwania, które nie przyniosło żadnych rezultatów, po czym opuścić Twój sklep/serwis.
5. Liczba tabel biorących udział w zapytaniu SQL
Równie oczywisty jest fakt, iż od liczby połączeń z różnymi tabelami zależeć będzie ostateczny czas pobrania pożądanych informacji. Choć język SQL wykorzystuje relacje do zestawiania określonych zbiorów danych to z technicznego punktu widzenia zwiększenie liczby połączeń z różnymi tabelami zwiększa całościowy czas wykonania zapytania.
Jako iż bazy danych działają w oparciu o relacje, naturalnym jest dzielenie danych do części atomowych, tak by uzyskać 3 postać normalną (3NF). Jednak zbyt drobny podział danych może doprowadzić do wykonywania za każdym razem połączenia z kilkunastoma tabelami, co może negatywnie odbić się na wydajności zapytań do takiej bazy. Należy więc już na poziomie konstruowania bazy danych pomyśleć, do czego i w jaki sposób będziemy potrzebowali danych. Czasem względem wydajności możesz odstąpić od 3NF, jednak zanim to zrobisz: zastanów się czy warto, najlepiej kilka razy.
Podsumowanie rozważań
Powyższa lista zdecydowanie nie jest ostateczną listą czynników wpływających na wydajność wykonywania zapytań do bazy danych, a jedynie stanowi spis podstawowych czynników mających największy wpływ na jej wydajność. W rzeczywistych aplikacjach trzeba najczęściej stosować osobliwe rozwiązania i radzić sobie z dedykowanymi problemami, jednak zważając na powyższe elementy i ich przestrzegając, unikniesz wielu często popełnianych błędów i problemów. Pozostaną jedynie te dedykowane.
Pamiętaj również, że przy projektowaniu rzeczywistych aplikacji internetowych najważniejsze jest testowanie! Musisz porównywać różne zapytania (sposobów na zapis jakiegokolwiek zapytania jest co najmniej kilka!), śledzić ścieżki przebiegu wykonania zapytania, testować, poprawiać i szukać optymalnego rozwiązania! O sposobach testowania i praktycznego badania wydajności zapytań postaram się napisać już wkrótce. Tymczasem życzę powodzenia przy projektowaniu swoich własnych baz danych :-)
Pamiętaj również, że przy projektowaniu rzeczywistych aplikacji internetowych najważniejsze jest testowanie! Musisz porównywać różne zapytania (sposobów na zapis jakiegokolwiek zapytania jest co najmniej kilka!),[..]
Nie sposób się z tym nie zgodzić.
Często jest tak, że to co w jednym zapytaniu jest szybsze – w innym będzie wolniejsze.
Dokładnie. Ciężko wyczuć, które zapytanie wykona się szybciej (zwłaszcza, gdy uzbiera się więcej danych po kilku miesiącach działania aplikacji), dlatego najlepiej wykonywać testy i porównania w celu wyłonienia najoptymalniejszych zapytań – tym bardziej, że to bardzo proste dla SQL :)
„Proste” jest względne… w pewnym momencie zapytanie przestaje być proste, a nadal musi być szybkie.
Czasem też warto rozbijać zapytanie na mniejsze… szczególnie gdy jest dużo join’ów.
Ale to taka moja mała fanaberia…
Proste w odniesieniu do testów i analiz, gdyż sama konsola pokazuje czas wykonania zapytania. Poza tym mamy polecenia EXPLAIN, ANALYZE i inne, co bardzo ułatwia analizę :) Jednak samo wyodrębnienie najbardziej optymalnego zapytania do już wyższa szkoła jazdy.