18. 使用MySQL之全文字搜尋

hisun9發表於2024-11-05
MySql

1. 理解全文字搜尋

注意：

  • 並非所有引擎都支援全文字搜尋：

    正如第21章所述，MySQL支援幾種基本的資料庫引擎。並非所有的引擎都支援這裡所描述的全文字搜尋。

    兩個最常使用的引擎為MyISAM和InnoDB，前者支援全文字搜尋，而後者不支援。這就是為什麼雖然本書中建立的多數樣例表使用 InnoDB ，而有一個樣例表（productnotes表）卻使用MyISAM的原因。

    如果你的應用中需要全文字搜尋功能，應該記住這一點。

第8章介紹了LIKE關鍵字，它利用通配運算子匹配文字（和部分文字）。使用LIKE，能夠查詢包含特殊值或部分值的行（不管這些值位於列內什麼位置）。

在第9章中，用基於文字的搜尋作為正規表示式匹配列值的更進一步的介紹。使用正規表示式，可以編寫查詢所需行的非常複雜的匹配模式。

雖然這些搜尋機制非常有用，但存在幾個重要的限制：

  • 效能 —— 萬用字元和正規表示式匹配通常要求MySQL嘗試匹配表中所有行（而且這些搜尋極少使用表索引）。因此，由於被搜尋行數不斷增加，這些搜尋可能非常耗時。

  • 明確控制 —— 使用萬用字元和正規表示式匹配，很難（而且並不總是能）明確地控制匹配什麼和不匹配什麼。例如，指定一個詞必須匹配，一個詞必須不匹配，而一個詞僅在第一個詞確實匹配的情況下才可以匹配或者才可以不匹配。

  • 智慧化的結果 —— 雖然基於萬用字元和正規表示式的搜尋提供了非常靈活的搜尋，但它們都不能提供一種智慧化的選擇結果的方法。例如，一個特殊詞的搜尋將會返回包含該詞的所有行，而不區分包含單個匹配的行和包含多個匹配的行（按照可能是更好的匹配來排列它們）。類似，一個特殊詞的搜尋將不會找出不包含該詞但包含其他相關詞的行。

所有這些限制以及更多的限制都可以用全文字搜尋來解決。在使用全文字搜尋時，MySQL不需要分別檢視每個行，不需要分別分析和處理每個詞。MySQL建立指定列中各詞的一個索引，搜尋可以針對這些詞進行。這樣，MySQL可以快速有效地決定哪些詞匹配（哪些行包含它們），哪些詞不匹配，它們匹配的頻率，等等。

2. 使用全文字搜尋

為了進行全文字搜尋，必須索引被搜尋的列，而且要隨著資料的改變不斷地重新索引。在對錶列進行適當設計後，MySQL會自動進行所有的索引和重新索引。

在索引之後，SELECT可與Match()和Against()一起使用以實際執行
搜尋。

2.1 啟用全文字搜尋

一般在建立表時啟用全文字搜尋。CREATE TABLE語句（第21章中介紹）接受FULLTEXT子句，它給出被索引列的一個逗號分隔的列表。

比如：

create table productnotes
(
	note_id		int			not null auto_increment,
    prod_id		char(10)	not null,
    note_date	datetime	not null,
    note_text	text		null,
    primary key(note_id),
    fulltext(note_text)
)engine=MyISAM;

第21章將詳細考察CREATE TABLE語句。現在，只需知道這條CREATE TABLE語句定義表productnotes並列出它所包含的列即可。這些列中有一個名為note_text的列，為了進行全文字搜尋，MySQL根據子句FULLTEXT(note_text)的指示對它進行索引。這裡的FULLTEXT索引單個列，如果需要也可以指定多個列。

在定義之後，MySQL自動維護該索引。在增加、更新或刪除行時，索引隨之自動更新。

可以在建立表時指定FULLTEXT，或者在稍後指定（在這種情況下所有已有資料必須立即索引）。

注意：

  • 不要在匯入資料時使用FULLTEXT：

    更新索引要花時間，雖然不是很多，但畢竟要花時間。如果正在匯入資料到一個新表，此時不應該啟用FULLTEXT索引。應該首先匯入所有資料，然後再修改表，定義FULLTEXT。這樣有助於更快地匯入資料（而且使索引資料的總時間小於在匯入每行時分別進行索引所需的總時間）。

2.2 進行全文字搜尋

在索引之後，使用兩個函式Match()和Against()執行全文字搜尋，

其中Match()指定被搜尋的列，Against()指定要使用的搜尋表示式。

  • 比如：

    select note_text
