Jak wybrać optymalny typ danych w bazach MySQL

Twórcy stron WWW zbyt często używają złych typów danych w swoich bazach danych. Mając na uwadze to, że zapytania do bazy zajmują najwięcej czasu w trakcie wykonywania skryptu, konieczne jest, aby baza danych i jej tabele były jak najlepiej zoptymalizowane w celu zapewnienia im jak największej wydajności, zwłaszcza w wysokowydajnych aplikacjach.

Bazę MySQL można najprościej zoptymalizować, wybierając poprawny, najlepszy, a więc najbardziej wydajny typ danych. Ale wielu konstruktorów zarówno baz MySQL, jak i innych ma czasem z tym problem. Ludzie niezajmujący się bazami danych, tacy jak programiści PHP, którzy są zmuszeni do użycia MySQL-a w swoich skryptach, bardzo często mylą się w wyborze typu danych.

Aby Wam pomóc zrozumieć dostępne typy danych i – miejmy nadzieję – używać ich poprawnie, przygotowałem opisy kilku najpopularniejszych – tych, których prawdopodobnie używacie najczęściej.

Ze znakiem czy bez znaku (SIGNED or UNSIGNED)?

Dla tych, którzy nie wiedzą, co oznacza, że liczba jest ze znakiem lub bez znaku, podaję krótkie wytłumaczenie:

„Liczby całkowite ze znakiem to takie, w których jeden bit jest przeznaczony na to, by oznaczyć, czy liczba jest ujemna czy nie. W typach bez znaku ten jeden bit nie jest zarezerwowany i liczba może być tylko dodatnia. Liczba INT ze znakiem (SIGNED) może więc przyjąć wartości pomiędzy -2,147,483,648 a 2,147,483,647, a bez znaku od zera do 4,294,967,295.

Jeśli nie będziesz używał liczb mniejszych od zera, powinieneś użyć typu UNSIGNED. Typ bez znaku również nadaje się bardziej na klucz podstawowy z autoinkrementacją w tabeli, ponieważ pozwala on na wprowadzenie większej liczby wierszy”.

Zbyt duże?

Powinieneś pomyśleć dokładnie przed tworzeniem pól. Jeśli masz tabelę użytkowników w małej witrynie internetowej, a klucz podstawowy ma typ INSIGNED INT, powinieneś zadać sobie pytanie, czy kiedykolwiek uzyskasz 4,294,967,295 użytkowników. Najbardziej prawdopodobną odpowiedzią jest „nie”. Musisz pomyśleć, ilu maksymalnie użytkowników będzie prawdopodobnie na Twojej stronie. Później wybierz prawidłowy typ danych numerycznych, który pokrywa się z Twoimi potrzebami. Stworzyłem tabelkę z typami danych numerycznych oraz ich minimalnymi i maksymalnymi wartościami:

Type	Minimum	Maximum
SIGNED TINYINT	-128	127
UNSIGNED TINYINT	0	255
SIGNED SMALLINT	-32,768	32,767
UNSIGNED SMALLINT	0	65,535
SIGNED MEDIUMINT	-8,388,608	8,388,607
UNSIGNED MEDIUMINT	0	16,777,215
SIGNED INT	-2,147,483,648	2,147,483,647
UNSIGNED INT	0	4,294,967,295
SIGNED BIGINT	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
UNSIGNED BIGINT	0	18,446,744,073,709,551,615

Jaka jest różnica pomiędzy CHAR I VARCHAR?

Te dwa typy różnią się tym, że CHAR ma stałą długość, VARCHAR natomiast zmienną. Oznacza to, że CHAR ma zawsze tę samą wielkość i zajmuje tyle samo bajtów, podczas gdy VARCHAR się zmienia, a ponadto dodaje jeden dodatkowy bajt danych. Kiedy powinniśmy używać CHAR?

Do przechowywania haseł MD5, które mają zawsze 32 znaki (jeśli przechowujemy je w postaci szesnastkowej, lepiej przechowywać je jako 16 bajtów w polu BINARY).

Do przechowywania kodów paskowych o stałej długości.

A kiedy VARCHAR?

Do przechowywania adresów e-mailowych oraz imion i nazwisk ludzi, które różnią się długością.

Będziemy raczej stosowali VARCHAR do danych wprowadzanych przez użytkowników, ponieważ mogą się one różnić długością.

Przechowywanie jako liczby

Kiedy jest to możliwe, powinno się przechowywać dane jako liczby, ponieważ przeszukiwanie i sortowanie ich jest szybsze. Na przykład adres IP może zostać przechowany w formie:

123.123.123.123

