none
聚集键的选择,关于查询效率和碎片容忍度的选择问题(即便碎片度很高,在查询关键字上建立聚集索引还是值得的) RRS feed

  • 问题

  • 聚集索引的重要性就不说了,关于聚集键的选择也有很多说法,这里我只是假定在特定的场景下,这种场景其实来源于实际情况

    下面描述一下问题

    一个商品表,查询最多的就是根据商品的编号来查询,商品编号可以存在重复,同时商品表中的数据时不断增多的(可以使同样编号的商品,但是厂家价格等其他条件都不一样),

    我的测试时用两个表,字段都是一样的,都有一个自增列,第一个表把聚集索引建立在自增列上,

    尽可能低减少碎片,同时为了查询尽可能地公平起见,在商品编号字段上建立了非聚集索引

    第二个表把聚集索引建立在商品编号上,不用考虑,碎片度是很高的,等下看截图就知道了

    IF (OBJECT_ID('TABLE1') IS NOT NULL)
    DROP TABLE TABLE1
    IF (OBJECT_ID('TABLE2') IS NOT NULL)
    DROP TABLE TABLE2
    go
    
    
    --测试表1
    CREATE TABLE [TABLE1](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[CATEGORYID] [varchar](10) NULL,
    	[OtherCol1] [varchar](200) NULL,
    	[OtherCol2] [varchar](200) NULL
    ) 
    --聚集索引建立在自增列上,最大程度上减少碎片
    CREATE CLUSTERED INDEX INDEX_ID ON TABLE1([ID])
    --因为要以categoryid作为查询字段,在categoryid上建立非聚集索引
    CREATE INDEX INDEX_CATEGORYID ON TABLE1(CATEGORYID)
    
    --测试表2
    CREATE TABLE [TABLE2](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[CATEGORYID] [varchar](10) NULL,
    	[OtherCol1] [varchar](200) NULL,
    	[OtherCol2] [varchar](200) NULL
    ) 
    --聚集索引建立在随即列上,同时也存在重复性
    CREATE CLUSTERED INDEX INDEX_CATEGORYID ON [TABLE2]([CATEGORYID])
    go
    
    
    TRUNCATE TABLE TABLE1
    TRUNCATE TABLE TABLE2
    go
    
    
    DECLARE @I INT,@CategoryID varchar(20),@OtherCol varchar(50)
    SET @I=1
    WHILE @I<=100000
    BEGIN
    	--模拟一个商品ID,随即生成,0001——9999
    	set @CategoryID=SUBSTRING(CAST(RAND()+0.1 AS VARCHAR(10)),3,3);
    	set @OtherCol=CAST(NEWID() as varchar(50));
    	INSERT INTO TABLE1 VALUES (@CategoryID,@OtherCol,@OtherCol) ;
    	INSERT INTO TABLE2 VALUES (@CategoryID,@OtherCol,@OtherCol) ;	
    	set @I=@I+1;
    END

    对索引的碎片

    如下截图,很明显,第二个表的碎片度很高,这里目的就在此,测试碎片容忍度和查询性能的关系(即便碎片度很高,在查询关键字上建立聚集索引还是值得的

    --下面来查询

    UPDATE STATISTICS TABLE1[INDEX_ID] UPDATE STATISTICS TABLE1[INDEX_CATEGORYID] UPDATE STATISTICS TABLE2[INDEX_CATEGORYID] DBCC DROPCLEANBUFFERS SET STATISTICS IO ON SELECT * FROM TABLE1 WHERE CATEGORYID='666' SELECT * FROM TABLE2 WHERE CATEGORYID='666'

    结果截图

    结果就不用说了

    这里就是想说明,在特定(再次强调是特定)的条件下,为了查询的性能,碎片是可以容忍的,另外就是,对于碎片的处理,我们可以通过重建或者重新生成索引来实现

    另外,在这里(仅仅在测试场景中),table2还节约了一个非聚集的索引,也算是一种优势吧

    不知道说的对不对,请大家批判,谢谢。


    • 已编辑 X_PICK 2012年8月11日 8:33
    2012年8月11日 8:31

答案

  • 用空间换时间,简单的说,没问题。

    数据库查询中最大的开销是IO,只要你能确保你的查询计划能节省的IO 大于碎片造成的额外的IO,那就OK。当然前提是磁盘空间足够。



    • 已编辑 Jacky_shen 2012年8月11日 12:26
    • 已标记为答案 X_PICK 2012年8月13日 6:21
    2012年8月11日 12:21
  • 另外,按msdn上面的说法,如果碎片比小于30%,我们可以重新组织索引,如果碎片比大于等于30%,我们可以选择重新生成索引。

    安装你给出的测试图片,你可以选择重新生成索引了


    给我写信: QQ我:点击这里给我发消息

    • 已标记为答案 X_PICK 2012年8月13日 6:21
    2012年8月12日 1:17

全部回复