Plan wykonania zapytania

Plan wykonania zapytania to wybrana przez optymalizator sekwencja operacji prowadząca do wyniku.

Plan wykonania zapytania jako mapa pracy bazy danych

Plan wykonania zapytania to praktyczny opis sposobu, w jaki system zarządzania bazą danych zamierza odnaleźć, połączyć, przefiltrować i uporządkować dane potrzebne do udzielenia odpowiedzi. Nie jest to treść samego zapytania SQL, lecz jego opracowany przez optymalizator wariant wykonawczy. W planie przedstawiane są operacje wykonywane przez silnik bazy, ich kolejność, zależności między etapami oraz przewidywany koszt obliczeniowy. Dzięki temu da się prześledzić drogę od odczytu danych do końcowego zestawu wierszy.

W najprostszym przypadku plan może obejmować odczyt tabeli, zastosowanie warunku w klauzuli WHERE i zwrócenie pasujących rekordów. Przy bardziej rozbudowanych zapytaniach pojawiają się łączenia tabel, grupowanie, sortowanie, eliminowanie duplikatów, obliczanie funkcji agregujących oraz ograniczanie liczby wyników. Każda z tych czynności wymaga zasobów procesora, pamięci operacyjnej i dostępu do nośnika danych. Plan wykonania zapytania pozwala powiązać konkretną operację z jej przewidywanym wpływem na czas odpowiedzi.

Warto odróżnić plan szacowany od planu rzeczywistego. Plan szacowany powstaje przed uruchomieniem zapytania i pokazuje przewidywania optymalizatora. Plan rzeczywisty jest rejestrowany podczas wykonania, dlatego może zawierać rzeczywistą liczbę przetworzonych wierszy, faktyczne czasy etapów oraz informacje o użyciu pamięci. Różnica między tymi dwoma widokami bywa najważniejszym śladem prowadzącym do przyczyny spowolnienia.

Plan wykonania zapytania a wybór metody dostępu

Jednym z podstawowych elementów planu jest sposób odczytywania danych. Pełne skanowanie tabeli oznacza przejście przez znaczną część jej zawartości i sprawdzenie rekordów pod kątem warunku. Taki sposób może być uzasadniony, gdy tabela jest niewielka albo gdy zapytanie zwraca dużą część jej wierszy. W przypadku selektywnego warunku korzystniejszy bywa dostęp przez indeks, który pozwala szybciej wskazać pasujące wartości, a następnie pobrać odpowiadające im rekordy.

Sam fakt istnienia indeksu nie oznacza, że zostanie on użyty. Optymalizator porównuje przewidywane koszty różnych rozwiązań. Bierze pod uwagę między innymi rozmiar tabeli, rozkład wartości, selektywność warunku, kolejność kolumn w indeksie, rodzaj porównania oraz liczbę oczekiwanych wyników. Jeżeli odczyt przez indeks wymagałby później pobrania bardzo dużej liczby rekordów z różnych miejsc, pełne skanowanie może zostać uznane za rozwiązanie tańsze.

W planie spotyka się także operacje dostępu pokrywającego, gdy wszystkie potrzebne kolumny znajdują się w indeksie. W takiej sytuacji nie trzeba wracać do głównej struktury tabeli po dodatkowe dane. Przy analizie należy jednak pamiętać, że indeks zwiększa także koszt modyfikacji danych i zajmuje przestrzeń. Projektowanie indeksów powinno wynikać z rzeczywistych zapytań, a nie z samego dążenia do posiadania jak największej liczby struktur pomocniczych.

Przydatną wskazówką jest odczytanie estymowanej liczby wierszy na każdym etapie. Gdy przewidywana liczba jest zbliżona do rzeczywistej, statystyki opisujące dane prawdopodobnie są aktualne. Gdy wartości znacznie się różnią, optymalizator może wybrać metodę niepasującą do aktualnego rozkładu danych. Wtedy trzeba sprawdzić statystyki, warunki zapytania, sposób przekazywania parametrów oraz zmiany zachodzące w tabelach.

Plan wykonania zapytania przy łączeniu i sortowaniu danych

Zapytania obejmujące kilka tabel są przedstawiane jako zestaw operacji łączenia. Kolejność łączenia ma znaczenie, ponieważ wcześniejsze ograniczenie liczby wierszy może zmniejszyć pracę wykonywaną w kolejnych etapach. W planie może zostać zastosowane łączenie zagnieżdżone, łączenie haszujące albo łączenie przez uporządkowane zakresy. Każda metoda ma inne zastosowanie i zależy od liczby rekordów, dostępnych indeksów, warunków porównania oraz przewidywanego rozmiaru wyniku pośredniego.

Łączenie zagnieżdżone może być korzystne, gdy jeden zbiór jest niewielki, a dla każdego jego wiersza da się szybko znaleźć pasujące rekordy w drugiej tabeli. Łączenie haszujące bywa użyteczne przy większych zbiorach, gdy tworzenie struktury pomocniczej pozwala sprawnie dopasować wartości. Łączenie oparte na uporządkowanych danych może ograniczyć dodatkową pracę, jeśli oba źródła są dostępne w odpowiedniej kolejności. Nie istnieje jedna metoda właściwa dla każdego przypadku, dlatego ocena powinna obejmować cały plan, a nie pojedynczy operator.

