怎样提高数据库查询效率

怎样提高数据库查询效率

2.应尽量避免在where子句中对字段进行null值推断，否则将导致引擎放弃使用索引而进

行全表扫描，如：

selectidfromtwherenumisnull

能够在num上设置默认值0,确保表中num列没有null值，然后这样查询：

selectidfromtwherenum=0

3.应尽量避免在where子句中使用!=或者v＞操作符，否则将引擎放弃使用索引而进行全表

扫描。

4.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全

表扫描，如：

selectidfromtwherenum=10ornum=20

能够这样查询：

selectidfromtwherenum=10

unionall

selectidfromtwherenum=20

5.in与notin也要慎用，否则会导致全表扫描，如：

selectidfromtwherenumin(l,2,3)

关于连续的数值，能用between就不要用in了：

selectidfromtwherenumbetween1and3

6.下面的查询也将导致全表扫描：

selectidfromtwherenamelike'%abc%'

若要提高效率，能够考虑全文检索。

7.假如在where子句中使用参数，也会导致全表扫描。由于SQL只有在运行时才会解析局

部变量，但优化程序不能将访问计划的选择推迟到运行时；它务必在编译时进行选择。然而,

假如在编译时建立访问计•划，变量的值还是未知的，因而无法作为索引选择的输入项。如下

面语句将进行全表扫描：

selectidfromtwherenum=@num

能够改为强制查询使用索引：

selectidfromtwith(index(索弓|名))wherenum=@num

8.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行

全表扫描。如：

selectidfromtwherenum/2=100

应改为：

selectidfromtwherenum=100*2

9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表

扫描。如：

selectidfromtwheresubstring(name,1,3)='abc,—name以abc开头的id

selectidfromtwheredatediff(day,createdate,,2005-ll-30,)=0—'2005-11-30'生成的id

应改为:

selectidfromtwherenamelike'abc%'

selectidfromtwherecreatedate>=,2005-11-30'andcreatedate<,2005-12-1,

10.不要在where子句中的“=”左边进行函数、算术运算或者其他表达式运算，否则系统

将可能无法正确使用索引。

11.在使用索引字段作为条件时，假如该索引是复合索引，那么务必使用到该索引中的第一

个字段作为条件时才能保证系统使用该索引，否则该索引将不可能被使用，同时应尽可能的

让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询，如需要生成一个空表结构:

selectcoll,col2into#tfromtwhere1=0

这类代码不可能返回任何结果集，但是会消耗系统资源的，应改成这样:

createtable

13.很多时候用exists代替in是一个好的选择:

selectnumfromawherenumin(selectnumfromb)

用下面的语句替换:

selectnumfromawhereexists(select1frombwherenum=a.num)

14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大

量数据重复时，SQL查询可能不可能去利用索引，如一表中有字段sex,male、female几乎

各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好，索引固然能够提高相应的select的效率，但同时也降低了insert

及update的效率，由于insert或者update时有可能会重建索引，因此如何建索引需要慎

重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使

用到的列上建的索引是否有必要。

16.应尽可能的避免更新clustered索引数据列，由于clustered索引数据列的顺序就是表记

录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。

若应用系统需要频繁更新clustered索引数据列，那么需要考虑是否应将该索引建为

clustered索弓I。

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询与

连接的性能，并会增加存储开销。这是由于引擎在处理查询与连接时会逐个比较字符串中每

一个字符，而关于数字型而言只需要比较一次就够了。

18.尽可能的使用varchar/nvarchar代替char/nchar,由于首先变长字段存储空间小，能够

节约存储空间，其次关于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用select*fromt,用具体的字段列表代替“*”，不要返回用不到的任

何字段。

20.尽量使用表变量来代替临时表。假如表变量包含大量数据，请注意索引非常有限（只有

主键索引）。

21.避免频繁创建与删除临时表，以减少系统表资源的消耗。

22.临时表并不是不可使用，适当地使用它们能够使某些例程更有效，比如，当需要重复引

用大型表或者常用表中的某个数据集时。但是，关于一次性事件，最好使用导出表。

23.在新建临时表时，假如一次性插入数据量很大，那么能够使用selectinto代替create

table,避免造成大量log以提高速度；假如数据量不大，为了缓与系统表的资源，应先

createtable,然后insert。

24.假如使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate

table,然后droptable,这样能够避免系统表的较长时间锁定。

25.尽量避免使用游标，由于游标的效率较差，假如游标操作的数据超过1万行，那么就应

该考虑改写。

26.使用基于游标的方法或者临时表方法之前，应先寻找基于集的解决方案来解决问题，基

于集的方法通常更有效。

27.与临时表一样，游标并不是不可使用。对小型数据集使用FAST_FORWARD游标通常要

优于其他逐行处理方法，特别是在务必引用几个表才能获得所需的数据时。在结果集中包含

“合计”的例程通常要比使用游标执行的速度快。假如开发时间同意，基于游标的方法与基

于集的方法都能够尝试一下，看哪一种方法的效果更好。

28.在所有的存储过程与触发器的开始处设置SETNOCOUNTON,在结束时设置SET

NOCOUNTOFF。无需在执行存储过程与触发器的每个语句后向客户端发送

DONE_IN_PROC消息、。

