SQL
Indeksy

Wprowadzenie

Uwagi

Tworzenie indeksu

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

Spowoduje to utworzenie indeksu dla kolumny EmployeeId w tabeli Cars . Ten indeks poprawi szybkość zapytań proszących serwer o sortowanie lub wybieranie według wartości w EmployeeId , takich jak:

SELECT * FROM Cars WHERE EmployeeId = 1

Indeks może zawierać więcej niż 1 kolumnę, jak poniżej;

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

W takim przypadku indeks byłby przydatny w przypadku zapytań o sortowanie lub wybieranie według wszystkich zawartych kolumn, jeśli zestaw warunków jest uporządkowany w ten sam sposób. Oznacza to, że podczas pobierania danych może znaleźć wiersze do pobrania przy użyciu indeksu, zamiast przeglądać pełną tabelę.

Na przykład następujący przypadek wykorzystałby drugi indeks;

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

Jeśli kolejność jest inna, indeks nie ma takich samych zalet, jak poniżej;

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

Indeks nie jest tak pomocny, ponieważ baza danych musi pobrać cały indeks we wszystkich wartościach EmployeeId i CarID, aby ustalić, które elementy mają OwnerId = 17 .

(Indeks może być nadal używany; może się zdarzyć, że optymalizator zapytań stwierdzi, że pobieranie indeksu i filtrowanie na OwnerId , a następnie pobieranie tylko potrzebnych wierszy jest szybsze niż pobieranie pełnej tabeli, zwłaszcza jeśli tabela jest duża.)

Indeksy klastrowane, unikalne i posortowane

Indeksy mogą mieć kilka cech, które można ustawić albo podczas tworzenia, albo przez zmianę istniejących indeksów.

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email);  

Powyższa instrukcja SQL tworzy nowy indeks klastrowy dla pracowników. Indeksy klastrowe to indeksy, które dyktują rzeczywistą strukturę tabeli; sama tabela jest sortowana w celu dopasowania do struktury indeksu. Oznacza to, że w tabeli może znajdować się najwyżej jeden indeks klastrowany. Jeśli indeks klastrowany już istnieje w tabeli, powyższa instrukcja zakończy się niepowodzeniem. (Tabele bez indeksów klastrowych nazywane są również stertami).

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

Spowoduje to utworzenie unikalnego indeksu dla kolumny E-mail w tabeli Klienci . Indeks ten, wraz z przyspieszaniem zapytań, jak normalny indeks, wymusi także, aby każdy adres e-mail w tej kolumnie był unikalny. Jeśli wiersz zostanie wstawiony lub zaktualizowany przy użyciu nieunikalnej wartości e - mail , wstawienie lub aktualizacja domyślnie się nie powiedzie.

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

Spowoduje to utworzenie indeksu dla klientów, który tworzy również ograniczenie tabeli, że identyfikator pracownika musi być unikalny. (To się nie powiedzie, jeśli kolumna nie jest obecnie unikatowa - w tym przypadku, jeśli są pracownicy, którzy dzielą identyfikator.)

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

Spowoduje to utworzenie indeksu posortowanego w kolejności malejącej. Domyślnie indeksy (przynajmniej na serwerze MSSQL) rosną, ale można to zmienić.

Wstawianie z unikalnym indeksem

UPDATE Customers SET Email = "[email protected]" WHERE id = 1;

Nie powiedzie się to, jeśli w kolumnie E-mail klientów zostanie ustawiony unikalny indeks. W tym przypadku można jednak zdefiniować alternatywne zachowanie:

UPDATE Customers SET Email = "[email protected]" WHERE id = 1 ON DUPLICATE KEY; 

SAP ASE: Indeks upuszczania

To polecenie spowoduje usunięcie indeksu z tabeli. Działa na serwerze SAP ASE .

Składnia:

DROP INDEX [table name].[index name]

Przykład:

DROP INDEX Cars.index_1

Posortowany indeks

Jeśli użyjesz indeksu posortowanego w taki sposób, w jaki byś go odzyskał, instrukcja SELECT nie wykona dodatkowego sortowania podczas pobierania.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

Po wykonaniu zapytania

SELECT * FROM scoreboard ORDER BY score DESC;

System bazy danych nie przeprowadzałby dodatkowego sortowania, ponieważ może wyszukiwać indeks w tej kolejności.

Upuszczanie indeksu lub jego wyłączanie i przebudowywanie

DROP INDEX ix_cars_employee_id ON Cars;  

Możemy użyć polecenia DROP aby usunąć nasz indeks. W tym przykładzie będziemy DROP indeks o nazwie ix_cars_employee_id na stole Cars.

