The Data Game

Indeks klastrowy, po co?

Wyobraźmy sobie biurko. Moje biurko. Zero porządku. Wszystkie rzeczy walają się we wszystkich miejscach. “Artystyczny nieład” jak to kiedyś ktoś ładnie określił. Żeby coś znaleźć trzeba się trochę naszukać, może nagrzebać pod stertami papieru. No koszmar.

Albo, dla starszych wiekiem czytelników, wyobraźmy sobie książkę telefoniczną. Którą dopadł syn córki brata siostry szwagra sąsiada, lat 3. I stwierdził, że takiego tomiszcza to on nie udźwignie, ale jakby powyrywał kartki i każdą nosił osobno to już da radę. Więc tak, tona stron porozrzucana losowo. I teraz, dalej poprzez magiczną sztukę wyobraźni, mamy 10 lat, a jednym z najlepszych dowcipów na świecie jest dzwonienie do ludzi z pytaniem “Czy pana lodówka chodzi?”, a najlepiej jakby jeszcze ta osoba miała jakieś fajne nazwisko, np. “Pupalski” (w tym miejscu przepraszam wszystkich Pupalskich).

Co musimy zrobić żeby znaleźć wszystkich Pupalskich? Wziąć te powyrywane strony i przejrzeć je, jedna po drugiej, bo przecież nie są w żadnej konkretnej kolejności, szukając kolejnych osób, które miały pecha i zostały obrane za cel naszych wspaniałych dowcipów. Przewalone…

A właśnie tak wygląda tabela w SQL Serverze bez indeksu klastrowanego (CLUSTERED INDEX). Wszystkie dane w bazie są zapisywane na stronach o wielkości 8 KB. Tak, KB, nie MB czy nawet GB. 8 KB, tyle ma jedna strona w SQL Serverze, witamy w 2020. I te strony na stercie (tak się nazywa tabelę bez indeksu klastrowego, heap table, nazwa idealnie pasująca do opisu naszej książki telefonicznej po spotkaniu z Kacperkiem) mogą być porozrzucane losowo po całym dysku. Tzn. baza stara się je umieszczać w miarę sekwencyjnie, ale a to tutaj nie ma miejsca, a to inny powód i bam, lista osób na literę A jest tutaj, a osoby na literę B już 300 GB dalej. Przynajmniej dysk nam się nie rozleniwi (SSD mniej cierpią z tego powodu ale chodzi o zobrazowanie). A wyszukanie pana Jana Pupalskiego? Olaboga, toż to baza musi złapać pierwszą stronę z danymi i lecieć aż do końca, bo pewność, że więcej Pupalskich nie ma, będzie dopiero wtedy jak przejrzymy wszystkie strony.

I wtedy na ratunek przybywa indeks klastrowy! Dzięki niemu SQL Server wie dokładnie w jakiej kolejności ma zapisywać dane w tabeli i będzie się tej kolejności trzymał aż do korupcji danych! Taki indeks po prostu określa nam sortowanie danych NA DYSKU w tabeli. Prawdą jest więc, że indeks klastrowy tak naprawdę JEST tabelą (i w związku z tym też praktycznie nie zajmuje miejsca na dysku). Więc założenie go w naszej telefonicznej metaforze można przyrównać do posadzenia niegrzecznego Kacperka, lat 3, przed powyrywanymi kartkami i nie włączenia mu bajek dopóki znowu ich nie poukłada alfabetycznie.

Unikatowość?

I tutaj pojawia się problem. Bo sortowanie musi być jednoznaczne. A takich Janów Pupalskich jest 15 w naszej książce! I czy teraz pierwszy ma być Jan z Zamościa? A może Jan z Miejskiej Górki? Albo Jan z Warszawy. Pamiętajmy, że sortujemy tylko po imieniu i nazwisku (a bardziej po nazwisku i imieniu, inaczej to by było okropnie kiepskie sortowanie). Jak więc posortować tych biednych Janów? Czy to nie oznacza, że na książce telefonicznej nie jesteśmy w stanie założyć indeksu klastrowanego?

Jesteśmy.

Częstym błędem popełnianym przez kandydatów na stanowisko SQL Developera jest stwierdzenie że indeks klastrowy musi być unikatowy. Pewnie dlatego, że klucz główny (primary key) musi być unikatowy, a domyślnie wraz z nim SQL Server tworzy także indeks klastrowy na tych kolumnach (chociaż wcale nie musi tworzyć takiego indeksu). Jest w tym jednak ziarno prawdy. Indeks klastrowy musi być unikatowy, ale dla bazy, nie dla użytkownika. Osoba używająca bazy bez problemu może wcisnąć w taki indeks nawet stu Janów Pupalskich i dla niej w tabeli będzie tych 100 Janów, ale za kulisami, do każdej zdublowanej wartości, SQL Server doda wewnętrzny “unifikator” (uniqueifier) będący 4-bajtową wartością. A czym jest ta wartość? Możemy to łatwo sprawdzić, chociaż nie za szybko 😉

SET NOCOUNT ON;
DROP TABLE IF EXISTS [dbo].[testMaxValue], #temp15mlnrows;
CREATE TABLE [dbo].[testMaxValue] ([Id] TINYINT NOT NULL,
				   INDEX [IX_sorrow] CLUSTERED ([Id]));

;WITH CTE AS (
SELECT
	CAST(1 as TINYINT) as [a]
FROM
	master..spt_values a
CROSS JOIN
	master..spt_values b
UNION ALL
SELECT
	CAST(1 as TINYINT) as [a]
FROM
	master..spt_values a
CROSS JOIN
	master..spt_values b
UNION ALL
SELECT
	CAST(1 as TINYINT) as [a]
FROM
	master..spt_values a
CROSS JOIN
	master..spt_values b
)
SELECT TOP(15000000)
	[a] as [Id]
INTO #temp15mlnrows
FROM
	[CTE];

DECLARE @i INT = 1,
		@message VARCHAR(32);

WHILE @i <= 143
BEGIN
	BEGIN TRAN
		INSERT INTO [dbo].[testMaxValue]([Id])
		SELECT * FROM #temp15mlnrows;
	COMMIT

	SET @message = CONCAT('Loop no: ',@i);
	RAISERROR(@message,0,1) WITH NOWAIT;

	SET @i += 1
END

No, trochę to zajęło… To teraz ostatnie zadanie. Puśćmy to ostatni raz.

INSERT INTO [dbo].[testMaxValue]([Id])
SELECT * FROM #temp15mlnrows;

I po błędzie (swoją drogą, ciekawy numer błędu) już chyba wiemy co to za typ danej, którą dodaje SQL Server 😉

Oki, to pa!