MySQL 5.6 ve Ubuntu 16.04 Üzerinde Full-Text Search (Tam Metin Arama) ile Veritabanı Aramalarını İyileştirme

MySQL 5.6 ve Ubuntu 16.04 Üzerinde Full-Text Search (Tam Metin Arama) ile Veritabanı Aramalarını İyileştirme

Günümüz web uygulamalarında, kullanıcıların aradıkları bilgiye hızlı ve doğru bir şekilde ulaşabilmesi kritik bir öneme sahiptir. Veritabanlarında saklanan büyük miktardaki metinsel veriler içinde arama yapmak, geleneksel yöntemlerle hem performans hem de doğruluk açısından ciddi zorluklar barındırır. Standart SQL sorgularında sıkça kullanılan “LIKE” operatörü, küçük veri kümelerinde iş görse de, veri hacmi büyüdükçe yetersiz kalır ve veritabanı sunucusunu aşırı yükler.

Bu makalede, Ubuntu 16.04 işletim sistemi üzerinde çalışan MySQL 5.6 veritabanı motorunda Full-Text Search (Tam Metin Arama) özelliğinin nasıl yapılandırılacağını, bu özelliğin geleneksel yöntemlere göre avantajlarını ve arama performansını optimize etmenin yollarını detaylı bir şekilde ele alacağız.

—

Geleneksel Arama Yöntemleri ve Sınırları

Veritabanı geliştiricilerinin metin tabanlı aramalarda ilk başvurduğu yöntem genellikle “LIKE” operatörüdür. Örneğin, bir makale tablosunda “veritabanı” kelimesini aramak için aşağıdaki gibi bir sorgu yazılır:

SELECT * FROM articles WHERE content LIKE '%veritabanı%';

Bu sorgu yapısı ilk bakışta basit ve kullanışlı görünse de, arka planda ciddi performans problemlerine yol açar.

1. Full Table Scan (Tam Tablo Taraması)

MySQL, “LIKE ‘%terim%'” ifadesini gördüğünde, aranan kelimenin başında ve sonunda joker karakter (%) olduğu için mevcut B-Tree indekslerini kullanamaz. Bu durum, veritabanının tablodaki tüm satırları tek tek okumasına (Full Table Scan) neden olur. Tablonuzda binlerce veya milyonlarca satır olduğunda, bu işlem disk I/O (girdi/çıktı) oranını zirveye çıkarır ve sorgu süresini kabul edilemez seviyelere uzatır.

2. Alaka Düzeyi (Relevancy) Eksikliği

Geleneksel “LIKE” aramaları, eşleşen sonuçları bir alaka düzeyine göre sıralayamaz. “Veritabanı” kelimesinin makale içinde 10 kez mi yoksa sadece 1 kez mi geçtiği, sorgu sonucu için farksızdır. Kullanıcılar ise her zaman kendileri için en alakalı sonuçları en üstte görmek isterler.

3. Dilbilgisi ve Esneklik Sorunları

“LIKE” operatörü tam eşleşme arar. Kelimenin çoğul halleri, eş anlamlıları veya yazım hataları gibi durumlarda esneklik sunmaz. Örneğin, “veritabanları” kelimesi “veritabanı” aramasıyla eşleşebilir ancak “veri” ve “taban” kelimeleri arasına başka bir kelime girdiğinde standart “LIKE” araması başarısız olur.

—

MySQL Full-Text Search (FTS) Nedir?

MySQL Full-Text Search (Tam Metin Arama), metin tabanlı kolonlarda arama işlemlerini optimize etmek için tasarlanmış özel bir indeksleme ve arama mekanizmasıdır. FTS, metin içerisindeki kelimeleri tek tek ayrıştırarak bir “Ters Dizin” (Inverted Index) oluşturur. Bu dizin, hangi kelimenin hangi satırda ve hangi konumda geçtiğini haritalandırır. Böylece arama işlemi yapıldığında, tüm tabloyu taramak yerine doğrudan bu indeks üzerinden ilgili satırlara milisaniyeler içinde ulaşılır.

