Aracılığıyla paylaş

Filtrelenmiş dizinler oluşturma

Şunlar için geçerlidir:SQL ServerAzure SQL VeritabanıAzure SQL Yönetilen ÖrneğiMicrosoft Fabric'te SQL veritabanı

Bu makalede, SQL Server Management Studio (SSMS) veya Transact-SQL kullanarak filtrelenmiş dizin oluşturma açıklanmaktadır. Filtrelenmiş dizin, belirli bir veri alt kümesini kapsayan sorguları karşılamak için özel olarak optimize edilmiş disk tabanlı bir kümelenmemiş satır deposu dizinidir. Tablodaki satırların bir bölümünü dizine almak için bir filtre koşulu kullanır. İyi tasarlanmış bir filtrelenmiş dizin, sorgu performansını artırabilir ve tam tablo dizinleriyle karşılaştırıldığında dizin bakım ve depolama maliyetlerini azaltabilir.

Filtrelenmiş dizinler, tam tablo dizinlerine göre aşağıdaki avantajları sağlayabilir:

  1. Geliştirilmiş sorgu performansı ve plan kalitesi.

    İyi tasarlanmış bir filtrelenmiş dizin, tam tablo içermeyen bir dizinden daha küçük olduğundan ve istatistikleri filtrelediğinden sorgu performansını ve yürütme planı kalitesini artırır. Filtrelenen istatistikler, yalnızca filtrelenmiş dizindeki satırları kapsadığından tam tablo istatistiklerinden daha doğrudur.

  2. Daha düşük dizin bakım maliyetleri.

    Dizin yalnızca veri değiştirme dili (DML) deyimleri dizindeki verileri etkilediğinde güncellenir. Filtrelenmiş dizin, daha küçük olduğundan ve yalnızca dizindeki veriler değiştirildiğinde güncellendiğinden, tam tablolu kümesiz bir dizinle karşılaştırıldığında dizin bakım maliyetlerini azaltır. Özellikle seyrek değiştirilen veriler içerdiğinde çok sayıda filtrelenmiş dizine sahip olmak mümkündür. Benzer şekilde, filtrelenmiş bir dizin yalnızca sık değiştirilen verileri içeriyorsa, dizinin boyutu daha küçükse istatistikleri güncelleştirme maliyeti azalır.

  3. Daha düşük dizin depolama maliyetleri.

    Filtrelenmiş dizin oluşturmak, tam tablo dizini gerekli olmadığında, kümelenmemiş dizinler için disk depolama alanını azaltabilir. Depolama gereksinimlerini önemli ölçüde artırmadan, tam tablodaki bir kümelenmemiş dizini birden çok filtrelenmiş dizinle değiştirebilirsiniz.

Tasarımla ilgili dikkat edilecek noktalar

Bir sütunda sorgular için yalnızca birkaç ilgili değer olduğunda, değerlerin alt kümesinde filtrelenmiş bir dizin oluşturabilirsiniz. Sonuçta elde edilen dizin, aynı anahtar sütunlarında tanımlanan tam tablolu bir kümelenmemiş dizinden daha küçük olur ve bakımının maliyeti daha düşük olur.

Örneğin, aşağıdaki veri senaryolarında filtrelenmiş bir dizin düşünün. Her durumda, WHERE filtrelenmiş dizinden yararlanmak için sorgunun yan tümcesi filtrelenmiş dizinin yan tümcesinin bir alt kümesi WHERE olmalıdır.

  • Bir sütundaki değerler çoğunlukla NULL olduğunda ve sorgu yalnızca NULL olmayan değerlerden seçim yaparken. NULL olmayan veri satırları için filtrelenmiş bir dizin oluşturabilirsiniz.
  • Tablodaki satırlar yinelenen bir iş akışı veya kuyruk işlemi tarafından işlendi olarak işaretlendiğinde. Zamanla, tablodaki satırların çoğu işlenmiş olarak işaretlenir. Henüz işlenmemiş satırlar üzerinde filtrelenmiş bir indeks, bu satırları arayan yinelenen sorguya fayda sağlar.
  • Bir tabloda heterojen veri satırları olduğunda. Bir veya daha fazla veri kategorisi için filtrelenmiş dizin oluşturabilirsiniz. Bu, sorgunun odağını tablonun belirli bir alanına daraltarak bu veri satırlarında sorguların performansını artırabilir. Sonuçta elde edilen dizin, tam tablo üzerinde bir kümelenmemiş dizinden daha küçük ve daha az maliyetli olur.

