ORACLE SQL优化

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

　　

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

　　

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

　　

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

　　

　　4、内存不足

　　

　　5、网络速度慢

　　

　　6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）

　　

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

　　

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

　　

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

　　

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

 

可以通过如下方法来优化查询 :

　　1、把数据、日志、索引放到不同的I/O设备上(键盘、打印机、显示器)，增加读取速度，以前可以将Tempdb应放在RAID0上，SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要.?

　　

　　2、纵向、横向分割表，减少表的尺寸(sp_spaceuse)  --简单来说就是将行分割成若干个表、将列分割成若干个表。

　　

　　3、升级硬件

　　

　　4、根据查询条件,建立索引,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值0）。索引应该尽量小，使用字节数小的列建索引好（参照索引的创建）,不要对有限的几个值的字段建单一索引如:性别字段。

　　

　　5、提高网速;

　　

　　6、扩大服务器的内存,Windows2000和SQL server 2000能支持4-8G的内存。配置虚拟内存：虚拟内存大小应基于计算机上并发运行的服务进行配置。运行 Microsoft SQLServer 2000 时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的 1.5倍。如果另外安装了全文检索功能，并打算运行 Microsoft 搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory服务器配置选项配置为物理内存的 1.5 倍（虚拟内存大小设置的一半）。

　　

　　7、增加服务器CPU个数;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接、扫描和GROUPBY字句同时执行，SQLSERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU的查询最适合并行处理。但是更新操作Update,Insert，Delete还不能并行处理。

　　

　　8、如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。like 'a%' 使用索引 ，like '%a' 不使用索引，用 like '%a%' 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR（因为char的长度是固定的，varchar的长度不是固定的），对于字段的值很长的建全文索引。

　　

　　9、DB Server 和APPLication Server分离；OLTP（on-line transaction processing联机事务处理）和OLAP（On-Line AnalyticalProcessing 联机分析处理）分离

　　

　　10、分布式分区视图可用于实现数据库服务器联合体，联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照SQL帮助文件'分区视图'）

　　

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

　　

　　b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。数据的位置对应用程序是透明的。

　　

　　11、重建索引 DBCC REINDEX,DBCC INDEXDEFRAG,收缩数据和日志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。在T-sql（是标准 SQL 程式设计语言的增强版，它是用来让应用程式与 SQL Server沟通的主要语言）的写法上有很大的讲究，下面列出常见的要点：首先，DBMS处理查询计划的过程是这样的： 

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

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

　　c、 优化器做代数优化和存取路径的优化

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

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

　　f、 最后将执行结果返回给用户其次，看一下SQLSERVER的数据存放的结构：一个页面的大小为8K(8060)字节，8个页面为一个盘区，按照B树存放。

　　

　　12、Commit和rollback的区别Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tranexec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。

　　

　　13、在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。

　　

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

　　15、尽可能不使用游标，它占用大量的资源。如果需要row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table变量，用子查询，用Case语句等等。?

　　

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

　　

　　17、注意UNion和UNionall 的区别(union 需要排序去重，会耗费时间)。UNION all好。

　　

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

　　

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

　　

　　20、用sp_configure 'query governor costlimit'或者SETQUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，查询调控器自动取消查询,在查询之前就扼杀掉。 SETLOCKTIME设置锁的时间 。?

　　

　　21、用select top 100 / 10Percent 来限制用户返回的行数或者SET ROWCOUNT来限制操作的行

　　

　　22、在SQL2000以前，一般不要用如下的字句:"IS NULL", "<>", "!=", "!>","!<", "NOT", "NOT EXISTS", "NOT IN","NOT LIKE", and "LIKE '%500'"，因为他们不走索引全是表扫描。也不要在Where字句中的列名加函数，如Convert，substring等,如果必须用函数的时候，创建计算列再创建索引来替代.还可以变通写法：Where SUBSTRING(firstname,1,1) = 'm'改为Where firstname like 'm%'（索引扫描），一定要将函数和列名分开。并且索引不能建得太多和太大。NOTIN会多次扫描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用，现在2000的优化器能够处理了。相同的是ISNULL，"NOT", "NOT EXISTS", "NOT IN"能优化她，而"<>"等还是不能优化，用不到索引。

　　

