SQL数据库的字段与索引重复创建后..数据库MDF文件增长..

Sql数据量不变的情况下..数据文件mdf大幅度增加.. http://bbs.csdn.net/topics/392089229

在早上的帖子中..最终的结论推测可能是字段和索引重复建立造成的数据库文件虚增..
所以去写了一段代码来专门测试是否是这个问题..


            //事先在表中添加了265w行数据.. 只有一列 MemberNumber
            int INDEX = 0;
            while (INDEX < 10)
            {

                int cou = 15;
                for (int i = 0; i < cou; i++)
                {
                    var ItemField = "value" + i;
                    Console.WriteLine("======检查索引,并删除=====value" + i);
                    David.DB.DoSQL.Ext_DropIndex(TargetTable, string.Format("INDEX_{0}_{1}", TargetTable, ItemField).ToUpper());


                    Console.WriteLine("======删除列重建=====value" + i);
                    bool IsSeccessAddCol = David.DB.DoSQL.Ext_InitColumn(TargetTable, ItemField, David.Common.ComEnum.OverAll.ExcDataType.整数, "", "0");


                    Console.WriteLine("======开始覆盖数据=====value" + i);
                    SQL_Sync = "UPDATE " + TargetTable + " SET " + ItemField + "=100";
                    sync_Result = David.DB.DoSQL.ExecuteRowCount(SQL_Sync);
                    Console.WriteLine("修改 [" + sync_Result + "] 行数据");


                    Console.WriteLine("======创建索引=====value" + i);
                    David.DB.DoSQL.Ext_CreateIndex(TargetTable, string.Format("INDEX_{0}_{1}", TargetTable, ItemField).ToUpper(), ItemField);
                    Console.WriteLine("创建索引完成");
                }
                //在来一次
                INDEX++;
            }

思路是这样..
1.  一张空表..有一个字段MemberNumber..添加入265w数据..给该字段建立聚集索引..
2.  然后双循环嵌套..
     2.1  内层执行顺序是删索引 -> 重建字段 -> 每行都赋值为10  -> 建立索引   这样建立15个字段和15个索引
     2.2  外层循环把内层的整个过程执行10次..每次在 Index++ 的时候..看一下库的Mdf文件的大小..

实际执行时只跑了四圈就很明显的看出问题了..
值分别如下..:
      2155.00 M
      2327.00 M
      2498.00 M
      2671.00 M..
      记录时数据库都没有什么空闲值..基本100%占用..
      每次都有171M左右的增加..

这就奇怪了.. 字段数量没有增加..索引数量没有增加..字段的内容没有改变..为什么会多出这171M..

请高手解答..

16 个解决方案

#1

你看看你的数据库每次增长多少，一般是按照原大小的半分比，或者是一个固定的大小来增长的

#2

引用 1 楼 yupeigu 的回复:

你看看你的数据库每次增长多少，一般是按照原大小的半分比，或者是一个固定的大小来增长的

每次171M左右..

#3

引用 1 楼 yupeigu 的回复:

你看看你的数据库每次增长多少，一般是按照原大小的半分比，或者是一个固定的大小来增长的

呃..可能有个误会..我指的并不是数据库的自然增长..
如果数据有增加..比如原来有100行现在变为2000行..那数据文件增长是正常的..
但是现在问题是数据内容没有改变..只是重新计算..数据文件也会增长..
增长了171M占用仍然是100%.. 等于白白多出了171M的占用..
我是想知道这是什么东西..

#4

正常啊。
记录更新时会把旧数据复制一份，在事务递交前作为回滚的依据，事务递交后一样要留着，作为事务备份/事务复制的依据。
既然你265w数据全体更新，就会有固定大小的旧数据复制保存。

你试试每次执行后截断事务。

#5

引用 4 楼 Tiger_Zhao 的回复:

正常啊。
记录更新时会把旧数据复制一份，在事务递交前作为回滚的依据，事务递交后一样要留着，作为事务备份/事务复制的依据。
既然你265w数据全体更新，就会有固定大小的旧数据复制保存。

你试试每次执行后截断事务。

