Optymalizacja zapytań SQL

Tagi artykułów:

100mbru

, CentOS , cms , drupal , joomla , LAMP , linux , mysql , nicru , Open source , Czerwony kapelusz , seo , Typo3 , ubuntu , auto , administracja , algorytmy , alkohol , biznes , bitrix , wideo , wojskowy , wybory , Niemcy , gino , domeny , ciekawość , badania , zdjęcia , kino , kodowanie , komputery , Libia , sklep , szaleństwo , masterhost , przetwarzanie obrazu , paintball , polityka , ekonomia polityczna , fajne wideo , programowanie , podróżować , pracować , religia , runet , budowanie strony internetowej , seo , media , technologia , Ukraina , forum , hihanki , holivary , hosting - 10-10-12
Tagi artykułów: kodowanie , mysql , algorytmy

Więcej aplikacji korzysta z baz danych. Więcej danych musi być przechowywanych i przetwarzanych. Jeśli aplikacja działa wolno, programiści, użytkownicy i administratorzy odnoszą się przede wszystkim do słabej wydajności sieci, złego sprzętu serwerowego i do siebie nawzajem :). I zapomnij o optymalizacji.

Będzie to kontynuowane, dopóki aplikacja nie zostanie poddana okrutnej analizie w celu zwiększenia wydajności. Jednym ze sposobów na zwiększenie szybkości aplikacji jest optymalizacja zapytań SQL. Ta metoda jest dobra, ponieważ nie musisz wchodzić w szaleństwo optymalizacji serwera SQL. Łatwiej jest nie dopuścić do pojawienia się nieefektywnych zapytań SQL. Ale jeśli to już się wydarzyło, szukaj dróg wyjścia z obecnych nieprzyjemnych sytuacji.

Ogólna optymalizacja

Każda operacja SQL ma tzw. „Współczynnik użyteczności” - poziom wydajności tej operacji. Im wyższy wynik, tym bardziej użyteczna jest operacja, co oznacza, że ​​zapytanie SQL jest wykonywane szybciej.

Praktycznie każdy warunek składa się z dwóch argumentów i znaku operacji między nimi.

Przykłady

Aby lepiej zrozumieć tabele, rozważ przykład obliczania oceny zapytania.

... WHERE smallint_column = 12345

5 punktów za pole po lewej stronie (smallint_column), 2 punkty za dokładny cyfrowy operand (smallint_column), 10 punktów za operację porównania (=) i 10 punktów za wartość po prawej (12345). Razem otrzymało 27 punktów. Rozważ teraz bardziej złożony przykład:

... WHERE char_column> = varchar_column || „x”

5 punktów za lewe pole (char_column), 0 punktów za symboliczny operand (char_column), 5 punktów za operację większą lub równą (> =) , 3 punkty za wyrażenie logiczne (varchar_column || "x") , 0 punktów za operand symboliczny ( varchar_column). W rezultacie otrzymujemy 13 punktów.

Oczywiście takie obliczenia nie są konieczne dla każdego żądania. Ale gdy pojawia się pytanie o szybkość warunków konkretnego zapytania, można je wyjaśnić za pomocą tych dwóch tabel. Na szybkość żądania ma również wpływ ilość danych do wyboru i dodatkowe dyrektywy, które omówiono poniżej. Należy również pamiętać, że obliczenie „współczynnika użyteczności” nie jest rodzajem uniwersalnej metody optymalizacji. Wszystko zależy od konkretnej sytuacji.

Głównym prawem w optymalizacji zapytań jest prawo transformacji. Niezależnie od tego, jak przedstawimy warunek, najważniejsze jest to, że wynik pozostaje taki sam. I znowu rozważmy przykład. Istnieje zapytanie: ... WHERE kolumna1 <kolumna2 ORAZ kolumna2 = kolumna3 ORAZ kolumna1 = 5 . Używając permutacji, otrzymujesz zapytanie: ... GDZIE 5 <kolumna2 ORAZ kolumna2 = kolumna3 ORAZ kolumna1 = 5 . Wynik zapytania będzie taki sam, a wydajność będzie inna, ponieważ użycie dokładnej wartości (5) wpływa na wydajność.

Jeśli studiowałeś C lub C ++, wiesz, że wyrażenie x = 1 + 1-1-1 w czasie kompilacji będzie wynosić x = 0. Co zaskakujące, tylko niektóre bazy danych są w stanie wykonywać takie operacje. Podczas wykonywania zapytania baza danych będzie wykonywać operacje dodawania i odejmowania oraz marnować cenny czas. Dlatego zawsze lepiej jest natychmiast obliczyć takie wyrażenia, jeśli to możliwe. Nie ... GDZIE a - 3 = 5 , ale ... GDZIE a = 8 .

Inną możliwością optymalizacji zapytania jest trzymanie się ogólnej idei tworzenia warunków w SQL. Innymi słowy, warunek musi mieć postać: <kolumna> <operacja> <wyrażenie>. Na przykład zapytanie „... WHERE column1 - 3 = -column2” jest lepsze w postaci: ... WHERE column1 = -column2 + 3 .

Te techniki optymalizacji działają prawie zawsze i wszędzie.

Optymalizujemy warunki

Teraz nadszedł czas, aby zoptymalizować warunkowe instrukcje SQL. Większość zapytań korzysta z dyrektywy SQL WHERE, więc optymalizując warunki, można uzyskać znaczną wydajność zapytania. Jednocześnie z jakiegoś powodu tylko niewielka część aplikacji bazodanowych wykorzystuje warunki optymalizacji.

ORAZ

Jest oczywiste, że w szeregu kilku operatorów ORAZ warunki powinny być uporządkowane w kolejności rosnącego prawdopodobieństwa prawdziwości tego warunku. Dzieje się tak, aby podczas sprawdzania warunków bazy danych nie sprawdzać pozostałych warunków. Te zalecenia nie dotyczą bazy danych Oracle, w której warunki zaczynają być sprawdzane od końca. W związku z tym ich kolejność powinna zostać odwrócona - w malejącym prawdopodobieństwie prawdy.

LUB

Sytuacja z tym operatorem jest dokładnie odwrotna do sytuacji z AND. Warunki powinny być zgodne z malejącym prawdopodobieństwem prawdy. Firma Microsoft zdecydowanie zaleca stosowanie tej metody podczas tworzenia zapytań, chociaż wielu nawet o tym nie wie, a przynajmniej nie zwraca na to uwagi. Ale znowu nie dotyczy to bazy danych Oracle, gdzie warunki powinny być w porządku rosnącym prawdy.

Innym warunkiem optymalizacji może być fakt, że jeśli te same kolumny znajdują się obok siebie, zapytanie jest wykonywane szybciej. Na przykład zapytanie „.. WHERE kolumna1 = 1 LUB kolumna2 = 3 LUB kolumna1 = 2” będzie wolniejsza niż zapytanie „WHERE kolumna1 = 1 LUB kolumna1 = 2 LUB kolumna2 = 3” . Nawet jeśli prawdopodobieństwo prawdy warunku kolumna 2 = 3 jest wyższe niż kolumna 1 = 2.

AND + OR

W szkole powiedziano mi o prawie dystrybucji. Stwierdza, że A AND (B OR C) jest taki sam jak (A i B) LUB (A i C ). Ustalono eksperymentalnie, że zapytanie takie jak „... WHERE column1 = 1 AND (column2 =„ A ”LUB kolumna 2 =„ B ”) jest nieco szybsze niż „ ... GDZIE (kolumna 1 = 1 I kolumna 2 = ”A „) OR (kolumna1 = 1 ORAZ kolumna2 =„ B ”)” . Niektóre bazy danych są w stanie zoptymalizować zapytania tego typu, ale lepiej być bezpiecznym.

NIE

Ta operacja powinna zawsze prowadzić do bardziej „czytelnej” formy (oczywiście w rozsądnych granicach). Zatem zapytanie „... GDZIE NIE (kolumna 1> 5)” jest konwertowane na „... GDZIE kolumna1 <= 5” . Bardziej złożone warunki mogą zostać przekształcone za pomocą reguły de Morgana, której również musieliście się nauczyć w szkole. Zgodnie z tą zasadą NIE (A I B) = (NIE A) LUB (NIE B) i NIE (A LUB B) = (NIE A) ORAZ (NIE B) . Na przykład warunek „... GDZIE NIE (kolumna 1> 5 LUB kolumna 2 = 7)” jest konwertowany na prostszą postać: ... GDZIE kolumna1 <= 5 ORAZ kolumna2 <> 7 .

IN

Wielu naiwnie wierzy, że zapytanie „... GDZIE kolumna1 = 5 LUB kolumna1 = 6” jest odpowiednikiem zapytania „... GDZIE kolumna1 IN (5, 6)” . W rzeczywistości tak nie jest. Operacja IN jest znacznie szybsza niż seria OR. Dlatego należy zawsze zamieniać OR na IN, gdzie jest to możliwe, mimo że niektóre bazy danych same wykonują tę optymalizację. Gdy używana jest seria kolejnych numerów, IN należy zmienić na MIĘDZY. Na przykład „... WHERE kolumna 1 IN (1, 3, 4, 5)” jest zoptymalizowana dla postaci: … WHERE kolumna1 MIĘDZY 1 I 5 I kolumna1 <> 2 . A to zapytanie jest naprawdę szybsze.

JAK

Ta operacja powinna być używana tylko wtedy, gdy jest to absolutnie konieczne, ponieważ lepiej i szybciej można użyć wyszukiwania opartego na indeksach pełnotekstowych. Niestety, muszę wysłać ci informacje o wyszukiwaniu w sieci World Wide Web.

PRZYPADEK

Ta funkcja może być użyta do zwiększenia prędkości zapytania, gdy występuje więcej niż jedno wywołanie wolnej funkcji w stanie. Na przykład, aby uniknąć ponownego wywoływania funkcji slow_function () w zapytaniu „... WHERE funkcja slow_function (kolumna1) = 3 OR funkcja slow_function (kolumna1) = 5” , musisz użyć CASE:

... GDZIE 1 = CASE slow_function (kolumna 1)

KIEDY 3 TO 1

KIEDY 5 THEN 1

KONIEC

Sortuj

ORDER BY służy do sortowania, o którym wiadomo, że wymaga czasu. Im większa ilość danych, tym dłużej trwa sortowanie, więc musisz je zoptymalizować. Trzy czynniki wpływają na szybkość sortowania w zapytaniach:

• liczba wybranych wpisów;

Najbardziej zasobochłonne sortowanie to sortowanie wierszy. Pomimo faktu, że pola tekstowe mają stałą długość, długość zawartości tych pól może być różna (w obrębie rozmiaru pola). Dlatego nie jest zaskakujące, że sortowanie kolumny VARCHAR (100) będzie wolniejsze niż sortowanie kolumny VARCHAR (10) (nawet jeśli dane są takie same). Dzieje się tak, ponieważ podczas sortowania sama baza danych przydziela pamięć do swoich operacji zgodnie z maksymalnym rozmiarem pola, niezależnie od zawartości. Dlatego deklarując pola, należy zawsze używać potrzebnego rozmiaru, a nie przydzielać dodatkowych bajtów dla bezpieczeństwa.

Na komputerach z systemem Windows pola INTEGER to 32 bity, a pola SMALLINT to 16 bitów. Logiczne jest założenie, że sortowanie pól typu SMALLINT powinno być szybsze. W rzeczywistości sortowanie INTEGER jest szybsze niż SMALLINT. Ponadto sortowanie INTEGER jest szybsze niż CHAR.

Sortowanie znaków ma również swoje własne niuanse, których opis zajmie więcej niż jeden artykuł. Może być szybki i zły lub wolny, ale z mniejszą liczbą błędów. Optymalizacja sortowania odbywa się w konkretnej sytuacji, więc nikt nie może podać uniwersalnych zaleceń.

Grupowanie

Operacja GROUP BY jest używana do zdefiniowania podzbioru w wyniku zapytania, a także do zastosowania funkcji agregujących do tego podzbioru. Rozważ niektóre z najbardziej skutecznych metod optymalizacji operacji grupowania.

