[SQL] Wo werden Abfrageergebnisse gespeichert? [Archiv]

FlashBurn

2014-09-08, 11:30:29

PatkIllA

2014-09-08, 11:41:17

Je nach Zugriffsschicht ist die Ergebnistabelle nirgends als ganzes gespeichert.
Bei vielen Abstraktionen wird das ganze Ergebnis auf dem anfragenden Rechner zwischen gespeichert

FlashBurn

2014-09-08, 11:50:31

Je nach Zugriffsschicht ist die Ergebnistabelle nirgends als ganzes gespeichert.
Bei vielen Abstraktionen wird das ganze Ergebnis auf dem anfragenden Rechner zwischen gespeichert
Wie bekomme ich heraus wo das Ergebnis zwischengespeichert wird?

PatkIllA

2014-09-08, 11:57:36

Wie bekomme ich heraus wo das Ergebnis zwischengespeichert wird?
Dokumentation oder in den Source Code schauen. Wenn man direkt Anzahl der Ergebnisse bekommt, dann kannst du mit ziemlicher Sicherheit davon ausgehen, dass es auf dem Client als ganzes gespeichert wird.
Eine Serverdatenbank, die immer das Ergebnis als speichert und dann erst zum Client schickt kann ich mir kaum vorstellen.

Das es lokal deutlich schneller geht ist jetzt auch nicht ungewöhnlich. Da fehlt der ganze Netzwerkoverhead und ein paar Sicherheitscheck kann man sich da je nach Situation auch noch sparen.
Aus wievielen Datensätzen wird denn da ausgewählt? Bei 3000 Ergebnissen würde ich jetzt von einem messbaren aber nicht merkbaren Unterschied ausgehen.

Schon mal geschaut wieviel Daten übers Netzwerk gehen? SELECT * würde ich mir am besten auch gleich abgewöhnen und nur die Spalten abfragen die man wirklich braucht.

FlashBurn

2014-09-08, 12:09:02

Dokumentation oder in den Source Code schauen.

Welchen Source Code meinst du?

Ich sollte vllt noch erwähnen das ich mit keinem der normalen Verdächtigen, was den Compiler/IDE betrifft, arbeite und in dieser Doku ist dazu leider nichts zu finden.
Die Zugriffe finden über den ODBC Connector von MySQL statt.

Wenn man direkt Anzahl der Ergebnisse bekommt, dann kannst du mit ziemlicher Sicherheit davon ausgehen, dass es auf dem Client als ganzes gespeichert wird.

Keine Ahnung was du genau meinst. Ich stelle eine SQL Abfrage an die DB und kann dann die Ergebnismenge mit FetchNext()/FetchPrev() Funktionen durchgehen, aber habe keine Funktion um die Anzahl der Zeilen vom Ergebnis zu bekommen. Daher halt die Vermutung dass das Ergebnis auf dem Server zwischengespeichert ist.

Das es lokal deutlich schneller geht ist jetzt auch nicht ungewöhnlich. Da fehlt der ganze Netzwerkoverhead und ein paar Sicherheitscheck kann man sich da je nach Situation auch noch sparen.
Aus wievielen Datensätzen wird denn da ausgewählt? Bei 3000 Ergebnissen würde ich jetzt von einem messbaren aber nicht merkbaren Unterschied ausgehen.

3000 war nur ein Bsp. Die DB läuft jetzt seit Anfang Februar und pro Minute kommen ca. 320 Zeilen dazu. Ausgewählt werden in diesem speziellen Fall ca. 6000 Zeilen und man merkt den Unterschied mehr als deutlich. Ohne es im Moment vor Ort testen zu können, reden wir von mehreren Sekunden, aber nicht im Minuten Bereich.

Schon mal geschaut wieviel Daten übers Netzwerk gehen?

Nope, dürfte auch nicht viel sein. Denn die reinen Daten sind eigentlich nicht der Rede Wert (aus meiner Sicht)

