Mam tabelę z 2 kolumnami. Typ obu kolumn jest ustawiony navarchar(38) . Jeśli utworzę wiersz z pustą wartością dla jednej z kolumn, czy zajmie to samo miejsce do przechowywania, jakby wartość nie była pusta?
Innymi słowy, czy MySQL zarezerwuje przestrzeń dyskową dla kolumny (w zależności od jej typu) podczas tworzenia wiersza?
Odpowiedzi:
Z fizycznej struktury wiersza Innodb, punktor 7 pod REDUNDANT ROW_FORMAT
Wartość SQL NULL powoduje rezerwację jednego lub dwóch bajtów w katalogu rekordów. Poza tym wartość SQL NULL rezerwuje zero bajtów w części danych rekordu, jeśli jest przechowywana w kolumnie o zmiennej długości . W kolumnie o stałej długości rezerwuje stałą długość kolumny w części danych rekordu. Zarezerwowanie stałej przestrzeni dla wartości NULL umożliwia aktualizację kolumny z NULL na wartość inną niż NULL, która może zostać wykonana w miejscu bez powodowania fragmentacji strony indeksu.
Z fizycznej struktury wiersza Innodb, punktor # 2 pod COMPACT ROW_FORMAT
Część nagłówka rekordu o zmiennej długości zawiera wektor bitowy do wskazania NULL kolumn. Jeśli liczba kolumn w indeksie, która może wynosić NULL, wynosi N, wektor bitowy zajmuje bajty SUFITOWE (N / 8) . (Na przykład, jeśli jest gdzieś od 9 do 15 kolumn, które mogą mieć wartość NULL, wektor bitowy używa dwóch bajtów.) Kolumny, które są NULL, nie zajmują miejsca innego niż bit w tym wektorze . Część nagłówka o zmiennej długości zawiera również długości kolumn o zmiennej długości. Każda długość zajmuje jeden lub dwa bajty, w zależności od maksymalnej długości kolumny. Jeśli wszystkie kolumny w indeksie NIE mają wartości NULL i mają stałą długość, nagłówek rekordu nie ma części o zmiennej długości.
W oparciu o te punkty punktowe, oto, co NULLprzyjmuje wartość dla magazynu kolumny
Teraz musisz zdecydować się na użycie CHAR i VARCHAR ze względu na to, co przyniósł pierwszy punkt
Zarezerwowanie stałej przestrzeni dla wartości NULL umożliwia aktualizację kolumny z NULL na wartość inną niż NULL, aby można ją wykonać bez powodowania fragmentacji strony indeksu
Zapobiegnie to wprowadzaniu fragmentacji wiersza idącego w dół drogi po zapisaniu danych innych niż NULL. Jest to coś, co omówiłem wcześniej w odniesieniu do MyISAM: Zobacz mój stary post Jaki jest wpływ na wydajność używania CHAR vs VARCHAR na polu o stałym rozmiarze? .
SELECTktóry musi utworzyć tabelę tymczasową. Jeśli to możliwe, użyje MEMORY, i przekonwertować VARCHARdo CHARdo tabeli tmp. Teraz VARCHAR(100)zajmuje ustalone 100 (lub 300) bajtów, co prawdopodobnie spowalnia zapytanie.
Bez względu na długość, którą określisz dla swojej kolumny varchar, przestrzeń dyskowa używana przez pustą kolumnę będzie taka sama.
Dotyczy to tylko miejsca używanego przez kolumnę varchar i nie uwzględnia całkowitej przestrzeni dyskowej używanej przez wiersz, jego indeksów, kluczy podstawowych i innych kolumn.
Jak wspomina ypercube w swoim komentarzu, istnieją dodatkowe uwagi dotyczące przechowywania wierszy jako całości, gdy obecna jest co najmniej jedna kolumna z dopuszczalnymi wartościami.
Fizyczna struktura rzędów Innodb
Część nagłówka rekordu o zmiennej długości zawiera wektor bitowy do wskazania NULL kolumn. Jeśli jest gdzieś od 9 do 15 kolumn, które mogą mieć wartość NULL, wektor bitowy używa dwóch bajtów.)
...
Część nagłówka o zmiennej długości zawiera również długości kolumn o zmiennej długości. Każda długość zajmuje jeden lub dwa bajty, w zależności od maksymalnej długości kolumny. Jeśli wszystkie kolumny w indeksie NIE mają wartości NULL i mają stałą długość, nagłówek rekordu nie ma części o zmiennej długości
I tak, używane miejsce do przechowywania zmienia się w zależności od wybranego typu, czy jest stały, czy zmienny, sortowanie i inne czynniki, takie jak silnik.
MySQL zawiera zalecenia dotyczące optymalizacji przechowywania danych: Optymalizacja rozmiaru danych
Aktualizacja
Dodatkowa uwaga związana z varchar i pamięcią. W MySQL ważne jest, aby maksymalnie ograniczyć rozmiar kolumny o zmiennej długości. Mimo że kolumna jest zmienna, a używane miejsce do przechowywania jest zmienne, MySQL przydzieli pamięć w stałych porcjach do przechowywania wartości. Na przykład varchar (200) zużyje więcej pamięci niż varchar (5). To nie jest problem z przestrzenią dyskową, ale wciąż coś do rozważenia przy definiowaniu kolumn.
CHARACTER SETlatin1 lub ascii. W przypadku utf8 wymagana pamięć CHAR(4)to 12.