sql语句优化之SQLServer（详细整理）
这篇⽂章主要介绍了sql语句优化之SQL Server篇，整理的⽐较详细，推荐收藏
MS SQL Server查询优化⽅法
查询速度慢的原因很多，常见如下⼏种
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、扩⼤服务器的内存,Windows 2000和SQL server 2000能⽀持4-8G的内存。配置虚拟内存：虚拟内存⼤⼩应基于计算机上并发运⾏的服务进⾏配置。运⾏ Microsoft SQL Server? 2000 时，可考虑将虚拟内存⼤⼩设置为计算机中安装的物理内存的 1.5 倍。如果另外安装了全⽂检索功能，并打算运⾏ Microsoft 搜索服务以便执⾏全⽂索引和查询，可考虑：将虚拟内存⼤⼩配置为⾄少是计算机中安装的物理内存的 3 倍。将 SQL Server max server memory 服务器配置选项配置为物理内存的 1.5 倍（虚拟内存⼤⼩设置的⼀半）。
7、增加服务器CPU个数；但是必须明⽩并⾏处理串⾏处理更需要资源例如内存。使⽤并⾏还是串⾏程是MsSQL⾃动评估选择的。单个任务分解成多个任务，就可以在处理器上运⾏。例如耽搁查询的排序、连接、扫描和GROUP BY字句同时执⾏，SQL SERVER根据系统的负载情况决定最优的并⾏等级，复杂的需要消耗⼤量的CPU的查询最适合并⾏处理。但是更新操作UPDATE,INSERT， DELETE还不能并⾏处理。
8、如果是使⽤like进⾏查询的话，简单的使⽤index是不⾏的，但是全⽂索引，耗空间。 like 'a%' 使⽤索引 like '%a' 不使⽤索引⽤ like '%a%' 查询时，查询耗时和字段值总长度成正⽐，所以不能⽤CHAR类型，⽽是VARCHAR。对于字段的值很长的建全⽂索引。
9、DB Server 和APPLication Server 分离；OLTP和OLAP分离
10、分布式分区视图可⽤于实现数据库服务器联合体。联合体是⼀组分开管理的服务器，但它们相互协作分担系统的处理负荷。这种通过分区数据形成数据库服务器联合体的机制能够扩⼤⼀组服务器，以⽀持⼤型的多层 Web 站点的处理需要。有关更多信息，参见设计联合数据库服务器。（参照SQL帮助⽂件'分区视图'）
a、在实现分区视图之前，必须先⽔平分区表
b、在创建成员表后，在每个成员服务器上定义⼀个分布式分区视图，并且每个视图具有相同的名称。这样，引⽤分布式分区视图名的查询可以在任何⼀个成员服务器上运⾏。系统操作如同每个成员服务器上都有⼀个原始表的复本⼀样，但其实每个服务器上只有⼀个成员表和⼀个分布式分区视图。数据的位置对应⽤程序是透明的。
11、重建索引 DBCC REINDEX ,DBCC INDEXDEFRAG，收缩数据和⽇志 DBCC SHRINKDB,DBCC SHRINKFILE. 设置⾃动收缩⽇志.对于⼤的数据库不要设置数据库⾃动增长，它会降低服务器的性能。在T-sql的写法上有很⼤的讲究，下⾯列出常见的要点：⾸先，DBMS处理查询计划的过程是这样的：
1、查询语句的词法、语法检查
2、将语句提交给DBMS的查询优化器
3、优化器做代数优化和存取路径的优化
4、由预编译模块⽣成查询规划
5、然后在合适的时间提交给系统处理执⾏
6、最后将执⾏结果返回给⽤户其次，看⼀下SQL SERVER的数据存放的结构：⼀个页⾯的⼤⼩为8K(8060)字节，8个页⾯为⼀个盘区，按照B树存放。
12、Commit和rollback的区别 Rollback：回滚所有的事物。 Commit：提交当前的事物. 没有必要在动态SQL⾥写事物，如果要写请写在外⾯如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程。
13、在查询Select语句中⽤Where字句限制返回的⾏数，避免表扫描，如果返回不必要的数据，浪费了服务器的I/O资源，加重了⽹络的负担降低性能。如果表很⼤，在表扫描的期间将表锁住，禁⽌其他的联接访问表，后果严重。
14、SQL的注释申明对执⾏没有任何影响
15、尽可能不使⽤光标，它占⽤⼤量的资源。如果需要row-by-row地执⾏，尽量采⽤⾮光标技术，如：
在客户端循环，⽤临时表，Table变量，⽤⼦查询，⽤Case语句等等。游标可以按照它所⽀持的提取选项进⾏分类：只进必须按照从第⼀⾏到最后⼀⾏的顺序提取⾏。FETCH NEXT 是唯⼀允许的提取操作，也是默认⽅式。可滚动性可以在游标中任何地⽅随机提取任意⾏。游标的技术在SQL2000下变得功能很强⼤，他的⽬的是⽀持循环。
有四个并发选项
READ_ONLY：不允许通过游标定位更新(Update)，且在组成结果集的⾏中没有锁。
OPTIMISTIC WITH valueS：乐观并发控制是事务控制理论的⼀个标准部分。乐观并发控制⽤于这样的情形，即在打开游标及更新⾏的间隔中，只有很⼩的机会让第⼆个⽤户更新某⼀⾏。当某个游标以此选项打开时，没有锁控制其中的⾏，这将有助于最⼤化其处理能⼒。如果⽤户试图修改某⼀⾏，则此⾏的当前值会与最后⼀次提取此⾏时获取的值进⾏⽐较。如果任何值发⽣改变，则服务器就会知道其他⼈已更新了此⾏，并会返回⼀个错误。如果值是⼀样的，服务器就执⾏修改。选择这个并发选项 OPTIMISTIC WITH ROW VERSIONING：此乐观并发控制选项基于⾏版本控制。使⽤⾏版本控制，其中的表必须具有某种版本标识符，服务器可⽤它来确定该⾏在读⼊游标后是否有所更改。
在 SQL Server 中，这个性能由 timestamp 数据类型提供，它是⼀个⼆进制数字，表⽰数据库中更改的相对顺序。每个数据库都有⼀个全局当前时间戳值：@@DBTS。每次以任何⽅式更改带有 timesta
mp 列的⾏时，SQL Server 先在时间戳列中存储当前的 @@DBTS 值，然后增加 @@DBTS 的值。如果某个表具有 timestamp 列，则时间戳会被记到⾏级。服务器就可以⽐较某⾏的当前时间戳值和上次提取时所存储的时间戳值，从⽽确定该⾏是否已更新。服务器不必⽐较所有列的值，只需⽐较 timestamp 列即可。如果应⽤程序对没有 timestamp 列的表要求基于⾏版本控制的乐观并发，则游标默认为基于数值的乐观并发控制。
SCROLL LOCKS 这个选项实现悲观并发控制。在悲观并发控制中，在把数据库的⾏读⼊游标结果集时，应⽤程序将试图锁定数据库⾏。在使⽤服务器游标时，将⾏读⼊游标时会在其上放置⼀个更新锁。如果在事务内打开游标，则该事务更新锁将⼀直保持到事务被提交或回滚；当提取下⼀⾏时，将除去游标锁。如果在事务外打开游标，则提取下⼀⾏时，锁就被丢弃。因此，每当⽤户需要完全的悲观并发控制时，游标都应在事务内打开。更新锁将阻⽌任何其它任务获取更新锁或排它锁，从⽽阻⽌其它任务更新该⾏。
然⽽，更新锁并不阻⽌共享锁，所以它不会阻⽌其它任务读取⾏，除⾮第⼆个任务也在要求带更新锁的读取。滚动锁根据在游标定义的 SELECT 语句中指定的锁提⽰，这些游标并发选项可以⽣成滚动锁。滚动锁在提取时在每⾏上获取，并保持到下次提取或者游标关闭，以先发⽣者为准。下次提取时，服务器为新提取中的⾏获取滚动锁，并释放上次提取中⾏的滚动锁。滚动锁独⽴于事务锁，并可以保持到⼀个提交或回滚操作之后。如果提交时关闭游标的选项为关，则 COMMIT 语句并不关闭任何
打开的游标，⽽且滚动锁被保留到提交之后，以维护对所提取数据的隔离。所获取滚动锁的类型取决于游标并发选项和游标 SELECT 语句中的锁提⽰。
锁提⽰只读乐观数值乐观⾏版本控制锁定⽆提⽰未锁定未锁定未锁定更新 NOLOCK 未锁定未锁定未锁定未锁定 HOLDLOCK 共享共享共享更新 UPDLOCK 错误更新更新更新 TABLOCKX 错误未锁定未锁定更新其它未锁定未锁定未锁定更新 *指定 NOLOCK 提⽰将使指定了该提⽰的表在游标内是只读的。
16、⽤Profiler来跟踪查询，得到查询所需的时间，出SQL的问题所在;⽤索引优化器优化索引
17、注意UNion和UNion all 的区别。UNION all好
18、注意使⽤DISTINCT，在没有必要时不要⽤，它同UNION⼀样会使查询变慢。重复的记录在查询⾥是没有问题的
19、查询时不要返回不需要的⾏、列
20、⽤sp_configure 'query governor cost limit'或者SET QUERY_GOVERNOR_COST_LIMIT来限制查询消耗的资源。当评估查询消耗的资源超出限制时，服务器⾃动取消查询，在查询之前就扼杀掉。 SET LOCKTIME设置锁的时间
21、⽤select top 100 / 10 Percent 来限制⽤户返回的⾏数或者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%'（索引扫描），⼀定要将函数和列名分开。并且索引不能建得太多和太⼤。NOT IN会多次扫描表，使
⽤EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 来替代，特别是左连接,⽽Exists⽐IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作⽤，现在2000的优
化器能够处理了。相同的是IS NULL，“NOT", "NOT EXISTS", "NOT IN"能优化她，⽽” <> ”等还是不能优化，⽤不到索引。
23、使⽤Query Analyzer，查看SQL语句的查询计划和评估分析是否是优化的SQL。⼀般的20%的代码占据了80%的资源，我们优化的重点是这些慢的地⽅。
24、如果使⽤了IN或者OR等时发现查询没有⾛索引，使⽤显⽰申明指定索引： SELECT * FROM PersonMember (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 * from chineseresume where between '男' and '⼥' 是⼀样的。由于in会在⽐较多次，所以有时会慢些。
30、在必要是对全局或者局部临时表创建索引，有时能够提⾼速度，但不是⼀定会这样，因为索引也耗费⼤量的资源。他的创建同是实际表⼀样。
31、不要建没有作⽤的事物例如产⽣报表时，浪费资源。只有在必要使⽤事物时使⽤它。
32、⽤OR的字句可以分解成多个查询，并且通过UNION 连接多个查询。他们的速度只同是否使⽤索引有关，如果查询需要⽤到联合索引，⽤UNION all执⾏的效率更⾼.多个OR
的字句没有⽤到索引，改写成UNION的形式再试图与索引匹配。⼀个关键的问题是否⽤到索引。
33、尽量少⽤视图，它的效率低。对视图操作⽐直接对表操作慢，可以⽤stored procedure来代替她。特别的是不要⽤视图嵌套，嵌套视图增加了寻原始资料的难度。我们看视图
的本质：它是存放在服务器上的被优化好了的已经产⽣了查询规划的SQL。对单个表检索数据时，不要使⽤指向多个表的视图，直接从表检索或者仅仅包含这个表的视图上读，
否则增加了不必要的开销，查询受到⼲扰.为了加快视图的查询，MsSQL增加了视图索引的功能。
34、没有必要时不要⽤DISTINCT和ORDER BY，这些动作可以改在客户端执⾏。它们增加了额外的开销。这同UNION 和UNION ALL⼀样的道理。
SELECT top 20 adpanyname,comid,ferenceid,worklocation, convert(varchar(10),ad.postDate,120) as postDate1,workyear,degreedescription FROM jobcn_query.dbo.COMPANYAD_ ad where referenceID in('JCNAD00329667','JCNAD132168','JCNAD00337748','JCNAD00338345','JCNAD00333138','JCNAD00303570', 'JCNAD00303569','JCNAD00303568','JCNAD00306698','JC
35、在IN后⾯值的列表中，将出现最频繁的值放在最前⾯，出现得最少的放在最后⾯，减少判断的次数
36、当⽤SELECT INTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时⽤显⽰申明语句，⽽不
是 select INTO. drop table t_lxh begin tran select * into t_lxh from chineseresume where name = '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中。以前由于SQL SERVER对复杂的数学计算不⽀持，所以不
得不将这个⼯作放在其他的层上⽽增加⽹络的开销。SQL2000⽀持UDFs，现在⽀持复杂的数学计算，函数的返回值不要太⼤，这样的开销很⼤。⽤户⾃定义函数象光标⼀样执⾏
的消耗⼤量的资源，如果返回⼤的结果采⽤存储过程
42、不要在⼀句话⾥再三的使⽤相同的函数，浪费资源，将结果放在变量⾥再调⽤更快
43、SELECT COUNT(*)的效率教低，尽量变通他的写法，⽽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 Server Performance Monitor监视相应硬件的负载 Memory: Page Faults / sec计数器如果该值偶尔⾛⾼，表明当时有线程竞争内存。如果持续很⾼，则内存可能是
瓶颈。 Process:
1、% DPC Time 指在范例间隔期间处理器⽤在缓延程序调⽤(DPC)接收和提供服务的百分⽐。(DPC 正在运⾏的为⽐标准间隔优先权低的间隔)。由于 DPC 是以特权模式执⾏
的，DPC 时间的百分⽐为特权时间百分⽐的⼀部分。这些时间单独计算并且不属于间隔计算总数的⼀部分。这个总数显⽰了作为实例时间百分⽐的平均忙时。
2、%Processor Time计数器 如果该参数值持续超过95%，表明瓶颈是CPU。可以考虑增加⼀个处理器或换⼀个更快的处理器。
3、% Privileged Time 指⾮闲置处理器时间⽤于特权模式的百分⽐。(特权模式是为操作系统组件和操纵硬件驱动程序⽽设计的⼀种处理模式。它允许直接访问硬件和所有内存。
另⼀种模式为⽤户模式，它是⼀种为应⽤程序、环境分系统和整数分系统设计的⼀种有限处理模式。操作系统将应⽤程序线程转换成特权模式以访问操作系统服务)。特权时间
的 % 包括为间断和 DPC 提供服务的时间。特权时间⽐率⾼可能是由于失败设备产⽣的⼤数量的间隔⽽引起的。这个计数器将平均忙时作为样本时间的⼀部分显⽰。
4、% User Time表⽰耗费CPU的数据库操作，如排序，执⾏aggregate functions等。如果该值很⾼，可考虑增加索引，尽量使⽤简单的表联接，⽔平分割⼤表格等⽅法来降低该
值。 Physical Disk: Curretn Disk Queue Length计数器该值应不超过磁盘数的1.5~2倍。要提⾼性能，可增加磁盘。 SQLServer:Cache Hit Ratio计数器该值越⾼越好。如果持续
低于80%，应考虑增加内存。注意该参数值是从SQL Server启动后，就⼀直累加记数，所以运⾏经过⼀段时间后，该值将不能反映系统当前值。
47、分析select emp_name form employee where salary > 3000 在此语句中若salary是Float类型的，则优化器对其进⾏优化为Convert(float,3000)，因为3000是个整数，我们应
在编程时使⽤3000.0⽽不要等运⾏时让DBMS进⾏转化。同样字符和整型数据的转换。
======================================================================================================
我们要做到不但会写SQL，还要做到写出性能优良的SQL，以下为笔者学习、摘录、并汇总部分资料与⼤家分享！
（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：
ORACLE 的解析器按照从右到左的顺序处理FROM⼦句中的表名，FROM⼦句中写在最后的表(基础表 driving table)将被最先处理，在FROM⼦句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引⽤的表.
（2） WHERE⼦句中的连接顺序．：
ORACLE采⽤⾃下⽽上的顺序解析WHERE⼦句，根据这个原理，表之间的连接必须写在其他WHERE
条件之前, 那些可以过滤掉最⼤数量记录的条件必须写在WHERE⼦句的末尾.（3） SELECT⼦句中避免使⽤ ‘ * ‘：
ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个⼯作是通过查询数据字典完成的, 这意味着将耗费更多的时间
（4）减少访问数据库的次数：
ORACLE在内部执⾏了许多⼯作: 解析SQL语句, 估算索引的利⽤率, 绑定变量 , 读数据块等；
（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ，建议值为200
（6）使⽤DECODE函数来减少处理时间：
使⽤DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
（7）整合简单,⽆关联的数据库访问：
如果你有⼏个简单的数据库查询语句，你可以把它们整合到⼀个查询中(即使它们之间没有关系)
（8）删除重复记录：
最⾼效的删除重复记录⽅法 ( 因为使⽤了ROWID)例⼦：
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
（9）⽤TRUNCATE替代DELETE：
当删除表中的记录时，在通常情况下, 回滚段(rollback segments ) ⽤来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执⾏删除命令之前的状况) ⽽当运⽤TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运⾏后，数据不能被恢复.因此很少的资源被调⽤，执⾏时间也会很短. (译者按: TRUNCATE只在删除全表适⽤,TRUNCATE是DDL不是DML)
（10）尽量多使⽤COMMIT：
只要有可能，在程序中尽量多使⽤COMMIT, 这样程序的性能得到提⾼，需求也会因为COMMIT所释放的资源⽽减少:
COMMIT所释放的资源:
a. 回滚段上⽤于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费
（11）⽤Where⼦句替换HAVING⼦句：
避免使⽤HAVING⼦句, HAVING 只会在检索出所有记录之后才对结果集进⾏过滤. 这个处理需要排序，总计等操作. 如果能通过WHERE⼦句限制记录的数⽬，那就能减少这⽅⾯的开销. (⾮oracle中)on、where、having这三个都可以加条件的⼦句中，on是最先执⾏，where次之，having最后，因为on是先把不符合条件的记录过滤后才进⾏统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该⽐having快点的，因为它过滤数据后才进⾏sum，在两个表联接时才⽤on的，所以在⼀个表的时候，就剩下where跟having⽐较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是⼀样的，只是where可以使⽤rushmore技术，⽽having就不能，在速度上后者要慢如果要涉及到计算的字段，就表⽰在没计算之前，这个字段的值是不确定
的，根据上篇写的⼯作流程，where的作⽤时间是在计算之前就完成的，⽽having就是在计算后才起作⽤的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on⽐where更早起作⽤。系统⾸先根据各个表之间的联接条件，把多个表合成⼀个临时表后，再由where进⾏过滤，然后再计算，计算完后再由having进⾏过滤。由此可见，要想过滤条件起到正确的作⽤，⾸先要明⽩这个条件应该在什么时候起作⽤，然后再决定放在那⾥
（12）减少对表的查询：
在含有⼦查询的SQL语句中，要特别注意减少对表的查询.例⼦：
SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT
TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)
（13）通过内部函数提⾼SQL效率.：
复杂的SQL往往牺牲了执⾏效率. 能够掌握上⾯的运⽤函数解决问题的⽅法在实际⼯作中是⾮常有意义的
（14）使⽤表的别名(Alias)：
当在SQL语句中连接多个表时, 请使⽤表的别名并把别名前缀于每个Column上.这样⼀来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
（15）⽤EXISTS替代IN、⽤NOT EXISTS替代NOT IN：
在许多基于基础表的查询中，为了满⾜⼀个条件，往往需要对另⼀个表进⾏联接.在这种情况下, 使⽤EXISTS(或NOT EXISTS)通常将提⾼查询的效率. 在⼦查询中,NOT IN⼦句将执⾏⼀个内部的排序和合并. ⽆论在哪种情况下,NOT IN都是最低效的 (因为它对⼦查询中的表执⾏了⼀个全表遍历). 为了避免使⽤NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
例⼦：
（⾼效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.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⼯具来解决问题始终是⼀个
最好的⽅法：
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_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;
（17）⽤索引提⾼效率：
索引是表的⼀个概念部分,⽤来提⾼检索数据的效率，ORACLE使⽤了⼀个复杂的⾃平衡B-tree结构. 通常，通过索引查询数据⽐全表扫描要快. 当ORACLE出执⾏查询和Update 语句的最佳路径时, ORACLE优化器将使⽤索引. 同样在联结多个表时使⽤索引也可以提⾼效率. 另⼀个使⽤索引的好处是，它提供了主键(primary key)的唯⼀性验证.。那些LONG 或LONG RAW数据类型, 你可以索引⼏乎所有的列. 通常, 在⼤型表中使⽤索引特别有效. 当然，你也会发现, 在扫描⼩表时，使⽤索引同样能提⾼效率. 虽然使⽤索引能得到查询效率的提⾼，但是我们也必须注意到它的代价. 索引需要空间来存储，也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本⾝也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理，那些不必要的索引反⽽会使查询反应时间变慢.。定期的重构索引是有必要的.：ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>
18）⽤EXISTS替换DISTINCT：
当提交⼀个包含⼀对多表信息(⽐如部门表和雇员表)的查询时，避免在SELECT⼦句中使⽤DISTINCT. ⼀般可以考虑⽤EXIST替换, EXISTS 使查询更为迅速，因为RDBMS核⼼模块将在⼦查询的条件⼀旦满⾜后,⽴刻返回结果. 例⼦：
(低效):
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E
WHERE D.DEPT_NO = E.DEPT_NO
(⾼效):
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X'
FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
（19） sql语句⽤⼤写的；因为oracle总是先解析sql语句，把⼩写的字母转换成⼤写的再执⾏
（20）在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 WHERE 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_ID 和REGION上都建有索引.
⾼效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNION
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = “MELBOURNE”
低效:
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
如果你坚持要⽤OR, 那就需要返回记录最少的索引列写在最前⾯.
（25）⽤IN来替换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将⽆法使⽤该索引．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录. 如果⾄少有⼀个列不为空，则记录存在于索引中．举例: 如果唯⼀性索引建⽴在表的A列和B列上, 并且表中存在⼀条记录的A,B值为(123,null) , ORACLE将不接受下⼀条具有相同A,B值（123,null）的记录(插⼊). 然⽽如果所有的索引列都为空，ORACLE将认为整个键值为空⽽空不等于空. 因此你可以插⼊1000 条具有相同键值的记录，当然它们都是空! 因为空值不存在于索引列中，所以WHERE⼦句中对索引列进⾏空值⽐较将使ORACLE停⽤该索引.
低效: (索引失效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
⾼效: (索引有效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
（27）总是使⽤索引的第⼀个列：
如果索引是建⽴在多个列上, 只有在它的第⼀个列(leading column)被where⼦句引⽤时，优化器才会选择使⽤该索引. 这也是⼀条简单⽽重要的规则，当仅引⽤索引的第⼆个列时，优化器使⽤了全表扫描⽽
忽略了索引
28）⽤UNION-ALL 替换UNION ( 如果有可能的话)：
当SQL 语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的⽅式被合并, 然后在输出最终结果前进⾏排序. 如果⽤UNION ALL替代UNION, 这样排序就不是必要了. 效率就会因此得到提⾼. 需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使⽤UNION ALL的可⾏性. UNION 将对结果集合排序，这个操作会使⽤到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的. 下⾯的SQL可以⽤来查询排序的消耗量
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
⾼效:
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
（29）⽤WHERE替代ORDER BY：
ORDER BY ⼦句只在两种严格的条件下使⽤索引.
ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.
ORDER BY中所有的列必须定义为⾮空.
sql语句优化方式WHERE⼦句使⽤的索引和ORDER BY⼦句中所使⽤的索引不能并列.
例如:
表DEPT包含以下列:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
低效: (索引不被使⽤)
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
⾼效: (使⽤索引)
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0
（30）避免改变索引列的类型.:
当⽐较不同数据类型的数据时, ORACLE⾃动对列进⾏简单的类型转换.
假设 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 EMP WHERETO_NUMBER(EMP_TYPE)=123
因为内部发⽣的类型转换, 这个索引将不会被⽤到! 为了避免ORACLE对你的SQL进⾏隐式的类型转换, 最好把类型转换⽤显式表现出来. 注意当字符和数值⽐较时, ORACLE会优先转换数值类型到字符类型
（31）需要当⼼的WHERE⼦句:
某些SELECT 语句中的WHERE⼦句不使⽤索引. 这⾥有⼀些例⼦.
在下⾯的例⼦⾥, (1)‘!=' 将不使⽤索引. 记住, 索引只能告诉你什么存在于表中, ⽽不能告诉你什么不存在于表中. (2) ‘||'是字符连接函数. 就象其他函数那样, 停⽤了索引. (3) ‘+'是数学函数. 就象其他数学函数那样, 停⽤了索引. (4)相同的索引列不能互相⽐较，这将会启⽤全表扫描.
（32） a. 如果检索数据量超过30%的表中记录数.使⽤索引将没有显著的效率提⾼.
b. 在特定情况下, 使⽤索引也许会⽐全表扫描慢, 但这是同⼀个数量级上的区别. ⽽通常情况下，使⽤索引⽐全表扫描要块⼏倍乃⾄⼏千倍!
（33）避免使⽤耗费资源的操作:
带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎
执⾏耗费资源的排序(SORT)功能. DISTINCT需要⼀次排序操作, ⽽其他的⾄少需要执⾏两次排序. 通常, 带有UNION, MINUS , INTERSECT的SQL语句都可以⽤其他⽅式重写. 如果你的数据库的SORT_AREA_SIZE调配得好, 使⽤UNION , MINUS, INTERSECT也是可以考虑的, 毕竟它们的可读性很强
（34）优化GROUP BY:
提⾼GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉.下⾯两个查询返回相同结果但第⼆个明显就快了许多.
低效:
1 2 3 4 5SELECT JOB , AVG(SAL) FROM EMP
GROUP by JOB
HAVING JOB = ‘PRESIDENT' OR JOB = ‘MANAGER'
⾼效:
1 2 3 4 5SELECT JOB , AVG(SAL) FROM EMP
WHERE JOB = ‘PRESIDENT' OR JOB = ‘MANAGER' GROUP by JOB
优化SQL查询：如何写出⾼性能SQL语句
1、⾸先要搞明⽩什么叫执⾏计划？
执⾏计划是数据库根据SQL语句和相关表的统计信息作出的⼀个查询⽅案，这个⽅案是由查询优化器⾃动分析产⽣的，⽐如⼀条SQL语句如果⽤来从⼀个 10万条记录的表中查1条记录，那查询优化器会选择“索引查”⽅式，如果该表进⾏了归档，当前只剩下5000条记录了，那查询优化器就会改变⽅案，采⽤ “全表扫描”⽅式。
可见，执⾏计划并不是固定的，它是“个性化的”。产⽣⼀个正确的“执⾏计划”有两点很重要：
(1) SQL语句是否清晰地告诉查询优化器它想⼲什么？
(2) 查询优化器得到的数据库统计信息是否是最新的、正确的？
2、统⼀SQL语句的写法
对于以下两句SQL语句，程序员认为是相同的，数据库查询优化器认为是不同的。
复制代码代码如下:
select * from dual
select * From dual
其实就是⼤⼩写不同，查询分析器就认为是两句不同的SQL语句，必须进⾏两次解析。⽣成2个执⾏计划。所以作为程序员，应该保证相同的查询语句在任何地⽅都⼀致，多⼀个空格都不⾏！
3、不要把SQL语句写得太复杂
我经常看到，从数据库中捕捉到的⼀条SQL语句打印出来有2张A4纸这么长。⼀般来说这么复杂的语句通常都是有问题的。我拿着这2页长的SQL语句去请教原作者，结果他说时间太长，他⼀时也看不懂了。可想⽽知，连原作者都有可能看糊涂的SQL语句，数据库也⼀样会看糊涂。
⼀般，将⼀个Select语句的结果作为⼦集，然后从该⼦集中再进⾏查询，这种⼀层嵌套语句还是⽐较常见的，但是根据经验，超过3层嵌套，查询优化器就很容易给出错误的执⾏计划。因为它被绕晕了。像这种类似⼈⼯智能的东西，终究⽐⼈的分辨⼒要差些，如果⼈都看晕了，我可以保证数据库也会晕的。
另外，执⾏计划是可以被重⽤的，越简单的SQL语句被重⽤的可能性越⾼。⽽复杂的SQL语句只要有⼀个字符发⽣变化就必须重新解析，然后再把这⼀⼤堆垃圾塞在内存⾥。可想⽽知，数据库的效率会何等低下。
4、使⽤“临时表”暂存中间结果
简化SQL语句的重要⽅法就是采⽤临时表暂存中间结果，但是，临时表的好处远远不⽌这些，将临时结果暂存在临时表，后⾯的查询就在tempdb中了，这可以避免程序中多次扫描主表，也⼤⼤减少了程序执⾏中“共享锁”阻塞“更新锁”，减少了阻塞，提⾼了并发性能。
5、 OLTP系统SQL语句必须采⽤绑定变量
复制代码代码如下:
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论