SELECT * würde ich mir am besten auch gleich abgewöhnen und nur die Spalten abfragen die man wirklich braucht.

Keine Sorge, war erstens nur ein Bsp. und zweitens brauche ich alle Spalten für die Verarbeitung. Wenn ich nicht alle Spalten brauche, wähle ich auch immer nur die aus, die nötig sind ;)

Exxtreme

2014-09-08, 12:27:35

Ich verwende MySQL und die Datenbank läuft auf einem Server. Zugegriffen wird meistens von anderen Rechnern im Netzwerk, aber auch direkt vom Server aus.

Wenn ich jetzt eine Select-Abfrage mache, wo wird das Ergebnis gespeichert?

Mir geht es darum, die Performance wenn möglich zu erhöhen und ich sehe einen deutlichen Unterschied ob die Abfrage direkt auf dem Server oder von einem Rechner im Netzwerk gemacht wird.

Als Bsp. wird eine Abfrage "SELECT * FROM Daten WHERE Adresse = 10" gemacht und diese liefert eine Tabelle mit 3000 Zeilen wieder. Im Code hab ich dann eine for-Schleife die die einzelnen Zeilen einliest und verarbeitet.
Ist diese Ergebnistabelle auf dem Server (im DB-System) oder ist die schon auf dem jeweiligen Rechner?

Meine Vermutung ist das die Ergebnistabelle immer auf dem Server ist und dann die einzelnen Zeilen der Tabelle immer übers Netzwerk transportiert werden und für jede Zeile wieder eine Art Anfrage an die DB gemacht werden muss.
Wenn der Client die 3k Datensätze liefert dann sind diese bereits auf dem Client. Ich kenne mich mit Mysql nicht aus aber die DB2 hat einen sog. temporären Tabellenbereich. Da erstellt sie temporäre Tabellen, die sich quasi als Cache nutzt. Gibt auch oft Performance-Empfehlung diese Tabellenbereiche auf extra Datenträger auszulagern.

Ach ja, SELECT * ... ist nicht so toll. Besser das angeben was man wirklich braucht. Ist schneller und macht hinterher weniger Ärger wenn man die Tabelle erweitert.

fezie

2014-09-08, 13:00:18

FlashBurn

2014-09-08, 13:11:28

Ich weis jetzt nicht wie es bei ODBC aussieht aber die C Api von MySQL bietet die 2 Funktionen mysql_use_result und mysql_store_result. Der Unterschied ist bei use muss der Client nicht soviel Arbeitsspeicher haben um das komplette Ergebnis zu speichern. Bei store wird alles auf einmal gespeichert

Das würde ja meine Theorie unterstützen, dass das Ergebnis eben nicht vollständig beim Clienten liegt.

ODBC war eine Anforderung, weil nur so die Funktionen vom Compiler/IDE genutzt werden konnten. Eine Umstellung ist jetzt auch nicht mehr praktikabel, aber ich hätte doch ganz gerne, vorallem für das nächste Projekt, gewusst wo das Problem liegt und wie man es lösen kann.

PatkIllA

2014-09-08, 13:17:25

Wenn der Client die 3k Datensätze liefert dann sind diese bereits auf dem Client. Ich kenne mich mit Mysql nicht aus aber die DB2 hat einen sog. temporären Tabellenbereich. Da erstellt sie temporäre Tabellen, die sich quasi als Cache nutzt. Gibt auch oft Performance-Empfehlung diese Tabellenbereiche auf extra Datenträger auszulagern.Bei einer ganz einfachen Abfrage mit Abfrage auf indizierter Spalte, ohne Sortierung, Joins oder sonst was sollte das nie nötig sein.
Die Zugriffe finden über den ODBC Connector von MySQL statt.Das wäre dann die richtige Stelle zum Nachschauen.
Keine Ahnung was du genau meinst. Ich stelle eine SQL Abfrage an die DB und kann dann die Ergebnismenge mit FetchNext()/FetchPrev() Funktionen durchgehen, aber habe keine Funktion um die Anzahl der Zeilen vom Ergebnis zu bekommen. Daher halt die Vermutung dass das Ergebnis auf dem Server zwischengespeichert ist.Das Ergebnis muss dafür aber nirgends zwischengepeichert werden. Der Server kann dem Client schon Daten schicken, während eher noch nach weiteren Ergebnissen sucht und gleichzeitig kannst du die Daten zeilenweise verarbeiten. Dabei gibt es nirgends ein komplettes Ergebnis.

