從測試成績來看，使用mysql自帶的全文搜索索引類型 FULLTEXT，20w數(shù)據(jù)，對比*like ‘%xxxx%’*雙向模糊查詢，查詢效率提升了54.75倍，還是相當(dāng)不錯的；

場景：

最近工作中實現(xiàn)了一個通過字段名，提供映射工具給到客戶，使客戶得以通過可視化的字段規(guī)則自行拼裝sql，實現(xiàn)數(shù)據(jù)篩選分析功能；我們這張表的字段數(shù)非常多，足有上百個；

分兩個表的業(yè)務(wù)，一張表日表20w左右，另一張表日表200萬左右；

因為sql規(guī)則是用戶自定義的，一直沒有做什么優(yōu)化；

但是最近因為用戶拼裝出了超長sql，包含大量like查詢以及and、or，最終導(dǎo)致查詢跟不上，不得已考慮對他的優(yōu)化。

本文講述主要問題，大量的雙模糊 like ‘%xxx%’優(yōu)化

mysql中的全文索引介紹

MySQL 5.6開始支持全文索引，可以在變長的字符串類型上創(chuàng)建全文索引，來加速模糊匹配業(yè)務(wù)場景的DML操作。

它是一個inverted index（反向索引），創(chuàng)建fulltext index時會自動創(chuàng)建6個auxiliary index tables（輔助索引表），同時支持索引并行創(chuàng)建，并行度可以通過參數(shù)innodb_ft_sort_pll_degree設(shè)置，對于大表可以適當(dāng)增加該參數(shù)值。

在MySQL5.6之前的版本中，只有 MyISAM 存儲引擎支持全文索引，而且對中文搜索支持不是太好，需要自己進(jìn)行分詞后將段落預(yù)處理拆分成單詞在入庫。

MySQL5.7 開始才增加了對Inodb存儲引擎的支持，并且有了內(nèi)置的分詞器 ngram。ngram 支持設(shè)置設(shè)置分詞的長度，可以將中文按長度拆分為不同的單詞（雖然不太智能，但滿足大部分場景）。

-- 查詢mysql版本
select version();
-- 8.0.23

測試部分

1、創(chuàng)建無測試字段索引的測試表，并導(dǎo)入20w數(shù)據(jù)，進(jìn)行無索引狀態(tài)下模糊查詢耗時計算

-- 無索引狀態(tài)下耗時
select * from yd_alarminfo_all_20220825 where alarmTitle like "%端口故障%"
-- 耗時：0.438秒

2、使用ngram分詞，創(chuàng)建全文索引

alter table yd_alarminfo_all_20220825 add fulltext index idx_full_title(alarmTitle) with parser ngram;

3、再次查詢

-- 無索引狀態(tài)下耗時
select * from yd_alarminfo_all_20220825 WHERE MATCH (alarmTitle) against('端口故障' IN BOOLEAN MODE)
--耗時：0.008秒

使用過程中的其他問題 

1）關(guān)于參數(shù)微調(diào)

我在使用的時候并沒有對配置參數(shù)做調(diào)整，根據(jù)官方文檔的介紹。

全文搜索并沒有提供很多的可供調(diào)整的參數(shù)，而且默認(rèn)行為不管是對中文的分詞都是滿足的，大多數(shù)場景屬于開箱即用，無需調(diào)整。

更多參考官方文檔： 12.10.6 Fine-Tuning MySQL Full-Text Search

2）Natural Language 模式下

查詢結(jié)果不太一樣，匹配字符串“端口故障”被進(jìn)行了再次分詞；該模式為默認(rèn)的查詢模式，需要注意一下

MySQL全文檢索模式主要有兩種：

一、自然語言模式(NATURAL LANGUAGE MODE) 自然語言模式是MySQL 默認(rèn)的全文檢索模式。自然語言模式不能使用操作符，不能指定關(guān)鍵詞必須出現(xiàn)或者必須不能出現(xiàn)等復(fù)雜查詢。

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

3）在MATCH … AGAINST(…) 中

有自己的 AND OR 語法，如果使用傳統(tǒng)的AND、OR拼裝，效率拉胯

-- 錯誤示例
select * from yd_alarminfo_all_20220825 WHERE MATCH (alarmTitle) against('網(wǎng)卡端口故障' IN BOOLEAN MODE) OR MATCH (alarmTitle) against('AAA' IN BOOLEAN MODE)
-- 正確示例
select * from yd_alarminfo_all_20220825 WHERE MATCH (alarmTitle) against('網(wǎng)卡端口故障 -AAA-' IN BOOLEAN MODE)

4）補(bǔ)充BOOLEAN MODE下的語法

示例：

MATCH (col1,col2,...) AGAINST (expr IN BOOLEAN MODE)
expr 語法示例：
	'apple banana'
	查找至少包含兩個單詞之一的行。
	'+apple +juice'
	查找包含這兩個單詞的行。
	'+apple macintosh'
	查找包含“apple”一詞的行，但如果它們也包含“macintosh”，則排名更高。
	'+apple -macintosh'
	查找包含“apple”一詞但不包含“macintosh”一詞的行。
	'+apple ~macintosh'
	查找包含“apple”一詞的行，但如果該行也包含“macintosh”一詞，則將其評分低于行不包含。這比搜索'+apple -macintosh'“軟”，因為“macintosh”的存在導(dǎo)致該行根本不返回。
	'+apple +(>turnover <strudel)'
	查找包含“apple”和“turnover”或“apple”和“strudel”（按任何順序）的行，但排名“apple turnover”高于“apple strudel”。
	'apple*'
	查找包含“apple”、“apples”、“applesauce”或“applet”等單詞的行。
	'"some words"'
	查找包含確切短語“一些單詞”的行（例如，包含“一些智慧單詞”但不包含“一些噪音單詞”的行）。請注意，包含短語的"字符是劃定短語的運算符字符。它們不是包圍搜索字符串本身的引號。

結(jié)論部分

從測試成績來看，使用mysql自帶的全文搜索索引類型 FULLTEXT，20w數(shù)據(jù)，對比*like ‘%xxxx%’*雙向模糊查詢，查詢效率提升了54.75倍，還是相當(dāng)不錯的；

關(guān)于mysql全文搜索更多的基礎(chǔ)知識我就不再介紹了，主要是進(jìn)行測試其有效性，還是令人滿意的。

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

參考：

官方文檔

Functions and Operators 》 Full-Text Search Functions

最新評論