Geschwindigkeit und Performance PHP - MySQL

[Fabienne]

Hallo und guten Abend,

ich arbeite gerade an einem PHP-MySQL-Skript, das ca. 10000 Kundendaten aus mehreren Tabellen zusammenführen soll und in einer CSV ausgeben soll.
Dabei geht es mir Maßgeblich um die gesamte Performance des Skripts (derzeitige Laufzeit ca. 30min!!).

Folgende Tabellen gibt es (exemplarisch):

db.kunden
ID | Kundennummer | Name
Es gibt ca. 10000 Kundendatensätze

db.umsatz
ID | db.kunden.id | Umsatz | Jahr | erstellt_am
Es gibt pro Kunde mehrere Einträge (auch für ein Jahr, z.B. Aktualisierungen)

db.kontakte
ID | db.kunden.id | Name | Vorname | key
key ist ENUM [yes,no] = "Schlüssel-Person", kann nur einen pro Kunde geben

Momentanes Skript (sinngemäß, bitte nicht auf korrekte Schreibweisen achten :-))) ):

// Erstmal alle Kundendatensätze erfragen
SELECT Name FROM db.kunden
while($kundendaten=mysql_fetch_array())
{
  // Umsatz vom letzten Jahr, letzter Eintrag
    SELECT Umsatz FROM db.umsatz WHERE Jahr=2007 AND db.kunden.id=$kundendaten[id] ORDER BY erstellt_am DESC LIMIT 0,1
    $umsatzdaten_vorjahr=mysql_fetch_array();

// Umsatz von diesem Jahr, letzter Eintrag
    SELECT Umsatz FROM db.umsatz WHERE Jahr=2008 AND db.kunden.id=$kundendaten[id] ORDER BY erstellt_am DESC LIMIT 0,1
    $umsatzdaten_dieses_jahr=mysql_fetch_array();

// Hole den Key-Manager
    SELECT Name FROM db.kontakte WHERE db.kunden.id=$kundendaten[id] AND key=yes LIMIT 0,1
    $kundendaten=mysql_fetch_array();

// Daten ausgeben
    print $kundendaten[Name].";".
          $umsatzdaten_vorjahr[Umsatz].";".
          $umsatzdaten_dieses_jahr[Umsatz].";".
          $kontaktdaten[Name];
}

Nun muss bei jedem Kundendatensatz wieder erneut auf die DB zugegriffen werden, was meines Achtens nach die Performance stark schwächt.
Wäre es besser zuerst die ganzen Umsätze in einer Abfrage in ein assoziatives Array einzulesen und dann in der Form $alle_umsaetz[$kundendaten[id]][2007][Umsatz] wieder auszuspucken?
Wenn ja, wie bekomme ich alle diese Umsätze (pro Jahr nur einer, und zwar der zuletzt erstellte) in einer DB-Abfrage unter und anschließend in ein Array?

Könnte ich alles zusammen in einer einzigen "großen" DB-Abfrage vereinen und anschließend ausgeben?

Welche sonstigen Potenziale seht ihr?

Vielen Dank für Eure Antwort!
Fabienne

[Vinzenz Mai]

Hallo Fabienne,

Momentanes Skript (sinngemäß, bitte nicht auf korrekte Schreibweisen achten :-))) ):

[...]typisches Anfängerbeispiel: DB-Funktionalität durch PHP-Skript nachgeahmt

Könnte ich alles zusammen in einer einzigen "großen" DB-Abfrage vereinen und anschließend ausgeben?

Bestimmt, als Lesetipp unsere Join-Artikel:
Einführung in Joins
Fortgeschrittene Jointechniken

DB-Zugriffe sind teuer, verdammt teuer. Minimiere ihre Anzahl.

print $kundendaten[Name].";".
          $umsatzdaten_vorjahr[Umsatz].";".
          $umsatzdaten_dieses_jahr[Umsatz].";".
          $kontaktdaten[Name];

Du möchtest also zu Jedem Kunden:
a) den Kunden
b) den Gesamtumsatz aus dem aktuellen Jahr
   ist das die Summe der Einträge oder der zeitlich letzte Eintrag im Jahr
c) gleiches wie b) nur zum Vorjahr
d) Ansprechpartnerdaten.

Sieht nach zwei Joins und ein-, zwei Subselects aus.
Nichts schlimmes. Nichts, was bei Deinem Datenbestand auch nur in den
Minutenbereich kommen dürfte (vernünftige Indizierung vorausgesetzt).
Benötigt vermutlich MySQL 4.1 oder neuer und sollte kein großes Problem sein.