　　23、使用QueryAnalyzer，查看SQL语句的查询计划和评估分析是否是优化的SQL。一般的20%的代码占据了80%的资源，我们优化的重点是这些慢的地方。

　　

　　24、如果使用了IN或者OR等时发现查询没有走索引，使用显示申明指定索引： Select * FROMPersonMember (INDEX = IX_Title) Where processid IN ('男'，'女')

　　

　　25、将需要查询的结果预先计算好放在表中，查询的时候再Select。这在SQL7.0以前是最重要的手段。例如医院的住院费计算。

　　

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

　　

　　27、数据库有一个原则是代码离数据越近越好，所以优先选择Default,依次为Rules,Triggers,Constraint（约束如外健主健CheckUNIQUE……,数据类型的最大长度等等都是约束）,Procedure.这样不仅维护工作小，编写程序质量高，并且执行的速度快。

　　

　　28、如果要插入大的二进制值到Image列，使用存储过程，千万不要用内嵌Insert来插入(不知JAVA是否)。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍），服务器受到字符后又将他转换成二进制值.存储过程就没有这些动作:方法：Create procedure p_insert as insert into table(Fimage) values (@image),在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

　　

　　29、Between在某些时候比IN 速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。select * from chineseresume where title in ('男','女') Select * fromchineseresume where between '男' and '女' 是一样的。由于in会在比较多次，所以有时会慢些。

　　

　　30、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。

　　

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

　　

　　32、用OR的字句可以分解成多个查询，并且通过UNION连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNION all执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。

　　

　　 33、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用storedprocedure来代替她。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。

　　

　　34、没有必要时不要用DISTINCT和ORDERBY，这些动作可以改在客户端执行（用程序去排序或去重）。它们增加了额外的开销。这同UNION和UNION ALL一样的道理。

　　

　　select top 20ad.companyname,comid,position,ad.referenceid,worklocation,convert(varchar(10),ad.postDate,120) as postDate1,workyear,degreedescriptionFROM jobcn_query.dbo.COMPANYAD_query ad where referenceIDin('JCNAD00329667','JCNAD132168','JCNAD00337748','JCNAD00338345',

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

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

　　'JCNAD00254567','JCNAD00254585','JCNAD00254608',

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

　　'JCNAD00279196','JCNAD00268613')order by postdate desc

　　

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

　　

　　36、当用SelectINTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是 selectINTO. drop table t_lxh begin tran select * into t_lxh from chineseresume wherename = 'XYZ' --commit 在另一个连接中Select * from sysobjects可以看到 Select INTO会锁住系统表，Create table也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。 ?

　　

　　37、一般在GROUP BY个HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select的Where字句选择所有合适的行，Group By用来分组个统计行，Having字句用来剔除多余的分组。这样Group By个Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果Group BY的目的不包括计算，只是分组，那么用Distinct更快 。

　　

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

　　

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

　　

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

　　

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

　　

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

　　

　　c、必须配制如下选项 ANSI_NULLS =ON, ANSI_PADDINGS = ON, …….

　　

　　41、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。反复执行的动态SQL,可以使用临时存储过程，该过程（临时表）被放在Tempdb中。以前由于SQLSERVER对复杂的数学计算不支持，所以不得不将这个工作放在其他的层上而增加网络的开销。SQL2000支持UDFs,现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程。

　　

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

　　

　　43、SelectCOUNT(*)的效率教低，尽量变通他的写法，而EXISTS快.同时请注意区别： select count(Field of null) from Table和 select count(Field of NOT null) from Table 的返回值是不同的！?

　　

　　44、当服务器的内存够多时，配制线程数量 =最大连接数+5，这样能发挥最大的效率；否则使用 配制线程数量<最大连接数启用SQL SERVER的线程池来解决,如果还是数量 =最大连接数+5，严重的损害服务器的性能。

　　

　　45、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。如果锁定顺序没有被预先详细的设计好，死锁很难被发现 。?

　　

　　46、通过SQL ServerPerformance Monitor监视相应硬件的负载 Memory: Page Faults /sec计数器如果该值偶尔走高，表明当时有线程竞争内存。如果持续很高，则内存可能是瓶颈。

　　Process:

　　

　　1、% DPC Time指在范例间隔期间处理器用在缓延程序调用(DPC)接收和提供服务的百分比。(DPC 正在运行的为比标准间隔优先权低的间隔)。 由于 DPC是以特权模式执行的，DPC 时间的百分比为特权时间百分比的一部分。这些时间单独计算并且不属于间隔计算总数的一部分。这个总数显示了作为实例时间百分比的平均忙时。

　　

　　2、%ProcessorTime计数器　如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加一个处理器或换一个更快的处理器。

　　

　　3、% Privileged Time指非闲置处理器时间用于特权模式的百分比。(特权模式是为操作系统组件和操纵硬件驱动程序而设计的一种处理模式。它允许直接访问硬件和所有内存。另一种模式为用户模式，它是一种为应用程序、环境分系统和整数分系统设计的一种有限处理模式。操作系统将应用程序线程转换成特权模式以访问操作系统服务)。特权时间的% 包括为间断和 DPC 提供服务的时间。特权时间比率高可能是由于失败设备产生的大数量的间隔而引起的。这个计数器将平均忙时作为样本时间的一部分显示。

　　

　　4、% UserTime表示耗费CPU的数据库操作，如排序，执行aggregatefunctions等。如果该值很高，可考虑增加索引，尽量使用简单的表联接，水平分割大表格等方法来降低该值。 Physical Disk: CurretnDisk Queue Length计数器该值应不超过磁盘数的1.5~2倍。要提高性能，可增加磁盘。 SQLServer:Cache HitRatio计数器该值越高越好。如果持续低于80%，应考虑增加内存。 注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。

　　

　　47、分析select emp_nameform employee where salary > 3000在此语句中若salary是Float类型的，则优化器对其进行优化为Convert(float,3000)，因为3000是个整数，我们应在编程时使用3000.0而不要等运行时让DBMS进行转化。同样字符和整型数据的转换。

　　

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

　　

　　selecta.personMemberID, * from chineseresume a,personmember b where personMemberID =b.referenceid and a.personMemberID = 'JCNPRH39681' （A = B ,B = '号码'）

　　

　　selecta.personMemberID, * from chineseresume a,personmember b where a.personMemberID= b.referenceid and a.personMemberID = 'JCNPRH39681' and b.referenceid ='JCNPRH39681' （A = B ,B = '号码'， A = '号码'）

　　

　　selecta.personMemberID, * from chineseresume a,personmember b where b.referenceid ='JCNPRH39681' and a.personMemberID = 'JCNPRH39681' （B = '号码'， A = '号码'）

　　

　　49、

　　

　　(1)IF 没有输入负责人代码 THENcode1=0 code2=9999 ELSE code1=code2=负责人代码 END IF 执行SQL语句为: Select 负责人名 FROMP2000 Where 负责人代码>=:code1 AND负责人代码 <=:code2

　　

　　(2)IF 没有输入负责人代码 THEN　Select 负责人名 FROM P2000 ELSE code= 负责人代码 Select 负责人代码 FROM P2000 Where负责人代码=:code END IF第一种方法只用了一条SQL语句,第二种方法用了两条SQL语句。在没有输入负责人代码时,第二种方法显然比第一种方法执行效率高,因为它没有限制条件;在输入了负责人代码时,第二种方法仍然比第一种方法效率高,不仅是少了一个限制条件,还因相等运算是最快的查询运算。我们写程序不要怕麻烦

　　

　　50、关于JOBCN现在查询分页的新方法（如下），用性能优化器分析性能的瓶颈，如果在I/O或者网络的速度上，如下的方法优化切实有效，如果在CPU或者内存上，用现在的方法更好。请区分如下的方法，说明索引越小越好。

　　

