Langsame Datenbankabfragen in PHP untersuchen, ohne blind zu indexieren

Ein langsamer Bildschirm beweist weder, dass die Datenbank das Problem ist, noch dass ein Index die Lösung darstellt. Dieselbe Wahrnehmung kann durch PHP-Code, ausgelastete Verbindungen, einen externen HTTP-Aufruf, die Serialisierung von Antworten, Sperren oder eine Abfrage entstehen, die zu viele Daten zurückgibt. Zu wissen, wie langsame Abfragen in PHP untersucht werden, bedeutet, Belege zu sammeln, bevor das Schema geändert oder Optimierungen hinzugefügt werden, die Schreibvorgänge verteuern könnten.

Latenz der Anwendung von der Datenlatenz trennen

Beginnen Sie damit, die Gesamtdauer einer Anfrage aufzuschlüsseln. Erfassen Sie eine identifizierbare Route oder einen Befehl, Start- und Endzeit, die ausgeführten Abfragen, deren Dauer sowie externe Abhängigkeiten. Es reicht nicht, die durchschnittliche Zeit zu messen: Eine API kann gesund erscheinen und dennoch bei bestimmten Filtern, Clients oder tiefen Seiten versagen.

Bei jedem Vorfall ist es sinnvoll, zu unterscheiden zwischen:

• Zeit in PHP: Transformation von Collections, Schleifen, JSON-Serialisierung, Dokumenterzeugung oder übermäßiger Speicherverbrauch.
• Datenbankzeit: Dauer jeder Abfrage, Wartezeit auf Sperren, Öffnen von Verbindungen und Anzahl übertragener Zeilen.
• Netzwerkzeit und Abhängigkeiten: Remote-Caches, APIs von Drittanbietern, Dateispeicher, Queues oder Identitätsdienste.
• Wartezeit in der Queue: Anfragen, die auf PHP-Worker, verfügbare Verbindungen oder Datenbankressourcen warten.

Verwenden Sie Traces oder strukturierte Logs mit einer Anfrage-ID. Eine isolierte Abfrage von 20 ms kann zu einem Problem von Sekunden werden, wenn sie in derselben Antwort Hunderte Male ausgeführt wird. Umgekehrt muss eine Abfrage von 500 ms nicht die Hauptursache sein, wenn der Prozess mehrere Sekunden auf einen externen Dienst wartet.

Belege sammeln, bevor der Code geändert wird

Erfassen Sie die parametrisierte Abfrage und separat repräsentative Parameter. Vermeiden Sie es, Geheimnisse, vollständige personenbezogene Daten oder Werte zu protokollieren, die für die Reproduktion des Falls nicht erforderlich sind. Eine Suche nach einem häufigen Status verhält sich nicht wie eine Suche nach einer eindeutigen Kennung; nur den bequemen Fall zu bewerten, führt zu falschen Entscheidungen.

Die Mindestbelege müssen Folgendes enthalten:

• Betroffene Route, asynchroner Job oder Befehl sowie deren Häufigkeit.
• Beobachtete Dauer, hohe Perzentile und Zeitpunkt des Auftretens.
• SQL, typisierte Parameter und Anzahl der Ausführungen pro Anfrage.
• Zurückgegebene Zeilen und, wenn möglich, gelesene oder untersuchte Zeilen.
• Ungefähre Größe der Tabellen und Verteilung der gefilterten Werte.
• Gleichzeitigkeit, parallele Schreibvorgänge und relevante Sperren.

Die Slow Logs der Datenbank-Engine helfen, Kandidaten zu entdecken, ersetzen aber keinen Anwendungstrace: Sie geben normalerweise nicht an, welcher Endpoint eine Abfrage erstellt hat oder wie oft sie wiederholt wurde. Kombinieren Sie diese Informationen in MySQL und PostgreSQL mit Metriken zu Verbindungen, CPU, I/O und Wartezeiten, um einen mangelhaften Plan nicht mit einer vorübergehend überlasteten Infrastruktur zu verwechseln.

Muster erkennen, die die Kosten vervielfachen

Bevor Sie eine komplexe Anweisung analysieren, suchen Sie nach häufigen Mustern. N+1 tritt auf, wenn eine Liste ihre Hauptzeilen abruft und danach für jede Beziehung eine zusätzliche Abfrage ausführt. Auch wenn jede einzelne Abfrage schnell ist, steigen die Anzahl der Datenbank-Roundtrips, der Planungsaufwand und die Konkurrenz mit der Seitengröße.

