Warum kann das Hinzufügen eines booleschen Flags zu einem kombinierten Index die Leistung von Active-Record-Abfragen im Vergleich zu einer Strategie mit teilweisem Index manchmal verschlechtern, und wie unterscheidet sich die Schätzung der Selektivität des Abfrageplaners zwischen diesen Ansätzen?

Antwort auf die Frage

Historie der Frage

Soft-Delete-Muster entstanden als Alternative zur harten Löschung für Audit-Trails und Datenwiederherstellungen. Frühe Implementierungen verwendeten einfache boolesche Flags oder Zeitstempelspalten, aber Entwickler stießen bald auf Leistungsabfälle, als diese Flags in Standard-B-Baum-Indizes integriert wurden. Das Problem wurde mit der weit verbreiteten Einführung von PostgreSQL-Teilindizes und SQL Server-gefilterten Indizes in den 2000er Jahren deutlich, die das Indizieren nur aktiver Datensätze erlaubten. Das Verständnis der Selektivitätsschätzung – wie der Abfrageplaner den Prozentsatz der Zeilen schätzt, die einer Bedingung entsprechen – wurde entscheidend, wenn man volle kombinierte Indizes mit strategischen Teilindizes verglich.

Das Problem

Wenn ein Soft-Delete-Flag (z.B. is_deleted) zu einem kombinierten Index wie (is_deleted, user_id, created_at) hinzugefügt wird, kann der Datenbankoptimierer die Zeilenselektivität für Abfragen, die WHERE is_deleted = false filtern, falsch berechnen. Wenn 90 % der Zeilen aktiv sind, könnte der Optimierer einen sequentiellen Scan anstelle eines Index-Scans wählen oder umgekehrt, wenn die Verteilung verzerrt ist, könnte er den Index unangemessen bevorzugen. Teilindizes (WHERE is_deleted = false) speichern nur aktive Zeilen, was eine hohe Selektivität garantiert, während Standardkombinationsindizes alle Zeilen speichern, was zu einer Indexaufblähung und unscharfen Kardinalitätsschätzungen führt, wenn Statistiken die Soft-Delete-Verteilung nicht genau widerspiegeln.

Die Lösung

Implementieren Sie Teilindizes (in PostgreSQL) oder gefilterte Indizes (in SQL Server), die weich gelöschte Zeilen vollständig ausschließen, verbunden mit separaten Indizes für gelöschte Daten, falls erforderlich. Für MySQL oder Datenbanken ohne partielle Indizierung verwenden Sie einen kombinierten Index mit dem Soft-Delete-Flag nur als führende Spalte, wenn der aktive Datensatz klein ist; andernfalls partitionieren Sie die Tabelle nach Löschstatus. Analysieren Sie die Tabellenstatistiken explizit nach Massendeletion, um veraltete Histogramme zu vermeiden. Verwenden Sie bei Abfragen aktiver Datensätze den genauen Prädikatsausdruck aus der Definition des teilweisen Index (WHERE is_deleted = false), um sicherzustellen, dass der Optimierer die Anwendbarkeit des Index erkennt.

Codebeispiel

-- PostgreSQL: Teilindex nur für aktive Datensätze
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_deleted = false;

-- SQL Server: Gefilterter Index-Äquivalent
CREATE INDEX IX_Active_Users_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

-- Abfrage, die den Partialindex nutzt
SELECT * FROM users 
WHERE email = 'alice@example.com' 
AND is_deleted = false;

Situation aus dem Leben

Eine SaaS-Plattform, die 10 Millionen Benutzerdatensätze verwaltet, erlebte erhebliche Verlangsamungen im Admin-Dashboard, als aktive Benutzer nach Erstellungsdatum gefiltert wurden. Zunächst verwendeten sie einen kombinierten Index (is_deleted, created_at) in PostgreSQL und gingen davon aus, dass dies die Abfragen WHERE is_deleted = false ORDER BY created_at beschleunigen würde. Mit dem Wachstum des Datensatzes auf 80 % weich gelöschte historische Konten begannen Abfragen 8–12 Sekunden zu dauern, da der Planer die Kosten des Scannens des aufgeblähten Indexes unterschätzte.

Lösung A: Behalten Sie den kombinierten Index und zwingen Sie die Nutzung des Indexes mit Hinweisen. Dieser Ansatz verwendete SET enable_seqscan = off oder Abfrageplanhinweise, um die Indexnutzung zu erzwingen. Während dies einige Abfragen vorübergehend verbesserte, führte es zu Wartungsschulden und zwang oft zu suboptimalen Plänen für andere Zugriffsmuster, als sich die Datenverteilung verschob. Diese Lösung adressierte auch nicht das zugrunde liegende Problem der Indexaufblähung, das erhöhte Speicher- und VACUUM-Überhead verursachte.

Lösung B: Erstellen Sie separate Teilindizes für aktive und gelöschte Datensätze. Die Implementierung von CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false reduzierte die Indexgröße um 80 % und ermöglichte es dem Planer, 2 Millionen aktive Zeilen gegenüber 8 Millionen gelöschten genau abzuschätzen. Die Abfragen fielen auf 40 ms, erforderten jedoch eine Umgestaltung aller Anwendungsabfragen, um sicherzustellen, dass das Prädikat is_deleted = false explizit blieb und nicht in Funktionen oder hinter Ansichten verborgen war, die die Bedingung verschleierten.

Das Team wählte Lösung B, weil sie nachhaltige Leistung ohne Wartung der Abfragehinweise bot. Das Ergebnis war eine Reduzierung der Abfrage-Latenz um 95 % und die Beseitigung von sporadischen VACUUM-Aufblähungsproblemen, die durch den vorher übergroßen kombinierten Index verursacht wurden. Das Monitoring bestätigte konsistente Antwortzeiten von unter einer Sekunde für die primären Anwendungsfälle des Dashboards.

Was Kandidaten oft übersehen

Wie beeinflusst die Anwesenheit von NULL-Werten in einer Soft-Delete-Zeitstempelspalte (NULL für aktiv, Zeitstempel für gelöscht) die Nutzung des teilweisen Index im Vergleich zu booleschen Flag-Ansätzen?

Bei der Verwendung eines nullable deleted_at-Zeitstempels stehen partielle Indizes wie WHERE deleted_at IS NULL vor Herausforderungen mit der Handhabung der NULL-Indexierbarkeit in PostgreSQL. Anders als bei booleschen Flags, wo = false explizit und sargfähig ist, erfordert die Bedingung IS NULL, dass der Planer die Anwendbarkeit des Indexes erkennt, was fehlschlagen kann, wenn die Abfrage parametrisierten Anweisungen nutzt, bei denen der Planer nicht beweisen kann, dass der Parameter NULL sein wird. Darüber hinaus verursachen Updates, die deleted_at = CURRENT_TIMESTAMP setzen, eine Indexaufblähung im partiellen Index für aktive Datensätze, da Zeilen daraus entfernt werden, während Updates mit dem booleschen Flag das Bit umschalten, aber innerhalb eines vollständigen kombinierten Index bleiben. Der nullable Ansatz erfordert häufigere ANALYZE-Aufrufe und sorgfältige Überlegungen zu den Indexfüllfaktoren, um den hohen Wechsel der Löschstatusänderungen zu bewältigen.

Warum könnte ein Abdeckungsindex, der Soft-Delete-Spalten enthält, die Schreibvorgänge langsamer machen als erwartet, selbst bei niedriger Löschfrequenz?

Abdeckungsindizes (die INCLUDE-Klausel in PostgreSQL 11+ oder SQL Server verwenden) fügen is_deleted hinzu, um Tabellenlookups zu vermeiden, verschlechtern tatsächlich die Schreibleistung, da jeder Soft-Delete-Vorgang (ein UPDATE) mehrere Indexstrukturen ändern muss. Wenn ein Benutzer weich gelöscht wird, muss die Datenbank den alten Indexeintrag im aktiven partiellen Index als tot markieren, einen neuen Eintrag in irgendein gelöschtes Datensatzindex einfügen und die Heap-Pointer des Abdeckungsindex aktualisieren. Kandidaten übersehen oft, dass partielle Indizes diesen Wechsel isolieren – nur die spezifischen partiellen Indizes für aktive oder gelöschte Zustände werden geändert – während Abdeckungsindizes auf der Haupttabelle die Hauptindexstruktur unabhängig vom Soft-Delete-Status aktualisieren müssen, was eine Schreibamplifikation erzeugt, die den Transaktionsdurchsatz beeinträchtigt.

Wann ignoriert der Abfrageoptimierer einen partiellen Index für weich gelöschte Daten, selbst wenn die Abfrage explizit nach gelöschten Datensätzen filtert?

Wenn der partielle Index als WHERE is_deleted = true für Audit-Abfragen definiert ist, die Anwendung jedoch eine vorbereitete Anweisung mit einem Parameter $1 für sowohl aktive als auch gelöschte Abfragen verwendet, kann PostgreSQL einen generischen Plan cachen, der den partiellen Index für den spezifischen true-Fall nicht erkennt. Dies geschieht, weil vorbereitete Anweisungen Pläne generieren, bevor die Parameterwerte zugeordnet werden, und der Optimierer nicht beweisen kann, dass $1 = true immer dem Indexprädikat entspricht. Kandidaten übersehen, dass dynamisches SQL oder Recompilationshinweise (OPTION (RECOMPILE) in SQL Server, Ausführung mit Literale in PostgreSQL) erforderlich sind, um sicherzustellen, dass der Planer den konkreten Wert sieht und ihn mit dem Prädikat des partiellen Index abgleicht, anstatt sich auf generische Pläne zu verlassen, die aufgrund der Unsicherheit des Parameterwerts standardmäßig sequentielle Scans durchführen.