MyBlog: MySQL 全文索引搜尋 Fulltext Index Search

http://www.sosauce.com/entry/6444/mysql%E5%85%A8%E6%96%87%E7%B4%A2%E5%BC%95%E8%88%87%E6%90%9C%E5%B0%8B/

http://dev.mysql.com/doc/refman/5.1/en/fulltext-search.html

http://onlamp.com/onlamp/2003/06/26/fulltext.html

http://hi.baidu.com/gazi/blog/item/ebbd2e73f29fb2198701b0c5.html

（玩半天，結果不支援中文！）

MySQL從3.23.23開始就逐漸支援全文索引和搜尋。
- 全文索引就是建index，全文搜尋就是去查index。
- LIKE是用Regular Expression去做查詢。

MySQL全文索引是一種index type：FULLTEXT。

全文索引的index只能用在MyISAM表格的char、varchar和text的欄位上。

全文索引的index可以在create table、alter table和create index時產生。
- create table...

CREATE TABLE article (
   id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT(title, body)
) TYPE=MYISAM;

- alter table...
- create index...

要倒大量的資料到有全文索引index的table速度會很慢，建議先拿掉全文索引index再倒資料，倒完後再加上全文索引index。

全文搜尋的語法：

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

三種搜尋方式：
- IN BOOLEAN MODE
- IN NATURAL LANGUAGE MODE
- IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION

IN NATURAL LANGUAGE MODE
- expr就是要搜尋的字串。
- 沒有特殊字元。
- 套用Stopwords。
- 剔
  除一半row以上都有的字，譬如說，每個row都有mysql這個字的話，那用mysql去查時，會找不到任何row，這在row的數量無敵多時很有用，
  因為把所有row都找出來是沒有意義的，這時，mysql幾乎被當作是stopword；但是當row只有兩筆時，是啥鬼也查不出來的，因為每個字都出現
  50%以上，要避免這種狀況，請用IN BOOLEAN MODE。
- 預設的搜尋方式。

SELECT *
FROM article
WHERE MATCH(title, body)
AGAINST ('xxx' IN NATURAL LANGUAGE MODE);

- 預設搜尋是不分大小寫，若要分大小寫，columne的character set要從utf8改成utf8_bin。
- 預設MATCH...AGAINST是以相關性排序，由高到低。
- MATCH...AGAINST可以跟所有MySQL語法搭配使用，像是JOIN或是加上其他過濾條件。

-- 第一種count
SELECT COUNT(*)
FROM article
WHERE MATCH(title, body)
AGAINST ('xxx' IN NATURAL LANGUAGE MODE);

-- 第二種count
SELECT COUNT(IF(MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE), 1, NULL)) AS count
FROM article

- 當符合的筆數較多時，第一種count比較慢，因為MATCH...AGAINST會先依相關性排序。
- 當符合的筆數較少時，第二種count比較慢，因為第二種count會掃過所有資料。
- MATCH(title,
  body)裡的欄位必須和FULLTEXT(title,
  body)裡的欄位一模一樣，如果只要單查title或body一個欄位，那得另外再建一個FULLTEXT(title)或
  FULLTEXT(body)，也因為如此，MATCH()的欄位一定不能跨table，但是另外兩種搜尋方式好像可以。

SELECT id, MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE) as score
FROM article;

- 這樣可以取得相關值，而且也因為沒有WHERE和ORDER BY，所以不會排序。

SELECT id, MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE) as score
FROM article
WHERE MATCH(title, body)
AGAINST ('xxx' IN NATURAL LANGUAGE MODE);

- 排序又取得相關性，雖然MATCH...AGAINST用了兩次，但是MySQL知道這兩個MATCH...AGAINST是一樣的，所以只會用一次。

SELECT id, MATCH(title, body) AGAINST ('xxx' IN NATURAL LANGUAGE MODE) as score
FROM article
ORDER BY score desc;

- 為啥不這樣用就好？
- MySQL的FULLTEXT怎麼斷字：
  - 字母、數字、底線的組合視為一個字，不會把底線斷字。
  - 會被斷字的字元：空白、逗號（,）與點（.），但不用這些斷字的語言，如中文，就得自行手動斷字。
  - 可以自行實做一個斷字的外掛來取代內建的斷字parser。
  - 接受一個單引號，如aaa'bbb視為一個字，但是aaa''bbb就是兩個字。
  - 字首或字尾的單引號會被去掉，如'aaa或aaa'。
- 全文搜尋時，stopword與少於四個字元的字串會被忽略。
  - 可以覆寫內建的stopword清單。
  - 可以修改最少四個字元的設定。

IN BOOLEAN MODE
- expr裡有特殊字元輔助特殊的搜尋語法。

SELECT *
FROM article
WHERE MATCH(title, body)
AGAINST ('+mysql -yoursql' IN BOOLEAN MODE);

- 一定要有msysql，且不要有yoursql。
- IN BOOLEAN MODE的特色：
  - 不剔除50%以上符合的row。
  - 不自動以相關性反向排序。
  - 可以對沒有FULLTEXT index的欄位進行搜尋，但會非常慢。
  - 限制最長與最短的字串。
  - 套用Stopwords。
