关于SQL优化的问题

 之前对SQL处于解决问题,能出结果的阶段,近日看到同事因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的性能优化是一个复杂的过程，上述这些只是在应用层次的一种体现，深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

 

SQL Server中有几个可以让你检测、调整和优化SQL Server性能的工具。在本文中，我将
说明如何用SQL Server的工具来优化数据库索引的使用，本文还涉及到有关索引的一般性知识
　　关于索引的常识
　　
　　影响到数据库性能的最大因素就是索引。由于该问题的复杂性，我只可能简单的谈谈这个
问题，不过关于这方面的问题，目前有好几本不错的书籍可供你参阅。我在这里只讨论两种
SQL Server索引，即clustered索引和nonclustered索引。当考察建立什么类型的索引时，你
应当考虑数据类型和保存这些数据的column。同样，你也必须考虑数据库可能用到的查询类型
　　索引的类型
　　如果column保存了高度相关的数据，并且常常被顺序访问时，最好使用clustered索引，
这是因为如果使用clustered索引，SQL Server会在物理上按升序（默认）或者降序重排数据
列，这样就可以迅速的找到被查询的数据。同样，在搜寻控制在一定范围内的情况下，对这些
column也最好使用clustered索引。这是因为由于物理上重排数据，每个表格上只有一个
　　与上面情况相反，如果columns包含的数据相关性较差，你可以使用nonculstered索引。
你可以在一个表格中使用高达249个nonclustered索引??尽管我想象不出实际应用场合会用
　　当表格使用主关键字（primary keys），默认情况下SQL Server会自动对包含该关键字的
column(s)建立一个独有的cluster索引。很显然，对这些column(s)建立独有索引意味着主关
键字的唯一性。当建立外关键字（foreign key）关系时，如果你打算频繁使用它，那么在外
关键字cloumn上建立nonclustered索引不失为一个好的方法。如果表格有clustered索引，那
么它用一个链表来维护数据页之间的关系。相反，如果表格没有clustered索引，SQL Server
　　数据页
　　当索引建立起来的时候，SQLServer就建立数据页（datapage），数据页是用以加速搜索
的指针。当索引建立起来的时候，其对应的填充因子也即被设置。设置填充因子的目的是为了
指示该索引中数据页的百分比。随着时间的推移，数据库的更新会消耗掉已有的空闲空间，这
就会导致页被拆分。页拆分的后果是降低了索引的性能，因而使用该索引的查询会导致数据存
储的支离破碎。当建立一个索引时，该索引的填充因子即被设置好了，因此填充因子不能动态
　　为了更新数据页中的填充因子，我们可以停止旧有索引并重建索引，并重新设置填充因子
（注意：这将影响到当前数据库的运行，在重要场合请谨慎使用）。DBCC INDEXDEFRAG和
DBCC DBREINDEX是清除clustered和nonculstered索引碎片的两个命令。INDEXDEFRAG是一种在
线操作（也就是说，它不会阻塞其它表格动作，如查询），而DBREINDEX则在物理上重建索引
。在绝大多数情况下，重建索引可以更好的消除碎片，但是这个优点是以阻塞当前发生在该索
引所在表格上其它动作为代价换取来得。当出现较大的碎片索引时，INDEXDEFRAG会花上一段
比较长的时间，这是因为该命令的运行是基于小的交互块（transactional block）。
　　填充因子
　　当你执行上述措施中的任何一个，数据库引擎可以更有效的返回编入索引的数据。关于填
充因子（fillfactor）话题已经超出了本文的范畴，不过我还是提醒你需要注意那些打算使用
　　在执行查询时，SQL Server动态选择使用哪个索引。为此，SQL Server根据每个索引上分
布在该关键字上的统计量来决定使用哪个索引。值得注意的是，经过日常的数据库活动（如插
入、删除和更新表格），SQL Server用到的这些统计量可能已经“过期”了，需要更新。你可
以通过执行DBCC SHOWCONTIG来查看统计量的状态。当你认为统计量已经“过期”时，你可以
执行该表格的UPDATE STATISTICS命令，这样SQL Server就刷新了关于该索引的信息了。
　　建立数据库维护计划
　　SQL Server提供了一种简化并自动维护数据库的工具。这个称之为数据库维护计划向导（
Database Maintenance Plan Wizard ，DMPW）的工具也包括了对索引的优化。如果你运行这
个向导，你会看到关于数据库中关于索引的统计量，这些统计量作为日志工作并定时更新，这
样就减轻了手工重建索引所带来的工作量。如果你不想自动定期刷新索引统计量，你还可以在
DMPW中选择重新组织数据和数据页，这将停止旧有索引并按特定的填充因子重建索引

 

