Posts tagged optymalizacja

Tytułem wstępu

Jedną z najlepszych praktyk w relacyjnych bazach danych jest utworzenie indeksów na kolumnach, które wchodzą w skład kluczy obcych. Kolumny takie są często wykorzystywane do łączenia tabel w zapytaniach. Oczywiście nie należy bezwarunkowo zakładać indeksu na każdej kombinacji kolumn, tworzącej klucz obcy, ale przynajmniej należy taką możliwość rozpatrzyć (decyzja zależy również od rozkładu danych).

SQL Server nie tworzy takich indeksów samodzielnie (niektóre systemy potrafią zakładać indeksy automatycznie przy tworzeniu kluczy obcych). Celem niniejszego artykułu jest pokazanie na przykładach, dlaczego indeksowanie kolumn kluczy obcych może być dobrym pomysłem.

Tworzymy poligon

Posłużę się klasycznym przykładem dwóch tabel: tabeli produktów i tabeli kategorii produktów. Tabele są powiązane związkiem binarnym jeden do wielu (jedna kategoria może mieć wiele produktów). W tabeli dbo.Product (zbiór produktów) pojawia się kolumna CategoryID, wskazująca na odpowiednią kategorię w tabeli dbo.Category. Kod do wygenerowania bazy danych i jej zawartości:

CREATE DATABASE Test;
GO
USE Test;
GO
CREATE TABLE dbo.Category (
  CategoryID int NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
  CategoryName varchar(50) NOT NULL UNIQUE CLUSTERED
);
GO
CREATE TABLE dbo.Product (
  ProductID int NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
  ProductName varchar(50) NOT NULL UNIQUE CLUSTERED,
  CategoryID int NULL
);
GO
ALTER TABLE dbo.Product
ADD CONSTRAINT FK_Product_Category
FOREIGN KEY (CategoryID)
REFERENCES dbo.Category (CategoryID);
GO
-- Generujemy 1000 rekordów kategorii produktów
INSERT INTO dbo.Category (CategoryName)
SELECT 'Category ' + CONVERT(varchar(4), number)
FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 1 AND 1000;
GO
-- Generujemy około 248 tys. rekordów produktów
INSERT INTO dbo.Product (ProductName, CategoryID)
SELECT
  'Product ' + CONVERT(varchar(7), Q.n),
  CASE WHEN Q.n % 1000 = 0 THEN 1 ELSE Q.n % 1000 END
FROM (
  SELECT DISTINCT v1.number * v2.number AS n
  FROM master.dbo.spt_values AS v1
  CROSS JOIN master.dbo.spt_values AS v2
  WHERE v1.number BETWEEN 1 AND 1000 AND v1.type = 'P'
  AND v2.number BETWEEN 1 AND 1000 AND v2.type = 'P'
) AS Q
ORDER BY Q.n;
GO

Przypadek 1. – SELECT

Rozpatrzmy następujące zapytanie SELECT:

SELECT c.CategoryName, p.ProductName
FROM dbo.Category AS c
LEFT JOIN dbo.Product AS p
ON c.CategoryID = p.CategoryID
WHERE c.CategoryName = 'Category 1000';

Ma ono za zadanie wybranie wszystkich produktów należących do kategorii o nazwie „Category 1000” (a jeżeli do tej kategorii nie należy żaden produkt, powinien pojawić się jeden rekord z pustą nazwą produktu). Takich produktów w tabeli dbo.Product nie ma. Ale mimo to zapytanie wykonywane jest w zauważalnie długim czasie, ponieważ brak indeksu na kolumnie CategoryID powoduje, że musi nastąpić przeskanowanie (odczyt wszystkich rekordów) tabeli dbo.Product. Plan zapytania wygląda następująco:

Po założeniu, za pomocą poniższego kodu, indeksu na kolumnie CategoryID, w planie nie widzimy już skanowania tabeli produktów wykonywanego w pętli, a samo zapytanie wykonuje się błyskawicznie.

CREATE INDEX IX_Product_CategoryID
ON dbo.Product (CategoryID);

Przypadek 2. – UPDATE

Ale indeks może posłużyć nie tylko do optymalizacji zapytań SELECT. Dzięki niemu można także spowodować, że operacje modyfikacji danych będą wykonywać się szybciej. Weźmy takie polecenie UPDATE:

UPDATE p
SET p.CategoryID = 1
FROM dbo.Category AS c
INNER JOIN dbo.Product AS p
ON c.CategoryID = p.CategoryID
WHERE c.CategoryName = 'Category 1000';

Celem jest przypisanie produktów, dotąd należących do „Category 1000”, do nowej kategorii o identyfikatorze 1. Jeśli na kolumnie CategoryID w tabeli dbo.Product brak indeksu, przy wykynywaniu powyższego polecenia UPDATE praktycznie cały koszt jest generowany przez skanowanie tabeli dbo.Product oraz operatora złączenia typu Nested Loops.

Ale jeżeli dodany zostanie indeks (taki sam, jak w przypadku 1.), skanowanie zastępowane jest przez operację jego przeszukania. Sama modyfikacja danych będzie wykonywana szybciej, na skutek szybszego znalezienia rekordów do zmiany. Odpowiedni fragment planu zapytania po założeniu indeksu:

Przypadek 3. – DELETE

Także operacje kasowania danych mogą być optymalizowane przez założenie indeksu na kolumnie, będącej kluczem obcym. Przykładowe polecenie DELETE:

DELETE FROM dbo.Category
WHERE CategoryName = 'Category 1000';

W planie wykonania, w przypadku braku indeksu na kolumnie CategoryID w tabeli dbo.Product, pojawia się skanowanie całej tabeli w poszukiwaniu rekordów produktów, należących do „Category 1000” (nawet, jeśli takich produktów nie ma, przejrzana zostaje cała tabela). Jest to spowodowane tym, że w tabeli dbo.Product istnieje klucz obcy, odwołujący się do tabeli dbo.Category i uniemożliwiający usuwanie rekordu kategorii, jeżeli ma ona przypisany choć jeden produkt.

Jakie korzyści daje założenie indeksu na kolumnie (-ach) klucza obcego w tabeli dbo.Product (takiego samego indeksu, jak w przypadkach 1. i 2.)?

Tym razem operacja wykonuje się szybciej, ponieważ zamiast skanowania tabeli dbo.Product następuje przeszukanie indeksu na kolumnie CategoryID w tej tabeli.

Wnioski daleko idące

Nie zawsze indeksy na kolumnach, będących kluczami obcymi, pozwalają na optymalizację zapytań. Ale na pewno kolumny takie powinny być uwzględniane jako propozycje kluczy indeksów. Przykłady z tego artykułu pokazują, że takim indeksem można także przyspieszyć modyfikacje danych (choć spotyka się ogólne i niezbyt trafne stwierdzenia, że indeksy zawsze powodują spowolnienie takich operacji).