Czy powinienem używać wielu indeksów pojedynczych pól zamiast określonych indeksów wielokolumnowych?

To pytanie dotyczy skuteczności techniki indeksowania programu SQL Server. Myślę, że jest to znane jako „przecięcie indeksu”.

Pracuję z istniejącą aplikacją SQL Server (2008), która ma wiele problemów z wydajnością i stabilnością. Programiści zrobili kilka dziwnych rzeczy z indeksowaniem. Nie udało mi się uzyskać jednoznacznych testów porównawczych w tych kwestiach, ani też nie mogę znaleźć żadnej naprawdę dobrej dokumentacji w Internecie.

W tabeli jest wiele przeszukiwalnych kolumn. Twórcy utworzyli indeks jednej kolumny na KAŻDEJ z możliwych do przeszukiwania kolumn. Teoria była taka, że ​​SQL Server będzie w stanie łączyć (przecinać) każdy z tych indeksów, aby w większości przypadków skutecznie uzyskać dostęp do tabeli . Oto uproszczony przykład (prawdziwa tabela ma więcej pól):

CREATE TABLE [dbo].[FatTable](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [col1] [nchar](12) NOT NULL,
    [col2] [int] NOT NULL,
    [col3] [varchar](2000) NOT NULL, ...

CREATE NONCLUSTERED INDEX [IndexCol1] ON [dbo].[FatTable]  ( [col1] ASC )
CREATE NONCLUSTERED INDEX [IndexCol2] ON [dbo].[FatTable] ( [col2] ASC )

select * from fattable where col1 = '2004IN' 
select * from fattable where col1 = '2004IN' and col2 = 4

Myślę, że indeksy wielu kolumn ukierunkowane na kryteria wyszukiwania są znacznie lepsze, ale mogę się mylić. Widziałem plany zapytań, które pokazują, że SQL Server wykonuje dopasowanie skrótu dla dwóch prób indeksowych. Być może ma to sens, gdy nie wiesz, jak przeszukiwana jest tabela? Dzięki.

To, czego potrzebujesz, obejmuje indeksy, tj. indeksy, które same mogą spełnić zapytanie. Ale indeks „obejmujący” ma jeden problem: obejmuje określone zapytanie . Aby więc opracować dobrą strategię indeksowania, musisz zrozumieć swoje obciążenie: jakie zapytania trafiają do bazy danych, które są krytyczne, a które nie, jak często uruchamiane są poszczególne typy zapytań itp. Itd. zrównoważyć to z kosztem zapisu i aktualizacji każdego indeksu, a tam masz swoją strategię indeksowania. Jeśli brzmi to skomplikowanie, to dlatego, że jest skomplikowane.

Możesz jednak zastosować pewne praktyczne zasady. MSDN dość dobrze obejmuje podstawy:

• Podstawy projektowania indeksu
• Ogólne wytyczne dotyczące projektowania indeksu
• Wskazówki dotyczące projektowania indeksu klastrowego
• Wytyczne dotyczące projektowania indeksu nieklastrowanego

Istnieje również mnóstwo artykułów nadesłanych przez społeczność, np. Nagrywanie transmisji internetowej - nagrody DBA Darwin Awards: edycja indeksu .

I aby odpowiedzieć konkretnie na twoje pytanie: osobne indeksy w każdej kolumnie mogą działać, pod warunkiem, że każda kolumna ma wysoką selektywność (wiele odrębnych wartości, każda z wartości pojawia się tylko kilka razy w bazie danych). Wynikowy plan dostępu z łączeniem mieszającym między dwoma skanami zakresu indeksu zwykle działa całkiem dobrze. Kolumny o niskiej selektywności (kilka odrębnych wartości, każda wartość pojawia się wiele razy w bazie danych) nie mają sensu być indeksowane same, optymalizator zapytań po prostu je zignoruje. Jednak kolumny o niskiej selektywności wielokrotnie tworzą dobre klucze kompozytowe , gdy są sparowane z kolumną o wysokiej selektywności.