柚子快報邀請碼778899分享：全文檢索 MySql 全文索引

http://yzkb.51969.com/

MySql 全文索引

1.MySql 全文索引介紹2.ngram 簡介3.數(shù)據(jù)庫配置4.創(chuàng)建全文索引5.使用全文索引布爾模式校驗 ngram

自然語言模式拓展查詢

6.相關(guān)性排序7.注意事項

1.MySql 全文索引介紹

Mysql 的全文索引主要用于全文字段的檢索場景，支持 char、varchar、text 幾個字段加全文索引，僅支持 InNoDB 與 MyISAM 引擎。

MySql 內(nèi)置了 ngram 解析器來支持中文、日文、韓文等語言的文本，全文索引支持通過建表來創(chuàng)建或建表后新增。

全文索引支持三種模式：

布爾模式(Boolean Full-Text Searches)自然語言模式(Natural Language Full-Text Searches)查詢拓展(Full-Text Searches with Query Expansion)

2.ngram 簡介

ngram 一種基于統(tǒng)計語言模型的算法，簡單來說，就是通過一個大小為 n 的滑動窗口，將一段文本分成多個由 n 個連續(xù)單元組成的 term。例：

n = 2

text = 湖北省武漢市

經(jīng)過 ngram 解析器解析后，得到如下分詞：

湖北 北省 省武 武漢 漢市

ngram 全文解析器是 MySql 服務(wù)內(nèi)置的插件，與其他插件一樣，在 MySql 服務(wù)啟動的時候會自動加載。

參考： https://zhuanlan.zhihu.com/p/32829048《自然語言處理中N-Gram模型介紹》 https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html《ngram Full-Text Parser》

3.數(shù)據(jù)庫配置

先看 MySql 版本 ngram 解析器是 mysql5.7 版本后，內(nèi)置的全文搜索解析器，所以要求 mysql 版本要在5.7及以上 查看 mysql 版本：select version()

配置 ngram ngram 可以作為啟動字符串的一部分或者在配置文件中設(shè)置 啟動字符串：-- 中文分詞長度位2，每個字都可以查出來

mysqld --ngram_token_size=2

配置文件(my.ini)： 以 windows 系統(tǒng)為例，首先找到 my.ini 文件(默認安裝路徑：C:\ProgramData\MySQL\MySQL Server 5.7\my.ini)，編輯該文件，在文件后加上：ngram_token_size=2，配置完成后重啟服務(wù)。

4.創(chuàng)建全文索引

通過建表語句創(chuàng)建CREATE TABLE `news` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵',

`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '內(nèi)容',

`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '標(biāo)題',

PRIMARY KEY (`id`) USING BTREE,

FULLTEXT INDEX `idx_full_text`(`content`) WITH PARSER `ngram`

) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

通過 CREATE FULLTEXT INDEX 語句創(chuàng)建CREATE FULLTEXT INDEX idx_full_text ON news (content) WITH PARSER ngram;

5.使用全文索引

首先寫入部分測試數(shù)據(jù)：

INSERT INTO `news` VALUES (1, '武漢市今天有小雨，大家出門記得帶雨傘', '武漢天氣');

INSERT INTO `news` VALUES (2, '湖北省武漢市是中部最大的城市，華中地區(qū)第一大都市', '湖北省武漢市');

INSERT INTO `news` VALUES (3, '武漢大學(xué)今年不開放櫻花觀賞，請各位游客不必前往', '武大櫻花');

INSERT INTO `news` VALUES (4, '湖北省是千湖之省，風(fēng)景秀麗，歡迎全國各地游客', '湖北省旅游');

INSERT INTO `news` VALUES (5, '武漢光谷是武漢市高新技術(shù)公司聚集地，大量來自全球各地的優(yōu)秀人才聚集在此', '武漢光谷');

布爾模式

布爾模式可以使用操作符，可以支持指定關(guān)鍵詞必須出現(xiàn)或者不能出現(xiàn)或者關(guān)鍵詞的權(quán)重高低等復(fù)雜查詢。

操作符含義無操作符出現(xiàn)了，相關(guān)性會更高+必須包含-必須不包含@distance需要滿足一定的編輯距離 僅僅支持InnoDB存儲引擎<降低相關(guān)性>增加相關(guān)性~負相關(guān)性，如果包含，相關(guān)性會比不包含更低*與 like 類似，放在字段前或后“”將引號內(nèi)的內(nèi)容當(dāng)整體檢索

下面分別介紹幾種操作符的用法：

