整理：sql语句优化之SQLServer

1、整理： sql 语句优化之SQL ServerMSSQLServer 查询优化方法查询速度慢的原因很多，常见如下几种1、没有索引或者没有用到索引(这是查询慢最常见的问题，是程序设计的缺陷 )2、 I/O 吞吐量小，形成了瓶颈效应。3、没有创建计算列导致查询不优化。4、内存不足5、网络速度慢6、查询出的数据量过大（可以采用多次查询，其他的方法降低数据量）7、锁或者死锁 (这也是查询慢最常见的问题，是程序设计的缺陷 )8、sp_lock,sp_who,活动的用户查看,原因是读写竞争资源。9、返回了不必要的行和列10 、查询语句不好，没有优化可以通过如下方法来优化查询1、把数据、日志、索引放到不同的

2、I/O设备上，增加读取速度，以前可以将Tempdb应放在RAID0上， SQL2000不在支持。数据量（尺寸）越大，提高I/O越重要.2、纵向、横向分割表， 减少表的尺寸 (sp_spaceuse)3、升级硬件4、根据查询条件 ,建立索引 ,优化索引、优化访问方式，限制结果集的数据量。注意填充因子要适当（最好是使用默认值 0）。索引应该尽量小， 使用字节数小的列建索引好（参照索引的创建） ,不要对有限的几个值的字段建单一索引如性别字段5、提高网速 ;6、扩大服务器的内存,Windows2000 和 SQLserver2000 能支持 4-8G 的内存。配置虚拟内存：虚拟内存大小应基于计算机上并

3、发运行的服务进行配置。运行MicrosoftSQLServer?2000时，可考虑将虚拟内存大小设置为计算机中安装的物理内存的1.5倍。如果另外安装了全文检索功能，并打算运行Microsoft搜索服务以便执行全文索引和查询，可考虑：将虚拟内存大小配置为至少是计算机中安装的物理内存的3倍。将SQLServermaxservermemory服务器配置选项配置为物理内存的1.5倍（虚拟内存大小设置的一半）。7、增加服务器CPU 个数 ;但是必须明白并行处理串行处理更需要资源例如内存。使用并行还是串行程是MsSQL 自动评估选择的。单个任务分解成多个任务，就可以在处理器上运行。例如耽搁查询的排序、连接

4、、扫描和GROUPBY 字句同时执行，SQLSERVER根据系统的负载情况决定最优的并行等级，复杂的需要消耗大量的CPU 的查询最适合并行处理。但是更新操作UPDATE,INSERT， DELETE还不能并行处理。8、如果是使用like进行查询的话， 简单的使用index是不行的，但是全文索引，耗空间。likea%使用索引like%a不使用索引用like%a%查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR 。对于字段的值很长的建全文索引。9、DBServer和APPLicationServer分离； OLTP和OLAP分离、分布式分区视图可用于实现数据库服务器

5、联合体。联合体是一组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩大一组服务器，以支持大型的多层Web站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照 SQL 帮助文件 分区视图 ）a、在实现分区视图之前，必须先水平分区表b、在创建成员表后，在每个成员服务器上定义一个分布式分区视图，并且每个视图具有相同的名称。这样，引用分布式分区视图名的查询可以在任何一个成员服务器上运行。系统操作如同每个成员服务器上都有一个原始表的复本一样，但其实每个服务器上只有一个成员表和一个分布式分区视图。 数据的位置对应用程序是透明的。11 、重建索

6、引DBCCREINDEX,DBCCINDEXDEFRAG,收缩数据和日志DBCCSHRINKDB,DBCCSHRINKFILE.设置自动收缩日志.对于大的数据库不要设置数据库自动增长，它会降低服务器的性能。在 T-sql 的写法上有很大的讲究，下面列出常见的要点：首先，DBMS 处理查询计划的过程是这样的：1、查询语句的词法、语法检查2、将语句提交给DBMS 的查询优化器3、优化器做代数优化和存取路径的优化4、由预编译模块生成查询规划5、然后在合适的时间提交给系统处理执行6、最后将执行结果返回给用户其次，看一下SQLSERVER 的数据存放的结构：一个页面的大小为8K(8060)字节，8 个页

7、面为一个盘区，按照B 树存放。12 、Commit和rollback的区别Rollback:回滚所有的事物。Commit:提交当前的事物.没有必要在动态SQL里写事物，如果要写请写在外面如：begintranexec(s)committrans或者将动态SQL写成函数或者存储过程。13 、在查询Select语句中用Where字句限制返回的行数,避免表扫描 ,如果返回不必要的数据，浪费了服务器的I/O 资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重。14 、SQL 的注释申明对执行没有任何影响15、尽可能不使用光标， 它占用大量的资源。如果需要

8、 row-by-row地执行，尽量采用非光标技术,如：在客户端循环，用临时表，Table 变量，用子查询，用Case语句等等。游标可以按照它所支持的提取选项进行分类：只进必须按照从第一行到最后一行的顺序提取行。FETCHNEXT是唯一允许的提取操作,也是默认方式。可滚动性可以在游标中任何地方随机提取任意行。游标的技术在 SQL2000下变得功能很强大，他的目的是支持循环。有四个并发选项READ_ONLY ：不允许通过游标定位更新(Update) ，且在组成结果集的行中没有锁。OPTIMISTICWITHvalueS: 乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形，即在打

9、开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值是一样的，服务器就执行修改。选择这个并发选项OPTIMISTICWITHROWVERSIONING:此乐观并发控制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用它来确定该行在读入游标后是否有所更改。在SQLServer中，这个性能由timestamp数据类型提供，它是一个二

10、进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全局当前时间戳值： DBTS 。每次以任何方式更改带有timestamp列的行时， SQLServer先在时间戳列中存储当前的DBTS值，然后增加DBTS的值。如果某个表具有timestamp列，则时间戳会被记到行级。服务器就可以比较某行的当前时间戳值和上次提取时所存储的时间戳值，从而确定该行是否已更新。服务器不必比较所有列的值，只需比较timestamp列即可。如果应用程序对没有timestamp列的表要求基于行版本控制的乐观并发，则游标默认为基于数值的乐观并发控制。SCROLLLOCKS这个选项实现悲观并发控制。在悲观并发控制中，在把

11、数据库的行读入游标结果集时，应用程序将试图锁定数据库行。在使用服务器游标时，将行读入游标时会在其上放置一个更新锁。如果在事务内打开游标，则该事务更新锁将一直保持到事务被提交或回滚；当提取下一行时，将除去游标锁。如果在事务外打开游标，则提取下一行时，锁就被丢弃。因此，每当用户需要完全的悲观并发控制时，游标都应在事务内打开。更新锁将阻止任何其它任务获取更新锁或排它锁，从而阻止其它任务更新该行。然而，更新锁并不阻止共享锁，所以它不会阻止其它任务读取行，除非第二个任务也在要求带更新锁的读取。滚动锁根据在游标定义的SELECT语句中指定的锁提示，这些游标并发选项可以生成滚动锁。滚动锁在提取时在每行上获取

12、，并保持到下次提取或者游标关闭，以先发生者为准。下次提取时，服务器为新提取中的行获取滚动锁，并释放上次提取中行的滚动锁。滚动锁独立于事务锁，并可以保持到一个提交或回滚操作之后。如果提交时关闭游标的选项为关，则COMMIT语句并不关闭任何打开的游标，而且滚动锁被保留到提交之后，以维护对所提取数据的隔离。所获取滚动锁的类型取决于游标并发选项和游标SELECT语句中的锁提示。锁提示只读乐观数值乐观行版本控制锁定无提示未锁定未锁定未锁定更新NOLOCK未锁定未锁定未锁定未锁定HOLDLOCK共享共享共享更新UPDLOCK错误更新更新更新TABLOCKX错误未锁定未锁定更新其它未锁定未锁定未锁定更新*指

13、定NOLOCK提示将使指定了该提示的表在游标内是只读的。16 、用 Profiler 来跟踪查询，得到查询所需的时间，找出SQL 的问题所在 ;用索引优化器优化索引17 、注意UNion和UNionall的区别。 UNIONall好18 、注意使用DISTINCT，在没有必要时不要用，它同UNION 一样会使查询变慢。重复的记录在查询里是没有问题的19、查询时不要返回不需要的行、列20 、用sp_configurequerygovernorcostlimit 或者SETQUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器自动取消查询,在

14、查询之前就扼杀掉。SETLOCKTIME设置锁的时间21 、用 selecttop100/10Percent来限制用户返回的行数或者SETROWCOUNT来限制操作的行22 、在SQL2000以前，一般不要用如下的字句:ISNULL, ,!=,! ,! ,NOT,NOTEXISTS,NOTIN,NOTLIKE,andLIKE%500 ，因为他们不走索引全是表扫描。也不要在WHere 字句中的列名加函数， 如 Convert ，substring等 ,如果必须用函数的时候，创建计算列再创建索引来替代 .还可以变通写法：WHERESUBSTRING(firstname,1,1)=m 改为 WHER

