mysql 的 like %字段% 如何优化

[lianxiaoyi]

mysql 在 like %字段%情况下没法使用索引,现有 sql

SELECT * FROM 表 WHERE (text1=100) AND text2 LIKE "%ssss%" order by id desc LIMIT 20

在 100 多万条数据下挂了

后来改了一版

SELECT * FROM 表 WHERE (text1=113) AND INSTR(text1, "ssss") order by id desc LIMIT 20

符合 text1 值比较少的情况下速度还行吧，但是多了之后也挂了

text1 和 text2 有联合索引

我已经放弃了。。。。。老老实实用搜索引擎吧 。。。。。。

[lianxiaoyi]

数据库引擎 innerDB ，不允许改动

[shimanooo]

https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

[holyghost]

100w 再不上 es 说不过去了吧

[qiayue]

如果数据实在太多，对需要 like 的字段做一个倒排索引，那就速度飞起了

[lianxiaoyi]

@holyghost es ???不懂。。。。。

@qiayue 我去百度查一下这个概念....跪谢大神

[lianxiaoyi]

@shimanooo 感谢。。。我去看看。。。

[zwh8800]

把需要搜索的数据同步一份到 elasticsearch 上，然后重构整个搜索服务，所有用到 like 的地方都换成调用 elasticsearch 。

一般企业里数据量过大之后，搜索都是这么做的。

[skyleft]

5.6 以上 innodb 也支持 fulltext

[lianxiaoyi]

@zwh8800 我曾想过 sphinx 。。。然后查询出一个 id ，，然后我再根据 id 去 in 查询。。。。但是工作量有点大。。毕竟程序员都很懒。。。啊哈哈。。。。实在不行就改吧 。。。。。。

[lianxiaoyi]

@skyleft 我刚实验了一下。。。 varchar 字段没法使用 fulltext 索引哇。。。。。

[mooncakejs]

100w 用 es 杀牛吗？ fulltext 就好了

[lianxiaoyi]

@mooncakejs 大神。。。。。我去试试。。。。。

[Felldeadbird]

升级到最新版 MYSQL ， innodb 支持 fulltext 。好像 5.7 有支持中文全文搜索的方法。当然，数据量大了，还是用第三方的插件去做全文搜索吧。

[ebony0319]

我有大量的数据都是跟你一样的。我的经验是这样的。查两次。
第一次在用户输入的时候去查数据库，然后自动补全。就像搜索你在淘宝搜索的时候给你补全。
第二次在查的话就可以用=了。
这样查的好处就是第一次查询其实速度是非常快的。如果第二个表联结很多表。可以使用到索引。

[wujunze]

elasticsearch +1

[chaegumi]

@mooncakejs

@lianxiaoyi

果断 elasticsearch

[mooncakejs]

@chaegumi 100w fulltext search 照样可以毫秒级搜索，免去了 es 同步 mysql 的问题。

[lianxiaoyi]

@mooncakejs 我正在实验。。。啊哈哈

[enenaaa]

刚试过 5.7 的 innodb 虽然支持 fulltext ， 但 3 个字以下的还是匹配不了，改了 ft_min_word_len 也不行， MyISAM 可以。
由于中文分词问题，全文索引貌似也不太适合直接替换 like 。 可以考虑第三方， 或者分阶段分表查询

[lianxiaoyi]

@enenaaa
@mooncakejs
大神 为啥
SELECT * FROM `表` WHERE MATCH (字段 1) against ('2b4a640ec7617c652824' in boolean mode) ;

查询不出数据啊？？用 like 查是有 3 条数据的......

[enenaaa]

@lianxiaoyi 全文搜索基于词， 如果一长串字符没有空格逗号之类的分隔符的话， 会被认作单个单词。
模糊匹配只能在字符串后面加*号， 不能加前面。
http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html

[setonfocus]

用 elasticsearch 吧，数据量大，绝对不能 like

[lianxiaoyi]

