亿级订单表 要对物流追踪号支持 LIKE %123% 这样的前后缀都模糊查询，现在的 MySQL 查一次要几分钟，必须上 ES 或者 ClickHouse 吗？另外归档数据也要查，有没有办法压缩存储数据
drymonfidelia · 18 天前 · 8245 次点击
第 1 条附言 · 17 天前
这套系统是给其它网店用的 SaaS ，总订单量有几亿条，需求是各店需要能模糊搜索自己店的物流追踪号。各店的订单量不固定，有的店有几千万条，有的店是来测试系统的只有几条
因为用户经常反馈问题的时候是直接扔张截图甚至模糊不清的照片，OCR 也不太好处理，所以需要模糊搜索追踪号
详细细节补充见 /t/1086471
因为用户经常反馈问题的时候是直接扔张截图甚至模糊不清的照片，OCR 也不太好处理，所以需要模糊搜索追踪号
详细细节补充见 /t/1086471
106 条回复 • 2024-11-13 15:08:47 +08:00
 |
1
Rickkkkkkk 18 天前
你确实需要 es
mysql 不是干这个的 |
 |
2
sagaxu 18 天前 via Android
一亿个字符串 glob ，搞 100 台机器，每台匹配 100 万个就快了
|
 |
3
lmshl 18 天前  1
https://www.postgresql.org/docs/current/pgtrgm.html
你们 mysql 这么拉垮的么 |
 |
4
defaw 18 天前
试试 tidb ，ti kv 就适合干这个，唯一的问题是需要很多机器
|
 |
5
changwei 17 天前 1
MySQL 5.6 之后好像支持创建 FULLTEXT 索引，不知道有人试过这个功能没？顺带问问 MySQL 的 FULLTEXT 索引好不好用？
|
 |
6
akira 17 天前
我们现在在尝试用 doris 替换掉 ES ，全文检索不知道效果如何
|
 |
7
xausky 17 天前
mysql 也有全文索引了，吧 pgsql 的话我们是上 pgtrgm 或者 zhparser 了
|
 |
8
winglight2016 17 天前 1
居然碰到同行了，不过这个需求有点奇怪，物流号都是完整的搜索才有意义，模糊查询有什么用呢？
|
 |
9
drymonfidelia OP 1
@winglight2016 用户懒得输入完整的
|
 |
10
laminux29 17 天前
@lmshl 笑死，你发的东西，你都不看一下嘛？
pg_trgm ignores non-word characters (non-alphanumerics) when extracting trigrams from a string. |
|
11
laminux29 17 天前 3
这种需求，是没办法用索引的，所以无论什么数据库，包括 ES 、Click House 、Redis 、PG 甚至 Oracle ，都不好使，只能用硬件去硬抗。2 楼老哥说对了。
可以使用基于区域或散列，进行分片，用很多物理机去扛，每台物理机扛一个分片。简单一些可以用 MongoDB ，天生分布式 + 分片。需要高性能那就用 Redis 集群，大内存，数据提前加载到内存里。 话说回来，这种需求，虽然提高了用户体验，但真的烧钱。公司没钱的话，不建议这么做。 |
|
12
laminux29 17 天前 1
另外，是否考虑修改需求，比如淘宝业务，用户的查询范围只是自己的订单，菜鸟驿站老板查询的范围只是他的驿站范围，那么可以通过剪枝 + 数据冗余的方法，空间换时间，能控制到一个比较理想的性能范围。
然后查询接口，再按时间进行分区：最近 3 个月内是默认查询，如果要查询所有时间范围，就让他们排队查询，或查询结果异步通知。 |
 |
13
Granado 17 天前 7
@drymonfidelia 按你说的，可能是为了方便用户输入，这种是不是应该先构建一个 userid_运单号的索引表，先按用户筛选，再对筛选出的运单号 like
|
 |
14
photon006 17 天前 1
可以试试 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 左右 |
 |
15
julyclyde 17 天前
你把订单号逆过来存一遍不就得了
|
 |
16
GARLICTRUMP 17 天前
只支持查询尾号就行了
|
 |
17
llsquaer 17 天前
别 like “%132%”。。 要么前五位 ，要么后 5 位，谁神经病从中间开始。当然不排除特殊情况，订单确实看不清前后几位。这种情况单独处理，让他等。。。
|
 |