Verdächtig sind außerdem Filter mit geringer Selektivität, etwa sehr häufige Status; auf gefilterte Spalten angewandte Funktionen; implizite Typkonvertierungen; Suchen mit führendem Platzhalter; Sortierungen großer Mengen und Paginierung mit hohem OFFSET. Das Anfordern von SELECT * kann Transfer, Speicher und Leseaufwand erhöhen, selbst wenn der Plan bereits einen Index verwendet.

Die Korrektur besteht nicht immer in einer einzigen Abfrage. Beziehungen kontrolliert zu laden kann ein N+1 lösen, doch ein unbegrenztes Eager Loading kann eine riesige Abfrage oder Antwort erzeugen. Definieren Sie, welche Beziehungen die Route tatsächlich benötigt, begrenzen Sie ihre Spalten und messen Sie die Auswirkung mit der erwarteten Seitengröße.

Den Ausführungsplan mit echten Daten interpretieren

Führen Sie EXPLAIN aus, um den vorgeschlagenen Plan kennenzulernen, und verwenden Sie die Variante mit tatsächlicher Ausführung, wenn sie für die Umgebung sicher und geeignet ist. In PostgreSQL führt EXPLAIN ANALYZE die Abfrage aus; eine Änderungsanweisung sollte nicht auf diese Weise analysiert werden, ohne ihre Auswirkung zu verstehen. In MySQL hängen die verfügbaren Modi von Version und Konfiguration ab, doch das Ziel ist dasselbe: Schätzungen mit der tatsächlichen Arbeit zu vergleichen.

Ein sequenzieller Scan ist nicht automatisch schlecht. Wenn die Abfrage einen großen Teil einer kleinen oder wenig selektiven Tabelle benötigt, kann deren Durchlaufen weniger kosten als zwischen Index und Tabelle zu springen. Untersuchen Sie hingegen, wenn der Plan deutlich mehr tatsächliche als geschätzte Zeilen, kostspielige Sortierungen, temporäre Lesevorgänge, Joins über große Mengen oder sehr oft ausgeführte innere Schleifen zeigt.

Nützliche Fragen bei der Überprüfung eines Plans

• Wie viele Zeilen erwartete der Optimizer und wie viele verarbeitete er tatsächlich?
• Welcher Knoten bündelt die meiste Zeit, die meisten Lesevorgänge oder Iterationen?
• Wird der Filter früh angewendet oder erst nach dem Kombinieren großer Mengen?
• Erfolgt die Sortierung über mehr Zeilen, als die Antwort benötigt?
• Spiegeln die Statistiken die aktuelle Verteilung der Daten wider?

Abweichungen zwischen Schätzung und Realität können eine Aktualisierung der Statistiken oder eine Überprüfung von Typen und Bedingungen erfordern, nicht die sofortige Erstellung eines Index. Der Plan ist eine Erklärung einer Ausführung unter bestimmten Parametern und Lastbedingungen; keine automatische Änderungsanweisung.

Zwischen Umschreiben, Paginierung, Datenzugriff und Index wählen

Reduzieren Sie zuerst die unvermeidbare Arbeit. Wählen Sie nur notwendige Spalten aus, wenden Sie angemessene Limits an, entfernen Sie nicht verwendete Beziehungen und vermeiden Sie, vollständige Verläufe nach PHP zu übertragen, um sie dort anschließend zu filtern. Wenn der Anwendungsfall die Erkundung eines wachsenden Verlaufs verlangt, ersetzen Sie tiefe Paginierung durch Cursor- oder Schlüssel-Paginierung: Setzen Sie beispielsweise mit einer stabilen Kombination aus Datum und Kennung fort, statt Tausende von Zeilen mit OFFSET zu verwerfen.

Überprüfen Sie Joins und Filter darauf, dass sie kompatible Spalten vergleichen und die Bedingung klar ausdrücken. Manchmal ist es sinnvoll, eine Beziehung blockweise abzufragen; in anderen Fällen ist eine einzige, klar begrenzte Abfrage vorzuziehen. Die Entscheidung hängt von Kardinalität, Antwortvolumen und Häufigkeit ab, nicht von einer universellen Regel.

Ein zusammengesetzter Index hilft, wenn er zum Zugriffsmuster passt. Seine Reihenfolge ist wichtig: In der Regel sollten Gleichheits- und selektive Spalten das Filtern vor den für Bereich oder Sortierung verwendeten Spalten erleichtern, doch die konkrete Abfrage und die Engine bestimmen das Ergebnis. Ein Index kann zudem helfen, eine Sortierung zu vermeiden, wenn er eine kompatible Reihenfolge abdeckt, auch wenn dies nicht jede Kombination aus WHERE und ORDER BY erlaubt.

