SQLite Full-Text Search: FTS5 ve MATCH Kullanımı

Q: SQLite'ta FTS5 nedir?

FTS5, SQLite'ın yerleşik tam metin arama eklentisidir. CREATE VIRTUAL TABLE ... USING fts5(...) ile özel bir sanal tablo oluşturup MATCH operatörü ile sorgularsın. Veriyi eklerken metni token'lara ayırır, ters indeks (inverted index) tutar ve sonuçları varsayılan olarak BM25 ile sıralar.

Q: MATCH ile LIKE arasındaki fark nedir?

LIKE satır satır gezerek substring araması yapar ve kelime sınırlarını umursamaz. MATCH ise FTS5'in ters indeksini kullandığı için büyük tablolarda çok hızlıdır; üstelik token'ları, prefix sorgularını (term*), boolean operatörleri (AND, OR, NOT) ve cümle aramasını ("tam ifade") anlar. MATCH yalnızca FTS sanal tablolarında çalışır.

Q: SQLite'ta tam metin arama sonuçlarını nasıl sıralarım?

FTS5'in gizli bir rank kolonu vardır ve BM25 skorunu döner (küçük olan daha iyidir). Doğrudan ORDER BY rank yazabilirsin. Skoru açıkça istiyorsan bm25(table) çağrısını kullan; başlığı gövdeden daha ağırlıklı yapmak için bm25(posts, 10.0, 1.0) gibi kolon ağırlıkları da geçebilirsin.

`LIKE` Ölçeklenmiyor

SQLite'da daha önce metin araması yaptıysanız muhtemelen LIKE '%kelime%' kullanmışsınızdır. Küçük tablolarda iş görür, büyüklerde ise çöker. İşin içine girebilecek hiçbir index yoktur — SQLite her satırı tek tek taramak, küçük harfe çevirmek ve içinde aranan parçayı kontrol etmek zorundadır. Kelime sınırları, sıralama, çok kelimeli sorgular ve önek eşleşmesi gibi her şeyi de elinizle çözmeniz gerekir.

İşte tam burada SQLite'ın yerleşik cevabı olan FTS5 devreye giriyor. FTS5, metin sütunlarınız üzerinde ters indeks (inverted index) tutan, küçük bir sorgu dili anlayan ve sonuçları BM25 ile sıralayan bir virtual table türüdür. SQLite ile birlikte hazır gelir — kurulacak ek bir eklenti yok.

FTS5 Virtual Table Oluşturma

FTS5 tablosunu CREATE VIRTUAL TABLE ... USING fts5(...) söz dizimiyle, indekslemek istediğiniz metin sütunlarını listeleyerek oluşturursunuz:

CREATE VIRTUAL TABLE posts USING fts5(title, body);

INSERT INTO posts (title, body) VALUES
  ('SQLite tam metin araması', 'FTS5 hızlıdır ve dahili olarak gelir.'),
  ('SQLite''te indeksler', 'B-ağaçları SQLite''teki çoğu aramayı destekler.'),
  ('Neden SQLite kullanmalı', 'Süreç içinde çalışır ve verileri tek bir dosyada saklar.');

SELECT title FROM posts WHERE posts MATCH 'sqlite';

Dikkat etmeniz gereken üç nokta var. Sütunların tipi yok — FTS5 her şeyi metin olarak ele alır. MATCH operatörü bir sütuna değil, tablo adına karşı çalışır (posts MATCH ...). Ve sorgu büyük/küçük harfe duyarsız olup tokenize edildiği için 'sqlite' ifadesi satırlardaki SQLite kelimesini de bulur.

`MATCH` Sorgu Dili

MATCH tek bir kelimeyle sınırlı değildir. Sorgu metninin kendine ait küçük bir grameri vardır:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Tam metin araması', 'FTS5 ifade ve önek sorgularını destekler.'),
  ('İndeksler', 'B-ağacı indeksi eşitlik aramalarını hızlandırır.'),
  ('Tetikleyiciler', 'FTS''i kaynak tabloyla senkronize tutmak için tetikleyicileri kullanın.');

SELECT title FROM posts WHERE posts MATCH 'fts5 AND prefix';
SELECT title FROM posts WHERE posts MATCH '"keep fts"';
SELECT title FROM posts WHERE posts MATCH 'trig*';
SELECT title FROM posts WHERE posts MATCH 'index NOT trigger';

Her birinin işi şu:

'fts5 AND prefix' — her iki kelime de bulunmalı (sıra fark etmez, satırın herhangi bir yerinde).
'"keep fts"' — birebir bu sırayla geçen ifade.
'trig*' — önek aramasıdır; trigger, triggers, trigonometry gibi sonuçları yakalar.
'index NOT trigger' — index geçen ama trigger geçmeyen kayıtlar.

Tek bir sütunda arama yapmak isterseniz column:term kalıbını kullanabilirsiniz, örneğin 'title:sqlite'. Dilbilgisinin tamamı; gruplama için parantezleri ve alternatifler için OR operatörünü de kapsıyor — yani tipik bir arama motorundan beklediğiniz yapının aynısı.

BM25 ile sqlite sıralama

FTS5 varsayılan olarak her satıra gizli bir rank sütunu ekler. Bu sütun BM25 alaka skorunu tutar — değer ne kadar küçükse eşleşme o kadar isabetlidir. En alakalı sonuçları üste almak için bu sütuna göre sıralamanız yeterli:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite eğitimi', 'SQLite''i sıfırdan öğrenin.'),
  ('SQLite ve Postgres', 'Küçük uygulamalar için SQLite ile Postgres karşılaştırması.'),
  ('SQLite SQLite SQLite', 'SQLite''i defalarca anan bir gönderi.');

SELECT title, rank
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY rank;

Bazı sütunlara diğerlerinden daha fazla ağırlık vermek ister misin? bm25() fonksiyonunu, sütun ağırlıklarını tanım sırasına göre vererek çağırmanız yeterli:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('SQLite dizinleri', 'B-ağaçları hakkında kısa bir not.'),
  ('Postgres notları', 'Karşılaştırma için sqlite hakkında bir paragraf içerir.');

SELECT title, bm25(posts, 10.0, 1.0) AS score
FROM posts
WHERE posts MATCH 'sqlite'
ORDER BY score;

İlk gönderi kazanır çünkü sqlite kelimesi sadece body (1× ağırlıklı) yerine title alanında (10× ağırlıklı) geçiyor. Ağırlıkları uygulamanızın nasıl sıralama yapmasını istediğinize göre seçin.

FTS5 indeksini veriyle senkron tutmak

En basit FTS5 tablosu, metnin kendi kopyasını içinde tutar. Sadece veri eklediğiniz log tarzı senaryolar için bu yeterli; ama çoğu uygulamanın zaten asıl bir tablosu vardır ve FTS'in bu tabloyu takip etmesini ister. Burada temiz çözüm, external content (dış içerikli) bir FTS tablosu ve buna eşlik eden üç trigger kullanmaktır.

CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT,
    body TEXT
);

CREATE VIRTUAL TABLE articles_fts USING fts5(
    title, body,
    content='articles',
    content_rowid='id'
);

CREATE TRIGGER articles_ai AFTER INSERT ON articles BEGIN
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

CREATE TRIGGER articles_ad AFTER DELETE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
END;

CREATE TRIGGER articles_au AFTER UPDATE ON articles BEGIN
    INSERT INTO articles_fts(articles_fts, rowid, title, body)
    VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO articles_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;

INSERT INTO articles (title, body) VALUES
  ('SQLite tetikleyicileri', 'AFTER INSERT tetikleyicileri nasıl kullanılır.'),
  ('FTS5 desenleri', 'Harici içerik, veriyi tek bir yerde tutar.');

SELECT a.id, a.title
FROM articles a
JOIN articles_fts f ON f.rowid = a.id
WHERE articles_fts MATCH 'triggers';

content='articles' ifadesi FTS5'e metni kendi içinde tutmamasını söyler — gerektiğinde gidip articles tablosundan okur. Trigger'lar ise yapılan yazma işlemlerini FTS dizinine yansıtır. Böylece articles asıl kaynak olur, articles_fts da onun yanında duran salt arama yapısına dönüşür.

İlk bakışta tuhaf duran o INSERT INTO articles_fts(articles_fts, ...) VALUES ('delete', ...) satırı aslında FTS5'in komut sözdizimidir; dizine "şu satırı sil" demenin yoludur.

Snippet ve vurgulama ile sonuç önizlemesi

Arama sonuçlarında genellikle eşleşen kelimelerin öne çıkarıldığı kısa bir önizleme isteriz. FTS5 bunun için iki hazır fonksiyon sunar:

CREATE VIRTUAL TABLE posts USING fts5(title, body);
INSERT INTO posts (title, body) VALUES
  ('Başlarken', 'SQLite, verileri diskte tek bir dosyada saklayan küçük, hızlı, gömülü bir veritabanı motorudur.');

