mysql, Query optimieren

[Jörg]

Hallo,

ich habe eine kleine Horrorquery, die ziemlich viel leisten muss, es auch tut, aber sich inzwischen auch seine Zeit dafür mimmt.

Und ich bin nicht sicher, wo ich anfangen soll, sie zu optimieren.

Sicher ist nur, dass sie optimiert wreden muss. Sie braucht so um die 7-8 Sekunden, wenn sie ungecached auf die DB trifft.

Wo kann ich ansetzen?

SELECT SQL_CALC_FOUND_ROWS DISTINCT
r.KundenID,
r....,
...da.ListenID,
ADDDATE(r.Rechnungsdatum,INTERVAL r.Faelligkeit DAY),
m.Mahnstatus,
ADDDATE(m.Mahndatum,INTERVAL m.Faelligkeit DAY),
r.bezahlt,
r.Storno,
UNIX_TIMESTAMP(ADDDATE(r.Rechnungsdatum,INTERVAL r.Faelligkeit DAY)),
UNIX_TIMESTAMP(ADDDATE(m.Mahndatum,INTERVAL m.Faelligkeit DAY)),
k.kdel,
SUM(ze.Bruttozahlung),
rz.GesamtRetoure
FROM _table_rechnungen r
LEFT JOIN _table_rechnungszuordnung rz ON r.RechnungenID = rz.RechnungenID
LEFT JOIN _table_zahlungseingang ze ON r.RechnungenID = ze.RechnungenID
LEFT JOIN _table_da_belege da ON da.BelegID = r.RechnungenID
LEFT JOIN _table_berechnungen b ON r.RechnungenID = b.RechnungenID
LEFT JOIN _table_mahnungen m ON ( r.RechnungenID = m.RechnungenID AND m.aktiv = 1)
AND m.RechnungenID =(SELECT MAX(tmp.RechnungenID) FROM _table_mahnungen tmp
    WHERE tmp.RechnungenID = r.RechnungenID AND m.aktiv = 1)
LEFT JOIN _table_kunden k ON r.KundenID = k.KundenID
WHERE r.Belegart = "R"
GROUP BY r.RechnungsNummer
ORDER BY r.RechnungenID DESC LIMIT 450,50

Explain zur Query.

Was ich z.b. selber schon gemerkt habe, ist, dass ich ohne die Spalte SUM(ze.Bruttozahlung) ca. 2 Sekunden spare, aber diese Spalte benötoge ich halt. 😕

Wenn ich quasi alle Spalten weglasse, aber die Where-Bedingung bleibt 1:1 erhalten, ist die Query auch signifikant schneller, was mich eigentlich wundert. Die wenigen Berechnungen in den Spalten sollten soviel doch gar nicht ausmachen, oder? Jörg

[Rolf B]

Hallo Jörg,

(1) GROUP BY und SUM

Du summierst nur einen Wert aus den Bruttozahlungen und damit ist es der Overkill, erstmal einen riesigen Join aufzuspannen und den mit GROUP BY wieder einzudampfen. Das geht besser. Dazu gleich, erstmal eine Predigt.

Du begehst mit deinem GROUP BY (wieder einmal?) den heißgeliebtesten Fehler aller MYSQL Nutzer. Korrektes SQL muss alle Spalten im Group By auflisten, die nicht aggregiert werden. Jede SQL Datenbank außer MySQL und seiner Stiefschwester MariaDB wirft einen Fehler, wenn man das missachtet, aber nur weil MySQL nicht meckert, ist es nicht automatisch gut.

Es ist deshalb falsch, weil für Spalten, die im GROUP BY nicht stehen und nicht aggregiert werden, nicht festgelegt ist, aus welcher der aggregierten Rows ihr Wert stammt.

Wenn das die Daten sind:

ID  Name   Wert
 1  Hugo     5
 1  Otto     6
 2  Paul     3
 3  Lisa     1
 3  Lara     9

dann würde das Statement

SELECT ID, Name, SUM(Wert)
FROM tabelle
GROUP BY ID

von MySQL und MariaDB ausgeführt, aber jede ordentliche Datenbank würde es Dir auf die Füße kotzen. Weil Name nicht im GROUP BY steht.

Ob dein MySQL nämlich

ID  Name   Wert
 1  Hugo    11
 2  Paul     3
 3  Lisa    10

oder

ID  Name   Wert
 1  Otto    11
 2  Paul     3
 3  Lara    10

liefert, ist undefiniert!!!1!1!!!elf!!1!

In deinem Fall gibt es einen einfachen Ausweg, weil Du nur eine Spalte summierst und die ze Tabelle nur für diesen Zweck joinst.

Du kannst diesen JOIN sowie den GROUP BY weglassen und statt dessen diesen Subselect verwenden:

...
k.kdel,
(SELECT SUM(ze.Bruttozahlung) 
     FROM _table_zahlungseingang ze 
     WHERE ze.RechnungenID = r.RechnungenID) as BruttoSumme,
rz.GesamtRetoure
...

Berechneten Spalten einen Aliasnamen zu geben ist übrigens guter Brauch, dann kannst Du sie nachher im PHP auch namentlich verwenden und musst nicht mit Spaltennummern arbeiten.

(2) _table_mahnungen

Was ich nicht verstehe, ist der JOIN der Mahnungen.

LEFT JOIN _table_mahnungen m
    ON ( r.RechnungenID = m.RechnungenID AND m.aktiv = 1)
      AND m.RechnungenID = (SELECT MAX(tmp.RechnungenID)
                            FROM _table_mahnungen tmp
                            WHERE tmp.RechnungenID = r.RechnungenID AND m.aktiv = 1)

MAX(tmp.RechnungenID) muss immer gleich r.RechnungenID sein, weil andere Rows nicht selektiert werden. Es könnte höchstens sein, dass _table_mahnungen keinen einzigen Satz zu dieser RechnungenID enthält, aber den Fall fängt schon die erste Join-Bedingung, vor dem AND, ab. Der ganze AND-Teil erscheint mir nutzlos und der Subselect darin dann auch.

(3) _table_berechnungen

Ob der LEFT JOIN der Tabelle _table_berechnungen nötig ist, weißt nur Du. Dein gezeigtes SQL verwendet keine Spalte aus dieser Tabelle.

Damit hast Du schonmal eine Menge Arbeit, hoffentlich nötzt se was.

Rolf

[Jörg]

Hallo Rolf.

erstmal vielen lieben Dank, dass Du Dich meines Problems annimmst.

(1) GROUP BY und SUM

Hm...wußte ich nicht. 😕

Ob dein MySQL nämlich

ID  Name   Wert
 1  Hugo    11
 2  Paul     3
 3  Lisa    10

oder

ID  Name   Wert
 1  Otto    11
 2  Paul     3
 3  Lara    10

liefert, ist undefiniert!!!1!1!!!elf!!1!

Oh...

In deinem Fall gibt es einen einfachen Ausweg, weil Du nur eine Spalte summierst und die ze Tabelle nur für diesen Zweck joinst.

Du kannst diesen JOIN sowie den GROUP BY weglassen und statt dessen diesen Subselect verwenden:

...
k.kdel,
(SELECT SUM(ze.Bruttozahlung) 
     FROM _table_zahlungseingang ze 
     WHERE ze.RechnungenID = r.RechnungenID) as BruttoSumme,
rz.GesamtRetoure
...

Habe ich gemacht, hat aber vergleichsweise wenig gebracht.

Berechneten Spalten einen Aliasnamen zu geben ist übrigens guter Brauch, dann kannst Du sie nachher im PHP auch namentlich verwenden und musst nicht mit Spaltennummern arbeiten.

Ich arbeite tatsächlich fast imme mit Nummern... eine Angewohnheit... 😉

(2) _table_mahnungen

Was ich nicht verstehe, ist der JOIN der Mahnungen.

LEFT JOIN _table_mahnungen m
    ON ( r.RechnungenID = m.RechnungenID AND m.aktiv = 1)
      AND m.RechnungenID = (SELECT MAX(tmp.RechnungenID)
                            FROM _table_mahnungen tmp
                            WHERE tmp.RechnungenID = r.RechnungenID AND m.aktiv = 1)

