Forum: PC-Programmierung Das hier ist doch ein MySQL-Forum


von Winfried (Gast)


Lesenswert?

In einer Datenbank sollen von relativ vielen Datenquellen (anhand ihrer 
id durchnummeriert) jeweils Zeit und ein paar Temperaturen gespeichert 
werden. Also jede Zeile besteht vereinfacht aus

id, time, temp1, temp2, temp3

So weit so gut. Zum Darstellen der Daten wird immer für eine bestimmte 
id ein bestimmter Zeitraum rausgefischt. Also steht in der Abfrage sowas 
wie "where id=7 and time>min and time<max". Am Ende wird noch sortiert: 
"order by time".

Das Ganze soll jetzt optimiert werden, damit die Abfrage schneller wird. 
Ich habe dazu einen Index (primary) über id und time erstellt. Ist das 
korrekt? Mich macht stutzig, dass der Index genau so viel Platz wegnimmt 
wie die Daten. Also der Speicherplatz hat sich gerade mal fast 
verdoppelt. Bringt dann diese Optimierung so überhaupt etwas? Andere 
Ideen?

von Andreas B. (bitverdreher)


Lesenswert?

Stimmt schon so.

von Purzel H. (hacky)


Lesenswert?

Und weshalb willst du Speicherplatz vorhalten ? Die Werte koennen doch 
waerend des Suchens verglichen werden. Man benoetigt dann nur die 
gueltigen Eintraege.

von Theor (Gast)


Lesenswert?

Unter Vorbehalt, da ich zwar Erfahrungen mit SQL resp. Datenbanken habe, 
aber diese Frage eher aus theoretischer Sicht beantworte.

Es kommt darauf an, wieviele individuelle Ids und Zeiten es gibt.
(Wohlgemerkt: "individuelle"! D.h. Egal, wie oft eine Id, - d.h. in wie 
vielen Datensätzen -, auftritt, sie zählt einmal als Individum. Genauso 
mit den Zeiten.) Das lässt sich auch mit einer Abfrage ermitteln. Weiß 
nur leider gerade die Syntax nicht.

Nimm einmal an, Du hättest n Datensätze. Je näher die Anzahl der Ids und 
Zeiten an n heranrückt, desto grösser wird der Index im Verhältnis zur 
ursprünglichen Datengrösse. Vermutlich gibt es also in Deinem Fall recht 
viele Individuen.

von Winfried (Gast)


Lesenswert?

Noch muss ich keinen Platz sparen, aber mich macht die ganze Sache 
stutzig, weil ja trotz Index doch fast alles (d.h. von der Datenmenge 
her) durchsucht werden muss.
Also der Index hält quasi die Tabelle nochmal vor (aber halt ohne die 
paar Bytes für die Temperaturen und sortiert). So stelle ich mir das 
momentan vor.

von eProfi (Gast)


Lesenswert?

Wenn die Daten nach der Zeit sortiert sind, kannst Du eine recht 
schnelle binäre Suche machen.

Doppelt verkettete Liste könnte auch helfen. Alles Standard-Kram,

von Jan H. (j_hansen)


Lesenswert?

Winfried schrieb:
> Noch muss ich keinen Platz sparen, aber mich macht die ganze Sache
> stutzig, weil ja trotz Index doch fast alles (d.h. von der Datenmenge
> her) durchsucht werden muss.

Eben nicht. Ein Telefonbuch ohne Sortierung ist genauso dick wie ein 
alphabetisch sortiertes. Und jetzt such mal in beiden alle "Meier" raus.

von Johannes S. (Gast)


Lesenswert?

Winfried schrieb:
> Andere Ideen?

es gibt optimierte DB für zeitbasierte Daten, z.B. InfluxDB. Die ist 
auch bei sehr vielen Daten sehr schnell. Und ältere Daten können über 
die Konfiguration verdichtet werden um Platz zu sparen.
Mit einem Frontend wie Grafana kann man die Daten sehr einfach per 
Webbrowser anzeigen.

von Winfried (Gast)


Lesenswert?

Jan H. schrieb:
> Eben nicht. Ein Telefonbuch ohne Sortierung ist genauso dick wie ein
> alphabetisch sortiertes. Und jetzt such mal in beiden alle "Meier" raus.

Um mal bei dieser Analogie zu bleiben (die ich sehr gut finde, weil ich 
damit verdeutlichen kann, was ich meine):
Ich benutze ausschließlich das alphabetisch sortierte Telefonbuch und 
suche eine Möglichkeit, das andere wegzuwerfen, da es nur Platz 
verbraucht.