Sortowanie może pojawić się przy ORDER BY, DISTINCT, części operacji grupowania albo przy realizacji określonego sposobu łączenia. Jeżeli porządek danych odpowiada istniejącemu indeksowi, dodatkowe sortowanie może nie być potrzebne. W przeciwnym razie dane mogą zostać umieszczone w pamięci, a przy większym rozmiarze także w tymczasowych plikach. Wysoki koszt sortowania nie zawsze oznacza błąd, lecz powinien skłonić do sprawdzenia, czy sortowany jest pełny zbiór, czy da się wcześniej zastosować filtr oraz czy kolejność kolumn jest zgodna z potrzebą zapytania.

Podobnej analizy wymagają operacje grupowania. Grupowanie po kolumnie o niewielu różnych wartościach może prowadzić do dużych grup pośrednich, natomiast grupowanie po dobrze ograniczonym zbiorze zwykle wymaga mniej pracy. W planie warto obserwować zarówno liczbę wierszy wejściowych, jak i wynikową liczbę grup. Pozwala to ocenić, czy koszt wynika z samej agregacji, czy z nadmiarowych danych przekazywanych do tego etapu.

Plan wykonania zapytania i typowe przyczyny spowolnień

Najczęściej problem nie znajduje się w pojedynczej instrukcji, lecz w rozbieżności między założeniami optymalizatora a rzeczywistym stanem danych. Nieaktualne statystyki mogą sugerować równomierny rozkład wartości, mimo że jedna wartość występuje wyjątkowo często. W efekcie wybierany jest indeks, który sprawdza się dla małej liczby wyników, lecz przy konkretnym parametrze prowadzi do wielu dodatkowych odczytów. Innym razem zakładana liczba rekordów jest zaniżona, przez co przydzielana pamięć okazuje się niewystarczająca.

Problemy bywają także związane z warunkami zapisanymi w sposób utrudniający użycie indeksu. Przekształcenie kolumny w funkcji, niejawna konwersja typów, zastosowanie symbolu wieloznacznego na początku wyszukiwanego tekstu lub porównanie niezgodnych typów może ograniczyć możliwość szybkiego dostępu. Nie oznacza to, że każda funkcja w warunku jest niedopuszczalna. Wymaga się jednak sprawdzenia planu i porównania kilku równoważnych zapisów zapytania.

Na czas odpowiedzi wpływają również blokady, konkurencyjne operacje, ograniczenia pamięci oraz oczekiwanie na odczyt danych. Plan wykonania zapytania pokazuje drogę przetwarzania, lecz nie zawsze wyjaśnia całe zachowanie aplikacji. Gdy plan wygląda poprawnie, a wykonanie nadal trwa długo, warto sprawdzić czas oczekiwania, obciążenie systemu, połączenie z bazą oraz częstotliwość uruchamiania danego zapytania. Pojedyncze wykonanie może być szybkie, natomiast tysiące powtórzeń tej samej operacji mogą powodować zauważalne opóźnienia.

Nie należy automatycznie poprawiać planu przez wymuszanie konkretnego indeksu lub metody łączenia. Takie rozwiązanie może pomóc dla jednego zestawu danych, lecz pogorszyć działanie po zmianie rozmiaru tabeli albo parametrów. Bezpieczniejsza jest obserwacja planu przed zmianą, wprowadzenie jednej modyfikacji i ponowne porównanie czasu, liczby odczytów, zużycia pamięci oraz liczby przetworzonych wierszy.

Plan wykonania zapytania w praktycznej diagnostyce

Analizę warto rozpocząć od zebrania zapytania, parametrów i informacji o warunkach, w których występuje opóźnienie. Następnie należy odczytać plan oraz zaznaczyć operację o najwyższym koszcie, największej liczbie wierszy lub największej różnicy między estymacją a wykonaniem. Koszt procentowy jest wskazówką w obrębie konkretnego planu, ale nie powinien być traktowany jako uniwersalna miara jakości. Niekiedy etap o pozornie małym udziale powoduje przekazanie ogromnej liczby wierszy do następnej operacji.

Przy porównywaniu dwóch planów należy zachować ten sam zakres danych i te same parametry. Warto zestawić czas pierwszego wykonania z czasem kolejnych wykonań, ponieważ dane mogą być już obecne w pamięci. Istotne są także liczby odczytów logicznych i fizycznych, zużycie pamięci, liczba wierszy na wyjściu z poszczególnych operatorów oraz ewentualne ostrzeżenia. Dopiero taki zestaw informacji pozwala ocenić, czy poprawa jest rzeczywista, czy wynika wyłącznie ze zmienionych warunków testu.

W praktyce przydatne jest zachowanie planów przed i po zmianie w osobnych plikach lub w narzędziu do monitorowania. Dzięki temu można wrócić do wcześniejszego wariantu, gdy modyfikacja pogorszy inne zapytania. Należy również testować przypadki skrajne: brak wyników, bardzo dużą liczbę wyników oraz parametry często występujące. Plan korzystny dla jednego przypadku może być niekorzystny dla innego.

Jeżeli spowolnienie dotyczy całego komputera, aplikacji lub dostępu do danych, pomocna może być konsultacja z serwisem Warszawskie Pogotowie Komputerowe 24/7. Przed zgłoszeniem warto przygotować opis objawu, moment jego występowania, nazwę używanego programu oraz zrzut planu, jeśli jest dostępny. Ułatwia to oddzielenie problemu z samym zapytaniem od kłopotów z konfiguracją, pamięcią, nośnikiem danych lub działaniem systemu.