none
为什么数据不进MDF文件中,反而到NDF文件中去了? RRS feed

  • 常规讨论

  • 各位大侠:

        今天小弟有这样一个想法,在MDF中建立表,在表上建立一个聚集非唯一的索引并将索引保存到一个空的NDF文件中,用以测试数据增长和索引增长的情况;

        在实验的过程中发现,在插入1221863条数据后,MDF文件增长为0,反而NDF文件(初始5M)增长23.5MB(均收缩数据库后得出的大小);这和聚集索引的特点有关么?(聚集索引根据数据行的键值在表或视图中排序和保存数据),我再试试非聚集索引是否会一样。

        非聚集索引MDf和NDF文件都有增长。

    drop table [聚集索引非唯一测试];
    /*聚集索引非唯一测试*/
    create table 聚集索引非唯一测试
    (
     id int not null,
     name varchar(20) not null
    );
    go
    create clustered index ix_聚集索引非唯一测试_id
    on [聚集索引非唯一测试](id)
    on grp2;
    go

    ---------------------------
    --测试聚集索引插入数据对索引的增长情况
    ---------------------------

    --建立filegroup及其file用于测试插入数据对索引的增长情况
    alter database today
    add filegroup grp2;

    go
    alter database today
    add file
    (
     name = testindex,
     filename = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\testindex.ndf',
     size = 5MB,
     maxsize = 2GB,
     filegrowth = 5MB
    )
    to filegroup grp2;

    --测试前执行收缩数据库
    dbcc shrinkdatabase(today);

    --测试前记录:MDF:4.06MB;NDF:5MB

    --记录数据库变化前执行收缩数据库
    dbcc shrinkdatabase(today);

    --测试前记录:MDF:4.06MB;NDF:28.5MB

    --测试结果:数据未保存MDF文件中

    declare @ids int;
    set @ids = 1;
    while(@ids < 10000000)
    begin
      insert into [聚集索引非唯一测试](id,name) values(@ids,'info1');
      set @ids += 1;
      print @ids;
    end;

    ---------------------------
    --测试非聚集索引插入数据对索引的增长情况
    ---------------------------

    drop table [非聚集索引非唯一测试];
    /*聚集索引非唯一测试*/
    create table 非聚集索引非唯一测试
    (
     id int not null,
     name varchar(20) not null
    );
    go
    create nonclustered index ix_非聚集索引非唯一测试_id
    on [非聚集索引非唯一测试](id)
    on grp2;
    go

    --建立filegroup及其file用于测试插入数据对索引的增长情况
    alter database today
    add filegroup grp2;

    go
    alter database today
    add file
    (
     name = testindex,
     filename = 'C:\\Program Files\\Microsoft SQL Server\\MSSQL11.MSSQLSERVER\\MSSQL\\DATA\\testindex.ndf',
     size = 5MB,
     maxsize = 2GB,
     filegrowth = 5MB
    )
    to filegroup grp2;

    --测试前执行收缩数据库
    dbcc shrinkdatabase(today);

    --测试前记录:MDF:4.06MB;NDF:5MB

    --记录数据库变化前执行收缩数据库
    dbcc shrinkdatabase(today);

    --测试前记录:MDF:14.5MB;NDF:9.56MB   随机插入数据条数,存在变化

    --测试结果:数据保存至MDF中,索引数据保存至NDF中

    declare @ids int;
    set @ids = 1;
    while(@ids < 10000000)
    begin
      insert into [非聚集索引非唯一测试](id,name) values(@ids,'info1');
      set @ids += 1;
      print @ids;
    end;

    • 已编辑 Ronins 2014年3月20日 7:16 补充脚本,
    2014年3月20日 6:56

