Który indeks zostanie wykorzystany w tym scenariuszu?

SQL Server 2014 Standard Edition

Muszę znaleźć liczbę lotów, które odbywają się do i z określonych miast na określone miesiące. Na przykład

select count(*) 
from flights 
where flightTo_AirportCode = 'aaaa' 
and flightFrom_Airportcode = 'bbbb' 
and flightdate < '2016-04-01' 
and flightdate > '2016-02-28' ;

Schemat tabeli znajduje się poniżej.

Próbuję oszacować, czy preferowany jest model indeksu A lub model indeksu B (poniżej) (tworzenie indeksu zajmuje wiele godzin, a miejsce na dysku pozwala istnieć tylko jeden na raz, więc próbuję spojrzeć, zanim skoczę).

Z mojego doświadczenia wynika, że każdy indeks wystarczy. Czy mam rację?

  create index [modelA] on flights (flightTo_AirportCode, flightFrom_AirportCode, flightDate)

  create index [modelB] on flights (flightDate, flightTo_AirportCode, flightFrom_AirportCode)

(Lub, lepiej, czy istnieje indeks binarny lub zaawansowany mechanizm, którego można użyć, aby do tego podejść?)

CREATE TABLE [dbo].[flights](
    [flightId] [uniqueidentifier] NOT NULL,
    [accountId] [uniqueidentifier] NULL,
    [flightDate] [datetime] NULL,
    [flightTo_AirportCode] [nvarchar](30) NULL,
    [flightFrom_AirportCode] [nvarchar](30) NULL,
    -- ... 45 more fields
    CONSTRAINT [PK_flight] PRIMARY KEY CLUSTERED 
(
    [flightId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

sql-server sql-server-2014 index-tuning

— Jonesome przywraca Monikę
źródło

Indeks A jest lepszy dla tego zapytania. Gdy wszystkie warunki w WHEREsą sprawdzeniami równości, z wyjątkiem jednego, który używa warunku zakresu lub INoperatora w kolumnie, to ostatnia kolumna powinna być ostatnia w indeksie, po wszystkich kolumnach, które mają kontrolę równości.

Dzięki temu optymalizator może użyć wyszukiwania indeksu do pierwszego wiersza spełniającego warunki, a następnie przechodzić przez indeks, aż znajdzie wiersz, który go nie pasuje. Wszystkie wiersze pomiędzy nimi również są identyczne.

Tak więc najlepszym indeksem dla tego zapytania będzie (to, from, date)(Twój model A) lub (from, to, date).

Indeks modelu B ma datę pierwszą, więc nie jest najlepszy, chociaż nadal jest indeksem kryjącym dla zapytania. Gdyby tak było, plan zapytań byłby prawie taki sam. Indeks szuka pierwszego wiersza, który pasuje do warunku zakresu ( date > '2016-02-28'), a następnie przechodzi przez indeks, aż znajdzie wiersz, który nie pasuje do date < '2016-04-01'. Ale wszystkie wiersze pomiędzy nimi niekoniecznie pasują do 2 innych warunków, więc musiałyby zostać sprawdzone pod kątem tych warunków i (być może wielu z nich) odrzucone.

Podczas gdy plany byłyby podobne, plan modelu A musiałby przejść tylko przez część indeksu, która ma wszystkie potrzebne wiersze i tylko one, podczas gdy plan modelu B musiałby przejść (prawdopodobnie znacznie) większą część indeks.

Najlepiej byłoby również użyć w 100% bezpiecznego formatu dla dat ( YYYYMMDD).
A jeśli chcesz daty w marcu, powinieneś skorzystać z czeku wyłącznego:
```
AND flightdate >= '20160301' AND flightdate < '20160401' 
```
Gwarantujemy pracę z typami daty i godziny. Twoje obecne zapytanie będzie obejmować również każdy wiersz, który ma '2016-02-28'inny czas niż '00:00:00'(czy możesz zagwarantować, że nie ma takiego?), Którego, jak zakładam, nie chcesz. Metoda obejmująca wyłączność będzie również działać w latach przestępnych (przypominając, że 2016 jest rokiem przestępnym, więc data 29 lutego również została zwrócona).

Przeczytaj także następujące posty na blogu Aarona Bertranda:

— ypercubeᵀᴹ
źródło