Pierwszą rzeczą do zapamiętania jest użycie jak najmniejszej liczby kolumn do grupowania. Unikaj również niepotrzebnych warunków. Na przykład w kwerendzie SELECT, kolumna_podrzędna_klucza, kolumna_klucza_kluczowego, COUNT (*) FROM Tabela1 GRUPA W kolumnie drugorzędnej_kluczowej, kolumna_kluczowa, kolumna wtyczka_kolumna jest całkowicie niepotrzebna. Powód jest prosty: kolumna secondary_key_column jest unikalnym polem, może nie mieć wartości NULL, co oznacza, że ​​niektóre dane mogą zostać po prostu utracone. Jeśli jednak usuniesz kolumnę secondary_key_column z sekcji GROUP BY, niektóre bazy danych mogą wygenerować błąd stwierdzający, że niemożliwe jest określenie tego pola, jeśli nie zostało zadeklarowane w sekcji GROUP BY. Aby rozwiązać ten problem, możesz napisać zapytanie w tej formie: SELECT MIN (secondary_key_column), primary_key_column, COUNT (*) FROM Table1 GROUP BY primary_key_column . To zapytanie jest szybsze i bardziej „poprawne” pod względem projektowania zapytań.

W większości baz danych operacje WHERE i HAVING nie są równoważne i nie są wykonywane w ten sam sposób. Oznacza to, że następujące dwa zapytania są logicznie takie same, ale są wykonywane z różnymi prędkościami:

SELECT kolumna1 FROM Tabela1 WHERE kolumna2 = 5 GROUP BY kolumna1 HAVING kolumna1> 6

SELECT kolumna1 FROM Tabela1 WHERE kolumna2 = 5 AND kolumna1> 6 GROUP BY kolumna1

Drugie zapytanie jest szybsze niż pierwsze. HAVING należy stosować w rzadkich przypadkach, w których warunek (w przykładzie kolumna 1> 6) jest trudny do wyrażenia bez poświęcania wydajności.

Jeśli wymagane jest grupowanie, ale bez użycia funkcji agregujących ( COUNT (), MIN (), MAX itp.), Rozsądne jest użycie DISTINCT. Tak więc zamiast kolumny SELECT1 FROM Table1 GROUP BY kolumna1 lepiej jest użyć kolumny SELECT DISTINCT1 FROM Table1 .

Używając MIN () i MAX (), uważamy, że te funkcje działają lepiej oddzielnie. Oznacza to, że są one najlepiej używane w oddzielnych zapytaniach lub w zapytaniach przy użyciu UNION.

W przypadku korzystania z funkcji SUM () można uzyskać większą wydajność, używając SUMA (x + y) , a nie SUMA (x) + SUMA (y) . Dla odejmowania lepiej jest odwrotnie: SUMA (x) - SUMA (y) jest szybsza niż SUMA (x - y).

Łączenie tabel (DOŁĄCZA)

W tym miejscu trudno powiedzieć coś o optymalizacji, to jest przy użyciu JOIN . Faktem jest, że szybkość wykonywania takich operacji zależy w dużej mierze od organizacji samej tabeli: użycia klucza obcego, klucza podstawowego, liczby połączeń zagnieżdżonych itp. Czasami lepszą wydajność można uzyskać za pomocą zagnieżdżonych pętli bezpośrednio w programie. Czasami JOIN działa szybciej. Nie ma konkretnej porady, jak korzystać z różnych sposobów łączenia tabel. Wszystko zależy od przypadku i architektury bazy danych.

Podzapytania (SUBQUERIES)

Wcześniej nie wszystkie bazy danych mogły pochwalić się obsługą podkwerend, ale teraz może to zrobić niemal każda nowoczesna baza danych. Nawet MySQL, który zaimplementował podzapytania od kilku lat, w końcu uzyskał wsparcie. Głównym problemem w optymalizacji podzapytań nie jest optymalizacja samego kodu żądania, ale wybór właściwego sposobu realizacji żądania. Zadania, dla których używane są podzapytania, można również rozwiązać za pomocą zagnieżdżonych pętli lub JOIN. Gdy używasz JOIN, włączasz bazę danych, aby wybrać mechanizm łączenia tabel. Jeśli używasz podzapytań, wyraźnie zaznaczasz użycie zagnieżdżonych pętli.

Co wybrać?

Poniżej znajdują się argumenty na rzecz jednej lub innej metody. Wybierz siebie w zależności od sytuacji.

DOŁĄCZ SIŁY:

• Jeśli zapytanie zawiera klauzulę WHERE, wbudowany optymalizator bazy danych zoptymalizuje zapytanie jako całość, natomiast w przypadku użycia podzapytań kwerendy zostaną zoptymalizowane oddzielnie.
• Niektóre bazy danych działają bardziej efektywnie z JOIN niż z podkwerendami (na przykład Oracle).
• Po JOIN informacja pojawi się na ogólnej „liście”, której nie można powiedzieć o podzapytaniach.

Zalety SUBQUERIES:

• Podzapytania pozwalają na luźniejsze warunki.
• Podzapytania mogą zawierać GROUP BY, HAVING, co jest znacznie trudniejsze do zaimplementowania w JOIN.
• Podzapytania mogą być używane z UPDATE, co nie jest możliwe w przypadku JOIN.
• Ostatnio optymalizacja podzapytań przez same DB (ich wbudowany optymalizator) znacznie się poprawiła.

Główną zaletą JOIN jest to, że nie trzeba określać DB, w jaki sposób wykonać operację. Główną zaletą podzapytań jest to, że cykl podzapytania może mieć kilka iteracji (powtórzeń), co z kolei może znacznie zwiększyć wydajność.

Wniosek

W tym artykule przedstawiono najpowszechniejsze sposoby zwiększenia wydajności zapytań SQL. Jednakże, aby zoptymalizować żądania, nadal istnieje wiele różnych sztuczek i sztuczek. Optymalizacja zapytań jest bardziej jak sztuka niż nauka. Każda baza danych ma własne wbudowane optymalizatory, które mogą pomóc w tym trudnym zadaniu, ale nikt nie wykona za ciebie pracy. Jak powiedział stary nauczyciel fizyki: „Aby rozwiązać problemy, muszą zostać rozwiązane”.

Nie zaleca się używania ORDER BY w połączeniu z operacjami takimi jak DISTINCT lub GROUP B Y, ponieważ operatorzy ci mogą tworzyć efekty uboczne do sortowania. W rezultacie możesz otrzymać niepoprawnie posortowany zestaw danych, co może być krytyczne w niektórych sytuacjach. Ta konsekwencja nie dotyczy optymalizacji, ale nie należy o tym zapominać.

Przed poprawą wydajności sieci i zwiększeniem sprzętu serwerowego spróbuj zoptymalizować.

Każda operacja SQL ma współczynnik użyteczności. Im wyższy współczynnik, tym bardziej użyteczna jest operacja: zapytanie jest wykonywane szybciej.

W przeciwieństwie do kompilatorów, nie wszystkie bazy danych mogą uprościć wyrażenia typu x = 1 + 1-1-1 do x = 0. W konsekwencji spędzają cenny czas na wykonywaniu pustych operacji. Zoptymalizuj je z wyprzedzeniem.

Korzystając z funkcji SUM (), można osiągnąć lepszą wydajność przy użyciu SUM (x + y) , a nie SUM (x) + SUM (y) .

Ale jeśli funkcje SUM () są wymagane do odejmowania, użyj odwrotnego: SUMA (x) - SUMA (y). SUM (x - y) jest wolniejszy.

Każda baza danych ma własne wbudowane optymalizatory, ale są dalekie od doskonałości. Dlatego zoptymalizuj z wyprzedzeniem.

• liczba kolumn po operatorze ORDER BY;
• długość i typ kolumn określonych po operatorze ORDER BY.
• Jeśli zapytanie zawiera klauzulę WHERE, wbudowany optymalizator bazy danych zoptymalizuje zapytanie jako całość, natomiast w przypadku użycia podzapytań kwerendy zostaną zoptymalizowane oddzielnie.
• Niektóre bazy danych działają bardziej efektywnie z JOIN niż z podkwerendami (na przykład Oracle).
• Po JOIN informacja pojawi się na ogólnej „liście”, której nie można powiedzieć o podzapytaniach.
• Podzapytania pozwalają na luźniejsze warunki.
• Podzapytania mogą zawierać GROUP BY, HAVING, co jest znacznie trudniejsze do zaimplementowania w JOIN.
• Podzapytania mogą być używane z UPDATE, co nie jest możliwe w przypadku JOIN.
• Ostatnio optymalizacja podzapytań przez same DB (ich wbudowany optymalizator) znacznie się poprawiła.


http://www.xakep.ru/magazine/xs/052/040/1.asp