MySQL全文索引應用簡明教程

發表於2016-10-18
MySql索引

本文從以下幾個方面介紹下MySQL全文索引的基礎知識：

  1. MySQL全文索引的幾個注意事項
  2. 全文索引的語法
  3. 幾種搜尋型別的簡介
  4. 幾種搜尋型別的例項

全文索引的幾個注意事項

  • 搜尋必須在型別為fulltext的索引列上，match中指定的列必須在fulltext中指定過
  • 僅能應用在表引擎為MyIsam型別的表中（MySQL 5.6以後也可以用在Innodb表引擎中了）
  • 僅能再char、varchar、text型別的列上面建立全文索引
  • 像普通索引一樣，可以在定義表時指定，也可以在建立表後新增或者修改
  • 對於一個大數量級記錄插入，向沒有索引的表中插入資料後建立索引比向有索引的資料表中插入的過程要快很多
  • 搜尋字串必須是一個常量字串，不能是表的列名
  • 在搜尋記錄的選擇性超過50%的時候，認為沒有匹配（只在自然搜尋中限制）

全文索引搜尋語法

MATCH (列名1, 列名2,…) AGAINST (搜尋字串 [搜尋修飾符])

其中在match裡面指定的列名1、2等，就是在建立全文索引中指定的列名， 後面的搜尋修飾符說明如下：

search_modifier:

{

IN NATURAL LANGUAGE MODE

| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

| IN BOOLEAN MODE

| WITH QUERY EXPANSION

}

幾種搜尋型別的簡介

上面的搜尋修飾符，實際上說明了3種全文搜尋型別

IN NATURAL LANGUAGE MODE

簡介：預設的搜尋形式（不加任何搜尋修飾符或者修飾符為 IN NATURAL LANGUAGE MODE 的情況）

特點：

  1. 對於搜尋字串中的字元都解析為正常的字元，沒有特殊意義
  2. 對遮蔽字元列表中的字串進行過濾
  3. 當記錄的選擇性超過50%的時候，通常被認為是不匹配。
  4. 返回記錄按照記錄的相關性進行排序顯示

IN BOOLEAN MODE

簡介：布林模式搜尋（搜尋修飾符為IN BOOLEAN MODE的情況）

特點：

  1. 會按照一定的規則解析搜尋字串中的特殊字元的含義，進行一些邏輯意義的規則。如：某個單詞必須出現，或者不能出現等。
  2. 這種型別的搜尋返回的記錄是不按照相關性進行排序的

WITH QUERY EXPANSION

簡介：一種稍微複雜的搜尋形式，實際上是進行了2次自然搜尋，可以返回記錄直接簡介性關係的記錄，修飾詞IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 或者WITH QUERY EXPANSION modifier

特點：這種型別的搜素，實際上提供了一種間接的搜尋功能，比如：我搜尋某個詞，而且返回的第一行中卻不包含搜尋詞中的任意字串。可以根據第一次搜尋結果的記錄詞進行第二次匹配，從而可能找到一些間接關係的匹配記錄。

幾種搜尋型別的例項介紹

IN NATURAL LANGUAGE MODE模式下的應用： 

還是應用在product表，其中在name欄位我們建立了全文索引，因為我需要根據關鍵詞在name列匹配出相關記錄

Sql語句如下：

SELECT * FROM product WHERE match(name) against(‘auto’)

時間還不錯，在將近87w的記錄中命中了1w多條，耗時1.15秒，效果還是不錯

注意：在預設的情況下已經是根據相關性從高到低返回記錄了

我們可以SELECT match(name) against(‘auto’) FROM product 檢視記錄的相關性值，值都在0和1之間， 0代表記錄不匹配

 重要的幾個特性：

1. 哪些詞會被忽略

搜尋詞太短 預設全文索引認為4個以上字元的單詞是有效詞，我們可以在配置中修改ft_min_word_len進行配置

遮蔽詞表中的詞 預設的全文索引將一些常用詞遮蔽掉，因為這些詞太常見了，沒有任何語義作用，所以搜尋過程中是忽略不計的。當然這個列表也是可以配置的。

2. 如何進行分詞的

全文索引認為一個連續的有效字元（正則中\w匹配的字符集）是一個單詞，也可以包含一個“’”， 但是連續的兩個’會被認為是一個分隔符。其他的分隔符如：空格、逗號、句點等

IN BOOLEAN MODE 模式下的應用：

在布林匹配模式中，我們可以新增一些特殊的符號，增加一些搜尋過程的邏輯功能。如官方網站中提供的例項（搜尋含有mysql字串 且 不含Yousql的語句）：

 SELECT * FROM articles WHERE MATCH (title,body)

-> AGAINST (‘+MySQL -YourSQL’ IN BOOLEAN MODE);

可見，我們對搜尋的控制又多了一些，看起來“高大上”了些。

實際上，上面的操作隱含了幾個意思：

  • 加號：相當於and
  • 減號：相當於not
  • 沒有：相當於or

下面看下布林型別搜尋的幾個重要特性： 

  • 1. 沒有50%記錄選擇性的限制，即使搜尋結果記錄超過總數的50%也同樣返回結果
  • 2. 不會自動的按記錄的相關性進行降序排序
  • 3. 可以直接應用在沒有建立fulltext的全文索引上，但是這樣會查詢的非常慢，所以說還是別用了。
  • 4. 支援最小、最大單詞長度
  • 5. 應用遮蔽詞列表

布林搜尋支援的操作符：

  • n 加號 +：指示修飾的單詞必須出現在記錄中
  • n 減號 -：指示修飾的單詞必須不能出現在記錄中
  • n 沒有任何操作符：單詞可有可無，但是包含該詞的記錄相關性高
  • n 雙引號 “ ： 將一個片語作為一個匹配。如：”one word” 匹配one word在一起的單詞

下面是官方的一些例項：

至少包含一個詞的記錄

‘apple banana’

必須包含著兩個詞

‘+apple +juice’

必須包含apple，包含macintosh的記錄相關性高，也可以不包含

‘+apple macintosh’

必須包含apple且不能喊有macintosh

‘+apple -macintosh’

查詢apple開頭單詞的記錄

‘apple*’

完整匹配some words單詞

‘”some words”‘

瞭解了基本的mysql全文索引知識，覺得它的全文索引比like當然是強了很多。但是面對高階的搜尋還是略顯簡陋，且效能問題也是擔憂。

本人只是作為入門瞭解，也是對官網一些基本知識的翻譯。

相關文章