SQL基础能力-SQL中的LIKE语句

1,250 阅读5分钟

这是我参与8月更文挑战的第7天,活动详情查看:8月更文挑战

继上一篇写了where语句以后,就很有一种继续写like的冲动。首先也是打算顺着where继续往下细讲,另外也是因为like查询作为模糊匹配查询在SQL也大量存在。然而它实在是让人又爱有怕,稍一不注意可能就会导致数据库慢查的出现。这里主要和大家讲一下like的具体使用以及导致慢查的原因。

在SQL中,like语句一般出现在where条件中使用。少部分在case when 语句中会出现,这种情况比较少见。

通配符

谈到like语句,通配符是不得不提。当前在SQL中主要有两种通配符,分别是

通配符含义
%代表0个或更多字符
_代表1个字符

在实际的查询过程中,通过与like语句结合,通配符会代表相应个数的字符进行模糊匹配。例如,如果想查找名字以“CHEN”开头的所有数据,可以使用这个where子句:

SELECT CASE WHEN y.name like 'CHEN%' THEN 1 else 0 END FROM (SELECT 'chenqi' as `name`)y

使用LIKE关键字而不是=符号,因为我们使用的是部分匹配。在字符串中,我们以字符“CHEN”开始,然后是百分号“%”。这意味着要返回一个记录,第一个名字必须等于“CHEN”,然后是任意数量的字符。值得注意的是,MySQL不区分大小写。而spark与flink对大小写都非常敏感。 spark执行结果

image.png

flink执行结果

image.png

使用'%'

'%'代表0或者更多的字符,可以用于在搜索关键字的前面或者后面,如形如下列的情况

  • %chen% 代表查询中间为'chen'的记录
  • chen% 代表查询开头为'chen'的记录
  • %chen 代表查询结尾为'chen'的记录 msyql下三种查询结果:

image.png

spark下三种查询结果:

image.png

flink 下三种查询结果:

image.png

可以看到,对于‘%’的使用还是比较通用的,在三中引擎下的表现都一样,我们再来看'_'

使用'_'

相对于'%', '_' 减少了可匹配的字符数,这样也就在某种意义上更加精确,比如我明确知道某个条件的开头是'CH'并且长度为4位,则可以用'_'补全。如

SELECT CASE WHEN y.name like 'ch__' THEN 1 else 0 END FROM (SELECT 'chen' as `name`)y

而此时用如下语句就无法查询出想要的结果

SELECT CASE WHEN y.name like 'ch_' THEN 1 else 0 END FROM (SELECT 'chen' as `name`)y

image.png

'_'的用法相对'%'拥有更多的可选择性。

flink 中使用

image.png

spark 中使用

image.png

使用'_'与'%'

除了单独使用以外,他们还可以组合使用,一下仅选择个案进行演示

saprk image.png flink

image.png

NOT LIKE

这里稍微提一下,就是对like的动作取反。不进行深入的案例展示。

使用通配符带来的问题

使用通配符在提升了模糊查询的便利的同时,也带来了一个糟糕的问题,也就是使用不恰当导致的全表查询。当然这里讨论的是mysql。大数据计算引擎没有此方面的担忧。

like导致索引失效的原因: LIKE过滤器在树遍历过程中只能使用第一个通配符之前的字符。其余字符只是过滤谓词,不会缩小扫描索引范围。因此,单个模糊匹配表达式可以包含两种谓词类型:

(1) 第一个通配符之前的部分作为访问谓词;

(2) 将其他字符作为过滤谓词。

第一个通配符之前的前缀越有选择性,扫描的索引范围就越小。这反过来又使索引查找速度更快。如下图使用三种不同的LIKE表达式说明了这种关系。三者都选择同一行,但扫描的索引范围,因此性能是非常不同的。

各种LIKE搜索

image.png 第一个表达式在通配符之前有两个字符。它们将扫描的索引范围限制为 18 行。其中只有一个匹配整个LIKE表达式——其他 17 个被提取但被丢弃。第二个表达式有一个更长的前缀,将扫描的索引范围缩小到两行。使用这个表达式,数据库只读取一个与结果无关的额外行。最后一个表达式根本没有过滤谓词:数据库只读取与整个LIKE表达式匹配的条目。LIKE以通配符开头的表达式。这样的LIKE表达式不能用作访问谓词。如果没有其他条件提供访问谓词,则数据库必须扫描整个表。

LIKE特殊语法匹配

在mysql、spark中存在更为多样的like模糊匹配方式。 比如支持正则匹配的模式,具有更加灵活的模板配置

正则匹配

spark中进行like正则匹配主要用到的关键是使用RLIKE or REGEXP。还是使用上面的案例进行 演示 msyql

image.png

spark

image.png

在flink中无法支持

image.png

定义转义字符

定义转义字符,使用关键字ESCAPE,需要和like绑定使用,如

image.png

image.png

问题

既然使用like可能导致性能问题,那么在case when中使用,会有同样问题吗?要解答这个问题,大家可以自行去看第一章《SQL执行顺序》,相信看完以后你心里就有底了。

总结

  1. like 语句中,MySQL对匹配字符大小写不敏感,而spark与flink则非常敏感。
  2. 在MySQL库中进行查询,需要避免使用通配符开头的查询模板,否则会导致索引失效。
  3. 正则匹配语法在flink中不适用。

参考文献: