MySQL Full-Text Search ile Metin Arama Optimizasyonu

Veritabanınızda milyonlarca kayıt var ve kullanıcılar bir şeyler arıyor. LIKE ‘%kelime%’ sorgusu yazıyorsunuz, sonuç geliyor ama sunucu neredeyse ağlıyor. Tanıdık geldi mi? MySQL’in Full-Text Search özelliği tam bu noktada devreye giriyor ve hayatınızı ciddi ölçüde kolaylaştırıyor. Bu yazıda MySQL full-text search’ü sıfırdan yapılandırmayı, optimize etmeyi ve production ortamında nasıl kullanacağınızı ele alacağız.

LIKE ile Full-Text Search Arasındaki Fark

Önce neden LIKE sorguları kötü performans gösteriyor, bunu anlayalım. WHERE content LIKE '%mysql%' yazdığınızda MySQL tablodaki her satırı baştan sona taramak zorunda kalır. İndeks kullanamaz çünkü baştaki % işareti indeksin işe yaramasını engeller. 10 milyon satırlık bir tabloda bu işlem dakikalar alabilir.

Full-Text Search ise tamamen farklı bir yaklaşım kullanır. Metinleri önceden tokenize eder, kelimeleri bir inverted index’e kaydeder ve arama sırasında bu indeksi kullanır. Aynı 10 milyon satırlık tabloda milisaniyeler içinde sonuç döner.

# LIKE sorgusunun ne kadar sürdüğünü test edelim
mysql -u root -p test_db -e "
SELECT SQL_NO_CACHE COUNT(*) 
FROM articles 
WHERE content LIKE '%veritabani%';
" 

# EXPLAIN ile tarama türünü görelim
mysql -u root -p test_db -e "
EXPLAIN SELECT * FROM articles WHERE content LIKE '%veritabani%';
"
# type: ALL göreceksiniz - tam tablo taraması

Full-Text Index Oluşturma

Yeni Tabloda Index Tanımlamak

En temiz yol tabloyu oluştururken full-text index’i de tanımlamak:

mysql -u root -p << 'EOF'
USE blog_db;

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    summary TEXT,
    author VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_title_content (title, content),
    FULLTEXT INDEX ft_summary (summary)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

EOF

Burada dikkat edilmesi gereken noktar: InnoDB ve MyISAM motorlarının ikisi de full-text search’ü destekliyor ancak InnoDB MySQL 5.6’dan itibaren bu desteği kazandı. Production’da zaten InnoDB kullanıyorsunuzdur, sorun yok.

Mevcut Tabloya Index Eklemek

Büyük ihtimalle mevcut tablolarınıza index eklemeniz gerekecek:

mysql -u root -p blog_db << 'EOF'
-- Önce mevcut durumu kontrol edelim
SHOW INDEX FROM articles;

-- Full-text index ekle
ALTER TABLE articles ADD FULLTEXT INDEX ft_search (title, content);

-- Büyük tablolarda bu işlem uzun sürebilir
-- İşlemin ilerlemesini takip etmek için:
SELECT * FROM information_schema.INNODB_SESSION_TEMP_TABLESPACES;

EOF

Büyük tablolarda ALTER TABLE işlemi ciddi zaman alabilir. 50 milyon satırlık bir tabloda saatler sürebilir. Bu durumu yönetmek için pt-online-schema-change aracını kullanabilirsiniz ama bunu ilerleyen bölümde ele alacağız.

MATCH…AGAINST Sözdizimi

Full-text search’ün temel sözdizimi MATCH(kolonlar) AGAINST(arama_terimi) şeklinde:

mysql -u root -p blog_db << 'EOF'
-- Basit natural language search
SELECT id, title, 
       MATCH(title, content) AGAINST('mysql veritabani') AS relevance_score
FROM articles
WHERE MATCH(title, content) AGAINST('mysql veritabani')
ORDER BY relevance_score DESC
LIMIT 10;

-- Boolean mode - daha fazla kontrol
SELECT id, title,
       MATCH(title, content) AGAINST('+mysql +optimizasyon -yedekleme' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('+mysql +optimizasyon -yedekleme' IN BOOLEAN MODE)
ORDER BY score DESC;

-- Query expansion - benzer terimleri de ara
SELECT id, title
FROM articles
WHERE MATCH(title, content) AGAINST('performans' WITH QUERY EXPANSION)
LIMIT 20;

EOF

Natural Language Mode: MySQL belge frekansına göre otomatik alaka puanı hesaplar. Çok yaygın kelimeler düşük puan alır.

Boolean Mode: Arama operatörleri kullanabilirsiniz:

+kelime: Bu kelime mutlaka olmalı
-kelime: Bu kelime kesinlikle olmamalı
kelime*: Wildcard, kelimenin başıyla eşleşir
“tam ifade”: Tam bu ifade aranır
~kelime: Negatif katkı, puanı düşürür

Query Expansion: İlk sonuçlardaki kelimeleri de aramaya katar, daha geniş sonuç seti döner.

MySQL Full-Text Konfigürasyonu

my.cnf Ayarları

Full-text search performansını doğrudan etkileyen parametreler var:

# /etc/mysql/mysql.conf.d/mysqld.cnf dosyasını düzenleyelim
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Eklenecek parametreler:

[mysqld]
# Minimum kelime uzunluğu (varsayılan: 4 InnoDB için, 4 MyISAM için)
innodb_ft_min_token_size = 2

# Maksimum kelime uzunluğu
innodb_ft_max_token_size = 84

# Stopword listesini devre dışı bırak veya özelleştir
innodb_ft_enable_stopword = ON
innodb_ft_server_stopword_table = 'blog_db/custom_stopwords'

# Full-text cache boyutu - büyük index işlemleri için artır
innodb_ft_cache_size = 8000000
innodb_ft_total_cache_size = 640000000

# Boolean mode maksimum kelime sayısı
ft_max_word_len = 84

# MyISAM için minimum token boyutu
ft_min_word_len = 2

Değişikliği uygulamak için:

sudo systemctl restart mysql

# Restart sonrası full-text index'leri yeniden oluşturmak gerekebilir
mysql -u root -p blog_db -e "REPAIR TABLE articles QUICK;"
# veya InnoDB için:
mysql -u root -p blog_db -e "OPTIMIZE TABLE articles;"

Stopword Listesini Özelleştirme

Türkçe içerik için varsayılan İngilizce stopword listesi işe yaramaz. Kendi listenizi oluşturun:

mysql -u root -p << 'EOF'
USE blog_db;

-- Özel stopword tablosu oluştur
CREATE TABLE custom_stopwords (
    value VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Türkçe yaygın stopword'leri ekle
INSERT INTO custom_stopwords VALUES
('bir'), ('bu'), ('da'), ('de'), ('den'), ('dir'),
('e'), ('en'), ('i'), ('ile'), ('in'), ('ise'),
('icin'), ('ki'), ('mi'), ('mu'), ('mu'), ('ne'),
('o'), ('su'), ('ta'), ('te'), ('ve'), ('ya'),
('ya'), ('ye'), ('yi'), ('yu'), ('yla'), ('yle');

EOF

Sonra my.cnf’ye innodb_ft_server_stopword_table = 'blog_db/custom_stopwords' ekleyip MySQL’i yeniden başlatın.

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

Bir e-ticaret sitesi düşünün. Ürün adı, açıklaması ve kategorisi üzerinde arama yapmanız gerekiyor. Kullanıcılar “siyah deri çanta” yazdığında hem tam eşleşmeleri hem de yakın sonuçları görmeliler.

mysql -u root -p ecommerce_db << 'EOF'
-- Ürün tablosu
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    brand VARCHAR(100),
    tags TEXT,
    price DECIMAL(10,2),
    stock INT DEFAULT 0,
    FULLTEXT INDEX ft_product_search (name, description, tags),
    FULLTEXT INDEX ft_name_only (name),
    INDEX idx_category (category),
    INDEX idx_price (price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Arama fonksiyonu - hem relevance hem de stock durumunu göz önünde bulundur
SELECT 
    p.id,
    p.name,
    p.price,
    p.stock,
    MATCH(p.name, p.description, p.tags) AGAINST('siyah deri canta' IN BOOLEAN MODE) AS relevance,
    -- İsim eşleşmesine daha fazla ağırlık ver
    MATCH(p.name) AGAINST('siyah deri canta' IN BOOLEAN MODE) * 2 AS name_boost
FROM products p
WHERE 
    p.stock > 0
    AND MATCH(p.name, p.description, p.tags) AGAINST('siyah deri canta' IN BOOLEAN MODE)
ORDER BY (relevance + name_boost) DESC, p.price ASC
LIMIT 20;

EOF

Bu sorguda hem relevance score hesaplıyoruz hem de isme eşleşmeye ekstra ağırlık veriyoruz. Bu yaklaşım basit ama etkili bir boosting stratejisi.

İndex Yönetimi ve Bakım

Index Durumunu İzleme

mysql -u root -p << 'EOF'
-- Full-text index bilgilerini görüntüle
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE LIMIT 20;

-- Index'teki kelime sayısını kontrol et
SELECT COUNT(*) FROM information_schema.INNODB_FT_INDEX_TABLE;

-- Optimize edilmesi gereken tabloları bul
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    DATA_FREE,
    DATA_LENGTH,
    INDEX_LENGTH
FROM information_schema.TABLES 
WHERE ENGINE = 'InnoDB' 
AND DATA_FREE > 1000000
ORDER BY DATA_FREE DESC;

EOF

Periyodik Bakım Script’i

Production’da düzenli çalıştırmanız gereken bakım script’i:

#!/bin/bash
# /opt/scripts/fulltext_maintenance.sh

DB_HOST="localhost"
DB_USER="admin"
DB_PASS="guclu_sifre_buraya"
DB_NAME="blog_db"
LOG_FILE="/var/log/mysql/fulltext_maintenance.log"

log() {
    echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}

log "Full-text maintenance basliyor..."

# Tabloları optimize et - fragmantasyonu gider
mysql -h "$DB_HOST" -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" << 'SQLEOF'
-- InnoDB full-text index'i yeniden oluştur
SET GLOBAL innodb_optimize_fulltext_only = ON;
OPTIMIZE TABLE articles;
OPTIMIZE TABLE products;
SET GLOBAL innodb_optimize_fulltext_only = OFF;

-- Silinmiş kayıtları index'ten temizle
-- InnoDB bunu otomatik yapar ama manuel tetiklemek hızlandırır
FLUSH TABLES articles, products;
SQLEOF

if [ $? -eq 0 ]; then
    log "Maintenance tamamlandi."
else
    log "HATA: Maintenance sirasinda sorun olustu!"
    exit 1
fi

# Log rotasyonu - 30 günden eski logları sil
find /var/log/mysql/ -name "*.log" -mtime +30 -delete

log "Script tamamlandi."

# Script'i çalıştırılabilir yap ve cron'a ekle
chmod +x /opt/scripts/fulltext_maintenance.sh

# Her pazar sabahı 3'te çalışsın
echo "0 3 * * 0 /opt/scripts/fulltext_maintenance.sh" | crontab -

Büyük Tablolarda Zero-Downtime Index Ekleme

Production’da çalışan büyük bir tabloya full-text index eklemek riskli. Percona Toolkit’in pt-online-schema-change aracı burada kurtarıcı:

# Percona Toolkit kurulumu (Ubuntu/Debian)
sudo apt-get install percona-toolkit

# pt-online-schema-change ile güvenli index ekleme
pt-online-schema-change 
    --host=localhost 
    --user=admin 
    --password=sifre 
    --database=blog_db 
    --table=articles 
    --alter="ADD FULLTEXT INDEX ft_content (title, content)" 
    --chunk-size=1000 
    --max-load="Threads_running=50" 
    --critical-load="Threads_running=100" 
    --check-interval=5 
    --progress=time,30 
    --execute

# İşlem sırasında tablo kilitleri oluşmuyor
# Replikasyon gecikmesini izliyor ve gerekirse yavaşlıyor

Bu araç tabloyu kopyalar, değişikliği kopya üzerinde uygular, asıl tabloyu otomatik olarak senkronize eder ve son adımda atomik bir swap yapar. Tablo kilidi saniyenin altında kalır.

Performans Karşılaştırması ve Monitoring

Query Cache ve Full-Text

MySQL 8.0 ile query cache tamamen kaldırıldı. Uygulama tarafında Redis veya Memcached kullanarak full-text arama sonuçlarını cache’lemek ciddi performans farkı yaratır:

# Full-text query performansını ölç
mysql -u root -p blog_db << 'EOF'
-- Profiling'i aktif et
SET PROFILING = 1;

-- Test sorgusu
SELECT id, title, 
       MATCH(title, content) AGAINST('+php +mysql +performans' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('+php +mysql +performans' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 10;

-- Profil detaylarını görüntüle
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;

-- EXPLAIN ile sorgu planını incele
EXPLAIN SELECT id, title, 
       MATCH(title, content) AGAINST('+php +mysql +performans' IN BOOLEAN MODE) AS score
FROM articles
WHERE MATCH(title, content) AGAINST('+php +mysql +performans' IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 10;

SET PROFILING = 0;
EOF

EXPLAIN çıktısında type: fulltext görüyorsanız full-text index kullanılıyor demektir. Extra kolonunda Using where varsa ek filtreleme yapılıyor.

Slow Query Log ile Full-Text Sorunlarını Tespit Etme

# my.cnf ayarları
sudo tee -a /etc/mysql/mysql.conf.d/mysqld.cnf << 'EOF'
# Slow query log
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow_queries.log
long_query_time = 2
log_queries_not_using_indexes = ON
EOF

sudo systemctl reload mysql

# Slow query logunu analiz et
sudo mysqldumpslow -s t -t 20 /var/log/mysql/slow_queries.log

# Percona'nın pt-query-digest aracıyla daha detaylı analiz
sudo pt-query-digest /var/log/mysql/slow_queries.log 
    --filter '$event->{arg} =~ m/MATCH|AGAINST/i' 
    --limit 10

Sık Karşılaşılan Sorunlar ve Çözümleri

Kısa kelimeler aranmıyor: innodb_ft_min_token_size değerinden kısa kelimeler indekslenmez. “PHP”, “SQL”, “JS” gibi 3 karakter ve altındaki terimler için bu değeri 2’ye indirin ve tabloyu yeniden optimize edin.

Türkçe karakterler sorun çıkarıyor: Tablo ve kolon charset’inin utf8mb4 olduğundan emin olun. latin1 veya eski utf8 ile Türkçe karakter sorunları yaşarsınız.

Stopword’ler arama sonuçlarını etkiliyor: AGAINST('bir sey' IN BOOLEAN MODE) yazdığınızda “bir” stopword listesinde varsa ignore edilir. Kendi stopword tablonuzu oluşturup MySQL’e tanıtın.

%50’den fazla belgede geçen kelimeler sonuç döndürmüyor: Natural language mode’da çok yaygın kelimeler otomatik olarak elenir. Boolean mode kullanın.

# Stopword listesini kontrol et
mysql -u root -p -e "
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;
"

# Bir kelimenin stopword olup olmadığını test et
mysql -u root -p blog_db -e "
SELECT MATCH(title) AGAINST('ve') FROM articles LIMIT 1;
"
# 0 dönüyorsa stopword olarak işaretlenmiş

MariaDB’de Full-Text Search Farklılıkları

MariaDB MySQL ile büyük ölçüde uyumlu ama bazı farklar var. MariaDB 10.0.5’ten itibaren Mroonga storage engine ile Japonca ve diğer diller için gelişmiş full-text support geliyor. Ayrıca MariaDB’nin ft_min_word_len parametresi yapılandırma değişikliği sonrası yeniden başlatma gerektirmeden SET GLOBAL ile değiştirilebiliyor:

# MariaDB'ye özgü full-text ayarı
mysql -u root -p -e "
SET GLOBAL innodb_ft_min_token_size = 2;
SHOW VARIABLES LIKE 'innodb_ft_%';
"

# MariaDB'de index optimize etme
mysql -u root -p blog_db -e "
OPTIMIZE TABLE articles;
"

Sonuç

Full-text search, LIKE sorgularına kıyasla büyük veri setlerinde onlarca kat daha iyi performans sunuyor. Doğru yapılandırıldığında ve düzenli bakımı yapıldığında milyonlarca kayıt üzerinde alt saniye yanıt süreleri elde etmek mümkün.

Özetle yapmanız gerekenler: innodb_ft_min_token_size değerini içeriğinize göre ayarlayın, Türkçe içerik için özel stopword listesi oluşturun, büyük tablolara index eklerken pt-online-schema-change kullanın, slow query log ile sorunlu sorguları izleyin ve tabloları düzenli olarak optimize edin.

En önemlisi, full-text search’ü production’a almadan önce gerçekçi veri setleriyle test edin. Her arama senaryosu farklı, bazı durumlarda Elasticsearch veya Solr gibi özelleşmiş çözümler daha uygun olabilir. Ama eğer zaten MySQL altyapısı üzerindeyseniz ve ek bir sistem kurmaktan kaçınmak istiyorsanız, MySQL full-text search ihtiyacınızın büyük bölümünü karşılayacaktır.

Full-Text Search: MySQL’de Metin Arama Optimizasyonu