June 11th, 2006 by depesz | Tags: | 4 comments »
Did it help? If yes - maybe you can help me?

przy wielu projektach pojawia się potrzeba przechowywania zmiennej listy “cech" jakichś obiektów.

weźmy na przykład sklep internetowy: mamy jakieś tam kategorie (struktura drzewiasta, moja ulubiona 🙂 ), w nich produkty. każdy produkt ma pewne cechy stałe – cena, tytuł, opis. natomiast produkty w określonych kategoriach mają swoje własne cechy dodatkowe.

np. dla samochodów możemy chcieć przechowywać:

  • ilość drzwi
  • pojemność silnika
  • typ paliwa
  • rodzaj skrzyni biegów

z drugiej strony, ogłoszenia w kategorii komputery będą miały pola takie jak:

  • ilość pamięci
  • wielkość dysku
  • typ procesora

najprostszym rozwiązaniem jest trzymanie produktów w każdej kategorii w oddzielnej tabelce – gdzie każda z tych tabelek ma różną strukturę (inna lista pól).

jest to rozwiązanie nieakceptowalne – osobiście uważam, że jakiekolwiek rozwiązanie zakładające modyfikacje struktury bazy danych w trakcie normalnego użytkowania jest błędne.

inną metodą jest zrobienie sobie tabelki typu:

 CREATE TABLE zmienne_cechy (
id         SERIAL PRIMARY KEY,
produkt_id INT  NOT NULL references produkty(id),
cecha1     TEXT,
cecha2     TEXT,
cecha3     TEXT,
cecha4     TEXT,
...
);

i pilnowanie, że dla danego produktu kolumna cecha1 oznacza pojemność silnika, a dla innego jest to ilość pamięci.

takie rozwiązanie ma swoje zalety – najważniejszą jest to, że aby wyciągnąć informacje o wszystkich polach dla danego produktu wystarczy pobrać jeden rekord z bazy.

ale dopóki nie obsługujesz miliona page-views dziennie w swoim sklepie – ten problem jest mało istotny 🙂

zdecydowanie najskuteczniejszą metodą jest tabelka:

 CREATE TABLE zmienne_cechy (
id         SERIAL PRIMARY KEY,
produkt_id INT  NOT NULL references produkty(id),
cecha      TEXT,
wartosc    TEXT
);

taka tabelka w jednej prostej strukturze pozwala na zapisanie wszystkich mozliwych cech i łatwe wyszukiwanie. no właśnie. czy na pewno łatwe?

tabelka pokazana taka jak tu – jest może i fajna, ale brakuje jej jeszcze jednej rzeczy:

create unique index ui_zmienne_cechy_pic on zmienne_cechy (produkt_id, cecha);

jeśli nie czytacie sql'i ze 100% zrozumieniem, to powyższe powoduje, że dany produkt może mieć tylko jedną wartość danej cechy. może mieć dowolnie wiele cech, ale żadna z cech nie może mieć wielu wartości.

zazwyczaj takie ograniczenie w niczym nie przeszkadza. zdarzają się czasem (ale bardzo rzadko) sytuacje, że istnieje potrzeba wielu wartości jednej cechy – sugeruję by wtedy nie kasować tego indeksu/klucza unikalnego tylko po prostu użyć ciut innych cech.

czemu?

otóż taka tabelka z pokazanym kluczem unikalnym pozwala nam w trywialny sposób zrobienie tego co bez klucza jest dużo trudniejsze (zasobochłonne): znalezienia produktów w/g kilku cech jednocześnie.

załóżmy, że chcemy znaleźć samochody o pojemności silnika 2000 z automatyczną skrzynią biegów.

bez klucza unikalnego jesteśmy skazani na coś takiego:

 select zc1.produkt_id
from zmienne_cechy zc1 join zmienne_cechy zc2 on zc1.produkt_id = zc2.produkt_id
where zc1.cecha='pojemnosc silnika' and zc1.wartosc = '2000' and zc2.cecha = 'skrzynia biegow' and zc2.wartosc = 'automat';

nie jest to oczywiście takie złe. ale przy dużej ilości produktów stanie się problematyczne. nie mówiąc o tym jak będziemy chcieli sprawdzić produkty w/g np. 5 cech na raz. 5 joinów? jeśli w bazie jest np. 1000 produktów i każdy ma średnio 10 cech, to łączymy 5 razy ze sobą tabelę o 50000 rekordów. i szukamy na nich wszystkich. mało przyjemne.

dodanie wspomnianego wyżej klucza unikalnego pozwala na użycie w naszym select'cie rzadko używanej (i słabo znanej) klauzuli HAVING:

 select produkt_id
from zmienne_cechy
where
(cecha='pojemnosc silnika' and wartosc='2000')
or
(cecha='skrzynia biegow' and wartosc = 'automat')
group by produkt_id
having count(*) = 2;

powstałe zapytanie ma kilka zalet:

  • jest trywialnie rozbudowywalne o kolejne cechy – bez konieczności dodatkowych joinów = wystarczy dodać dodatkowe warunki i podbić wartość w klauzuli having
  • jeśli używamy postgresql'a 8.1 i mamy dodatkowo indeks dwupoowy na (cecha, wartosc), to postgresql uzyje bardzo szybkich bitmap-or'ów

moje testy wykazały, że na postgresie 8.1 obie metody dają bardzo podobne wyniki (chodzi o czas zapytania) jeśli szukamy dwóch cech, natomiast już od 3 przewaga rozwiązania z having jest olbrzymia.

  1. 4 comments

  2. # Acid
    Jul 26, 2006

    hm.. no dobra a jak będzie z wydajnościa
    CREATE TABLE zmienne_cechy (
    id SERIAL PRIMARY KEY,
    produkt_id INT NOT NULL references produkty(id),
    cecha TEXT,
    wartosc TEXT
    );
    tego rozwiazania, jesli będziemy mieli w bazie duza liczbe produktow, kazdy z duza liczba cech, i dojdzie do tego duza liczba odwiedzin ;>

    czy w tedy duzo szybsze bedzie rozwiazanie, ze produkty z osobny kategori beda trzymane w osobnych tabelach ;>

  3. Jul 30, 2006

    czy ja wiem? co rozumiesz przez “duże”?
    wiesz. policzmy.
    duży sklep z artykułami biurowymi miał 7000 produktów.
    gumka do ścierania ma powiedzmy 1 cechę (kolor), wypasione xero moze miec z kilkanascie cech. statystycznie mysle, ze produkt ma z 2-3 cechy.
    powiedzmy 10 cech. to daje 70000 rekordów. tyle co nic.
    a jakby się faktycznie okazało, że mamy za dużo rekordów to mozna partycjonować.

  4. # Acid
    Jul 30, 2006

    hm… partycjonowac? mozesz rozwinac jakos temat albo odeslac do jakis materiałów na temat tego zagadnienia

    no ja w tej chwili mam 29 tabel w kazdej od 10 do 30 cech… bede chyba przenosił całosc na rozwiazanie z tego posta… musze tylko pomyslec jak miło i przyjemnie rozwiazac dodawanie do takiej zmiennej listy cech….

  5. Jul 30, 2006

    partycjonowanie: http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
    z tym, ze miej swiadomosc, ze nie jest to jeszcze w pelni dojrzala technika w postgresie. ma potencjal, ale sa pewne problemy.
    i tak szczerze – dla tabel ponizej kilku milionow rekordow to nawet bym sobie tym glowy nie zaprzatal.

Sorry, comments for this post are disabled.