von (prx) A. K. (prx)


Lesenswert?

Winfried schrieb:
> Bringt dann diese Optimierung so überhaupt etwas? Andere
> Ideen?

Wenn es beispielsweise 5 Sensoren gibt, die über "id" gekennzeichnet 
werden, und mit der Zeit 100 Mio Einträge zusammen kommen, über alle 5 
Sensoren gleichverteilt, dann bringt ein Index für "id" wenig, da bei 
einem einfachen "WHERE id=..." immer zig Mio Einträge betroffen sind.

Wenn andererseits die Abfragen stets auch ein "WHERE time>... AND 
time<..." enthalten, und damit ein gegenüber der Anzahl Einträge 
insgesamt nur kleiner Zeitraum betroffen ist, dann ist ein Index für 
"time" wesentlich hilfreicher. Deine oben skizzierte kombinierte Abfrage 
wird dann über den Index für "time" gehen und erst in den Ergebnissen 
davon nach der "id" suchen. Ein Index für "id" hilft dann überhaupt 
nicht.

von Winfried (Gast)


Lesenswert?

Wie gesagt ist der Index über (id, time). Da id in der Abfrage konstant 
ist, bleibt time für eine Range-Abfrage (und auch das Sortieren) nutzbar 
(wenn ich die ganzen Tutorials für Indices richtig verstanden habe).

von (prx) A. K. (prx)


Lesenswert?

Winfried schrieb:
> Wie gesagt ist der Index über (id, time). Da id in der Abfrage konstant
> ist, bleibt time für eine Range-Abfrage (und auch das Sortieren) nutzbar
> (wenn ich die ganzen Tutorials für Indices richtig verstanden habe).

Insgesamt stimmts, wenngleich es aus ähnlichem wie vorhin skizziertem 
Grund etwas effektiver sein dürfte, den Index über (time,id) statt 
(id,time) anzulegen. Ein solcher Index ist dann auch für Abfragen ohne 
"id" hilfreich.

Es kann allerdings bei stets kleinem Zeitraum in der Abfrage 
mlglicherweise auch völlig ausreichen, nur über "time" zu indizieren und 
im Ergebnis zu suchen. Wenn es darum geht, den Platz zu optimieren. Wird 
aber bei kleinem Datentyp für "id" nicht viel Unterschied ausmachen.

: Bearbeitet durch User
von (prx) A. K. (prx)


Lesenswert?

Winfried schrieb:
> Am Ende wird noch sortiert:
> "order by time".

Mit "time" im Index und (time,id) oder id=N kommt das schon sortiert aus 
der Abfrage raus. Der Optimizer wird das hoffentlich merken und sich 
eine separate Sortierung ersparen.

> Mich macht stutzig, dass der Index genau so viel Platz wegnimmt
> wie die Daten.

Es ist völlig normal, wenn bei Datenbanken die Indexe grob ähnlich viel 
Platz wegnehmen wie die Daten.

> Bringt dann diese Optimierung so überhaupt etwas?

Den Platz für die Daten und den Platz für die Indexe darfst du nicht 
einfach addieren, wenn es um Abschätzung des Aufwands einer Abfrage 
geht. Wenn eine Abfrage mangels passendem Index über die gesamte Tabelle 
geht, zählt nur der Platz der Daten, nicht der vom Index.

: Bearbeitet durch User
von Winfried (Gast)


Lesenswert?

Ich habe es mit EXPLAIN schon ausgetüftelt, wie es optimal ist. (time, 
id) macht aus einer Abfrage vom Typ "ref" eine Abfrage vom Typ "ALL", 
das ist deutlich schlechter.

Allerdings will mir immer noch nicht in den Kopf, wieso MySQL alles 
doppelt speichert. Man müsste eine Grundsortierung angeben können, quasi 
speichern der Tabelle schon (id, time)-sortiert, sodass dafür kein Index 
nötig ist, aber das kann MySQL wohl nicht.
Schade, muss ich wohl oder übel den doppelten Platz einplanen. :/ 
Irgendwann wird die Tabelle mal sehr groß.

von guest (Gast)


Lesenswert?

A. K. schrieb:
> Wenn es beispielsweise 5 Sensoren gibt, die über "id" gekennzeichnet
> werden, und mit der Zeit 100 Mio Einträge zusammen kommen, über alle 5
> Sensoren gleichverteilt, dann bringt ein Index für "id" wenig, da bei
> einem einfachen "WHERE id=..." immer zig Mio Einträge betroffen sind.

