【屎山代码雕花日记】处理垃圾SQL，优化从100s+到 1.5s内。

最新推荐文章于 2024-10-30 16:01:41 发布
最新推荐文章于 2024-10-30 16:01:41 发布
阅读量517 收藏 3
点赞数 8
分类专栏： 运维 文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/EasyJax/article/details/139718863
版权

首选要说明的一点，这个代码仅仅是处理数量为千级以上的数据就会造成严重超时，且这个代码非笔者本人所写。但是这个代码相关的一个模块因为该代码造成了严重的生产环境问题，亟需解决。

起初在生产环境调试，日志提示该问题为Open Feign调用异常，所以一直对feign做debug，而且日志也能正常输出该语句的执行记录。导致一直没法准确定位到问题，但是设置feign超时时间为60秒后，仍不能解决问题后，俺把真正的问题定位在SQL代码层面。

来欣赏这坨shit，新建查询跑起来之后，就能闻到代码有股“坏味道”。（代码有点长，不想看可以直接跳过，勿展开）

-- ps:部分mybatis语法已注释掉，以下是shit 代码
select
		     DISTINCT t.task_id,
		     t.task_name,
		     t.content,
		     t.province,
		     t.city,
		     t.area,
		     t.address,
		     t.latitude,
		     t.longitude,
		     t.task_status,
		     t.begin_time,
		     t.end_time,
			 t.dept_ids,
			 t.deptName,
			 t.is_consultation,
			 t.consultation_status,
		     t.consultation_id,
		     t.user_id,
		     t.create_time
		from (
				select
				      t.task_id,
				      t.task_name,
				      t.content,
		              t.province,
		              t.city,
				      t.area,
				      t.address,
				      t.latitude,
				      t.longitude,
				      t.task_status,
				      t.begin_time,
				      t.end_time,
					  t.dept_ids,
						(
						   select GROUP_CONCAT(`name` SEPARATOR '、') deptName
								from sys_dept
								where  FIND_IN_SET(dept_id,t.dept_ids)
						) deptName,
						CASE
						WHEN 1=1 THEN
							  1
						END is_consultation,
						CASE
						WHEN c.status is not null THEN
							  c.status
						else
						0
						END consultation_status,
		                IFNULL(c.consultation_id,0) consultation_id,
		                t.user_id,
		                t.create_time
				from sys_task t
				LEFT JOIN
				(
					select
					     c.consultation_id,
					     c.status,
					     c.task_id
					from sys_task t
					LEFT JOIN consultation c on t.task_id=c.task_id
					where t.del_flag=0
					and c.del_flag=0
					and t.task_type=1
					and t.user_id= 1  #{userId}
				)c on t.task_id=c.task_id
				where t.del_flag=0
				and t.task_type=1
				and t.user_id=  1 #{userId}
				UNION
				SELECT
				      t.task_id,
				      t.task_name,
				      t.content,
		              t.province,
		              t.city,
				      t.area,
				      t.address,
				      t.latitude,
				      t.longitude,
				      t.task_status,
				      t.begin_time,
				      t.end_time,
				      t.dept_ids,
					  (
				       select GROUP_CONCAT(`name` SEPARATOR '、') deptName
					   from sys_dept
					   where  FIND_IN_SET(dept_id,t.dept_ids)
				       ) deptName,
						CASE
						WHEN 1=1 THEN
								2
						END is_consultation,
						CASE
						WHEN c.status is not null THEN
							  c.status
						else
						0
						END consultation_status,
		                IFNULL(c.consultation_id,0) consultation_id,
		                t.user_id,
		                t.create_time
					FROM sys_task AS t
					JOIN sys_user_task AS ut ON t.task_id = ut.task_id
					JOIN sys_user AS u ON ut.user_id = u.user_id
					LEFT JOIN
					(
						select
						     c.consultation_id,
						     c.status,
						     c.task_id
						from sys_task t
						LEFT JOIN consultation c on t.task_id=c.task_id
						LEFT JOIN consultation_user cu on c.consultation_id=cu.consultation_id
						where t.del_flag=0
						and c.del_flag=0
						and cu.del_flag=0
						and t.task_type=1
						and cu.user_id= 1 #{userId}
					)c on t.task_id=c.task_id
					where t.del_flag=0
					and ut.del_flag=0
					and u.del_flag=0
					and t.task_type=1
		            and ut.user_id= 1 #{userId}
				-- <if test="deptList!=null and deptList.size>0">
				UNION
				select
				     t.task_id,
				     t.task_name,
				     t.content,
					 t.province,
					 t.city,
				     t.area,
				     t.address,
				     t.latitude,
				     t.longitude,
				     t.task_status,
				     t.begin_time,
				     t.end_time,
				     t.dept_ids,
					(
					   select GROUP_CONCAT(`name` SEPARATOR '、') deptName
							from sys_dept
							where  FIND_IN_SET(dept_id,t.dept_ids)
					) deptName,
					CASE
					 WHEN tt.task_id is not null THEN
							 2
					 else
					0
					END is_consultation,
							CASE
					WHEN c.status is not null THEN
						  c.status
					else
					0
					END consultation_status,
					IFNULL(c.consultation_id,0) consultation_id,
					t.user_id,
					t.create_time
				from sys_task t
				LEFT JOIN sys_user u on t.user_id=u.user_id
				LEFT JOIN
				(
					select
					      c.consultation_id,
					      c.status,
					      c.task_id
					from sys_task t
					LEFT JOIN consultation c on t.task_id=c.task_id
					where t.del_flag=0
					and c.del_flag=0
					and t.task_type=1
					and t.user_id= 1 #{userId}
				)c on t.task_id=c.task_id
				LEFT JOIN
				(
					SELECT
						 tt.task_id,
						 tt.task_name,
						 u.username,
						 ut.user_task_id
					FROM sys_task AS tt
					JOIN sys_user_task AS ut ON tt.task_id = ut.task_id
					JOIN sys_user AS u ON ut.user_id = u.user_id
					WHERE  tt.del_flag=0
					and ut.del_flag=0
					and u.del_flag=0
					and tt.task_type=1
					and u.user_id = 1 #{userId}
				) tt on t.task_id=tt.task_id
				where t.del_flag=0
				and u.del_flag=0
				and t.task_type=1
				and u.dept_id in
-- 				<foreach collection="deptList" open="(" separator="," close=")" item="item">
-- 						#{item}
-- 				</foreach>
				(...)   -- 这里是传入一大堆参数，数据量级一旦扩大势必会导致性能低下，要注意
				and t.task_id not in
				(
					select
					      t.task_id
					from sys_task t
					LEFT JOIN
					(
						select
						      c.consultation_id,
						      c.status,
						      c.task_id
						from sys_task t
						LEFT JOIN consultation c on t.task_id=c.task_id
						where t.del_flag=0
						and c.del_flag=0
						and t.task_type=1
						and t.user_id=1 #{userId}
					)c on t.task_id=c.task_id
					where t.del_flag=0
					and t.task_type=1
					and t.user_id= 1 #{userId}
					UNION
					SELECT
					      t.task_id
					FROM sys_task AS t
					JOIN sys_user_task AS ut ON t.task_id = ut.task_id
					JOIN sys_user AS u ON ut.user_id = u.user_id
					LEFT JOIN
					(
						select
							c.consultation_id,
							c.status,
							c.task_id
						from sys_task t
						LEFT JOIN consultation c on t.task_id=c.task_id
						LEFT JOIN consultation_user cu on c.consultation_id=cu.consultation_id
						where t.del_flag=0
						and c.del_flag=0
						and cu.del_flag=0
						and t.task_type=1
						and cu.user_id= 1 #{userId}
					)c on t.task_id=c.task_id
					where t.del_flag=0
					and ut.del_flag=0
					and u.del_flag=0
					and t.task_type=1
					and ut.user_id= 1 #{userId}
				)
				-- </if>
		   )t
		where 1=1

整个SQL查询执行结果耗费时间居然要达到一百多秒甚至不止：
在这里插入图片描述

我们再 56 - 71行这里看到有以下代码：

LEFT JOIN
(
    select
         c.consultation_id,
         c.status,
         c.task_id
    from sys_task t
    LEFT JOIN consultation c on t.task_id=c.task_id
    where t.del_flag=0
    and c.del_flag=0
    and t.task_type=1
    and t.user_id= 1
)c on t.task_id=c.task_id
where t.del_flag=0 and t.task_type=1 and t.user_id=  1

这里在前面已经通过嵌套查询了一层情况下，还要进行一次联表查询，再嵌套一层联表去查。这里完全就是多此一举的操作，观察后完全可以把以上这个代码简化成一个联表查询而不需要去嵌套。为什么呢？注意看这里只是联表consultation，而上方的sys_task已经做过查询，完全可以做联表。

所以直接修改成以下：

LEFT JOIN consultation c on t.task_id=c.task_id and c.del_flag=0 and t.task_type=1 and t.user_id= 1

然后我们执行一遍SQL （23 - 71行）。可以看到这个查询就需要花费70多秒的时间：

在这里插入图片描述

我们再执行以下优化好的代码：

select
				      t.task_id,
				      t.task_name,
				      ... -- 省略
						 ( select GROUP_CONCAT(`name` SEPARATOR '、') deptName from sys_dept where  FIND_IN_SET(dept_id,t.dept_ids) ) deptName,
						CASE WHEN 1=1 THEN 1 END is_consultation,
						CASE WHEN c.status is not null THEN c.status else 0 END consultation_status,
						IFNULL(c.consultation_id,0) consultation_id,
						t.user_id,
						t.create_time
				from sys_task t
				-- 优化后
				LEFT JOIN consultation c on t.task_id=c.task_id and c.del_flag=0 and t.task_type=1 and t.user_id= 1 
				where t.del_flag=0
				and t.task_type=1
				and t.user_id= 1

