Bu makalede Sql Server ’da metin tabanlı veriler üzerinde etkili arama performansı sağlayan Full-Text Search ’ten bahsedeceğim.
Full-Text Search işlemlerini yapabilmesi için Full-Text index kolona sahip olmamız gerekmektedir. Full-Text indexler, metin içeriği barındıran ve belirli veri türlerine sahip kolonlarda oluşturulabilir. Bu veri türleri “Char, Varchar, NChar, NVarchar, Text, NText, XML, Varbinary, Image ” yer alır.
Full-Text index oluşturulabilmesi için ilgili tabloda tek kolonlu bir unique bir index bulunması zorunlu bir koşuldur. Bu kısım biraz daha detaylandıralım, örnek olarak mevcut ortamda bulunan tablonuzda “Id” ve “Tarih” kolonları ile partition yapınız var bu yapıda mevcut partition index unique durumda bulunuyor ama yine de full-text index oluşturamıyorsunuz çünkü tek kolonlu bir unique index gerekmektedir. Benim test ettiğim yöntemlerden birisi de “Id” ve “Tarih” kolonları ile computed column oluşturarak bu kolona unique index oluşturmaya çalışmak oldu bu yöntem ile görece unique bir kolon oluşturarak bu kolona index oluşturulabilir ancak bu computed column oluştururken tablonuza oluşturacağı maliyeti ve transaction log file oluşacak büyümeyi de unutmamalısınız, bu konuda sizin de aklınıza gelecek farklı yöntemler olursa yorumlarda benimle paylaşırsanız sevinirim 🙂
Görsel – 1
Full-Text Search ’in Temel Bileşenleri
- Full-Text Index: Metin veya büyük verilerde arama yapmayı hedeflediğimiz belirli metin sütun veya sütunlara uygulanır. Full-Text Index ile normal bir indeksler kullanım alanları ve işlevleri bakımından birbirinden oldukça farklıdır, bu kısımda biraz daha detaya girerek karşılaştırmasını yapabiliriz;
- Normal indeksler, belirli sütunlarda bulunan veri erişimini hızlandırmak için kullanılır örneğin, TcKimlik kolonu veya Tarih kolonu gibiyken; Full-Text Indexler, büyük metin verilerin bulunduğu kolonlarda anahtar kelime veya kelime öbeği gibi metin tabanlı arama yapmak için kullanılır örneğin, açıklama kolonu içerisinde geçen bir kelimeyi aramak gibi.
- Normal indeksler, B-Tree yapısını kullanarak sütunda arama yaparken; Full-Text Index, ilgili kolonda bulunan metin kelimelere ayrılarak dizinlenir.
- Normal indeksler, basit ve belirli sütunlarda çok hızlı arama imkanı sağlarken; Full-Text Index, büyük metin ve veri kümelerinde hızlı sonuçlar verir.
- Full-Text Catalogs: Bir veya daha fazla full-text index içeren mantıksal kapsayıcıdır buna şu şekilde örnek verebiliriz, aynı veri tabanı altında bulunan Icerik tablosunda bulunan “aciklama” kolonu ile musteri tablosunda bulunan “adres” kolonuna full-text index attığımız senaryoda iki tablonun ortak bir catalog kullanabilir.
- Full-Text Queries: SQL Server ‘da full-text search yapmak için özel olarak kullanılan sorgulardır. Bu sorgular,
NOT: Full-text search işlemlerinde LIKE ile arama yaparken kullanılan ‘%’ operatörü yerine ‘*’ operatörünü kullanabilirsiniz.
- Contains:
SELECT * FROM Icerik WHERE CONTAINS (aciklama, 'elma') --aciklama kolonunda elma geçen tüm kayıtları getirir. SELECT * FROM Icerik WHERE CONTAINS (aciklama, '*ma') --aciklama kolonunda "ma" ile biten tüm kayıtları getirir. SELECT * FROM Icerik WHERE CONTAINS (aciklama, 'elma AND kırmızı') --aciklama kolonunda elma ve kırmızı geçen tüm kayıtları getirir.
- Containstable: Daha kesin ve kontrollü arama sonuçları ile daha az sayıda arama sonucu ile hassas arama sonuçları getirir.
SELECT * FROM Icerik WHERE CONTAINSTABLE (aciklama, 'elma') --aciklama kolonunda tam olarak "elma" geçen tüm kayıtları getirir. SELECT * FROM Icerik WHERE CONTAINSTABLE (aciklama, 'kırmızı elma') --aciklama kolonunda tam olarak "kırmızı elma" geçen tüm kayıtları getirir.
- Freetext: Esnek bir yapıda olup diğer full-text search arama yöntemlerine göre daha fazla sayıda sonuç döndürürken daha alakasız sonuçlarda getirebilir.
SELECT * FROM Icerik WHERE CONTAINS (aciklama, FREETEXT('kırmızı elma')) --aciklama kolonunda "kırmızı" ve "elma" geçen tüm kayıtları getirir.
Bu sorgu fonksiyonları kapsamında çok daha detayları var ancak ben daha fazla detaya girmeyeceğim.
Full-Text Search Avantajları
- Büyük metin verileri üzerinde ortama bağlı olarak yüksek hız ve performans sağlayabilir.
- Kelime, kelime grubu veya tüm cümle üzerinde arama yapabilme esneklik imkanı sunar.
- Freetext ile eş anlamlı ve yakınsama gibi gelişmiş arama özellikleri sunar.
Full-Text Search Dezavantajları
- Full-text indexler, ek depolama alanı gerektirir.
- Full-text indexler, oluşturulması ve güncellemesi zaman alabilir ve yönetimi (DBA) olarak daha karmaşık olabilir.
- Bazı dillerde bulunan karakter ve karakter setlerinde tam arama desteği sınırlı olabilir.
Full-Text Index Oluşturma Adımları
1) İlk adım kataloğumuzu oluşturuyoruz, Catalog ismi belirlendikten sonra owner belirliyoruz. Sensitive ya da Insensitive kendi ortam şartlarınıza göre karar vermelisiniz.
Görsel – 2
2) Şimdi stoplist oluşturacağız ama öncesinde açıklayalım, belirli kelimelerin full-text indexleme ve arama işlemleri sırasında göz ardı edilmesini sağlayan listemizdir. Stoplist genellikle çok sık kullanılan ancak arama sonuçlarında anlamlı bir bilgi taşımayan kelimelerdir; stoplist ismi belirlendikten sonra owner belirliyoruz.
Görsel – 3
Görsel – 4
Stoplist oluşturulduktan sonra kelime ekleme işlemini gerçekleştirebilirsiniz.
Görsel – 5
3) İlgili tabloya gelerek sağ tıklayarak Full-Text Index > Define Full-Text Index… ‘e tıklıyoruz.
Görsel – 6
Karşımıza gelen ekran next diyerek ilerliyoruz ve Görsel – 8 ‘de olduğu gibi unique bir index seçmemiz gereken ekran ile karşılaşıyoruz bu kısmı makalenin girişinde detaylı bir şekilde bahsetmiştim, oluşturmuş olduğumuz unique index seçerek next diyerek ilerliyoruz.
Görsel – 7
Görsel – 8
4) Şimdi karşımıza gelecek olan ekranda ilgili tablonun kolonlarından hangilerine full-text index oluşturmak istiyoruz bunları belirliyoruz. Bu kısımda tek bir kolon seçmek veya birden fazla kolon seçmek zorunluluğu gibi bir durum yok sizin ihtiyaçlarınız dahilinde seçebilirsiniz.
Görsel – 9
5) Şimdi karşımıza gelecek olan ekrandaki seçenekleri bir örnek ile açıklamak isterim, siz tablonuza verileri aktardınız ve sonrasında da full-text index oluşturdunuz sonrasında ilgili tabloya veriler gelmeye devam etti o zaman nasıl bir senaryo ile ilerleyelim diye soruyor yani 1. seçenek olan ben gelen verileri otomatik olarak mı aktarılacak ? Seçenek manuel yani kendin el ile mi yapacaksın ? 3. Seçenek yoksa artık gelecek olan verileri full-text index ‘e atmak istemiyor musun ? Bu işlemi ileri ki adımlarda da population olarak tekrar üzerinden geçeceğiz. Ben otomatik seçerek next diyerek ilerliyorum, sizde burada ortam ve ihtiyaçlarınıza yönelik tercihinizi yapabilirsiniz.
Görsel – 10
6) Karşımıza gelecek olan ekranda, Catalog seçimi yukarıda oluşturmuş olduğumuz “select full-text catalog“ kısmından seçiyoruz. “select full-text stoplist” kısmından yukarıda oluşturmuş olduğumuz stoplistimizi seçiyoruz. “select index filegroup” kısmı ise oluşturacağımız index ‘in konumunu belirliyoruz, ilgili veri tabanında indeksleriniz için kullandığınız farklı bir filegroup var ise indeksinizi oraya konumlandırabilirsiniz veya girişte bahsetmiş olduğum senaryolardan biri olan partition filegroup kısmına da konumlandırabilirsiniz.
Burada “Create a new catalog” seçeneği ile yeni bir catalog da oluşturabilirsiniz ancak biz zaten bir catalog oluşturduğumuz için buna gerek duymadık.
Görsel – 11
Görsel – 12
Finish diyerek full-text index kurulumunu tamamlamış oluyoruz.
7) Şimdi full-text index oluştu ancak süreç henüz bitmedi, İlgili veri tabanının altından Storage > Full Text Catalogs şeklinde ilerlediğinizde ilk aşamada catalog oluşturduğunuzda Görsel – 13 ‘teki gibi “Catalog Size”, “Item Count” (tablonuzdaki row count eşit olacaktır) ve “Unique Key Count” boş görünürken şimdi ilgili tabloda full-text index oluşturma adımlarını tamamlayıp cataloga bağladığınızda Görsel – 14 ‘teki gibi değerleriniz değişmeye başlayacaktır. Burada unutulmaması gereken şeylerden birisi de bu adım veri tabanınızın transaction log file boyutunu artıracak buna bağlı olarak transaction log backup boyutu da büyüyecektir.
Görsel – 13
Görsel – 14
Dikkat edilmesi gereken bir diğer konu ise full-text index oluşturmak istediğiniz tablonuzda population işlemi tamamlanmadan sorgulamalara geçerseniz hatalı veya eksik sonuçlar alabilirsiniz, bunu bir örnek ile açıklamak istiyorum; örnek veriyorum sizin full-text index oluşturmak istediğiniz tablonuzun row count 1000000 olsun ilk 6 adımda bahsettiğimiz şekilde full-text index oluşturma aşamalarını tamamladınız Storage > Full Text Catalogs şeklinde ilerleyerek ilgili catalogu kontrol ettiğinizde ise population işleminin henüz bitmediğini gördünüz ancak “Item Count” değerinin 300000 olduğunu gördünüz bu durumda full-text index referans aldığı unique indexin 300000 değerini catalogda population tamamlanmışken henüz geriye kalan veriler dahil değildir o yüzden sorgunuzda hatalı veya eksik sonuçlar alabilirsiniz.
Population işlemi tablonuzun boyutu, row count ve full-text index atılan kolonlarda bulunan verilere göre değişkenlik gösterecektir. Bu süreçte yukarda da bahsettiğim gibi transaction log file ve transaction log backup boyutunuzu göz önünde bulundurmanız gerekmektedir. Population işlemi tamamlandığında Görsel – 15 ‘te örnekteki gibi görüntüleyebilir ve sonuç olarak çıktısı hakkında bilgi verecektir.
Görsel – 15
Bu makalede Full-text index oluşturulması adımları ve örnekleri üzerinde durmaya çalıştım, bir sonraki makalede görüşünceye dek iyi ki varsınız, sevgiler 🙂
0 Yorum