18
dragonfsky1 17 天前
物流追踪号 这种也可以模糊查询吗？ 这不是就查到比人的了
|
|
19
lmshl 17 天前 8
@laminux29 笑死, 上网不带脑子
一亿行 pgtrgm 不做分区直接搜, 平均时间也不到 200ms, 要什么集群分片硬件硬扛? 单核 pg, 一年百十块搞定的需求, 照你的方案没个几百万硬件成本怎么玩  |
 |
20
AkaGhost 17 天前
如果你换 PostgreSQL 的话，你可以用 TimescaleDB 自动压缩较旧数据
|
 |
21
ferock 17 天前 via iPhone
是客户的需求还是领导的需求？
|
|
22
lmshl 17 天前 2
如果是物流追踪号这种定长且总长度不长的短字符串, 直接把每个截断前缀都做一遍索引还更简单, 也能支持精确匹配.
tracking_number[0:16] tracking_number[1:16] tracking_number[2:16] tracking_number[3:16] ... tracking_number[13:16] 都做成表达式索引, 查询的时候只需要 LIKE ‘123%’, 一样可以做的很快 |
 |
23
rrfeng 17 天前 via Android
es 对这个问题毫无帮助。
|
 |
24
kk2syc 17 天前 6
把数据库挂公网上，过一阵子就有秒查的可以用了 /doge/doge
|
 |
25
levelworm 17 天前 via Android
能不能用时间来切啊？总不至于全表查询？用户最多也就一个礼拜之外吧。。。
|
 |
27
xuanbg 17 天前 19
@drymonfidelia OP 你这个就是伪需求。首先，这个查物流单号是随便查的还是只能查自己的？如果是随便查的，那就必须要精确，不然，你查到别人的物流信息算怎么回事？如果是只能查自己的，那条件就不是只有 like 了，先根据用户 id 查，在几十最多几百个结果中 like ，根本不影响什么。
|
 |
28
mayli 17 天前
@drymonfidelia 前缀后缀 ngram
|
 |
29
spiffing 17 天前
应该想办法优化一下需求。
我们也有订单匹配物流的查询，前提是订单信息查出来才继续给显示物流的部分。 |
 |
32
yuxian 17 天前
用 ES 吧，毕竟是标配。一次部署，后面梭哈。资源不够加机器，加节点。成本？等你的价值有机器贵了再说。可以早点下班的事儿，为何不干呢？我用 es 处理百亿级别的数据，都很轻松。什么垃圾都可以往里面丢。
ES 有优秀的生态，出问题了，都可以找到相应的解决方案。有专业人在维护。你只是用 ES 做索引而已。又不用担心数据丢失问题。 如果再想加速，套个 redis 也不是不行，对于常常用到的缓存一下。基本上 99.99%都是冷数据。 关系型数据库，已经用 mysql 了，没有必要用 pgdb 。本质上没有太大区别。 |
 |
33
isnullstring 17 天前
物流号不应该是%%查询呀
想清楚 这个需求是不是对的，是不是硬性要求再考虑怎么做 |
|
34
sagaxu 17 天前
@lmshl 19#
1. 订单号不是随机数，部分匹配区分度低，比如 2023 匹配出来就是 2023 年全年订单。 2. pg_trgm 是匹配出相似度大于阈值的条目，跟 is_substring 逻辑不同，有假阳性。 3. 切分单号改成 like 'xxx%'匹配，索引规模放大十几倍，且解决不了'%a??b%'这种。 |
 |
37
tairan2006 17 天前
伪需求把，模糊查询岂不是信息泄露了……
|
 |
39
ala2008 17 天前
不应该先查询出 id ，再去匹配吗
|
 |
40
cexll 17 天前 1
其实没有必要争论楼主是不是伪需求，菜鸟不就是这样吗？ 根据运单尾号 之类的 查询在当前站点的快递单号，当然前提肯定是 WHERE + WHERE 你在亿级里面全量查询 必然不合理
|
 |
41
niubee1 17 天前 2
首先，全文检索对你这个问题的效率也很低，因为订单号你怎么分词？全文检索是建立在分词然后倒排索引的基础上的，你这是需要对十几位订单号，就按 18 位好了，你知道 18 位全组合的组合模式有多少种么？无脑说上全文检索的，先搞清楚全文检索原理先啊。
相比起来 22 楼 @lmshl 的方案更具备工程上的可行性。 |
 |
42
windyboy 17 天前
like % 这种类型，数据量大就是要全文索引
|
|
43
winglight2016 17 天前
@drymonfidelia #9 不能既要又要还要啊，哪有懒得输入这种需求的，最多给他提供复制粘贴、图片识别、bar 枪扫码这些工具
另外，ES 并不能解决模糊查询的问题，ES 的反向索引是基于分词去做的，连续的字母数字会被分为一个词，没法提前建立索引。 归根结底，不定长模糊查询查出来的数量无法提前预估，超过 100 条了，必然需要分页，那这个功能对于用户来说毫无意义。 |
 |
44
kaf 17 天前
v2 就是性能焦虑太严重，类似需求 6 亿数据 es 查询轻轻松松
|
 |
45
securityCoding 17 天前
这种需求一般是数仓来做, 业务做的话应该是缩小数据范围,uid->order id list -> like order id
|
 |
47
xmh51 17 天前
不确定的检索，推荐 starrocks, ClickHouse 等列数据库，暴力检索，适当的分片，在没有索引的情况下，列数据库比行数据库好多了，你说归档数据就更加适合了，基本上都是数仓的场景，数仓会稍微慢一点点，这个数据量毫秒级。
假如要非常快的查询，可以说服产品，允许少概率查不中的场景，es 分词一般情况下能够命中，某些场景下，会检索不到，它毕竟不是模糊检索。 事实上全世界都没有解决低延迟的海量数据的模糊匹配，快和模糊查询是不可能并存的。 |
 |
48
ichou 17 天前
ClickHouse 吧， 你这亿级对它来说都不够看，估计单机都能做到秒级查询
数据比你大一个数量级的，多字段模糊查询 + 统计聚合出报表，分片后也能坐到秒级 ES 很多年我也用过，也是亿级数据（甚至也是查单号），很折腾，除非你能保证一辈子不重建索引，重建一次就半条命 但，我不确定你是不是真的需要这些东西，在企业里这些成本都挺高的 如果你只是单字段有模糊查询的需求的话，pg 都足够应付了，楼上也有兄弟提到了 只有查询没有聚合需求的话，你还可以极端一点，搞一张表两字段，专门用来查询 |
 |
49
Morriaty 17 天前
那些说 ES 能搞定的，你们告诉我这种需求，analyzer 要怎么配置？
限制用户查询范围，做前缀、后缀匹配，才是正解 |
 |
50
hejw19970413 17 天前
少量多次查询，可以根据发生的查询时间进行少量多次的查询，第一次查询可能是近一周的，根据模糊查询的词的长短来控制查询时间段，关键词越少可能量就会很多，那么就缩短查询的时间间隔。最不理想的情况是数据的全部查询。核心还是前面所说的数据剪枝，根据当前的场景进行条件的增加。
|
 |
51
chobitssp 17 天前
新添加 2 列 前后三位 专门用于这种场景
|
 |
52
raysonlu 17 天前 1
这种需求，可以先从产品经理开始优化（ doge
|
 |
58
yuji 17 天前
订单都到亿级了，上个 ES 不过分的
|
 |
59
lefer 17 天前
@drymonfidelia #9 好神奇，主要是正常软件 APP 里面都是提供一键复制运单号，我以为输入完整运单号才是简单的。
|
 |
60
sockpuppet9527 17 天前
@laminux29 #11 gin index 了解一下 https://www.postgresql.org/docs/current/gin.html
|
 |
61
encro 17 天前
哥直接给你出个主意。
对于模糊查询的，只支持最近 1 个月，这样你 1 亿可能就变成了 1 千万，好了，剩下的可以时间可以接受了。 |
|
62
encro 17 天前
|
 |
63
dabingbing 17 天前
提示：请复制或输入完整订单号
|
 |
