Hilfe/Tipps - Abfragen optimieren - evtl. Datenbankstruktur ändern

[news86]

Hallo zusammen,

ich habe mein erstes Projekt bekommen und muss eine Statistikauswertung entwickeln. In die Tabelle kommen laufend Daten an - ca. 20.000 pro Tag bzw. ca. 5 Mio. pro Jahr (das heißt es wird immer mehr – aktuell 19 Mio.). Ich habe 3 Detailtabellen (Ort, Rolle, Link) und eine Verknüpfungstabelle wo alle Tabellen mit einander verbunden werden.
Nun will mein Chef z.B. Wissen wie oft der Kunde in Berlin als Anwender den Linktyp 7 in den letzten 6 Monaten aufgerufen hat. Die Abfragenkriterien will er per PHP-Frontend ändern und andere Abfragen starten. Die Abfragen funktionieren auch richtig - nur es dauert viel zu lang.

Nun zur meiner Frage: Wie kann ich die Abfragen bzw. die Datenbankstruktur ändern damit die Abfragen super schnell erledigt werden können?

Tabellenaufbau: mySQL

tblOrt

intID	strOrtsname
1	München
2	Berlin

tblRolle

intID	strRolleName
1	Anwender
2	Techniker

tblLink

intID	strLink	intTyp	bolIsFavorit
1	http://www.google.de	2	false
2	http://www.intranet.de	7	true

tblStatistik

intID	intOrt	intRolle	intLink	tspZeit
1	1	2	2	01.01.2013 05:10:02
2	1	1	1	22.04.2013 12:17:42

Beispielabfrage

SELECT COUNT(tblStatistik. intOrt) as anzahl, 
                               tblLink.strLink, tblOrt.strOrt, tblRolle.strRolleName
                              FROM tblStatistik_
                                INNER JOIN tblLink ON 
                                  tblStatistik.intLink = tblLink.intID
                                INNER JOIN tblOrt ON
                                  tblStatistik.intOrt = tblOrt.intID
                               WHERE (tblStatistik. tspZeit BETWEEN "20130505000000" AND  
                                    AND "20130606235959")                               
                               	  AND (tblLink.intTyp = 7)
 AND (tblOrt. strOrtsname LIKE "München")
                               	 AND tblStatistik.intRolle LIKE 
                        (SELECT tblRolle.intID 
                          FROM tblRolle WHERE tblRolle.strRollName LIKE 'Anwender') 
GROUP BY tblStatistik.intLink

 

[Yaslaw]

Wie kann ich die Abfragen bzw. die Datenbankstruktur ändern damit die Abfragen super schnell erledigt werden können?

Zeig doch mal ein SQL dazu.

Zudem, wo hast du was für Indexe?

 

[news86]

ich habe keine Indexe gesetzt, muss man es?

 

[Yaslaw]

In deinem Fall: JA!
Auf alle Schlüssel gehören Indexe.
Also bei jeder Datentabelle ein UNIQUE INDEX auf die ID
Bei der tblStatistik einen Index (nicht UNIQUE) über die 3 Spalten intOrt, intRolle, intLink
Dann ggf noch ein Index über den Timestamp

Bei der Tabelle noch ein BITMAP Index über intTyp und einen weiteren über bolIsFavorit.

Ich ba mal dein SQL durch den Formatter geschickt um es besser lesen zu können

SELECT 
    COUNT(tblstatistik.intort) AS anzahl, 
    tbllink.strlink, 
    tblort.strort, 
    tblrolle.strrollename 
FROM 
    tblstatistik_ 
    INNER JOIN tbllink ON tblstatistik.intlink = tbllink.intid 
    INNER JOIN tblort ON tblstatistik.intort = tblort.intid 
WHERE 
    (
        tblstatistik.tspzeit BETWEEN "20130505000000" 
        AND 
        AND "20130606235959"
    ) 
    AND (tbllink.inttyp = 7) 
    AND (
        tblort.strortsname LIKE 
    ) 
    AND tblstatistik.introlle LIKE (
        SELECT 
            tblrolle.intid 
        FROM 
            tblrolle 
        WHERE 
            tblrolle.strrollname LIKE 'Anwender'
    ) 
GROUP BY 
    tblstatistik.intlink

Du hast da ganz am Anfang im WHERE ein AND zuviel

Warum hast du die Rolle mittels LIKE im WHERE eingebaut? Mach auch da ein INNER JOIN draus.
Ggf gard als Subquery. Beim LIKE kann es sein, dass er für jede Zeile das Subquery ausführt.
Mein Vorschlag (ohne Garantie!) (und die Indexe nicht vergessen)

SELECT 
    COUNT(tblstatistik.intort) AS anzahl, 
    tbllink.strlink, 
    tblort.strort, 
    tblrolle.strrollename 
FROM 
    tblstatistik_ 
    INNER JOIN tbllink ON tblstatistik.intlink = tbllink.intid 
    INNER JOIN tblort ON tblstatistik.intort = tblort.intid 
    INNER JOIN (
        SELECT  tblrolle.intid 
        FROM    tblrolle 
        WHERE   tblrolle.strrollname LIKE 'Anwender'
    ) AS r ON r.intid = tblstatistik.introlle
WHERE 
    tblstatistik.tspzeit BETWEEN 20130505000000 AND 20130606235959
    AND tbllink.inttyp = 7
    AND tblort.strortsname LIKE 
GROUP BY 
    tblstatistik.intlink

Und auch hier gilt leider

Performance-Verbesserungen sind keine eindeutige und einfache Sache. Es ist ein testen und heran tasten. Auswerten von Explainplans, SQL umschreiben - ggf. total umschreiben, weiter testen, Anzahl Datensätze der Tabelle vergleichen, ggf. Subqueries machen um die Datenmenge so früh wie möglich klein zu kriegen etc.
Also, so einfach *schnipp*und ein SQL ist schnell - das kannst du vergessen. Diese ganzen Punkte die ich aufgezählt habe gehen auch nicht einfach so mit im Forum hin und her schreiben. Lies dich in das Thema ein. Verscuh es zu verstehen. Und dann setz dich hin und probier dich durch.

 

[news86]

erstmal vielen Dank für deine Tipps, ich werde mich mal damit befassen.

Ist die Struktur so in Ordnung? Ich habe vor bei der Abfrage den gesamten gesuchten Zeitraum in eine sog. Temp-Tabelle zu kopieren, weil der Zeitraum sich bei den Abfragen nicht so oft ändert. Alle weiteren Abfragen finden dann in der kleineren Tabelle statt. Ist das eine gute Idee?

Bei der Tabelle noch ein BITMAP Index über intTyp und einen weiteren über bolIsFavorit.

was ist denn bitte BITMAP und wie kann es angelegt werden?

Werden die Abfragen mit Indexen schneller? Muss ich auch den Format der Tabellen ändern, aktuell habe ich MyISAM. InnoDB soll auch große Datenbanken geeignet sein.

 

[BaseBallBatBoy]

Mach es so wie Yaslaw gesagt, hat. Alles per Inner Join und Indizes.

Zusatzfrage:
>Nun will mein Chef z.B. Wissen wie oft der Kunde in Berlin als Anwender den Linktyp 7 in den letzten 6 Monaten aufgerufen hat. Die Abfragenkriterien will er per PHP-Frontend ändern und andere Abfragen starten.

Will dein Chef immer Einschränkungen auf allen 3 Tabellen haben? Oder soll auch der Fall möglich sein, wo nur Anwender mit Linktyp 7 gesucht werden, aber für alle Orte?

Falls immer 3 Einschränkungen: erweitere jeden Inner Join mit einem Subselect wie bereits bei tblrolle gezeigt. Dann gibt es zusätzlich nur die Zeit im Where.

Nachtrag:
Ja, Indizes sind schneller. Das ist deren Zweck, die Abfrage zu beschleunigen. Hier gibt's gute Infos dazu:
http://use-the-index-luke.com/de/sql/vorwort

 

[news86]

Will dein Chef immer Einschränkungen auf allen 3 Tabellen haben? Oder soll auch der Fall möglich sein, wo nur Anwender mit Linktyp 7 gesucht werden, aber für alle Orte?

Die Abfragen sind dynamisch, d.h. es kann sein das alle Kriterien festlegt werden, oder nur eins davon.

 

[BaseBallBatBoy]

Wenn Ort, Rolle und Link nicht allzu gross sind, würde ich alle Einschränkungen im Where machen.
Falls nicht, kannst du in deinem PHP dafür sorgen dass im Inner Join Subquery entweder die Einschränkung verwendet wird, oder falls es keine Einschränkung gibt stattdessen Where 1=1 geschrieben wird. Das entspricht allen Datensätzen.

 

[Yaslaw]

was ist denn bitte BITMAP und wie kann es angelegt werden?

Sorry mein Fehler. MySQL kennt glaub keine Bitmapindexe (Schade)

 

[news86]

Tabelle
Ort - 90 Datensätze
Rolle - 30 Datensätze
Link - 5000 Datensätze

Das heißt nie mit INNER JOIN arbeiten sondern immer mit WHERE Subquery?