select * from news where MATCH (content) against ('武漢 雨傘' in Boolean MODE);

無操作符則表示出現(xiàn)’武漢’或者’雨傘’的數(shù)據(jù)會有更高的相關(guān)性。

select * from news where MATCH (content) against ('+武漢' in Boolean MODE);

+武漢表示必須出現(xiàn)’武漢’這個單詞數(shù)據(jù)才能被檢索到，從結(jié)果可以看出，'武漢’這個單詞出現(xiàn)次數(shù)最多的排在最前面(參考第7章節(jié))。

select * from news where MATCH (content) against ('+武漢 -雨傘' in Boolean MODE);

+武漢表示被檢索到的數(shù)據(jù)必須包含’武漢’這個分詞，-雨傘表示被檢索到的數(shù)據(jù)必須不能包含’雨傘這個分詞’。

select * from news where MATCH (content) against ('+武漢 <雨傘' in Boolean MODE);

'武漢 <雨傘’表示需要檢索到包含武漢的數(shù)據(jù)，但是當(dāng)出現(xiàn)’雨傘’這個分詞時，需要降低相關(guān)性，所以帶有’雨傘’的數(shù)據(jù)會排在最后。

select * from news where MATCH (content) against ('"全球 優(yōu)秀"@5' in Boolean MODE);

這個@distance語法不知道為啥一直沒有查出結(jié)果(待查明原因)

select * from news where MATCH (content) against ('武漢 ~雨傘' in Boolean MODE);

'武漢 ~雨傘’表示需要檢索到包含武漢的數(shù)據(jù)，但是當(dāng)出現(xiàn)’雨傘’這個分詞時，相關(guān)性為負，所以帶有’雨傘’的數(shù)據(jù)會排在最后(這個效果與<操作符有類似效果)。

select * from news where MATCH (content) against ('全球*' in Boolean MODE);

*操作符的作用其實與like的通配符類似。

select * from news where MATCH (content) against ('"武漢光谷"' in Boolean MODE);

雙引號表示’武漢光谷’以短語的方式被檢索到。

校驗 ngram

前面講到了 ngram 分詞，下面校驗一下 ngram 分詞： 以數(shù)據(jù)表的第一條文本為例：

select * from news where MATCH (content) against ('天有' in Boolean MODE);

檢索’天有’這個分詞能夠檢索出’武漢市今天有小雨，大家出門記得帶雨傘’這條文本，說明這條文本的分詞情況如下：

武漢 漢市 市今 今天 天有 有小 小雨 。。。

說明 ngram(n=2) 解析器將文本分成了以2為滑動窗口，將一段文本分成多個由2個連續(xù)單元組成的 term。

自然語言模式

自然語言模式是默認全文檢索模式，簡單來說就是把檢索關(guān)鍵詞當(dāng)作自然語言來處理，自然語言模式也等價于布爾模式中的無操作符，下面三種查詢，結(jié)果是一樣的：

-- 自然語言模式

select * from news where MATCH (content) against ('技術(shù) 武漢 光谷' IN NATURAL LANGUAGE MODE);

-- 布爾模式 無操作符

select * from news where MATCH (content) against ('技術(shù) 武漢 光谷' in Boolean MODE);

-- 默認模式

select * from news where MATCH (content) against ('技術(shù) 武漢 光谷');

這三種查詢結(jié)果和排序都是一樣的

拓展查詢

拓展查詢模式會進行兩次查詢，第一次查詢命中關(guān)鍵詞，第二次查詢會根據(jù)第一次查詢到的結(jié)果作為輸入，再進行一次查詢。 首先，我們 doc 列表如下： 先查詢關(guān)鍵詞 ‘西湖’：

select * from news where MATCH (content) against ('西湖' WITH QUERY EXPANSION);

上面通過 WITH QUERY EXPANSION 查詢 ‘西湖’ 就返回一條與其相關(guān)的數(shù)據(jù) 下面再插入一條數(shù)據(jù)： 再來拓展查詢 ‘西湖’：

select * from news where MATCH (content) against ('西湖' WITH QUERY EXPANSION);

再看結(jié)果，發(fā)現(xiàn)多了一條與 ‘西湖’ 無關(guān)的記錄(就是剛剛新增的數(shù)據(jù))。