假设你想找书中的某一个句子。你可以一页一页地逐页搜索，但这会花很多时间。而通过使用索引，你可以很快地找到你要搜索的主题。
   表的索引与附在一本书后面的索引非常相似。它可以极大地提高查询的速度。对一个较大的表来说，通过加索引，一个通常要花费几个小时来完成的查询只要几分钟就可以完成。因此没有理由对需要频繁查询的表增加索引。
   注意：
   当你的内存容量或硬盘空间不足时，也许你不想给一个表增加索引。对于包含索引的数据库，SQL Sever需要一个可观的额外空间。例如，要建立一个聚簇索引，需要大约１.２倍于数据大小的空间。要看一看一个表的索引在数据库中所占的空间大小，你可以使用 系统存储过程sp_spaceused，对象名指定为被索引的表名。

聚簇索引和非聚簇索引
   假设你已经书的索引找到了一个句子所在的页码。一旦已经知道了页码后，你很可能漫无目的翻寻这本书，直至找到正确的页码。通过随机的翻寻，你最终可以到达正确的页码。但是，有一种找到页码的更有效的方法。
   首先，把书翻到大概一半的地方，如果要找的页码比半本书处的页码小，就书翻到四分之一处，否则，就把书翻到四分之三的地方。通过这种方法，你可以继续把书分成更小的部分，直至找到正确的页码附近。这是找到书页的非常有效的一种方法。
SQL Sever的表索引以类似的方式工作。一个表索引由一组页组成，这些页构成了一个树形结构。根页通过指向另外两个页，把一个表的记录从逻辑上分成和两个部分。而根页所指向的两个页又分别把记录分割成更小的部分。每个页都把记录分成更小的分割，直至到达叶级页。
   索引有两种类型：聚簇索引和非聚簇索引。在聚簇索引中，索引树的叶级页包含实际的数据：记录的索引顺序与物理顺序相同。在非聚簇索引中，叶级页指向表中的记录：记录的物理顺序与逻辑顺序没有必然的联系。
   聚簇索引非常象目录表，目录表的顺序与实际的页码顺序是一致的。非聚簇索引则更象书的标准索引表，索引表中的顺序通常与实际的页码顺序是不一致的。一本书也许有多个索引。例如，它也许同时有主题索引和作者索引。同样，一个表可以有多个非聚簇索引。
   通常情况下，你使用的是聚簇索引，但是你应该对两种类型索引的优缺点都有所理解。
   每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。通常你要对一个表按照标识字段建立聚簇索引。但是，你也可以对其它类型的字段建立聚簇索引，如字符型，数值型和日期时间型字段。
   从建立了聚簇索引的表中取出数据要比建立了非聚簇索引的表快。当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。例如，假设你用一个表来记录访问者在你网点上的活动。如果你想取出在一定时间段内的登录信息，你应该对这个表的DATETIME型字段建立聚簇索引。
   对聚簇索引的主要限制是每个表只能建立一个聚簇索引。但是，一个表可以有不止一个非聚簇索引。实际上，对每个表你最多可以建立249个非聚簇索引。你也可以对一个表同时建立聚簇索引和非聚簇索引。
   假如你不仅想根据日期，而且想根据用户名从你的网点活动日志中取数据。在这种情况下，同时建立一个聚簇索引和非聚簇索引是有效的。你可以对日期时间字段建立聚簇索引，对用户名字段建立非聚簇索引。如果你 发现你需要更多的索引方式，你可以增加更多的非聚簇索引。
   非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中 取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。

索引属性
   这两种类型的索引都有两个重要属性：你可以用两者中任一种类型同时对多个字段建立索引（复合索引）；两种类型的索引都可以指定为唯一索引。
   你可以对多个字段建立一个复合索引，甚至是复合的聚簇索引。假如有一个表记录了你的网点访问者的姓和名字。如果你希望根据完整姓名从表中取数据，你需要建立一个同时对姓字段和名字字段进行的索引。这和分别对两个字段建立单独的索引是不同的。当你希望同时对不止一个字段进行查询时，你应该建立一个对多个字段的索引。如果你希望对各个字段进行分别查询，你应该对各字段建立独立的索引。
   两种类型的索引都可以被指定为唯一索引。如果对一个字段建立了唯一索引，你将不能向这个字段输入重复的值。一个标识字段会自动成为唯一值字段，但你也可以对其它类型的字段建立唯一索引。假设你用一个表来保存你的网点的用户密码，你当然不希望两个用户有相同的密码。通过强制一个字段成为唯一值字段，你可以防止这种情况的发生。

用SQL建立索引
   为了给一个表建立索引，启动任务栏SQL Sever程序组中的ISQL/w程序。进入查询窗口后，输入下面的语句：

      CREATE INDEX mycolumn_index ON mytable (myclumn)

   这个语句建立了一个名为mycolumn_index的索引。你可以给一个索引起任何名字，但你应该在索引名中包含所索引的字段名，这对你将来弄清楚建立该索引的意图是有帮助的。
   注意：
   执行后，都会收到如下的信息：
   This  command did not return data,and it did not return any rows
这说明该语句执行成功了。
索引mycolumn_index对表mytable的mycolumn字段进行。这是个非聚簇索引，也是个非唯一索引。（这是一个索引的缺省属性）
如果你需要改变一个索引的类型，你必须删除原来的索引并重建 一个。建立了一个索引后，你可以用下面的SQL语句删除它：

DROP INDEX mytable.mycolumn_index

注意在DROP INDEX 语句中你要包含表的名字。在这个例子中，你删除的索引是mycolumn_index，它是表mytable的索引。
要建立一个聚簇索引，可以使用关键字CLUSTERED。记住一个表只能有一个聚簇索引。
这里有一个如何对一个表建立聚簇索引的例子：

CREATE CLUSTERED INDEX mycolumn_clust_index ON mytable(mycolumn)

如果表中有重复的记录，当你试图用这个语句建立索引时，会出现错误。但是有重复记录的表也可以建立索引；你只要使用关键字ALLOW_DUP_ROW把这一点告诉SQL Sever即可：

CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH ALLOW_DUP_ROW

这个语句建立了一个允许重复记录的聚簇索引。你应该尽量避免在一个表中出现重复记录，但是，如果已经出现了，你可以使用这种方法。
要对一个表建立唯一索引，可以使用关键字UNIQUE。对聚簇索引和非聚簇索引都可以使用这个关键字。这里有一个例子：

CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)

这是你将经常使用的索引建立语句。无论何时，只要可以，你应该尽量对一个对一个表建立唯一聚簇索引来增强查询操作。
最后，要建立一个对多个字段的索引──复合索引──在索引建立语句中同时包含多个字段名。下面的例子对firstname和lastname两个字段建立索引：

CREATE INDEX name_index ON username(firstname,lastname)

这个例子对两个字段建立了单个索引。在一个复合索引中，你最多可以对16个字段进行索引。

用事务管理器建立索引
用事务管理器建立索引比用SQL语句容易的多。使用事务管理器，你可以看到已经建立的索引的列表，并可以通过图形界面选择索引选项。
使用事务管理器你可以用两种方式建立索引：使用Manage Tables窗口或使用Manage Indexes窗口。
   要用Manage Tables 窗口建立一个新索引，单击按钮Advanced Options(它看起来象一个前面有一加号的表)。这样就打开了Advanced Options对话框。这个对话框有一部分标名为Primary Key。

要建立一个新索引，从下拉列表中选择你想对之建立索引的字段名。如果你想建立一个对多字段的索引，你可以选择多个字段名。你还可以选择索引是聚簇的还是非聚簇的。在保存表信息后，索引会自动被建立。在Manage Tables窗口中的字段名旁边，会出现一把钥匙。
你已经为你的表建立了“主索引”。主索引必须对不包含空值的字段建立。另外，主索引强制一个字段成为唯一值字段。
要建立没有这些限制的索引，你需要使用Manage Indexes窗口。从菜单中选择Manage|Indexes，打开Manage Indexes 窗口。在Manage Indexes 窗口中，你可以通过下拉框选择表和特定的索引。（见图11.2）。要建立一个新索引，从Index下拉框中选择New Index.，然后就可以选择要对之建立索引的字段。单击按钮Add，把字段加人到索引中。

你可以为你的索引选择许多不同的选项。例如，你可以选择该索引是聚簇的还是非聚簇的。你还可以指定该索引为唯一索引。设计好索引后，单击按钮Build，建立该索引。

注意：
唯一索引是指该字段不能有重复的值，而不是只能建立这一个索引。

维护Sql Server中表的索引

<script type="text/javascript"> </script> <script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"> </script>

 

第一步：查看是否需要维护，查看扫描密度/Scan Density是否为100%

declare @table_id int

set @table_id=object_id(表名)

dbcc showcontig(@table_id)

第二步:重构表索引

dbcc dbreindex(表名,pk_索引名,100)

重做第一步，如发现扫描密度/Scan Density还是小于100%则重构表的所有索引

--杨铮：并不一定能达100%。

dbcc dbreindex(表名,,100)