SELECT
    highlight(posts, 0, '<b>', '</b>') AS title,
    snippet(posts, 1, '<mark>', '</mark>', '...', 10) AS preview
FROM posts
WHERE posts MATCH 'sqlite veritabanı';

highlight(tablo, sütun_indeksi, aç, kapat): ilgili sütunun tam metnini döndürür ve eşleşen tokenları sarar.
snippet(tablo, sütun_indeksi, aç, kapat, üç_nokta, token_sayısı): eşleşmenin etrafında kısa bir alıntı döndürür.

Sütun indeksleri sıfırdan başlar ve tanımlama sırasına göredir. Her arama arayüzünde gördüğümüz "eşleşen kelimeler sarı renkte" davranışının temel yapı taşları bunlar.

Dikkat Edilmesi Gereken Tuzaklar

İnsanların sıkça takıldığı birkaç nokta var:

MATCH yalnızca FTS tablolarında çalışır. Normal bir sütunda MATCH kullanamazsınız. Mevcut bir tablo üzerinde arama yapmanız gerekiyorsa, yukarıda anlatılan external-content (harici içerik) yaklaşımını tercih edin.
rank'e göre sıralamayı unutmayın. Bunu yapmazsanız FTS5 satırları depolama sırasına göre döndürür ve bunun alaka düzeyiyle hiçbir alakası yoktur.
Tokenizer seçimi önemlidir. Varsayılan tokenizer (unicode61) Unicode kelime sınırlarına göre böler ve küçük harfe çevirir. Kök bulma (yani run'ın running ile eşleşmesi) için porter tokenizer'ını kullanın: USING fts5(body, tokenize='porter').
FTS5 bir yazım hatası toleranslı motor değildir. Önek (prefix) eşleştirmesi yapar, bulanık (fuzzy) eşleştirme yapmaz. "Bunu mu demek istediniz?" gibi bir davranış istiyorsanız, bunu FTS5'in üzerine kendiniz inşa etmelisiniz.
İçeriksiz tablolar (content='') daha küçüktür ama kayıplıdır. Arama yapabilirsiniz fakat orijinal metni geri alamazsınız — yalnızca rowid'e ulaşabilirsiniz. Metni başka bir yerde sakladığınız durumlarda işe yarar.

Sırada: Pencere Fonksiyonları (Window Functions)

FTS5, metin arama tarafını hallediyor. Bir sonraki sayfada farklı türden bir gelişmiş sorgu yapısını ele alacağız: pencere fonksiyonları. Bunlar, satırlarınızı agregasyonla tek satıra düşürmeden çalışan toplamlar, sıralamalar ve grup bazlı analitikler hesaplamanızı sağlar.

Sıkça Sorulan Sorular

SQLite'ta FTS5 nedir?

FTS5, SQLite'ın yerleşik tam metin arama eklentisidir. CREATE VIRTUAL TABLE ... USING fts5(...) ile özel bir sanal tablo oluşturup MATCH operatörü ile sorgularsın. Veriyi eklerken metni token'lara ayırır, ters indeks (inverted index) tutar ve sonuçları varsayılan olarak BM25 ile sıralar.

MATCH ile LIKE arasındaki fark nedir?

LIKE satır satır gezerek substring araması yapar ve kelime sınırlarını umursamaz. MATCH ise FTS5'in ters indeksini kullandığı için büyük tablolarda çok hızlıdır; üstelik token'ları, prefix sorgularını (term*), boolean operatörleri (AND, OR, NOT) ve cümle aramasını ("tam ifade") anlar. MATCH yalnızca FTS sanal tablolarında çalışır.

FTS5 indeksini gerçek tabloyla nasıl senkron tutarım?

İki yol var: ya gerçek tablonu işaret eden contentless ya da external-content bir FTS5 tablosu kullanırsın, ya da AFTER INSERT, AFTER UPDATE ve AFTER DELETE trigger'ları yazıp değişiklikleri FTS tablosuna yansıtırsın. External-content yaklaşımı (content='posts') metni iki kez saklamamanı sağlar.

SQLite'ta tam metin arama sonuçlarını nasıl sıralarım?

FTS5'in gizli bir rank kolonu vardır ve BM25 skorunu döner (küçük olan daha iyidir). Doğrudan ORDER BY rank yazabilirsin. Skoru açıkça istiyorsan bm25(table) çağrısını kullan; başlığı gövdeden daha ağırlıklı yapmak için bm25(posts, 10.0, 1.0) gibi kolon ağırlıkları da geçebilirsin.

İlgili kavramlar