mysql 全文索引學習筆記詳解

情境：需要做一個關於標題的模糊查詢，只是記錄有點多，而且需要相對精確，比如搜尋：ac， 不能出現abc，可以接受acb,bac,之類。

mysql全文檢索搜尋有三種模式：

一、自然語言尋找。這是mysql預設的全文檢索搜尋方式，sql樣本：
[code=plain]

代碼如下	複製代碼
select  id,title FROM post WHERE MATCH(content) AGAINST ('search keyword')

或者顯式聲明使用自然語言搜尋方式
[code=plain]

代碼如下	複製代碼
select  id,title FROM post WHERE MATCH(content) AGAINST ('search keyword' IN NATURAL LANGUAGE MODE)

測試：

1、100萬資料，mysql / mongo ，在這種情況下。無論是查詢什麼資料，基本上都在0.00x秒級，
mysql的查詢是like '%xxxx%' , mongo 是 {title:/xxxx/i}
一般情況下，兩者速度真心差不多，但如果查詢一下不資料庫中不存在的關鍵字，一般都在0.2秒至2秒左右，mongo會相對好一點，在0.5秒
 
2、500萬～1000萬資料
查詢條件如上
mySQL 查詢的時候 cpu 佔40%左右，20多秒 （mysql 1100萬資料）
mongo 查詢的時候 CPU佔50%左右，10秒/8秒左右 （mongo 550萬）
這種效能沒法用啊
 
---下一步
1、xunsearch / coreseek(sphinx)
2、mysql 全文索引
 
需要再次測試一下。關鍵mysql雖然100萬隻有0.00x或者0.0x秒左右。但是如果多個並發的時候就會卡死了。
所以需要再次考慮 情境的複雜性

繼昨天的處理之後，又來新的筆記 ，這次的筆記純粹是個人的測試，與實際條件有關，比如，我要查詢的欄位不超過varchar的255的長度，所以我才這麼做。

昨天做普通索引後，1100萬條記錄，索引 為220M，改成全文索引後，索引檔案為1.1G，儲存空間上，漲了5倍左右。

以下是筆記 ，請不要笑話，情境不同而已
 

•經過測試

•title 欄位改為全文索引後，在1100萬條的時候
•優點：
•速度也為0.0x秒級。速度非常快
•即使有or條件，只要帶了limit參數，速度也非常快
•缺點：
•如果查詢不帶limit ,直接卡死，因為他要計算total count
•select count() 卡死
•如果查詢不存在的關鍵字，卡死
•使用方法
•盡量不做select count 查詢 （數量低於100萬時可以考慮，超過100萬時，其實已經沒有必要）
•查詢一定要帶上limit條件
•每次查詢到不存在的關鍵字時，記錄到關鍵詞庫，每次有新增記錄時，select 關鍵詞庫一下，如果新增房間中有關鍵字，則將關鍵詞去除，避免卡死
•暫時不使用coreseek（sphinx）/xunsearch等第三方工具
•xunsearch只支援分詞查詢，不支援完全符合
•第三方工具，耗記憶體，而且增量的時候，不夠及時

費話說了一大篇下面進入測試

一個SELECT查詢中的LIKE語句來執行這種查詢，儘管這種方法可行，

但對於全文尋找而言，這是一種效率極端低下的方法，尤其在處理大量資料的時候。

-------------------
上面這句話 我是在網上看到的， 說得挺有道理的， mysql 本身提供了一種叫做全文檢索搜尋的技術吧，

不過這好像從後來的版本才有的， 比較舊的版本不支援，不過那是很早期的版本了，

現在大家使用的版本 應該都支援的。我現在使用 mysql6.0.4 來示範

全文檢索搜尋 比起 索引 我覺得更加全面， 索引只對某一個欄位，然後在查詢時候使用 like 配合。

全文檢索搜尋它可以設定多個欄位進行搜尋，可以說是比起 select .... like 進階吧。

好了， 既然 全文檢索搜尋 有這樣的優點，下面我們來看看 是否真的如此。

下面提供的測試例子，是 mysql 手冊上面的一個例子

代碼如下	複製代碼
CREATE TABLE articles (    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,    title VARCHAR(200),     body TEXT,      FULLTEXT (title,body)    );

上面這是建立表的 mysql 語句， 其中最後一句  FULLTEXT (title,body)

