记录一次数据库优化的过程

前段时间用SSH开发了一个系统,其中有一个需求比较麻烦,在一个显示列表中,要执行几个数据表关联,而且是满足几种情况之一的数据都要显示出来,刚开始开发的时候在程序里面使用HQL,多表联立查询,将满足结果的数据一次性取出。

具体代码如下:

	/**
	 * 获取被邀请的教案,即有权看到的教案(包括自己发布的),过滤年级、科目
	 *
	 * @param userInfoId
	 * @param termNo
	 * @param gradeInfoId
	 * @param courseInfoId
	 * @param teachingPlanType
	 * @param currentPageNum
	 * @param pageSize
	 * @return
	 */
	@Deprecated
	public Pager<TeachingPlan> getAllSharedPaperList(String userInfoId, String termNo, String gradeInfoId, String courseInfoId, Integer teachingPlanType, int currentPageNum, int pageSize) {
		String querySql = "Select tp from TeachingPlan tp, UserInfo ui " +
				"where tp.termInfo.termNo = ? and tp.teachingPlanType = ? and tp.gradeInfo.gradeInfoId = ? and tp.courseInfo.courseInfoId = ? " +
				"and (" +
					// 个人发布的教案
						"(tp.userInfo.userInfoId = ? and tp.userInfo.userInfoId = ui.userInfoId)" +
					// 学科共享
					" or (tp.shareType = 2 and ui.schoolInfo.schoolInfoId = tp.schoolInfo.schoolInfoId and ui.userInfoId = ? and tp.courseInfo.courseInfoId in " +
							"(Select tc.courseInfo.courseInfoId from TeachingCourse tc where ui.userInfoId = tc.userInfo.userInfoId and tc.termInfo.termNo = tp.termInfo.termNo and tc.userInfo.userInfoId = ?))" +
					// 学校共享
					" or (tp.shareType = 3 and ui.schoolInfo.schoolInfoId = tp.schoolInfo.schoolInfoId and ui.userInfoId = ?)" +
					// 中心共享
					" or (tp.shareType = 4 and tp.userInfo.userInfoId = ui.userInfoId)" +
					// 指定人员共享
					" or (tp.shareType = 5 and tp.teachingPlanId in " +
							"(Select tpsi.teachingPlan.teachingPlanId from TeachingPlanShareInfo tpsi where ui.userInfoId = tpsi.userInfo.userInfoId and tpsi.userInfo.userInfoId = ?)" +
					")" +
				") order by tp.addDate desc";
		String countSql = "Select count(tp.teachingPlanId) from TeachingPlan tp, UserInfo ui " +
				"where tp.termInfo.termNo = ? and tp.teachingPlanType = ? and tp.gradeInfo.gradeInfoId = ? and tp.courseInfo.courseInfoId = ? " +
				"and (" +
					// 个人发布的教案
						"(tp.userInfo.userInfoId = ? and tp.userInfo.userInfoId = ui.userInfoId)" +
					// 学科共享
					" or (tp.shareType = 2 and ui.schoolInfo.schoolInfoId = tp.schoolInfo.schoolInfoId and ui.userInfoId = ? and tp.courseInfo.courseInfoId in " +
							"(Select tc.courseInfo.courseInfoId from TeachingCourse tc where ui.userInfoId = tc.userInfo.userInfoId and tc.termInfo.termNo = tp.termInfo.termNo and tc.userInfo.userInfoId = ?))" +
					// 学校共享
					" or (tp.shareType = 3 and ui.schoolInfo.schoolInfoId = tp.schoolInfo.schoolInfoId and ui.userInfoId = ?)" +
					// 中心共享
					" or (tp.shareType = 4 and tp.userInfo.userInfoId = ui.userInfoId)" +
					// 指定人员共享
					" or (tp.shareType = 5 and tp.teachingPlanId in " +
							"(Select tpsi.teachingPlan.teachingPlanId from TeachingPlanShareInfo tpsi where ui.userInfoId = tpsi.userInfo.userInfoId and tpsi.userInfo.userInfoId = ?)" +
					")" +
				")";
		Object[] values = new Object[]{termNo, teachingPlanType, gradeInfoId, courseInfoId, userInfoId, userInfoId, userInfoId, userInfoId, userInfoId};
		return this.teachingPlanDao.getPageListBySql(querySql, values, countSql, currentPageNum, pageSize);
	}

系统上线后,由于数据量越来越多,导致查询速度越慢,显示出一个列表的数据,用了十几秒到二十几秒不等。

这种速度肯定不行的啦,客户要求想办法进行优化。

