Weiter Zurück [Inhalt] Online Suche im Handbuch

28.18 EXPLAIN (Tuning der Geschwindigkeit)

Vorab ein wirklich einfaches Beispiel der Komplexität falscher Abfragen: 

Tabelle 1

Zeile   Bemerkungen
===================
Zeile 1 Tabelle 1 
Zeile 2 Tabelle 1 
Zeile 3 Tabelle 1 
Zeile 4 Tabelle 1 
Zeile 5 Tabelle 1 
Zeile 6 Tabelle 1


Tabelle 2

Zeile   Bemerkungen
===================
Zeile 1 Tabelle 2 
Zeile 2 Tabelle 2 
Zeile 3 Tabelle 2 
Zeile 4 Tabelle 2 
Zeile 5 Tabelle 2 
Zeile 6 Tabelle 2

Wir verknüpfen nun Tabelle 1 mit Tabelle 2: 

SELECT * FROM TABELLE1, TABELLE2

ZEILE BEMERKUNGEN ZEILE BEMERKUNGEN 
=================================== 
Zeile 1 Tabelle 1 Zeile 1 Tabelle 2 
Zeile 1 Tabelle 1 Zeile 2 Tabelle 2 
Zeile 1 Tabelle 1 Zeile 3 Tabelle 2 
Zeile 1 Tabelle 1 Zeile 4 Tabelle 2 
Zeile 1 Tabelle 1 Zeile 5 Tabelle 2 
Zeile 1 Tabelle 1 Zeile 6 Tabelle 2 
Zeile 2 Tabelle 1 Zeile 1 Tabelle 2 
Zeile 2 Tabelle 1 Zeile 2 Tabelle 2 
Zeile 2 Tabelle 1 Zeile 3 Tabelle 2 
Zeile 2 Tabelle 1 Zeile 4 Tabelle 2 
Zeile 2 Tabelle 1 Zeile 5 Tabelle 2 
Zeile 2 Tabelle 1 Zeile 6 Tabelle 2 
Zeile 3 Tabelle 1 Zeile 1 Tabelle 2 
Zeile 3 Tabelle 1 Zeile 2 Tabelle 2 
Zeile 3 Tabelle 1 Zeile 3 Tabelle 2 
Zeile 3 Tabelle 1 Zeile 4 Tabelle 2 
Zeile 3 Tabelle 1 Zeile 5 Tabelle 2 
Zeile 3 Tabelle 1 Zeile 6 Tabelle 2 
Zeile 4 Tabelle 1 Zeile 1 Tabelle 2 
Zeile 4 Tabelle 1 Zeile 2 Tabelle 2 
Zeile 4 Tabelle 1 Zeile 3 Tabelle 2 
Zeile 4 Tabelle 1 Zeile 4 Tabelle 2 
Zeile 4 Tabelle 1 Zeile 5 Tabelle 2 
Zeile 4 Tabelle 1 Zeile 6 Tabelle 2 
Zeile 5 Tabelle 1 Zeile 1 Tabelle 2 
Zeile 5 Tabelle 1 Zeile 2 Tabelle 2 
Zeile 5 Tabelle 1 Zeile 3 Tabelle 2 
Zeile 5 Tabelle 1 Zeile 4 Tabelle 2 
Zeile 5 Tabelle 1 Zeile 5 Tabelle 2 
Zeile 5 Tabelle 1 Zeile 6 Tabelle 2 
Zeile 6 Tabelle 1 Zeile 1 Tabelle 2 
Zeile 6 Tabelle 1 Zeile 2 Tabelle 2 
Zeile 6 Tabelle 1 Zeile 3 Tabelle 2 
Zeile 6 Tabelle 1 Zeile 4 Tabelle 2 
Zeile 6 Tabelle 1 Zeile 5 Tabelle 2 
Zeile 6 Tabelle 1 Zeile 6 Tabelle 2

Wie man sehen kann, multipliziert sich die Menge der in den Tabellen enthaltenen Daten. Das wird im Allgemeinen als kartesiches Produkt bezeichnet, und ähnelt dem Vektorprodukt in der Mathematik. Man sollte sich also immer genau Gedanken darüber machen, wie man die Menge der Daten vor einer Weiterverarbeitung (im SQL Statement) einschränken kann.

