mysql 查询优化策略

数据库系统是管理信息系统的核心，基于数据库的联机事务处理（OLTP）以及联机分析处理(OLAP)是银行、企业、政府等部门最为重要的计算机应用之一。从大多数系统的应用实例来看，查询操作在各种数据库操作中所占据的比重最大，而查询操作所基于的SELECT语句在SQL语句中又是代价最大的语句。举例来说，如果数据的量积累到一定的程度，比如一个银行的账户数据库表信息积累到上百万甚至上千万条记录，全表扫描一次往往需要数十分钟，甚至数小时。如果采用比全表扫描更好的查询策略，往往可以使查询时间降为几分钟，由此可见查询优化技术的重要性。 
笔者在应用项目的实施中发现，许多程序员在利用一些前端数据库开发工具（如PowerBuilder、Delphi等）开发数据库应用程序时，只注重用户界面的华丽，并不重视查询语句的效率问题，导致所开发出来的应用系统效率低下，资源浪费严重。因此，如何设计高效合理的查询语句就显得非常重要。本文以应用实例为基础，结合数据库理论，介绍查询优化技术在现实系统中的运用。 
分析问题 
许多程序员认为查询优化是DBMS（数据库管理系统）的任务，与程序员所编写的SQL语句关系不大，这是错误的。一个好的查询计划往往可以使程序性能提高数十倍。查询计划是用户所提交的SQL语句的集合，查询规划是经过优化处理之后所产生的语句集合。DBMS处理查询计划的过程是这样的：在做完查询语句的词法、语法检查之后，将语句提交给DBMS的查询优化器，优化器做完代数优化和存取路径的优化之后，由预编译模块对语句进行处理并生成查询规划，然后在合适的时间提交给系统处理执行，最后将执行结果返回给用户。在实际的数据库产品(如Oracle、Sybase等)的高版本中都是采用基于代价的优化方法，这种优化能根据从系统字典表所得到的信息来估计不同的查询规划的代价，然后选择一个较优的规划。虽然现在的数据库产品在查询优化方面已经做得越来越好，但由用户提交的SQL语句是系统优化的基础，很难设想一个原本糟糕的查询计划经过系统的优化之后会变得高效，因此用户所写语句的优劣至关重要。系统所做查询优化我们暂不讨论，下面重点说明改善用户查询计划的解决方案。  
解决问题 
下面以关系数据库系统Informix为例，介绍改善用户查询计划的方法。 
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个表，模式如下所示： 
1．part表 
零件号零件描述其他列 
（part_num）（part_desc）（other column） 
102，032Seageat 30G disk…… 
500，049Novel 10M network card…… 
…… 
2．vendor表 
厂商号厂商名其他列 
（vendor _num）（vendor_name） （other column） 
910，257Seageat Corp…… 
523，045IBM Corp…… 
…… 
3．parven表 
零件号厂商号零件数量 
（part_num）（vendor_num）（part_amount） 
102，032910，2573,450,000 
234，423321，0014，000，000 
…… 
下面的查询将在这些表上定期运行，并产生关于所有零件数量的报表： 
SELECT part_desc，vendor_name，part_amount 
FROM part，vendor，parven 
WHERE part.part_num=parven.part_num 
AND parven.vendor_num = vendor.vendor_num 
ORDER BY part.part_num 
如果不建立索引，上述查询代码的开销将十分巨大。为此，我们在零件号和厂商号上建立索引。索引的建立避免了在嵌套中反复扫描。关于表与索引的统计信息如下： 
表行尺寸行数量每页行数量数据页数量 
（table）(row size)（Row count）（Rows/Pages）（Data Pages） 
part15010，00025400 
Vendor1501，000 2540 
Parven13 15，000300 50 
索引键尺寸每页键数量页面数量 
（Indexes）(Key Size)（Keys/Page)(Leaf Pages） 
part450020 
Vendor45002 
Parven825060 
看起来是个相对简单的3表连接，但是其查询开销是很大的。通过查看系统表可以看到，在part_num上和vendor_num上有簇索引，因此索引是按照物理顺序存放的。parven表没有特定的存放次序。这些表的大小说明从缓冲页中非顺序存取的成功率很小。此语句的优化查询规划是：首先从part中顺序读取400页，然后再对parven表非顺序存取1万次，每次2页（一个索引页、一个数据页），总计2万个磁盘页，最后对vendor表非顺序存取1.5万次，合3万个磁盘页。可以看出在这个索引好的连接上花费的磁盘存取为5.04万次。 
实际上，我们可以通过使用临时表分3个步骤来提高查询效率： 
1．从parven表中按vendor_num的次序读数据： 
SELECT part_num，vendor_num，price 
FROM parven 
ORDER BY vendor_num 
INTO temp pv_by_vn 
这个语句顺序读parven（50页），写一个临时表（50页），并排序。假定排序的开销为200页，总共是300页。 
2．把临时表和vendor表连接，把结果输出到一个临时表，并按part_num排序： 
SELECT pv_by_vn，＊ vendor.vendor_num 
FROM pv_by_vn，vendor 
WHERE pv_by_vn.vendor_num=vendor.vendor_num 
ORDER BY pv_by_vn.part_num 
INTO TMP pvvn_by_pn 
DROP TABLE pv_by_vn 
这个查询读取pv_by_vn(50页)，它通过索引存取vendor表1.5万次，但由于按vendor_num次序排列，实际上只是通过索引顺序地读vendor表（40＋2=42页），输出的表每页约95行，共160页。写并存取这些页引发5＊160=800次的读写，索引共读写892页。 
3．把输出和part连接得到最后的结果： 
SELECT pvvn_by_pn.＊，part.part_desc 
FROM pvvn_by_pn，part 
WHERE pvvn_by_pn.part_num=part.part_num 
DROP TABLE pvvn_by_pn 
这样，查询顺序地读pvvn_by_pn(160页)，通过索引读part表1.5万次，由于建有索引，所以实际上进行1772次磁盘读写，优化比例为30∶1。笔者在Informix Dynamic Sever上做同样的实验，发现在时间耗费上的优化比例为5∶1(如果增加数据量，比例可能会更大)。 

小结 

20％的代码用去了80％的时间，这是程序设计中的一个著名定律，在数据库应用程序中也同样如此。我们的优化要抓住关键问题，对于数据库应用程序来说，重点在于SQL的执行效率。查询优化的重点环节是使得数据库服务器少从磁盘中读数据以及顺序读页而不是非顺序读页。




人们在使用SQL时往往会陷入一个误区，即太关注于所得的结果是否正确，而忽略了不同的实现方法之间可能存在的 
性能差异，这种性能差异在大型的或是复杂的数据库环境中（如联机事务处理OLTP或决策支持系统DSS）中表现得尤为明 
显。笔者在工作实践中发现，不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对 
它们进行适当的优化后，其运行速度有了明显地提高！下面我将从这三个方面分别进行总结： 

---- 为了更直观地说明问题，所有实例中的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,302)。 

