千万数据的连续ID表，快速读取其中指定的某1000条数据？
[2] 千万数据的连续ID表，快速读取其中指定的某1000条数据？
三、修改聚集索引，以检查查询速度
/*删除系统自动创建的聚集索引
*/
ALTER TABLE [dbo].[bigTable] DROP CONSTRAINT [PK__bigTable__7C8480AE]
go
/*创建一个非聚集索引
在PID和addtime字段
*/
CREATE NONCLUSTERED INDEX bigTable_NoClusIdx
ON [bigTable]([AddTime] ASC,[PID] ASC);
go
DROP Index [bigTable_NoClusIdx] on dbo.[bigTable]
/*创建一个非聚集索引
在PID字段
*/
Create NONCLUSTERED INDEX bigTable_NoclusIdx
ON [bigTable](PID);
go
DROP Index [bigTable_NoClusIdx] on dbo.[bigTable]
/*创建一个非聚集索引
在AddTime字段
*/
CREATE NONCLUSTERED INDEX bigTable_NoclusIdx
ON [bigTable](AddTime);
go
DROP Index [bigTable_NoClusIdx] on dbo.[bigTable]
/*创建一个非聚集索引
在GUID字段
*/
CREATE NONCLUSTERED INDEX bigTable_NoclusIdx
ON [bigTable](PGuid);
go
DROP Index [bigTable_NoClusIdx] on dbo.[bigTable]
/*创建一个聚集索引
在GUID字段
*/
CREATE CLUSTERED INDEX bigTable_ClusIdx
ON [bigTable](PGuid);
go
DROP Index [bigTable_ClusIdx] on dbo.[bigTable]
/*创建一个聚集索引
在addTime字段
*/
CREATE CLUSTERED INDEX bigTable_ClusIdx
ON [bigTable](AddTime);
go
DROP Index [bigTable_ClusIdx] on dbo.[bigTable]
/*创建一个聚集索引
在PID字段
*/
CREATE CLUSTERED INDEX bigTable_ClusIdx
ON [bigTable](PID);
go
测试结果有些令我意外：
1、在没有聚集索引的前提下，无论在GUID,AddTime,PID创建非聚集索引，查询的速度均相差甚远。平均在200毫秒以上，并且此时每次查询均在10秒以上。这与查询的计划缓存有关。
2、在创建聚集索引时，性能PID>AddTime>PGuid,但总体相差不明显。
四、检查索引存储内部
使用微软未公开的一个命令DBCC IND
DBCC IND (HugeData_10Millons, bigTable, -1);
结果约有21万个数据页：(211985 row(s) affected)
为了更方便找出根页(Root Page),我们使用一个表来存放DBCC IND的查询结果：
IF OBJECTPROPERTY(object_id('sp_tablepages'), 'IsUserTable') IS NOT NULL
DROP TABLE sp_tablepages;
go
CREATE TABLE sp_tablepages
(
PageFID tinyint,
PagePID int,
IAMFID tinyint,
IAMPID int,
ObjectID int,
IndexID tinyint,
PartitionNumber tinyint,
PartitionID bigint,
iam_chain_type varchar(30),
PageType tinyint,
IndexLevel tinyint,
NextPageFID tinyint,
NextPagePID int,
PrevPageFID tinyint,
PrevPagePID int,
CONSTRAINT sp_tablepages_PK
PRIMARY KEY (PageFID, PagePID)
);
go
--TRUNCATE TABLE sp_tablepages;
INSERT sp_tablepages
EXEC ('DBCC IND (HugeData_10Millons, bigTable, 1)');
go
SELECT IndexLevel
, PageFID
, PagePID
, PrevPageFID
, PrevPagePID
, NextPageFID
, NextPagePID
FROM sp_tablepages
ORDER BY IndexLevel DESC, PrevPagePID;
GO
假定我们要找PID为100000的记录。附查找过程如下：
小结：
1、一个聚集索引的叶级正好就是数据自身，所以当一个聚集索引被创建时，表中数据被复制并依据聚集键排序，聚集索引被逻辑维护而不是物理维护。这样，查询时通过逻辑扫描可以很快找到某行所在的索引页，进而找出连续的1000条记录所在的页。
2、对于一个非聚集索引来说，如果是Heap，行的标识就是它们的物理行标识(RID)；如果是聚集表，则为聚集健，这个值称为书签值(bookmaark value)，它和索引键、包含性列一起组成了非聚集索引的叶级。另外，在B树查找非叶级的页时，将可能不得不通过指向子页的指针进行物理定位，这可能会增加查询的时间。还有，非聚集索引仅仅包含被索引定义的数据，对于没有在索引中定义的数据，可能需要在物理行进行一个书签查找(bookmark lookup)。
3、对于大数据量的查询，建立聚集索引是必须的。如果查询以ID序列为主，可以直接在标识列建立聚集索引。如果查询以时间段为主，则可以考虑用时间和标识列建聚集索引。
以上结论谨供参考，欢迎交流。
关于索引的物理存储与查询，请查看：《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(3)