dzięki użyciu VARCHAR(15). Jeśli zostanie on przetłumaczony na adres sieciowy protokołu IPv4 (dzięki użyciu np. funkcji ip2long() w PHP), może zostać zachowany jako:

2071690107

w polu SIGNED INT.

Wybór poprawnego typu daty/czasu

W MySQL-u istnieje pięć różnych typów daty i czasu:

DATE,
TIME,
TIMESTAMP,
DATETIME,
YEAR.

DATE podaje tylko datę z dokładnością do jednego dnia. TIME reprezentuje czas (ale nie datę) wraz z sekundami. DATETIME to data i czas z dokładnością do pojedynczych sekund. TIMESTAMP to liczba sekund od EPOCH, a YEAR to po prostu rok.

YEAR wymaga jednego bajta, jest więc najlepszy do zapisu samego roku.

DATE i TIME to najmniejsze typy daty/czasu, które wymagają jedynie 3 bajtów.

TIMESTAMP wymaga 4 bajtów.

DATETIME wymaga 8 bajtów.

W prawie każdym przypadku najlepiej jest używać TIMESTAMP, który podaje datę i czas z dokładnością do sekundy.

źródło: http://making-the-web.com/2008/04/27/choosing-optimal-mysql-data-types/

Podoba Ci się ten tekst? Powiedz o tym innym na Fb: lub na G+:

publikuj:

wydrukuj

8 skomentuj »

Komentarze

#1 jacek 2008-07-07 11:39:51 0
"Jeśli masz tabelę użytkowników w małej witrynie internetowej, a klucz podstawowy ma typ INSIGNED INT, powinieneś zadać sobie pytanie, czy kiedykolwiek uzyskasz 4,294,967,295 użytkowników"

Nie - UNSIGNED INT jest potrzebny już powyżej 16,777,215 użytkowników.
None
#2 zyczliwy 2008-07-07 11:39:51 0
"oznaczone" ? po słownik do sklepu lub na lekcje matematyki - powinno być "ze znakiem" (bądź "bez znaku" dla unsigned).

Adres IP można też zamienić na postać liczbową przy użyciu funkcji MySQL o nazwie INET_ATON.
None
#3 dAREuS 2008-07-07 11:39:51 0
@zyczliwy: dziękuję za zwrócenie uwagi na tę oczywistość, była już redakcyjna chłosta.
None
#4 mario 2008-07-07 11:39:51 0
Odnośnie typów danych to należy rozważać różne aspekty.

Zaprezentowany sposób wyboru typów jest dla optymalizacji pod względem ilości danych.

Jednak gdy zależy nam na szybkości to już nie opłaca się wybierać najmniejszych typów danych.

Np nie uzywanie typu varchar ale char i to ze sporym zapasem daje przewage przy przeszukiwaniu baz danych (stała wielkość wiersza).

Integer jest też szybszy niz tinyint bo nie wymaga konwersji gdyż komputery i tak liczą na większych liczbach np 32 bitowych :)

W przyrodzie jest coś za coś tzn albo mniej miejsca będą zajmować dane albo szybciej je otrzymamy.

Dlatego warto się głębiej zastanowić nad wyborem typu danych.
None
#5 ktosik 2008-07-07 11:39:51 0
Adresu IP bym nie zalecał przechowywać jako 4-bajtowy INT. Jak wejdzie IPv6 to będzie z takim polem sporo problemów.
None
#6 Blaze^® 2008-08-04 23:54:12 0
Ale mimo wszystko artykuł daje do myślenia i bardzo się cieszę, że tutaj się znalazł.. szczególnie osobom, które MySQLa znają, bo znają, ale tak w średnim stopniu może to być pomocne, wasze uwagi również ;)

Ja wam za to bardzo dziękuję :)
None
#7 Admin 2009-12-02 23:16:21 0
ciekaw ciekawe
IP: 89.72.58.[...] Mozilla/5.0 (Windows; U; Windows NT 5.1; pl; rv:1.9.1.5) Gecko/20091102 Firefox/3.5.5 (.NET CLR 3.5.30729)
#8 d3uter 2011-02-23 12:12:37 0
Przydaloby sie kilka uwag na temat sensu stosowania zerofill, ilosc danych vs szybkość.
IP: 89.79.99.[...] Mozilla/5.0 (Windows; U; Windows NT 6.1; pl; rv:1.9.2.13) Gecko/20101203 Firefox/3.6.13

Aby dodać komentarz, musisz podać swój nick, treść komentarza oraz poprawnie przepisać oba słowa z obrazka (słowa muszą być rozdzielone spacją).
W treści komentarza można używać języka formatowania BBcode.