mysql 全文索引學習筆記詳解

全文索引是mysql中一個比較特殊的功能，下面我來給大家介紹我自己在學習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);

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