呃..表示不赞同..
如果按您的理解的话..那一个库如果使用事务较多..那岂不要产生极多的硬盘占用..并且是无意义的..

另外我这个demo里没有使用事务提交..

#6

引用 5 楼 sunyanjie_china 的回复:

Quote: 引用 4 楼 Tiger_Zhao 的回复:

正常啊。
记录更新时会把旧数据复制一份，在事务递交前作为回滚的依据，事务递交后一样要留着，作为事务备份/事务复制的依据。
既然你265w数据全体更新，就会有固定大小的旧数据复制保存。

你试试每次执行后截断事务。

呃..表示不赞同..
如果按您的理解的话..那一个库如果使用事务较多..那岂不要产生极多的硬盘占用..并且是无意义的..

另外我这个demo里没有使用事务提交..

事务提交前保留一份数据做为回滚的依据我是赞同的..但如果说提交后依然留有备份..则不赞同..

#7

昨天回去路上就想到了，我把回滚用数据和日志用数据搞混了。 SQL数据库的字段与索引重复创建后..数据库MDF文件增长..

更新时有两份副本：
一份旧数据，用于事务回滚，事务结束则丢弃。
一份新数据，用于备份/复制，会一直保留到截断事务时。
因为用事务备份恢复时，旧数据数据库中有，但是这个操作将要更新的新数据需要从备份中取。

#8

引用 7 楼 Tiger_Zhao 的回复:

昨天回去路上就想到了，我把回滚用数据和日志用数据搞混了。
更新时有两份副本：
一份旧数据，用于事务回滚，事务结束则丢弃。
一份新数据，用于备份/复制，会一直保留到截断事务时。
因为用事务备份恢复时，旧数据数据库中有，但是这个操作将要更新的新数据需要从备份中取。

对..其实现在的重心在数据文件 mdf上..
日志的话现在是简单模式,定时维护计划有收缩..不然一天能跑300G+的日志..再大也能跑..因为磁盘剩余空间就300G了..

我觉得有可能是在删除重建列之后..原本的列占用的空间并没有释放.. 故每次增加171M..虽然前后两次建立的列名一样..但是其实地址或者指针什么的不一样了..之前的列变成了无名数据..却一直占用空间..
只是猜测..有大神看到了请指正..

#9

碎片也不多..
下边是碎片扫描的结果..

SQL数据库的字段与索引重复创建后..数据库MDF文件增长..

最优和实际之间的差距很小..

但每页可用字节数有点高..

#10

收缩一下日志文件吧

#11

这个不知道是不是数据库的问题？

#12

引用 9 楼 sunyanjie_china 的回复:

我觉得有可能是在删除重建列之后..原本的列占用的空间并没有释放

本来就应该这样的。删列只需要把定义标记为不用，并不会对数据进行处理，数据库总是时间优先。
你可以对每步操作测下时间，删列应该是很快的。

#13

引用 12 楼 Tiger_Zhao 的回复:

[Quote=引用 9 楼 sunyanjie_china 的回复:]我觉得有可能是在删除重建列之后..原本的列占用的空间并没有释放

本来就应该这样的。删列只需要把定义标记为不用，并不会对数据进行处理，数据库总是时间优先。
你可以对每步操作测下时间，删列应该是很快的。

对的..我其实只是想在重新计算前..把原有数据初始化掉..当时使用 Update set col=null 速度太慢.. 改用了直接删列重建..
我把方法改回来了..正在测试数据库是否会变大.. 目前来看是正常的..

#14

几百万数据都要重算，有这样的需求？
设计没问题吧？通常是只有按照时间相关或有业务发生相关的一批数据才需要重算。

#15

引用 14 楼 Tiger_Zhao 的回复:

几百万数据都要重算，有这样的需求？
设计没问题吧？通常是只有按照时间相关或有业务发生相关的一批数据才需要重算。

用户标签的计算.. 几百万是用户.. 每个用户的各种属性和指标..在一定期限后要重新计算..譬如生命周期.. 最近28天的消费和互动指数..
因为要求web端实时的能够任意组合标签和属性来得到交集用户.. 所以使用hive端计算..sql端更新和存储..
设计应该是没问题的.. 当然有大神觉得有更好方案的不妨交流一下..