64
wangyzj 17 天前
这个应该是寻找产品需求和技术的平衡点
这种需求双边模糊匹配，没有索引可以用，es ，ch 和 doris 这种应该都不会快 只能说分布能解决部分性能问题，但不是解决问题的根本办法 除非你在订单格式里面做文章 而且模糊匹配出的结果也太不注重隐私了 |
|
65
laminux29 17 天前
@skinny
OP 是希望用纯数字字符串当成子串来进行搜索，或者更严谨来说，是匹配，而且还不限定位置。这种匹配，在算法与数据结构的层面，是没办法加索引的，只能蛮干，这也是为什么这类业务很少见，而且搜索引擎对这种搜索也没办法做到高精准度。 |
 |
67
alansfinal 17 天前
@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 能识别订单号吗？ |
 |
70
mamumu 17 天前
存个逆序的订单号，然后把需求定为后 n 位，右模糊就可以了，还可以走索引
|
 |
71
maomaosang 17 天前
只用 MySQL 确实没想到什么优雅的好办法，楼上有人说 ES 不行这我要反驳了，ES 索引的时候做 ngram 切分可以解决这个问题，我们有个几亿的库，要对昵称和一段数字做类似楼主业务的模糊检索，4 核 16G 配置，检索都是秒出，应该在 50ms 以内，这个 ES 同时还要负担很多其他工作。
可以根据业务实际情况设置一个最小 n ，比如 5 ，也就是要求至少输入 5 位才能检索，这样存储的消耗也不会太大。如果非要用 MySQL ，那就自己手动做个 ngram 吧，不优雅但是肯定能用。 |
|
73
laminux29 16 天前
@alansfinal
那篇文档的意思是，pg_trgm 这玩意本质上是类似于全文搜索的，需要预先对自然语言的词汇进行处理，所以无法处理非词汇的结果，也就是纯数字是处理不了的。 而且这还是一种近似搜索，会有错误的结果，并不是 LIKE %keyString% 这种精确搜索。 |
 |
74
pvnk1u 16 天前
我试了一下，单机在内存里存一千万条 11 位的订单号列表，模糊查询只需要不到 0.1s 就能查找出符合条件的数据，可以加机器的话，大可以取模分散到不同的机器上，查询的时候去指定的机器上模糊查询就行
|
 |
76
Jinnrry 16 天前
@changwei 我记得 mysql 默认只支持 n-gram 分词。虽然在绝大部分场景中，都是废物，但是恰恰楼主这个场景，还挺适合的。楼主可以试试 FULLTEXT 索引的效果，肯定比你现在不加索引性能好
|
|
77
Jinnrry 16 天前
@Morriaty ES 装个 n-gram 就能解决，或者自定义分词器
"tokenizer": { "char_tokenizer": { "type": "pattern", "pattern": "" } } 这样直接单字分词，也能解决楼主的问题 |
 |
78
iseki 16 天前 via Android
@laminux29 不会有错误结果，现实中都是使用 like %keyword% 搜索，数据库会在使用基于 k-gram 分词的 GIN 索引基础上再做一次 recheck 。
trigram 就是分词，索引是 GIN 或者 GiST 。我们这边线上在用，没什么大问题。 |
|
80
iseki 16 天前 via Android
这个需求最大的问题是，如果不加其他限定条件，那就必须考虑当输入的 keyword 很短时，召回特别大量数据以至于几乎无法使用的现实问题。
|
 |
