Dlaczego dodanie flagi usuwania logicznego do kompozytowego indeksu czasami pogarsza wydajność zapytań w aktywnych rekordach w porównaniu do strategii indeksów częściowych, oraz jak szacowanie selektywności przez planner zapytań różni się pomiędzy tymi podejściami?

Odpowiedź na pytanie

Historia pytania

Wzorce usuwania logicznego powstały jako alternatywa dla twardego usuwania w celu tworzenia śladów audytowych i odzyskiwania danych. Wczesne implementacje używały prostych flag boolean lub kolumn znaczników czasu, ale programiści wkrótce napotkali problemy z wydajnością, gdy te flagi zostały włączone do standardowych indeksów B-drzew. Problem stał się szczególnie widoczny wraz z powszechnym wprowadzeniem częściowych indeksów PostgreSQL i indeksów filtrowanych SQL Server w połowie lat 2000, które umożliwiały indeksowanie tylko aktywnych rekordów. Zrozumienie szacowania selektywności — jak planner zapytań przewiduje procent wierszy odpowiadających warunkowi — stało się kluczowe w porównaniu pełnych indeksów kompozytowych z strategią indeksów częściowych.

Problem

Gdy flaga usuwania logicznego (np. is_deleted) jest dodawana do indeksu kompozytowego, takiego jak (is_deleted, user_id, created_at), optymalizator bazy danych może źle oszacować selektywność wierszy dla zapytań filtrujących WHERE is_deleted = false. Jeśli 90% wierszy jest aktywnych, optymalizator może wybrać skanowanie sekwencyjne zamiast skanowania indeksu, lub odwrotnie, jeśli rozkład jest przesunięty, może nieproporcjonalnie faworyzować indeks. Indeksy częściowe (WHERE is_deleted = false) przechowują tylko aktywne wiersze, zapewniając wysoką selektywność, ale standardowe indeksy kompozytowe przechowują wszystkie wiersze, prowadząc do nadmiernego rozrostu indeksu i niejednoznacznych oszacowań kardynalności, gdy statystyki nie odzwierciedlają dokładnie rozkładu usunięć logicznych.

Rozwiązanie

Wdrożenie indeksów częściowych (w PostgreSQL) lub indeksów filtrowanych (w SQL Server), które całkowicie wykluczają usunięte wiersze, w połączeniu z oddzielnymi indeksami dla usuniętych danych w razie potrzeby. Dla MySQL lub baz danych, które nie obsługują indeksowania częściowego, używaj indeksu kompozytowego z flagą usuwania logicznego jako pierwszą kolumną tylko wtedy, gdy aktywny zestaw danych jest mały; w przeciwnym razie podziel tabelę według statusu usunięcia. Eksploracyjnie analizuj statystyki tabeli po usunięciach masowych, aby zapobiec nieaktualnym histogramom. Podczas zapytywania o aktywne rekordy, używaj dokładnego predykatu z definicji indeksu częściowego (WHERE is_deleted = false), aby zapewnić, że optymalizator rozpozna zastosowanie indeksu.

Przykład kodu

-- PostgreSQL: Indeks częściowy dla aktywnych rekordów
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_deleted = false;

-- SQL Server: Odpowiedni indeks filtrowany
CREATE INDEX IX_Active_Users_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

-- Zapytanie, które wykorzystuje indeks częściowy
SELECT * FROM users 
WHERE email = 'alice@example.com' 
AND is_deleted = false;

Sytuacja z życia

Platforma SaaS zarządzająca 10 milionami rekordów użytkowników doświadczyła poważnych opóźnień w swoim panelu administracyjnym podczas filtrowania aktywnych użytkowników według daty utworzenia. Początkowo używali indeksu kompozytowego (is_deleted, created_at) w PostgreSQL, zakładając, że przyspieszy zapytania WHERE is_deleted = false ORDER BY created_at. Jednak wraz z rozrostem zestawu danych do 80% usuniętych logicznie historycznych kont, zapytania zaczęły zajmować 8-12 sekund, ponieważ planner niedoszacował koszt skanowania nadmiarowego indeksu.

Rozwiązanie A: Utrzymuj indeks kompozytowy i wymuś wykorzystanie indeksu za pomocą wskazówek. To podejście używało SET enable_seqscan = off lub wskazówek planu zapytania, aby wymusić wykorzystanie indeksu. Chociaż poprawiło to konkretną wydajność zapytań tymczasowo, stworzyło dług terminowy w zakresie utrzymania i często wymuszało suboptymalne plany dla innych wzorców dostępu, gdy rozkład danych się zmieniał. Rozwiązanie nie rozwiązało również problemu nadmiarowego rozrostu indeksu, co prowadziło do zwiększonego przechowywania i obciążenia VACUUM.

