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?
Und weshalb willst du Speicherplatz vorhalten ? Die Werte koennen doch
waerend des Suchens verglichen werden. Man benoetigt dann nur die
gueltigen Eintraege.
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.
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.
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,
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.
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.
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.
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.
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).
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.
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.
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ß.
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.
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.
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.
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
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.
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.
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.
> 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).
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.
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.
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.
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 :)
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.
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
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.
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".
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.
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.
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.
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.
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 ;)
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.
@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.
>>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 ;)
