关于SQLSERVER数据库开发过程中的优化方案

关于SQLSERVER数据库开发过程中的优化方案

 

SQLSERVER查询慢的原因与注意点

SQLSERVER查询速度慢的原因很多，常见如下几种：
　　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语句等等。游标可以按照它所支持的提取选项进行分类： 只进 必须按照从第一行到最后一行的顺序提取行。FETCHNEXT 是唯一允许的提取操作,也是默认方式。可滚动性可以在游标中任何地方随机提取任意行。游标的技术在SQL2000下变得功能很强大，他的目的是支持循环。有四个并发选项 READ_ONLY：不允许通过游标定位更新(Update)，且在组成结果集的行中没有锁。 OPTIMISTIC WITHvalueS:乐观并发控制是事务控制理论的一个标准部分。乐观并发控制用于这样的情形，即在打开游标及更新行的间隔中，只有很小的机会让第二个用户更新某一行。当某个游标以此选项打开时，没有锁控制其中的行，这将有助于最大化其处理能力。如果用户试图修改某一行，则此行的当前值会与最后一次提取此行时获取的值进行比较。如果任何值发生改变，则服务器就会知道其他人已更新了此行，并会返回一个错误。如果值是一样的，服务器就执行修改。选择这个并发选项OPTIMISTIC WITH ROW VERSIONING:此乐观并发控制选项基于行版本控制。使用行版本控制，其中的表必须具有某种版本标识符，服务器可用它来确定该行在读入游标后是否有所更改。在 SQL Server 中，这个性能由 timestamp 数据类型提供，它是一个二进制数字，表示数据库中更改的相对顺序。每个数据库都有一个全局当前时间戳值：@@DBTS。每次以任何方式更改带有 timestamp 列的行时，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 procedurep_insert as insert into table(Fimage) values (@image), 在前台调用这个存储过程传入二进制参数，这样处理速度明显改善。

　　29、Between在某些时候比IN 速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。 select * fromchineseresume 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 ad.companyname,comid,position,ad.referenceid,worklocation, convert(varchar(10),ad.postDate,120) as　postDate1,workyear,degreedescrīption FROM jobcn_query.dbo.COMPANYAD_query ad where referenceID in

('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、当用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，这样能发挥最大的效率;否则使用 配制线程数量<最大连接数启用SQLSERVER的线程池来解决,如果还是数量 = 最大连接数+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%，应考虑增加内存。 注意该参数值是从SQLServer启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。

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

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

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

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

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

　　49、

　　(1)IF 没有输入负责人代码 THEN code1=0 code2=9999 ELSE code1=code2=负责人代码 END IF 执行SQL语句为: Select 负责人名FROM P2000 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_variable table (FID int identity(1,1),ReferenceID varchar(20)) 
　　
　　insert into @local_variable (ReferenceID) 
　　
　　select top 100000 ReferenceID from chineseresume order by ReferenceID 
　　
　　select * from @local_variable where Fid > 40 and fid <= 60 
　　
　　end 和 
　　
　　begin 
　　
　　DECLARE @local_variable table (FID int identity(1,1),ReferenceID varchar(20)) 
　　
　　insert into @local_variable (ReferenceID) 
　　
　　select top 100000 ReferenceID from chineseresume order by updatedate 
　　
　　select * from @local_variable where Fid > 40 and fid <= 60 
　　
　　end 的不同 
　　
　　begin 
　　
　　create table #temp (FID int identity(1,1),ReferenceID varchar(20)) 
　　
　　insert into #temp (ReferenceID) 
　　
　　select top 100000 ReferenceID from chineseresume order by updatedate 
　　
　　select * from #temp where 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前，必须已经使用set showplan on分析过查询计划，做过自身的查询优化检查。
　　3、高程序运行效率，优化应用程序，在SP编写过程中应该注意以下几点：

　　a)SQL的使用规范：
　　i.　尽量避免大事务操作，慎用holdlock子句，提高系统并发能力。
　　ii.　尽量避免反复访问同一张或几张表，尤其是数据量较大的表，可以考虑先根据条件提取数据到临时表中，然后再做连接。
　　iii.　尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写;如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

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