三、不可优化的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次数，尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体 
现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。



主　　题：  什么是聚集索引，什么是非聚集索引，什么又是主键？
用聚集索引
聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻。例如，如果应用程序执行的一个查询经常检索某一日期范围内的记录，则使用聚集索引可以迅速找到包含开始日期的行，然后检索表中所有相邻的行，直到到达结束日期。这样有助于提高此类查询的性能。同样，如果对从表中检索的数据进行排序时经常要用到某一列，则可以将该表在该列上聚集（物理排序），避免每次查询该列时都进行排序，从而节省成本。

当索引值唯一时，使用聚集索引查找特定的行也很有效率。例如，使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法，是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。

说明  如果该表上尚未创建聚集索引，且在创建 PRIMARY KEY 约束时未指定非聚集索引，PRIMARY KEY 约束会自动创建聚集索引。

也可以在 lname（姓氏）列和 fname（名字）列上创建聚集索引，因为雇员记录常常是按姓名而不是按雇员 ID 分组和查询的。
使用非聚集索引
非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。如果在表中未创建聚集索引，则无法保证这些行具有任何特定的顺序。

与使用书中索引的方式相似，Microsoft&reg; SQL Server&#8482; 2000 在搜索数据值时，先对非聚集索引进行搜索，找到数据值在表中的位置，然后从该位置直接检索数据。这使非聚集索引成为精确匹配查询的最佳方法，因为索引包含描述查询所搜索的数据值在表中的精确位置的条目。如果基础表使用聚集索引排序，则该位置为聚集键值；否则，该位置为包含行的文件号、页号和槽号的行 ID (RID)。例如，对于在 emp_id 列上有非聚集索引的表，如要搜索其雇员 ID (emp_id)，SQL Server 会在索引中查找这样一个条目，该条目精确列出匹配的 emp_id 列在表中的页和行，然后直接转到该页该行。

多个非聚集索引
有些书籍包含多个索引。例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。对于非聚集索引也是如此。可以为在表中查找数据时常用的每个列创建一个非聚集索引。

