MySQL - Primary Key Date,Time vs ID

[J90]

Hallo,

ich verwende aktuell in meinen Kursdaten-Tables eine autoinkrementierte ID (int) als primary key und
2 Attribute date und time als zusätzlichen Index. Zudem existiert der Key UNIQUE(date,time).
Dieser Aufbau entstand zu einem Zeitpunkt, bei welchem ich nicht stark mit SQL bewandert war und nur die
Tables anlegen musste.

Mit neuem Wissen aus meiner Datenbanksysteme-Vorlesung stellt sich mir nun die Frage,
ob nicht eher gelten sollte primary key(date,time) ohne ID.

Wichtig ist, dass Date und Time immer aufsteigend sortiert sind.
Mir ist nun leider unklar, ob die aktuelle Variante (mit ID) oder die Neue effizienter wäre
(automatisch unique date,time durch primary key). Leider gibt es dazu widersprüchliche Aussagen im I-Net.

Soweit ich mich richtig erinnere hatte ich schoneinmal in der Vergangenheit einen Table mit primary key(date,time),
welcher langsamer Fragen verarbeitete.

Hat jemand einen klare Antwort für den Aufbau? Führt das Verwenden eines extra Primary Key (ID) mit Indexen auf Date,Time
zu redundanten Mehraufwand oder ist es im allgemeinen besser als nur primary key(date,time)?

 

[mihe7]

Hat jemand einen klare Antwort für den Aufbau?

In der Regel einen künstlichen PK verwenden. Ein PK identifiziert einen Datensatz, als solcher sollte er sich auch dann nicht ändern, wenn die Inhalte des Satzes geändert werden und das ist bei natürlichen Schlüsseln oft nicht (auf Dauer) der Fall. So kann es z. B. dass der Zeitpunkt eines Kurses verschoben werden muss, was zu einer Änderung des PK führen würde, was wiederum zu Problemen führt, da Fremdschlüsselbeziehungen dies ggf. verhindern.

Wichtig ist, dass Date und Time immer aufsteigend sortiert sind.

Das ist für den PK völlig unerheblich.

Mir ist nun leider unklar, ob die aktuelle Variante (mit ID) oder die Neue effizienter wäre

Effizienter im Hinblick worauf? Was das Einfügen betrifft, bist Du ohne künstlichen PK natürlich schneller, da kein weiterer Index verwaltet werden muss. Umgekehrt ist ein Lookup mit einem geeigneten PK sehr viel effizienter als mit einem zusammengesetzten PK oder einem ungünstigen PK.

 

[J90]

Ok, danke für die Antwort.
Damit werde ich es am besten so lassen wie es nun ist. Da es sehr viele Tables betrifft musste ich mir Gedanken machen..

 

[FrittenFritze]

Zum "autoincrement(int)" als PK: bitte gewöhne es Dir ab, ein INT als PK zu nehmen, das macht Dir jeden Index "kaputt". Warum das so ist, erkennst Du, wenn Du Dir den Aufbau eines B-Tree-Indexes anschaust. Für PKs nimmt man eine sys_guid, bzw. in MySQL nennt sich das Ding UUID().

Nun zu Deiner Frage: die PKs haben normalerweise keine fachliche Bedeutung und dienen nur dazu einen Datensatz zu identifizieren, nicht mehr und nicht weniger, also ein künstlicher Schlüssel, wie mihe7 es schon sagte.

Warum hast Du Date und Time getrennt? Das date() ist doch mit der Uhrzeit in MySQL?

Zusammengesetzt unique Indexes sind absolut okay, solange sie NICHT als Filter- oder Joinkriterium verwendet werden. Das hat was mit dem Zugriffspfad zu tun, ob ein Index-Skip-Scan stattfindet oder nicht.

 

[Thallius]

Zum "autoincrement(int)" als PK: bitte gewöhne es Dir ab, ein INT als PK zu nehmen, das macht Dir jeden Index "kaputt". Warum das so ist, erkennst Du, wenn Du Dir den Aufbau eines B-Tree-Indexes anschaust. Für PKs nimmt man eine sys_guid, bzw. in MySQL nennt sich das Ding UUID().

Die gibt es aber erst seit mySQL v8. Ich glaube 99% aller aktiven mysql DB's sind deutlich daruntern angesiedelt

Gruß

Claus

 

[mihe7]

Zum "autoincrement(int)" als PK: bitte gewöhne es Dir ab, ein INT als PK zu nehmen, das macht Dir jeden Index "kaputt". Warum das so ist, erkennst Du, wenn Du Dir den Aufbau eines B-Tree-Indexes anschaust.

Was hat der B-Tree damit zu tun?

 

[Thallius]

Was hat der B-Tree damit zu tun?

Zumal es jahrzente auch mit INT geht ist mir das auch ein wenig scheleirhaft wieso das jetzt plötzlich Baeh sein soll nur wein ein neuer Typ dafür eingeführt wurde der intern sicher auch nur auf int abgebildet wird (Alles andere würde ja performance kosten)

Aber ich lasse mich gerne eines besseren belehren.

Claus

 

[Xyz1]

INT verwenden, keine esoterischen Schlüssel verwenden und nicht unbedingt auf die (Troll) Antwort von Fritten befolgen, das wäre meine Meinung.

 

[mrBrown]

Das sagen MySQL-Entwicker dazu:

Disadvantages:
[...]
– performance issues: mainly because of the size and not being ordered