from productnotes
where match(note_text) against('rabbit');

    輸出如下：

    img

    此SELECT語句檢索單個列note_text。由於WHERE子句，一個全文字搜尋被執行。

    Match(note_text)指示MySQL針對指定的列進行搜尋，

    Against('rabbit')指定詞rabbit作為搜尋文字。由於有兩行包含詞rabbit，這兩個行被返回。

    補充：

    • 使用完整的 Match() 說明：

      傳遞給 Match() 的值必須與FULLTEXT()定義中的相同。如果指定多個列，則必須列出它們（而且次序正確）。

    • 搜尋不區分大小寫：

      除非使用BINARY方式（本章中沒有介紹），否則全文字搜尋不區分大小寫。

  • 事實是剛才的搜尋可以簡單地用LIKE子句完成，如下所示：

    select note_text
from productnotes
where note_text like '%rabbit%';

    輸出如下：

    img

    這條SELECT語句同樣檢索出兩行，但次序不同（雖然並不總是出現這種情況）。

  • 上述兩條SELECT語句都不包含ORDER BY子句。後者（使用LIKE）以不特別有用的順序返回資料。前者（使用全文字搜尋）返回以文字匹配的良好程度排序的資料。兩個行都包含詞rabbit，但包含詞rabbit作為第3個詞的行的等級比作為第20個詞的行高。

    這很重要。全文字搜尋的一個重要部分就是對結果排序。具有較高等級的行先返回（因為這些行很可能是你真正想要的行）

    為演示排序如何工作，請看以下例子：

    select note_text,
        match(note_text) against('rabbit') as rank
from productnotes;

    輸出如下：

    img

    這裡，在SELECT而不是WHERE子句中使用Match()和Against()。這
    使所有行都被返回（因為沒有WHERE子句）。

    Match()和Against()用來建立一個計算列（別名為rank），此列包含全文字搜尋計算出的等級值。

    等級由MySQL根據行中詞的數目、唯一詞的數目、整個索引中詞的總數以及包含該詞的行的數目計算出來。

    正如所見，不包含詞rabbit的行等級為0（因此不被前一例子中的WHERE子句選擇）。確實包含詞rabbit的兩個行每行都有一個等級值，文字中詞靠前的行的等級值比詞靠後的行的等級值高。

    這個例子有助於說明全文字搜尋如何排除行（排除那些等級為0的行），如何排序結果（按等級以降序排序）。

    補充：

    排序多個搜尋項：

    如果指定多個搜尋項，則包含多數匹配詞的那些行將具有比包含較少詞（或僅有一個匹配）的那些行高的等級值。

  • 正如所見，全文字搜尋提供了簡單LIKE搜尋不能提供的功能。而且，由於資料是索引的，全文字搜尋還相當快。

2.3 使用查詢擴充套件

查詢擴充套件用來設法放寬所返回的全文字搜尋結果的範圍。

考慮下面的情況。你想找出所有提到anvils的註釋。只有一個註釋包含詞anvils，但你還想找出可能與你的搜尋有關的所有其他行，即使它們不包含詞anvils。

這也是查詢擴充套件的一項任務。在使用查詢擴充套件時，MySQL對資料和索引進行兩遍掃描來完成搜尋：

  • 首先，進行一個基本的全文字搜尋，找出與搜尋條件匹配的所有行；

  • 其次，MySQL檢查這些匹配行並選擇所有有用的詞（我們將會簡要地解釋MySQL如何斷定什麼有用，什麼無用）。

  • 再其次，MySQL再次進行全文字搜尋，這次不僅使用原來的條件，而且還使用所有有用的詞。

利用查詢擴充套件，能找出可能相關的結果，即使它們並不精確包含所查詢的詞。

比如：

  • 首先進行一個簡單的全文字搜尋，沒有查詢擴充套件：

    select note_text
from productnotes
where match(note_text) against('anvils');

    輸出如下：

    img

    只有一行包含詞anvils，因此只返回一行。

  • 下面是相同的搜尋，這次使用查詢擴充套件：

    select note_text
from productnotes
where match(note_text) against('anvils' with query expansion);

    輸出如下：

    img

    這次返回了7行。第一行包含詞anvils，因此等級最高。第二行與anvils無關，但因為它包含第一行中的兩個詞（customer和recommend），所以也被檢索出來。第3行也包含這兩個相同的詞，但它們在文字中的位置更靠後且分開得更遠，因此也包含這一行，但等級為第三。第三行確實也沒有涉及anvils（按它們的產品名）。

  • 正如所見，查詢擴充套件極大地增加了返回的行數，但這樣做也增加了實際上並不想要的行的數目。

補充：

行越多越好：

