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
lianxiaoyi
V2EX  ›  MySQL

mysql 的 like %字段% 如何优化

  •  
  •   lianxiaoyi · 2016-11-04 11:10:23 +08:00 · 10075 次点击
    这是一个创建于 2940 天前的主题,其中的信息可能已经有所发展或是发生改变。

    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
        1
    lianxiaoyi  
    OP
       2016-11-04 11:15:53 +08:00
    数据库引擎 innerDB ,不允许改动
    shimanooo
        2
    shimanooo  
       2016-11-04 11:16:47 +08:00   ❤️ 1
    holyghost
        3
    holyghost  
       2016-11-04 11:17:34 +08:00   ❤️ 1
    100w 再不上 es 说不过去了吧
    qiayue
        4
    qiayue  
       2016-11-04 11:18:07 +08:00   ❤️ 1
    如果数据实在太多,对需要 like 的字段做一个倒排索引,那就速度飞起了
    lianxiaoyi
        5
    lianxiaoyi  
    OP
       2016-11-04 11:23:44 +08:00
    @holyghost es ???不懂。。。。。

    @qiayue 我去百度查一下这个概念....跪谢大神
    lianxiaoyi
        6
    lianxiaoyi  
    OP
       2016-11-04 11:24:02 +08:00
    @shimanooo 感谢。。。我去看看。。。
    zwh8800
        7
    zwh8800  
       2016-11-04 11:37:58 +08:00   ❤️ 1
    把需要搜索的数据同步一份到 elasticsearch 上,然后重构整个搜索服务,所有用到 like 的地方都换成调用 elasticsearch 。

    一般企业里数据量过大之后,搜索都是这么做的。
    skyleft
        8
    skyleft  
       2016-11-04 11:42:02 +08:00   ❤️ 1
    5.6 以上 innodb 也支持 fulltext
    lianxiaoyi
        9
    lianxiaoyi  
    OP
       2016-11-04 11:45:22 +08:00
    @zwh8800 我曾想过 sphinx 。。。然后查询出一个 id ,,然后我再根据 id 去 in 查询。。。。但是工作量有点大。。毕竟程序员都很懒。。。啊哈哈。。。。实在不行就改吧 。。。。。。
    lianxiaoyi
        10
    lianxiaoyi  
    OP
       2016-11-04 11:46:36 +08:00
    @skyleft 我刚实验了一下。。。 varchar 字段没法使用 fulltext 索引哇。。。。。
    mooncakejs
        11
    mooncakejs  
       2016-11-04 12:03:44 +08:00   ❤️ 1
    100w 用 es 杀牛吗? fulltext 就好了
    lianxiaoyi
        12
    lianxiaoyi  
    OP
       2016-11-04 12:19:39 +08:00
    @mooncakejs 大神。。。。。我去试试。。。。。
    Felldeadbird
        13
    Felldeadbird  
       2016-11-04 12:27:24 +08:00   ❤️ 1
    升级到最新版 MYSQL , innodb 支持 fulltext 。好像 5.7 有支持中文全文搜索的方法。当然,数据量大了,还是用第三方的插件去做全文搜索吧。
    ebony0319
        14
    ebony0319  
       2016-11-04 12:36:53 +08:00   ❤️ 1
    我有大量的数据都是跟你一样的。我的经验是这样的。查两次。
    第一次在用户输入的时候去查数据库,然后自动补全。就像搜索你在淘宝搜索的时候给你补全。
    第二次在查的话就可以用=了。
    这样查的好处就是第一次查询其实速度是非常快的。如果第二个表联结很多表。可以使用到索引。
    wujunze
        15
    wujunze  
       2016-11-04 13:08:58 +08:00
    elasticsearch +1
    chaegumi
        16
    chaegumi  
       2016-11-04 14:05:35 +08:00
    @mooncakejs

    @lianxiaoyi

    果断 elasticsearch
    mooncakejs
        17
    mooncakejs  
       2016-11-04 14:08:35 +08:00   ❤️ 1
    @chaegumi 100w fulltext search 照样可以毫秒级搜索,免去了 es 同步 mysql 的问题。
    lianxiaoyi
        18
    lianxiaoyi  
    OP
       2016-11-04 14:17:44 +08:00
    @mooncakejs 我正在实验。。。啊哈哈
    enenaaa
        19
    enenaaa  
       2016-11-04 14:36:44 +08:00   ❤️ 1
    刚试过 5.7 的 innodb 虽然支持 fulltext , 但 3 个字以下的还是匹配不了,改了 ft_min_word_len 也不行, MyISAM 可以。
    由于中文分词问题,全文索引貌似也不太适合直接替换 like 。 可以考虑第三方, 或者分阶段分表查询
    lianxiaoyi
        20
    lianxiaoyi  
    OP
       2016-11-04 14:39:21 +08:00
    @enenaaa
    @mooncakejs
    大神 为啥
    SELECT * FROM `表` WHERE MATCH (字段 1) against ('2b4a640ec7617c652824' in boolean mode) ;

    查询不出数据啊??用 like 查是有 3 条数据的......
    enenaaa
        21
    enenaaa  
       2016-11-04 15:10:01 +08:00   ❤️ 1
    @lianxiaoyi 全文搜索基于词, 如果一长串字符没有空格逗号之类的分隔符的话, 会被认作单个单词。
    模糊匹配只能在字符串后面加*号, 不能加前面。
    http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html
    setonfocus
        22
    setonfocus  
       2016-11-04 15:13:22 +08:00
    用 elasticsearch 吧,数据量大,绝对不能 like
    lianxiaoyi
        23
    lianxiaoyi  
    OP
       2016-11-04 15:15:16 +08:00
    @enenaaa 对,如果后面带上*能查出以它开头的数据。。。。反正感觉不太适合我。。。。我放弃了。。。。
    xiyangzh
        24
    xiyangzh  
       2016-11-04 16:15:32 +08:00
    这是典型的联合索引没起作用啊。 不要用联合索引,在 text1 上弄个索引就行, 速度上就会有质的提升。

    题外话, 100w 数据真的是小 case , 如果索引得当, mysql 可以在 3 亿多的数据 20ms 内查询出结果,真实案例
    sampeng
        25
    sampeng  
       2016-11-04 16:23:17 +08:00
    全文搜索 elasticsearch +1

    因为部署开箱即用。。。。
    chaegumi
        26
    chaegumi  
       2016-11-04 16:38:20 +08:00
    @mooncakejs

    我的项目中,做了一个分表,主表 MRG_MYISAM 就不能用 fulltext.
    minipeach
        27
    minipeach  
       2016-11-04 16:38:35 +08:00   ❤️ 1
    @xiyangzh 有这么厉害?
    skyboy
        28
    skyboy  
       2016-11-04 17:21:09 +08:00   ❤️ 1
    换 postgrelsql 试试,性能更好。
    cxbig
        29
    cxbig  
       2016-11-04 17:44:25 +08:00   ❤️ 1
    这种级别用搜索引擎
    ES+1

    像 LIKE 或者 REGEXP 都是 debug 的时候用一下。
    setonfocus
        30
    setonfocus  
       2016-11-04 17:57:14 +08:00
    @xiyangzh 你不考虑并发的吗?
    NeinChn
        31
    NeinChn  
       2016-11-04 18:17:50 +08:00   ❤️ 1
    @xiyangzh 你这 20ms 是 TP99 么?
    3 亿数据能做到全文检索还 20ms 那真的挺厉害的。
    如果只是说普通的结构化查询 TP99 20ms ,那 20ms 还真不算快
    kemingcao
        32
    kemingcao  
       2016-11-04 18:20:26 +08:00   ❤️ 1
    配合 Elasticsearch 比较好,数据灌到里面, Like 应该轻松解决。
    anuxs
        33
    anuxs  
       2016-11-04 18:58:15 +08:00 via iPhone   ❤️ 1
    用 es ,有插件把 MySQL 同步到 es 。
    freestyle
        34
    freestyle  
       2016-11-05 08:39:18 +08:00 via iPhone   ❤️ 1
    @lianxiaoyi Sphinx Search 有过滤 id 的 filter 函数喔,而且速度非常快
    kekeones
        35
    kekeones  
       2016-11-05 17:45:55 +08:00 via iPhone
    elasticsearch +1
    lianxiaoyi
        36
    lianxiaoyi  
    OP
       2016-11-05 19:00:20 +08:00 via Android
    @freestyle 好像挺屌的样子!
    lianxiaoyi
        37
    lianxiaoyi  
    OP
       2016-11-05 19:01:18 +08:00 via Android
    @anuxs 我再研究研究! mysql 真脆!玩着玩着服务就起不来了!
    lianxiaoyi
        38
    lianxiaoyi  
    OP
       2016-11-05 19:01:57 +08:00 via Android
    @kemingcao 嘿嘿!程序猿都比较懒!
    fox0001
        39
    fox0001  
       2016-11-06 10:06:37 +08:00 via Android   ❤️ 1
    可以考虑全文索引。或者新建一个字段,把 text2 中的关键字抠出来
    xiyangzh
        40
    xiyangzh  
       2016-11-07 16:19:12 +08:00   ❤️ 1
    @NeinChn 我说的 20ms 是普通的结构化查询。

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

    你想么, 2kw 的数据,根据主键查询都得 20ms 左右, 何况上亿级别了。
    wsbnd9
        41
    wsbnd9  
       2016-11-07 17:08:05 +08:00
    es+1
    lianxiaoyi
        42
    lianxiaoyi  
    OP
       2016-11-07 17:21:27 +08:00
    @xiyangzh 好像听屌的。。。。我去写 2000 万数据看看 。。。。。。第一次玩这么大数据。。。。
    NeinChn
        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 内存以上。
    yuedingwangji
        44
    yuedingwangji  
       2016-11-08 00:20:57 +08:00   ❤️ 1
    @holyghost mysql 超过 100w 条就顶不住了?
    lianxiaoyi
        45
    lianxiaoyi  
    OP
       2016-11-08 09:29:28 +08:00
    @NeinChn 这是一个悲伤的故事。。。我们服务器才 4G 内存。。。。。数据库用的阿里云的 RDS 。。。。。。。
    lianxiaoyi
        46
    lianxiaoyi  
    OP
       2016-11-08 09:30:49 +08:00
    @yuedingwangji 100 万我估计还好 。。。。。像 like 右模糊查询或者=这种操作。。。。还是挺快的。。。。但是现在是左右模糊查询 。。。不走索引了。。。。 100 万就 TM 呵呵了。。。。
    NeinChn
        47
    NeinChn  
       2016-11-08 11:19:08 +08:00   ❤️ 1
    @lianxiaoyi
    2333 。配置差这就很尴尬了。上 ES 也没用啊, ES 内存小的时候性能差的无法忍受。
    你想想要随机读多少次磁盘....
    yuedingwangji
        48
    yuedingwangji  
       2016-11-08 11:54:55 +08:00   ❤️ 1
    @lianxiaoyi 不是吧 ,对这块不熟悉,不过我们公司 用的是 oracle ,我查询几百万的数据还是蛮快的说
    lianxiaoyi
        49
    lianxiaoyi  
    OP
       2016-11-08 12:18:20 +08:00
    @NeinChn 这就尴尬了。。。。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2645 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 02:01 · PVG 10:01 · LAX 18:01 · JFK 21:01
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.