Ein weiteres Beispiel für eine Beschleunigung um Faktor 100 bei einem einfachen Statement mit count(*). Hiermit sollen die Spalten einer Ausgabe gezählt werden: Versuch 1: 

select count(*) from tbl where a=# and c=0; 

Die Antwortzeiten:

0.16111115813255 
0.073595218658447 
0.10604506850243
MySQL scheint recht schnell zu laufen, allerdings gibt es immer starke Schwankungen bei der Gesamtzeit, bis die Ausgabe auf dem Bildschirm erscheint. Obwohl die internen Zeiten für "time elapsed" recht schnell sind, können durchaus 1 bis 5 Sekunden vergehen, bis das Ergebnis angezeigt wird. MySQL scheint also noch viel Zeit mit anderen Dingen zu verbringen.....

Dasselbe Beispiel ohne count(), Versuch 2: 


select a from tbl where a=# and c=0; 

Die Antwortzeiten:

0.0011586213111877 
0.0015420305728912 
0.0011256754398346
tbl besitzt einen eindeutigen index(a,b) und einen nicht eindeutigen index(a,c). Die Zahl der Einträge liegt zwischen 0 und 1000, Versuch 3: 

select b from tbl where a=# and c=0; 

Die Antwortzeiten:

0.0012334552731355 
0.0016372671293174 
0.0011582265184521

Vergleicht man nun die Ausgabezeiten der Versuche 1-3 miteinander, dann stellt man fest, daß MySQL im dritten Versuch unglaublich schnell geworden ist:

Versuch 1: Durchschnitt ist 0.15165980696678, maximale Ausgabezeit ist 9.3659509420395

Versuch 2: Durchschnitt ist 0.2846958220005, maximale Ausgabezeit ist 5.8125519752502

Versuch 3: Durchschnitt ist 0.2899955201149, maximale Ausgabezeit ist 0.071884989738464

Das Geheimnis liegt in der Anzahl der Verknüpfungen, die der JOIN erzeugt und der Geschwindigkeit der INDEX Tabellen. Es lohnt sich sehr, mit JOINS, INDEXen u.s.w. herumzuspielen. Der Geschwindigkeitsvorteil kann bis zu Faktor 1000 betragen. Folgendes Beispiel macht es vielleicht noch klarer:

Ein weiteres Beispiel mit dem EQUI-JOIN: 

SELECT A.EineSpalte, B.EineAndereSpalte 
FROM Tabelle1 AS A, Tabelle2 AS BWHERE A.Ein Code = B.EinAndererCode;
Man kann ihn aber auch ganz anders schreiben, und die Ergebnismenge wird die gleiche sein, nämlich so : 

SELECT A.EineSpalte, B.EineAndereSpalte
FROM Tabelle1 AS A JOIN Tabelle2 AS B       ON A.Ein Code = B.EinAndererCode;

Wenn die Ergebnismenge die gleiche ist, wo liegt dann der Unterschied zwischen diesen beiden Formen ? Gibt es überhaupt einen Unterschied ?

Der Unterschied liegt in der Laufzeit. Im ersten Beispiel wird zuerst das kartesische Produkt aus beiden Tabellen gebildet (jede Zeile aus Tabelle1 wird mit jeder Zeile aus Tabelle2 verknüpft), und wenn beide Tabellen nur jeweils 100 Zeilen enthalten, sind das schon 10.000 Zeilen in der temporären Zwischentabelle. Erst dann werden die Zeilen gelöscht, die nicht die WHERE Bedingung erfüllen.

Im zweiten Fall wird zuerst die Bedingung im ON Teil geprüft und nur solche Zeilen in die Zwischentabelle übernommen, bei denen die Bedingung erfüllt ist. In dem Beispiel mit den je 100 Zeilen pro Tabelle sind das wahrscheinlich nicht mehr als 100 Zeilen. Das ist ein Faktor von 10 !

Bei noch größeren Tabellen wird dann der Geschwindigkeitsunterschied dramatisch. Faktor 1000 sind dann keine Seltenheit.

Hier noch ein kleines Beispiel:

