Langsame MySQL-Abfrage

Question

Ich habe eine Abfrage in MySQL (die in einer gespeicherten Prozedur verwendet wird), die nach Namen und einem anderen Feld sucht. Wenn ich verschiedene Kombinationen dieser Suchparameter verwende, erhalte ich schnelle Ergebnisse (zwischen 1 und 2 Sekunden), aber mit einigen bestimmten Werten erhalte ich eine Abfrage, die 9 Sekunden braucht, um Ergebnisse auf der Produktionswebsite zu liefern. Die EXPLAIN-Anweisung liefert folgende Ergebnisse:

id, select_type, table, type, possible_keys, key,       key_len, ref,   rows, Extra
--------------------------------------------
1,  SIMPLE,      Names, ref,  IX_Name,       IX_Name,   17,      const, 3173, Using where

Name ist als varchar(40) deklariert und das andere Feld ist Unsigned smallint(6). Ich verwende einen Index für die ersten 15 Zeichen des Namens (IX_Name), der in der Abfrage verwendet wird. Ich kann sehen, dass die langsamen Abfragen eine ziemlich große Anzahl von Zeilen erhalten, die in der Spalte "rows" der EXPLAIN-Ausgabe zu überprüfen sind.

Ich bin mir nicht sicher, was ich tun kann, um die Leistung zu verbessern. Ist an der obigen EXPLAIN-Ausgabe irgendetwas auffällig falsch?

Danke! Tim

Answer 1

Wie haben Sie die Tabelle ausgefüllt? Indizes sind Baumstrukturen, und um effizient zu arbeiten, müssen sie ausbalanciert sein - was automatisch geschieht, wenn die Tabelle im Stapel geladen wird oder eine regelmäßige Wartung erfolgt. Ist beides nicht der Fall, wird der Index für die Teile des Baums, die übermäßig gewachsen sind, deutlich weniger effizient sein.

Am einfachsten ist es, den Index zu löschen und ihn neu zu erstellen. Wenn Sie danach das gleiche Verhalten haben, liegt es an etwas anderem, aber zumindest ist damit eine Möglichkeit ausgeschlossen.

Answer 2

Es scheint, dass Ihre Abfrage nur den Index für ein Feld verwendet. Sie haben erwähnt, dass Sie nach dem Namen und "einem anderen Feld" suchen. MySQL ist (außer in sehr aktuellen Versionen unter bestimmten Umständen) auf einen Index pro Tabellenvorkommen in einer Abfrage beschränkt. Das bedeutet, wenn Sie einen Index auf den Namen und einen Index auf das andere Feld haben, muss MySQL wahrscheinlich raten, welcher Index am hilfreichsten wäre, und den anderen ignorieren. Es scheint, dass eine bessere Abfragestruktur oder Indexdefinition hilfreich wäre. Wenn Ihr Name ziemlich eindeutig ist und Sie 3.000 Zeilen im Erklärungsplan erhalten, dann sind entweder die Metadaten in der DB nicht gut oder Sie haben eine Menge anderer Möglichkeiten für das andere Feld.

Können Sie die Abfrage und das Schema für die Tabelle veröffentlichen?

Sind Ihre Abfragen immer schnell oder immer langsam für dieselbe SQL? d.h. wenn Sie nach Fisher suchen, ist es manchmal schnell und manchmal langsam, oder sind sie konsistent. Wenn sie konstant sind, liegt es wahrscheinlich an der CPU- oder Festplattenaktivität. Wenn sie variabel sind, liegt es wahrscheinlich an anderen Abfragen auf der DB.

Je nachdem, was Sie auswählen, können Sie Ihr vollständiges Ergebnis auch in einen Index aufnehmen, was Ihre Abfrage beschleunigt, da sie nicht auf die Festplatte zugreifen muss, um die Datensätze zu überprüfen. Ich habe ziemlich erstaunliche Verbesserungen mit Abfragen mit Index erzielt.

Jakob

Answer 3

Okay, Sie haben einen Index für ein Präfix. Sie verwenden die ersten 15 Zeichen, aber nehmen wir an, Sie verwenden nur 1, und Ihre Tabelle hat diese Werte für Namen:

Al Barb Beth Betsy Bill Biff Bob Bonny Buck Bud Carl

Da sich mein Index nur auf das erste Zeichen bezieht, muss die Datenbank alle Zeilen lesen, die der Index zurückgibt, und jeden vollständigen Namen mit dem Prädikat vergleichen.

Wenn ich nun nach "Al" suche, gibt mein Index eine Zeile zurück. Ich vergleiche dann "Al" im Prädikat mit "Al" in der Zeile, und ich habe eine Übereinstimmung, also gebe ich diese Zeile zurück, und ich bin fertig.

Wenn ich jetzt nach "Alex" suche, gibt mein Index eine Zeile zurück. Wenn ich dann "Alex" im Prädikat mit "Al" in der Zeile vergleiche, habe ich keine Übereinstimmung und keine weiteren potenziellen Übereinstimmungen, und ich bin fertig.

Wenn ich aber nach "Bud" (oder irgendetwas, das mit "B" beginnt) suche, liefert mein Index neun Zeilen zurück. Ich muss also noch neun Zeilen lesen und mit dem Prädikat vergleichen, bevor ich fertig bin.

Tun Sie dies:

select substring( name, 1, 15), count(*)
from names
group by substring( name, 1, 15);

Ich denke, Sie werden feststellen, dass Ihre schnellen Suchvorgänge einzigartig sind, während die langsamen Suchvorgänge viele Namen mit einem gemeinsamen Präfix enthalten.

Answer 4

Ich halte 3173 Zeilen für eine ziemlich große Zahl, wenn man davon ausgeht, dass man sie einem Benutzer vorlegen will. Wenn die tatsächliche Anzahl der abgerufenen Datensätze wesentlich geringer ist, sollten Sie die Erstellung weiterer Indizes in Betracht ziehen. Es wäre nützlich zu wissen, was EXPLAIN für einen anderen Suchbegriff meldet.