V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
chinafengzhao
V2EX  ›  数据库

大数据量模糊匹配有快速响应的优化方案吗?

  •  1
     
  •   chinafengzhao · 2021-04-14 10:04:49 +08:00 · 2440 次点击
    这是一个创建于 1304 天前的主题,其中的信息可能已经有所发展或是发生改变。

    背景

    MySQL,单表数据量超过 2000 万,需求是要求快速模糊查询。

    select * from xxx where aaa like '%yyyyy' ;

    有快速优化方案吗?

    31 条回复    2021-04-15 06:13:39 +08:00
    johnsona
        1
    johnsona  
       2021-04-14 10:10:02 +08:00 via iPhone   ❤️ 1
    加一列把原来的列 reverse 一下 这样就能用到索引了( bushi
    lscexpress
        2
    lscexpress  
       2021-04-14 10:10:11 +08:00
    使用 fulltext
    Justin13
        3
    Justin13  
       2021-04-14 10:16:54 +08:00 via Android
    对实时性要求不高可以加缓存,把查询结果存储起来。
    要求实时但是查询是固定的话,可以加一索引列,存储查询的结果,写入时一并添加。
    如果什么都不能做,只能改查询,那就洗洗睡吧。
    simon1991
        4
    simon1991  
       2021-04-14 10:18:40 +08:00
    上 elasticsearch ()
    MinQ
        5
    MinQ  
       2021-04-14 10:20:12 +08:00
    上 ES 或者 Solr 吧
    MinQ
        6
    MinQ  
       2021-04-14 10:21:04 +08:00
    而且像 xxxx%的前缀查询才能用索引,%yyyyy 建议直接放弃治疗
    3dwelcome
        7
    3dwelcome  
       2021-04-14 10:21:52 +08:00
    @johnsona "加一列把原来的列 reverse 一下 这样就能用到索引了( bushi"

    我觉得 reverse 这种,新数据库已经自己优化掉了。要不然 2000 万条数据无索引暴力查询,不知道慢到什么程度了。
    chinafengzhao
        8
    chinafengzhao  
    OP
       2021-04-14 10:30:16 +08:00
    不好意思,查询逻辑要求的是 like %企业名关键词%
    MinQ
        9
    MinQ  
       2021-04-14 10:30:37 +08:00
    @3dwelcome mysql 是没有优化的,需要自己新增,比如取前 N 个 reverse 然后存下来
    MinQ
        10
    MinQ  
       2021-04-14 10:33:04 +08:00
    @chinafengzhao 还是 fulltext 或者 ES 吧
    chinafengzhao
        11
    chinafengzhao  
    OP
       2021-04-14 10:38:43 +08:00
    我试试 fulltext
    3dwelcome
        12
    3dwelcome  
       2021-04-14 10:39:31 +08:00
    @MinQ "mysql 是没有优化的,需要自己新增,比如取前 N 个 reverse 然后存下来"
    新版本已经加上了,有新的创建 index 语法, DESC 关键词, 不用手动 reverse 。
    leviathan0992
        13
    leviathan0992  
       2021-04-14 10:39:47 +08:00
    全文索引
    Drinker
        14
    Drinker  
       2021-04-14 10:44:36 +08:00
    es 可以或者分表。
    securityCoding
        15
    securityCoding  
       2021-04-14 10:45:49 +08:00
    2000 万表这个数量级应该禁止用 like 了 , 推荐用其他的中间件来解决这个问题,比如 es
    MinQ
        16
    MinQ  
       2021-04-14 10:48:51 +08:00   ❤️ 1
    @3dwelcome
    你说的是这个吗?

    https://dev.mysql.com/doc/refman/8.0/en/create-index.html

    A key_part specification can end with ASC or DESC to specify whether index values are stored in ascending or descending order. The default is ascending if no order specifier is given. ASC and DESC are not permitted for HASH indexes. ASC and DESC are also not supported for multi-valued indexes. As of MySQL 8.0.12, ASC and DESC are not permitted for SPATIAL indexes.

    好像这个只是吧索引的顺序按从小到大或者从大到小排吧?而不是把某一列的字符 reverse 以后生成索引?
    fiypig
        17
    fiypig  
       2021-04-14 11:02:55 +08:00
    %% 基本上索引就失效了
    3dwelcome
        18
    3dwelcome  
       2021-04-14 11:11:52 +08:00   ❤️ 1
    @MinQ 记错了,那就是 CREATE INDEX idx1 ON table1 REVERSE(col1);

    REVERSE 是对针对字符串处理的。
    MinQ
        19
    MinQ  
       2021-04-14 11:19:16 +08:00
    @3dwelcome create index 的时候不能使用 reverse 吧
    wakzz
        20
    wakzz  
       2021-04-14 11:21:53 +08:00
    这种模糊查询,mysql 还是放弃吧,用 es
    catchexception
        21
    catchexception  
       2021-04-14 11:28:26 +08:00
    倒排索引。
    3dwelcome
        22
    3dwelcome  
       2021-04-14 11:37:57 +08:00
    @MinQ 我刚试了一下,没发现有什么问题。

    ALTER TABLE table1 ADD INDEX REVERSE(col);
    zhangysh1995
        23
    zhangysh1995  
       2021-04-14 12:41:32 +08:00
    @johnsona 一楼这个虽然有点魔幻,但是挺靠谱的。。。贴主要不考虑下?@chinafengzhao
    zzl22100048
        24
    zzl22100048  
       2021-04-14 12:48:12 +08:00
    上 ES,我们 2 亿企业数据,就靠 ES 撑着
    ilylx2008
        25
    ilylx2008  
       2021-04-14 13:06:42 +08:00
    数据变化不大的话,遍历 2000w 条记录,与公司的对应关系,生成一个关系表。
    新增数据同样处理。
    L1shen
        26
    L1shen  
       2021-04-14 15:43:54 +08:00
    嫌 es 重的话,还可以用 sonic
    https://github.com/valeriansaliou/sonic
    mlcq
        27
    mlcq  
       2021-04-14 16:26:37 +08:00
    2000 万还不分表吗,还有你的索引要建好,尽量不要回表。你那种写法是不走索引的
    zhaokun
        28
    zhaokun  
       2021-04-14 16:31:56 +08:00
    不增加服务比如 ES,可以考虑自己做倒排索引,当然要依赖分词服务
    jenlors
        29
    jenlors  
       2021-04-14 17:49:59 +08:00
    试试 MySQL 全文索引,最简单的方案,增加中间件也会增加系统复杂度,需要仔细评估
    johnsona
        30
    johnsona  
       2021-04-14 23:04:47 +08:00 via iPhone
    @zhangysh1995 主要 es 依赖分词 效果和模糊搜索不一样 分词没分出来 那就搜不到 github 的 issue 模块搜索功能就这样
    vindurriel
        31
    vindurriel  
       2021-04-15 06:13:39 +08:00 via iPhone
    ES Solr Lucene 解决不了 like %foo% 中文默认单字分词的时候可以按字搜出来 但这不是 like %foo% 而是 in (f, o, o)
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   951 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 20:38 · PVG 04:38 · LAX 12:38 · JFK 15:38
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.