FlashBurn

2014-09-08, 13:30:08

Bei einer ganz einfachen Abfrage mit Abfrage auf indizierter Spalte, ohne Sortierung, Joins oder sonst was sollte das nie nötig sein.

Naja, meine genaue Abfrage habe ich ja noch nicht mitgeteilt. Ich bin bisher davon ausgegangen dass das keinen Einfluss auf den Zeitunterschied hat.

Puh, wie bekommt man denn heraus ob der MySQL ODBC Connector Daten zwischenspeichert oder nicht bzw wo? In den Sourcen von dem Connector wollte ich eigentlich nicht noch rumsuchen.
Hatte gehofft das ist ein typisches Erscheinungsbild und es gibt dafür eine Lösung oder halt nicht ;)

PatkIllA

2014-09-08, 13:51:36

Normalerweise wird das Ergebnis mit einem Puffer auf den Client gestreamt, während der Server noch liest.
Alles andere wäre ineffizient.

Wenn du das auf dem Server direkt machst ist das dann auch das exakt gleiche Programm?

Exxtreme

2014-09-08, 13:58:24

Naja, meine genaue Abfrage habe ich ja noch nicht mitgeteilt. Ich bin bisher davon ausgegangen dass das keinen Einfluss auf den Zeitunterschied hat.

Puh, wie bekommt man denn heraus ob der MySQL ODBC Connector Daten zwischenspeichert oder nicht bzw wo? In den Sourcen von dem Connector wollte ich eigentlich nicht noch rumsuchen.
Hatte gehofft das ist ein typisches Erscheinungsbild und es gibt dafür eine Lösung oder halt nicht ;)
Der Sinn von SQL ist, dass man sich um solche Details eben nicht kümmern soll. Deshalb hat man das auch als eine High-Level Abfragesprache konzipiert. Damit die DB-Hersteller das selbst optimiert bekommen. Auf die internen Datenstrukturen des ODBC-Connectors wirst du wohl sowieso nicht zugreifen können. Und wenn du tatsächlich die Daten per for-Schleife holst dann musst du die Netzwerklatenzen noch mitberücksichtigen. Viele ODBC-Konnektoren sind so schlau und erkennen es, dass die DB lokal läuft. Dann wird nicht per Netzwerk drauf zugegriffen sondern direkt im Speicher kopiert. Und das dürfte beträchtlich schneller sein.

FlashBurn

2014-09-08, 14:03:37

Wenn du das auf dem Server direkt machst ist das dann auch das exakt gleiche Programm?

Beides Mal das genau selbe Programm, nur einmal läuft es auf dem gleichen Rechner wie auch die DB und einmal läuft es auf einem beliebigen anderen Rechner im Netzwerk.

.. Und wenn du tatsächlich die Daten per for-Schleife holst dann musst du die Netzwerklatenzen noch mitberücksichtigen.

Heißt was genau?
Ich gehe zwar das Ergebnis per for-Schleife durch, aber die Abfrage wird nur einmal gemacht.

Viele ODBC-Konnektoren sind so schlau und erkennen es, dass die DB lokal läuft. Dann wird nicht per Netzwerk drauf zugegriffen sondern direkt im Speicher kopiert. Und das dürfte beträchtlich schneller sein.

Das könnte dann auch den Unterschied erklären.

Ich hatte gehofft das dort noch ein wenig Zeit rauszuholen ist, aber dann muss das halt so akzeptiert werden.

