Cezary Walenciuk

Indeksy

@walenciukC

Agenda

Podział indeksów
Indeks Clustered
Architektura Indeksu i jego użycie
Jak zorganizować indeks i tabele?
Wizualizacja systemu
Wycena zadań i ich kierunek
Indeks NonClustered
Jak dopasować indeksy do zapytań
Indeksy a zapytania z JOIN-ami
Indeksowanie widoków
Columnstore
Podsumowanie

Tabelki do dema

Indeksy podstawy

Typy indeksów

Clustered
Nonclustered

Clustered

Sortuje dane (zazwyczaj po kluczu głównym)
Kierunek sortowania możesz ustalić
Jeżeli go nie ma wtedy dane są nieposortowane w strukturze Heap (sterta,kopiec)

Clustered

Nie zadziała jeśli twoim kluczem głównym jest typ: GUID
Indeks ten nie pochłania żadnej przestrzeni gdyż on tylko układa dane
Są to najszybsze indeksy, gdyż nie wymagaja dodatkowego kroku szukania

Clustered

Jeżeli twój klucz główny (ID) jest unikatowy to baza danych automatycznie go tworzy
Jeśli próbujesz go utworzyć gdy już on istnieje to...pojawi się sugestia, aby utworzyć indeks Nonclustered

Clustered

Gdy clustered index nie jest unikatowy to wtedy tworzy dodatkowa ukrytą kolumnę (zajmuje ona 4 bajty)

                    

                        SELECT 
                             [Id]
                            ,[Class]
                            ,[PowerLevel]
                            ,[NickName]
                            ,[Adress]
                            ,[CustomCodeAbilityId]
                        FROM [HeroDataBase].[dbo].[SuperHeroes]

                    

                        SELECT [Id]
                            ,[Class]
                            ,[PowerLevel]
                            ,[NickName]
                            ,[Adress]
                            ,[CustomCodeAbilityId]
                        FROM [HeroDataBase].[dbo].[SuperHeroes_NoIndex]
                        ORDER BY ID DESC

Najpierw jednak coś o przechowywowaniu danych

SQL Server przetrzymuje dane 8kb (8192b) kawałkach, które nazywamy "page"

"Pages" mają znaczenie nie tylko na dysku, ale także w pamięci
Jeśli jesteś wstanie umieść cały rekord do jednego "page" to super

SQL Server przetrzymuje dane 8kb (8192b) kawałkach, które nazywa "page"

Jeśli 1 rekord w tabeli zajmuje 4100 bajtów to tylko on będzie w jednym "page", a reszta (3960) będzie niewykorzystana
Dlatego 1 rekord najlepiej, aby reprezentował informację mniejszą niż 4000 bajtów. Wtedy do 1 page trafią 2 rekordy

Architektura indeksu

SQL Server przetrzymuje dane 8kb kawałkach, które nazywa "page". Dotyczy to też indeksów
Te kawałki "pages" są przetrzymwane w strukture drzewiastą (Balance Tree)
Załóżony indeks na kolumnie/nach pozwala nam łatwiej wyszukiwać dane

Operacje indeksowe w Execution Plans

Index Seek : Nawigacja w dół drzewa, aby znaleźć wartość
Index Scan : Przeczytaj kilka lub wszystkie liście drzewa
Key Lookup : Pojedyńcze wyszukiwanie

                    

                        SELECT [MonsterAttackId]
                        ,[AttackType]
                        ,[DamageToTheCityInNumbers]
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [AttackType] = 'A' AND 
                        [DamageToTheCityInNumbers] >= 24928

                        -- SCAN or SEEK

                    

                        SELECT 
                        SUM([DamageToTheCityInNumbers])
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        Group By [AttackType]

                        -- SCAN

Idealny Index na jakiej kolumnie

Idealny index

Unikatowy (ma unikatowe wartości)
Niezmieniający
Rosnący według wzoru (do góry, do dołu)
Wąski (ile kolumn chcesz w indeksie)

Spełnienie tych wszystkich zasad może być trudne

Niezmieniający

Zmiana może spowodować rozbicie "Page" i utworzenie pustej "Page"

