MariaDB ve MySQL’de Full-Text Search ile Metin Arama

Tarafındankomut
0 Yorum

Veritabanında milyonlarca kayıt arasında klasik LIKE '%arama%' sorgusuyla metin aramaya çalışmak, bir zamanlar hepimizin başvurduğu ama artık acı çekerek hatırladığımız bir yöntemdir. Tablo büyüdükçe sorgular yavaşlar, index kullanılamaz, sunucu CPU’su patlar ve kullanıcılar “neden bu kadar yavaş?” diye sormaya başlar. İşte tam bu noktada MariaDB ve MySQL’in Full-Text Search özelliği devreye girer.

Full-Text Search, metin içerikli sütunlarda anlam tabanlı, hızlı ve güçlü arama yapmanı sağlar. Sadece karakter eşleşmesi değil, kelime kökü analizi, alaka puanlaması ve doğal dil sorguları gibi yetenekleri de sunar. Bu yazıda teoriden pratiğe her şeyi ele alacağız.

Full-Text Search Nedir ve Nasıl Çalışır?

Full-Text Search, bir metni kelime kelime indeksleyen ve bu indeks üzerinden arama yapan bir teknolojisidir. Klasik B-Tree indekslerin aksine, FTS indeksi her kelimenin hangi kayıtta geçtiğini ve kaç kez tekrar ettiğini tutar. Bu sayede hem hız hem de alaka skorlaması mümkün olur.

MariaDB ve MySQL’de iki depolama motoru Full-Text Search destekler:

  • MyISAM: Eski motor, FTS desteği erken sürümlerden beri var ama transaction desteği yok
  • InnoDB: MySQL 5.6+ ve MariaDB 10.0.5+ sürümlerinden itibaren FTS desteği eklendi, production için bu motoru kullan

InnoDB ile FTS kullanırken şunu bilmek lazım: indeks arka planda asenkron olarak oluşturulur. Büyük tablolarda indeks güncellemesi biraz gecikebilir ama bu genellikle sorun değildir.

Test Ortamını Kuralım

Gerçek dünya senaryosu olarak bir blog platformu veritabanı oluşturalım. Makaleler, yorumlar ve ürün açıklamaları içeren tablolar kullanacağız.

-- Veritabanı ve tablo oluşturma
CREATE DATABASE blog_db CHARACTER SET utf8mb4 COLLATE utf8mb4_turkish_ci;
USE blog_db;

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    content TEXT NOT NULL,
    author VARCHAR(100),
    category VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_idx (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Örnek veri ekleyelim
INSERT INTO articles (title, content, author, category) VALUES
('Linux Sunucu Güvenliği', 'Linux sunucularda güvenlik duvarı kurulumu ve SSH sertleştirme işlemleri oldukça önemlidir. Fail2ban kurarak brute force saldırılarına karşı koruma sağlayabilirsiniz.', 'Ahmet Yılmaz', 'Güvenlik'),
('MySQL Performans İpuçları', 'Veritabanı performansını artırmak için index optimizasyonu ve sorgu analizi yapılmalıdır. EXPLAIN komutu sorgu planını gösterir.', 'Mehmet Kaya', 'Veritabanı'),
('Docker ile Konteyner Yönetimi', 'Docker konteynerler izole ortamlar sağlar. docker-compose ile çoklu servis yönetimi oldukça kolaylaşır.', 'Ayşe Demir', 'Konteyner'),
('Nginx Load Balancing Kurulumu', 'Nginx ile yük dengeleme yapılandırması upstream blokları kullanılarak gerçekleştirilir. Round-robin ve least-conn algoritmaları mevcuttur.', 'Ali Çelik', 'Web Sunucu'),
('Backup Stratejileri ve Felaket Kurtarma', 'Düzenli yedekleme stratejisi işletmenin sürekliliği için kritik önem taşır. rsync ve mysqldump araçlarıyla otomatik yedekleme yapılandırılabilir.', 'Fatma Şahin', 'Yedekleme');

Mevcut Tabloya Full-Text Index Ekleme

Zaten var olan bir tabloya sonradan FTS indeksi eklemek de gayet basit:

-- Mevcut tabloya FTS index ekle
ALTER TABLE articles ADD FULLTEXT INDEX ft_title_idx (title);

-- Ya da birden fazla sütunu kapsayan indeks
ALTER TABLE articles ADD FULLTEXT INDEX ft_full_idx (title, content, author);

-- Index oluşturma durumunu kontrol et
SHOW INDEX FROM articles;

-- Oluşturulan FTS indekslerini listele
SELECT
    TABLE_NAME,
    INDEX_NAME,
    COLUMN_NAME,
    INDEX_TYPE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'blog_db'
AND INDEX_TYPE = 'FULLTEXT';

Temel MATCH…AGAINST Sözdizimi

Full-Text Search için MATCH()...AGAINST() sözdizimini kullanıyoruz. LIKE operatörüne göre hem daha hızlı hem de çok daha esnek.

-- En basit FTS sorgusu
SELECT id, title, author
FROM articles
WHERE MATCH(title, content) AGAINST('güvenlik');

-- Alaka skoru ile birlikte çekme
SELECT
    id,
    title,
    author,
    MATCH(title, content) AGAINST('güvenlik') AS relevance_score
FROM articles
WHERE MATCH(title, content) AGAINST('güvenlik')
ORDER BY relevance_score DESC;

-- Birden fazla kelime ile arama
SELECT
    id,
    title,
    MATCH(title, content) AGAINST('linux sunucu güvenlik') AS score
FROM articles
WHERE MATCH(title, content) AGAINST('linux sunucu güvenlik')
ORDER BY score DESC
LIMIT 10;

Burada dikkat etmeni istediğim şey şu: MATCH() içindeki sütun listesi, indeks tanımıyla birebir aynı olmalı. Yani indeksi (title, content) olarak tanımladıysan, sorguda da MATCH(title, content) yazmalısın. Sadece title yazarsan indeks kullanılmaz.

Üç Farklı Arama Modu

Full-Text Search’in üç arama modu var ve her birinin farklı kullanım senaryosu mevcut.

Natural Language Mode

Varsayılan moddur. Doğal dil gibi davranır, kelimelerin alaka skorunu hesaplar ve çok yaygın kelimeleri otomatik olarak filtreler.

-- Natural Language Mode (varsayılan)
SELECT
    id,
    title,
    MATCH(title, content) AGAINST('veritabanı performans' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('veritabanı performans' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

-- Toplam kaç sonuç var
SELECT COUNT(*) AS total_results
FROM articles
WHERE MATCH(title, content) AGAINST('linux konteyner' IN NATURAL LANGUAGE MODE);

Natural Language Mode’da önemli bir sınırlama var: tablonun yüzde ellisinden fazlasında geçen kelimeler otomatik olarak “stopword” sayılır ve arama sonuçlarına dahil edilmez. Küçük test tablolarında bu durumla karşılaşabilirsin. Bu yüzden geliştirme ortamında en az 20-30 kayıtla test yap.

Boolean Mode

Boolean Mode, arama ifadelerini çok daha hassas kontrol etmeni sağlar. Zorunlu kelimeler, hariç tutmalar, wildcard kullanımı gibi gelişmiş özellikler burada devreye girer.

-- Boolean Mode örnekleri
-- '+' işareti kelimeyi zorunlu yapar
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST('+linux +güvenlik' IN BOOLEAN MODE);

-- '-' işareti kelimeyi hariç tutar
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST('+linux -docker' IN BOOLEAN MODE);

-- '*' wildcard ile başlangıç eşleşmesi
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST('konteyner*' IN BOOLEAN MODE);

-- Çift tırnak ile tam ifade arama
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST('"yük dengeleme"' IN BOOLEAN MODE);

-- Karma kullanım: zorunlu, opsiyonel ve hariç
SELECT
    id,
    title,
    MATCH(title, content) AGAINST('+sunucu nginx -apache' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('+sunucu nginx -apache' IN BOOLEAN MODE)
ORDER BY score DESC;

Boolean Mode operatörlerini özetleyecek olursak:

  • +kelime: Bu kelime mutlaka bulunmalı
  • -kelime: Bu kelime kesinlikle bulunmamalı
  • kelime: Opsiyonel, varsa skoru artırır
  • ~kelime: Varsa skoru düşürür (negatif etki)
  • kelime*: Wildcard, “kelime” ile başlayan her şey
  • “tam ifade”: Tam kelime öbeği arama
  • (grup): Parantez ile gruplama

Query Expansion Mode

Bu mod iki aşamalı çalışır. Önce normal arama yapar, bulunan sonuçlardaki kelimeleri analiz eder, sonra bu kelimelerle genişletilmiş bir arama daha yapar. Kullanıcının tam olarak doğru kelimeyi yazamadığı durumlar için faydalıdır.

-- Query Expansion Mode
SELECT
    id,
    title,
    MATCH(title, content) AGAINST('yedekleme' WITH QUERY EXPANSION) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('yedekleme' WITH QUERY EXPANSION)
ORDER BY score DESC
LIMIT 20;

Query Expansion gürültülü sonuçlar üretebilir, yani alakasız sonuçlar da gelebilir. Üretim ortamında dikkatli kullan.

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

Şimdi daha gerçekçi bir senaryo üzerinden gidelim. Bir e-ticaret sitesinin ürün arama özelliğini FTS ile nasıl yapılandırabileceğimize bakalım.

-- E-ticaret ürün tablosu
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(500) NOT NULL,
    description TEXT,
    short_desc VARCHAR(1000),
    brand VARCHAR(100),
    tags VARCHAR(500),
    price DECIMAL(10,2),
    stock INT DEFAULT 0,
    is_active TINYINT DEFAULT 1,
    FULLTEXT INDEX ft_products (name, description, short_desc, tags)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_turkish_ci;

-- Kullanıcı araması simülasyonu
-- Alaka skoru + filtre kombinasyonu
SELECT
    p.id,
    p.name,
    p.brand,
    p.price,
    p.stock,
    MATCH(p.name, p.description, p.short_desc, p.tags)
        AGAINST('+kablosuz +kulaklık' IN BOOLEAN MODE) AS relevance
FROM products p
WHERE
    MATCH(p.name, p.description, p.short_desc, p.tags)
        AGAINST('+kablosuz +kulaklık' IN BOOLEAN MODE)
    AND p.is_active = 1
    AND p.stock > 0
    AND p.price BETWEEN 100 AND 2000
ORDER BY relevance DESC, p.price ASC
LIMIT 20 OFFSET 0;

-- Arama önerisi için: benzer ürünleri bul
SELECT
    id,
    name,
    price,
    MATCH(name, description, short_desc, tags)
        AGAINST('gaming mouse oyun fare' IN NATURAL LANGUAGE MODE) AS score
FROM products
WHERE
    MATCH(name, description, short_desc, tags)
        AGAINST('gaming mouse oyun fare' IN NATURAL LANGUAGE MODE)
    AND is_active = 1
ORDER BY score DESC
LIMIT 5;

Minimum Kelime Uzunluğu Ayarı

FTS varsayılan olarak 4 karakterden kısa kelimeleri indekslemiyor. Bu Türkçe için sorun yaratabilir çünkü “göz”, “baş”, “el” gibi kelimelerimiz var. Bu değeri değiştirmek için:

-- Mevcut ayarı kontrol et
SHOW VARIABLES LIKE 'innodb_ft_min_token_size';
SHOW VARIABLES LIKE 'ft_min_word_len';  -- MyISAM 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 sunucuyu yeniden başlat
-- Ardından indeksleri yeniden oluştur
ALTER TABLE articles DROP INDEX ft_idx;
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, content);

-- Ya da OPTIMIZE ile
OPTIMIZE TABLE articles;

-- Stopword listesini görüntüle
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

-- Özel stopword tablosu oluştur
CREATE TABLE custom_stopwords (value VARCHAR(30)) ENGINE=InnoDB;
INSERT INTO custom_stopwords VALUES ('ve'), ('ile'), ('bir'), ('bu'), ('da'), ('de');

-- Özel stopword tablosunu aktif et (my.cnf'ye ekle)
-- innodb_ft_server_stopword_table = 'blog_db/custom_stopwords'

FTS Performans İzleme ve Debug

Sorgunun gerçekten FTS indeksini kullanıp kullanmadığını kontrol etmek için:

-- EXPLAIN ile sorgu planını incele
EXPLAIN SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST('linux güvenlik' IN BOOLEAN MODE)G

-- FTS indeks durumu
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 20;

-- Pending indeks işlemleri
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;

-- Silinen kayıtların silinmesini bekleyen indeks girişleri
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;

-- FTS konfigürasyonunu görüntüle
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG;

-- FTS indeks istatistiklerini zorla güncelle
SET GLOBAL innodb_optimize_fulltext_only = ON;
OPTIMIZE TABLE articles;
SET GLOBAL innodb_optimize_fulltext_only = OFF;

Pagination ile Büyük Sonuç Kümeleri

Arama sonuçlarını sayfalama ile sunmak için performanslı bir yapı:

-- Önce toplam sonuç sayısını al
SELECT COUNT(*) AS total
FROM articles
WHERE MATCH(title, content) AGAINST('+linux' IN BOOLEAN MODE)
AND category IS NOT NULL;

-- Sonra ilgili sayfayı çek
SELECT
    id,
    title,
    author,
    category,
    LEFT(content, 200) AS excerpt,
    created_at,
    MATCH(title, content) AGAINST('+linux' IN BOOLEAN MODE) AS relevance
FROM articles
WHERE MATCH(title, content) AGAINST('+linux' IN BOOLEAN MODE)
AND category IS NOT NULL
ORDER BY relevance DESC, created_at DESC
LIMIT 10 OFFSET 20;  -- 3. sayfa, sayfa başına 10 sonuç

-- Arama ile birlikte highlighting (excerpt içinde bulunan kelimeyi işaretle)
-- Bu MySQL'de native desteklenmez, uygulama katmanında yapılır
-- Ama hangi kayıtların eşleştiğini öğrenmek için:
SELECT
    id,
    title,
    MATCH(title) AGAINST('+linux' IN BOOLEAN MODE) AS title_match,
    MATCH(content) AGAINST('+linux' IN BOOLEAN MODE) AS content_match
FROM articles
WHERE MATCH(title, content) AGAINST('+linux' IN BOOLEAN MODE)
ORDER BY (MATCH(title) AGAINST('+linux' IN BOOLEAN MODE) * 2 +
          MATCH(content) AGAINST('+linux' IN BOOLEAN MODE)) DESC;

Buradaki son sorguda bir trick uyguladım: başlıkta eşleşen sonuçlara içerikte eşleşenlere göre daha yüksek ağırlık verdim. Bu, arama sonuçlarının kalitesini artırmak için sıkça kullanılan bir yöntemdir.

InnoDB FTS Bakım Görevleri

Üretim ortamında FTS indekslerinin zamanla şişebildiğini ve performansın düşebildiğini göreceksin. Düzenli bakım şart:

-- InnoDB FTS cache boyutunu göster
SHOW VARIABLES LIKE 'innodb_ft_cache_size';
SHOW VARIABLES LIKE 'innodb_ft_total_cache_size';

-- Silinmiş kayıtları temizle (OPTIMIZE)
OPTIMIZE TABLE articles;

-- Büyük tablolarda sadece FTS kısmını optimize et
SET GLOBAL innodb_optimize_fulltext_only = ON;
OPTIMIZE TABLE products;
SET GLOBAL innodb_optimize_fulltext_only = OFF;

-- FTS indeksini tamamen yeniden oluştur (çok büyük değişiklikler sonrasında)
ALTER TABLE articles DROP INDEX ft_idx;
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, content);

-- Indeks boyutunu kontrol et
SELECT
    TABLE_NAME,
    INDEX_NAME,
    ROUND(SUM(stat_value * @@innodb_page_size) / 1024 / 1024, 2) AS index_size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'blog_db'
AND stat_name = 'size'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY index_size_mb DESC;

Yaygın Hatalar ve Çözümleri

Production’da en sık karşılaştığım sorunları paylaşayım:

“Can’t find FULLTEXT index” hatası: MATCH() içindeki sütun listesi indeks tanımıyla eşleşmiyor demektir. Sütun sırasının bile önemi olabilir.

Tüm aramalar sıfır sonuç dönüyor: Tabloda çok az kayıt var ve Natural Language Mode’un %50 kuralı devreye giriyor. Boolean Mode kullan veya test verisini artır.

Kısa kelimeler bulunamıyor: innodb_ft_min_token_size değeri çok büyük. Yukarıdaki ayar bölümüne bak.

Türkçe karakter sorunları: Tablo ve sütun charset’i utf8mb4 ve collation utf8mb4_turkish_ci olmalı. Bunun yanı sıra bağlantı charset’ini de kontrol et.

FTS indeksi LIKE kadar yavaş: EXPLAIN ile kontrol et. Eğer indeks kullanılmıyorsa MATCH() sütun listesi yanlıştır veya optimizer başka bir yol seçiyordur.

LIKE ile Karşılaştırma

FTS ne zaman tercih edilmeli, LIKE ne zaman yeterlidir?

  • FTS kullan: Uzun metin içerikli sütunlarda (article body, product description), çoklu kelime araması gerektiğinde, alaka sıralaması önemliyse, tablo büyükse (100k+ kayıt)
  • LIKE kullan: Prefix araması yapıyorsan (LIKE 'prefix%' index kullanabilir), kısa ve sabit formatlı alanlarda, tablonuz küçükse, pattern matching gerekiyorsa

Basit bir prefix araması için LIKE 'linux%' hala indeks kullanır ve FTS’den daha az overhead ile çalışır. FTS’yi her yere koymak gerekmez.

Sonuç

Full-Text Search, metin arama ihtiyaçlarında LIKE operatörünün sınırlarını aştığında en doğal çözümdür. Natural Language, Boolean ve Query Expansion modları sayesinde farklı kullanım senaryolarına uyum sağlar. Boolean Mode’un operatörleri, alaka skoru hesabı ve birden fazla sütunu kapsayan indeksler bir arada kullanıldığında gerçekten güçlü bir arama deneyimi ortaya çıkar.

Türkçe içerikler için utf8mb4_turkish_ci collation ve innodb_ft_min_token_size ayarına özellikle dikkat et. Üretim ortamında periyodik OPTIMIZE TABLE çalıştırmayı cron’a ekle ve indeks boyutlarını izlemeyi unutma.

Milyonlarca kayıt içeren bir tabloda LIKE '%kelime%' ile tam tarama yerine FTS indeksi kullanan bir sorgunun getirdiği performans farkını bir kez deneyimleyince bir daha geriye dönmek istemezsin.

Bir yanıt yazın

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