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