29.尽量避免大事务操作，提高系统并发能力。

30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理

1：影响最大的是在数据库端家索引。

2：假如数据库字段很多，建议使用Select字段列表的方式而不使用Select*

3:请使用TQuery或者TADOQuery,同时WHERE语句一定要建立索引，尽量少使用TTable

或者TADOTable.

4:多使用存储过程。

5：关于ORACLE与SYBASE数据库要使用BDE,而关于MSSQLSERVER使

用ADO方式连接。

同时，关于ORACLE与SQLSERVER,OLEDB的效率要比ADO效率高。BDE应该

是效率比较低下的一种方式，这个在开发过程中我们有过比较。

另外：尽量保持建立的数据库连接，不必每次都重新连接数据库。这在SQLSERVER

与ACCESS下表现不明显，但是在ORACLE中就非常明显，由于ORACLE的建链时间非

常长，大约有2秒钟，而SQL与ACCESS大约只需0.01秒。

在查询语句中，要考虑where语句中的字段顺序。

尽量不用ORDERBY语句（数据量比较大的时候）

关于一个有超过百万条记录的数据表，怎么才能提高效率。

VB+ADO+SQLSERVER2000.

我能想到的有建立索引、使用存储过程。还有什么其他办法吗？

拆表，升级硬件。检索时尽量带参数，要用的数据才检出来。

对关联字段建索引：

优化SQL语句，减少或者避免多表连结。

添加内存、CPU

建立视图

充分利用高速缓存

充分利用索引与SQL语句语法

无关紧要的查询能够进行脏读

建索引的选择务必结合SQL查询、修改、删除语句的需要，通常的说法是在WHERE里经

常出现的字段建索引。假如在WHERE经常是几个字段一起出现而且是用AND连接的，那

就应该建这几个字段一起的联合索引，而且次序也需要考虑，通常是最常出现的放前面，重

复率低的放前面。

如有一张员工表，与部门表.1、直接用select*from员工表，部门表where员

工表.部门ID=部门.ID。2、select*from员工表。然后当程序选择到某个员工

后，再根据部门的ID来选择部门信息。

这两种方法，哪个效率高

假如是需要同时显示所有员工的部门信息，则第一种方法好；

假如是用户选中一个员工再带出来该员工的部门信息，则第二种方法好；

在真正需要的时候才从数据库提取数据

1。比较具有相同类型的列。

2。比较中应尽量使索引列独立。

3。在like模式的起始处不要使用通配符

比如：wherenamelike"%string%"

优化为：wherenamelike"string%"

4o帮助优化程序更好的评估索引的有效性

可用isamchk或者myisamchk的--analyze选项给优化程序提供更好的信

息，以便分析键值的分布。

5。利用EXPLAIN检验优化程序操作。

检查用于查询中的索引是否能很快的排除行

一个表中大概有20万以上的条目（做音乐查询）/

每次查询都需要在这个表的两个字段中查寻/（歌手与歌名）

如何提高效率？需要达到至少每秒30次的查询/而cpu尽量占用低一点

请问如何处理数据库与程序才能达到好的效果？

perl写的程序。谢谢了。

表如下

id歌手歌曲url专辑格式性别xxxxxxxxx

xxx.

创建索引

建索引的目的是为了更好的查询，但是不要盲目建索引，有的时候候适得其反。

遵循这样的原则：

1.搜索的索引列，不一定是索要选择的列

比如：selectcol_afromtbllleftjointbl2on

tbl1,col_b=tbl2.col_c

wherecol_d=expr

在这查询重视合作索引列的是tbll.col_bandtbl2.col_candcol_d

2.使用唯一索引：关于唯一值的列，索引的效果最好，而具有多个重复值的列

其索引效果最差。

3.使用短索引：应该指定一个前缀的长度，只要有可能就应该这样做

比如：varchar（lOO）列，对前10个或者者20个字符内，多数值是唯一的那就不要对

整个列索引。

4.利用最左索引：在创建n列的索引时，实际是创建了mysql可利用的n个索引，多列索

引能够起几个索引的作用，由于能够利用索引中最左边的列集来匹配。

ps:mysql不能使用不涉及左前缀的搜索。

5.不要过渡索引：

每个额外的索引都是要占额外的磁盘空间，并降低写操作的性能。

6.考虑在列上进行比较的类型：

索引可用于与between运算。

查询速度慢的原因很多，常见如下几种：

1、没有索引或者者没有用到索引（这是查询慢最常见的问题，是程序设计的缺陷）

2、I/O吞吐量小，形成了瓶颈效应。

3、没有创建计算列导致查询不优化。

4、内存不足

5、网络速度慢

6、查询出的数据量过大（能够使用多次查询，其他的方法降低数据量）

7、锁或者者死锁（这也是查询慢最常见的问题，是程序设计的缺陷）

8、sp」ock,sp_who,活动的用户查看源因是读写竞争资源。

9、返回了不必要的行与列

10、查询语句不好，没有优化

能够通过如下方法来优化查询：

1、把数据、日志、索引放到不一致的UO设备上，增加读取速度，往常能够将Tempdb应

放在RAID0上，SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要.

2、纵向、横向分割表，减少表的尺寸（sp_spaceuse）

3、升级硬件

