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

[drymonfidelia]

这套系统是给其它网店用的 SaaS ，总订单量有几亿条，需求是各店需要能模糊搜索自己店的物流追踪号。各店的订单量不固定，有的店有几千万条，有的店是来测试系统的只有几条
因为用户经常反馈问题的时候是直接扔张截图甚至模糊不清的照片，OCR 也不太好处理，所以需要模糊搜索追踪号

详细细节补充见 /t/1086471

[Rickkkkkkk]

你确实需要 es

mysql 不是干这个的

[sagaxu]

一亿个字符串 glob ，搞 100 台机器，每台匹配 100 万个就快了

[lmshl]

https://www.postgresql.org/docs/current/pgtrgm.html
你们 mysql 这么拉垮的么

[defaw]

试试 tidb ，ti kv 就适合干这个，唯一的问题是需要很多机器

[changwei]

MySQL 5.6 之后好像支持创建 FULLTEXT 索引，不知道有人试过这个功能没？顺带问问 MySQL 的 FULLTEXT 索引好不好用？

[akira]

我们现在在尝试用 doris 替换掉 ES ，全文检索不知道效果如何

[xausky]

mysql 也有全文索引了，吧 pgsql 的话我们是上 pgtrgm 或者 zhparser 了

[winglight2016]

居然碰到同行了，不过这个需求有点奇怪，物流号都是完整的搜索才有意义，模糊查询有什么用呢？

[drymonfidelia]

@winglight2016 用户懒得输入完整的

[laminux29]

@lmshl 笑死，你发的东西，你都不看一下嘛？

pg_trgm ignores non-word characters (non-alphanumerics) when extracting trigrams from a string.

[laminux29]

这种需求，是没办法用索引的，所以无论什么数据库，包括 ES 、Click House 、Redis 、PG 甚至 Oracle ，都不好使，只能用硬件去硬抗。2 楼老哥说对了。

可以使用基于区域或散列，进行分片，用很多物理机去扛，每台物理机扛一个分片。简单一些可以用 MongoDB ，天生分布式 + 分片。需要高性能那就用 Redis 集群，大内存，数据提前加载到内存里。

话说回来，这种需求，虽然提高了用户体验，但真的烧钱。公司没钱的话，不建议这么做。

[laminux29]

另外，是否考虑修改需求，比如淘宝业务，用户的查询范围只是自己的订单，菜鸟驿站老板查询的范围只是他的驿站范围，那么可以通过剪枝 + 数据冗余的方法，空间换时间，能控制到一个比较理想的性能范围。

然后查询接口，再按时间进行分区：最近 3 个月内是默认查询，如果要查询所有时间范围，就让他们排队查询，或查询结果异步通知。

[Granado]

@drymonfidelia 按你说的，可能是为了方便用户输入，这种是不是应该先构建一个 userid_运单号的索引表，先按用户筛选，再对筛选出的运单号 like

[photon006]

可以试试 starrocks ，语法跟 mysql 兼容，业务代码可能改动很小甚至不需改动

数据分区存，创建表让数据按年份存到不同桶

测试了一张表，dbeaver 显示它有 64G

select count(*) from table
耗时: 1m7s
结果: 16007223598

整张表有 160 亿数据

like 查询过去半年数据，time >= '2024-05-01 00:00:00' and a like '%keyword%' limit 2000 ，耗时 3s 左右

[julyclyde]

你把订单号逆过来存一遍不就得了

[GARLICTRUMP]

只支持查询尾号就行了

[llsquaer]

别 like “%132%”。。 要么前五位 ，要么后 5 位，谁神经病从中间开始。当然不排除特殊情况，订单确实看不清前后几位。这种情况单独处理，让他等。。。

[dragonfsky1]

物流追踪号 这种也可以模糊查询吗？ 这不是就查到比人的了

[lmshl]

@laminux29 笑死, 上网不带脑子

一亿行 pgtrgm 不做分区直接搜, 平均时间也不到 200ms, 要什么集群分片硬件硬扛?
单核 pg, 一年百十块搞定的需求, 照你的方案没个几百万硬件成本怎么玩

[AkaGhost]

如果你换 PostgreSQL 的话，你可以用 TimescaleDB 自动压缩较旧数据

[ferock]

是客户的需求还是领导的需求？

[lmshl]

