在项目中遇到两表关联更新的问题，将A表的a字段更新到B表的b字段，A表和B表都有相同的元素列。
遇到这个题目想的最简单的方式就是 update?A set a=B.b? from? where A.id=B.id。下面详细介绍本次优化过程?
一 update set? from 语句
update?openans_ncc_topo_default_ltp?set?related_ltp_id=openans_ncc_topo_default_layer.underlay from?openans_ncc_topo_default_layer?where?openans_ncc_topo_default_ltp.id=openans_ncc_topo_default_layer."_overlay";
2 将layer的下层id与端口主表id相同的话，将这行记录中的上层赋值给ltp表中的relative_ltp_id
update?openans_ncc_topo_default_ltp?set?related_ltp_id=openans_ncc_topo_default_layer."_overlay" from?openans_ncc_topo_default_layer?where?openans_ncc_topo_default_ltp.id=openans_ncc_topo_default_layer.underlay;
3 删除原来layer表中的所有端口的layer关系
delete?from?openans_ncc_topo_default_layer?where?model_type=30;
4?将端口中capacity中的max_capacity迁移到eth_ltp_extension中的band_width
update?openans_ncc_topo_default_eth_ltp_extension?set?band_width=openans_ncc_topo_default_capacity.max_capacity from?openans_ncc_topo_default_capacity?where?openans_ncc_topo_default_eth_ltp_extension.id=openans_ncc_topo_default_capacity.id;
5?删除物理端口的capacity中的行记录
delete?from?openans_ncc_topo_default_capacity?where?model_type=32;
6? 将某一张表的某几行的某几个字段更新到另外一张表
Insert?into??openans_ncc_topo_default_layer (technology,sub_technology,"_overlay","underlay",model_type)?select?1,1,openans_ncc_topo_default_ltp.id,openans_ncc_topo_default_ltp.related_ltp_id,32 from?openans_ncc_topo_default_ltp?where?model_type=33;
7??将某一张表的某几行的某几个字段更新到另外一张表
Insert?into?openans_ncc_topo_default_layer (technology,sub_technology,"_overlay","underlay",model_type)?select?1,1,openans_ncc_topo_default_ltp.related_ltp_id,openans_ncc_topo_default_ltp.id,32 from?openans_ncc_topo_default_ltp?where?model_type=31;
8??将某一张表的某几行的某几个字段更新到另外一张表
insert?into?openans_ncc_topo_default_capacity (model_type,id,max_capacity)?select?32,openans_ncc_topo_default_eth_ltp_extension.id,openans_ncc_topo_default_eth_ltp_extension.band_width from?openans_ncc_topo_default_eth_ltp_extension;
9? 多表关联更新
对于这里，想采用并行的方式，但是貌似并行更新不生效啊，反而更慢了，update可能加了间隙锁？
多表并联查询 三种方式
hash join? 对于没有采用索引的情况下，会走到这里，首先是以小表建立hash槽 然后找到对应的值
merge join 先将两个表进行排序，之后进行关联字段的匹配
nested loop 实际上是一个嵌套循环
可以看到，大部分时间都用在了扫描ltp表
对应的sql:
explain analyse
update openans_ncc_topo_default_ltp set related_ltp_id =
case
? when openans_ncc_topo_default_ltp.id=openans_ncc_topo_default_layer."_overlay" then openans_ncc_topo_default_layer.underlay
? when openans_ncc_topo_default_ltp.id=openans_ncc_topo_default_layer.underlay then openans_ncc_topo_default_layer."_overlay"
end from openans_ncc_topo_default_layer where openans_ncc_topo_default_layer.model_type=30;
10 pg 的连接方式??
right join? 右连接
left join 左连接
full join? ?全连接
inner join? 内连接
11??多表并联查询?
开启并行 set max_parallel_workers_per_gather=4;
执行
一 右连接
explain analyse select _overlay,underlay from openans_ncc_topo_default_ltp right join openans_ncc_topo_default_layer?
on( openans_ncc_topo_default_layer."_overlay"=openans_ncc_topo_default_ltp.id);
大概613ms 300w左右记录
关闭并行?set max_parallel_workers_per_gather=0; 好像没啥差别啊
二 左连接
三 内连接
四 全连接
优化记录
采用upsert语句
explain analyse;
INSERT INTO openans_ncc_topo_default_ltp (id, related_ltp_id)
? ? select _overlay,underlay? from openans_ncc_topo_default_layer where model_type=30
? ? ON CONFLICT (id) DO UPDATE SET related_ltp_id = EXCLUDED.related_ltp_id;