全部回复

  • LZ您好,在创建索引的时候你指定了on关键字,就是说索引和数据都会保存在grp2文件组,也就是存放在testindex.ndf文件中

    如果你不指定on关键字,默认索引和数据都会存放在primary文件组,即mdf文件中,这个不是聚集索引的什么特点

    create clustered index ix_聚集索引非唯一测试_id
    on [聚集索引非唯一测试](id)
    on grp2;
    go

    还有on [聚集索引非唯一测试](id)不知道从哪里来的,你的表用了表分区吗,分区方案是跟grp2文件组关联的吗

    建议看一下msdn,补一下基础

    地址:http://msdn.microsoft.com/zh-cn/library/ms188783.aspx

    ON partition_scheme_name(column_name)

    <sentencetext xmlns="http://www.w3.org/1999/xhtml">指定分区方案,该方案定义要将分区索引的分区映射到的文件组。</sentencetext> CREATE PARTITION SCHEME 或 ALTER PARTITION SCHEME,使数据库中存在该分区方案。 column_name 指定对已分区索引进行分区所依据的列。 partition_scheme_name 使用的分区函数参数的数据类型、长度和精度相匹配。column_name 不限于索引定义中的列。 column_name 外,还可以指定基表中的任何列。 通过此限制,数据库引擎可验证单个分区中的键值唯一性。

    注意 注意

    <sentencetext xmlns="http://www.w3.org/1999/xhtml">在对非唯一的聚集索引进行分区时,如果尚未指定分区依据列,则默认情况下数据库引擎将在聚集索引键列表中添加分区依据列。</sentencetext> 在对非唯一的非聚集索引进行分区时,如果尚未指定分区依据列,则数据库引擎会添加分区依据列作为索引的非键(包含)列。

    partition_scheme_name 或 filegroup 且该表已分区,则索引会与基础表使用相同分区依据列并被放入同一分区方案中。

    注意 注意

    <sentencetext xmlns="http://www.w3.org/1999/xhtml">您不能对 XML 索引指定分区方案。</sentencetext> 如果基表已分区,则 XML 索引与该表使用相同的分区方案。

    已分区表和已分区索引

    ON filegroup_name

    <sentencetext xmlns="http://www.w3.org/1999/xhtml">为指定文件组创建指定索引。</sentencetext> 如果未指定位置且表或视图尚未分区,则索引将与基础表或视图使用相同的文件组。 该文件组必须已存在。

    ON "default"

    为默认文件组创建指定索引。

    <sentencetext xmlns="http://www.w3.org/1999/xhtml">在此上下文中,“default”一词不是关键字。</sentencetext> "default" 或 ON [default])。 如果指定了 "default",则当前会话的 QUOTED_IDENTIFIER 选项必须为 ON。 这是默认设置。 SET QUOTED_IDENTIFIER (Transact-SQL)

    2014年3月20日 11:24
  • as 桦仔 said,you put your table on non pimary file group,that's why your mdf file didn't grow.


    Please Mark As Answer if it is helpful.

    2014年3月20日 12:01
  • 聚集索引的叶级不就是数据吗?你将聚集索引建到ndf上,说明就是将数据库放到ndf上呀。

    chenl

    2014年3月20日 12:26
  • 聚集索引的叶级不就是数据吗?你将聚集索引建到ndf上,说明就是将数据库放到ndf上呀。

    chenl

    You mean table instead of db I assume.
    2014年3月20日 17:44
  • 表未分区,我把表存在默认的primary组中,索引保存到grp2组中的文件中,创建索引的语句个人认为没有错误。如果有其他我还未了解的内容敬请指出。
    2014年3月21日 1:52
  • Clustered index is built on data directly. When you create clustered index on grp2, sql moves whole table to that file group.
    2014年3月21日 2:09
  • LZ您还,您可能对聚集索引理解错了,我猜你是这样想的,把表的数据放在primary组,而索引是放在grp2文件组

    实际上,聚集索引无论表和数据都是放在一起的

    具体可以看一下我这篇文章:SQLSERVER聚集索引与非聚集索引的再次研究(上)

    2014年3月21日 2:11
  • 之前我了解的非聚集索引是索引和数据分开的,但我不是很清楚聚集索引是如何操作数据的,求指教。
    2014年3月21日 3:30
  • We talked about it several times above, you can check 'Clustered Index Structures' in books online for more info.
    2014年3月21日 3:33
  • 谢谢你的建议,我正在阅读。谢谢。
    2014年3月21日 3:33
  • 之前我了解的非聚集索引是索引和数据分开的,但我不是很清楚聚集索引是如何操作数据的,求指教。

    简单理解:

    聚集索引:数据跟索引是一起的

    非聚集索引:数据跟索引是分开的

    2014年3月21日 3:57
  • 你写的文章太复杂了,要死好多闹细胞。我还是先看一些简单些的,积累还不够。
    2014年3月21日 6:19
  • 因为按照定义,聚集索引的叶级别与其数据页相同,所以创建聚集索引和使用 ON partition_scheme_name 或 ON filegroup_name 子句实际上会将表从创建该表时所在的文件组移到新的分区方案或文件组中。对特定的文件组创建表或索引之前,应确认哪些文件组可用并且有足够的空间供索引使用。有关详细信息,请参阅确定索引的磁盘空间要求

    2014年3月21日 7:08
  • 你写的文章太复杂了,要死好多闹细胞。我还是先看一些简单些的,积累还不够。

    LZ您就简单的理解为

    聚集索引:数据跟索引是一起的

    非聚集索引:数据跟索引是分开的

    当你在在表上创建聚集索引,并在创建聚集索引的最后指定了ONpartition_scheme_name 或 ON filegroup_name 

    就会将表数据搬到相应的 partition_scheme_name 或 ON filegroup_name (分区方案或文件组上)

    如果ON filegroup_name 文件组不是主文件组,是其他文件组,那么你的表数据当然不在主文件组里

    2014年3月21日 7:14
  • 给你一个文章系列

    http://blog.csdn.net/smallfools/article/details/4937878

    2014年3月21日 7:15
  • 谢谢 。
    2014年3月21日 8:52