　　v.　不要在where子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
　　vi.　尽量使用exists代替select count(1)来判断是否存在记录，count函数只有在统计表中所有行数时使用，而且count(1)比count(*)更有效率。

　　vii.　尽量使用“>=”，不要使用“>”。
　　viii.　注意一些or子句和union子句之间的替换
　　ix.　注意表之间连接的数据类型，避免不同类型数据之间的连接。
　　x.　注意存储过程中参数和数据类型的关系。
　　xi.　注意insert、update操作的数据量，防止与其他应用冲突。如果数据量超过200个数据页面(400k)，那么系统将会进行锁升级，页级锁会升级成表级锁。

　　b)索引的使用规范：
　　i.　索引的创建要与应用结合考虑，建议大的OLTP表不要超过6个索引。
　　ii.　尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index_name来强制指定索引
　　iii.　避免对大表查询时进行table scan，必要时考虑新建索引。
　　iv.　在使用索引字段作为条件时，如果该索引是联合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用。

　　v.　要注意索引的维护，周期性重建索引，重新编译存储过程。
　　c)tempdb的使用规范：

　　i.　尽量避免使用distinct、order by、group by、having、join、cumpute，因为这些语句会加重tempdb的负担。
　　ii.　避免频繁创建和删除临时表，减少系统表资源的消耗。
　　iii.　在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度;如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。

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

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

　　vi.　慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。
　　d)合理的算法使用：
　　根据上面已提到的SQL优化技术和ASE Tuning手册中的SQL优化内容,结合实际应用,采用多种算法进行比较,以获得消耗资源最少、效率最高的方法。具体可用ASE调优命令：set statistics io on, set statistics time on , set showplan on 等。

 

---

 

SQL 语句优化

 

提高数据库性能的方式有两种

一、一种是DBA通过对数据库的各个方面调优

   调整数据库:共享池,java池,高速缓存,大型池,java池   针对于window操作系统 32位,oracle内存占用，最大为1.7G,超过则不作用,因此这几项值之和不应超过1.7G

   目前各池参数为:

         共享池:512MB

         高速缓存:904MB

         大型池:64MB

         java池:40MB

         PGA:312MB

二、对sql语句的优化

1         总纲

  

l       建立必要的索引

这 次传授的降龙十八掌，总纲只有一句话：建立必要的索引，这就是后面降龙十八掌的内功基础。这一点看似容易实际却很难。难就难在如何判断哪些索引是必要的， 哪些又是不必要的。判断的最终标准是看这些索引是否对我们的数据库性能有所帮助。具体到方法上，就必须熟悉数据库应用程序中的所有SQL语句，从中统计出常用的可能对性能有影响的部分SQL，分析、归纳出作为Where条 件子句的字段及其组合方式；在这一基础上可以初步判断出哪些表的哪些字段应该建立索引。其次，必须熟悉应用程序。必须了解哪些表是数据操作频繁的表；哪些 表经常与其他表进行连接；哪些表中的数据量可能很大；对于数据量大的表，其中各个字段的数据分布情况如何；等等。对于满足以上条件的这些表，必须重点关 注，因为在这些表上的索引，将对SQL语句的性能产生举足轻重的影响。不过下面还是总结了一下降龙十八掌内功的入门基础，建立索引常用的规则如下：

1、表的主键、外键必须有索引；

2、数据量超过300的表应该有索引；

3、经常与其他表进行连接的表，在连接字段上应该建立索引；

4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；

5、索引应该建在选择性高的字段上；

6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；

7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：

   A、正确选择复合索引中的主列字段，一般是选择性较好的字段；

   B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；

   C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；

   D、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；

  E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

8、频繁进行数据操作的表，不要建立太多的索引；

9、删除无用的索引，避免对执行计划造成负面影响；

以 上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充 分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。 另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。

第一 避免对列的操作

任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。  

例1：下列SQL条件语句中的列都建有恰当的索引，但30万行数据情况下执行速度却非常慢：  

select * from record where substrb(CardNo,1,4)='5378'(13秒) 

