MariaDB-Kurs / Einfache und zusammengesetzte Indizes mit passender Spaltenreihenfolge erstellen
Ein Index speichert Schlüsselwerte in einer zusätzlichen Datenstruktur, mit der MariaDB passende Zeilen suchen und eine Sortierung unterstützen kann. Bei einem zusammengesetzten Index bestimmt die Spaltenreihenfolge mit, welche Abfragen davon profitieren. Prüfen Sie Filter, Sortierung und vorhandene Schlüssel und wägen Sie Lesezugriffe gegen Schreibaufwand und Speicherbedarf ab.
Indizes als gepflegte Datenstrukturen betrachten
Geordnete Schlüssel können einen Suchbereich eingrenzen, statt alle Tabellenzeilen zu untersuchen. Beim Einfügen, Ändern und Löschen muss der Server die betroffenen Indizes jedoch ebenfalls pflegen. Entscheidend sind deshalb häufige Abfragen, der Anteil passender Zeilen sowie Anforderungen an Verknüpfung und Sortierung. Eine größere Anzahl von Indizes ist allein kein Qualitätsmerkmal.
| Abfrage oder Datenregel | Mögliche Schlüsselreihenfolge | Prüfpunkt |
|---|---|---|
| Aktuelle Bestellungen eines Kunden | customer_id, ordered_at, order_id | Gleichheit, Zeitraum und eindeutige Reihenfolge |
| Bestellungen aller Kunden nach Datum | Ein mit dem Datum beginnender Index kann erforderlich sein | Andere Anforderung als eine kundenspezifische Suche |
| Eindeutiger Produktcode | UNIQUE(sku) | Zusätzlich eine Integritätsregel |
| Vorhandener Fremdschlüssel | SHOW INDEX untersuchen | Unbeabsichtigte doppelte Unterstützung vermeiden |
Die Übung zu ALTER TABLE verwendet UNIQUE für Produktcodes. Ein Schlüssel kann gleichzeitig Abfragen und Datenregeln unterstützen. Prüfen Sie beide Aufgaben, bevor Sie ihn wegen einer einzelnen SELECT-Abfrage entfernen.
Vorhandene Schlüssel und Übungsdaten untersuchen
Verbinden Sie sich als course_user mit dem Übungsserver und wählen Sie shopcourse. Die Anleitung zum MariaDB-Client und zu UTF-8-SQL-Dateien erläutert das Speichern der Blöcke und den Aufruf SOURCE C:/mariadb-lab/lesson.sql; mit Ihrem tatsächlichen Dateipfad. SQL-Anweisungen gehören in den MariaDB-Client.
Kontrollieren Sie SELECT VERSION(), DATABASE(); und SHOW TABLES;, bevor Sie c39_customers, c39_products, c39_orders und c39_items anlegen. Diese Namen sind ausschließlich für die Übung vorgesehen. Existieren die Tabellen bereits, prüfen Sie Definitionen und Datensätze; behalten Sie benötigte Daten und Anwendungstabellen bei.
USE shopcourse;
CREATE TABLE c39_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 c39_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 c39_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_c39_order_customer FOREIGN KEY (customer_id)
REFERENCES c39_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_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_c39_item_order FOREIGN KEY (order_id)
REFERENCES c39_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c39_item_product FOREIGN KEY (product_id)
REFERENCES c39_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c39_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c39_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c39_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 c39_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 SQL-Beispiele behalten koreanische Namen und Ortsangaben 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. Lassen Sie die UTF-8-Zeichenfolgen unverändert, wenn Sie die Ergebnisse nachvollziehen.
SHOW INDEX FROM c39_orders;
Lesen Sie Key_name, Column_name und Seq_in_index gemeinsam: Die letzte Spalte gibt die Position innerhalb des jeweiligen Schlüssels an. Non_unique unterscheidet eindeutige und nicht eindeutige Indizes; Sub_part zeigt gegebenenfalls eine Präfixlänge. Verschiedene Namen allein belegen keinen unterschiedlichen Aufbau. Die offizielle SHOW-INDEX-Referenz erklärt die Ausgabefelder.
Primär- und Fremdschlüssel können bereits unterstützende Indizes mitbringen. Untersuchen Sie mit SHOW CREATE TABLE auch die zugehörigen Bedingungen. Beurteilen Sie einen automatisch angelegten Schlüssel anhand seiner tatsächlichen Spalten und Abhängigkeiten.
Einen Index für Kunde, Zeitpunkt und Bestellnummer erstellen
CREATE INDEX idx_c39_customer_date ON c39_orders(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at,status FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SELECT order_id FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SHOW INDEX FROM c39_orders;
Die SELECT-Abfrage liefert die Bestellungen 101 und 102. customer_id schränkt auf einen Kunden ein. ordered_at beschreibt den Januar mit einer ausgeschlossenen oberen Grenze am 1. Februar. order_id legt die Reihenfolge fest, wenn mehrere Bestellungen denselben Zeitpunkt haben.
Vergleichen Sie SHOW INDEX vor und nach dem Anlegen: Beim neuen Index müssen die drei Spalten an den Positionen 1, 2 und 3 stehen. Seine Definition ist nicht eindeutig; gleiche Kunden- und Zeitwerte sind erlaubt. Eine eindeutige Bestellnummer wird bereits durch den Primärschlüssel abgesichert.
Lesen Sie den tatsächlich zurückgegebenen EXPLAIN-Plan. possible_keys nennt Kandidaten, key den ausgewählten Index. Das Anlegen erzwingt keine Auswahl. Bei nur vier Bestellungen kann das Lesen der ganzen Tabelle sinnvoll sein; die Übung belegt keinen Geschwindigkeitsgewinn. Die CREATE-INDEX-Referenz beschreibt Syntax, Berechtigungen und mögliche Wartezeiten auf Metadatensperren.
Führende Spalten, Bereiche und Abdeckung unterscheiden
Ein zusammengesetzter Index ordnet Schlüssel nach seiner Spaltenfolge. Ein Index, der mit dem Kunden beginnt, bietet einer reinen Datumssuche deshalb nicht automatisch denselben effizienten Bereichszugriff. Daraus folgt allerdings auch nicht, dass ohne Gleichheitsbedingung auf der ersten Spalte grundsätzlich keinerlei Indexlesung möglich wäre.
Bereichsbedingungen, Sortierrichtung, Zeichenpräfixe und ausgewählte Spalten beeinflussen den möglichen Zugriffsweg. Ein abdeckender Index enthält die für eine Abfrage benötigten Informationen und kann dadurch zusätzliche Zugriffe auf Tabellenzeilen vermeiden. Alle Spalten vorsorglich aufzunehmen vergrößert jedoch Schlüssel und Pflegeaufwand. Im Beispiel wird auch status ausgewählt; aus der bloßen Existenz des neuen Index folgt keine vollständige Abdeckung dieser Abfrage.
Trennen Sie Indexkandidat, tatsächliche Auswahl und Abdeckung, wenn Sie key und Extra betrachten. Der offizielle Leitfaden zum Indexentwurf für SELECT-Abfragen erläutert Auswahlkriterien und Ausnahmen, die Sie auf Ihre Version und Abfragen beziehen müssen.
Überlappungen und Abhängigkeiten vor dem Entfernen prüfen
Indizes mit unterschiedlichen Namen können dieselben oder teilweise überlappende Spaltenfolgen besitzen. Vergleichen Sie zusätzlich Eindeutigkeit und Präfixlängen. Untersuchen Sie SHOW INDEX nach der Erstellung erneut, um zu sehen, wie der Server die Fremdschlüsselunterstützung abbildet.
Das Entfernen des einzigen unterstützenden Fremdschlüsselindex kann abgewiesen werden. Ermitteln Sie abhängige Bedingungen und weitere Abfragen und sorgen Sie gegebenenfalls vorher für einen geeigneten Ersatz. Die Übung zu Tabellenbeziehungen und Fremdschlüsseln erklärt diese Abhängigkeiten. PRIMARY KEY und UNIQUE sichern außerdem Datenregeln; eine einzelne Abfrage ohne Verwendung dieses Index macht sie nicht entbehrlich.
Ergebnisse erhalten und den Zugriffsaufwand vergleichen
Die Indexergänzung muss die angeforderten Zeilen und die Bedeutung der Sortierung erhalten. Vergleichen Sie die Ergebnisfolge vor und nach der Änderung. Prüfen Sie für eine Leistungsbewertung außerdem repräsentative Ausführungspläne, tatsächliche Lesezugriffe, Laufzeiten und Schreibkosten mit einer realistischen Datenverteilung. Ein wärmerer Cache bei der zweiten Ausführung erklärt unter Umständen einen Teil des Unterschieds.
Prüfen Sie vor einer Wiederholung die ausgewählte Datenbank, Definitionen und gespeicherten Datensätze und sichern Sie benötigte Inhalte. Entfernen Sie die folgenden vier Tabellen nur als entbehrliche Übungsobjekte, zuerst die abhängigen Tabellen. DROP TABLE löscht Definitionen und Daten und lässt sich nicht durch ein gewöhnliches ROLLBACK rückgängig machen. Führen Sie diese Bereinigung getrennt von Transaktionsversuchen aus.
DROP TABLE shopcourse.c39_items;
DROP TABLE shopcourse.c39_orders;
DROP TABLE shopcourse.c39_products;
DROP TABLE shopcourse.c39_customers;




