MariaDB'de FULLTEXT Arama: MATCH AGAINST, Boolean Kip ve Stopword Ayarları

FULLTEXT ne zaman LIKE'tan iyidir?

LIKE '%kelime%' her satırdaki metni baştan sona tarar ve öndeki joker karakter yüzünden normal bir B-tree index'ten yararlanamaz. FULLTEXT index ise metni kelimelere ayırıp hangi kelimenin hangi satırda geçtiğini ayrı bir yapıda tutar. Böylece "içinde şu kelimeler geçen satırları getir" türündeki sorgular tüm tabloyu okumadan yanıtlanabilir ve sonuçlara bir alaka puanı eşlik eder. Belgeler basit alt dize aramaları için LIKE operatörünü önerir; yani hangisini seçeceğiniz arama ihtiyacınıza bağlıdır. Kelime bazlı, puanlı ve çok sütunlu aramalarda FULLTEXT, bir sütunun belirli bir parçayla başlayıp başlamadığını kontrol etmek gibi işlerde LIKE uygundur. LIKE ve düzenli ifade fonksiyonları için MariaDB string fonksiyonları yazısına bakın.

FULLTEXT index oluşturma

FULLTEXT index tabloyu oluştururken CREATE TABLE içinde tanımlanabilir ya da sonradan ALTER TABLE ... ADD FULLTEXT veya CREATE FULLTEXT INDEX ile eklenebilir. Tek bir index birden çok sütunu kapsayabilir; bu durumda sorgudaki MATCH listesinin index'teki sütun listesiyle eşleşmesi gerekir. Aşağıdaki örnekte bir blog tablosuna önce başlık ve gövdeyi birlikte kapsayan bir index, ardından iki ayrı tek sütunlu index ekleniyor:

CREATE TABLE makaleler (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  baslik VARCHAR(200) NOT NULL,
  govde TEXT NOT NULL,
  FULLTEXT (baslik, govde)
) ENGINE=InnoDB;

ALTER TABLE makaleler ADD FULLTEXT INDEX ft_baslik (baslik);

CREATE FULLTEXT INDEX ft_govde ON makaleler (govde);

Belgeler büyük veri kümeleri için önemli bir ipucu verir: veriyi önce FULLTEXT index'i olmayan bir tabloya yükleyip index'i sonra oluşturmak, index'i hazır bir tabloya yüklemekten çok daha hızlıdır. Toplu yükleme yöntemleri için LOAD DATA ve mariadb-import yazısına bakabilirsiniz. InnoDB'nin bir seferde yalnızca tek bir FULLTEXT index oluşturabildiğini de unutmayın; aynı anda birden fazla oluşturmaya çalışırsanız 1795 kodlu "InnoDB presently supports one FULLTEXT index creation at a time" hatası alırsınız.

MATCH ... AGAINST sözdizimi

Tam metin araması MATCH() ... AGAINST yapısıyla yapılır. MATCH aranacak sütunları virgülle ayrılmış bir liste olarak alır; AGAINST aranacak dizeyi ve isteğe bağlı bir arama kipini alır. Belgeler önemli bir kısıtı özellikle vurgular: arama dizesi bir değişken ya da sütun adı olamaz, sabit bir dize olmalıdır.

MATCH (col1,col2,...) AGAINST (expr [search_modifier])

MATCH ifadesi hem WHERE içinde süzgeç olarak hem de seçim listesinde puan almak için kullanılabilir. Belgelerdeki temel örnek bir MyISAM tablosunda tek kelimelik aramayı ve bir stopword aramasının boş sonuç döndürmesini gösterir. "Once" stopword listesinde olduğu için hiçbir satır gelmez:

CREATE TABLE ft_myisam(copy TEXT,FULLTEXT(copy)) ENGINE=MyISAM;

INSERT INTO ft_myisam(copy) VALUES ('Once upon a time'),
  ('There was a wicked witch'), ('Who ate everybody up');

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked');
+--------------------------+
| copy                     |
+--------------------------+
| There was a wicked witch |
+--------------------------+

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('Once');
Empty set (0.00 sec)

Doğal dil kipi ve alaka puanı

