Strojenie baz danych - optymalizacja składni zapytań SQL
kotu - 21 kwiecień, 2009
Kategorie:
Schemat bazy danych może zawierać wiele elementów zwiększających wydajność (np. indeksy, statystyki, procedury składowe) jednak żaden z tych potencjalnie efektywnych środków może nie spełnić swojej roli, jeśli konstrukcja używanych przez nas zapytań będzie nieprawidłowa. Przykładowo zapytanie SQL może nie wykorzystywać efektywnie dostępnych indeksów a struktura samego zapytania może prowadzić do (możliwego do uniknięcia) zwiększenia kosztów wykonania zapytania. Czasami też pisząc wydawałby się poprawne zapytanie piszemy je w sposób „proceduralny”, określający po kolei mające zostać wykonane operacje zamiast położyć nacisk na określenie poszukiwanego zbioru zostawiając elastyczność w dobrze planu wykonania zapytania optymalizatorowi. Celem niniejszym eksperymencie jest zobrazowanie problemów wynikających z użycia niepoprawnej składni języka SQL oraz wskazanie rozwiązań bardziej optymalnych. Zamierzam porównywać zbliżone ze sobą konstrukcje języka SQL analizując plany wykonanych z ich pomocą zapytań i inne statystyki.
W trakcie testów zamierzam poruszyć następujące obszary:
- Operowanie na możliwie najmniejszych zbiorach
- Efektywne wykorzystanie indeksów ·
- Unikanie „zasobożernych” zapytań
- Ostatnim etapem bardziej próba optymalizacji bardziej złożonego zapytania z wykorzystaniem omówionych zależności
Testy wydajnościowe przeprowadzane były w środowisku SZBD SQL Server 2005 z włączonymi opcjami statystyk (SET STATISTICS TIME ON, SET STATISTICS IO ON). Każdorazowo przed spisaniem wyników czyszczony był bufor serwera oraz cache procedur (DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE). Testową bazą danych była ogólnie dostępna baza „AdventureWorks”, którą dodatkowo (w celu uwidocznienia różnic wydajności) wypełniono losowymi danymi (oprogramowanie SQL Data Generator firmy RedGate).
Optymalizacja składni zapytań
Często, aby uzyskać ten sam rezultat możemy zapytanie napisać na wiele różnych sposobów, w wielu przypadkach optymalizator wygeneruje ten sam (optymalny) plan wykonania zapytania bez względu na strukturę naszego zapytania. Czasami optymalizator jest w stanie „poprawić” nasze błędy, zastępując niektóre niewydajne konstrukcje. Niestety w niektórych przypadkach struktura zapytania nie pozowali optymalizatorowi na wybór optymalnej strategii, stąd też tak istotna jest wiedza o tym, kiedy takie sytuacje mogą wystąpić i jak ich unikać. Generalnie powinniśmy się trzymać ogólnie przyjętych reguł, których kilka postaram się przedstawić wraz z przykładami i pomiarami wydajności.
1. Operowanie na możliwie najmniejszych zbiorach
Podstawowym sposobem na zwiększenie wydajności zapytania jest ograniczeni ilości danych, na jakich ono operuje (zarówno wiersze jak i kolumny). Zmniejszenie ilości przetwarzanych danych obniża ilość potrzebnych zasobów oraz zwiększa efektywność działania indeksów. Podstawowym metodami wpływającymi na obniżenie ilości przetwarzanych danych będzie użycie wysoce selektywnych warunków w instrukcjach WHERE oraz ograniczenie listy kolumn w instrukcji SELECT.
1.1 Ograniczenie ilości kolumn
Zawsze należy starać się możliwie jak najbardziej ograniczyć listę kolumn w zapytaniu SELECT. Dla przykładowego zapytania (indeks pogrupowany na kolumnie „TerritoryID” oraz niepogrupowany na „Name”):
SELECT [Name], TerritoryID
FROM Sales.SalesTerritory AS st
WHERE st.[Name] = 'Australia'
Wykonywana jest wyłącznie operacja wyszukiwania w indeksie:
(1 row(s) affected)
Table 'SalesTerritory'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
Z kolei dla zapytania:
SELECT *
FROM Sales.SalesTerritory AS st
WHERE st.[Name] = 'Australia'
Generowany jest mniej wydajny plan wykonania zapytania(w poprzednim przypadku jest tylko jedno wyszukiwanie po indeksie):
(1 row(s) affected)
Table 'SalesTerritory'. Scan count 0, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.
1.2 Używanie wysoce selektywnych warunków w instrukcji WHERE
Selektywność warunku WHERE wpływa na efektywność użycia indeksu założonego na danej kolumnie. Zapytanie o niskiej selektywność może uniemożliwić lub (w przypadku indeksu niepogrupowanego) na tyle zwiększyć koszt całej operacji(dużo logicznych/fizycznych odczytów), iż optymalizator po prostu z rezygnuje użycia tego indeksu.
Przykładowo poniższe zapytanie(indeks niepogrupowany na ProductID a pogrupowany na (SalesOrderID,SalesOrderDetailID)) zwraca 228 rekordów.
SELECT SalesOrderID,UnitPrice
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = 776
Jak widać użyty został indeks niepogrupowany założony na kolumnie „ProductID”:
(228 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 710, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 42 ms.
Jeśli w tym samym zapytaniu zmienimy ID Produktu, zwrócone zostanie 705 wierszy.
SELECT SalesOrderID,UnitPrice
FROM Sales.SalesOrderDetail AS sod
WHERE sod.ProductID = 793
(705 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 1238, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 179 ms.
Jak się okazuje rozmiar zwracanych danych jest już na tyle duży, że optymalizator ze względu na potencjalny koszt operacji całkowicie rezygnuje z użycia niepogrupowanego indeksu. Mając wątpliwości czy optymalizator postąpił słusznie możemy go zmusić do użycia utworzonego przez nas na kolumnie „ProductID” indeksu:
SELECT SalesOrderID,UnitPrice
FROM Sales.SalesOrderDetail AS sod
WITH (INDEX (IX_SalesOrderDetail_ProductID))
-- wymuszamy uzycie indeksu
WHERE sod.ProductID = 793
(705 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 2173, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 217 ms.
Uzyskane wyniki potwierdzają dużą zależność selektywności warunków instrukcji WHERE z wydajnością całego zapytania. W tym wypadku zmniejszenie ilości pobieranych danych oprócz wartości samej w sobie ma jeszcze istotną wartość dodaną w postaci zwiększenia efektywności wykorzystania zastosowanych indeksów.
2. Efektywne wykorzystanie indeksów
Jak widać na wcześniej przedstawionym przykładzie zastosowanie indeksów jest bardzo istotnym czynnikiem mającym bezpośredni wpływ na wydajność naszych zapytań. Niemniej jednak samo założenie indeksów nie wystarcza – równie istotna jest odpowiednia struktura zapytań, która umożliwia efektywne ich wykorzystanie. W trzech kolejnych podpunktach postaram się przedstawić podstawowe zasady wspomagające wykorzystanie indeksów.
2.1 Unikanie „nieodpowiednich” warunków wyszukiwania
W literaturze anglojęzycznej warunki zapewniające należytą wydajność podczas wyszukiwania bywają określane, jako „sargable”, jest to akronim utworzony ze skrótu „Serach ARGument ABLE”. Ogólnie rzecz ujmując warunki te umożliwiają optymalizatorowi użycie indeksu założonego na kolumnie, do której się odnoszą (instrukcja WHERE). Istnieją również warunki będące ich przeciwieństwem („nonsargable”), których powinniśmy unikać.
Poniższa tabela przedstawia zestawianie popularnych warunków z obu w/w grup.
|
Sargable
|
=,>,>=,<,<=, BETWEEN, LIKE ’<znaki>%’
|
|
Nonsargable
|
<>,!=,!>,!<,NOT EXISTS, NOT IN, IN, OR, NOT LIKE, LIKE ’%<znaki>%’
|
Sprawdźmy teraz jak wygląda porównanie wydajności zapytań wykorzystujących te operatory.
Operatory BETWEEN i IN
Porównajmy 2 zapytania:
SELECT sod.*
FROM Sales.SalesOrderDetail AS sod
WHERE sod.SalesOrderID IN (51820,51821,51822,51823,51824,51825)
i:
SELECT sod.*
FROM Sales.SalesOrderDetail AS sod
WHERE sod.SalesOrderID BETWEEN 51820 AND 51825
Oba zapytania zwrócą ten same dane, ponadto optymalizator generuje dla nich taki sam plan wykonania:
Jednak po bliższym przyjrzeniu się statystykom można zauważyć, że zapytanie z instrukcją IN zostanie „rozpisane” na 6 warunków OR z kolei BETWEEN potraktowane zostanie jak dwa warunki >= i <= :
<- IN BETWEEN ->
W rezultacie w pierwszym przypadku indeks odczytywany jest 6-kronie dla każdego warunku OR, dla instrukcji BETWEEN odczytywane jest tylko raz. Oczywiście przekłada się to na różnicę w czasie wykonania obu zapytań (odpowiednio 407 i 282 ms).
Table 'SalesOrderDetail'. Scan count 6, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 407 ms.
Table 'SalesOrderDetail'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 282 ms.
Widzimy więc korzyść wynikająca z zastosowania instrukcji BETWEEN zamiast instrukcji IN lub kilku instrukcji OR. Oczywiście od razu możemy użyć kombinacji >= i <= zamiast BETWEEN.
Czasami optymalizator jest w stanie automatycznie przekonwertować nasz niezbyt dobry pod kątem wyszukiwanie warunek do postaci umożliwiającej wykorzystanie indeksu. Tą bardzo dobrą jeśli chodzi o optymalizacje wydajności cechę optymalizatora przedstawię i przetestuję na przykładzie warunków LIKE oraz !<.
Używając warunku LIKE w instrukcji WHERE powinniśmy na jego początku (jeżeli to możliwe) używać kilku znaków, pozwoli to optymalizatorowi „przepisać” instrukcję LIKE na warunek sprzyjający wykorzystaniu indeksu. Im dłuższy ciąg znaków zastosujemy w warunku LIKE tym lepiej wykorzystany zostanie indeks. Należy wystrzegać się użycia ’%’ jako pierwszego znaku w warunki LIKE ponieważ blokuje to możliwość użycia indeksu na rzecz kosztownego odczytywania całej tabeli.
Oto przykładowe zapytanie pokazujące zasadę działania optymalizatora.
SELECT c.CurrencyCode
FROM Sales.Currency AS c
WHERE c.[Name] LIKE 'Pol%'
Table 'Currency'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
Jak widzimy powyższe zapytanie zastało automatycznie przekonwertowane do zapytania mu równoważnego wspierającego użycie indeksu (operatory >= i <), czyli:
SELECT c.CurrencyCode
FROM Sales.Currency AS c
WHERE c.[Name] >= 'Pol'
AND c.[Name] < 'PoM'
Table 'Currency'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.
Pomimo, że operatory „!<” oraz „>=” zwracają ten sam zbiór mogą być wewnętrznie przetwarzane w zupełnie inny sposób. Operator „>=” pozwala optymalizatorowi na użycie indeksu (na kolumnie do której odnoszą się kryteria wyszukiwania) z kolei operator „!<” odczytuje wartości dla każdego wiersza.
Oba przykładowe zapytania:
SELECT *
FROM Purchasing.PurchaseOrderHeader AS p
WHERE p.PurchaseOrderID >= 2975
SELECT *
FROM Purchasing.PurchaseOrderHeader AS p
WHERE p.PurchaseOrderID !< 2975
przetwarzane będą jednak dokładnie ten sam sposób:
Okazuje się, że i tym razem optymalizator dokonał automatycznej konwersji (z operatora „!<” na „>=”) chroniąc nas przed konsekwencjami dobrania niewydajnego operatora.
Pomimo, że w niektórych przypadkach możemy zostać uratowani z opresji przez automatyczną optymalizację na poziomie składni zapytania to jednak nie powinniśmy polegać na tych mechanizmach lecz świadomie stosować wydajniejsze konstrukcje.
2.2 Unikanie działań arytmetycznych na kolumnie w instrukcji WHERE
Wykonywanie działań arytmetycznych na kolumnie, której dotyczy instrukcja WHERE uniemożliwia użycie założonego na niej indeksu. Np. zapytania:
SELECT *
FROM Purchasing.PurchaseOrderHeader AS p
WHERE p.PurchaseOrderID * 3 = 6000
SELECT *
FROM Purchasing.PurchaseOrderHeader AS p
WHERE p.PurchaseOrderID = 6000 / 3
oczywiście zwracają ten sam zbiór rezultatów (1 rekord), jednak pierwsze z nich nie wykorzystuje założonego na kolumnie „PurchaseOrderID” pogrupowanego indeksu:
Jak widać koszt wykonania pierwszego zapytania jest nieporównywalnie większy:
(1 row(s) affected)
Table 'PurchaseOrderHeader'. Scan count 1, logical reads 221, physical reads 4, read-ahead reads 216, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 278 ms.
(1 row(s) affected)
Table 'PurchaseOrderHeader'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 5 ms.
2.3 Unikanie stosowania funkcji na kolumnie w instrukcji WHERE
Również użycie funkcji na kolumnie w instrukcji WHERE może być przyczyna niewykorzystanie optymalnego indeksu, zobrazuję to na przykładzie funkcji SUBSTRING:
SELECT dept.[Name]
FROM HumanResources.Department AS dept
WHERE SUBSTRING(dept.[Name],1,1) = 'A'
Zapytanie to można by zastąpić poniższym, równoważnym z warunkiem LIKE:
SELECT dept.[Name]
FROM HumanResources.Department AS dept
WHERE dept.[Name] LIKE 'A%'
Plany wykonania zapytań:
Podobne zmiany planów wykonania na mniej optymalne możemy zaobserwować dla innych funkcji np. DATEPART użytej do zdefiniowania zakresu dat.
Powyższe plany wykonania zapytań wskazują, że podobnie jak w przypadku działań arytmetycznych zastosowanie funkcji w instrukcji WHERE może skutkować spadkiem wydajności.
Kategorie
Wyszukiwarka
Ankieta
Czy podoba Ci się ten serwis !?
Tak, jest super !!!
22% (16 głosów)
Fajny jest :)
39% (28 głosów)
Może być...
8% (6 głosów)
Ujdzie w tłumie...
10% (7 głosów)
Nie, jest beznadziejny :P
21% (15 głosów)
Razem głosów: 72
Komentarze
Dodaj nowy komentarz