[MySQL]Jaki typ danych dla bazy Opcje

[mperlam]

Cześć. Mam mały dylemat jak stworzyć bazę danych aby była ona jak najbardziej optymalna. Chce zrobić takie pola:

ID (UNSIGNED int(11 czy bez wartości max?))
login (varchar (z wartością w nawiasie lepiej czy nie?))
pass (char(32))
level (tinyint(1))

Prędzej będzie jeszcze mail ale pomijam ponieważ chodzi o samą zasadę. Czy umieszczenie w nawiasie maksymalnej wielkości coś daje? (chodzi o wielkość i szybkość bazy) Czy raczej to bez różnicy? Jeżeli tak to jakie wartości wystarczą dla mail oraz login?

[nospor]

ID (UNSIGNED int(11 czy bez wartości max?))

11 nie okresla zadnego max. Skad wy to bierzecie? wywal te 11 bo to niczemu nie sluzy.
podanie liczby sluzy przy ZEROFILL a tego nikt przewaznie nie uzywa bo na dobrą sprawę niczemy nie sluzy

login (varchar (z wartością w nawiasie lepiej czy nie?))

Tak, dla varchar nalezy okreslic max

[Crozin]

1. Typ całkowity (INTEGER) i jego pochodne nie mają czegoś takiego jak "wartość maksymalna definiowana w nawiasie" - to tylko informacja używana przy dopełnianiu zerami (ZEROFILL).
2. W MySQL-u typ (VAR)CHAR musi mieć zdefiniowaną długość. O ile w przypadku CHAR ma to bezpośrednie przełożenie na rozmiar bazy danych o tyle w przypadku VARCHAR szczerze powiedziawszy nie wiem czy ma to jakieś istotne znaczenie. Na wydajność nie będzie to miało wpływu.
3. Adres e-mail może być w teorii bardzo długi, jednakże w praktyce około 50 znaków w zupełności wystarcza. Login użytkownika z reguły również nie będzie przekraczał 50 znaków.

[mperlam]

Dzięki. W takim razie czemu służy ta wartość? Po co stosować ją w varchar? Czy wartość 1 w tinyint mogę sobie odpuścić? (level usera będzie od 0 do 9)

[Crozin]

Dzięki. W takim razie czemu służy ta wartość? Po co stosować ją w varchar?

Ma ona marginalne znaczenie przy pewnych operacjach (ich wydajności).

Czy wartość 1 w tinyint mogę sobie odpuścić?

Tak. Co więcej jak już wspomniał @nospor możesz ją sobie darować wszędzie tam gdzie nie korzystasz z ZEROFILL, czyli pewnie wszędzie.

(level usera będzie od 0 do 9)

Prawdopodobnie lepszym rozwiązaniem będzie tutaj użycie typu ENUM, a nie INT - dla czystej wygody i przejrzystości.

[mperlam]

Ok, chciałbym jeszcze się dowiedzieć dlaczego stosowanie enum jest lepsze. Chciałem zrobić system "rang" przydzielając odpowiedni numer:

0 - ban
1 - konto nieaktywne
2 - konto aktywowane ale nie włączone przez admina
3 - konto aktywne
4 - VIP
8 - moderator
9 - admin

A pózniej sobie czytać i przydzielac rangi w razie potrzeby za pomocą switch. Chciałbym jeszcze zrobić system aktywacji konta (oraz pozniej kluczy do "zapamietaj mnie") oraz przetrzymywac ip ostatniego logowania (i datę) ale nie wiem czy robić to w osobnej tabeli, może bany tez przenieść do osobnej?

Ten post edytował mperlam 18.01.2011, 18:26:09

[nospor]

bo do bazy zamiast wkladac 9 bedziesz wkladal 'admin' itd. Wizualnie jest to czytelniejsze - ot i cala wygoda.
Taka wygoda ze ja osobiscie nigdy z tego nie korzystam i wale tinyint zamiast enum (IMG:style_emoticons/default/smile.gif)

[mperlam]

W przyszłości chcę mogę zmienić nazwy/rozbudować rangi więc myślę, że mój sposób jest ok (IMG:style_emoticons/default/smile.gif)

Mogę poprosić jeszcze o sugestię jak rozwiązać sprawę kluczy (autoryzacyjnych, zmienianych co logowanie) oraz IP? Lepiej zrobić osobną tabelę (wtedy bana na użytkownika również tam przenieść) czy dodać do istniejącej (users, której część podałem)?

[thek]

@nospor: Enum jest wydajniejsze z prostej, jednej, przyczyny. Nie jest to jawnie napisane nigdzie, ale zauważyłem że enum jest nie tylko polem, ale i jednocześnie indeksem. Masz więc 2 w 1 (IMG:style_emoticons/default/smile.gif) Sprawdź sobie dając nawet jako enum liczby. Typ wyliczeniowy powinien być szybszy nawet od tinyint ale podobny wydajnościowy jeśli na tinyint założysz indeks (IMG:style_emoticons/default/winksmiley.jpg)

@Crozin: Varchar jest ciut mniej wydajny niż char, ponieważ baza musi zawsze przeliczyć gdzie jest koniec danej kolumny. Char nie ma tego problemu - zawsze następna kolumna jest po x znakach. Przy varchar może być od 0 do maksymalnie określonej. Stąd pod kątem wydajności jako pierwsze nie powinny być varchar i text, ale int, char i inne z ściśle precyzyjną długością. Dzięki temu dostęp do tych kolumn w bazie jest szybszy. Jeśli ustawi się 5 intów po kolei a potem varchar oraz text to podczas operacji odczytu, dostęp do nich będzie nieco szybszy niż miały by inty być z text i varchar przemieszane. Baza nie musi wiedzieć gdzie się kończy pole varchar lub text by znaleźć, gdzie leży dokładnie następna kolumna jeśli zachowasz porządek i kolumny o stłej długości są na samym początku. Baza wie, że int ma x bajtów, float y bajtów i od razu skacze we właściwe miejsca. Zmienna długość sprawia, że baza musi najpierw ją poznać, bo dla każdego rekordu może początek następnej kolumny być gdziekolwiek. Stąd choćby jeśli zakładasz pole o nazwie hash i wiesz, że będzie ono zawsze 32 znakowe (tak ma md5) to używasz char(32) a nie varchar(32) bo ma to wpływ na szybkość dostępu do następnej kolumny za tym polem. To są już głupoty i ne każdy mus o tym wiedzieć, ale podczas optymalizacji dostępu la dużych i obciążonych serwisów takie coś jak kolejność kolumn staje się istotne.

[nospor]

@nospor: Enum jest wydajniejsze z prostej, jednej, przyczyny. Nie jest to jawnie napisane nigdzie, ale zauważyłem że enum jest nie tylko polem, ale i jednocześnie indeksem. Masz więc 2 w 1 Sprawdź sobie dając nawet jako enum liczby. Typ wyliczeniowy powinien być szybszy nawet od tinyint ale podobny wydajnościowy jeśli na tinyint założysz indeks

Jesli ktoś zamierza po danym polu wyszukiwać to chyba logiczne, że założy indeks.

Pewnie że bułka z kremem jest słodsza od samej bułki, gdyż bułka z kremem posiada jeszcze krem a bułka bez kremu nie posiada kremu. Ale jakbym chciał zjeść bułkę z kremem to bym se ten krem na bułkę nałożył (IMG:style_emoticons/default/smile.gif)

[wookieb]

@nospor: Enum jest wydajniejsze z prostej, jednej, przyczyny. Nie jest to jawnie napisane nigdzie, ale zauważyłem że enum jest nie tylko polem, ale i jednocześnie indeksem. Masz więc 2 w 1 (IMG:style_emoticons/default/smile.gif) Sprawdź sobie dając nawet jako enum liczby. Typ wyliczeniowy powinien być szybszy nawet od tinyint ale podobny wydajnościowy jeśli na tinyint założysz indeks (IMG:style_emoticons/default/winksmiley.jpg)

Każda wartość ma przyporządkowany numer "index". To nie znaczy, że to pole jest indeksem. Jeżeli lista wartości w kolumnie jest stała ENUM będzie jak najbardziej prawidłowym typem pola.

[Crozin]

