Utworzone indeksy są użyteczne jeżeli zapytania ich używają. Trzeba wiedzieć jakie rodzaje zapytań mogą korzystać z indeksów oraz jakie zapytania nie korzystają z indeksów nawet jeżeli takie istnieją. Ponadto należy napisać poprawne predykaty służące do filtrowania wierszy tak aby SQL Serwer Query Optymizer mógł użyć istniejących indeksów.

Wsparcie indeksów w zapytaniach

Pisanie wydajnych zapytań zaczynamy od użycia klauzuli WHERE filtrującej wiersze. Klauzula WHERE jest jedną z najważniejszych części zapytania która może korzystać z indeksów. Można sprawdzić kiedy indeks został użyty poprzez wyświetlenie planu wykonania. Można również przeglądać użycie indeksu poprzez odpytania widoku systemowego sys.dm_db_index_usage_stats. Poniższe zapytanie pokazuje użycie indeksów tabeli Sales.Orders bazy TSQL2012 od początku pracy instancji serwera:

SELECT OBJECT_NAME(S.object_id) AS table_name
	,I.NAME AS index_name
	,S.user_seeks
	,S.user_scans
	,s.user_lookups
FROM sys.dm_db_index_usage_stats AS S
INNER JOIN sys.indexes AS i ON S.object_id = I.object_id
	AND S.index_id = I.index_id
WHERE S.object_id = OBJECT_ID(N'Sales.Orders', N'U');

Zapytanie to będzie używane w następnych przykładach dlatego nazwiemy je zapytaniem "wykorzystania indeksu". Wykonajmy zapytanie na tabeli Sales.Orders z włączonym podglądem planu zapytania:

SELECT orderid, custid, shipcity
FROM Sales.Orders;

Plan wykonania: ep1 Plan wykonania tego zapytania pokazał, że SQL Serwer użył skanu indeksu klastrowanego. Cała tabela została przeskanowana pomimo tego, że tabela ta zawiera wiele indeksów. Zauważmy, że skanowanie było nieuporządkowane. Właściwość Ordered jest ustawiona na FALSE. Kolejność jest gwarantowana tylko gdy użyjemy klauzuli ORDER BY. Dodanie klauzuli WHERE do zapytania nie gwarantuje użycia indeksu. Klauzula musi być poparta odpowiednim indeksem i być na tyle selektywna. Jeżeli zapytanie zwraca za wiele wierszy to SQL Serwer uzna, że mniej kosztowną operacją jest skan tabeli lub indeksu klastrowanego w porównaniu z wyszukaniem w indeksie nieklastrowanym i użyciem RID lub key lookups. Przykładowo jeżeli zapytanie jest odpowiednio selektywne to SQL Serwer może użyć skanowania indeksu klastrowanego ponieważ predykat w klauzuli WHERE nie jest wspierany przez żaden indeks. Np żaden indeks nie zawiera jako klucza kolumny shipcity:

SELECT orderid, custid, shipcity
FROM Sales.Orders
WHERE shipcity = N'Vancouver';

Jeżeli zapytanie agreguje dane i używa klauzuli GROUP BY to należy rozważyć złożenie indeksu wspierającego tę agregację. SQL Serwer agreguje dane używając operatora HASH lub STREAM. Operator STREAM jest szybszy lecz potrzenuje posortowanych danych wejściowych. Zapytanie agregujące może używać indeksu nawet jeżeli zapytanie nie zawiera klauzuli GROUP BY. Przykładowo użycie agregacji MIN() z odpowiednim indeksem pozwala na to, że SQL Serwer wyszuka pierwszą wartość z indeksu i nie będzie musiał skanować całej tabeli. Poniższe zapytanie agregujące nie użyje indeksu ponieważ nie istnieje indeks na tabeli Sales.Orders który zawiera kolumnę shipregion:

SELECT shipregion, COUNT(*) AS num_regions
FROM Sales.Orders
GROUP BY shipregion;

