嵌入式數(shù)據(jù)庫（MySQL）中數(shù)據(jù)模糊檢索方法解析

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

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

中圖分類號： TP3? ? 文獻標識碼： A? ? DOI：10.3969/j.issn.1003-6970.2020.09.032

本文著錄格式：吳小青. 嵌入式數(shù)據(jù)庫（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是一個關系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典MySQL AB公司開發(fā)，目前屬于Oracle旗下產品。MySQL是時下最流行的關系型數(shù)據(jù)庫管理系統(tǒng)之一，在WEB應用方面，MySQL是最好的 RDBMS （Relational Database Management System，關系數(shù)據(jù)庫管理系統(tǒng)）應用軟件之一。

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

1? 導入問題

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

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

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

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

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

2.1? Like

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

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

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

查詢結果：

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

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

查詢結果：

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

2.2? RegExp

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

Like語法的匹配原則是要求整個目標字段與模式串匹配時，才返回該條記錄;而RegExp中則是當目標字段包含模式串時即返回該條記錄，由此可見RegExp比Like更加“模糊”。

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

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

為了限定正則表達式以某個模式串開頭或者結尾，可以通過添加"^"和"$"標識符來限定。

例如，使用RegExp搜索productLine以" Classic "開頭的目標字段，SQL語句如下：

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

使用RegExp搜索productLine以"ol "結尾的目標字段，SQL語句如下：

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

使用RegExp搜索productLine以" Classic "開頭或以"ol "結尾的目標字段，SQL語句如下：

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

2.3? 內置函數(shù)

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

例如，以下語句均能成功檢索且返回索引下標2：

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

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

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

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

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? 全文索引

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

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

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

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

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

例如，如下SQL語句返回表中每條記錄對目標字段"hello"的匹配度：

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

返回結果如下：

3? 模糊搜索性能對比

下面我就對Like、RegExp、內置函數(shù)和全文索引4中模糊檢索的方式進行性能對比，因為數(shù)據(jù)庫查詢速度非?？欤院撩胗?，為了使對比結果更加明顯，我們采用數(shù)據(jù)量更大的表來執(zhí)行搜索對比。prod表中只有一列prodes，此列是對汽車生產構造的簡單描述，表中共有25300條記錄。

同樣任務的4種檢索方式用時對比：

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

（1）LIKE通配符：查詢用時0.062s

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

（2）REGEXP正則匹配：查詢用時0.082s

SELECT prodes FROM prod WHERE prodes REGEXP 'wheels'

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

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

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

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

（4）全文索引： 查詢用時0.044 s

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

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

全文索引查詢計劃：

Like通配符查詢計劃：

在MySQL中對于添加索引的字段使用Like查詢時，可以使用索引來加速查詢，下面進行第二組性能測試：

查詢語句中以"This"開頭的記錄（全文索引方式不支持指定單詞開頭的查詢任務），相應SQL語句即執(zhí)行時間如下：

SELECT prodes FROM prod WHERE prodes LIKE 'This%' 查詢用時0.034 s

SELECT prodes FROM prod WHERE prodes REGEXP '^This'查詢用時0.066 s

可以看到，查詢匹配開頭字符的Like查詢效率較以往有提升明顯，但explain查詢計劃發(fā)現(xiàn)，雖然possible_key顯示了索引字段，但實際仍然未應用任何索引（key為null），即仍然進行全表查詢（Type = All）。之所以帶來速度上的大幅提升，僅僅是因為后者要整列匹配，前者僅需匹配開頭的單詞即可），而與索引無關[7-8]。

4? 模糊搜索應用總結

本文探討了MySQL中4中模糊查詢方式，并對其查詢方法進行實踐，查詢效率進行對比，得出以下結論：

（1）Like通配符用于查詢目標字段與模式串完全匹配的記錄，但無法應用全文索引提高查詢速度。備注：以特定字符開頭的模糊查詢比以"%"開頭時速度有提升。

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

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

（4）對于包含全文索引的目標字段查詢，應用全文索引查詢效率最高，但可定制性差，不支持任意匹配查詢;

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

參考文獻

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

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

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

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

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

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

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

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