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.
:
Bearbeitet durch User
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
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.
:
Bearbeitet durch User
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
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
> 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.
:
Bearbeitet durch User
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.
:
Bearbeitet durch User
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/
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.
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/
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.
:
Bearbeitet durch User
Gehen wir mal davon aus, das die Datenbank darselbst das beherrscht ( Zwischenspeichern von Page-Zugriffen und Sortieren und Zusammenfassen dieser )
@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
>>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 ;)
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
Mit Google-Account einloggen
Noch kein Account? Hier anmelden.