mysql 模糊查询like优化方案(亲测)

这篇具有很好参考价值的文章主要介绍了mysql 模糊查询like优化方案(亲测)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

前言

本文的测试是基于740w条测试数据进行的,只讨论like模糊查询的优化方案。其他SQL优化可参考:
SQL优化的几种方式

查询开头是“今天不开心”的聊天记录,是可以走索引的。

select * from message_1 where content like "今天不开心%;

查询包含“今天不开心”的聊天记录,是不能走索引的。

select * from message_1 where content like "%今天不开心%";

咱们主要优化的是第二种情况,我本人测试查询耗时是在9秒。

优化方案

对于查询包含某个关键词的需求,从业务上来说应尽量避免这种不合理的需求。

但是实际使用中,总有些类似需求避免不掉模糊查询,就可以采取下列优化方式。

  • 稍微优化
select * from message_1 where instr(content, "今天不开心") > 0;
select  * from message_1 where locate("今天不开心", content) > 0;

这个方法优化效果有限,这两种方法耗时相差不多,比不优化要快上2~3秒。

我还测试了一些其他的一些情况,这种优化方式,在某些情况下会比优化前还要慢,由此可见这种方式是有坑的

比如优化前:

select content from message_1 where content like "%今天不开心%";

优化后:

select content from message_1 where instr(content, "今天不开心") > 0;

select content from message_1 where locate("今天不开心", content) > 0;

这种情况,优化后比不优化要慢上2秒左右。。。。

  • 大幅度优化
select * from message_1 where content in 
(select content from message_1 where content like "%今天不开心%");

这种方法,能将查询优化至3秒左右,优化效果已经很明显。

优化原理:用索引全扫描取代表的全扫描。因为索引全扫描的代价是全表扫描的1/N (即索引块数与数据块数的比例),表数据越多,优化效果越明显。

优化后的sql语句,根据索引再回表的代价要看符合条件的记录数多少:如果in子查询返回的记录数很少,那么优化的效果就相当于效率提高了N倍;如果in子查询返回的记录数较多,两种SQL的性能区别就不是很明显了。

  • 根本优化

使用ClickHouse 或者 Elasticsearch 同步数据库。

这两种方式可以从根本上解决模糊查询的高延时,但是需要引入一套新的系统,代价还是不小的。

二者的对比可参考:
Elasticsearch和Clickhouse基本查询对比
ClickHouse 与es比较文章来源地址https://www.toymoban.com/news/detail-401278.html

到了这里,关于mysql 模糊查询like优化方案(亲测)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL中如何高效的实现模糊查询(附30条优化建议)

    在使用msyql进行模糊查询的时候,很自然的会用到like语句,通常情况下,在数据量小的时候,不容易看出查询的效率,但在数据量达到百万级,千万级的时候,查询的效率就很容易显现出来。这个时候查询的效率就显得很重要! 一般情况下like模糊查询的写法为(field已建立索

    2024年01月22日
    浏览(53)
  • 【性能优化】MySql数据库查询优化方案

    了解系统运行效率提升的整体解决思路和方向 学会MySQl中进行数据库查询优化的步骤 学会看慢查询、执行计划、进行性能分析、调优 ​关于这个问题,我们通常首先考虑的是硬件升级,毕竟服务器的内存、CPU、磁盘IO速度 、网络速度等都是制约我们系统快慢的首要因素。硬

    2024年02月03日
    浏览(57)
  • MySQL查询优化方案汇总(索引相关)

    类型隐式转换 大坑 大数据深度分页,用主键 避免使用MySQL函数 避免类型的隐式转换 避免使用函数或表达式,尽量只让数据库做纯粹的增删改查。 避免使用不等值做排除法 避免使用null值 减少大字段查询,避免使用*,不说磁盘io的损耗,连网络带宽都跟着损耗。 如果只sel

    2024年03月09日
    浏览(45)
  • Mysql如何优化数据查询方案

    mysql做读写分离 读写分离是提高mysql并发的首选方案。 Mysql主从复制的原理 mysql的主从复制依赖于binlog,也就是记录mysql上的所有变化并以二进制的形式保存在磁盘上,复制的过程就是将binlog中的数据从主库传输到从库上。 主从复制过程详细分为3个阶段: 第一阶段:主库写

    2024年02月21日
    浏览(43)
  • MySQL ——多条件查询(like)

    MySQL LIKE多条件查询语句的基本语法如下:         在上面的多条件查询语句中,%是通配符,表示任意字符。如果您在LIKE语句中使用%字符,则表示查询结果将返回具有指定值的任何字符。因此,以上语句将返回同时在column1和column2中具有value1和value2的所有行。此外,如果您在

    2024年02月03日
    浏览(43)
  • 微信小程序 连接云数据库(不使用云函数)进行 登录、注册、查询(包括模糊查询)快速实现 亲测可用

    当连接MySQL的时候总是出现各种各样的小问题,可以选用微信小程序自带的云数据库 注:测试号不能建立云数据库 按图中步骤来 第4步中,有两种添加方式 第一种 第二种 注意: 如果你是粘贴复制的,他会报错 非数字字符\\\"无法使用外部字符串表示法位于第1行 。这时,在第

    2023年04月20日
    浏览(55)
  • elasticsearch7.17.3 实现类似mysql的like查询

    前言:现在想要实现在elasticsearch中类似于mysql的like查询方式,有下面几种方法可以参考 建议 :wildcard方法是纯纯的like查询方式平替,但是性能差,上百GB的数据量后就会很慢。根据自己业务量需求,前面两种方式能解决的情况下尽量用前面两种方式。前两种方式可以修改索

    2024年02月08日
    浏览(47)
  • springboot整合elasticsearch实现类似于mysql的like查询

    目录 一、ES分页查询常用方式 二、引入es的依赖 三、es配置文件 四、es工具类 五、分页查询示例 1.from + size from表示从第几行开始,size表示查询多少条文档。from默认为0,size默认为10,最灵活的分页方式。 2.scroll 不适合用来做实时搜索,而更适用于后台批处理任务,如日志导

    2023年04月09日
    浏览(36)
  • 大数据量模糊查询优化(流去重,流分批,建树操作)

    有时候会进行大数据量查询后的建树操作,如果直接使用sql语句会大大延缓业务效率,因此需要优化,本文章介绍了使用java8流操作以及hutool树工具栏的建树操作。 流是支持各种方法的对象序列(一系列对象,列入:LIst对象) 代码如下(示例): 代码如下(示例): 代码如

    2024年02月13日
    浏览(43)
  • ES模糊查询失效的坑,附解决方案

    最近在做需求开发的时候,有个需求,需要根据去ES中进行模糊搜索。于是首先想到了wildcard查询,根据,利用*通配符,类似于mysql中的like一样进行模糊搜索。 但是遇到了问题就是,根据单个汉字能正常模糊搜索,多个汉字就无法查询出数据。 单个汉字正常查询

    2024年02月11日
    浏览(52)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包