Plan wykonania: ep2 Operator agregujący HASH MATCH jest używany gdy zapytanie agregujące nie jest obsługiwane przez indeks. Zauważmy, że wynik poprzedniego zapytania nie jest posortowany. Klauzula GROUP BY nie gwarantuje kolejności wyniku. Jeżeli chcemy aby wynik był posortowany należy użyć klauzuli ORDER BY. Jednakże zawarcie takiej klauzuli powinno być również rozważane wraz z dodaniem odpowiedniego indeksu. Jeżeli nie istnieje indeks dla klauzuli ORDER BY to SQL Serwer musi przesortować zwracane dane. Sortowanie dużych zbiorów danych może być bardzo kosztowne. Dane muszą być sortowane w pamięci lub być przeniesione do TEMPDB jeżeli nie mieszczą się w pamięci operacyjnej. Poniższe zapytanie używa klauzuli ORDER BY która nie jest wspierana przez indeks:

SELECT shipregion
FROM Sales.Orders
ORDER BY shipregion;

Plan wykonania tego zapytania zawiera operator sortowania: ep3 Użycie zapytania wykorzystania indeksów pokaże następujący wynik:

table_name index_name user_seeks user_scans user_lookups ---------- ---------- ---------- ---------- ------------ Orders PK_Orders 0 4 0

Wszystkie zapytania do tej pory używały skanowania indeksu klastrowanego. Stwórzmy indeks nieklastrowany używający kolumny shipregion:

CREATE NONCLUSTERED INDEX idx_nc_shipregion ON Sales.Orders(shipregion);

Wykonanie zapytań agregujących dane i sortujących dane jak poniższe zwróci różne plany wykonania:

-- Query that aggregates the data
SELECT shipregion, COUNT(*) AS num_regions
FROM Sales.Orders
GROUP BY shipregion;

-- Query that sorts the output
SELECT shipregion
FROM Sales.Orders
ORDER BY shipregion;

Plan pierwszego zapytania użył operatora agregacji STREAM a plan drugiego zapytania nie użył operatora sortowania: ep4 Użycie zapytania wykorzystania indeksów pokazuje następujący wynik:

table_name index_name user_seeks user_scans user_lookups ---------- ----------------- ---------- ---------- ------------ Orders idx_nc_shipregion 0 2 0 Orders PK_Orders 0 4 0

Zauważmy, że indeks nieklastrowany idx_nc_shipregion został użyty do dwóch skanów i nie dokonano dodatkowego skanowania indeksu klastrowanego. SQL Serwer znalazł wszystkie dane w indeksie nieklastrowanym i nie musiał używać RID lub key lookup. Kiedy SQL Serwer znajdzie wszystkie dane w indeksie nieklastrowanym to zapytanie jest pokryte przez indeks nieklastrowany i indeks jest nazywany pokrywającym. Indeksy pokrywające są bardzo wydajne. Można dodać więcej kolumn do indeksu nieklastrowanego aby przykrywał więcej zapytań. Jednakże dłuższy klucz powoduje, że indeks staje się mniej wydajny. W SQL Serwerze 2012 istnieje inna opcja. Do indeksu nieklastrowanego można dołączyć kolumny tylko na poziomie liści nie jako część klucza. Dokonuje się tego za pomocą klauzuli INCLUDE wyrażenia CREATE INDEX. Włączone kolumny nie są częścią klucza i SQL Serwer nie używa ich do wyszukiwania. Włączone kolumny pomagają w pokryciu zapytań. Jednakże należy być ostrożnym i nie załączać zbyt wielu kolumn. Jeżeli załączymy wszystkie kolumny tabeli to tak naprawdę skopiujemy tabelę. Indeks idx_nc_shipregion nie będzie więcej używany w przykładach więc należy go usunąć:

DROP INDEX idx_nc_shipregion ON Sales.Orders;

Argumenty wyszukiwania

