您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

你知道什么是 MySQL 的模糊查询?

时间:2020-04-27 13:57:37  来源:  作者:
你知道什么是 MySQL 的模糊查询?

作者 | luanhz

责编 | 郭芮

本文对MySQL中几种常用的模糊搜索方式进行了介绍,包括LIKE通配符、RegExp正则匹配、内置字符串函数以及全文索引,最后给出了性能对比。

1 引言

MySQL根据不同的应用场景,支持的模糊搜索方式有多种,例如应用最广泛的可能是Like匹配和RegExp正则匹配,二者虽然用法和原理都很相似,但实际上匹配原则却不尽相同,其中Like要求模式串与整个目标字段完全匹配才检索该记录,而RegExp则是要求目标字段包含模式串即可。

对于简单的判断模式串是否存在类型的模糊搜索,应用MySQL内置函数即可实现,例如Instr、Locate、Position等。

当然,提到MySQL查询性能就不得不提到索引,对于字段模糊查询需求,我们也可以考虑添加全文索引(Fulltext)。

注:本文所用MySQL版本8.0.19,可视化工具Navicat Primium。

2  4种模糊查询

为了便于描述和测试不同模糊查询方式结果,首先给出一个简单的测试用数据表tests如下:

你知道什么是 MySQL 的模糊查询?

其中,tests表仅含有一个名为words的字段,并对该字段添加全文索引。表中共有6条记录。

你知道什么是 MySQL 的模糊查询?

Like

Like算作MySQL中的谓词,其应用与is、=、>和<等符号用法类似。Like主要支持两种通配符,分别是"_"和"%",其中前者代表匹配1个任意字符,常用于充当占位符;而后者代表匹配0个或多个任意字符。从某种意义上讲,Like可看作是一个精简的正则表达式功能。

例如,在如上表中查找所有以"hello"开头的记录,则其SQL语句为:

1SELECT words FROM tests WHERE words LIKE 'hello%';

查询结果:

你知道什么是 MySQL 的模糊查询?

如果想查找所有以"hello"开头且至少含有6个字符的记录,则可简单修改SQL语句如下:

1SELECT words FROM tests WHERE words LIKE 'hello_%';

查询结果:

你知道什么是 MySQL 的模糊查询?

另外:当在Like模式字段中,若不包含任何"_"和"%"通配符,则等价于"=",表示精确匹配,例如查询语句……Like "hello",则仅返回hello一条记录;还可在Like前加限定词Not,表示结果取反。

RegExp

正则表达式具有庞大而丰富的语法,MySQL语法中支持绝大部分正则表达式功能,几乎可以满足所有需求。本文不过多展开正则表达式相关介绍,仅在Like的基础上,简单介绍其与Like模糊搜索方式的区别。

如前所述,Like匹配原则是要求模式串与整个目标字段匹配时,才返回该条记录;而RegExp中则是当目标字段包含模式串时即返回该条记录。例如如下SQL语句将返回所有包含"hello"的记录:

1SELECT words FROM tests WHERE words REGEXP 'hello';

而在Like中这样的写法仅返回记录="hello"的记录。为了限定正则表达式以某个模式串开头或者结尾,可以通过添加"^"和"$"标识符来限定,例如仍然搜索以"hello"开头的目标字段,则其SQL语句为:

1SELECT words FROM tests WHERE words REGEXP '^hello';

内置函数

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

1SELECT INSTR("hello,world", 'hello');-- 12SELECT LOCATE('hello', "hello,world");-- 13SELECT POSITION('hello' in "hello, world"); -- 1

应用以上3个内置函数,搜索上述测试表中包含"hello"的记录,则相应SQL语句为:

1SELECT words FROM tests WHERE INSTR(words, 'hello');2SELECT words FROM tests WHERE LOCATE('hello', words);3SELECT words FROM tests WHERE POSITION('hello' in words);

全文索引

抛开索引谈查询性能,都是耍流氓!

全文索引是MySQL中索引的一种,曾经仅在引擎为MyISAM的表中支持,从5.6版本开始在InnoDB中也开始支持全文索引,支持的字段格式包括CHAR、VARCHAR和TEXT。在如上已经添加了全文索引的tests表中,仍然查询包含"hello"的记录,应用全文索引查询的SQL语句为:

1SELECT words FROM tests WHERE MATCH(words) against('hello');

