您的位置:知识库 » 数据库

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

作者: 邀月  来源: 博客园  发布时间: 2010-08-29 19:56  阅读: 2609 次  推荐: 0   原文链接   [收藏]  
摘要:一张上千万数据的表,结构很简单:ID是自增的,你怎么快速读取其中指定的某1000条数据,比如100万到100万零1000?怎么做呢?看下文。
[1] 千万数据的连续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的记录。附查找过程如下:

邀月工作室http://nfnhgq.blu.livefilestore.com/y1p6LlHd2IySTDYOYmb31ic0ix6cKEh160UDrt_iS5eQRUDVbGpD7QOpgqHpHM9cdIsWKn08i5wU-8Y5cxvR_cKrLddPXBX1Z8r/2010-08-27%2017-35-41.png?psid=1

邀月工作室

邀月工作室

  小结:

  1、一个聚集索引的叶级正好就是数据自身,所以当一个聚集索引被创建时,表中数据被复制并依据聚集键排序,聚集索引被逻辑维护而不是物理维护。这样,查询时通过逻辑扫描可以很快找到某行所在的索引页,进而找出连续的1000条记录所在的页。

  2、对于一个非聚集索引来说,如果是Heap,行的标识就是它们的物理行标识(RID);如果是聚集表,则为聚集健,这个值称为书签值(bookmaark value),它和索引键、包含性列一起组成了非聚集索引的叶级。另外,在B树查找非叶级的页时,将可能不得不通过指向子页的指针进行物理定位,这可能会增加查询的时间。还有,非聚集索引仅仅包含被索引定义的数据,对于没有在索引中定义的数据,可能需要在物理行进行一个书签查找(bookmark lookup)。

  3、对于大数据量的查询,建立聚集索引是必须的。如果查询以ID序列为主,可以直接在标识列建立聚集索引。如果查询以时间段为主,则可以考虑用时间和标识列建聚集索引。

  以上结论谨供参考,欢迎交流。

  关于索引的物理存储与查询,请查看:《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(3)

[第1页][第2页]
0
0
 
标签:SQL Server

数据库热门文章

    数据库最新文章

      最新新闻

        热门新闻