Najlepsze praktyki dotyczące długości kolumny varchar SQL [zamknięte]

Za każdym razem, gdy tworzona jest nowa tabela SQL lub dodawana jest nowa varcharkolumna do istniejącej tabeli, zastanawiam się jedno: jaka jest najlepsza wartość length.

Powiedzmy, że masz kolumnę nametypu varchar. Musisz więc wybrać długość. Nie mogę wymyślić nazwy> 20 znaków, ale nigdy się nie dowiesz. Ale zamiast 20, zawsze zaokrąglam w górę do następnej 2 ^ n liczby. W tym przypadku wybrałbym 32 jako długość. Robię to, ponieważ z punktu widzenia informatyków liczba 2 ^ n wygląda bardziej evenna mnie niż na inne liczby i po prostu zakładam, że architektura poniżej może obsłużyć te liczby nieco lepiej niż inne.

Z drugiej strony na przykład serwer MSSQL ustawia domyślną wartość długości na 50, gdy zdecydujesz się utworzyć kolumnę varchar. To sprawia, że ​​o tym myślę. Dlaczego 50 czy to tylko liczba losowa, czy oparta na średniej długości kolumny, czy co?

Może być również - lub prawdopodobnie jest - że różne implementacje serwerów SQL (takie jak MySQL, MSSQL, Postgres, ...) mają różne najlepsze wartości długości kolumn.

Żaden DBMS, o którym wiem, nie ma żadnej „optymalizacji”, która sprawi, że a VARCHARprzy 2^ndługości będzie działać lepiej niż ta o maxdługości, która nie jest potęgą 2.

Myślę, że wczesne wersje programu SQL Server traktowały VARCHARdługość 255 inaczej niż ta o większej maksymalnej długości. Nie wiem, czy nadal tak jest.

W przypadku prawie wszystkich DBMS rzeczywista wymagana pamięć zależy tylko od liczby wprowadzonych znaków, a nie od maxzdefiniowanej długości. Zatem z punktu widzenia przechowywania (i prawdopodobnie również wydajności) nie ma znaczenia, czy deklarujesz kolumnę jako VARCHAR(100)czy VARCHAR(500).

maxDługość VARCHARkolumny powinna być postrzegana raczej jako rodzaj ograniczenia (lub reguły biznesowej) niż rzecz techniczna / fizyczna.

W przypadku PostgreSQL najlepszą konfiguracją jest użycie textbez ograniczenia długości i CHECK CONSTRAINTznaku, który ogranicza liczbę znaków do wszystkiego, czego wymaga Twoja firma.

Jeśli to wymaganie ulegnie zmianie, zmiana ograniczenia sprawdzania jest znacznie szybsza niż zmiana tabeli (ponieważ tabela nie musi być ponownie zapisywana)

To samo można zastosować do Oracle i innych - w Oracle byłoby to jednak VARCHAR(4000)zamiast text.

Nie wiem, czy istnieje fizyczna różnica w przechowywaniu między VARCHAR(max)np. VARCHAR(500)W SQL Server. Ale najwyraźniej ma to wpływ na wydajność podczas korzystania varchar(max)w porównaniu do varchar(8000).

Zobacz ten link (opublikowany przez Erwina Brandstettera jako komentarz)

Edytuj 22.09.2013

Odnośnie komentarza bigown:

W wersji PostgreSQL 9.2 przed (który nie był dostępny kiedy pisałem wstępną odpowiedź) zmiana definicji kolumny zrobił przepisać całą tabelę, patrz na przykład tutaj . Od wersji 9.2 już tak nie jest, a szybki test potwierdził, że zwiększenie rozmiaru kolumny dla tabeli z 1,2 milionami wierszy rzeczywiście zajęło tylko 0,5 sekundy.

W przypadku Oracle wydaje się to również prawdą, sądząc po czasie potrzebnym na zmianę varcharkolumny dużego stołu . Ale nie mogłem znaleźć na to odniesienia.

W przypadku MySQL instrukcja mówi: „ W większości przypadków ALTER TABLEtworzy tymczasową kopię oryginalnej tabeli ”. I moje własne testy to potwierdzają: uruchomienie ALTER TABLEtabeli z 1,2 milionami wierszy (tak samo jak w moim teście z Postgres), aby zwiększyć rozmiar kolumny, zajęło 1,5 minuty. W MySQL nie można jednak użyć „obejścia”, aby użyć ograniczenia sprawdzania w celu ograniczenia liczby znaków w kolumnie.

