MariaDB ve MySQL’de MATCH AGAINST ile Full-Text Sorgu Yazma

Tarafındankomut
0 Yorum

Veri tabanlarında metin araması yaparken çoğu sysadmin’in ilk içgüdüsü LIKE '%kelime%' kullanmak olur. Bu yaklaşım küçük tablolar için işe yarasa da, milyonlarca kayıt içeren bir sistemde tam anlamıyla felakete davet çıkarmak demektir. MySQL ve MariaDB’nin sunduğu Full-Text Search özelliği ve MATCH ... AGAINST söz dizimi, bu sorunu çözmek için tasarlanmış güçlü bir araçtır. Bugün bu özelliği hem teorik hem de pratik açıdan, gerçek dünya senaryolarıyla birlikte ele alacağız.

Full-Text Search Nedir ve Neden Kullanmalısınız?

LIKE '%kelime%' kullandığınızda veritabanı motoru tüm tabloyu baştan sona tarar. Buna full table scan denir ve index kullanılmaz. 10 milyon satırlık bir makaleler tablosunda bu sorguyu çalıştırdığınızda sunucunuzun neden inlediğini anlarsınız.

Full-Text Search ise metinleri önceden indeksler, kelimeleri tokenize eder ve özel bir index yapısı oluşturur. Arama sırasında bu index kullanıldığı için performans farkı dramatik olabilir. Özellikle içerik yoğun uygulamalarda şu avantajları sunar:

  • Kelime kökü eşleştirmesi: “çalışıyor”, “çalışma”, “çalıştı” gibi türevleri yakalayabilirsiniz
  • Alaka skoru: Her sonuç için bir alaka puanı hesaplanır, buna göre sıralama yapabilirsiniz
  • Boolean operatörleri: Zorunlu kelimeler, hariç tutulan kelimeler ve benzeri mantıksal filtreler uygulayabilirsiniz
  • Phrase search: Tam cümle veya kelime öbeği araması yapabilirsiniz
  • Stopword desteği: “ve”, “ile”, “bir” gibi anlamsız kelimeleri otomatik filtreler

Full-Text Index Oluşturma

Her şeyden önce, MATCH ... AGAINST kullanabilmek için ilgili sütunlarda Full-Text index bulunması gerekir. Index olmadan bu sorgu çalışmaz.

Yeni tablo oluştururken index eklemek:

CREATE TABLE makaleler (
    id INT AUTO_INCREMENT PRIMARY KEY,
    baslik VARCHAR(255) NOT NULL,
    icerik TEXT NOT NULL,
    ozet TEXT,
    yazar VARCHAR(100),
    yayin_tarihi DATETIME,
    FULLTEXT INDEX ft_baslik_icerik (baslik, icerik),
    FULLTEXT INDEX ft_ozet (ozet)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Mevcut bir tabloya sonradan Full-Text index eklemek:

-- Tek sütuna index ekle
ALTER TABLE makaleler ADD FULLTEXT INDEX ft_baslik (baslik);

-- Birden fazla sütuna birleşik index ekle
ALTER TABLE makaleler ADD FULLTEXT INDEX ft_kombine (baslik, icerik, ozet);

-- CREATE INDEX söz dizimi ile de yapılabilir
CREATE FULLTEXT INDEX ft_icerik ON makaleler(icerik);

Önemli not: InnoDB tabloları için Full-Text search MySQL 5.6+ ve MariaDB 10.0+ sürümlerinden itibaren desteklenmektedir. Daha eski sürümlerde yalnızca MyISAM tablolarında çalışırdı. Bugün InnoDB kullanıyorsanız sorun yok.

MATCH AGAINST Temel Söz Dizimi

Temel kullanım şu şekildedir:

SELECT sütunlar
FROM tablo
WHERE MATCH(sütun1, sütun2) AGAINST('aranacak metin' [MOD]);

MATCH() içindeki sütun listesi, tam olarak Full-Text index tanımıyla eşleşmek zorundadır. Index (baslik, icerik) olarak tanımlandıysa MATCH içinde de aynı sırayla aynı sütunlar belirtilmelidir.

Basit bir arama örneği:

-- Makaleler arasında "veritabanı" geçen sonuçları bul
SELECT id, baslik, yayin_tarihi
FROM makaleler
WHERE MATCH(baslik, icerik) AGAINST('veritabanı');

-- Alaka skoru ile birlikte getir
SELECT id, baslik,
       MATCH(baslik, icerik) AGAINST('veritabanı') AS skor
FROM makaleler
WHERE MATCH(baslik, icerik) AGAINST('veritabanı')
ORDER BY skor DESC
LIMIT 20;

İkinci örnekte dikkat edilmesi gereken şey: MATCH ... AGAINST ifadesi hem SELECT listesinde hem de WHERE koşulunda kullanılmış olsa da, MySQL bu hesaplamayı bir kez yapar. Yani performans açısından endişelenmenize gerek yok.

Arama Modları

Full-Text search üç farklı modda çalışır. Doğru modu seçmek hem doğruluk hem de performans için kritiktir.

Natural Language Mode (Varsayılan)

Herhangi bir mod belirtmezseniz veya IN NATURAL LANGUAGE MODE yazarsanız bu mod aktif olur. Metin doğal dil kurallarına göre analiz edilir, her sonuca alaka skoru atanır.

-- Natural Language Mode (varsayılan)
SELECT id, baslik,
       MATCH(baslik, icerik) AGAINST('linux sunucu performans') AS alaka_skoru
FROM makaleler
WHERE MATCH(baslik, icerik) AGAINST('linux sunucu performans' IN NATURAL LANGUAGE MODE)
ORDER BY alaka_skoru DESC
LIMIT 10;

Bu modda dikkat edilmesi gereken bazı noktalar:

  • Tablodaki toplam satır sayısının yüzde 50’sinden fazlasında geçen kelimeler otomatik olarak stopword sayılır ve görmezden gelinir. Küçük tablolarda test yaparken bu davranışla karşılaşabilirsiniz.
  • Minimum kelime uzunluğu varsayılan olarak 4 karakterdir (ft_min_word_len parametresi). “php”, “api”, “sql” gibi kısa kelimeleri aramak istiyorsanız bu ayarı değiştirmeniz gerekir.
  • Sonuçlar alaka skoruna göre doğal olarak sıralanabilir, bu da arama motorlarına benzer bir deneyim sunar.

Boolean Mode

En esnek ve güçlü moddur. Özel operatörler kullanarak arama kriterlerini çok daha hassas şekilde tanımlayabilirsiniz.

-- Boolean Mode örnekleri
SELECT id, baslik
FROM makaleler
WHERE MATCH(baslik, icerik) AGAINST('+linux +sunucu -windows' IN BOOLEAN MODE);

Boolean Mode operatörleri:

  • +kelime: Bu kelime sonuçta MUTLAKA bulunmalıdır
  • -kelime: Bu kelime sonuçta KESINLIKLE bulunmamalıdır
  • kelime: Bu kelime olursa alaka skoru artar ama zorunlu değildir
  • “kelime öbeği”: Tam olarak bu sırayla geçmeli (phrase search)
  • kelime*: Wildcard, “kelime” ile başlayan tüm türevleri kapsar
  • ~kelime: Bu kelime negatif alaka etkisi yapar (varsa skoru düşürür)
  • (grup): Kelime grupları oluşturmak için parantez kullanılabilir

Gerçek dünya senaryosu: Bir blog platformunda kullanıcıların arama yapacağı bir sistem kuralım:

-- "mysql" içermeli, "oracle" içermemeli, "performans" varsa bonus puan
SELECT
    id,
    baslik,
    LEFT(icerik, 200) AS on_izleme,
    MATCH(baslik, icerik) AGAINST('+mysql -oracle performans' IN BOOLEAN MODE) AS skor
FROM makaleler
WHERE MATCH(baslik, icerik) AGAINST('+mysql -oracle performans' IN BOOLEAN MODE)
ORDER BY skor DESC
LIMIT 15;

-- Wildcard ile "veri" ile başlayan tüm kelimeleri ara (veritabanı, veriyi, verileri...)
SELECT id, baslik
FROM makaleler
WHERE MATCH(baslik, icerik) AGAINST('veri*' IN BOOLEAN MODE);

-- Tam cümle araması
SELECT id, baslik
FROM makaleler
WHERE MATCH(baslik, icerik) AGAINST('"yüksek erişilebilirlik"' IN BOOLEAN MODE);

Query Expansion Mode

Bu mod iki aşamalı çalışır. Önce normal arama yapar, dönen sonuçlardaki ilgili kelimeleri analiz eder, sonra bu kelimelerle arama tekrarlar. Kullanıcının tam olarak ne aradığını bilmediği durumlarda daha kapsamlı sonuçlar sunar.

-- Query Expansion ile genişletilmiş arama
SELECT id, baslik,
       MATCH(baslik, icerik) AGAINST('replikasyon' WITH QUERY EXPANSION) AS skor
FROM makaleler
WHERE MATCH(baslik, icerik) AGAINST('replikasyon' WITH QUERY EXPANSION)
ORDER BY skor DESC
LIMIT 20;

Bu modun dezavantajı: Bazen alakasız sonuçlar da gelebilir çünkü ikinci aşamada bulunan kelimeler her zaman orijinal aramayla doğrudan ilgili olmayabilir. Dikkatli kullanın.

Gerçek Dünya Senaryosu: E-Ticaret Ürün Arama

Diyelim ki bir e-ticaret platformu yönetiyorsunuz ve ürün arama sistemini optimize etmeniz gerekiyor. Tablonuz şöyle:

CREATE TABLE urunler (
    id INT AUTO_INCREMENT PRIMARY KEY,
    urun_adi VARCHAR(255) NOT NULL,
    aciklama TEXT,
    etiketler VARCHAR(500),
    kategori VARCHAR(100),
    fiyat DECIMAL(10,2),
    stok INT DEFAULT 0,
    aktif TINYINT(1) DEFAULT 1,
    FULLTEXT INDEX ft_arama (urun_adi, aciklama, etiketler)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Kullanıcı arama sorgusu:

-- Kullanıcı "kablosuz mouse" arıyor, aktif ve stokta olan ürünleri listele
SELECT
    id,
    urun_adi,
    fiyat,
    stok,
    MATCH(urun_adi, aciklama, etiketler)
        AGAINST('+kablosuz +mouse' IN BOOLEAN MODE) AS alaka_skoru
FROM urunler
WHERE
    aktif = 1
    AND stok > 0
    AND MATCH(urun_adi, aciklama, etiketler)
        AGAINST('+kablosuz +mouse' IN BOOLEAN MODE)
ORDER BY alaka_skoru DESC, fiyat ASC
LIMIT 20 OFFSET 0;

-- Aynı sorguyu sayfalama için FOUND_ROWS() ile birlikte kullan
SELECT SQL_CALC_FOUND_ROWS
    id, urun_adi, fiyat,
    MATCH(urun_adi, aciklama, etiketler)
        AGAINST('"kablosuz mouse"' IN BOOLEAN MODE) AS skor
FROM urunler
WHERE
    aktif = 1
    AND MATCH(urun_adi, aciklama, etiketler)
        AGAINST('"kablosuz mouse"' IN BOOLEAN MODE)
ORDER BY skor DESC
LIMIT 20;

SELECT FOUND_ROWS() AS toplam_sonuc;

Minimum Kelime Uzunluğu Ayarı

Daha önce bahsettiğim gibi, varsayılan minimum kelime uzunluğu 4 karakterdir. Eğer sisteminizde “php”, “sql”, “api”, “css” gibi 3 karakterli teknik terimler sık aranıyorsa bu ayarı değiştirmeniz gerekir.

-- Mevcut ayarı kontrol et
SHOW VARIABLES LIKE 'ft_min_word_len';        -- MyISAM için
SHOW VARIABLES LIKE 'innodb_ft_min_token_size'; -- InnoDB için

-- my.cnf veya my.ini dosyasına ekle
[mysqld]
innodb_ft_min_token_size = 2
ft_min_word_len = 2

Ayarı değiştirdikten sonra MySQL/MariaDB’yi yeniden başlatıp Full-Text indexlerini yeniden oluşturmanız şarttır:

-- Servisi yeniden başlat
systemctl restart mysql
# veya
systemctl restart mariadb

-- Index'leri yeniden oluştur
REPAIR TABLE makaleler QUICK;
-- veya ALTER TABLE ile de yeniden oluşturabilirsiniz
ALTER TABLE makaleler DROP INDEX ft_kombine;
ALTER TABLE makaleler ADD FULLTEXT INDEX ft_kombine (baslik, icerik, ozet);

Stopword Listesini Özelleştirme

MySQL ve MariaDB’nin varsayılan stopword listesi İngilizce kelimelere göre ayarlanmıştır. Türkçe içerik için bu liste pek işlevsel değildir. Kendi stopword listenizi oluşturabilirsiniz:

-- Önce bir stopword dosyası oluşturun: /etc/mysql/ft_stopwords.txt
-- İçeriği:
-- bir
-- ve
-- ile
-- için
-- bu
-- da
-- de
-- mi

-- my.cnf dosyasına ekleyin:
[mysqld]
ft_stopword_file = /etc/mysql/ft_stopwords.txt

-- InnoDB için stopword tablosu oluşturun:
CREATE TABLE innodb_stopwords (value VARCHAR(30)) ENGINE=InnoDB;
INSERT INTO innodb_stopwords VALUES ('bir'),('ve'),('ile'),('için'),('bu');

-- my.cnf'e ekleyin:
[mysqld]
innodb_ft_user_stopword_table = veritabani_adi/innodb_stopwords

EXPLAIN ile Sorgu Analizi

Full-Text sorgularınızın gerçekten index kullanıp kullanmadığını doğrulamak için EXPLAIN kullanın:

EXPLAIN SELECT id, baslik
FROM makaleler
WHERE MATCH(baslik, icerik) AGAINST('+linux +performans' IN BOOLEAN MODE)G

-- Beklenen çıktıda şunlara dikkat edin:
-- type: fulltext (index kullanılıyor demek)
-- key: ft_baslik_icerik (hangi index kullanıldığı)
-- rows: yaklaşık taranacak satır sayısı

Eğer type kolonunda ALL görüyorsanız bir şeyler yanlış gidiyordur. Ya index yok, ya sütun adları uyuşmuyor ya da arama ifadesi çok kısa.

Performans İpuçları ve Dikkat Edilmesi Gerekenler

Gerçek sistemlerde karşılaşılan yaygın sorunlar ve çözümleri:

  • Büyük tablolarda index boyutu: Full-Text indexler metin miktarına göre büyük yer kaplayabilir. Düzenli olarak OPTIMIZE TABLE makaleler; çalıştırmak index parçalanmasını azaltır.
  • Yüksek yazma yükü altında performans: InnoDB Full-Text indexleri, yazma işlemlerini geciktirmemek için değişiklikleri önce bir ara tabloya (FTS_INDEX_TABLE) yazar, sonra birleştirir. Yoğun yazma sistemlerinde bu davranışı anlamak önemlidir.
  • MATCH olmadan SELECT’te kullanım: Full-Text sorgusunu WHERE olmadan yalnızca skor almak için de kullanabilirsiniz, ancak bu durumda index kullanılmaz ve tüm tablo taranır. Her zaman WHERE koşulunda da kullanın.
  • Charset tutarsızlığı: Tablo utf8mb4 ama sorgularınız farklı charset ile geliyorsa sonuçlar yanlış olabilir. Bağlantı charset’inizi kontrol edin: SET NAMES utf8mb4;

Gerçek Dünya Senaryosu: Destek Talebi Arama Sistemi

Bir IT destek sistemi yönetiyorsunuz. Teknisyenler benzer sorunları bulmak için geçmiş talepleri arıyor:

-- Çözülmüş biletler arasında benzer sorunları ara
SELECT
    t.id,
    t.konu,
    LEFT(t.aciklama, 300) AS sorun_ozeti,
    t.cozum,
    t.teknisyen,
    t.kapanis_tarihi,
    MATCH(t.konu, t.aciklama, t.cozum)
        AGAINST('+printer +"kağıt sıkışması"' IN BOOLEAN MODE) AS skor
FROM destek_talepleri t
WHERE
    t.durum = 'kapali'
    AND t.kapanis_tarihi >= DATE_SUB(NOW(), INTERVAL 1 YEAR)
    AND MATCH(t.konu, t.aciklama, t.cozum)
        AGAINST('+printer +"kağıt sıkışması"' IN BOOLEAN MODE)
ORDER BY skor DESC, t.kapanis_tarihi DESC
LIMIT 10;

Bu sorgu hem Boolean Mode hem de phrase search’ü birleştiriyor, aynı zamanda tarih ve durum filtresiyle birlikte çalışıyor. Bu kombinasyon gerçek sistemlerde oldukça güçlüdür.

InnoDB Full-Text ile Özel Arama Tablosu

Büyük sistemlerde bazen ana tablodan ayrı bir arama tablosu oluşturmak daha mantıklıdır:

-- Sadece aranabilir içeriği tutan lean bir tablo
CREATE TABLE arama_indeksi (
    kaynak_id INT NOT NULL,
    kaynak_turu ENUM('makale', 'urun', 'yorum') NOT NULL,
    baslik VARCHAR(255),
    icerik MEDIUMTEXT,
    guncelleme_tarihi TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (kaynak_id, kaynak_turu),
    FULLTEXT INDEX ft_genel (baslik, icerik)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Arama yap ve kaynak türüne göre JOIN ile detayları getir
SELECT
    ai.kaynak_id,
    ai.kaynak_turu,
    ai.baslik,
    MATCH(ai.baslik, ai.icerik) AGAINST('$arama_terimi' IN BOOLEAN MODE) AS skor
FROM arama_indeksi ai
WHERE MATCH(ai.baslik, ai.icerik) AGAINST('$arama_terimi' IN BOOLEAN MODE)
ORDER BY skor DESC
LIMIT 25;

Sonuç

MATCH ... AGAINST ile Full-Text arama, LIKE '%metin%' ile karşılaştırıldığında hem performans hem de işlevsellik açısından bambaşka bir kategoridir. Özellikle metin ağırlıklı uygulamalarda, içerik yönetim sistemlerinde, e-ticaret platformlarında ve destek sistemlerinde bu özelliği kullanmak sorgularınızı kökten değiştirebilir.

Özetlemek gerekirse:

  • Küçük, basit aramalar için Natural Language Mode yeterlidir
  • Karmaşık, filtrelenmiş aramalar için Boolean Mode tercih edin
  • Kullanıcı niyetini genişletmek istiyorsanız Query Expansion deneyin
  • Kısa teknik terimler arıyorsanız innodb_ft_min_token_size ayarını mutlaka düşürün
  • EXPLAIN ile index kullanımını doğrulayın, tahminlere güvenmeyin
  • Türkçe içerik için stopword listesini özelleştirin

Full-Text search, Elasticsearch veya Solr gibi özel çözümlerin yerini her zaman tutmaz. Ama MySQL ve MariaDB üzerinde zaten çalışan bir uygulamanız varsa, ayrı bir servis kurup yönetmek yerine bu yerleşik özelliği doğru şekilde kullanmak çoğu zaman hem daha pratik hem de yeterince güçlüdür.

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir