Bardziej wydajne SQL poprzez kolejkowanie zapytań i optymalizację

Teraz, gdy znasz już wiele sposobów korzystania z danych i korzystasz z SELECT na wielu tabelach, to dobry czas, aby porozmawiać o wydajności zapytań SQL - jak szybko się wykonują, co zrobić, aby wykonywały się szybciej?
SQL jest językiem deklaratywnym - każde zapytanie deklaruje co chcemy, aby silnik SQL zrobił, ale nie mówimy mu jak.  Jak się okazuje, to właśnie  jak -- "plan" -- ma wpływ na wydajność zapytań i jest on bardzo ważny.

Dlaczego należy opracować plan zapytań SQL?

Na przykład, załóżmy, że mamy takie proste zapytanie:
SELECT * FROM books WHERE author = "J K Rowling";
W tym zapytaniu, SQL może znaleźć wynik na dwa sposoby:
  • Wykonać "pełny skan tabeli": sprawdzić każdy wiersz tabeli, a następnie zwrócić zgodne wiersze.
  • Stworzyć "index": Stworzyć kopię tabeli posortowanej według autora, wykonać wyszukiwanie binarne, aby znaleźć wiersz, którego autor to "J K Rowling", znaleźć pasujące ID, a następnie wykonać wyszukiwanie binarne na oryginalnej tabeli, które zwróci wiersze, których ID się zgadzają.
Który sposób jest szybszy? To zależy od danych i od tego jak często zapytanie będzie wykonywane. Jeśli tabela zawiera 10 wierszy, to pełne skanowanie tabeli będzie sprawdzać tylko 10 wierszy, więc w tym wypadku będzie to wydajne rozwiązanie.
Jeśli tabela zawierałaby 10 milionów wierszy, to pełne skanowanie wymagałoby sprawdzenia 10 milionów wierszy. Szybciej byłoby wykonać wyszukiwanie binarne na posortowanej tabeli - potrzebowalibyśmy tylko 23 wyszukiwania, aby znaleźć wartość z 10 milionów wierszy. Jednakże, stworzenie posortowanej tabeli trochę potrwa (około 230 milionów operacji, w zależności od silnika). Gdybyśmy wykonywali to zapytanie wiele razy (więcej niż 23 razy) lub mielibyśmy już stworzoną tabelę, to druga opcja byłaby lepsza.
Jak silnik SQL ma zdecydować, którą opcję wybrać? To ważny krok o którym wcześniej nie rozmawialiśmy, ponieważ do tej pory skupialiśmy się na składni zapytań, nie na implementacji. Gdy będziesz korzystał z SQL na dużych bazach danych, ten krok będzie bardzo ważny.

Cykl życia zapytania SQL

Możemy założyć, że silnik SQL przechodzi przez poniższe etapy dla każdego zapytania, które mu nadamy:
Parsowanie, Optymalizacja i Wykonywanie
  1. Parser zapytań upewnia się, że zapytanie jest prawidłowe składniowo (np. czy przecinki są we właściwym miejscu) i semantycznie (czy tabele istnieją), a następnie zwraca błąd jeśli coś się nie zgadza. W przeciwnym wypadku, zapytanie przekształcane jest w wyrażenie algebraiczne i całość przechodzi do następnego kroku.
  2. Planowanie i optymalizacja zapytania wykonuje trudną pracę. Na początku przeprowadzane są proste optymalizacje (ulepszenia, które zawsze prowadzą do zwiększenia wydajności, na przykład zmiana 5*10 na 50). Następnie bierze pod uwagę różne "plany zapytań", które są optymalizowane w inny sposób, sprawdza koszt (CPU i czas) każdego planu zapytania na podstawie ilości wierszy i tabeli, a następnie wybiera najbardziej optymalny plan i przekazuje go do następnego kroku.
  3. Egzekutor zapytań przekształca plan w operacje na bazie danych i zwraca nam wynik, jeśli jakiś istnieje.

Gdzie w tym wszystkim zadanie dla ludzi?

Planowanie i optymalizacja jest wykonywana dla każdego zapytania i mógłbyś używać zapytań SQL nie zdając sobie z tego sprawy. Jednakże, gdy zaczniesz pracować z dużymi zbiorami danych, będziesz zwracać uwagę na szybkość wykonywania zapytań i być może będziesz zastanawiał się czy jest jakiś sposób na przyspieszenie wykonywania zapytań.
Często, szczególnie w wypadku skomplikowanych zapytań, są sposoby na optymalizację zapytań i znamy ten proces jako "dopracowywanie zapytań".
Pierwszym krokiem jest określenie, które zapytania można poprawić, a możesz to sprawdzić za pomocą narzędzi, takich jak profiler SQL. Czasem, źle napisane zapytania rozpoznasz po tym, że ich wykonanie zajmuje tak długo, że baza danych jest przeciążona. Mam nadzieję, że przekonasz się o tym dość szybko.
Następnym krokiem jest zrozumienie jak dany silnik SQL wykonuje zapytanie, a każdy system SQL ma zaimplementowane wytłumaczenie jak działa silnik. W SQLlite możesz zaufać EXPLAIN QUERY PLAN, aby sprawdzić jak zachowuje się SQL za kulisami. Jeśli skorzystasz z tej funkcji przygotuj się do przejrzenia dokumentacji EXPLAIN QUERY PLAN, ponieważ "wytłumaczenie" jest dość szczegółowe, a implementacja dokładna. Jeśli korzystasz z innego silnika SQL, możesz poszukać "how do I get an execution plan in X".
Teraz trudna część: ręczna optymalizacja, która poprawi plan. To część, która często zależy od silnika SQL z którego korzystasz i od szczególnych cech danych.
Na przykład, pamiętasz to zapytanie, o którym mówiliśmy powyżej? Gdybyśmy wiedzieli, że chcemy wykonać setki zapytań, które wykorzystywały WHERE na kolumnie 'autor', to moglibyśmy stworzyć indeks korzystając z CREATE INDEX. Następnie silnik SQL mógłby użyć tego indeksu do wydajniejszego znalezienia pasujących wierszy. Możesz przeczytać ten artykuł na temat planowania zapytań SQLite, który pomoże ci zrozumieć kiedy indeksy pomagają w wykonywaniu zapytań.
Tworzenie indeksów pozwala na szybsze wykonywanie powtarzalnych zapytań. Istnieje również wiele innych metod. Możesz przeczytać więcej o tym zagadnieniu dla SQLite w przegląd planowania zapytań pamiętaj o dokładnym sprawdzeniu sekcji "manual".
Nie możemy omówić wszystkich zawiłości optymalizacji zapytań, więc polecam ci głębsze poznanie tego tematu, gdy będziesz go potrzebował.
(Poniżej kilka interesujących artykułów na temat planowania SQL: Optymalizator Zapytań Serwerowych SQLPoprawianie Oracle SQLPodstawy Planu Wykonywania MSSQL)