如果是物流追踪号这种定长且总长度不长的短字符串, 直接把每个截断前缀都做一遍索引还更简单, 也能支持精确匹配.
tracking_number[0:16]
tracking_number[1:16]
tracking_number[2:16]
tracking_number[3:16]
...
tracking_number[13:16]
都做成表达式索引, 查询的时候只需要 LIKE ‘123%’, 一样可以做的很快

[rrfeng]

es 对这个问题毫无帮助。

[kk2syc]

把数据库挂公网上，过一阵子就有秒查的可以用了 /doge/doge

[levelworm]

能不能用时间来切啊？总不至于全表查询？用户最多也就一个礼拜之外吧。。。

[laminux29]

@lmshl

如果你看不懂 10 楼发的英文，可以试试翻译软件。

[xuanbg]

@drymonfidelia OP 你这个就是伪需求。首先，这个查物流单号是随便查的还是只能查自己的？如果是随便查的，那就必须要精确，不然，你查到别人的物流信息算怎么回事？如果是只能查自己的，那条件就不是只有 like 了，先根据用户 id 查，在几十最多几百个结果中 like ，根本不影响什么。

[mayli]

@drymonfidelia 前缀后缀 ngram

[spiffing]

应该想办法优化一下需求。
我们也有订单匹配物流的查询，前提是订单信息查出来才继续给显示物流的部分。

[wssy001]

@sagaxu 只顾着优化单条数据查询却不考虑报表聚合查询是吧？

[skinny]

@laminux29 虽然但是，难道物流追踪号不是只包含字母数字吗？！

[yuxian]

用 ES 吧，毕竟是标配。一次部署，后面梭哈。资源不够加机器，加节点。成本？等你的价值有机器贵了再说。可以早点下班的事儿，为何不干呢？我用 es 处理百亿级别的数据，都很轻松。什么垃圾都可以往里面丢。
ES 有优秀的生态，出问题了，都可以找到相应的解决方案。有专业人在维护。你只是用 ES 做索引而已。又不用担心数据丢失问题。
如果再想加速，套个 redis 也不是不行，对于常常用到的缓存一下。基本上 99.99%都是冷数据。
关系型数据库，已经用 mysql 了，没有必要用 pgdb 。本质上没有太大区别。

[isnullstring]

物流号不应该是%%查询呀

想清楚 这个需求是不是对的，是不是硬性要求再考虑怎么做

[sagaxu]

@lmshl 19#

1. 订单号不是随机数，部分匹配区分度低，比如 2023 匹配出来就是 2023 年全年订单。
2. pg_trgm 是匹配出相似度大于阈值的条目，跟 is_substring 逻辑不同，有假阳性。
3. 切分单号改成 like 'xxx%'匹配，索引规模放大十几倍，且解决不了'%a??b%'这种。

[iseki]

@laminux29 non-word 是指空白符什么的，数字属于 word ，虽然我也不知道这是哪里的规定

[iseki]

@sagaxu pg 一般这种场景直接用 like ，系统会自动加一个 recheck 解决假阳性

[tairan2006]

伪需求把，模糊查询岂不是信息泄露了……

[godwinma]

@Granado 这个思路很合理

[ala2008]

不应该先查询出 id ，再去匹配吗

[cexll]

其实没有必要争论楼主是不是伪需求，菜鸟不就是这样吗？ 根据运单尾号 之类的 查询在当前站点的快递单号，当然前提肯定是 WHERE + WHERE 你在亿级里面全量查询 必然不合理

[niubee1]

首先，全文检索对你这个问题的效率也很低，因为订单号你怎么分词？全文检索是建立在分词然后倒排索引的基础上的，你这是需要对十几位订单号，就按 18 位好了，你知道 18 位全组合的组合模式有多少种么？无脑说上全文检索的，先搞清楚全文检索原理先啊。
相比起来
22 楼 @lmshl 的方案更具备工程上的可行性。

[windyboy]

like % 这种类型，数据量大就是要全文索引

[winglight2016]

@drymonfidelia #9 不能既要又要还要啊，哪有懒得输入这种需求的，最多给他提供复制粘贴、图片识别、bar 枪扫码这些工具

另外，ES 并不能解决模糊查询的问题，ES 的反向索引是基于分词去做的，连续的字母数字会被分为一个词，没法提前建立索引。

归根结底，不定长模糊查询查出来的数量无法提前预估，超过 100 条了，必然需要分页，那这个功能对于用户来说毫无意义。