4、根据查询条件，建立索引，优化索引、优化访问方式，限制结果集的数据量。注意填充因子

要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引

的创建），不要对有限的几个值的字段建单一索引如性别字段

5、提高网速；

6、扩大服务器的内存,Windows2000与SQLserver2000能支持4-8G的内存。配置虚拟内存：

虚拟内存大小应基于计算机上并发运行的服务进行配置。运行MicrosoftSQLServer?2000

时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的1.5倍。假如另外安装了全

文检索功能，并打算运行Microsoft搜索服务以便执行全文索引与查询，可考虑：将虚拟内

存大小配置为至少是计算机中安装的物理内存的3倍。将SQLServermaxservermemory

服务器配置选项配置为物理内存的1.5倍（虚拟内存大小设置的一半）。

7、增加服务器CPU个数;但是务必明白并行处理串行处理更需要资源比如内存。使用并行

还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就能够在处理器上运行。

比如耽搁查询的排序、连接、扫描与GROUPBY字句同时执行，SQLSERVER根据系统的

负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是

更新操作UPDATE,INSERT,DELETE还不能并行处理。

8、假如是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。like

'a%，使用索引Hke，％a，不使用索引用like'%a%'查询时，查询耗时与字段值总长度成正比,

因此不能用CHAR类型，而是VARCHAR-关于字段的值很长的建全文索引。

9、DBServer与APPLicationServer分离：OLTP与OLAP分离

10、分布式分区视图可用于实现数据库服务器联合体。联合体是一组分开管理的服务器，但

它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够

扩大一组服务器，以支持大型的多层Web站点的处理需要。有关更多信息，参见设计联合

数据库服务器。(参照SQL帮助文件，分区视图，)

a、在实现分区视图之前，务必先水平分区表

b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，同时每个视图具有相

同的名称。这样，引用分布式分区视图名的查询能够在任何一个成员服务器上运行。系统操

作如同每个成员服务器上都有一个原始表的复本一样，但事实上每个服务器上只有一个成员

表与一个分布式分区视图。数据的位置对应用程序是透明的。

11、重建索弓IDBCCREINDEX,DBCCINDEXDEFRAG,收缩数据与日志DBCC

SHRINKDBQBCCSHRINKFILE.设置自动收缩日志.关于大的数据库不要设置数据库自动

增长，它会降低服务器的性能。在T-sql的写法上有很大的讲究，下面列出常见的要点：首

先，DBMS处理查询计划的过程是这样的：

I、查询语句的词法、语法检查

2、将语句提交给DBMS的查询优化器

3、优化器做代数优化与存取路径的优化

4、由预编译模块生成查询规划

5、然后在合适的时间提交给系统处理执行

6、最后将执行结果返回给用户其次，看一下SQLSERVER的数据存放的结构：一个页面

的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。

12、Commit与rollback的区别Rollback:回滚所有的事物。Commit:提交当前的事物.没有

必要在动态SQL里写事物，假如要写请写在外面如：beginIranexec(@s)committrans或者

者将动态SQL写成函数或者者存储过程。

13、在查询Select语句中用Where字句限制返回的行数，避免表扫描，假如返回不必要的数据,

浪费了服务器的I/O资源，加重了网络的负担降低性能。假如表很大，在表扫描的期间将表

锁住，禁止其他的联接访问表，后果严重。

14、SQL的注释申明对执行没有任何影响

15、尽可能不使用光标，它占用大量的资源。假如需要row-by-row地执行，尽量使用非光

标技术，如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。游标能

够按照它所支持的提取选项进行分类：只进务必按照从第一行到最后一行的顺序提取行。

FETCHNEXT是唯一同意的提取操作，也是默认方式。可滚动性能够在游标中任何地方随

机提取任意行。游标的技术在SQL2000下变得功能很强大，他的目的是支持循环。有四个

并发选项READ_ONLY：不同意通过游标定位更新(Update),且在构成结果集的行中没有锁。

OPTIMISTICWITHvalueS:乐观并发操纵是事务操纵理论的一个标准部分。乐观并发操纵用

于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。

当某个游标以此选项打开时，没有锁操纵其中的行，这将有助于最大化其处理能力。假如用

户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。假如任何

值发生改变，则服务器就会明白其他人已更新了此行，并会返回一个错误。假如值是一样的，

服务器就执行修改。选择这个并发选项OPTIMISTICWITHROWVERSIONING:此乐观

并发操纵选项基于行版本操纵。使用行版本操纵，其中的表务必具有某种版本标识符，服务

器可用它来确定该行在读入游标后是否有所更换。在SQLServer中，这个性能由timestamp

数据类型提供，它是一个二进制数字，表示数据库中更换的相对顺序。每个数据库都有一个

全局当前时间戳值：@@DBTSo每次以任何方式更换带有timestamp列的行时、SQLServer

先在时间戳列中存储当前的@@DBTS值，然后增加@@DBTS的值。假如某个表具有

timestamp歹则时间戳会被记到行级。服务器就能够比较某行的当前时间戳值与上次提取

时所存储的时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较

timestamp列即可。假如应用程序对没有timestamp列的表要求基于行版本操纵的乐观并

发，则游标默认为基于数值的乐观并发操纵。SCROLLLOCKS这个选项实现悲观并发操

