Zwei Bestellabfragen optimieren

Einführung

Ein wachsendes Geschäft benötigt zwei nützliche Bestellansichten: die ältesten abholbereiten Bestellungen eines Geschäfts und den neuesten Bestellverlauf einer Kundin oder eines Kunden. Die erste Seite enthält sieben Bestellungen. Separate Bestell-IDs müssen separat bleiben, auch bei gleichen Zeitstempeln und Preisen; eine Werbebestellung über null Cent kann trotzdem abholbereit sein.

Du speicherst zwei wiederverwendbare MySQL-Abfragen und ein Indexskript, wendest nützliche Indizes an und hältst beide ersten Seiten innerhalb eines gemeinsamen Budgets von zwei Sekundärindizes und 64 nativen diagnostischen Zeilenanforderungen pro Seite. Der ursprüngliche synthetische Datenstand enthält mehr als 96.000 Bestellungen. Diese Einheit optimiert erste Seiten; die nächste unabhängige Einheit vermittelt vollständige Cursorpaginierung.

Du benötigst grundlegende Kenntnisse der Terminalnavigation, von MySQL-Verbindungen und SELECT/WHERE. Parameter, halboffene Zeitbereiche, Sortierung, Indizes und Beobachtungen tatsächlicher Pläne werden vor ihrer Verwendung erklärt. Der private MySQL-Server, ursprüngliche Daten und Terminal sind bereit; Python- oder Frontendentwicklung ist nicht erforderlich.

Arbeite in /home/labex/project/order-desk. Erstelle oder bearbeite queue.sql, history.sql und indexes.sql und verwalte höchstens zwei Sekundärindizes. Bewahre die ursprünglichen Bestellungen, Spalten, den Primärschlüssel, Identitäten, Statuswerte, exakte Anwendungszeitstempel, Centbeträge, Notizen und ausführbaren Werkzeuge. Lösche oder überschreibe keine Quellzeilen, ändere keine Diagnoseeinstellungen, füge keine Optimizer-Hinweise hinzu und verwende keinen externen Cache. Indexnamen und gleichwertiges SQL dürfen abweichen. Laufzeitbeschriftungen und Ausgaben bleiben Englisch.

Die vorbereiteten Diagnoseverbindungen deaktivieren Index Condition Pushdown, eine Engine-Optimierung, die Filterung innerhalb der Speicher-Engine verbergen kann. Dies erleichtert die Interpretation von Vergleichen nativer Zeilenanforderungen. Es gilt nur für diese Verbindungen; die Übung misst keine physischen Datenträger-E/A und verspricht keinen Beschleunigungsfaktor im Produktivbetrieb. Millisekunden und eine exakte Planzeichenfolge sind keine Abnahmekriterien.

Die Abfragelast lesen und die Abholwarteschlange optimieren

In diesem Schritt prüfst du gültige Bestellungen, bevor du einen nützlichen Index auswählst. Eine schnellere erste Seite muss weiterhin dieselben erforderlichen Bestellfakten enthalten.

Beginne in Terminal an der normalen Shell. cd wechselt in den Arbeitsbereich; ./ führt ein Programm im aktuellen Verzeichnis aus. db öffnet den vorbereiteten MySQL-Client zum Lesen von Bestellungen und Verwalten der Kursindizes. Ein Here-Dokument sendet die Zeilen zwischen der Markierung SQL in Anführungszeichen und ihrem abschließenden SQL an diesen Client. Die Endmarkierung steht allein. SQL-Anweisungen enden mit Semikolons, und diese Clientverbindung schließt sich am Ende des Blocks. Ist ein Pfad oder Befehlsname falsch geschrieben, prüfe mit ls die tatsächlichen Namen und wiederhole den korrigierten Befehl; lass die ursprünglichen Daten intakt.

Prüfe die abholbereiten Bestellungen von Geschäft 3 am 10. Mai. Der Vergleich >= schließt den Beginn ein; < schließt die nächste Mitternacht aus. ORDER BY placed_at,order_id sortiert nach Zeit und dann ID, aufsteigend. Die ID löst Zeitstempelgleichstände auf. total_cents speichert ganzzahlige Centbeträge, und placed_at ist ein DATETIME(6): ein Anwendungszeitstempel, der sechs Mikrosekundenstellen bewahrt. Diese bereitgestellten Zeitstempel erfordern keine Zeitzonenumrechnung.

cd /home/labex/project/order-desk
./db <<'SQL'
SELECT order_id,shop_id,customer_id,state,placed_at,total_cents
FROM orders
WHERE shop_id=3 AND state='ready'
  AND placed_at>='2025-05-10' AND placed_at<'2025-05-11'
ORDER BY placed_at,order_id;
SQL

