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:
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?