PatkIllA

2014-09-08, 14:04:26

Und wenn du tatsächlich die Daten per for-Schleife holst dann musst du die Netzwerklatenzen noch mitberücksichtigen.Da sollte aber auch jede Implementierung so schlau sein, dass es da nicht für jede Zeile einen kompletten Roundtrip über Netzwerk gibt.

Exxtreme

2014-09-08, 14:10:19

Heißt was genau?
Ich gehe zwar das Ergebnis per for-Schleife durch, aber die Abfrage wird nur einmal gemacht.
OK ... hau mal das SELECT * aus der Abfrage raus und schau ob es schneller läuft. Wenn du ein SELECT COUNT machst dann nimm die ID-Spalte anstatt *. Die liegt idR. im Index und das läuft dann schnell.

FlashBurn

2014-09-08, 14:28:08

OK ... hau mal das SELECT * aus der Abfrage raus und schau ob es schneller läuft. Wenn du ein SELECT COUNT machst dann nimm die ID-Spalte anstatt *. Die liegt idR. im Index und das läuft dann schnell.

Wie soll ich aus einer Abfrage wie "SELECT * FROM Daten WHERE Adresse = 10" "SELECT *" rausnehmen und es dann noch ausprobieren?! Oder meinst du das ich probieren soll, ob es schneller wird, wenn ich alle Spalten aufzähle anstatt * zu nehmen?

Leider kann ich das ganze auch im Moment nicht realitätsnah testen, da ich hier alles nur auf einem Entwicklerrechner simuliere und beim Kunden läuft das dann auch im Netzwerk.
Aber da ich sowieso den Server zu Testzwecken hier auch mal auf einen extra Rechner packen wollte, kann ich das vllt morgen mal machen.

Ist es denn überhaupt möglich diese Netzwerklaufzeiten zu verringern?

Das wäre ja nur der Fall wenn ich der DB oder dem Connector irgendwie sagen könnte das ich gerne alle Daten auf einmal haben möchte und die dann auf dem lokalen Clienten verarbeite.

Exxtreme

2014-09-08, 14:48:57

Wie soll ich aus einer Abfrage wie "SELECT * FROM Daten WHERE Adresse = 10" "SELECT *" rausnehmen und es dann noch ausprobieren?! Oder meinst du das ich probieren soll, ob es schneller wird, wenn ich alle Spalten aufzähle anstatt * zu nehmen?
Genau so. Anstatt * alle Spalten in der SELECT-Anweisung angeben.
Das wäre ja nur der Fall wenn ich der DB oder dem Connector irgendwie sagen könnte das ich gerne alle Daten auf einmal haben möchte und die dann auf dem lokalen Clienten verarbeite.
Ich weiss halt nicht welche Datenmengen da übertragen werden und wie schnell das Netzwerk ist. Das können sehr schnell zig MB werden wenn da viele Strings dabei sind. Und Unicode verdoppelt das Ganze auch noch da pro Zeichen 16 Byte übertragen werden. Einfach mal grob durchrechnen.

FlashBurn

2014-09-08, 14:55:14

Genau so. Anstatt * alle Spalten in der SELECT-Anweisung angeben.

Ich hab mir gerade nochmal den konkreten Code angeguckt und dort mache ich das sogar schon so ;)
Allerdings ist die Abfrage dort nicht mehr ganz so einfach. Sie sieht ungefähr so aus (das ist direkt aus dem Code kopiert, die Platzhalter werden durch Zahlen ersetzt):

SELECT AdresseGeraet, Sekunden, Wert, Intervall FROM Daten WHERE Sekunden >= %.0f AND AdresseGeraet IN ( %d, %d, %d, %d ) ORDER BY Sekunden ASC

Ich weiss halt nicht welche Datenmengen da übertragen werden und wie schnell das Netzwerk ist. Das können sehr schnell zig MB werden wenn da viele Strings dabei sind. Und Unicode verdoppelt das Ganze auch noch da pro Zeichen 16 Byte übertragen werden. Einfach mal grob durchrechnen.

