Pomoc w zrozumieniu clustered index

0

Podczas czytania książki SQL Performance Explained natknąłem się na pojęcie clustered index. Oczywiście od razu zacząłem przekopywac internet, żeby dowiedzieć się co to za twór.

Udało mi się ustalić, że jest podział na clustered-index i non-clustered index. W systemach zarządzania bazami danych takich jak MySQL i MS SQL klucz głowny defaultowo jest tworzony jako clustered index, dzięki czemu zachodzi takie cos:

CREATE TABLE `tblTest` (
  `id` int(11) NOT NULL,
  `value` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

INSERT INTO tblTest VALUES (2, 'test_2');
INSERT INTO tblTest VALUES (1, 'test_1');

SELECT * FROM tblTest;
1, test_1
2, test_2

Czyli dane są fizycznie trzymane w kolejności i do tego momentu wszystko jest jasne.

[Pytanie 1]
Jeżeli będę miał tabelę bez cluster index i jakiegokolwiek innego indexu, to jakie będą tego implikacje?

[Pytanie 1.1]
Z tego co wiem dane będą trzymane w czymś, co się nazywa heap i będzie zachowany (lub nie) insertion order (czyli powyższy przykład wypluje 2, 1 zamiast 1, 2)?

[Pytanie 1.2]
Jeżeli zrobie zapytanie SELECT * FROM tabela to będzie ono wolniejsze przez to, że brakuje jakiegokolwiek indeksu? W sumie pobieram wszystko i nie używam indeksu w zapytaniu, wiec nie ma chyba znaczenia, czy bedzie porządek czy nie.

Wracając do tematu... dalej czytam tak:

źródło

Consider using a clustered index for queries that do the following:
Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

W tym momencie dwa use-case'y przyszły do głowy:

  • mam tabelę 100 mln rekordów, z której wszystko jest wyciągane zawsze na zasadzie date BETWEEN ... AND ....
  • mam tabelę 100 mln rekordów, z której wszystko jest wyciągane zawsze na zasadzie WHERE user_id = ...

Zamiast mieć klucz głowny jako clustered index, którego nigdy nie używam w tych tabelach, to może dobrym pomysłem jest założenie go na innych kolumnach, typu date lub user_id.

Do clustered indexes have to be unique?

They don't have to be unique but it certainly is encouraged.
I haven't encountered a scenario yet where I wanted to create a CI on a non-unique column.

Moja kolumna date, a tym bardziej user_id, który jest kluczem obcym, na pewno nie zawiera unikalnych wartości... więc lipa. Trochę się to kłóci z tym co przeczytałem na stronie microsoftu, bo zazwyczaj nie robie pk BETWEEN ... AND ....

Tutaj (w 5:20) koleś znowu zakłada index na dwie, nieunikalne kolumny, więc mam już totalny mindfuck.

[PYTANIE 2]
Jak to w końcu jest? Czy use-case'y, które przytoczyłem wyżej mają sens?

[PYTANIE 3]
Jaka jest różnica w wydajności clustered index vs non-clustered index?

Z tego co wiem clustered index to B+ tree, na którego końcu są dane we własnej osobie, a nie rowid, ale poza tym ich działanie i budowa są takie same. W przypadku zwykłego indexu RDBMS musi wykonać jeden dodatkowy krok, czyli odszukanie danych po rowid, ale z mojego punktu widzenia nie brzmi to jak duży overhead.

[PYTANIE 3.1]
Jak duża różnica w wydajności będzie przy założeniu clustered index vs non-clustered index na kolumny z use-case'ów, które podałem wyżej i czy to w ogóle ma sens?

@Marcin.Miga

0

Ogólnie domyslnie PK jest jako clusterd, ale nic nie stoi na przeszkodzie, aby nie był:

alter table tabela add constraint PK_ID primary key nonclustered (id);

Chyba źle sobie przetłumaczyłeś przykład, index clustered/nonclusterd nie musi być unikalny spokojnie możesz go założyć na kolumnach z bez unikatów i z nullami.
dodatkowo o tym jak są fizycznie trzymane dane dane decyduje OS nie RDBMS... To założenie jest mylne

Ja gdzieś czytałem, że rzadko kiedy klucz powinien być clustered ;)

co do twoich use case, to odpowiedź brzmi zależy ;)

Generalnie wyszukiwanie po indeksie clustered jest z reguły szybsze, ponieważ poziomem klastrowym indeksu clusterd jest tabela. Indeks "zwykły" to w uproszczeniu jakaś kopia danych.

P.S. Moja wypowiedź odnosi się do MS SQL

4

Odpowiedź 1:
Dostęp do tabeli z indeksem klastrowanym wg pól z tego indeksu będzie szybszy, z drugiej strony koszt modyfikacji danych będzie większy, ponieważ baza zawsze będzie porządkowała dane w tabeli wg tego indeksu. W MSSQL jeżeli nie założysz jawnie klastrowanego indeksu, to zostanie on niejawnie założony na PK.
Odpowiedź 1.1:
Nie, dane będą uporządkowane wg klucza klastrowanego, którym jest w tym przypadku id.
Odpowiedź 1.2:
Wolniejsze niż co? Nie można mówić, że coś jest wolniejsze, jeżeli z niczym tego nie porównujesz. SELECT * FROM tabela bez warunków zwróci dane uporządkowane wg klucza głównego, więc zawsze wykorzystujesz jakiś indeks.
Odpowiedź 2:
Indeks klastrowany może być bez problemu założony na pola nieunikalne, to żaden problem. W takim wypadku MSSQL doklei sobie dodatkową kolumnę z losowym unikalnym numerkiem, tak aby zapewnić unikalność. Ty jako użytkownik nawet tego nie zauważysz.
Odpowiedź 3 i 3.1:
Indeks klastrowany wymusza określony porządek w tabeli, dzięki czemu dostęp fizyczny do danych wg pól tego indeksu zawsze będzie szybszy. Z tego powodu w miarę możliwości powinno się go zakładać na polach na których często będą śmigać zapytania.

1 użytkowników online, w tym zalogowanych: 0, gości: 1