Die Kunst der zusammengesetzten Indizes – Warum die Spaltenreihenfolge entscheidend ist
Takashi Yamamoto
Infrastructure Engineer · Leapcell

Die Kunst der zusammengesetzten Indizes: Warum die Spaltenreihenfolge entscheidend ist
Datenbanken sind das Rückgrat der meisten Anwendungen, und ihre Leistung beeinträchtigt die Benutzererfahrung erheblich. Wenn Abfragen langsamer werden, leidet das gesamte System. Unter den vielen verfügbaren Optimierungstechniken für Datenbankadministratoren und Entwickler sticht die Indizierung als leistungsstarkes Werkzeug hervor. Doch die wahre Kunst der Indizierung liegt nicht nur darin, sie zu erstellen, sondern ihre Nuancen zu verstehen, insbesondere wenn es um zusammengesetzte Indizes geht. Ein gut gestalteter zusammengesetzter Index kann die Datenabfrage dramatisch beschleunigen, während ein schlecht gestalteter Index vom Abfrageoptimierer möglicherweise komplett ignoriert oder sogar die Leistung verschlechtert wird. Dieser Artikel untersucht die entscheidende Rolle der Spaltenreihenfolge innerhalb zusammengesetzter Indizes und veranschaulicht, warum dieses scheinbar kleine Detail von immenser praktischer Bedeutung für die Datenbankeffizienz ist.
Im Kern ist ein Datenbankindex ähnlich dem Index am Ende eines Buches. Anstatt jede Seite sequenziell zu durchsuchen, um einen bestimmten Begriff zu finden, können Sie schnell zu den relevanten Seiten springen, die im Index aufgeführt sind. Dieses Prinzip gilt für Datenbanken und ermöglicht es ihnen, Datenzeilen zu finden, ohne jeden einzelnen Datensatz prüfen zu müssen. Wenn wir über Indizes sprechen, fallen zwei Schlüsselbegriffe auf:
-
Einzelspaltenindex: Ein Index, der auf einer einzelnen Spalte einer Tabelle erstellt wird. Er hilft dabei, Zeilen schnell anhand der Werte in dieser spezifischen Spalte zu finden. Zum Beispiel ermöglicht ein Index für
users.emailschnelle Abfragen nach E-Mail-Adresse. -
Zusammengesetzter (Mehrspalten-)Index: Ein Index, der auf zwei oder mehr Spalten einer Tabelle erstellt wird. Diese Art von Index kann besonders leistungsfähig für Abfragen sein, die mehrere Spalten in ihren
WHERE,ORDER BYoderGROUP BY-Klauseln verwenden. Der entscheidende Aspekt zusammengesetzter Indizes ist die Reihenfolge, in der die Spalten definiert sind.
Betrachten wir ein praktisches Szenario, um die Bedeutung der Spaltenreihenfolge zu verdeutlichen. Stellen Sie sich eine orders-Tabelle mit Millionen von Datensätzen vor, die Spalten wie customer_id, order_date und status enthält.
Angenommen, wir führen häufig Abfragen wie diese aus:
SELECT * FROM orders WHERE customer_id = 123;SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01' ORDER BY order_date DESC;SELECT * FROM orders WHERE order_date > '2023-01-01';SELECT * FROM orders WHERE status = 'shipped';
Nun untersuchen wir, wie ein zusammengesetzter Index ((customer_id, order_date)) im Vergleich zu ((order_date, customer_id)) abschneidet.
Verständnis der Regel für das linkeste Präfix
Das grundlegende Prinzip, das zusammengesetzte Indizes regelt, ist die „Regel für das linkeste Präfix“. Ein Index ((col_a, col_b, col_c)) kann effizient für die Suche auf folgenden Kombinationen verwendet werden:
col_acol_a, col_bcol_a, col_b, col_c
Er kann jedoch nicht effizient direkt auf col_b, col_c, col_b, col_b oder col_a, col_c (ohne col_b) suchen, da er nicht mit der äußersten linken Spalte des Index beginnt. Stellen Sie es sich wie ein Telefonbuch vor, das nach (Nachname, Vorname) sortiert ist. Sie können leicht alle Personen mit einem bestimmten Nachnamen finden oder alle Personen mit einem bestimmten Nachnamen UND Vornamen. Aber Sie können nicht einfach alle Personen mit einem bestimmten Vornamen finden, ohne zuerst ihren Nachnamen zu kennen.
Szenario 1: Index ((customer_id, order_date))
Lassen Sie uns diesen Index erstellen:
CREATE INDEX idx_customer_date ON orders (customer_id, order_date);
- Abfrage 1 (
WHERE customer_id = 123): Diese Abfrage kannidx_customer_datevollständig nutzen. Die Datenbank kann schnell auf die Datensätze fürcustomer_id = 123eingrenzen, indem sie dencustomer_id-Teil des Index traverse. Dies funktioniert, weilcustomer_iddie äußerste linke Spalte ist. - Abfrage 2 (
WHERE customer_id = 123 AND order_date > '2023-01-01'): Diese Abfrage kannidx_customer_dateebenfalls vollständig nutzen. Die Datenbank filtert zunächst nachcustomer_idund findet dann effizient Datensätze innerhalb dieser Teilmenge, bei denenorder_datedie Bedingung erfüllt. Dies bietet einen doppelten Vorteil, da beideWHERE-Klauseln vom Index abgedeckt sind. - Abfrage 3 (
WHERE customer_id = 123 AND order_date > '2023-01-01' ORDER BY order_date DESC): Hier hilft der Index nicht nur beim Filtern der Daten, sondern auch beim Sortieren. Da die Daten innerhalb voncustomer_idbereits nachorder_datesortiert sind, kann die Datenbank dieORDER BY-Klausel effizient ausführen, ohne eine zusätzliche Sortieroperation durchführen zu müssen (im MySQL-Jargon ein „Filesort“). Dies ist ein enormer Leistungsgewinn. - Abfrage 4 (
WHERE order_date > '2023-01-01'): Diese Abfrage kannidx_customer_datenicht effektiv nutzen. Daorder_datenicht die äußerste linke Spalte ist, greift die Datenbank wahrscheinlich auf einen vollständigen Tabellenscan zurück oder verwendet einen anderen Einzelspaltenindex, falls einer fürorder_datevorhanden ist. - Abfrage 5 (
WHERE status = 'shipped'): Diese Abfrage kannidx_customer_datedefinitiv nicht nutzen, dastatusnicht Teil des Index ist.
Szenario 2: Index ((order_date, customer_id))
Betrachten wir nun den umgekehrten Index:
CREATE INDEX idx_date_customer ON orders (order_date, customer_id);
- Abfrage 1 (
WHERE customer_id = 123): Diese Abfrage kannidx_date_customernicht effektiv nutzen, dacustomer_idnicht die äußerste linke Spalte ist. Die Datenbank führt wahrscheinlich einen vollständigen Tabellenscan durch oder verwendet einen Einzelspaltenindex fürcustomer_id, falls vorhanden. - Abfrage 2 (
WHERE customer_id = 123 AND order_date > '2023-01-01'): Diese Abfrage kannidx_date_customerteilweise nutzen. Sie kann effizient nachorder_date > '2023-01-01'filtern, müsste dann aber immer noch die ausgewählten Zeilen durchsuchen, um nachcustomer_idzu filtern. Obwohl besser als ein vollständiger Tabellenscan, ist sie nicht so effizient wieidx_customer_datefür dieses spezifische Abfragemuster. - Abfrage 3 (
WHERE customer_id = 123 AND order_date > '2023-01-01' ORDER BY order_date DESC): Ähnlich wie bei Abfrage 2 wären die Filterung nachorder_dateund dieORDER BY-Klausel effizient, aber fürcustomer_idwäre immer noch ein nachgelagerter Filtervorgang erforderlich. - Abfrage 4 (
WHERE order_date > '2023-01-01'): Diese Abfrage kannidx_date_customervollständig nutzen, daorder_datedie äußerste linke Spalte ist. Dies wäre sehr schnell. - Abfrage 5 (
WHERE status = 'shipped'): Dieser Index ist immer noch nutzlos.
Wichtige Erkenntnisse zur Spaltenreihenfolge
- Kardinalität: Legen Sie im Allgemeinen die Spalte mit der höchsten Kardinalität (die meisten eindeutigen Werte) zuerst ab, insbesondere wenn sie häufig in
WHERE-Klauseln für Gleichheitsbedingungen verwendet wird. Dies hilft dem Index, den Suchraum frühzeitig am effektivsten einzugrenzen. Dies ist jedoch eine Richtlinie, keine feste Regel. - Nutzungsmuster: Der wichtigste Faktor sind Ihre Abfragemuster.
- Wenn Sie häufig nur nach
col_aoder nachcol_aundcol_babfragen, dann ist((col_a, col_b))geeignet. - Wenn Sie häufig nur nach
col_babfragen, dann ist((col_b, col_a))(oder ein separater Index aufcol_b) besser. - Berücksichtigen Sie jegliche
ORDER BY- oderGROUP BY-Klauseln. WennORDER BY col_bhäufig nach dem Filtern nachcol_avorkommt, kann((col_a, col_b))sowohl den Filter als auch die Sortierung erfüllen und so einen teuren „Filesort“-Vorgang vermeiden.
- Wenn Sie häufig nur nach
- Gleichheit vs. Bereich: Spalten, die für Gleichheitsbedingungen (
=) verwendet werden, sollten im Allgemeinen vor Spalten kommen, die für Bereichsbedingungen (<,>,BETWEEN,LIKE 'präfix%') verwendet werden. Wenn SieWHERE col_a = 'X' AND col_b > 'Y'haben, dann funktioniert((col_a, col_b))sehr gut. Der Index kann zucol_a = 'X'springen und dann effizient entlangcol_bvon'Y'aus scannen. Wenn die Reihenfolge((col_b, col_a))wäre, würde der Index einen viel größeren Bereich voncol_b-Werten scannen, bevor nachcol_agefiltert wird. - Abdeckende Indizes: Ein zusammengesetzter Index kann zu einem „abdeckenden Index“ werden, wenn alle von der Abfrage benötigten Spalten (
SELECT,WHERE,ORDER BY,GROUP BY) Teil des Index sind. Das bedeutet, dass die Datenbank gar keine tatsächlichen Tabellenzeilen mehr aufrufen muss, was die Abfrage weiter beschleunigt. Zum Beispiel könnteSELECT customer_id, order_date FROM orders WHERE customer_id = 123durch((customer_id, order_date))abgedeckt werden.
-- Beispiel, das die Bedeutung der Reihenfolge für eine ORDER BY-Klausel zeigt -- Angenommen, die Tabelle 'orders' mit 'customer_id' und 'order_date' -- Index 1: customer_id zuerst, dann order_date CREATE INDEX idx_customer_date_order ON orders (customer_id, order_date); -- Abfrage 1: Filtern nach customer_id, sortieren nach order_date EXPLAIN SELECT customer_id, order_date, status FROM orders WHERE customer_id = 123 ORDER BY order_date DESC; -- Dies wird wahrscheinlich idx_customer_date_order effizient für WHERE und ORDER BY nutzen. -- Der Index ist nach customer_id sortiert, dann nach order_date innerhalb jedes customer_id, -- was ein effizientes Scannen in der gewünschten Reihenfolge ermöglicht. -- Index 2: order_date zuerst, dann customer_id CREATE INDEX idx_date_customer_order ON orders (order_date, customer_id); -- Abfrage 2: Gleiche Abfrage wie oben, aber mit anderer Indexstruktur EXPLAIN SELECT customer_id, order_date, status FROM orders WHERE customer_id = 123 ORDER BY order_date DESC; -- Während idx_date_customer_order in gewissem Umfang *genutzt* werden könnte, -- ist er für die WHERE-Klausel nach customer_id nicht so effizient, da er nicht die führende Spalte ist. -- Er könnte immer noch bei der ORDER BY helfen, wenn der Optimierer dies nach dem Filtern für vorteilhaft hält. -- Wenn es jedoch keine Gleichheitsbedingung für customer_id gab und nur eine Bereichsabfrage für order_date, -- dann würde idx_date_customer_order glänzen. -- Betrachten Sie eine Abfrage, bei der order_date ein Bereich ist und customer_id eine Gleichung ist EXPLAIN SELECT customer_id, order_date, status FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31' AND customer_id = 456; -- Für diese Abfrage wäre idx_date_customer_order effizienter, -- da er zuerst den Bereich für order_date von seiner äußersten linken Spalte aus nutzen kann.
Die Kunst des Entwurfs zusammengesetzter Indizes liegt im tiefen Verständnis der Abfragelast Ihrer Anwendung. Es geht nicht darum, blind Indizes für jede Spalte oder jede Spaltenkombination zu erstellen, da übermäßige Indizierung eigene Leistungseinbußen für Schreibvorgänge mit sich bringt. Stattdessen geht es darum, Spalten strategisch so anzuordnen, dass die Nützlichkeit des Index für die kritischsten oder häufigsten Abfragen maximiert wird, während der gesamte Indexspeicher minimiert wird. Indem Sie die Regel für das linkeste Präfix, die Spaltenkardinalität und die Art Ihrer Abfragen (Gleichheit vs. Bereich, WHERE vs. ORDER BY) sorgfältig berücksichtigen, können Sie signifikante Leistungssteigerungen erzielen und sicherstellen, dass Ihre Datenbank unter hoher Last effizient arbeitet. Die Reihenfolge der Spalten in einem zusammengesetzten Index ist kein bloßes Detail; sie ist der Eckpfeiler seiner Wirksamkeit.

