Kiedy stosować TINYINT zamiast INT?

91

Ogólnie zawsze używam Ints. Wiem, że teoretycznie nie jest to najlepsza praktyka, ponieważ należy używać najmniejszego typu danych, który gwarantuje przechowywanie danych.

Na przykład lepiej jest użyć, tinyintgdy wiesz, że jedyne dane, które będziesz przechowywać, to 1, 0 lub zero (z bardzo małą szansą na rozszerzenie ich do 2 lub 3 później).

Jednak jedyny powód, dla którego to robię, to do celów przechowywania - użycie 1 bajtu w wierszu zamiast 4 bajtów.

Jakie są skutki używania tinyint(lub smallintnawet bigint) po prostu intinnego niż oszczędzanie miejsca na dysku twardym?

sql-server database-theory

— Richard
źródło

2

To bardzo miłe pytanie (+1). MySQL ma SELECT ... PROCEDURE ANALYZE (), która faktycznie zaleca najmniejsze typy danych, które powinna mieć tabela dla danego SELECT. To była częściowo inspiracja dla mojej odpowiedzi.

— RolandoMySQLDBA

3

Świetne pytanie, ale precyzyjnie zakres malutkiego wynosi 0–255. Pole bitowe ma wartość 0 lub 1 (lub NULL). Koszt przechowywania dla maleinty wynosi 1 bajt. Każde 8-bitowe pola w tabeli będą kosztować 1 bajt pamięci. msdn.microsoft.com/en-us/library/ms187745.aspx i msdn.microsoft.com/en-us/library/ms177603.aspx

— billinkc

@billinkc Right. Dlatego wspomniałem o możliwości rozszerzenia kolumny o wartości 2 lub 3. Jeśli podasz 2 lub 3, musisz użyć tinyint (w bardzo najmniejszej skali).

— Richard

1

„Na przykład lepiej jest używać tinyint, gdy wiesz, że jedyne dane, które będziesz przechowywać, to 1, 0 lub zero (z bardzo małą szansą na rozszerzenie ich do 2 lub 3 później)”. Użyłbym ENUM do takiej rzeczy. Są one przechowywane jako pola bitowe i, jak wielu innych tutaj wskazało, małe oszczędności na rekord sumują się do dużych oszczędności w całej bazie danych - nawet więcej, jeśli kolumna jest indeksowana.

2

@ user6665 I'd use an ENUM for such a thing.Nie w SQL Server, nie zrobiłbyś tego, ponieważ nie ma żadnych wyliczeń.

— underscore_d

92

Miejsce na dysku jest tanie ... nie o to chodzi!

Przestań myśleć o przestrzeni dyskowej, zamiast tego pomyśl o puli buforów i przepustowości pamięci . Na samym końcu przepustowość pamięci podręcznej procesora i magistrali pamięci . Powiązany artykuł jest częścią serii podkreślającej problemy ze słabym wyborem klucza klastrowego (INT vs GUID vs Sekwencyjny GUID), ale podkreśla różnicę, jaką mogą wprowadzić bajty.

Nadrzędnym przesłaniem są kwestie projektowe. Różnica nie pojawi się w indywidualnej bazie danych na odpowiednio określonym serwerze, dopóki nie trafisz na terytorium VLDB, ale jeśli możesz zaoszczędzić kilka bajtów, dlaczego nie zrobić tego.

Przypomina mi się środowisko opisane we wcześniejszym pytaniu . Ponad 400 baz danych o wielkości od 50 MB do 50 GB na instancję SQL. Przeszukiwanie kilku bajtów na rekord, tabelę i bazę danych w tym środowisku może mieć znaczącą różnicę.

— Mark Storey-Smith
źródło

29

Oprócz innych odpowiedzi ...

Wiersze i wpisy indeksu są przechowywane na 8 000 stronach. Tak więc milion wierszy po 3 bajty na wiersz nie ma 3 MB na dysku: wpływa na liczbę wierszy na stronę („gęstość strony”).

To samo dotyczy nvarchar do varchar, smalldatetime do datetime, int do tinyint itp

Edycja, czerwiec 2013 r

http://sqlblog.com/blogs/joe_chang/archive/2013/06/16/load-test-manifesto.aspx

Ten artykuł stanowi

Ważnymi kryteriami są liczność i stosunek stron do wierszy.

Wybór typu danych ma więc znaczenie

— gbn
źródło

5

Słuszna uwaga. Bezwzględnym najgorszym przykładem jest 4028-bajtowy wiersz składający się z kolumn o stałej długości, do których chcesz dodać kolumnę. Dodanie smallinta zabrałoby cię do 4030 (2 rzędy na stronę), ale int popycha cię ponad granicę (1 rząd na stronę, 4028 bajtów zmarnowanych na stronę).

— Mark Storey-Smith

Kiedyś zrobiłem test wydajności na int vs bigint. Zapisywanie 1 miliona rekordów, porównywanie czasu i pamięci oraz pobieranie ich pojedynczo, ponownie mierząc wydajność. Nie widziałem większych różnic. Zrobię ten sam test wydajności dla int vs tinyint. Naprawdę uważam, że można to pominąć w przypadku 80% aplikacji, co skutkuje bardziej spójnymi typami danych i niższymi kosztami konserwacji.

— Saeed Neamati,

1

