01 引言

MySQL根據(jù)不同的應(yīng)用場(chǎng)景，支持的模糊搜索方式有多種，例如應(yīng)用最廣泛的可能是Like匹配和RegExp正則匹配，二者雖然用法和原理都很相似，但實(shí)際上匹配原則卻不盡相同，其中Like要求模式串與整個(gè)目標(biāo)字段完全匹配才檢索該記錄，而RegExp則是要求目標(biāo)字段包含模式串即可。

對(duì)于簡(jiǎn)單的判斷模式串是否存在類(lèi)型的模糊搜索，應(yīng)用MySQL內(nèi)置函數(shù)即可實(shí)現(xiàn)，例如Instr()、Locate()、Position()等。

當(dāng)然，提到MySQL查詢性能就不得不提到索引，對(duì)于字段模糊查詢需求，我們也可以考慮添加全文索引(Fulltext)。

注：本文所用MySQL版本8.0.19，可視化工具Navicat Primium。

02 4種模糊查詢

為了便于描述和測(cè)試不同模糊查詢方式結(jié)果，首先給出一個(gè)簡(jiǎn)單的測(cè)試用數(shù)據(jù)表tests如下：

其中，tests表僅含有一個(gè)名為words的字段，并對(duì)該字段添加全文索引。表中共有6條記錄。

•  Like

Like算作MySQL中的謂詞，其應(yīng)用與is、=、>和<等符號(hào)用法類(lèi)似。Like主要支持兩種通配符，分別是"_"和"%"，其中前者代表匹配1個(gè)任意字符，常用于充當(dāng)占位符；而后者代表匹配0個(gè)或多個(gè)任意字符。從某種意義上講，Like可看作是一個(gè)精簡(jiǎn)的正則表達(dá)式功能。

例如，在如上表中查找所有以"hello"開(kāi)頭的記錄，則其SQL語(yǔ)句為： 

1SELECT words FROM tests WHERE words LIKE 'hello%'; 

查詢結(jié)果：

如果想查找所有以"hello"開(kāi)頭且至少含有6個(gè)字符的記錄，則可簡(jiǎn)單修改SQL語(yǔ)句如下： 

1SELECT words FROM tests WHERE words LIKE 'hello_%'; 

查詢結(jié)果：

另外：當(dāng)在Like模式字段中，若不包含任何"_"和"%"通配符，則等價(jià)于"="，表示精確匹配，例如查詢語(yǔ)句……Like "hello"，則僅返回hello一條記錄；還可在Like前加限定詞Not，表示結(jié)果取反。

•  RegExp

正則表達(dá)式具有龐大而豐富的語(yǔ)法，MySQL語(yǔ)法中支持絕大部分正則表達(dá)式功能，幾乎可以滿足所有需求。本文不過(guò)多展開(kāi)正則表達(dá)式相關(guān)介紹，僅在Like的基礎(chǔ)上，簡(jiǎn)單介紹其與Like模糊搜索方式的區(qū)別。

如前所述，Like匹配原則是要求模式串與整個(gè)目標(biāo)字段匹配時(shí)，才返回該條記錄；而RegExp中則是當(dāng)目標(biāo)字段包含模式串時(shí)即返回該條記錄。例如如下SQL語(yǔ)句將返回所有包含"hello"的記錄： 

1SELECT words FROM tests WHERE words REGEXP 'hello'; 

而在Like中這樣的寫(xiě)法僅返回記錄="hello"的記錄。為了限定正則表達(dá)式以某個(gè)模式串開(kāi)頭或者結(jié)尾，可以通過(guò)添加"^"和"$"標(biāo)識(shí)符來(lái)限定，例如仍然搜索以"hello"開(kāi)頭的目標(biāo)字段，則其SQL語(yǔ)句為： 

1SELECT words FROM tests WHERE words REGEXP '^hello'; 

•  內(nèi)置函數(shù)

對(duì)于包含某些特定模式串的模糊搜索，可以通過(guò)MySQL內(nèi)置函數(shù)實(shí)現(xiàn)?？梢酝瓿蛇@一功能的函數(shù)包括Instr()、Locate()和Position()等，其功能語(yǔ)法很相近，均是返回子串在字符串中的索引，且索引下標(biāo)從1開(kāi)始，當(dāng)子串不存在是返回0。需要注意的是三個(gè)函數(shù)中子串和字符串的先后順序是不一致的。例如以下語(yǔ)句均成功檢索，且返回目標(biāo)索引1 

