• Artykuł

          

Plany zapytań i co z nich wynika Udostępnij na: Facebook

Autor: Jacek Włodarski

Opublikowano: 2011-07-01

  1. Wstęp
  2. Czy mamy wpływ na zachowanie naszej bazy danych?
  3. A zatem jaką mam pewność, że zapytanie jest realizowane w najlepszy sposób?
  4. Czy da się lepiej?
  5. Czy mogę dodać swój plan wykonania zapytania?
  6. Podsumowanie

Wstęp

W tym artykule chciałbym w krótki sposób wyjaśnić, czym są plany zapytań oraz jak z ich pomocą możemy wpływać na wydajność bazy danych.

Czy mamy wpływ na zachowanie naszej bazy danych?

Jednym z głównych założeń systemów zarządzania bazami danych jest to, że użytkownik końcowy określa co dokładnie go interesuje i nie zastanawia się, w jak sposób zostanie mu to dostarczone. Interesuje go przede wszystkim efekt końcowy, tzn. czy zostaną zwrócone właściwe dane, te, o które pytał. Natomiast sposobem uzyskania tego wyniku zajmuje się już system zarządzania bazą danych, który określi najlepszy według niego sposób uzyskania wyników, o które prosił użytkownik – jest to stworzenie tzw. planu zapytania. Pamiętajmy jednak, że oprócz uzyskania interesujących nas informacji, równie ważna jest szybkość wykonania tego zapytania. Jeśli wyjdziemy z założenia, że czas nie jest tutaj ważny, bo chodzi przecież o poprawne wyniki, to zastanówmy się, co nam z zapytania, które będzie realizowane nieoptymalną drogą, np. sprawdzany będzie każdy rekord po kolei przez dwie godziny, jeśli wyniki potrzebujemy natychmiast.  

A zatem jaką mam pewność, że zapytanie jest realizowane w najlepszy sposób?

Do tego celu stosowany jest optymalizator zapytań, który jest jednym z najważniejszych elementów wchodzących w skład SQL Server. Dla zapewnienia wydajności niezbędne jest, aby system zarządzania bazą danych wykorzystał optymalną drogę, czyli najmniej kosztowny plan zapytania. Optymalizator zapytań systemu zarządzania bazą danych wykorzysta najlepsze rozwiązanie spośród tych, które posiada. Te rozwiązania nazywane są planami wykonania zapytań i znajdują się w buforze zapytań.

Czy da się lepiej?

Nasuwa się myśl, że rozwiązanie wybrane przez optymalizator nie musi być wcale rozwiązaniem optymalnym. Jeśli na przykład bardzo dobrze znamy bazę danych, może się zdarzyć, że będziemy w stanie przygotować lepszy plan zapytania. Warto więc się przyjrzeć, jak wygląda taki plan wykonania zapytania proponowany dla danego przypadku przez optymalizator, czyli tzw. EXPLAIN PLAN. Jest to swego rodzaju symulacja, która pokaże, w jaki sposób silnik bazy danych będzie wykonywał zapytanie na bazie. Z planu wykonania można odczytać, jakie indeksy będą użyte, które tabele będą skanowane w całości, jaka będzie kolejność wykonania zapytania. SQL Server dostarcza do tego celu narzędzia Query Analyzer. Jeśli zapytanie ma zwrócić niewielki procent wierszy przetwarzanych tabel, to warto w takiej sytuacji szukać możliwości użycia indeksu. Pełne skanowanie na pewno wydłuży czas. Zasady i wytyczne pomagające właściwie dobrać indeksy i sposób zarządzania nimi w bazie danych zostały opisane w artykule „Indeksy”. Należy pamiętać przy tym, że każda baza danych jest inna i wypracowanie optymalnych procesów wymaga nieraz sporego zaangażowania i czasu poświęconego na testy i analizy. Czas ten jednak bardzo szybko się zwróci.

