摘? 要： 為了加深對(duì)嵌入式數(shù)據(jù)庫(kù)（MySQL）中數(shù)據(jù)模糊檢索方法的認(rèn)識(shí)。文中主要對(duì)嵌入式數(shù)據(jù)庫(kù)搜索模式的應(yīng)用進(jìn)行解釋，并根據(jù)實(shí)例給出性能對(duì)比。通過(guò)實(shí)驗(yàn)對(duì)比，對(duì)每一種搜索模式的性能都有了一定的了解，可以針對(duì)不同的應(yīng)用場(chǎng)景采用不同的搜索模式。

關(guān)鍵詞： 嵌入式數(shù)據(jù)庫(kù)、模糊搜索、性能對(duì)比

中圖分類號(hào)： TP3? ? 文獻(xiàn)標(biāo)識(shí)碼： A? ? DOI：10.3969/j.issn.1003-6970.2020.09.032

本文著錄格式：吳小青. 嵌入式數(shù)據(jù)庫(kù)（MySQL）中數(shù)據(jù)模糊檢索方法解析[J]. 軟件，2020，41（09）：115118

【Abstract】： In order to deepen the understanding of data fuzzy retrieval method in embedded database （MySQL）. This paper mainly explains the application of embedded database search mode， and gives the performance comparison according to the example. Through experimental comparison， we have a certain understanding of the performance of each search mode， and we can use different search patterns for different application scenarios.

【Key words】： Embedded database; Fuzzy search; Performance comparison

0? 引言

MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB公司開(kāi)發(fā)，目前屬于Oracle旗下產(chǎn)品。MySQL是時(shí)下最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，在WEB應(yīng)用方面，MySQL是最好的 RDBMS （Relational Database Management System，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)）應(yīng)用軟件之一。

無(wú)論何種類型的數(shù)據(jù)庫(kù)服務(wù)模式，在數(shù)據(jù)庫(kù)的使用中查詢是最重要也是使用頻率最高的一種操作，而用戶對(duì)數(shù)據(jù)的查詢操作往往并不能夠精確給出查詢需求，于是各類數(shù)據(jù)庫(kù)管理系統(tǒng)都給出了滿足用戶需求的模糊查詢的方法[1]。以下就MySQL中常用的模糊查詢做一一介紹和對(duì)比。

1? 導(dǎo)入問(wèn)題

根據(jù)不同的應(yīng)用場(chǎng)景，MySQL支持的模糊搜索方式不止一種，其中應(yīng)用較為廣泛的是大家熟知的Like匹配和RegExp正則匹配。表面上看，這兩種模糊搜索用法和原理頗為相仿，但它們的匹配原則不盡相同。Like要求模式串與整個(gè)目標(biāo)字段完全匹配才檢索該記錄，而RegExp則是要求目標(biāo)字段包含模式串即可。通常，若要簡(jiǎn)單判斷模式串是否存，我們可以使用MySQL內(nèi)置函數(shù)實(shí)現(xiàn)，例如Instr（）、Locate（）、Position（）等。

當(dāng)然，所有涉及到數(shù)據(jù)庫(kù)管理系統(tǒng)查詢性能都繞不開(kāi)索引，對(duì)于字段模糊查詢需求，也可以考慮添加全文索引（Fulltext）。

本文所用MySQL版本8.0，可視化工具Navicat。

2? 模糊查詢的種類及使用方法

為了說(shuō)明問(wèn)題，首先給出一個(gè)簡(jiǎn)單的測(cè)試數(shù)據(jù)庫(kù)，也是一個(gè)較為經(jīng)典的數(shù)據(jù)庫(kù)——yiibaidb，這個(gè)數(shù)據(jù)庫(kù)是一個(gè)典型汽車零售商數(shù)據(jù)庫(kù)模型。它包含典型的業(yè)務(wù)數(shù)據(jù)，如客戶，產(chǎn)品，銷售訂單，銷售訂單等。讀者可以在以下鏈接中下載本文所使用的示例數(shù)據(jù)庫(kù)：http：//www.yiibai.com/downloads/yiibaidb.zip。

2.1? Like

SQL Like是MySQL中的謂詞，其使用方法與is、=、>和<等關(guān)系運(yùn)算符類似，如果在Like中沒(méi)有使用通配符，那么它就是=。Like主要支持兩種通配符："_"和"%"，前者代表匹配1個(gè)任意字符，常用于充當(dāng)占位符;后者代表匹配0個(gè)或多個(gè)任意字符，類似于UNIX或正則表達(dá)式中的星號(hào)*，從某種意義上講，Like可看作是一個(gè)精簡(jiǎn)的正則表達(dá)式功能。

例如，在yiibaidb數(shù)據(jù)庫(kù)中有一張存儲(chǔ)汽車的數(shù)據(jù)的products表，我們要在該表中找到所有productline字段中“Classic”開(kāi)頭的數(shù)據(jù)，可以使用以下的方法：

SELECT productname，productLine FROM products WHERE productLine LIKE 'Classic%' LIMIT 5;

查詢結(jié)果：

如果想查找所有productLine第二個(gè)字母是“o”的記錄，則可修改SQL語(yǔ)句如下：

SELECT productname，productLine FROM products WHERE productLine LIKE '_o%' LIMIT 5;

查詢結(jié)果：

注意：當(dāng)在Like模式字段中，若不包含任何"_"和"%"通配符，則等價(jià)于"="，表示精確匹配，還可在Like前加限定詞Not，表示結(jié)果取反。

2.2? RegExp

MySQL中使用 REGEXP 操作符來(lái)進(jìn)行正則表達(dá)式匹配，并且中支持絕大部分正則表達(dá)式功能，幾乎可以滿足你在開(kāi)發(fā)時(shí)碰到的所有需求，盡管正則表達(dá)式的語(yǔ)法是如此龐大[2-4]。本文以下僅在Like的基礎(chǔ)上，簡(jiǎn)單介紹正則表達(dá)式與Like模糊搜索方式的區(qū)別之處，而有關(guān)正則表達(dá)式詳細(xì)內(nèi)容不做過(guò)多闡述。

Like語(yǔ)法的匹配原則是要求整個(gè)目標(biāo)字段與模式串匹配時(shí)，才返回該條記錄;而RegExp中則是當(dāng)目標(biāo)字段包含模式串時(shí)即返回該條記錄，由此可見(jiàn)RegExp比Like更加“模糊”。

例如，使用RegExp搜索productLine中包含"Classic"的記錄，SQL語(yǔ)句如下：

SELECT productname， productLine FROM products WHERE productLineRegExp'Classic' LIMIT 5;

為了限定正則表達(dá)式以某個(gè)模式串開(kāi)頭或者結(jié)尾，可以通過(guò)添加"^"和"$"標(biāo)識(shí)符來(lái)限定。

例如，使用RegExp搜索productLine以" Classic "開(kāi)頭的目標(biāo)字段，SQL語(yǔ)句如下：

SELECT productname， productLine FROM products WHERE productLineRegExp'^Classic' LIMIT 5;

使用RegExp搜索productLine以"ol "結(jié)尾的目標(biāo)字段，SQL語(yǔ)句如下：

SELECT productname，productLine FROM products WHERE productLineRegExp'ol$' LIMIT 5;

使用RegExp搜索productLine以" Classic "開(kāi)頭或以"ol "結(jié)尾的目標(biāo)字段，SQL語(yǔ)句如下：

SELECT productname，productLine FROM products WHERE productLineRegExp'^[Classic]|ol$' LIMIT 5;

2.3? 內(nèi)置函數(shù)

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

例如，以下語(yǔ)句均能成功檢索且返回索引下標(biāo)2：

SELECT INSTR（"Vintage，Cars"，'Cars'）;-- 2

SELECT LOCATE（'Cars'，"Vintage，Cars"）;-- 3

SELECT POSITION（'Cars' in "Vintage，Cars"）; -- 2

而將以上3個(gè)內(nèi)置函數(shù)轉(zhuǎn)變?yōu)樗阉鳒y(cè)試表中包含"Cars"的記錄，則相應(yīng)SQL語(yǔ)句為：

SELECT productLine FROM products WHERE INSTR（productLine， 'Cars'）;

SELECT productLine FROM products WHERE LOCATE （'Cars'，productLine）;

SELECT productLine FROM products WHERE POSITION （'Cars' in productLine）;

2.4? 全文索引

全文檢索過(guò)程類似于通過(guò)字典中的檢索字表查字的過(guò)程，它是對(duì)每一個(gè)詞建立一個(gè)索引，指明該詞在文章中出現(xiàn)的次數(shù)和位置，當(dāng)用戶查詢時(shí)，檢索程序就根據(jù)事先建立的索引進(jìn)行查找，并將查找的結(jié)果反饋給用戶的檢索方式[5-6]。

全文索引是MySQL中索引的一種，支持的字段格式包括CHAR、VARCHAR和TEXT。我們已經(jīng)在products表中對(duì)productLine字段建立了FullText全文索引。

例如，在已經(jīng)添加了全文索引的products表中，查詢包含"Cars"的記錄，應(yīng)用全文索引查詢的SQL語(yǔ)句為：

SELECT productLine FROM products WHERE MATCH（productLine） against（'Cars'）;

MATCH（productLine） against（'Cars'）返回的是字段productLine對(duì)目標(biāo)字符" Cars"的匹配程度：當(dāng)不存在任何匹配結(jié)果時(shí)，返回0;否則，根據(jù)匹配次數(shù)的多少和位置先后返回一個(gè)匹配度。

例如，如下SQL語(yǔ)句返回表中每條記錄對(duì)目標(biāo)字段"hello"的匹配度：

SELECT MATCH（productLine） against（'Cars'） FROM products;

返回結(jié)果如下：

3? 模糊搜索性能對(duì)比

下面我就對(duì)Like、RegExp、內(nèi)置函數(shù)和全文索引4中模糊檢索的方式進(jìn)行性能對(duì)比，因?yàn)閿?shù)據(jù)庫(kù)查詢速度非常快，以毫秒計(jì)，為了使對(duì)比結(jié)果更加明顯，我們采用數(shù)據(jù)量更大的表來(lái)執(zhí)行搜索對(duì)比。prod表中只有一列prodes，此列是對(duì)汽車生產(chǎn)構(gòu)造的簡(jiǎn)單描述，表中共有25300條記錄。

同樣任務(wù)的4種檢索方式用時(shí)對(duì)比：

任務(wù)：在prod表中對(duì)prodes字段中是否包含"wheels"進(jìn)行查詢，4種方式SQL語(yǔ)句及執(zhí)行時(shí)間為：

（1）LIKE通配符：查詢用時(shí)0.062s

SELECT prodes FROM prod WHERE prodes LIKE '%wheels%'

（2）REGEXP正則匹配：查詢用時(shí)0.082s

SELECT prodes FROM prod WHERE prodes REGEXP 'wheels'

（3）內(nèi)置函數(shù)查找

SELECT prodes FROM prod WHERE INSTR （prodes， 'wheels'）? 查詢用時(shí)0.086 s

SELECT prodes FROM prod WHERE LOCATE （'wheels'，prodes） 查詢用時(shí)0.086 s

SELECT prodes FROM prod WHERE POSITION （'wheels' in prodes） 查詢用時(shí)0.086 s

（4）全文索引： 查詢用時(shí)0.044 s

SELECT prodes FROM prod WHERE MATCH （prodes） against（'wheels'）

由查詢用時(shí)來(lái)看全文索引速度最快，Like通配符速度其次，REGEXP正則匹配和內(nèi)置函數(shù)查詢?cè)俅?。通過(guò)Explain查詢計(jì)劃分析，可以發(fā)現(xiàn)全文索引方式由于應(yīng)用了索引而無(wú)需全表查詢，所以執(zhí)行速度快，而其他三種模糊查詢方式均為執(zhí)行全表查詢。

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

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