1SELECT INSTR("hello,world", 'hello');-- 1  
2SELECT LOCATE('hello', "hello,world");-- 1  
3SELECT POSITION('hello' in "hello, world"); -- 1 

應(yīng)用以上3個(gè)內(nèi)置函數(shù)，搜索上述測(cè)試表中包含"hello"的記錄，則相應(yīng)SQL語(yǔ)句為： 

1SELECT words  FROM tests WHERE INSTR(words, 'hello');  
2SELECT words  FROM tests WHERE LOCATE('hello', words);  
3SELECT words  FROM tests WHERE POSITION('hello' in words); 

•  全文索引

拋開(kāi)索引談查詢性能，都是耍流氓！

全文索引是MySQL中索引的一種，曾經(jīng)僅在引擎為MyISAM的表中支持，從5.6版本開(kāi)始在InnoDB中也開(kāi)始支持全文索引，支持的字段格式包括CHAR、VARCHAR和TEXT。在如上已經(jīng)添加了全文索引的tests表中，仍然查詢包含"hello"的記錄，應(yīng)用全文索引查詢的SQL語(yǔ)句為： 

1SELECT words FROM tests WHERE MATCH(words) against('hello'); 

實(shí)際上，MATCH(words) against('hello')返回的是字段words對(duì)目標(biāo)字符"hello"的匹配程度：當(dāng)不存在任何匹配結(jié)果時(shí)，返回0；否則，根據(jù)匹配次數(shù)的多少和位置先后返回一個(gè)匹配度。例如，如下SQL語(yǔ)句返回表中每條記錄對(duì)目標(biāo)字段"hello"的匹配度： 

1SELECT MATCH(words) against('hello') FROM tests; 

返回結(jié)果如下：

03 查詢性能對(duì)比

為了對(duì)比以上4種模糊搜索方式的性能，我們這里構(gòu)建一個(gè)規(guī)模較大且更具一般性的數(shù)據(jù)表。本文選擇采集若干條英文格言，用于創(chuàng)建目標(biāo)數(shù)據(jù)庫(kù)。

•  創(chuàng)建數(shù)據(jù)表。為簡(jiǎn)單起見(jiàn)，僅創(chuàng)建一個(gè)名為says的字段，且對(duì)其添加全文索引。

1CREATE TABLE IF NOT EXISTS sayings(says TEXT, FULLTEXT (says)); 

•  英文格言信息獲取

在網(wǎng)上找了個(gè)英文格言的網(wǎng)站，并寫(xiě)了一個(gè)python小爬蟲(chóng)爬取頁(yè)面全部300條英文格言，爬蟲(chóng)源碼如下（為了增加記錄條數(shù)，將300條記錄重寫(xiě)100詞，即數(shù)據(jù)庫(kù)中包含30000條記錄）： 

1from pyquery import PyQuery  as pq  
 2from pymysql import connect  
 3  
 4doc = pq(url='http://www.1juzi.com/new/43141.html', encoding = 'gb18030')  
 5items=doc("div.content>p:nth-child(2n+1)").items()  
 6hots = [item.text() for item in items]  
 7with connect(host="localhost", user="root", password="123456", db='teststr', charset='utf8') as cur:  
 8    sql_insert = 'insert into sayings values (%s);'  
 9    for _ in range(100):  
10        cur.executemany(sql_insert, hots) 

注：如果對(duì)pyquery爬蟲(chóng)運(yùn)用感興趣，可移步：用pyquery5行代碼爬取百度熱點(diǎn)新聞一文

對(duì)爬取的英文短句寫(xiě)入創(chuàng)建的數(shù)據(jù)表中，結(jié)果如下：

既然是英文勵(lì)志格言短句，那么我們就來(lái)查詢其中包括"success"的記錄。

•  首先查詢語(yǔ)句中任意位置包含"success"的記錄，4種方式SQL語(yǔ)句及執(zhí)行時(shí)間為： 

 1-- LIKE通配符  
 2SELECT says FROM sayings WHERE says LIKE '%success%'  
 3> OK  
 4> 時(shí)間: 0.036s  
 5  
 6-- REGEXP正則匹配  
 7SELECT says FROM sayings WHERE says REGEXP 'success'  
 8> OK  
 9> 時(shí)間: 0.053s  