Die Tabelle, aus der die Daten abgefragt werden, wird so erstellt:

CREATE TABLE IF NOT EXISTS Daten (AdresseGeraet smallint NOT NULL, Sekunden double precision NOT NULL, Wert double precision, Intervall tinyint NOT NULL, INDEX ind_sekunden (Sekunden) )

Exxtreme

2014-09-08, 15:03:29

Die Tabelle ist recht klein. Die Datenmenge wird also kein Problem sein. Was wohl ziemlich helfen wird wenn man einen Index auf Sekunden setzt. Man macht da einen SELECT drauf und sortiert auch noch danach.

Ich weiss jetzt auch nicht welche Version von Mysql da eingesetzt wird. Aber wenn diese Index scans unterstützt dann wäre ein Index auf die Felder und zwar in dieser Reihenfolge: Sekunden, AdresseGeraet, Wert, Intervall wohl noch schneller. Weil dann spart sich die DB-Engine den Zugriff auf die Tabelle und holt die Werte direkt aus dem Index.

FlashBurn

2014-09-08, 15:19:20

Die Tabelle ist recht klein. Die Datenmenge wird also kein Problem sein. Was wohl ziemlich helfen wird wenn man einen Index auf Sekunden setzt. Man macht da einen SELECT drauf und sortiert auch noch danach.

Korrigiere mich wenn ich falsch liege, aber genau das (Index setzen) habe ich doch gemacht.
Die Tabelle ist auch nur in der Anzahl der Spalten klein, bei den Zeilen finde ich sie schon recht groß, vorallem mit der Zeit.

Ich weiss jetzt auch nicht welche Version von Mysql da eingesetzt wird. Aber wenn diese Index scans unterstützt dann wäre ein Index auf die Felder und zwar in dieser Reihenfolge: Sekunden, AdresseGeraet, Wert, Intervall wohl noch schneller. Weil dann spart sich die DB-Engine den Zugriff auf die Tabelle und holt die Werte direkt aus dem Index.

Soweit ich es verstanden habe, sind zu viele Indezes wieder negativ oder nicht?
Die MySQL Version ist recht aktuell, sollte 5.6 sein.

Exxtreme

2014-09-08, 15:38:09

Korrigiere mich wenn ich falsch liege, aber genau das (Index setzen) habe ich doch gemacht.
Die Tabelle ist auch nur in der Anzahl der Spalten klein, bei den Zeilen finde ich sie schon recht groß, vorallem mit der Zeit.

Hast recht, habe jetzt nach rechts gescrollt und sehe das mit dem Index.

Die Spaltenanzahl und die Spaltendatentypen sind aber recht klein. Das mit 3000 multipliziert ergibt wohl paar kb. Also nichts was das Netzwerk blockiert.
Soweit ich es verstanden habe, sind zu viele Indezes wieder negativ oder nicht?
Die MySQL Version ist recht aktuell, sollte 5.6 sein.
Das war 1985 vielleicht so als RAM sauteuer war.

FlashBurn

2014-09-08, 15:43:40

Wie weit kann man das denn mit dem Index treiben? Ich meine ab wann wird es wieder schlechter für die Performance?

Ich muss nämlich noch eine andere Tabelle erstellen, wo es leider auch keinen eindeutigen Key gibt und ich somit wieder mit mind. einem Index arbeiten muss. Noch besser wäre es natürlich wenn ich noch mehr erstellen könnte.

Exxtreme

2014-09-08, 16:06:55

FlashBurn

2014-09-08, 16:29:57

Meiner Erfahrung nach wird es nie schlechter. Und ich habe Tabellen hier mit 10 Indizes drauf. Für jede halbwegs größere SQL-Anweisung einen speziellen und die haben alle einen Performance-Boost gebracht. Wichtig ist halt, dass genug RAM da ist. Wenn die Indizes nicht mehr in den RAM passen und das OS anfängt auszulagern dann ist's blöd.