纵。在悲观并发操纵中，在把数据库的行读入游标结果集时，应用程序将试图锁定数据库行。

在使用服务器游标时，将行读入游标时会在其上放置一个更新锁。假如在事务内打开游标，

则该事务更新锁将一直保持到事务被提交或者回滚；当提取下一行时，将除去游标锁。假如

在事务外打开游标，则提取下一行时，锁就被丢弃。因此，每当用户需要完全的悲观并发操

纵时，游标都应在事务内打开。更新锁将阻止任何其它任务获取更新锁或者排它锁，从而阻

止其它任务更新该行。然而，更新锁并不阻止共享锁，因此它不可能阻止其它任务读取行，

除非第二个任务也在要求带更新锁的读取。滚动锁根据在游标定义的SELECT语句中指定

的锁提示，这些游标并发选项能够生成滚动锁。滚动锁在提取时在每行上获取，并保持到下

次提取或者者游标关闭，以先发生者为准。下次提取时，服务器为新提取中的行获取滚动锁,

并释放上次提取中行的滚动锁。滚动锁独立于事务锁，并能够保持到一个提交或者回滚操作

之后。假如提交时关闭游标的选项为关，则COMMIT语句并不关闭任何打开的游标，而

且滚动锁被保留到提交之后，以保护对所提取数据的隔离。所获取滚动锁的类型取决于游标

并发选项与游标SELECT语句中的锁提示。锁提示只读乐观数值乐观行版本操纵锁定

无提示未锁定未锁定未锁定更新NOLOCK未锁定未锁定未锁定未锁定

HOLDLOCK共享共享共享更新UPDLOCK错误更新更新更新TABLOCKX错误

未锁定未锁定更新其它未锁定未锁定未锁定更新*指定NOLOCK提示将使指定了

该提示的表在游标内是只读的。

16、用Profiler来跟踪查询，得到查询所需的时间，找出SQL的问题所在;用索引优化器优

化索引

17、注意UNion与UNionall的区别。UNIONall好

18、注意使用DISTINCT,在没有必要时不要用，它同UNION一样会使查询变慢。重复的

记录在查询里是没有问题的

19、查询时不要返回不需要的行、列

20、用sp_configure'querygovernorcostlimit'或者者SETQUERY_GOVERNOR_COST_LIMIT

来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在查询

之前就扼杀掉。SETLOCKTIME设置锁的时间

21、用selecttop100/10Percent来限制用户返回的行数或者者SETROWCOUNT来限制操

作的行

22、在SQL2000往常，通常不要用如下的字句:"ISNULL","NOT",

"NOTEXISTS","NOTIN","NOTLIKE",and"LIKE'%500'",由于他们不走索引全是表扫描。

也不要在WHere字句中的列名加函数，如Convert,substring等，假如务必用函数的时候，创

建计算列再创建索引来替代.还能够变通写法：WHERESUBSTRING(firstname,1,1)=改为

WHEREfirstnamelikehn%，(索引扫描)，一定要将函数与列名分开。同时索引不能建得太多

与太大。NOTIN会多次扫描表，使用EXISTS、NOTEXISTS,IN,LEFTOUTERJOIN来

替代，特别是左连接，而Exists比IN更快，最慢的是NOT操作.假如列的值含有空，往常它

的索引不起作用，现在2000的优化器能够处理了。相同的是ISNULL,"NOT","NOTEXISTS",

"NOTIN”能优化她，而”<>”等还是不能优化，用不到索引。

23、使用QueryAnalyzer,查看SQL语句的查询计划与评估分析是否是优化的SQL。通常

的20%的代码占据了80%的资源，我们优化的重点是这些慢的地方。

24、假如使用了IN或者者OR等时发现查询没有走索引，使用显示申明指定索引：

SELECT*FROMPersonMember(INDEX=IX_Title)WHEREprocessidIN('男'，'女')

25、将需要查询的结果预先计算好放在表中，查询的时候再SELECT。这在SQL7.0往常是

最重要的手段。比如医院的住院费计算。

26、MIN()与MAX()能使用到合适的索引。

27、数据库有一个原则是代码离数据越近越好，因此优先选择Default,依次为Rules,Triggers,

Constraint(约束如外健主健CheckUNIQUE……，数据类型的最大长度等等都是约

束)，Procedure.这样不仅保护工作小，编写程序质量高，同时执行的速度快。