Ein paar Beispieldatensätze (keine echten natürlich) in den beteiligten
Tabellen und das daraus gewünschte Resultat - mit Begründung - wären hilfreich.

Freundliche Grüße

Vinzenz

[Fabienne]

Hallo Vinzenz,

[...]typisches Anfängerbeispiel: DB-Funktionalität durch PHP-Skript nachgeahmt

Jeder muss mal anfangen und dazulernen....

Bestimmt, als Lesetipp unsere Join-Artikel:

Vielen Dank! Werde ich mir "reinziehen"!

b) den Gesamtumsatz aus dem aktuellen Jahr
   ist das die Summe der Einträge oder der zeitlich letzte Eintrag im Jahr

zeitlich der letzte

Minutenbereich kommen dürfte (vernünftige Indizierung vorausgesetzt).

Gerade mal nachgemessen: Pro Datensatz zwischen 0,5 und 0,9 Sekunden!!!

db.kunden
ID | Kundennummer | Name
1 | 12345  | Müller GmbH
2 | 932749 | Moritz AG

db.umsatz
ID | db.kunden.id | Umsatz | Jahr | erstellt_am
15 | 1 | 1789.12 | 2007 | 2007-01-05
16 | 1 | 1812.15 | 2007 | 2007-01-08
17 | 2 | 66.09   | 2007 | 2007-01-05
18 | 1 | 89.99   | 2008 | 2008-01-16

db.kontakte
ID | db.kunden.id | Name | Vorname | key
1 | 1 | Meier   | Fritz  | no
2 | 1 | Metzger | Anton  | yes
3 | 2 | Kohl    | Helmut | yes

Ergebnis sollte sein:
Firma       ; 2007    ; 2008  ; Key-Manager

Müller Gmbh ; 1812.15 ; 89.99 ; Metzger
Moritz AG   ;   66.09 ;       ; Kohl

Vielen Dank für die schnelle Hilfe!
Fabienne

[Vinzenz Mai]

Hallo Fabienne,

[...]typisches Anfängerbeispiel: DB-Funktionalität durch PHP-Skript nachgeahmt
Jeder muss mal anfangen und dazulernen....

das war kein Vorwurf, der auf Dich gemünzt war.
Im Gegensatz zu Dir wollen viele gar nicht dazulernen und glauben, dass ihr
PHP-Code völlig richtig und angemessen sei.

b) den Gesamtumsatz aus dem aktuellen Jahr
   ist das die Summe der Einträge oder der zeitlich letzte Eintrag im Jahr
zeitlich der letzte

Wer lesen kann, ist klar im Vorteil. Beim aufmerksamen Lesen Deines Postings
habe ich das auch feststellen können.

Minutenbereich kommen dürfte (vernünftige Indizierung vorausgesetzt).
Gerade mal nachgemessen: Pro Datensatz zwischen 0,5 und 0,9 Sekunden!!!

Das ist verdammt viel, zu viel.

db.kunden
ID | Kundennummer | Name
1  | 12345        | Müller GmbH
2  | 932749       | Moritz AG

db.umsatz
ID | kunden_id | Umsatz  | Jahr | erstellt_am
15 | 1         | 1789.12 | 2007 | 2007-01-05
16 | 1         | 1812.15 | 2007 | 2007-01-08
17 | 2         | 66.09   | 2007 | 2007-01-05
18 | 1         | 89.99   | 2008 | 2008-01-16

db.kontakte
ID | kunden_id | Name | Vorname   | key
1  | 1         | Meier   | Fritz  | no
2  | 1         | Metzger | Anton  | yes
3  | 2         | Kohl    | Helmut | yes

Ergebnis sollte sein:
Firma       ; 2007    ; 2008  ; Key-Manager

Müller Gmbh ; 1812.15 ; 89.99 ; Metzger
Moritz AG   ;   66.09 ;       ; Kohl

OK, der Reihe nach:

Ein paar Anmerkungen zu Feldnamen:
Reservierte Worte wie "key" zu verwenden, ist keine gute Idee.
Es ist bei generiertem Code immer eine gute Idee, sicherheitshalber alle
Namen von Tabellen und Spalten zu maskieren. Bei MySQL ist der Backtick das
Maskierungszeichen.
Punkte in Spaltennamen zu verwenden, ist eine ganz extrem schlechte Idee.
Die ist noch schlechter als die Verwendung von reservierten Worten. Punkte
trennen bei den diversen SQL-Dialekten Datenbanken, Schemata, Tabellen und
Spaltennamen bei vollqualifizierten Namen.

