千万数据的连续ID表,快速读取其中指定的某1000条数据？

有这样一个需求：一张上千万数据的表，结构很简单：ID是自增的，你怎么快速读取其中指定的某1000条数据，比如100万到100万零1000？

这个需求其实很简单，因为是自增型ID，可能分两种状况：有聚集索引或Heap，如果是后者，我想用ID和新增时间组建非聚集索引。效果应该相差不大。

于是动手，过程如下：

一、准备测试数据：

基本测试环境：

插入1000万测试数据：

1. /***************创建千万级测试数据库*********** 
2. ****************downmoon 3w@live.cn ***************/ 
3.  
4. Create database HugeData_10Millons 
5. go 
6. use HugeData_10Millons 
7. go 
8.  
9. /***************创建测试表********************* 
10. ****************downmoo  3w@live.cn ***************/ 
11.  
12. IF NOT OBJECT_ID('[bigTable]') IS NULL 
13.     DROP TABLE [bigTable] 
14. GO 
15. Create table bigTable 
16. (PID int identity(1,1) primary key not null 
17. ,PName nvarchar(100) null 
18. ,AddTime dateTime null 
19. ,PGuid Nvarchar(40) 
20. ) 
21. go 
22.  
23. truncate table [bigTable] 
24.  
25. /***************创建第一个25万测试数据********************* 
26. ****************downmoo  3w@live.cn ***************/ 
27.  
28. declare @d datetime  
29. set @d=getdate()  
30.  
31. declare @i int 
32. set @i=1 
33. while @i<=250000 
34. begin 
35.     insert into [bigTable] 
36.     select cast(datepart(ms,getdate()) as nvarchar(3))+Replicate('A',datepart(ss,getdate())) 
37.     ,getdate() 
38.     ,NewID() 
39.     set @i=@i+1 
40. end 
41.  
42. select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())  
43.  
44. /* 
45. 语句执行花费时间(毫秒) 
46. 94750 
47. */ 
48.  
49. /***************创建第二个25万测试数据********************* 
50. ****************downmoo  3w@live.cn ***************/ 
51.  
52. declare @d datetime  
53. set @d=getdate()  
54.  
55. declare @i int 
56. set @i=1 
57. while @i<=250000 
58. begin 
59.     insert into [bigTable] 
60.     select cast(datepart(ms,getdate()) as nvarchar(3))+Replicate(Substring(cast(NEWID() as nvarchar(40)),1,6),3) 
61.     ,getdate() 
62.     ,NewID() 
63.     set @i=@i+1 
64. end 
65.  
66. select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())  
67.  
68. /* 
69. 语句执行花费时间(毫秒) 
70. 115640 
71. */ 
72.  
73. /***************创建900万测试数据********************* 
74. ****************downmoo  3w@live.cn ***************/ 
75.  
76. declare @d datetime  
77. set @d=getdate()  
78.  
79. declare @i int 
80. set @i=1 
81. while @i<=9000000 
82. begin 
83.     insert into [bigTable] 
84.     select replicate('X',ROUND((RAND()* 60),0) )+cast(datepart(ms,getdate()) as nvarchar(3)) 
85.     ,getdate() 
86.     ,NewID() 
87.     set @i=@i+1 
88. end 
89.  
90. select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())  
91. /* 
92. 语句执行花费时间(毫秒) 
93. 3813686 
94. */ 
95.  
96. /***************创建最后50万测试数据********************* 
97. ****************downmoo  3w@live.cn ***************/ 
98.  
99. declare @d datetime  
100. set @d=getdate()  
101.  
102. declare @i int 
103. set @i=1 
104. while @i<=500000 
105. begin 
106.     insert into [bigTable] 
107.     select replicate('X',ROUND((RAND()* 60),0) )+cast(NewID() as nvarchar(40)) 
108.     ,getdate() 
109.     ,NewID() 
110.     set @i=@i+1 
111. end 
112.  
113. select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())  
114. /* 
115. 语句执行花费时间(毫秒) 
116. 207436 
117. */ 
118.  
119. /* 
120. 检查数量 
121. select count(1) from dbo.bigTable 
122. ----------10000000 
123. 清除日志 
124. DUMP TRANSACTION HugeData_10Millons WITH NO_LOG 
125. BACKUP LOG HugeData_10Millons WITH NO_LOG 
126. DBCC SHRINKDATABASE(HugeData_10Millons) 
127.  
128. */ 

完成后，数据文件大小如下：

二、创建一个存储过程用于测试：

1. /***************查中间某段1000条顺序数据********************* 
2. ****************downmoo  3w@live.cn ***************/ 
3. Create procedure GetTop1000RecordsByRange 
4. (@begin int 
5. ,@end int 
6. ) 
7. as  
8. select top 1000 * from [bigTable] 
9. where PID between @begin and @end 
10. go 

邀月说明：其实，加不加top对查询并没有影响。后面的测试证实了这一点。因为将top　1000 去掉后，清除过程计划缓存，仍然得出相同的计划结果。
测试语句：

1. declare @d datetime  
2. set @d=getdate()  
3.  
4. exec GetTop100RecordsByRange 1000000,10001000 
5.  
6. select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())  

此时，由于SQL Server默认为主键PID创建了聚集索引，查询速度比较理想，平均为0-16毫秒之间，更接近于0

查询计划也如我所料：

 而如果以Pguid作为聚集索引键，查询计划如下：

如果以AddTime作为聚集索引键，查询计划：

 三、修改聚集索引，以检查查询速度

1. /*删除系统自动创建的聚集索引 
2. */ 
3. ALTER TABLE [dbo].[bigTable] DROP CONSTRAINT [PK__bigTable__7C8480AE] 
4. go 
5.  
6.  
7. /*创建一个非聚集索引 
8. 在PID和addtime字段 
9. */ 
10. CREATE NONCLUSTERED INDEX bigTable_NoClusIdx 
11. ON [bigTable]([AddTime] ASC,[PID] ASC); 
12. go 
13. DROP Index [bigTable_NoClusIdx] on dbo.[bigTable] 
14.  
15.  
16. /*创建一个非聚集索引 
17. 在PID字段 
18. */ 
19.  
20. Create NONCLUSTERED INDEX bigTable_NoclusIdx 
21. ON [bigTable](PID); 
22. go 
23.  
24. DROP Index [bigTable_NoClusIdx] on dbo.[bigTable] 
25.  
26. /*创建一个非聚集索引 
27. 在AddTime字段 
28. */ 
29. CREATE NONCLUSTERED INDEX bigTable_NoclusIdx 
30. ON [bigTable](AddTime); 
31. go 
32.  
33. DROP Index [bigTable_NoClusIdx] on dbo.[bigTable] 
34.  
35. /*创建一个非聚集索引 
36. 在GUID字段 
37. */ 
38.  
39. CREATE NONCLUSTERED INDEX bigTable_NoclusIdx 
40. ON [bigTable](PGuid); 
41. go 
42.  
43. DROP Index [bigTable_NoClusIdx] on dbo.[bigTable] 
44.  
45.  
46. /*创建一个聚集索引 
47. 在GUID字段 
48. */ 
49.  
50. CREATE CLUSTERED INDEX bigTable_ClusIdx 
51. ON [bigTable](PGuid); 
52. go 
53.  
54. DROP Index [bigTable_ClusIdx] on dbo.[bigTable] 
55.  
56. /*创建一个聚集索引 
57. 在addTime字段 
58. */ 
59. CREATE CLUSTERED INDEX bigTable_ClusIdx 
60. ON [bigTable](AddTime); 
61. go 
62.  
63. DROP Index [bigTable_ClusIdx] on dbo.[bigTable] 
64.  
65.  
66. /*创建一个聚集索引 
67. 在PID字段 
68. */ 
69. CREATE CLUSTERED INDEX bigTable_ClusIdx 
70. ON [bigTable](PID); 
71. go 

测试结果有些令我意外：
1、在没有聚集索引的前提下，无论在GUID,AddTime,PID创建非聚集索引，查询的速度均相差甚远。平均在200毫秒以上，并且此时每次查询均在10秒以上。这与查询的计划缓存有关。
2、在创建聚集索引时，性能PID>AddTime>PGuid,但总体相差不明显。
四、检查索引存储内部
使用微软未公开的一个命令DBCC IND

1. DBCC IND (HugeData_10Millons, bigTable, -1); 

结果约有21万个数据页：(211985 row(s) affected)

为了更方便找出根页(Root Page),我们使用一个表来存放DBCC IND的查询结果：

1. IF OBJECTPROPERTY(object_id('sp_tablepages'), 'IsUserTable') IS NOT NULL 
2.     DROP TABLE sp_tablepages; 
3. go 
4.  
5. CREATE TABLE sp_tablepages 
6. ( 
7.     PageFID         tinyint, 
8.     PagePID         int, 
9.     IAMFID          tinyint, 
10.     IAMPID          int, 
11.     ObjectID        int, 
12.     IndexID         tinyint, 
13.     PartitionNumber tinyint, 
14.     PartitionID     bigint, 
15.     iam_chain_type  varchar(30), 
16.     PageType        tinyint, 
17.     IndexLevel      tinyint, 
18.     NextPageFID     tinyint, 
19.     NextPagePID     int, 
20.     PrevPageFID     tinyint, 
21.     PrevPagePID     int, 
22.     CONSTRAINT sp_tablepages_PK 
23.         PRIMARY KEY (PageFID, PagePID) 
24. ); 
25. go 
26. --TRUNCATE TABLE sp_tablepages; 
27. INSERT sp_tablepages 
28. EXEC ('DBCC IND (HugeData_10Millons, bigTable, 1)'); 
29. go 
30.  
31. SELECT IndexLevel 
32.     , PageFID 
33.     , PagePID 
34.     , PrevPageFID 
35.     , PrevPagePID 
36.     , NextPageFID 
37.     , NextPagePID 
38. FROM sp_tablepages 
39. ORDER BY IndexLevel DESC, PrevPagePID; 
40. GO  

假定我们要找PID为100000的记录。附查找过程如下：

小结：
1、一个聚集索引的叶级正好就是数据自身，所以当一个聚集索引被创建时，表中数据被复制并依据聚集键排序，聚集索引被逻辑维护而不是物理维护。这样，查询时通过逻辑扫描可以很快找到某行所在的索引页，进而找出连续的1000条记录所在的页。
2、对于一个非聚集索引来说，如果是Heap，行的标识就是它们的物理行标识(RID)；如果是聚集表，则为聚集健，这个值称为书签值(bookmaark value)，它和索引键、包含性列一起组成了非聚集索引的叶级。另外，在B树查找非叶级的页时，将可能不得不通过指向子页的指针进行物理定位，这可能会增加查询的时间。还有，非聚集索引仅仅包含被索引定义的数据，对于没有在索引中定义的数据，可能需要在物理行进行一个书签查找(bookmark lookup)。
3、对于大数据量的查询，建立聚集索引是必须的。如果查询以ID序列为主，可以直接在标识列建立聚集索引。如果查询以时间段为主，则可以考虑用时间和标识列建聚集索引。
以上结论谨供参考，欢迎交流。