Optymalizacja zapytań SQL, dobre praktyki cz.1

Co ma wpływa na długość wykonywania się zapytań.

Tym wpisem rozpoczynam serię (mam nadzieję 🙂 ) artykułów dotyczących czynników wpływających na czas wykonywania zapytań SQL oraz techniki lub mechanizmy ich optymalizacji. W tym wpisie chciałbym skupić się na podstawach, czyli jakie czynniki wpływają na czas wykonywania się zapytań SQL.

Struktura bazy danych

To jak jest zbudowana baza danych ma znaczenie podczas tworzenia zapytań do niej. Jeżeli dane które chcemy rejestrować w bazie danych rozbijemy na "tysiąc kawałków/tabel" tym częściej będziemy musieli później korzystać z JOIN-ów a tym samym wpłynie to na czas wykonywania zapytania. Z drugiej jednak strony jeżeli istnieją dane które można "zesłownikować" to należy to zrobić. Przy tworzeniu struktury na pewno musisz zwrócić szczególną uwagę na kilka rzeczy:
– jeżeli w danej tabeli są dane które można "zesłownikować" zrób to
– dokładnie przemyśl typy relacji pomiędzy tabelami
– dobrze skonfiguruj klucze główne i klucze obce
– rozrysuj sobie strukturę na kartce i postaraj się przemyśleć wszystkie możliwe zapytania – oczywiście nikt nie mówi o ślęczeniu godzinami i wymyślaniu wszystkich, nawet teoretycznych zapytań, nie jesteś też w stanie od razu wymyślić wszystkich możliwych zapytań które przyjdzie Ci zrobić ale z reguły wiesz o jakie statystyki szef prosi najczęściej i pod tym kątem przeanalizuj Twoją strukturę np. masz bazę sklepu to wiadomo, że prędzej czy później ktoś będzie chciał zrobić analizę sprzedaży np. najczęściej kupowane produkty, jak kształtowałą się sprzedaż w ogóle (trendy), targetowanie klientów itp.
Pamiętaj, że liczba tabel których później będziemy używać w zapytaniu także ma znaczenie.

Wielkość bazy danych (liczba rekordów)

Tutaj sprawa jest prosta. Wiadomo, że z biegiem czasu danych w bazie przybywa i im więcej danych będziesz przechowywał tym zapytania będą działały wolniej. O ile przy "małych" bazach nie musisz się zbytnio martwić bo zapytania będą działały całkiem sprawnie to w "dużych" bazach należy już wziąć ten czynnik pod uwagę. Widzę tutaj dwie płaszczyzny do przemyśleń. Po pierwsze musisz się zastanowić jak poprawnie ustawić klucze główne i klucze obce oraz poprawne indeksowanie. Drugim czynnikiem jest sprzęt. Przy tworzeniu bazy musisz mniej więcej wziąć po uwagę przyrost danych w ciągu jakiejś jednostki czasu np. roku i dobrać do tego maszynę na której będzie stała baza. Przykład: tworzysz bazę dla sklepu. Inaczej podejdziesz do osiedlowego sklepu wędkarskiego a inaczej do dyskontu spożywczego. Wielkości baz po któtkim czasie będą różniły się diametralnie (inną sprawą jest liczba użytkowników ale o tym później).

Ilość użytkowników

Temat ten potraktuję (w tym wpisie) bardzo ogólnei i tylko chcę zasygnalizować, że coś takiego jest. Temat jest szeroki i przeplata się tutaj wiele wątków np. współbieżność, przepustowość itp.
Logicznie rzecz biorą im więcej użytkowników tym większe problemy z dostępem do zasobów bazy. W momencie kiedy jednej użytkownik korzysta z jakiegoś zasobu i pojawia się drugi użytkownik mogą wystąpić różnego rodzaju blokad które w efekcie mogą doprowadzić do utraty spójności danych.


Poziomy dostępu do danych

Wiadomo, że użytkownicy jeżeli mogą to w każdej sytuacji pobierali by jak najwięcej danych, czy potrzebują czy nie, nie patrząc na to czy rzeczywiście ich potrzebują. Rozwiązaniem sytuacji możę być pogrupowanie użytkowników w grupy i dla każdej z nich stworzyć odpowiedni raport.
Ograniczenia czasowe
To, że Pania Kasia potrzebuje danych sprzedażowych to nie znaczy, że musi je pobierać od 1999 r. może wystarczą jej dane za ostatni rok lub nawet kwartał. W raportach z parametrami które podaje użytkownik także można zastosować metodę podawania np. domyślnych dat. Jak Pani Kasia ma wpisać datę sama to wpisze od 2000 r. a jak domyślnie pojawi się jej data na rok wstecz to nie będzie się zastanawiała i kliknie z taką datą jaka jest domyślnie. Z doświadczenia wiem, że różnica między zrobieniem zapytania za ostatnie 5 lat i za ostatni rok może być ogromna (oczywiście mówimy o bardzo dużych bazach danych).
Właściwość miejscowa
To, że Pania Kasia potrzebuje danych sprzedażowych to nie znaczy, że musi je pobierać za wszystkie sklepy z całego kraju. Może potrzebne jej są tylko dane z województwa Małopolskiego. Stworzenie odpowiednich parametrów w raportach ograniczających dane do jakiejś mniejszej struktury organizacyjnej jest bardzo dobrym pomyśłem.

Filtrowanie danych

Przy tworzeniu zapytań dużo uwagi poświęć na analizę warunków w WHERE i HAVING. Staraj się odrzucić tutaj jak największy zbiór danych. Inną techniką (którą czasami stosuję) jest wybranie interesujących mnie identyfikatorów z interesującej mnie tabeli a później dowiązywanie do nich reszty danych. Z doświadczenia wiem, że czasami ta technika się sprawdza, oczywiście zawsze musisz sprawdzić co w Twoim przypadku będzie lepszym rozwiązaniem.

Zakres zwracanych danych

Ten temat już troszeczkę omówiłem w punkcie "Poziomy dostępu do danych". Jest jednak jeszcze jedna kwestia którą chciałem zasygnalizować. Niektórzy projektanci zapytań mają manię korzystania ze struktury SELECT * FROM …itd. W takim przypadku wiadomo, że zwrócimy wszystkie kolumny z danej tabeli lub tabel (jeżeli korzystamy ze złączeń). Nie zawsze jest to konieczne. Widziałem już takie zapytanie które zwracały po 15 a nawet 20 kolumn, ale jak drążyłem temat i pytałem użytkowników (korzystających z raportów) czego tak naprawdę potrzebują to okazywało się, że tak naprawdę oni wykorzystują dane tylko z 5 kolumn. Sami widzicie, ile treści jest czasami generowane niepotrzebnie.

Podsumowując
Opisane powyżej pojęcia nie wyczerpują oczywiście tematu, są jedynie pokazaniem (moim zdaniem) podstawowych problemów z którymi zetknie się każdy kto pisze zapytania SQL. Wpis ma tylko za zadanie abyś spojrzał na tworzoenie zapytań SQL z innej strony (jeżeli nigdy się nad tym nie zastanawiałeś) i przy ich tworzeniu analizował także inne sprawy niż tylko żeby zwróciło poprawny wynik. Jako programista masz świadomość, że baza musi zwrócić wynik jak najszybciej się da ale pamiętaj również, że czas otrzymania wyniku jest także uzależniony od ilości zwracanych danych.

Już zapraszam na dalsze części serii o optymalizacji zapytań i dobrych praktykach przy ich pisaniu. 🙂