亿级订单表 要对物流追踪号支持 LIKE %123% 这样的前后缀都模糊查询，现在的 MySQL 查一次要几分钟，必须上 ES 或者 ClickHouse 吗？另外归档数据也要查，有没有办法压缩存储数据

[drymonfidelia]

[encro]

哥直接给你出个主意。
对于模糊查询的，只支持最近 1 个月，这样你 1 亿可能就变成了 1 千万，好了，剩下的可以时间可以接受了。

[encro]

这个问题，也是我换 pg 的一个原因。


@sockpuppet9527

[dabingbing]

提示：请复制或输入完整订单号

[wangyzj]

这个应该是寻找产品需求和技术的平衡点
这种需求双边模糊匹配，没有索引可以用，es ，ch 和 doris 这种应该都不会快
只能说分布能解决部分性能问题，但不是解决问题的根本办法
除非你在订单格式里面做文章
而且模糊匹配出的结果也太不注重隐私了

[laminux29]

@skinny

OP 是希望用纯数字字符串当成子串来进行搜索，或者更严谨来说，是匹配，而且还不限定位置。这种匹配，在算法与数据结构的层面，是没办法加索引的，只能蛮干，这也是为什么这类业务很少见，而且搜索引擎对这种搜索也没办法做到高精准度。

[laminux29]

@iseki

它后面写了 non-alphanumerics

[alansfinal]

@laminux29 索引这些我也不懂啊，就是单纯看你发的这个链接，alpha 在英语指字母比如 abc ，numerics 指数字比如 123 ，alphanumerics 指字母+数字比如 a1b23c4 ，non-alphanumerics 就是非字母数字比如，。！@#¥%｜
那么“pg_trgm ignores non-word characters (non-alphanumerics) when extracting trigrams from a string.“ 这句话不就代表 pg_trgm 能识别订单号吗？

[yh7gdiaYW]

@changwei 全文索引纯废物，需要能分词才行，不适用自定义输的搜索条件

[yh7gdiaYW]

@Morriaty 这种需求 starrocks 、doris 、clickhouse 直接无脑暴力搜就很快，es 这个场景不怎么样

[mamumu]

存个逆序的订单号，然后把需求定为后 n 位，右模糊就可以了，还可以走索引

[maomaosang]

只用 MySQL 确实没想到什么优雅的好办法，楼上有人说 ES 不行这我要反驳了，ES 索引的时候做 ngram 切分可以解决这个问题，我们有个几亿的库，要对昵称和一段数字做类似楼主业务的模糊检索，4 核 16G 配置，检索都是秒出，应该在 50ms 以内，这个 ES 同时还要负担很多其他工作。
可以根据业务实际情况设置一个最小 n ，比如 5 ，也就是要求至少输入 5 位才能检索，这样存储的消耗也不会太大。如果非要用 MySQL ，那就自己手动做个 ngram 吧，不优雅但是肯定能用。

[lambdaq]

@changwei 用过。mysql 应该是 2gram 。上面 pg 老哥发的 3gram

[laminux29]

@alansfinal

那篇文档的意思是，pg_trgm 这玩意本质上是类似于全文搜索的，需要预先对自然语言的词汇进行处理，所以无法处理非词汇的结果，也就是纯数字是处理不了的。

而且这还是一种近似搜索，会有错误的结果，并不是 LIKE %keyString% 这种精确搜索。

[pvnk1u]

我试了一下，单机在内存里存一千万条 11 位的订单号列表，模糊查询只需要不到 0.1s 就能查找出符合条件的数据，可以加机器的话，大可以取模分散到不同的机器上，查询的时候去指定的机器上模糊查询就行

[pvnk1u]

@pvnk1u 我的这个回答有问题，只考虑了单机查询，但是查询的时候还是得所有服务器都模糊查一遍

[Jinnrry]

@changwei 我记得 mysql 默认只支持 n-gram 分词。虽然在绝大部分场景中，都是废物，但是恰恰楼主这个场景，还挺适合的。楼主可以试试 FULLTEXT 索引的效果，肯定比你现在不加索引性能好

[Jinnrry]

@Morriaty ES 装个 n-gram 就能解决，或者自定义分词器

"tokenizer": {
"char_tokenizer": {
"type": "pattern",
"pattern": ""
}
}

这样直接单字分词，也能解决楼主的问题

[iseki]

@laminux29 不会有错误结果，现实中都是使用 like %keyword% 搜索，数据库会在使用基于 k-gram 分词的 GIN 索引基础上再做一次 recheck 。
trigram 就是分词，索引是 GIN 或者 GiST 。我们这边线上在用，没什么大问题。

[iseki]

@lambdaq PostgreSQL 有个日本人写的扩展，bigm ，2gram

[iseki]

这个需求最大的问题是，如果不加其他限定条件，那就必须考虑当输入的 keyword 很短时，召回特别大量数据以至于几乎无法使用的现实问题。