Das wäre meine nächste Frage gewesen ;) Es macht also Sinn für jede Art (so fern nicht zu viele verschiedene) Abfrage einen eigenen Index zu erstellen.

Und eindeutige Identifikationsspalten schaden nicht.

Ich weiß, lässt sich halt nur leider nicht immer so umsetzen. Nicht jeder Mensch denkt daran das sowas ja mal für eine DB hilfreich sein könnte ;)

PatkIllA

2014-09-08, 16:37:33

Meiner Erfahrung nach wird es nie schlechter.Bei SQLite habe ich schon mehrfach ziemlich schlechte Auswahl des Indexes gesehen. Bei anderen Datenbanken musste ich mich damit zum Glück nicht so intensiv beschäftigen.

Wichtig ist halt, dass genug RAM da ist. Wenn die Indizes nicht mehr in den RAM passen und das OS anfängt auszulagern dann ist's blöd.Selbst mit lesen des Indizes von Platte sollte es in der Regel einen Full Table Scan schlagen.
Alle ernsthaften Serveranwendungen berücksichtigen die Menge des RAMs (oder die entsprechende Einstellung dazu). Der ganze IO Cache des OS wird dabei sowieso umgangen.

Exxtreme

2014-09-08, 16:54:44

Das wäre meine nächste Frage gewesen ;) Es macht also Sinn für jede Art (so fern nicht zu viele verschiedene) Abfrage einen eigenen Index zu erstellen.

Ich würde es nur für die machen, bei denen die Wartezeit störend ist. Ist keine Wartezeit da weil der Datensatz sofort aufploppt dann lohnt es sich eigentlich nicht. Das muss man aber von Fall zu Fall entscheiden. Auch 5 Sekunden Wartezeit kann störend sein wenn man bestimmte Masken 20x am Tag aufruft.

PatkIllA

2014-09-08, 17:01:08

Das wäre meine nächste Frage gewesen ;) Es macht also Sinn für jede Art (so fern nicht zu viele verschiedene) Abfrage einen eigenen Index zu erstellen.Der Index muss beim INSERT, UPDATE und DELETE aktualisiert werden.
Es bringt teilweise jede Menge Zeit, wenn man die Datenbank ohne Index erstellt, dann die Daten reinpumpt und später den oder die Index erstellt.
Es hat schon seinen Grund, warum es Leute gibt die Vollzeit nur Datenbanken bzw die Zugriffe optimieren. Und das sogar nur für ein einzelnen System.

Marscel

2014-09-08, 17:14:14

Das wäre meine nächste Frage gewesen ;) Es macht also Sinn für jede Art (so fern nicht zu viele verschiedene) Abfrage einen eigenen Index zu erstellen.

Ja/nein. Es macht Sinn, einen Index für jede Perspektive zu erstellen, die aus Sicht der Anwendung sinnvoll ist. Wenn ich z.B. eine 1-n Relation habe, dann schadet es sicher nicht einen Index auf die Fremdschlüsselspalte zu setzen, wenn ich die Abfrage danach essentiel für die Anwendung ist. Ohne Index sinkt die Performance mit wachsender Row-Zahl schneller als man vermutet, wenn ich ständig lesen möchte.

Wie erwähnt, wenn man einen ganzen Haufen Daten hat, die man erstmal hineinhieven möchte, dann ist es sinnvoller, die Indizes erst hinterher reinzuschaufeln oder temporär zu deaktivieren.

Und zu SQLite: Das ist total gaga. Wir haben hier auf Windows, Linux und Mac z.T. ganz anderes Verhalten bzgl. Setzen der Indizes. Wir haben es mit verschiedenen Setups ausprobiert und festgestellt: Zum Einfügen von mehreren Millionen Rows läuft es unter Windows am besten, unter Mac katastrophal. Beim Abfragen ist widerum Linux hier deutlich flotter als Windows, trotz mieserer CPU und HDD (knapp 1 Sek. vs. keine Verzögerung fühlbar) und selben Indizes. Dateisystem, Cache ...? kA.