28、假如要插入大的二进制值到Image歹U，使用存储过程，千万不要用内嵌INsert来插入(不

知JAVA是否）。由于这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务

器受到字符后又将他转换成二进制值.存储过程就没有这些动作：方法：

Createprocedurep_insertasinsertintotable(Fimage)values(@image)

在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

29、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优

化器可见到差别。

select*fromchineseresumewheretitlein（'男女'）

Select*fromchineseresumewheretitlebetween'男’and'女’

是一样的。由于in会在比较多次，因此有的时候会慢些。

30、在必要是对全局或者者局部临时表创建索引，有的时候能够提高速度，但不是一定会这

样，由于索引也耗费大量的资源。他的创建同是实际表一样。

31、不要建没有作用的事物比如产生报表时，浪费资源。只有在必要使用事物时使用它。

32、用OR的字句能够分解成多个查询，同时通过UNION连接多个查询。他们的速度只同

是否使用索引有关，假如查询需要用到联合索引，用UNIONall执行的效率更高.多个OR的

字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索

引。

33、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,能够用storedprocedure来

代替她。特别的是不要用视图嵌套，嵌套视图增加了寻找原始资料的难度。我们看视图的本

质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据

时，不要使用指向多个表的视图，直接从表检索或者者仅仅包含这个表的视图上读，否则增

加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。

34、没有必要时不要用DISTINCT与ORDERBY,这些动作能够改在客户端执行。它们增

加了额外的开销。这同UNION与UNIONALL一样的道理。

convert(varchar(10),ad.postDate,120)aspostDate1,workyear,degreedescriptionFROM

'JCNAD00333138','JCNAD00303570','JCNAD00303569',

,JCNAD00303568','JCNAD00306698','JCNAD00231935','JCNAD00231933,,

'JCNAD00254567','JCNAD00254585'；JCNAD00254608,,

'JCNAD00254607','JCNAD00258524'；JCNAD00332133','JCNAD00268618,,

'JCNAD00279196','JCNAD00268613')orderbypostdatedesc

35、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减

少推断的次数。

36、当用SELECTINTO时，它会锁住系统表（sysobjects,sysindexes等等），堵塞其他的连

接的存取。创建临时表时用显示申明语句，而不是

selectINTO,droptablet_lxhbegintranselect*intot_lxhfromchineseresume

wherename='XYZ'—commit

在另一个连接中SELECT*fromsysobjects能够看到SELECTINTO会锁住系统表，Create

table也会锁系统表（不管是临时表还是系统表）。因此千万不要在事物内使用它！”这样的

话假如是经常要用的临时表请使用实表，或者者临时表变量。

37、通常在GROUPBY个HAVING字句之前就能剔除多余的行，因此尽量不要用它们来做

剔除行的工作。他们的执行顺序应该如下最优：select的Where字句选择所有合适的行，

GroupBy用来分组个统计行，Having字句用来剔除多余的分组。这样GroupBy个Having

的开销小，查询快.关于大的数据行进行分组与Having十分消耗资源。假如GroupBY的目

的不包含计算，只是分组，那么用Distinct更快

38、一次更新多条记录比分多次更新每次一条快，就是说批处理好

39、少用临时表，尽量用结果集与Table类性的变量来代替它,Table类型的变量比临时表好

40、在SQL2000下，计算字段是能够索引的，需要满足的条件如下：

a、计算字段的表达是确定的

b、不能用在TEXT,Ntext,Image数据类型

c、务必配制如下选项ANS1_NULLS=ON,ANSI_PADDINGS=ON,.

41、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是

编译好、优化过、同时被组织到一个执行规划里、且存储在数据库中的SQL语句，是操纵

流语言的集合，速度当然快。反复执行的动态SQL,能够使用临时存储过程，该过程（临时

表）被放在Tempdb中。往常由于SQLSERVER对复杂的数学计算不支持，因此不得不将

这个工作放在其他的层上而增加网络的开销。SQL2000支持UDFs,现在支持复杂的数学计

算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量

的资源，假如返回大的结果使用存储过程

42、不要在一句话里再三的使用相同的函数，浪费资源，将结果放在变量里再调用更快

43、SELECTCOUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别：

selectcount(Fieldofnull)fromTable与selectcount(FieldofNOTnull)fromTable的返回值

是不一致的！！！

44、当服务器的内存够多时，配制线程数量=最大连接数+5,这样能发挥最大的效率；否

则使用配制线程数量〈最大连接数启用SQLSERVER的线程池来解决,假如还是数量=最

大连接数+5,严重的损害服务器的性能。

45、按照一定的次序来访问你的表。假如你先锁住表A,再锁住表B,那么在所有的存储过

程中都要按照这个顺序来锁定它们。假如你(不经意的)某个存储过程中先锁定表B,再锁

定表A,这可能就会导致一个死锁。假如锁定顺序没有被预先全面的设计好，死锁很难被发

现

46、通过SQLServerPerformanceMonitor监视相应硬件的负载Memory:PageFaults/sec计

数器假如该值偶尔走高，说明当时有线程竞争内存。假如持续很高，则内存可能是瓶颈。

Process:

1、%DPCTime指在范例间隔期间处理器用在缓延程序调用(DPC)接收与提供服务的百分

比。(DPC正在运行的为比标准间隔优先权低的间隔)。由于DPC是以特权模式执行的，

DPC时间的百分比为特权时间百分比的一部分。这些时间单独计算同时不属于间隔计算总

数的一部分。这个总数显示了作为实例时间百分比的平均忙时。

2、％ProcessorTime计数器假如该参数值持续超过95%,说明瓶颈是CPU。能够考虑增加

一个处理器或者换一个更快的处理器。

3、%PrivilegedTime指非闲置处理器时间用于特权模式的百分比。(特权模式是为操作系统

组件与操纵硬件驱动程序而设计的一种处理模式。它同意直接访问硬件与所有内存。另一种

模式为用户模式,它是一种为应用程序、环境分系统与整数分系统设计的一种有限处理模式。

操作系统将应用程序线程转换成特权模式以访问操作系统服务)。特权时间的%包含为间

断与DPC提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间隔而引