Rozwiązanie B: Stwórz oddzielne indeksy częściowe dla aktywnych i usuniętych rekordów. Wdrożenie CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false zmniejszyło rozmiar indeksu o 80% i pozwoliło plannerowi dokładnie oszacować 2 miliony aktywnych wierszy w porównaniu do 8 milionów usuniętych. Czas odpowiedzi zredukowano do 40ms, ale wymagało to refaktoryzacji wszystkich zapytań aplikacji, aby zapewnić, że predykat is_deleted = false pozostał jawny i nie został ukryty w funkcjach lub widokach, które zasłaniały warunek.

Zespół wybrał Rozwiązanie B, ponieważ zapewniło to zrównoważoną wydajność bez konieczności utrzymywania wskazówek zapytań. Efektem była 95% redukcja opóźnienia zapytań i wyeliminowanie okresowych problemów z nadmiarem VACUUM spowodowanych wcześniejszym zbyt dużym indeksem kompozytowym. Monitorowanie potwierdziło konsekwentne czasy odpowiedzi poniżej jednej sekundy dla głównych przypadków użycia panelu instrumentów.

Co kandydaci często przeoczają

Jak obecność wartości NULL w kolumnie znacznika czasu usunięcia logicznego (używając NULL dla aktywnych, znacznika czasu dla usuniętych) wpływa na wykorzystanie indeksów częściowych w porównaniu do podejść z flagą boolean?

Podczas używania nullable deleted_at znaczników czasu, indeksy częściowe jak WHERE deleted_at IS NULL napotykają wyzwania związane z obsługą NULL w indeksach przez PostgreSQL. W przeciwieństwie do flag boolean, gdzie = false jest eksplicytne i sargowalne, warunki IS NULL wymagają od planera uznania stosowności indeksu, co może się nie udać, jeśli zapytanie używa zparametryzowanych instrukcji, gdzie planner nie może udowodnić, że parametr będzie NULL. Dodatkowo, aktualizacje ustawiające deleted_at = CURRENT_TIMESTAMP powodują nadmiar indeksu w indeksie częściowym dla aktywnych rekordów, ponieważ wiersze są z niego usuwane, podczas gdy aktualizacje flag boolean zmieniają bit, ale pozostają w pełnym indeksie kompozytowym. Podejście nullable wymaga częstszych wywołań ANALYZE i starannego rozważania czynników wypełnienia indeksów, aby poradzić sobie z wysoką churnem zmian statusu usunięcia.

Dlaczego indeks pokrywający zawierający kolumny usuwania logicznego może powodować wolniejsze zapisy niż oczekiwano, nawet przy niskiej częstotliwości usunięcia?

Indeksy pokrywające (używając klauzuli INCLUDE w PostgreSQL 11+ lub SQL Server) dodający is_deleted, aby uniknąć wyszukiwania w tabeli, w rzeczywistości pogarszają wydajność zapisu, ponieważ każda operacja usunięcia logicznego (aktualizacja) musi modyfikować wiele struktur indeksów. Gdy użytkownik jest usuwany logicznie, baza danych musi oznaczyć stary wpis indeksu jako martwy w aktywnym indeksie częściowym, dodać nowy wpis do wszelkich indeksów rekordów usuniętych i zaktualizować wskaźniki do stosu indeksu pokrywającego. Kandydaci często przeoczają, że indeksy częściowe izolują ten churn — tylko konkretne indeksy częściowe dla aktywnych lub usuniętych stanów są modyfikowane — podczas gdy indeksy pokrywające na głównej tabeli wymagają aktualizacji struktury głównego indeksu niezależnie od statusu usunięcia, co tworzy wzrost zapisu, który wpływa na przezroczystość transakcji.

Kiedy optymalizator zapytań ignoruje indeks częściowy dla usuniętych danych, nawet gdy zapytanie wyraźnie filtruje usunięte rekordy?

Jeśli indeks częściowy jest zdefiniowany jako WHERE is_deleted = true dla zapytań audytowych, ale aplikacja używa przygotowanej instrukcji z parametrem $1 zarówno dla aktywnych, jak i usuniętych zapytań, PostgreSQL może zbuforować ogólny plan, który nie rozpoznaje indeksu częściowego dla konkretnego przypadku true. Dzieje się tak, ponieważ przygotowane instrukcje generują plany przedwiązania wartości parametrów, a optymalizator nie może udowodnić, że $1 = true zawsze będzie pasować do predykatu indeksu. Kandydaci przeoczają, że dynamiczny SQL lub wskazówki ponownej kompilacji (OPTION (RECOMPILE) w SQL Server, wykonywanie z wartościami dosłownymi w PostgreSQL) są wymagane, aby zapewnić, że planner widzi konkretną wartość i dopasowuje ją do predykatu indeksu częściowego, zamiast polegać na ogólnych planach, które domyślnie przechodzą do skanowania sekwencyjnego z powodu niepewności co do wartości parametru.