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

最新推荐文章于 2024-07-13 18:10:19 发布

weixin_34092370

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量84

点赞数

文章标签：数据库

注：本文仅针对 DiscuzNT3.0, sqlserver 2000版本，其他版本请勿对号入座.

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

由于有了之前的两次经验，现在找起问题来比之前要快多了，现在先找到删帖的存储过程 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 缓存和死锁

weixin_34092370

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
优化你的DiscuzNT3.0，让它跑起来（3）删帖篇

注：本文仅针对 DiscuzNT3.0, sqlserver 2000版本，其他版本请勿对号入座. 在前两篇已经对看帖，发帖回帖做了优化，做过优化之后论坛访问起来果然顺畅多了。随着论坛帖子数的不断增加，论坛出现了一个新的问题，管理员在删帖的时候经常报错，超时，删除失败。由于有了之前的两次经验，现在找起问题来比之前要快多了，现在先找到删帖的存储过程 dnt_deletetopicbytidlist3...
复制链接

扫一扫