表中的行越多（這些行中的文字就越多），使用查詢擴充套件返回的結果越好。

2.4 布林文字搜素

MySQL支援全文字搜尋的另外一種形式，稱為布林方式（boolean mode）。

以布林方式，可以提供關於如下內容的細節：

  • 要匹配的詞；

  • 要排斥的詞（如果某行包含這個詞，則不返回該行，即使它包含
    其他指定的詞也是如此）；

  • 排列提示（指定某些詞比其他詞更重要，更重要的詞等級更高）；

  • 表示式分組；

  • 另外一些內容。

補充：

  • 即使沒有FULLTEXT索引也可以使用：

    布林方式不同於迄今為止使用的全文字搜尋語法的地方在於，即使沒有定義
    FULLTEXT索引，也可以使用它。但這是一種非常緩慢的操作（其效能將隨著資料量的增加而降低）

為演示IN BOOLEAN MODE的作用，舉一個簡單的例子：

select note_text
from productnotes
where match(note_text) against('heavy' in boolean mode);

輸出如下：

img

此全文字搜尋檢索包含詞heavy的所有行（有兩行）。其中使用了關鍵字IN BOOLEAN MODE，但實際上沒有指定布林運算子，因此，其結果與沒有指定布林方式的結果相同。

補充：

  • IN BOOLEAN MODE的行為差異：

    雖然這個例子的結果與沒有IN BOOLEAN MODE的相同，但其行為有一個重要的差別（即使在這個特殊的例子沒有表現出來）。我們將在後面（2.5）指出。

為了匹配包含heavy但不包含任意以rope開始的詞的行，可使用以下查詢：

select note_text
from productnotes
where match(note_text) against('heavy -rope*' in boolean mode);

輸出如下：

img

這次只返回一行。這一次仍然匹配詞heavy，但-rope*明確地指示MySQL排除包含rope*（任何以rope開始的詞，包括ropes）的行，這就是為什麼上一個例子中的第一行被排除的原因。

我們已經看到了兩個全文字搜尋布林運算子-和*，-排除一個詞，而*是截斷運算子（可想象為用於詞尾的一個萬用字元）。

表18-1列出支援的所有布林運算子。

img

下面舉幾個例子，說明某些運算子如何使用：

  • 第一個例子

    select note_text
from productnotes
where match(note_text) against('+rabbit +bait' in boolean mode);

    輸出如下：

    img

    這個搜尋匹配包含詞rabbit和bait的行。

  • 第二個例子

    select note_text
from productnotes
where match(note_text) against('rabbit bait' in boolean mode);

    輸出如下：

    img

    沒有指定運算子，這個搜尋匹配包含rabbit和bait中的至少一個詞的行。

  • 第三個例子

    select note_text
from productnotes
where match(note_text) against('"rabbit bait"' in boolean mode);

    輸出如下：

    img

    這個搜尋匹配短語rabbit bait而不是匹配兩個詞rabbit和bait。

  • 第四個例子

    select note_text
from productnotes
where match(note_text) against('>rabbit <carrot"' in boolean mode);

    輸出如下：

    img

    匹配rabbit和carrot，增加前者的等級，降低後者的等級。

  • 第五個例子

    select note_text
from productnotes
where match(note_text) against('+safe +(<combination)' in boolean mode);

    輸出如下：

    img

    這個搜尋匹配詞safe和combination，降低後者的等級。

補充：

排列而不排序：

在布林方式中，不按等級值降序排序返回的行。

2.5 全文字搜尋的使用說明

給出關於全文字搜尋的某些重要的說明。

  • 在索引全文字資料時，短詞被忽略且從索引中排除。短詞定義為那些具有3個或3個以下字元的詞（如果需要，這個數目可以更改）。

  • MySQL帶有一個內建的非用詞（stopword）列表，這些詞在索引全文字資料時總是被忽略。如果需要，可以覆蓋這個列表（請參閱MySQL文件以瞭解如何完成此工作）。

  • 許多詞出現的頻率很高，搜尋它們沒有用處（返回太多的結果）。因此，MySQL規定了一條50%規則，如果一個詞出現在50%以上的行中，則將它作為一個非用詞忽略。50%規則不用於IN BOOLEAN MODE。

  • 如果表中的行數少於3行，則全文字搜尋不返回結果（因為每個詞
    或者不出現，或者至少出現在50%的行中）。

  • 忽略詞中的單引號。例如，don't索引為dont。

  • 不具有詞分隔符（包括日語和漢語）的語言不能恰當地返回全文
    本搜尋結果。

  • 如前所述，僅在MyISAM資料庫引擎中支援全文字搜尋。

相關文章