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

Mysql全文搜索對模糊查詢的性能提升測試方式

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

從測試成績來看,使用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

相關(guān)文章

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

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

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

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

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

    MySQL讀取Binlog日志常見的3種錯誤

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

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

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

    同時運行多個MySQL服務(wù)器的方法

    在同一臺機(jī)器上運行多個有些情況下你可能想要在同一臺機(jī)器上運行多個服務(wù)器。例如,你可能想要測試一個新的MySQL版本而讓你現(xiàn)有生產(chǎn)系統(tǒng)的設(shè)置不受到干擾, 或你可能是想要為不同的客戶提供獨立的MySQL安裝一個因特網(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ì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2023-07-07
  • mysql中批量插入數(shù)據(jù)(1萬、10萬、100萬、1000萬、1億級別)

    mysql中批量插入數(shù)據(jù)(1萬、10萬、100萬、1000萬、1億級別)

    本文主要介紹了mysql中批量插入數(shù)據(jù)(1萬、10萬、100萬、1000萬、1億級別),文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2022-08-08
  • MySQL常用慢查詢分析工具詳解

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

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

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

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

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

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

最新評論