MySQL建立全文索引學習筆記

使用索引是資料庫效能最佳化的必備技能之一。在MySQL資料庫中，有四種索引：叢集索引(主鍵索引)、普通索引、唯一索引以及我們這裡將要介紹的全文索引(FULLTEXT INDEX)。

全文索引(也稱全文檢索索引)是目前seo搜尋引擎使用的一種關鍵技術。它能夠利用“分詞技術“等多種演算法智能分析出文本文字中關鍵字詞的頻率及重要性，然後按照一定的演算法規則智能地篩選出我們想要的搜尋結果。在這裡，我們就不追根究底其底層實現原理了，現在我們來看看在MySQL中如何建立並使用全文索引。

全文索引只能在MyISAM資料表中建立 少於3個字元的單詞不會被包含在全文索引裡，可以通過修改my.cnf修改選項

ft_min_word_len=3

重新啟動MySQL伺服器，用

repair table tablename quick 為有關資料表重建全文索引
select * from tablename

where match(column1,column2) against('word1 word2 word3')>0.001
match ... against 把column1,column2資料列中至少包含word1,word2,word3三個單詞之一的資料記錄尋找到，在關鍵字match後的資料列必須跟建立全文索引的資料列相同，檢索詞不區分大小寫和先後順序，少於3個字元的單詞通常被忽略。match... against ...運算式返回一個浮點數作為它本身的求值結果，這個數字反映了結果記錄與被檢索單詞的匹配程度。如果沒有匹配到任何記錄，或者匹配到的結果記錄太多反而被忽略，運算式將返回0，運算式>0.001的作用是排除match的傳回值太小的結果記錄。

代碼如下	複製代碼
select *,match(column1,column2) against ('word1 word2 word3') as mtch from tablename having mtch>0.01 order by mtch desc limit 5

找出最匹配的5條記錄，在where字句中不能使用假名，所以用having

建立全文索

在MySQL中，建立全文索引相對比較簡單。例如，我們有一個文章表(article)，其中有主鍵ID(id)、文章標題(title)、文章內容(content)三個欄位。現在我們希望能夠在title和content兩個列上建立全文索引，article表及全文索引的建立SQL語句如下：

   

代碼如下	複製代碼
--建立article表     CREATE TABLE article (         id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,         title VARCHAR(200),         content TEXT,         FULLTEXT (title, content) --在title和content列上建立全文索引     );

上面就是在建立表的同時建立全文索引的SQL樣本。此外，如果我們想要給已經存在的表的指定欄位建立全文索引，同樣以article表為例，我們可以使用如下SQL語句進行建立：

代碼如下	複製代碼
--給現有的article表的title和content欄位建立全文索引     --索引名稱為fulltext_article     ALTER TABLE article     ADD FULLTEXT INDEX fulltext_article (title, content)

在MySQL中建立全文索引之後，現在就該瞭解如何使用了。眾所周知，在資料庫中進行模糊查詢是使用LIKE關鍵字進行查詢，例如：

代碼如下	複製代碼
SELECT * FROM article WHERE content LIKE '%查詢字串%'

那麼，我們使用全文索引也是這樣用的嗎？當然不是，我們必須使用特有的文法才能使用全文索引進行查詢。例如，我們想要在article表的title和content列中全文檢索索引指定的查詢字串，可以如下編寫SQL語句：

代碼如下	複製代碼
SELECT * FROM article WHERE MATCH(title, content) AGAINST('查詢字串')

強烈注意：MySQL內建的全文索引只能用於資料庫引擎為MyISAM的資料表，如果是其他資料引擎，則全文索引不會生效。此外，MySQL內建的全文索引只能對英文進行全文檢索索引，目前無法對中文進行全文檢索索引。如果需要對包含中文在內的文本資料進行全文檢索索引，我們需要採用Sphinx(斯芬克斯)/Coreseek技術來處理中文。本站將會在後續文章中對Sphinx以及Coreseek進行介紹
。
備忘1：目前，使用MySQL內建的全文索引時，如果查詢字串的長度過短將無法得到期望的搜尋結果。MySQL全文索引所能找到的詞的預設最小長度為4個字元。另外，如果查詢的字串包含停止詞，那麼該停止詞將會被忽略。

備忘2：如果可能，請盡量先建立表並插入所有資料後再建立全文索引，而不要在建立表時就直接建立全文索引，因為前者比後者的全文索引效率要高。