嵌入式数据库（MySQL）中数据模糊检索方法解析

摘  要： 为了加深对嵌入式数据库（MySQL）中数据模糊检索方法的认识。文中主要对嵌入式数据库搜索模式的应用进行解释，并根据实例给出性能对比。通过实验对比，对每一种搜索模式的性能都有了一定的了解，可以针对不同的应用场景采用不同的搜索模式。

關键词： 嵌入式数据库、模糊搜索、性能对比

中图分类号： TP3    文献标识码： A    DOI：10.3969/j.issn.1003-6970.2020.09.032

本文著录格式：吴小青. 嵌入式数据库（MySQL）中数据模糊检索方法解析[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是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle旗下产品。MySQL是时下最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的 RDBMS （Relational Database Management System，关系数据库管理系统）应用软件之一。

无论何种类型的数据库服务模式，在数据库的使用中查询是最重要也是使用频率最高的一种操作，而用户对数据的查询操作往往并不能够精确给出查询需求，于是各类数据库管理系统都给出了满足用户需求的模糊查询的方法[1]。以下就MySQL中常用的模糊查询做一一介绍和对比。

1  导入问题

根据不同的应用场景，MySQL支持的模糊搜索方式不止一种，其中应用较为广泛的是大家熟知的Like匹配和RegExp正则匹配。表面上看，这两种模糊搜索用法和原理颇为相仿，但它们的匹配原则不尽相同。Like要求模式串与整个目标字段完全匹配才检索该记录，而RegExp则是要求目标字段包含模式串即可。通常，若要简单判断模式串是否存，我们可以使用MySQL内置函数实现，例如Instr（）、Locate（）、Position（）等。

当然，所有涉及到数据库管理系统查询性能都绕不开索引，对于字段模糊查询需求，也可以考虑添加全文索引（Fulltext）。

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

2  模糊查询的种类及使用方法

为了说明问题，首先给出一个简单的测试数据库，也是一个较为经典的数据库——yiibaidb，这个数据库是一个典型汽车零售商数据库模型。它包含典型的业务数据，如客户，产品，销售订单，销售订单等。读者可以在以下链接中下载本文所使用的示例数据库：http：//www.yiibai.com/downloads/yiibaidb.zip。

2.1  Like

SQL Like是MySQL中的谓词，其使用方法与is、=、>和<等关系运算符类似，如果在Like中没有使用通配符，那么它就是=。Like主要支持两种通配符："_"和"%"，前者代表匹配1个任意字符，常用于充当占位符;后者代表匹配0个或多个任意字符，类似于UNIX或正则表达式中的星号*，从某种意义上讲，Like可看作是一个精简的正则表达式功能。

例如，在yiibaidb数据库中有一张存储汽车的数据的products表，我们要在该表中找到所有productline字段中“Classic”开头的数据，可以使用以下的方法：

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 操作符来进行正则表达式匹配，并且中支持绝大部分正则表达式功能，几乎可以满足你在开发时碰到的所有需求，尽管正则表达式的语法是如此庞大[2-4]。本文以下仅在Like的基础上，简单介绍正则表达式与Like模糊搜索方式的区别之处，而有关正则表达式详细内容不做过多阐述。

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  内置函数

对于包含某些特定模式串的模糊搜索，可以通过MySQL内置函数实现，例如Instr（）、Locate（）和Position（）等，它们的功能均是返回子串在字符串中的索引，且索引下标从1开始，当子串不存在则返回0。它们的语法很相近，但需要注意的是三个函数中子串和字符串的先后顺序是不一致的。

例如，以下语句均能成功检索且返回索引下标2：

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

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

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

而将以上3个内置函数转变为搜索测试表中包含"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  全文索引

全文检索过程类似于通过字典中的检索字表查字的过程，它是对每一个词建立一个索引，指明该词在文章中出现的次数和位置，当用户查询时，检索程序就根据事先建立的索引进行查找，并将查找的结果反馈给用户的检索方式[5-6]。

全文索引是MySQL中索引的一种，支持的字段格式包括CHAR、VARCHAR和TEXT。我们已经在products表中对productLine字段建立了FullText全文索引。

例如，在已经添加了全文索引的products表中，查询包含"Cars"的记录，应用全文索引查询的SQL语句为：

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

MATCH（productLine） against（'Cars'）返回的是字段productLine对目标字符" Cars"的匹配程度：当不存在任何匹配结果时，返回0;否则，根据匹配次数的多少和位置先后返回一个匹配度。

例如，如下SQL语句返回表中每条记录对目标字段"hello"的匹配度：

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

返回结果如下：

3  模糊搜索性能对比

下面我就对Like、RegExp、内置函数和全文索引4中模糊检索的方式进行性能对比，因为数据库查询速度非常快，以毫秒计，为了使对比结果更加明显，我们采用数据量更大的表来执行搜索对比。prod表中只有一列prodes，此列是对汽车生产构造的简单描述，表中共有25300条记录。

同样任务的4种检索方式用時对比：

任务：在prod表中对prodes字段中是否包含"wheels"进行查询，4种方式SQL语句及执行时间为：

（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）内置函数查找

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正则匹配和内置函数查询再次。通过Explain查询计划分析，可以发现全文索引方式由于应用了索引而无需全表查询，所以执行速度快，而其他三种模糊查询方式均为执行全表查询。

全文索引查询计划：

Like通配符查詢计划：

在MySQL中对于添加索引的字段使用Like查询时，可以使用索引来加速查询，下面进行第二组性能测试：

查询语句中以"This"开头的记录（全文索引方式不支持指定单词开头的查询任务），相应SQL语句即执行时间如下：

SELECT prodes FROM prod WHERE prodes LIKE 'This%' 查询用时0.034 s

SELECT prodes FROM prod WHERE prodes REGEXP '^This'查询用时0.066 s

可以看到，查询匹配开头字符的Like查询效率较以往有提升明显，但explain查询计划发现，虽然possible_key显示了索引字段，但实际仍然未应用任何索引（key为null），即仍然进行全表查询（Type = All）。之所以带来速度上的大幅提升，仅仅是因为后者要整列匹配，前者仅需匹配开头的单词即可），而与索引无关[7-8]。

4  模糊搜索应用总结

本文探讨了MySQL中4中模糊查询方式，并对其查询方法进行实践，查询效率进行对比，得出以下结论：

（1）Like通配符用于查询目标字段与模式串完全匹配的记录，但无法应用全文索引提高查询速度。备注：以特定字符开头的模糊查询比以"%"开头时速度有提升。

（2）RegExp正则表达式功能强大，可实现任意模式查询，但效率一般;

（3）Instr（）、Locate（）和Position（）等内置函数查询，用法相近，但效率一般;

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

（5）记录数目较少时，几种查询方式效率区别不大，可根据实际任务任意选用。

参考文献

[1]王斌， 田西兰， 吴昭. 基于MySQL的雷达目标特征数据库设计[J]. 数字技术与应用， 2020， 38（01）： 140-141.

[2]张捷. 分布式数据库查询处理和优化算法[J]. 电子测试， 2019（24）： 66-67+34.

[3]谢华成， 马学文. MongoDB 数据库下文件型数据存储研究[J]. 软件， 2015， 36（11）： 12-14.

[4]季菁苇. 计算机数据库技术在信息管理中的应用研究探讨[J]. 软件， 2018， 39（6）： 160-163.

[5]刘翔宇， 朱大明. Arcgis中基于Python的地理数据库批量合并方法研究[J]. 软件， 2018， 39（7）： 161-165.

[6]雷钢. 基于Oracle的数据库安全研究[J]. 软件， 2012， 33（1）： 75-77.

[7]殷仲磊， 赵广鹏. 关于计算机数据库入侵检测技术的几点思考[J]. 软件， 2012， 33（5）： 70-72.

[8]牛亚伟， 林昭文， 马严， 等. 数据流信息从MySQL 到HBase 的迁移策略的研究[J]. 软件， 2015， 36（11）： 01-05.