select * from record where amount/30< 1000（11秒） 

select * from record where to_char(ActionTime,'yyyymmdd')='19991201'（10秒） 

由于where子句中对列的任何操作结果都是在SQL运行时逐行计算得到的，因此它不得不进行表扫描，而没有使用该列上面的索引；如果这些结果在查询编译时就能得到，那么就可以被SQL优化器优化，使用索引，避免表扫描，因此将SQL重写如下：

select * from record where CardNo like '5378%'（< 1秒）

select * from record where amount < 1000*30（< 1秒）

select * from record where ActionTime= to_date ('19991201' ,'yyyymmdd')（< 1秒）

差别是很明显的！

第二 避免不必要的类型转换

需要注意的是，尽量避免潜在的数据类型转换。如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致全表扫描。

例2：表tab1中的列col1是字符型（char)，则以下语句存在类型转换：

select col1,col2 from tab1 where col1>10，

应该写为： select col1,col2 from tab1 where col1>'10'。

第三 增加查询的范围限制

增加查询的范围限制，避免全范围的搜索。

例3：以下查询表record 中时间ActionTime小于2001年3月1日的数据：

       select * from record where ActionTime < to_date ('20010301' ,'yyyymm')

查询计划表明，上面的查询对表进行全表扫描，如果我们知道表中的最早的数据为2001年1月1日，那么，可以增加一个最小时间，使查询在一个完整的范围之内。修改如下： select * from record where

ActionTime < to_date ('20010301' ,'yyyymm')

and   ActionTime > to_date ('20010101' ,'yyyymm')

后一种SQL语句将利用上ActionTime字段上的索引，从而提高查询效率。把'20010301'换成一个变量，根据取值的机率，可以有一半以上的机会提高效率。同理，对于大于某个值的查询，如果知道当前可能的最大值，也可以在Where子句中加上 “AND 列名< MAX(最大值)”。

第四 尽量去掉"IN"、"OR"

含有"IN"、"OR"的Where子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。  

例4：     select count(*) from stuff where id_no in('0','1')（23秒）

可以考虑将or子句分开：  

select count(*) from stuff where id_no='0' 

select count(*) from stuff where id_no='1'

然后再做一个简单的加法，与原来的SQL语句相比，查询速度更快。

第五 尽量去掉 "<>"

尽量去掉 "<>"，避免全表扫描，如果数据是枚举值，且取值范围固定，则修改为"OR"方式。

例5：

UPDATE SERVICEINFO SET STATE=0 WHERE STATE<>0;

以上语句由于其中包含了"<>"，执行计划中用了全表扫描（TABLE ACCESS FULL），没有用到state字段上的索引。实际应用中，由于业务逻辑的限制，字段state为枚举值，只能等于0，1或2，而且，值等于=1，2的很少，因此可以去掉"<>"，利用索引来提高效率。

修改为：UPDATE SERVICEINFO SET STATE=0 WHERE STATE = 1 OR STATE = 2 。进一步的修改可以参考第4种方法。

第六 去掉Where子句中的IS NULL和IS NOT NULL

Where字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉Where子句中的IS NULL和IS NOT NULL。

第七 索引提高数据分布不均匀时查询效率

索引的选择性低，但数据的值分布差异很大时，仍然可以利用索引提高效率。A、数据分布不均匀的特殊情况下，选择性不高的索引也要创建。

表ServiceInfo中数据量很大，假设有一百万行，其中有一个字段DisposalCourseFlag，取值范围为枚举值：[0，1，2，3，4，5，6，7]。按照前面说的索引建立的规则，“选择性不高的字段不应该建立索引，该字段只有8种取值，索引值的重复率很高，索引选择性明显很低，因此不建索引。然而，由于该字段上数据值的分布情况非常特殊，具体如下表：

 

取值范围	1~5	6	7
占总数据量的百分比	1%	98%	1%

而且，常用的查询中，查询DisposalCourseFlag<6 的情况既多又频繁，毫无疑问，如果能够建立索引，并且被应用，那么将大大提高这种情况的查询效率。因此，我们需要在该字段上建立索引。

