笔记135 聚集键的选择/测试，关于查询效率和碎片容忍度的选择

 1 --聚集键的选择/测试，关于查询效率和碎片容忍度的选择
 2 --（即便碎片度很高，在查询关键字上建立聚集索引还是值得的
 3 
 4 
 5 
 6 --RAND()生成 1以内的随机数 例如：0.703350438790276
 7 --SELECT SUBSTRING(CAST(RAND()+0.1 AS VARCHAR(10)),3,3); 
 8 
 9 --SELECT CAST(NEWID() as varchar(50));
10 
11 USE pratice
12 IF (OBJECT_ID('TABLE1') IS NOT NULL)
13 DROP TABLE TABLE1
14 IF (OBJECT_ID('TABLE2') IS NOT NULL)
15 DROP TABLE TABLE2
16 go
17 
18 
19 --测试表1
20 CREATE TABLE [TABLE1](
21      [ID] [int] IDENTITY(1,1) NOT NULL,
22      [CATEGORYID] [varchar](10) NULL,
23      [OtherCol1] [varchar](200) NULL,
24      [OtherCol2] [varchar](200) NULL
25 )
26 --聚集索引建立在自增列上，最大程度上减少碎片
27 CREATE CLUSTERED INDEX INDEX_ID ON TABLE1([ID])
28 --因为要以categoryid作为查询字段，在categoryid上建立非聚集索引
29 CREATE INDEX INDEX_CATEGORYID ON TABLE1(CATEGORYID)
30 
31 --测试表2
32 CREATE TABLE [TABLE2](
33      [ID] [int] IDENTITY(1,1) NOT NULL,
34      [CATEGORYID] [varchar](10) NULL,
35      [OtherCol1] [varchar](200) NULL,
36      [OtherCol2] [varchar](200) NULL
37 )
38 --聚集索引建立在随即列上，同时也存在重复性
39 CREATE CLUSTERED INDEX INDEX_CATEGORYID ON [TABLE2]([CATEGORYID])
40 go
41 
42 
43 TRUNCATE TABLE TABLE1
44 TRUNCATE TABLE TABLE2
45 go
46 
47 
48 DECLARE @I INT,@CategoryID varchar(20),@OtherCol varchar(50)
49 SET @I=1
50 WHILE @I<=1000
51 BEGIN
52      --模拟一个商品ID，随即生成，0001——9999
53      set @CategoryID=SUBSTRING(CAST(RAND()+0.1 AS VARCHAR(10)),3,3);
54      set @OtherCol=CAST(NEWID() as varchar(50));
55      INSERT INTO TABLE1 VALUES (@CategoryID,@OtherCol,@OtherCol) ;
56      INSERT INTO TABLE2 VALUES (@CategoryID,@OtherCol,@OtherCol) ;    
57      set @I=@I+1;
58 END
59 
60 --按msdn上面的说法，如果碎片比小于30%,我们可以重新组织索引，如果碎片比大于等于30%，我们可以选择重新生成索引。
61 --动态函数sys.dm_db_index_physical_stats
62 --查看当前库的所有表的所有索引碎片
63 SELECT * FROM sys.dm_db_index_physical_stats(DB_ID( ),NULL ,NULL ,NULL,NULL)
64 
65 SELECT * FROM sys.dm_db_index_physical_stats( DB_ID('DBTEST'),OBJECT_ID('table1'),OBJECT_ID('INDEX_CATEGORYID'),NULL,NULL)
66 SELECT * FROM sys.dm_db_index_physical_stats( DB_ID('DBTEST'),OBJECT_ID('table2'),OBJECT_ID('INDEX_CATEGORYID'),NULL,NULL)
67 
68 --下面来查询
69 UPDATE STATISTICS TABLE1[INDEX_ID]
70 UPDATE STATISTICS TABLE1[INDEX_CATEGORYID]
71 UPDATE STATISTICS TABLE2[INDEX_CATEGORYID]
72 
73 DBCC DROPCLEANBUFFERS
74 SET STATISTICS IO ON
75 
76 SELECT * FROM TABLE1 WHERE CATEGORYID='666'
77 SELECT * FROM TABLE2 WHERE CATEGORYID='666'
78 
79 
80 --DBCC命令查看索引碎片
81 USE GPOSDB
82 GO
83 DBCC SHOWCONTIG WITH tableresults,ALL_INDEXES
84 GO