有这样一个需求:一张上千万数据的表,结构很简单:ID是自增的,你怎么快速读取其中指定的某1000条数据,比如100万到100万零1000?
这个需求其实很简单,因为是自增型ID,可能分两种状况:有聚集索引或Heap,如果是后者,我想用ID和新增时间组建非聚集索引。效果应该相差不大。
于是动手,过程如下:
一、准备测试数据:
基本测试环境:
插入1000万测试数据:
完成后,数据文件大小如下:
二、创建一个存储过程用于测试:
邀月说明:其实,加不加top对查询并没有影响。后面的测试证实了这一点。
测试语句:
此时,由于SQL Server默认为主键PID创建了聚集索引,查询速度比较理想,平均为0-16毫秒之间,更接近于0
查询计划也如我所料:
而如果以Pguid作为聚集索引键,查询计划如下:
如果以AddTime作为聚集索引键,查询计划:
三、修改聚集索引,以检查查询速度
测试结果有些令我意外:
1、在没有聚集索引的前提下,无论在GUID,AddTime,PID创建非聚集索引,查询的速度均相差甚远。平均在200毫秒以上,并且此时每次查询均在10秒以上。这与查询的计划缓存有关。
2、在创建聚集索引时,性能PID>AddTime>PGuid,但总体相差不明显。
四、检查索引存储内部
使用微软未公开的一个命令DBCC IND
结果约有21万个数据页:(211985 row(s) affected)
为了更方便找出根页(Root Page),我们使用一个表来存放DBCC IND的查询结果:
假定我们要找PID为100000的记录。附查找过程如下:
小结:
1、一个聚集索引的叶级正好就是数据自身,所以当一个聚集索引被创建时,表中数据被复制并依据聚集键排序,聚集索引被逻辑维护而不是物理维护。这样,查询时通过逻辑扫描可以很快找到某行所在的索引页,进而找出连续的1000条记录所在的页。
2、 对于一个非聚集索引来说,如果是Heap,行的标识就是它们的物理行标识(RID);如果是聚集表,则为聚集健,这个值称为书签值(bookmaark value),它和索引键、包含性列一起组成了非聚集索引的叶级。另外,在B树查找非叶级的页时,将可能不得不通过指向子页的指针进行物理定位,这可能会 增加查询的时间。还有,非聚集索引仅仅包含被索引定义的数据,对于没有在索引中定义的数据,可能需要在物理行进行一个书签查找(bookmark lookup)。
3、对于大数据量的查询,建立聚集索引是必须的。如果查询以ID序列为主,可以直接在标识列建立聚集索引。如果查询以时间段为主,则可以考虑用时间和标识列建聚集索引。
以上结论谨供参考,欢迎交流。
关于索引的物理存储与查询,请查看:《Microsoft Sql server 2008 Internals》读书笔记--第六章Indexes:Internals and Management(3)
助人等于自助! 3w@live.cn