@thek: Super, że napisałeś mały wywód, ale... nijak nie ma się on do tematu, a sam doskonale wiem jaka jest różnica pomiędzy CHAR i VARCHAR. Napisałem, że nie wiem jaki sens ma podawanie maksymalnej długości dla VARCHAR (VARCHAR(n) - o to nieszczęsne n chodziło) - odpowiedź jest jednak bez problemu dostępna w Google'u - więc...

Swoją drogą. Masz paskudny styl pisania - masa tekstu, mało treści.

[thek]

@Wookieb: nie chodzi mi o numer index. Doświadczalnie kiedyś sprawdzał kumpel jak się ma enum do szybkości operacji w bazie na danych i empirycznie doszliśmy do tego, że enum musi mieć jakiś indeks założony, bo inaczej nie mógłby być taki szybki. Wygląda na to, że same nazwy są jedynie "nakładką tekstową" na najbliższy pasujący ilości elementów wariant inta lub binary z założonym indeksem i to na tym tak naprawdę on operuje, a nie na tekstach widocznych dla usera. Inaczej nie mogę wytłumaczyć takiej szybkości operacji. A nie sprawdzałem nigdy kodu silnika MySQL więc są to moje przypuszczenia na temat możliwej implementacji enum w tej bazie.

@Crozin: jak to nie ma nic wspólnego? Zauważ, że to iż Ty znasz różnicę jest czymś innym niż wiedza autora tematu. On jej nie zna (co widać w postach) i wypowiedzi osób w tym temacie mają mu to przybliżyć. To że piszę dużo wynika z faktu, iż chcę zaprezentować w miarę zrozumiałe przykłady. Gdybym ich nie dał, to post byłby mniej więcej 1/3 tego co napisałem. A to dlaczego odpowiedziałem ma związek z punktem 2 Twojego postu gdzie stwierdziłeś, że liczba w varchar nie ma wpływu, co nie jest dokońca prawdą. Im mniejsza tym czas dostępu do kolejnej kolumny także jest mniejszy. Dlatego varchar powinien być możliwie jak najkrótszy. Ma to więc przełożenie na wydajność w pewien sposób. Dodatkowo jesli wierzyć komentarzom na stronie mysql, wystarczy że choć jedna kolumna rekordu jest typu varchar i cała wydajnośc z char jest nic nie warta, ponieważ prosty dostęp do danych poprzez szybkie wyliczanie pozycji wiersza i kolumny offsetem jest zakłócony. Innymi słowy char ma sens jedynie gdy długość każdego wiersza w tabeli jest zawsze stała.

Gdy mowa o rangach to problem można rozwiązać na 2 sposoby.
1) Podajesz enum, który łatwo rozszerzać o kolejne rangi, ale może takie podejście nieco przy ACL (system uprawnień) pomieszać szyki, ponieważ wymaga ciut większego przemyślenia problemu tychże uprawnień.
2) zastosować tinyint (lub większy) i porobić kolejne rangi nie po kolei, ale co połowę zakresu by w owe luki mieć możliwość wepchnięcia nowych rang. Większa bowiem jest możliwość rozwarstwiania uprawnień nisko w hierarchii niż wysoko. Prędzej powstanie kilka różnych grup userów o innych uprawnieniach niż różne rodzaje moderatorów.

Najlepiej jednak zrobisz czytając o ACL więcej i wyrabiając sobie pomysł pasujący do Twojego problemu/cms/serwisu.

[wookieb]

@Wookieb: nie chodzi mi o numer index. Doświadczalnie kiedyś sprawdzał kumpel jak się ma enum do szybkości operacji w bazie na danych i empirycznie doszliśmy do tego, że enum musi mieć jakiś indeks założony, bo inaczej nie mógłby być taki szybki. Wygląda na to, że same nazwy są jedynie "nakładką tekstową" na najbliższy pasujący ilości elementów wariant inta lub binary z założonym indeksem i to na tym tak naprawdę on operuje, a nie na tekstach widocznych dla usera. Inaczej nie mogę wytłumaczyć takiej szybkości operacji. A nie sprawdzałem nigdy kodu silnika MySQL więc są to moje przypuszczenia na temat możliwej implementacji enum w tej bazie.