15、Efirstnamelikem% （索引扫描），一定要将函数和列名分开。并且索引不能建得太多和太大。NOTIN会多次扫描表，使用EXISTS、NOTEXISTS，IN,LEFTOUTERJOIN来替代，特别是左连接,而Exists比IN更快，最慢的是NOT 操作 .如果列的值含有空，以前它的索引不起作用，现在2000 的优化器能够处理了。相同的是ISNULL ，“ NOT,NOTEXISTS,NOTIN 能优化她，而” ”等还是不能优化，用不到索引。23 、使用 QueryAnalyzer ，查看 SQL 语句的查询计划和评估分析是否是优化的SQL 。一般的 20% 的代码占据了80%的资源，

16、我们优化的重点是这些慢的地方。24 、如果使用了IN 或者 OR 等时发现查询没有走索引，使用显示申明指定索引：SELECT*FROMPersonMember(INDEX=IX_Title)WHEREprocessidIN(男，女)25、将需要查询的结果预先计算好放在表中，查询的时候再SELECT。这在SQL7.0 以前是最重要的手段。例如医院的住院费计算。26 、 MIN()和MAX() 能使用到合适的索引、数据库有一个原则是代码离数据越近越好，所以优先选择 Default, 依次为 Rules,Triggers,Constraint （约束如外健主健 CheckUNIQUE , 数据类型的

17、最大长度等等都是约束） ,Procedure. 这样不仅维护工作小，编写程序质量高，并且执行的速度快。28 、如果要插入大的二进制值到 Image 列，使用存储过程， 千万不要用内嵌INsert 来插入(不知 JAVA 是否 )。因为这样应用程序首先将二进制值转换成字符串（尺寸是它的两倍） ，服务器受到字符后又将他转换成二进制值 .存储过程就没有这些动作 : 方法： Createprocedurep_insertas insertintotable(Fimage)values(image),在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。29 、Between 在某些时候比IN 速

18、度更快 ,Between能够更快地根据索引找到范围。用查询优化器可见到差别。select*fromchineseresumewheretitlein(男 ,女)Select* fromchineseresumewherebetween男 and女 是一样的。由于in 会在比较多次，所以有时会慢些。30 、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。31 、不要建没有作用的事物例如产生报表时，浪费资源。只有在必要使用事物时使用它。32 、用 OR 的字句可以分解成多个查询，并且通过UNION连接多个查询。他们的速度只

19、同是否使用索引有关,如果查询需要用到联合索引，用UNIONall 执行的效率更高 .多个 OR 的字句没有用到索引，改写成UNION 的形式再试图与索引匹配。一个关键的问题是否用到索引。、尽量少用视图，它的效率低。对视图操作比直接对表操作慢 ,可以用 stored procedure 来代替她。特别的是不要用视图嵌套 ,嵌套视图增加了寻找原始资料的难度。 我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的 SQL 。对单个表检索数据时， 不要使用指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销 ,查询受到干扰 .为了加快视图的查询， MsS

20、QL增加了视图索引的功能。34 、没有必要时不要用DISTINCT和ORDERBY ，这些动作可以改在客户端执行。 它们增加了额外的开销。这同UNION和UNIONALL一样的道理。SELECTtop20panyname,comid,position,ad.referenceid,worklocation,convert(varchar(10),ad.postDate,120)aspostDate1,workyear,degreedescriptionFROMadwherereferenceIDin(JCNAD00329667,JCNAD132168,JCNAD00337748,JCNAD00

21、338345,JCNAD00333138,JCNAD00303570,JCNAD00303569,JCNAD00303568,JCNAD00306698,JCNAD00231935,JCNAD00231933,JCNAD00254567,JCNAD00254585,JCNAD00254608,JCNAD00254607,JCNAD00258524,JCNAD00332133,JCNAD00268618,JCNAD00279196,JCNAD00268613)orderbypostdatedesc35 、在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，减少判断的次数36

22、、当用 SELECTINTO 时，它会锁住系统表 (sysobjects ，sysindexes等等 )，阻塞其他的连接的存取。创建临时表时用显示申明语句，而不是selectINTO.droptablet_lxhbegintranselect*intot_lxhfromchineseresumewherename=XYZ-commit在另一个连接中SELECT*fromsysobjects可以看到SELECTINTO会锁住系统表，Createtable也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。37 、一般在 G