起的。这个计数器将平均忙时作为样本时间的一部分显示。

4、％UserTime表示耗费CPU的数据库操作，如排序，执行aggregatefunctions等。假如该

值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。

PhysicalDisk:CurretnDiskQueueLength计数器该值应不超过磁盘数的1.5~2倍。要提高性

能，可增加磁盘。SQLServer:CacheHilRatio计数器该值越高越好。假如持续低于80%,

应考虑增加内存。注意该参数值是从SQLServer启动后，就一直累加记数，因此运行通过

一段时间后，该值将不能反映系统当前值。

47、分析selectemp_nameformemployeewheresalary>3000在此语句中若salary是Float

类型的，则优化器对其进行优化为Convert(float,3000),由于3000是个整数，我们应在编程

时使用3000.0而不要等运行时让DBMS进行转化。同样字符与整型数据的转换。

48、查询的关联同写的顺序

selecta.personMemberlD,*fromchineseresumea,personmcmberbwherepersonMemberlD

=b.referenceidanda.personMemberlD='JCNPRH39681'（A=B,B='号码'）

selecta.personMemberlD,*fromchineseresumea,personmemberbwherea.personMemberlD

=b.referenceidanda.personMemberlD='JCNPRH39681'andb.referenceid='JCNPRH39681,

（八=82='号码'，人='号码'）

selecta.personMemberlD,*fromchineseresumea,personmemberbwhereb.referenceid

='JCNPRH39681'anda.personMemberlD='JCNPRH39681'（B=号码'，A='号码'）

49、

(1)1F没有输入负责人代码THENcodel=0code2=9999ELSEcodel=code2=负责人代码

ENDIF执行SQL语句为：SELECT负责人名FROMP2000WHERE负责人代码>=:codel

AND负责人代码<=:code2

⑵IF没有输入负责人代码THENSELECT负责人名FROMP2000ELSEcode=负责人代

码SELECT负责人代码FROMP2000WHERE负责人代码=:codeENDIF第一种方法只

用了一条SQL语句,第二种方法用了两条SQL语句。在没有输入负责人代码时，第二种方法

显然比第一种方法执行效率高，由于它没有限制条件;在输入了负责人代码时,第二种方法仍

然比第一种方法效率高，不仅是少了一个限制条件，还因相等运算是最快的查询运算。我们写

程序不要怕烦恼

50、关于JOBCN现在查询分页的新方法(如下)，用性能优化器分析性能的瓶颈，假如在

I/O或者者网络的速度上，如下的方法优化切实有效，假如在CPU或者者内存上，用现在的

方法更好。请区分如下的方法，说明索引越小越好。

begin

DECLARE@local_variabletable(FIDintidenlily(1,1),ReferenceIDvarchar(20))

insertinto@local_variable(ReferencelD)

selecttop100000ReferencelDfromchineseresumeorderbyReferencelD

select*from@local_variablewhereFid>40andfid<=60

end

与

begin

DECLARE@local_variabletable(FIDintidentity(1,1),ReferenceIDvarchar(20))

insertinto@local_variable(ReferencelD)

selecttop100000ReferencelDfromchineseresumeorderbyupdatedate

select*from@local_variablewhereFid>40andfid<=60

end

的不一致

begin

createtable#temp(FIDintidentity(1,1),ReferencelDvarchar(20))

insertinto#temp(ReferencelD)

selecttop100000ReferencelDfromchineseresumeorderbyupdatedate

select*from#tempwhereFid>40andfid<=60droptable#temp

end

数据库性能调优技术-索引调优

一、概述

随着数据库在各个领域的使用不断增长，越来越多的应用提出了高性能的要求。数据库性

能调优是知识密集型的学科，需要综合考虑各类复杂的因素：数据库缓冲区的大小、索引的

创建、语句改写等等。总之，数据库性能调优的目的在于使系统运行得更快。

调优需要有广泛的知识，这使得它既简单又复杂。

说调优简单，是由于调优者不必纠缠于复杂的公式与规则。许多学术界与业界的研究者都在

尝试将调优与查询处理建立在数学基础之上。

称调优复杂，是由于假如要完全懂得常识所依靠的原理，还需要对应用、数据库管理系统、

操作系统与硬件有广泛而深刻的懂得。

数据库调优技术能够在不一致的数据库系统中使用。假如需要调优数据库系统，最好掌握如

下知识：1)查询处理、并发操纵与数据库恢复的知识；2)一些调优的基本原则。

这里要紧描述索引调优。

二、索引调优

索引是建立在表上的一种数据组织，它能提高访问表中一条或者多条记录的特定查询效

率。因此，适当的索引调优是很重要的。

关于索引调优存在如下的几个误区：

误区1:索引创建得越多越好？

实际上：创建的索引可能建立后从来未使用。索引的创建也是需要代价的，关于删除、

某些更新、插入操作，关于每个索引都要进行相应的删除、更新、插入操作。从而导致删除、

某些更新、插入操作的效率变低。

误区2：关于一个单表的查询，能够索引1进行过滤再使用索引2进行过滤？

实际上:假设查询语句如下select*fromtlwherec1=1andc2=2,cl列与c2列上分别建

有索引icl、ic2。先使用icl(或者ic2)进行过滤，产生的结果集是临时数据，不再具有索引，

因此不可使用ic2(或者icl)进行再次过滤。

索引优化的基本原则：

1.将索引与数据存放到不一致的文件组

没有将表数据与索引数据存储到不一致的文件组，而不加区别地将它们存储到同一文件

组。这样，不但会造成I/O竞争，也为数据库的保护工作带来不变。

2.组合索引的使用

假设存在组合索引itlclc2(cl,c2),查询语句select*fromtlwherecI=1andc2=2能够使用

该索引。查询语句select*fromtlwherecl=l也能够使用该索引。但是，查询语句select*from

tlwherec2=2不能够使用该索引，由于没有组合索引的引导列，即，要想使用c2列进行查

找，必需出现cl等于某值。

根据where条件的不一致，归纳如下：

l)cl=landc2=2：使用索引itlclc2进行等值查找。

2)cl=landc2>2：使用索引itlclc2进行范围查找，能够有两种方法。

方法1，使用通过索引键(1,2)在B树中命中一条记录，然后向后扫描找出第一条符

合条件的记录，从此记录往后的每一条记录都是符合条件的。这种方法的弊端在于：假如

cl=landc2=2对应的记录数很多，会产生很多无效的扫描。

方法2,假如c2对应的int型数据，能够使用索引键(1,3)在B树中命中一条记录，从此记

录往后的每一条记录都是符合条件的。

本文中的例子均使用方法1»

3)cl>landc2=2：由于索引的第一个列不是等于号的，索引即使后面出现了c2=2,也不能

将c2=2应用于索引查找。这里，通过索引键(1,-8)在B树中命中一条记录，向后扫描找出

第一条符合cl>l的记录，此后的每一条记录推断是否符合c2=2,假如符合则输出，否则过

滤掉。这里我们称c2=2没有参与到索引运算中去。这种情况在实际应用中经常出现。

4)cl>l：通过索引键(1,-8)在B树中命中一条记录，以此向后扫描找出第一条符合cl>l

的记录，此后的每条记录都是符合条件的。

3.唯一索引与非唯一索引的差异

假设索引intlcl(cl)是唯一索引，关于查询语句selectclfromtlwherecl=l,达梦数据库

使用索引键(1)命中B树中一条记录，命中之后直接返回该记录(由因此唯一索引，因此最

多只能有一条cl=l的记录)。

假设索引itlc2(c2)是非唯一索引，关于查询语句selectc2fromt2wherec2=2,达梦数据

库使用索引键(2)命中B树中一条记录，返回该记录，并继续向后扫描，假如该记录是满足

c=2,返回该记录，继续扫描，直到遇到第一条不符合条件c2=2的记录。

因此，我们能够得知，关于不存在重复值的列，创建唯一索引优于创建非唯一索引。

4.非聚集索引的作用

每张表只可能一个聚集索引，聚集索引用来组织真实数据。语句“createtableemployee(id

intclusterprimarykey,namevarchar(20),addrvarcharQO))"。表employee的数据用id来组织。

假如要查找id=1000的员工记录，只要用索引键(1000)命中该聚集索引。但是，关于要查

找name='张三'的员工记录就不能使用该索引了，需要进行全表扫描，关于每一条记录推

断是否满足name='张三'，这样会导致查询效率非常低。

要使用聚集索弓I，必需提供id,我们只能提供name,因此需要引入一个辅助结构实现name

到id的转换，这就是非聚集索引的作用。该非聚集索引的键是name,值是id。因此语句“select