MAX(tmp.RechnungenID) muss immer gleich r.RechnungenID sein, weil andere Rows nicht selektiert werden. Es könnte höchstens sein, dass _table_mahnungen keinen einzigen Satz zu dieser RechnungenID enthält, aber den Fall fängt schon die erste Join-Bedingung, vor dem AND, ab. Der ganze AND-Teil erscheint mir nutzlos und der Subselect darin dann auch.

Liegt daran, dass es zu jeder Rechnung mehr als 1 Mahnung geben kann, die sich nach Mahnstufe unterscheiden und ich immer die höchste Mahnstufe nehmen wollte... ist aber vermutlich für den JOIN eh egal, oder?

(3) _table_berechnungen

Ob der LEFT JOIN der Tabelle _table_berechnungen nötig ist, weißt nur Du. Dein gezeigtes SQL verwendet keine Spalte aus dieser Tabelle.

Nein, die war nicht nötig...keine Ahnung, war die noch drin stand...ver,mu tlich hatte ich irgendwann mal nen Wert aus dsieser Tabelle genutzt, der aber weggefallen ist. Das Herausnehmen dieses JOINS hat sehr viel gebracht. Sicher 5 Sekunden oder so...

Leider bringts trotzdem unterm Strich nicht soviel, wie gewünscht, weil wohl auch mein PHP-Teil ziemlich viel Arbeit leisten muss. Da werde ich also auch nochmal ran müssen.

SQL mäßig bin ich jedenfalls durch Deine Hilfe schonmal auf ca. 1,5 Sek (von ca.7 sec) runter... danke!

Damit hast Du schonmal eine Menge Arbeit, hoffentlich nötzt se was.

Warum fällt mir hierzu die Feuerzangenbowle ein? War da mal was? 😉

Jörg

[Rolf B]

Hallo Jörg,

Liegt daran, dass es zu jeder Rechnung mehr als 1 Mahnung geben kann, die sich nach Mahnstufe unterscheiden und ich immer die höchste Mahnstufe nehmen wollte...

Ja ok, aber schau was da steht:

m.RechnungenID = (SELECT MAX(tmp.RechnungenID)
                            FROM _table_mahnungen tmp
                            WHERE tmp.RechnungenID = r.RechnungenID AND m.aktiv = 1)

Ein Auswählen der höchsten Mahnstufe erkenne ich da nicht.

Der Subselect wählt nur die Sätze aus, wo tmp.RechnungenID = r.RechnungenID ist. D.h. in den ausgewählten Mahnungszeilen sind die Werte für tmp.RechnungenID alle gleich, und zwar alle gleich r.RechnungenID. Davon ein Maximum zu bilden und damit zu vergleichen ist zwecklos.

Statt RechnungenID müsstest Du den maximalen Mahnstatus finden und damit vergleichen.

Mir ist aber was aufgefallen - prüf mal, ob deine Query noch sinnvolle Ergebnisse liefert. Ich habe nämlich übersehen, dass deine Relationen über die RechnungenID gebilet werden, der GROUP BY aber über RechnungenNummer. So richtig sinnvoll ist das wohl nur, wenn es zu einer RechnungenNummer mehrere RechnungenID gibt, und dann ist der SUM als Subselect wohl nicht zielführend. Deine ursprüngliche Query hat ja die Bruttozahlungen über alle Zahlungseingänge einer Rechnungsnummer gebildet, und nur eine Zeile pro Nummer geliefert. Ohne den GROUP BY RechnungenNummer liefert sie eine Zeile pro RechnungenID. Oder auch mehrere, wenn einer der LEFT JOINs mehr als eine Zeile liefert.

Wie man damit richtig umgeht, hängt von deinen Daten ab. Kannst Du sagen, wieviele Zeilen jeder LEFT JOIN maximal liefern kann? Wie stehen RechnungenNummer und RechnungenID zueinander in Beziehung, ist das 1:1, 1:N oder N:1? Ein M:N will ich jetzt mal ausschließen...

