none
索引逻辑碎片对index seek的影响 RRS feed

答案

  •  测试示例
    USE tempdb;  
    GO  
    SET NOCOUNT ON;  
    -- 测试环境  
    CREATE TABLE dbo.tb(  
        col char(900)  
            CONSTRAINT PK_tb_col PRIMARY KEY 
    );  
    DECLARE 
        @row int;  
    SET @row = 1000;  
    WHILE @row > 0  
    BEGIN 
        INSERT dbo.tb VALUES(CONVERT(char(36), NEWID()));  
        SET @row = @row - 1;  
    END;  
    GO  
     
    -- 测试  
    DBCC FREEPROCCACHE;  
    DBCC DROPCLEANBUFFERS;  
    CHECKPOINT;  
    GO  
    SET STATISTICS IO ON;  
    GO  
    SELECT * FROM dbo.tb;  
    GO  
    SET STATISTICS IO OFF;  
    GO  
     
    ALTER INDEX PK_tb_col  
        ON dbo.tb  
        REBUILD;  
    GO  
    DBCC FREEPROCCACHE;  
    DBCC DROPCLEANBUFFERS;  
    CHECKPOINT;  
    GO  
    SET STATISTICS IO ON;  
    GO  
    SELECT * FROM dbo.tb;  
    GO  
    SET STATISTICS IO OFF;  
    GO  
     
    DROP TABLE dbo.tb  
     
    2009年2月9日 21:57
  • seek读的页几乎不会增加。scan的话,现在的大型存储设备,已经不存在这样的问题了,当然,split会增加页的数目。

    B+ Tree不会多出来一层的,你仔细想想就知道了。
    无我原非你,从他不解伊
    2009年2月12日 8:19
    版主