Vermeiden Sie es, Spalten nur zu indexieren, weil sie in einer Bedingung vorkommen. Indizes benötigen Speicherplatz, verbrauchen Arbeitsspeicher und erhöhen den Aufwand für INSERT, UPDATE und DELETE. Redundante oder wenig nützliche Indizes können ein System mit vielen Schreibvorgängen verschlechtern. Prüfen Sie auch, ob der Index die benötigten Spalten ohne zusätzliche Lesevorgänge bereitstellen kann, fügen Sie aber keine abdeckenden Spalten hinzu, ohne die Kosten zu messen.

Hypothetisches Beispiel: ein Operationsverlauf

Nehmen Sie eine Liste an, die Operationen nach Konto, Status und Datum anzeigt. Mit wachsendem Verlauf verschlechtert sich Seite 200. Die erste Hypothese könnte sein, einen Index auf dem Datum zu erstellen. Der Trace zeigt jedoch eine Hauptabfrage, gefolgt von einer Abfrage je Operation, um den verantwortlichen Benutzer abzurufen: Es liegt ein N+1 vor. Der Plan der Hauptabfrage liest zudem viele Zeilen, um die vorherigen mittels OFFSET zu verwerfen.

Die sinnvolle Reihenfolge wäre, die Verantwortlichen gruppiert oder über einen auf die erforderlichen Spalten begrenzten Join zu laden, die tiefe Paginierung durch einen auf created_at und id basierenden Cursor zu ersetzen und erneut zu messen. Erst dann wird ein Index bewertet, der mit dem Kontofilter, der stabilen Reihenfolge und dem Cursor übereinstimmt. Die Änderung des Zugriffs kann mehr Arbeit reduzieren als ein isolierter Index, und der endgültige Index muss auch gegenüber der Erstellung neuer Operationen geprüft werden.

Unter Last validieren und Regressionen bei Schreibvorgängen überwachen

Vergleichen Sie vorher und nachher mit repräsentativen Parametern, Datenverteilungen und Gleichzeitigkeit. Messen Sie Dauer, verarbeitete Zeilen, Lesevorgänge, CPU-Nutzung, Speicher in PHP, Größe der Antwort und Anzahl der Abfragen pro Anfrage. Messen Sie bei einem neuen Index auch die Latenz und Kapazität der betroffenen Schreibvorgänge.

Definieren Sie explizite Akzeptanzgrenzen: beispielsweise eine überprüfbare Senkung des hohen Perzentils der Route, ohne die Erstellungs- oder Aktualisierungszeit inakzeptabel zu erhöhen. Testen Sie leere Fälle, sehr häufige Werte, seltene Filter, erste und letzte Seiten sowie Berechtigungen, die den Umfang der Daten verändern.

Änderungen beobachtbar und reversibel bereitstellen

Trennen Sie, wenn möglich, das Code-Deployment von der Indexerstellung. Die Erstellung von Indizes kann je nach Engine, Operation und Umgebung um Ressourcen konkurrieren oder Sperren erwerben. Planen Sie den Zeitpunkt, prüfen Sie die von Ihrer Datenbank unterstützte Methode und überwachen Sie Dauer, Fehler und I/O-Druck.

Führen Sie die neue Abfrage schrittweise ein, wenn die Architektur dies erlaubt, und halten Sie eine klare Rückabwicklung bereit: die vorherige Abfrage wiederherstellen, einen alternativen Zugriffspfad deaktivieren oder einen Index entfernen, der sich als schädlich erweist. Eine Rückabwicklung ersetzt weder Backups noch die Überprüfung von Migrationen, verringert jedoch die Expositionszeit bei unerwartetem Verhalten.

Checkliste für eine wiederholbare Verbesserung

1. Identifizieren Sie die Route, das Symptom und die Parameter, die es reproduzieren.
2. Trennen Sie Zeit für PHP, Datenbank, Netzwerk und externe Abhängigkeiten.
3. Quantifizieren Sie Wiederholungen, zurückgegebene Zeilen und verarbeitete Zeilen.
4. Suchen Sie nach N+1, tiefer Paginierung, wenig selektiven Filtern und Sortierungen.
5. Überprüfen Sie den Plan und vergleichen Sie seine Schätzungen mit der tatsächlichen Ausführung.
6. Testen Sie Datenreduktion, Umschreiben und eine Änderung der Paginierung, bevor Sie indexieren.
7. Entwerfen Sie den Index nach dem vollständigen Muster aus Filterung, Sortierung und Schreibvorgängen.
8. Validieren Sie Lese- und Schreibvorgänge mit repräsentativer Last, Beobachtbarkeit und Rückabwicklung.