MariaDB-Kurs / Zeitfilter, Sortierung und Seitennavigation verbessern

Eine Abfrageverbesserung erhält die gewünschten Ergebniszeilen und stimmt Filter, Sortierung und Seitennavigation auf geeignete Zugriffswege ab. Formulieren Sie Zeitgrenzen und eindeutige Sortierregeln, vergleichen Sie OFFSET mit einer Navigation anhand der letzten Schlüsselwerte und prüfen Sie Leistungsänderungen mit tatsächlichen Plänen und repräsentativen Daten.

Vor dem Aufwand die Ergebnisgleichheit prüfen

Eine schnellere Abfrage, die Randzeitpunkte oder Zeilen mit gleichem Sortierwert verliert, erfüllt eine andere Anforderung. Vergleichen Sie zuerst die zurückgegebenen Primärschlüssel und deren Reihenfolge, anschließend Plan, Arbeitsaufwand und Laufzeit. Die Anleitung zu ORDER BY und LIMIT erklärt stabile Sortierung und Seitengrenzen; die Übung zum Vergleich von EXPLAIN-Plänen untersucht den Zugriff vor und nach einer Änderung.

Bereich Entscheidung Beispiel
Zeitfilter Beginn einschließen, nächste Grenze ausschließen Ein vollständiger Kalendertag
Sortierung Gleiche Zeitwerte eindeutig auflösen ordered_at, order_id
Seitennavigation Navigation und Konsistenz festlegen OFFSET oder letzte Sortierwerte
Textsuche Das benötigte Suchmuster bestimmen Präfix und enthaltene Zeichenfolge unterscheiden

Übungstabellen und Zeitindex vorbereiten

Verbinden Sie sich als course_user mit dem Übungsserver und wählen Sie shopcourse. Die Anleitung zum Client und zu UTF-8-SQL-Dateien erläutert das Speichern und den Aufruf SOURCE C:/mariadb-lab/lesson.sql; mit Ihrem Dateipfad. Führen Sie die SQL-Blöcke innerhalb des MariaDB-Clients aus.

Prüfen Sie SELECT VERSION(), DATABASE(); und SHOW TABLES;, bevor Sie c41_customers, c41_products, c41_orders und c41_items erstellen. Diese Namen gehören zur Übung. Untersuchen Sie bereits vorhandene Definitionen und Daten, bevor Sie über eine Entfernung entscheiden; erhalten Sie benötigte Anwendungsdaten.

USE shopcourse;
CREATE TABLE c41_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c41_order_customer FOREIGN KEY (customer_id)
  REFERENCES c41_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c41_item_order FOREIGN KEY (order_id)
  REFERENCES c41_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c41_item_product FOREIGN KEY (product_id)
  REFERENCES c41_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c41_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c41_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c41_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c41_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

Die Beispiele behalten koreanische Namen und Ortswerte bei. Die Produkte 1–4 sind Notizbuch, Kugelschreiber, Becher und Schreibtischlampe. Kunde 1 und Kunde 3 haben den Ortswert für Seoul, Kunde 2 für Busan und Kunde 4 für Daejeon. Bewahren Sie die UTF-8-Zeichenfolgen beim Nachvollziehen der Ergebnisse.

CREATE INDEX idx_c41_time ON c41_orders(ordered_at,order_id);

Die vier Bestellungen lassen sich nach Zeitpunkt und Kennung vergleichen. Für eine so kleine Tabelle kann der Optimierer einen vollständigen Tabellenzugriff wählen. Der Aufbau dient der Prüfung korrekter Zeilen und Grenzen; eine Bewertung größerer Leistungseffekte benötigt repräsentative Daten.

DATE und einen ausdrücklichen Zeitraum vergleichen

SELECT order_id FROM c41_orders WHERE DATE(ordered_at)='2026-01-10'
ORDER BY order_id;
SELECT order_id FROM c41_orders
WHERE ordered_at>='2026-01-10' AND ordered_at<'2026-01-11' ORDER BY order_id;

Beide Abfragen liefern Bestellung 101. Der Zeitraum enthält den Beginn des 10. Januar und schließt den Beginn des 11. Januar aus. Damit ist der ganze Tag erfasst, auch wenn eine Spalte Sekundenbruchteile speichern kann. Ein einschließliches Ende bei 23:59:59 würde spätere Bruchteile derselben Sekunde auslassen.

Seit MariaDB 11.1 können unterstützte DATE- und YEAR-Bedingungen auf indizierten Zeitspalten in Bedingungen für einen Indexzugriff umgewandelt werden. Eine Funktion auf der Spalte verhindert dessen Verwendung deshalb nicht grundsätzlich. Die offizielle Dokumentation zur DATE- und YEAR-Optimierung nennt die unterstützten Vergleichsformen und Datentypen.