Silnik bazy danych tworzy statystyki każdej tabeli, a na podstawie zgromadzonych statystyk układane są plany zapytań. Plany zapytań są tworzone dodatkowo na podstawie analizy parametru określającego koszt czasu dostępu do losowych fragmentów dysku, w stosunku do danych odczytywanych sekwencyjnie. Im mniejsza wartość parametru odpowiadającego za koszt czasu dostępu do losowych fragmentów dysku, tym częściej są wykorzystywane indeksy.

Za pomocą polecenia EXPLAIN możemy wskazać, gdzie powinniśmy dodać indeksy do tabel, aby otrzymać szybsze wykonanie operacji SELECT, które w tym przypadku użyje indeksów do znalezienia wierszy. Poza tym można użyć EXPLAIN do sprawdzenia, czy optymalizator łączy tabele w optymalnej kolejności. Jeśli z kolei mamy problem z tym, że nie są używane indeksy, które według Ciebie powinny być używane, należy uruchomić polecenie ANALYZETABLE – aby zaktualizować statystyki, np. ilość kluczy, które mogą wpływać na decyzje podejmowane przez optymalizator.

Podgląd planu zapytań dla polecenia SELECT silnika bazy danych giełdy papierów wartościowych wygląda mniej więcej tak:

**Rys.1. Przykładowy podgląd planu zapytania dla polecenie SELECT.

Najważniejszą częścią wyświetlaną w tym miejscu jest szacowany koszt wykonania instrukcji, o które zapytaliśmy, tzn. jak długo będzie wykonane zapytanie.

Jak widać, w pierwszej kolejności serwer szuka w tabeli wierszy, w których atrybut id_sesji jest mniejszy od 10. W tym celu używa indeksu, a następnie filtruje wiersze, używając w tym celu indeksu, a następnie filtruje wiersze spełniające warunek id_spolki = 100.

Pierwszy wiersz skanuje szacunkowy koszt zapytania:

**Rys.2.  Szacunkowy koszt wykonania zapytania.

0.00 –szacowany koszt rozpoczęcia zapytania (np. sortowania),

115.95 –szacowany całkowity koszt generowania wyniku na zapytanie,

8 –szacowana liczba wyników (na podstawie zgromadzonych statystyk),

46 –szacowana średnia wielkość (w bajtach) pojedynczej krotki wyniku.

Czy da się to zrobić wydajniej? Spróbujmy to zweryfikować. Co się stanie, kiedy do bazy danych dodamy kolejny indeks?

**Rys.3. Koszt planu wykonania zapytania po dodaniu  kolejnego indeksu do bazy danych.

W tym przypadku zamiast operacji filtrowania wyników, jak miało to miejsce w pierwszym przypadku, wykorzystany został drugi indeks. Wynikiem tego jest znaczne zmniejszenie szacowanego czasu generowania odpowiedzi. Poza tym widać, że tym razem przy realizacji zapytania zostaną wykonane dwa podzapytania. Wyniki tych podzapytań zostaną wynikowo połączone, natomiast dodatkowo otrzymamy wgląd w ich szacunkowe koszty oraz liczbę wierszy.

Wszystkie uzyskane wartości były do tej pory jedynie szacunkowe. Jeśli chcemy wyświetlić dokładne dane dotyczące sposobu realizacji zapytania, należy do słowa EXPLAIN dodać słowo kluczowe ANALYZE. Wiązało się to będzie jednak z faktycznym zrealizowaniem zapytania przez silnik bazy danych. O ile przy takim wykonaniu zapytania SELECT rzeczywiście nie narażamy bazy na duże ryzyko, to w przypadku próby dokładnej analizy któregoś polecenia modyfikującego dane, jak na przykład DELETE, to zmiany te w bazie zostaną faktycznie wykonane:

**Rys.4. Szczegółowy koszt planu wykonania zapytania przy użyciu komendy „explain analyze”.

Po wykonaniu takiego zapytania w odpowiedzi znalazły się tym razem informacje na temat dokładnego czasu generowania poszczególnych wyników na podzapytania, a także TOTALTIME, czyli całkowity czas uzyskania odpowiedzi.

Jeśli chcemy jednak przetestować polecenia modyfikujące dane (DELETE, UPDATE, INSERT) bez wprowadzania zmian do bazy danych, należy zamknąć zapytanie w bloku BEGIN … ROLLBACK,

BEGIN;

EXPLAINANALYZE ...;

ROLLBACK;

**Rys.5. Szczegółowy koszt planu wykonania zapytania przy użyciu komendy „explain analyze” bez wykonania operacji na bazie z użyciem „BEGIN … ROLLBACK”.

Zostały zwrócone nam dokładne informacje dotyczące wykonania takiego zapytania, a zawartość bazy danych pozostała niezmieniona.

W momencie pomiaru kosztów czasowych poszczególnych węzłów w planie wykonania bieżąca implementacja EXPLAINANALYZE może zawierać znacznie większy koszt wykonania zapytania. W związku z uruchomieniem EXPLAINANALYZE w zapytaniu operacja ta niekiedy może trwać znacznie dłużej niż wykonywana normalnie przez zapytanie. A wielkość nadwyżki zależy od rodzaju zapytania. 

Czy mogę dodać swój plan wykonania zapytania?

Serwer SQL 2008 umożliwia wybranie planu zapytania z pliku XML – za pomocą procedury sp_create_plan_guide oraz procedury składowanej sp_create_plan_guide_from_handle. Umożliwia ona utworzenie jednego lub więcej planów wykonań zapytań w buforze.

Aby dodać plan zapytania przy użyciu procedury sp_create_plan_guide_from_handle, niezbędne jest określenie parametrów takich jak:

@name –określający nazwę planu wykonania zapytania,

@plan_handle – będący identyfikatorem planu zapytania,

@statement_start_offset – parametr ten jednoznacznie określał będzie pozycję tego planu zapytania w paczce.

Po określeniu i zdefiniowaniu naszego planu zapytania bardzo ważne jest następnie zweryfikowanie poprawności sugerowanego planu wykonania zapytania. Do tego celu służy funkcja tablicowa: sys.fn_validate_plan_guide.

Przy definiowaniu planów zapytań będzie zależało nam przede wszystkim na wykorzystaniu konkretnego zdefiniowanego przez nas indeksu w celu osiągnięcia płynących z tego korzyści. SQL Server w wersjach niższych niż 2008 posiadał do tej pory możliwość proponowania takiego indeksu, który miałby zostać użyty przez optymalizator przy realizacji danego zapytania, jednakże była to jedynie sugestia z naszej strony, a optymalizator wcale nie gwarantował wykorzystania naszej propozycji. W wersji SQL Server 2008 mamy możliwość wymuszenia operacji przeszukania danego indeksu podczas pobierania danych z tabeli. Należy do tego wykorzystać tzw. wskazówkę o nazwie FORCESSEK. Daje to administratorowi bardzo dużą władzę nad tym, jak w praktyce zostanie wykonane zapytanie. Jeśli znajomość bazy danych oraz doświadczenie przy pracy z nią mówią nam, że wydajniejsza będzie operacja przeszukania indeksu, niż to, co proponuje optymalizator, możemy bez wahania wykorzystać tę wskazówkę, odnosząc przy tym wymierne korzyści. Należy pamiętać jednak również o tym, że to, co do tej pory dawało najlepszy rezultat, po zmianach w zawartości tabel wynikających z jej użytkowania może okazać się po pewnym czasie bardziej kosztowne, niż rozwiązanie proponowane przez optymalizator. Jeśli chodzi o zapytania parametryzowane, to mamy możliwość uproszczenia planów zapytań oraz zmniejszenie ilości tych zapytań w buforze planów zapytań. Mamy również możliwość określenia, który plan zapytania zostanie użyty dla danego określonego wzorca kwerendy, a zatem mamy możliwość większej ingerencji w to, jak w praktyce będzie wykonywane dane zapytanie.

 

Podsumowanie

W tym artykule omówiliśmy, czym są plany zapytań i jak je praktycznie wykorzystać, oraz jak zdefiniować swój plan zapytań i wykorzystać go przy realizacji zapytania.