资源描述：
《sql开发中容易忽视的一些小地方(四)》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。
1、SQL开发中容易忽视的一些小地方(四)>>教育资源库 非聚集索引结构: 1:非聚集索引与聚集索引具有相同的B树结构，它们之间的显著差别在于以下两点： *基础表的数据行不按非聚集键的顺序排序和存储。 *非聚集索引的叶层是由索引页而不是由数据页组成。 2:非聚集索引行中的行定位器或是指向行的指针，或是行的聚集索引键，如下所述： *如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针。该指针由文件标识符(ID)、页码和页上的行数生成。整个指针称为行ID(RID)。 *如果表有聚集索引或
2、索引视图上有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQLServer将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQLServer通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。 网络观点:orderby子句中使用了的列,可以在此列上建非聚集索引以提高查询速度. 原文地址:blog10697_1221.htm 本人观点:总之一句话,环境不同,表结构不同,数据分
3、布不同,最终结果也不一定相同. 案例:本人最近做一个项目时有两个大表关联,都接近千万.一个表是订单表order,另一个是会员表member,订单中有一字段create_date:类型为datatime,其中的值都是不相同且唯一,而且并不连续,下面是一些值: create_date -----------------------2008-10-0504:00:56.0002008-10-0503:55:55.0002008-10-0503:55:42.0002008-10-0503:54:40.000
4、2008-10-0503:54:32.0002008-10-0503:54:23.0002008-10-0503:47:16.0002008-10-0503:46:08.0002008-10-0503:42:28.0002008-10-0503:42:09.000 订单表和会员表有一个关联字段为proxyID,各自均建有索引.查询语句如下: select*fromorderinnerjoinmemberonorder.proxyID=member.proxyID emer表进行了大量的表扫描.835
5、88次. 情况二:删除create_date上的索引,按理来说应该会比有索引会慢些,下面是执行的IO和时间消耗图: 对此我有以下发现: 1:orderby字段没有创建索引的情况下,对member表只扫描了9次.远少于创建索引时的83588次. 2:还有一个现象就是如果按在查询分析器中全部显示出数据来看,没有创建索引最终所用时更少. 3:创建索引的查询会比没有创建索引的查询早一步显示数据,不过最终完成的时间要长. 测试未知难题: 1:就查询速度来说,是早一步在查询分析器中显示数据的查询
6、快还是说要看最终完全的时间来判断.(create_date创建索引的情况会更早显示数据,不过总共用时会比不创建索引的慢)园友zping曾告诉我不要看时间要看IO数量.不知道大家是怎么分析的. 2:在一个字段上创建索引为什么会引发member表的多次表扫描. 测试说明:由于SQL2005有缓存功能,所有两次查询的时间段并不相同,但数据量都差不多. 根据园友perfectdesign的观点,orderby时,如果字段是聚集索引将会是最优的,这点我个人以及MSDN都同意,奇怪的是,上面的语句中,leav
7、e_date上即聚集索引,然后orderbyleave_datedesc,然而也会产生5万多次的member表扫描,好像是orderby索引字段,无论是聚集还是非聚集都会大量增加对member表的扫描.真是百思不得其解.下面是详细的ID情况: (2000roember'.Scancount52796,logicalreads234885,physicalreads0,read-aheadreads3687,loblogicalreads0,lobphysicalreads0,lobread-ah
8、eadreads0. Table'v_hotel'.Scancount1,logicalreads3121,physicalreads0,read-aheadreads28,loblogicalreads0,lobphysicalreads0,lobread-aheadreads0. 测试结论:这种情况足以说明对于orderby字段创建索引并不一定能发挥非聚集索引的优势,至于其中原因本人不才,目前并无答案,
