Abfrageergebnisse dauern zu lange bei 200K-Datenbank, Tipps zur Beschleunigung?

Question

Ich habe eine Sql-Anweisung, in der ich 4 Tabellen mit jeweils 200K Zeilen verknüpfe. Die Abfrage läuft, bleibt aber hängen. Wenn ich stattdessen eine Verknüpfung mit 3 Tabellen durchführe, werden die Zeilen zurückgegeben (dauert etwa 10 Sekunden). Irgendein Vorschlag, warum? Vorschläge zur Beschleunigung?

Gracias.

Code

SELECT *
FROM equipment, tiremap, workreference, tirework
WHERE equipment.tiremap = tiremap.`TireID` AND 
      tiremap.`WorkMap` = workreference.`aMap` AND
      workreference.`bMap` = tirework.workmap
LIMIT 5

p.s.

und wenn es hilft, ich bin mit Sql Alchemy, um diesen Code zu generieren, der Sqlalchemy-Code für diese ist

query = session.query(equipment, tiremap, workreference, tirework)
query = query.filter(equipment.c.tiremap == tiremap.c.TireID)
query = query.filter(tiremap.c.WorkMap==workreference.c.aMap)
query = query.filter(workreference.c.bMap == tirework.c.workmap)
query = query.limit(5)
query.all()

Answer 1

Stellen Sie sicher, dass die Indizes aktiviert sind:

• Ausrüstung (tiremap)
• tiremap (TireID)
• tiremap (WorkMap)
• workreference (aMap)
• workreference (bMap)
• tirework (Arbeitsplan)

Bearbeiten: Ich denke, ich sollte der Vollständigkeit halber etwas Kontext dazu liefern.

Der SQL-Optimierer betrachtet eine Anweisung, analysiert sie und bestimmt dann einen Ausführungsplan für sie auf der Grundlage der Abfrage, der referenzierten Tabellen und der verfügbaren Indizes. Wenn Sie SELECT * FROM tab1 dann wird ein vollständiger Tabellenscan von tab1 durchgeführt, da es keine andere Möglichkeit gibt, dies auszuführen.

Wenn Sie das tun SELECT * FROM person WHERE lastname LIKE 'V%' und Sie haben eine Million Datensätze, wird es langsam sein, jede Zeile abzufragen, aber wenn lastname indiziert ist, ist es viel effizienter.

Bei einer Abfrage wie der Ihren wird eine dieser Tabellen die treibende Tabelle sein, die unabhängig von Indizes einfach als vollständiger Tabellenscan ausgeführt werden kann. Daran ist nichts auszusetzen. Eine Tabelle muss die Abfrage steuern. Wenn es eine WHERE Klausel (für etwas anderes als Join-Bedingungen) kann sich dies ändern, aber ansonsten ist es allgemein wahr.

Von dieser treibenden Tabelle aus beginnt MySQL dann, Joins an den Ausführungsplan anzuhängen. Diese Joins erfordern Indizes auf der anderen Seite, damit sie effizient funktionieren.

Bei drei Tabellen kann es also sein, dass eine Tabelle nicht indiziert ist, aber das spielt keine Rolle, weil sie die Abfrage steuert. Bei der vierten Tabelle kann es zwei nicht indizierte Tabellen geben, und das ist jetzt ein Problem, weil MySQL für jede Zeile in einer Tabelle einen vollständigen Tabellenscan in der anderen Tabelle durchführen muss.

Grundsätzlich erstellen Sie also einen Index auf jede Fremdschlüssel und Join-Spalte, damit MySQL die verfügbaren Informationen nutzen kann, um den besten Ausführungsplan für die von Ihnen gestellte Anfrage zu erstellen.

Schließlich geben die meisten Tools Auskunft über das Datenbankschema. PHPMyAdmin ist ein beliebtes Tool für gehostete Datenbanken. Ich persönlich bevorzuge jedoch eine Desktop-Anwendung für diese Art von Aufgaben. Navicat Lite ist ein geeignetes kostenloses Tool für diese Aufgabe.

Answer 2

Sie führen eine natürliche Verknüpfung von 4 Tabellen durch. Außerdem gibt es in Ihrer "WHERE"-Anweisung keine besonderen Bedingungen.

Die Datenbank-Engine wird folgendes tun:

Zunächst wird ein rekursives Produkt aller Daten in jeder Tabelle erstellt.

Betrachten Sie die folgenden Zeilen in den Tabellen A, B und C:

A = rowA1
    rowA2
    rowA3;
B = rowB1
    rowB2
    rowB3;
C = rowC1
    rowC2
    rowC3;

Wenn Sie eine natürliche Verknüpfung dieser 3 Tabellen vornehmen, hat die Maschine im Grunde genommen einen Speicherplatz:

rowA1 - rowB1 - rowC1
rowA1 - rowB1 - rowC2
rowA1 - rowB1 - rowC3
rowA1 - rowB2 - rowC1
rowA1 - rowB2 - rowC2
rowA1 - rowB2 - rowC3
rowA1 - rowB3 - rowC1
rowA1 - rowB3 - rowC2
rowA1 - rowB3 - rowC3
...
...
...
rowA3 - rowB3 - rowC1
rowA3 - rowB3 - rowC2
rowA3 - rowB3 - rowC3

Insgesamt werden 27 Zeilen im Speicher abgelegt. Wir wollen aber nur 3 Zeilen:

rowA1 - rowB1 - rowC1
rowA2 - rowB2 - rowC2
rowA3 - rowB3 - rowC3

Wenn Ihre Datenbank-Engine die Optimierung nicht von selbst vornimmt, ist eine natürliche Verknüpfung von 3 Tabellen sehr teuer. Bei 4 Tabellen ist dies unvorstellbar, selbst bei einer begrenzten Anzahl von Zeilen.

Wie können wir nun etwas Besseres bekommen?

Wenn wir uns den Code ansehen, wissen wir, dass wir nur 5 Werte benötigen. Außerdem heißt es in der Datenbankoptimierung, dass man den SELECT so früh wie möglich durchführen sollte.

Hier ist ein ungetesteter Code, der Ihnen helfen soll. Möglicherweise müssen Sie ihn ändern, je nachdem, welche DB-Engine Sie verwenden:

SELECT *
FROM (SELECT * FROM equipment LIMIT 5) e, tiremap, workreference, tirework
WHERE e.tiremap = tiremap.TireID AND
      tiremap.WorkMap = workreference.`aMap` AND
      workreference.`bMap` = tirework.workmap

Dadurch sollte es so aussehen, als hätten wir nur 3 Tische und nicht 4. Das ist jedoch nicht wirklich das, was Sie wollen. Wenn eine Zeile der "Ausrüstung" nicht in den anderen Tabellen referenziert wird, erhalten Sie am Ende weniger als 5 Zeilen. Dies ist jedoch nur ein Beispiel, um Ihnen zu zeigen, dass wir nicht unbedingt alle Zeilen aus allen Tabellen benötigen.

Nun, ich denke, was Sie wollen, könnte Folgendes sein:

SELECT * FROM equipment 
INNER JOIN tiremap ON equipment.tiremap = tiremap.TireID
INNER JOIN workreference ON tiremap.WorkMap = workreference.aMap
INNER JOIN tirework ON workreference.bMap = tirework.workmap
LIMIT 5

Sie könnten hier ein Problem haben: wenn Ihre Engine nicht so gut ist (mySQL, sorry), kann es sehr lange dauern.

Wenn Sie die Optimierung wirklich selbst vornehmen wollen:

SELECT * FROM tirework, 
   (SELECT * FROM workreference, 
       (SELECT * FROM tiremap,
           (SELECT * FROM equipment) e
        WHERE e.tiremap = tiremap.TireID) t
    WHERE t.WorkMap = workreference.aMap) w
WHERE w.bMap = tirework.workmap
LIMIT 5

Und voilà! Selbst wenn Ihre Engine nicht optimiert ist, sollte diese Abfrage nicht allzu lange dauern. Anstatt ein großes Produkt aus allem zu machen, wird Ihre Maschine ein Produkt nach dem anderen machen und die schlechten Zeilen herausnehmen, bevor sie es mit einer neuen Tabelle verbindet.

Versuchen Sie es.

Answer 3

Es könnte sein, dass die 4. Tabelle, der Sie beitreten, viel größer ist als die anderen. Es könnte auch sein, dass die Spalte, die Sie verknüpfen wollen, keinen Index hat.

Answer 4

Die meisten SQL-Datenbanken verfügen über eine Variante von "EXPLAIN PLAN" oder "EXPLAIN", die Sie verwenden können, um zu sehen, wie die Abfrage geparst wird. Suchen Sie nach vollständigen Tabellenscans als Ort, an dem Sie Indizes benötigen.