Wąski

Im większy indeks tym większe koszty w przestrzeni
Dla Clustered Indeks to 4/9000 bity
Może stworzyć głębsze drzewo

Rosnący ciągle tak samo

Insert losowych wartości w indeksie powoduje rozbijanie struktury "page"

Zróbmy test

                    

                        CREATE CLUSTERED INDEX idx_MonsterAttacks_ClusteredALL
                        on dbo.MonsterAttacks_ClusteredALL 
                        ([MonsterAttackId],[MonsterId],[CityId],
                        [AttackDate],[AttackType],[DamageToTheCityInNumbers])

                    

                        CREATE CLUSTERED INDEX 
                        idx_MonsterAttacks_ClusteredDamagToTheCity
                        on dbo.MonsterAttacks_ClusteredDamagToTheCity 
                        (DamageToTheCityInNumbers)

                    

                        CREATE CLUSTERED INDEX 
                        idx_MonsterAttacks_ClusteredAttackDate
                        on dbo.MonsterAttacks_ClusteredAttackDate 
                        (AttackDate)

                    

                        CREATE OR ALTER  PROCEDURE [dbo].[InsertMonsterAttacks_ClusteredAttackDate]
                        AS
                        
                        INSERT INTO [MonsterAttacks_ClusteredAttackDate]
                                   ([MonsterId]
                                   ,[CityId]
                                   ,[AttackDate]
                                   ,[AttackType]
                                   ,[DamageToTheCityInNumbers])
                             VALUES
                                   (1
                                   ,1
                                   ,GETDATE()
                                   ,'T'
                                   ,RAND(CHECKSUM(NEWID()))*5000)

                    

                        CREATE OR ALTER  PROCEDURE [dbo].[UpdateRowMonsterAttacks_ClusteredDamagToTheCity]
                        AS
                        
                        DECLARE @i INT = FLOOR(RAND(CHECKSUM(NEWID())) * 18008)
                        
                        UPDATE [dbo].InsertMonsterAttacks_ClusteredDamagToTheCity
                            SET [DamageToTheCityInNumbers] = RAND(CHECKSUM(NEWID()))*500
                            WHERE MonsterAttackId = @i;

                    
                        //C#
                        using (var conn = new SqlConnection(connectionString))
                        {
                            watch.Start();
                            conn.Open();
                            for (int i = 0; i < 5000; i++)
                            {
                                using (var command = new SqlCommand(insertProcedureName, conn)
                                {
                                    CommandType = CommandType.StoredProcedure
                                })
                                {
                                    command.ExecuteNonQuery();
                                }
                            }
                        }

Sprawdzenie rozmiarów indeksów na Tabelkach

                    
                        SELECT OBJECT_NAME(OBJECT_ID) AS TableName,
                        'Index' = 
                          CASE 
                             WHEN index_id  =  0 THEN 'HEAP'
                             WHEN index_id  = 1  THEN 'Clustered index.'
                             WHEN index_id  > 1 THEN 'Nonclustered index'
                             ELSE 'ERROR'
                        END,
                        used_page_count,
                        reserved_page_count,
                        row_count
                        FROM sys.dm_db_partition_stats
                        WHERE OBJECT_NAME(object_id) IN 
                        (
                            'MonsterAttacks_ClusteredALL',
                            'MonsterAttacks_ClusteredAttackDate',
                            'MonsterAttacks_ClusteredDamagToTheCity',
                            'MonsterAttacks_ClusteredId'
                        )
                        and index_id  = 1

Co trzeba zrobić : Zorganizować tabele

Tabelka powinna być jak najmniejsza
Ograniczyć do minium "page" split
Unikać czkawek przy aktualizacji

Co trzeba zrobić : Wspieranie jakich zapytań

Być może będzie trzeba stworzyć indeksy nonclustered
Tabelka może zajmować więcej dysku, ale zapytanie będzie działać szybciej
Oczywiście, że będą problemy z page split

Kompromis 1

W wielu przypadkach wystarczy standardowy indeks clustered na ID, który jest kluczem głównym
Wspierasz tak pewne zapytania (choćby JOIN-y) i spełniasz specfikacje idealnego indeksu

Kompromis 2

Załóżmy, że masz tabele gdzie kolumna DATY
Może tam wstawić indeks
Zakładając też, że DATY są unikatowe i zawsze rosną w przyszłość

Typy indeksów

Clustered
Nonclustered

Indeksy Nonclustered

Oddzielna struktura od Tabelki
Możesz utworzyć ich więcej w danej Tabeli (10 jest ok, 76 to za dużo)
Nie muszą zawierać wszystkich kolumn
Zawsze są sychronizowne z tabelką

Predytkaty

Predytkaty Równości

                    

                        WHERE DamageToTheCityInNumbers = 105

                        WHERE DamageToTheCityInNumbers = 105 AND
                        CityId = 2

Predytkaty Nie równości

                    

                        WHERE DamageToTheCityInNumbers > 5000

                        WHERE DamageToTheCityInNumbers > 5000 AND
                        CityId = 2

                        WHERE DamageToTheCityInNumbers > 5000 AND
                        CityId > 3

Predytkaty połączone z OR

                    

                        WHERE AttackType = 'M' OR AttackType = 'S'

                        WHERE DamageToTheCityInNumbers > 5000 
                        OR AttackType = 'S'

                        WHERE DamageToTheCityInNumbers > 5000 OR
                        CityId > 3

Jak tam JOIN-y

                    

                        SELECT 
                            F.[DidDefeatMonster],
                            MA.AttackDate,
                            M.Name,
                            M.DisasterLevel
                        FROM [HeroDataBase].[dbo].[Fight] AS F
                        INNER JOIN [MonsterAttacks] AS MA ON 
                        MA.MonsterAttackId = F.MonsterAttackId
                        INNER JOIN [Monster] AS M ON M.IdMonster = MA.MonsterId

Zasady dla równości

Zapytanie musi filtrować zgodnie z lewym podzbiorem danego indeksu
Kolejność kolumn ma zaczenie gdy chcemy aby wiele zapytań mogło skorzystać z jednego indeksu

A co teraz?

A to da radę?

Zmiana indeksu : co teraz gdy jest inna kolejność kolumn

Co jeśli mamy wiele zapytań

Zasady dla NIE równości

Lewy podzbiór indeksu ma znaczenie
Kolumny równości mają pierwszeństwo przed kolumnami nierówności
Wiele nierówności ciężko jest zaindeksować

Zmiana kolejności indeksu

Zobaczmy to w praktyce

Zapytanie dla CityId = 4

                    

                        SELECT  [MonsterAttackId]
                        ,[CityId]
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [CityId] = 4

Indeks NONCLUSTERED na CityId

                    

                        CREATE NONCLUSTERED INDEX 
                        idx_MonsterAttacks_CityId 
                        ON [dbo].[MonsterAttacks](CityId);

Równości WHERE [AttackType] = 'S';

                    

                        SELECT  [MonsterAttackId]
                        ,[AttackType]
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [AttackType] = 'S';

Tworze kolejny indeks NonClustered dla [AttackType]

Równości [AttackType] = 'S' AND [CityId] = 51

                    

                        SELECT  [MonsterAttackId]
                        ,[AttackType]
                        ,[CityId]
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [AttackType] = 'S' AND [CityId] = 51

Tworze kolejny indeks NonClustered dla [AttackType,CityId]

Predytkaty nierówności

                    

                        SELECT  [MonsterAttackId]
                        ,[DamageToTheCityInNumbers]
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [DamageToTheCityInNumbers] > 9000
                  
                        SELECT  [MonsterAttackId]
                                ,[DamageToTheCityInNumbers], [CityId] 
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [DamageToTheCityInNumbers] > 9000 AND [CityId] = 175
                  
                        SELECT  [MonsterAttackId]
                                ,[DamageToTheCityInNumbers], [AttackDate] 
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [DamageToTheCityInNumbers] > 9000 AND [AttackDate] < '2019-10-02'

Jak to jest z operacją OR

Jak kto jest z operacją OR

Zwiększają rezultat lub go nie zmieniają
Potrzeba wielu indeksów, aby operacja OR została wykonana najlepiej

Predytkaty połączone z OR

                    

                        SELECT  [MonsterAttackId]
                        ,[DamageToTheCityInNumbers], [AttackDate] 
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [DamageToTheCityInNumbers] > 8000 
                        OR [AttackDate] < '2019-10-10'

Załóżmy 1 indeks na ([DamageToTheCityInNumbers]), i drugi na ([AttackDate])

Predytkaty połączone z OR

                    

                        SELECT  [MonsterAttackId]
                        ,[DamageToTheCityInNumbers], [AttackDate] 
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [DamageToTheCityInNumbers] = 8000 
                        OR [AttackDate] = '2019-10-10'

Indeksowanie, a JOIN-y i operacje

Jakiś słownik? Algorytmy scalajace rekordy

Nested loop joins : to algorytm dwóch pętli chodząca po dwóch tabelach. Wykonują one operacje Join-owania. Wydajna gdy mamy indeksy
Hash match : Analizator go wybiera, gdy mamy dużo rekordów do łączenia
Merge : Scala wartości dwóch tabel i później je sortuje

Jakiś słownik? Słowa kluczowe Merge Join i Hash Join

Merge Joins : Scala wartości dwóch tabel i później sortuje
Hash Joins : buduje w pamięci tabelę hash na bazie wewnętrznej dołączanej kolumny. Potem zewnętrzna tabelka jest porównywana z hash tabel.

Indeksowanie, a JOIN-y i operacje

Nested loop joins : lubią indeks na wewnętrznej tabeli
Merge Joins : mogą mieć korzyści z indeksów które dają odpowiednią kolejność
Hash Joins : nie dostaja żadnych zalet z indeksów

JOIN

                    

                        SELECT 
                            F.[DidDefeatMonster],
                            MA.AttackDate,
                            M.Name,
                            M.DisasterLevel
                        FROM [HeroDataBase].[dbo].[Fight] AS F
                        INNER JOIN [MonsterAttacks] AS MA 
                        ON MA.MonsterAttackId = F.MonsterAttackId
                        INNER JOIN [Monster] AS M ON M.IdMonster = MA.MonsterId
                        WHERE M.Name = 'Geryuganshoop'

                    

                        SELECT 
                            F.[DidDefeatMonster],
                            MA.AttackDate,
                            M.Name,
                            M.DisasterLevel
                        FROM [HeroDataBase].[dbo].[Fight] AS F
                        INNER MERGE JOIN [MonsterAttacks] AS MA 
                        ON MA.MonsterAttackId = F.MonsterAttackId
                        INNER MERGE JOIN [Monster] AS M ON M.IdMonster = MA.MonsterId
                        WHERE M.Name = 'Geryuganshoop'

                    

                        SELECT 
                            F.[DidDefeatMonster],
                            MA.AttackDate,
                            M.Name,
                            M.DisasterLevel
                        FROM [HeroDataBase].[dbo].[Fight] AS F
                        INNER HASH JOIN [MonsterAttacks] AS MA 
                        ON MA.MonsterAttackId = F.MonsterAttackId
                        INNER HASH JOIN [Monster] AS M ON M.IdMonster = MA.MonsterId
                        WHERE M.Name = 'Geryuganshoop'

Indeksy i dodanie do nich kolumn

Dodanie kolumny/kolumn na poziomie liścia w indeksie
Używa się tej techniki, aby uniknąć operacji "key lookup"
Key Lookup : Pojedyńcze wyszukiwanie przy clustered index

Key Lookups

Single Row Seek: Poszukiwanie rekordu w clustered index
Pobiera kolumny które są wymagane, ale nie są przez indeks używane
Powolne jeśli jest duża liczba wyników