Prüfe dann noch, ob Du den DISTINCT in der ersten Zeile brauchst. Der kostet auf jeden Fall auch Zeit, und er war möglicherweise nur deshalb nötig, weil Du die Berechnungen-Tabelle unnötigerweise hinzugemischt hattest.

Rolf

[Raketenlogikbaustein]

Prüfe dann noch, ob Du den DISTINCT in der ersten Zeile brauchst. Der kostet auf jeden Fall auch Zeit, und er war möglicherweise nur deshalb nötig, weil Du die Berechnungen-Tabelle unnötigerweise hinzugemischt hattest.

Sonst wäre auch fraglich, was für einen sachlichen Sinn denn DISTINCT mit SUM haben soll…

[Rolf B]

Hallo Raketenlogikbaustein,

Sonst wäre auch fraglich, was für einen sachlichen Sinn denn DISTINCT mit SUM haben soll…

Es gibt auch Leute, die tragen Hosenträger zum Gürtel.

Oder programmieren das, was meine COBOL-Kollegen den Sicherheits-MOVE nennen:

let x = 0;
x = 42;

Rolf

[Jörg]

Hi Rolf,

m.RechnungenID = (SELECT MAX(tmp.RechnungenID)
                            FROM _table_mahnungen tmp
                            WHERE tmp.RechnungenID = r.RechnungenID AND m.aktiv = 1)

Ein Auswählen der höchsten Mahnstufe erkenne ich da nicht.

Stimmt, müsste heißen??:

m.RechnungenID = (SELECT tmp.RechnungenID
                            FROM _table_mahnungen tmp
                            WHERE tmp.RechnungenID = r.RechnungenID AND m.aktiv = 1 AND Mahnstatus = SELECT MAX(tmp.Mahnstatus) WHERE tmp.RechnungenID = r.RechnungenID)

Bitte nicht... bin grad etwas verwirrt. 😕

Der Subselect wählt nur die Sätze aus, wo tmp.RechnungenID = r.RechnungenID ist. D.h. in den ausgewählten Mahnungszeilen sind die Werte für tmp.RechnungenID alle gleich, und zwar alle gleich r.RechnungenID. Davon ein Maximum zu bilden und damit zu vergleichen ist zwecklos.

Ja, das sehe ich ein.

Statt RechnungenID müsstest Du den maximalen Mahnstatus finden und damit vergleichen.

Ja.

Mir ist aber was aufgefallen - prüf mal, ob deine Query noch sinnvolle Ergebnisse liefert. Ich habe nämlich übersehen, dass deine Relationen über die RechnungenID gebilet werden, der GROUP BY aber über RechnungenNummer. So richtig sinnvoll ist das wohl nur, wenn es zu einer RechnungenNummer mehrere RechnungenID gibt, und dann ist der SUM als Subselect wohl nicht zielführend. Deine ursprüngliche Query hat ja die Bruttozahlungen über alle Zahlungseingänge einer Rechnungsnummer gebildet, und nur eine Zeile pro Nummer geliefert. Ohne den GROUP BY RechnungenNummer liefert sie eine Zeile pro RechnungenID. Oder auch mehrere, wenn einer der LEFT JOINs mehr als eine Zeile liefert.

Wie man damit richtig umgeht, hängt von deinen Daten ab. Kannst Du sagen, wieviele Zeilen jeder LEFT JOIN maximal liefern kann? Wie stehen RechnungenNummer und RechnungenID zueinander in Beziehung, ist das 1:1, 1:N oder N:1? Ein M:N will ich jetzt mal ausschließen...

1:1 RechnungenID und RechnungsNummer sind beide unique und 1: 1 zugeordnet.

Prüfe dann noch, ob Du den DISTINCT in der ersten Zeile brauchst. Der kostet auf jeden Fall auch Zeit, und er war möglicherweise nur deshalb nötig, weil Du die Berechnungen-Tabelle unnötigerweise hinzugemischt hattest.

Mach ich.

Jörg

[Rolf B]

Hallo Jörg,

Bitte nicht...

Nein nein, entspann Dich, so nicht.

Was Du im Moment hast, ist (mit etwas mehr Einrückungen für bessere Forenlesbarkeit)

