String vs. integer als PRIMARY KEY und Query Performance

Hallo Leute,

ich hab mal ne Frage:

Nehmen wir an, ich habe zwei Tabellen. Eine hat eine Int (INTEGER) als Primary Key, und die andere eine String (TEXT) als Primary Key. Zudem lege ich noch bei beiden der Tabellen auf den Primary Key jeweils noch einen Index anlege. (was aber die primary key Spalte implizit macht!?!?)

Wie sieht es mit der Query Performance aus, wenn ich eine Query jeweils in der where Bedienung nach primary Key suche.

Handelt die Datenbank String-Indexe intern numerisch über hash ids etc.? was so keine Performance unterscheide im verlgeich zur Integer-Index macht?

Indexe sind ja Binary-Trees oder?

Grüße

P.S. ich verwende sqlite.

hustbaer

Indexe sind B-Bäume bzw. Derivate davon (B+, B*, ...).

Der Primary-Key hat auch implizit bei allen Systemen die ich kenne bereits einen Index, d.h. du musst nicht noch zusätzlich einen anlegen.

----

Ich gehe jetzt davon aus dass deine Strings im Durchschnitt länger wären als die Integers (z.B. 4 Byte Int vs. Strings mit durchschnittlich 10+ Bytes).

Dann wird die String-Variante schonmal sicher nicht schneller sein als mit Integers. Die Frage ist nur: wie viel langsamer ist es wirklich?

Und da spielen jetzt viele Faktoren mit rein - zu viele, als dass ich hier eine wirklich sinnvolle Antwort geben könnte.

EDIT: achso, DIE Frage kann ich noch klar beantworten...

Handelt die Datenbank String-Indexe intern numerisch über hash ids etc.? was so keine Performance unterscheide im verlgeich zur Integer-Index macht?

Nein. Ein System bei dem String-Indexe unter keinen Umständen einen Performance-Nachteil gegenüber Integer-Indexen haben können, kann es nicht geben.

Unix-Tom

Den Nachteil bei STring-Index sieht man nicht beim SELECT. Man sieht ihn mein UPDATE/INSERT.

Sowas sollte man nicht machen wobei es auch auf den Text ankommt der da drin ist.
Eine Tabelle mit PLZ und Ortsnamen macht man es schon.

Ist der Text aber z.B. HTML und speicher man da HTML-Seiten dann ... den Rest kann man sich denken wielange das RDBMS braucht um jedesmal den Index neu anzulegen.

BadWolf

Verwendet SQLite nicht sowieso intern ausschliesslich Strings? Habe mich schon ewig nicht mehr damit befasst, aber irgendwie habe ich da noch sowas im Hinterkopf.

hustbaer

@BadWolf:
SQLite verwendet schon Datentypen, bloss nicht pro Spalte sondern pro Wert. D.h. Integers stehen nicht als String in der DB, sondern wirklich als "echte" Integer.
Es geht aber primär um die Grösse der Index-Einträge, und Strings können eben im Vergleich zu Integers recht gross werden.

@Unix-Tom:
Wieso Index neu anlegen? Das ist ein B-Baum, da wird einfach eingefügt/rausgelöscht, und nicht jedes mal der Index neu angelegt.

Klar, je grösser die Keys sind, desto mehr Daten müssen beim Einfügen/Rauslöschen rumgeschoben werden, desto mehr IO Kosten fallen an.

Ist aber bei SELECT grundsätzlich das selbe. Je grösser der Index, desto tiefer wird er, desto länger dauert das SELECT.

Schwierig werden Performance-Abschätzungen halt u.A. dadurch, dass die Datenbank nen Cache verwendet. So lange der Index komplett in den Cache passt, wird man bei einfachen SELECTs kaum nen Unterschied merken.
Wenn es aber mal soweit ist, dass der Index eben nicht mehr komplett in den Cache passt, dann bricht die Performance rasant ein. Und an dem Punkt ist man eben schneller, je grösser die Index Keys sind.

Und selbst wenn man nen String-Index der nicht mehr in den Cache passt mit einem Integer-Index der nicht mehr in den Cache passt vergleicht, wird immer noch der Integer-Index schneller sein. Weil er eben immer noch kleiner ist. D.h. er hat weniger Ebenen, und auch der Hit/Miss Ratio wird immer noch besser aussehen als mit dem String Index.

Ein weiterer Punkt der Performance kosten kann sind die String-Vergleiche selbst. Nämlich dann, wenn man mit einer "nicht trivialen" Collation arbeitet. Also z.B. irgendwas was case-insensitive ist. Oder auch nur Unicode-Collations die verschiedene binäre Darstellungen des selben Strings als "gleiche" behandelt.
Dann werden die grösser/kleiner/gleich Vergleiche nämlich auf einmal ordentlich teuer. Wenn man dann über solche Spalten nen JOIN macht, kann das ordentlich zuschlagen.
Wobei sich das auch in einer Grössenordnung abspielt, die man nur merkt, wenn der Index komplett im Cache ist. Wenn wirklich physikalisch IO gemacht werden muss, dann geht das mehr oder weniger in den IO Kosten unter.

Bei SQLite ist das allerdings kein Thema, da SQLite einfach nicht "schlau genug" beim Strings Vergleichen ist.