性能文章>注意!分页 + 模糊查询有坑!>

注意!分页 + 模糊查询有坑!原创

246414

前言

不知道你有没有使用过Mysql的like语句,进行模糊查询?
不知道你有没有将查询结果,进行分页处理?

模糊查询,加上分页处理,会有意想不到的坑,不信我们继续往下看。
我之前提供过一个品牌查询接口,给前端品牌选择控件使用的。

当时为了性能考虑,怕前端控件因为一次性加载太多的品牌,而导致页面卡死。

因此,对品牌查询接口做了分页处理。刚开始品牌表的数据比较少,没有出现什么问题。后来,产品加需求了,在品牌下拉选择控件中,让用户可以输入自定义品牌。

在用户添加品牌前,需要先查一下,如果该品牌存在,则使用已有品牌。如果不存在,则新增品牌。(这里是精确匹配)

这个需求很简单,很容易实现了。

后来,产品又加需求了,需要按名称模糊查询品牌。该功能上线后,使用了很长一段时间,也没啥问题。突然,在不经意的某一天,这个功能却出问题了。到底怎么回事呢?

1.案发现场

某一天下午,运营找到测试反馈一个问题说:明明品牌苏三,已经存在了,但用户输入关键字:苏三时,系统没有让用户直接选择已有品牌,而是添加了一个叫:苏三的自定义的品牌。

我过去一看,还真的有问题。不一会儿,就定位到原因了,初步判断是分页的问题。搜索关键字:苏三,竟然出现了好几页的数据,把我惊呆了,品牌表怎么多了这么多数据了。

我查了数据库,其实数据量并不是特别多,但有些品牌名称比较特殊,有些品牌名称是多个品牌名称拼接而成的,比如:苏三,李四 或者 苏三,李四,王五,这是一个品牌。

其实是品牌名称建的不规范导致的问题,但已经没法让运营修改品牌了,只能通过技术手段解决目前的问题。

查询第一页的数据sql:

select * from brand where name like '%苏三%' 
order by edit_date desc limit 5;

执行结果:
image1.png
我们可以看到,图中并没有等于苏三这两个字的数据。
注意:为了好演示,这里给的每页大小是5,真实的场景并非如此。
查询第二页的数据sql:

select * from brand where name like '%苏三%' 
order by edit_date desc limit 5,5;

执行结果:
image2.png

从图中看到,在第二行,出现了正好等于苏三这两个字的数据。
用户搜索关键字:苏三 时,前端页面在调用品牌查询接口,pageNo默认是1。由于能够匹配关键字的数据太多了,第一页返回不完,需要多页才能全部返回。

前端获取到第一页的数据后,跟关键字:苏三 做比较,发现没有等于苏三的品牌。

这样就会在下拉控件中,自动添加一个品牌:苏三,同时在右边增加自定义标签。

这样就出问题了,明明苏三这个品牌是有的,但用户还能自定义一个苏三,而不是直接选择。

2.思考123

苏三这个关键字,通过模糊查询可以查询出来,但由于品牌接口做了分页,全匹配的品牌:苏三,出现在第二页了,才导致问题的产生。
如果要解决这个问题,让它出现在第一页不就OK了?
这时候,就有下面几种解决方案。

2.1 方案1

分页查询品牌接口,pageSize是5。

我们为什么不把pageSize调大一点?比如改成:200、500等。
这样通过苏三关键字,进行模糊查询的时候,结果基本都在第一页。
这样就能非常快速的解决问题。但有个缺点就是:如果这次调大了pageSize,但后面查询关键字的品牌又出现在第二页怎么办?不可能一直改pageSize吧?

2.2 方案2

把分页查询接口的数据,拆分成两部分:

  • 精确查询
  • 模糊查询
    在代码中做处理的时候,先根据关键字精确查询,即sql中使用name=‘苏三’,这种方式查询一次数据。

如果没查出数据,则再直接用like '%苏三’进行模糊查询。
如果查出了一条数据,则把它放在返回结果集合中的第一位置。接下来,使用like '%苏三’进行模糊查询的时候,再加上条件 name <> ‘苏三’。将查出的结果,从第二个位置往后放。

这样可以拼接出你想要的集合。但有个缺点,就是代码耦合性太大了。

2.3 方案3

之前,品牌苏三在第二页,最根本的原因是使用了edit_time字段进行逆序的。

也就是说,修改时间越大的越排在前面,而品牌苏三的修改时间很小,所以排在第二页了。