[kaf]

v2 就是性能焦虑太严重，类似需求 6 亿数据 es 查询轻轻松松

[securityCoding]

这种需求一般是数仓来做, 业务做的话应该是缩小数据范围,uid->order id list -> like order id

[dylanqqt]

@lmshl 开战！！

[xmh51]

不确定的检索，推荐 starrocks, ClickHouse 等列数据库，暴力检索，适当的分片，在没有索引的情况下，列数据库比行数据库好多了，你说归档数据就更加适合了，基本上都是数仓的场景，数仓会稍微慢一点点，这个数据量毫秒级。

假如要非常快的查询，可以说服产品，允许少概率查不中的场景，es 分词一般情况下能够命中，某些场景下，会检索不到，它毕竟不是模糊检索。

事实上全世界都没有解决低延迟的海量数据的模糊匹配，快和模糊查询是不可能并存的。

[ichou]

ClickHouse 吧， 你这亿级对它来说都不够看，估计单机都能做到秒级查询
数据比你大一个数量级的，多字段模糊查询 + 统计聚合出报表，分片后也能坐到秒级

ES 很多年我也用过，也是亿级数据（甚至也是查单号），很折腾，除非你能保证一辈子不重建索引，重建一次就半条命

但，我不确定你是不是真的需要这些东西，在企业里这些成本都挺高的
如果你只是单字段有模糊查询的需求的话，pg 都足够应付了，楼上也有兄弟提到了
只有查询没有聚合需求的话，你还可以极端一点，搞一张表两字段，专门用来查询

[Morriaty]

那些说 ES 能搞定的，你们告诉我这种需求，analyzer 要怎么配置？

限制用户查询范围，做前缀、后缀匹配，才是正解

[hejw19970413]

少量多次查询，可以根据发生的查询时间进行少量多次的查询，第一次查询可能是近一周的，根据模糊查询的词的长短来控制查询时间段，关键词越少可能量就会很多，那么就缩短查询的时间间隔。最不理想的情况是数据的全部查询。核心还是前面所说的数据剪枝，根据当前的场景进行条件的增加。

[chobitssp]

新添加 2 列 前后三位 专门用于这种场景

[raysonlu]

这种需求，可以先从产品经理开始优化（ doge

[jhdxr]

@lmshl 嘲讽了半天但你图片里的例子也不是%132%这一类的啊

[liprais]

@jhdxr 建议你再看看

[yudoo]

@yuxian 百亿级别机器啥配置,我们单节点 64G 内存只能存储几千万条数据了

[a67793581]

@photon006 感谢分享

[a67793581]

@lmshl 感谢分享

[yuji]

订单都到亿级了，上个 ES 不过分的

[lefer]

@drymonfidelia #9 好神奇，主要是正常软件 APP 里面都是提供一键复制运单号，我以为输入完整运单号才是简单的。

[sockpuppet9527]

@laminux29 #11 gin index 了解一下 https://www.postgresql.org/docs/current/gin.html

