Kilka pytan odnosnie MySQL, Klucze/Indexy, Relacje, Typy tabel. Opcje

[thornag]

Witam

Obilo mi sie o uszy ze MySQL nie jest prawdziwie relacyjna baza danych, obilo sie takze ze zeby przyspieszyc jej dzialanie trzeba "zakladac" indeksy.

Idea kluczy podstawowych czy obcych chyba jasno wskazuje na relacyjnosc ?

Ale zmierzajac do pytania, chcialbym sie dowiedziec jak najwiecej na temat indeksow, jak to welasciwie dziala jakie sa zasady dodawania kluczy, kiedy powinno sie to stosowac i jak z tego w stu procentach korzystac.

Ten post edytował thornag 6.10.2006, 10:43:48

[dr_bonzo]

Myslq obsluguje wiele typow tabel, m.in.
MyISAM -- domyslna? bez ograniczen referencyjnych (czy jak sie to zwie; nie pilnuje np kluczy obcych), bez transakcji, jest szybka i tyle ma zalet
InnoDB - tranzakcje, integralnosc referencyjna (tego slowa mi braklo (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) )


Indexy -- sa jak indeks w ksiazce -- nie musisz CALEJ przejrzec zeby znalezc fragment o interesujacym cie zagadnieniu, przegladasz tylko POSORTOWANY indeks. Potem sprawa sie komplikuje, gdy obecnosc indeksow wyszukiwanie danych itd -- nic tylko czytac, sprawdzac itd.

[marcini82]

Mowiac najprosciej i bez wchodzenia w szczegoly techniczne: indeks zalozony na kolumnie przyspiesza wyszukiwanie danych wzgledem tej kolumny, ale za to spowalnia operacje dodawania i usuwania rekordow.
Indeksy na kluczach glownych i obcych sa w mysql obowiazkowe.

[SongoQ]

Index stosuje sie wszedzie tam gdzie mamy pewne kryteria (w sekcji WHERE). Nalezy uwazac z indeksami bo jesli za wiele zalozysz to optymalizator moze nieprawidlowy plan przygotowac. Index ma sens gdzie do okolo 20% rekordow sie powtarza, powyzej tego przedzialu raczej index jest nie zalecany.

Index jest to posortowany zbior wartosci, ktory bezposrednio wskazuje na rekord, przewaznie uzywany jest algorytm btree. Index mozesz zalozyc na jedno pole, na wiele pol (index zlozony) no i oczywiscie na wartosc, czyli masz pole np nazwisko i chcesz uzywac LIKE dla wielkich czy malych liter wiec zakladasz inndex na wartosc LOWER(nazwisko).

Nie wiem jak to jest dokladnie w MySQLu ale sa index zlozony moze zostac uzyty do wyciagniecia danych, po wyrazie SELECT.

[Luciano]

Jeszcze dodam od siebie, ze momentem najbardziej spowalniajacym "wyluskiwanie" okreslonych danych z bazy jest przeszukiwanie dysku twardego ktory ma sprzetowe ograniczenia i ponad pewien poziom szybciej sie juz nie da. W zwiazku z tym jak moi przedmowcy nadmienili baza sobie robie "zakladki" wzgledem tego czego chcemy ja przeszukiwac w przyszlosci.

Ten post edytował Luciano 6.10.2006, 09:08:33

[thornag]

Rzeczywiscie sporo mi to rozjasnilo.

Przyklad, powiedzmy ze mam trzy tabele w bazie danych channel i item i channel_item (RSS).

W pierwszych dwoch zakladam klucze na ID (wiadomo auto_increment), a w drugiej indeksy na obydwa pola ? Czy w ten sposob moge zdefiniowac relacje ktora spowoduje usuniecie rekordow powiazanych po usunieciu np rekordu z tabeli channel ?

Relacja oczywiscie jeden do wieliu (channel - item)

[SongoQ]

@Luciano Ogonie proces strojenia SQL jest to odciaznie operacji I/0 czyli zapis odczyt z hdd.

[dr_bonzo]

thornag: jesli relacja jeden-do-wielu to nie potrzebujesz trzeciej tablicy.

Czy w ten sposob moge zdefiniowac relacje ktora spowoduje usuniecie rekordow powiazanych po usunieciu np rekordu z tabeli channel ?

Mozesz: uzyj InnoDB, i FOREIGNKEYow z ON DELETE CASCADE

[thornag]

Hmm, czyli przy relacji jeden do wielu lepiej umiescic referencje do channel w tabeli item, a w przypadku opisywanym przez Zyxa w jednym z artykulow gdzie mamy utwory i albumy lepiej zrobic 3 poniewaz utwor moze znajdowac sie na wielu albumach ?