1. Schritt: Ermittle die Key-Manager der Kunden:

  
SELECT  
    kunden.Name AS Firma,            -- mit kann man freundliche Namen vergeben  
    kontakte.`key` AS `Key-Manager`  -- key ist ein Schlüsselwort und muss daher  
                                     -- maskiert werden. Minuszeichen sind auch  
                                     -- nicht gut :-)  
FROM  
    kunden  
LEFT JOIN                            -- Wir nehmen auch Firmen mit, die uns  
    kontakte                         -- keinen Key-Manager genannt haben  
ON  
    kunden.id = kontakte.id_kunden  

2. Schritt: Ermittle den Umsatz der Kunden im Jahr 2007:
Das erfordert eine korrelierte Unterabfrage, siehe dazu z.B. dieses Archivposting:

  
SELECT                               -- Gib mir  
    u.kunden_id,                     -- die id des Kunden  
    u.umsatz                         -- und seinen Umsatz  
FROM                                 -- aus der Tabelle  
    umsatz u                         -- umsatz, die ich über das Alias u anspreche  
WHERE                                -- wobei nur der Umsatz angezeigt wird,  
    u.erstellt_am = (                -- bei dem das Erstellungsdatum  
        SELECT                       --  
            MAX(um.erstellt_am)      -- das maximale und somit neueste Datum  
        FROM  
            umsatz um                -- in der Tabelle umsatz, die hier über  
                                     -- um angesprochen wird  
        WHERE                        -- für  
            um.kunden_id = u.kunden_id  -- jede kunden_id  
            AND Jahr = 2007             -- und das Jahr 2007  
     )  

3. Der Umsatz der Kunden für das Jahr 2008 kann analog ermittelt werden.

4. Nun bauen wir die Jahresumsätze ein.
   Durch einen LEFT JOIN auf die Jahresumsätze wird berücksichtigt, dass auch
   Kundendaten angezeigt werden, von Kunden, die in wenigstens einem der Jahre
   keinen Umsatz gemacht haben (z.B. Neukunden)

  
SELECT  
    kunden.Name AS Firma,  
    u2007.umsatz as `Umsatz 2007`,   -- Aussagekräftige Spaltenüberschriften,  
    u2008.umsatz as `Umsatz 2008`,   -- die Maskierung erfordern  
    kontakte.`key` AS `Key-Manager`  
FROM  
    kunden  
LEFT JOIN                            -- Wir nehmen auch Firmen mit, die uns  
    kontakte                         -- keinen Key-Manager genannt haben  
ON  
    kunden.id = kontakte.id_kunden  
LEFT JOIN (                          -- will ich Daten aus einem Subselect,  
    SELECT                           -- das ich als "Tabelle" anspreche, so  
        u.kunden_id,  
        u.umsatz  
    FROM  
        umsatz u  
    WHERE  
        u.erstellt_am = (  
            SELECT  
                MAX(um.erstellt_am)  
            FROM  
                umsatz um  
            WHERE  
                um.kunden_id = u.kunden_id  
                AND Jahr = '2007'  
        )  
) u2007                          -- muss ich dafür Namen vergeben.  
ON kunden.id = u2007.kunden_id  
  
LEFT JOIN (  
    SELECT  
        u.kunden_id,  
        u.umsatz  
    FROM  
        umsatz u  
    WHERE  
        u.erstellt_am = (  
            SELECT  
                MAX(um.erstellt_am)  
            FROM  
                umsatz um  
            WHERE  
                um.kunden_id = u.kunden_id  
                AND Jahr = '2008'        -- In MySQL kann und sollte man auch Zahlen  
                                         -- als Zeichenketten übergeben  
        )  
) u2008  
ON kunden.id = u2008.kunden_id  

sollte das gewünschte Ergebnis liefern, MySQL 4.1.x vorausgesetzt.
Getestet (mit anderen Daten) mit MySQL 5.0.45

Für die Performance wichtig sind Indexe für die Spalten kunden_id in den
Tabellen umsatz und kontakte.

Anschließend kannst Du die Daten mit PHP wie gewohnt ausgeben.

Freundliche Grüße

Vinzenz