Es gibt zehn geeignete Bestellungen: Neun unterschiedliche IDs teilen sich 09:15, und Bestellung 900021 liegt bei 23:59:59.999999. Bestellung 900004 kostet null und ist trotzdem abholbereit. Die meisten Bestellungen mit gleicher Uhrzeit kosten 1250 Cent, aber ein gleicher Preis ist keine Identität. Die exakte nächste Mitternacht gehört zum nächsten Tag. Bewahre diese Fakten beim Ändern der Zugriffspfade.

Ein Index ist eine zusätzliche geordnete Struktur, die MySQL beim Auffinden von Zeilen hilft. Er kostet Speicherplatz und Wartungsaufwand bei Schreibzugriffen; jeden möglichen Index hinzuzufügen, ist daher nicht das Ziel. Ein zusammengesetzter Index ordnet Einträge nach mehreren Spalten. Der Primärschlüssel identifiziert bereits order_id; dieser Arbeitsplatz erlaubt über beide Abfragelasten hinweg höchstens zwei zusätzliche Sekundärindizes.

Ein Plan beschreibt, wie MySQL ein Ergebnis erreicht. Gewöhnliches EXPLAIN enthält Schätzungen. EXPLAIN ANALYZE führt das SELECT tatsächlich aus und meldet Iteratorbeobachtungen wie actual ... rows=... loops=.... FORMAT=TREE zeigt Ausführungsschritte, sogenannte Iteratoren, als eingerückten Eltern-/Kindtext. LIMIT 7 fordert höchstens sieben geordnete Zeilen an. Dieser oberste Iterator kann sieben Zeilen zurückgeben, während ein unterer Tabellenscan-Iterator eine deutlich größere Menge liest. Seine Laufzeiten variieren und werden nicht bewertet. DATE(placed_at) unten entnimmt das Kalenderdatum, sodass diese konkrete Ausgangsabfrage weiterhin den richtigen einzelnen Tag auswählt.

./db <<'SQL'
EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id,shop_id,customer_id,state,placed_at,total_cents
FROM orders
WHERE shop_id=3 AND state='ready' AND DATE(placed_at)='2025-05-10'
ORDER BY placed_at,order_id
LIMIT 7;
SQL

Lies den unteren Zugriffsiterator, nicht nur die sieben Zeilen oben. Ohne geeigneten Index durchsucht er ungefähr 96.000 Bestellungen. Die Diagnoseverbindung lässt Index Condition Pushdown ausgeschaltet, damit die Filterung sichtbar bleibt; behalte diese vorbereitete Vergleichseinstellung bei. Eine kleine zurückgegebene Seite allein belegt noch keinen geringen Datenbankaufwand.

Speichere eine Abfrage, die unterschiedliche Geschäfts-/Statusanfragen und beliebige halboffene Zeitbereiche verarbeiten kann. Ein Name mit dem Präfix @ wird zu einer nativen MySQL-Sitzungsvariablen. Das Arbeitsplatzprogramm liefert @group_id, @state, @from_at und @until_at für jede Anfrage in der Verbindung dieser Abfrage. Eine in einer manuellen db-Verbindung gesetzte Variable bleibt nicht in einer anderen erhalten. Lass SET-Anweisungen außerhalb der gespeicherten Abfragedatei.

Die beiden Abfrageschnittstellen geben sechs Spalten in dieser Reihenfolge zurück: order_id, group_id, actor_id, state, event_at, amount_cents. AS gibt einer ausgewählten Spalte den Schnittstellennamen: Das Geschäft ist die Gruppe, und der Kunde ist der Akteur. Bewahre die tatsächlichen Werte, statt sie durch Zusammenfassungen zu ersetzen. LIMIT 7 ist die Vorgabe des Arbeitsplatzprogramms für eine Seite mit sieben Bestellungen; es zu verkleinern, würde Bestellungen verbergen, statt die Seite zu optimieren. ASC macht die aufsteigende Reihenfolge ausdrücklich.

cat > queue.sql schreibt den folgenden vollständigen Text in diese Datei und ersetzt vorherigen Inhalt. Die Here-Dokument-Markierung in Anführungszeichen verhindert Shell-Expansion. Füge den gesamten Block mit alleinstehender Endmarkierung ein:

cat > queue.sql <<'SQL'
SELECT order_id,shop_id AS group_id,customer_id AS actor_id,
       state,placed_at AS event_at,total_cents AS amount_cents
FROM orders
WHERE shop_id=@group_id AND state=@state
  AND placed_at>=@from_at AND placed_at<@until_at
ORDER BY placed_at ASC,order_id ASC
LIMIT 7;
SQL
./desk page queue

page führt dein gespeichertes SQL unverändert aus und zeigt dessen tatsächliche Zeilen. Die erste Seite enthält die IDs 900001 bis 900007, einschließlich der Null-Cent-Bestellung. Der native Wert handler_requests zählt Anforderungen an der Grenze zwischen Server und Speicher-Engine in dieser Diagnoseverbindung; er ist keine Anzahl physischer E/A. Vor der Indexierung bleibt er groß. Der angezeigte Cursor für die nächste Anfrage stammt ausschließlich aus der tatsächlich zuletzt zurückgegebenen Zeile; diese Erste-Seite-Abfrage verwendet ihn noch nicht. Als null angezeigte Cursoreingaben bedeuten, dass eine Anfrage für die erste Seite keine vorherige Zeile hat.

Ein nützliches Zugriffspräfix legt zuerst Geschäft und Status fest und folgt dann dem angeforderten Zeitbereich und der (time,ID)-Reihenfolge. Die beiden Gleichheitsspalten können für diese Abfragen ihre Positionen tauschen; ihre textuelle Reihenfolge in WHERE bestimmt nicht die Indexreihenfolge. Ein reiner Zeitindex muss bei anderen Anfragen möglicherweise weiterhin unbeteiligte Geschäfte oder Statuswerte durchsuchen. Speichere den ausgewählten Kandidaten in indexes.sql, damit er auch auf eine neue Kopie angewendet werden kann.

cat > indexes.sql <<'SQL'
CREATE INDEX queue_time ON orders(shop_id,state,placed_at,order_id);
SQL
./db < indexes.sql

< liefert eine Datei als SQL-Eingabe an den Client. Dies erstellt eine Zugriffsstruktur, keine neuen Bestellungen. Führst du dasselbe CREATE INDEX später erneut aus, meldet MySQL einen doppelten Schlüsselnamen, weil dieser Index bereits vorhanden ist. Prüfe vorhandene Indizes, bevor du entscheidest, ob ein im Kurs erstellter Kandidat beibehalten oder ersetzt werden soll; lösche keine Quellbestellungen und erwarte von diesem Skript keine idempotente Migration.

Hier führt -e die vollständige SQL-Zeichenfolge in Anführungszeichen aus und beendet sich. Die Aktion plan des Arbeitsplatzprogramms führt EXPLAIN ANALYZE für die tatsächlich gespeicherte Abfrage aus. --from und --until wählen einen anderen Beginn und ein exklusives Ende für diese Anfrage; reine Datumswerte bedeuten Mitternacht.

./db -e "SHOW INDEX FROM orders;"
./desk page queue
./desk plan queue
./desk page queue --from 2025-01-05 --until 2025-01-12

Der Zugriffspfad sollte nun einen engen indexierten Bereich verwenden, während die ursprüngliche Seite des 10. Mai weiterhin dieselben sieben Bestell-IDs und exakten Felder enthält. Prüfe beide Anfragen gegen das Budget von 64 Anforderungen. SHOW INDEX belegt die Existenz; der tatsächliche Plan, zurückgegebene Zeilen und die Anforderungsanzahl belegen das nützliche Verhalten. Exakte Indexnamen, Kosten und Millisekunden dürfen abweichen.

Ist ein Befehl oder Dateiname falsch geschrieben, lies den Fehler, prüfe mit ls den Arbeitsbereich und versuche es mit dem korrigierten Namen erneut. Order desk error bedeutet einen Datei-, SQL- oder Verbindungsfehler; es ist kein erfolgreiches leeres Ergebnis. Korrigiere das gespeicherte SQL oder den Indexkandidaten und führe die tatsächliche Anfrage erneut aus. Führe die Schrittprüfung aus, nachdem du sowohl Korrektheit als auch beobachteten Aufwand bestätigt hast.

Das Indexbudget mit dem Kundenverlauf teilen

In diesem Schritt unterstützt du eine zweite geschäftliche Anfrage, ohne die Abholwarteschlange zu verlieren oder zwei Sekundärindizes zu überschreiten.

Bleibe in Terminal und /home/labex/project/order-desk. Bewahre die ursprünglichen Tabellen und queue.sql. Die Verlaufsansicht wählt eine Kundin oder einen Kunden unabhängig von Abholstatus oder Geschäft aus und zeigt die neuesten Bestellungen zuerst. DESC kehrt die Reihenfolge um; verwende es für Zeitstempel und ID, damit Bestellungen mit gleicher Zeit eine deterministische Reihenfolge mit den neuesten zuerst haben. Der native Parameter @actor_id identifiziert den Kunden. Ersetze ihn nicht durch einen festen Kunden und ergänze nicht die Statusbeschränkung der Warteschlange.

Schreibe die vollständige Verlaufsabfrage für die erste Seite. Sie verwendet dieselbe Schnittstelle mit sechs Spalten und denselben halboffenen Bereich wie die Abholabfrage:

cat > history.sql <<'SQL'
SELECT order_id,shop_id AS group_id,customer_id AS actor_id,
       state,placed_at AS event_at,total_cents AS amount_cents
FROM orders
WHERE customer_id=@actor_id
  AND placed_at>=@from_at AND placed_at<@until_at
ORDER BY placed_at DESC,order_id DESC
LIMIT 7;
SQL
./desk page history
./desk plan history

Der Verlauf des 10. Mai beginnt mit Bestellung 900021 in der letzten Mikrosekunde, gefolgt von den IDs mit gleicher Uhrzeit in absteigender Reihenfolge. Ihre tatsächlichen Centbeträge und Statuswerte bleiben sichtbar. Diese korrekte erste Seite kann weiterhin zu viel Aufwand erfordern: Der vorhandene Warteschlangenindex beginnt mit Geschäft/Status, die der Verlauf nicht einschränkt.

Ein zweites nützliches Präfix beginnt mit dem Kunden und danach Zeit/ID. Ein aufsteigender Index kann für die absteigende Verlaufsreihenfolge rückwärts gelesen werden; der beobachtete Plan zeigt, was tatsächlich geschieht. Zusätzliche abdeckende Spalten sind für diese Aufgabe nicht erforderlich. Behalte höchstens zwei Sekundärindizes und nützlichen Zugriff für beide Abfragelasten bei.

cat >> indexes.sql hängt die neue Anweisung an die vorhandene Quelldatei an; > würde sie ersetzen. Führe nach dem Anhängen nur die neue CREATE-Anweisung unten aus, weil der Warteschlangenindex in dieser VM bereits existiert. Auf einer neuen Datenbank ist die gespeicherte Datei mit zwei Anweisungen die vollständige Indexquelle.

cat >> indexes.sql <<'SQL'
CREATE INDEX actor_time ON orders(customer_id,placed_at,order_id);
SQL
./db -e "CREATE INDEX actor_time ON orders(customer_id,placed_at,order_id);"
./db -e "SHOW INDEX FROM orders;"
./desk page history
./desk plan history

Prüfe einen breiteren Kundenverlauf, eine andere Identität, einen leeren Datumsbereich und die ursprüngliche Warteschlange. --actor ändert die angeforderte Kunden-ID, nicht die Quelldaten. Eine gültige Abfrage kann keine Zeilen zurückgeben; das unterscheidet sich von einem Ausführungsfehler.

./desk page history --from 2025-01-01 --until 2025-05-01
./desk page history --actor 78
./desk page history --from 2027-01-01 --until 2027-01-02
./desk page queue

Beide Abfragelasten müssen korrekte erste Seiten innerhalb des Diagnosebudgets beibehalten. Die leere Anfrage gibt ehrlich eine leere Zeilenliste zurück, keine erfundenen Bestellungen. Die gespeicherte indexes.sql und die aktuellen Definitionen werden unabhängig geprüft: Eine gute Datei belegt nicht, dass sie angewendet wurde, und ein funktionierender aktueller Index ersetzt nicht die gespeicherte Quelle.

Änderst du einen Kandidaten, prüfe SHOW INDEX, entferne nur den im Kurs erstellten Index, den du ersetzt, aktualisiere indexes.sql und wende die neue Definition an. Entferne niemals den ursprünglichen Primärschlüssel, Spalten oder Zeilen. Der Fehler wegen eines doppelten Namens bei wiederholtem CREATE ist ein Grund, die aktuelle Definition zu prüfen, nicht die VM zurückzusetzen.

Das Endziel dieser Einheit sind zwei nützliche Abfragen der ersten Seite. Sieben zurückgegebene Zeilen sind keine vollständige längere Warteschlange. Die nächste unabhängige Einheit beginnt mit diesen bereits vorbereiteten Erste-Seite-Abfragen und Indizes und vermittelt dann Cursorbedingungen, die jede Bestellung über mehrere Seiten hinweg bewahren. Führe die Abschlussprüfung mit beiden gespeicherten Abfragen, dem vollständigen Indexskript und höchstens zwei wirksamen Sekundärindizes aus.

Zusammenfassung

Du hast tatsächliche Bestellfakten und Beobachtungen der tatsächlichen Ausführung geprüft, wiederverwendbare halboffene Abhol- und Kundenverlaufsabfragen gespeichert und beide ersten Seiten mit einer begrenzten Menge nützlicher Indizes unterstützt. Ursprüngliche Identitäten, Zeitstempel, Centbeträge und Notizen bleiben intakt. Du hast native diagnostische Zeilenanforderungen von zurückgegebenen Zeilenanzahlen, Schätzungen und physischen E/A unterschieden. Die vollständige Ausgabe über mehrere Seiten ist das Ziel der nächsten Einheit.