jakie klucze stosować, jakie wybrać

13 października, 2009
Tagi: baza danych, josh berkus, klucz główny, mysql, postgresql

Josh Berkus w cyklu „Primary Keyvil” I, II i III 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.

Liczba komentarzy: 4

15 października 2009 o godzinie 0:08 Szymon skomentował:

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.

15 października 2009 o godzinie 12:29 Marcin skomentował:

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.

6 listopada 2009 o godzinie 12:43 mpawlikowski skomentował:

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.

6 listopada 2009 o godzinie 14:19 Marcin skomentował:

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.

Dodaj komentarz

Należy wpełnić pola oznaczone znakiem gwiazdki "*". Proszę zapoznać się z zasadami komentowania.

jakie klucze stosować, jakie wybrać

Liczba komentarzy: 4

15 października 2009 o godzinie 0:08 Szymon skomentował:

15 października 2009 o godzinie 12:29 Marcin skomentował:

6 listopada 2009 o godzinie 12:43 mpawlikowski skomentował:

6 listopada 2009 o godzinie 14:19 Marcin skomentował:

Dodaj komentarz

Szukaj

ostatnie komentarze

ostatnio popularne wpisy

Więcej w baza danych, josh berkus, klucz główny, mysql

Aktualizacja IP.Board 2.3.5 do 3.0.2

jakie klucze stosować, jakie wybrać

Liczba komentarzy: 4

15 października 2009 o godzinie 0:08 Szymon skomentował:

15 października 2009 o godzinie 12:29 Marcin skomentował:

6 listopada 2009 o godzinie 12:43 mpawlikowski skomentował:

6 listopada 2009 o godzinie 14:19 Marcin skomentował:

Dodaj komentarz

Szukaj

Tagi

ostatnie komentarze

ostatnio popularne wpisy

Więcej w baza danych, josh berkus, klucz główny, mysql

Aktualizacja IP.Board 2.3.5 do 3.0.2