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
lianxiaoyi OP 数据库引擎 innerDB ,不允许改动
|
2
shimanooo 2016-11-04 11:16:47 +08:00 1
|
3
holyghost 2016-11-04 11:17:34 +08:00 1
100w 再不上 es 说不过去了吧
|
4
qiayue 2016-11-04 11:18:07 +08:00 1
如果数据实在太多,对需要 like 的字段做一个倒排索引,那就速度飞起了
|
5
lianxiaoyi OP |
6
lianxiaoyi OP @shimanooo 感谢。。。我去看看。。。
|
7
zwh8800 2016-11-04 11:37:58 +08:00 1
把需要搜索的数据同步一份到 elasticsearch 上,然后重构整个搜索服务,所有用到 like 的地方都换成调用 elasticsearch 。
一般企业里数据量过大之后,搜索都是这么做的。 |
8
skyleft 2016-11-04 11:42:02 +08:00 1
5.6 以上 innodb 也支持 fulltext
|
9
lianxiaoyi OP @zwh8800 我曾想过 sphinx 。。。然后查询出一个 id ,,然后我再根据 id 去 in 查询。。。。但是工作量有点大。。毕竟程序员都很懒。。。啊哈哈。。。。实在不行就改吧 。。。。。。
|
10
lianxiaoyi OP @skyleft 我刚实验了一下。。。 varchar 字段没法使用 fulltext 索引哇。。。。。
|
11
mooncakejs 2016-11-04 12:03:44 +08:00 1
100w 用 es 杀牛吗? fulltext 就好了
|
12
lianxiaoyi OP @mooncakejs 大神。。。。。我去试试。。。。。
|
13
Felldeadbird 2016-11-04 12:27:24 +08:00 1
升级到最新版 MYSQL , innodb 支持 fulltext 。好像 5.7 有支持中文全文搜索的方法。当然,数据量大了,还是用第三方的插件去做全文搜索吧。
|
14
ebony0319 2016-11-04 12:36:53 +08:00 1
我有大量的数据都是跟你一样的。我的经验是这样的。查两次。
第一次在用户输入的时候去查数据库,然后自动补全。就像搜索你在淘宝搜索的时候给你补全。 第二次在查的话就可以用=了。 这样查的好处就是第一次查询其实速度是非常快的。如果第二个表联结很多表。可以使用到索引。 |
15
wujunze 2016-11-04 13:08:58 +08:00
elasticsearch +1
|
16
chaegumi 2016-11-04 14:05:35 +08:00
|
17
mooncakejs 2016-11-04 14:08:35 +08:00 1
@chaegumi 100w fulltext search 照样可以毫秒级搜索,免去了 es 同步 mysql 的问题。
|
18
lianxiaoyi OP @mooncakejs 我正在实验。。。啊哈哈
|
19
enenaaa 2016-11-04 14:36:44 +08:00 1
刚试过 5.7 的 innodb 虽然支持 fulltext , 但 3 个字以下的还是匹配不了,改了 ft_min_word_len 也不行, MyISAM 可以。
由于中文分词问题,全文索引貌似也不太适合直接替换 like 。 可以考虑第三方, 或者分阶段分表查询 |
20
lianxiaoyi OP @enenaaa
@mooncakejs 大神 为啥 SELECT * FROM `表` WHERE MATCH (字段 1) against ('2b4a640ec7617c652824' in boolean mode) ; 查询不出数据啊??用 like 查是有 3 条数据的...... |
21
enenaaa 2016-11-04 15:10:01 +08:00 1
@lianxiaoyi 全文搜索基于词, 如果一长串字符没有空格逗号之类的分隔符的话, 会被认作单个单词。
模糊匹配只能在字符串后面加*号, 不能加前面。 http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html |
22
setonfocus 2016-11-04 15:13:22 +08:00
用 elasticsearch 吧,数据量大,绝对不能 like
|
23
lianxiaoyi OP @enenaaa 对,如果后面带上*能查出以它开头的数据。。。。反正感觉不太适合我。。。。我放弃了。。。。
|
24
xiyangzh 2016-11-04 16:15:32 +08:00
这是典型的联合索引没起作用啊。 不要用联合索引,在 text1 上弄个索引就行, 速度上就会有质的提升。
题外话, 100w 数据真的是小 case , 如果索引得当, mysql 可以在 3 亿多的数据 20ms 内查询出结果,真实案例 |
25
sampeng 2016-11-04 16:23:17 +08:00
全文搜索 elasticsearch +1
因为部署开箱即用。。。。 |
26
chaegumi 2016-11-04 16:38:20 +08:00
|
28
skyboy 2016-11-04 17:21:09 +08:00 1
换 postgrelsql 试试,性能更好。
|
29
cxbig 2016-11-04 17:44:25 +08:00 1
这种级别用搜索引擎
ES+1 像 LIKE 或者 REGEXP 都是 debug 的时候用一下。 |
30
setonfocus 2016-11-04 17:57:14 +08:00
@xiyangzh 你不考虑并发的吗?
|
31
NeinChn 2016-11-04 18:17:50 +08:00 1
|
32
kemingcao 2016-11-04 18:20:26 +08:00 1
配合 Elasticsearch 比较好,数据灌到里面, Like 应该轻松解决。
|
33
anuxs 2016-11-04 18:58:15 +08:00 via iPhone 1
用 es ,有插件把 MySQL 同步到 es 。
|
34
freestyle 2016-11-05 08:39:18 +08:00 via iPhone 1
@lianxiaoyi Sphinx Search 有过滤 id 的 filter 函数喔,而且速度非常快
|
35
kekeones 2016-11-05 17:45:55 +08:00 via iPhone
elasticsearch +1
|
36
lianxiaoyi OP @freestyle 好像挺屌的样子!
|
37
lianxiaoyi OP @anuxs 我再研究研究! mysql 真脆!玩着玩着服务就起不来了!
|
38
lianxiaoyi OP @kemingcao 嘿嘿!程序猿都比较懒!
|
39
fox0001 2016-11-06 10:06:37 +08:00 via Android 1
可以考虑全文索引。或者新建一个字段,把 text2 中的关键字抠出来
|
40
xiyangzh 2016-11-07 16:19:12 +08:00 1
@NeinChn 我说的 20ms 是普通的结构化查询。
纠正你一点,普通的结构化查询要在亿级的数据做到 20ms 一下也是很困难的。 给你一个真实案例, 2kw 级的数据, 根据 id 查询, mysql 都需要 17ms 。 你想么, 2kw 的数据,根据主键查询都得 20ms 左右, 何况上亿级别了。 |
41
wsbnd9 2016-11-07 17:08:05 +08:00
es+1
|
42
lianxiaoyi OP @xiyangzh 好像听屌的。。。。我去写 2000 万数据看看 。。。。。。第一次玩这么大数据。。。。
|
43
NeinChn 2016-11-07 19:39:43 +08:00 1
@xiyangzh
我们用 ES 做到了 1 亿数据 AVG RESP 20ms 虽然 TP99 并不好看... 数据库 2kw 用主键也没见 AVG 需要 20ms 的,大概内存太小需要扫磁盘,而且硬盘不是 SSD ?就 2wk 而言很容易命中缓存的,毕竟才 2kw 数据,服务器都是 128G 内存以上。 |
44
yuedingwangji 2016-11-08 00:20:57 +08:00 1
@holyghost mysql 超过 100w 条就顶不住了?
|
45
lianxiaoyi OP @NeinChn 这是一个悲伤的故事。。。我们服务器才 4G 内存。。。。。数据库用的阿里云的 RDS 。。。。。。。
|
46
lianxiaoyi OP @yuedingwangji 100 万我估计还好 。。。。。像 like 右模糊查询或者=这种操作。。。。还是挺快的。。。。但是现在是左右模糊查询 。。。不走索引了。。。。 100 万就 TM 呵呵了。。。。
|
47
NeinChn 2016-11-08 11:19:08 +08:00 1
|
48
yuedingwangji 2016-11-08 11:54:55 +08:00 1
@lianxiaoyi 不是吧 ,对这块不熟悉,不过我们公司 用的是 oracle ,我查询几百万的数据还是蛮快的说
|
49
lianxiaoyi OP @NeinChn 这就尴尬了。。。。
|