Co powoduje, że instrukcja SQL może być wymienna?

252

Z definicji (przynajmniej z tego, co widziałem) sargable oznacza, że zapytanie może zoptymalizować mechanizm wykonywania zapytań przez silnik zapytań. Próbowałem poszukać odpowiedzi, ale wydaje się, że nie ma wiele na ten temat. Pytanie brzmi: co sprawia, że zapytanie SQL może zostać przeszukiwane? Każda dokumentacja byłaby bardzo mile widziana.

Dla porównania: SARGable

sql-server performance

— DForck42
źródło

58

+1 za „sargable”. To moje dzisiejsze słowo. :-p

— BFree

1

Mogę również dodać do odpowiedzi Adama, że góry informacji są w większości przypadków wyjątkowo specyficzne dla każdego silnika DB.

— Hoagie

30

SARG = ARGument wyszukiwania. Zabawne jest to, że „SARG” po niemiecku oznacza „trumnę”, więc zawsze muszę się uśmiechać, kiedy ludzie mówią o SARGABLE - czy można je umieścić w trumnie? :-)

— marc_s

możliwość zakupu zależy od środowiska. MySQL jest udokumentowany tutaj: dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

— Frank Farmer

Posiadanie pól tekstowych zamiast „tabel odnośników” jest również sprzeczne z duchem tworzenia zapytań. Użytkownicy wpisują dowolny tekst podczas wpisywania dowolnego tekstu (np. Nazwa miasta), podczas gdy tabele wyszukiwania zmuszają użytkowników do wybrania poprawnego wpisu. Wart drobnych dodatkowych problemów, ponieważ można to poprawnie zindeksować zamiast używać LIKE '% ...%' w predykacie.

— Odwrócony inżynier

256

Najczęstszą rzeczą, która powoduje, że zapytanie nie jest możliwe do wysłania, jest umieszczenie pola wewnątrz funkcji w klauzuli where:

SELECT ... FROM ...
WHERE Year(myDate) = 2008

Optymalizator SQL nie może używać indeksu na myDate, nawet jeśli taki istnieje. Będzie musiał dosłownie ocenić tę funkcję dla każdego wiersza tabeli. Znacznie lepiej użyć:

WHERE myDate >= '01-01-2008' AND myDate < '01-01-2009'

Kilka innych przykładów:

Bad: Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate())

— BradC
źródło

7

Czy włączenie funkcji wewnątrz GROUP BYspowoduje, że zapytanie stanie się niewymienne?

— Mike Bailey

1

Niektóre silniki baz danych (Oracle, PostgreSQL) obsługują indeksy wyrażeń, prawda?

— Craig

3

Czy WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))byłaby to jeszcze lepsza wersja SELECT... FROM ... WHERE FullName = 'Ed Jones' UNION SELECT...FROM...WHERE FullName IS NULL? Pewnego razu specjalista od optymalizacji powiedział mi, że użycie OR w klauzuli where może rozładować zapytania ..?

— High Plains Grifter

2

@HighPlainsGrifter powinieneś użyć UNION WSZYSTKIE do tego zapytania - związek ma ukrytą różnicę, co sprawia, że zapytanie jest znacznie droższe, niż musi być, gdy trzeba wzajemnie wykluczać zestawy danych

— Devin Lamothe

1

@BradC W MSSQL 2016 nie ma różnicy między planem wykonania Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones'a Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL)). Oboje używają indeksu na FullName i szukają indeksu.

— CEGRD,

79

Nie rób tego:

WHERE Field LIKE '%blah%'

Powoduje to skanowanie tabeli / indeksu, ponieważ wartość LIKE zaczyna się od znaku wieloznacznego.

Nie rób tego:

WHERE FUNCTION(Field) = 'BLAH'

To powoduje skanowanie tabeli / indeksu.

Serwer bazy danych będzie musiał ocenić FUNCTION () dla każdego wiersza w tabeli, a następnie porównać go z „BLAH”.

Jeśli to możliwe, zrób to w odwrotnej kolejności:

WHERE Field = INVERSE_FUNCTION('BLAH')

Spowoduje to uruchomienie INVERSE_FUNCTION () dla parametru jeden raz i nadal pozwoli na użycie indeksu.

— plaża
źródło

5

Twoja sugestia z przerzuceniem funkcji naprawdę działałaby tylko wtedy, gdy funkcja zaokrągla dane (co oznacza, że f (f (n)) = n).

— Adam Robinson

5

Prawdziwe. Rozważałem dodanie INVERSE_FUNCTION, ale nie chciałem się mylić. Zmienię to.

— plaża

9

W tej odpowiedzi zakładam, że baza danych ma wystarczającą liczbę indeksów. Jest wystarczająco dużo pytań na ten temat .

Często zdatność zapytania do zapytania zależy od punktu krytycznego powiązanych indeksów. Punkt krytyczny określa różnicę między wyszukiwaniem a skanowaniem indeksu podczas łączenia jednej tabeli lub zestawu wyników do drugiej. Jedno wyszukiwanie jest oczywiście znacznie szybsze niż skanowanie całej tabeli, ale gdy trzeba szukać wielu wierszy, skanowanie może mieć większy sens.

Zatem między innymi instrukcja SQL jest bardziej zrozumiała, gdy optymalizator spodziewa się, że liczba wynikowych wierszy jednej tabeli będzie mniejsza niż punkt końcowy możliwego indeksu w następnej tabeli.

Szczegółowy post i przykład można znaleźć tutaj .

— Dries Van Hansewijck
źródło

4

Aby operacja została uznana za możliwą do sargowania, nie wystarczy, aby mogła po prostu użyć istniejącego indeksu. W powyższym przykładzie dodanie wywołania funkcji względem kolumny indeksowanej w klauzuli where najprawdopodobniej nadal skorzystałoby z określonego indeksu. Będzie „skanować”, czyli pobiera wszystkie wartości z tej kolumny (indeksu), a następnie eliminuje te, które nie pasują do podanej wartości filtru. Nadal nie jest wystarczająco wydajny w przypadku tabel z dużą liczbą wierszy. To, co tak naprawdę definiuje sargility, to zdolność zapytania do przeszukania indeksu b-drzewa za pomocą binarnej metody wyszukiwania, która polega na eliminacji w połowie zestawu tablicy posortowanych elementów. W SQL byłby wyświetlany w planie wykonania jako „wyszukiwanie indeksu”.

— użytkownik2011845
źródło