Ojej. A poza tym, że "DOSZLIŚCIE" do czegoś co jest wszędzie znane nie wpadłeś na pomysł, że byłoby GŁUPIE aby mysql sam tworzył indeks dla pola które jest typu ENUM? BA! Tworzyłby je w swoim "wymiarze" tylko po to by użytkownik o tym nie wiedział? Wynika z tego, że tak bo przecież

Inaczej nie mogę wytłumaczyć takiej szybkości operacji.

A jak nie wytłumaczysz mi dlaczego bóg nie istnieje tzn, że istnieje?

Dla pełnej jasności

[SQL] pobierz, plaintext 
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pole` enum('t1','t2','t3','t4') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=9 ;
 
--
-- Zrzut danych tabeli `test`
--
 
INSERT INTO `test` (`id`, `pole`) VALUES
(1, 't1'),
(2, 't2'),
(3, 't3'),
(4, 't4'),
(5, 't1'),
(6, 't2'),
(7, 't3'),
(8, 't4');
[SQL] pobierz, plaintext 

Test

[SQL] pobierz, plaintext 
EXPLAIN SELECT * FROM `test` WHERE pole = 't2'
[SQL] pobierz, plaintext 

Kod

    SIMPLE     test     ALL     NULL     NULL     NULL     NULL     [color="#FF0000"][size="5"]8[/size][/color]     Using where

Dodajemy indeks

[SQL] pobierz, plaintext 
ALTER TABLE `woocms_trunk`.`test` ADD INDEX ( `pole` ) 
[SQL] pobierz, plaintext 

EXPLAIN

[SQL] pobierz, plaintext 
	SIMPLE 	test 	ref 	pole 	pole 	1 	const 	[color="#FF0000"][size="5"]2[/size][/color] 	USING WHERE; [b]USING INDEX[/b]
[SQL] pobierz, plaintext 

No chyba, że wcześniej straciłem połączenie z innym wymiarem.

Powód edycji: [wookieb]:

[Ulysess]

a czy nie lepszym sposobem jest zrobienie w głownej tabeli pola status typu TINITY 1 w której przetrzymywana był był status konta czyli
nie aktywne
aktywne
zablokowane

+ tabela np rank z polami
id_user
rank
name (tutaj można pominąć i zastosować switcha w php)

(IMG:style_emoticons/default/questionmark.gif)

może źle kombinuje ale wydaje mi się że lepiej zrobić oddzielną tabele w której będa te wyższe rangi niż w głownej tabeli dla każdego usera trzymać czy jest adminem /modem itp

przy okazji zapytam się.
na swojej stronie w polach typu int ustawiałem długości , rozumiem że to głupota ale czy jeśli już jest to może tak pozostać czy lepiej pozmieniać na NULL czy tam 0 (IMG:style_emoticons/default/questionmark.gif) .

i jeszczne jedno (odpowiedzi nigdzie nie mogłem znaleźć) każde z pól typu INT jest dodatkowo UNSIGNE . przykładowo mam wartość 10 od tej wartości odejmę 12 , w polu znajdzie się 0 ale czy istnieje jakieś niebezpieczeństwo powstania jakiegoś błędu czy coś w tym stylu ?

[wookieb]

na swojej stronie w polach typu int ustawiałem długości , rozumiem że to głupota ale czy jeśli już jest to może tak pozostać czy lepiej pozmieniać na NULL czy tam 0 (IMG:style_emoticons/default/questionmark.gif) .

Ma to znaczenie TYLKO przy polach typu ZEROFILL. Inaczej może zostać.

i jeszczne jedno (odpowiedzi nigdzie nie mogłem znaleźć) każde z pól typu INT jest dodatkowo UNSIGNE . przykładowo mam wartość 10 od tej wartości odejmę 12 , w polu znajdzie się 0 ale czy istnieje jakieś niebezpieczeństwo powstania jakiegoś błędu czy coś w tym stylu ?

Zależy od logiki. Nie patrz na to pod kątem bezpieczeństwa tylko pod tym co chcesz osiągnąć. W skrócie dodanie unsigned zagwarantuje Ci, że wartości w tym polu będą dodatnie (np przy polach typu ID)

P.s o tinyint własnie gadaliśmy.

[Crozin]

Co do UNSIGNED: http://en.wikipedia.org/wiki/Signedness