Doch, es macht schon eine Unterschied, ob 100M Datesätze gelesen werden 
müssen oder nur 20M.

A. K. schrieb:
> Winfried schrieb:
>> Wie gesagt ist der Index über (id, time). Da id in der Abfrage konstant
>> ist, bleibt time für eine Range-Abfrage (und auch das Sortieren) nutzbar
>> (wenn ich die ganzen Tutorials für Indices richtig verstanden habe).
>
> Insgesamt stimmts, wenngleich es aus ähnlichem wie vorhin skizziertem
> Grund etwas effektiver sein wird, den Index über (time,id) statt
> (id,time) anzulegen. Ein solcher Index ist dann auch für Abfragen ohne
> "id" hilfreich.

Hängt von der Verteilung der Daten ab, dürfte im vorliegenden Fall aber 
tatsächlich was bringen. Eventuell sogar wenn der Index nur über 'time' 
geht, dürfte auch dessen Größe ein Stück reduzieren. Keine Ahnung ob das 
immer noch so ist, aber früher hatte MySQL ein ziemliches Problem, wenn 
der Index nicht mehr in den RAM passte.

von (prx) A. K. (prx)


Lesenswert?

Winfried schrieb:
> Allerdings will mir immer noch nicht in den Kopf, wieso MySQL alles
> doppelt speichert. Man müsste eine Grundsortierung angeben können, quasi
> speichern der Tabelle schon (id, time)-sortiert, sodass dafür kein Index
> nötig ist, aber das kann MySQL wohl nicht.

Geht bei solchen Datenbanken nicht. Dazu müsste man das schon bei der 
Tabellendefinition spezifizieren können. Also entweder, dass sie 
sortiert vorgehalten wird, oder das neue Einträge hinsichtlich der 
Kriteriens stets aufsteigend angefügt werden.

von (prx) A. K. (prx)


Lesenswert?

Winfried schrieb:
> Ich habe es mit EXPLAIN schon ausgetüftelt, wie es optimal ist. (time,
> id) macht aus einer Abfrage vom Typ "ref" eine Abfrage vom Typ "ALL",
> das ist deutlich schlechter.

Das Ergebnis des query optimizers kann davon abhängen, wieviel Einträge 
insgesamt vorhanden sind, und wieviele davon mit wievielen verschiedenen 
Werten von "id" und von "time". Und wie gut diese Statistik grad ist (=> 
ANALYZE). Da kann er auch bei vorhandenem Index schon mal auf die Idee 
kommen, dass ein full table scan effizienter als der Index ist.

PS: Das gilt eher allgemein für Datenbanken. Welche Optimizer abhängig 
von was wie arbeiten habe ich grad nicht parat.

von Frank _. (fbi)


Lesenswert?

Winfried schrieb:
> Ich habe es mit EXPLAIN schon ausgetüftelt, wie es optimal ist. (time,
> id) macht aus einer Abfrage vom Typ "ref" eine Abfrage vom Typ "ALL",
> das ist deutlich schlechter.

Wie viele Datensätze, wie viele unterschiedliche IDs und welche 
Zeitspanne hast Du momentan in der DB? Und über welche Zeitspanne geht 
Deine Query?

Ab einer gewissen Anzahl an Datensätzen würde sich eventuell auch eine 
Partitionierung der Tabelle anbieten

von (prx) A. K. (prx)


Lesenswert?

guest schrieb:
> Doch, es macht schon eine Unterschied, ob 100M Datesätze gelesen werden
> müssen oder nur 20M.

Es kann aber sein, dass die 100M Einträge schneller gelesen werden als 
die 20M. Ein full table scan geht sequentiell über die Datenblöcke, was 
wesentlich flotter ist als random access über Index.

: Bearbeitet durch User
von Frank E. (Firma: Q3) (qualidat)


Lesenswert?

Ein Index beschleunigt das Lesen, nicht das Schreiben - im Gegenteil, 
durch das Updaten des Index ist das Schreiben mit Index langsamer als 
ohne. Das ist aber sinnvoll so, weil in den allermeisten Datenbanken 
viel öfter gelesen als geschrieben wird.

Ein Index ist quasi eine sortierte Kopie des jeweiligen Feldes (und 
damit auch genau so groß), in der mit systematischen Vor- und 
Rückwärts-Sprüngen das gesuchte Datum ähnlich einer sukzessiven 
Approxximation in kürzester Zeit gefunden wird. Ohne Index benötigt man 
einen sog. "full table scan" ...

Die Original-Datenbsätze liegen auf dem Datenträger physikalisch so, wie 
sie hereingekommen sind.

