SQLServer索引结构及其使用（四）

聚集索引的重要性以及如何选择聚集索引

在最后一节的标题中，作者写道:实现小数据和海量数据的通用分页显示存储过程。这是因为当这个存储过程应用到“办公自动化”系统的实践中时，作者发现这个第三存储过程在数据量很小的情况下有以下现象:

1.寻呼速度一般保持在1秒到3秒之间。

2.查询最后一页时，速度一般在5秒到8秒，即使分页总数只有3页或者30万页。

虽然在超大容量的情况下分页过程很快，但是在划分前几页的时候，1-3秒的速度甚至比第一种连优化都没有的分页方式还要慢。用用户的话来说就是“还不如ACCESS数据库快”，足以导致用户放弃使用你开发的系统。
笔者对此进行了分析，事实证明这种现象的症结如此简单，却又如此重要:排序后的字段并不是聚集索引！
本文标题为《查询优化与分页算法方案》。我只是把“查询优化”和“分页算法”这两个话题放在一起，因为它们都需要一个非常重要的东西——聚集索引。
正如我们在前面的讨论中提到的，聚集索引有两个优点:

1.尽快缩小查询范围。

2.尽快对字段进行排序。

第1条主要用于查询优化，而第2条主要用于分页期间的数据排序。

但是，每个表中只能建立一个聚集索引，这使得聚集索引更加重要。索引的选择可以说是实现“查询优化”和“高效分页”的最关键因素。
但是让聚集索引列同时满足查询列和排序列的需求通常是一对矛盾。在前面关于“指数”的讨论中，以fariqi，即用户发帖的日期作为聚合指数的起始列，日期的精度为“日”。这种方式的优点，如前所述，比在时分快速查询中使用ID主键列有很大的优势。
但是在分页时，由于该聚集索引列中存在重复记录，因此max或min不能作为分页的参考，从而无法实现更高效的排序。但是，如果将ID主键列用作聚集索引，那么聚集索引除了排序之外没有任何用处，实际上浪费了聚集索引的宝贵资源。
为了解决这个矛盾，作者后来添加了一个日期列，默认值为getdate()。当用户写入记录时，该列自动写入当前时间，精确到毫秒。尽管如此，为了避免不太可能的巧合，应该在这个列上创建一个唯一的约束。将此日期列用作聚集索引列。
有了这个基于时间的聚集索引列，用户在插入数据时，不仅可以用这个列来查找某个时间段的查询，还可以把它作为一个列来实现max或min，成为分页算法的参考。
经过这样的优化，笔者发现无论是大数据量还是小数据量，分页速度一般都是几十毫秒，甚至0ms。然而，通过日期字段缩小范围的查询速度并不比以前慢。聚集索引如此重要和珍贵，所以作者总结了一下，它必须建立在:

1.在您最常用的字段上缩小查询范围；

2.在您最常用且需要排序的字段上。

结束语

本文收集了作者近期使用数据库的经验，是作者在“办公自动化”系统中实践经验的积累。希望这篇文章不仅能给大家的工作带来一些帮助，也能让大家认识到分析问题的方法。最重要的是，希望本文能抛砖引玉，引起大家学习讨论的兴趣，共同为公安科技强警事业和金盾工程做出自己的努力。
最后需要说明的是，在实验中，我发现在用户查询大量数据时，影响数据库速度的不是内存大小，而是CPU。当我在我的P4 2.4机器上试用时，查看“资源管理器”，我发现CPU经常保持在100%，但内存使用率没有变化或变化很大。即使在我们的HP ML 350 G3服务器上测试，CPU峰值也能达到90%，一般持续在70%左右。
本文中的实验数据均来自我们的HP ML 350服务器。服务器配置:双Inter Xeon超线程CPU 2.4G，内存1G，操作系统Windows Server 2003企业版，数据库SQL Server 2000 SP3
从上表可以看出，三个存储过程在执行100页以下的分页命令时，都是可以信任的，并且有不错的速度。然而，在第一种方案中，在分页超过1000页之后，速度下降。第二种方案是分页超过10000页后速度开始变慢。但第三方案一直没有大的下跌趋势，后劲还是很足的。
确定了第三种分页方案后，我们就可以根据它编写一个存储过程了。众所周知，SQL SERVER的存储过程是预先编译好的SQL语句，其执行效率比通过网页传输的SQL语句要高。下面的存储过程不仅包含分页方案，还根据来自页面的参数确定是否对总数据进行计数。

-获取指定页面的数据:
创建过程分页3

@ tblname varchar (255)，-表名

@ strgetfields varchar (1000) ='' * '，-要返回的列

@ fldname varchar (255) = ' ' '，-排序的字段名称

@ pagesizeint = 10，-页面大小

@ pageindexint = 1，-页码

@ docount bit = 0，-返回记录总数，否则不为0则返回。

@ ordertypebit = 0，-设置排序类型，非零值按降序排列。

@ strhere varchar(1500)= ' ' '-查询条件(注意:不要加where)

如同

declare @ strsqlvarchar(5000)-主语句

declare @ strtmpvarchar(110)-临时变量

declare @ strordevarchar(400)-排序类型

if @doCount！= 0

开始

if @strWhere！=''''

set @ strSQL = " select count(*)as Total from["+@ TB lname+"]where "+@ str where

其他

set @ strSQL = " select count(*)as Total from["+@ TB lname+"]"

end
-上面的代码意味着如果@doCount传递非0的值，将执行总计数。以下所有代码都是@doCount为0的情况:

其他

开始

if @OrderType！= 0

开始

set @strTmp = "

set @ stroder = " order by["+@ fld name+"]desc "
-如果@OrderType不为0，则执行降序。这句话很重要！

目标

其他

开始

set @strTmp = " >(选择最大值"

set @ strOrder = " order by["+@ fld name+"]ASC "

目标

如果@PageIndex = 1

开始

if @strWhere！= ''''

set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+"

from [" + @tblName + "]其中"+ @strWhere + " " + @strOrder

其他

set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+"

from["+@ TB lname+"]"+@ stroder
-如果是第一页，执行上面的代码，会加快执行速度。

目标

其他

begin
-下面的代码为@strSQL提供了实际执行的SQL代码

set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+" from["

+ @tblName + "]其中["+@ fld name+"]"+@ strTmp+"(["+@ fld name+"])

from(select top "+str((@ PageIndex-1)* @ PageSize)+"["+@ fld name+"]

from["+@ TB lname+"]"+@ strOrder+")as TBL tmp)"+@ strOrder

if @strWhere！= ''''

set @ strSQL = " select top "+str(@ PageSize)+" "+@ strGetFields+" from["

+ @tblName + "]其中[" + @fldName + "]" + @strTmp + "(["

+@ fldName+"])from(select top "+str((@ PageIndex-1)* @ PageSize)+"["

+@ fld name+"]from["+@ TB lname+"]where "+@ str where+" "

+ @strOrder +")作为tblTmp)和"+ @strWhere + " " + @strOrder

目标

目标

exec (@strSQL)

去

上面的存储过程是一般的存储过程，它的注释已经写在里面了。在数据量较大的情况下，尤其是查询最后几页时，查询时间一般不会超过9秒；但是使用其他存储过程在实践中会导致超时，所以这个存储过程非常适合查询大容量的数据库。希望通过以上存储过程的分析，能给大家带来一些启示，提高工作效率，也希望同事们能提出更好的实时数据分页算法。