Mysql 全文本檢索,Mysql全文檢索索引
mysql 全文索引
注意 並非所有的引擎都支援 全文檢索索引
mysql最常用的引擎 INnodb 和 myisam 後者支援全文檢索索引 前者不支援
建立表的時候指定要檢索列
CREATE TABLE TEST_FULLTEXT(note_id int not null auto_increment,note_text text null,primaty key(note_id),FULLTEXT(note_text))engine=myisam;
fulltext 索引某個列 fulltext(note_text) ,在某note_text列上建立全文索引
插入資料
然後用 match()指定列 Against()指定詞
如 語句
select *from TEST_FULLTEXTwhere Match(note_text) Against('hello');
尋找note_txt列中含有 hello詞的行 返回的結果為 兩行
note_text'hello' was said by quester quster say 'hello' to pp and he try again
- 注意 搜尋是不區分大小的 除非使用 BINARY方式
既然這樣 為什麼 不用 like語句呢 再來看上面例子 用like實現
select *from TEST_FULLTEXTwhere note_text like '%hello%';
返回的結果一樣為兩行
note_textquster say 'hello' to pp and he try again'hello' was said by quester
看採用全文檢索搜尋和like的返回結果 使用全文檢索搜尋的返回結果是已經排好序的 而 like的返回結果則沒有排序主要是針對 hello出現在行的位置 全文結果中 第一個詞 和 第三個詞 like則沒有按順序排
Mysql主要根據等級來進行排序
我們可以採用下面方式查看 表中某一列 在某一個詞的等級 ,繼續用上面的例子
select note_text, Match(note_text) Aginst('hello') as rannkfrom TEST_FULLTEXT
輸出如下:
note_text rank fhgjkhj 0 fdsf shi jian 0 quster say 'hello' to pp and he try again 1.3454876123454 huijia quba 0 'hello' was said by quester 1.5656454547876
等級的計算 由 mysql 由根據行中詞的數目、唯一詞的數目、整個索引中詞的總數以及包含改詞行的數目計算出來 不包含詞的行的等級 為0 上面的結果中 詞在前面的等級值要高於在後面的
使用查詢擴充
當你想要在note_text 中尋找 pp時 從上面知道 只有一行 如果用下面語句
select note_text from test_fulltextwhere match(note_text) against('pp');
返回結果是
note_textquster say 'hello' to pp and he try again
如果採用擴充查詢,分為以下三部
- 1、先根據全文檢索索引 尋找到 所有行 如上面的返回結果 只有一行
- 2、mysql檢索上面1的所以行,選擇有用的詞
- 3、mysql再次全文檢索索引,這一次還需要加上2中選擇出來的有用的詞 作為against中的詞
select note_text from test_fulltextwhere match(note_text) against('pp' with query expansion);
返回結果
note_textquster say 'hello' to pp and he try again'hello' was said by quester
如pp本來有的行中含有 hello 所以hello也作為關鍵字
使用布爾查詢
即使沒有建立fulltext索引也能夠用,但是速度非常慢 沒有50%規則 (參見下 50%規則介紹)可以用包含特定意義的操作符,如 +、-、"",作用於查詢字串上。查詢結果不是以相關性排序的。
如語句
select note_text from test_fulltextwhere match(note_text) against('hello -pp*' IN BOOLEAN MODE );
表示匹配hello但是不包含 pp的行 結果為
note_text'hello' was said by quester
全文檢索索引的一些說明 和限制
- 1、只有MyISAM表支援
- 2、對大多數的多位元組字元集適用,進行全文索引的列必須使用相同的字元集和校正碼(collation)。
- 3、表意性語言,如漢語、日語沒有詞分界符(英語用空格隔開每個單詞),全文分析器無法確定一個詞的開始和結尾,所以MySQL中的全文檢索索引不支援。
- 4、在自然語言檢索中,只能檢索被全文索引的那些列,如果要對索引的多列進行某一列的檢索,必須對這一列單獨建立全文索引。布爾檢索可以在非索引的列上進行,但會慢一些。
- 5、against後的參數必須是常量字串。
- 6、索引沒有記錄關鍵詞在字串中的位置,排序演算法太單一。
- 7、如果索引不在記憶體中,檢索速度會很慢;如果是短語查詢,需要索引和資料都在記憶體中,否則速度會很慢,所以需要更大的key buffer。索引有片段時也會很慢,所以需要更頻繁的optimize table操作。
- 8、全文索引對於insert、update、delete都很慢。如更改100個詞需要進行100次的索引操作而不是1次。
50% 規則
如果一個詞出現在50%以上的行中,那麼mysql將他作為一個非用詞忽略 50%規則不適用於布爾查詢 如果行數小於三行 則不返回結果 參考 50%規則