实际上,MATCH(words) against('hello')返回的是字段words对目标字符"hello"的匹配程度:当不存在任何匹配结果时,返回0;否则,根据匹配次数的多少和位置先后返回一个匹配度。例如,如下SQL语句返回表中每条记录对目标字段"hello"的匹配度:

1SELECT MATCH(words) against('hello') FROM tests;

返回结果如下:

你知道什么是 MySQL 的模糊查询?

查询性能对比

为了对比以上4种模糊搜索方式的性能,我们这里构建一个规模较大且更具一般性的数据表。本文选择采集若干条英文格言,用于创建目标数据库。

创建数据表

为简单起见,仅创建一个名为says的字段,且对其添加全文索引。

1CREATE TABLE IF NOT EXISTS sayings(says TEXT, FULLTEXT (says));

英文格言信息获取

在网上找了个英文格言的网站,并写了一个Python小爬虫爬取页面全部300条英文格言,爬虫源码如下(为了增加记录条数,将300条记录重写100词,即数据库中包含30000条记录):

 1from pyquery import PyQuery as pq 2from pymysql import connect 3 4doc = pq(url='http://www.1juzi.com/new/43141.html', encoding = 'gb18030') 5items=doc("div.content>p:nth-child(2n+1)").items 6hots = [item.text() for item in items] 7with connect(host="localhost", user="root", password="123456", db='teststr', charset='utf8') as cur: 8 sql_insert = 'insert into sayings values (%s);' 9 for _ in range(100):10 cur.executemany(sql_insert, hots)

对爬取的英文短句写入创建的数据表中,结果如下:

你知道什么是 MySQL 的模糊查询?

既然是英文励志格言短句,那么我们就来查询其中包括"success"的记录。

首先查询语句中任意位置包含"success"的记录,4种方式SQL语句及执行时间为:

 1-- LIKE通配符 2SELECT says FROM sayings WHERE says LIKE '%success%' 3> OK 4> 时间: 0.036s 5 6-- REGEXP正则匹配 7SELECT says FROM sayings WHERE says REGEXP 'success' 8> OK 9> 时间: 0.053s1011-- 内置函数查找12SELECT says FROM sayings WHERE INSTR(says, 'success')13> OK14> 时间: 0.045s1516SELECT says FROM sayings WHERE LOCATE('success', says)17> OK18> 时间: 0.044s1920SELECT says FROM sayings WHERE POSITION('success' in says)21> OK22> 时间: 0.047s2324-- 全文索引25SELECT says FROM sayings WHERE MATCH(says) against('Success')26> OK27> 时间: 0.006s

可见,全文索引速度最宽,领先其他方式接近一个量级;Like通配符速度其次,但与其他几种查询方式效率相差不大。

通过Explain查询计划,我们可以发现全文索引方式由于应用了索引而无需全表查询,所以执行速度快,而其他三种模糊查询方式均为执行全表查询。

你知道什么是 MySQL 的模糊查询?

全文索引查询计划

你知道什么是 MySQL 的模糊查询?

Like通配符查询计划

实际上,对于添加索引的字段应用Like查询时,可以应用索引加速查询,为勒验证全文索引条件下是否仍然可以应用索引,我们进行第二组性能测试:

查询语句中以"success"开头的记录(全文索引方式不支持指定单词开头的查询任务),相应SQL语句即执行时间如下:

 1SELECT says FROM sayings WHERE says LIKE 'success%' 2> OK 3> 时间: 0.015s 4 5SELECT says FROM sayings WHERE says REGEXP '^success' 6> OK 7> 时间: 0.046s 8 9SELECT says FROM sayings WHERE INSTR(says, 'success')=110> OK11> 时间: 0.042s1213SELECT says FROM sayings WHERE LOCATE('success', says)=114> OK15> 时间: 0.051s1617SELECT says FROM sayings WHERE POSITION('success' in says)=118> OK19> 时间: 0.049s2021SELECT says FROM sayings WHERE MATCH(says) against('Success')22> OK23> 时间: 0.007s

可以看到,修改后的Like查询效率提升明显,并大幅超过其他方式。但解释查询计划发现,虽然possible_key显示了索引字段,但实际仍然未应用任何索引(key为),即仍然进行全表查询(Type = All)。之所以带来速度上的大幅提升,仅仅是因为对'success%'要比'%success%'执行字符串匹配要快得多(后者要整列匹配,前者仅需匹配开头的单词即可),而与索引无关。

你知道什么是 MySQL 的模糊查询?

Like'success%'仍然无法应用全文索引

