JAK używa indeksu, CHARINDEX nie?

To pytanie dotyczy mojego starego pytania . Wykonanie poniższego zapytania trwało od 10 do 15 sekund:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE (Charindex('123456789',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0) 

W niektórych artykułach widziałem, że używanie CASTi CHARINDEXnie skorzysta z indeksowania. Istnieje również kilka artykułów, które mówią, że używanie LIKE '%abc%'nie skorzysta z indeksowania, podczas gdy LIKE 'abc%':

http://bytes.com/topic/sql-server/answers/81467-using-charindex-vs-like-where /programming/803783/sql-server-index-any-improvement-for podobne zapytania http://www.sqlservercentral.com/Forums/Topic186262-8-1.aspx#bm186568

W moim przypadku mogę przepisać zapytanie jako:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE [company].dbo.[customer].[Phone no]  LIKE '%123456789%'

To zapytanie daje takie same wyniki jak poprzednie. Utworzyłem indeks nieklastrowany dla kolumnyPhone no . Kiedy wykonuję to zapytanie, działa ono w ciągu zaledwie 1 sekundy . To ogromna zmiana w porównaniu z 14 sekundami wcześniej.

Jakie są LIKE '%123456789%'korzyści z indeksowania?

Dlaczego wymienione artykuły stwierdzają, że nie poprawi to wydajności?

Próbowałem przepisać zapytanie, aby użyć CHARINDEX, ale wydajność jest wciąż niska. DlaczegoCHARINDEX nie korzysta z indeksowania, jak się wydaje, że LIKEzapytanie?

Zapytanie za pomocą CHARINDEX:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [Company].dbo.[customer]
 WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 ) 

Plan wykonania:

Zapytanie za pomocą LIKE:

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
 FROM [Company].dbo.[customer]
 WHERE[Company].dbo.[customer].[Phone no] LIKE '%9000413237%'

Plan wykonania:

W jaki sposób LIKE „% 123456789%” korzysta z indeksowania?

Tylko trochę. Procesor zapytań może skanować cały indeks nieklastrowany w poszukiwaniu dopasowań zamiast całej tabeli (indeks klastrowany). Indeksy nieklastrowane są zazwyczaj mniejsze niż tabela, na której są zbudowane, więc skanowanie indeksu nieklastrowanego może być szybsze.

Minusem jest to, że wszelkie kolumny potrzebne w zapytaniu, które nie są zawarte w definicji indeksu nieklastrowanego, muszą być wyszukiwane w tabeli podstawowej, w wierszu.

Optymalizator podejmuje decyzję między skanowaniem tabeli (indeksem klastrowanym) a skanowaniem indeksu nieklastrowanego za pomocą odnośników, na podstawie szacunków kosztów. Szacowane koszty zależą w dużej mierze od tego, ile wierszy optymalizator spodziewa swoje LIKElub CHARINDEXorzeczenie wybrać.

Dlaczego wymienione artykuły stwierdzają, że nie poprawi to wydajności?

W przypadku LIKEstanu, który nie zaczyna się od znaku wieloznacznego, SQL Server może wykonać częściowe skanowanie indeksu zamiast skanowania całej rzeczy. Na przykład LIKE 'A%można go poprawnie ocenić, testując tylko rekordy indeksu >= 'A'i < 'B'(dokładne wartości graniczne zależą od sortowania).

W tego rodzaju zapytaniach można skorzystać z możliwości wyszukiwania indeksów b-drzewa: możemy przejść bezpośrednio do pierwszego rekordu >= 'A'za pomocą b-drzewa, a następnie skanować do przodu w kolejności kluczy indeksu, aż osiągniemy rekord, który nie przejdzie < 'B'testu. Ponieważ musimy zastosować LIKEtest tylko do mniejszej liczby wierszy, wydajność jest na ogół lepsza.

Natomiast LIKE '%Anie można go przekształcić w skanowanie częściowe, ponieważ nie wiemy, od czego zacząć, czy zakończyć; dowolny rekord może się kończyć 'A', dlatego nie możemy poprawić skanowania całego indeksu i testowania każdego wiersza z osobna.

Próbowałem przepisać zapytanie, aby użyć CHARINDEX, ale wydajność jest wciąż niska. Dlaczego CHARINDEXnie korzysta z indeksowania, ponieważ wydaje się, że robi to zapytanie LIKE?

Optymalizator zapytań ma ten sam wybór między skanowaniem tabeli (indeks klastrowany) a skanowaniem indeksu nieklastrowanego (z wyszukiwaniem) w obu przypadkach.

Wybór między nimi odbywa się na podstawie oszacowania kosztów . Zdarza się, że SQL Server może wygenerować inny szacunek dla obu metod. W przypadku LIKEformy zapytania oszacowanie może być w stanie użyć specjalnych statystyk ciągów, aby uzyskać dość dokładne oszacowanie. CHARINDEX > 0Forma daje szacunkową w oparciu o przypuszczenie.

Różne szacunki są wystarczające, aby optymalizator wybrał Skanowanie indeksu klastrowanego CHARINDEXi Skanowanie indeksu nieklastrowanego z wyszukiwaniem LIKE. Jeśli CHARINDEXwymusisz użycie zapytania z indeksem nieklastrowanym z podpowiedź, otrzymasz taki sam plan jak dla LIKE, a wydajność będzie mniej więcej taka sama:

SELECT
    [Customer name],
    [Sl_No],
    [Id]
FROM dbo.customer WITH (INDEX (f))
WHERE 
    CHARINDEX('9000413237', [Phone no]) >0;

Liczba wierszy przetworzonych w czasie wykonywania będzie taka sama dla obu metod, po prostu LIKEformularz generuje dokładniejsze oszacowanie w tym przypadku, więc optymalizator zapytań wybiera lepszy plan.

Jeśli LIKE %thing%często potrzebujesz wyszukiwania, możesz rozważyć technikę, o której pisałem w Trigram Wildcard String Search w SQL Server .

SQL Server utrzymuje statystyki podłańcuchów w kolumnach łańcuchowych w formie prób, które mogą być użyte przez LIKEzapytanie, ale nie przez CHARINDEX.

Aby uzyskać więcej informacji, zobacz sekcję Statystyka podsumowań ciągów .

Kilka ważnych ostrzeżeń polega na tym, że wszelkie ucieczkę symboli wieloznacznych należy wykonywać przy użyciu zastrzeżonej techniki nawiasów kwadratowych zamiast ESCAPEsłowa kluczowego i że w przypadku łańcuchów dłuższych niż 80 znaków używane są tylko pierwsze i ostatnie 40 znaków.

WHERE ( Charindex('9000413237',[Company].dbo.[customer].[Phone no])>0 ) 

użyje standardowego odgadnięcia dla predykatu nierówności, że 30% wierszy zostanie zwróconych.

LIKEZapytań (w danym przypadku) szacuje przypuszczalnie znacznie mniej wierszy dopasuje predykat.

Zauważ, że wiodący symbol wieloznaczny nadal uniemożliwia wyszukiwanie indeksu. Cały indeks jest nadal skanowany, ale używa innego, który jest węższy niż indeks klastrowany. Węższy indeks nie obejmuje wszystkich kolumn używanych przez zapytanie, więc drugi plan wymaga wyszukiwania klucza w celu odzyskania brakujących kolumn.

Jest mało prawdopodobne, aby ten plan został wybrany z 30% szacunkiem. SQL Server uzna, że ​​tańsze jest skanowanie całego indeksu klastrowego i unikanie tylu wyszukiwań. Dodatkowe przykłady można znaleźć w tym artykule na temat punktu krytycznego.