Ein ausdrücklicher Zeitraum kann die Absicht über Versionen hinweg verständlich machen. Eine höhere Geschwindigkeit ist damit noch nicht nachgewiesen. Vergleichen Sie die Zugriffswege mit EXPLAIN. Wenn Sie Datumswerte zur Suche in Text umwandeln, prüfen Sie auch Datentypen und die Vermischung von Anzeigeformat und Filterbedingung.

Zeitpunkt und Kennung gemeinsam als Seitengrenze verwenden

SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
 OR(ordered_at='2026-02-05 12:00:00' AND order_id<103)
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;

Die erste absteigende Seite enthält 104 und 103. Ihr letzter Datensatz ist Bestellung 103 mit 2026-02-05 12:00:00. Die zweite Abfrage verwendet beide Werte als Cursor und liefert 102 und 101: Sie sucht frühere Zeitpunkte oder bei gleichem Zeitpunkt eine kleinere Kennung. Ändern Sie die Sortierrichtung, müssen Sie auch die Vergleichsrichtungen überprüfen.

Diese Navigation anhand von Schlüsselwerten heißt auch Keyset-Paginierung. Sie kann das Überspringen vieler Zeilen bei tiefen OFFSET-Werten vermeiden. Ein beliebiger Sprung zu einer nummerierten Seite ist damit allein jedoch nicht abgebildet. Gleichzeitige Änderungen können die Liste zwischen Anfragen weiterhin verändern. Legen Sie fest, ob die Anwendung einen festen Datenstand benötigt; ein Cursor friert die Gesamtliste nicht ein. Der offizielle Leitfaden zur Seitennavigation beschreibt die Navigation ab einer gespeicherten Position.

Testen Sie einen gleichen Zeitwert direkt an der Seitengrenze mit dieser rücknehmbaren Änderung. Führen Sie den ganzen Block in derselben Verbindung mit den angegebenen InnoDB-Tabellen aus:

START TRANSACTION;
UPDATE c41_orders SET ordered_at='2026-02-05 12:00:00' WHERE order_id=104;
SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
   OR (ordered_at='2026-02-05 12:00:00' AND order_id<104)
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
ROLLBACK;

Die erste Abfrage liefert 104, die folgende 103 mit demselben Zeitpunkt. Eine Grenze ausschließlich über einen früheren Zeitpunkt könnte 103 überspringen. Prüfen Sie nach ROLLBACK, dass Bestellung 104 wieder 2026-02-10 13:00:00 enthält. Die Kennung ergänzt den Zeitpunkt und macht die Reihenfolge bei Gleichstand eindeutig.

Spaltenauswahl und tatsächliches Suchmuster prüfen

SELECT * kann unnötig große Texte übertragen und Speicherverbrauch oder Tabellenzugriffe erhöhen. Weniger ausgewählte Spalten sind etwas anderes als weniger passende Datensätze. Prüfen Sie im Plan, ob ein abdeckender Index die benötigten Werte bereitstellen kann.

Eine Präfixsuche mit LIKE und eine Suche mit führendem Platzhalter haben bei gewöhnlichen B-Baum-Indizes unterschiedliche Zugriffsmöglichkeiten. Ändern Sie eine Suche nach enthaltenem Text in eine Präfixsuche, ändern sich möglicherweise die Ergebnisse. Beispielsweise passt alpha% zu alpha, aber nicht zu xalpha; %alpha% kann beide finden. Bestimmen Sie Suchmuster und Collation, also die Vergleichsregeln für Text, anhand der Anforderung. Die LIKE-Referenz beschreibt Platzhalter und Vergleichsverhalten.

Die Bedingungen des Vergleichs dokumentieren

Bewahren Sie beide Abfragen, Ergebniskennungen, Serverversion, Indexdefinitionen sowie Anzahl und Verteilung der Zeilen auf. Unterscheiden Sie Cacheeffekte von einer strukturellen Verbesserung. Untersuchen Sie einen unerwarteten Optimiererentscheid, bevor Sie einen bestimmten Index erzwingen. Beziehen Sie auch verwandte Sortier- und Navigationsanforderungen ein, die denselben Zugriffsweg verwenden.

Prüfen Sie vor einer Wiederholung Datenbank, Definitionen und gespeicherte Zeilen und sichern Sie benötigte Inhalte. Entfernen Sie die vier folgenden Tabellen nur als entbehrliche Übungsobjekte, zuerst die abhängigen Tabellen. DROP TABLE löscht Struktur und Daten und ist nicht durch ein gewöhnliches ROLLBACK rückgängig zu machen. Halten Sie die Bereinigung vom Transaktionstest mit gleichen Zeitwerten getrennt.

DROP TABLE shopcourse.c41_items;
DROP TABLE shopcourse.c41_orders;
DROP TABLE shopcourse.c41_products;
DROP TABLE shopcourse.c41_customers;