优化你的DiscuzNT，让它跑起来（3）删帖篇

在前两篇已经对看帖，发帖回帖做了优化，做过优化之后论坛访问起来果然顺畅多了。随着论坛帖子数的不断增加，论坛出现了一个新的问题，管理员在删帖的时候经常报错，超时，删除失败。

由于有了之前的两次经验，现在找起问题来比之前要快多了，现在先找到删帖的存储过程 dnt_deletetopicbytidlist3，脚本如下：

ALTER   PROCEDURE [dnt_deletetopicbytidlist3]
@tidlist AS VARCHAR(2000),
@chanageposts AS BIT
AS

    DECLARE @postcount int
    DECLARE @topiccount int
    DECLARE @todaycount int
    DECLARE @sqlstr nvarchar(4000)
    DECLARE @fid varchar(2000)
    DECLARE @posterid varchar(200)
    DECLARE @tempFid int
    DECLARE @tempPosterid int
    DECLARE @tempLayer int
    DECLARE @temppostdatetime datetime

    DECLARE @tempfidlist AS VARCHAR(1000)

    SET @fid = ''
    SET @posterid = ''
    SET @postcount=0
    SET @topiccount=0
    SET @todaycount=0

    SET @tempfidlist = '';

    IF @tidlist<>''
    BEGIN
        -- 问题：列运算
        DECLARE cu_dnt_posts CURSOR FOR SELECT [fid],[posterid],[layer],[postdatetime]
            FROM [dnt_posts3]
            WHERE CHARINDEX(','+RTRIM([dnt_posts3].[tid])+',', ','+@tidlist+',') > 0

        OPEN cu_dnt_posts
        FETCH NEXT FROM cu_dnt_posts into @tempFid,@tempPosterid,@tempLayer,@temppostdatetime
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @postcount = @postcount + 1

            IF @tempLayer = 0
            BEGIN
                SET @topiccount = @topiccount + 1

            END

            IF DATEDIFF(d,@temppostdatetime,GETDATE()) = 0
            BEGIN
                SET @todaycount = @todaycount + 1
            END

            IF CHARINDEX(',' + LTRIM(STR(@tempFid)) + ',',@fid + ',') = 0
            BEGIN
                -- 把所有关联的论坛id保存到变量 @tempfidlist，后面会用到
                SELECT @tempfidlist = ISNULL([parentidlist],'')
                    FROM [dnt_forums] WHERE [fid] = @tempFid

                IF RTRIM(@tempfidlist)<>''
                BEGIN
                    SET @fid = RTRIM(@fid) + ',' +  RTRIM(@tempfidlist) + ',' + CAST(@tempFid AS VARCHAR(10))
                END
                ELSE
                BEGIN
                    SET @fid =RTRIM(@fid) + ',' +  CAST(@tempFid AS VARCHAR(10))
                END

            END

            IF @chanageposts = 1
            BEGIN
                -- 更新用户发帖数量
                UPDATE [dnt_users] SET [posts] = [posts] - 1 WHERE [uid] = @tempPosterid
            END

            FETCH NEXT FROM cu_dnt_posts into @tempFid,@tempPosterid,@tempLayer,@temppostdatetime

        END

        CLOSE cu_dnt_posts
        DEALLOCATE cu_dnt_posts

        IF LEN(@fid)>0
        BEGIN
            SET @fid = SUBSTRING(@fid,2,LEN(@fid)-1)

        IF @chanageposts = 1
        BEGIN

            UPDATE [dnt_statistics] SET [totaltopic]=[totaltopic] - @topiccount, [totalpost]=[totalpost] - @postcount

            -- 问题：列运算
            UPDATE [dnt_forums]
                SET [posts]=[posts] - @postcount,
                [topics]=[topics] - @topiccount,
                [todayposts] = [todayposts] - @todaycount
                WHERE CHARINDEX(','+RTRIM([fid])+',', ','+@fid+',') > 0

        END

        -- 问题：列运算
        DELETE FROM [dnt_favorites] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0 AND [typeid]=0

        -- 问题：列运算
        DELETE FROM [dnt_polls] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0

        -- 问题：列运算
        DELETE FROM [dnt_posts3] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0

        -- 问题：列运算
        DELETE FROM [dnt_mytopics] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0

    END

    -- 问题：列运算
    DELETE FROM [dnt_topics] WHERE CHARINDEX((','+RTRIM([closed])+','), ','+@tidlist+',') > 0
        OR CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0

    -- 问题：列运算
    UPDATE [dnt_tags] SET [count]=[count]-1, [fcount]=[fcount]-1
        WHERE [tagid] IN (SELECT [tagid] FROM [dnt_topictags]
            WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0 )

    -- 问题：列运算
    DELETE FROM [dnt_topictags] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0

    -- 问题：列运算
    DELETE FROM [dnt_topictagcaches] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0
        OR CHARINDEX((','+RTRIM([linktid])+','), ','+@tidlist+',') > 0

end

删帖的操作流程是这样的：

1）传入@tidlist, 用逗号把帖子id隔开，比如“1,2,3,4,5” 这样的格式；

2）用游标 cu_dnt_posts 循环读取 dnt_posts3 表（此处用到了列运算，已标出）；

3）找到该贴子id关联的所有论坛id，放到@tempfidlist变量，后面会用到这个变量，这个语句

SELECT @tempfidlist = ISNULL([parentidlist],'') FROM [dnt_forums] WHERE [fid] = @tempFid

同时更新用户的发帖数量，这一句 UPDATE [dnt_users] SET [posts] = [posts] - 1 WHERE [uid] = @tempPosterid

4）关闭游标

5）更新统计信息，这句 UPDATE [dnt_statistics] SET [totaltopic]=[totaltopic] - @topiccount, [totalpost]=[totalpost] - @postcount

6）更新论坛发帖数量，这句 UPDATE [dnt_forums]

SET [posts]=[posts] - @postcount,

[topics]=[topics] - @topiccount,

[todayposts] = [todayposts] - @todaycount

WHERE CHARINDEX(','+RTRIM([fid])+',', ','+@fid+',') > 0

7）删除收藏夹，删除回复表，删除用户发帖信息表等等

8）删除主贴表，删除标签表等等

这个过程代码量很大，逻辑稍微的有些复杂，更糟糕的是里面大半的操作都做了列运算，charindex()这个函数就是，oh，my god，我们论坛目前的主贴表是 320万，其他回复表，最少的也有400万，最多有1000万，我可以分负责任的说，只要一运行这个过程，就死定了。不信？我们还是请profiler来帮我们看看吧（当然这是在测试环境，生产环境请勿尝试，否则后果自负）。

后台管理删帖界面：

点击提交，等了N久，得到下图（超时了）：

看看profiler跟踪的结果，如下图：

看看这几个性能参数 cpu 27360，reads 71290，duration 30016 ，看到这里应该了然了，不超时是不可能的。

优化的方案有很多种，我这里只提供一种，修改他的存储过程，不修改他的业务逻辑，我的原理是，用in代替charindex()函数，到目前为止dnt所有的数据库性能问题都和charindex()这个函数有关，而我也都是改成in，这次比之前稍微有难度，因为他涉及到了游标,修改要谨慎，改过之后的过程脚本如下：

ALTER      PROCEDURE [dnt_deletetopicbytidlist3]
@tidlist AS VARCHAR(2000),
@chanageposts AS BIT
AS

DECLARE @postcount int
DECLARE @topiccount int
DECLARE @todaycount int
DECLARE @sqlstr nvarchar(4000)
DECLARE @fid varchar(2000)
DECLARE @posterid varchar(200)

declare @sql nvarchar(4000)

SET @fid = ''
SET @posterid = ''
SET @postcount=0
SET @topiccount=0
SET @todaycount=0

IF @tidlist = ''
    return

set @sql  =
'DECLARE @tempFid int ' + char(13) + char(10) +
'DECLARE @tempPosterid int ' + char(13) + char(10) +
'DECLARE @tempLayer int ' + char(13) + char(10) +
'DECLARE @temppostdatetime datetime ' + char(13) + char(10) +
'DECLARE @tempfidlist AS VARCHAR(1000) ' + char(13) + char(10) +
'SET @tempfidlist = '''' '  + char(13) + char(10) +
'declare @cu_dnt_posts cursor ' + char(13) + char(10) +
'set @cu_dnt_posts = CURSOR read_only forward_only FOR
    SELECT [fid],[posterid],[layer],[postdatetime] FROM [dnt_posts3] WHERE tid in('
    + @tidlist + ') ' +char(13) + char(10) +
'OPEN @cu_dnt_posts ' +char(13) + char(10) +
'FETCH NEXT FROM @cu_dnt_posts into @tempFid,@tempPosterid,@tempLayer,@temppostdatetime '
    + char(13) + char(10) +
'WHILE @@FETCH_STATUS = 0 ' +char(13) + char(10) +
'BEGIN ' +char(13) + char(10) +

'   SET @postcount = @postcount + 1 ' +char(13) + char(10) +

'   IF @tempLayer = 0' +char(13) + char(10) +
'       SET @topiccount = @topiccount + 1 ' +char(13) + char(10) +

'   IF DATEDIFF(d,@temppostdatetime,GETDATE()) = 0 ' + char(13) + char(10) +
'       SET @todaycount = @todaycount + 1 ' +char(13) + char(10) +

'   IF CHARINDEX('','' + LTRIM(STR(@tempFid)) + '','',@fid + '','') = 0 '
    + char(13) + char(10) +
'   BEGIN ' + char(13) + char(10) +
'       SELECT @tempfidlist = ISNULL([parentidlist],'''') FROM [dnt_forums] WHERE [fid] = @tempFid '
    + char(13) + char(10) +
