欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

Mysql全文搜索對(duì)模糊查詢的性能提升測(cè)試方式

 更新時(shí)間:2023年08月31日 10:16:25   作者:AbsoluteCuteness  
這篇文章主要介紹了Mysql全文搜索對(duì)模糊查詢的性能提升測(cè)試方式,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

從測(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)文章

  • mysql的json處理實(shí)現(xiàn)

    mysql的json處理實(shí)現(xiàn)

    本文主要介紹了mysql的json處理實(shí)現(xiàn),文中通過示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2023-08-08
  • MySQL外鍵約束的禁用與啟用命令

    MySQL外鍵約束的禁用與啟用命令

    下面小編就為大家?guī)硪黄狹ySQL外鍵約束的禁用與啟用命令。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧
    2017-03-03
  • MySQL讀取Binlog日志常見的3種錯(cuò)誤

    MySQL讀取Binlog日志常見的3種錯(cuò)誤

    這篇文章主要介紹了MySQL讀取Binlog日志常見的3種錯(cuò)誤,非常不錯(cuò),具有一定的參考借鑒價(jià)值 ,需要的朋友可以參考下
    2019-04-04
  • 從其他電腦訪問本機(jī)的Mysql的設(shè)置方法

    從其他電腦訪問本機(jī)的Mysql的設(shè)置方法

    如果需要讓特定的用戶從給定域(例如mydomain.com)的所有計(jì)算機(jī)上訪問 MySQL 服務(wù)器,你可以執(zhí)行在賬戶名的 host 部分使用了通配符“%” 的 GRANT 語句
    2008-11-11
  • 同時(shí)運(yùn)行多個(gè)MySQL服務(wù)器的方法

    同時(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-05
  • Linux下如何通過MySQL二進(jìn)制包安裝MySQL5.7

    Linux下如何通過MySQL二進(jìn)制包安裝MySQL5.7

    這篇文章主要介紹了Linux下如何通過MySQL二進(jìn)制包安裝MySQL5.7,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2023-07-07
  • mysql中批量插入數(shù)據(jù)(1萬、10萬、100萬、1000萬、1億級(jí)別)

    mysql中批量插入數(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
  • MySQL常用慢查詢分析工具詳解

    MySQL常用慢查詢分析工具詳解

    這篇文章主要介紹了MySQL常用慢查詢分析工具詳解,文章圍繞主題展開詳細(xì)的內(nèi)容介紹,具有一定的參考價(jià)值,需要的小伙伴可以參考一下
    2022-08-08
  • mysql 獲取當(dāng)天發(fā)布的信息的語句

    mysql 獲取當(dāng)天發(fā)布的信息的語句

    mysql 獲取當(dāng)天發(fā)布的信息的實(shí)現(xiàn)語句,需要的朋友可以參考下。
    2010-03-03
  • mysql之TIMESTAMP(時(shí)間戳)用法詳解

    mysql之TIMESTAMP(時(shí)間戳)用法詳解

    這篇文章主要介紹了mysql之TIMESTAMP(時(shí)間戳)用法,需要的朋友可以參考下
    2014-07-07

最新評(píng)論