jakie klucze stosować, jakie wybrać

Josh Berkus w cyklu „Primary KeyvilI, IIIII rozważa kwestie związane z używaniem kluczy głównych w tabelach. Zastanawia się nad kluczami będącymi w jego odczuciu surogatami prawdziwych kluczy głównych. Za taki surogat uważa klucze oparte na automatycznie inkrementowanych polach w bazie (autoincrement dla mysql’a oraz serial dla postgres’a) i przyznam, że podane argumenty są bardzo mocno i ciężko mi z nimi polemizować.

Generalnie autor zachęca do stosowania kluczy głównych które są rzeczywistym wyróżnikiem. Rozważmy sytuację w której mamy trzy proste tabele: users, groups, user2group – dwie pierwsze zawierają dane dotyczące odpowiednio: użytkowników oraz grup, ostatnia zawiera informację do których grup należy użytkownik.

Jeżeli zastosujemy jako klucze główne pola „ID” to w efekcie tabela user2group przyjmie zawartość:

+---------+----------+
| user_id | group_id |
+---------+----------+
|       1 |        1 |
|       2 |        2 |
|       2 |        6 |
+---------+----------+

Co jak widać niewiele mówi.

W przypadku w którym za klucze przyjmiemy odpowiednio email użytkownika oraz nazwę grupy to taka tabela będzie miała następującą zawartość:

+----------+----------+
| email    | group    |
+----------+----------+
| illi@x   | root     |
| gurthg@x | adm      |
| gurthg@x | www-data |
+----------+----------+

Trudno się nie zgodzić, że pomyłka w insercie typu: INSERT INTO tabela VALUES ( 1, 2 );
jest trudniejsza do wychwycenia niż w: INSERT INTO tabela VALUES ( 'illi@x', 'adm' );

Czytanie danych, szczególnie po latach albo obcych jest o niebo łatwiejsze wtedy kiedy naszymi kluczami są dane które coś znaczą.

Następnym drobnym zyskiem jest fakt zmniejszenia ilości zapytań lub joinów w zapytaniach.

Zauważymy, że w ostatnim przypadku żeby wylistować adresy email użytkowników należących do danej grupy, wtedy kiedy znamy tylko jej nazwę, wymusza w pierwszym przypadku użycie dwóch joinów. W drugim przypadku jest to prosty select. Oczywiście sytuacje tak proste raczej nie występują w życiu, ale są dobrym przykładem na zobrazowanie korzyści.

Oczywiście są sytuacje w których będę nadal używał kluczy opartych o inkrementowaną liczbę naturalną, ale tylko tam gdzie uznam to za naprawdę konieczne.

Natomiast w kwestiach prostych zostałem całkowicie przekonany do używania jako klucze główne takich danych, które niosą ze sobą rzeczywistą różnicę między rekordami.

5 odpowiedzi do “jakie klucze stosować, jakie wybrać”

  1. Teoretycznie tak, ale praktycznie czasami się przydaje to, że zawsze masz kolumnę ID. Zapytania są prostsze, nie musisz porównywać 5 kolumn przy joinie itd. Oczywiście baza jest wtedy zdenormalizowana, ale trudno i tak prędzej czy później taka będzie. Zawsze możesz zrobić taką tabelkę tak:
    primary key (id)
    unique( email, group)

    No i oczywiście są jeszcze takie przypadki jak chociażby tabela zawierająca listę mejli do wysłania. Jakoś nie widzę w takiej tabeli żadnej kolumny, która mogłaby być naturalnym kluczem głównym, bo w końcu dwa takie same mejle do tej samej osoby mogą być całkiem OK z punktu widzenia założeń biznesowych. Dodanie całkiem sztucznej kolumny ID jako klucza głównego pozwoli ładnie zidentyfikować konkretnego mejla, a zarazem jest całkiem sztuczne.
    Ale i tak do swoich baz zawsze ładuję id jako klucz główny, a potem unique() dla klucza naturalnego. Joiny są nieco wygodniejsze wtedy i nie trzeba zawsze lecieć do definicji tabeli żeby sprawdzić co akurat jest kluczem głównym.

  2. Oczywiście, że są sytuacje w których ID jest „naturalnym” sposobem, co nie oznacza, że rzeczywisty klucz główny jest złym rozwiązaniem. Jak we wszystkim, dla każdej sytuacji sprawę należy rozpatrywać indywidualnie.

  3. Inty 4 bajotwe maja wplyw przede wszystkim na wydajnosc. Stosowanie innych kluczy glownych (dluzszych niz 4 bajty) znaczaco wplywa na czas wiekszosci operacji np joinowania.
    Wyobrazmy sobie tabele, ktora zawiera 100mln rekordow i klucz id oraz taka sama tylko, ze z kluczem character varying(4). W postgesie varchar dla 4 znakow to 4 bajty przechowujace informacje o dlugosci + tyle bajtow ile jest znakow. Zatem wypelnienie tego pola 4 znakami zajmuje 8 bajtow (przykladowo w MSSQL varchar to 2bytes+string).
    Index na polu id int zajmuje 4x100m bajtow ~ 380MB, na polu varchar 8x100m ~ 762MB.
    Sytuacja idealna to taka kiedy caly index miesci sie w pamieci RAM. Jak widac dla 4 znakow jest juz potrzebne 762MB, sytuacja pogarsza sie jesli zamierzamy uzyc dluzszego ciagu znakow lub kilku kolumn w jednym indeksie. Wtedy praktycznym rozwiazaniem jest stosowanie 4 bajotwych intow lub 8 bigintow (w zaleznosci od potrzeb). Joinowanie na takich kolumnach i przy indeksach zalozonych na PK I FK jest o wiele szybsze, dlatego zaleca sie stosowanie indeksow sztucznych (autoincrement, serial, identity – to z mssql) jako klucze glowne, a biznesowe oznaczac jako indeks unikalny nieklastrowany (potocznie: zwykly ;) )
    Problem ten moze nie dotyczyc malych baz. Natomiast jesli zalezy nam na skalowalnosci i oszczednosci miejsca (na dysku i w ram) to lepiej przebolec kwestie poslugiwania sie numerkami niz pozniej borykac sie z problemami wydajnosciowymi przy laczeniu tabel czy np. replikacji takiej jak slony czy transactional mssql.

  4. Aktualnie ram jest tani. Jeżeli masz bazę o takich wymiarach, to więcej kosztuje tydzień pracy zespołu, który to utrzymuje niż jakakolwiek możliwa dokładka ramu. Z jednego serwisu porównującego: 2x8GB (ECC) kosztuje brutto marne 4000 pln. To naprawdę nic z punktu widzenia takiego projektu.

  5. Zgadzam się z autorem. Myślę, że najlepszy jest zdrowy rozsądek, czasem tabelka aż prosi się, żeby PRIMARY KEY nie był intem, i wtedy należy temu ulec :)

Możliwość komentowania jest wyłączona.

Jeżeli chcesz skomentować, napisz mail na adres marcin w domenie strony na której jesteś. Dodam twoj komentarz.