大数据量联表操作

[ModiKa2022]

公司现在的业务, 总会涉及到连表操作, 百万级的数据, 一联表, 查询会变得的缓慢. 例如订单, 收货单, 入库单, 退货单, 验收单 数据一直在写入 如果涉及到这种情况, 除了冗余存储数据外, 还有什么其他的解决方案吗? 诚恳的咨询下各位

现在主要的解决方案是使用 PG 的物化视图的功能, 每隔几分钟生成一次物化视图, 但是数据实时性会有问题

• 感谢解答
• 补充下业务场景: -- 1,有好几张百万级别的表在关联查询 -- 2,PG使用的是阿里云的服务器,预算有限,现在后端在想解决方案 -- 3,每个表都有主表和明细表, 现在都是明细表相关联,查询计算的东西较复杂

[opengps]

硬限制没有太好的办法，就是靠闲时提前生成缓存之类的

[sagaxu]

难道不先分析一下查询计划？

[coderxy]

分开查询，在程序里关联

[xiaohang427]

看查询内容
查询明细，建议不要连表，先查主表，在关联其它表补充数据
汇总查询，定期生成汇总表，避免在大表里做汇总操作。很耗时

[lingalonely]

为什么需要连表，你又不是做分析，单纯查明细就不要连表

[endershadow]

大数据领域的技术都卷得这么厉害,感觉好多人都不知道似的

[dddd1919]

1. 正常业务功能，用 CDC 工具做宽表，在宽表处理
2. 数据分析，那就把业务数据同步到分析库比如 es

[wxyrrcj]

分开查询，在程序里关联

[gerefoxing]

减少大表关联，程序里查询赋值，使用本地缓存+redis 缓存

[lolizeppelin]

首先...数据库是不是 mysql?
如果是...抬走！

[ModiKa2022]

@coderxy 列表展示的, 所有的字段都允许搜索, 不能分开写 SQL

[ModiKa2022]

@lingalonely 列表展示的, 所有的字段都允许搜索, 不能分开写 SQL

[ModiKa2022]

@endershadow 给个具体的可以实现的关键词或者思路,感谢 !

[ModiKa2022]

@lolizeppelin PG

[wysnxzm]

数据库 join 改为代码 join
优点:笛卡尔积优化为 hash(你写个双重循环当我没说);硬盘查找变为内存查找;可跨库关联且不限制数据来源;可使用缓存优化性能
缺点:需要更多内存;数据库事务失效需要自己实现分布式事务或者不使用事务

[ModiKa2022]

@wysnxzm 感觉处理的代价比较大

[ModiKa2022]

@dddd1919 感谢

[746970179]

个人经验, 外键设置正常, 不会很慢
1 走索引: 业务上绝大部分查询, 都是使用唯一单号(运单号, 订单号, 产品 sku 等)或者时间, 这些字段设置好索引, 就挺快
2 只取要用的字段: 分两步, 第一步是获取当前页的 id, 第二步是针对这一页的 id, 再查询数据.
3 减少查询次数: 一页 100 条数据, 不要每个字段都是 1 个 sql, 同一字段的可以一个 sql 生成字典

[ModiKa2022]

@746970179 这种是常用的方式, 但是只能处理搜索主表的情况, 现在是每个字段都可以搜索, 所以要 join

[thinkwei2012]

技术上这种详情表再关联订单表进行某字段搜索查询确实很慢，最简单的方法就是做冗余，大胆一点就单独拉一张宽表专门搜索数据双写。
要么改下页面设计，比如让产品重新设计页面拆分一些到不同的查询表单中，尽量避免少的联表

[simen513]

最常用的做法是：

特大数据量的表，按时间做 partition 分表。
查询时分时段查询，再汇总到周、月、年，一级一级汇总。

[ModiKa2022]

@thinkwei2012 冗余数据,涉及到很多逻辑上的处理, 对每个列表都要单独冗余数据, 系统比较大, 涉及的查看页面有些多

[ModiKa2022]

@simen513 挺好的思路, 现在的问题是, 每个独立的表都是单独的单子,时间是独立的, 如果按照时间分表, 逻辑上很多 join 不好处理

[kestrelBright]

这种上 es 吧

[jjx]

erp 很难

表现在客户查询条件巴不得每个字段都可以用来查询

报表页面必须有小计总计, 使得优化困难

数据一定要实时性, 高度要求数据正确

数据查看需要各种制约,比方说按地区/员工/合作伙伴/部门等等, 不得不联表

当然,这些还仅仅是 erp 变态需求中的一部分

[volvo007]

我一直以为是我使用数据库的方法有问题，原来大家也都会遇到类似的问题。不过这里想问问，所谓的查询速度快是多快呢？

对于内部用户，我认为百万级的多个表关联（例如 10 张表），返回 100 条以内的数据的响应时间在 3 秒内算是可以接受。如果面向 toB 用户，那么秒级差不多；而面向 toC 用户，大概要在 100 ms 这个水平？

[flxaq]

先看你 pg 硬件配置，我们公司一个表几亿的，pg 连几十个表没什么问题，ssd 的还要做好数据库参数优化，要改成适合 ssd 的参数，让 sql 能走合理的索引，连表关键要看索引是否合理，对应表要看具体业务场景，有时间纬度的可以按时间分区。数据库内存 等参数要合理分配

[feifan19]

分库分表，数据缓存，读写分离之类的没什么好说的，
提供一种冷热分离的思路，我们项目中每天凌晨会有一个定时任务，把 7 天前的交易数据压缩为 zip 上传到 Blob 上
7 天内的数据自动查 DB 表，7 天以前的数据都会从 Blob 上下载下来读取到内存后再返回给前台画面
Blob 速度也挺快的，目前系统一切都很正常

[lyusantu]

PG 百万数据就卡不应该吧 EXPLAIN 一下看看优化优化

[iamzcr]

百万就卡，要不先看看执行计划？

[yiyufxst]

我们 PG 大表（千万、亿级）连太慢，现在说把 PG 同步到 Starrocks ，SR 大表关联性能还可以，缺点是他复杂查询性能好，简单查询不如 PG 、MySQL 这种关系型数据库

[lpn666]

flink cdc 然后 join ，最后吧结果数据写到 es 。转批处理为流处理，upsert 保证数据一致性。 后台查询 es 大表，保证实时。

[yjfkk]

这不是搜索典型应用场景吗？上 ES

[ModiKa2022]

@jjx 现在就是把公司原有的 ERP 功能, 替代掉, ERP 更改总是要找外包处理, 现在在肢解原有的 ERP 系统

[ModiKa2022]

@volvo007 主要是 TOB, 查过 1s 的话, 前端会有明显的卡顿

[ModiKa2022]

@flxaq 有几张过亿的表, 那是一点儿都不敢连表, 稳定崩的, 现在是好几张百万级的表在关联查询, 卡顿很明显

[ModiKa2022]

@flxaq pg 使用的是阿里云, 运维那边的预算有限, 现在主要想的是后端的优化方案

[ModiKa2022]

@feifan19 业务上的时效性, 感觉跟你们的不太一样, 一个订单 可以一个月之前下单, 然后下个月才能到达(供应链上的业务, 可以提前很久下单的), 如果单纯的按照几天之前的数据分割, 不满足业务的需求

[ModiKa2022]

@lyusantu 好几张百万级别的表关联查询

[ModiKa2022]

@iamzcr 好几张百万级别的表关联查询

[jjx]

erp 的这种业务形态,用 es 无法解决的

sap 就是干脆搞 hana, 关系数据直接搞内存里

普通人就没有办法了, 除非这个使用习惯有调整

[oneisall8955]

查询字段溶于一张表，详细表其余表走主键

[feifan19]

@ModiKa2022 #38 精髓就在于数据分离，分库分表其实也一样，就是把使用频率较低的数据迁移出去，提高数据查询速度。
分库分表其实也是冷热分离，月别做不了，年别应该是可以的吧

[flxaq]

你们这个业务可以不怎么用动，我们公司几万亿的数据，用 pg 完全没问题。设计下数据库就行了，你这几百万的，研究下索引基本 ok

[ModiKa2022]

@oneisall8955 处理的业务太多了,冗余的数据也很多

[ModiKa2022]

@flxaq 我很好奇你们公司数据库的硬件配置

[caronyan]

用 cdc 预联表打宽做个新表，在新表上把联表查询改成单层的查询就行了，这个数据量延迟也就秒级

[RandomJoke]

基于关系型数据库 能优化的：
1. 首次 fetch 只查 id ，根据 id 最后获取想要的数据
2. 部分冗余，部分卡顿场景具体分析解决
3. 分表，这个要针对业务场景了，有些情况要就是没法分的
4. 冷数据处理，比如几年前的数据，把它迁移走
5. 建立好索引，优化查询语句
基于 CDC：
1. 做个新的宽表，数据仍旧放在 PG
2. 新宽表放到 ES CH 等地方

[wu00]

要是挡不住这个需求的话，赶紧抽数据做宽表吧。

我们的更恶心，相当于要把“订单”、“收货单”、“入库单”、“退货单”等不同类型的单据强行抽象成同一种业务单据供分页查询，并且每个字段可筛选，充分迎合业务人员的需求“一个页面可以把所有事情都干了”。

其实这需求都还好... 最恶心的是产品没想清楚，就潦草设计快速上线，然后三天两头的加字段改字段、改抽象逻辑、刷历史数据，苦的都是开发、测试。

[ModiKa2022]

@wu00 一样, 产品比较模糊, 开发涉及好 model 后, 业务需求变更了

[opzpy]

先查询主表 ，然后多线程查询 join 表，内存连接，如果存在 join 表条件就在这个 join 表的查询结果中过滤掉主表的记录

[changdy]

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) 内存之类的数据库 ( 这个我没试过 ,听介绍有可能可以.

[Link9898]

按照不同类别计算没有必要精确到明细上面，针对不同的需求去搞不同的处理办法，页面上全量搜索所有数据这特喵不开玩笑么

[lvlongxiang199]

先明确下查询是 AP 还是 TP. 如果根据经过 filter 后, join 的数据量比较少是 TP 否则为 AP. AP 的话用 Doris 之类的 MPP 数据库吧

如果是 TP, 明确下为啥查询慢, join 顺序不对还是没加索引, 如果是前者的话, 通过 join hint 指定下 join 顺序

[ModiKa2022]

@Link9898 那是比较简单的业务场景, 真实的业务场景就是一个大列表, 列表中展示了 30-60 个字段, 每个字段可以单独搜索, 这些字段来自不同的表, 没有开玩笑, ToB 的业务很多就是这些东西

[ModiKa2022]

@changdy 感谢回复, 垂直拆分和水平拆分, 对我们这边的业务不太适用, 现在一直在寻求宽表的解决方案

[Link9898]

@ModiKa2022 我也是搞 ToB 的，，就感觉这样的业务很奇怪，不做限制么，，，所有数据搞到一张大表上数据权限怎么控制，，可以全部查看，，给上层领导看又没有必要精确到每一条明细上，，，

[endershadow]

上 pg 插件 hydra

[mark2025]

统计场景可以考虑单独的一个库跑批或者物化视图

[sead]

@ModiKa2022 PG 不应该这么菜啊，SQL 层面优化是否下足功夫？我测试过单 SQL 10 多张表一起处理（ 70 万单表有多张），整体比分开查快。。。后端减少了请求次数，处理数据所用的内存对象也大量减少；以前经常用 ORM ，怎么写都提升不了性能，后面手撸 SQL 差异肉眼可见

[macttt]

如果查询并发量不大的话，感觉这个页面的内容给 click house 查更方便。如果查询并发量比较大，还是对业务单设置好唯一编号再用 cdc+ES 更方便

[encro]

我们几千万几亿，都是连表查询，毫无压力。。。2H4G 阿里云。。。

阿里云 rds 打开慢查询，找到经常运行的慢 sql ，然后让他帮你分析下，一般是能分析出点东西的。

[lingalonely]

你能模拟一个你现在 连表的 sql 吗

[chunworkhard]

pg 查询 count 慢，如果限制分页还是挺快的，可以把一些大表单据数据放到 Clickhouse 中，程序里需要处理下，一张表冗余查询出来的数据，初始化数据可以用 DataX