Limitations

  • Görünümde filtrelenmiş dizin oluşturamazsınız. Ancak sorgu optimizasyonu, görünümde yer alan bir tabloda tanımlanan filtrelenmiş dizinden faydalanabilir. Sorgu iyileştiricisi, sorgu sonuçlarının doğru olup olmadığını bir görünümden seçen bir sorgu için filtrelenmiş dizini dikkate alır.

  • Filtre ifadesinde erişilen sütun bir CLR veri türünde olduğunda tabloda filtrelenmiş dizin oluşturamazsınız.

  • Filtrelenmiş dizinler, dizinlenmiş görünümlere göre aşağıdaki avantajlara sahiptir:

    • Daha düşük dizin bakım maliyetleri. Örneğin, sorgu işlemcisi filtrelenmiş dizini güncelleştirmek için dizinlenmiş görünümden daha az CPU kaynağı kullanır.

    • Geliştirilmiş plan kalitesi. Örneğin, sorgu derleme sırasında, sorgu iyileştiricisi eşdeğer dizinli görünümden daha fazla durumda filtrelenmiş dizin kullanmayı göz önünde bulundurr.

    • Çevrimiçi dizin yeniden oluşturma. Sorgular için kullanılabilir durumdayken filtrelenmiş dizinleri yeniden oluşturabilirsiniz. Çevrimiçi dizin yeniden derlemeleri dizinlenmiş görünümler için desteklenmez. Daha fazla bilgi için REBUILD seçeneğine bakın.

    • Benzersiz olmayan dizinler. Filtrelenmiş dizinler benzersiz olmayabilir, ancak dizinlenmiş görünümler benzersiz olmalıdır.

  • Filtrelenmiş dizinler bir tabloda tanımlanır ve yalnızca basit karşılaştırma işleçlerini destekler. Birden çok tabloya başvuran veya karmaşık mantığı olan bir filtre ifadesi gerekiyorsa, bir görünüm oluşturmanız gerekir. Filtrelenmiş dizinler işleçleri desteklemez LIKE .

  • Filtrelenen dizin ifadesi sorgu koşuluna eşdeğerse ve sorgu filtrelenmiş dizin ifadesindeki sütunu sorgu sonuçlarıyla döndürmediyse, filtrelenmiş dizin ifadesindeki bir sütunun anahtar veya eklenmiş sütun olması gerekmez.

  • Sorgu koşulu filtrelenmiş dizin ifadesiyle eşdeğer olmayan bir karşılaştırmada sütunu kullanıyorsa, filtrelenmiş dizin ifadesindeki bir sütun, filtrelenmiş dizin tanımında bir anahtar veya eklenen sütun olmalıdır.

  • Filtrelenmiş dizin ifadesindeki bir sütun, eğer sorgu sonuç kümesinde yer alıyorsa, filtrelenmiş dizin tanımında anahtar sütun veya dahil edilmiş sütun olarak bulunmalıdır.

  • Tablonun kümelenmiş dizin anahtarının filtrelenmiş dizin tanımına bir anahtar veya eklenmiş sütun olması gerekmez. Kümelenmiş dizin anahtarı, filtrelenmiş dizinler de dahil olmak üzere tüm kümelenmemiş dizinlere otomatik olarak eklenir. Dizin mimarisi ve tasarım kılavuzunda daha fazla bilgi edinin.

  • Filtrelenen dizinin filtrelenmiş dizin ifadesinde belirtilen karşılaştırma işleci örtük veya açık bir veri dönüştürmeyle sonuçlanırsa, dönüştürme bir karşılaştırma işlecinin sol tarafında gerçekleşirse bir hata oluşur. Çözüm, filtrelenmiş dizin ifadesini karşılaştırma işlecinin sağ tarafına veri dönüştürme işleci (CAST veya CONVERT) ile yazmaktır.

  • SET söz diziminde filtrelenmiş dizin oluşturmak için gerekli seçenekleri gözden geçirin

  • Filtreler birincil anahtara veya benzersiz kısıtlamalara uygulanamaz, ancak özelliğine UNIQUE sahip dizinlere uygulanabilir.

  • Filtrelenmiş bir dizin oluşturabilir ve anahtarda hesaplanan bir sütuna başvurabilir veya içerebilir, ancak filtre tanımında hesaplanan bir sütuna başvuramazsınız.

Permissions

ALTER izni, tablo veya görünüm üzerinde gereklidir. Kullanıcının sysadmin sabit sunucu rolünün veya db_ddladmin ve db_owner sabit veritabanı rollerinin üyesi olması gerekir. Filtrelenmiş dizin ifadesini değiştirmek için kullanın CREATE INDEX WITH DROP_EXISTING.

SSMS ile filtrelenmiş dizin oluşturma

  1. Nesne Gezgini'nde artı işaretini seçerek filtrelenmiş dizin oluşturmak istediğiniz tabloyu içeren veritabanını genişletin.

  2. artı işaretini seçerek Tablolar klasörünü genişletin.

  3. Filtrelenmiş dizin oluşturmak istediğiniz tabloyu genişletmek için artı işaretini seçin.

  4. Dizinler klasörüne sağ tıklayın, Yeni Dizin'in üzerine gelin ve Kümelenmemiş Dizin... öğesini seçin.

  5. Yeni Dizin iletişim kutusundaki Genel sayfasında, Dizin adı kutusuna yeni dizinin adını girin.

  6. Dizin anahtarı sütunları'nın altında Ekle...'yi seçin.

  7. table_nameSütun Seç iletişim kutusunda, dizine eklenecek tablo sütununun veya sütunlarının onay kutusunu veya onay kutularını seçin.

  8. Tamam'ı seçin.

  9. Filtre sayfasındaki Filtre İfadesi'nin altında, filtrelenmiş dizini oluşturmak için kullanacağınız SQL ifadesini girin.

  10. Tamam'ı seçin.

Transact-SQL ile filtrelenmiş dizin oluşturma

Bu makaledeki kod örnekleri, AdventureWorks2025 giriş sayfasından indirebileceğiniz AdventureWorksDW2025 veya örnek veritabanını kullanır.

  1. Nesne Gezgini'nde Veritabanı Motoru'nun bir örneğine bağlanın.

  2. Standart araç çubuğunda, Yeni Sorgu'yu seçin.

  3. Aşağıdaki örneği kopyalayıp sorgu penceresine yapıştırın ve Yürüt'e tıklayın.

USE AdventureWorks2022;
GO

DROP INDEX IF EXISTS FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials
GO

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL ;
GO

Filtrelenen dizin FIBillOfMaterialsWithEndDate aşağıdaki sorgu için geçerlidir. Sorgu iyileştiricisinin filtrelenmiş dizini kullanıp kullanmadığını belirlemek için gerçek bir yürütme planı görüntüleyin.

USE AdventureWorks2022;
GO

SELECT ProductAssemblyID, ComponentID, StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
    AND ComponentID = 5
    AND StartDate > '01/01/2008' ;
GO

İlgili içerik

  • Last updated on