Josh Berkus w cyklu „Primary Keyvil” 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.
Szymon
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.
Marcin
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.
mpawlikowski
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.
Marcin
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.
benek
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 :)