MySQL 5.6 sürümü, Full-Text Search teknolojisi için önemli bir dönüm noktasıdır. Bu sürümden önce FTS yalnızca MyISAM depolama motoru (storage engine) tarafından destekleniyordu. MyISAM motorunun işlemsel bütünlük (ACID), yabancı anahtarlar (foreign keys) ve kilitlenme kurtarma (crash recovery) gibi kritik kurumsal özelliklerden yoksun olması, geliştiricileri zor bir seçim yapmaya zorluyordu. MySQL 5.6 ile birlikte, modern ve güvenilir olan InnoDB depolama motoru da Full-Text Search desteğine kavuşmuştur. Bu sayede hem veri güvenliğinden ödün verilmemiş hem de yüksek performanslı arama yetenekleri elde edilmiştir.

—

Ubuntu 16.04 Üzerinde MySQL 5.6 Kurulumu ve Kontrolü

Uygulamaya geçmeden önce, sisteminizde MySQL 5.6’nın kurulu olduğundan emin olmalısınız. Ubuntu 16.04 varsayılan depolarında genellikle MySQL 5.7 ile gelir, ancak birçok legacy (eski) sistem hala 5.6 sürümünü kullanmaktadır. Sürümünüzü kontrol etmek için terminalde şu komutu çalıştırabilirsiniz:

mysql --version

Alternatif olarak, MySQL istemcisine bağlanıp sürüm sorgulaması yapabilirsiniz:

SELECT VERSION();

Eğer sisteminiz hazırsa, Full-Text Search özelliklerini test etmek için örnek bir veritabanı ve tablo yapısı oluşturalım.

—

Uygulama: Örnek Veritabanı ve Tablo Tasarımı

FTS yeteneklerini test etmek amacıyla, teknik makalelerin saklandığı sanal bir blog veritabanı oluşturalım. Bu tabloda makalelerin başlığı ve içeriği yer alacaktır.

İlk olarak MySQL istemcisine bağlanın:

mysql -u root -p

Ardından aşağıdaki SQL komutları ile veritabanını ve tablomuzu oluşturalım. Tablonun InnoDB motorunu kullandığına dikkat edin:

CREATE DATABASE tech_blog DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE tech_blog;

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

FULLTEXT İndeksi Tanımlama

Arama yapacağımız title (başlık) ve body (içerik) kolonları üzerinde bir FULLTEXT indeksi oluşturmamız gerekir. Bu indeksi tabloyu oluştururken tanımlayabileceğimiz gibi, mevcut bir tabloya sonradan da ekleyebiliriz. Mevcut tablomuza indeksi eklemek için şu komutu çalıştırın:

ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title, body);

Bu komutla birlikte MySQL, hem title hem de body kolonlarını kapsayan ft_index adında bir tam metin indeksi oluşturur. Artık bu iki kolonda ortaklaşa veya ayrı ayrı hızlı aramalar gerçekleştirebiliriz.

Örnek Verilerin Yüklenmesi

Arama sonuçlarının doğruluğunu ve alaka düzeyini test edebilmek için tablomuza çeşitli içerikler ekleyelim:

INSERT INTO articles (title, body) VALUES
('MySQL ve Performans Optimizasyonu', 'MySQL veritabanlarında indeksleme, sorgu optimizasyonu ve bellek yönetimi performansı doğrudan etkiler. InnoDB motoru bu konuda oldukça başarılıdır.'),
('Ubuntu 16.04 Sunucu Kurulumu', 'Ubuntu 16.04 LTS sürümü, sunucu ortamlarında kararlılığı ile bilinir. Güvenlik duvarı ve SSH yapılandırması ilk yapılması gerekenler arasındadır.'),
('NoSQL vs SQL Veritabanları', 'İlişkisel veritabanları (SQL) ve doküman tabanlı veritabanları (NoSQL) arasındaki farklar projelerin mimarisini belirler. MySQL popüler bir SQL örneğidir.'),
('Full-Text Search Özellikleri', 'MySQL 5.6 ile birlikte InnoDB motoruna gelen Full-Text Search özelliği, arama sorgularını hızlandırır ve alaka düzeyine göre sıralama imkanı sunar.'),
('Linux Terminal Kılavuzu', 'Ubuntu ve diğer Debian tabanlı dağıtımlarda terminal komutları sistem yönetimini kolaylaştırır. Paket yöneticisi olarak apt kullanılır.');

