前言
本文的测试是基于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 同步数据库。
这两种方式可以从根本上解决模糊查询的高延时,但是需要引入一套新的系统,代价还是不小的。文章来源:https://www.toymoban.com/news/detail-401278.html
二者的对比可参考:
Elasticsearch和Clickhouse基本查询对比
ClickHouse 与es比较文章来源地址https://www.toymoban.com/news/detail-401278.html
到了这里,关于mysql 模糊查询like优化方案(亲测)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!