Ogromne spowolnienie zapytania SQL Server przy dodawaniu symboli wieloznacznych (lub górnych)

Mam zoo z 20 milionami zwierząt, które śledzę w mojej bazie danych SQL Server 2005. Około 1% z nich jest czarnych, a około 1% z nich to łabędzie. Chciałem uzyskać szczegółowe informacje na temat wszystkich czarnych łabędzi, więc nie chcąc zalać strony wyników, zrobiłem:

select top 10 * 
from animal 
where colour like 'black'  
and species like 'swan'

(Tak, nieumyślnie te pola są dowolne, ale oba są indeksowane). Okazuje się, że nie mamy takich zwierząt, ponieważ zapytanie zwraca pusty zestaw w około 300 milisekund. Byłbym około dwa razy szybszy, gdybym użył „=” niż „lubię”, ale mam przeczucie, że to ostatnie uratuje mi trochę pisania.

Okazuje się, że główny obserwator myśli, że mógł wprowadzić niektóre łabędzie jako „czarne”, więc odpowiednio modyfikuję zapytanie:

select top 10 * 
from animal  
where colour like 'black%' 
and species like 'swan'

Okazuje się, że nie ma też żadnego z nich (i w rzeczywistości nie ma zwierząt „czarnych%” z wyjątkiem zwierząt „czarnych”), ale zapytanie zwraca teraz około 30 sekund.

Wydaje się, że to tylko kombinacja „góry” i „jak%” powoduje problemy, ponieważ

select count(*) 
from animal  
where colour like 'black%' 
and species like 'swan'

bardzo szybko zwraca 0, a nawet

select * 
from animal 
where colour like 'black%' 
and species like 'swan'

zwraca pusty w ułamku sekundy.

Czy ktoś ma pojęcie, dlaczego „top” i „%” powinny spiskować, aby spowodować tak dramatyczną utratę wydajności, szczególnie w pustym zestawie wyników?

EDYCJA: Żeby wyjaśnić, nie używam żadnych indeksów FreeText, chodziło mi tylko o to, że pola są dowolne w punkcie wejścia, tj. Nie są znormalizowane w bazie danych. Przepraszam za zamieszanie, złe sformułowanie z mojej strony.

Jest to decyzja optymalizatora kosztowego.

Szacunkowe koszty użyte w tym wyborze są nieprawidłowe, ponieważ zakłada statystyczną niezależność między wartościami w różnych kolumnach.

Jest podobny do problemu opisanego w Row Goals Gone Rogue, w którym liczby parzyste i nieparzyste są ujemnie skorelowane.

Jest łatwy do odtworzenia.

CREATE TABLE dbo.animal(
    id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    colour varchar(50) NOT NULL,
    species varchar(50) NOT NULL,
    Filler char(10) NULL
);

/*Insert 20 million rows with 1% black and 1% swan but no black swans*/
WITH T
     AS (SELECT TOP 20000000 ROW_NUMBER() OVER (ORDER BY @@SPID) AS RN
         FROM   master..spt_values v1,
                master..spt_values v2,
                master..spt_values v3)
INSERT INTO dbo.animal
            (colour,
             species)
SELECT CASE
         WHEN RN % 100 = 1 THEN 'black'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END,
       CASE
         WHEN RN % 100 = 2 THEN 'swan'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END
FROM   T 

/*Create some indexes*/
CREATE NONCLUSTERED INDEX ix_species ON  dbo.animal(species);
CREATE NONCLUSTERED INDEX ix_colour ON  dbo.animal(colour);

Spróbuj teraz

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black'
       AND species LIKE 'swan' 

Daje to plan, poniżej którego kosztuje 0.0563167.

Plan jest w stanie wykonać łączenie scalające między wynikami dwóch indeksów w idkolumnie. ( Więcej szczegółów na temat algorytmu łączenia scalającego tutaj ).

Scal sprzężenie wymaga, aby oba dane wejściowe były uporządkowane według klucza łączenia.

Indeksy nieklastrowane są uporządkowane według (species, id)i (colour, id)odpowiednio (w nieunikalnych indeksach nieklastrowanych zawsze lokalizator wierszy jest zawsze dodawany na końcu klucza niejawnie, jeśli nie jest jawnie dodawany). Zapytanie bez symboli wieloznacznych wykonuje wyszukiwanie równości do species = 'swan'i colour ='black'. Ponieważ każde wyszukiwanie pobiera tylko jedną dokładną wartość z kolumny wiodącej, pasujące wiersze zostaną uporządkowane według, iddlatego ten plan jest możliwy.

Operatorzy planu zapytań wykonują od lewej do prawej . Lewy operator żąda wierszy od swoich potomków, które z kolei żądają wierszy od swoich potomków (i tak dalej, aż dojdą do węzłów liści). TOPIterator zatrzyma zainteresowanie kolejnych wierszy z jej dziecko po raz 10 zostały otrzymane.

SQL Server ma statystyki dotyczące indeksów, które mówią, że 1% wierszy pasuje do każdego predykatu. Zakłada się, że statystyki te są niezależne (tzn. Nie są skorelowane ani dodatnio, ani ujemnie), więc średnio po przetworzeniu 1000 wierszy pasujących do pierwszego predykatu znajdzie 10 pasujących do drugiego i może wyjść. (powyższy plan pokazuje 987 zamiast 1000, ale wystarczająco blisko).

W rzeczywistości, ponieważ predykaty są ujemnie skorelowane, rzeczywisty plan pokazuje, że wszystkie 200 000 pasujących wierszy wymagało przetworzenia z każdego indeksu, ale jest to w pewnym stopniu złagodzone, ponieważ zero połączone wiersze oznacza również, że faktycznie potrzebne były zerowe wyszukiwania.

Porównać z

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

Co daje plan, poniżej którego kosztuje 0.567943

Dodanie końcowej karty wieloznacznej spowodowało teraz skanowanie indeksu. Koszt planu jest jednak nadal dość niski w przypadku skanowania tabeli z 20 milionami wierszy.

Dodanie querytraceon 9130pokazuje więcej informacji

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'       
OPTION (QUERYTRACEON 9130)  

Można zauważyć, że SQL Server uważa, że ​​będzie musiał skanować tylko około 100 000 wierszy, zanim znajdzie 10 pasujących predykatów i TOPmoże przestać żądać wierszy.

Ponownie ma to sens przy założeniu niezależności jako 10 * 100 * 100 = 100,000

Wreszcie spróbujmy wymusić plan przecięcia indeksu

SELECT TOP 10 *
FROM   animal WITH (INDEX(ix_species), INDEX(ix_colour))
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

Daje mi to równoległy plan z szacowanym kosztem 3,4625

Główną różnicą jest to, że colour like 'black%'predykat może teraz pasować do wielu różnych kolorów. Oznacza to, że nie można już zagwarantować, że pasujące wiersze indeksu dla tego predykatu będą sortowane w kolejności od id.

Na przykład wyszukiwanie indeksu like 'black%'może zwrócić następujące wiersze

+------------+----+
|   Colour   | id |
+------------+----+
| black      | 12 |
| black      | 20 |
| black      | 23 |
| black      | 25 |
| blackberry |  1 |
| blackberry | 50 |
+------------+----+

W obrębie każdego koloru identyfikatory są uporządkowane, ale identyfikatory w różnych kolorach mogą nie być.

W rezultacie SQL Server nie może już wykonywać przecięcia indeksu łączenia scalającego (bez dodawania blokującego operatora sortowania) i zamiast tego wybiera łączenie mieszające. Łączenie hash blokuje dane wejściowe kompilacji, więc teraz koszt odzwierciedla fakt, że wszystkie pasujące wiersze będą musiały zostać przetworzone z danych wejściowych kompilacji, zamiast zakładać, że będzie musiał skanować tylko 1000, jak w pierwszym planie.

Dane wejściowe sondy nie są jednak blokowane i nadal niepoprawnie szacuje, że będzie w stanie zatrzymać sondowanie po przetworzeniu 987 wierszy od tego.

(Więcej informacji na temat nieblokowania vs. blokowania iteratorów tutaj)

Biorąc pod uwagę zwiększone koszty dodatkowych szacowanych wierszy i skrótu, częściowe skanowanie indeksu klastrowego wygląda taniej.

W praktyce oczywiście „częściowy” skan indeksu klastrowego wcale nie jest częściowy i musi przeszukiwać całe 20 milionów wierszy, a nie 100 tysięcy założonych podczas porównywania planów.

Zwiększenie wartości TOP(lub całkowite usunięcie) ostatecznie napotyka punkt krytyczny, w którym liczba wierszy, które według szacunków będzie musiał pokryć skan CI, powoduje, że plan wygląda na droższy i wraca do planu przecięcia indeksu. Dla mnie punktem odcięcia pomiędzy dwoma planami jest TOP (89)kontra TOP (90).

Dla ciebie może się różnić, ponieważ zależy od tego, jak szeroki jest indeks klastrowany.

Usuwanie TOPi wymuszanie skanowania CI