81
hedwi 16 天前
每家店应该只搜自己店的 这样加一个条件就从亿级别问题变成千万级别的问题了
|
|
82
drymonfidelia OP @hedwi 千万级没索引还是慢
|
|
83
Morriaty 16 天前
@Jinnrry 楼主提的需求应该是任意长度的匹配，如果用 n-gram 来配置，就要配置 min_gram:1, min_gram: N ，产生的索引量级就是 O(N**2 )。至于 pattern analyzer ，我没有看明白是怎么满足需求的
|
|
84
Morriaty 16 天前
打错：min_gram:1, max_gram: N
|
|
85
laminux29 16 天前
@sockpuppet9527
这玩意也是分词机制，你仔细看看你发的链接。分词与近似策略是无法精确处理这类需求的。所以我在开始就说了，这类需求，就没办法用索引。 业内目前应对这部分问题，第一是不处理。很多电商平台通过关键字搜索历史订单时，会发生这种情况，搜不出来；然后是通过剪枝需求去做的，比如银行只允许搜最近 3 个月的交易记录。 |
|
86
laminux29 16 天前
@iseki
分词与近似策略是无法精确处理这类需求的。所以我在开始就说了，这类需求，就没办法用索引。 recheck 只能解决假阳性，解决不了假阴性。 你说你们这边线上在用，没什么大问题，那是因为你们没发现问题，而且用户也没汇报。不仅如此，就连大电商平台，比如淘宝京东的历史订单号的搜索，因为他们采用分词搜索，所以他们也没办法实现这个需求。 |
|
87
Jinnrry 16 天前
@Morriaty #84 min_gram 设置成 1 ，max_gram 也设置成 1 就行了，他这个场景 max_gram 根本没意义。这样设置以后和我写的 pattern analyzer 分词结果是一样的。
比如“12345”这么一个订单号，分词结果为"1" "2" "3" "4" "5" 然后使用 match_phrase 查询 “123”或者“345”就都能查出来，match_phrase 是要求顺序和连续性的 |
|
89
iseki 16 天前
这个东西的分词是 "12345" -> "123", "234", "345" 这种策略的，不会有你说的那种问题
@laminux29 存在类似风险时（比如输入长度小于 3 或者因为字符编码等问题没法搞），PG 会直接回退，放弃这个索引 |
|
90
iseki 16 天前
@laminux29
``` select show_trgm('12345'); show_trgm --------------------------------- {" 1"," 12",123,234,345,"45 "} (1 row) ``` |
|
91
Morriaty 16 天前
@Jinnrry 我还真没试过 NGram 和 match_phrase 的组合，直觉上会有很多误召回 badcase ，需要疯狂的调 `slop` 和 `minimum_should_match`🤣，把问题复杂化了，我还是倾向于，直接在需求设计上拒绝这种查询，改为全匹配或者前缀匹配
|
|
92
sockpuppet9527 16 天前
@laminux29 gin 是倒排索引，分词是另外的东西
|
 |
93
lyxxxh2 16 天前
1. 匹配几亿太过了吧!
先固定在某个范围,不然用户 id 或者时间范围,降低模糊匹配的数量。 2. 如果真要匹配几亿: 建立一个 10 亿订单号的数组,作于自定义搜索索引,用程序语言来匹配 id,最后 whereIn 。 这应该是最快的方式。 |
 |
94
dejavuwind 16 天前
|
|
95
dejavuwind 16 天前
|
|
96
iseki 16 天前
@dejavuwind 你用的是相似度，我说的是 LIKE '%keyword%'，咱俩说的不是一个东西啊
|
 |
97
sampeng 15 天前
改 pg 是不可能的。业务在线上跑着呢。疯了，这个时候改存储方案。
才亿级数据而已。。es 有 es 的问题。。你还要考虑很多其他的事。 这个事就不是分词的事啊。。。我觉得是业务实现逻辑问题。如果只是物流号。自己给物流号自己建个 fst 结构索引就是了 我有几个方案： 1.sonic 。全 rust 开发，比 es 部署和维护都简单，也非常轻量，可以做一层全文搜索，但貌似无法做高可用？ sonic 是有 fst 存储的。 2.既然是 mysql 。怎么没人提当年大名鼎鼎的 sphinx 呢？ mysql 插件，代码是无缝集成。该写 sql 写 sql 。亿级，完全够用。 3.自己撸 fst 或者 lucene 。一起就 12 位的 fst 能有多大。每次新增的物流号时都刷新这个 fst 。从 fst 里面查然后提示给用户，用户选择要的那个。再去数据库拿原始数据。 |
|
98
sampeng 15 天前
我推测你们的功能其实是想输入一部分，然后快速提示这一部分所属的。
现成产品就是 sonic/es ，这两者都有提示词能力。都是基于 FST 等变种数据结构来做的。 自己撸一个也不是不可以，就是要考虑每一家的 fst 怎么存怎么取的问题。大致围绕这个逻辑来做。。。 |
|
99
dejavuwind 15 天前
@iseki #96 感谢回复。没用过这个 PG 的 pg_trgm ，被 #19 这个截图的语句给误导了，实际上使用 LIKE + pg_trgm 进行%xxx%的模糊查询才是正确的打开方式，而不是使用 similarity 进行查询
|
|
100
laminux29 15 天前
|
Select Language