Dlaczego indeksy klastrowe są takie ważne?

W SQL Server wyróżniamy dwa podstawowe typy indeksów: klastrowy (clustered) i nieklastrowy (nonclustered), nazywanymi również odpowiednio grupującym i niegrupującym. Dlaczego używanie pierwszego z nich jest takie ważne?

Odpowiedź jest bardzo prosta. W przypadku gdy nie posiadamy indeksu klastrowego dane w tabeli zapisywane są kolejno na stronach danych (HEAP). Konsekwencją tego jest to, że wyświetlając interesujące nas dane musimy przeskanować całą tabelę.

Tworząc indeks klastrowy pozbywamy się tego problemu. W tym przypadku zamiast skanować całą tabelę – przechodzimy przez strukturę b-drzewa dzięki czemu w pamięci ląduje mniej stron danych, a same dane są układane zgodnie z tym co jest w indeksie.

Zalecenia do tworzenia indeksu tego typu są bardzo proste. Wybieramy jak najszybszy typ danych, najlepiej jedną kolumnę i na jej podstawie organizujemy ten indeks. Kryterium te spełnia idealnie klucz podstawowy (indeks ten jest domyślnie zakładany wraz z kluczem podstawowym).  Należy pamiętać, że mimo to, że indeks ten układa wg danej kolumny dane na stronach danych, to indeks ten należy regularnie konserwować.

Poniższe doświadczenie w świetny sposób ilustruje jak dzięki indeksowi klastrowemu  założonemu w naszym przypadku domyślnie z kluczem podstawowym pewne operacje (w naszym przypadku UPDATE) przebiegają szybciej.

Bez indeksu klastrowego:

USE tempdb;
GO

--Create a table as a heap

CREATE TABLE dbo.TestDB
( ID int IDENTITY(1,1) NOT NULL,
  TestColumn1 datetime2 NOT NULL,
  TestColumn2 nvarchar(100) NOT NULL,
  TestColumn3 int NOT NULL
);
GO

-- Query sys.indexes to view the structure

SELECT * FROM sys.indexes WHERE OBJECT_NAME(object_id) = N'TestDB';
GO

-- Insert some data into the table

SET NOCOUNT ON;

DECLARE @Counter int = 0;

WHILE @Counter < 10000 BEGIN
  INSERT dbo.TestDB (TestColumn1, TestColumn2, TestColumn3)
    VALUES(SYSDATETIME(),'999-9999',CAST(RAND() * 1000 AS int));
  SET @Counter += 1;
END;
GO

-- Modify the data in the table 

SET NOCOUNT ON;

DECLARE @Counter int = 0;

WHILE @Counter < 10000 BEGIN
  UPDATE dbo.TestDB SET TestColumn2 = REPLICATE('9',CAST(RAND() * 100 AS int))
    WHERE ID = @Counter;
  IF @Counter % 100 = 0 PRINT @Counter;
  SET @Counter += 1;
END;
GO

--CLEANING

DROP TABLE dbo.TestDB;
GO

Z indeksem klastrowym:

USE tempdb;
GO

--Create a table with a primary key 

CREATE TABLE dbo.TestDB
( ID int IDENTITY(1,1) PRIMARY KEY,
  TestColumn1 datetime2 NOT NULL,
  TestColumn2 nvarchar(100) NOT NULL,
  TestColumn3 int NOT NULL
);
GO

-- Query sys.indexes to view the structure

SELECT * FROM sys.indexes WHERE OBJECT_NAME(object_id) = N'TestDB';
GO
SELECT * FROM sys.key_constraints WHERE OBJECT_NAME(parent_object_id) = N'TestDB';
GO

-- Insert some data into the table

SET NOCOUNT ON;

DECLARE @Counter int = 0;

WHILE @Counter < 10000 BEGIN
  INSERT dbo.TestDB (TestColumn1, TestColumn2, TestColumn3)
    VALUES(SYSDATETIME(),'999-9999',CAST(RAND() * 1000 AS int));
  SET @Counter += 1;
END;
GO

-- Modify the data in the table 

SET NOCOUNT ON;

DECLARE @Counter int = 0;

WHILE @Counter < 10000 BEGIN
  UPDATE dbo.TestDB SET TestColumn2 = REPLICATE('9',CAST(RAND() * 100 AS int))
    WHERE ID = @Counter % 10000;
  IF @Counter % 100 = 0 PRINT @Counter;
  SET @Counter += 1;
END;
GO

--Cleaning 

DROP TABLE dbo.TestDB;
GO

Jak widać założenie klucza podstawowego (a za nim indeksu klastrowego) poprawiło kilkunastokrotnie czas wykonania zapytania.