Przykład

                    

                        SELECT [MonsterAttackId]
                            ,[CityId]
                            ,[AttackDate]
                            ,[AttackType]
                            ,[DamageToTheCityInNumbers]
                            ,[IsDone]
                            ,[IsRepaired]
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [CityId] = 2487 AND [AttackType] = 'J'
                    
                        CREATE NONCLUSTERED INDEX idx_test2 ON 
                        [dbo].[MonsterAttacks]([CityId],[AttackType])

                    

                        SELECT [MonsterAttackId]
                            ,[CityId]
                            ,[AttackDate]
                            ,[AttackType]
                            ,[DamageToTheCityInNumbers]
                            ,[IsDone]
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [CityId] = 2487 AND [AttackType] = 'J'
                    
                        CREATE NONCLUSTERED INDEX idx_test3 ON 
                        [dbo].[MonsterAttacks]([CityId],[AttackType])
                        INCLUDE([DamageToTheCityInNumbers],[AttackDate],
                        [MonsterAttackId],[IsDone])

Filtry na Indeksach

Możesz zaindeksować część rekordów w tabelce
Przydatne gdy wiesz, że pewne dane nie powinny być odczytywane
Nie działa z zapytaniami które mają parametry

                    

                        SELECT [MonsterAttackId]
                        ,[CityId]
                        ,[AttackType]
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [CityId] = 2487 AND [AttackType] = 'J'
                  
                        CREATE NONCLUSTERED INDEX idx_Test ON 
                        [dbo].[MonsterAttacks]([CityId])
                        WHERE [AttackType] = 'J'

Problem z filtrem gdy korzystamy z parametrów

                    

                        DECLARE @CityId INT = 2487, 
                        @AttackType CHAR(1) = 'J'

                        SELECT [MonsterAttackId]
                            ,[CityId]
                            ,[AttackType]
                        FROM [HeroDataBase].[dbo].[MonsterAttacks]
                        WHERE [CityId] = @CityId 
                        AND [AttackType] = @AttackType

Jak wiele indeksów NonClustered?

Jak wiele indeksów NonClustered

Tyle ile potrzebujesz
Nie ma jednak sensu tworzyć indeks NonClustered per kolumna
Lepiej jest przeanalizować jakie zapytania będą trafiać do tej właśnie tabelki

Indeksy dla View

Normalnie View(widoki) są zapisanymi wyrażeniami SELECT
Jest możliwe utworzenie indeksu clustered na widoku (ale nie każdym)
Materializuje to widok w taki sposób jakby to była tabela
Dane widoku są aktualizowane nabierzącą gdy główna tabela, która ją reprezentuje się zmieni

Istnieją jednak ograniczenia

Blokuje ona główną tabele do zmian (najpier musisz skasować indeks z widoku, skasować widok, zrobić zmianę na tabeli i utworzyć wszystko od nowa)
Widok nie może zawierać pod zapytań
Wszystkie JOIN w widoku muszą być INNER LEFT JOINS

Gdzie są użyteczne?

Do Agregacji
Proste zapytania z JOIN-ami i filtrami

                    

                        CREATE OR ALTER VIEW dbo.MonsterAttacksWithTotals
                        WITH SCHEMABINDING
                        AS
                        
                        SELECT
                        COUNT_BIG(*) AS NumberOfAttacks,
                        ma.CityId,
                        SUM(ma.DamageToTheCityInNumbers) AS TotalDamage,
                        AVG(ma.DamageToTheCityInNumbers) AS AVGDamage
                        FROM [dbo].[MonsterAttacks] AS ma
                        GROUP BY ma.CityId
                        HAVING COUNT_BIG(*)< 100 AND COUNT_BIG(*) > 2
                        
                        GO
                        
                        CREATE UNIQUE CLUSTERED INDEX idx_MonsterAttacksWithTotals
                        ON dbo.MonsterAttacksWithTotals (CityId);

                    

                        CREATE OR ALTER VIEW dbo.MonsterAttacksWithTotals
                        WITH SCHEMABINDING
                        AS
                        
                        SELECT
                        COUNT_BIG(*) AS NumberOfAttacks,
                        ma.CityId,
                        SUM(ma.DamageToTheCityInNumbers) AS TotalDamage
                        FROM [dbo].[MonsterAttacks] AS ma
                        GROUP BY ma.CityId
                        
                        GO
                        
                        CREATE UNIQUE CLUSTERED INDEX idx_MonsterAttacksWithTotals
                        ON dbo.MonsterAttacksWithTotals (CityId);

