İletişim
IBSurgeon
Bu sayfa makine çevirisidir. İngilizce orijinalini okuyun. English

NGRAM dizini (HQbird): LIKE '%...%' ve CONTAINING ile alt dize araması

← HQbird server features

Bir NGRAM dizini, satırları bir metin değerinin alt dizesine göre bulur; böylece WHERE col LIKE '%error 404%' tüm tabloyu okumak zorunda kalmaz. Seyrek n-gram yöntemini kullanır; bu, GitHub kod aramasının yöntemidir (uygulama SereneDB’yi takip eder).

Sözdizimi

CREATE NGRAM INDEX name ON table (column);
CREATE NGRAM INDEX name ON table COMPUTED BY (expression);
CREATE NGRAM INDEX name ON table (column) WHERE condition;
  • Bir CHAR, VARCHAR veya BLOB SUB_TYPE TEXT sütunu ya da metin veya metin BLOB sonucu veren bir ifade.
  • Her karakter kümesi ve collation’a izin verilir; büyük/küçük harf ve aksan duyarsız (UNICODE_CI, UNICODE_CI_AI) ve ICU collation’ları (UNICODE, yerel ayarlı UTF8) dahil.
  • Kısmi bir dizin (WHERE) yalnızca sorgu aynı koşula sahipse kullanılır.
  • İzin verilmez: UNIQUE, DESCENDING, birden çok sütun, CONCURRENTLY, ikili BLOB’lar, diziler.
  • ALTER INDEX ... ACTIVE | INACTIVE, SET STATISTICS INDEX, DROP INDEX her zamanki gibi çalışır.
  • SHOW INDEX NGRAM INDEX yazdırır, isql -x CREATE NGRAM INDEX yazdırır.
  • Veritabanı ODS’si 13.1 olmalıdır (Firebird 5 için varsayılan).

Dizini kullanan sorgular

SELECT ... WHERE col LIKE '%needle%';
SELECT ... WHERE col LIKE '%part1%part2%';
SELECT ... WHERE col LIKE 'pre_fix%' ESCAPE '\';
SELECT ... WHERE col LIKE ?;                   -- the pattern is a parameter
SELECT ... WHERE UPPER(col) LIKE '%NEEDLE%';  -- with an index COMPUTED BY (UPPER(col))
SELECT ... WHERE col CONTAINING 'needle';     -- see "CONTAINING" below
  • Desenin en az 3 karakterlik bir değişmez kısmı olmalıdır (% ve _ arasında). Böyle bir kısmı olmayan sabit bir desen dizini kullanmaz. Böyle bir kısmı olmayan bir parametre tam tarama yapar (sonuç yine de doğrudur).
  • Dizin diğer dizinlerle birleştirilebilir (AND).
  • LIKE bulunan her satır için her zaman yeniden kontrol edilir, bu nedenle dizin hiçbir zaman bir sorgunun sonucunu değiştirmez.
  • Birkaç LIKE‘ın OR‘u her biri için dizini kullanır.
  • Şunlar için kullanılmaz: SIMILAR TO, STARTING WITH, =, aralıklar, IS NULL, ORDER BY, MIN/MAX.

Collation’lar

Dizin anahtarları, LIKE‘ın karşılaştırdığı metnin aynı kanonik biçiminden yapılır. Bu nedenle büyük/küçük harf duyarsız bir collation ile LIKE büyük/küçük harf duyarsızdır ve dizin aynı satırları bulur:

CREATE TABLE doc (id INT, title VARCHAR(200) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI);
CREATE NGRAM INDEX doc_title_ng ON doc (title);
SELECT id FROM doc WHERE title LIKE '%cafe%';   -- finds 'Café', 'CAFE', 'café'

ICU collation’larının kanonik biçimi, sıradan dizinlerde olduğu gibi ICU sürümüne bağlıdır. ICU kütüphanesi değiştikten sonra dizini yeniden oluşturun (ALTER INDEX ... INACTIVE, ardından ACTIVE).

CONTAINING

col CONTAINING 'x' büyük/küçük harf duyarsızdır. Bir NGRAM dizinini iki durumda kullanır:

  • col, büyük/küçük harf duyarsız bir Unicode collation’a (UNICODE_CI, UNICODE_CI_AI) sahip UTF8‘dir: sütunun kendisi üzerinde bir dizin;
  • diğer herhangi bir collation: COMPUTED BY (UPPER(col)) ile bir dizin. Sorguyu her zamanki gibi yazın, col CONTAINING 'x': iyileştirici UPPER dizinini kendisi bulur.

UTF8 ve UNICODE_FSS dışındaki çok baytlı karakter kümeleri için kullanılmaz (örneğin SJIS_0208) varsayılan collation’larıyla: orada CONTAINING karakterleri değil baytları karşılaştırır.

Metin BLOB’ları

CREATE TABLE note (id INT, body BLOB SUB_TYPE TEXT CHARACTER SET UTF8);
CREATE NGRAM INDEX note_body_ng ON note (body);
CREATE NGRAM INDEX note_body_up_ng ON note COMPUTED BY (UPPER(body));
SELECT id FROM note WHERE body LIKE '%timeout%';
SELECT id FROM note WHERE body CONTAINING 'Timeout';  -- uses note_body_up_ng
  • Dizin, herhangi bir uzunluktaki tüm BLOB’un gramlarına sahiptir. Sunucu gramları 64K karakterlik pencerelerde oluşturur ve yalnızca farklı gramları tutar, bu nedenle anahtarlar için bellek BLOB boyutuna değil, farklı gramların sayısına bağlıdır. Çok baytlı bir karakter kümesindeki bir BLOB, LIKE‘ın okuduğu gibi bütün olarak okunur.
  • BLOB, bir satır eklendiğinde, BLOB değiştirildiğinde ve bir kez daha çöp toplama eski sürümü kaldırdığında okunur. Diğer sütunların bir UPDATE‘i aynı BLOB’u korur ve onu okumaz (COMPUTED BY (UPPER(body)) dizini için de).
  • Dizin, bir satırın her farklı gramı için bir girişe sahiptir. Uzun belgeler birçok gramı tekrarlar: Firebird kaynak dosyaları karakter başına 0,3 giriş verir, kısa satırlar 1,4.

Önce verileri yükleyin, sonra dizini oluşturun (“Boyut ve maliyet” bölümüne bakın).

Plan çıktısı:

PLAN (MSG INDEX (MSG_TEXT_NG))

Select Expression
    -> Filter
        -> Table "MSG" Access By ID
            -> Bitmap
                -> Index "MSG_TEXT_NG" N-gram Scan

Boyut ve maliyet

Firebird kaynak kodunun satırlarında ölçüldü (953 000 satır, 27 milyon karakter): dizin 188 MB (metnin 7,2 katı, tablonun 1,7 katı), 16 s’de oluşturuldu; LIKE '%word%' tam taramadan 12 ila 4000 kat daha hızlı (nadir kelimeler için 0,2 ms’den 35 000 satırdaki bir kelime için 50 ms’ye, tarama için 600 ms’ye karşı). UNICODE_CI_AI ile tarama 6 kat daha yavaştır (3,5 s), dizin değildir: LIKE ve CONTAINING 20 ila 8000 kat daha hızlıdır.

  • Metnin karakteri başına yaklaşık 1,4 dizin girişi (her giriş 8 baytlık bir gram karması ve bir kayıt numarasıdır). Dizinin metinden birkaç kat daha büyük olmasını bekleyin.
  • Her ekleme, karakter başına yaklaşık 1,4 B-ağacı girişi ekler: bir NGRAM dizinine sahip bir tabloya eklemeler, dizinsiz olana göre çok daha yavaştır. Hedef, nadir güncellemelerin olduğu çoğunlukla ekleme yapılan verilerdir (günlükler, mesajlar, belgeler).
  • Güncellemeler yalnızca yeni gramları ekler. Eski gramlar, hiçbir kayıt sürümü artık onlara ihtiyaç duymadığında çöp toplama tarafından kaldırılır.
  • Dizin oluşturma, CONCURRENTLY‘nin yaptığı gibi her sayfada %5 boş alan bırakır. Bir kaydın gramları birçok yaprak sayfadadır; boş alan olmadan oluşturmadan sonraki ilk güncellemeler neredeyse her yaprak sayfayı böler.
  • Dizinlenen sütunu değiştirmeyen bir UPDATE (bir ifade üzerindeki dizin için: ifadenin kullandığı sütunlar) gramları hiç hesaplamaz.
  • Toplu yükleme için dizini yüklemeden sonra oluşturun veya yükleme sırasında INACTIVE yapın. BLOB olarak 1 163 kaynak dosyası (28 milyon karakter): iki NGRAM diziniyle yükleme 419 s; dizinsiz yükleme 0,6 s, ardından her iki dizinin oluşturulması 10,7 s.
  • Dizin oluşturma tüm gramları sıralar: geçici alan gram başına yaklaşık 20 bayttır. ParallelWorkers oluşturmayı hızlandırır.

NGRAM desteği olmayan sunucularla uyumluluk

Dizin ODS 13.1’de saklanır. Koşulu (RDB$CONDITION_BLR) bir dönüştürme hatasıyla başarısız olan bir koruma ifadesiyle başlar. NGRAM desteği olmayan bir sunucu (stok Firebird 5.0.x, eski HQbird):

  • veritabanını açar; SELECT doğru sonuçlar verir ve NGRAM dizinini kullanmaz;
  • tabloya INSERT ve UPDATE‘i şu hatayla reddeder conversion error from string "HQBIRD NGRAM INDEX: use HQbird server" (böylece dizin asla bozulmaz);
  • DELETE ve sweep’e izin verir (dizin fazladan girişler tutar: yalnızca bir boyut maliyeti);
Query time: full scan vs NGRAM index
Speed-up of NGRAM vs full scan
Size: text, table, and NGRAM index
BLOB bulk load with and without NGRAM during load
  • dizini etkinleştiremez.

Stok Firebird 5.0.4.1812 ve HQbird 5.0.4.1773 ile test edildi. Üçüncü taraf araçlar dizini bir WHERE yan tümcesiyle DESCENDING olarak gösterebilir.

gbak, NGRAM dizinlerini yedekler ve geri yükler (geri yükleme, NGRAM desteğine sahip HQbird tarafından yapılmalıdır). Stok bir geri yükleme, bir BLOB sütunu üzerindeki NGRAM dizininde başarısız olur (attempt to index BLOB column) ve veritabanını çevrimdışı bırakır (gfix -online onu o dizin olmadan çevrimiçi hale getirir).