注意事项
在创建非聚集索引之前，应先了解您的数据是如何被访问的。可考虑将非聚集索引用于： 

包含大量非重复值的列，如姓氏和名字的组合（如果聚集索引用于其它列）。如果只有很少的非重复值，如只有 1 和 0，则大多数查询将不使用索引，因为此时表扫描通常更有效。


不返回大型结果集的查询。


返回精确匹配的查询的搜索条件（WHERE 子句）中经常使用的列。


经常需要联接和分组的决策支持系统应用程序。应在联接和分组操作中使用的列上创建多个非聚集索引，在任何外键列上创建一个聚集索引。


在特定的查询中覆盖一个表中的所有列。这将完全消除对表或聚集索引的访问。 

--------------
PRIMARY KEY 约束
表中经常有一个列或列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。

一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束确保唯一数据，所以经常用来定义标识列。

当为表指定 PRIMARY KEY 约束时，Microsoft&reg; SQL Server&#8482; 2000 通过为主键列创建唯一索引强制数据的唯一性。当在查询中使用主键时，该索引还可用来对数据进行快速访问。

如果 PRIMARY KEY 约束定义在不止一列上，则一列中的值可以重复，但 PRIMARY KEY 约束定义中的所有列的组合的值必须唯一。

如下图所示，titleauthor 表中的 au_id 和 title_id 列组成该表的组合 PRIMARY KEY 约束，以确保 au_id 和 title_id 的组合唯一。

1、什么是聚集索引和非聚集索引


SQL SERVER提供了两种索引：聚集索引（clustered index，也称聚类索引、簇集索引）和非聚集索引（nonclustered index，也称非聚类索引、非簇集索引）。


其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，就会很自然地翻开字典的前几页，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字；同样的，如果查“张”字，那您也会将您的字典翻到最后部分，因为“张”的拼音是“zhang”。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。 
　　如果您认识某个字，您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字，不知道它的发音，这时候，您就不能按照刚才的方法找到您要查的字，而需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。 
　　通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。


2、何时使用聚集索引或非聚集索引 
下面的表总结了何时使用聚集索引或非聚集索引（很重要）： 

动作描述

 使用聚集索引

 使用非聚集索引

 
列经常被分组排序

 应

 应

 
返回某范围内的数据

 应

 不应

 
一个或极少不同值

 不应

 不应

 
小数目的不同值

 应

 不应

 
大数目的不同值

 不应

 应

 
频繁更新的列

 不应

 应

 
外键列

 应

 应

 
主键列

 应

 应

 
频繁修改索引列

 不应

 应


　　事实上，我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如：返回某范围内的数据一项。比如您的某个表有一个时间列，恰好您把聚合索引建立在了该列，这时您查询2004年1月1日至2004年10月1日之间的全部数据时，这个速度就将是很快的，因为您的这本字典正文是按日期进行排序的，聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可；而不像非聚集索引，必须先查到目录中查到每一项数据对应的页码，然后再根据页码查到具体内容。


3、索引是如何工作的？改善SQL语句 

　　很多人不知道SQL语句在SQL SERVER中是如何执行的，他们担心自己所写的SQL语句会被SQL SERVER误解。比如： 


select * from table1 where name=''zhangsan'' and tID > 10000


和执行:


select * from table1 where tID > 10000 and name=''zhangsan''


　　一些人不知道以上两条语句的执行效率是否一样，因为如果简单的从语句先后上看，这两个语句的确是不一样，如果tID是一个聚合索引，那么后一句仅仅从表的10000条以后的记录中查找就行了；而前一句则要先从全表中查找看有几个name=''zhangsan''的，而后再根据限制条件条件tID>10000来提出查询结果。 
　　事实上，这样的担心是不必要的。SQL SERVER中有一个“查询分析优化器”，它可以计算出where子句中的搜索条件并确定哪个索引能缩小表扫描的搜索空间，也就是说，它能实现自动优化。 
　　虽然查询优化器可以根据where子句自动的进行查询优化，但大家仍然有必要了解一下“查询优化器”的工作原理，如非这样，有时查询优化器就会不按照您的本意进行快速查询。 
　　在查询分析阶段，查询优化器查看查询的每个阶段并决定限制需要扫描的数据量是否有用。如果一个阶段可以被用作一个扫描参数（SARG），那么就称之为可优化的，并且可以利用索引快速获得所需数据。 
　　SARG的定义：用于限制搜索的一个操作，因为它通常是指一个特定的匹配，一个值得范围内的匹配或者两个以上条件的AND连接。形式如下： 