第八 利用HINT强制指定索引

在ORACLE优化器无法用上合理索引的情况下，利用HINT强制指定索引。

继续上面7的例子，ORACLE缺省认定，表中列的值是在所有数据行中均匀分布的，也就是说，在一百万数据量下，每种DisposalCourseFlag值各有12.5万数据行与之对应。假设SQL搜索条件DisposalCourseFlag=2，利用DisposalCourseFlag列上的索引进行数据搜索效率，往往不比全表扫描的高，ORACLE因此对索引“视而不见”，从而在查询路径的选择中，用其他字段上的索引甚至全表扫描。根据我们上面的分析，数据值的分布很特殊，严重的不均匀。为了利用索引提高效率，此时，一方面可以单独对该字段或该表用analyze语句进行分析，对该列搜集足够的统计数据，使ORACLE在查询选择性较高的值时能用上索引；另一方面，可以利用HINT提示，在SELECT关键字后面，加上“/*+ INDEX(表名称，索引名称）*/”的方式，强制ORACLE优化器用上该索引。

比如： select * from serviceinfo where DisposalCourseFlag=1 ;

上面的语句，实际执行中ORACLE用了全表扫描，加上蓝色提示部分后，用到索引查询。如下：

select /*+ INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG) */  *

from serviceinfo where DisposalCourseFlag=1;

请注意，这种方法会加大代码维护的难度，而且该字段上索引的名称被改变之后，必须要同步所有指定索引的HINT代码，否则HINT提示将被ORACLE忽略掉。

第九 屏蔽无用索引

继续上面8的例子，由于实际查询中，还有涉及到DisposalCourseFlag=6的查询，而此时如果用上该字段上的索引，将是非常不明智的，效率也极低。因此这种情况下，我们需要用特殊的方法屏蔽该索引，以便ORACLE选择其他字段上的索引。比如，如果字段为数值型的就在表达式的字段名后，添加“+ 0”，为字符型的就并上空串：“||""”

如： select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = '36' 。

不过，不要把该用的索引屏蔽掉了，否则同样会产生低效率的全表扫描。

第十 分解复杂查询，用常量代替变量

对于复杂的Where条件组合，Where中含有多个带索引的字段，考虑用IF语句分情况进行讨论；同时，去掉不必要的外来参数条件，减低复杂度，以便在不同情况下用不同字段上的索引。

继续上面9的例子，对于包含

Where (DisposalCourseFlag < v_DisPosalCourseFlag) or (v_DisPosalCourseFlag is null) and ....的查询，(这里v_DisPosalCourseFlag为一个输入变量，取值范围可能为[NULL，0，1，2，3，4，5，6，7])，可以考虑分情况用IF语句进行讨论，类似：

IF v_DisPosalCourseFlag =1 THEN

Where DisposalCourseFlag = 1 and ....

ELSIF v_DisPosalCourseFlag =2 THEN

Where DisposalCourseFlag = 2 and .... 

。。。。。。

第十一 like子句尽量前端匹配

因为like参数使用的非常频繁，因此如果能够对like子句使用索引，将很高的提高查询的效率。

例6：select * from city where name like ‘%S%’

以上查询的执行计划用了全表扫描（TABLE ACCESS FULL），如果能够修改为：

select * from city where name like ‘S%’

那么查询的执行计划将会变成（INDEX RANGE SCAN），成功的利用了name字段的索引。这意味着Oracle SQL优化器会识别出用于索引的like子句，只要该查询的匹配端是具体值。因此我们在做like查询时，应该尽量使查询的匹配端是具体值，即使用like ‘S%’。

第十二 用Case语句合并多重扫描

我们常常必须基于多组数据表计算不同的聚集。例如下例通过三个独立查询：

例8：1）select count(*) from emp where sal<1000;

     2）select count(*) from emp where sal between 1000 and 5000;

     3）select count(*) from emp where sal>5000;

这样我们需要进行三次全表查询，但是如果我们使用case语句：

select

count (sale when sal <1000

then 1 else null end)              count_poor,

count (sale when between 1000 and 5000

then 1 else null end)              count_blue_collar,