在这里插入图片描述

可以看到直接把运行速度干到 秒内，证明我们的思路是对的，就是排查掉SQL内的多层嵌套联表，顺着这个思路把SQL内存在同样操作的代码优化掉。 还有很重要的一点就是 注意优化后的语句结果需要注意是否和原本结果吻合，屎山代码难以改动其中之一的一点在于改完之后结果也需要对得上。

-- 108 到 127 行原来的代码
LEFT JOIN
(
    select
         c.consultation_id,
         c.status,
         c.task_id
    from sys_task t
    LEFT JOIN consultation c on t.task_id=c.task_id
    LEFT JOIN consultation_user cu on c.consultation_id=cu.consultation_id
    where t.del_flag=0
    and c.del_flag=0
    and cu.del_flag=0
    and t.task_type=1
    and cu.user_id= 1
)c on t.task_id=c.task_id
where t.del_flag=0and ut.del_flag=0 and u.del_flag=0 and t.task_type=1 and ut.user_id= 1

-- 优化之后的代码
LEFT JOIN  consultation c on t.task_id = c.task_id and c.del_flag=0 
LEFT JOIN  consultation_user cu on c.consultation_id = cu.consultation_id and cu.del_flag=0 and cu.user_id= 1
where t.del_flag=0 and ut.del_flag=0 and u.del_flag=0 and t.task_type=1 and ut.user_id= 1

总结：

1、不要进行过多的联表嵌套查询；

2、在有过多业务筛选条件的情况之下，不要把条件塞在一条SQL内运行；

3、很多代码当下不出问题可能是因为数据库数据样本不够多，这点需要重视起来；

4、遵守编程规范，保持良好的编程习惯。

