MySQL 根据不同的应用场景, 支持的模糊搜索方式有多种, 例如应用最广泛的可能是 Like 匹配和 RegExp 正则匹配, 二者虽然用法和原理都很相似, 但实际上匹配原则却不尽相同, 其中 Like 要求模式串与整个目标字段完全匹配才检索该记录, 而 RegExp 则是要求目标字段包含模式串即可.
对于简单的判断模式串是否存在类型的模糊搜索, 应用 MySQL 内置函数即可实现, 例如 Instr(),Locate(),Position() 等.
当然, 提到 MySQL 查询性能就不得不提到索引, 对于字段模糊查询需求, 我们也可以考虑添加全文索引 (Fulltext).
注: 本文所用 MySQL 版本 8.0.19, 可视化工具 Navicat Primium.
02 4 种模糊查询
为了便于描述和测试不同模糊查询方式结果, 首先给出一个简单的测试用数据表 tests 如下:
其中, tests 表仅含有一个名为 words 的字段, 并对该字段添加全文索引. 表中共有 6 条记录.
Like
Like 算作 MySQL 中的谓词, 其应用与 is,=,> 和 < 等符号用法类似. Like 主要支持两种通配符, 分别是 "_" 和 "%", 其中前者代表匹配 1 个任意字符, 常用于充当占位符; 而后者代表匹配 0 个或多个任意字符. 从某种意义上讲, Like 可看作是一个精简的正则表达式功能.
例如, 在如上表中查找所有以 "hello" 开头的记录, 则其 SQL 语句为:
1SELECT words FROM tests WHERE words LIKE 'hello%';
查询结果:
如果想查找所有以 "hello" 开头且至少含有 6 个字符的记录, 则可简单修改 SQL 语句如下:
1SELECT words FROM tests WHERE words LIKE 'hello_%';
查询结果:
另外: 当在 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
- SELECT INSTR("hello,world", 'hello');-- 1
- SELECT LOCATE('hello', "hello,world");-- 1
- SELECT POSITION('hello' in "hello, world"); -- 1
应用以上 3 个内置函数, 搜索上述测试表中包含 "hello" 的记录, 则相应 SQL 语句为:
- SELECT words FROM tests WHERE INSTR(words, 'hello');
- SELECT words FROM tests WHERE LOCATE('hello', words);
- SELECT 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;
返回结果如下:
03 查询性能对比
为了对比以上 4 种模糊搜索方式的性能, 我们这里构建一个规模较大且更具一般性的数据表. 本文选择采集若干条英文格言, 用于创建目标数据库.
创建数据表. 为简单起见, 仅创建一个名为 says 的字段, 且对其添加全文索引.
1CREATE TABLE IF NOT EXISTS sayings(says TEXT, FULLTEXT (says));
英文格言信息获取
在网上找了个英文格言的网站, 并写了一个 python 小爬虫爬取页面全部 300 条英文格言, 爬虫源码如下 (为了增加记录条数, 将 300 条记录重写 100 词, 即数据库中包含 30000 条记录):
- from pyquery import PyQuery as pq
- from pymysql import connect
- doc = pq(url='http://www.1juzi.com/new/43141.html', encoding = 'gb18030')
- items=doc("div.content>p:nth-child(2n+1)").items()
- hots = [item.text() for item in items]
- with connect(host="localhost", user="root", password="123456", db='teststr', charset='utf8') as cur:
- sql_insert = 'insert into sayings values (%s);'
- for _ in range(100):
- cur.executemany(sql_insert, hots)
注: 如果对 pyquery 爬虫运用感兴趣, 可移步: 用 pyquery5 行代码爬取百度热点新闻一文
对爬取的英文短句写入创建的数据表中, 结果如下:
既然是英文励志格言短句, 那么我们就来查询其中包括 "success" 的记录.
首先查询语句中任意位置包含 "success" 的记录, 4 种方式 SQL 语句及执行时间为:
-- LIKE 通配符
- SELECT says FROM sayings WHERE says LIKE '%success%'
- > OK
> 时间: 0.036s
-- REGEXP 正则匹配
- SELECT says FROM sayings WHERE says REGEXP 'success'
- > OK
> 时间: 0.053s
-- 内置函数查找
- SELECT says FROM sayings WHERE INSTR(says, 'success')
- > OK
> 时间: 0.045s
- SELECT says FROM sayings WHERE LOCATE('success', says)
- > OK
> 时间: 0.044s
- SELECT says FROM sayings WHERE POSITION('success' in says)
- > OK
> 时间: 0.047s
-- 全文索引
- SELECT says FROM sayings WHERE MATCH(says) against('Success')
- > OK
> 时间: 0.006s
可见, 全文索引速度最宽, 领先其他方式接近一个量级; Like 通配符速度其次, 但与其他几种查询方式效率相差不大.
通过 Explain 查询计划, 我们可以发现全文索引方式由于应用了索引而无需全表查询, 所以执行速度快, 而其他三种模糊查询方式均为执行全表查询.
全文索引查询计划
Like 通配符查询计划
实际上, 对于添加索引的字段应用 Like 查询时, 可以应用索引加速查询, 为勒验证全文索引条件下是否仍然可以应用索引, 我们进行第二组性能测试:
查询语句中以 "success" 开头的记录 (全文索引方式不支持指定单词开头的查询任务), 相应 SQL 语句即执行时间如下:
- SELECT says FROM sayings WHERE says LIKE 'success%'
- > OK
> 时间: 0.015s
- SELECT says FROM sayings WHERE says REGEXP '^success'
- > OK
> 时间: 0.046s
- SELECT says FROM sayings WHERE INSTR(says, 'success')=1
- > OK
> 时间: 0.042s
- SELECT says FROM sayings WHERE LOCATE('success', says)=1
- > OK
> 时间: 0.051s
- SELECT says FROM sayings WHERE POSITION('success' in says)=1
- > OK
> 时间: 0.049s
- SELECT says FROM sayings WHERE MATCH(says) against('Success')
- > OK
> 时间: 0.007s
可以看到, 修改后的 Like 查询效率提升明显, 并大幅超过其他方式. 但解释查询计划发现, 虽然 possible_key 显示了索引字段, 但实际仍然未应用任何索引 (key 为 null), 即仍然进行全表查询 (Type = All). 之所以带来速度上的大幅提升, 仅仅是因为对'success%'要比'%success%'执行字符串匹配要快得多 (后者要整列匹配, 前者仅需匹配开头的单词即可), 而与索引无关.
Like'success%'仍然无法应用全文索引
所以, 得到的结论是 Like 通配符无法有效利用全文索引加速查询, 但在特定模式下的查询速度可快于通配符 % 模式下的查询.
04 总结
本文探讨了 MySQL 中 4 中模糊查询方式, 包括:
Like 通配符用于查询目标字段与模式串完全匹配的记录, 且无法应用全文索引提高查询速度, 但以特定字符开头的模糊查询比以 "%" 开头时速度提升明显
RegExp 正则表达式功能强大, 可实现任意模式查询, 但执行效率一般
简单的子串有无查询还可应用 MySQL 内置函数, 包括 Instr(),Locate() 和 Position() 等, 用法相近, 但效率一般
对于包含全文索引的目标字段查询, 应用全文索引查询效率最高, 但可定制性差, 不支持任意匹配查询
记录数目较少时, 几种查询方式效率均可接受, 可根据任务需求灵活选用
来源: http://database.51cto.com/art/202004/614332.htm