23、ROUPBY个 HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select的Where 字句选择所有合适的行，GroupBy 用来分组个统计行， Having 字句用来剔除多余的分组。这样GroupBy个 Having 的开销小，查询快.对于大的数据行进行分组和Having 十分消耗资源。如果GroupBY 的目的不包括计算，只是分组，那么用Distinct 更快38 、一次更新多条记录比分多次更新每次一条快,就是说批处理好39 、少用临时表，尽量用结果集和Table 类性的变量来代替它,Table类型的变量比临时表好40 、在SQL20

24、00下，计算字段是可以索引的，需要满足的条件如下：a、计算字段的表达是确定的b、不能用在TEXT,Ntext， Image数据类型c、必须配制如下选项ANSI_NULLS=ON,ANSI_PADDINGS=ON,.41、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过、并且被组织到一个执行规划里、且存储在数据库中的 SQL 语句，是控制流语言的集合，速度当然快。反复执行的动态 SQL, 可以使用临时存储过程，该过程（临时表）被放在 Tempdb 中。以前由于 SQLSERVER 对复杂的数学计算不支持， 所以不得不将这个工作放在其他的层上而增加网络的开

25、销。SQL2000支持 UDFs, 现在支持复杂的数学计算，函数的返回值不要太大，这样的开销很大。用户自定义函数象光标一样执行的消耗大量的资源，如果返回大的结果采用存储过程42 、不要在一句话里再三的使用相同的函数，浪费资源 ,将结果放在变量里再调用更快43 、 SELECTCOUNT(*)的效率教低，尽量变通他的写法，而EXISTS快 .同时请注意区别：selectcount(Fieldofnull)fromTable和selectcount(FieldofNOTnull)fromTable的返回值是不同的。44 、当服务器的内存够多时，配制线程数量=最大连接数+5 ，这样能发挥最大的效率；

26、否则使用配制线程数量3000在此语句中若 salary 是 Float 类型的，则优化器对其进行优化为Convert(float,3000)，因为 3000 是个整数， 我们应在编程时使用 3000.0 而不要等运行时让DBMS 进行转化。同样字符和整型数据的转换。=我们要做到不但会写SQL, 还要做到写出性能优良的SQL, 以下为笔者学习、摘录、并汇总部分资料与大家分享！1）选择最有效率的表名顺序 ( 只在基于规则的优化器中有效)：ORACLE的解析器按照从右到左的顺序处理FROM 子句中的表名， FROM 子句中写在最后的表(基础表driving table)将被最先处理，在FROM 子句

27、中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3 个以上的表连接查询 , 那就需要选择交叉表 (intersection table) 作为基础表 , 交叉表是指那个被其他表所引用的表 .（2） WHERE 子句中的连接顺序 ：ORACLE 采用自下而上的顺序解析WHERE 子句 ,根据这个原理 ,表之间的连接必须写在其他WHERE 条件之前 , 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾 .（3） SELECT 子句中避免使用 *：ORACLE 在解析的过程中 , 会将 * 依次转换成所有的列名 , 这个工作是通过查询数据字典完成的 , 这意味着将耗费

28、更多的时间（4） 减少访问数据库的次数：ORACLE 在内部执行了许多工作: 解析 SQL 语句 , 估算索引的利用率 , 绑定变量, 读数据块等；（5） 在 SQL*Plus , SQL*Forms和 Pro*C 中重新设置ARRAYSIZE参数 , 可以增加每次数据库访问的检索数据量 ,建议值为 200（6） 使用 DECODE函数来减少处理时间：使用 DECODE函数可以避免重复扫描相同记录或重复连接相同的表 .7） 整合简单 ,无关联的数据库访问：如果你有几个简单的数据库查询语句 ,你可以把它们整合到一个查询中 (即使它们之间没有关系 )8） 删除重复记录：最高效的删除重复记录方法(

29、因为使用了ROWID) 例子：DELETE FROM EMP E WHERE E.ROWID (SELECTMIN(X.ROWID)FROM EMP X WHERE X.EMP_NO = E.EMP_NO);9） 用 TRUNCATE 替代 DELETE ：当删除表中的记录时 ,在通常情况下 , 回滚段 (rollback segments ) 用来存放可以被恢复的信息 . 如果你没有COMMIT事务 ,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE 时 , 回滚段不再存放任何可被恢复的信息 .当命令运行后 ,数据不能被恢复 .因此

