Clustered Indexes — PostgreSQL/MySQL

Do napisania tego artykułu skłoniła mnie ciekawa historia, otóż w trakcie jednej z rozmów o projektowaniu baz danych, padło pytanie o clustered indexes. Musiałem szczerze przyznać, że chyba ten temat pominąłem w tracie swojej wieloletniej pracy z bazdami danych. Postanowiłem więc podzielić się z wami czego nowego się nauczyłem.

https://pixabay.com/illustrations/elephant-forest-nature-animal-4461911/

Czym jest clustered Index ?

Wyobraźmy sobie sytuację, że mamy prostą tabele składającą się z trzech kolumn ID, produkt, date.

postgres=# create table test (id int, produkt text, date date);
CREATE TABLE
postgres=# \d+ test
                                           Table "public.test"
 Column  |  Type   | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
---------+---------+-----------+----------+---------+----------+-------------+--------------+-------------
 id      | integer |           |          |         | plain    |             |              | 
 produkt | text    |           |          |         | extended |             |              | 
 date    | date    |           |          |         | plain    |             |              | 
Access method: heap

Naturalne dla większości z nas będzie założenie klucza głównego na kolumnie ID. Tak jakoś działają nasze mózgi, że jest to logiczne rozwiązanie. Przyjmijmy założenie, że ID może się zmieniać w czasie, może być usuwane lub zmieniane. Przykład:

1 | Zeszyt | 2024-02-12 11:00:00
2 | Dlugopis | 2024-03-01 08:00:00
3 | Gumka   | 2024-04-15 23:00:00

Teraz przechodzimy do sedna.

Jeśli wiemy że ID może się zmieniać to index założony na tej kolumnie będzie indexem non-clustered. Ponieważ kolejność ID nie będzie zgodna z ich faktycznym zapisem na dysku. Zgodnie z naszym wcześniejszym założeniem po jakimś czasie tabela może wyglądać tak:

5 | Zeszyt | 2024-02-12 11:00:00
2 | Dlugopis | 2024-03-01 08:00:00
3 | Gumka   | 2024-04-15 23:00:00

Jeżeli teraz użyjemy zapytania:

SELECT * FROM test ORDER BY id;

To w teorii będziemy musieli przedstawić wyniki i uszeregować je w innej kolejności niż zostały zapisane na dysku. Przypominam, że Zeszyt wcześniej miał ID 1 teraz ma 5.

W takim razie co będzie clustered indexem ?

W naszym przypadku takim indexem będzie index na kolumnie Date, ponieważ nie planujemy zmieniać daty . Czyli zawsze, wywołując SELECT wyniki zostaną przeczytane w tej samej kolejności w której są zapisane na dysku ( nawet bez ORDER BY ). Dzięki temu uzyskamy lepsze performance i mniej czasu stracimy na poprawne szeregowanie wyniku.

Podsumowując głównym kryterium clustered indexu jest fakt, iż rekordy są zapisane i odczytywane w dokładnie w takiej samej kolejności. Wiec nie planujemy zmian w tej kolumnie.

Clustered Indexes najczęściej są oparte o kolumny date lub time, czasem o ID jeśli oczywiście nie planujem zmian w ID.

Non-clusted Indexes świetnie nadają się na indexy z wieloma kolumnami lub takimi, które się często zmieniają.

Na konieć warto dodać, że nie ma w PostgreSQL/MySQL żadnej flagi, która rozróżnia te dwa typy indexów. Jest to tylko teoretyczny podział.

Ot taka ciekawostka :)

Tomasz Gintowt jest Architektem/DevOps/DBA/Trenerem, głównie skupiony na dostarczaniu rozwiązań składowania i przetwarzania danych. Nie są mu obce wszelkiej maści bazy danych, systemy real-time data i streamingu. Obecnie pracuje z Apache Kafka, RabbitMQ, Elastic Stack i PostgreSQL. Organizator spotkań DataOps Poland.

https://www.linkedin.com/in/tomasz-gintowt/

https://dataops-academy.pl — kursy i szkolenia.