列名 操作符 <常数 或 变量>


或


<常数 或 变量> 操作符列名


列名可以出现在操作符的一边，而常数或变量出现在操作符的另一边。如：


Name=’张三’


价格>5000


5000<价格


Name=’张三’ and 价格>5000


　　如果一个表达式不能满足SARG的形式，那它就无法限制搜索的范围了，也就是SQL SERVER必须对每一行都判断它是否满足WHERE子句中的所有条件。所以一个索引对于不满足SARG形式的表达式来说是无用的。 
　　介绍完SARG后，我们来总结一下使用SARG以及在实践中遇到的和某些资料上结论不同的经验： 

1、Like语句是否属于SARG取决于所使用的通配符的类型 


如：name like ‘张%’ ，这就属于SARG


而：name like ‘%张’ ,就不属于SARG。


原因是通配符%在字符串的开通使得索引无法使用。 

2、or 会引起全表扫描 
　　Name=’张三’ and 价格>5000 符号SARG，而：Name=’张三’ or 价格>5000 则不符合SARG。使用or会引起全表扫描。 

3、非操作符、函数引起的不满足SARG形式的语句 
　　不满足SARG形式的语句最典型的情况就是包括非操作符的语句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外还有函数。下面就是几个不满足SARG形式的例子： 


ABS(价格)<5000


Name like ‘%三’


有些表达式，如：


WHERE 价格*2>5000


SQL SERVER也会认为是SARG，SQL SERVER会将此式转化为：


WHERE 价格>2500/2


但我们不推荐这样使用，因为有时SQL SERVER不能保证这种转化与原始表达式是完全等价的。 

4、IN 的作用相当与OR 

语句： 


Select * from table1 where tid in (2,3)


和


Select * from table1 where tid=2 or tid=3


是一样的，都会引起全表扫描，如果tid上有索引，其索引也会失效。 

5、尽量少用NOT 

6、exists 和 in 的执行效率是一样的 
　　很多资料上都显示说，exists要比in的执行效率要高，同时应尽可能的用not exists来代替not in。但事实上，我试验了一下，发现二者无论是前面带不带not，二者之间的执行效率都是一样的。因为涉及子查询，我们试验这次用SQL SERVER自带的pubs数据库。运行前我们可以把SQL SERVER的statistics I/O状态打开： 


（1）select title,price from titles where title_id in (select title_id from sales where qty>30)


该句的执行结果为： 

表 ''sales''。扫描计数 18，逻辑读 56 次，物理读 0 次，预读 0 次。 
表 ''titles''。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。 


（2）select title,price from titles 


　　　　　　　where exists (select * from sales 


　　　　　　　where sales.title_id=titles.title_id and qty>30)


第二句的执行结果为： 

表 ''sales''。扫描计数 18，逻辑读 56 次，物理读 0 次，预读 0 次。 
表 ''titles''。扫描计数 1，逻辑读 2 次，物理读 0 次，预读 0 次。 

我们从此可以看到用exists和用in的执行效率是一样的。 

7、用函数charindex()和前面加通配符%的LIKE执行效率一样 
　　前面，我们谈到，如果在LIKE前面加上通配符%，那么将会引起全表扫描，所以其执行效率是低下的。但有的资料介绍说，用函数charindex()来代替LIKE速度会有大的提升，经我试验，发现这种说明也是错误的： 
　 


select gid,title,fariqi,reader from tgongwen 


　　　　　　　　　where charindex(''刑侦支队'',reader)>0 and fariqi>''2004-5-5''


用时：7秒，另外：扫描计数 4，逻辑读 7155 次，物理读 0 次，预读 0 次。


select gid,title,fariqi,reader from tgongwen 


　　　　　　　　　where reader like ''%'' + ''刑侦支队'' + ''%'' and fariqi>''2004-5-5''


用时：7秒，另外：扫描计数 4，逻辑读 7155 次，物理读 0 次，预读 0 次。 

8、union并不绝对比or的执行效率高 
　　我们前面已经谈到了在where子句中使用or会引起全表扫描，一般的，我所见过的资料都是推荐这里用union来代替or。事实证明，这种说法对于大部分都是适用的。 


select gid,fariqi,neibuyonghu,reader,title from Tgongwen 


　　　　　　　　　　where fariqi=''2004-9-16'' or gid>9990000


用时：68秒。扫描计数 1，逻辑读 404008 次，物理读 283 次，预读 392163 次。


select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16'' 


union


select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000


用时：9秒。扫描计数 8，逻辑读 67489 次，物理读 216 次，预读 7499 次。 

