java 有一个sql失败就回滚 sql在java代码中执行很慢

【问题描述】

系统包含PC管理端和移动端，生产环境在用户访问高峰时间段数据无法加载，卡死，如图：

【问题定位】

1. 生产环境
   单机部署：
   应用服务JRE8 WebApplication
   Web服务器Jetty
   操作系统Windwos Server 2008
   数据库SQLServer 2008 R2
   
   外部对接：
   接收外部系统的图片推送，保存到服务器磁盘；
    
2. 应用卡死时查看服务器性能
   磁盘IO：
   
   网络连接：
   
   CPU使用情况：
   
   可以看到服务器在高峰时间段明显的会出现性能瓶颈，各个方面指标都出现明显峰值，需要对性能进行综合优化；
3. 应用过滤器中增加页面响应计时代码，监控响应大于10s的页面（根据系统实际卡顿情况设置）：
   com.xloa.util.MyFilter
   
   根据控制台输出定位响应慢的页面；
4. 数据库性能瓶颈定位
   查询慢SQL（脚本见附件）：
   
   可以看到有三条SQL的执行时间达到了百秒级别，重点优化这些SQL；
5. Java应用控制台提示数据库数据死锁；
6. 磁盘IO读写频繁，通过系统内监视器：

1. 读写压力主要是保存外部接口推送的图片, 图片5M左右，会有多张同时读写的进程；
2. C盘空间会快速占据（非图片保存空间）；

【问题分析及解决】

1. 首先解决磁盘IO问题，因为IO缓慢，系统整体会性能下降；

1. 外部接口调整，推送压缩后图片，减少读写压力；
2. 外部接口进一步调整，只传入图片访问链接，本系统中不再保存图片，将压力转移到外部接口的服务器；
3. 如果确实需要频繁读写，云主机更换高性能磁盘HDD->SSD；
4. 文件存储要根据日期或月创建目录；
5. C盘空间快速被占用，通过资源管理器中查看，是SQLServer的Report数据库占用空间快速增加，解决方法：

1. 调整数据快照、日志等策略，降低备份频率，减少空间占用；如数据库有审计日志，定期清理；
2. 新开专用数据库服务器；
    

2. 响应慢的页面与数据库慢查询有关，首先优化慢查询：

1. 分析慢查询，是查询部门等不经常变动信息，可以将信息保存到缓存中；

3. 服务器连接数增大，一方面是本身业务需要有大量访问；另一方面是请求没有及时响应、响应时间较长；

1. 本身业务需要有大量访问，调整Web服务器的并发连接数相关参数；如果问题仍然无法解决考虑增加负载均衡；本例在做了其他方面优化后，性能问题已经解决，成本考虑，没有做分布式部署；
2. 请求响应慢，在进行了慢查询优化后得到了解决；
    

4. 数据库死锁：

本例是对接数据的频繁写入造成，解决方案是将对接的数据先插入临时表，通过定时任务，读取临时表数据，设置任务的调度时间，减慢插入正式表的速度；
更进一步可以考虑读写分离及数据库集群；

【后记】

1. 业务上如无必须，不重复保存数据，导致资源浪费，还增加风险。比如本次问题中外部接口已经保存了图片，本应用不必重复保存。
2. 本次性能优化涉及到网络并发请求、数据库慢查询、磁盘读写，遇到性能问题时，要根据自身应用定位问题，然后采取相应的解决方案；

【相关脚本】

SQLServer查询慢SQL：

#清除缓存

dbcc freeProcCache;

#然后查询慢SQL

SELECT
	DB_ID ( DB.dbid ) '数据库名',
	OBJECT_ID ( db.objectid ) '对象',
	QS.creation_time '编译计划的时间',
	QS.last_execution_time '上次执行计划的时间',
	QS.execution_count '执行的次数',
	QS.total_elapsed_time / 1000 '占用的总时间（毫秒）',
	QS.total_physical_reads '物理读取总次数',
	QS.total_worker_time / 1000 'CPU 时间总量（毫秒）',
	QS.total_logical_writes '逻辑写入总次数',
	QS.total_logical_reads N '逻辑读取总次数',
	QS.total_elapsed_time / 1000 N '总花费时间（毫秒）',
	SUBSTRING(
		ST.text,
		( QS.statement_start_offset / 2 ) + 1,
		(
			(
			CASE
					statement_end_offset 
					WHEN - 1 THEN
					DATALENGTH ( st.text ) ELSE QS.statement_end_offset 
				END - QS.statement_start_offset 
			) / 2 
		) + 1 
	) AS '执行语句' 
FROM
	sys.dm_exec_query_stats AS QS CROSS APPLY sys.dm_exec_sql_text ( QS.sql_handle ) AS ST
	INNER JOIN (
	SELECT
		* 
	FROM
		sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) 
	) DB ON QS.plan_handle = DB.plan_handle 
WHERE
	SUBSTRING(
		st.text,
		( qs.statement_start_offset / 2 ) + 1,
		(
			(
			CASE
					statement_end_offset 
					WHEN - 1 THEN
					DATALENGTH ( st.text ) ELSE qs.statement_end_offset 
				END - qs.statement_start_offset 
			) / 2 
		) + 1 
	) NOT LIKE '%fetch%' 
ORDER BY
	QS.total_elapsed_time / 1000 DESC