Bewährte Praktiken für SQL varchar-Spaltenlänge

Question

Jedes Mal, wenn Sie eine neue SQL-Tabelle einrichten oder eine neue varchar Spalte in eine bestehende Tabelle einfügen möchte, frage ich mich eines: Was ist der beste Wert für die length .

Nehmen wir also an, Sie haben eine Spalte namens name vom Typ varchar . Sie müssen also die Länge wählen. Mir fällt kein Name ein, der mehr als 20 Zeichen hat, aber man kann ja nie wissen. Aber anstatt 20 zu verwenden, runde ich immer auf die nächste 2^n-Zahl auf. In diesem Fall würde ich 32 als Länge wählen. Ich tue das, weil aus der Sicht eines Informatikers eine Zahl 2^n besser aussieht even Ich gehe einfach davon aus, dass die zugrunde liegende Architektur mit diesen Zahlen etwas besser umgehen kann als mit anderen.

Auf der anderen Seite setzt z.B. MSSQL Server den Standardlängenwert auf 50, wenn Sie eine varchar-Spalte erstellen wollen. Das macht mich nachdenklich. Warum 50? Ist es nur eine zufällige Zahl, oder basiert sie auf der durchschnittlichen Spaltenlänge, oder was?

Es könnte auch sein - oder ist wahrscheinlich - dass verschiedene SQL-Server-Implementierungen (wie MySQL, MSSQL, Postgres, ...) unterschiedliche beste Spaltenlängenwerte haben.

Answer 1

Kein mir bekanntes DBMS hat eine "Optimierung", die eine VARCHAR mit einer 2^n Länge besser abschneiden als eine mit einer max Länge, die nicht eine Potenz von 2 ist.

Ich glaube, frühe SQL Server-Versionen behandelten tatsächlich eine VARCHAR mit der Länge 255 anders als eine mit einer höheren Maximallänge. Ich weiß nicht, ob dies immer noch der Fall ist.

Bei fast allen DBMS hängt der tatsächlich benötigte Speicherplatz nur von der Anzahl der Zeichen ab, die Sie eingeben, nicht von der max Länge definieren Sie. Unter dem Gesichtspunkt der Speicherung (und wahrscheinlich auch der Leistung) macht es also keinen Unterschied, ob Sie eine Spalte als VARCHAR(100) o VARCHAR(500) .

Sie sollten die max Länge vorgesehen für eine VARCHAR Spalte als eine Art Einschränkung (oder Geschäftsregel) und nicht als eine technische/physikalische Sache.

Für PostgreSQL ist die beste Einstellung die Verwendung von text ohne Längenbeschränkung und eine CHECK CONSTRAINT die die Anzahl der Zeichen auf das begrenzt, was Ihr Unternehmen benötigt.

Wenn sich diese Anforderung ändert, ist die Änderung der Prüfbeschränkung viel schneller als die Änderung der Tabelle (weil die Tabelle nicht neu geschrieben werden muss).

Das Gleiche gilt für Oracle und andere - bei Oracle wäre es VARCHAR(4000) anstelle von text Allerdings.

Ich weiß nicht, ob es einen Unterschied in der physischen Speicherung gibt zwischen VARCHAR(max) und z.B. VARCHAR(500) in SQL Server. Aber offenbar gibt es Leistungseinbußen bei der Verwendung von varchar(max) im Vergleich zu varchar(8000) .

Siehe dieser Link (veröffentlicht von Erwin Brandstetter als Kommentar)

Bearbeiten 2013-09-22

Zum Kommentar von bigown:

In Postgres-Versionen vor 9.2 (die noch nicht verfügbar waren, als ich die erste Antwort schrieb) ist eine Änderung der Spaltendefinition hat die gesamte Tabelle neu zu schreiben, siehe z.B. aquí . Seit Version 9.2 ist dies nicht mehr der Fall, und ein kurzer Test bestätigte, dass die Erhöhung der Spaltengröße für eine Tabelle mit 1,2 Millionen Zeilen tatsächlich nur 0,5 Sekunden dauerte.

Für Oracle scheint dies ebenfalls zu gelten, wenn man die Zeit betrachtet, die es braucht, um die Daten einer großen Tabelle zu ändern. varchar Spalte. Ich konnte jedoch keinen Hinweis darauf finden.

Für MySQL das Handbuch sagt " In den meisten Fällen, ALTER TABLE erstellt eine temporäre Kopie der Originaltabelle ". Und meine eigenen Tests bestätigen das: Die Ausführung eines ALTER TABLE bei einer Tabelle mit 1,2 Millionen Zeilen (wie in meinem Test mit Postgres), um die Größe einer Spalte zu erhöhen, 1,5 Minuten. In MySQL hingegen können Sie no Verwenden Sie die "Abhilfe", um die Anzahl der Zeichen in einer Spalte durch eine Prüfbeschränkung zu begrenzen.