@enenaaa 对,如果后面带上*能查出以它开头的数据。。。。反正感觉不太适合我。。。。我放弃了。。。。

[xiyangzh]

这是典型的联合索引没起作用啊。 不要用联合索引，在 text1 上弄个索引就行， 速度上就会有质的提升。

题外话， 100w 数据真的是小 case ， 如果索引得当， mysql 可以在 3 亿多的数据 20ms 内查询出结果，真实案例

[sampeng]

全文搜索 elasticsearch +1

因为部署开箱即用。。。。

[chaegumi]

@mooncakejs

我的项目中，做了一个分表，主表 MRG_MYISAM 就不能用 fulltext.

[minipeach]

@xiyangzh 有这么厉害？

[skyboy]

换 postgrelsql 试试，性能更好。

[cxbig]

这种级别用搜索引擎
ES+1

像 LIKE 或者 REGEXP 都是 debug 的时候用一下。

[setonfocus]

@xiyangzh 你不考虑并发的吗？

[NeinChn]

@xiyangzh 你这 20ms 是 TP99 么？
3 亿数据能做到全文检索还 20ms 那真的挺厉害的。
如果只是说普通的结构化查询 TP99 20ms ，那 20ms 还真不算快

[kemingcao]

配合 Elasticsearch 比较好，数据灌到里面， Like 应该轻松解决。

[anuxs]

用 es ，有插件把 MySQL 同步到 es 。

[freestyle]

@lianxiaoyi Sphinx Search 有过滤 id 的 filter 函数喔，而且速度非常快

[kekeones]

elasticsearch +1

[lianxiaoyi]

@freestyle 好像挺屌的样子！

[lianxiaoyi]

@anuxs 我再研究研究！ mysql 真脆！玩着玩着服务就起不来了！

[lianxiaoyi]

@kemingcao 嘿嘿！程序猿都比较懒！

[fox0001]

可以考虑全文索引。或者新建一个字段，把 text2 中的关键字抠出来

[xiyangzh]

@NeinChn 我说的 20ms 是普通的结构化查询。

纠正你一点，普通的结构化查询要在亿级的数据做到 20ms 一下也是很困难的。
给你一个真实案例， 2kw 级的数据， 根据 id 查询， mysql 都需要 17ms 。

你想么， 2kw 的数据，根据主键查询都得 20ms 左右， 何况上亿级别了。

[wsbnd9]

es+1

[lianxiaoyi]

@xiyangzh 好像听屌的。。。。我去写 2000 万数据看看 。。。。。。第一次玩这么大数据。。。。

[NeinChn]

@xiyangzh
我们用 ES 做到了 1 亿数据 AVG RESP 20ms
虽然 TP99 并不好看...
数据库 2kw 用主键也没见 AVG 需要 20ms 的，大概内存太小需要扫磁盘，而且硬盘不是 SSD ？就 2wk 而言很容易命中缓存的，毕竟才 2kw 数据，服务器都是 128G 内存以上。

[yuedingwangji]

@holyghost mysql 超过 100w 条就顶不住了？

[lianxiaoyi]

@NeinChn 这是一个悲伤的故事。。。我们服务器才 4G 内存。。。。。数据库用的阿里云的 RDS 。。。。。。。

[lianxiaoyi]

@yuedingwangji 100 万我估计还好 。。。。。像 like 右模糊查询或者=这种操作。。。。还是挺快的。。。。但是现在是左右模糊查询 。。。不走索引了。。。。 100 万就 TM 呵呵了。。。。

[NeinChn]

@lianxiaoyi
2333 。配置差这就很尴尬了。上 ES 也没用啊， ES 内存小的时候性能差的无法忍受。
你想想要随机读多少次磁盘....

[yuedingwangji]

@lianxiaoyi 不是吧 ，对这块不熟悉，不过我们公司 用的是 oracle ，我查询几百万的数据还是蛮快的说

[lianxiaoyi]

@NeinChn 这就尴尬了。。。。