—

MySQL Full-Text Search Arama Modları

MySQL, Full-Text Search sorguları için üç farklı arama modu sunar. Her modun kullanım senaryosu ve sunduğu esneklik farklıdır. Arama yapmak için MATCH() ve AGAINST() fonksiyonları kullanılır. MATCH(), indekslenen kolonları belirtirken, AGAINST() aranacak kelimeleri tanımlar.

1. Doğal Dil Araması (Natural Language Search)

Bu mod, MySQL’in varsayılan arama modudur. Kullanıcının arama kutusuna yazdığı ifadeyi günlük dilde yazılmış bir cümle gibi kabul eder. Herhangi bir özel operatör (+, – gibi) içermez. Kelimelerin sıklığına ve dökümanlardaki yaygınlığına göre bir alaka skoru (relevance score) hesaplar.

Örnek sorgu:

SELECT id, title, MATCH(title, body) AGAINST('MySQL' IN NATURAL LANGUAGE MODE) AS score 
FROM articles 
WHERE MATCH(title, body) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);

Bu sorgu, içinde “MySQL” kelimesi geçen satırları getirecek ve her satır için hesaplanan alaka skorunu score takma adıyla gösterecektir. Alaka skoru 0’dan büyük olan sonuçlar en yüksek skordan en düşüğe doğru otomatik olarak sıralanır.

2. Boolean Araması (Boolean Mode)

Boolean modu, arama sorgularında mantıksal operatörler kullanarak daha hassas aramalar yapılmasına olanak tanır. Kullanıcılar veya uygulama mantığı, belirli kelimelerin mutlaka bulunmasını, bazılarının ise kesinlikle bulunmamasını isteyebilir.

Boolean modunda kullanılan temel operatörler şunlardır:

* + (Artı): Kelimenin sonuçta mutlaka bulunması gerektiğini belirtir.
* - (Eksi): Kelimenin sonuçta kesinlikle bulunmaması gerektiğini belirtir.
(Yıldız): Kelimenin sonuna eklenerek joker karakter görevi görür (önek araması). Örneğin optimizasyon* ifadesi “optimizasyonu”, “optimizasyonlar” gibi kelimeleri bulur.
* "" (Çift Tırnak): Kelime grubunun tam yazıldığı sırayla aranmasını sağlar (öbek araması).

Örnek sorgularla bu operatörleri inceleyelim:

Sorgu A: İçinde mutlaka “MySQL” geçen ama “NoSQL” geçmeyen makaleleri bulalım:

SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('+MySQL -NoSQL' IN BOOLEAN MODE);

Sorgu B: İçinde “Ubuntu” ile başlayan kelimeler barındıran satırları bulalım:

SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('Ubuntu*' IN BOOLEAN MODE);

Sorgu C: “Performans Optimizasyonu” kelime grubunu tam olarak bu sırayla arayalım:

SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('"Performans Optimizasyonu"' IN BOOLEAN MODE);

3. Sorgu Genişletme (Query Expansion)

Kullanıcının arama terimi çok kısa olduğunda, MySQL arama sonuçlarını genişletmek için iki aşamalı bir arama yapar. İlk aşamada aranan kelimeyle eşleşen en alakalı satırları bulur. İkinci aşamada, bu satırlardaki diğer yaygın kelimeleri de arama kriterine ekleyerek sorguyu tekrarlar. Bu yöntem, kullanıcının tam olarak ne arayacağını bilmediği durumlarda “bunu da beğenebilirsiniz” tarzı sonuçlar üretmek için kullanışlıdır.

