Optymalizacja planu wykonania

Optymalizacja planu wykonania to doprowadzenie do wyboru efektywniejszej ścieżki przez zmianę zapytania, indeksów lub statystyk.

Optymalizacja planu wykonania – czym jest i jaki ma cel

Optymalizacja planu wykonania to proces prowadzący do wyboru takiej ścieżki realizacji zapytania, która pozwala sprawnie odczytać, połączyć, przefiltrować i uporządkować dane. W systemach bazodanowych nie wystarcza samo sprawdzenie, czy zapytanie zwraca poprawny wynik. Istotne jest również to, ile rekordów zostanie odczytanych, w jakiej kolejności będą wykonywane operacje, czy użyty zostanie indeks oraz ile zasobów zajmie cała procedura. Ten sam rezultat może zostać uzyskany przez kilka różnych planów, lecz ich czas działania i obciążenie pamięci, procesora oraz nośnika danych mogą znacząco się różnić.

Plan wykonania można traktować jako techniczny scenariusz realizacji zapytania. Zawiera między innymi informacje o sposobie wyszukiwania w tabelach, metodzie łączenia tabel, przewidywanej liczbie wierszy, kosztach poszczególnych operatorów oraz kolejności wykonywania etapów. Optymalizator analizuje treść zapytania, strukturę danych, dostępne indeksy i statystyki, a następnie porównuje możliwe warianty. Wybór nie opiera się na wyglądzie zapytania w kodzie, lecz na przewidywanym koszcie wykonania. Z tego powodu prosta zmiana warunku, kolejności połączeń albo sposobu przekazywania parametrów może spowodować wybór innej ścieżki.

W praktyce optymalizacja planu wykonania nie oznacza wyłącznie dodania indeksu. Czasami indeks poprawia wyszukiwanie, ale jednocześnie zwiększa koszt zapisu, zajmuje miejsce i nie jest używany, gdy zapytanie zwraca znaczną część tabeli. W innych sytuacjach problemem nie jest brak indeksu, lecz nieaktualna statystyka, niezgodny typ danych, funkcja zastosowana na kolumnie albo filtr, którego selektywności nie da się prawidłowo oszacować.

Optymalizacja planu wykonania a kontekst techniczny i porównania

Podczas analizy warto odróżnić plan logiczny od fizycznego. Plan logiczny opisuje zależności wynikające z zapytania, na przykład filtrowanie danych, połączenie tabel i agregację. Plan fizyczny wskazuje już konkretne sposoby wykonania tych czynności. Dla odczytu danych może zostać użyty pełny skan tabeli, skan indeksu, wyszukiwanie po kluczu albo inna metoda zależna od silnika bazy danych. Do łączenia tabel mogą zostać wybrane między innymi nested loops, hash join lub merge join. Każdy z tych wariantów ma zastosowanie w innych warunkach.

Nested loops często sprawdza się wtedy, gdy jedna strona połączenia zwraca niewiele wierszy, a po drugiej stronie istnieje skuteczny indeks. Przy dużych zbiorach i braku selektywnego filtra korzystniejszy może być hash join, w którym budowana jest struktura pomocnicza dla jednej z tabel. Merge join może być efektywny przy danych uporządkowanych według klucza połączenia, choć jego opłacalność zależy od kosztu sortowania. Sam fakt, że jeden operator ma niższy koszt szacunkowy, nie daje jeszcze pewności, że w każdej sytuacji będzie szybszy. Należy porównać plan szacowany z wykonaniem rzeczywistym.

Znaczenie ma także różnica między odczytem z indeksu a odczytem z tabeli. Indeks może szybko wskazać pasujące klucze, lecz jeśli trzeba następnie pobrać większość kolumn z wielu odległych stron danych, dodatkowe odwołania mogą okazać się droższe niż pełny skan. Dlatego analizuje się nie tylko obecność indeksu, lecz także jego kolejność kolumn, pokrycie wymaganych pól, selektywność oraz sposób użycia predykatów. Indeks wielokolumnowy może wspierać filtr po pierwszej kolumnie, ale jego przydatność może być ograniczona, gdy warunek dotyczy wyłącznie dalszego elementu klucza.

Porównanie planów powinno obejmować rzeczywisty czas wykonania, liczbę odczytanych wierszy, liczbę wierszy zwróconych przez operator, zużycie pamięci, operacje sortowania i ewentualne zapisy do obszaru tymczasowego. Warto też obserwować zachowanie zapytania przy małej, średniej i dużej liczbie danych. Plan korzystny dla krótkiego zakresu dat nie musi być właściwy dla zapytania obejmującego wiele miesięcy.

Optymalizacja planu wykonania – typowe problemy i ich przyczyny

Jednym z częstych problemów jest niezgodność pomiędzy szacowaną a rzeczywistą liczbą wierszy. Jeżeli optymalizator przewiduje kilka rekordów, a w rzeczywistości musi przetworzyć setki tysięcy, może wybrać metodę odpowiednią dla małego zbioru, lecz kosztowną dla dużego. Przyczyną bywają nieaktualne statystyki, nierównomierny rozkład wartości, silna korelacja kolumn albo filtr o zmiennej selektywności. W takiej sytuacji analizuje się histogramy, rozkłady danych i częstotliwość odświeżania statystyk.

Problematyczne bywają funkcje stosowane bezpośrednio na kolumnach, niejawne konwersje typów oraz operacje matematyczne w warunku. Mogą one uniemożliwić efektywne użycie indeksu, ponieważ silnik musi przekształcić dużą liczbę wartości zamiast szybko odszukać gotowy zakres. Podobny skutek może wystąpić przy użyciu symbolu wieloznacznego na początku wyszukiwanego tekstu, rozbudowanych warunków alternatywnych albo filtrów zależnych od parametrów o bardzo różnym rozkładzie wyników.

Wydłużenie działania może powodować sortowanie dużego zbioru, grupowanie po nieindeksowanych polach, wielokrotne skanowanie tej samej tabeli lub pobieranie większej liczby kolumn, niż jest potrzebna. Zapytanie zwracające wszystkie pola utrudnia ocenę wymagań i może zwiększać transfer danych. Nadmierne stosowanie podzapytań, zagnieżdżeń i funkcji wierszowych także może utrudniać optymalizatorowi znalezienie dobrej ścieżki, choć sama obecność takiej konstrukcji nie przesądza jeszcze o błędzie.

Osobnej uwagi wymaga parametrizacja. To samo zapytanie może być uruchamiane dla wartości zwracających jeden rekord albo dla wartości obejmujących dużą część tabeli. Plan utworzony dla pierwszego przypadku może być niekorzystny dla drugiego. Przy diagnozie sprawdza się więc różne parametry, częstotliwość ponownego użycia planu oraz ewentualne blokady lub oczekiwanie na zasoby. Nie każdy problem planu jest problemem samego zapytania. Czas wykonania może zależeć także od równoległych operacji, przeciążenia pamięci, wolnego nośnika albo blokowania transakcji.

Optymalizacja planu wykonania – praktyczne wskazówki do analizy

Analizę rozpoczyna się od odtworzenia problemu w warunkach możliwie zbliżonych do rzeczywistych. Należy zebrać tekst zapytania, parametry, czas wykonania, liczbę zwróconych rekordów oraz informacje o czasie oczekiwania. Następnie warto obejrzeć plan szacowany i plan rzeczywisty, zwracając uwagę na operatory o największym udziale w koszcie, rozbieżności liczby wierszy, sortowania, skany, ostrzeżenia i operacje tymczasowe. Dobrą praktyką jest zapisanie planu przed zmianą, aby później można było rzetelnie porównać rezultat.

Zmiany powinny być wprowadzane pojedynczo. Najpierw można sprawdzić, czy statystyki są aktualne i czy predykaty wykorzystują właściwe typy danych. Następnie analizuje się indeksy oraz możliwość uproszczenia zapytania. Jeśli wymagane jest dodanie indeksu, należy uwzględnić zarówno kolumny używane w filtrze, jak i te wykorzystywane do łączenia, sortowania lub zwracania wyniku. Nie powinno się tworzyć wielu podobnych indeksów bez oceny wpływu na zapisy, aktualizacje i zajętość przestrzeni.

Warto ograniczyć zbiór danych możliwie wcześnie, ale tylko wtedy, gdy nie zmienia to znaczenia zapytania. Przeniesienie selektywnego filtra do odpowiedniego etapu może zmniejszyć liczbę wierszy przekazywanych do kolejnych operatorów. W przypadku agregacji należy sprawdzić, czy grupowanie nie obejmuje pól zbędnych dla wyniku. Przy sortowaniu można zweryfikować, czy kolejność wyników jest rzeczywiście wymagana oraz czy właściwy indeks może ją zapewnić bez dodatkowej operacji.

Po każdej zmianie potrzebny jest test na danych o reprezentatywnym rozkładzie. Należy porównać nie tylko czas pierwszego uruchomienia, lecz również wykonania powtarzalne, obciążenie zasobów i zachowanie przy innych parametrach. Korzystny plan powinien poprawiać działanie bez pogorszenia innych zapytań. Przydatne jest także monitorowanie po wdrożeniu, ponieważ dane rosną, zmieniają się proporcje wartości, a plan wybrany dziś może wymagać ponownej oceny w przyszłości.

Kiedy zgłosić optymalizację planu wykonania do serwisu

Do serwisu warto zgłosić się wtedy, gdy aplikacja wyraźnie zwalnia podczas wyszukiwania, raportowania, logowania użytkowników albo zapisywania danych, a problem nie wynika z pojedynczej awarii programu. Szczególnie pomocne są informacje o tym, od kiedy występuje spowolnienie, dla jakich operacji jest widoczne, czy dotyczy wszystkich użytkowników oraz czy zmieniła się ilość danych lub konfiguracja środowiska. Można przygotować przykładowe zapytanie, zanonimizowany plan wykonania i opis oczekiwanego rezultatu, bez przekazywania poufnych danych.

Warszawskie Pogotowie Komputerowe 24/7 może pomóc w uporządkowaniu objawów, sprawdzeniu podstawowych przyczyn spowolnienia oraz wskazaniu, czy potrzebna jest dalsza analiza konfiguracji, bazy danych albo aplikacji. Przed rozpoczęciem prac należy ustalić zakres dostępu, sposób zabezpieczenia kopii zapasowej i metodę weryfikacji efektu. Dzięki temu optymalizacja planu wykonania pozostaje kontrolowaną zmianą, a nie przypadkowym doborem indeksów lub wymuszaniem planu bez rozpoznania źródła problemu.