@ SaeedNeamati Możesz przeczytać artykuł z odpowiedzi Marka („ Czy słyszałeś kiedyś ... zróbmy to - zmartwimy się później o wydajność? ... słyszę to cały czas ... ”) i gbn jest tutaj . Wydaje mi się, że najlepszym wyjściem będzie to, że każdy nieefektywny wybór pokaże paski we właściwej skali, a jelita OP nie są złe.

— ruffin

14

Uwzględnia się nie tylko przechowywanie stolików. Jeśli korzystasz z indeksów, w których kolumna int jest częścią klucza złożonego, naturalnie chciałbyś, aby strony indeksu były jak najbardziej pełne, ponieważ jest to wynik tak małych wpisów indeksu, jak to możliwe.

Na pewno spodziewałbym się, że sprawdzenie wpisów indeksu na stronach BTREE byłoby nieco szybsze przy mniejszych typach danych. Jednak wszelkie zmienne VARCHAR biorące udział we wpisach indeksu zrównoważyłyby (nullify) wzrost wydajności dzięki zastosowaniu TINYINT zamiast INT.

Niezależnie od tego, jeśli wpisy indeksu zawierają wpisy złożone i wszystkie są liczbami całkowitymi, im mniejsze są liczby całkowite, tym lepiej i szybciej.

— RolandoMySQLDBA
źródło

13

Wszystkie rzeczy stają się bardziej złożone, gdy bazy danych stają się większe:

okna serwisowe muszą zostać powiększone lub przełożone
kopie zapasowe (pełna kopia zapasowa na koniec dnia staje się absurdalnym zjadaczem czasu, więc potrzebujesz różnicowych lub nawet dzienników kopii zapasowych i wykonuj pełne raz w tygodniu, a może raz w miesiącu)
konserwacje występów stają się żmudne (tworzenie indeksu w tabeli zawierającej wiele milionów wierszy nie zajmuje trywialnego czasu) i wymaga zmiany harmonogramu i pogorszenia, jeśli tabela jest szeroka ...
A przesyłanie tej kopii zapasowej 100 Gb przez sieć nie jest czymś, co nazywam bułką z masłem - szczególnie jeśli sieć (z jakiegoś nieznanego powodu) upiera się po zerwaniu połączenia na znaku 75 Gb ... (zdarzyło się to przy instalacji, nad którą pracowałem tworzył kopię zapasową na zamapowanym dysku w sieci - sieci) ...

A jakie typy danych mają z tym wspólnego? WSZYSTKO. Używanie rozmiarów wierszy większych niż to konieczne powoduje, że strony bazy danych wypełniają się wcześniej niż jest to konieczne, a nawet marnuje miejsce, jeśli rozmiar wiersza jest taki, że na stronie nie można zapisać więcej niż jednego rekordu. Rezultatem jest więcej stron potrzebnych do zapisania i odczytu, więcej pamięci RAM jest wykorzystywane do buforowania tego (większe rekordy wymagają większej pamięci). A ponieważ typy danych są określone jako większe niż potrzebne z dysku, twoje indeksy będą miały ten sam problem - szczególnie jeśli klastrujesz ten klucz główny złożony z 2 kolumn BIGINT, ponieważ wszelkie inne utworzone indeksy skopiują ten klucz podstawowy domyślnie na ich definicji.

Jeśli wiesz, że niektóre kolumny w tabeli, która będzie miała miliony wierszy lub nawet małą tabelę, która będzie FK do wielomilionowego wiersza, który nie potrzebuje 4-bajtowej liczby całkowitej do przechowywania swoich danych, ale 2-bajtowy wystarczy - użyj SMALLINT . Jeśli wartości z zakresu 0–255 są wystarczające, TINYINT . Flaga Tak / Nie? Jest BIT .

— Fabricio Araujo
źródło

9

Podczas gdy w przypadku tinyintvs intistnieją wyraźne różnice, takie jak miejsce na dysku, podziały stron i czas konserwacji, nie byłoby żadnego z nich varchar.

Dlaczego więc nie zadeklarować wszystkich pól tekstowych jako varchar(4000), ponieważ i tak zużyje ono tylko potrzebną przestrzeń? Co więcej, masz gwarancję, że Twoje dane nigdy nie zostaną obcięte.

Odpowiedź brzmi oczywiście:

Wyjaśnienie twoich intencji (ponieważ nikt nie zrozumie, dlaczego pole nazwy powinno mieć 4000 znaków)
Walidacja, ponieważ chcesz mieć pewność, że nikt nie poda całej biografii jako nazwy.

Dotyczy to również tych samych powodów tinyint.

— yoel halb
źródło

3

To starszy wątek, ale wyjaśnienie i walidacja nie są jedynym powodem. Jeśli masz VARCHAR (4000) dla czegoś, co powinno być VARCHAR (20), plan zapytań pomyśli, że twoje wymagania dotyczące pamięci i procesora są wieloma wielokrotnościami tego, co powinny być w odniesieniu do tej kolumny. Nie poświęciłem czasu, aby to zrobić, ale domyślam się, że prawdopodobnie możesz to zobaczyć, przeglądając plan zapytań dla VARCHAR (20), a następnie przejdź do VARCHAR (4000) i sprawdź szacunkowe koszty.

3

@GeorgeShouse Demonstracja tego tutaj

— Martin Smith,