10  
11-- 內(nèi)置函數(shù)查找  
12SELECT says FROM sayings WHERE INSTR(says, 'success')  
13> OK  
14> 時(shí)間: 0.045s  
15  
16SELECT says FROM sayings WHERE LOCATE('success', says)  
17> OK  
18> 時(shí)間: 0.044s  
19  
20SELECT says FROM sayings WHERE POSITION('success' in says)  
21> OK  
22> 時(shí)間: 0.047s  
23  
24-- 全文索引  
25SELECT says FROM sayings WHERE MATCH(says) against('Success')  
26> OK  
27> 時(shí)間: 0.006s 

可見(jiàn)，全文索引速度最寬，領(lǐng)先其他方式接近一個(gè)量級(jí)；Like通配符速度其次，但與其他幾種查詢方式效率相差不大。

通過(guò)Explain查詢計(jì)劃，我們可以發(fā)現(xiàn)全文索引方式由于應(yīng)用了索引而無(wú)需全表查詢，所以執(zhí)行速度快，而其他三種模糊查詢方式均為執(zhí)行全表查詢。

全文索引查詢計(jì)劃

Like通配符查詢計(jì)劃

實(shí)際上，對(duì)于添加索引的字段應(yīng)用Like查詢時(shí)，可以應(yīng)用索引加速查詢，為勒驗(yàn)證全文索引條件下是否仍然可以應(yīng)用索引，我們進(jìn)行第二組性能測(cè)試：

•  查詢語(yǔ)句中以"success"開(kāi)頭的記錄（全文索引方式不支持指定單詞開(kāi)頭的查詢?nèi)蝿?wù)），相應(yīng)SQL語(yǔ)句即執(zhí)行時(shí)間如下： 

 1SELECT says FROM sayings WHERE says LIKE 'success%'  
 2> OK  
 3> 時(shí)間: 0.015s  
 4  
 5SELECT says FROM sayings WHERE says REGEXP '^success'  
 6> OK  
 7> 時(shí)間: 0.046s  
 8  
 9SELECT says FROM sayings WHERE INSTR(says, 'success')=1  
10> OK  
11> 時(shí)間: 0.042s  
12  
13SELECT says FROM sayings WHERE LOCATE('success', says)=1  
14> OK  
15> 時(shí)間: 0.051s  
16  
17SELECT says FROM sayings WHERE POSITION('success' in says)=1  
18> OK  
19> 時(shí)間: 0.049s  
20  
21SELECT says FROM sayings WHERE MATCH(says) against('Success')  
22> OK  
23> 時(shí)間: 0.007s 

可以看到，修改后的Like查詢效率提升明顯，并大幅超過(guò)其他方式。但解釋查詢計(jì)劃發(fā)現(xiàn)，雖然possible_key顯示了索引字段，但實(shí)際仍然未應(yīng)用任何索引（key為null），即仍然進(jìn)行全表查詢（Type = All）。之所以帶來(lái)速度上的大幅提升，僅僅是因?yàn)閷?duì)'success%'要比'%success%'執(zhí)行字符串匹配要快得多（后者要整列匹配，前者僅需匹配開(kāi)頭的單詞即可），而與索引無(wú)關(guān)。

Like'success%'仍然無(wú)法應(yīng)用全文索引

所以，得到的結(jié)論是Like通配符無(wú)法有效利用全文索引加速查詢，但在特定模式下的查詢速度可快于通配符%模式下的查詢。

04 總結(jié)

本文探討了MySQL中4中模糊查詢方式，包括：

•  Like通配符用于查詢目標(biāo)字段與模式串完全匹配的記錄，且無(wú)法應(yīng)用全文索引提高查詢速度，但以特定字符開(kāi)頭的模糊查詢比以"%"開(kāi)頭時(shí)速度提升明顯
•  RegExp正則表達(dá)式功能強(qiáng)大，可實(shí)現(xiàn)任意模式查詢，但執(zhí)行效率一般
•  簡(jiǎn)單的子串有無(wú)查詢還可應(yīng)用MySQL內(nèi)置函數(shù)，包括Instr()、Locate()和Position()等，用法相近，但效率一般
•  對(duì)于包含全文索引的目標(biāo)字段查詢，應(yīng)用全文索引查詢效率最高，但可定制性差，不支持任意匹配查詢
•  記錄數(shù)目較少時(shí)，幾種查詢方式效率均可接受，可根據(jù)任務(wù)需求靈活選用