这是一个创建于 368 天前的主题，其中的信息可能已经有所发展或是发生改变。
公司现在的业务, 总会涉及到连表操作, 百万级的数据, 一联表, 查询会变得的缓慢. 例如订单, 收货单, 入库单, 退货单, 验收单 数据一直在写入 如果涉及到这种情况, 除了冗余存储数据外, 还有什么其他的解决方案吗? 诚恳的咨询下各位
第 1 条附言 · 2024-07-30 17:00:31 +08:00
现在主要的解决方案是使用 PG 的物化视图的功能, 每隔几分钟生成一次物化视图, 但是数据实时性会有问题
第 2 条附言 · 2024-07-31 11:32:38 +08:00
- 感谢解答
- 补充下业务场景: -- 1,有好几张百万级别的表在关联查询 -- 2,PG使用的是阿里云的服务器,预算有限,现在后端在想解决方案 -- 3,每个表都有主表和明细表, 现在都是明细表相关联,查询计算的东西较复杂
 |
1
opengps 2024-07-30 14:17:22 +08:00
硬限制没有太好的办法，就是靠闲时提前生成缓存之类的
|
 |
2
sagaxu 2024-07-30 14:19:39 +08:00
难道不先分析一下查询计划？
|
 |
3
coderxy 2024-07-30 14:24:51 +08:00
分开查询，在程序里关联
|
 |
4
xiaohang427 2024-07-30 14:25:48 +08:00
看查询内容
查询明细，建议不要连表，先查主表，在关联其它表补充数据 汇总查询，定期生成汇总表，避免在大表里做汇总操作。很耗时 |
 |
5
lingalonely 2024-07-30 15:07:47 +08:00
为什么需要连表，你又不是做分析，单纯查明细就不要连表
|
 |
6
endershadow 2024-07-30 15:11:40 +08:00
大数据领域的技术都卷得这么厉害,感觉好多人都不知道似的
|
 |
7
dddd1919 2024-07-30 15:15:02 +08:00  6
1. 正常业务功能，用 CDC 工具做宽表，在宽表处理
2. 数据分析，那就把业务数据同步到分析库比如 es |
 |
8
wxyrrcj 2024-07-30 15:19:13 +08:00
分开查询，在程序里关联
|
 |
9
gerefoxing 2024-07-30 16:40:09 +08:00
减少大表关联，程序里查询赋值，使用本地缓存+redis 缓存
|
 |
10
lolizeppelin 2024-07-30 16:43:14 +08:00
首先...数据库是不是 mysql?
如果是...抬走！ |
 |
11
ModiKa2022 OP @coderxy 列表展示的, 所有的字段都允许搜索, 不能分开写 SQL
|
|
12
ModiKa2022 OP @lingalonely 列表展示的, 所有的字段都允许搜索, 不能分开写 SQL
|
|
13
ModiKa2022 OP @endershadow 给个具体的可以实现的关键词或者思路,感谢 !
|
|
14
ModiKa2022 OP @lolizeppelin PG
|
 |
15
wysnxzm 2024-07-30 16:50:08 +08:00
数据库 join 改为代码 join
优点:笛卡尔积优化为 hash(你写个双重循环当我没说);硬盘查找变为内存查找;可跨库关联且不限制数据来源;可使用缓存优化性能 缺点:需要更多内存;数据库事务失效需要自己实现分布式事务或者不使用事务 |
|
16
ModiKa2022 OP @wysnxzm 感觉处理的代价比较大
|
|
17
ModiKa2022 OP @dddd1919 感谢
|
 |
18
746970179 2024-07-30 17:12:47 +08:00
个人经验, 外键设置正常, 不会很慢
1 走索引: 业务上绝大部分查询, 都是使用唯一单号(运单号, 订单号, 产品 sku 等)或者时间, 这些字段设置好索引, 就挺快 2 只取要用的字段: 分两步, 第一步是获取当前页的 id, 第二步是针对这一页的 id, 再查询数据. 3 减少查询次数: 一页 100 条数据, 不要每个字段都是 1 个 sql, 同一字段的可以一个 sql 生成字典 |
|
19
ModiKa2022 OP @746970179 这种是常用的方式, 但是只能处理搜索主表的情况, 现在是每个字段都可以搜索, 所以要 join
|
 |
20
thinkwei2012 2024-07-30 17:51:00 +08:00
技术上这种详情表再关联订单表进行某字段搜索查询确实很慢，最简单的方法就是做冗余，大胆一点就单独拉一张宽表专门搜索数据双写。
要么改下页面设计，比如让产品重新设计页面拆分一些到不同的查询表单中，尽量避免少的联表 |
 |
21
simen513 2024-07-30 18:18:54 +08:00
最常用的做法是：
特大数据量的表，按时间做 partition 分表。 查询时分时段查询，再汇总到周、月、年，一级一级汇总。 |
|
22
ModiKa2022 OP @thinkwei2012 冗余数据,涉及到很多逻辑上的处理, 对每个列表都要单独冗余数据, 系统比较大, 涉及的查看页面有些多
|
|
23
ModiKa2022 OP @simen513 挺好的思路, 现在的问题是, 每个独立的表都是单独的单子,时间是独立的, 如果按照时间分表, 逻辑上很多 join 不好处理
|
 |
24
kestrelBright 2024-07-30 20:20:30 +08:00 via iPhone
这种上 es 吧
|
 |
25
jjx 2024-07-30 20:27:47 +08:00 2
erp 很难
表现在客户查询条件巴不得每个字段都可以用来查询 报表页面必须有小计总计, 使得优化困难 数据一定要实时性, 高度要求数据正确 数据查看需要各种制约,比方说按地区/员工/合作伙伴/部门等等, 不得不联表 当然,这些还仅仅是 erp 变态需求中的一部分 |
 |
26
volvo007 2024-07-30 22:29:51 +08:00
我一直以为是我使用数据库的方法有问题，原来大家也都会遇到类似的问题。不过这里想问问，所谓的查询速度快是多快呢？
对于内部用户，我认为百万级的多个表关联（例如 10 张表），返回 100 条以内的数据的响应时间在 3 秒内算是可以接受。如果面向 toB 用户，那么秒级差不多；而面向 toC 用户，大概要在 100 ms 这个水平？ |
 |
27
flxaq 2024-07-31 00:57:24 +08:00 via iPhone
先看你 pg 硬件配置，我们公司一个表几亿的，pg 连几十个表没什么问题，ssd 的还要做好数据库参数优化，要改成适合 ssd 的参数，让 sql 能走合理的索引，连表关键要看索引是否合理，对应表要看具体业务场景，有时间纬度的可以按时间分区。数据库内存 等参数要合理分配
|
 |
28
feifan19 2024-07-31 08:51:16 +08:00
分库分表，数据缓存，读写分离之类的没什么好说的，
提供一种冷热分离的思路，我们项目中每天凌晨会有一个定时任务，把 7 天前的交易数据压缩为 zip 上传到 Blob 上 7 天内的数据自动查 DB 表，7 天以前的数据都会从 Blob 上下载下来读取到内存后再返回给前台画面 Blob 速度也挺快的，目前系统一切都很正常 |
 |
29
lyusantu 2024-07-31 09:04:40 +08:00
PG 百万数据就卡不应该吧 EXPLAIN 一下看看优化优化
|
 |
30
iamzcr 2024-07-31 09:19:13 +08:00
百万就卡，要不先看看执行计划？
|
 |
31
yiyufxst 2024-07-31 09:23:13 +08:00
我们 PG 大表（千万、亿级）连太慢，现在说把 PG 同步到 Starrocks ，SR 大表关联性能还可以，缺点是他复杂查询性能好，简单查询不如 PG 、MySQL 这种关系型数据库
|
 |
32
lpn666 2024-07-31 09:33:54 +08:00
flink cdc 然后 join ，最后吧结果数据写到 es 。转批处理为流处理，upsert 保证数据一致性。 后台查询 es 大表，保证实时。
|
 |
33
yjfkk 2024-07-31 10:25:42 +08:00
这不是搜索典型应用场景吗？上 ES
|
|
34
ModiKa2022 OP @jjx 现在就是把公司原有的 ERP 功能, 替代掉, ERP 更改总是要找外包处理, 现在在肢解原有的 ERP 系统
|
|
35
ModiKa2022 OP @volvo007 主要是 TOB, 查过 1s 的话, 前端会有明显的卡顿
|
|
36
ModiKa2022 OP @flxaq 有几张过亿的表, 那是一点儿都不敢连表, 稳定崩的, 现在是好几张百万级的表在关联查询, 卡顿很明显
|
|
37
ModiKa2022 OP @flxaq pg 使用的是阿里云, 运维那边的预算有限, 现在主要想的是后端的优化方案
|
|
38
ModiKa2022 OP @feifan19 业务上的时效性, 感觉跟你们的不太一样, 一个订单 可以一个月之前下单, 然后下个月才能到达(供应链上的业务, 可以提前很久下单的), 如果单纯的按照几天之前的数据分割, 不满足业务的需求
|
|
39
ModiKa2022 OP @lyusantu 好几张百万级别的表关联查询
|
|
40
ModiKa2022 OP @iamzcr 好几张百万级别的表关联查询
|
|
41
jjx 2024-07-31 11:27:19 +08:00
erp 的这种业务形态,用 es 无法解决的
sap 就是干脆搞 hana, 关系数据直接搞内存里 普通人就没有办法了, 除非这个使用习惯有调整 |
 |
42
oneisall8955 2024-07-31 12:45:25 +08:00
查询字段溶于一张表，详细表其余表走主键
|
|
43
feifan19 2024-07-31 13:04:20 +08:00
@ModiKa2022 #38 精髓就在于数据分离，分库分表其实也一样，就是把使用频率较低的数据迁移出去，提高数据查询速度。
分库分表其实也是冷热分离，月别做不了，年别应该是可以的吧 |
|
44
flxaq 2024-07-31 13:29:44 +08:00 via iPhone
你们这个业务可以不怎么用动，我们公司几万亿的数据，用 pg 完全没问题。设计下数据库就行了，你这几百万的，研究下索引基本 ok
|
|
45
ModiKa2022 OP @oneisall8955 处理的业务太多了,冗余的数据也很多
|
|
46
ModiKa2022 OP @flxaq 我很好奇你们公司数据库的硬件配置
|
 |
47
caronyan 2024-07-31 14:29:05 +08:00
用 cdc 预联表打宽做个新表，在新表上把联表查询改成单层的查询就行了，这个数据量延迟也就秒级
|
 |
48
RandomJoke 2024-07-31 15:27:21 +08:00
基于关系型数据库 能优化的：
1. 首次 fetch 只查 id ，根据 id 最后获取想要的数据 2. 部分冗余，部分卡顿场景具体分析解决 3. 分表，这个要针对业务场景了，有些情况要就是没法分的 4. 冷数据处理，比如几年前的数据，把它迁移走 5. 建立好索引，优化查询语句 基于 CDC： 1. 做个新的宽表，数据仍旧放在 PG 2. 新宽表放到 ES CH 等地方 |
 |
49
wu00 2024-07-31 15:52:33 +08:00
要是挡不住这个需求的话，赶紧抽数据做宽表吧。
我们的更恶心，相当于要把“订单”、“收货单”、“入库单”、“退货单”等不同类型的单据强行抽象成同一种业务单据供分页查询，并且每个字段可筛选，充分迎合业务人员的需求“一个页面可以把所有事情都干了”。 其实这需求都还好... 最恶心的是产品没想清楚，就潦草设计快速上线，然后三天两头的加字段改字段、改抽象逻辑、刷历史数据，苦的都是开发、测试。 |
|
50
ModiKa2022 OP @wu00 一样, 产品比较模糊, 开发涉及好 model 后, 业务需求变更了
|
 |
51
opzpy 2024-07-31 17:03:05 +08:00
先查询主表 ，然后多线程查询 join 表，内存连接，如果存在 join 表条件就在这个 join 表的查询结果中过滤掉主表的记录
|
 |
52
changdy 2024-07-31 20:45:37 +08:00 1
N 年前也遇到了和楼主同样的情况,数据量不大. 但是 join 之后就的体积就大了.同样也是查询条件分散在不同的表上.
我当时的做法是用 debezium 重新聚合到另外一张表上 .并适当的做 水平&垂直分表, 一个完整的 etl 流程 https://juejin.cn/post/7207410405786484796 https://juejin.cn/post/7323570678690185242 ps 楼上不少同学对复杂查询的经验不太足, 可能是比较幸运的程序员没有接受过复杂 erp 中各种逆天的 sql 1) 推荐 es 的: 虽然 es 是查询引擎 但 es 的查询侧重的并不是点对点查询 ,op 的问题主要在于 join 后的笛卡尔积太大. 2) 分开查询在 join: 如果你的所有条件都在一张表上是没问题的.如果是多张表那肯定还是数据库直接写 join 吧 同时 @yiyufxst 的经验非常好 数仓其实主要优化的还是复杂查询 ,在内存充足的情况下能保证结果尽量能出来. @jjx 的提到的 hana 的确可以搞一搞..但是就是价格也不菲. 我感觉几条可行的解决方案是 : 1) 自己写一套 etl 流程.制作宽表 2) 不写 etl 了, 直接全量 all in 到关系型数仓中 ,在关系型数仓中进行查询 (不过数仓也很耗费资源 .并且如果将来数据量大,同样也是会查询失败) 3) 内存之类的数据库 ( 这个我没试过 ,听介绍有可能可以. |
 |
53
Link9898 2024-08-01 00:48:23 +08:00
按照不同类别计算没有必要精确到明细上面，针对不同的需求去搞不同的处理办法，页面上全量搜索所有数据这特喵不开玩笑么
|
 |
54
lvlongxiang199 2024-08-01 07:41:26 +08:00
先明确下查询是 AP 还是 TP. 如果根据经过 filter 后, join 的数据量比较少是 TP 否则为 AP. AP 的话用 Doris 之类的 MPP 数据库吧
如果是 TP, 明确下为啥查询慢, join 顺序不对还是没加索引, 如果是前者的话, 通过 join hint 指定下 join 顺序 |
|
55
ModiKa2022 OP @Link9898 那是比较简单的业务场景, 真实的业务场景就是一个大列表, 列表中展示了 30-60 个字段, 每个字段可以单独搜索, 这些字段来自不同的表, 没有开玩笑, ToB 的业务很多就是这些东西
|
|
56
ModiKa2022 OP @changdy 感谢回复, 垂直拆分和水平拆分, 对我们这边的业务不太适用, 现在一直在寻求宽表的解决方案
|
|
57
Link9898 2024-08-01 11:56:35 +08:00
@ModiKa2022 我也是搞 ToB 的，，就感觉这样的业务很奇怪，不做限制么，，，所有数据搞到一张大表上数据权限怎么控制，，可以全部查看，，给上层领导看又没有必要精确到每一条明细上，，，
|
|
58
endershadow 2024-08-01 14:25:33 +08:00
上 pg 插件 hydra
|
 |
59
mark2025 2024-08-01 14:51:14 +08:00
统计场景可以考虑单独的一个库跑批或者物化视图
|
 |
60
sead 2024-08-01 14:58:08 +08:00
@ModiKa2022 PG 不应该这么菜啊，SQL 层面优化是否下足功夫？我测试过单 SQL 10 多张表一起处理（ 70 万单表有多张），整体比分开查快。。。后端减少了请求次数，处理数据所用的内存对象也大量减少；以前经常用 ORM ，怎么写都提升不了性能，后面手撸 SQL 差异肉眼可见
|
 |
61
macttt 2024-08-01 15:38:08 +08:00
如果查询并发量不大的话，感觉这个页面的内容给 click house 查更方便。如果查询并发量比较大，还是对业务单设置好唯一编号再用 cdc+ES 更方便
|
 |
62
encro 2024-08-01 16:06:52 +08:00
我们几千万几亿，都是连表查询，毫无压力。。。2H4G 阿里云。。。
阿里云 rds 打开慢查询，找到经常运行的慢 sql ，然后让他帮你分析下，一般是能分析出点东西的。 |
|
63
lingalonely 2024-08-02 09:55:34 +08:00
你能模拟一个你现在 连表的 sql 吗
|
 |
64
chunworkhard 2024-08-02 11:00:55 +08:00
pg 查询 count 慢，如果限制分页还是挺快的，可以把一些大表单据数据放到 Clickhouse 中，程序里需要处理下，一张表冗余查询出来的数据，初始化数据可以用 DataX
|
Select Language