Örnek sorgu:

SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('optimizasyon' WITH QUERY EXPANSION);

—

MySQL FTS Yapılandırması ve İnce Ayarları

MySQL Full-Text Search özelliğinin verimli çalışabilmesi için sistem seviyesinde bazı yapılandırmaların yapılması gerekir. Bu ayarlar arama kalitesini ve indeks boyutunu doğrudan etkiler.

1. Minimum ve Maksimum Kelime Uzunluğu

MySQL, varsayılan olarak çok kısa kelimeleri indekse dahil etmez. InnoDB depolama motoru için bu sınır varsayılan olarak 3 karakterdir. Yani “db”, “ip”, “os” gibi 2 karakterli teknik terimler varsayılan ayarlarla indekslenmez ve aramalarda bulunamaz.

Bu sınırı değiştirmek için Ubuntu 16.04 üzerindeki MySQL yapılandırma dosyasını düzenlememiz gerekir. Terminalde şu komutla yapılandırma dosyasını açın:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld] bloğunun altına aşağıdaki satırı ekleyin (kelime sınırını 2 karakter yapmak için):

innodb_ft_min_token_size = 2

(Not: Eğer MyISAM motoru kullanıyorsanız, parametre ft_min_word_len = 2 şeklinde olmalıdır.)

Dosyayı kaydedip kapatın (Nano için CTRL+O, Enter, CTRL+X). Değişikliklerin etkinleşmesi için MySQL servisini yeniden başlatın:

sudo systemctl restart mysql

Önemli Not: Yapılandırma dosyasındaki bu tür değişiklikler mevcut indeksleri otomatik olarak güncellemez. Değişikliğin uygulanması için mevcut FULLTEXT indekslerinin silinip tekrar oluşturulması veya tablonun optimize edilmesi gerekir:

USE tech_blog;
ALTER TABLE articles DROP INDEX ft_index;
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title, body);

2. Durdurma Kelimeleri (Stopwords)

“ve”, “veya”, “bir”, “ile”, “the”, “a”, “an” gibi dilde çok sık geçen ama arama mantığı açısından ayırt edici özelliği olmayan kelimelere Stopwords (Durdurma Kelimeleri) denir. MySQL, indeks boyutunu küçültmek ve arama performansını artırmak için bu kelimeleri varsayılan olarak filtreler ve indekslemez.

Ancak Türkçe içerikli bir veritabanında varsayılan İngilizce stopword listesi yetersiz kalabilir veya tam tersine, teknik bir terim olan “it” (bilgi teknolojileri anlamında) İngilizce stopword listesinde olduğu için filtrelenebilir.

InnoDB için varsayılan stopword listesini devre dışı bırakmak veya kendi listenizi tanımlamak mümkündür. Varsayılan listeyi tamamen kapatmak için mysqld.cnf dosyasına şu satırı ekleyebilirsiniz:

innodb_ft_enable_stopword = OFF

Kendi özel stopword listenizi oluşturmak istiyorsanız, öncelikle bu kelimeleri içeren tek kolonlu bir tablo oluşturmalı ve ardından bu tabloyu MySQL’e bildirmelisiniz:

CREATE TABLE my_stopwords (value VARCHAR(30)) ENGINE = InnoDB;
INSERT INTO my_stopwords VALUES ('ve'), ('ile'), ('veya'), ('bir');

-- MySQL yapılandırmasına eklemek için:
-- SET GLOBAL innodb_ft_server_stopword_table = 'tech_blog/my_stopwords';

—

Performans Karşılaştırması: LIKE vs. Full-Text Search

