SQL Server Full-Text Search: temel yapı taşları ve özellikler
Bart Wullems, SQL Server'ın yerleşik Full-Text Search özelliğinin yapı taşlarını ve sorgu yeteneklerini derledi. LIKE '%term%' gibi baştan joker karakterli aramalar index seek kullanamadığı ve dil bilgisi tanımadığı için ters indeks, word breaker ve stemmer devreye giriyor. Yazıda full-text catalog ve index oluşturma, CONTAINS/FREETEXT sorguları, CONTAINSTABLE ile sıralama ve stoplist davranışı ele alınıyor.
Kilit noktalar
- LIKE '%term%' baştan joker karakter nedeniyle index seek yapamaz; full-text search ters indeks kullanır.
- Full-text index, tek kolonlu ve nullable olmayan benzersiz bir index (genelde primary key) gerektirir.
- CHANGE_TRACKING = AUTO değişiklikleri arka planda alır; indeks asenkron olduğu için yeni satırlar gecikebilir.
- CONTAINS önek ("mount*"), tam ifade, AND/OR/AND NOT, NEAR((a, b), 5) ve FORMSOF(INFLECTIONAL, ...) destekler.
- CONTAINSTABLE/FREETEXTTABLE RANK döndürür; ISABOUT(... WEIGHT(.8)) ile terim ağırlıkları ayarlanır.
- Varsayılan sistem stoplist 'the', 'and' gibi sözcükleri indexlemez; sys.fulltext_system_stopwords ile listelenebilir.
Büyük tablolarda LIKE '%term%' her satırı taramak zorunda kalır, çekimli biçimleri bulamaz ve alaka (relevance) kavramı yoktur. SQL Server'ın Full-Text Search özelliği bunun yerine ters indeks kurar: word breaker metni kelimelere ayırır, stemmer 'ride' ile 'riding' gibi biçimleri eşleştirir. Şirket içinde bu özellik isteğe bağlıdır ve SERVERPROPERTY('IsFullTextInstalled') ile kontrol edilir; Azure SQL Database'de ek kurulum gerekmez.
Önce full-text catalog, sonra tablo başına tek bir full-text index oluşturulur; index'in satırları eşleyebilmesi için benzersiz, tek kolonlu bir index gerekir. Kolon bazında LANGUAGE 1033 gibi bir LCID verilir ve CHANGE_TRACKING = AUTO değişiklikleri arka planda alır. CONTAINS önek, tam ifade, AND/OR/AND NOT, NEAR ve FORMSOF(INFLECTIONAL) destekler; FREETEXT daha esnek ama denetimi zayıftır. CONTAINSTABLE/FREETEXTTABLE RANK döndürür, ISABOUT ile WEIGHT ağırlık verir. Varsayılan stoplist 'the', 'and' gibi sözcükleri hiç indexlemez, bu yüzden bazı aramalar sessizce boş dönebilir.
Gerçek dünyada kullanımı
- Ürün kataloğunda açıklama alanı üzerinden çekim eklerini de yakalayan bir arama kutusu
- Müşteri destek taleplerinde anahtar kelime yerine alaka sırasına göre listeleme
- varbinary(max) kolonlarında saklanan Word/PDF dokümanları içinde arama
- ElasticSearch'e geçmeden önce tek veritabanıyla idare etmesi gereken iç uygulamalar
Neden önemli
Mimariye yeni bir arama bileşeni eklemeden, dil farkındalığı olan ve alaka sırasına göre sonuç döndüren bir arama elde ediyorsunuz; ancak varsayılan stoplist ve asenkron indeks davranışı gözden geçirilmezse sonuçlar sessizce eksik kalabilir.