SELECT *
FROM   animal WITH (INDEX = 1)
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan' 

Koszt w 88.0586mojej maszynie kosztuje mój przykładowy stół.

Jeśli SQL Server byłby świadomy, że zoo nie ma czarnych łabędzi i że będzie musiał wykonać pełne skanowanie, a nie tylko odczytać 100 000 wierszy, ten plan nie zostałby wybrany.

Próbowałem wielo statystyk kolumnowej na animal(species,colour)i animal(colour,species)i sączy się na statystyki animal (colour) where species = 'swan', ale żaden z nich nie pomoże przekonać go, że czarne łabędzie nie istnieją i TOP 10skanowania będą musiały przetwarzać ponad 100.000 wierszy.

Wynika to z „założenia włączenia”, w którym SQL Server zasadniczo zakłada, że ​​jeśli szukasz czegoś, prawdopodobnie istnieje.

W wersji 2008+ dostępna jest udokumentowana flaga śledzenia 4138, która wyłącza cele rzędu. Skutkuje to tym, że plan jest kosztowany bez założenia, że TOPpozwoli operatorom potomnym na wcześniejsze zakończenie bez odczytywania wszystkich pasujących wierszy. Z tą flagą śledzenia w naturalny sposób otrzymuję bardziej optymalny plan przecięcia indeksu.

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 4138)       

Plan ten teraz poprawnie kosztuje za odczyt pełnych 200 tysięcy wierszy w obu poszukiwaniach indeksu, ale przekracza koszty kluczowych wyszukiwań (szacunkowo 2 tysiące w porównaniu do rzeczywistego 0. TOP 10Ograniczałoby to do maksymalnie 10, ale flaga śledzenia nie pozwala na wzięcie tego pod uwagę) . Mimo to plan jest znacznie tańszy niż pełny skan CI, więc wybrano go.

Oczywiście plan ten może nie być optymalna dla kombinacji, które są wspólne. Takie jak białe łabędzie.

Indeks złożony na animal (colour, species)lub idealnie animal (species, colour)pozwoliłby, aby zapytanie było znacznie bardziej wydajne dla obu scenariuszy.

Aby jak najefektywniej wykorzystać indeks złożony LIKE 'swan', należy go również zmienić na = 'swan'.

Poniższa tabela pokazuje predykaty wyszukiwania i pozostałe predykaty pokazane w planach wykonania dla wszystkich czterech permutacji.

+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
|                 WHERE clause                 |       Index       |                         Seek Predicate                         |              Residual Predicate              |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
| colour LIKE 'black%' AND species LIKE 'swan' | ix_colour_species | colour >= 'black' AND colour < 'blacL'                         | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species LIKE 'swan' | ix_species_colour | species >= 'swan' AND species <= 'swan'                        | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species = 'swan'    | ix_colour_species | (colour,species) >= ('black', 'swan')) AND colour < 'blacL'    | colour LIKE 'black%' AND species = 'swan'    |
| colour LIKE 'black%' AND species = 'swan'    | ix_species_colour | species = 'swan' AND (colour >= 'black' and colour <  'blacL') | colour like 'black%'                         |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+

Uznając to za intrygujące, trochę szukałem i natknąłem się na to pytanie. Jak (i ​​dlaczego) TOP wpływa na plan wykonania?

Zasadniczo, użycie TOP zmienia koszt operatorów podążających za nim (w sposób nieprofesjonalny), co powoduje, że zmienia się również ogólny plan (byłoby świetnie, gdybyś zawierał ExecPlany z TOP 10 i bez niego), co właściwie zmienia ogólne wykonanie Zapytanie.

Mam nadzieję że to pomoże.

Na przykład, wypróbowałem to w bazie danych i: -w przypadku gdy nie jest wywoływany żaden wierzchołek, używany jest paralelizm -w TOP, równoległość nie jest używana

Ponownie pokazanie planów wykonania dostarczyłoby więcej informacji.

Miłego dnia

Wierzę, że może to wynikać z podstawowej natury MSSQL 2005 i sposobu, w jaki optymalizator zapytań decyduje, który plan wykonania jest najbardziej wydajny.

Jeśli używasz zmiennej SQL, powinna ona „oszukać” optymalizator zapytań, aby używał dopasowań mieszających zamiast zagnieżdżonych pętli, co spowoduje znacznie wyższy stopień równoległości.

Próbować:

DECLARE @topn INT = 10
SELECT TOP (@topn) *
FROM    animal
WHERE   colour LIKE 'black%' 
AND species LIKE 'swan'