Dołączenie klauzuli WHERE do zapytania nawet jeżeli predykat jest bardzo selektywny i wspierany przez indeks nie zawsze gwarantuje, że SQL Serwer użyje indeksu. Trzeba napisać odpowiedni predykat aby pozwolić optymalizatorowi zapytań na wykorzystanie indeksów. Optymalizator zapytań nie jest nieomylny. Potrafi zdecydować o użyciu indeksu tylko wtedy, gdy argumenty predykatu można przeszukiwać. Należy nauczyć się jak pisać poprawne argumenty wyszukiwania (ang. appropriate search arguments - SARGs). Aby napisać poprawny SARG należy upewnić się, że kolumna na której jest założony indeks pojawia się w predykacie osobno, nie jako parametr funkcji. SARG musi mieć formę: kolumna operator wartość lub wartość operator kolumna. Nazwa kolumny musi być samotna  po jednej stronie wyrażenia a stała lub wynik wyrażenia musi być po drugiej. Do operatorów należą: =, >, <, =>, <=, BETWEEN i LIKE. Jednakże operator LIKE tylko gdy nie używa symboli wieloznacznych % lub _ na początku wyrażenia do porównania. Przykładowo poniższe zapytanie zwraca zamówienia dla dat 10-07-2006 i 11-07-2006:

SELECT orderid, custid, orderdate, shipname
FROM Sales.Orders
WHERE DATEDIFF(day, '20060709', orderdate) <= 2
AND DATEDIFF(day, '20060709', orderdate) > 0;

Zapytanie zwraca dwa wiersze więc predykat w klauzuli WHERE jest bardzo selektywny. Na tabeli został założony indeks nieklastrowany na kolumnie orderdate. Jednakże SQL Serwer nie użył indeksu jak widać na poniższym planie wykonania: ep5 Kolumna orderdate nie występuje w predykacie samotnie lecz jest argumentem funkcji. Można przekształcić takie zapytanie na wiele sposobów aby było poprawnym SARG, oto jeden z nich:

SELECT orderid, custid, orderdate, shipname
FROM Sales.Orders
WHERE DATEADD(day, 2, '20060709') >= orderdate
AND '20060709' < orderdate;

Plan wykonania pokazujący, że SQL Serwer użył indeksu idx_nc_orderdate: ep5 Zapytanie może być przekształcone na wiele różnych sposobów. Można użyć operator IN z listą dat dla których chcemy pobrać wiersze. Można użyć operatora równości dla każdej daty i złączyć predykaty operatorem logicznym OR. Takie zapytania będą wewnętrznie traktowane tak samo, optymalizator zapytań skonwertuje zapytania używające operatora IN na zapytanie z operatorem logicznym OR i operatorem równości dla każdego elementu z listy. Poniższe zapytania zwracają taki sam wynik i są  wewnętrznie traktowane przez optymalizator jako równe:

SELECT orderid, custid, orderdate, shipname
FROM Sales.Orders
WHERE orderdate IN ('20060710', '20060711');
SELECT orderid, custid, orderdate, shipname
FROM Sales.Orders
WHERE orderdate = '20060710'
OR orderdate = '20060711';

Plan wykonania: ep6 Użycie operatora logicznego AND w klauzuli WHERE oznacza, że każda część predykatu ogranicza zbiór wynikowy, nawet bardziej niż poprzednia część. Przykładowo pierwszy warunek ogranicza zbiór wynikowy do 5 wierszy więc następny warunek złączony operatorem AND ogranicza zbiór wynikowy do najwyżej 5 wierszy. Optymalizator zapytań rozumie działanie operatora logicznego AND i potrafi użyć odpowiednich indeksów. Inaczej jest w przypadku operatora OR który jest operatorem łącznym. Przykładowo jeżeli pierwszy warunek predykatu ogranicza zbiór wynikowy do 5 wierszy a drugi połączony operatorem OR ogranicza zbiór wynikowy do 6 wierszy to zbiór wynikowy może posiadać od 6 do 11 wierszy. jeżeli warunki używają różnych kolumn to SQL Serwer zachowawczo przyjmuje najgorszy przypadek i estymuje, że zapytanie zwróci 11 wierszy. Posiadanie wielu warunków OR w predykacie obniża możliwość wykorzystania indeksów przez SQL Serwer. W takich przypadkach należy rozważyć przepisanie predykatu na taki który jest równoważny i używa operatora AND. Ćwiczenia

