Sorunun yanıtı
Soru geçmişi
Yumuşak silme desenleri, denetim izleri ve veri kurtarma için sert silmeye alternatif olarak ortaya çıktı. İlk uygulamalar, basit boolean bayraklar veya zaman damgası sütunları kullandı, ancak geliştiriciler bu bayraklar standart B-ağaç indekslerine dahil edildiğinde performansın düştüğünü fark ettiler. Sorun, 2000'lerin ortalarında, yalnızca aktif kayıtların indekslenmesini sağlayan PostgreSQL kısmi indeksleri ve SQL Server filtrelenmiş indekslerinin yaygın olarak benimsenmesiyle önem kazandı. Seçicilik tahminini anlamak - sorgu planlayıcısının bir koşula uyan satırların yüzdesini tahmin etmesi - tam bileşik indekslerle kısmi indeks stratejilerini karşılaştırırken kritik hale geldi.
Sorun
Bir yumuşak silme bayrağı (örneğin, is_deleted) (is_deleted, user_id, created_at) şeklindeki bir bileşik indekse eklendiğinde, veritabanı optimizasyonu, WHERE is_deleted = false koşuluyla filtrelenen sorgular için satır seçiciliğini yanlış hesaplayabilir. Eğer satırların %90'ı aktife ise, optimizasyon sıralı taramayı bir indeks taraması yerine tercih edebilir; aksi takdirde dağılım bozulursa indeksi uygun bir şekilde tercih edebilir. Kısmi indeksler (WHERE is_deleted = false) yalnızca aktif satırları depolayarak yüksek seçicilik garantiler, ancak standart bileşik indeksler tüm satırları depolar, bu da indekste fazlalık ve istatistikler doğru bir şekilde yumuşak silme dağılımını yansıtmadığında belirsiz kardinalite tahminlerine yol açar.
Çözüm
Yumuşak silinmiş satırları tamamen dışlayan kısmi indeksler (PostgreSQL'de) veya filtrelenmiş indeksler (SQL Server'da) uygulayın; gerektiğinde silinen veriler için ayrı indeksler ekleyin. MySQL gibi kısmi indeksleme olmadan çalışan veritabanları için, yalnızca aktif veri kümesi küçükse yumuşak silme bayrağını öncü sütun olarak kullanarak bir bileşik indeks kullanın; aksi takdirde, tabloyu silme durumu ile bölümlere ayırın. Toplu silmelerden sonra tablo istatistiklerini açıkça analiz edin, böylece bayat histogramları önleyin. Aktif kayıtları sorgularken, optimizasyonun indeks uygulanabilirliğini tanıması için kısmi indeks tanımından tam olarak aynı koşulu (WHERE is_deleted = false) kullanın.
Kod örneği
-- PostgreSQL: Sadece aktif kayıtlar için kısmi indeks CREATE INDEX idx_active_users_email ON users(email) WHERE is_deleted = false; -- SQL Server: Filtrelenmiş indekse eşdeğer CREATE INDEX IX_Active_Users_Email ON Users(Email) WHERE IsDeleted = 0; -- Kısmi indeksi kullanan sorgu SELECT * FROM users WHERE email = 'alice@example.com' AND is_deleted = false;
Hayattan durum
10 milyon kullanıcı kaydı yöneten bir SaaS platformu, yaratım tarihine göre aktif kullanıcıları filtrelerken yönetici panellerinde ciddi yavaşlamalar yaşadı. Başlangıçta, WHERE is_deleted = false ORDER BY created_at sorgularını hızlandıracağını varsayarak PostgreSQL'de (is_deleted, created_at) şeklinde bir bileşik indeks kullandılar. Ancak, veri kümesi %80 yumuşak silinmiş tarihi hesaplara ulaştıkça sorgular 8-12 saniye sürmeye başladı çünkü planlayıcı, şişmiş indeksi taramanın maliyetini hafife aldı.
Çözüm A: Bileşik indeksi koruyun ve indeks kullanımını zorlayın.
Bu yaklaşım, indeks kullanımını zorlamak için SET enable_seqscan = off veya sorgu planı ipuçları kullandı. Belirli sorgular için geçici bir iyileştirme sağlasa da, bakım borcu yarattı ve veri dağılımı değiştiğinde diğer erişim kalıpları için sıklıkla suboptimal planlar oluşturdu. Çözüm, önceki aşırı büyük bileşik indeks nedeniyle artan depolama ve VACUUM yükünün sorununu çözmede de başarısız oldu.
Çözüm B: Aktif ve silinen kayıtlar için ayrı kısmi indeksler oluşturun.
CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false uygulamak, indeks boyutunu %80 oranında azalttı ve planlayıcının 2 milyon aktif satırı 8 milyon silinmiş satır ile doğru bir şekilde tahmin etmesine olanak tanıdı. Sorgular 40 ms'ye düştü, ancak is_deleted = false koşulunun açık kalmasını sağlamak için tüm uygulama sorgularında yeniden yapılandırma gerektirdi; bu, koşulu gizleyen işlevlere veya görünümlere sarılmamalıydı.
Takım, ipucu bakımını gerektirmeyen sürdürülebilir bir performans sağladığı için Çözüm B’yi seçti. Sonuç olarak, sorgu gecikmesinde %95'lik bir azalma ve önceki büyük bileşik indeksin neden olduğu periyodik VACUUM fazlalık sorunlarının ortadan kaldırılması gerçekleşti. İzleme, panellerin ana kullanım durumları için tutarlı alt saniye yanıt sürelerini doğruladı.
Adayların genellikle gözden kaçırdığı noktalar
Yumuşak silme zaman damgası sütununda NULL değerlerinin varlığı (aktif için NULL, silinen için zaman damgası kullanımı) kısmi indeks kullanımını boolean bayrak yaklaşımlarına göre nasıl etkiler?
Nullable deleted_at zaman damgası kullanıldığında, kısmi indeksler gibi WHERE deleted_at IS NULL koşulları, PostgreSQL'de NULL indeksleme ile ilgili zorluklarla karşılaşır. = false gibi açık ve sargable olan boolean bayrakların aksine, IS NULL koşulları planlayıcının indeks uygulanabilirliğini tanımasını gerektirir; bu da parametreli ifadelerin kullanıldığı sorgularda, planlayıcının parametrenin her zaman NULL olduğunu kanıtlayamadığı durumlarda başarısız olabilir. Ayrıca, deleted_at = CURRENT_TIMESTAMP olarak güncelleme yapmak, aktif kayıtlar için kısmi indekste indeks fazlalığı oluşturur, oysaki boolean bayrak güncellemeleri bit için döner ancak tam bir bileşik indeks içinde kalır. Nullable yaklaşım, yüksek silme durumu değişikliklerinin elden geçirmesi nedeniyle daha sık ANALYZE çağrıları ve indeks dolum faktörlerinin dikkatli bir şekilde değerlendirilmesini gerektirir.
Kapsama indeksi, yumuşak silme sütunlarını içerdiğinde neden yazmaların beklenenden daha yavaş olmasına neden olabilir, düşük silme sıklığında bile?
Kapsama indeksleri (PostgreSQL 11+ veya SQL Server'da INCLUDE ifadesini kullanarak) is_deleted eklenerek tablo aramalarını önlemek için, aslında yazma performansını kötüleştirir çünkü her yumuşak silme işlemi (bir GÜNCELLEME) birden fazla indeks yapısını değiştirmek zorundadır. Bir kullanıcı yumuşak silindiğinde, veritabanı aktif kısmi indekste eski indeks girişini ölü olarak işaretlemeli, silinen kayıt indekslerine yeni bir giriş eklemeli ve kapsama indeksinin yığın işaretçilerini güncellemelidir. Adaylar genellikle kısmi indekslerin bu değişimi izole ettiğini gözden kaçırıyor; yalnızca aktif veya silinmiş durumlar için belirli kısmi indeksler değiştirilirken, ana tabloda kapsama indeksleri, yumuşak silme durumu ne olursa olsun, birincil indeks yapısını güncellemeyi gerektirir, bu da yazma amplifikasyonu oluşturarak işlem verimliliğini etkiler.
Sorgu optimizasyonu neden, sorgu açıkça silinen kayıtlar için filtrelendiğinde bile bir kısmi indeksi göz ardı eder?
Kısmi indeks WHERE is_deleted = true şeklinde denetim sorguları için tanımlanmışsa, ancak uygulama aktif ve silinmiş sorgular için bir parametre $1 ile hazırlanmış ifadeyi kullanıyorsa, PostgreSQL muhtemelen belirli true durumu için kısmi indeksi tanımayan genel bir planı önbelleğe alır. Bunun nedeni, hazırlanmış ifadelerin planları, parametre değerleri bağlanmadan önce oluşturulmasıdır ve optimizasyon, $1 = true ifadesinin her zaman indeks koşulunu karşılayacağını kanıtlayamaz. Adaylar, planlayıcının somut değerleri görmesi ve bunları kısmi indeks koşuluna eşlemesi için dinamik SQL veya yeniden derleme ipuçlarının (OPTION (RECOMPILE) SQL Server'da, PostgreSQL'de literal değerlerle yürütme) gerektirdiğini gözden kaçırır; böylece, parametre değerinin belirsizliği nedeniyle sıralı taramalara varsayılan genel planlar üzerinde bağımlı kalmazlar.