在MySQL中對(duì)于添加索引的字段使用Like查詢時(shí)，可以使用索引來(lái)加速查詢，下面進(jìn)行第二組性能測(cè)試：

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

SELECT prodes FROM prod WHERE prodes LIKE 'This%' 查詢用時(shí)0.034 s

SELECT prodes FROM prod WHERE prodes REGEXP '^This'查詢用時(shí)0.066 s

可以看到，查詢匹配開(kāi)頭字符的Like查詢效率較以往有提升明顯，但explain查詢計(jì)劃發(fā)現(xiàn)，雖然possible_key顯示了索引字段，但實(shí)際仍然未應(yīng)用任何索引（key為null），即仍然進(jìn)行全表查詢（Type = All）。之所以帶來(lái)速度上的大幅提升，僅僅是因?yàn)楹笳咭衅ヅ?，前者僅需匹配開(kāi)頭的單詞即可），而與索引無(wú)關(guān)[7-8]。

4? 模糊搜索應(yīng)用總結(jié)

本文探討了MySQL中4中模糊查詢方式，并對(duì)其查詢方法進(jìn)行實(shí)踐，查詢效率進(jìn)行對(duì)比，得出以下結(jié)論：

（1）Like通配符用于查詢目標(biāo)字段與模式串完全匹配的記錄，但無(wú)法應(yīng)用全文索引提高查詢速度。備注：以特定字符開(kāi)頭的模糊查詢比以"%"開(kāi)頭時(shí)速度有提升。

（2）RegExp正則表達(dá)式功能強(qiáng)大，可實(shí)現(xiàn)任意模式查詢，但效率一般;

（3）Instr（）、Locate（）和Position（）等內(nèi)置函數(shù)查詢，用法相近，但效率一般;

（4）對(duì)于包含全文索引的目標(biāo)字段查詢，應(yīng)用全文索引查詢效率最高，但可定制性差，不支持任意匹配查詢;

（5）記錄數(shù)目較少時(shí)，幾種查詢方式效率區(qū)別不大，可根據(jù)實(shí)際任務(wù)任意選用。

參考文獻(xiàn)

[1]王斌， 田西蘭， 吳昭. 基于MySQL的雷達(dá)目標(biāo)特征數(shù)據(jù)庫(kù)設(shè)計(jì)[J]. 數(shù)字技術(shù)與應(yīng)用， 2020， 38（01）： 140-141.

[2]張捷. 分布式數(shù)據(jù)庫(kù)查詢處理和優(yōu)化算法[J]. 電子測(cè)試， 2019（24）： 66-67+34.

[3]謝華成， 馬學(xué)文. MongoDB 數(shù)據(jù)庫(kù)下文件型數(shù)據(jù)存儲(chǔ)研究[J]. 軟件， 2015， 36（11）： 12-14.

[4]季菁葦. 計(jì)算機(jī)數(shù)據(jù)庫(kù)技術(shù)在信息管理中的應(yīng)用研究探討[J]. 軟件， 2018， 39（6）： 160-163.

[5]劉翔宇， 朱大明. Arcgis中基于Python的地理數(shù)據(jù)庫(kù)批量合并方法研究[J]. 軟件， 2018， 39（7）： 161-165.

[6]雷鋼. 基于Oracle的數(shù)據(jù)庫(kù)安全研究[J]. 軟件， 2012， 33（1）： 75-77.

[7]殷仲磊， 趙廣鵬. 關(guān)于計(jì)算機(jī)數(shù)據(jù)庫(kù)入侵檢測(cè)技術(shù)的幾點(diǎn)思考[J]. 軟件， 2012， 33（5）： 70-72.

[8]牛亞偉， 林昭文， 馬嚴(yán)， 等. 數(shù)據(jù)流信息從MySQL 到HBase 的遷移策略的研究[J]. 軟件， 2015， 36（11）： 01-05.