I. Wsparcie dla operatora logicznego OR

  1. Otwórz nowe okno SSMS i połącz się do instancji SQL Serwera. Zmień kontekst na bazę danych TSQL2012. 
    USE TSQL2012
GO
  2. Utwórz indeks nieklastrowany na tabeli Sales.orders na kolumnie shipcity: 
    CREATE NONCLUSTERED INDEX idx_nc_shipcity ON Sales.Orders(shipcity);
  3. Pobierz kolumny orderid, custid i shipcity dla miasta Vancouver: 
    SELECT orderid
	,custid
	,shipcity
FROM Sales.Orders
WHERE shipcity = N'Vancouver';
  4. Zapytanie jest selektywne ponieważ zwraca tylko trzy wiersze. Sprawdź czy utworzony indeks nieklastrowany został użyty: 
    SELECT OBJECT_NAME(S.object_id) AS table_name
	,I.NAME AS index_name
	,S.user_seeks
	,S.user_scans
	,s.user_lookups
FROM sys.dm_db_index_usage_stats AS S
INNER JOIN sys.indexes AS i ON S.object_id = I.object_id
	AND S.index_id = I.index_id
WHERE S.object_id = OBJECT_ID(N'Sales.Orders', N'U')
	AND I.NAME = N'idx_nc_shipcity';
  5. Włącz wyświetlanie aktualnego planu zapytania. Pobierz te same kolumny dla klienta z identyfikatorem 42: 
    SELECT orderid
	,custid
	,shipcity
FROM Sales.Orders
WHERE custid = 42;
    Zauważ, że został użyty inny indeks.
  6. Pobierz ten sam wynik jeszcze raz lecz tym razem użyj obu predykatów i operatora OR: 
    SELECT orderid
	,custid
	,shipcity
FROM Sales.Orders
WHERE custid = 42
	OR shipcity = N'Vancouver';
  7. Przeanalizuj plan zapytania powyższego zapytania. Zatrzymaj kursor myszy na skanowaniu indeksu klastrowanego i sprawdź przybliżoną liczbę wierszy: ep11

Wsparcie dla operatora AND

  1. Zmień w ostatnim zapytaniu operator OR na AND i sprawdź plan zapytania: 
    SELECT orderid
	,custid
	,shipcity
FROM Sales.Orders
WHERE custid = 42
	AND shipcity = N'Vancouver';
  2. Usuń indeks na kolumnie shipcity: 
    DROP INDEX idx_nc_shipcity ON Sales.Orders;
  3. Utwórz indeks na kolumnie shipcity z załączoną kolumną custid: 
    CREATE NONCLUSTERED INDEX idx_nc_shipcity_i_custid ON Sales.Orders(shipcity)
INCLUDE (custid);
  4. Użyj jeszcze raz zapytania z operatorem OR: 
    SELECT orderid
	,custid
	,shipcity
FROM Sales.Orders
WHERE custid = 42
	OR shipcity = N'Vancouver';
  5. Pomimo pokrycia zapytania przez utworzony indeks otrzymujemy skanowanie indeksu nieklastrowanego. Zmieńmy operator na AND: 
    SELECT orderid
	,custid
	,shipcity
FROM Sales.Orders
WHERE custid = 42
	AND shipcity = N'Vancouver';
    Tym razem SQL Serwer używa operatora SEEK.
  6. Usuń utworzony indeks: 
    DROP INDEX idx_nc_shipcity_i_custid ON Sales.Orders;

Podsumowanie

  1. Indeksy mogą być używane przez różne części zapytania (WHERE, JOIN, GROUP BY, ORDER BY i SELECT).
  2. Poprawne argumenty wyszukiwania nie zawierają kolumn indeksu w wyrażeniach.