Berni

2014-09-08, 20:10:15

Also bei Java JDBC gibts die Funktion setFetchSize() womit man für ein Statement einstellen kann, wieviele Ergebnisse lokal vorgehalten werden. Manche Treiber ignorieren das, der MySQL ConnectorJ reagiert da aber wirklich drauf. Default ist hier glaube ich, dass ALLE Sätze abgeholt werden, was natürlich ziemlich viel RAM brauchen kann lokal...und zudem kann es damit sein, dass es erstmal dauert bis alle Sätze da sind während man bei einem kleineren Wert schonmal anfangen könnte.
Ob das bei ODBC nun ähnlich ist, keine Ahnung.

Edit: Laut http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html gibts bei ODBC zumindest als Connection-Parameter "prefetch", was wohl ähnliches bewirkt...

arcanum

2014-09-08, 20:54:57

ich kann mich daran erinnern, dass ich zu dem schluss gekommen bin, dass ein client-side cursor genutzt wird, also komplett auf der client-seite landen. bei mir lief der application server ständig in heap size probleme, als ich große resultsets angefordert habe. habe mysql mit jdbc treiber und standard-settings genutzt.

hier ein link:
http://wiki.genexus.com/commwiki/servlet/hwiki?Client+and+server+cursors+-+using+MySQL,

RattuS

2014-09-08, 22:14:42

Recordsets mit üblichen Größen werden normalerweise immer vollständig übertragen. Das ist deutlich einfacher und natürlich auch schneller. Nur bei großen Datenmengen (BLOB/TEXT können das selbst bei nur wenigen Zeilen schon provozieren) oder zu geringem Speicher seitens Client wird - je nach Treiber - über einen Cursor gestreamed, aber nicht zwangsweise Zeile für Zeile.

FlashBurn

2014-09-09, 11:29:45

Das war jetzt mal viel Input ;)

Falls ich diese Woche noch zum Kunden komme, werde ich mal gucken wo genau die ganze Zeit verbraten wird.

Ich hab mal angefangen mich in die Index-Geschichte, vorallem Mehrspalten-Index, einzulesen.
Was mir gerade ein wenig Sorgen macht, wie bekomme ich raus wieviel Speicher der gesamte Index verbraucht?

Ich habe einfach mal grob und Pi mal Daumen gerechnet (aktuelle Dateigröße und Anzahl Datenzeilen darin) um auf den Jarhresverbrauch zu kommen und da komme ich auf >13GB. Das ist schon ordentlich, aber wieviel davon muss jetzt in den RAM für den Index?

FlashBurn

2014-09-09, 13:16:09

Ich habe gerade mal mit dem Index rumgespielt auf meiner DB.

Eine Sache kommt mir noch komisch vor, wenn ich mir mit EXPLAIN anzeigen lasse was die DB macht, wird immer der erste Index genommen!? Sollte er nicht den besseren Index nehmen?

Genauer habe ich ja einen Index der nur auf einer Spalte ist und habe jetzt noch einen über mehrere Spalten angelegt. Dann hab ich das mal ausprobiert und es wurde immer nur der alte Index genommen. Also hab ich den alten Index mal gelöscht und siehe da, der neue Index würde auch weniger Rows zurückliefern. Dann hab ich den alten Index wieder hinzugefügt (eine Spalte) und es wird immer noch der neue (mehrere Spalten) Index genommen. Entferne ich jetzt wieder den Index über mehrere Spalten und füge ihn wieder hinzu, wird wieder der Index über eine Spalte genommen, da er zu erst da war!?

Marscel

2014-09-09, 16:33:10

Was mir gerade ein wenig Sorgen macht, wie bekomme ich raus wieviel Speicher der gesamte Index verbraucht?