那么為什么會出現(xiàn)這樣的結(jié)果呢？ 原因是拓展查詢會根據(jù)第一次查詢到的結(jié)果再進行一次查詢，第一次查詢到的結(jié)果是關(guān)于 ‘西湖’ 的結(jié)果，結(jié)果理包含了 ‘杭州’、 ‘美麗’、 ‘地方’ 等關(guān)鍵詞，第二次查詢會檢索 ‘杭州’、 ‘美麗’、 ‘地方’ 等關(guān)鍵詞，所以自然會查詢到上面兩條數(shù)據(jù)，下面再新增幾條數(shù)據(jù)來驗證: 上面新增數(shù)據(jù)11和12。再次拓展查詢’西湖’：

select * from news where MATCH (content) against ('西湖' WITH QUERY EXPANSION);

查詢結(jié)果包括剛剛新增的11、12兩條數(shù)據(jù)(因為這兩條數(shù)據(jù)包括’西湖’關(guān)鍵字的第一次查詢結(jié)果里面的關(guān)鍵詞)。 上面的拓展查詢結(jié)果相當(dāng)于下面這兩次查詢：

-- 首先根據(jù)'西湖'關(guān)鍵詞 查詢出 '杭州'、'美麗'、'地方' 等結(jié)果

select content from news where MATCH (content) against ('西湖' IN NATURAL LANGUAGE MODE)

-- 再根據(jù) '杭州'、'美麗'、'地方' 等結(jié)果二次查詢

select * from news where MATCH (content) against ('杭州 美麗 地方' IN NATURAL LANGUAGE MODE)

拓展查詢很有可能查詢出很多意想不到的結(jié)果，有點盲查的意思。

6.相關(guān)性排序

InNoDB 的全文搜索基于 Sphinx 搜索引擎，相關(guān)性排序算法采用的是 BM25 and TF-IDT，熟悉 ES 的同學(xué)應(yīng)該對 TF-IDT 不陌生，TF-IDT 簡單來說就是：被檢索關(guān)鍵詞出現(xiàn)越多的 doc 相關(guān)性越高，包含高辨識度(整體上在 doc 里面出現(xiàn)的比較少)關(guān)鍵詞的 doc 相關(guān)性越高，相關(guān)性越高，就越會被排在結(jié)果的前面。

SELECT

*,

MATCH ( content ) against ( '武漢 杭州' IN Boolean MODE ) AS score

FROM

news

ORDER BY

score DESC;

上面檢索了’武漢’、‘杭州’兩個關(guān)鍵詞，從結(jié)果中可以看出：

出現(xiàn)’杭州’(因為’杭州’整體上出現(xiàn)的較少，所以辨識度較高 IDF)的記錄(doc)得分較高(相關(guān)性交大)，排在前面武漢出現(xiàn)較多的記錄得分也比較高(TF)

7.注意事項

只能在類型為 char、varchar 或者 text 的字段上創(chuàng)建全文索引全文索引只支持 InnoDB 和 MyISAM 引擎MATCH (columnName) AGAINST (‘keywords’)。MATCH() 函數(shù)使用的字段名，必須要與創(chuàng)建全文索引時指定的字段名一致。如上面的示例，MATCH ( title,body) 使用的字段名與全文索引 ft_articles(title,body) 定義的字段名一致。如果要對 title 或者 body 字段分別進行查詢，就需要在 title 和 body 字段上分別創(chuàng)建新的全文索引MATCH() 函數(shù)使用的字段名只能是同一個表的字段，因為全文索引不能夠跨多個表進行檢索如果不是英文單詞，是字符串，用串中子字符串查不到（同分詞長度有一定關(guān)系）；但如果串中是用空格、‘,’,‘，’,';'等符號隔開，則可以查詢?nèi)绻粋€字段中英文混合，用英文模式建，默認單詞長度位4。查中文要求：查詢內(nèi)容3個字或以上，且是單獨句型，前后用語法分隔符分隔的；用中文模式建，英文查找慢，且結(jié)果不正確，匹配項大量增加多字段共建成一個全文索引，則存儲少，但查詢速度慢多字段共建成一個全文索引，如果中英文混合，按英文模式建，中文（查詢內(nèi)容3個字或以上，且是單獨句型，前后用語法分隔符分隔的）；按中文模式建，中文查找模式準(zhǔn)確，也快，但英文查找慢且匹配項大量增加表設(shè)計建議：1 一個表最好只有一個全文索引字段、2 字段內(nèi)容最好是全英文或全中文

好事定律：每件事最后都會是好事，如果不是好事，說明還沒到最后。

柚子快報邀請碼778899分享：全文檢索 MySql 全文索引

http://yzkb.51969.com/

文章鏈接