由于要满足客户的这个需求,查询方法肯定是要满足这几种情况的啦,因此只能把HQL语句变化成存储过程,而在改造成存储过程的时候,也是经过了几次测试,到最终定下来,显示出一个列表的数据,大概用了2~3秒。优化过程如下:

刚开始只是简单的将HQL语句变成SQL语句,但是发现速度还是不够快,而且当数据量越来越大的时候,在那么大的数据量的情况下执行多表联立,速度绝对是个问题。

因此考虑使用临时表,将大表缩小,再进行联立。基本思想是:根据某些已知的条件,尽量将本次需要用到的数据取出,并放到临时表中,最后再将这些小表进行联立查询,用时大概2~3秒。存储过程如下:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		gevin
-- Create date: 20120226
-- Description:	集体备课
-- =============================================
ALTER PROCEDURE [dbo].[pro_teachingPlan_showList]
	@termNo char(5),
	@teachingPlanType tinyint,
	@gradeInfoId nvarchar(32),
	@courseInfoId nvarchar(32),
	@userInfoId nvarchar(32)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- 创建临时表
	create table #TeachingPlanTemp(
		teachingPlanId nvarchar(32),
		teachingPlanType tinyint,
		teachingPlanName nvarchar(100),
		userInfoId nvarchar(32),
		schoolInfoId nvarchar(32),
		termNo nvarchar(5),
		courseInfoId nvarchar(32),
		gradeInfoId nvarchar(32),
		isOpenComment tinyint,
		isFinalText tinyint,
		isExecuteFinalText tinyint,
		isCompleteFinalText tinyint,
		isSendInvite tinyint,
		originalTeachingPlanId nvarchar(32),
		shareType tinyint,
		addDate datetime,
		trueName nvarchar(20),
		schoolName nvarchar(50),
		courseName nvarchar(50)
	);

	-- 创建索引
	create index i_userInfoId on #TeachingPlanTemp(userInfoId);
	create index i_userInfoId_gradeInfoId_courseInfoId on #TeachingPlanTemp(userInfoId, gradeInfoId, courseInfoId);

	-- 插件本学期 集体备课教案
	insert into #TeachingPlanTemp
		Select teachingPlanId, teachingPlanType, teachingPlanName, tp.userInfoId, tp.schoolInfoId, termNo, tp.courseInfoId, gradeInfoId,
			 isOpenComment, isFinalText, isExecuteFinalText, isCompleteFinalText, isSendInvite, originalTeachingPlanId, shareType, tp.addDate,
			 ui.trueName, si.schoolName, ci.courseName
		From TeachingPlan tp, UserInfo ui, SchoolInfo si, CourseInfo ci
		Where
			tp.termNo=@termNo and tp.teachingPlanType=@teachingPlanType
			and tp.userInfoId = ui.userInfoId and tp.schoolInfoId = si.schoolInfoId and tp.courseInfoId = ci.courseInfoId;

	-- 创建课程临时表,用于存储本次需要用到的数据
	create table #TeachingCourseTemp(
		courseInfoId varchar(32),
		userInfoId varchar(32)
	)
	-- 插入本人的数据
	insert into #TeachingCourseTemp
		Select courseInfoId, userInfoId
		from TeachingCourse
		where userInfoId = @userInfoId	

	-- 创建课程临时表,用于存储本次需要用到的数据
	create table #TeachingPlanShareInfoTemp(
		teachingPlanId varchar(32),
		userInfoId varchar(32)
	)
	-- 插入本人的数据
	insert into #TeachingPlanShareInfoTemp
		Select teachingPlanId, userInfoId
		from TeachingPlanShareInfo tpsi
		where userInfoId = @userInfoId;

	-- 查询数据
    if @gradeInfoId='all' and @courseInfoId='all'
        Select tp.*, (Select count(*) from TeachingPlanCommentInfo tpci where tpci.teachingPlanId=tp.teachingPlanId) as commentNum
        from #TeachingPlanTemp tp, UserInfo ui
        where
            --个人发布的教案
             ((tp.userInfoId = @userInfoId and tp.userInfoId = ui.userInfoId)
                    --学科共享
                    or (tp.shareType = 2 and ui.schoolInfoId = tp.schoolInfoId and ui.userInfoId = @userInfoId and tp.courseInfoId in
                    (Select tc.courseInfoId from #TeachingCourseTemp tc where ui.userInfoId = tc.userInfoId))
                    --学校共享
                    or (tp.shareType = 3 and ui.schoolInfoId = tp.schoolInfoId and ui.userInfoId = @userInfoId)
                    --中心共享
                    or (tp.shareType = 4 and tp.userInfoId = ui.userInfoId)
                    --指定人员共享
                    or (tp.shareType = 5 and tp.teachingPlanId in
                        (Select tpsi.teachingPlanId from #TeachingPlanShareInfoTemp tpsi where ui.userInfoId = tpsi.userInfoId)
                        )
            ) order by tp.addDate desc
    else
        Select tp.*, (Select count(*) from TeachingPlanCommentInfo tpci where tpci.teachingPlanId=tp.teachingPlanId) as commentNum
        from #TeachingPlanTemp tp, UserInfo ui, GradeInfo gi, CourseInfo ci
        where tp.gradeInfoId = @gradeInfoId and tp.gradeInfoId = gi.gradeInfoId and tp.courseInfoId = @courseInfoId and tp.courseInfoId=ci.courseInfoId
            --个人发布的教案
            and ((tp.userInfoId = @userInfoId and tp.userInfoId = ui.userInfoId)
                    --学科共享
                    or (tp.shareType = 2 and ui.schoolInfoId = tp.schoolInfoId and ui.userInfoId = @userInfoId and tp.courseInfoId in
                    (Select tc.courseInfoId from #TeachingCourseTemp tc where ui.userInfoId = tc.userInfoId))
                    --学校共享
                    or (tp.shareType = 3 and ui.schoolInfoId = tp.schoolInfoId and ui.userInfoId = @userInfoId)
                    --中心共享
                    or (tp.shareType = 4 and tp.userInfoId = ui.userInfoId)
                    --指定人员共享
                    or (tp.shareType = 5 and tp.teachingPlanId in
                        (Select tpsi.teachingPlanId from #TeachingPlanShareInfoTemp tpsi where ui.userInfoId = tpsi.userInfoId)
                        )
            ) order by tp.addDate desc

    --删除临时表
	Truncate table #TeachingPlanTemp;
	drop table #TeachingPlanTemp;

	Truncate table #TeachingCourseTemp;
	drop table #TeachingCourseTemp;

	Truncate table #TeachingPlanShareInfoTemp;
	drop table #TeachingPlanShareInfoTemp;

END

因为经过了几次的修改,以为已经不能再优化了,也就没有去管了。(其实上面多表关联的时候,不应该使用全连接,可能使用左连接或右连接,说不定能更快)

就这样,用了一个星期,2~3秒的时间,用户表示还是可以接受的。

不过今天,新的问题又出现了,用户在浏览到该模块的时候,速度变得非常的慢,有时候打开页面时,出现一片空白,监控了一下Tomcat的使用情况,当时设置了最大并发1000个进程,已经使用了562个,而且不会自己消失。查看了一下MSSQL的连接情况,发现也有五六百个进程在那里,而大部分进程都被阻塞,而且是C被B阻塞,B又被A阻塞,重启Tomcat后就没问题了。

因此,客户又提要求了,要求尽快解决这个问题。

虽然暂时还不敢确定是不是读取数据的代码有问题,但从Tomcat后台监控得到的访问日志可以看到,其中有两三百个进程都是停留在这个页面上(即上述提到的多表联立)。

所以只能想办法继续优化该程序了,看看能不能将时间从2~3秒缩得更短,之前觉得不能再优化,主要是想到了,这么多个表,这么多种情况,联立起来查询,速度就是这样了。

下午突然想到,能不能不通过多表联立来获取数据呢,能不能将上述几种情况独立开来呢?再新建一个临时表,然后分别将每种情况查询出来的数据放到该临时表内,再通过日期来进行排序,方便前台分页显示。还有,这种方法,速度会不会更快呢,还是会比原来更慢。

好吧,经过测试,发现这种方法比多表联立,速度快了很多,现在显示该列表,只用了一秒,算是优化又进一步了。

优化后的存储过程如下:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		gevin
-- Create date: 20120314
-- Description:	集体备课
-- =============================================
ALTER PROCEDURE [dbo].[pro_teachingPlan_showList_v2]
	@termNo char(5),
	@teachingPlanType tinyint,
	@gradeInfoId nvarchar(32),
	@courseInfoId nvarchar(32),
	@userInfoId nvarchar(32)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- 创建临时表
	create table #TeachingPlanTemp(
		teachingPlanId nvarchar(32),
		teachingPlanType tinyint,
		teachingPlanName nvarchar(100),
		userInfoId nvarchar(32),
		schoolInfoId nvarchar(32),
		termNo nvarchar(5),
		courseInfoId nvarchar(32),
		gradeInfoId nvarchar(32),
		isOpenComment tinyint,
		isFinalText tinyint,
		isExecuteFinalText tinyint,
		isCompleteFinalText tinyint,
		isSendInvite tinyint,
		originalTeachingPlanId nvarchar(32),
		shareType tinyint,
		addDate datetime,
		trueName nvarchar(20),
		schoolName nvarchar(50),
		courseName nvarchar(50)
	);

	-- 创建索引
	create index i_userInfoId on #TeachingPlanTemp(userInfoId);
	create index i_userInfoId_gradeInfoId_courseInfoId on #TeachingPlanTemp(userInfoId, gradeInfoId, courseInfoId);

	-- 插件本学期 集体备课教案
	insert into #TeachingPlanTemp
		Select teachingPlanId, teachingPlanType, teachingPlanName, tp.userInfoId, tp.schoolInfoId, termNo, tp.courseInfoId, gradeInfoId,
			 isOpenComment, isFinalText, isExecuteFinalText, isCompleteFinalText, isSendInvite, originalTeachingPlanId, shareType, tp.addDate,
			 ui.trueName, si.schoolName, ci.courseName
		From TeachingPlan tp, UserInfo ui, SchoolInfo si, CourseInfo ci
		Where
			tp.termNo=@termNo and tp.teachingPlanType=@teachingPlanType
			and tp.userInfoId = ui.userInfoId and tp.schoolInfoId = si.schoolInfoId and tp.courseInfoId = ci.courseInfoId;

	-- 创建最终存放查询结果的临时表
	create table #TeachingPlanTempOk(
		teachingPlanId nvarchar(32),
		teachingPlanType tinyint,
		teachingPlanName nvarchar(100),
		userInfoId nvarchar(32),
		schoolInfoId nvarchar(32),
		termNo nvarchar(5),
		courseInfoId nvarchar(32),
		gradeInfoId nvarchar(32),
		isOpenComment tinyint,
		isFinalText tinyint,
		isExecuteFinalText tinyint,
		isCompleteFinalText tinyint,
		isSendInvite tinyint,
		originalTeachingPlanId nvarchar(32),
		shareType tinyint,
		addDate datetime,
		trueName nvarchar(20),
		schoolName nvarchar(50),
		courseName nvarchar(50)
	);

	-- 创建索引
	create index i_userInfoId on #TeachingPlanTempOk(userInfoId);
	create index i_userInfoId_addDate on #TeachingPlanTempOk(addDate desc);
	create index i_userInfoId_gradeInfoId_courseInfoId on #TeachingPlanTempOk(userInfoId, gradeInfoId, courseInfoId);

	-- 创建课程临时表,用于存储本次需要用到的数据
	create table #TeachingCourseTemp(
		courseInfoId varchar(32),
		userInfoId varchar(32)
	);

	-- 插入本人的数据
	insert into #TeachingCourseTemp
		Select courseInfoId, userInfoId
		from TeachingCourse
		where userInfoId = @userInfoId;

	-- 创建课程临时表,用于存储本次需要用到的数据
	create table #TeachingPlanShareInfoTemp(
		teachingPlanId varchar(32),
		userInfoId varchar(32)
	);
	-- 插入本人的数据
	insert into #TeachingPlanShareInfoTemp
		Select teachingPlanId, userInfoId
		from TeachingPlanShareInfo tpsi
		where userInfoId = @userInfoId;

	-- 查询数据
    if @gradeInfoId='all' and @courseInfoId='all'
        begin
        --个人发布的教案
        insert into #TeachingPlanTempOk
            Select tp.*
            From #TeachingPlanTemp tp, UserInfo ui
            Where
                tp.userInfoId = @userInfoId and tp.userInfoId = ui.userInfoId;

        --学科共享
        insert into #TeachingPlanTempOk
            Select tp.*
            From #TeachingPlanTemp tp, UserInfo ui
            Where tp.shareType = 2 and ui.schoolInfoId = tp.schoolInfoId and ui.userInfoId = @userInfoId and tp.courseInfoId in
                    (Select tc.courseInfoId from #TeachingCourseTemp tc where ui.userInfoId = tc.userInfoId);

        --学校共享
        insert into #TeachingPlanTempOk
            Select tp.*
            From #TeachingPlanTemp tp, UserInfo ui
            Where tp.shareType = 3 and ui.schoolInfoId = tp.schoolInfoId and ui.userInfoId = @userInfoId;

        --中心共享
        insert into #TeachingPlanTempOk
            Select tp.*
            From #TeachingPlanTemp tp, UserInfo ui
            Where tp.shareType = 4 and tp.userInfoId = ui.userInfoId;

        --指定人员共享
        insert into #TeachingPlanTempOk
            Select tp.*
            From #TeachingPlanTemp tp, UserInfo ui
            Where tp.shareType = 5 and tp.teachingPlanId in
                    (Select tpsi.teachingPlanId from #TeachingPlanShareInfoTemp tpsi where ui.userInfoId = tpsi.userInfoId);
        end

    else
        begin
        --个人发布的教案
        insert into #TeachingPlanTempOk
            Select tp.*
            From #TeachingPlanTemp tp, UserInfo ui, GradeInfo gi, CourseInfo ci
            Where tp.gradeInfoId = @gradeInfoId and tp.gradeInfoId = gi.gradeInfoId and tp.courseInfoId = @courseInfoId and tp.courseInfoId=ci.courseInfoId
             and tp.userInfoId = @userInfoId and tp.userInfoId = ui.userInfoId;

        --学科共享
        insert into #TeachingPlanTempOk
            Select tp.*
            From #TeachingPlanTemp tp, UserInfo ui, GradeInfo gi, CourseInfo ci
            Where tp.gradeInfoId = @gradeInfoId and tp.gradeInfoId = gi.gradeInfoId and tp.courseInfoId = @courseInfoId and tp.courseInfoId=ci.courseInfoId
             and  tp.shareType = 2 and ui.schoolInfoId = tp.schoolInfoId and ui.userInfoId = @userInfoId and tp.courseInfoId in
                    (Select tc.courseInfoId from #TeachingCourseTemp tc where ui.userInfoId = tc.userInfoId);

        --学校共享
        insert into #TeachingPlanTempOk
            Select tp.*
            From #TeachingPlanTemp tp, UserInfo ui, GradeInfo gi, CourseInfo ci
            Where tp.gradeInfoId = @gradeInfoId and tp.gradeInfoId = gi.gradeInfoId and tp.courseInfoId = @courseInfoId and tp.courseInfoId=ci.courseInfoId
             and  tp.shareType = 3 and ui.schoolInfoId = tp.schoolInfoId and ui.userInfoId = @userInfoId;

        --中心共享
        insert into #TeachingPlanTempOk
            Select tp.*
            From #TeachingPlanTemp tp, UserInfo ui, GradeInfo gi, CourseInfo ci
            Where tp.gradeInfoId = @gradeInfoId and tp.gradeInfoId = gi.gradeInfoId and tp.courseInfoId = @courseInfoId and tp.courseInfoId=ci.courseInfoId
             and  tp.shareType = 4 and tp.userInfoId = ui.userInfoId;

        --指定人员共享
        insert into #TeachingPlanTempOk
            Select tp.*
            From #TeachingPlanTemp tp, UserInfo ui, GradeInfo gi, CourseInfo ci
            Where tp.gradeInfoId = @gradeInfoId and tp.gradeInfoId = gi.gradeInfoId and tp.courseInfoId = @courseInfoId and tp.courseInfoId=ci.courseInfoId
             and  tp.shareType = 5 and tp.teachingPlanId in
                    (Select tpsi.teachingPlanId from #TeachingPlanShareInfoTemp tpsi where ui.userInfoId = tpsi.userInfoId);
        end

	Select tp.*, (Select count(*) from TeachingPlanCommentInfo tpci where tpci.teachingPlanId=tp.teachingPlanId) as commentNum
	from #TeachingPlanTempOk tp
	order by tp.addDate desc;

    --删除临时表
	Truncate table #TeachingPlanTemp;
	drop table #TeachingPlanTemp;

	Truncate table #TeachingPlanTempOk;
	drop table #TeachingPlanTempOk;

	Truncate table #TeachingCourseTemp;
	drop table #TeachingCourseTemp;

	Truncate table #TeachingPlanShareInfoTemp;
	drop table #TeachingPlanShareInfoTemp;

END

经过这两次优化,发现用存储过程的确比用程序来处理快得很多,特别是在多表联立,或者比较复杂的查询下,建议使用存储过程。而在使用存储过程时,也需要掌握一些优化技巧,当数据量比较大的时候,尽量使用临时表,将需要的数据取出,然后再对该临时表进行操作,同时要注意使用索引,加快数据查询!

虽然不知道能不能解决上面的问题,但能将速度缩短成1秒,已经算是取得很大的成功了,接下来只能再观察几天了,看看还会不会出现这种现象。

 

本文固定链接: http://gevin.me/365.html | Gevin's Blog

该日志由 admin 于2012年03月14日发表在 Database, Java 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: 记录一次数据库优化的过程 | Gevin's Blog
关键字: ,

记录一次数据库优化的过程:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter