Ich versuche herauszufinden, warum eine meiner Fragen langsam ist und wie ich es beheben kann, aber ich bin ein wenig verwirrt über meine Ergebnisse.
Ich habe eine orders -Tabelle mit ungefähr 80 Spalten und 775179 Zeilen und mache folgende Anfrage:
SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC LIMIT 200
gibt 38 Zeilen in 4.5s zurück
Beim Entfernen von ORDER BY bekomme ich eine schöne Verbesserung:
SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL LIMIT 200
38 Zeilen in 0.30s
Aber wenn ich die LIMIT entferne, ohne die ORDER BY zu berühren, bekomme ich ein noch besseres Ergebnis:
SELECT * FROM orders WHERE id_state = 2 AND id_mp IS NOT NULL ORDER BY creation_date DESC
38 Zeilen in 0.10s (??)
Warum ist mein LIMIT so hungrig?
WEITER WEITER
Ich habe ein paar Dinge versucht, bevor ich meine Antwort gesendet habe und nachdem ich bemerkt habe, dass ich einen Index für creation_date hatte (was ein datetime ist), entfernte ich ihn und die erste Abfrage läuft nun in 0.10s. Warum ist das so?
BEARBEITEN
Gut geraten, ich habe Indizes auf den anderen Spalten Teil der wo.
%Vor%1 Reihe im Satz (0.00 sec)
%Vor% Indizes verbessern nicht notwendigerweise die Leistung. Um besser zu verstehen, was passiert, wäre es hilfreich, wenn Sie die explain für die verschiedenen Abfragen eingefügt hätten.
Meine beste Schätzung wäre, dass Sie einen Index in id_state oder sogar id_state, id_mp haben, der verwendet werden kann, um die where -Klausel zu erfüllen. Wenn ja, würde die erste Abfrage ohne order by diesen Index verwenden. Es sollte ziemlich schnell sein. Dies erfordert auch ohne Index einen sequentiellen Scan der Seiten in der Tabelle orders , der immer noch ziemlich schnell sein kann.
Wenn Sie dann den Index für creation_date hinzufügen, entscheidet sich MySQL, diesen Index stattdessen für order by zu verwenden. Dies erfordert das Lesen jeder Zeile im Index, dann das Abrufen der entsprechenden Datenseite, um die where -Bedingungen zu prüfen und die Spalten zurückzugeben (falls es eine Übereinstimmung gibt). Dieses Lesen ist sehr ineffizient, da es nicht in der "Seiten" -Reihenfolge ist, sondern vielmehr durch den Index spezifiziert wird. Zufällige Lesevorgänge können ziemlich ineffizient sein.
Schlimmer noch, obwohl Sie eine limit haben, müssen Sie immer noch die gesamte Tabelle lesen, da die gesamte Ergebnismenge benötigt wird. Obwohl Sie eine Sortierung für 38 Datensätze gespeichert haben, haben Sie eine massiv ineffiziente Abfrage erstellt.
Diese Situation wird übrigens deutlich schlechter, wenn die Tabelle orders nicht in den verfügbaren Speicher passt. Dann haben Sie eine Bedingung namens "Thrashing", bei der jeder neue Datensatz dazu neigt, einen neuen I / O-Read zu generieren. Wenn also eine Seite 100 Datensätze enthält, muss die Seite möglicherweise 100 Mal gelesen werden.
Sie können alle diese Abfragen schneller ausführen, indem Sie einen Index für orders(id_state, id_mp, creation_date) erstellen. Die where -Klausel verwendet die ersten beiden Spalten und die order by die letzte.
Dasselbe Problem ist in meinem Projekt passiert, Ich habe einen Test gemacht und herausgefunden, dass LIMIT wegen Zeilenaufrufe langsam ist
Siehe: MySQL ORDER BY / LIMIT Leistung: Late-Row-Lookups
Also, die Lösung ist:
(A) Wenn Sie LIMIT verwenden, wählen Sie nicht alle Spalten aus, sondern nur die PK-Spalten
(B) Markieren Sie alle benötigten Spalten und verbinden Sie sie mit der Ergebnismenge von (A)
SQL sollte gerne:
%Vor%in meiner Datenbank,
Das alte SQL, das alle Spalten mit "LIMIT 21560, 20" auswählt, kostet ungefähr 4.484s.
Der neue Sql kostet nur 0,063s. Der neue ist etwa 71 mal schneller
Tags und Links sql mysql performance