所以,得到的结论是Like通配符无法有效利用全文索引加速查询,但在特定模式下的查询速度可快于通配符%模式下的查询。

4 总结

本文探讨了MySQL中4中模糊查询方式,包括:

  • Like通配符用于查询目标字段与模式串完全匹配的记录,且无法应用全文索引提高查询速度,但以特定字符开头的模糊查询比以"%"开头时速度提升明显;

  • RegExp正则表达式功能强大,可实现任意模式查询,但执行效率一般;

  • 简单的子串有无查询还可应用MySQL内置函数,包括Instr、Locate和Position等,用法相近,但效率一般;

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

  • 记录数目较少时,几种查询方式效率均可接受,可根据任务需求灵活选用。

声明:本文为作者投稿,版权归其所有。



Tags:MySQL 模糊查询   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,如有任何标注错误或版权侵犯请与我们联系(Email:2595517585@qq.com),我们将及时更正、删除,谢谢。
▌相关推荐
作者 | luanhz责编 | 郭芮本文对MySQL中几种常用的模糊搜索方式进行了介绍,包括LIKE通配符、RegExp正则匹配、内置字符串函数以及全文索引,最后给出了性能对比。1 引言MySQL根...【详细内容】
2020-04-27  Tags: MySQL 模糊查询  点击:(71)  评论:(0)  加入收藏
▌简易百科推荐
作者:雷文霆 爱可生华东交付服务部 DBA 成员,主要负责Mysql故障处理及相关技术支持。爱好看书,电影。座右铭,每一个不曾起舞的日子,都是对生命的辜负。 本文来源:原创投稿 *爱可生...【详细内容】
2021-12-24  爱可生    Tags:MySQL   点击:(7)  评论:(0)  加入收藏
生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下。有关Mysql记录锁、间隙(gap)锁、临键锁(next-key)锁的一些理论知识之前有写过,详细内容可以看这篇文章...【详细内容】
2021-12-14  python数据分析    Tags:MySQL记录锁   点击:(18)  评论:(0)  加入收藏
binlog 基本认识 MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二...【详细内容】
2021-12-14  linux上的码农    Tags:mysql   点击:(13)  评论:(0)  加入收藏
为查询优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候,这些查...【详细内容】
2021-12-09  元宇宙iwemeta    Tags:mysql   点击:(15)  评论:(0)  加入收藏
测试的目的和原因,公司有很多程序员,每个程序员对数据库和表结构都有自己的理解。而且每个程序员的理解往往是以效率考虑。既然都是为了效率考虑,那么我就来测试一下究竟哪种使...【详细内容】
2021-12-08  吴彬的分享    Tags:Mysql数据库   点击:(14)  评论:(0)  加入收藏
当你们考虑项目并发的时候,我在部署环境,当你们在纠结使用ArrayList还是LinkedArrayList的时候,我还是在部署环境。所以啊,技术不止境,我在部环境。今天这篇文章缕一下在同一台服...【详细内容】
2021-12-08  秃头码哥    Tags:MySQL数据库   点击:(17)  评论:(0)  加入收藏
对于数据分析来说,MySQL使用最多的是查询,比如对数据进行排序、分组、去重、汇总及字符串匹配等,如果查询的数据涉及多个表,还需要要对表进行连接,本文就来说说MySQL中常用的查询...【详细内容】
2021-12-06  笨鸟学数据分析    Tags:MySQL   点击:(21)  评论:(0)  加入收藏
在学习SQL语句之前,首先需要区分几个概念,我们常说的数据库是指数据库软件,例如MySQL、Oracle、SQL Server等,而本文提到的数据库是指数据库软件中的一个个用于存储数据的容器。...【详细内容】
2021-11-24  笨鸟学数据分析    Tags:SQL语句   点击:(23)  评论:(0)  加入收藏
概述以前参加过一个库存系统,由于其业务复杂性,搞了很多个应用来支撑。这样的话一份库存数据就有可能同时有多个应用来修改库存数据。比如说,有定时任务域xx.cron,和SystemA域...【详细内容】
2021-11-05  Java云海    Tags:分布式锁   点击:(31)  评论:(0)  加入收藏
MySQL的进阶查询 一、 按关键字排序 使用ORDERBY语句来实现排序排序可针对一个或多个字段ASC:升序,默认排序方式 【升序是从小到大】DESC:降序 【降序是从大到小】ORDER BY的...【详细内容】
2021-11-05  Java热点    Tags:SQL语句   点击:(28)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条