MySQL'de MATCH AGAINST ile Full-Text Search

· Onur Bilgin · 4 dk okuma · MySQL , SQL , Full-Text Search , Veritabanı

Selamlar, bu yazımda MySQL tarafında LIKE '%kelime%' çekmekten yorulmuş arkadaşların hayatını kolaylaştıracak bir konuya, FULLTEXT indeksleri ve MATCH AGAINST ifadesine bakacağız. Konu görünürde basit; ama doğal dil modu, boolean modu ve Türkçe metinlerde ngram parser meselesi devreye girince işin rengi değişiyor.

Bir uygulamada arama kutusu açtığımızda ilk refleks WHERE body LIKE '%mysql%' yazmak oluyor. Bu sorgu küçük tablolarda iş görür, ama satır sayısı altı haneye çıkınca planner full table scan yapar ve cevap süreleri uçar. FULLTEXT indeksi ise tersine çevrilmiş bir indeks (inverted index) tutar; her kelimeyi, o kelimenin geçtiği satırlarla eşler.

FULLTEXT indeksi nedir?

FULLTEXT, MySQL'in metin sütunları (CHAR, VARCHAR, TEXT) için tuttuğu özel bir indeks tipi. InnoDB'de varsayılan destekleniyor. Mantığı şu: tabloya yazılan her metin parçalanıyor (tokenization), durdurma kelimeleri (stop words) atılıyor, kalan token'lar arama yapılabilir bir sözlüğe yazılıyor.

Hadi bir makale tablosu kuralım:

CREATE TABLE makaleler (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    baslik   VARCHAR(200),
    govde    TEXT,
    kategori VARCHAR(50),
    FULLTEXT idx_ft_makaleler (baslik, govde)
);

İki sütunu birden indeksledik; arama her ikisinde de geçerli. Mevcut bir tabloya sonradan eklemek için ALTER TABLE makaleler ADD FULLTEXT INDEX idx_ft_govde (govde); yeter.

Doğal dil modu

Varsayılan mod budur. Sorgu metni cümle gibi yorumlanır, sonuçlar otomatik olarak alaka skoruna (relevance) göre döner:

SELECT id, baslik,
       MATCH(baslik, govde) AGAINST('mysql indeksleme') AS skor
FROM makaleler
WHERE MATCH(baslik, govde) AGAINST('mysql indeksleme')
ORDER BY skor DESC;

Buradaki kritik nokta: MATCH ... AGAINST ifadesini hem WHERE hem SELECT listesinde aynı yazın. MySQL bunu görüp tek indeks taramasıyla iki ihtiyacı karşılıyor.

Boolean modu

Kullanıcıya arama kutusu sunuyorsanız, bence boolean modu daha kontrollü. Operatörlerle hangi kelimenin zorunlu, hangisinin yasak olduğunu söyleyebiliyorsunuz:

+kelime   zorunlu
-kelime   yasak
kelime*   prefix wildcard
"ifade"   tam eşleşen ifade

Örnek olarak 'MySQL geçsin ama NoSQL geçmesin' demek istersek:

SELECT baslik, kategori
FROM makaleler
WHERE MATCH(baslik, govde) AGAINST('+MySQL -NoSQL' IN BOOLEAN MODE);

Boolean modu skoru otomatik sıralamaz; ORDER BY'ı kendiniz vermeniz gerek. Ama esneklik için kullanıcı arayüzlü aramalarda tercihim hep bu yöndedir.

Query expansion

WITH QUERY EXPANSION, aramayı iki kez yapar: önce normal doğal dil sorgusu, sonra ilk sonuçların en alakalı kelimeleriyle ikinci bir sorgu. Böylece sorgudaki kelimeleri içermeyen ama anlamca yakın satırlar da gelir:

SELECT baslik
FROM makaleler
WHERE MATCH(baslik, govde) AGAINST('veritabani' WITH QUERY EXPANSION)
ORDER BY MATCH(baslik, govde) AGAINST('veritabani' WITH QUERY EXPANSION) DESC;

Açıkçası ben bu modu nadiren kullanıyorum; sonuçlar zaman zaman sapabiliyor. Ama içerik öneri akışlarında deneyebilirsiniz.

Türkçe ve CJK için ngram parser

Varsayılan parser kelimeleri boşluğa göre keser. Türkçede iş görür ama Çince/Japonca/Korece (CJK) gibi boşlukla ayrılmayan dillerde yetmez. MySQL bunun için ngram parser sunar:

CREATE TABLE urunler (
    id INT PRIMARY KEY,
    aciklama TEXT,
    FULLTEXT idx_ng (aciklama) WITH PARSER ngram
);

ngram_token_size varsayılan 2'dir. Türkçede de kısa kelimelerle (ev, iş, su) çalışıyorsanız pratik bir kaçış; ama indeks boyutu epey şişer, bunu göz önünde tutun.

Minimum kelime uzunluğu tuzağı

InnoDB için innodb_ft_min_token_size varsayılanı 3'tür. Yani SQL aranabilir ama IT aranamaz. Kontrol için:

SHOW VARIABLES LIKE 'innodb_ft_min_token_size';

Değiştirirseniz indeksleri yeniden kurmanız gerekir, sadece my.cnf'ye yazıp restart yetmez.

Sık karşılaşılan hatalar

  • MATCH'ı sadece WHERE'e koymak: Skoru SELECT'te göstermezseniz MySQL ikinci tarama yapabilir. İkisinde de aynı ifadeyi kullanın.
  • Kısa Türkçe kelimelerle ngram'sız test: 2-3 harfli aramaların boş döndüğünü görüp şaşırırsınız. Önce min_token_size'a bakın.
  • Stop words'ü unutmak: the, is gibi kelimeler indeksten atılır. Türkçe için dahili liste yok, kendi listenizi tanımlayabilirsiniz.
  • Büyük tabloda indeks oluştururken kilitlenmek: ALTER TABLE ... ADD FULLTEXT online DDL desteği sınırlıdır; pencerede yapın.

Ne zaman Elasticsearch'e geçmek lazım?

Şahsi kanaatim: birkaç milyon satıra kadar basit kelime eşleşmesi yetiyorsa MySQL FULLTEXT yeter. Ama şunlar varsa ayrı bir arama motoru (Elasticsearch, Meilisearch, Typesense) gerekir:

  • Çok dilli, gerçekten dilbilimsel analiz isteyen aramalar (stemming, eş anlamlı, fuzzy)
  • Faceted search, filtreli aggregations
  • Yazım hatasına toleranslı arama
  • Saniyede yüzlerce arama isteği

Bu noktadan sonra asıl veriyi MySQL'de tutup arama indeksini ayrı bir motora replicate etmek daha sağlıklı olur.

Kapanış

Bu yazıda FULLTEXT indeksinin temel mantığına, üç arama moduna ve ngram parser'a baktık. Bana sorarsanız küçük ve orta ölçekli projelerde FULLTEXT, ek servis kurmadan ihtiyacın çoğunu karşılar; arama kalitesine ciddi yatırım gereken noktada zaten Elasticsearch'e geçmek mecburi olur. Umarım faydalı olur, bir sonraki yazıda görüşmek üzere.