Carian Full-Text MySQL daripada CFML: Panduan dan Ukuran di Rig

Carian full-text MySQL daripada CFML bermula dengan satu index FULLTEXT dan satu predikat MATCH ... AGAINST. Pada jadual 20,000 baris dalam rig ujian Lucee 7.1.1.17-RC dengan MySQL 8.4.11, predikat itu mengira 1,666 baris dalam 0.75 ms, manakala LIKE '%pemasangan%' mengambil 14.8 ms untuk hasil yang sama. Kedua-dua bacaan datang daripada SHOW PROFILES pada pelayan yang sama.

Artikel ini menunjukkan cara memasang index, dua mod carian, cara membersihkan input, dan empat sebab carian pulang kosong. Semua blok cfml dijalankan dalam rig, dan output yang ditunjukkan ialah output sebenar.

Index yang diuji dan data contoh

Index penuh-teks hanya berfungsi pada lajur CHAR, VARCHAR dan TEXT, dan MATCH mesti menyebut lajur yang sama dengan index dalam susunan yang sama. Apabila saya menulis MATCH(badan) pada index (tajuk, badan), MySQL membalas Can't find FULLTEXT index matching the column list, ralat yang paling kerap pada carian pertama.

CREATE TABLE artikel (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tajuk VARCHAR(200) NOT NULL,
  badan TEXT NOT NULL,
  kategori VARCHAR(40) NOT NULL,
  FULLTEXT KEY ft_artikel (tajuk, badan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO artikel (tajuk, badan, kategori) VALUES
('Panduan pemasangan Lucee pada Ubuntu','Lucee ialah enjin CFML sumber terbuka. Panduan ini menerangkan pemasangan Lucee pada Ubuntu dengan Docker dan tetapan datasource MySQL.','Panduan'),
('Cache query dalam Lucee','Lucee boleh menyimpan hasil query dalam cache. cachedWithin menetapkan tempoh cache, dan panduan ini mengukur kosnya.','Panduan'),
('Keselamatan aplikasi CFML dengan Lucee','Lucee menyediakan encodeForHTML untuk mengelak XSS. Keselamatan bermula daripada pengesahan input pengguna.','Panduan'),
('Panduan e-dagang dengan Lucee dan MyInvois','Pemasangan gerbang pembayaran untuk e-dagang memerlukan pengesahan tandatangan. Lucee menghantar e-invois MyInvois melalui API.','Panduan');

Empat baris itu digunakan oleh semua contoh di bawah, jadi angkanya boleh diulang. Untuk ukuran prestasi saya membina jadual berasingan 20,000 baris: data 3.52 MB, index penuh-teks 0.36 MB, kira-kira satu persepuluh saiz data.

Dua mod yang berkelakuan berbeza

Tanpa IN BOOLEAN MODE, MySQL menggunakan mod bahasa semula jadi: +, -, * dan tanda petikan hanyalah aksara biasa. Mod boolean menafsirkan aksara itu sebagai operator.

Carian pemasangan docker dalam mod bahasa semula jadi memulangkan dua baris, dan baris yang mengandungi kedua-dua perkataan mendapat skor lebih tinggi.

<cfscript>
kata = "pemasangan docker";

q = queryExecute(
    "SELECT id, tajuk,
            MATCH(tajuk, badan) AGAINST(:kata IN NATURAL LANGUAGE MODE) AS skor
     FROM artikel
     WHERE MATCH(tajuk, badan) AGAINST(:kata IN NATURAL LANGUAGE MODE)
     ORDER BY skor DESC",
    {kata: {value: kata, cfsqltype: "cf_sql_varchar"}},
    {datasource = "kedai"}
);

writeOutput("baris=" & q.recordCount & chr(10));
for (row in q) {
    writeOutput(row.id & " | " & row.tajuk & " | " & numberFormat(row.skor, "0.0000") & chr(10));
}
</cfscript>

baris=2
1 | Panduan pemasangan Lucee pada Ubuntu | 0.1181
4 | Panduan e-dagang dengan Lucee dan MyInvois | 0.0591

Nama datasource dalam semua contoh ialah kedai, datasource lalai rig. Kalau anda masih memakai LIKE, corak asasnya ada dalam carian dan penapis projek inventori.

Skor kaitan dan perangkap skor sifar

Skor kaitan dikira daripada kekerapan perkataan dalam baris berbanding bilangan baris yang mengandunginya, dan alias skor boleh digunakan terus dalam ORDER BY.

Apabila satu perkataan muncul dalam setiap baris, skornya menjadi sifar. Pada jadual empat baris di atas, carian lucee memulangkan empat baris dan kesemuanya berskor 0.0000: baris tetap keluar, tetapi susunan mengikut skor tidak bermakna lagi.

MyISAM berkelakuan berbeza: dengan empat baris yang sama, carian bahasa semula jadi untuk lucee memulangkan sifar baris kerana perkataan yang muncul dalam sekurang-kurangnya 50 peratus baris dianggap umum, manakala mod boolean memulangkan empat baris. Dokumentasi MySQL menyebut had itu khusus untuk MyISAM. Gunakan InnoDB, dan jangan bergantung pada skor apabila kata kunci anda ada dalam setiap dokumen.

Input pengguna: parameter bukan penapis operator

cfqueryparam menyelesaikan masalah petikan, bukan masalah operator. Nilai yang dicantum terus ke dalam SQL memecahkan query apabila kata kunci mengandungi petikan: lucee's memberi You have an error in your SQL syntax, manakala nilai yang sama melalui parameter berjalan dan memulangkan empat baris. Sebab itu parameter wajib, seperti dibincangkan dalam cfqueryparam dan suntikan SQL.

Tetapi parameter tidak menghalang operator. Nilai +lucee -docker melalui parameter tetap berfungsi sebagai "wajib ada lucee, tolak yang ada docker", kerana MySQL membaca operator itu daripada kandungan rentetan.

Ujian yang paling mengejutkan ialah e-dagang: satu baris dalam mod bahasa semula jadi, sifar baris dalam mod boolean, kerana sengkang dibaca sebagai operator tolak. Perkataan seperti e-invois dan e-dagang biasa dalam kandungan Malaysia, jadi ini bukan kes tepi. Bersihkan input sebelum ia menjadi rentetan boolean:

<cfscript>
function bersihkanBoolean(required string teks) {
    var bersih = reReplace(arguments.teks, '[+><()~*"@-]', ' ', 'all');
    var hasil = [];
    for (var w in listToArray(bersih, ' ')) {
        w = trim(w);
        if (len(w) >= 3) { arrayAppend(hasil, w); }
    }
    return trim(arrayToList(hasil, ' '));
}

masukan = '+lucee -"(docker"';
kata = bersihkanBoolean(masukan);
writeOutput("masukan [" & masukan & "] -> [" & kata & "]" & chr(10));

if (len(kata)) {
    q = queryExecute(
        "SELECT id, tajuk FROM artikel
         WHERE MATCH(tajuk, badan) AGAINST(:kata IN BOOLEAN MODE)
         ORDER BY MATCH(tajuk, badan) AGAINST(:kata IN BOOLEAN MODE) DESC",
        {kata: {value: kata, cfsqltype: "cf_sql_varchar"}},
        {datasource = "kedai"}
    );
    writeOutput("baris=" & q.recordCount & chr(10));
    for (row in q) {
        writeOutput(row.id & " | " & row.tajuk & chr(10));
    }
} else {
    writeOutput("tiada kata kunci selepas dibersihkan" & chr(10));
}
</cfscript>

masukan [+lucee -"(docker"] -> [lucee docker]
baris=4
1 | Panduan pemasangan Lucee pada Ubuntu
2 | Cache query dalam Lucee
3 | Keselamatan aplikasi CFML dengan Lucee
4 | Panduan e-dagang dengan Lucee dan MyInvois

Fungsi itu membuang setiap aksara operator dan mengekalkan perkataan tiga aksara ke atas. Ia menukar e-dagang menjadi dagang, yang masih memadankan dokumen. Kesan sampingannya: pengguna yang menaip -kucing tidak lagi mendapat pengecualian, kerana semua perkataan dianggap syarat positif, dan itu perlu ditulis dalam teks bantuan carian.

Penjaga len(kata) perlu. Input di ke dibersihkan menjadi rentetan kosong kerana kedua-dua perkataan terlalu pendek, dan AGAINST('') memulangkan sifar baris tanpa ralat.

Token minimum, stopword dan perkataan Melayu

InnoDB mengabaikan perkataan yang lebih pendek daripada tiga aksara, dan MyISAM empat. Carian di memulangkan sifar baris walaupun perkataan itu ada dalam teks, jadi di dan ke tidak boleh dicari sementara dan dan yang boleh.

Senarai stopword lalai berisi 36 perkataan Inggeris, daripada a hingga www. Saya membacanya daripada information_schema.INNODB_FT_DEFAULT_STOPWORD dan menyemak bahawa dan, yang, untuk, dengan dan ialah tiada di dalamnya, jadi perkataan Melayu biasa diindeks.

innodb_ft_min_token_size tidak boleh ditukar ketika pelayan berjalan, ia membalas Variable is a read only variable. Ia mesti ditetapkan dalam fail konfigurasi sebelum MySQL dimulakan, dan index dibina semula selepas itu.

Senarai stopword pula boleh diganti tanpa mula semula, tetapi hanya dengan jadual yang benar-benar wujud. Menetapkan jadual yang belum dicipta membalas can't be set to the value. Selepas jadual itu dicipta dan OPTIMIZE TABLE dijalankan, carian panduan jatuh daripada tiga baris kepada sifar; selepas tetapan dikosongkan dan index dibina semula, tiga baris kembali. OPTIMIZE TABLE menjawab Table does not support optimize, doing recreate + analyze instead, iaitu jadual dibina semula, dan itulah yang diperlukan oleh index penuh-teks. Amalkan hanya kalau anda mahu perkataan itu hilang daripada carian.

Ukuran: MATCH lawan LIKE

Jadual 20,000 baris diuji dengan SHOW PROFILES di dalam MySQL, jadi masa di bawah tidak termasuk kos sambungan JDBC. Setiap query dijalankan tiga kali pada pelayan yang sudah panas.

COUNT(*) menunjukkan perbezaan sekitar dua puluh kali ganda, daripada dua pelan berbeza: fulltext membaca index, manakala LIKE dengan wildcard di hadapan membaca setiap baris. EXPLAIN menunjukkan 19,876 baris untuk LIKE, iaitu seluruh jadual. Untuk beberapa ratus baris, 15 ms tidak dirasai; untuk jadual yang membesar, MATCH kekal hampir rata. Profil prestasi CFML menunjukkan cara membaca masa peringkat permintaan, dan cache query dengan cachedWithin boleh menyembunyikan carian yang sama berulang.

Bila full-text bukan pilihan

MySQL tidak melakukan stemming, dan ini diukur: memasang memulangkan sifar baris walaupun teksnya mengandungi pemasangan, manakala pemasang* memulangkan dua baris.

Empat keadaan lain lebih baik diselesaikan dengan cara lain: jadual di bawah beberapa ribu baris, padanan tepat atau awalan kod produk dengan index B-tree, dokumen Cina atau Jepun yang memerlukan parser ngram (nilai ngram_token_size dalam rig ialah dua), dan carian kabur.

Soalan Lazim (FAQ)

Adakah FULLTEXT berfungsi pada InnoDB atau hanya MyISAM? Kedua-duanya berfungsi, dan InnoDB yang saya sarankan. Pada InnoDB, ambang 50 peratus tiada, jadi perkataan yang muncul dalam setiap baris masih dipadankan. Pada MyISAM, carian yang sama memulangkan sifar baris.

Kenapa carian saya pulang kosong walaupun perkataan itu ada dalam teks? Empat sebab yang saya ukur: perkataan lebih pendek daripada tiga aksara, perkataan itu stopword, sengkang dalam mod boolean dibaca sebagai operator tolak, atau bentuk perkataan berbeza kerana tiada stemming.

Adakah cfqueryparam cukup untuk carian boolean? Tidak. Ia menghalang petikan daripada memecahkan SQL, tetapi operator +, - dan * masih berfungsi kerana MySQL membacanya daripada kandungan rentetan. Bersihkan input, atau gunakan mod bahasa semula jadi yang mengabaikan operator.

Bolehkah saya cuma guna LIKE dan tinggalkan index penuh-teks? Boleh untuk jadual kecil. Pada 20,000 baris, LIKE '%pemasangan%' mengambil 14.8 ms berbanding 0.75 ms untuk MATCH, dan jurang itu melebar apabila jadual membesar kerana LIKE membaca setiap baris.

Bagaimana saya menukar senarai stopword kepada bahasa Melayu? Cipta jadual dengan satu lajur value, tetapkan innodb_ft_server_stopword_table kepada nama_pangkalan_data/nama_jadual, kemudian jalankan OPTIMIZE TABLE untuk membina semula index. Had minimum tiga aksara tidak boleh ditukar dengan cara ini kerana pemboleh ubah itu hanya boleh dibaca ketika pelayan berjalan.

Sumber

• Full-Text Search Functions, MySQL 8.4 Reference Manual
• Natural Language Full-Text Searches, MySQL 8.4
• Boolean Full-Text Searches, MySQL 8.4
• InnoDB Full-Text Indexes, MySQL 8.4
• Full-Text Stopwords, MySQL 8.4
• Fine-Tuning MySQL Full-Text Search
• queryExecute(), dokumentasi Lucee
• Tag cfquery, dokumentasi Lucee