Mysql使用全文索引(FullText?index)的實例代碼

更新時間：2022年04月02日 10:27:27 作者：程序猿小哈

使用索引時數(shù)據(jù)庫性能優(yōu)化的必備技能之一,下面這篇文章主要給大家介紹了關于Mysql使用全文索引(FullText?index)的相關資料,文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下

什么是全文索引

全文索引,通過建立倒排索引,可以極大的提升檢索效率,解決判斷字段是否包含的問題. 例如: 有title字段,需要查詢所有包含 "政府"的記錄. 需要 like "%政府%"方式查詢,查詢速度慢,當查詢包含"政府" OR "中國"的需要是,sql難以簡單滿足.全文索引就可以實現(xiàn)這個功能.

倒排索引（英語：Inverted index），也常被稱為反向索引、置入檔案或反向檔案，是一種索引方法，被用來存儲在全文搜索下某個單詞在一個文檔或者一組文檔中的存儲位置的映射。它是文檔檢索系統(tǒng)中最常用的數(shù)據(jù)結構。

注意

在MySQL 5.6版本以前,只有MyISAM存儲引擎支持全文引擎.在5.6版本中,InnoDB加入了對全文索引的支持,但是不支持中文全文索引.在5.7.6版本,MySQL內置了ngram全文解析器,用來支持亞洲語種的分詞.

創(chuàng)建全文索引

創(chuàng)建表的時候創(chuàng)建

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR (200),
    body TEXT,
    FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';

創(chuàng)建了一個給title和body字段添加全文縮影的表

給字段添加全文索引 ALTER TABLE articles ADD FULLTEXT INDEX title_body_index (title,body) WITH PARSER ngram;

使用全文索引

MySQL的全文索引查詢有多種模式

自然語言搜索

普通的判斷是否包含

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神' IN NATURAL LANGUAGE MODE);
+----+-----------------+-------------------------+
| id | title           | body                    |
+----+-----------------+-------------------------+
|  1 | 弘揚正能量      | 貫徹黨的18大精神        |
+----+-----------------+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('精神');
+----+-----------------+-------------------------+
| id | title           | body                    |
+----+-----------------+-------------------------+
|  1 | 弘揚正能量      | 貫徹黨的18大精神        |
+----+-----------------+-------------------------+
1 row in set (0.00 sec)
可以看到,搜索結果命中了一條,且在不指定搜索模式的情況下,默認模式為自然語言搜索.

BOOLEAN MODE

這個模式和lucene中的BooleanQuery很像,可以通過一些操作符,來指定搜索詞在結果中的包含情況.比如 + 表示必須包含 , -表示必須不包含,默認為誤操作符,代表可以出現(xiàn)可以不出現(xiàn),但是出現(xiàn)時在查詢結果集中的排名較高一些.也就是該結果和搜索詞的相關性高一些.

具體包含的所有操作符可以通過MySQL查詢來查看:

show variables like '%ft_boolean_syntax%'

+-------------------+----------------+
| Variable_name     | Value          |
+-------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
+-------------------+----------------+
mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+精神' IN BOOLEAN MODE);
+----+-----------------+-------------------------+
| id | title           | body                    |
+----+-----------------+-------------------------+
|  1 | 弘揚正能量      | 貫徹黨的18大精神        |
+----+-----------------+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+精神 -貫徹' IN BOOLEAN MODE);
Empty set (0.01 sec)
當搜索必須命中精神時,命中了一條數(shù)據(jù),當在加上不能包含貫徹的時候,無命中結果.

強烈注意：MySql自帶的全文索引只能用于數(shù)據(jù)庫引擎為MYISAM的數(shù)據(jù)表，如果是其他數(shù)據(jù)引擎，則全文索引不會生效。此外，MySql自帶的全文索引只能對英文進行全文檢索，目前無法對中文進行全文檢索。如果需要對包含中文在內的文本數(shù)據(jù)進行全文檢索，我們需要采用Sphinx（斯芬克斯）/Coreseek技術來處理中文。

注：目前，使用MySql自帶的全文索引時，如果查詢字符串的長度過短將無法得到期望的搜索結果。MySql全文索引所能找到的詞默認最小長度為4個字符。另外，如果查詢的字符串包含停止詞，那么該停止詞將會被忽略。

注：如果可能，請盡量先創(chuàng)建表并插入所有數(shù)據(jù)后再創(chuàng)建全文索引，而不要在創(chuàng)建表時就直接創(chuàng)建全文索引，因為前者比后者的全文索引效率要高。