*fromemployeewherename=＞张三的执行流程是：通过键('张三’)命中非聚集索引，

得到对应的id值3(假设‘张三'对应的id为3),然后用键(3)命中聚集索引，得到相应的

记录。

5.是不是使用非聚集索引的查询都需要进行聚集的查询？

不是的，尽管在上一点中查询转换为聚集索引的查找，有的时候候能够只需要使用非聚

集索引。

创建表并创建相应的索引：createtabletl(clint,c2int,c3int);createindexitlc2c3on

tl(c2,c3)o查询语句为：selectc3from11wherec2=1»

由于索引itlc2c3(c2,c3)覆盖查询语句中的列(c2,c3).因此，该查询语句的执行流程为：通过

索引键(1,-8)命中索引itk2c3,关于该记录直接返回c3对应的值，继续向后扫描，假如索

引记录中cl还是等于1,那么输出c3,以此类推，直到出现第一条cl不等于1的索引记录,

结束查询。

6.创建索引的规则

创建索引首先要考虑的是列的可选择性。比较一下列中唯一键的数量与表中记录的行数，

就能够推断该列的可选择性。假如该列的“唯一键的数量/表中记录行数”的比值越接近于1,

则该列的可选择行越高。在可选择性高的列上进行查询，返回的数据就较少，比较适合索引

查询。相反，比如性别列上只有两个值，可选择行就很小，不适合索引查询。

SQLServer中调整自增字段的当前初始值

前几天在把一个CommuntiyServer的数据库从SQL2000升级到SQL2005的时候，碰到一

个怪异的问题，报如下错误：

ViolationofPRIMARYKEYconstraint'PK_cs_Threads'.Cannotinsertduplicatekeyinobject

'dbo.cs_Threads'.

分析进去后，居然发现这个表的自增字段数据库中已经达到了6144,而数据库保护的这个

表的初始自增值只到6109。

解决方法很简单，利用下列SQL语句即可搞定:

DBCCCHECKIDENT('cs_Threads')

上述语句的意思就是：假如表'cs_Threads的当前标识值小于列中存储的最大标识值，则使用

标识列中的最大值对其进行重置。

CHECKIDENT命令能够有下列几种写法：

1、DBCCCHECKIDENT('table_name',NORESEED)

不重置当前标识值。DBCCCHECKIDENT返回一个报表，它指明当前标识值与应有的标识

值。

类似如下的报表：

Checkingidentityinformation:currentidentityvalue'6109',currentcolumnvalue'6144'.

2、DBCCCHECKIDENT('table_name')或者DBCCCHECKIDENT('table_name',RESEED)

假如表的当前标识值小于列中存储的最大标识值，则使用标识列中的最大值对其进行重置。

上述命令执行的时候，也会报类似上面的报表。

3、DBCCCHECKIDENT('table_name',RESEED,new_reseed_value)

当前值设置为new_reseed_value。

假如自创建表后没有将行插入该表，则在执行DBCCCHECKIDENT后插入的第一行将使

用new_reseed_value作为标识。否则，下一个插入的行将使用new_reseed_value+1o

假如new_reseed_value的值小于标识列中的最大值，以后引用该表时将产生2627号错误

信息。

使用时容易忽略SQLServer的21个问题

假如你正在负责一个基于SQLServer的项目，或者者你刚刚接触SQLServer,你都有可能

要面临一些数据库性能的问题，这篇文章会为你提供一些有用的指导(其中大多数也能够用

于其它的DBMS)。

在这里，我不打算介绍使用SQLServer的窍门，也不能提供一个包治百病的方案，我

所做的是总结一些经验--关于如何形成一个好的设计。这些经验来自我过去几年中经受的

教训，一直来，我看到许多同样的设计错误被一次又一次的重复。

你熟悉工具吗？

不要轻视这一点，这是我在这篇文章中讲述的最关键的一条。也许你也看到有很多的

SQLServer程序员没有掌握全部的T-SQL命令与SQLServer提供的那些有用的工具。

“什么？我要浪费一个月的时间来学习那些我永远也不可能用到的SQL命令？？？”，

你也许会这样说。对的，你不需要这样做。但是你应该用一个周末浏览所有的T-SQL命令。

在这里，你的任务是熟悉，将来，当你设计一个查询时，你会记起来：“对了，这里有一个

命令能够完全实现我需要的功能”，因此，到MSDN查看这个命令的确切语法。

不要使用光标

让我再重复一遍：不要使用光标。假如你想破坏整个系统的性能的话，它们倒是你最有

效的首选办法。大多数的初学者都使用光标，而没有意识到它们对性能造成的影响。它们占

用内存，还用它们那些不可思议的方式锁定表，另外，它们简直就像蜗牛。而最糟糕的是，

它们能够使你的DBA所能做的一切性能优化等于没做。不知你是否明白每执行一次FETCH

就等于执行一次SELECT命令？这意味着假如你的光标有10000条记录，它将执行10000

次SELECT!假如你使用一组SELECT、UPDATE或者者DELETE来完成相应的工作，那

将有效率的多。

初学者通常认为使用光标是一种比较熟悉与舒适的编程方式，可很不幸，这会导致糟糕

的性能。显然，SQL的总体目的是你要实现什么，而不是如何实现。

我曾经用T-SQL重写了一个基于光标的存储过程，那个表只有100,000条记录，原先的

存储过程用了40分钟才执行完毕，而新的存储过程只用了10秒钟。在这里，我想你应该能

够看到一个不称职的程序员毕竟在干了什么!!!

我们能够写一个小程序来取得与处理数据同时更新数据库，这样做有的时候会更有效。

记住：关于循环，T-SQL无能为力。

我再重新提醒一下：使用光标没有好处。除了DBA的工作外，我从来没有看到过使用

光标能够有效的完成任何工作。

规范化你的资料表

为什么不规范化数据库？大概有两个借口：出于性能的考虑与纯粹由于懒惰。至于第二

点，你迟早得为此付出代价。而关于性能的问题，你不需要优化根本就不慢的东西。我经常

看到一些程序员“反规范化”数据库，他们的理由是“原先的设计太慢了”，可结果却常常

是他们让系统更慢了。DBMS被设计用来处理规范数据库的，因此，记住：按照规范化的

要求设计数据库。

不要使用SELECT*

这点不太容易做到，我太熟悉了，由于我自己就经常这样干。但是，假如在SELECT

中指定你所需要的列，那将会带来下列的好处：

1减少内存耗费与网络的带宽

2你能够得到更安全的设计

3给查询优化器机会从索引读取所有需要的列

熟悉你将要对数据进行的操作

为你的数据库创建一个健壮的索引，那但是功德一件。可要做到这一点简直就是一门艺

术。每当你为一个表添加一个索引，SELECT会更快了，可INSERT与DELETE去［1大大的

变慢了，由于创建了保护索引需要许多额外的工作。显然，这里问题的关键是：你要对这张

表进行什么样的操作。这个问题不太好把握，特别是涉及DELETE与