Wady indeksów w widokach

Przeciążanie, jeśli tabelka główna jest ciagle aktualizowana/kasowana
Zajmują przestrzeń pamięci
Niesamowite wymagania i ustawienia "SET Options"

Indeksy ColumnStore

Pojawił się w SQL Server 2012, ale dopiero teraz działa on dobrze
Są to zupełnie inne indexy. Gdzie dane są przetrzymywane innaczej
Dane z kolumn są przechowywane razem

Indeksy ColumnStore

Są skompresowane więc są super dla gigatycznej liczby rekordów np. 2 miliony

Architektura ColumnStore Index

                    

                        CREATE NONCLUSTERED COLUMNSTORE INDEX 
                        [NonClusteredColumnStoreIndex-20201004] 
                        ON [dbo].[MonsterAttacks_ColumnIndex]
                        (
                            [MonsterAttackId],
                            [AttackDate],
                            [AttackType],
                            [DamageToTheCityInNumbers]
                        )

Typy Indeksy ColumnStore

Clustered ColumnStore : Organizuje tabele i zawiera wszystkie kolumny
NonClustered columnstore : Zawiera tylko kolumny, które określiśmy

                    

                        SELECT [Id]
                            ,[Class]
                            ,[PowerLevel]
                            ,[NickName]
                            ,[Adress]
                        FROM [HeroDataBase].[dbo].[SuperHeroes_ColumnIndex]
                    
                    
                        SELECT [Id]
                            ,[Class]
                            ,[PowerLevel]
                            ,[NickName]
                            ,[Adress]
                        FROM [HeroDataBase].[dbo].[SuperHeroes]

Kiedy ColumnStore

Clustered ColumnStore : na dużych tabelach
NonClustered columnstore : może pomóc w wyszukiwaniach

ColumnStore słaby dla tabel gdy często ją modyfikujemy

DeltaStore jest Drzewem
Nowe rekordy nie są skompresowane

ColumnStore

Kompresja działa najlepiej dla powtarzających się danych
Nie może być używany do operacji "SEEK" (operacja 'SEEK' zamienia się w operacje 'Scan')

ColumnStore

Kolejność kolumn w tym indeksie nie ma znaczenia

Dane na temat SuperHeroes_ColumnInde : Ile zajmuje PAGE

                    

                        SELECT 
                            OBJECT_ID,
                            index_id,
                            used_page_count,
                            reserved_page_count,
                            row_count,
                            OBJECT_NAME(OBJECT_ID)
                        FROM sys.dm_db_partition_stats 
                        WHERE OBJECT_ID = OBJECT_ID('SuperHeroes_ColumnIndex') 
                        OR OBJECT_ID = OBJECT_ID('MonsterAttacks_ColumnIndex');

Jak jest skompresowane? Columnstore

                    

                        SELECT OBJECT_NAME(OBJECT_ID),* FROM sys.column_store_row_groups;

Zróbmy 18008 Insertów

                    

                        INSERT INTO [dbo].[SuperHeroes_ColumnIndex]
                        ([Class]
                        ,[PowerLevel]
                        ,[NickName]
                        ,[Adress])
                        VALUES
                        ('Y'
                        ,@randompowerlevel
                        ,@randomname
                        ,@randomname2)

Zróbmy 5000 losowych kasowań i 18000 update-ów

                    

                        DECLARE @i INT = FLOOR(RAND(CHECKSUM(NEWID())) * 18008)

                        UPDATE [dbo].[SuperHeroes_ColumnIndex]
                            SET [PowerLevel] = RAND(CHECKSUM(NEWID()))*500
                            WHERE Id = @i;

                        DECLARE @j INT = FLOOR(RAND(CHECKSUM(NEWID())) * 18008)

                        DELETE [dbo].[SuperHeroes_ColumnIndex]
                            WHERE Id = @j;

Jeszcze 18000 update-ów

Podsumowanie

Dzięki za obecność