EasyJax
关注
专栏目录
从数据洞察到智能决策：合合信息&infiniflow RAG技术的实战案例分享
丨汀、的博客
09-11 311
从数据洞察到智能决策：合合信息&infiniflow RAG技术的实战案例分享
一条垃圾SQL，把 64 核 CPU 快跑崩了！
sinat_32849897的博客
09-09 234
最近系统出了一个严重问题，应用程序卡崩导致不可用，把 Oracle 数据库服务器 64 核 CPU 快被跑满了：经定位，是因为一条垃圾 SQL 引起的！！其实也就是一条很简单的 SQL：...
垃圾sql
刘瑾炎
04-04 216
1.Minus 　　MINUS运用在两个SQL语句上，先找出第一条SQL语句所产生的结果，然后看这些结果有没有在第二个SQL语句的结果中。如果有的话，那这一笔记录就被去除，而不会在最后的结果中出现。如果第二个SQL语句所产生的结果并没有存在于第一个SQL语句所产生的结果内，那这笔资料就被抛弃. 2.DISTINCT 去重 3.ALTER --修改表的编码 ...
傻逼的sql
韩俊的个人博客
06-07 661
今天调试程序，发现了不知道哪位大神写的sql select * from t1,(select * from t2) t2 where t1.id=t2.id 这个sql相当于select * from t1, t2 where t1.id=t2.id 不知道脑子抽了还是怎么了多这么傻逼的sql。 于是乎我写了个傻逼sql生成器，专门生成这样的sql。 public class 傻逼sq
如何找到垃圾SQL语句,你知道这些方式吗？
西魏陶渊明的博客
01-04 342
作者: 西魏陶渊明 博客: https://blog.springlearn.cn/ (opens new window) 西魏陶渊明 莫笑少年江湖梦，谁不少年梦江湖 这篇文章主要是讲如何找到需要优化的SQL语句，即找到查询速度非常慢的SQL语句。 # 一、慢查询日志 # 1. 何为慢查询日志 慢查询日志是MySQL提供的一种日志记录，它用来记录查询响应时间超过阀值的SQL语句 这个时间阀值通...
年度最传神新词:屎上雕花
Peter的博客
12-24 691
各位 Merry Chrismas!
3dmax一键雕花插件下载
02-11
用户只需将“一键雕花.mse”文件拖放到3Dmax的工作视口内，即可启动插件。这种方法避免了复杂的安装步骤，使得插件的部署快速而便捷。 “一键雕花.jpg”可能是插件的界面截图或示例效果展示，用户可以通过查看这张...
3DMAX一键中式图案雕花插件
08-16
而".mse"文件是3DMAX插件的扩展名，它包含了插件的所有代码和资源，安装时需要将这个文件复制到3DMAX的插件目录下，重启软件后就能在菜单栏或工具栏中找到插件的相关选项。 在使用3DMAX一键中式图案雕花插件时，...
3dmax脚本一键雕花.zip
06-29
总的来说，"3dmax脚本一键雕花.zip" 是一个优化3ds Max工作流程的实用工具，它展示了脚本在3D建模中的强大潜力，同时也要求用户具备一定的3ds Max脚本知识和使用经验。通过熟练掌握这样的脚本，设计师可以大大提高...
Springboot 整合 Java DL4J 实现室内装饰设计系统
欢迎拜读我的作品，喜欢的领域请给我留言
10-26 795
在当今数字化时代，利用人工智能技术为室内装饰设计提供创新解决方案成为了可能。本文将介绍如何使用 Spring Boot 整合 Java Deeplearning4j 构建一个室内装饰设计系统，该系统能够根据房间照片自动生成装修方案。 本系统采用了 `Spring Boot` 作为后端框架，结合 `Java Deeplearning4j` 进行图像识别和处理。前端可以使用任何现代的 Web 框架来展示装修方案和与用户进行交互。
从零开始写屎山数据库代码
qq_63747498的博客
03-15 472
本篇文章专门记录从零开始写屎山数据库代码的，希望走过路过跑一下给一点意见，将后续进行持续优化。所有给我提出真知灼见的同学们对此表示深深的感激，并且将在后边的实验中永远加入特别鸣谢的附录中。1、18级计工本李浩然师哥：对于drop_database的path路径的修正。工作的改造前景：模仿SSMS开发一个图形化界面。第一章：进行数据库的创建。
祖传屎山代码平时不优化，一重构就翻天覆地
pengyachuan的博客
03-14 914
代码垒了又垒，维护困难，屎山代码重构，改善代码可读性和可维护性，本文旨在帮助读者了解重构的思路、技巧。
NoSQL之 Redis配置与优化
最新发布
abandon的博客
10-30 730
正常情况下，大部分的访问请求应该时先被redis缓存响应，在redis那里得不到响应的小部分访问请求才会去请求MySQL数据库获取数据，这样MySQL数据库的负载压力会非常小，能够正常稳定工作。6）开启lazy-free机制（lazyfree-lazy-* yes），将删除过期key的操作放到后台线程执行，减少大量的删除操作对redis主进程的阻塞。持久化保存的文件占用空间更大，恢复速度更慢，性能影响更大，AOF文件重写期间在fork子进程时也会阻塞redis主进程，且IO压力更大。
解决Navicat连接postgresql时出现‘datlastsysoid does not exist‘报错
小琳猫的博客
10-29 373
解决Navicat连接postgresql时出现‘datlastsysoid does not exist‘报错
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值