W przypadku programu SQL Server nie mogłem znaleźć jednoznacznej instrukcji na ten temat, ale czas wykonania w celu zwiększenia rozmiaru varcharkolumny (ponownie powyższej tabeli 1,2 miliona wierszy) wskazuje, że nie ma miejsca żadne przepisywanie.

Edytuj 24.01.2017

Wygląda na to, że myliłem się (przynajmniej częściowo) w kwestii programu SQL Server. Zobacz tę odpowiedź Aarona Bertranda, która pokazuje, że zadeklarowana długość a nvarcharlub varcharkolumn ma ogromne znaczenie dla wydajności.

VARCHAR(255)i VARCHAR(2)zajmij dokładnie taką samą ilość miejsca na dysku! Więc jedynym powodem, aby to ograniczyć, jest to, że istnieje szczególna potrzeba, aby była mniejsza. W przeciwnym razie ułóż je wszystkie 255.

W szczególności, podczas sortowania, większa kolumna zajmuje więcej miejsca, więc jeśli to negatywnie wpływa na wydajność, musisz się tym martwić i zmniejszyć. Ale jeśli kiedykolwiek wybierzesz tylko 1 wiersz z tej tabeli, możesz po prostu zrobić wszystkie 255 i to nie będzie miało znaczenia.

Zobacz: Jakie są optymalne rozmiary varchar dla MySQL?

Ilekroć konfiguruję nową tabelę SQL, czuję w ten sam sposób, że 2 ^ n jest bardziej „parzysty” ... ale podsumowując odpowiedzi tutaj, nie ma znaczącego wpływu na przestrzeń dyskową po prostu poprzez zdefiniowanie varchar (2 ^ n) a nawet varchar (MAX).

To powiedziawszy, powinieneś nadal przewidywać potencjalne konsekwencje dla przechowywania i wydajności przy ustawianiu wysokiego limitu varchar (). Załóżmy na przykład, że tworzysz kolumnę varchar (MAX) do przechowywania opisów produktów z indeksowaniem pełnotekstowym. Jeśli 99% opisów ma tylko 500 znaków, a następnie nagle pojawia się ktoś, kto zamienia wspomniane opisy na artykuły z wikipedii, możesz zauważyć nieprzewidziane znaczące straty pamięci i wydajności.

Kolejna rzecz do rozważenia od Billa Karwina :

Istnieje jeden możliwy wpływ na wydajność: w MySQL tabele tymczasowe i MEMORY przechowują kolumnę VARCHAR jako kolumnę o stałej długości, uzupełnioną do maksymalnej długości. Jeśli projektujesz kolumny VARCHAR znacznie większe niż największy wymagany rozmiar, zużyjesz więcej pamięci niż musisz. Wpływa to na wydajność pamięci podręcznej, szybkość sortowania itp.

Zasadniczo po prostu wymyśl rozsądne ograniczenia biznesowe i błąd na nieco większym rozmiarze. Jak zauważył @onayay, jak wskazano, nazwiska w Wielkiej Brytanii mają zwykle od 1 do 35 znaków. Jeśli zdecydujesz się uczynić go varchar (64), tak naprawdę nic nie skrzywdzisz ... chyba że przechowujesz nazwisko tego faceta o długości do 666 znaków. W takim przypadku może varchar (1028) ma większy sens.

A jeśli jest to pomocne, oto jak może wyglądać varchar 2 ^ 5 do 2 ^ 10, jeśli jest wypełniony:

varchar(32)     Lorem ipsum dolor sit amet amet.

varchar(64)     Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie

varchar(128)    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
                vestibulum massa. Nullam dignissim elementum molestie. Vehiculas

varchar(256)    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
                vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
                velit metus, sit amet tristique purus condimentum eleifend. Quis
                que mollis magna vel massa malesuada bibendum. Proinde tincidunt

varchar(512)    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
                vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
                velit metus, sit amet tristique purus condimentum eleifend. Quis
                que mollis magna vel massa malesuada bibendum. Proinde tincidunt
                dolor tellus, sit amet porta neque varius vitae. Seduse molestie
                lacus id lacinia tempus. Vestibulum accumsan facilisis lorem, et
                mollis diam pretium gravida. In facilisis vitae tortor id vulput
                ate. Proin ornare arcu in sollicitudin pharetra. Crasti molestie

varchar(1024)   Lorem ipsum dolor sit amet, consectetur adipiscing elit. Donecie
                vestibulum massa. Nullam dignissim elementum molestie. Vehiculas
                velit metus, sit amet tristique purus condimentum eleifend. Quis
                que mollis magna vel massa malesuada bibendum. Proinde tincidunt
                dolor tellus, sit amet porta neque varius vitae. Seduse molestie
                lacus id lacinia tempus. Vestibulum accumsan facilisis lorem, et
                mollis diam pretium gravida. In facilisis vitae tortor id vulput
                ate. Proin ornare arcu in sollicitudin pharetra. Crasti molestie
                dapibus leo lobortis eleifend. Vivamus vitae diam turpis. Vivamu
                nec tristique magna, vel tincidunt diam. Maecenas elementum semi
                quam. In ut est porttitor, sagittis nulla id, fermentum turpist.
                Curabitur pretium nibh a imperdiet cursus. Sed at vulputate este
                proin fermentum pretium justo, ac malesuada eros et Pellentesque
                vulputate hendrerit molestie. Aenean imperdiet a enim at finibus
                fusce ut ullamcorper risus, a cursus massa. Nunc non dapibus vel
                Lorem ipsum dolor sit amet, consectetur Praesent ut ultrices sit

Najlepsza wartość to ta, która jest odpowiednia dla danych zdefiniowanych w domenie podstawowej.

Dla niektórych domen VARCHAR(10)jest odpowiedni dla Nameatrybutu, dla innych domen VARCHAR(255)może być najlepszym wyborem.

Dodając do odpowiedzi konia bez nazwy, możesz znaleźć następujące interesujące ...

nie ma znaczenia, czy deklarujesz kolumnę jako VARCHAR (100) czy VACHAR (500).

-- try to create a table with max varchar length
drop table if exists foo;
create table foo(name varchar(65535) not null)engine=innodb;

MySQL Database Error: Row size too large.

-- try to create a table with max varchar length - 2 bytes for the length
drop table if exists foo;
create table foo(name varchar(65533) not null)engine=innodb;

Executed Successfully

-- try to create a table with max varchar length with nullable field
drop table if exists foo;
create table foo(name varchar(65533))engine=innodb;

MySQL Database Error: Row size too large.

-- try to create a table with max varchar length with nullable field
drop table if exists foo;
create table foo(name varchar(65532))engine=innodb;

Executed Successfully

Nie zapomnij o bajcie długości i bajcie null, więc:

name varchar(100) not null będzie 1 bajt (długość) + do 100 znaków (latin1)

name varchar(500) not null będą mieć 2 bajty (długość) + do 500 znaków (latin1)

name varchar(65533) not null będzie mieć 2 bajty (długość) + do 65533 znaków (latin1)

name varchar(65532) będzie mieć 2 bajty (długość) + do 65532 znaków (latin1) + 1 bajt zerowy

Mam nadzieję że to pomoże :)

Zawsze skonsultuj się z ekspertem od domeny biznesowej. Jeśli to ty, poszukaj standardu branżowego. Jeśli na przykład domeną jest nazwisko osoby (nazwisko) osoby fizycznej, to w przypadku brytyjskiej firmy przejdę do katalogu standardów danych Govtalk w Wielkiej Brytanii w celu uzyskania informacji o osobie i odkryję, że nazwisko będzie mieć od 1 do 35 znaków .

Nie sprawdzałem tego ostatnio, ale wiem w przeszłości z Oracle, że sterownik JDBC zarezerwowałby część pamięci podczas wykonywania zapytania, aby zatrzymać zestaw wyników wracający. Rozmiar fragmentu pamięci zależy od definicji kolumny i wielkości pobierania. Zatem długość kolumn varchar2 wpływa na ilość zarezerwowanej pamięci. Spowodowało to dla mnie poważne problemy z wydajnością lata temu, ponieważ zawsze używaliśmy varchar2 (4000) (maks. W tym czasie), a odśmiecanie było znacznie mniej wydajne niż obecnie.

W pewnym sensie masz rację, chociaż cokolwiek mniejszego niż 2 ^ 8 znaków nadal będzie rejestrowane jako bajt danych.

Jeśli uwzględnisz postać podstawową, która pozostawia cokolwiek z VARCHAR <255, ponieważ zajmuje tyle samo miejsca.

255 jest dobrą definicją wyjściową, chyba że szczególnie chcesz ograniczyć nadmierne nakłady.