Spowoduje to całkowite usunięcie indeksu, a jeśli indeks jest klastrowany, usunie wszelkie klastry. Nie można go odbudować bez odtworzenia indeksu, który może być powolny i kosztowny obliczeniowo. Alternatywnie indeks można wyłączyć:

ALTER INDEX ix_cars_employee_id ON Cars DISABLE; 

Dzięki temu tabela może zachować strukturę wraz z metadanymi dotyczącymi indeksu.

Krytycznie zachowuje to statystyki indeksu, dzięki czemu można łatwo ocenić zmianę. Jeśli jest to uzasadnione, indeks można później odbudować zamiast całkowicie odtworzyć;

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

Unikalny indeks, który pozwala na wartości NULLS

CREATE UNIQUE INDEX idx_license_id 
   ON Person(DrivingLicenseID) WHERE DrivingLicenseID IS NOT NULL
GO

Ten schemat pozwala na relację 0..1 - ludzie mogą mieć zero lub jedno prawo jazdy, a każde prawo jazdy może należeć tylko do jednej osoby

Przebuduj indeks

Z biegiem czasu indeksy B-Tree mogą ulec fragmentacji z powodu aktualizacji / usuwania / wstawiania danych. W terminologii SQLServer możemy mieć wewnętrzną (strona indeksu, która jest w połowie pusta) i zewnętrzną (logiczna kolejność stron nie odpowiada fizycznej kolejności). Indeks odbudowy jest bardzo podobny do upuszczania i ponownego tworzenia.

Możemy ponownie zbudować indeks za pomocą

ALTER INDEX index_name REBUILD; 

Domyślnie indeks odbudowy jest operacją offline, która blokuje tabelę i zapobiega przed nią DML, ale wiele RDBMS pozwala na przebudowę online. Ponadto niektórzy dostawcy DB oferują alternatywy dla przebudowy indeksu, takie jak REORGANIZE (SQLServer) lub COALESCE / SHRINK SPACE (Oracle).

Indeks klastrowy

Podczas korzystania z indeksu klastrowego wiersze tabeli są sortowane według kolumny, do której stosuje się indeks klastrowany. Dlatego w tabeli może znajdować się tylko jeden indeks klastrowany, ponieważ nie można uporządkować tabeli według dwóch różnych kolumn.

Zasadniczo najlepiej jest używać indeksu klastrowego podczas wykonywania odczytów na tabelach dużych zbiorów danych. Donwside indeksu klastrowego ma miejsce podczas zapisywania do tabeli, a dane muszą zostać zreorganizowane (zastosowane).

Przykład tworzenia indeksu klastrowego w tabeli Pracownicy w kolumnie Nazwisko pracownika:

CREATE CLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

Indeks nieklastrowany

Indeksy nieklastrowane są przechowywane oddzielnie od tabeli. Każdy indeks w tej strukturze zawiera wskaźnik do wiersza w tabeli, którą reprezentuje.

Wskaźniki te nazywane są lokalizatorami wierszy. Struktura lokalizatora wierszy zależy od tego, czy strony danych są przechowywane w stercie czy w tabeli klastrowej. W przypadku sterty lokalizator wierszy jest wskaźnikiem wiersza. W przypadku tabeli klastrowej lokalizatorem wiersza jest klastrowany klucz indeksu.

Przykład tworzenia indeksu nieklastrowego w tabeli Pracownicy i kolumnie Nazwisko pracownika:

CREATE NONCLUSTERED INDEX ix_employees_name ON Employees(Employee_Surname);  

W tabeli może znajdować się wiele indeksów nieklastrowanych. Operacje odczytu są zwykle wolniejsze w przypadku indeksów nieklastrowanych niż w przypadku indeksów klastrowych, ponieważ najpierw trzeba przejść do indeksowania, a następnie do tabeli. Jednak nie ma żadnych ograniczeń w operacjach zapisu.

Indeks częściowy lub filtrowany

SQL Server i SQLite pozwalają tworzyć indeksy, które zawierają nie tylko podzbiór kolumn, ale także podzbiór wierszy.

Rozważmy stałą rosnącą liczbę zamówień o order_state_id równym zakończeniu (2) i stabilną liczbę zamówień o order_state_id equal początkowej (1).

Jeśli Twoja firma korzysta z takich zapytań:

SELECT id, comment
  FROM orders
 WHERE order_state_id =  1
   AND product_id = @some_value;

Częściowe indeksowanie pozwala ograniczyć indeks, w tym tylko niedokończone zamówienia:

CREATE INDEX Started_Orders
          ON orders(product_id)
       WHERE order_state_id = 1;

Indeks będzie mniejszy niż indeks niefiltrowany, co oszczędza miejsce i zmniejsza koszty aktualizacji indeksu.