注:本文仅针对 DiscuzNT3.0, sqlserver 2000版本,其他版本请勿对号入座.
上次对DiscuzNT的看帖做了性能优化,这次主要针对发帖和回帖做下优化,本文还是以优化数据库为主,C#的优化以后有时间另外记录。
看帖,发帖,回帖 都是一个论坛的基本操作,如果性能不好导致这些操作变慢,论坛的效果可想而知了,那我们来看看DiscuzNT发帖和回帖都有些什么操作,性能如何。
做事情总要有个先后,我给这次的优化工作也做了个步骤:(有图有真相)
1)看代码,查到发帖回帖调用的存储过程;
发帖调用了两个过程,代码如下:
{
DbParameter[] parms = {
DbHelper.MakeInParam( " @fid " , (DbType)SqlDbType.SmallInt, 2 , topicInfo.Fid),
DbHelper.MakeInParam( " @iconid " , (DbType)SqlDbType.SmallInt, 2 , topicInfo.Iconid),
DbHelper.MakeInParam( " @title " , (DbType)SqlDbType.NChar, 60 , topicInfo.Title),
DbHelper.MakeInParam( " @typeid " , (DbType)SqlDbType.SmallInt, 2 , topicInfo.Typeid),
DbHelper.MakeInParam( " @readperm " , (DbType)SqlDbType.Int, 4 , topicInfo.Readperm),
DbHelper.MakeInParam( " @price " , (DbType)SqlDbType.SmallInt, 2 , topicInfo.Price),
DbHelper.MakeInParam( " @poster " , (DbType)SqlDbType.NChar, 15 , topicInfo.Poster),
DbHelper.MakeInParam( " @posterid " , (DbType)SqlDbType.Int, 4 , topicInfo.Posterid),
DbHelper.MakeInParam( " @postdatetime " , (DbType)SqlDbType.SmallDateTime, 4 , DateTime.Parse(topicInfo.Postdatetime)),
DbHelper.MakeInParam( " @lastpost " , (DbType)SqlDbType.VarChar, 0 , topicInfo.Lastpost),
DbHelper.MakeInParam( " @lastpostid " , (DbType)SqlDbType.Int, 4 , topicInfo.Lastpostid),
DbHelper.MakeInParam( " @lastposter " , (DbType)SqlDbType.NChar, 15 , topicInfo.Lastposter),
DbHelper.MakeInParam( " @views " , (DbType)SqlDbType.Int, 4 , topicInfo.Views),
DbHelper.MakeInParam( " @replies " , (DbType)SqlDbType.Int, 4 , topicInfo.Replies),
DbHelper.MakeInParam( " @displayorder " , (DbType)SqlDbType.Int, 4 , topicInfo.Displayorder),
DbHelper.MakeInParam( " @highlight " , (DbType)SqlDbType.VarChar, 500 , topicInfo.Highlight),
DbHelper.MakeInParam( " @digest " , (DbType)SqlDbType.Int, 4 , topicInfo.Digest),
DbHelper.MakeInParam( " @rate " , (DbType)SqlDbType.Int, 4 , topicInfo.Rate),
DbHelper.MakeInParam( " @hide " , (DbType)SqlDbType.Int, 4 , topicInfo.Hide),
DbHelper.MakeInParam( " @attachment " , (DbType)SqlDbType.Int, 4 , topicInfo.Attachment),
DbHelper.MakeInParam( " @moderated " , (DbType)SqlDbType.Int, 4 , topicInfo.Moderated),
DbHelper.MakeInParam( " @closed " , (DbType)SqlDbType.Int, 4 , topicInfo.Closed),
DbHelper.MakeInParam( " @magic " , (DbType)SqlDbType.Int, 4 , topicInfo.Magic),
DbHelper.MakeInParam( " @special " , (DbType)SqlDbType.TinyInt, 1 , topicInfo.Special),
DbHelper.MakeInParam( " @attention " , (DbType)SqlDbType.Int, 4 , topicInfo.Attention)
};
return TypeConverter.ObjectToInt(DbHelper.ExecuteDataset(CommandType.StoredProcedure,
string .Format( " {0}createtopic " , BaseConfigs.GetTablePrefix),
parms).Tables[ 0 ].Rows[ 0 ][ 0 ], - 1 );
}
/// </summary>
/// <param name="postinfo"> 帖子信息类 </param>
/// <returns> 返回帖子id </returns>
public int CreatePost(PostInfo postInfo, string postTableId)
{
DbParameter[] parms = {
DbHelper.MakeInParam( " @fid " ,(DbType)SqlDbType.SmallInt, 2 ,postInfo.Fid),
DbHelper.MakeInParam( " @tid " ,(DbType)SqlDbType.Int, 4 ,postInfo.Tid),
DbHelper.MakeInParam( " @parentid " ,(DbType)SqlDbType.Int, 4 ,postInfo.Parentid),
DbHelper.MakeInParam( " @layer " ,(DbType)SqlDbType.Int, 4 ,postInfo.Layer),
DbHelper.MakeInParam( " @poster " ,(DbType)SqlDbType.VarChar, 15 ,postInfo.Poster),
DbHelper.MakeInParam( " @posterid " ,(DbType)SqlDbType.Int, 4 ,postInfo.Posterid),
DbHelper.MakeInParam( " @title " ,(DbType)SqlDbType.NVarChar, 60 ,postInfo.Title),
DbHelper.MakeInParam( " @topictitle " ,(DbType)SqlDbType.NVarChar, 60 ,postInfo.Topictitle),
DbHelper.MakeInParam( " @postdatetime " ,(DbType)SqlDbType.SmallDateTime, 4 , DateTime.Parse(postInfo.Postdatetime)),
DbHelper.MakeInParam( " @message " ,(DbType)SqlDbType.NText, 0 ,postInfo.Message),
DbHelper.MakeInParam( " @ip " ,(DbType)SqlDbType.VarChar, 15 ,postInfo.Ip),
DbHelper.MakeInParam( " @lastedit " ,(DbType)SqlDbType.NVarChar, 50 ,postInfo.Lastedit),
DbHelper.MakeInParam( " @invisible " ,(DbType)SqlDbType.Int, 4 ,postInfo.Invisible),
DbHelper.MakeInParam( " @usesig " ,(DbType)SqlDbType.Int, 4 ,postInfo.Usesig),
DbHelper.MakeInParam( " @htmlon " ,(DbType)SqlDbType.Int, 4 ,postInfo.Htmlon),
DbHelper.MakeInParam( " @smileyoff " ,(DbType)SqlDbType.Int, 4 ,postInfo.Smileyoff),
DbHelper.MakeInParam( " @bbcodeoff " ,(DbType)SqlDbType.Int, 4 ,postInfo.Bbcodeoff),
DbHelper.MakeInParam( " @parseurloff " ,(DbType)SqlDbType.Int, 4 ,postInfo.Parseurloff),
DbHelper.MakeInParam( " @attachment " ,(DbType)SqlDbType.Int, 4 ,postInfo.Attachment),
DbHelper.MakeInParam( " @rate " ,(DbType)SqlDbType.SmallInt, 2 ,postInfo.Rate),
DbHelper.MakeInParam( " @ratetimes " ,(DbType)SqlDbType.Int, 4 ,postInfo.Ratetimes)
};
return TypeConverter.ObjectToInt(DbHelper.ExecuteScalar(CommandType.StoredProcedure,
string .Format( " {0}createpost{1} " , BaseConfigs.GetTablePrefix, postTableId),
parms), - 1 );
}
有两个主要方法,1个是CreateTopic(),对应调用dnt_createtopic存储过程,这个是把主贴的基本信息保存到dnt_topics表,其中不包含帖子内容;
1个是CreatePost()对应调用dnt_createposts{0},{0}是分表名称,是把帖子的所有信息保存到dnt_posts{0}表,此表帖子的详细信息(包括回复也在此表);
论坛展示帖子列表的时候查询的是dnt_topics表的信息,帖子里面展示各楼层信息的时候查询的是dnt_posts{0}表。
2)发一个帖或者回复一个帖子,看看存储过程的性能 ;
我们用profiler跟踪一下这两个过程的性能如何,看图:
从上图看到 dnt_createposts3这个过程的reads比较高,我们看看它是怎么写的,有没有优化的可能。
3)查看存储过程的sql写法, 如果发现问题,指出问题;
dnt_createposts3这个过程的脚本如下:
@fid int ,
@tid int ,
@parentid int ,
@layer int ,
@poster varchar ( 20 ),
@posterid int ,
@title nvarchar ( 60 ),
@topictitle nvarchar ( 60 ),
@postdatetime char ( 20 ),
@message ntext ,
@ip varchar ( 15 ),
@lastedit varchar ( 50 ),
@invisible int ,
@usesig int ,
@htmlon int ,
@smileyoff int ,
@bbcodeoff int ,
@parseurloff int ,
@attachment int ,
@rate int ,
@ratetimes int
AS
DEClARE @postid int
DELETE FROM [ dnt_postid ] WHERE DATEDIFF (n, postdatetime, GETDATE ()) > 5
INSERT INTO [ dnt_postid ] ( [ postdatetime ] ) VALUES ( GETDATE ())
SELECT @postid = SCOPE_IDENTITY ()
INSERT INTO [ dnt_posts3 ] ( [ pid ] , [ fid ] , [ tid ] , [ parentid ] , [ layer ] , [ poster ] ,
[ posterid ] , [ title ] , [ postdatetime ] , [ message ] , [ ip ] , [ lastedit ] ,
[ invisible ] , [ usesig ] , [ htmlon ] , [ smileyoff ] , [ bbcodeoff ] , [ parseurloff ] ,
[ attachment ] , [ rate ] , [ ratetimes ] )
VALUES ( @postid , @fid , @tid , @parentid , @layer , @poster , @posterid , @title ,
@postdatetime , @message , @ip , @lastedit , @invisible , @usesig , @htmlon , @smileyoff ,
@bbcodeoff , @parseurloff , @attachment , @rate , @ratetimes )
IF @parentid = 0
BEGIN
UPDATE [ dnt_posts3 ] SET [ parentid ] = @postid WHERE [ pid ] = @postid
END
IF @@ERROR = 0
BEGIN
IF @invisible = 0
BEGIN
UPDATE [ dnt_statistics ] SET [ totalpost ] = [ totalpost ] + 1
DECLARE @fidlist AS VARCHAR ( 1000 )
DECLARE @strsql AS VARCHAR ( 4000 )
SET @fidlist = '' ;
SELECT @fidlist = ISNULL ( [ parentidlist ] , '' ) FROM [ dnt_forums ] WHERE [ fid ] = @fid
IF RTRIM ( @fidlist ) <> ''
BEGIN
SET @fidlist = RTRIM ( @fidlist ) + ' , ' + CAST ( @fid AS VARCHAR ( 10 ))
END
ELSE
BEGIN
SET @fidlist = CAST ( @fid AS VARCHAR ( 10 ))
END
-- 性能隐患,此sql语句进行了列运算
UPDATE [ dnt_forums ] SET [ posts ] = [ posts ] + 1 ,
[ todayposts ] = CASE
WHEN DATEDIFF ( day , [ lastpost ] , GETDATE ()) = 0
THEN [ todayposts ] * 1 + 1
ELSE 1
END ,
[ lasttid ] = @tid ,
[ lasttitle ] = @topictitle ,
[ lastpost ] = @postdatetime ,
[ lastposter ] = @poster ,
[ lastposterid ] = @posterid
WHERE ( CHARINDEX ( ' , ' + RTRIM ( [ fid ] ) + ' , ' , ' , '
+ ( SELECT @fidlist AS [ fid ] ) + ' , ' ) > 0 )
UPDATE [ dnt_users ] SET
[ lastpost ] = @postdatetime ,
[ lastpostid ] = @postid ,
[ lastposttitle ] = @title ,
[ posts ] = [ posts ] + 1 ,
[ lastactivity ] = GETDATE ()
WHERE [ uid ] = @posterid
IF @layer <= 0
BEGIN
UPDATE [ dnt_topics ] SET [ replies ] = 0 , [ lastposter ] = @poster ,
[ lastpost ] = @postdatetime , [ lastposterid ] = @posterid
WHERE [ tid ] = @tid
END
ELSE
BEGIN
UPDATE [ dnt_topics ] SET [ replies ] = [ replies ] + 1 , [ lastposter ] = @poster ,
[ lastpost ] = @postdatetime , [ lastposterid ] = @posterid
WHERE [ tid ] = @tid
END
END
UPDATE [ dnt_topics ] SET [ lastpostid ] = @postid WHERE [ tid ] = @tid
IF @posterid <> - 1
BEGIN
INSERT [ dnt_myposts ] ( [ uid ] , [ tid ] , [ pid ] , [ dateline ] )
VALUES ( @posterid , @tid , @postid , @postdatetime )
END
END
SELECT @postid AS postid
GO
这个过程比较长,不过存在性能隐患的脚本上面已经注明,就是update forums 这句,dnt_forums 数据量越大(我们现在有3000个论坛), 更新这个表所花的时间越多,因为它进行了列运算 WHERE (CHARINDEX(',' + RTRIM([fid]) + ',', ',' + (SELECT @fidlist AS [fid]) + ',') > 0) ,用不到索引。
4)优化,测试优化的结果。
@fid int ,
@tid int ,
@parentid int ,
@layer int ,
@poster varchar ( 20 ),
@posterid int ,
@title nvarchar ( 60 ),
@topictitle nvarchar ( 60 ),
@postdatetime char ( 20 ),
@message ntext ,
@ip varchar ( 15 ),
@lastedit varchar ( 50 ),
@invisible int ,
@usesig int ,
@htmlon int ,
@smileyoff int ,
@bbcodeoff int ,
@parseurloff int ,
@attachment int ,
@rate int ,
@ratetimes int
AS
declare @sql nvarchar ( 4000 )
DEClARE @postid int
DELETE FROM [ dnt_postid ] WHERE DATEDIFF (n, postdatetime, GETDATE ()) > 5
INSERT INTO [ dnt_postid ] ( [ postdatetime ] ) VALUES ( GETDATE ())
SELECT @postid = SCOPE_IDENTITY ()
SELECT @postid AS postid
INSERT INTO [ dnt_posts3 ] ( [ pid ] , [ fid ] , [ tid ] , [ parentid ] , [ layer ] , [ poster ] ,
[ posterid ] , [ title ] , [ postdatetime ] , [ message ] ,
[ ip ] , [ lastedit ] , [ invisible ] , [ usesig ] , [ htmlon ] ,
[ smileyoff ] , [ bbcodeoff ] , [ parseurloff ] , [ attachment ] , [ rate ] , [ ratetimes ] )
VALUES ( @postid , @fid , @tid , @parentid , @layer , @poster , @posterid , @title ,
@postdatetime , @message , @ip , @lastedit , @invisible ,
@usesig , @htmlon , @smileyoff , @bbcodeoff , @parseurloff , @attachment ,
@rate , @ratetimes )
IF @parentid = 0
BEGIN
UPDATE [ dnt_posts3 ] SET [ parentid ] = @postid WHERE [ pid ] = @postid
END
IF @@ERROR = 0
BEGIN
IF @invisible = 0
BEGIN
UPDATE [ dnt_statistics ] SET [ totalpost ] = [ totalpost ] + 1
DECLARE @fidlist AS VARCHAR ( 1000 )
DECLARE @strsql AS VARCHAR ( 4000 )
SET @fidlist = '' ;
SELECT @fidlist = ISNULL ( [ parentidlist ] , '' )
FROM [ dnt_forums ] WHERE [ fid ] = @fid
IF RTRIM ( @fidlist ) <> ''
BEGIN
SET @fidlist = RTRIM ( @fidlist ) + ' , ' + CAST ( @fid AS VARCHAR ( 10 ))
END
ELSE
BEGIN
SET @fidlist = CAST ( @fid AS VARCHAR ( 10 ))
END
-- 此处为优化后的sql语句,用动态sql语句,避免进行列运算,使sql用到索引
set @sql =
' UPDATE [dnt_forums] SET
[posts]=[posts] + 1,
[todayposts]=CASE
WHEN DATEDIFF(day, [lastpost], GETDATE())=0 THEN [todayposts]*1 + 1
ELSE 1
END,
[lasttid]=@tid,
[lasttitle]=@topictitle,
[lastpost]=@postdatetime,
[lastposter]=@poster,
[lastposterid]=@posterid
WHERE [fid] in ( ' + @fidlist + ' ) '
exec sp_executesql @sql ,N ' @tid int,@topictitle nvarchar(60),
@postdatetime datetime,@poster varchar(20),@posterid int ' ,
@tid , @topictitle , @postdatetime , @poster , @posterid
UPDATE [ dnt_users ] SET
[ lastpost ] = @postdatetime ,
[ lastpostid ] = @postid ,
[ lastposttitle ] = @title ,
[ posts ] = [ posts ] + 1 ,
[ lastactivity ] = GETDATE ()
WHERE [ uid ] = @posterid
IF @layer <= 0
BEGIN
UPDATE [ dnt_topics ] SET [ replies ] = 0 , [ lastposter ] = @poster ,
[ lastpost ] = @postdatetime , [ lastposterid ] = @posterid
WHERE [ tid ] = @tid
END
ELSE
BEGIN
UPDATE [ dnt_topics ] SET [ replies ] = [ replies ] + 1 , [ lastposter ] = @poster ,
[ lastpost ] = @postdatetime , [ lastposterid ] = @posterid
WHERE [ tid ] = @tid
END
END
UPDATE [ dnt_topics ] SET [ lastpostid ] = @postid WHERE [ tid ] = @tid
IF @posterid <> - 1
BEGIN
INSERT [ dnt_myposts ] ( [ uid ] , [ tid ] , [ pid ] , [ dateline ] )
VALUES ( @posterid , @tid , @postid , @postdatetime )
END
END
return @posterid
GO
这里改成了sql动态语句,where后面是这样写的 WHERE [fid] in (' + @fidlist + ')',这里用到了索引,有兴趣的朋友可以自己看看执行计划,优化后的效果如何呢,看图:
两图对比,差距还蛮大的,ok,发帖的优化到此结束。不过discuzNT的优化还远远没有结束。