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

吳小青

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

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

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

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

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

1 ?導(dǎo)入問題

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

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

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

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

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

2.1 ?Like

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

2.2 ?RegExp

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

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

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

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

2.4 ?全文索引

全文檢索過程類似于通過字典中的檢索字表查字的過程，它是對(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全文索引。

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

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

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

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

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

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

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

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

（1）Like通配符用于查詢目標(biāo)字段與模式串完全匹配的記錄，但無法應(yīng)用全文索引提高查詢速度。備注：以特定字符開頭的模糊查詢比以"%"開頭時(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ù)庫設(shè)計(jì)[J]. 數(shù)字技術(shù)與應(yīng)用， 2020， 38（01）： 140-141.

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

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

[4]季菁葦. 計(jì)算機(jī)數(shù)據(jù)庫技術(shù)在信息管理中的應(yīng)用研究探討[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]殷仲磊， 趙廣鵬. 關(guān)于計(jì)算機(jī)數(shù)據(jù)庫入侵檢測技術(shù)的幾點(diǎn)思考[J]. 軟件， 2012， 33（5）： 70-72.

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