Primärschlüssel: String vs. Integer

[Karsten Wutzke]

Hallo,

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]

Post by Karsten Wutzke
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.

Post by Karsten Wutzke
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]

Post by Viktor Zacek

Post by Karsten Wutzke
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.

Schon klar. Aber das ist ein anderes Thema... ;-)

Post by Viktor Zacek

Post by Karsten Wutzke
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]

Post by Karsten Wutzke
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]

Post by Kristian Köhntopp

Post by Karsten Wutzke
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.

True.

Post by Kristian Köhntopp
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.

Post by Kristian Köhntopp
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]

[...]

Post by Karsten Wutzke

Post by Kristian Köhntopp
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ß

[...]

Post by Karsten Wutzke
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.

Post by Karsten Wutzke
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

[Kristian Köhntopp]

Post by Karl Pflästerer
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

[Kristian Köhntopp]

Post by Karsten Wutzke
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.

[Karsten Wutzke]

Post by Kristian Köhntopp

Post by Karsten Wutzke
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?

Achso ja, ich habe eine Vererbungsbeziehung zu einer anderen Tabelle
mit diesem String (geplant):

Keywords <- MySqlKeywords, OracleKeywords, ...

Karsten

[Sebastian Suchanek]

Post by Kristian Köhntopp

Post by Karsten Wutzke
[...]
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