作者 | luanhz
本文對MySQL中幾種常用的模糊搜索方式進行了介紹，包括LIKE通配符、RegExp正則匹配、內置字符串函數以及全文索引，最後給出了性能對比。
引言
MySQL根據不同的應用場景，支持的模糊搜索方式有多種，例如應用最廣泛的可能是Like匹配和RegExp正則匹配，二者雖然用法和原理都很相似，但實際上匹配原則卻不盡相同，其中Like要求模式串與整個目標字段完全匹配才檢索該記錄，而RegExp則是要求目標字段包含模式串即可。
對於簡單的判斷模式串是否存在類型的模糊搜索，應用MySQL內置函數即可實現，例如Instr、Locate、Position等。
當然，提到MySQL查詢性能就不得不提到索引，對於字段模糊查詢需求，我們也可以考慮添加全文索引(Fulltext)。
注：本文所用MySQL版本8.0.19，可視化工具Navicat Primium。
4種模糊查詢
為了便於描述和測試不同模糊查詢方式結果，首先給出一個簡單的測試用數據表tests如下：
其中，tests表僅含有一個名為words的字段，並對該字段添加全文索引。表中共有6條記錄。
Like
Like算作MySQL中的謂詞，其應用與is、=、>和
例如，在如上表中查找所有以"hello"開頭的記錄，則其SQL語句為：
<code>1SELECT words FROM tests WHERE words LIKE 'hello%';/<code>
查詢結果：
如果想查找所有以"hello"開頭且至少含有6個字符的記錄，則可簡單修改SQL語句如下：
<code>1SELECT words FROM tests WHERE words LIKE 'hello_%';/<code>
查詢結果：
另外：當在Like模式字段中，若不包含任何"_"和"%"通配符，則等價於"="，表示精確匹配，例如查詢語句……Like "hello"，則僅返回hello一條記錄；還可在Like前加限定詞Not，表示結果取反。
RegExp
正則表達式具有龐大而豐富的語法，MySQL語法中支持絕大部分正則表達式功能，幾乎可以滿足所有需求。本文不過多展開正則表達式相關介紹，僅在Like的基礎上，簡單介紹其與Like模糊搜索方式的區別。
如前所述，Like匹配原則是要求模式串與整個目標字段匹配時，才返回該條記錄；而RegExp中則是當目標字段包含模式串時即返回該條記錄。例如如下SQL語句將返回所有包含"hello"的記錄：
<code>1SELECT words FROM tests WHERE words REGEXP 'hello';/<code>
而在Like中這樣的寫法僅返回記錄="hello"的記錄。為了限定正則表達式以某個模式串開頭或者結尾，可以通過添加"^"和"$"標識符來限定，例如仍然搜索以"hello"開頭的目標字段，則其SQL語句為：
<code>1SELECT words FROM tests WHERE words REGEXP '^hello';/<code>
內置函數
對於包含某些特定模式串的模糊搜索，可以通過MySQL內置函數實現。可以完成這一功能的函數包括Instr、Locate和Position等，其功能語法很相近，均是返回子串在字符串中的索引，且索引下標從1開始，當子串不存在是返回0。需要注意的是三個函數中子串和字符串的先後順序是不一致的。例如以下語句均成功檢索，且返回目標索引1
<code>1SELECT INSTR("hello,world", 'hello');-- 1/<code><code>2SELECT LOCATE('hello', "hello,world");-- 1/<code><code>3SELECT POSITION('hello' in "hello, world"); -- 1/<code>應用以上3個內置函數，搜索上述測試表中包含"hello"的記錄，則相應SQL語句為：
<code>1SELECT words FROM tests WHERE INSTR(words, 'hello');/<code><code>2SELECT words FROM tests WHERE LOCATE('hello', words);/<code><code>3SELECT words FROM tests WHERE POSITION('hello' in words);/<code> 全文索引
拋開索引談查詢性能，都是耍流氓！
全文索引是MySQL中索引的一種，曾經僅在引擎為MyISAM的表中支持，從5.6版本開始在InnoDB中也開始支持全文索引，支持的字段格式包括CHAR、VARCHAR和TEXT。在如上已經添加了全文索引的tests表中，仍然查詢包含"hello"的記錄，應用全文索引查詢的SQL語句為：
<code>1SELECT words FROM tests WHERE MATCH(words) against('hello');/<code>實際上，MATCH(words) against('hello')返回的是字段words對目標字符"hello"的匹配程度：當不存在任何匹配結果時，返回0；否則，根據匹配次數的多少和位置先後返回一個匹配度。例如，如下SQL語句返回表中每條記錄對目標字段"hello"的匹配度：
<code>1SELECT MATCH(words) against('hello') FROM tests;/<code>返回結果如下：
查詢性能對比
為了對比以上4種模糊搜索方式的性能，我們這裡構建一個規模較大且更具一般性的數據表。本文選擇採集若干條英文格言，用於創建目標數據庫。
創建數據表
為簡單起見，僅創建一個名為says的字段，且對其添加全文索引。
<code>1CREATE TABLE IF NOT EXISTS sayings(says TEXT, FULLTEXT (says));/<code>
英文格言信息獲取
在網上找了個英文格言的網站，並寫了一個python小爬蟲爬取頁面全部300條英文格言，爬蟲源碼如下（為了增加記錄條數，將300條記錄重寫100詞，即數據庫中包含30000條記錄）：
<code> 1from pyquery import PyQuery as pq/<code><code> 2from pymysql import connect/<code><code> 3/<code><code> 4doc = pq(url='http://www.1juzi.com/new/43141.html', encoding = 'gb18030')/<code><code> 5items=doc("div.content>p:nth-child(2n+1)").items/<code><code> 6hots = [item.text() for item in items]/<code><code> 7with connect(host="localhost", user="root", password="123456", db='teststr', charset='utf8') as cur:/<code><code> 8 sql_insert = 'insert into sayings values (%s);'/<code><code> 9 for _ in range(100):/<code><code>10 cur.executemany(sql_insert, hots)/<code>對爬取的英文短句寫入創建的數據表中，結果如下：
既然是英文勵志格言短句，那麼我們就來查詢其中包括"success"的記錄。
首先查詢語句中任意位置包含"success"的記錄，4種方式SQL語句及執行時間為：
<code> 1-- LIKE通配符/<code><code> 2SELECT says FROM sayings WHERE says LIKE '%success%'/<code><code> 3> OK/<code><code> 4> 時間: 0.036s/<code><code> 5/<code><code> 6-- REGEXP正則匹配/<code><code> 7SELECT says FROM sayings WHERE says REGEXP 'success'/<code><code> 8> OK/<code><code> 9> 時間: 0.053s/<code><code>10/<code><code>11-- 內置函數查找/<code><code>12SELECT says FROM sayings WHERE INSTR(says, 'success')/<code><code>13> OK/<code><code>14> 時間: 0.045s/<code><code>15/<code><code>16SELECT says FROM sayings WHERE LOCATE('success', says)/<code><code>17> OK/<code><code>18> 時間: 0.044s/<code><code>19/<code><code>20SELECT says FROM sayings WHERE POSITION('success' in says)/<code><code>21> OK/<code><code>22> 時間: 0.047s/<code><code>23/<code><code>24-- 全文索引/<code><code>25SELECT says FROM sayings WHERE MATCH(says) against('Success')/<code><code>26> OK/<code><code>27> 時間: 0.006s/<code>可見，全文索引速度最寬，領先其他方式接近一個量級；Like通配符速度其次，但與其他幾種查詢方式效率相差不大。
通過Explain查詢計劃，我們可以發現全文索引方式由於應用了索引而無需全表查詢，所以執行速度快，而其他三種模糊查詢方式均為執行全表查詢。
全文索引查詢計劃
Like通配符查詢計劃
實際上，對於添加索引的字段應用Like查詢時，可以應用索引加速查詢，為勒驗證全文索引條件下是否仍然可以應用索引，我們進行第二組性能測試：
查詢語句中以"success"開頭的記錄（全文索引方式不支持指定單詞開頭的查詢任務），相應SQL語句即執行時間如下：
<code> 1SELECT says FROM sayings WHERE says LIKE 'success%'/<code><code> 2> OK/<code><code> 3> 時間: 0.015s/<code><code> 4/<code><code> 5SELECT says FROM sayings WHERE says REGEXP '^success'/<code><code> 6> OK/<code><code> 7> 時間: 0.046s/<code><code> 8/<code><code> 9SELECT says FROM sayings WHERE INSTR(says, 'success')=1/<code><code>10> OK/<code><code>11> 時間: 0.042s/<code><code>12/<code><code>13SELECT says FROM sayings WHERE LOCATE('success', says)=1/<code><code>14> OK/<code><code>15> 時間: 0.051s/<code><code>16/<code><code>17SELECT says FROM sayings WHERE POSITION('success' in says)=1/<code><code>18> OK/<code><code>19> 時間: 0.049s/<code><code>20/<code><code>21SELECT says FROM sayings WHERE MATCH(says) against('Success')/<code><code>22> OK/<code><code>23> 時間: 0.007s/<code>可以看到，修改後的Like查詢效率提升明顯，並大幅超過其他方式。但解釋查詢計劃發現，雖然possible_key顯示了索引字段，但實際仍然未應用任何索引（key為），即仍然進行全表查詢（Type = All）。之所以帶來速度上的大幅提升，僅僅是因為對'success%'要比'%success%'執行字符串匹配要快得多（後者要整列匹配，前者僅需匹配開頭的單詞即可），而與索引無關。
Like'success%'仍然無法應用全文索引
所以，得到的結論是Like通配符無法有效利用全文索引加速查詢，但在特定模式下的查詢速度可快於通配符%模式下的查詢。
總結
本文探討了MySQL中4中模糊查詢方式，包括：
Like通配符用於查詢目標字段與模式串完全匹配的記錄，且無法應用全文索引提高查詢速度，但以特定字符開頭的模糊查詢比以"%"開頭時速度提升明顯；
RegExp正則表達式功能強大，可實現任意模式查詢，但執行效率一般；
簡單的子串有無查詢還可應用MySQL內置函數，包括Instr、Locate和Position等，用法相近，但效率一般；
對於包含全文索引的目標字段查詢，應用全文索引查詢效率最高，但可定製性差，不支持任意匹配查詢；
記錄數目較少時，幾種查詢方式效率均可接受，可根據任務需求靈活選用。
今日福利
遇見大咖
由 CSDN 全新專為技術人打造的高端對話欄目《大咖來了》來啦！
CSDN 創始人&董事長、極客幫創投創始合夥人蔣濤攜手京東集團技術副總裁、IEEE Fellow、京東人工智能研究院常務副院長、深度學習及語音和語言實驗室負責人何曉冬，來也科技 CTO 胡一川，共話中國 AI 應用元年來了，開發者及企業的路徑及發展方向！
閱讀更多 CSDN 的文章