Ziel: Es soll die Zahl der Personen in einem Ort ausgegeben werden, die keiner Niederlassung angehören: 

select distinct(a.ort), count(a.id) as anzahl 
  from person a, 
       rel_niederlassung_person b 
 where a.id != b.person_id 
 group by ort 
 order by ort
Tabelle person enthält ca. 4000 records.

Ergebnis: 0.60 sekunden execution time.

Eine andere Möglichkeit ist diese: 

select distinct(a.ort), count(a.id) as anzahl 
     from person a 
left join rel_niederlassung_person b 
       on a.id != b.person_id 
    group by ort 
    order by ort
Ergebnis: 0.88 Sekunden execution time.

Hier nun eine Möglichkeit, sehen zu können, wie die MySQL Datenbank intern arbeitet: 

EXPLAIN SELECT select_options

Wenn einem SELECT Statement das Schlüsselwort EXPLAIN vorangestellt wird, dann werden Informationen über den Prozeß SELECT ausgegeben, welche Tabellen ausgelesen werden, und in welcher Reihenfolge.

Mit der Hilfe von EXPLAIN kann man sehen, wann man INDIZES in Tabellen hinzufügen muß, damit die Datenbankabfrage schneller ablaufen kann.

Um eine bestimmte Reihenfolge für JOINS zu erzwingen, muß der Ausdruck STRAIGHT_JOIN angewendet werden.

Ein Beispiel: 

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
            tt.ProjectReference, tt.EstimatedShipDate,
            tt.ActualShipDate, tt.ClientID,
            tt.ServiceCodes, tt.RepetitiveID,
            tt.CurrentProcess, tt.CurrentDPPerson,
            tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
            et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
            AND tt.ActualPC = et.EMPLOYID
            AND tt.AssignedPC = et_1.EMPLOYID
            AND tt.ClientID = do.CUSTNMBR;

Ohne daß uns nun die Definitionen der Datenbank interessieren müssen, liefert MySQL beispielsweise folgende Ausgabe: 

table type possible_keys                key  key_len ref  rows  Extra
et    ALL  PRIMARY                      NULL NULL    NULL 74
do    ALL  PRIMARY                      NULL NULL    NULL 2135
et_1  ALL  PRIMARY                      NULL NULL    NULL 74
tt    ALL  AssignedPC,ClientID,ActualPC NULL NULL    NULL 3872
      range checked for each record (key map: 35)

Die Angabe ALL (type) für alle Tabellen zeigt uns, daß MySQL einen vollen JOIN für alle Tabellen durch führen muß. Das wird eine sehr lange Zeit beanspruchen, da hier 74 * 2135 * 74 * 3872 = 45,268,558,720 Reihen durchgearbeitet werden müssen. Also Vorsicht !

Ein Problem mit MySQL ist, daß es noch nicht effektiv mit Indizes auf Spalten umgehen kann, falls diese unterschiedlich deklariert sind. In diesem Zusammenhang sind CHAR und VARCHAR identisch, solange diese dieselbe Länge besitzen. Um dieses Problem zu lösen, und MySQL zu beschleunigen muß mit dem Befehl ALTER dafür gesorgt werden, daß die Spalten tt.ActualPC und et.EMPLOYID beide das Format VARCHAR(15) haben: 

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

Ein erneutes Starten von EXPLAIN geibt z.B. dann folgende Ausgabe: 

table type   possible_keys   key     key_len ref         rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL 3872    where used
do    ALL    PRIMARY         NULL    NULL    NULL        2135
      range checked for each record (key map: 1)
et_1  ALL    PRIMARY         NULL    NULL    NULL        74
      range checked for each record (key map: 1)
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC 1

Das ist noch nicht perfekt, aber schon um den Faktor 74 schneller !

Eine weitere Optimierung kann dadurch erfolgen, daß die Längen der Vergleiche von tt.AssignedPC = et_1.EMPLOYID und tt.ClientID = do.CUSTNMBR angeglichen werden: 

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

EXPLAIN zeigt nun: 

table type   possible_keys   key     key_len ref            rows     Extra
et    ALL    PRIMARY         NULL    NULL    NULL           74
tt    ref    AssignedPC,ClientID,ActualPC ActualPC 15 et.EMPLOYID 52 where
used
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

Wie man nun sehen kann, haben wir die Datenbank nocheinmal um einen Faktor von 2872 * 2135 = 6.131.720 ! beschleunigt. Besser geht´s nicht. Es ist schon ein Unterschied, ob die Abfrage nur 0.1 Sekunden, oder Stunden dauert !

Viele kommerzielle Datenbanken besitzen sogenannte "query optimizer", in welchen diese automatisch Optimierungen durchführen. In unserem Falle würden wahrscheinlich ORACLE und ADABAS dieses SELECT Statement automatisch verbessern und intern die Abfrage (heimlich) umstrukturieren, sodaß die höchstmögliche Performance auch ohne "Handoptimierung" erzielt würde. Verlassen kann man sich aber nicht darauf. Ich kenne keinen Fall von komplexen, größeren Datenbanken, wo nicht ein Spezialist des Datenbankherstellers für teures Geld vor Ort die Datenbank nocheinmal hätte Optimieren müssen. Vielleicht erinnern Sie sich noch an die Wette von Larry Allison, dem Chef von ORACLE, als er behauptete, ORACLE wäre um den Faktor 1000 bei einem bestimmten Benchmark schneller, als Microsoft SQL Server 6.5 (dahinter steckt SYBASE). Nun, wie wir gesehen haben, kann man also durch intelligente QUERY Optimizer durchaus noch mehr (Faktor Millionen !) an Geschwindigkeit herausholen. Wer sich also im Vorhinein gründlich Gedanken um den Aufbau seiner Datenbank macht, der kann sich Nachoptimierungen ersparen. Man sollte also direkt zu Anfang beim Aufbau der Datenbankstruktur schon einen Spezialisten hinzuziehen, es macht sich schnell bezahlt.

Ein verbleibendes Problem ist, daß MySQL annimmt, daß die Werte in der Spalte tt.ActualPC eventuell zerstreut sind, was aber nicht der Fall ist. Es ist aber einfach, dies MySQL mitzuteilen: 

shell> isamchk --analyze Pfad_zur_Mysql_Datenbank/tt
shell> mysqladmin refresh

Nun ist die interne Struktur der Tabelle gut geordnet, sodaß ein JOIN optimal ablaufen kann: 

table type   possible_keys   key     key_len ref            rows    Extra
tt    ALL    AssignedPC,ClientID,ActualPC NULL NULL NULL    3872    where
used
et    eq_ref PRIMARY         PRIMARY 15      tt.ActualPC    1
et_1  eq_ref PRIMARY         PRIMARY 15      tt.AssignedPC  1
do    eq_ref PRIMARY         PRIMARY 15      tt.ClientID    1

Der JOIN Optimizer von MySQL (ja, MySQL hat auch einen QUERY OPTIMIZER) in der Ausgabe von EXPLAIN hat eine Annahme getroffen (die auch zutrifft), um die Datenbank schneller abzuarbeiten.

Eventuell kann man noch mit dem Operator STRAIGHT_JOIN erreichen, indem man gleichzeitig die Tabellen noch in einer anderen Reichenfolge ordnet...Das hängt dann aber von den Inhalten der Datenbank ab, das ist eher ein statistisches, und kein systematisches Problem. Kommerzielle Datenbanken führen Statistiken über die Inhalte mit, und können somit eventuell die Abfragen noch weiter beschleunigen. Wer also extensiv DATA MINING in riesigen Datenbeständen durchführt, der sollte tunlichst eine gute SQL-Datenbank einsetzen. DATA MINING ist z.B. die Analyse der Unfälle bei einer Versicherung, die versucht, anhand des Autotyps, Fahrleistungen, und sonstigen Angaben das Unfallrisiko und somit die Beitragssätze zu bestimmen. DATA MINING ist sowas wie schwarze Magie, es gibt kaum Experten, die sich damit auskennen, und eventuell noch in der Lage sind, diese Analysen so durchzuführen, daß auch aussagefähige Ergebnisse herauskommen.

Wer also seine Datenbankstruktur testen möchte, der kommt um das Erzeugen von Test-Datensätzen nicht herum.

Weiter Zurück [Inhalt] Online Suche im Handbuch