　　begin

　　

　　DECLARE @local_variabletable (FID int identity(1,1),ReferenceID varchar(20))

　　

　　insert into@local_variable (ReferenceID)

　　

　　select top 100000ReferenceID from chineseresume order by ReferenceID

　　

　　select * from@local_variable where Fid > 40 and fid <= 60

　　

　　end 和

　　

　　begin

　　

　　DECLARE @local_variabletable (FID int identity(1,1),ReferenceID varchar(20))

　　

　　insert into@local_variable (ReferenceID)

　　

　　select top 100000ReferenceID from chineseresume order by updatedate

　　

　　select * from@local_variable where Fid > 40 and fid <= 60

　　

　　end 的不同

　　

　　begin

　　

　　create table #temp (FIDint identity(1,1),ReferenceID varchar(20))

　　

　　insert into #temp(ReferenceID)

　　

　　select top 100000ReferenceID from chineseresume order by updatedate

　　

　　select * from #tempwhere Fid > 40 and fid <= 60 drop table #temp

　　

　　end

 

另附：存储过程编写经验和优化措施From：网页教学网

 

　　一、适合读者对象：数据库开发程序员，数据库的数据量很多，涉及到对SP（存储过程）的优化的项目开发人员，对数据库有浓厚兴趣的人。　

 

　　二、介绍：在数据库的开发过程中，经常会遇到复杂的业务逻辑和对数据库的操作，这个时候就会用SP来封装数据库操作。如果项目的SP较多，书写又没有一定的规范，将会影响以后的系统维护困难和大SP逻辑的难以理解，另外如果数据库的数据量大或者项目对SP的性能要求很，就会遇到优化的问题，否则速度有可能很慢，经过亲身经验，一个经过优化过的SP要比一个性能差的SP的效率甚至高几百倍。　

 

　　三、内容： 　

 

　　1、开发人员如果用到其他库的Table或View，务必在当前库中建立View来实现跨库操作，最好不要直接使用“databse.dbo.table_name”，因为sp_depends不能显示出该SP所使用的跨库table或view，不方便校验。　　

 

　　2、开发人员在提交SP前，必须已经使用setshowplan on分析过查询计划，做过自身的查询优化检查。 　

 

　　3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点：　　

 

　　a)SQL的使用规范：

 

　　　i.　尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。

 

　　　ii.　尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。

 

　　　iii.　尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

 

　　　iv.　注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件子句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。

 

　　　v.　不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

 

　　　vi.　尽量使用exists代替selectcount(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。

 

　　　vii.　尽量使用“>=”，不要使用“>”。

 

　　　viii.　注意一些or子句和union子句之间的替换

 

　　　ix.　注意表之间连接的数据类型，避免不同类型数据之间的连接。

 

　　　x.　注意存储过程中参数和数据类型的关系。

 

　　　xi.　注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面（400k），那么系统将会进行锁升级，页级锁会升级成表级锁。　　

 

　　b)索引的使用规范：

 

　　　i.　索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。

 

　　　ii.　尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过indexindex_name来强制指定索引

 

　　　iii.　避免对大表查询时进行tablescan，必要时考虑新建索引。

 

　　　iv.　在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。

 

　　　v.　要注意索引的维护，周期性重建索引，重新编译存储过程。　　

 

　　c)tempdb的使用规范：

 

　　　i.　尽量避免使用distinct、orderby、group by、having、join、cumpute，因为这些语句会加重tempdb的负担。

 

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

 

　　　iii.　在新建临时表时，如果一次性插入数据量很大，那么可以使用selectinto代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。

 

　　　iv.　如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。

 

　　　v.　如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后droptable，这样可以避免系统表的较长时间锁定。

 

　　　vi.　慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。　　

 

　　d)合理的算法使用： 　　

 

　　根据上面已提到的SQL优化技术和ASETuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：setstatistics io on, set statistics time on , set showplan on 等。