count (sale when sal >5000

then 1 else null end)              count_poor

from emp;

这样查询的结果一样，但是执行计划只进行了一次全表查询。

第十三 使用nls_date_format

例9：

select * from record where to_char(ActionTime,'mm')='12'

这个查询的执行计划将是全表查询，如果我们改变nls_date_format，

SQL>alert session set nls_date_formate=’MM’;

现在重新修改上面的查询：

select * from record where ActionTime='12'

这样就能使用actiontime上的索引了，它的执行计划将是（INDEX RANGE SCAN）。

第十四 使用基于函数的索引

前面谈到任何对列的操作都可能导致全表扫描，例如：

select * from emp where substr(ename,1,2)=’SM’;

但是这种查询在客服系统又经常使用，我们可以创建一个带有substr函数的基于函数的索引，

create index emp_ename_substr on eemp ( substr(ename,1,2) );

 

这样在执行上面的查询语句时，这个基于函数的索引将排上用场，执行计划将是（INDEX RANGE SCAN）。

第十五 基于函数的索引要求等式匹配

上面的例子中，我们创建了基于函数的索引，但是如果执行下面的查询：

select * from emp where substr(ename,1,1)=’S’

得到的执行计划将还是（TABLE ACCESS FULL），因为只有当数据列能够等式匹配时，基于函数的索引才能生效，这样对于这种索引的计划和维护的要求都很高。请注意，向表中添加索引是非常危险的操作，因为这将导致许多查询执行计划的变更。然而，如果我们使用基于函数的索引就不会产生这样的问题，因为Oracle只有在查询使用了匹配的内置函数时才会使用这种类型的索引。

第十六 使用分区索引

在用分析命令对分区索引进行分析时，每一个分区的数据值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息来提取出那些只与SQL查询相关的数据分区。

例如，假设你已经定义了一个分区索引，并且某个SQL语句需要在一个索引分区中进行一次索引扫描。Oracle会仅仅访问这个索引分区，而且会在这个分区上调用一个此索引范围的快速全扫描。因为不需要访问整个索引，所以提高了查询的速度。

第十七 使用位图索引

位图索引可以从本质上提高使用了小于1000个唯一数据值的数据列的查询速度，因为在位图索引中进行的检索是在RAM中完成的，而且也总是比传统的B树索引的速度要快。对于那些少于1000个唯一数据值的数据列建立位图索引，可以使执行效率更快。

第十八 决定使用全表扫描还是使用索引

和 所有的秘笈一样，最后一招都会又回到起点，最后我们来讨论一下是否需要建立索引，也许进行全表扫描更快。在大多数情况下，全表扫描可能会导致更多的物理磁 盘输入输出，但是全表扫描有时又可能会因为高度并行化的存在而执行的更快。如果查询的表完全没有顺序，那么一个要返回记录数小于10％的查询可能会读取表中大部分的数据块，这样使用索引会使查询效率提高很多。但是如果表非常有顺序，那么如果查询的记录数大于40％时，可能使用全表扫描更快。因此，有一个索引范围扫描的总体原则是：

1）对于原始排序的表 仅读取少于表记录数40％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的40％的查询应该使用全表扫描。

2）对于未排序的表    仅读取少于表记录数7％的查询应该使用索引范围扫描。反之，读取记录数目多于表记录数的7％的查询应该使用全表扫描。

3       总结

以上的招式，是完全可以相互结合同时运用的。而且各种方法之间相互影响，紧密联系。这种联系既存在一致性，也可能带来冲突，当冲突发生时，需要根据实际情况进行选择，没有固定的模式。最后决定SQL优化功力的因素就是对ORACLE内功的掌握程度了。

另外，值得注意的是：随着时间的推移和数据的累计与变化，ORACLE对SQL语句的执行计划也会改变，比如：基于代价的优化方法，随着数据量的增大，优化器可能错误的不选择索引而采用全表扫描。这种情况可能是因为统计信息已经过时，在数据量变化很大后没有及时分析表；但如果对表进行分析之后，仍然没有用上合理的索引，那么就有必要对SQL语句用HINT提示，强制用合理的索引。但这种HINT提示也不能滥用，因为这种方法过于复杂，缺乏通用性和应变能力，同时也增加了维护上的代价；相对来说，基于函数右移、去掉“IN ，OR ，<> ，IS NOT NULL ”、分解复杂的SQL语句等等方法，却是“放之四海皆准”的，可以放心大胆的使用。

同时，优化也不是“一劳永逸”的，必须随着情况的改变进行相应的调整。当数据库设计发生变化，包括更改表结构：字段和索引的增加、删除或改名等；业务逻辑发生变化：如查询方式、取值范围发生改变等等。在这种情况下，也必须对原有的优化进行调整，以适应效率上的需求。

 

 

---

 

 

 

 在ASP开发之中如何避免对数据库操作的低效率，以下是在网上收集的一些方法，供大家参考。

方法一、尽量使用复杂的SQL来代替简单的一堆SQL. 
　 同样的事务，一个复杂的SQL完成的效率高于一堆简单SQL完成的效率。有多个查询时，要善于使用JOIN。 
oRs=oConn.Execute("Select * FROM Books") 
while not ors.Eof 
strSQL = "Select * FROM Authors Where AuthorID="&oRs("AuthorID") ors2=oConn.Execute(strSQL) 
Response.write ors("Title")&">>"&oRs2("Name")&"
&q uot; 
ors.MoveNext() 
wend 
要比下面的代码慢： 
strSQL="Select Books.Title,Authors.Name FROM Books JOIN Authors ON Authors.AuthorID=Books.AuthorID" 
ors=oConn.Execute(strSQL) 
while not ors.Eof 
Response.write ors("Title")&">>"&oRs("Name")&"
&qu ot; 
ors.MoveNext() 
wend 

方法二、尽量避免使用可更新Recordset 
oRs=oConn.Execute("Select * FROM Authors Where AuthorID=17",3,3) 
ors("Name")="DarkMan" 
ors.Update() 

要比下面的代码慢： 
strSQL = "Update Authors SET Name='DarkMan' Where AuthorID=17" 
oConn.Execute strSQL 

方法三、更新数据库时，尽量采用批处理更新 
　 将所有的SQL组成一个大的批处理SQL，并一次运行；这比一个一个地更新数据要有效率得多。这样也更加满足你进行事务处理 的需要： 
strSQL="" 
strSQL=strSQL&"SET XACT_ABORT ON\n"; 
strSQL=strSQL&"BEGIN TRANSACTION\n"; 
strSQL=strSQL&"Insert INTO orders(OrdID,CustID,OrdDat) VALUES('9999','1234',GETDATE())\n"; 
strSQL=strSQL&"Insert INTO orderRows(OrdID,OrdRow,Item,Qty) VALUES('9999','01','G4385',5)\n"; 
strSQL=strSQL&"Insert INTO orderRows(OrdID,OrdRow,Item,Qty) VALUES('9999','02','G4726',1)\n"; 
strSQL=strSQL&"COMMIT TRANSACTION\n"; 
strSQL=strSQL&"SET XACT_ABORT OFF\n"; 
oConn.Execute(strSQL); 
其中，SET XACT_ABORT OFF 语句告诉SQL Server，如果下面的事务处理过程中，如果遇到错误，就取消已经完成的事务。 

方法四、数据库索引 
　 那些将在Where子句中出现的字段，你应该首先考虑建立索引；那些需要排序的字段，也应该在考虑之列 。 
在MS Access中建立索引的方法：在Access里面选择需要索引的表，点击“设计”，然后设置相应字段的索引. 
在MS SQL Server中建立索引的方法：在SQL Server管理器中，选择相应的表，然后“设计表”，点击右键，选择“Properties”，选择“indexes/keys” 

方法五、避免使Text字段太大 
　 当字符串的值大小不固定时，用varchar比用char的效果要好 些。我曾经看到一个例子程序，字段被定义为TEXT(255)，但是他的取值经常只有20个字符。这个数据表有50k个记录，从而使这个数据库很大，大的数据库必然较慢。