# MySQL - Primary Key Date,Time vs ID



## J90 (5. Jul 2019)

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 (5. Jul 2019)

J90 hat gesagt.:


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



J90 hat gesagt.:


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


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



J90 hat gesagt.:


> 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 (5. Jul 2019)

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 (9. Jul 2019)

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 (9. Jul 2019)

FrittenFritze hat gesagt.:


> 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 (9. Jul 2019)

FrittenFritze hat gesagt.:


> 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 (9. Jul 2019)

mihe7 hat gesagt.:


> 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 (9. Jul 2019)

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


----------



## mrBrown (9. Jul 2019)

Das sagen MySQL-Entwicker dazu:



			
				https://mysqlserverteam.com/mysql-8-0-uuid-support/ hat gesagt.:
			
		

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




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





Tobias-nrw hat gesagt.:


> 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 (9. Jul 2019)

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 (9. Jul 2019)

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 (9. Jul 2019)

mihe7 hat gesagt.:


> Was hat der B-Tree damit zu tun?



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



Thallius hat gesagt.:


> 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 (9. Jul 2019)

FrittenFritze hat gesagt.:


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




FrittenFritze hat gesagt.:


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



FrittenFritze hat gesagt.:


> 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 (9. Jul 2019)

FrittenFritze hat gesagt.:


> 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 (9. Jul 2019)

mrBrown hat gesagt.:


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



mrBrown hat gesagt.:


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


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


----------



## vish234 (6. Sep 2022)

mihe7 hat gesagt.:


> 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 (11. Sep 2022)

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


----------

