Optymalizacja zapytań

Optymalizacja zapytań to przepisanie polecenia i dostrojenie struktur bazy tak, by ograniczyć liczbę odczytanych stron.

Na czym polega optymalizacja zapytań

Optymalizacja zapytań to analiza i modyfikacja poleceń kierowanych do bazy danych oraz struktur, z których silnik korzysta podczas wyszukiwania, łączenia i sortowania rekordów. Celem jest uzyskanie tego samego wyniku przy mniejszym koszcie obliczeniowym. Koszt może obejmować liczbę odczytanych stron danych, operacje wejścia-wyjścia, czas pracy procesora, zużycie pamięci oraz ilość danych przesyłanych między elementami systemu. Samo skrócenie tekstu zapytania nie gwarantuje poprawy. Decydujący jest plan wykonania wybrany przez optymalizator silnika bazy danych.

Podstawą analizy jest porównanie zapytania z rzeczywistą strukturą danych. Znaczenie mają między innymi liczba wierszy, rozkład wartości w kolumnach, obecność wartości pustych, relacje między tabelami oraz selektywność warunków filtrowania. Selektywność określa, jak dużą część tabeli pozostawia dany warunek. Warunek zawężający wynik do niewielkiej liczby rekordów może uzasadniać użycie indeksu, natomiast przy wyborze znacznej części tabeli pełny odczyt może być dla silnika tańszy niż przeglądanie indeksu i późniejsze pobieranie wielu wierszy.

Plan wykonania, filtry i indeksy

Plan wykonania opisuje kolejność operacji wykonywanych przez silnik. Może wskazywać między innymi skanowanie całej tabeli, odczyt indeksu, wyszukiwanie po kluczu, łączenie tabel, sortowanie, agregowanie oraz przekazywanie danych do kolejnych etapów. W narzędziu diagnostycznym właściwym dla danego silnika można zwykle sprawdzić zarówno plan przewidywany, jak i informacje zebrane podczas rzeczywistego wykonania. Szczególnie ważne są różnice między szacowaną a faktyczną liczbą wierszy. Duża rozbieżność może oznaczać nieaktualne statystyki albo nietrafne założenia optymalizatora.

Indeks przechowuje uporządkowane informacje pozwalające szybciej odnaleźć rekordy spełniające określone warunki, ale nie jest bezkosztowy. Zajmuje miejsce, a podczas dodawania, modyfikowania i usuwania danych wymaga aktualizacji. Indeks powinien odpowiadać sposobowi filtrowania, łączenia lub sortowania danych. Jeżeli zapytanie nakłada warunek na kolumnę przekształconą funkcją, indeks na nieprzekształconej wartości może nie zostać wykorzystany w oczekiwany sposób. Podobny problem występuje przy niezgodnych typach danych, ponieważ silnik może wykonać niejawną konwersję i utracić możliwość efektywnego wyszukiwania indeksowego.

Przepisywanie zapytania bez zmiany wyniku

Przepisywanie zapytania powinno zachować jego znaczenie logiczne. W praktyce analizuje się między innymi warunki w klauzuli filtrującej, sposób łączenia tabel, zakres zwracanych kolumn oraz kolejność operacji. Pobieranie wyłącznie potrzebnych kolumn ogranicza ilość danych przekazywanych między operatorami i może umożliwić wykorzystanie indeksu zawierającego wszystkie wartości wymagane przez zapytanie. Nie oznacza to jednak, że zawsze należy mechanicznie usuwać każdą kolumnę: wynik musi nadal spełniać wymagania aplikacji, raportu lub formularza.

W zapytaniach obejmujących wiele tabel warto sprawdzić warunki łączenia. Brak właściwego warunku może doprowadzić do utworzenia iloczynu kartezjańskiego, czyli zestawienia każdego wiersza jednej tabeli z każdym wierszem drugiej tabeli. Taki wynik może szybko zwiększyć liczbę przetwarzanych rekordów. Znaczenie mają również operacje sortowania, grupowania i eliminowania duplikatów, ponieważ mogą wymagać pamięci roboczej albo tymczasowego zapisu danych. Ograniczenie zbioru przed kosztowną operacją często zmniejsza jej obciążenie, lecz każdą zmianę trzeba zweryfikować na danych obejmujących także przypadki brzegowe, duplikaty i wartości puste.

Weryfikacja poprawy i skutków ubocznych

Rzetelna optymalizacja wymaga pomiaru przed zmianą i po zmianie. Należy porównywać czas wykonania, liczbę odczytów logicznych i fizycznych, zużycie procesora, wykorzystanie pamięci oraz liczbę zwróconych wierszy. Pojedynczy szybki pomiar może być mylący, ponieważ wynik zależy od rozgrzania pamięci podręcznej, aktualnego obciążenia, wielkości zbioru danych i współbieżności. Test powinien obejmować zarówno typowe zapytania, jak i przypadki zwracające bardzo mało oraz bardzo dużo rekordów.

Zmiana korzystna dla jednego zapytania może pogorszyć działanie innych operacji. Dodatkowy indeks przyspiesza odczyt tylko w określonych scenariuszach, a jednocześnie zwiększa koszt zapisu i konserwacji struktury. Aktualizacja statystyk może zmienić plan wykonania bez modyfikowania samego polecenia. Trzeba także sprawdzić, czy optymalizacja nie zmienia semantyki wyniku, kolejności rekordów, sposobu obsługi wartości pustych ani zachowania przy równoczesnych modyfikacjach danych. Diagnostyka komputera, na którym działa lokalna baza lub narzędzie korzystające z bazy, obejmuje zatem również analizę obciążenia procesora, pamięci masowej i pamięci operacyjnej, ponieważ wolne zapytanie nie zawsze jest jedyną przyczyną opóźnienia.