#16

全体清零，一次性操作的数据太多，事务就大了。

只对有需要的用户重新计算。
比如“最近28天的消费和互动指数”的用户ID挑出来加到一个临时表，还有其它的相关业务的用户ID也挑出来加到临时表……
最好依据临时表内的用户ID分批对用户重新计算。

#1

你看看你的数据库每次增长多少，一般是按照原大小的半分比，或者是一个固定的大小来增长的

#2

引用 1 楼 yupeigu 的回复:

你看看你的数据库每次增长多少，一般是按照原大小的半分比，或者是一个固定的大小来增长的

每次171M左右..

#3

引用 1 楼 yupeigu 的回复:

你看看你的数据库每次增长多少，一般是按照原大小的半分比，或者是一个固定的大小来增长的

#4

#5

引用 4 楼 Tiger_Zhao 的回复:

正常啊。
记录更新时会把旧数据复制一份，在事务递交前作为回滚的依据，事务递交后一样要留着，作为事务备份/事务复制的依据。
既然你265w数据全体更新，就会有固定大小的旧数据复制保存。

你试试每次执行后截断事务。

#6

引用 5 楼 sunyanjie_china 的回复:

Quote: 引用 4 楼 Tiger_Zhao 的回复:

正常啊。
记录更新时会把旧数据复制一份，在事务递交前作为回滚的依据，事务递交后一样要留着，作为事务备份/事务复制的依据。
既然你265w数据全体更新，就会有固定大小的旧数据复制保存。

你试试每次执行后截断事务。

呃..表示不赞同..
如果按您的理解的话..那一个库如果使用事务较多..那岂不要产生极多的硬盘占用..并且是无意义的..

另外我这个demo里没有使用事务提交..

事务提交前保留一份数据做为回滚的依据我是赞同的..但如果说提交后依然留有备份..则不赞同..

#7

昨天回去路上就想到了，我把回滚用数据和日志用数据搞混了。 SQL数据库的字段与索引重复创建后..数据库MDF文件增长..

#8

引用 7 楼 Tiger_Zhao 的回复:

昨天回去路上就想到了，我把回滚用数据和日志用数据搞混了。
更新时有两份副本：
一份旧数据，用于事务回滚，事务结束则丢弃。
一份新数据，用于备份/复制，会一直保留到截断事务时。
因为用事务备份恢复时，旧数据数据库中有，但是这个操作将要更新的新数据需要从备份中取。

#9

碎片也不多..
下边是碎片扫描的结果..

SQL数据库的字段与索引重复创建后..数据库MDF文件增长..

最优和实际之间的差距很小..

但每页可用字节数有点高..

#10

收缩一下日志文件吧

#11

这个不知道是不是数据库的问题？

#12

引用 9 楼 sunyanjie_china 的回复:

我觉得有可能是在删除重建列之后..原本的列占用的空间并没有释放

本来就应该这样的。删列只需要把定义标记为不用，并不会对数据进行处理，数据库总是时间优先。
你可以对每步操作测下时间，删列应该是很快的。

#13

引用 12 楼 Tiger_Zhao 的回复:

[Quote=引用 9 楼 sunyanjie_china 的回复:]我觉得有可能是在删除重建列之后..原本的列占用的空间并没有释放

#14

几百万数据都要重算，有这样的需求？
设计没问题吧？通常是只有按照时间相关或有业务发生相关的一批数据才需要重算。

#15

引用 14 楼 Tiger_Zhao 的回复:

几百万数据都要重算，有这样的需求？
设计没问题吧？通常是只有按照时间相关或有业务发生相关的一批数据才需要重算。

秒客网

SQL数据库的字段与索引重复创建后..数据库MDF文件增长..

16 个解决方案

#1

#2

#3

#4

#5

#6

#7

#8

#9

#10

#11

#12

#13

#14

#15

#16

#1

#2

#3

#4

#5

#6

#7

#8

#9

#10

#11

#12

#13

#14

#15

#16

相关文章