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.

Published by Marcin Pietrzak

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Duis nec ante nec lectus laoreet feugiat. Donec aliquet justo in tellus gravida, id elementum magna convallis. Curabitur faucibus mauris sed risus egestas lobortis. Nunc vestibulum elit ac ipsum ultricies, non ultrices lorem ultrices. Nam at dolor id elit lobortis ullamcorper. Fusce commodo turpis nisi, efficitur efficitur libero dictum eget. Morbi viverra dignissim lacus. Vestibulum mi justo, dictum ut iaculis eget, scelerisque et nibh.

5 replies on “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 :)

Comments are closed.

Więcej w baza danych, mysql
Aktualizacja IP.Board 2.3.5 do 3.0.2

IP.Board jest najlepszym jaki do tej pory spotkałem oprogramowaniem do forum. Jest to oprogramowanie płatne, na dodatek w sposób mieszany....

Zamknij