先上结论:
两个数据量并不大的表,一个200条数据,一个2000条数据,通过sql左连接查询耗时约0.5s,网络不好时会有1.5s(通过VPN远程连接), 通过代码逻辑的话基本上保持再0.1-0.3s。全部6表的左连接查询也不超过10s,通过sql查询6表数据耗时在10分钟以上。数据量上千之后的两表左连接查询就比代码逻辑处理慢了。
实验背景
两年前负责业务平台的运营,有个定期数据统计的任务,由于一开始比较简单,就直接使用sql语句查询,将数据导出,后来查询的字段和表越来越多,为了图省事,还是继续扩展sql语句进行查询,当时数据比较少,耗时还行,3分钟之内能出数据,但是sql语句已经很臃肿,6个表的左连接。
后来由于工作调动,去负责别的任务,这个就交接了,再后来又被调回来,不出所料目前仍继续使用这个sql执行该任务(此项目为存量项目,目前也是一些没有研发经验的同志维护,并没有引入新工具)。现在这个sql每次要执行10分钟以上,网络不好时会达到30分钟[捂脸]。
数据表情况
涉及6个表,目前数据最少的是用户表,有200多条;其次是项目表,2000多条数据,其他几个表的数据分别是CI:41000、CD:9000、CD_H:48000、CODE:96000
实验过程
本人技术能力有限,无法从理论层面分析此问题,只能通过实验,执行不同sql查看耗时来得出结论。
首先显示优化了sql语句,限制查询范围,去除无用的字段,优化左连接的方式,最终耗时稍微短了一点,但还是很长,有事仍会达到10分钟,基本断定是数据量过大,过多的左连接会导致数据查询时间成倍的增长。
SELECT LEFT
t.ID,
p.PROJECT_ID,
p.PROJECT_NAME,
count(i.ID ),
count( bi.ID ),
count( h.id ) + count( da.deploy_id ),
COUNT( commit_id ),
sum(ADD_COUNT),
sum(del_COUNT)
from tenant t
LEFT JOIN project p ON t.ID = p.TENANT_ID
LEFT JOIN ci_instance i ON i.PROJECT_ID = p.PROJECT_ID
LEFT JOIN cd_application da ON da.PROJECT_ID = p.PROJECT_ID
LEFT JOIN cd_application_history h ON h.DEPLOY_ID = da.DEPLOY_ID
LEFT JOIN code_commit com ON com.project_Id = p.PROJECT_ID
GROUP BY
t.ID,
p.PROJECT_ID,
p.PROJECT_NAME;
于是开始尝试减少左连接的表,先是测试了用户表与项目表,发现耗时比较短,在0.5-1.5s之间,于是增加了与CI表的连接,结果耗时增加到了7-9s。
单独查询这几个大表,限定用户和时间范围,查询时间均控制在3s以内。
通过代码,分别将6个表的所需数据单独查询出来,通过代码逻辑将数据进行组装,最终测试总用时在5-7s。
最终,用python简单制作了一个exe文件,实现以上逻辑,并将数据输出到表格。
也简单研究了一些开源的数据可视化工具,但是都无法进行数据的导出,而且考虑到其他同事的学习成本,也就暂时没有选用,后续有机会再进行研究。
本人技术造诣也不高,目前涉及到的技术类工作也比较少,就是偶尔觉得能用技术手段解决的就浅学一下;也尝试查了一下没有查到相关的记录,实际操作起来也废了不少力气,就简单记录一下,请各位大佬指点。