亿级订单表 要对物流追踪号支持 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 。
这应该是最快的方式。