Seine volle Beschleunigungs-Wirkung entwickelt ein Index beim Suchen von 
Einzelwerten. Bei Ergebnisgruppen (z.B. Zeit- oder Datumswerte) müssen 
die einzelnen Ergebnisdatensätze erst in einem interen Puffer gesammelt 
werden.

Dein Datenmodell mit "id, time, temp1, temp2, temp3 ..." widerspricht 
eklatant den Regeln der Normalisierung. Es muss heissen:

id, time, temp
id, time, temp
id, time, temp ... usw.

wenn ich mal davon ausgehe, dass temp1, temp2 usw. die werte 
verschiedener Sensoren sind und die id auf den Sensor verweist.

: Bearbeitet durch User
von (prx) A. K. (prx)


Lesenswert?

A. K. schrieb:
> Das Ergebnis des query optimizers kann davon abhängen, wieviel Einträge
> insgesamt vorhanden sind, und wieviele davon mit wievielen verschiedenen
> Werten von "id" und von "time". Und wie gut diese Statistik grad ist (=>
> ANALYZE). Da kann er auch bei vorhandenem Index schon mal auf die Idee
> kommen, dass ein full table scan effizienter als der Index ist.

Nachtrag: Ich hatte als Kunde schon mehrfach mit einem Standardfehler 
von Anwendungsentwicklern zu tun. Nämlich die Anwendung mit einem 
vergleichsweise kleinen Satz von Testdaten zu entwickeln. Klappt prima, 
also ab zum Kunden. Nach Monaten oder Jahren, mit den sehr viel 
grösseren und anders verteilten Realdaten, wurde sie unbrauchbar 
langsam. Indexe fehlten.

Mit wenig Daten ist es schnurzpiepegal, welchen Index du hast oder 
nicht. Und wie die Inhalte verteilt sind. Mit sehr vielen Daten nicht 
mehr.

: Bearbeitet durch User
von Jens G. (jensig)


Lesenswert?

> Allerdings will mir immer noch nicht in den Kopf, wieso MySQL alles
> doppelt speichert. Man müsste eine Grundsortierung angeben können, quasi
> speichern der Tabelle schon (id, time)-sortiert, sodass dafür kein Index
> nötig ist, aber das kann MySQL wohl nicht.

Eine Tabelle könnte zwar eine  Grundsortierung haben, nur hilft das 
trotzdem nicht viel bei der späteren Datensuche. Denn ein index ist ja 
nicht einfach nur sortiert, sondern er enthält auch eine spezielle 
Datenstruktur, die es erlaubt, sehr schnell die Stelle mit den 
gewünschten Daten zu finden. Da wird nicht einfach der gesamte Index 
durchsucht, bis wir bei den gewünschten Daten angekommen sind, sondern 
die Stellen (Pages) mit den gewünschten Daten werden in wenigen 
Schritten "angesprungen". Da dauert es trotz 100Mio Rows nur 
Millisekunden, bis die Row gefunden wird (es sei denn, man möchte 
größere Bereiche mit der Abfrage auslesen).

von Frank E. (Firma: Q3) (qualidat)


Lesenswert?

Frank E. schrieb:
> id, time, temp
> id, time, temp
> id, time, temp ... usw.
>
> wenn ich mal davon ausgehe, dass temp1, temp2 usw. die werte
> verschiedener Sensoren sind und die id auf den Sensor verweist.

Ich würde in diesem Falle die id (Sensor-Nummer) und time bzw. datetime 
indizieren, auf temp würde ich verzichten, weil danach sicher selten bis 
garnicht direkt gesucht wird, sondern dieser Wert das Ergebnis ist.

von (prx) A. K. (prx)


Lesenswert?

Frank E. schrieb:
> Dein Datenmodell mit "id, time, temp1, temp2, temp3 ..." widerspricht
> eklatant den Regeln der Normalisierung.

Könnte das nicht ein wenig davon abhängen, in welcher Beziehung die 
tempX Spalten stehen? Als ob die implizit einen Datensatz darstellen. 
Man trennt in Datenbanken ja auch nicht Vorname von Nachname in Form 
getrennter Tabellen.

von Winfried (Gast)


Lesenswert?

id ist sowas wie eine Geräte-ID. Jedes Gerät misst gleichzeitig mehrere 
Temperaturen (und noch ein paar andere Werte).
Übrigens: Dass in diesem Fall öfter gelesen als geschrieben wird, ist 
eigentlich gar nicht so. Es kommen ja kontinuierlich Werte, aber der 
Mensch guckt sich die Daten nur sporadisch an.

von Frank _. (fbi)


Lesenswert?

Frank E. schrieb:
> Die Original-Datenbsätze liegen auf dem Datenträger physikalisch so, wie
> sie hereingekommen sind.

Solange nur Daten hinzugefügt werden kann das so sein (muß aber nicht). 
Wenn Daten aber auch wieder gelöscht werden, stimmt das eher nicht mehr. 
Und wenn der Datenträger irgendwas flash basiertes ist sowieso nicht.


Winfried schrieb:
> Es kommen ja kontinuierlich Werte, aber der
> Mensch guckt sich die Daten nur sporadisch an.

Dafür reagiert der Mensch aber recht genervt, wenn Abfragen zu lange 
dauern :)

von Frank E. (Firma: Q3) (qualidat)


Lesenswert?

Winfried schrieb:
> id ist sowas wie eine Geräte-ID. Jedes Gerät misst gleichzeitig
> mehrere
> Temperaturen (und noch ein paar andere Werte).
> Übrigens: Dass in diesem Fall öfter gelesen als geschrieben wird, ist
> eigentlich gar nicht so. Es kommen ja kontinuierlich Werte, aber der
> Mensch guckt sich die Daten nur sporadisch an.

Mag sein, aber du weisst nicht, ob das immer so bleibt - also weitere 
Werte hinzukommen oder mal was wegfällt.

Bei einem Wert pro Datensatz ändert sich einfach deren Anzahl, 
anderenfalls musst du an der DB-Struktur herummanchen ... sowas tut man 
nicht. Eventuell benötigst du noch ein Feld, was den Sensortyp 
kennzeichnet, während die ID für die Maschine bzw. den Messort steht.

: Bearbeitet durch User
von Roland P. (pram)


Lesenswert?

Hast du mal probiert, (id, time) als Primärindex zu verwenden?
Es hängt dann auch noch von den Datentypen der Spalten ab, was der Index 
braucht. Varchars sind z. B. ganz schlecht, weil mit UTF gleich 3-4 
Bytes je Zeichen verbracht werden.

Und ja, in deiner Konstellation kann es wirklich seib, dass der Index 
mehr Platz braucht, als die Daten

von Peter M. (r2d3)


Lesenswert?

Hallo Winfried,

vorab:

Alle meine Anmerkungen ohne Gewähr, alles nur Studiumsreste und gesunder 
Menschenverstand.
Die Datenbank, für die ich zur Zeit etwas konzipieren muss, darf ich 
selber gar nicht anfassen.

Winfried schrieb:
> In einer Datenbank sollen von relativ vielen Datenquellen (anhand ihrer
> id durchnummeriert) jeweils Zeit und ein paar Temperaturen gespeichert
> werden. Also jede Zeile besteht vereinfacht aus
>
> id, time, temp1, temp2, temp3

Hier wäre es auch mal interessant zu wissen, wie breit die Daten sind.

> So weit so gut. Zum Darstellen der Daten wird immer für eine bestimmte
> id ein bestimmter Zeitraum rausgefischt. Also steht in der Abfrage sowas
> wie "where id=7 and time>min and time<max". Am Ende wird noch sortiert:
> "order by time".
>
> Das Ganze soll jetzt optimiert werden, damit die Abfrage schneller wird.
> Ich habe dazu einen Index (primary) über id und time erstellt. Ist das
> korrekt?

Keine Ahnung, ein einziger breiter Integer, der hochgezählt wird, kann 
schneller ausgewertet werden. Ich sehe den Mehrwert des verbundenen 
Schlüssels nicht.

Meiner Meinung nach brauchst Du für jedes Suchkriterium einen einzelnen 
Index. Der Index über dem verbundenen Primärschlüssel ist sinnlos, weil 
Du niemals nach den Werten des verbundenen Primärschlüssels suchst.

Ich bezweifele, dass das DBMS so schlau ist zu wissen, dass die Ordnung 
über einem verbundenen Primärschlüssels gleichzeitig eine Ordnung über 
genau einer Spalte aller Spalten, die zu einem Primärschlüssel verbunden 
werden, definiert.

> Mich macht stutzig, dass der Index genau so viel Platz wegnimmt
> wie die Daten. Also der Speicherplatz hat sich gerade mal fast
> verdoppelt.

Du hast halt nur ganz wenig Nutzdaten temp1, temp2, temp3 die wenig 
Platz brauchen. Mach da mal breite Strings draus, dann sollte das 
passieren, was Du erwartest. :)