如果想品牌苏三,排在第一页,修改一下排序规则,不就搞定了?
可以改成按:id或者name字段排序。

用id字段排序,不太合适,虽说用了雪花算法,但跟修改时间类似,先插入的数据,会越小。

select * from brand where name like '%苏三%' 
order by id desc limit 5;

image3.png
用它排序的结果,跟使用修改时间排序差不了太多。图片看来,只能使用name字段排序了。

3.如何排序?

我们在sql中直接对name字段,进行升序或者降序吗?显然不是。使用name字段降序:

select * from brand where name like '%苏三%' 
order by name desc limit 5;

执行结果:
image4.png
图中并没有看到我们想要的数据。
其实,使用name字段升序,也可能在第一页查不出我们所想要的数据。
到底该如何处理呢?
假如,我们有这样一种排序:全匹配显示在最前面,比如:苏三。
数据左半部分匹配,右边按字母排序,比如:苏三1、苏三2、苏三说技术。

从中间开始匹配,比如:1苏三、2苏三。第2步和第3步,还要根据字符长度排序,字符短的排在前面,比如:1苏三、1苏三1、苏三说技术。

如果我们能实现上面的这种排序方式,这个问题就能完美解决了。
说起来容易,做起来难。

难道要先全匹配:name=‘苏三’,再有匹配:name like ‘苏三%’,再左匹配:name like ‘%苏三’,把查询三次的结果组装起来?
显然这种做法有点low。

要实现上面我们设想的排序方式,在es中更好处理一下,但在mysql中要怎么处理呢?

4.解决方案

其实,我们可以换一种思路,根据字符的长度排序。
mysql给我们提供了很多非常有用的函数,比如:char_length。
通过该函数就能获取字符长度。
sql调整如下:

select * from brand where name like '%苏三%' 
order by char_length(name) asc limit 5;

name字段使用关键字模糊查询之后,再使用char_length函数,获取name字段的字符长度,然后按长度升序。

仅这一个骚操作,就搞定需求了:
image5.png
我们所期待的:苏三,终于排在第一个了。同时由于该sql做了分页的,即使name字段在查询时丢失了索引,执行效率也不会太低。业务上的需求搞定了。

但追求完美的我们,好奇,想看看第二页是什么情况:

select * from brand where name like '%苏三%' 
order by char_length(name) asc limit 5,5;

执行结果:
image6.png

并没有按照我们设想的剧本进行下去,我们之前假设的3条排序中,第2条和第3条都没有满足。

这时该怎么办?

答:可以使用mysql中的locate函数,通过它可以匹配的关键字,在字符串中的位置。使用locate函数改造之后sql如下:

select * from brand where name like '%苏三%' 
order by char_length(name) asc, locate('苏三',name) asc limit 5,5;

执行结果:
image7.png
完美,终于出现我们想要的结果了。
除此之外,还可以使用:instr和position函数,它们的功能跟locate函数类似,在这里我就不一一介绍了,感兴趣的小伙伴可以找我私聊。

5. 总结

其实,模糊查询和分页,如果分开用,一般是没问题的。但如果它们要一起使用,一定要考虑排序问题。如果只是按照简单的时间或者id排序,有些特殊的业务场景,没办法满足,很容易出现bug。

当然解决上面问题,还有其他办法,比如:pageSize调大一点,或者把全匹配放到第一页。

但更优的方案,是通过mysql的函数来解决问题。我们可以通过mysql提供的:char_length、locate、instr和position函数等,来实现很多复杂的排序功能。

点赞收藏
苏三说技术
请先登录,查看1条精彩评论吧
快去登录吧,你将获得
  • 浏览更多精彩评论
  • 和开发者讨论交流,共同进步

为你推荐

【全网首发】(大表小技巧)有时候 2 小时的 SQL 操作,可能只要 1 分钟

【全网首发】(大表小技巧)有时候 2 小时的 SQL 操作,可能只要 1 分钟

分库分表真的适合你的系统吗?聊聊分库分表和NewSQL如何选择

分库分表真的适合你的系统吗?聊聊分库分表和NewSQL如何选择

为什么mysql的count()方法这么慢?

为什么mysql的count()方法这么慢?

​Mysql数据库查询好慢,除了索引,还能因为什么?

​Mysql数据库查询好慢,除了索引,还能因为什么?

数据库篇——hash索引

数据库篇——hash索引

我说MySQL联合索引遵循最左前缀匹配原则,面试官让我回去等通知

我说MySQL联合索引遵循最左前缀匹配原则,面试官让我回去等通知

4
1