情景:
需求:数据治理过程中,建立新的表B,存入A、C表的数据;后期定时搜索A表新增的数据,关联C表,取的需要的字段后,插入到B表
过程:
思路1:搜索A表中not in B表中的数据,插入到B表中(已用left join方法替代not in)
--执行速度太慢,放弃
insert into table_list_info
(table_name, table_project, zymc, zty, zy, gxpl, jxqk, data_type, uploadtime, aqdj, create_time)
select m.table_name,m.table_project, m.zymc,m.zty,m.zy,m.gxpl, m.gxqk, m.data_type, m.uploadtime,s.st_security_level,s.st_create_time
from ali_cloud_resource_table_list m
left join zy_zyinfo s on s.st_name = m.table_name and s.st_project = m.table_project
--用join + where *** is null 替代not in方法
left join
(select a.table_name,a.table_project
from ali_cloud_resource_table_list a
inner join table_list_info b on a.table_name = b.table_name and a.table_project = b.table_project) p
on m.table_project = p.table_project and m.table_name = p.table_name
where p.table_name is null
结果:执行速度太慢,超过20s,放弃!!!
上面的路没走通,换个思路呗!
思路2:在每次更新B表的时候,先清空B表,再将A表的数据全部插入B中
--速度快,数据不是太大的时候,需求满足时可以考虑
truncate table_list_info;
insert into table_list_info
(table_name, table_project, zymc, lybm, lyxt, gxpl, jxqk, data_type, uploadtime, aqdj, create_time)
select m.table_name,m.table_project, m.zymc,m.lybm,m.lyxt,m.gxpl, m.gxqk, m.data_type, m.uploadtime,s.st_security_level,s.st_create_time
from ali_cloud_resource_table_list_base m
left join zy_zyinfo s on s.st_name = m.table_name and s.st_project = m.table_project;
结果:速度满足要求了,但客户端会对B表进行操作,例如B表中新增的is_show字段,清空B表,操作结果也就没了。GG!
结果还是回到思路1,思路不变,优化sql语句!就叫思路3吧
思路3:回到1的思路,重新构造sql语句:
--
insert into table_list_info
(table_name, table_project, zymc, lybm, lyxt,zty,zy,gxpl, jxqk, data_type, uploadtime, aqdj, create_time)
select p.table_name,p.table_project, p.zymc,p.lybm,p.lyxt,p.zty,p.zy,p.gxpl, p.gxqk, p.data_type, p.uploadtime,p.st_security_level,p.st_create_time
from
--搜索 ali_cloud_resource_table_list、 ali_cloud_resource_table_list_base表中不存在于table_list_info中的数据,union拼接后,插入table_list_info表
(select m.table_name,m.table_project, m.zymc,m.lybm,m.lyxt,m.zty,m.zy,m.gxpl, m.gxqk, m.data_type, m.uploadtime,s.st_security_level,s.st_create_time
from ali_cloud_resource_table_list_base m
left join zy_zyinfo s on s.st_name = m.table_name and s.st_project = m.table_project
WHERE 1=1 AND
(SELECT COUNT(1) FROM table_list_info b
WHERE m.table_name=b.table_name and m.table_project=b.table_project)=0
union
select m.table_name,m.table_project, m.zymc,m.lybm,m.lyxt,m.zty,m.zy,m.gxpl, m.gxqk, m.data_type, m.uploadtime,s.st_security_level,s.st_create_time
from ali_cloud_resource_table_list m
left join zy_zyinfo s on s.st_name = m.table_name and s.st_project = m.table_project
WHERE 1=1 AND
(SELECT COUNT(1) FROM table_list_info b
WHERE m.table_name=b.table_name and m.table_project=b.table_project)=0) p;
嗯嗯,完美解决需求,几十万数据,执行时间2秒,可以接受!
当然,如果大佬们有更好的解决办法,希望不吝指教!!!
简单的连表插入参考:https://blog.csdn.net/beyond_1990/article/details/100703584