30、很少的资源被调用 ,执行时间也会很短 . ( 译者按 : TRUNCATE 只在删除全表适用,TRUNCATE 是 DDL 不是 DML)10 ） 尽量多使用 COMMIT ：只要有可能 ,在程序中尽量多使用 COMMIT, 这样程序的性能得到提高 ,需求也会因为 COMMIT 所释放的资源而减少 : COMMIT 所释放的资源 :a. 回滚段上用于恢复数据的信息.被程序语句获得的锁c. redo log buffer中的空间d. ORACLE为管理上述3 种资源中的内部花费（11 ） 用 Where 子句替换HAVING 子句：避免使用 HAVING 子句 , HAVING 只会在检索出所有

31、记录之后才对结果集进行过滤 . 这个处理需要排序 ,总计等操作 . 如果能通过 WHERE 子句限制记录的数目 ,那就能减少这方面的开销 . ( 非 oracle 中 )on 、where 、 having 这三个都可以加条件的子句中， on 是最先执行， where 次之， having 最后，因为 on 是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where 也应该比having 快点的，因为它过滤数据后才进行sum ，在两个表联接时才用on 的，所以在一个表的时候，就剩下 where 跟 having 比较了。在这单表查询统计的情况下

32、，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是 where 可以使用 rushmore技术，而 having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程， where 的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where 进行过滤，然后再计算，计算完后再由 having 进行过滤。 由此可见， 要想过滤条件起到正确的作用，首先要明白这个

33、条件应该在什么时候起作用，然后再决定放在那里（12 ） 减少对表的查询：在含有子查询的SQL 语句中 ,要特别注意减少对表的查询.例子：SELECT TAB_NAME FROM TABLES WHERE(TAB_NAME,DB_VER) = ( SELECTTAB_NAME,DB_VER FROM TAB_COLUMNS WHEREVERSION = 604)（13 ） 通过内部函数提高SQL 效率 .：复杂的 SQL 往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的14 ） 使用表的别名 (Alias) ：当在 SQL 语句中连接多个表时, 请使用表的别名

34、并把别名前缀于每个Column 上 .这样一来 ,就可以减少解析的时间并减少那些由Column 歧义引起的语法错误.15 ）用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN ：在许多基于基础表的查询中 ,为了满足一个条件 ,往往需要对另一个表进行联接 .在这种情况下 , 使用 EXISTS( 或 NOTEXISTS)通常将提高查询的效率.在子查询中,NOT IN子句将执行一个内部的排序和合并.无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN , 我们可以把它改写成外连接(Outer Joins) 或 NO

35、T EXISTS.例子：（高效） SELECT * FROM EMP (基础表 ) WHERE EMPNO 0 AND EXISTS (SELECT X FROM DEPT WHEREDEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB)(低效 )SELECT * FROM EMP (基础表 ) WHERE EMPNO 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = MELB)（16 ） 识别 低效执行 的 SQL 语句：虽然目前各种关于SQL 优化的图形化工具层出不穷,但是写出自己的 SQL 工具来解决问题始终

36、是一个最好的方法：SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,ROUND(BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,SQL_TEXTFROM V$SQLAREAWHERE EXECUTIONS0AND BUFFER_GETS 0AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS 0.8ORDER BY 4 DESC;（17 ） 用索引提高效率：索引是表的一个概念部分 ,用来提高

37、检索数据的效率， ORACLE 使用了一个复杂的自平衡 B-tree 结构 . 通常 ,通过索引查询数据比全表扫描要快 . 当 ORACLE 找出执行查询和 Update 语句的最佳路径时 , ORACLE 优化器将使用索引 .同样在联结多个表时使用索引也可以提高效率 . 另一个使用索引的好处是 ,它提供了主键 (primary key) 的唯一性验证 .。那些 LONG 或 LONG RAW 数据类型 , 你可以索引几乎所有的列. 通常 , 在大型表中使用索引特别有效 . 当然 ,你也会发现 ,在扫描小表时 ,使用索引同样能提高效率 . 虽然使用索引能得到查询效率的提高 ,但是我们也必须注意

38、到它的代价 . 索引需要空间来存储 ,也需要定期维护 , 每当有记录在表中增减或索引列被修改时 , 索引本身也会被修改 . 这意味着每条记录的 INSERT , DELETE , UPDATE 将为此多付出 4 , 5 次的磁盘 I/O . 因为索引需要额外的存储空间和处理 ,那些不必要的索引反而会使查询反应时间变慢 .。定期的重构索引是有必要的 .：ALTER INDEX REBUILD 18 ） 用 EXISTS 替换 DISTINCT ：当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在 SELECT 子句中使用 DISTINCT. 一般可以考虑用EXIST 替换 , EX

39、ISTS使查询更为迅速,因为 RDBMS 核心模块将在子查询的条件一旦满足后,立刻返回结果 . 例子：(低效 ):SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP EWHERE D.DEPT_NO = E.DEPT_NO(高效 ):SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT XFROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);19 ） sql 语句用大写的； 因为 oracle 总是先解析 sql 语句，把小写的字母转换成大写的再执行20

40、 ） 在 java 代码中尽量少用连接符“”连接字符串！21 ） 避免在索引列上使用 NOT 通常，我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当 ORACLE”遇到” NOT,他就会停止使用索引转而执行全表扫描.（22 ） 避免在索引列上使用计算WHERE 子句中，如果索引列是函数的一部分优化器将不使用索引而使用全表扫描举例 :低效：SELECT FROM DEPT WHERE SAL * 12 25000;高效 :SELECT FROM DEPT WHERE SAL 25000/12;23 ） 用= 替代 高效 :SELECT * FROM EMP W

41、HERE DEPTNO =4低效 :SELECT * FROM EMP WHERE DEPTNO 3两者的区别在于, 前者 DBMS 将直接跳到第一个DEPT 等于4 的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3 的记录.24 ） 用 UNION 替换 OR ( 适用于索引列 )通常情况下 , 用 UNION 替换 WHERE 子句中的 OR 将会起到较好的效果 . 对索引列使用 OR 将造成全表扫描 . 注意 , 以上规则只针对多个索引列有效 . 如果有 column 没有被索引, 查询效率可能会因为你没有选择 OR 而降低 . 在下面的例子中 , LOC

42、_ID 和 REGION 上都建有索引 .高效 :SELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE LOC_ID = 10UNIONSELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE REGION =“ MELBOURNE”低效 :SELECT LOC_ID , LOC_DESC , REGION FROM LOCATIONWHERE LOC_ID = 10 OR REGION = “ MELBOURNE”如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面 .25） 用 IN 来

43、替换 OR这是一条简单易记的规则，但是实际的执行效果还须检验，在 ORACLE8i 下，两者的执行路径似乎是相同的低效 :SELECT . FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30高效SELECT FROM LOCATION WHERE LOC_IN IN(10,20,30);（26 ） 避免在索引列上使用IS NULL 和 IS NOT NULL避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引对于单列索引，如果列包含空值，索引中将不存在此记录 . 对于复合索引，如果每个列都为空，索引中同样不存在此

44、记录 .如果至少有一个列不为空，则记录存在于索引中举例:如果唯一性索引建立在表的A 列和B 列上,并且表中存在一条记录的A,B值为 (123,null) , ORACLE将不接受下一条具有相同A,B值（ 123,null）的记录(插入 ).然而如果所有的索引列都为空， ORACLE 将认为整个键值为空而空不等于空 . 因此你可以插入 1000 条具有相同键值的记录 ,当然它们都是空 ! 因为空值不存在于索引列中 ,所以 WHERE子句中对索引列进行空值比较将使ORACLE停用该索引 .低效 : (索引失效 )SELECT FROM DEPARTMENT WHERE DEPT_CODEIS NO

45、T NULL;高效 : (索引有效 )SELECT FROM DEPARTMENT WHERE DEPT_CODE=0;27 ） 总是使用索引的第一个列：如果索引是建立在多个列上 , 只有在它的第一个列 (leadingcolumn) 被 where 子句引用时 ,优化器才会选择使用该索引 . 这也是一条简单而重要的规则， 当仅引用索引的第二个列时 , 优化器使用了全表扫描而忽略了索引28 ） 用 UNION-ALL替换 UNION (如果有可能的话)：当 SQL 语句需要 UNION 两个查询结果集合时 ,这两个结果集合会以 UNION-ALL 的方式被合并 , 然后在输出最终结果前进行排序

46、 . 如果用 UNION ALL 替代 UNION, 这样排序就不是必要了 . 效率就会因此得到提高. 需要注意的是，UNION ALL将重复输出两个结果集合中相同记录.因此各位还是要从业务需求分析使用UNION ALL的可行性.UNION将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存 . 对于这块内存的优化也是相当重要的 . 下面的 SQL 可以用来查询排序的消耗量低效：SELECT ACCT_NUM, BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERE TRAN_DATE = 31-DEC-95UNIONSELECT ACCT_NUM,