Do tego kolejne pytanie. Tym razem z beczki projektowech. Majac tabele uzytkownikow ktorzy moga nalezec do kilku grup, czy dobrym pomyslem jest wydzielanie grup do innej tabeli czy moze zawrzec je w tabeli uzytkownik ?

Jesli rozwiazanie pierwsze to jaka jest najlepsza struktura, ja stosuje user_id - grupa1 - grupa2 pola tinyint/bool kazdy user to nowy rekord. Czy to dobre podejscie ?

Ten post edytował thornag 6.10.2006, 10:31:59

[SongoQ]

3 Tabele user, grupa, user_grupa. Tabela user_grupa jest tabela ktora laczy 2 pozostale. Wtedy 1 user moze byc w wielu grupach i wielu userow moze byc w tej samej grupie.

[thornag]

W rozwiazaniu ktore stosuje teraz jeden user moze nalezec do wielu grup, wystarczy edytowac mu pole grupy i ustawic na 1. W ten sposob mamy mniejsza ilosc rekordow (ja sie nie upieram przy swoim (IMG:http://forum.php.pl/style_emoticons/default/tongue.gif) ) i tylko dwie tabele. Calosc 'wydaje' sie byc bardziej optymalna. MOglibyscie wytlumaczyc dlaczego lepiej to zaprojektowac na trzech tabelach (biorac pod uwage ze w obydwu rozwiazaniach jest spelnione zalozenie ze user moze byc w wielu grupach).

Drugie, odnosze wrazenie ze tabele na silniku innoDB sa naprawde o wiele bardziej funkcjonalne od MyISAM, jakie sa natomiast ich wady (nie moze byc tak ze sa tylko lepsze bo wyjde na idiote korzystajac caly czas z MyISAM (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) )

P.S. Wiem ze moglbym to wszystko znalezc na googlu ale juz mnie topic wciagnal a moze i potomnym sie przyda (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

Ten post edytował thornag 6.10.2006, 10:42:31

[SongoQ]

Drugie, odnosze wrazenie ze tabele na silniku innoDB sa naprawde o wiele bardziej funkcjonalne od MyISAM, jakie sa natomiast ich wady (nie moze byc tak ze sa tylko lepsze bo wyjde na idiote korzystajac caly czas z MyISAM (IMG:http://forum.php.pl/style_emoticons/default/smile.gif) )

Minusem jest wydajnosc, wewnetrzne oid czy rowid spowalniaja poniewaz musza byc odwolania do innych tabel (mowa tu o relacji) podonie z transakcjami tez wymagany jest pewien czas. Pewnie by sie jeszcze wiele minusow znalazlo ale jednym z podstawowych jest czas.

Odnosnie 2 tabel jest zlym rozwiazanie jesli chcesz aby jeden user nalezal do wielu grup i jedna grupa do wielu userow. Po co tworzyc wiele userow po co dane powtarzac. Wszystko ma byc czysto i jasno wynikac z polaczen tabel. Jesli wezmiesz wydrukowany schemat bazy to powinienes linia zaznaczyc jak wyciagniesz grupy do usera a w Twoim przypadku tak nie jest. Jesli bedziesz mial wiele userow tych samych to np zmienisz 1 haslo to co we wszystkich modyfikujesz? Podobnie liczenie co bedziesz grupowal dane? To jest zle i tak sie nie robi. Zastanow sie rozrysuj sobie to na kartce i wszystko ladnie Ci wyjdzie.

[dr_bonzo]

Hmm, czyli przy relacji jeden do wielu lepiej umiescic referencje do channel w tabeli item, a w przypadku opisywanym przez Zyxa w jednym z artykulow gdzie mamy utwory i albumy lepiej zrobic 3 poniewaz utwor moze znajdowac sie na wielu albumach ?

A jeden item RSSa moze nalezec do wielu kanalow? No wlasciwie tez moze. Zalezy jakie masz zalozenia (IMG:http://forum.php.pl/style_emoticons/default/smile.gif)

Do tego kolejne pytanie. Tym razem z beczki projektowech. Majac tabele uzytkownikow ktorzy moga nalezec do kilku grup, czy dobrym pomyslem jest wydzielanie grup do innej tabeli czy moze zawrzec je w tabeli uzytkownik ?

Jak pisal SongoQ: 3 tabele, bedziesz mial baze znormalizowana (odsylam do googla) -- czyli -- nie bedziesz mial powtarzajacych sie danych, bedziesz mogl dodawac kolejne grupy (edycja schematu bazy aby dodac kolejna to nie jest rozwiazanie), itd.

[thornag]

Ok dzieki za wyczerpujace wyjasnienia. Reasumujac, przy relacjach jeden do wielu robimy dwie tabele, przy wiele do wielu trzy w tym jedna z powiazaniem. raz jeszcze dzieki

Pozdrawiam.