IN NATURAL LANGUAGE MODE varsayılan kiptir; yazmasanız da bu kip kullanılır. Özel operatör yoktur, arama bir veya daha fazla kelimeden oluşur ve sonuçlar alaka puanına göre azalan sırada döner. MariaDB alaka puanını birçok etkene göre hesaplar: index'teki kelime sayısı, satırdaki benzersiz kelime sayısı, hem index'teki hem sonuçtaki toplam kelime sayısı ve kelimenin ağırlığı. Puanı görmek için aynı MATCH ifadesini seçim listesine yazmanız yeterlidir:

SELECT copy,MATCH(copy) AGAINST('witch') AS relevance
  FROM ft_myisam WHERE MATCH(copy) AGAINST('witch');
+---------------------------------+--------------------+
| copy                            | relevance          |
+---------------------------------+--------------------+
| There was a wicked witch        | 0.6775632500648499 |
| There was a wicked wicked witch | 0.5031757950782776 |
+---------------------------------+--------------------+

Puanın mutlak değeri tabloya, veriye ve motora göre değişir; onu yalnızca aynı sorgunun sonuçlarını kendi aralarında sıralamak için kullanın, farklı tablolar ya da farklı sorgular arasında karşılaştırmayın. Uygulamanızda sayfa başına sonuç sınırlamak için LIMIT kullanabilirsiniz; ayrıntılar MariaDB SELECT, WHERE, ORDER BY ve LIMIT yazısında.

Boolean kip ve operatörler

IN BOOLEAN MODE aramaya operatör ekleme imkânı verir. Belgelere göre bu kipte sonuçlar alaka puanına göre sıralanmaz ve MyISAM'daki yüzde elli sınırı uygulanmaz; buna karşılık stopword listesi ve kelime uzunluğu sınırları her zamanki gibi geçerlidir. Kullanılabilen operatörler şunlardır:

~ ile - arasındaki fark sık karıştırılır: - kelimeyi içeren satırı tamamen dışlar, ~ ise satırı tutar ama puanını düşürür. < operatörü de satırı dışlamaz; yalnızca kelimenin katkısını azaltır. Belgelerdeki iki örnek joker karakteri ve zorunlu/yasak kelime birleşimini gösterir:

SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('Maria*' IN BOOLEAN MODE);
+--------------------------------+
| copy                           |
+--------------------------------+
| MySQL vs MariaDB database      |
| Oracle vs MariaDB database     |
| PostgreSQL vs MariaDB database |
| MariaDB overview               |
+--------------------------------+

SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('+MariaDB -database'
  IN BOOLEAN MODE);
+------------------+
| copy             |
+------------------+
| MariaDB overview |
+------------------+

Joker karakter Türkçe gibi eklemeli dillerde özellikle yararlıdır. Belgelere göre kısmi kelimeler sonuçlardan çıkarılır; yani doğal dil kipinde "kitap" araması "kitaplar" kelimesini içeren satırı kendiliğinden bulmaz. Kök üzerinden arama yapmak istiyorsanız boolean kipte kitap* yazmanız gerekir.

Sorgu genişletme (WITH QUERY EXPANSION)

Sorgu genişletme, doğal dil aramasının bir türevidir. Önce verilen dizeyle normal bir arama yapılır, sonra en alakalı satırlardaki kelimeler arama dizesine eklenir ve arama tekrarlanır; sorgu ikinci aramanın sonuçlarını döndürür. Belgelerdeki örnekte "database" araması üç satır bulurken genişletilmiş arama, bu satırlarda sürekli "MariaDB" ile birlikte geçtiği için "MariaDB overview" satırını da getirir:

SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
+--------------------------------+
| copy                           |
+--------------------------------+
| MySQL vs MariaDB database      |
| Oracle vs MariaDB database     |
| PostgreSQL vs MariaDB database |
+--------------------------------+
3 rows in set (0.00 sec)

SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database' WITH QUERY EXPANSION);
+--------------------------------+
| copy                           |
+--------------------------------+
| MySQL vs MariaDB database      |
| Oracle vs MariaDB database     |
| PostgreSQL vs MariaDB database |
| MariaDB overview               |
+--------------------------------+
4 rows in set (0.00 sec)

