mysql一千万的数据量如何一秒内实现模糊搜索?

数据库是mysql5.7,数据量就认为一千万吧,想在这一千万的数据量中实现高效的模糊查询,有什么好的办法么?
走不了索引,单字段搜索,要精确搜索, 类似于 select * from table where title like %关键词% limit 100 这种。
这里面加了个限制,只返回前100条数据。不然更慢了。

感觉无解了,因为模糊搜索,走不了索引,所以每次全量查询就很慢。大概在10s左右。

==================
试过了很多种方法,效果都不太理想。
1、换es这个方案就大可不必了,划不着,没有精力再去维护一个es,还得数据同步,最重要是占内存。
2、mysql的分词索引这个方案也试过了,非常难用,而且对中文支持极其不友好,而且搜索不精准。
3、自己手动维护一个索引表这个方案也考虑过,感觉也不太可行,增加代码复杂度,而且分词导致搜索不准确。
4、分库分表这个方案也不用推荐了哈。

不要借助第三方的中间件哈,比如es,大数据数据库等。

目前只有一个 mysql + java程序。目前想到的有一个可行的方案是这样的,将数据全部加载到内存中,
在内存中实现模糊搜索,效果确实是很快,测试了3百万的数据量,都是在500ms内完成的。

但是问题点在于,太太太占内存了,差不多一百万的数据量占100M的内存空间。但是分给整个java的
堆内存才512M。也就是说,除了java程序自己本身消耗的内存, 也就最多 缓存300W条的数据量差不多,

加不了内存哈,就这么大的内存了,所以说,在不加内存的情况下 如何实现快速模糊搜索呢?
希望大佬指点一下,在这先谢谢了。

内存这个是真的加不了,毕竟现在服务器死贵。能给到java程序的最多也就512M的内存了,所以有没有
可以从jvm优化这块下手的,或者其他方案来实现的呢?

对了,不知道everything是如何实现搜索的,这个搜索起来真的是飞快,有大佬知道么?

回复
阅读 7.7k
12 个回答

数据库新手,一个很简单的想法,为嘛不能做个类似下表的索引呢?

索引表

当前词下一词原记录主键ID
mysql1
1
1
1
………………
1
1
1
null1

搜索“模糊搜索”

SELECT 原记录主键ID
  FROM (SELECT 原记录主键ID FROM 索引表 WHERE 当前词 = '模' AND 下一词 = '糊')
  JOIN (SELECT 原记录主键ID FROM 索引表 WHERE 当前词 = '糊' AND 下一词 = '搜') USING(原记录主键ID)
  JOIN (SELECT 原记录主键ID FROM 索引表 WHERE 当前词 = '搜' AND 下一词 = '索') USING(原记录主键ID)
  JOIN (SELECT 原记录主键ID FROM 索引表 WHERE 当前词 = '索' AND 下一词 IS NULL) USING(原记录主键ID)

检索大多数情况都是空间换时间,没有内存那基本无解

我们有1.2亿条数据,大概占了300多G存储空间,要是全部加到内存里查立马破产

新手上路,请多包涵

MySQL使用全文索引+ngram全文解析器进行全文检索

新手上路,请多包涵

如果数据分冷热,可以加载5%或者10%的数据到内存。然后根据lfu、lru算法来更新热点数据。内存的没有的数据库里面查,不至于服务中断。不然无解。

为啥100万的数据需要100M的内存空间呢,如果你只加载 title 和 id 到内存中,title 最坏情况都是汉字,采用 UTF-8 编码,长度最大为100 即 300 字节,id 是 long 类型 占 8 字节 300 + 8 + 32 (不确定但差不多 对象头或者其他) 总共 340 字节,如果乘以 1000 万 总共也就 300 多M 虽然没解决你的问题

新手上路,请多包涵

除了ES还有个Sphinx.也是使用倒排索引的方式。
直接链接数据库,字段都一样,但是缺点就是需要自己做数据同步(一行命令就ok)。
比ES轻了一点。

为了在一秒内实现 MySQL 中数据量为一千万的表的模糊搜索,你需要考虑以下几点:

  1. 确保你的搜索语句使用了索引。如果没有索引,MySQL 将会扫描整张表,这将会非常慢。
  2. 尽可能地减少搜索的范围。例如,如果你只想搜索某一列,可以使用 WHERE column LIKE '%keyword%' 的形式来减少搜索范围。
  3. 使用 Full-Text Search(全文搜索)。这是 MySQL 的一项特性,能够快速地搜索大型文本字段。
  4. 在表上建立足够多的索引,以便 MySQL 可以快速地找到你要搜索的数据。
  5. 考虑使用其他的搜索引擎,例如 Elasticsearch,它可以在大型数据集中进行高效的搜索。

分页查询 + 利用主键索引和 mysql 缓存,sql 类似于,select * from table where id>=上次查询最后匹配的数据主键(初始1)title like %关键词% limit 100(数量多调整测试),不知道有没有用

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题
宣传栏