LEFT JOIN _table_mahnungen m
     ON ( r.RechnungenID = m.RechnungenID AND m.aktiv = 1)
    AND m.RechnungenID = (SELECT MAX(tmp.RechnungenID)
                          FROM _table_mahnungen tmp
                          WHERE tmp.RechnungenID = r.RechnungenID 
                            AND m.aktiv = 1)

Soweit ich erkennen kann, hat die Mahnungen-Tabelle diese Spalten:

• RechnungenID - Verknüpfung zu den Rechnungen
• Mahnstatus - Kann ich nicht einordnen. Könnte die Mahnstufe sein.
• Mahndatum - Wann wurde gemahnt
• Faelligkeit - Welches Ultimatum wurde in der Mahnung gestellt
• aktiv - 1 oder was anderes (vermutlich 0). Du willst nur aktive Mahnungen beachten, warum auch immer. Ist auch egal. Du willst es, du kriegst es!!! 😉

Achtung: Ich habe in deinen Beiträgen keine Spalte gesehen, die klar auf die Mahnstufe hinweist. Ich nehme daher an, dass sich die Mahnstufe in der Spalte Mahnstatus findet. Wenn das nicht stimmt, und es noch eine eigene Spalte für die Mahnstufe gibt, dann musst Du im Folgenden natürlich Mahnstatus durch den korrekten Spaltennahmen für die Mahnstufe ersetzen.

Dein erster Teil ( r.RechnungenID = m.RechnungenID AND m.aktiv = 1) findet alle aktiven Mahnungen zur RechnungenID. Die Klammern brauchst Du hier übrigens nicht, weil es danach mit AND weitergeht.

Aber du willst nur die mit dem höchsten Mahnstatus. Ich nehme an, dass eine Mahnung eindeutig durch das Wertepaar (RechnungenID, Mahnstatus) identifiziert wird. Wenn nicht - dann müssen wir da genauer hinschauen.

Die RechnungenID wird schon abgefragt, jetzt muss noch eine Abfrage auf Mahnstatus hinzukommen. Du musst die Zeile joinen, in der Mahnstatus die maximalen Mahnstufe für diese RechnungenID enthält. Bei der Maximumsuche darfst Du natürlich nur die aktiven Mahnungen berücksichtigen, sonst gehört das Maximum eventuell zu einer inaktiven Mahnung und dann bekommst Du gar nichts.

Dein Join muss also so aussehen:

LEFT JOIN _table_mahnungen m
     ON m.RechnungenID = r.RechnungenID 
    AND m.aktiv = 1
    AND m.Mahnstatus = (SELECT MAX(tmp.Mahnstatus)
                          FROM _table_mahnungen tmp
                          WHERE tmp.RechnungenID = r.RechnungenID 
                            AND tmp.aktiv = 1)

Den Tabellenalias tmp brauchst Du meines Wissens nicht. Probier's mal aus, ich bin nicht ganz sicher. Aber ich meine, dass in einem Subselect alle Spaltenangaben, die ohne explizite Angabe der Tabelle gemacht werden, auf den Subselect bezogen werden und nicht auf den äußeren Select.

Rolf

[Jörg]

Hallo Rolf,

Nein nein, entspann Dich, so nicht.

Ok. 😊

• RechnungenID - Verknüpfung zu den Rechnungen
• Mahnstatus - Kann ich nicht einordnen. Könnte die Mahnstufe sein.
• Mahndatum - Wann wurde gemahnt
• Faelligkeit - Welches Ultimatum wurde in der Mahnung gestellt
• aktiv - 1 oder was anderes (vermutlich 0). Du willst nur aktive Mahnungen beachten, warum auch immer. Ist auch egal. Du willst es, du kriegst es!!! 😉

Ja. Und viel mehr steht auch nicht in der Tabelle. Mahnstatus = Mahnstufe stimmt übrigens.

Aber du willst nur die mit dem höchsten Mahnstatus. Ich nehme an, dass eine Mahnung eindeutig durch das Wertepaar (RechnungenID, Mahnstatus) identifiziert wird. Wenn nicht - dann müssen wir da genauer hinschauen.

Von der Logik und in diesem Kontext ja. Zudem hat aber jede Mahnung ihr eigene ID.

Die RechnungenID wird schon abgefragt, jetzt muss noch eine Abfrage auf Mahnstatus hinzukommen. Du musst die Zeile joinen, in der Mahnstatus die maximalen Mahnstufe für diese RechnungenID enthält. Bei der Maximumsuche darfst Du natürlich nur die aktiven Mahnungen berücksichtigen, sonst gehört das Maximum eventuell zu einer inaktiven Mahnung und dann bekommst Du gar nichts.

Dein Join muss also so aussehen:

LEFT JOIN _table_mahnungen m
     ON m.RechnungenID = r.RechnungenID 
    AND m.aktiv = 1
    AND m.Mahnstatus = (SELECT MAX(tmp.Mahnstatus)
                          FROM _table_mahnungen tmp
                          WHERE tmp.RechnungenID = r.RechnungenID 
                            AND tmp.aktiv = 1)

Die funktioniert. Danke! 👍

Insgesamt ist auch eine deutliche Verbesserung der Geschwindigkeit erkennbar.

Wiue gesagt, der Rest hängt jetzt am php. Und da muss ich einfach mal schauen, ob irgendeine "Operation" da soviel Zeit frisst oder ob es einfach die Summe an Dingen ist, die php machen muss.

Was mir aber auffällt: Die Seite, die ausgeliefert wird (eine Rechnungsübersicht mit entsprechdenen Zusatzinfos) kommt nihct in einem Rutsch an, sondern baut sich erstmal ca. halb auf und anschließend kommt die 2. Hälfte, manchmal auch in 2 Etappen. Ist das ein Hinweis auf irgendwas?

Jörg

[Rolf B]

Hallo Jörg,

Insgesamt ist auch eine deutliche Verbesserung der Geschwindigkeit erkennbar.

Um zu unterscheiden, ob Du die Zeit im PHP oder im SQL verlierst, musst Du die Laufzeiten Etappe für Etappe messen. PHP ist allerdings im Allgemeinen sehr flink, die Wahrscheinlichkeit, dass Du da viel Zeit verlierst, ist nicht so hoch.

$startzeit = microtime(true);   // true: Liefere Float-Wert

// SQL Statement ausführen

echo "\n<!-- Nach SQL: " . (microtime(true) - $startzeit) . "ms -->\n";

// PHP Verarbeitung

$zeile = 1;
while ($row = /* fetch */) {
   // Berechnungen und Ausgaben für die Zeile

   echo "\n<!-- Nach Zeile $zeile: " . (microtime(true) - $startzeit) . "ms -->\n";
   $zeile++;
}

Du musst bei den echos nur drauf achten, dass Du diese Kommentare nicht mitten in ein HTML Tag hinschreibst. Guck Dir die Seite dann im Quelltext an (-> Browser Entwicklertools) und du siehst, wo die Zeit verloren geht. Wenn Du ohnehin einen eigenen Zeilenzähler hast, brauchst Du natürlich keinen eigenen hinzuzufügen.

Wenn Du ein funktionierendes Logging hast, kannst Du statt der echos auch ins Log schreiben.

Ich kann mir vorstellen, dass Du irgendwo eine Zeile hast, die nach nichts besonderem aussieht, die aber sehr viel mehr Zeit braucht. Das würde darauf hindeuten, dass der Puffer von PHP für die Querydaten nicht ausreicht und es an irgendeinem Punkt den Rest lesen muss. Das sollte von mysqli_query eigentlich abgefangen werden, weil es das Ergebnis komplett liest - aber verwendest Du vielleicht mysqli_real_query? Oder ein prepared statement, das Du dann mit fetch Row by Row liest?

Ich kann mir auch vorstellen, dass die Zeiten unauffällig sind und das HTML Ergebnis trotzdem in zwei Schwüngen kommt. Das würde darauf hindeuten, dass PHP die Ausgabe puffert, der Puffer irgendwann voll ist und ausgegeben wird und dann der Rest kommt. Guck Dir in deiner PHP.INI mal an, was bei output_buffering angegeben ist (mit ini_get, falls Du die PHP.INI nicht direkt lesen kannst).

Rolf