[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 很短时，召回特别大量数据以至于几乎无法使用的现实问题。

[hedwi]

每家店应该只搜自己店的 这样加一个条件就从亿级别问题变成千万级别的问题了

[drymonfidelia]

@hedwi 千万级没索引还是慢

[Morriaty]

@Jinnrry 楼主提的需求应该是任意长度的匹配，如果用 n-gram 来配置，就要配置 min_gram:1, min_gram: N ，产生的索引量级就是 O(N**2 )。至于 pattern analyzer ，我没有看明白是怎么满足需求的

[Morriaty]

打错：min_gram:1, max_gram: N

[laminux29]

@sockpuppet9527

这玩意也是分词机制，你仔细看看你发的链接。分词与近似策略是无法精确处理这类需求的。所以我在开始就说了，这类需求，就没办法用索引。

业内目前应对这部分问题，第一是不处理。很多电商平台通过关键字搜索历史订单时，会发生这种情况，搜不出来；然后是通过剪枝需求去做的，比如银行只允许搜最近 3 个月的交易记录。

[laminux29]

@iseki

分词与近似策略是无法精确处理这类需求的。所以我在开始就说了，这类需求，就没办法用索引。

recheck 只能解决假阳性，解决不了假阴性。

你说你们这边线上在用，没什么大问题，那是因为你们没发现问题，而且用户也没汇报。不仅如此，就连大电商平台，比如淘宝京东的历史订单号的搜索，因为他们采用分词搜索，所以他们也没办法实现这个需求。

[Jinnrry]

@Morriaty #84 min_gram 设置成 1 ，max_gram 也设置成 1 就行了，他这个场景 max_gram 根本没意义。这样设置以后和我写的 pattern analyzer 分词结果是一样的。

比如“12345”这么一个订单号，分词结果为"1" "2" "3" "4" "5"

然后使用 match_phrase 查询 “123”或者“345”就都能查出来，match_phrase 是要求顺序和连续性的

[iseki]

@laminux29 PG 的这个 trigram + GIN 很粗暴，不会有假阴性问题，缺点就是读放大有点大。

[iseki]

这个东西的分词是 "12345" -> "123", "234", "345" 这种策略的，不会有你说的那种问题

@laminux29 存在类似风险时（比如输入长度小于 3 或者因为字符编码等问题没法搞），PG 会直接回退，放弃这个索引

[iseki]

@laminux29
```
select show_trgm('12345');
show_trgm
---------------------------------
{" 1"," 12",123,234,345,"45 "}
(1 row)
```

[Morriaty]

@Jinnrry 我还真没试过 NGram 和 match_phrase 的组合，直觉上会有很多误召回 badcase ，需要疯狂的调 `slop` 和 `minimum_should_match`🤣，把问题复杂化了，我还是倾向于，直接在需求设计上拒绝这种查询，改为全匹配或者前缀匹配

[sockpuppet9527]

@laminux29 gin 是倒排索引，分词是另外的东西

[lyxxxh2]

1. 匹配几亿太过了吧!
先固定在某个范围,不然用户 id 或者时间范围,降低模糊匹配的数量。

2. 如果真要匹配几亿:
建立一个 10 亿订单号的数组,作于自定义搜索索引,用程序语言来匹配 id,最后 whereIn 。
这应该是最快的方式。

[dejavuwind]

@iseki #78 是我使用有什么问题么 结果验证就是会搜出来一堆错误结果的 recheck 需要主动配置？

难道这就是为什么一堆网站的所谓模糊搜索结果出来的内容根本匹配不上的原因？

[dejavuwind]

@dejavuwind 不过效率倒还可以 但是这么用应该不算是合适方案
获取 5 条记录平均每条 250ms
获取 50 条平均每条大概 4-500ms

[iseki]

@dejavuwind 你用的是相似度，我说的是 LIKE '%keyword%'，咱俩说的不是一个东西啊

[sampeng]

改 pg 是不可能的。业务在线上跑着呢。疯了，这个时候改存储方案。
才亿级数据而已。。es 有 es 的问题。。你还要考虑很多其他的事。

这个事就不是分词的事啊。。。我觉得是业务实现逻辑问题。如果只是物流号。自己给物流号自己建个 fst 结构索引就是了

我有几个方案：
1.sonic 。全 rust 开发，比 es 部署和维护都简单，也非常轻量，可以做一层全文搜索，但貌似无法做高可用？ sonic 是有 fst 存储的。
2.既然是 mysql 。怎么没人提当年大名鼎鼎的 sphinx 呢？ mysql 插件，代码是无缝集成。该写 sql 写 sql 。亿级，完全够用。
3.自己撸 fst 或者 lucene 。一起就 12 位的 fst 能有多大。每次新增的物流号时都刷新这个 fst 。从 fst 里面查然后提示给用户，用户选择要的那个。再去数据库拿原始数据。

[sampeng]

我推测你们的功能其实是想输入一部分，然后快速提示这一部分所属的。
现成产品就是 sonic/es ，这两者都有提示词能力。都是基于 FST 等变种数据结构来做的。
自己撸一个也不是不可以，就是要考虑每一家的 fst 怎么存怎么取的问题。大致围绕这个逻辑来做。。。

[dejavuwind]

@iseki #96 感谢回复。没用过这个 PG 的 pg_trgm ，被 #19 这个截图的语句给误导了，实际上使用 LIKE + pg_trgm 进行%xxx%的模糊查询才是正确的打开方式，而不是使用 similarity 进行查询

[laminux29]

@sockpuppet9527

倒排索引不是用来解决楼主这个需求的。建议仔细看一下倒排索引的说明，然后找几个物流单号，用这种方法试试，你就明白了。