Primärschlüssel: String vs. Integer
Karsten Wutzke
ich habe eine Situation in der sich Strings als natürlicher Schlüssel
anbieten, statt wie jeder heutzutage noch eine zusätzliche ID
einzuführen.
Mir schwebt vor sowas simples wie
CREATE TABLE Keywords
(
keyword VARCHAR(50) PRIMARY KEY
);
vor. Ich habe pro DBMS eine Sub-Tabelle, also
CREATE TABLE MySqlKeywords
(
keyword VARCHAR(50) PRIMARY KEY,
reserved BOOLEAN,
CONSTRAINT fk_MySqlKeywords_Keywords
FOREIGN KEY (keyword)
REFERENCES Keywords (keyword)
ON DELETE CASCADE
ON UPDATE CASCADE
);
Nun meine eigentliche Frage:
Hat es Nachteile einen String als Primärschlüssel zu wählen, also
grundsätzlich? Einen natürlicheren Schlüssel als Keywords kann es
eigentlich kaum geben, somit finde ich eine künstliche ID ein wenig
sinnlos.
Wie sieht es aus mit der Performance String vs. Integer ID's? Und wie
ist der Speicherüberhang? Für DB's mit hoher Last macht das wohl was
aus...
Was meint ihr zu dem Thema?
Karsten
Viktor Zacek
> Hat es Nachteile einen String als Prim�rschl�ssel zu w�hlen, also
> grunds�tzlich? Einen nat�rlicheren Schl�ssel als Keywords kann es
> eigentlich kaum geben, somit finde ich eine k�nstliche ID ein wenig
> sinnlos.
Unter manchen Umst�nden macht es deutlich mehr Sinn.
> Wie sieht es aus mit der Performance String vs. Integer ID's?
Wenn man daran denkt, dass man einen Index anlegt und der auch verwendet
wird, dann ist es kein Problem.
Liebe Gr��e,
Viktor Zacek
Karsten Wutzke
> Karsten Wutzke <kwut...@web.de> wrote:
> > Hat es Nachteile einen String als Primärschlüssel zu wählen, also
> > eigentlich kaum geben, somit finde ich eine künstliche ID ein wenig
> > sinnlos.
>
> Unter manchen Umständen macht es deutlich mehr Sinn.
>
Schon klar. Aber das ist ein anderes Thema... ;-)
> > Wie sieht es aus mit der Performance String vs. Integer ID's?
>
> Wenn man daran denkt, dass man einen Index anlegt und der auch verwendet
> wird, dann ist es kein Problem.
>
> Liebe Grüße,
> Viktor Zacek
Das dachte ich mir eben auch. Der Performance-Aspekt wäre somit
erschlagen. Aber wie sieht es mit dem Speicherverbrauch aus? Ich kenne
mich damit jetzt nicht so aus, aber es müssten dort doch eigentlich
auch intern die Indexstrukturen und nicht die Strings selbst
gespeichert werden oder irre ich mich da?
Karsten
Kristian Köhntopp
> CREATE TABLE Keywords
> (
> keyword VARCHAR(50) PRIMARY KEY
> );
> CONSTRAINT fk_MySqlKeywords_Keywords
> FOREIGN KEY (keyword)
> REFERENCES Keywords (keyword)
> ON DELETE CASCADE
> ON UPDATE CASCADE
> );
> Hat es Nachteile einen String als Prim�rschl�ssel zu w�hlen, also
> grunds�tzlich?
Haufenweise, und wenn Du InnoDB verwendest (ich vermute, da� Du das
tust, weil Du mit Foreign Key Constraints rumspielst), noch ein paar
mehr.
Du schreibst nicht, welchen Zeichensatz Deine Datenbank verwendet. Wenn
das UTF8 ist, ist Deine Schl�ssell�nge im schlimmsten Fall 50 Zeichen,
also bis zu 150 Bytes. Das ist gegen�ber einem INTEGER UNSIGNED, wie
Gott in seiner Inkarnation als Heikki es gewollt hat, ein Mehrverbrauch
von im schlimmsten Fall 146 Bytes.
Dazu kommt, da� in InnoDB die Daten in einem B+-Baum des
Prim�rschl�ssels gespeichert werden. Ein B+-Baum ist ein Baum, in dem
in den Bl�ttern die eigentlichen Datens�tze stehen. Das bedeutet, da�
es in InnoDB immer einen Prim�rschl�ssel gibt und da� die Daten
physikalisch in der Reihenfolge des Prim�rschl�ssels auf die Platte
gemalt werden. Je nachdem, was Du da machst, kann es vorteilhaft oder
nachteilig sein, das in auto_increment Reihenfolge oder
Keyword-Reihenfolge zu machen - auf jeden Fall sollte man es sich
�berlegen und nicht dem Zufall �berlassen.
Die Tiefe Deines B+-Baumes ist zum Teil davon abh�ngig, wie viele
Records man in einen Indexblock bekommt - wenn nur wenige Records in
einen Indexblock (kein Blatt) passen, dann wird der Baum tiefer. Ich
sage 'zum Teil' weil InnoDB mit 16K gro�en Bl�cken recht gro�e Bl�cke
hat, soda� f�r die meisten Anwender die Tiefe entweder 2 oder 3 ist,
solange keine extrem gro�en Indices gew�hlt werden.
Kris
Karsten Wutzke
wrote:
> On 2009-06-09 12:24:08 +0200, Karsten Wutzke <kwut...@web.de> said:
>
> > CREATE TABLE Keywords
> > (
> > keyword VARCHAR(50) PRIMARY KEY
> > );
> > CONSTRAINT fk_MySqlKeywords_Keywords
> > FOREIGN KEY (keyword)
> > REFERENCES Keywords (keyword)
> > ON DELETE CASCADE
> > ON UPDATE CASCADE
> > );
> > grundsätzlich?
>
> tust, weil Du mit Foreign Key Constraints rumspielst), noch ein paar
> mehr.
>
True.
> Du schreibst nicht, welchen Zeichensatz Deine Datenbank verwendet. Wenn
> das UTF8 ist, ist Deine Schlüssellänge im schlimmsten Fall 50 Zeichen,
> also bis zu 150 Bytes. Das ist gegenüber einem INTEGER UNSIGNED, wie
> Gott in seiner Inkarnation als Heikki es gewollt hat, ein Mehrverbrauch
> von im schlimmsten Fall 146 Bytes.
>
Yes, UTF8.
> Dazu kommt, daß in InnoDB die Daten in einem B+-Baum des
> Primärschlüssels gespeichert werden. Ein B+-Baum ist ein Baum, in dem
> in den Blättern die eigentlichen Datensätze stehen. Das bedeutet, daß
> es in InnoDB immer einen Primärschlüssel gibt und daß die Daten
> physikalisch in der Reihenfolge des Primärschlüssels auf die Platte
> gemalt werden. Je nachdem, was Du da machst, kann es vorteilhaft oder
> nachteilig sein, das in auto_increment Reihenfolge oder
> Keyword-Reihenfolge zu machen - auf jeden Fall sollte man es sich
> überlegen und nicht dem Zufall überlassen.
>
> Die Tiefe Deines B+-Baumes ist zum Teil davon abhängig, wie viele
> Records man in einen Indexblock bekommt - wenn nur wenige Records in
> einen Indexblock (kein Blatt) passen, dann wird der Baum tiefer. Ich
> sage 'zum Teil' weil InnoDB mit 16K großen Blöcken recht große Blöcke
> hat, sodaß für die meisten Anwender die Tiefe entweder 2 oder 3 ist,
> solange keine extrem großen Indices gewählt werden.
>
> Kris
Ok muss ich mich nochmal genau iwo einlesen. Was mich nun noch
interessiert: Also speichert MySQL oder nen anderes DBMS für jede
Entität tatsächlich zweimal den String PK ab, einmal in der Basis- und
einmal in der Subtabelle...?
Wenn ich nun von sagen wir 500 verschiedenen Schlüsselwörtern ausgehe
bei einer relativ geringen Last dann komme ich wohl mit der
ursprünglichen Version hin. (?) Wenn aus irgendeinem Grund das
irgendwann nicht mehr funktioniert kann ich ja immer noch den
künstlichen Schlüssel einführen. Hmm...
Karsten
Karl Pflästerer
> On 9 Jun., 15:24, Kristian K�hntopp <kris-usenet2...@koehntopp.de>
> wrote:
[...]
>> Dazu kommt, da� in InnoDB die Daten in einem B+-Baum des
>> Prim�rschl�ssels gespeichert werden. Ein B+-Baum ist ein Baum, in dem
>> in den Bl�ttern die eigentlichen Datens�tze stehen. Das bedeutet, da�
[...]
> Ok muss ich mich nochmal genau iwo einlesen. Was mich nun noch
> interessiert: Also speichert MySQL oder nen anderes DBMS f�r jede
> Entit�t tats�chlich zweimal den String PK ab, einmal in der Basis- und
> einmal in der Subtabelle...?
Nein. Wie Kristian schrieb, h�ngt es (zumindest bei MySQL) von der
verwendeten Engine ab, wie das Indexlayout ist.
> Wenn ich nun von sagen wir 500 verschiedenen Schl�sselw�rtern ausgehe
> bei einer relativ geringen Last dann komme ich wohl mit der
> urspr�nglichen Version hin. (?) Wenn aus irgendeinem Grund das
> irgendwann nicht mehr funktioniert kann ich ja immer noch den
> k�nstlichen Schl�ssel einf�hren. Hmm...
Bei 500 unterschiedlichen W�rtern wirst du wahrscheinlich auch kaum
Unterschiede sehen, ob du jetzt einen Index hast oder nicht. Aber gerade
bei InnoDB ist ein (Surrogat) Integer-PK zumindest f�r das Lesen
vorteilhaft. Bei einer schreibintensiven Tabelle k�nnte es von Vortei
sein, einen Wert als Prim�rschl�ssel zu haben, dessen Werte verteilt
sind (und nicht aufeinanderfolgend wie bei einem auto_increment Feld).
KP
Sebastian Suchanek
>> Hat es Nachteile einen String als Prim�rschl�ssel zu w�hlen, also
>> grunds�tzlich?
>
> Haufenweise, und wenn Du InnoDB verwendest (ich vermute, da� Du das
> tust, weil Du mit Foreign Key Constraints rumspielst), noch ein paar
> mehr.
Ich h�ng' mich an die Frage mal dran, weil ich k�rzlich �hnliche
�berlegungen angestellt habe. Wie s�he es denn mit einem DATE-Feld als
Prim�rschl�ssel aus?
Tsch�s,
Sebastian
Kristian Köhntopp
> Ok muss ich mich nochmal genau iwo einlesen. Was mich nun noch
> interessiert: Also speichert MySQL oder nen anderes DBMS f�r jede
> Entit�t tats�chlich zweimal den String PK ab, einmal in der Basis- und
> einmal in der Subtabelle...?
Was f�r eine Subtabelle?
In InnoDB ist es so, da� der Baum des Prim�rschl�ssels an den Bl�ttern
die eigentlichen Datens�tze enth�lt. Die anderen eventuell vorhandenen
B�ume weitere Schl�ssel in derselben Tabelle enthalten dann den
Prim�rschl�ssel als Datenzeiger.
CREATE TABLE t (
id integer unsigned not null primary key,
d varchar(50) charset latin1 not null default '',
e varchar(50) charset latin1 not null default '',
index (d)
) engine = innodb;
Hier wird der Indexbaum id angelegt, und die Daten werden in der
Tabelle als Bl�tter des id-Baumes abgespeichert, in der durch die Werte
f�r id vorgegebenen physikalischen Reihenfolge.
Es wird ein weiterer Indexbaum d erzeugt. Dazu werden die Paare (d, id)
aus t extrahiert, nach d sortiert und im Baum d gespeichert.
Der Optimizer wei� dies - wenn man EXPLAIN SELECT id FROM t WHERE d =
'...' verwendet, sieht man 'using index' bei InnoDB, bei MyISAM
hingegen weg, weil ein MyISAM-Index d keine Kopie von id enth�lt.
Kristian Köhntopp
> Bei einer schreibintensiven Tabelle k�nnte es von Vortei
> sein, einen Wert als Prim�rschl�ssel zu haben, dessen Werte verteilt
> sind (und nicht aufeinanderfolgend wie bei einem auto_increment Feld).
Bei einer schreibintensiven Tabelle ist es in InnoDB besonders
vorteilhaft ein auto_increment zu haben.
Die Daten werden ja in id-Reihenfolge gespeichert, also in der
Reihenfolge der auto_increment Werte. Dadurch werden also neue Werte
immer am rechten Rand der Tabelle angef�gt, der rechteste Block der
Tabelle wird also nach rechts aufgef�llt und mu� dann in zwei halb
volle Bl�cke gesplittet werden. Das ist der pessimale Fall und f�hrt zu
einer extrem schlecht und ineffizient gepackten Tabelle. Au�erdem mu�
der Index-Baum laufend rebalanciert werden, denn ohne das Rebalancieren
h�tte man ja keinen B+-Baum (einen balancierten Baum), sondern den
degenerierten Fall eines Baumes - die lineare Liste.
InnoDB erkennt das und hat speziellen Code, der diesen Fall erkennt und
in den optimalen Fall umwandelt - wenn der rechteste Block einer
Tabelle wiederholt gesplittet wird, greift die Index Merge Buffer
Optimization.
http://dev.mysql.com/doc/refman/5.0/en/innodb-insert-buffering.html
http://www.mysqlperformanceblog.com/2009/01/13/some-little-known-facts-about-innodb-insert-buffer/
Auf
diese Weise bekommt man 15/16 gef�llte Pages und einen Index-Baum, der
per Konstruktion balanciert ist. Das Einf�gen ist so auch deutlich
schneller.
Kris
Karsten Wutzke
wrote:
> On 2009-06-09 16:23:51 +0200, Karsten Wutzke <kwut...@web.de> said:
>
> > Ok muss ich mich nochmal genau iwo einlesen. Was mich nun noch
> > Entität tatsächlich zweimal den String PK ab, einmal in der Basis- und
> > einmal in der Subtabelle...?
>
> Was für eine Subtabelle?
>
Achso ja, ich habe eine Vererbungsbeziehung zu einer anderen Tabelle
mit diesem String (geplant):
Keywords <- MySqlKeywords, OracleKeywords, ...
Karsten