引入

在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分詞器把中文段落預(yù)處理拆分成單詞，然后存入數(shù)據(jù)庫(kù)。
從MySQL 5.7.6開始，MySQL內(nèi)置了ngram全文解析器，用來(lái)支持中文、日文、韓文分詞。

全文索引只支持InnoDB和MyISAM引擎，支持的類型為CHAR、VARCHAR或者TEXT的字段上創(chuàng)建全文索引。

了解：什么是n-gram模型

N-Gram是一種基于統(tǒng)計(jì)語(yǔ)言模型的算法。它的基本思想是將文本里面的內(nèi)容按照字節(jié)進(jìn)行大小為N的滑動(dòng)窗口操作，形成了長(zhǎng)度是N的字節(jié)片段序列。
每一個(gè)字節(jié)片段稱為gram，對(duì)所有g(shù)ram的出現(xiàn)頻度進(jìn)行統(tǒng)計(jì)，并且按照事先設(shè)定好的閾值進(jìn)行過(guò)濾，形成關(guān)鍵gram列表，也就是這個(gè)文本的向量特征空間，列表中的每一種gram就是一個(gè)特征向量維度。
該模型基于這樣一種假設(shè)，第N個(gè)詞的出現(xiàn)只與前面N-1個(gè)詞相關(guān)，而與其它任何詞都不相關(guān)，整句的概率就是各個(gè)詞出現(xiàn)概率的乘積。這些概率可以通過(guò)直接從語(yǔ)料中統(tǒng)計(jì)N個(gè)詞同時(shí)出現(xiàn)的次數(shù)得到。常用的是二元的Bi-Gram和三元的Tri-Gram。

MySQL分詞配置

ngram就是一段文字里面連續(xù)的n個(gè)字的序列。ngram全文解析器能夠?qū)ξ谋具M(jìn)行分詞，每個(gè)單詞是連續(xù)的n個(gè)字的序列。例如，用ngram全文解析器對(duì)“我是中國(guó)人”進(jìn)行分詞:

n=1: '我', '是', '中', '國(guó)','人' 
n=2: '我是', '中國(guó)', '人' 
n=3: '我是', '中國(guó)人' 
n=4: '我是中國(guó)人'

MySQL 中使用全局變量ngram_token_size來(lái)配置ngram中n的大小，它的取值范圍是1到10，默認(rèn)值是2。通常ngram_token_size設(shè)置為要查詢的單詞的最小字?jǐn)?shù)。如果需要搜索單字，就要把ngram_token_size設(shè)置為1。在默認(rèn)值是2的情況下，搜索單字是得不到任何結(jié)果的。因?yàn)橹形膯卧~最少是兩個(gè)漢字，推薦使用默認(rèn)值2。

全局變量ngram_token_size的兩種設(shè)置方法：

1. 啟動(dòng)mysqld命令時(shí)

mysqld --ngram_token_size=2

2. 修改MySQL配置文件

[mysqld]

ngram_token_size=2

創(chuàng)建全文檢索并分詞

1. 創(chuàng)建表的同時(shí)創(chuàng)建全文索引

CREATE TABLE `essay` (
  `essay_id` varchar(20) NOT NULL,
  `autoid` tinyint(20) NOT NULL COMMENT '序號(hào)',
  `essay_title` varchar(20) CHARACTER SET utf8 NOT NULL COMMENT '文章標(biāo)題',
  `essay_details` varchar(255) NOT NULL COMMENT '文章內(nèi)容',
  `essay_images` varchar(255) DEFAULT NULL COMMENT '文章圖片地址',
  `user_id` varchar(20) NOT NULL COMMENT '用戶id',
  `create_time` timestamp(6) NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
  `update_time` timestamp(6) NULL DEFAULT NULL COMMENT '修改時(shí)間',
  PRIMARY KEY (`essay_id`),
  UNIQUE KEY `idx_cs_id` (`essay_id`),
  FULLTEXT KEY `idx_cs_index` (`essay_title`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 通過(guò) alter table 的方式來(lái)添加

ALTER TABLE essay ADD FULLTEXT INDEX idx_cs_index(essay_title) WITH PARSER ngram;

3. 直接通過(guò)create index的方式

CREATE FULLTEXT INDEX idx_cs_index ON essay(essay_title) WITH PARSER ngram;

使用Navicat創(chuàng)建全文檢索

SQL中使用全文檢索

常用的全文檢索模式有兩種：

1. 自然語(yǔ)言模式(NATURAL LANGUAGE MODE) ，
   自然語(yǔ)言模式是MySQL 默認(rèn)的全文檢索模式。自然語(yǔ)言模式不能使用操作符，不能指定關(guān)鍵詞必須出現(xiàn)或者必須不能出現(xiàn)等復(fù)雜查詢。
2. BOOLEAN模式(BOOLEAN MODE)
   BOOLEAN模式可以使用操作符，可以支持指定關(guān)鍵詞必須出現(xiàn)或者必須不能出現(xiàn)或者關(guān)鍵詞的權(quán)重高還是低等復(fù)雜查詢。

-- 簡(jiǎn)單使用
select id,essay_title,essay_details from essay
where match(essay_title) against('全文檢索' IN NATURAL LANGUAGE MODE)
order by create_time desc
limit 200,20

-- 不指定模式，默認(rèn)使用自然語(yǔ)言模式
select id,essay_title,essay_details from essay
where match(essay_title) against('全文檢索')
order by create_time desc
limit 200,20

-- 搜索框提示查詢
select id,match(essay_title) against('全文檢索' IN NATURAL LANGUAGE MODE) as title from essay
order by create_time desc
limit 200,20

-- 使用BOOLEAN模式執(zhí)行高級(jí)查詢 必須包含"Java"
SELECT * FROM essay WHERE MATCH (essay_title) against('+Java' IN BOOLEAN MODE);

-- 使用BOOLEAN模式執(zhí)行高級(jí)查詢 必須包含"Java"，但是不能包含"匯編"
SELECT * FROM essay WHERE MATCH (essay_title) against('+Java -匯編' IN BOOLEAN MODE);

BOOLEAN模式下運(yùn)算符的使用方式：

'apple banana' 
無(wú)操作符，表示或，要么包含apple，要么包含banana
 
'+apple +juice'
必須同時(shí)包含兩個(gè)詞
 
'+apple macintosh'
必須包含apple，但是如果也包含macintosh的話，相關(guān)性會(huì)更高。
 
'+apple -macintosh'
必須包含apple，同時(shí)不能包含macintosh。
 
'+apple ~macintosh'
必須包含apple，但是如果也包含macintosh的話，相關(guān)性要比不包含macintosh的記錄低。
 
'+apple +(>juice <pie)'
查詢必須包含apple和juice或者apple和pie的記錄，但是apple juice的相關(guān)性要比apple pie高。
 
'apple*'
查詢包含以apple開頭的單詞的記錄，如apple、apples、applet。
 
'"some words"'
使用雙引號(hào)把要搜素的詞括起來(lái)，效果類似于like '%some words%'，

只能在類型為CHAR、VARCHAR或者TEXT的字段上創(chuàng)建全文索引。
全文索引只支持InnoDB和MyISAM引擎。
MATCH (columnName) AGAINST (‘keywords’)。MATCH()函數(shù)使用的字段名，必須要與創(chuàng)建全文索引時(shí)指定的字段名一致。如上面的示例，MATCH (title,body)使用的字段名與全文索引ft_articles(title,body)定義的字段名一致。如果要對(duì)title或者body字段分別進(jìn)行查詢，就需要在title和body字段上分別創(chuàng)建新的全文索引。
MATCH()函數(shù)使用的字段名只能是同一個(gè)表的字段，因?yàn)槿乃饕荒軌蚩缍鄠€(gè)表進(jìn)行檢索。
建議：如果要導(dǎo)入大數(shù)據(jù)集，數(shù)據(jù)初始化同步等需求時(shí)；建議先同步數(shù)據(jù)之后然后再創(chuàng)建全文索引，創(chuàng)建全文檢索后導(dǎo)入很影響TPS的

全文檢索與模糊查詢效率對(duì)比

第一張圖是模糊查詢的結(jié)果，第二張圖是全文檢索的結(jié)果

執(zhí)行器解釋查看

可以看到最明顯的區(qū)別，第一個(gè)沒(méi)全文索引的語(yǔ)句最后的rows是2120，有全文檢索的是1文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-466718.html

explain解讀

1. type：ALL, index, range, ref, eq_ref, const, system, NULL（從左到右，性能從差到好）
2. possible_keys：指出MySQL能使用哪個(gè)索引在表中找到記錄，查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢使用
3. key：表示MySQL實(shí)際決定使用的鍵（索引），如果沒(méi)有選擇索引，鍵是NULL。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
4. ref：表示選擇 key 列上的索引，哪些列或常量被用于查找索引列上的值
5. rows：根據(jù)表統(tǒng)計(jì)信息及索引選用情況，估算的找到所需的記錄所需要讀取的行數(shù)
6. Extra
   a.Using temporary：表示MySQL需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見于排序和分組查詢
   b.Using filesort：MySQL中無(wú)法利用索引完成的排序操作稱為“文件排序”

到了這里，關(guān)于MySQL全文檢索臨時(shí)代替ES實(shí)現(xiàn)快速搜索的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！