mysql 的 like %字段% 如何优化

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

这是一个创建于 2942 天前的主题，其中的信息可能已经有所发展或是发生改变。

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 有联合索引

第 1 条附言 · 2016-11-04 15:15:36 +08:00

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

49 条回复 • 2016-11-08 12:18:20 +08:00

lianxiaoyi

2016-11-04 11:15:53 +08:00

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

shimanooo

2016-11-04 11:16:47 +08:00

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

holyghost

2016-11-04 11:17:34 +08:00

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

qiayue

2016-11-04 11:18:07 +08:00

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

lianxiaoyi

2016-11-04 11:23:44 +08:00

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

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

lianxiaoyi

2016-11-04 11:24:02 +08:00

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

zwh8800

2016-11-04 11:37:58 +08:00

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

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

skyleft

2016-11-04 11:42:02 +08:00

5.6 以上 innodb 也支持 fulltext

lianxiaoyi

2016-11-04 11:45:22 +08:00

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

lianxiaoyi

2016-11-04 11:46:36 +08:00

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

mooncakejs

2016-11-04 12:03:44 +08:00

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

lianxiaoyi

2016-11-04 12:19:39 +08:00

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

Felldeadbird

2016-11-04 12:27:24 +08:00

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

ebony0319

2016-11-04 12:36:53 +08:00

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

wujunze

2016-11-04 13:08:58 +08:00

elasticsearch +1

chaegumi

2016-11-04 14:05:35 +08:00

@mooncakejs

@lianxiaoyi

果断 elasticsearch

mooncakejs

2016-11-04 14:08:35 +08:00

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

lianxiaoyi

2016-11-04 14:17:44 +08:00

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

enenaaa

2016-11-04 14:36:44 +08:00

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

lianxiaoyi

2016-11-04 14:39:21 +08:00

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

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

enenaaa

2016-11-04 15:10:01 +08:00

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

setonfocus

2016-11-04 15:13:22 +08:00

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

lianxiaoyi

2016-11-04 15:15:16 +08:00

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

xiyangzh

2016-11-04 16:15:32 +08:00

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

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

sampeng

2016-11-04 16:23:17 +08:00

全文搜索 elasticsearch +1

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

chaegumi

2016-11-04 16:38:20 +08:00

@mooncakejs

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

minipeach

2016-11-04 16:38:35 +08:00

@xiyangzh 有这么厉害？

skyboy

2016-11-04 17:21:09 +08:00

换 postgrelsql 试试，性能更好。

cxbig

2016-11-04 17:44:25 +08:00

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

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

setonfocus

2016-11-04 17:57:14 +08:00

@xiyangzh 你不考虑并发的吗？

NeinChn

2016-11-04 18:17:50 +08:00

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

kemingcao

2016-11-04 18:20:26 +08:00

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

anuxs

2016-11-04 18:58:15 +08:00 via iPhone

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

freestyle

2016-11-05 08:39:18 +08:00 via iPhone

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

kekeones

2016-11-05 17:45:55 +08:00 via iPhone

elasticsearch +1

lianxiaoyi

2016-11-05 19:00:20 +08:00 via Android

@freestyle 好像挺屌的样子！

lianxiaoyi

2016-11-05 19:01:18 +08:00 via Android

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

lianxiaoyi

2016-11-05 19:01:57 +08:00 via Android

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

fox0001

2016-11-06 10:06:37 +08:00 via Android

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

xiyangzh

2016-11-07 16:19:12 +08:00

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

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

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

wsbnd9

2016-11-07 17:08:05 +08:00

es+1

lianxiaoyi

2016-11-07 17:21:27 +08:00

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

NeinChn

2016-11-07 19:39:43 +08:00

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

yuedingwangji

2016-11-08 00:20:57 +08:00

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

lianxiaoyi

2016-11-08 09:29:28 +08:00

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

lianxiaoyi

2016-11-08 09:30:49 +08:00

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

NeinChn

2016-11-08 11:19:08 +08:00

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

yuedingwangji

2016-11-08 11:54:55 +08:00

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

lianxiaoyi

2016-11-08 12:18:20 +08:00

@NeinChn 这就尴尬了。。。。