'       IF RTRIM(@tempfidlist)<>'''' ' + char(13) + char(10) +
'           SET @fid = RTRIM(@fid) + '','' +  RTRIM(@tempfidlist) + '','' + CAST(@tempFid AS VARCHAR(10)) '
    + char(13) + char(10) +
'       ELSE ' +char(13) + char(10) +
'           SET @fid =RTRIM(@fid) + '','' +  CAST(@tempFid AS VARCHAR(10)) ' + char(13) + char(10) +
'   END ' +char(13) + char(10) +

'    IF @chanageposts = 1 ' +char(13) + char(10) +
'        UPDATE [dnt_users] SET [posts] = [posts] - 1 WHERE [uid] = @tempPosterid '
    + char(13) + char(10) +

'   FETCH NEXT FROM @cu_dnt_posts into @tempFid,@tempPosterid,@tempLayer,@temppostdatetime '
    + char(13) + char(10) +

'END ' +char(13) + char(10) +
'CLOSE @cu_dnt_posts ' + char(13) + char(10) +
'DEALLOCATE @cu_dnt_posts '

exec sp_executesql @sql,N'@postcount int output,@topiccount int output,
    @todaycount int output,@fid varchar(2000) output,@chanageposts int',
    @postcount output,@topiccount output,@todaycount output,@fid output,@chanageposts

IF LEN(@fid)>0
BEGIN


     SET @fid = SUBSTRING(@fid,2,LEN(@fid)-1)

     IF @chanageposts = 1
         BEGIN

             UPDATE [dnt_statistics] SET [totaltopic]=[totaltopic] - @topiccount, [totalpost]=[totalpost] - @postcount

             set @sql =
             'UPDATE [dnt_forums]
             SET [posts]=[posts] - @postcount,
             [topics]=[topics] - @topiccount,
             [todayposts] = [todayposts] - @todaycount
             where fid in (' + @fid +  ')'
             exec sp_executesql @sql,N'@postcount int,@topiccount int,@todaycount int',
                @postcount,@topiccount,@todaycount

         END

     set @sql = 'DELETE FROM [dnt_favorites] WHERE tid in (' + @tidlist + ') AND [typeid]=0'
     exec sp_executesql @sql
     print @sql

     set @sql = 'DELETE FROM [dnt_polls] WHERE tid in (' + @tidlist + ')'
     exec sp_executesql @sql
     print @sql

     set @sql = 'DELETE FROM [dnt_posts3] WHERE tid in (' + @tidlist + ')'
     exec sp_executesql @sql
     print @sql

     set @sql = 'DELETE FROM [dnt_mytopics] WHERE tid in (' + @tidlist + ')'
     exec sp_executesql @sql
     print @sql

END

set @sql = 'DELETE FROM [dnt_topics] WHERE [closed] in (' + @tidlist + ') or [tid] in (' + @tidlist + ')'
exec sp_executesql @sql
print @sql

set @sql = 'UPDATE [dnt_tags] SET [count]=[count]-1, [fcount]=[fcount]-1 WHERE [tagid] IN (SELECT [tagid] FROM [dnt_topictags] WHERE tid in (' + @tidlist + ') ) '
exec sp_executesql @sql
print @sql

set @sql = 'DELETE FROM [dnt_topictags] WHERE tid in (' + @tidlist + ')'
exec sp_executesql @sql
print @sql

set @sql = 'DELETE FROM [dnt_topictagcaches] WHERE tid in (' + @tidlist + ') OR linktid in (' + @tidlist + ')'
exec sp_executesql @sql

感兴趣的童鞋可以把@sql打印出来，看看sql脚本，修改时需谨慎，优化后的过程如何呢，我们来看看profiler的效果：

看性能参数：cpu 62，reads 2684，duration 420；KO。

如果有童鞋正在使用dnt3.0，并且你希望你的论坛真正的跑起来，不妨试一试。

到目前为止，本系列已经出了3篇随笔，都是针对dnt的数据库性能问题提供了一些基本的思路和解决方案，以后数据库不再是重点；下一篇会针对dnt的asp.net端，如有兴趣敬请期待。

下篇预告：优化你的DiscuzNT，让它跑起来（4）asp.net 缓存和死锁

作者: 鸽子飞扬发表于 2011-06-06 15:18 原文链接

Tag 优化你的discuznt,让它跑起来,删帖篇

推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架

IT Technology Blog

Post Comment

Bulletin

Article Search

Article Categories

Article Archive

New Article

New Comment

Hits Order

Comment Order

Links