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比较
————————————————
版权声明：本文为CSDN博主「终成一个大象」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Martin_chen2/article/details/121407417