笔记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