就是為 title 和 body 建立一個 全文檢索搜尋，也就是 以後 方便搜尋 標題 和

標題本文的內容。複製語句上去 就成功建立一張表了。
查看一下 全文檢索搜尋 是否被建立， 下面語句作用是：查看錶的 主鍵， 索引， 全文檢索搜尋

show indexes from 表名

代碼如下

複製代碼

mysql> show indexes from articles; +----------+------------+----------+--------------+-------------+ | Table    | Non_unique | Key_name | Seq_in_index | Column_name | +----------+------------+----------+--------------+-------------+ | articles |          0 | PRIMARY  |            1 | id          | | articles |          1 | title    |            1 | title       | | articles |          1 | title    |            2 | body        | +----------+------------+----------+--------------+-------------+ 3 rows in set (0.01 sec)

可以看到 成功建立了， Key_name名字叫做 title ， 它的欄位列名Column_name是 title 和 body

=================

下面為 表 添加資料內容， 好讓我們進行測試。

代碼如下

複製代碼

INSERT INTO articles (title,body) VALUES     ('MySQL Tutorial','DBMS stands for DataBase ...'),     ('How To Use MySQL Well','After you went through a ...'),     ('Optimizing MySQL','In this tutorial we will show ...'),     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),     ('MySQL vs. YourSQL','In the following database comparison ...'),     ('MySQL Security','When configured properly, MySQL ...'); mysql> select * from articles; +----+-----------------------+------------------------------------------+ | id | title                 | body                                     | +----+-----------------------+------------------------------------------+ |  1 | MySQL Tutorial        | DBMS stands for DataBase ...             | |  2 | How To Use MySQL Well | After you went through a ...             | |  3 | Optimizing MySQL      | In this tutorial we will show ...        | |  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      | |  5 | MySQL vs. YourSQL     | In the following database comparison ... | |  6 | MySQL Security        | When configured properly, MySQL ...      | +----+-----------------------+------------------------------------------+ 6 rows in set (0.00 sec)

添加好資料庫， 下面我們就使用 全文檢索搜尋 提供的 查詢語句 進行測試。

=================================================

使用語句的模版如下：

SELECT 表欄位 FROM 表名 WHERE MATCH (全文檢索搜尋表欄位) AGAINST ('搜尋字串');

下面 搜尋 title 和 body 包含 database 這個字串

代碼如下

複製代碼

mysql> SELECT * FROM articles     ->     ->     WHERE MATCH (title,body) AGAINST ('database'); +----+-------------------+------------------------------------------+ | id | title             | body                                     | +----+-------------------+------------------------------------------+ |  5 | MySQL vs. YourSQL | In the following database comparison ... | |  1 | MySQL Tutorial    | DBMS stands for DataBase ...             | +----+-------------------+------------------------------------------+ 2 rows in set (0.00 sec)

MATCH 相當於要找的列， 而  AGAINST 就是要找的內容。

比起 like 有點不一樣

而且 match ... against 還提供很多 操作， 對資料 進一步過濾，

一般可以作為 比較精確的搜尋，

例如下面的這個例子： 搜尋title和body中包含 MySQL ，但是不能有 YourSQL 的結果。

代碼如下	複製代碼
SELECT * FROM articles WHERE MATCH (title,body)     AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE); ------------------ |  5 | MySQL vs. YourSQL     | In the following database comparison ... |

你會發現 可以看到 這個結果被過濾掉了

fulltext 還提供了 更加多的邏輯搜尋，也就是一些 模糊搜尋 等等，要求更加高的
匹配字元搜尋， 大家可以上 mysql 官方網站查看。

=============================================================

上面 建立全文檢索搜尋 是在 建立表的同時建立的， 如果你已經建好表了， 但是想加入
這個功能的話， 可以使用下面語句

代碼如下	複製代碼
mysql> alter table articles add fulltext index(title,body);

--------------------

為了示範，我先把剛才的刪除掉

代碼如下	複製代碼
mysql> drop index title on articles;

再查看一下， 已經被刪除了。

代碼如下	複製代碼
mysql> show indexes from articles;

再為表添加 fulltext 上去

代碼如下	複製代碼
mysql> alter table articles add fulltext index(title,body);

看到了吧， 這樣就通過修改現有的表來添加全文檢索搜尋功能， 希望這個教程可以幫你
日後使用到這個搜尋功能。