优化你的DiscuzNT，让它跑起来（2）发帖回帖篇

时间：2011-05-15 来源：鸽子飞扬

上次对DiscuzNT的看帖做了性能优化，这次主要针对发帖和回帖做下优化，本文还是以优化数据库为主，C#的优化以后有时间另外记录。

看帖，发帖，回帖都是一个论坛的基本操作，如果性能不好导致这些操作变慢，论坛的效果可想而知了，那我们来看看DiscuzNT发帖和回帖都有些什么操作，性能如何。

做事情总要有个先后，我给这次的优化工作也做了个步骤：（有图有真相）

1）看代码，查到发帖回帖调用的存储过程；

发帖调用了两个过程，代码如下：

public int CreateTopic(TopicInfo topicInfo)
{
    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> /// 创建帖子
/// </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这个过程的脚本如下：

ALTER      PROCEDURE dnt_createpost3
@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

这个过程比较长，不过存在性能隐患的脚本上面已经注明，就是update forums 这句，dnt_forums 数据量越大（我们现在有3000个论坛）, 更新这个表所花的时间越多，因为它进行了列运算 WHERE (CHARINDEX(',' + RTRIM([fid]) + ',', ',' + (SELECT @fidlist AS [fid]) + ',') > 0) ，用不到索引。

4）优化，测试优化的结果。

优化的方法有多种，下面我给出一种优化的方案，使update dnt_forums 这个操作用到索引，优化后的过程如下： ALTER              PROCEDURE dnt_createpost3
@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的优化还远远没有结束。