Mysql全文搜索對(duì)模糊查詢的性能提升測(cè)試方式
從測(cè)試成績來看,使用mysql自帶的全文搜索索引類型 FULLTEXT,20w數(shù)據(jù),對(duì)比*like ‘%xxxx%’*雙向模糊查詢,查詢效率提升了54.75倍,還是相當(dāng)不錯(cuò)的;
場(chǎng)景:
最近工作中實(shí)現(xiàn)了一個(gè)通過字段名,提供映射工具給到客戶,使客戶得以通過可視化的字段規(guī)則自行拼裝sql,實(shí)現(xiàn)數(shù)據(jù)篩選分析功能;我們這張表的字段數(shù)非常多,足有上百個(gè);
分兩個(gè)表的業(yè)務(wù),一張表日表20w左右,另一張表日表200萬左右;
因?yàn)閟ql規(guī)則是用戶自定義的,一直沒有做什么優(yōu)化;
但是最近因?yàn)橛脩羝囱b出了超長sql,包含大量like查詢以及and、or,最終導(dǎo)致查詢跟不上,不得已考慮對(duì)他的優(yōu)化。
本文講述主要問題,大量的雙模糊 like ‘%xxx%’優(yōu)化
mysql中的全文索引介紹
MySQL 5.6開始支持全文索引,可以在變長的字符串類型上創(chuàng)建全文索引,來加速模糊匹配業(yè)務(wù)場(chǎng)景的DML操作。
它是一個(gè)inverted index(反向索引),創(chuàng)建fulltext index時(shí)會(huì)自動(dòng)創(chuàng)建6個(gè)auxiliary index tables(輔助索引表),同時(shí)支持索引并行創(chuàng)建,并行度可以通過參數(shù)innodb_ft_sort_pll_degree設(shè)置,對(duì)于大表可以適當(dāng)增加該參數(shù)值。
在MySQL5.6之前的版本中,只有 MyISAM 存儲(chǔ)引擎支持全文索引,而且對(duì)中文搜索支持不是太好,需要自己進(jìn)行分詞后將段落預(yù)處理拆分成單詞在入庫。
MySQL5.7 開始才增加了對(duì)Inodb存儲(chǔ)引擎的支持,并且有了內(nèi)置的分詞器 ngram。ngram 支持設(shè)置設(shè)置分詞的長度,可以將中文按長度拆分為不同的單詞(雖然不太智能,但滿足大部分場(chǎng)景)。
-- 查詢mysql版本 select version(); -- 8.0.23
測(cè)試部分
1、創(chuàng)建無測(cè)試字段索引的測(cè)試表,并導(dǎo)入20w數(shù)據(jù),進(jìn)行無索引狀態(tài)下模糊查詢耗時(shí)計(jì)算
-- 無索引狀態(tài)下耗時(shí) select * from yd_alarminfo_all_20220825 where alarmTitle like "%端口故障%" -- 耗時(shí):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)下耗時(shí) select * from yd_alarminfo_all_20220825 WHERE MATCH (alarmTitle) against('端口故障' IN BOOLEAN MODE) --耗時(shí):0.008秒
使用過程中的其他問題
1)關(guān)于參數(shù)微調(diào)
我在使用的時(shí)候并沒有對(duì)配置參數(shù)做調(diào)整,根據(jù)官方文檔的介紹。
全文搜索并沒有提供很多的可供調(diào)整的參數(shù),而且默認(rèn)行為不管是對(duì)中文的分詞都是滿足的,大多數(shù)場(chǎng)景屬于開箱即用,無需調(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拼裝,效率拉胯
-- 錯(cuò)誤示例 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' 查找至少包含兩個(gè)單詞之一的行。 '+apple +juice' 查找包含這兩個(gè)單詞的行。 '+apple macintosh' 查找包含“apple”一詞的行,但如果它們也包含“macintosh”,則排名更高。 '+apple -macintosh' 查找包含“apple”一詞但不包含“macintosh”一詞的行。 '+apple ~macintosh' 查找包含“apple”一詞的行,但如果該行也包含“macintosh”一詞,則將其評(píng)分低于行不包含。這比搜索'+apple -macintosh'“軟”,因?yàn)椤癿acintosh”的存在導(dǎo)致該行根本不返回。 '+apple +(>turnover <strudel)' 查找包含“apple”和“turnover”或“apple”和“strudel”(按任何順序)的行,但排名“apple turnover”高于“apple strudel”。 'apple*' 查找包含“apple”、“apples”、“applesauce”或“applet”等單詞的行。 '"some words"' 查找包含確切短語“一些單詞”的行(例如,包含“一些智慧單詞”但不包含“一些噪音單詞”的行)。請(qǐng)注意,包含短語的"字符是劃定短語的運(yùn)算符字符。它們不是包圍搜索字符串本身的引號(hào)。
結(jié)論部分
從測(cè)試成績來看,使用mysql自帶的全文搜索索引類型 FULLTEXT,20w數(shù)據(jù),對(duì)比*like ‘%xxxx%’*雙向模糊查詢,查詢效率提升了54.75倍,還是相當(dāng)不錯(cuò)的;
關(guān)于mysql全文搜索更多的基礎(chǔ)知識(shí)我就不再介紹了,主要是進(jìn)行測(cè)試其有效性,還是令人滿意的。
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。
參考:
Functions and Operators 》 Full-Text Search Functions
相關(guān)文章
同時(shí)運(yùn)行多個(gè)MySQL服務(wù)器的方法
在同一臺(tái)機(jī)器上運(yùn)行多個(gè)有些情況下你可能想要在同一臺(tái)機(jī)器上運(yùn)行多個(gè)服務(wù)器。例如,你可能想要測(cè)試一個(gè)新的MySQL版本而讓你現(xiàn)有生產(chǎn)系統(tǒng)的設(shè)置不受到干擾, 或你可能是想要為不同的客戶提供獨(dú)立的MySQL安裝一個(gè)因特網(wǎng)服務(wù)供應(yīng)商。2008-05-05Linux下如何通過MySQL二進(jìn)制包安裝MySQL5.7
這篇文章主要介紹了Linux下如何通過MySQL二進(jìn)制包安裝MySQL5.7,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-07-07mysql中批量插入數(shù)據(jù)(1萬、10萬、100萬、1000萬、1億級(jí)別)
本文主要介紹了mysql中批量插入數(shù)據(jù)(1萬、10萬、100萬、1000萬、1億級(jí)別),文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2022-08-08