全部回复

  • 简单的说,就是会增加I/O。所以DBA通常会定期重整索引。
    SQL 2005支持online的重整索引,这一点非常方便。
    无我原非你,从他不解伊
    2009年2月9日 7:55
    版主
  • 理论大家都知道,给个DEMO最好。

    自己先坐沙发。

    等会贴例子。
    2009年2月9日 8:06
  • 第一步,创建表看索引结构

    use tempdb;

    go

    drop table t;
    go
    create table t (

        id    char(6)        not null constraint pk_t primary key clustered,

        b                      varchar(8000)  not null

    )

    go

     

    insert t ( id, b ) values( '00001', replicate( 'A', 3000 ) )

    insert t ( id, b ) values( '00002', replicate( 'B', 1000 ) )

    insert t ( id, b ) values( '00003', replicate( 'C', 3000 ) )

    go

    DBCC IND ( 0, 't', 1);

    GO
    2009年2月9日 8:09
  • 怎么贴图啊
    2009年2月9日 8:23
  • 论坛好烂,不能贴图.

    转blog发吧:


    http://xuyuanfeng.spaces.live.com/

    2009年2月9日 8:33
  • 索引碎片一般是长期对数据库进行操作造成的,而且索引所占的空间也一定要大,如果索引只占几K,那是不会有碎片的概念的。所以例子不好给。呵呵
    郭勇成
    2009年2月9日 9:54
  • Three rows in the table can't explain anything. 
    2009年2月9日 20:33
  •  测试示例
    USE tempdb;  
    GO  
    SET NOCOUNT ON;  
    -- 测试环境  
    CREATE TABLE dbo.tb(  
        col char(900)  
            CONSTRAINT PK_tb_col PRIMARY KEY 
    );  
    DECLARE 
        @row int;  
    SET @row = 1000;  
    WHILE @row > 0  
    BEGIN 
        INSERT dbo.tb VALUES(CONVERT(char(36), NEWID()));  
        SET @row = @row - 1;  
    END;  
    GO  
     
    -- 测试  
    DBCC FREEPROCCACHE;  
    DBCC DROPCLEANBUFFERS;  
    CHECKPOINT;  
    GO  
    SET STATISTICS IO ON;  
    GO  
    SELECT * FROM dbo.tb;  
    GO  
    SET STATISTICS IO OFF;  
    GO  
     
    ALTER INDEX PK_tb_col  
        ON dbo.tb  
        REBUILD;  
    GO  
    DBCC FREEPROCCACHE;  
    DBCC DROPCLEANBUFFERS;  
    CHECKPOINT;  
    GO  
    SET STATISTICS IO ON;  
    GO  
    SELECT * FROM dbo.tb;  
    GO  
    SET STATISTICS IO OFF;  
    GO  
     
    DROP TABLE dbo.tb  
     
    2009年2月9日 21:57
  •  测试的结果
    I/O的差异(碎片整理前/后)
    表 'tb'。扫描计数 1,逻辑读取 224 次,物理读取 0 次,预读 20 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
    表 'tb'。扫描计数 1,逻辑读取 144 次,物理读取 1 次,预读 69 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
    2009年2月9日 21:58
  • 第二步,查看索引页:

    PageFID    PagePID    IAMFID    IAMPID    ObjectID    IndexID    PartitionNumber    PartitionID    iam_chain_type    PageType
    1    118    NULL    NULL    37575172    1    1    576460752590995456    In-row data    10
    1    115    1    118    37575172    1    1    576460752590995456    In-row data    1

    可以看出叶子节点只有一个,进一步查看内容:
    dbcc traceon(3604)

    dbcc page(0,1,115,3)

    可以看出所表中数据所有内容存储在page id=115的数据页中。


    第三步,增加或修改数据,使其page split.


    2009年2月10日 2:06
  • zjcxc.邹建 说:

     测试示例

    USE tempdb;  
    GO  
    SET NOCOUNT ON;  
    -- 测试环境  
    CREATE TABLE dbo.tb(  
        col char(900)  
            CONSTRAINT PK_tb_col PRIMARY KEY 
    );  
    DECLARE 
        @row int;  
    SET @row = 1000;  
    WHILE @row > 0  
    BEGIN 
        INSERT dbo.tb VALUES(CONVERT(char(36), NEWID()));  
        SET @row = @row - 1;  
    END;  
    GO  
     
    -- 测试  
    DBCC FREEPROCCACHE;  
    DBCC DROPCLEANBUFFERS;  
    CHECKPOINT;  
    GO  
    SET STATISTICS IO ON;  
    GO  
    SELECT * FROM dbo.tb;  
    GO  
    SET STATISTICS IO OFF;  
    GO  
     
    ALTER INDEX PK_tb_col  
        ON dbo.tb  
        REBUILD;  
    GO  
    DBCC FREEPROCCACHE;  
    DBCC DROPCLEANBUFFERS;  
    CHECKPOINT;  
    GO  
    SET STATISTICS IO ON;  
    GO  
    SELECT * FROM dbo.tb;  
    GO  
    SET STATISTICS IO OFF;  
    GO  
     
    DROP TABLE dbo.tb  
     



    看清楚题目了,我说是对index seek的影响。
    2009年2月10日 3:02
  • insert t ( id, b ) values( '000011', replicate( 'A', 5000 ) )



    DBCC IND ( 0, 't', 1);

    GO


    PageFID    PagePID    IAMFID    IAMPID    ObjectID    IndexID    PartitionNumber    PartitionID    iam_chain_type    PageType    IndexLevel
    1    118    NULL    NULL    37575172    1    1    576460752590995456    In-row data    10    NULL
    1    115    1    118    37575172    1    1    576460752590995456    In-row data    1    0
    1    114    1    118    37575172    1    1    576460752590995456    In-row data    2    1
    1    120    1    118    37575172    1    1    576460752590995456    In-row data    1    0

    新增加一行,增加了索引根节点114和索引叶子节点129

    查看115页和129页数据:

    dbcc traceon(3604)

    dbcc page(0,1,115,3)


    dbcc traceon(3604)

    dbcc page(0,1,120,3)

    page 115内容:

    '00001','000011'

    page 120 内容:

    '00002','00003'


    说明115页被split,把以前的'00002','00003'移至page 120上。


    2009年2月10日 5:42
  • 第四步,继续存入数据,使其发生逻辑碎片

    insert t ( id, b ) values( '000021', replicate( 'A', 7000 ) )


    GO
    update t set b=replicate( 'A', 8000 ) where id='000011'

    GO

    DBCC IND ( 0, 't', 1);

    1    118    NULL    NULL    37575172    1    1    576460752590995456    In-row data    10    NULL    0    0
    1    115    1    118    37575172    1    1    576460752590995456    In-row data    1    0    1    126
    1    114    1    118    37575172    1    1    576460752590995456    In-row data    2    1    0    0
    1    120    1    118    37575172    1    1    576460752590995456    In-row data    1    0    1    121
    1    121    1    118    37575172    1    1    576460752590995456    In-row data    1    0    0    0
    1    126    1    118    37575172    1    1    576460752590995456    In-row data    1    0    1    120


    现在page 链表如下:

    page 115- page 126-page 120 -page 121

    现在叶子节点数据内容如下:


    PAGE 115:
    '00001'


    page 126:
    '000011',

    page 120:

    '00002'

    '000021'

    page 121:

    ''00003'


    现在已经发生PAGE ID与存储内容顺序不一样的情况了。

    2009年2月10日 5:58
  • 在继续进行以前,贴一张图是碎片整理过程

    http://i.technet.microsoft.com/Cc966523.ss2kbp08(en-us,TechNet.10).gif
    2009年2月10日 6:07
  • 竟然没人看。
    2009年2月12日 2:13
  •  这又没什么意义,当然没人看了。重要的是I/O的数目,跟顺序没关系。再怎么split,也不会用B+ Tree多出来一层。
    无我原非你,从他不解伊
    2009年2月12日 4:24
    版主



  • 对于index seek来说,由于page split 页会增加index seek多读数据页。

    对于index scan来讲,由于逻辑顺序与存储内容顺序不一致,当需要排序scan时,会引起disk splindle 来回移动,增加物理IO。


    当使用alter index organize  整理index 时,对于mixed extent(前8个数据页) 只能压缩其中的数据页,而不能使逻辑顺序与存储顺序一致。
    对于统一盘区,尽量使其顺序一致。
    算法就是上面的贴图。

    2009年2月12日 6:21
  • 怡红公子 说:

     这又没什么意义,当然没人看了。重要的是I/O的数目,跟顺序没关系。再怎么split,也不会用B+ Tree多出来一层。


    无我原非你,从他不解伊




    问一个问题:

    当page split过多时,会不会使b+ 索引层数增加,我认为当然 是会的。
    2009年2月12日 6:27
  • seek读的页几乎不会增加。scan的话,现在的大型存储设备,已经不存在这样的问题了,当然,split会增加页的数目。

    B+ Tree不会多出来一层的,你仔细想想就知道了。
    无我原非你,从他不解伊
    2009年2月12日 8:19
    版主
  • 谢谢公子:

    那索引从两级变成三级时,是怎么变的?
    • 已编辑 四海 2009年2月12日 8:52
    2009年2月12日 8:36
  • 另外,page split 后,以前1页可以容纳2行数据,现在变成一页容纳一行,另一页再容纳一行,seek 当然要增加一个逻辑读了。

    2009年2月12日 8:39
  • 老贴,竟然翻出来了。
    2010年11月16日 8:43