Genişletmede kaç üst eşleşmenin kullanılacağını ft_query_expansion_limit değişkeni belirler; belgelere göre varsayılanı 20'dir. Genişletme, sonuçları kullanıcının hiç yazmadığı kelimelerle zenginleştirdiği için alakasız satırların da gelmesine yol açabilir; arama kutusunda varsayılan olarak değil, "benzer sonuçları da göster" gibi bilinçli bir seçenekle kullanmak daha güvenlidir.

Index'e girmeyen kelimeler

FULLTEXT aramasının en çok şaşırtan yanı, bazı kelimelerin hiç index'lenmemesidir. Belgeler şu kuralları sayar:

• Kısmi kelimeler sonuçlardan çıkarılır.
• MyISAM'da 4, InnoDB'de 3 karakterden kısa kelimeler index'e girmez. Sınır MyISAM için ft_min_word_len, InnoDB için innodb_ft_min_token_size ile değiştirilir.
• 84 karakterden uzun kelimeler de index'e girmez; sınır ft_max_word_len ve innodb_ft_max_token_size ile ayarlanır.
• Stopword listesindeki kelimeler, boolean kip kullanılmadıkça sonuçlara yansımaz.
• Yalnızca MyISAM ve Aria'da, satırların yarısından fazlasında geçen bir kelime de sonuçlardan çıkarılır.
• InnoDB index'lerinde yalnızca işlenmiş (commit edilmiş) satırlar görünür; geçerli transaction'daki değişiklikler aramaya yansımaz.

Yüzde elli kuralı belgelerdeki örnekte açıkça görülür: "wicked" kelimesi satırların yarısından fazlasına eklenince doğal dil araması boş döner, boolean kip ise satırları getirir:

INSERT INTO ft_myisam(copy) VALUES ('Once upon a wicked time'),
  ('There was a wicked wicked witch'), ('Who ate everybody wicked up');

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked');
Empty set (0.00 sec)

SELECT * FROM ft_myisam WHERE MATCH(copy) AGAINST('wicked' IN BOOLEAN MODE);
+---------------------------------+
| copy                            |
+---------------------------------+
| There was a wicked witch        |
| Once upon a wicked time         |
| There was a wicked wicked witch |
| Who ate everybody wicked up     |
+---------------------------------+

Uzunluk değişkenlerinin hepsi dinamik değildir; belgelere göre ft_min_word_len ve innodb_ft_min_token_size sunucu çalışırken değiştirilemez, bu yüzden seçenek dosyasına yazılıp sunucu yeniden başlatılmalıdır. Değeri değiştirdikten sonra mevcut FULLTEXT index'leri yeniden oluşturmanız gerekir; MyISAM için belgeler en hızlı yol olarak REPAIR TABLE tablo_adı QUICK komutunu önerir. Belgeler daha küçük bir sınırın index'i büyüteceğini ve sorguları yavaşlatabileceğini, buna karşılık kısa kelimelerin aranabilir hâle geleceğini belirtir; Çince, Japonca ve Korece karakter setlerinde InnoDB için 1 değeri önerilir.

[mariadbd]
innodb_ft_min_token_size = 2

Stopword listeleri

Stopword'ler, arama açısından anlam taşımadığı varsayılan yaygın kelimelerdir. MyISAM ve InnoDB varsayılan olarak farklı listeler kullanır. MyISAM listesi kaynak koddaki storage/myisam/ft_static.c dosyasından gelir; ft_stopword_file değişkeniyle başka bir dosya gösterilebilir ya da boş dize verilerek liste tamamen kapatılabilir. Liste değişirse mevcut index'lerin yeniden oluşturulması gerekir. InnoDB'nin yerleşik listesi çok daha kısadır ve INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD tablosundan görülebilir. Her iki varsayılan liste de İngilizce ağırlıklıdır; Türkçe bağlaçlar bu listelerde yoktur.

InnoDB'de stopword'ler, index oluşturulduğu anda innodb_ft_enable_stopword açıksa (varsayılan olarak açıktır) devreye girer. Liste şu sırayla belirlenir: oturum ve küresel düzeyde ayarlanabilen innodb_ft_user_stopword_table doluysa o tablo, değilse küresel innodb_ft_server_stopword_table tablosu, ikisi de boşsa yerleşik liste kullanılır. Gösterilen tablo değişken ayarlanmadan ve index oluşturulmadan önce var olmalı, tek sütunlu bir InnoDB tablosu olmalı ve bu sütun VALUE adında bir VARCHAR olmalıdır. Değişkene değer veritabanı/tablo biçiminde yazılır. Türkçe bir liste için akış şöyle olabilir:

CREATE TABLE arama.durak_kelimeler (VALUE VARCHAR(30)) ENGINE=InnoDB;
INSERT INTO arama.durak_kelimeler VALUES ('ve'), ('ile'), ('bir');

SET GLOBAL innodb_ft_server_stopword_table = 'arama/durak_kelimeler';

ALTER TABLE makaleler DROP INDEX ft_govde;
CREATE FULLTEXT INDEX ft_govde ON makaleler (govde);

InnoDB FULLTEXT bakımı

InnoDB, satır eklenip silindikçe FULLTEXT index'i hemen yeniden düzenlemez, çünkü bu pahalı bir işlemdir; değişiklikler ayrı bir yerde tutulur ve index ancak OPTIMIZE TABLE çalıştırıldığında tam olarak yeniden düzenlenir. Varsayılan olarak OPTIMIZE TABLE tabloyu birleştirmeye çalışır; yalnızca FULLTEXT verisini işlemesi için innodb_optimize_fulltext_only değişkeni 1 yapılmalı ve iş bitince yeniden 0'a döndürülmelidir. Bir çalıştırmada işlenecek kelime sayısını innodb_ft_num_word_optimize sınırlar; belgelere göre varsayılanı 2000'dir, bu yüzden büyük index'lerde komutu birkaç kez çalıştırmanız gerekebilir:

SET GLOBAL innodb_optimize_fulltext_only = 1;
OPTIMIZE TABLE blog.makaleler;
SET GLOBAL innodb_optimize_fulltext_only = 0;

Index'in içeriğini incelemek isterseniz INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE ve INNODB_FT_INDEX_CACHE tablolarına bakabilirsiniz; bunlar SUPER yetkisi ve innodb_ft_aux_table değişkeninin ayarlanmış olmasını gerektirir. Ayrıca innodb_ft_result_cache_limit, tek bir tam metin sorgusunun sonuç önbelleği için bayt cinsinden üst sınırdır; sınır aşılırsa sorgu hata döndürür.

MyISAM ile InnoDB arasındaki farklar

Aynı MATCH ... AGAINST sorgusu iki motorda farklı sonuç verebilir, çünkü kurallar motora göre değişir. MyISAM ve Aria'da kelime en az dört karakter olmalıdır ve satırların yarısından fazlasında geçen kelimeler doğal dil kipinde elenir. InnoDB'de sınır üç karakterdir, yüzde elli kuralı yoktur ama yalnızca işlenmiş satırlar görünür ve index değişiklikleri OPTIMIZE TABLE ile düzenlenene kadar ayrı tutulur. Stopword listeleri de farklıdır: MyISAM'ınki uzun bir İngilizce listedir, InnoDB'ninki kısadır ve bir tabloyla değiştirilebilir.

Belgelerdeki örneklerin çoğu MyISAM tablosuyla yazıldığı için, bu örnekleri InnoDB tablosunda denerseniz özellikle yüzde elli kuralını gösteren sorgunun farklı davranmasını bekleyin. Uygulamanızı bir motordan diğerine taşırken arama testlerinizi yeniden çalıştırmanız bu yüzden önemlidir.

Küçük bir uygulama: blog araması

Yukarıdaki makaleler tablosunu kullanarak bir site içi arama kuralım. Kullanıcı kutuya iki kelime yazdığında doğal dil kipiyle puanlı ilk on sonucu, gelişmiş arama seçeneğinde ise boolean kiple "yedek ile başlayan ama windows içermeyen" sonuçları getiriyoruz. İki MATCH ifadesinin sütun listesi, index'teki (baslik, govde) listesiyle birebir aynı:

SELECT id, baslik,
       MATCH (baslik, govde) AGAINST ('yedekleme sunucu') AS skor
  FROM makaleler
 WHERE MATCH (baslik, govde) AGAINST ('yedekleme sunucu')
 LIMIT 10;

SELECT id, baslik
  FROM makaleler
 WHERE MATCH (baslik, govde) AGAINST ('+yedek* -windows' IN BOOLEAN MODE);

Bu sorgularda dikkat edilecek üç ayrıntı var. Birincisi, arama dizesi sabit olmalı; uygulamada kullanıcı girdisini sorguya yazarken hazırlanmış ifade (prepared statement) kullanarak SQL enjeksiyonundan korunun, ayrıntı için SQL injection önleme yazısına bakın. İkincisi, InnoDB'de üç karakterden kısa kelimeler ve stopword'ler sonuç getirmez; kullanıcıya "çok kısa kelime" uyarısı göstermek iyi bir deneyimdir. Üçüncüsü, boolean kipte sonuçlar puana göre sıralanmaz; sıralama istiyorsanız puanı seçim listesinde hesaplayıp ORDER BY ile kendiniz sıralayın. Sorgu planını görmek için MariaDB EXPLAIN ve ANALYZE yazısındaki yöntemleri kullanabilirsiniz.

Anlamsal arama da gerekiyorsa FULLTEXT sonuçlarını vektör aramasıyla birleştiren hibrit yaklaşım belgelerde ayrı bir sayfada anlatılır; vektör tarafı için MariaDB vektör arama yazısına bakın.

Sık yapılan hatalar

• MATCH listesinin index'le uyuşmaması. Sorgudaki sütun listesine karşılık gelen bir FULLTEXT index yoksa 1191 kodlu "Can't find FULLTEXT index matching the column list" hatası gelir.
• Desteklemeyen motor. Tablo motoru FULLTEXT desteklemiyorsa 1214 kodlu hata alınır.
• Bölümlenmiş tabloda FULLTEXT. 1757 koduyla "FULLTEXT index is not supported for partitioned tables." hatası döner; bölümleme kısıtları için MariaDB tablo bölümleme yazısına bakın.
• AGAINST içine değişken veya sütun yazmak. Arama dizesi sabit olmalıdır.
• Kısa kelimelerin neden bulunmadığını anlamamak. Minimum uzunluk ve stopword kurallarını kontrol edin.
• Değişkeni değiştirip index'i yeniden oluşturmamak. Uzunluk ve stopword ayarları ancak index yeniden oluşturulunca etkili olur.
• Aynı transaction'da eklenen satırı aramak. InnoDB yalnızca işlenmiş satırları gösterir.

Sık Sorulan Sorular

InnoDB'de FULLTEXT index kullanabilir miyim?

Evet. Belgelere göre FULLTEXT index InnoDB, MyISAM, Aria ve Mroonga tablolarında desteklenir. Motorlar arasında minimum kelime uzunluğu, stopword listesi ve yüzde elli kuralı gibi farklar vardır.

Türkçe ekli kelimeleri nasıl ararım?

Kısmi kelimeler doğal dil kipinde eşleşmez. Kök üzerinden aramak için boolean kipte kelimenin sonuna * ekleyin; örneğin AGAINST('sunucu*' IN BOOLEAN MODE).

Sonuçlar neden puana göre sıralı değil?

Boolean kipte sonuçlar alaka puanına göre sıralanmaz. Puanı seçim listesinde MATCH ile hesaplayıp ORDER BY kullanabilirsiniz.

Kendi stopword listemi nasıl tanımlarım?

InnoDB için VALUE adlı tek bir VARCHAR sütunu olan bir InnoDB tablosu oluşturup innodb_ft_server_stopword_table ya da innodb_ft_user_stopword_table değişkenine gösterin ve index'i yeniden oluşturun. MyISAM için ft_stopword_file kullanılır.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: MariaDB öğrenme rehberi · MariaDB index oluşturma · MariaDB string fonksiyonları · MariaDB karakter seti ve collation · MariaDB vektör arama · MariaDB EXPLAIN ve ANALYZE