> Bringt dann diese Optimierung so überhaupt etwas?

Ja. Ein bestimmter Wert kann statt in n Durchläufen (was die anderen 
"Full table scan" nennen) in log(n) Durchläufen gefunden werden.

Was jetzt noch interessant ist, sind die Zugriffszeiten.
Liegt die Datenbank im RAM, geht alles rasend schnell.
Liegt die Datenbank auf der Platte, sind Einzelzugriffe eventuell so 
teuer,  dass bei großen Ergebnismengen der Suche der komplette 
Tabellendurchlauf billiger gewesen wäre (gucken, was der Festplattenkopf 
machen muss, bzw. ob   das Abfragemuster so ist, dass der Cache Zugriffe 
auf die Platte beschleunigt.)

Beispiel:

1000 Zeilen in der Tabelle, Abfrage liefert 700 Zeilen.
Im physischen Speicher bringt der Index einen Gewinn.
Auf Festplatte mussten die Zeilen alle einzeln angefahren werden.
Eventuell sind die 700 Fahrten teurer als ein linearer Durchlauf durch 
alle 1000 Zeilen.

siehe Erklärung von A.K.!

> Andere
> Ideen?

Siehe oben.

Theor schrieb:
> Nimm einmal an, Du hättest n Datensätze. Je näher die Anzahl der Ids und
> Zeiten an n heranrückt, desto grösser wird der Index im Verhältnis zur
> ursprünglichen Datengrösse. Vermutlich gibt es also in Deinem Fall recht
> viele Individuen.

Glaube ich nicht. Der Index muss auch bei 5 IDs und 1000 Zeilen alle 
Zeilen referenzieren.

Winfried schrieb:
> Also der Index hält quasi die Tabelle nochmal vor (aber halt ohne die
> paar Bytes für die Temperaturen und sortiert). So stelle ich mir das
> momentan vor.

Nö, eigentlich muss er nur die IDs der Zeilen der Tabelle nach Maßgabe 
des Sortierkriteriums auflisten. Über einen weiteren Index sollte er 
dann schnell die IDs wiederauffinden können.
Vielleicht wird aber auch der Primärschlüssel immer mitindexiert?
Da habe ich keine Ahnung!

eProfi schrieb:
> Wenn die Daten nach der Zeit sortiert sind, kannst Du eine recht
> schnelle binäre Suche machen.
> Doppelt verkettete Liste könnte auch helfen. Alles Standard-Kram,

Das macht das DBMS im Hintergrund für Dich, Du sagst ihm nur, was Du 
suchst! Dahinter steht natürlich eine Baumstruktur, die durchsucht wird.

A. K. schrieb:
> Es ist völlig normal, wenn bei Datenbanken die Indexe grob ähnlich viel
> Platz wegnehmen wie die Daten.

Wenn ich in einer Tabellenspalte Strings mit 255 Byte speichere (oder 
noch größere Strukturen), erwarte ich, dass der Index nur den Platz für 
die Zeigerstruktur verbrät, aber nicht für die Werte selber.

Winfried schrieb:
> Allerdings will mir immer noch nicht in den Kopf, wieso MySQL alles
> doppelt speichert. Man müsste eine Grundsortierung angeben können, quasi
> speichern der Tabelle schon (id, time)-sortiert, sodass dafür kein Index
> nötig ist, aber das kann MySQL wohl nicht.

Das macht keinen Sinn, weil das physische Hin- und Herschaufeln der 
Zeilen zur Einhaltung Deines Indexkriteriums mehr kostet, als die 
Verwaltung eines Index. Die Grundsortierung erfolgt über den ersten 
Index.

Das zitierte Telefonbuch ist nicht die Datenbank, sondern eine Sicht 
darauf, bei der alles nach Nachnamen und Vornamen geordnet ist.

> Schade, muss ich wohl oder übel den doppelten Platz einplanen. :/
> Irgendwann wird die Tabelle mal sehr groß.

siehe mein Erklärung oben.

A. K. schrieb:
> Es kann aber sein, dass die 100M Einträge schneller gelesen werden als
> die 20M. Ein full table scan geht sequentiell über die Datenblöcke, was
> wesentlich flotter ist als random access über Index.

Mein Hinweis oben stammt aus Deiner Quelle!
Vielen Dank für Deine Zeilen, den Aspekt hatte ich gar nicht auf dem 
Schirm.