47、BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERE TRAN_DATE = 31-DEC-95高效 :SELECT ACCT_NUM, BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERE TRAN_DATE = 31-DEC-95UNION ALLSELECT ACCT_NUM, BALANCE_AMTFROM DEBIT_TRANSACTIONSWHERE TRAN_DATE = 31-DEC-9529 ） 用 WHERE 替代 ORDER BY ：ORDER BY子句只在两种严格的条件下使用索引.ORDER BY中所有的列必须包含在相

48、同的索引中并保持在索引中的排列顺序.ORDER BY中所有的列必须定义为非空.WHERE 子句使用的索引和ORDER BY子句中所使用的索引不能并列 .例如 :表 DEPT 包含以下列 :DEPT_CODE PK NOT NULL DEPT_DESC NOT NULLDEPT_TYPE NULL低效 : ( 索引不被使用 )SELECT DEPT_CODE FROM DEPT ORDER BYDEPT_TYPE高效 : (使用索引 )SELECT DEPT_CODE FROM DEPT WHEREDEPT_TYPE 0（30 ） 避免改变索引列的类型.:当比较不同数据类型的数据时, ORACL

49、E自动对列进行简单的类型转换 .假设EMPNO 是一个数值类型的索引列.SELECT FROM EMP WHERE EMPNO = 123实际上 ,经过 ORACLE类型转换 , 语句转化为 :SELECT FROM EMP WHERE EMPNO =TO_NUMBER( 123)幸运的是 ,类型转换没有发生在索引列上,索引的用途没有被改变 .现在 ,假设 EMP_TYPE是一个字符类型的索引列.SELECT FROM EMP WHERE EMP_TYPE = 123这个语句被ORACLE 转换为 :SELECT FROM EMPWHERETO_NUMBER(EMP_TYPE)=123因为内部

50、发生的类型转换, 这个索引将不会被用到! 为了避免 ORACLE 对你的 SQL 进行隐式的类型转换 , 最好把类型转换用显式表现出来 . 注意当字符和数值比较时 , ORACLE会优先转换数值类型到字符类型31 ） 需要当心的 WHERE 子句 :某些 SELECT语句中的 WHERE 子句不使用索引. 这里有一些例子 .在下面的例子里 , (1) 将!=不使用索引 . 记住 , 索引只能告诉你什么存在于表中 , 而不能告诉你什么不存在于表中 . (2) | 是字符连接函数.就象其他函数那样 , 停用了索引 . (3) + 是数学函数 . 就象其他数学函数那样 , 停用了索引 . (4) 相

51、同的索引列不能互相比较,这将会启用全表扫描.（32 ） a. 如果检索数据量超过30% 的表中记录数 .使用索引将没有显著的效率提高.b. 在特定情况下 , 使用索引也许会比全表扫描慢, 但这是同一个数量级上的区别. 而通常情况下 ,使用索引比全表扫描要块几倍乃至几千倍!（33 ） 避免使用耗费资源的操作:带有 DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的 SQL 语句会启动SQL 引擎执行耗费资源的排序 (SORT) 功能 . DISTINCT 需要一次排序操作 , 而其他的至少需要执行两次排序 . 通常 , 带有 UNION, MINUS , INTER

52、SECT 的 SQL 语句都可以用其他方式重写 . 如果你的数据库的 SORT_AREA_SIZE 调配得好 , 使用UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强34 ） 优化 GROUP BY:提高 GROUP BY语句的效率 , 可以通过将不需要的记录在GROUP BY之前过滤掉 .下面两个查询返回相同结果但第二个明显就快了许多.低效 :SELECT JOB , AVG(SAL)FROM EMPGROUP by JOBHAVING JOB = PRESIDENTOR JOB = MANAGER高效 :SELECT JOB , AVG(SAL)FRO

53、M EMPWHERE JOB = PRESIDENTOR JOB = MANAGERGROUP by JOB=优化 SQL 查询：如何写出高性能SQL 语句1、 首先要搞明白什么叫执行计划？执行计划是数据库根据SQL 语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条 SQL 语句如果用来从一个10 万条记录的表中查1条记录，那查询优化器会选择“索引查找”方式，如果该表进行了归档，当前只剩下5000 条记录了，那查询优化器就会改变方案，采用“全表扫描”方式。可见，执行计划并不是固定的，它是“个性化的”。产生一个正确的“执行计划”有两点很重要：SQL 语句是

