Indeksy SQL Server - rosnąco czy malejąco, jakie to ma znaczenie?

Kiedy tworzysz indeks na kolumnie lub liczbie kolumn w MS SQL Server (używam wersji 2005), możesz określić, że indeks w każdej kolumnie będzie rosnący lub malejący. Trudno mi zrozumieć, dlaczego ten wybór jest tutaj. Czy przy użyciu technik sortowania binarnego wyszukiwanie nie byłoby równie szybkie? Jakie ma to znaczenie, które zamówienie wybieram?

Ma to znaczenie przede wszystkim w przypadku korzystania z indeksów złożonych:

CREATE INDEX ix_index ON mytable (col1, col2 DESC);

może być używany do:

SELECT  *
FROM    mytable
ORDER BY
        col1, col2 DESC

lub:

SELECT  *
FROM    mytable
ORDER BY
        col1 DESC, col2

, ale nie dla:

SELECT  *
FROM    mytable
ORDER BY
        col1, col2

Indeks w jednej kolumnie może być efektywnie używany do sortowania na oba sposoby.

Zobacz artykuł na moim blogu, aby uzyskać szczegółowe informacje:

• Indeksy malejące

Aktualizacja:

W rzeczywistości może to mieć znaczenie nawet dla indeksu pojedynczej kolumny, chociaż nie jest to takie oczywiste.

Wyobraź sobie indeks w kolumnie tabeli grupowanej:

CREATE TABLE mytable (
       pk INT NOT NULL PRIMARY KEY,
       col1 INT NOT NULL
)
CREATE INDEX ix_mytable_col1 ON mytable (col1)

Indeks na col1zachowuje uporządkowane wartości col1wraz z odwołaniami do wierszy.

Ponieważ tabela jest zgrupowana, odniesienia do wierszy są w rzeczywistości wartościami pk. Są również uporządkowane w ramach każdej wartości col1.

Oznacza to, że liście indeksu są faktycznie uporządkowane według (col1, pk), a to zapytanie:

SELECT  col1, pk
FROM    mytable
ORDER BY
        col1, pk

nie wymaga sortowania.

Jeśli utworzymy indeks w następujący sposób:

CREATE INDEX ix_mytable_col1_desc ON mytable (col1 DESC)

, wtedy wartości col1będą sortowane malejąco, ale wartości w pkramach każdej wartości col1będą sortowane rosnąco.

Oznacza to, że następujące zapytanie:

SELECT  col1, pk
FROM    mytable
ORDER BY
        col1, pk DESC

może być obsługiwany przez, ix_mytable_col1_descale nie przez ix_mytable_col1.

Innymi słowy, kolumny stanowiące a CLUSTERED INDEXw dowolnej tabeli są zawsze kolumnami końcowymi dowolnego innego indeksu w tej tabeli.

W przypadku prawdziwego indeksu pojedynczej kolumny ma to niewielki wpływ z punktu widzenia Optymalizatora zapytań.

Do definicji tabeli

CREATE TABLE T1( [ID] [int] IDENTITY NOT NULL,
                 [Filler] [char](8000) NULL,
                 PRIMARY KEY CLUSTERED ([ID] ASC))

Zapytanie

SELECT TOP 10 *
FROM T1
ORDER BY ID DESC

Używa uporządkowanego skanowania z kierunkiem skanowania, BACKWARDjak widać w Planie wykonania. Istnieje jednak niewielka różnica polegająca na tym, że obecnie tylko FORWARDskany mogą być równoległe.

Jednak może to mieć duże znaczenie pod względem logicznej fragmentacji . Jeśli indeks jest tworzony z kluczami malejącymi, ale nowe wiersze są dołączane z rosnącymi wartościami klucza, możesz skończyć z każdą stroną poza kolejnością logiczną. Może to poważnie wpłynąć na rozmiar odczytów IO podczas skanowania tabeli i nie ma ich w pamięci podręcznej.

Zobacz wyniki fragmentacji

                    avg_fragmentation                    avg_fragment
name   page_count   _in_percent         fragment_count   _size_in_pages
------ ------------ ------------------- ---------------- ---------------
T1     1000         0.4                 5                200
T2     1000         99.9                1000             1

dla skryptu poniżej

/*Uses T1 definition from above*/
SET NOCOUNT ON;

CREATE TABLE T2( [ID] [int] IDENTITY NOT NULL,
                 [Filler] [char](8000) NULL,
                 PRIMARY KEY CLUSTERED ([ID] DESC))

BEGIN TRAN

GO
INSERT INTO T1 DEFAULT VALUES
GO 1000
INSERT INTO T2 DEFAULT VALUES
GO 1000

COMMIT

SELECT object_name(object_id) AS name, 
       page_count, 
       avg_fragmentation_in_percent, 
       fragment_count, 
       avg_fragment_size_in_pages 
FROM 
sys.dm_db_index_physical_stats(db_id(), object_id('T1'), 1, NULL, 'DETAILED') 
WHERE  index_level = 0 
UNION ALL 
SELECT object_name(object_id) AS name, 
       page_count, 
       avg_fragmentation_in_percent, 
       fragment_count, 
       avg_fragment_size_in_pages 
FROM 
sys.dm_db_index_physical_stats(db_id(), object_id('T2'), 1, NULL, 'DETAILED') 
WHERE  index_level = 0 

Można użyć karty wyników przestrzennych, aby zweryfikować przypuszczenie, że dzieje się tak, ponieważ późniejsze strony mają rosnące wartości kluczy w obu przypadkach.

SELECT page_id,
       [ID],
       geometry::Point(page_id, [ID], 0).STBuffer(4)
FROM   T1
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )
UNION ALL
SELECT page_id,
       [ID],
       geometry::Point(page_id, [ID], 0).STBuffer(4)
FROM   T2
       CROSS APPLY sys.fn_PhysLocCracker( %% physloc %% )

Kolejność sortowania ma znaczenie, gdy chcesz pobrać wiele posortowanych danych, a nie pojedyncze rekordy.

Zwróć uwagę, że (jak sugerujesz w swoim pytaniu) kolejność sortowania jest zwykle znacznie mniej istotna niż to, które kolumny indeksujesz (system może odczytać indeks w odwrotnej kolejności, jeśli kolejność jest odwrotna do oczekiwanej). Rzadko kiedy zastanawiam się nad porządkiem sortowania indeksu, podczas gdy męczy mnie kolumny objęte indeksem.

@Quassnoi stanowi doskonały przykład tego, kiedy ma to znaczenie.