A. K. schrieb:
> Wenn andererseits die Abfragen stets auch ein "WHERE time>... AND
> time<..." enthalten, und damit ein gegenüber der Anzahl Einträge
> insgesamt nur kleiner Zeitraum betroffen ist, dann ist ein Index für
> "time" wesentlich hilfreicher. Deine oben skizzierte kombinierte Abfrage
> wird dann über den Index für "time" gehen und erst in den Ergebnissen
> davon nach der "id" suchen. Ein Index für "id" hilft dann überhaupt
> nicht.

Ist das DBMS so schlau?
Dann müsste es vor der Auswertung zuerst die Kardinalität in den 
einzelnen Suchkriterien prüfen. Sicherheitshalber würde ich die kleinste 
erwartete Ergebnismenge nach vorne ziehen, z.B. die Zeit.

: Bearbeitet durch User
von (prx) A. K. (prx)


Lesenswert?

Peter M. schrieb:
> Dann müsste es vor der Auswertung zuerst die Kardinalität in den
> einzelnen Suchkriterien prüfen.

Genau deshalb führen RDBMS Statistiken mit solchen Informationen.
https://mariadb.com/kb/en/library/statistics-for-optimizing-queries/

von Winfried (Gast)


Lesenswert?

Wie oben geschrieben, mit

Index (time, id)
ergibt sich eine Abfrage vom Typ "ALL" (full table scan!!!).

Mit

Index (id, time)
ergibt sich eine Abfrage vom Typ "ref".

von (prx) A. K. (prx)


Lesenswert?

Winfried schrieb:
> ergibt sich eine Abfrage vom Typ "ALL" (full table scan!!!).

Wie schon gesagt, das hängt nicht nur von einem vorhandenen Index ab, 
sondern auch von den konkreten Daten, bzw. den Statistiken dazu.

von Peter M. (r2d3)


Lesenswert?

A. K. schrieb:
> Peter M. schrieb:
>> Dann müsste es vor der Auswertung zuerst die Kardinalität in den
>> einzelnen Suchkriterien prüfen.
>
> Genau deshalb führen RDBMS Statistiken mit solchen Informationen.
> https://mariadb.com/kb/en/library/statistics-for-optimizing-queries/

Danke, wusste ich nicht!

Winfried schrieb:
> Wie oben geschrieben, mit
>
> Index (time, id)
> ergibt sich eine Abfrage vom Typ "ALL" (full table scan!!!).
>
> Mit
>
> Index (id, time)
> ergibt sich eine Abfrage vom Typ "ref".

Bau' doch mal einen automatischen Primärschlüssel, zwei Indizes für id 
und time und sag' was mit der Laufzeit passiert.

von (prx) A. K. (prx)


Lesenswert?

Peter M. schrieb:
> Wenn ich in einer Tabellenspalte Strings mit 255 Byte speichere (oder
> noch größere Strukturen), erwarte ich, dass der Index nur den Platz für
> die Zeigerstruktur verbrät, aber nicht für die Werte selber.

Das wäre höchst ineffizient, da jeder einzelne Vergleich eines einzelnen 
Index-Eintrags dann einen random Zugriff auf das data record erfordern 
würde.

von (prx) A. K. (prx)


Lesenswert?

Peter M. schrieb:
>> Genau deshalb führen RDBMS Statistiken mit solchen Informationen.
>> https://mariadb.com/kb/en/library/statistics-for-optimizing-queries/
>
> Danke, wusste ich nicht!

Zu query optimization:
https://mariadb.com/kb/en/library/query-optimizations/

von Purzel H. (hacky)


Lesenswert?

Wenn man sowieso alles durchsuchen muss, weil es nicht sortiert ist, ist 
der Speicherbedarf am Kleinsten. Alle Records anschauen, mit allen 
Kriterien vergleichen, und sich nur die Indices, die passen merken. Die 
kann man nachher einzeln hervorholen.
Falls schon eine Sortierung vorhanden ist, ist die (fast-)Unendlichkeit 
schon mal beschraenkt. Dann muss man eben nur noch dieses Subset 
anschauen.

von Samson (Gast)


Lesenswert?

A. K. schrieb:
> Winfried schrieb:
>> Allerdings will mir immer noch nicht in den Kopf, wieso MySQL alles
>> doppelt speichert. Man müsste eine Grundsortierung angeben können, quasi
>> speichern der Tabelle schon (id, time)-sortiert, sodass dafür kein Index
>> nötig ist, aber das kann MySQL wohl nicht.
>
> Geht bei solchen Datenbanken nicht. Dazu müsste man das schon bei der
> Tabellendefinition spezifizieren können. Also entweder, dass sie
> sortiert vorgehalten wird, oder das neue Einträge hinsichtlich der
> Kriteriens stets aufsteigend angefügt werden.