Büyük ölçekli projelerde performans hayati önem taşır. FTS ile LIKE arasındaki performans farkını anlamak için teorik bir senaryo üzerinden gidelim.

100.000 satırdan oluşan ve her satırında ortalama 500 kelime barındıran bir makale tablomuz olduğunu varsayalım.

* LIKE Operatörü ile Arama: MySQL, her satırdaki 500 kelimeyi tek tek tarayarak aranan kelimeyi bulmaya çalışır. Bu işlem diskten sürekli okuma yapılmasına (I/O) ve yüksek CPU kullanımına yol açar. Eşzamanlı olarak onlarca kullanıcının bu aramayı yaptığını düşündüğümüzde, veritabanı yanıt veremez hale gelecektir. Ortalama sorgu süresi 1.5 ila 3 saniye arasında değişebilir.
* Full-Text Search ile Arama: MySQL, önceden hazırlanmış olan ters dizin (inverted index) tablosuna bakar. Aranan kelimenin hangi satır kimliklerinde (ID) olduğunu doğrudan tespit eder ve sadece o satırları diskten çağırır. Bu işlem neredeyse sıfır CPU maliyeti ile gerçekleşir. Ortalama sorgu süresi 0.01 ila 0.05 saniye (milisaniyeler) düzeyindedir.

Bu karşılaştırma, FTS kullanımının sadece bir tercih değil, ölçeklenebilir uygulamalar için bir zorunluluk olduğunu açıkça göstermektedir.

—

Gelişmiş Arama İhtiyaçları ve Alternatifler

MySQL Full-Text Search, orta ölçekli projeler, bloglar, e-ticaret ürün aramaları ve dahili yönetim panelleri için mükemmel ve maliyetsiz bir çözümdür. Ekstra bir sunucu veya servis kurulumu gerektirmediği için sistem yönetimini karmaşıklaştırmaz.

Ancak projeniz büyüdükçe ve arama senaryolarınız karmaşıklaştıkça MySQL FTS yetersiz kalabilir. Aşağıdaki durumlarda daha gelişmiş arama motorlarına geçiş yapmayı düşünmelisiniz:

* Morfolojik Analiz (Stemming): Türkçe gibi eklemeli dillerde kelime köklerini bulma (örneğin “kitapçı”, “kitaplar” kelimelerinin kökünün “kitap” olduğunu anlama) konusunda MySQL’in yerleşik desteği sınırlıdır.
* Çok Büyük Veri Setleri: Terabaytlarca veri üzerinde gerçek zamanlı aramalar ve log analizleri için MySQL performansı zorlanabilir.
* Gelişmiş Filtreleme ve Aggregation: Arama sonuçlarıyla birlikte dinamik kategorisel filtreler (facet arama) üretmek istiyorsanız özel çözümler gerekir.

Bu gibi durumlarda Elasticsearch, Apache Solr veya Sphinx gibi harici arama motorları tercih edilmelidir. Bu sistemler, veritabanınızla senkronize çalışarak arama yükünü tamamen kendi üzerlerine alırlar.

—

Sonuç

Ubuntu 16.04 üzerinde MySQL 5.6 kullanarak Full-Text Search mimarisini hayata geçirmek, veritabanı arama performansınızı katlayarak artırmanın en hızlı ve efektif yoludur. InnoDB depolama motorunun sunduğu ACID güvenliği ile birleşen FTS, kullanıcılarınıza hızlı, alakalı ve esnek arama sonuçları sunmanıza olanak tanır.

Bu makalede ele alınan minimum kelime uzunluğu ayarları, stopword yönetimi ve Boolean arama operatörleri gibi teknik detayları projelerinize uygulayarak, veritabanı sunucunuzun üzerindeki yükü hafifletebilir ve kullanıcı deneyimini üst seviyeye taşıyabilirsiniz. Unutmayın ki doğru indeksleme stratejisi, donanım yükseltmekten her zaman daha az maliyetli ve daha kalıcı bir çözümdür.