- 搜尋語法：
  - +：一定要有。
  - -：不可以有，但這個「不可以有」指的是在符合的row裡不可以有指定的字串，所以不能只下「-yoursql」這樣是查不到任何row的，必須搭配其他語法使用。
  - ：（什麼都沒）預設用法，表示可有可無，有的話排比較前面，沒有的排後面。
  - >：提高該字的相關性。
  - <：降低相關性。
  - ( )：條件可以巢狀。

+aaa +(>bbb <ccc) // 找到有aaa和bbb，或者aaa和ccc，然後aaa&bbb排在aaa&ccc前面

- - ~：將其相關性由正轉負，表示擁有該字會降低相關性，但不像「-」將之排除，只是排在較後面。
  - *：萬用字，不像其他語法放在前面，這個要接在字串後面。
  - " "：用雙引號將一段句子包起來表示要完全相符，不可拆字。

IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
- 也可以用WITH QUERY EXPANSION。
- IN NATURAL LANGUAGE MODE的衍生版。
- 先用IN NATURAL LANGUAGE MODE做搜尋，得到最相關的欄位的字再加到原expr裡，再查一次。
- 神奇功能之一：可以用database查出mysql或oracle，第一次查詢用databae得到一些結果，從這些結果裡抽取字串，此時得到mysql與oracle的機率相當高，最後用database和這些出取出來的字串做一次查詢。
- 神奇功能之二：無法拼出正確字串時，第一次用「相似」的錯誤字串查詢，很有可以得到正確的字串，再用正確的字串急可以得到想要的結果。
- 因為這種查詢方式會讓「雜訊」爆增，所以建議第一次的查詢字串盡量精簡。

Stopwords請參考http://dev.mysql.com/doc/refman/5.1/en/fulltext-stopwords.html。

全文搜尋的限制：
- 只能用在MyISAM表格上。
- 支援UTF-8。
- 中文支援問題：
  - MySQL不會斷中文字：MySQL內建的字依據是空白、逗號和點，對此內建機制的白痴解法是，存中文字時自行塞入空白斷字，但是還是有下面的限制。
  - 查詢字串最少四個字元的限制：所以一二三個中文字都不能查，必須將ft_min_word_len從預設的4改成1。
- 雖然同一個表格可以有不同字元集的欄位，但是同一個FULLTEXT index裡的欄位必須是同一個字元集與collation。
- MATCH裡的欄位必須和FULLTEXT裡的一模一樣，IN BOOLEAN MODE允許不一樣，甚至使用未FULLTEXT index的欄位，但速度很慢。
- AGAINST裡必須是字串，不可以是變數或欄位名稱。
- 全文搜尋使index hint受限。

MySQL全文搜尋設定：
- 大部分的參數都是啟動參數，也就是修改後必須重新啟動MySQL。
- 有些參數修改必須重新產生索引檔。

mysql> SHOW VARIABLES LIKE 'ft%';

ft_boolean_syntax    + -><()~*:""&|
ft_min_word_len    4
ft_max_word_len    84
ft_query_expansion_limit 20 ft_stopword_file    (built-in)

- ft_min_word_len：最短的索引字串，預設值為4，修改後必須重建索引檔。
- ft_max_word_len：最長的索引字串，預設值因版本而不同，餘同上一點。

[mysqld]
ft_min_word_len=1

- ft_stopword_file：stopword檔案路徑，若留空白不設定表示要停用stopword過濾，修改後必須重新啟動MySQL和重建索引；stopword檔案內容可以用分行空白與逗號區隔stopword，但底線和單引號視為合法的字串字元。
- 50%
  的門檻限制：設定檔在storage/myisam/ftdefs.h，將 #define GWS_IN_USE GWS_PROB 改為
  #define GWS_IN_USE GWS_FREQ，然後重新編譯MySQL，因為近低門檻會影響資料的精準度，所以不建議如此，可用IN
  BOOLEAN MODE即可以避開50%的限制。
- ft_boolean_syntax：改變IN BOOLEAN MODE的查詢字元，不用重新啟動MySQL也不用重建索引。
- 修改字串字元的認定，譬如說將「-」認定為字串的合法字元：
  - 方法一：修改storage/myisam/ftdefs.h的true_word_char()與misc_word_char()，然後重新編譯MySQL，最後重建索引。
  - 方法二：修改字元集檔，然後在FULLTEXT index的欄位使用該字元集，最後重建索引。
- 重建索引：
  - 每個有FULLTEXT index的表格都要這麼做。

mysql> REPAIR TABLE tbl_name QUICK;

- - 要注意如果用過myisamchk，會導致上述的設定值回覆成預設值，因為myisamchk不是用MySQL的設定值。
  - 解法一：將修改過得設定值加到myisamchk的參數裡。

shell> myisamchk --recover --ft_min_word_len=1 tbl_name.MYI

- - 解法二：兩邊都要設定。

[mysqld]
ft_min_word_len=1
[myisamchk]
ft_min_word_len=1

- - 解法三：用REPAIR TABLE、ANALYZE TABLE、OPTIMIZE TABLE與ALTER TABLE取代myisamchk語法，因為這些語法是由MySQL執行的。

MyBlog

2010年11月14日星期日

MySQL 全文索引搜尋 Fulltext Index Search

沒有留言:

張貼留言

2010年11月14日 星期日

MySQL 全文索引搜尋 Fulltext Index Search

沒有留言:

張貼留言

2010年11月14日星期日