Gibts ja, nennt sich Clustered Index.
>>>"The key difference between clustered indexes and non clustered indexes is that 
the leaf level of the clustered index !is! the table. "

>> A.K.
>Das wäre höchst ineffizient, da jeder einzelne Vergleich eines einzelnen
>Index-Eintrags dann einen random Zugriff auf das data record erfordern
>würde.

Wird ja so gemacht, in der Regel fasst die Datenbank diese Zugriffe 
zusammen und ließt Seiten-weise. Zudem sprechen wir ja nicht mehr vom 
Festplattenkopf, man nutzt ja SSD heute auch für Datenbanken;)

>Winfried schrieb:
>>Wie oben geschrieben, mit

>>Index (time, id)
>>ergibt sich eine Abfrage vom Typ "ALL" (full table scan!!!).

Ne das ist nicht richtig, die Bedingung "time>min and time<max" wird 
natürlich dann zuerst ausgewertet, so schlau ist die Datenbank dann 
schon ;)

von (prx) A. K. (prx)


Lesenswert?

Samson schrieb:
> Zudem sprechen wir ja nicht mehr vom
> Festplattenkopf, man nutzt ja SSD heute auch für Datenbanken;)

Eine Samsung 850 Pro liefert bei Random Access 4KB und Queue=1 nur 
10.000 IOPS/s, also 40MB/s, sequentiell sind es über 500MB/sec.

Bei Queue=16 wird dann bis zu 400MB/sec, wobei die Frage ist, welches 
DRBMS das bei einem Index Scan wie tief durchführt.

: Bearbeitet durch User
von Samson (Gast)


Lesenswert?

Gehen wir mal davon aus, das die Datenbank darselbst das beherrscht ( 
Zwischenspeichern von Page-Zugriffen und Sortieren und Zusammenfassen 
dieser )

von Winfried (Gast)


Lesenswert?


von Jens G. (jensig)


Lesenswert?

@Samson (Gast)

>A. K. schrieb:
>> Winfried schrieb:
>>> Allerdings will mir immer noch nicht in den Kopf, wieso MySQL alles
>>> doppelt speichert. Man müsste eine Grundsortierung angeben können, quasi
>>> speichern der Tabelle schon (id, time)-sortiert, sodass dafür kein Index
>>> nötig ist, aber das kann MySQL wohl nicht.
>>
>> Geht bei solchen Datenbanken nicht. Dazu müsste man das schon bei der
>> Tabellendefinition spezifizieren können. Also entweder, dass sie
>> sortiert vorgehalten wird, oder das neue Einträge hinsichtlich der
>> Kriteriens stets aufsteigend angefügt werden.
>
>Gibts ja, nennt sich Clustered Index.
>>>>"The key difference between clustered indexes and non clustered indexes
>is that
>the leaf level of the clustered index !is! the table. "

Scheint wohl vom SQL-Server zu sein. Er benutzt aber wohl MySQL ...
Aber egal, spätestens dann, wenn er dieser Tabelle eine weitere 
Sortierreihenfolge einhauchen will, geht das dann nicht mehr mit einem 
clustered Index.

: Bearbeitet durch User
von Samson (Gast)


Lesenswert?

>>When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered 
index


https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

InnoDB ist eine Option bei mySQL ( Sprich Müü-SQL wers noch nicht wusste 
;) )

>>Da Oracle die Markenrechte an MySQL hält, mussten neue Namen für das 
Datenbanksystem und dessen Storage-Engines gefunden werden.[12] Der Name MariaDB 
geht auf Widenius’ jüngere Tochter Maria zurück; seine andere Tochter My war 
bereits die Namensgeberin für MySQL.[13]
(https://de.wikipedia.org/wiki/MariaDB)

https://de.wikipedia.org/wiki/My_(Vorname)

Ist glaube ich nicht so wild mit den indices hauptsache es sind 
überhaupt welche vorhanden. Wenn mal 100M Einträge drin sind ist die 
Technik auch schon weiter ;)

von Jens G. (jensig)


Lesenswert?

Widenius’ andere Tochter hieß Müü? Wie kann man denn jemanden wie einen 
Einheitenvorsatz nennen? ;-)

Bitte melde dich an um einen Beitrag zu schreiben. Anmeldung ist kostenlos und dauert nur eine Minute.
Bestehender Account
Schon ein Account bei Google/GoogleMail? Keine Anmeldung erforderlich!
Mit Google-Account einloggen
Noch kein Account? Hier anmelden.