Für SQL Server konnte ich keine klare Aussage dazu finden, aber die Ausführungszeit für die Vergrößerung einer varchar Spalte (wieder die Tabelle mit den 1,2 Millionen Zeilen von oben) zeigt, dass keine umgeschrieben wird.

Bearbeiten 2017-01-24

Es scheint, dass ich mich (zumindest teilweise) in Bezug auf SQL Server geirrt habe. Siehe diese Antwort von Aaron Bertrand die zeigt, dass die angegebene Länge einer nvarchar o varchar Spalten macht einen großen Unterschied für die Leistung.

Answer 2

VARCHAR(255) y VARCHAR(2) Nehmen Sie genau die gleiche Menge an Speicherplatz auf der Festplatte! Der einzige Grund, ihn zu begrenzen, ist also, wenn Sie ihn unbedingt kleiner haben wollen. Andernfalls machen Sie sie alle 255.

Insbesondere beim Sortieren nehmen größere Spalten mehr Platz ein. Wenn das die Leistung beeinträchtigt, sollten Sie sich darum kümmern und die Spalten verkleinern. Wenn Sie aber nur eine Zeile aus der Tabelle auswählen, können Sie sie einfach auf 255 verkleinern, und es spielt keine Rolle.

Siehe: Was sind die optimalen varchar-Größen für MySQL?

Answer 3

Jedes Mal, wenn ich eine neue SQL-Tabelle einrichte, habe ich das gleiche Gefühl, dass 2^n "gleichmäßiger" ist... aber um die Antworten hier zusammenzufassen, gibt es keine signifikanten Auswirkungen auf den Speicherplatz, wenn man einfach varchar(2^n) oder sogar varchar(MAX) definiert.

Dennoch sollten Sie bei der Festlegung einer hohen varchar()-Grenze die möglichen Auswirkungen auf Speicherplatz und Leistung berücksichtigen. Angenommen, Sie erstellen eine varchar(MAX)-Spalte, um Produktbeschreibungen mit Volltextindizierung zu speichern. Wenn 99 % der Beschreibungen nur 500 Zeichen lang sind und Sie dann plötzlich jemanden haben, der diese Beschreibungen durch Wikipedia-Artikel ersetzt, könnten Sie unvorhergesehene erhebliche Speicher- und Leistungseinbußen feststellen.

Ein weiterer Hinweis von Bill Karwin :

Es gibt eine mögliche Auswirkung auf die Leistung: In MySQL speichern temporäre Tabellen und MEMORY-Tabellen eine VARCHAR-Spalte als eine Spalte mit fester Länge speichern, die auf ihre maximale Länge aufgefüllt wird. Wenn Sie VARCHAR-Spalten entwerfen, die viel größer als die größte benötigte Größe entwerfen, werden Sie mehr Speicher verbrauchen als Sie benötigen. Dies beeinträchtigt die Effizienz des Cache, die Sortiergeschwindigkeit usw.

Im Grunde genommen sollten Sie sich einfach vernünftige geschäftliche Beschränkungen einfallen lassen und eine etwas größere Größe wählen. Wie @onedaywhen schon sagte, sind Familiennamen im Vereinigten Königreich normalerweise zwischen 1-35 Zeichen lang. Wenn Sie sich entscheiden, sie varchar(64) zu machen, werden Sie nicht wirklich etwas verletzen... außer Sie speichern der Familienname dieses Mannes die bis zu 666 Zeichen lang sein soll. In diesem Fall macht vielleicht varchar(1028) mehr Sinn.

Und falls es hilfreich ist, hier ist, wie varchar 2^5 bis 2^10 aussehen könnte, wenn sie gefüllt sind:

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

Answer 4

Der beste Wert ist derjenige, der für die Daten, wie sie im zugrunde liegenden Bereich definiert sind, geeignet ist.

Für einige Domänen, VARCHAR(10) ist das Richtige für die Name Attribut, für andere Domänen VARCHAR(255) könnte die beste Wahl sein.

Answer 5

Ergänzend zur Antwort von a_horse_with_no_name könnte Sie auch Folgendes interessieren...

macht es keinen Unterschied, ob Sie eine Spalte als VARCHAR(100) oder VACHAR(500) deklarieren.

-- 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

Vergessen Sie also nicht das/die Längenbyte(s) und das nullbare Byte:

name varchar(100) not null wird 1 Byte (Länge) + bis zu 100 Zeichen (latin1) sein

name varchar(500) not null wird 2 Byte (Länge) + bis zu 500 Zeichen (latin1) sein

name varchar(65533) not null wird 2 Bytes (Länge) + bis zu 65533 Zeichen (latin1) sein

name varchar(65532) 2 Bytes (Länge) + bis zu 65532 Zeichen (latin1) + 1 Null-Byte

Ich hoffe, das hilft :)