如何让你的SQL运行得更快 [推荐！！！]
人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略
了不同的实现方法之间可能存在的性能差异，这种性能差异在大型的或是复杂的数据库
环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明显。笔者在工作实践
中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的whe
re子句。在对它们进行适当的优化后，其运行速度有了明显地提高！下面我将从这三个
方面分别进行总结：
---- 为了更直观地说明问题，所有实例中的SQL运行时间均经过测试，不超过１秒的均
表示为（< 1秒）。
---- 测试环境--
---- 主机：HP LH II
---- 主频：330MHZ
---- 内存：128兆
---- 操作系统：Operserver5.0.4
----数据库：Sybase11.0.3
一、不合理的索引设计
----例：表record有620000行，试看在不同的索引下，下面几个 SQL的运行情况：
---- 1.在date上建有一非个群集索引
select count(*) from record where date >
'19991201' and date < '19991214'and amount >
2000 (25秒)
select date,sum(amount) from record group by date
(55秒)
select count(*) from record where date >
'19990901' and place in ('BJ','SH') (27秒)
---- 分析：
----date上有大量的重复值，在非群集索引下，数据在物理上随机存放在数据页上，在
范围查找时，必须执行一次表扫描才能找到这一范围内的全部行。
---- 2.在date上的一个群集索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000 （14秒）
select date,sum(amount) from record group by date
（28秒）
select count(*) from record where date >
'19990901' and place in ('BJ','SH')（14秒）
---- 分析：
---- 在群集索引下，数据在物理上按顺序在数据页上，重复值也排列在一起，因而在范
围查找时，可以先找到这个范围的起末点，且只在这个范围内扫描数据页，避免了大范
围扫描，提高了查询速度。
---- 3.在place，date，amount上的组合索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000 （26秒）
select date,sum(amount) from record group by date
（27秒）
select count(*) from record where date >
'19990901' and place in ('BJ', 'SH')（< 1秒）
---- 分析：
---- 这是一个不很合理的组合索引，因为它的前导列是place，第一和第二条SQL没有引
用place，因此也没有利用上索引；第三个SQL使用了place，且引用的所有列都包含在组
合索引中，形成了索引覆盖，所以它的速度是非常快的。
---- 4.在date，place，amount上的组合索引
select count(*) from record where date >
'19991201' and date < '19991214' and amount >
2000(< 1秒)
select date,sum(amount) from record group by date
（11秒）
select count(*) from record where date >
'19990901' and place in ('BJ','SH')（< 1秒）
---- 分析：
---- 这是一个合理的组合索引。它将date作为前导列，使每个SQL都可以利用索引，并
且在第一和第三个SQL中形成了索引覆盖，因而性能达到了最优。
---- 5.总结：
---- 缺省情况下建立的索引是非群集索引，但有时它并不是最佳的；合理的索引设计要
建立在对各种查询的分析和预测上。一般来说：
---- ①.有大量重复值、且经常有范围查询
（between, >,< ，>=,< =）和order by
、group by发生的列，可考虑建立群集索引；
---- ②.经常同时存取多列，且每列都含有重复值可考虑建立组合索引；
---- ③.组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。
二、不充份的连接条件：
---- 例：表card有7896行，在card_no上有一个非聚集索引，表account有191122行，在
account_no上有一个非聚集索引，试看在不同的表连接条件下，两个SQL的执行情况：
select sum(a.amount) from account a,
card b where a.card_no = b.card_no（20秒）
---- 将SQL改为：
select sum(a.amount) from account a,
card b where a.card_no = b.card_no and a.
account_no=b.account_no（< 1秒）
---- 分析：
---- 在第一个连接条件下，最佳查询方案是将account作外层表，card作内层表，利用
card上的索引，其I/O次数可由以下公式估算为：
---- 外层表account上的22541页+（外层表account的191122行*内层表card上对应外层
表第一行所要查找的3页）=595907次I/O
---- 在第二个连接条件下，最佳查询方案是将card作外层表，account作内层表，利用
account上的索引，其I/O次数可由以下公式估算为：
---- 外层表card上的1944页+（外层表card的7896行*内层表account上对应外层表每一
行所要查找的4页）= 33528次I/O
---- 可见，只有充份的连接条件，真正的最佳方案才会被执行。
---- 总结：
---- 1.多表操作在被实际执行前，查询优化器会根据连接条件，列出几组可能的连接方
案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的
表；内外表的选择可由公式：外层表中的匹配行数*内层表中每一次查找的次数确定，乘
积最小为最佳方案。
---- 2.查看执行方案的方法-- 用set showplanon，打开showplan选项，就可以看到连
接顺序、使用何种索引的信息；想看更详细的信息，需用sa角色执行dbcc(3604,310,30
2)。
三、不可优化的where子句
---- 1.例：下列SQL条件语句中的列都建有恰当的索引，但执行速度却非常慢：
select * from record where
substring(card_no,1,4)='5378'(13秒)
select * from record where
amount/30< 1000（11秒）
select * from record where
convert(char(10),date,112)='19991201'（10秒）
---- 分析：
---- where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的，因此它不得不
进行表搜索，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么
就可以被SQL优化器优化，使用索引，避免表搜索，因此将SQL重写成下面这样：
select * from record where card_no like
'5378%'（< 1秒）
select * from record where amount
< 1000*30（< 1秒）
select * from record where date= '1999/12/01'
（< 1秒）
---- 你会发现SQL明显快起来！
---- 2.例：表stuff有200000行，id_no上有非群集索引，请看下面这个SQL：
select count(*) from stuff where id_no in('0','1')
（23秒）
---- 分析：
---- where条件中的'in'在逻辑上相当于'or'，所以语法分析器会将in ('0','1')转化
为id_no ='0' or id_no='1'来执行。我们期望它会根据每个or子句分别查找，再将结果
相加，这样可以利用id_no上的索引；但实际上（根据showplan）,它却采用了"OR策略"
，即先取出满足每个or子句的行，存入临时数据库的工作表中，再建立唯一索引以去掉
重复行，最后从这个临时表中计算结果。因此，实际过程没有利用id_no上索引，并且完
成时间还要受tempdb数据库性能的影响。
---- 实践证明，表的行数越多，工作表的性能就越差，当stuff有620000行时，执行时
间竟达到220秒！还不如将or子句分开：
select count(*) from stuff where id_no='0'
select count(*) from stuff where id_no='1'
---- 得到两个结果，再作一次加法合算。因为每句都使用了索引，执行时间只有3秒，
在620000行下，时间也只有4秒。或者，用更好的方法，写一个简单的存储过程：
create proc count_stuff as
declare @a int
declare @b int
declare @c int
declare @d char(10)
begin
select @a=count(*) from stuff where id_no='0'
select @b=count(*) from stuff where id_no='1'
end
select @c=@a+@b
select @d=convert(char(10),@c)
print @d
---- 直接算出结果，执行时间同上面一样快！
---- 总结：
---- 可见，所谓优化即where子句利用了索引，不可优化即发生了表扫描或额外开销。
---- 1.任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时
要尽可能将操作移至等号右边。
---- 2.in、or子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把
子句拆开；拆开的子句中应该包含索引。
---- 3.要善于使用存储过程，它使SQL变得更加灵活和高效。
---- 从以上这些例子可以看出，SQL优化的实质就是在结果正确的前提下，用优化器可
以识别的语句，充份利用索引，减少表扫描的I/O次数，尽量避免表搜索的发生。其实S
QL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会
涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。
1．合理使用索引
索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处，其使用原则如下：
●在经常进行连接，但是没有指定为外键的列上建立索引，而不经常连接的字段则由优化器自动生成索引。
●在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就无必要建立索引。如果建立索引不但不会提高查询效率，反而会严重降低更新速度。
●如果待排序的列有多个，可以在这些列上建立复合索引（compound index）。
●使用系统工具。如Informix数据库有一个tbcheck工具，可以在可疑的索引上进行检查。在一些数据库服务器上，索引可能失效或者因为频繁操作而使得读取效率降低，如果一个使用索引的查询不明不白地慢下来，可以试着用tbcheck工具检查索引的完整性，必要时进行修复。另外，当数据库表更新大量数据后，删除并重建索引可以提高查询速度。
2．避免或简化排序
应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时，优化器就避免了排序的步骤。以下是一些影响因素：
●索引中不包括一个或几个待排序的列；
●group by或order by子句中列的次序与索引的次序不一样；
●排序的列来自不同的表。
为了避免不必要的排序，就要正确地增建索引，合理地合并数据库表（尽管有时可能影响表的规范化，但相对于效率的提高是值得的）。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。
3．消除对大型表行数据的顺序存取
在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号”这个连接字段上建立索引。
还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作：
SELECT ＊ FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008
虽然在customer_num和order_num上建有索引，但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合，所以应该改为如下语句：
SELECT ＊ FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT ＊ FROM orders WHERE order_num=1008
这样就能利用索引路径处理查询。
4．避免相关子查询
一个列的标签同时在主查询和where子句中的查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越多，效率越低，因此应当尽量避免子查询。如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
5．避免困难的正规表达式
MATCHES和LIKE关键字支持通配符匹配，技术上叫正规表达式。但这种匹配特别耗费时间。例如：SELECT ＊ FROM customer WHERE zipcode LIKE “98_ _ _”
即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT ＊ FROM customer WHERE zipcode >“98000”，在执行查询时就会利用索引来查询，显然会大大提高速度。
另外，还要避免非开始的子串。例如语句：SELECT ＊ FROM customer WHERE zipcode[2，3] >“80”，在where子句中采用了非开始子串，因而这个语句也不会使用索引。
6．使用临时表加速查询
把表的一个子集进行排序并创建临时表，有时能加速查询。它有助于避免多重排序操作，而且在其他方面还能简化优化器的工作。例如：
SELECT cust.name，rcvbles.balance，……other columns
FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
AND cust.postcode>“98000”
ORDER BY cust.name
如果这个查询要被执行多次而不止一次，可以把所有未付款的客户找出来放在一个临时文件中，并按客户的名字进行排序：
SELECT cust.name，rcvbles.balance，……other columns
FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
ORDER BY cust.name
INTO TEMP cust_with_balance
然后以下面的方式在临时表中查询：
SELECT ＊ FROM cust_with_balance
WHERE postcode>“98000”
临时表中的行要比主表中的行少，而且物理顺序就是所要求的顺序，减少了磁盘I/O，所以查询工作量可以得到大幅减少。
注意：临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下，注意不要丢失数据。
7．用排序来取代非顺序存取
非顺序磁盘存取是最慢的操作，表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况，使得我们在写应用程序时很容易写出要求存取大量非顺序页的查询。
有些时候，用数据库的排序能力来替代非顺序的存取能改进查询。
3.优化 tempdb 性能
对 tempdb 数据库的物理位置和数据库选项设置的一般建议包括：
使 tempdb 数据库得以按需自动扩展。这确保在执行完成前不终止查询，该查询所生成的存储在 tempdb 数据库内的中间结果集比预期大得多。
将 tempdb 数据库文件的初始大小设置为合理的大小，以避免当需要更多空间时文件自动扩展。如果 tempdb 数据库扩展得过于频繁，性能会受不良影响。
将文件增长增量百分比设置为合理的大小，以避免 tempdb 数据库文件按太小的值增长。如果文件增长幅度与写入 tempdb 数据库的数据量相比太小，则 tempdb 数据库可能需要始终扩展，因而将妨害性能。
将 tempdb 数据库放在快速 I/O 子系统上以确保好的性能。在多个磁盘上条带化 tempdb 数据库以获得更好的性能。将 tempdb 数据库放在除用户数据库所使用的磁盘之外的磁盘上。有关更多信息，请参见扩充数据库。
4.优化服务器:
使用内存配置选项优化服务器性能
Microsoft® SQL Server™ 2000 的内存管理组件消除了对 SQL Server 可用的内存进行手工管理的需要。SQL Server 在启动时根据操作系统和其它应用程序当前正在使用的内存量，动态确定应分配的内存量。当计算机和SQL Server 上的负荷更改时，分配的内存也随之更改。有关更多信息，请参见内存构架。
下列服务器配置选项可用于配置内存使用并影响服务器性能：
min server memory
max server memory
max worker threads
index create memory
min memory per query
min server memory 服务器配置选项可用于确保 SQL Server 在达到该值后不会释放内存。可以基于 SQL Server 的大小及活动将该配置选项设置为特定的值。如果选择设置此选项，必须为操作系统和其他程序留出足够的内存。如果操作系统没有足够的内存，会向 SQL Server 请求内存，从而导致影响 SQL Server 性能。
max server memory 服务器配置选项可用于：在 SQL Server 启动及运行时，指定 SQL Server 可以分配的最大内存量。如果知道有多个应用程序与 SQL Server 同时运行，而且想保障这些应用程序有足够的内存运行，可以将该配置选项设置为特定的值。如果这些其它应用程序（如 Web 服务器或电子邮件服务器）只根据需要请求内存，则 SQL Server 将根据需要给它们释放内存，因此不要设置 max server memory 服务器配置选项。然而，应用程序通常在启动时不假选择地使用可用内存，而如果需要更多内存也不请求。如果有这种行为方式的应用程序与 SQL Server 同时运行在相同的计算机上，则将 max server memory 服务器配置选项设置为特定的值，以保障应用程序所需的内存不由 SQL Server 分配出。
不要将 min server memory 和 max server memory 服务器配置选项设置为相同的值，这样做会使分配给 SQL Server 的内存量固定。动态内存分配可以随时间提供最佳的总体性能。有关更多信息，请参见服务器内存选项。
max worker threads 服务器配置选项可用于指定为用户连接到 SQL Server 提供支持的线程数。255 这一默认设置对一些配置可能稍微偏高，这要具体取决于并发用户数。由于每个工作线程都已分配，因此即使线程没有正在使用（因为并发连接比分配的工作线程少），可由其它操作（如高速缓冲存储器）更好地利用的内存资源也可能是未使用的。一般情况下，应将该配置值设置为并发连接数，但不能超过 32727。并发连接与用户登录连接不同。SQL Server 实例的工作线程池只需要足够大，以便为同时正在该实例中执行批处理的用户连接提供服务。如果增加工作线程的数量超过默认值，会降低服务器性能。有关更多信息，请参见max worker threads 选项。
说明 当 SQL Server 运行在 Microsoft Windows® 98 上时，最大工作线程服务器配置选项不起作用。
index create memory 服务器配置选项控制创建索引时排序操作所使用的内存量。在生产系统上创建索引通常是不常执行的任务，通常调度为在非峰值时间执行的作业。因此，不常创建索引且在非峰值时间时，增加该值可提高索引创建的性能。不过，最好将 min memory per query 配置选项保持在一个较低的值，这样即使所有请求的内存都不可用，索引创建作业仍能开始。有关更多信息，请参见 index create memory 选项。
min memory per query 服务器配置选项可用于指定分配给查询执行的最小内存量。当系统内有许多查询并发执行时，增大 min memory per query 的值有助于提高消耗大量内存的查询（如大型排序和哈希操作）的性能。不过，不要将 min memory per query 服务器配置选项设置得太高，尤其是在很忙的系统上，因为查询将不得不等到能确保占有请求的最小内存、或等到超过 query wait 服务器配置选项内所指定的值。如果可用内存比执行查询所需的指定最小内存多，则只要查询能对多出的内存加以有效的利用，就可以使用多出的内存。有关更多信息，请参见 min memory per query 选项和 query wait 选项。
使用 I/O 配置选项优化服务器性能
下列服务器配置选项可用于配置 I/O 的使用并影响服务器性能：
recovery interval
recovery interval 服务器配置选项控制 Microsoft® SQL Server™ 2000 在每个数据库内发出检查点的时间。默认情况下，SQL Server 确定执行检查点操作的最佳时间。然而，若要确定这是否为适当的设置，需要使用 Windows NT 性能监视器监视数据库文件上的磁盘写入活动。导致磁盘利用率达到 100% 的活动尖峰值会妨害性能。若更改该参数以使检查点进程较少出现，通常可以提高这种情况下的总体性能。但仍须继续监视性能以确定新值是否已对性能产生正面影响。有关更多信息，请参见recovery interval 选项。
5.优化数据库文件
分区
将数据库分区可提高其性能并易于维护。通过将一个大表拆分成更小的单个表，只访问一小部分数据的查询可以执行得更快，因为需要扫描的数据较少。而且可以更快地执行维护任务（如重建索引或备份表）。
实现分区操作时可以不拆分表，而将表物理地放置在个别的磁盘驱动器上。例如，将表放在某个物理驱动器上并将相关的表放在与之分离的驱动器上可提高查询性能，因为当执行涉及表之间联接的查询时，多个磁头同时读取数据。可以使用 Microsoft® SQL Server™ 2000 文件组指定将表放置在哪些磁盘上。
硬件分区
硬件分区将数据库设计为利用可用的硬件构架。硬件分区的示例包括：
允许多线程执行的多处理器，使得可以同时执行许多查询。换句话说，在多处理器上可以同时执行查询的各个组件，因此使单个查询的速度更快。例如，查询内引用的每个表可同时由不同的线程扫描。
RAID（独立磁盘冗余阵列）设备允许数据在多个磁盘驱动器中条带化，使更多的读/写磁头同时读取数据，因此可以更快地访问数据。在多个驱动器中条带化的表一般比存储在一个驱动器上的相同的表扫描速度要快。换句话说，将表与相关的表分开存储在不同的驱动器上可以显著提高联接那些表的查询的性能。
水平分区
水平分区将一个表分段为多个表，每个表包含相同数目的列和较少的行。例如，可以将一个包含十亿行的表水平分区成 12 个表，每个小表代表特定年份内一个月的数据。任何需要特定月份数据的查询只引用相应月份的表。
具体如何将表进行水平分区取决于如何分析数据。将表进行分区是为了使查询引用尽可能少的表。否则，查询时须使用过多的 UNION 查询来逻辑合并表，而这会削弱查询性能。有关查询水平分区的表的更多信息，请参见视图使用方案。
常用的方法是根据时期/使用对数据进行水平分区。例如，一个表可能包含最近五年的数据，但是只定期访问本年度的数据。在这种情况下，可考虑将数据分区成五个表，每个表只包含一年的数据。
垂直分区
垂直分区将一个表分段为多个表，每个表包含较少的列。垂直分区的两种类型是规范化和行拆分。
规范化是个标准数据库进程，该进程从表中删除冗余列并将其放到次表中，次表按主键与外键的关系链接到主表。
行拆分将原始表垂直分成多个只包含较少列的表。拆分的表内的每个逻辑行与其它表内的相同逻辑行匹配。例如，联接每个拆分的表内的第十行将重新创建原始行。
与水平分区一样，垂直分区使查询得以扫描较少的数据，因此提高查询性能。例如有一个包含七列的表，通常只引用该表的前四列，那么将该表的后三列拆分到一个单独的表中可获得性能收益。
应谨慎考虑垂直分区操作，因为分析多个分区内的数据需要有联接表的查询，而如果分区非常大将可能影响性能。
（一）深入浅出理解索引结构
实际上，您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。下面，我们举例来说明一下聚集索引和非聚集索引的区别：
其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。
我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。
我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。
通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。
进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。
（二）何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引（很重要）。
动作描述
使用聚集索引
使用非聚集索引
列经常被分组排序
应
应
返回某范围内的数据
应
不应
一个或极少不同值
不应
不应
小数目的不同值
应
不应
大数目的不同值
不应
应
频繁更新的列
不应
应
外键列
应
应
主键列
应
应
频繁修改索引列
不应
应
事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。
（三）结合实际，谈索引使用的误区
理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引，但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区，以便于大家掌握索引建立的方法。
1、主键就是聚集索引
这种想法笔者认为是极端错误的，是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。
通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但笔者认为这样做意义不大。
显而易见，聚集索引的优势是很明显的，而每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。
从我们前面谈到的聚集索引的定义我们可以看出，使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。
在办公自动化系统中，无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。
通常，办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况，但如果您的系统已建立了很长时间，并且数据量很大，那么，每次每个用户打开首页的时候都进行一次全表扫描，这样做意义是不大的，绝大多数的用户1个月前的文件都已经浏览过了，这样做只能徒增数据库的开销而已。事实上，我们完全可以让用户打开系统首页时，数据库仅仅查询这个用户近3个月来未阅览的文件，通过“日期”这个字段来限制表扫描，提高查询速度。如果您的办公自动化系统已经建立的2年，那么您的首页显示速度理论上将是原来速度8倍，甚至更快。
在这里之所以提到“理论上”三字，是因为如果您的聚集索引还是盲目地建在ID这个主键上时，您的查询速度是没有这么高的，即使您在“日期”这个字段上建立的索引（非聚合索引）。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现（3个月内的数据为25万条）：
（1）仅在主键上建立聚集索引，并且不划分时间段：
Select gid,fariqi,neibuyonghu,title from tgongwen
用时：128470毫秒（即：128秒）
（2）在主键上建立聚集索引，在fariq上建立非聚集索引：
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
用时：53763毫秒（54秒）
（3）将聚合索引建立在日期列（fariqi）上：
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
用时：2423毫秒（2秒）
虽然每条语句提取出来的都是25万条数据，各种情况的差异却是巨大的，特别是将聚集索引建立在日期列时的差异。事实上，如果您的数据库真的有1000万容量的话，把主键建立在ID列上，就像以上的第1、2种情况，在网页上的表现就是超时，根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。
得出以上速度的方法是：在各个select语句前加：declare @d datetime
set @d=getdate()
并在select语句后加：
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())
2、只要建立索引就能显著提高查询速度
事实上，我们可以发现上面的例子中，第2、3条语句完全相同，且建立索引的字段也相同；不同的仅是前者在fariqi字段上建立的是非聚合索引，后者在此字段上建立的是聚合索引，但查询速度却有着天壤之别。所以，并非是在任何字段上简单地建立索引就能提高查询速度。
从建表的语句中，我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中，我们每天都会发几个文件，这几个文件的发文日期就相同，这完全符合建立聚集索引要求的：“既不能绝大多数都相同，又不能只有极少数相同”的规则。由此看来，我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。
3、把所有需要提高查询速度的字段都加进聚集索引，以提高查询速度
上面已经谈到：在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要，我们可以把他们合并起来，建立一个复合索引（compound index）。
很多人认为只要把任何字段加进聚集索引，就能提高查询速度，也有人感到迷惑：如果把复合的聚集索引字段分开查询，那么查询速度会减慢吗？带着这个问题，我们来看一下以下的查询速度（结果集都是25万条数据）：（日期列fariqi首先排在复合聚集索引的起始列，用户名neibuyonghu排在后列）
（1）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5'
查询速度：2513毫秒
（2）select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>'2004-5-5' and neibuyonghu='办公室'
查询速度：2516毫秒
（3）select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu='办公室'
查询速度：60280毫秒
从以上试验中，我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的，甚至比用上全部的复合索引列还要略快（在查询结果集数目一样的情况下）；而如果仅用复合聚集索引的非起始列作为查询条件的话，这个索引是不起任何作用的。当然，语句1、2的查询速度一样是因为查询的条目数一样，如果复合索引的所有列都用上，而且查询结果少的话，这样就会形成“索引覆盖”，因而性能可以达到最优。同时，请记住：无论您是否经常使用聚合索引的其他列，但其前导列一定要是使用最频繁的列。
评论Feed: http://77521.cn/feed.asp?q=comment&id=173
引用链接: http://77521.cn/trackback.asp?id=173