Ich habe einfach mal grob und Pi mal Daumen gerechnet (aktuelle Dateigröße und Anzahl Datenzeilen darin) um auf den Jarhresverbrauch zu kommen und da komme ich auf >13GB. Das ist schon ordentlich, aber wieviel davon muss jetzt in den RAM für den Index?

Die Gesamtgröße aller Indizes kannst du damit (http://dev.mysql.com/doc/refman/5.6/en/show-table-status.html) rauslesen. Ansonsten kann das eigentlich jedes seriöse Frontend für dich rausfinden.

Das System wird von sich aus relevante Anteile der Indexstruktur in brauchbarer Größe in den RAM laden und durchsuchen, über dessen Interna sollte man sich erstmal keine Gedanken machen.

Eine Sache kommt mir noch komisch vor, wenn ich mir mit EXPLAIN anzeigen lasse was die DB macht, wird immer der erste Index genommen!? Sollte er nicht den besseren Index nehmen?

Welche Indizes verwendet werden, hängt vom konkreten Query ab. Änder die Conditions und das Explain wird es dir anzeigen.

Genauer habe ich ja einen Index der nur auf einer Spalte ist und habe jetzt noch einen über mehrere Spalten angelegt. Dann hab ich das mal ausprobiert und es wurde immer nur der alte Index genommen. Also hab ich den alten Index mal gelöscht und siehe da, der neue Index würde auch weniger Rows zurückliefern. Dann hab ich den alten Index wieder hinzugefügt (eine Spalte) und es wird immer noch der neue (mehrere Spalten) Index genommen. Entferne ich jetzt wieder den Index über mehrere Spalten und füge ihn wieder hinzu, wird wieder der Index über eine Spalte genommen, da er zu erst da war!?

Wo soll ich anfangen ... Nochmal, man setzt nicht auf gut Glück Indizes in eine Tabelle, sondern danach, wie man sie braucht. Das hängt vom Modell und den damit verbundenen Queries ab. Wenn du einen Index über [a] und einen über [a,b] hast, dann vermutlich deswegen, weil du im Wesentlichen zwei Queries effizient bedienen musst:

* WHERE a = <Wert> und
* WHERE a = <WertA> AND b = <WertB>

Der Query-Planer erkennt schon, welcher der beiden Indizes sinnvollerweise verwendet werden muss, oder überhaupt. Das heißt, wenn du isoliert nach 'b' fragst (z.B. auch wg. eines ORs), hast du damit keinen Gewinn, genauso wenig wie wenn du irgendwo anders nach 'c' oder gar nichts einengend fragst.

FlashBurn

2014-09-10, 08:04:32

Welche Indizes verwendet werden, hängt vom konkreten Query ab. Änder die Conditions und das Explain wird es dir anzeigen.

Wo soll ich anfangen ... Nochmal, man setzt nicht auf gut Glück Indizes in eine Tabelle, sondern danach, wie man sie braucht. Das hängt vom Modell und den damit verbundenen Queries ab. Wenn du einen Index über [a] und einen über [a,b] hast, dann vermutlich deswegen, weil du im Wesentlichen zwei Queries effizient bedienen musst:

* WHERE a = <Wert> und
* WHERE a = <WertA> AND b = <WertB>

Der Query-Planer erkennt schon, welcher der beiden Indizes sinnvollerweise verwendet werden muss, oder überhaupt. Das heißt, wenn du isoliert nach 'b' fragst (z.B. auch wg. eines ORs), hast du damit keinen Gewinn, genauso wenig wie wenn du irgendwo anders nach 'c' oder gar nichts einengend fragst.
Alles gut und schön und genauso hab ich mir das auch vorgestellt, aber ...

Ich habe immer den selben Query verwendet und laut EXPLAIN wird immer der älteste Index genutzt. Das ist das was mich verwundert.
Ich kann es für die konkrete Tabelle also ruhig bei meinem alten Index belassen und für die neue muss ich mir überlegen wie ich den dort definiere oder ob ich nicht sogar mehrere nehme.