[...]
This will have a significant performance impact, since the values will be inserted in random locations in the index tree which will require a lot of IO when the index tree will not fit in memory anymore.

UUID hat durchaus Vorteile, aber das int den Index negativ beeinflusst wäre mir neu...

INT verwenden, keine esoterischen Schlüssel verwenden und nicht unbedingt auf die (Troll) Antwort von Fritten befolgen, das wäre meine Meinung.

UUIDs sind alles andere als "esoterisch", und mit Troll-Vorwürfen solltest grade dazu vorsichtig sein

 

[Xyz1]

Nein, mir ist es egal wenn eine Antwort inhaltlich Sch*** ist, dann sage ich das auch. esoterisch bezog sich auf zusammengesetzte Schlüssel mit denen man nichts anfangen kann.

 

[mihe7]

Habe mir gerade noch ein paar Gedanken gemacht. @FrittenFritze mal ganz dumm gefragt: kann es sein, dass Du den B-Tree mit einem Binärbaum verwechselst?

 

[FrittenFritze]

Was hat der B-Tree damit zu tun?

Wie ist ein B-Tree aufgebaut?
Wo werden im B-Tree neue INT-Werte angehängt?

Zumal es jahrzente auch mit INT geht ist mir das auch ein wenig scheleirhaft wieso das jetzt plötzlich Baeh sein soll nur wein ein neuer Typ dafür eingeführt wurde der intern sicher auch nur auf int abgebildet wird (Alles andere würde ja performance kosten)

Aber ich lasse mich gerne eines besseren belehren.

Claus

Das ist nicht "jetzt plötzlich bäh", es war schon immer scheisse, die INTs als Schlüssel zu verwenden. Die UUIDs gibt es schon ewig, zumindest bei richtigen RDBMS, wenn bei MySQL es neu ist, dann ist es sehr traurig.

Nach einer gewissen Zeit (bzw. größe der Tabelle) ist der Index "kaputt" und langsam. Genau das passiert bei UUIDs nicht. Aber UUIDs scheinen bei MySQL ein Problem zu sein, wenn das Schreiben im Index "a lot of I/O".

@mihe7: Ein Index ist ein binärer Baum, also ein B-Tree. Habe gerade gesehen, dass MySQL auch ein Hash-Index hat, der ist nicht gemeint. Aber danke, dass Du nochmal drüber nachgedacht hast

@Tobias-nrw: Ich betreue seit 17 Jahren Oracle Datenbanken. Die größte (DWH) hat gerade ca 50TB Laufzeitdaten und nochmal 150TB Archivdaten. Wenn Du die Antwort nicht verstehst, ist sie nicht scheisse.

 

[mrBrown]

Nach einer gewissen Zeit (bzw. größe der Tabelle) ist der Index "kaputt" und langsam. Genau das passiert bei UUIDs nicht.

Deine Aussage erklärst du nicht, indem du sie wiederholst warum wird das denn mit Ints langsamer?

Aber UUIDs scheinen bei MySQL ein Problem zu sein, wenn das Schreiben im Index "a lot of I/O".

Ich würde bei anderen DBs das gleiche erwarten (wie relevante das jeweils bei int vs uuid ist, vermag ich nicht zu beurteilen.

Abgesehen von der Größe: Bei ints liegen Daten, die „nah beieinander liegen“ (hintereinander eingefügt, in diesem Fall dann vermutlich auch Zeitstempel nah beieinander), auch im Index nah beieinander, mit UUIDs sind die zufällig verteilt (korrigier mich, falls das nicht stimmt.)
Daten nah beieinander schreiben sollte schneller sein, als Daten weit entfernt zu schreiben, und fürs lesen ebenso, oder nicht?

Index ist ein binärer Baum, also ein B-Tree.

andersrum gilt das aber nicht, B-Tree meint nicht zwingend Binär-Baum. Afaik nutzt MySQL (zumindest InnoDB) auch B+-Trees.

 

[Xyz1]

Ich betreue seit 17 Jahren Oracle Datenbanken

Ist doch ok, aber Dein Auftreten hier macht nicht gerade den seriösesten Eindruck.

Hier ist ein bisschen Theorie:

Indexing in Databases - Set 1 - GeeksforGeeks

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

www.geeksforgeeks.org

Introduction of B+ Tree - GeeksforGeeks

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

www.geeksforgeeks.org

 

[mihe7]

Deine Aussage erklärst du nicht, indem du sie wiederholst warum wird das denn mit Ints langsamer?

Vermutlich geht es um die schlechten ca. 50 % Füllgrad, die im nackten B-Tree durch Einfügen aufeinanderfolgender Werte erreicht wird und die daraus resultierende Höhe.

andersrum gilt das aber nicht, B-Tree meint nicht zwingend Binär-Baum.

Ein Binärbaum meint auch nicht zwingend einen B-Tree

 

[vish234]

It's probably about the bad 50% fill level that is achieved in the bare B-Tree by inserting consecutive values and the resulting height.


A binary tree does not necessarily mean a B-tree either

Correct a B-tree and binary tree differ in that the B-tree is required to have all of its child nodes on the same level, but the binary tree is not. The maximum number of nodes or subtrees in a binary tree is 2, whereas the maximum number of nodes or subtrees in a B-tree is M, where M is the order of the B-tree.

 

[mihe7]

@vish234, yes, moreover a binary tree does not necessarily have to be a search tree. But this thread is more than 3 years old