54、否清晰地告诉查询优化器它想干什么？查询优化器得到的数据库统计信息是否是最新的、正确的？2、 统一 SQL 语句的写法对于以下两句SQL 语句，程序员认为是相同的，数据库查询优化器认为是不同的。select * from dual select * From dual复制代码其实就是大小写不同， 查询分析器就认为是两句不同的 SQL 语句，必须进行两次解析。生成 2 个执行计划。所以作为程序员，应该保证相同的查询语句在任何地方都一致，多一个空格都不行！3、 不要把 SQL 语句写得太复杂我经常看到，从数据库中捕捉到的一条SQL 语句打印出来有 2 张 A4 纸这么长。一般来说这么复杂的语句通常都

55、是有问题的。我拿着这 2 页长的 SQL 语句去请教原作者，结果他说时间太长，他一时也看不懂了。可想而知，连原作者都有可能看糊涂的 SQL 语句，数据库也一样会看糊涂。一般，将一个 Select 语句的结果作为子集，然后从该子集中再进行查询，这种一层嵌套语句还是比较常见的，但是根据经验，超过3 层嵌套，查询优化器就很容易给出错误的执行计划。因为它被绕晕了。像这种类似人工智能的东西，终究比人的分辨力要差些，如果人都看晕了，我可以保证数据库也会晕的。另外，执行计划是可以被重用的，越简单的SQL 语句被重用的可能性越高。而复杂的SQL 语句只要有一个字符发生变化就必须重新解析，然后再把这一大堆垃圾塞

56、在内存里。可想而知，数据库的效率会何等低下。4、 使用“临时表”暂存中间结果简化 SQL 语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb 中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。5、 OLTP 系统 SQL 语句必须采用绑定变量select * from orderheader where changetime 2010-10-20 00:00:01select * from orderheader where changetime 2010-0

57、9-22 00:00:01复制代码以上两句语句，查询优化器认为是不同的SQL 语句，需要解析两次。如果采用绑定变量select * from orderheader where changetime chgtimechgtime变量可以传入任何值，这样大量的类似查询可以重用该执行计划了，这可以大大降低数据库解析SQL 语句的负担。一次解析，多次重用，是提高数据库效率的原则。6、 绑定变量窥测事物都存在两面性， 绑定变量对大多数OLTP 处理是适用的，但是也有例外。比如在where 条件中的字段是“倾斜字段”的时候。“倾斜字段”指该列中的绝大多数的值都是相同的，比如一张人口调查表，其中“民族”这

58、列， 90% 以上都是汉族。那么如果一个 SQL 语句要查询30 岁的汉族人口有多少，那“民族”这列必然要被放在where 条件中。这个时候如果采用绑定变量nation 会存在很大问题。试想如果 nation 传入的第一个值是“汉族”，那整个执行计划必然会选择表扫描。然后，第二个值传入的是“布依族”，按理说“布依族”占的比例可能只有万分之一，应该采用索引查找。但是，由于重用了第一次解析的“汉族”的那个执行计划，那么第二次也将采用表扫描方式。这个问题就是著名的“绑定变量窥测”，建议对于“倾斜字段”不要采用绑定变量。7、 只在必要的情况下才使用begin tranSQL Server中一句 SQL

59、 语句默认就是一个事务，在该语句执行完成后也是默认commit 的。其实，这就是begin tran的一个最小化的形式，好比在每句语句开头隐含了一个begin tran，结束时隐含了一个commit。有些情况下， 我们需要显式声明begin tran，比如做“插、删、改”操作需要同时修改几个表，要求要么几个表都修改成功，要么都不成功。begin tran可以起到这样的作用，它可以把若干 SQL 语句套在一起执行，最后再一起commit 。好处是保证了数据的一致性，但任何事情都不是完美无缺的。Begintran 付出的代价是在提交之前，所有SQL 语句锁住的资源都不能释放，直到commit 掉。

60、可见，如果Begin tran套住的 SQL 语句太多，那数据库的性能就糟糕了。 在该大事务提交之前，必然会阻塞别的语句，造成 block 很多。Begin tran使用的原则是，在保证数据一致性的前提下，begin tran套住的 SQL 语句越少越好！ 有些情况下可以采用触发器同步数据，不一定要用begin tran 。8、 一些 SQL 查询语句应加上nolock在 SQL 语句中加 nolock 是提高 SQL Server 并发性能的重要手段，在 oracle 中并不需要这样做， 因为 oracle 的结构更为合理，有 undo 表空间保存“数据前影”，该数据如果在修改中还未 com