看来，用union在通常情况下比用or的效率要高的多。 

　　但经过试验，笔者发现如果or两边的查询列是一样的话，那么用union则反倒和用or的执行速度差很多，虽然这里union扫描的是索引，而or扫描的是全表。 
　 


select gid,fariqi,neibuyonghu,reader,title from Tgongwen 


　　　　　　　　　　where fariqi=''2004-9-16'' or fariqi=''2004-2-5''


用时：6423毫秒。扫描计数 2，逻辑读 14726 次，物理读 1 次，预读 7176 次。


select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16'' 


union


select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-2-5''


用时：11640毫秒。扫描计数 8，逻辑读 14806 次，物理读 108 次，预读 1144 次。 

9、字段提取要按照“需多少、提多少”的原则，避免“select *” 
　　我们来做一个试验： 


select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc


用时：4673毫秒


select top 10000 gid,fariqi,title from tgongwen order by gid desc


用时：1376毫秒


select top 10000 gid,fariqi from tgongwen order by gid desc


用时：80毫秒 

　　由此看来，我们每少提取一个字段，数据的提取速度就会有相应的提升。提升的速度还要看您舍弃的字段的大小来判断。 

10、count(*)不比count(字段)慢 
　　某些资料上说：用*会统计所有列，显然要比一个世界的列名效率低。这种说法其实是没有根据的。我们来看： 


select count(*) from Tgongwen


用时：1500毫秒


select count(gid) from Tgongwen 


用时：1483毫秒


select count(fariqi) from Tgongwen


用时：3140毫秒


select count(title) from Tgongwen


用时：52050毫秒 

　　从以上可以看出，如果用count(*)和用count(主键)的速度是相当的，而count(*)却比其他任何除主键以外的字段汇总速度要快，而且字段越长，汇总的速度就越慢。我想，如果用count(*)， SQL SERVER可能会自动查找最小字段来汇总的。当然，如果您直接写count(主键)将会来的更直接些。 

11、order by按聚集索引列排序效率最高 
　　我们来看：（gid是主键，fariqi是聚合索引列）： 


select top 10000 gid,fariqi,reader,title from tgongwen


用时：196 毫秒。 扫描计数 1，逻辑读 289 次，物理读 1 次，预读 1527 次。


select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc


用时：4720毫秒。 扫描计数 1，逻辑读 41956 次，物理读 0 次，预读 1287 次。


select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc


用时：4736毫秒。 扫描计数 1，逻辑读 55350 次，物理读 10 次，预读 775 次。


select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc


用时：173毫秒。 扫描计数 1，逻辑读 290 次，物理读 0 次，预读 0 次。


select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc


用时：156毫秒。 扫描计数 1，逻辑读 289 次，物理读 0 次，预读 0 次。 

　　从以上我们可以看出，不排序的速度以及逻辑读次数都是和“order by 聚集索引列” 的速度是相当的，但这些都比“order by 非聚集索引列”的查询速度是快得多的。 
　　同时，按照某个字段进行排序的时候，无论是正序还是倒序，速度是基本相当的。 

12、高效的TOP 
　　事实上，在查询和提取超大容量的数据集时，影响数据库响应时间的最大因素不是数据查找，而是物理的I/0操作。如： 


select top 10 * from (


select top 10000 gid,fariqi,title from tgongwen


where neibuyonghu=''办公室''


order by gid desc) as a


order by gid asc


　　这条语句，从理论上讲，整条语句的执行时间应该比子句的执行时间长，但事实相反。因为，子句执行后返回的是10000条记录，而整条语句仅返回10条语句，所以影响数据库响应时间最大的因素是物理I/O操作。而限制物理I/O操作此处的最有效方法之一就是使用TOP关键词了。TOP关键词是SQL SERVER中经过系统优化过的一个用来提取前几条或前几个百分比数据的词。经笔者在实践中的应用，发现TOP确实很好用，效率也很高。但这个词在另外一个大型数据库ORACLE中却没有，这不能说不是一个遗憾，虽然在ORACLE中可以用其他方法（如：rownumber）来解决。在以后的关于“实现千万级数据的分页显示存储过程”的讨论中，我们就将用到TOP这个关键词。 
　　到此为止，我们上面讨论了如何实现从大容量的数据库中快速地查询出您所需要的数据方法。当然，我们介绍的这些方法都是“软”方法，在实践中，我们还要考虑各种“硬”因素，如：网络性能、服务器的性能、操作系统的性能，甚至网卡、交换机等。