sql性能优化（1）

1.查询的模糊匹配

     尽量避免在一个复杂查询里面使用 LIKE '%parm1%'—— 红色标识位置的百分号会导致相关列的索引无法使用，最好不要用.

解决办法:

其实只需要对该脚本略做改进，查询速度便会提高近百倍。改进方法如下：

        a、修改前台程序——把查询条件的供应商名称一栏由原来的文本输入改为下拉列表，用户模糊输入供应商名称时，直接在前台就帮忙定位到具体的供应商，这样在调用后台程序时，这列就可以直接用等于来关联了。

        b、直接修改后台——根据输入条件，先查出符合条件的供应商，并把相关记录保存在一个临时表里头，然后再用临时表去做复杂关联

2.索引问题

        在做性能跟踪分析过程中，经常发现有不少后台程序的性能问题是因为缺少合适索引造成的，有些表甚至一个索引都没有。这种情况往往都是因为在设计表时，没去 定义索引，而开发初期，由于表记录很少，索引创建与否，可能对性能没啥影响，开发人员因此也未多加重视。然一旦程序发布到生产环境，随着时间的推移，表记 录越来越多，这时缺少索引，对性能的影响便会越来越大了。

        这个问题需要数据库设计人员和开发人员共同关注

法则：不要在建立的索引的数据列上进行下列操作:

避免对索引字段进行计算操作

避免在索引字段上使用not，<>，!=

避免在索引列上使用IS NULL和IS NOT NULL

避免在索引列上出现数据类型转换

避免在索引字段上使用函数

避免建立索引的列中使用空值。

3.复杂操作

部分UPDATE、SELECT 语句 写得很复杂（经常嵌套多级子查询）——可以考虑适当拆成几步，先生成一些临时数据表，再进行关联操作

4.update

同一个表的修改在一个过程里出现好几十次，如：

                update table1

                set col1=...

                where col2=...;

               

                update table1

                set col1=...

                where col2=...

                ......

        象这类脚本其实可以很简单就整合在一个UPDATE语句来完成（前些时候在协助xxx项目做性能问题分析时就发现存在这种情况）

5.在可以使用UNION ALL的语句里，使用了UNION

UNION 因为会将各查询子集的记录做比较，故比起UNION ALL ，通常速度都会慢上许多。一般来说，如果使用UNION ALL能满足要求的话，

                务必使用UNION ALL。还有一种情况大家可能会忽略掉，就是虽然要求几个子集的并集需要过滤掉重复记录，但由于脚本的特殊性，不可能存在重复记录，这时便应该使用 UNION ALL，如xx模块的某个查询程序就曾经存在这种情况，见，由于语句的特殊性，在这个脚本

                中几个子集的记录绝对不可能重复，故可以改用UNION ALL）

6.在WHERE 语句中，尽量避免对索引字段进行计算操作

                这个常识相信绝大部分开发人员都应该知道，但仍有不少人这么使用，我想其中一个最主要的原因可能是为了编写方便吧，但如果仅为了编

                写简单而损害了性能，那就不可取了

                9月份在对XX系统做性能分析时发现，有大量的后台程序存在类似用法，如：

                ......

                where trunc(create_date)=trunc(:date1)

                虽然已对create_date 字段建了索引，但由于加了TRUNC，使得索引无法用上。此处正确的写法应该是

                where create_date>=trunc(:date1) and create_date<trunc(:date1)+1

                或者是

                where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60)

                注意：因between 的范围是个闭区间（greater than or equal to low value and less than or equal to high value.），

                故严格意义上应该再减去一个趋于0的小数，这里暂且设置成减去1秒（1/(24*60*60)），如果不要求这么精确的话，可以略掉这步

7.对Where 语句的法则

7.1 避免在WHERE子句中使用in，not  in，or 或者having。

可以使用 exist 和not exist代替 in和not in。

可以使用表链接代替 exist。

Having可以用where代替，如果无法代替可以分两步处理。

例子

SELECT *  FROM ORDERS WHERE CUSTOMER_NAME NOT IN

                    (SELECT CUSTOMER_NAME FROM CUSTOMER)

优化

SELECT *  FROM ORDERS WHERE CUSTOMER_NAME not exist

                    (SELECT CUSTOMER_NAME FROM CUSTOMER)

7.2 不要以字符格式声明数字，要以数字格式声明字符值。（日期同样）

否则会使索引无效，产生全表扫描。

例子

使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;

不要使用：SELECT emp.ename, emp.job FROM emp WHERE emp.empno = ‘7369’

7.3 WHERE后面的条件顺序影响

 Oracle从下到上处理Where子句中多个查询条件，所以表连接语句应写在其他Where条件前，可以过滤掉最大数量记录的条件必须写在Where子句的末尾。

      

       WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如

    Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1

    Select * from zl_yhjbqk where xh_bz=1  and dy_dj = '1KV以下'

    以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的 比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。

8.对Select语句的法则

在应用程序、包和过程中限制使用select * from table这种方式。

例子

使用

SELECT empno,ename,category FROM emp WHERE empno = '7369‘

而不要使用

SELECT * FROM emp WHERE empno = '7369'

9. 排序

避免使用耗费资源的操作

带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行，耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序

10.临时表

慎重使用临时表可以极大的提高系统性能

 11.ORDER BY

ORDER BY 子句只在两种严格的条件下使用索引.

ORDER BY中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.

ORDER BY中所有的列必须定义为非空.

 12.SQL书写的影响（共享SQL语句可以提高操作效率）

    同一功能同一性能不同写法SQL的影响

    如一个SQL在A程序员写的为

    Select * from zl_yhjbqk

    B程序员写的为

    Select * from dlyx.zl_yhjbqk（带表所有者的前缀）

    C程序员写的为

    Select * from DLYX.ZLYHJBQK（大写表名）

    D程序员写的为

    Select *  from DLYX.ZLYHJBQK（中间多了空格）

     以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不 仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。

    推荐方案：不同区域出现的相同的Sql语句，要保证查询字符完全相同，以利用SGA共享池，防止相同的Sql语句被多次分析。

 

 

SQLServer数据库性能优化技术 无忧教程网整理

SQL Server数据库性能优化技术

要：影响SQL Server数据库性能的一些因素及SQL Server进行性能优化的原理，

　关键词：SQL Server数据库　性能优化　查询

　设计1个应用系统似乎并不难，但是要想使系统达到最优化的性能并不是一件容易的

事。在开发工具、数据库设计、应用程序的结构、查询设计、接口选择等方面有多种选择，这

取决于特定的应用需求以及开发队伍的技能。本文以SQL Server为例，从后台数据库的角度讨

　　1　数据库设计

　要在良好的SQL Server方案中实现最优的性能，最关键的是要有1个很好的数据库设

计方案。在实际工作中，许多SQL Server方案往往是由于数据库设计得不好导致性能很差。所

　　1.1　逻辑库规范化问题

　一般来说，逻辑数据库设计会满足规范化的前3级标准：

　1.第1规范：没有重复的组或多值的列。

　2.第2规范：每个非关键字段必须依赖于主关键字，不能依赖于1个组合式主关键字

　3.第3规范：1个非关键字段不能依赖于另1个非关键字段。

　遵守这些规则的设计会产生较少的列和更多的表，因而也就减少了数据冗余，也减少

了用于存储数据的页。但表关系也许需要通过复杂的合并来处理，这样会降低系统的性能。某

种程度上的非规范化可以改善系统的性能，非规范化过程可以根据性能方面不同的考虑用多种

不同的方法进行，但以下方法经实践验证往往能提高性能。

　1.如果规范化设计产生了许多4路或更多路合并关系，就可以考虑在数据库实体(表)

　2.常用的计算字段(如总计、最大值等)可以考虑存储到数据库实体中。

　比如某一个项目的计划管理系统中有计划表，其字段为：项目编号、年初计划、二次

计划、调整计划、补列计划…，而计划总数(年初计划+二次计划+调整计划+补列计划)是用户

经常需要在查询和报表中用到的，在表的记录量很大时，有必要把计划总数作为1个独立的字

段加入到表中。这里可以采用触发器以在客户端保持数据的一致性。

　3.重新定义实体以减少外部属性数据或行数据的开支。相应的非规范化类型是：

　(1)把1个实体(表)分割成2个表(把所有的属性分成2组)。这样就把频繁被访问的数据

同较少被访问的数据分开了。这种方法要求在每个表中复制首要关键字。这样产生的设计有利

　(2)把1个实体(表)分割成2个表(把所有的行分成2组)。这种方法适用于那些将包含

大量数据的实体(表)。在应用中常要保留历史记录，但是历史记录很少用到。因此可以把频繁

被访问的数据同较少被访问的历史数据分开。而且如果数据行是作为子集被逻辑工作组(部门

、销售分区、地理区域等)访问的，那么这种方法也是很有好处的。

　　1.2　生成物理数据库

　要想正确选择基本物理实现策略，必须懂得数据库访问格式和硬件资源的操作特点，

主要是内存和磁盘子系统I/O。这是一个范围广泛的话题，但以下的准则可能会有所帮助。

　1.与每个表列相关的数据类型应该反映数据所需的最小存储空间，特别是对于被索引

的列更是如此。比如能使用smallint类型就不要用integer类型，这样索引字段可以被更快地

读取，而且可以在1个数据页上放置更多的数据行，因而也就减少了I/O操作。

　2.把1个表放在某个物理设备上，再通过SQL Server段把它的不分簇索引放在1个不

同的物理设备上，这样能提高性能。尤其是系统采用了多个智能型磁盘控制器和数据分离技术

　3.用SQL Server段把一个频繁使用的大表分割开，并放在2个单独的智能型磁盘控制

器的数据库设备上，这样也可以提高性能。因为有多个磁头在查找，所以数据分离也能提高性

　4.用SQL Server段把文本或图像列的数据存放在1个单独的物理设备上可以提高性能

　　2　与SQL Server相关的硬件系统

　与SQL Server有关的硬件设计包括系统处理器、内存、磁盘子系统和网络，这4个部

分基本上构成了硬件平台，Windows NT和SQL Server运行于其上。

　　2.1　系统处理器(CPU)

　根据自己的具体需要确定CPU结构的过程就是估计在硬件平台上占用CPU的工作量的

过程。从以往的经验看，CPU配置最少应是1个80586/100处理器。如果只有2～3个用户，这就

足够了，但如果打算支持更多的用户和关键应用，推荐采用Pentium Pro或PⅡ级CPU。

　　

　　2.2　内存(RAM)

　为SQL Server方案确定合适的内存设置对于实现良好的性能是至关重要的。SQL

Server用内存做过程缓存、数据和索引项缓存、静态服务器开支和设置开支。SQL Server最多

能利用2GB虚拟内存，这也是最大的设置值。还有一点必须考虑的是Windows NT和它的所有相

　Windows NT为每个WIN32应用程序提供了4GB的虚拟地址空间。这个虚拟地址空间由

Windows NT虚拟内存管理器(VMM)映射到物理内存上，在某些硬件平台上可以达到4GB。SQL

Server应用程序只知道虚拟地址，所以不能直接访问物理内存，这个访问是由VMM控制的。

Windows NT允许产生超出可用的物理内存的虚拟地址空间，这样当给SQL Server分配的虚拟内

存多于可用的物理内存时，会降低SQL Server的性能。

　这些地址空间是专门为SQL Server系统设置的，所以如果在同一硬件平台上还有其它

软件(如文件和打印共享，应用程序服务等)在运行，那么应该考虑到它们也占用一部分内存

。一般来说硬件平台至少要配置32MB的内存，其中，Windows NT至少要占用16MB。1个简单的

法则是，给每一个并发的用户增加100KB的内存。例如，如果有100个并发的用户，则至少需

要32MB+100用户*100KB＝42MB内存，实际的使用数量还需要根据运行的实际情况调整。可以说

　　2.3　磁盘子系统

　设计1个好的磁盘I/O系统是实现良好的SQL Server方案的一个很重要的方面。这里讨

论的磁盘子系统至少有1个磁盘控制设备和1个或多个硬盘单元，还有对磁盘设置和文件系统

的考虑。智能型SCSI-2磁盘控制器或磁盘组控制器是不错的选择，其特点如下：

　(1)控制器高速缓存。

　(2)总线主板上有处理器，可以减少对系统CPU的中断。

　(3)异步读写支持。

　(4)32位RAID支持。

　(5)快速SCSI?2驱动。

　(6)超前读高速缓存(至少1个磁道)。

　　3　检索策略

　在精心选择了硬件平台，又实现了1个良好的数据库方案，并且具备了用户需求和应

用方面的知识后，现在应该设计查询和索引了。有2个方面对于在SQL Server上取得良好的查

询和索引性能是十分重要的，第1是根据SQL Server优化器方面的知识生成查询和索引；第2

　　3.1　SQL Server优化器

　Microsoft SQL Server数据库内核用1个基于费用的查询优化器自动优化向SQL提交

的数据查询操作。数据操作查询是指支持SQL关键字WHERE或HAVING的查询，如SELECT、DELETE

和UPDATE。基于费用的查询优化器根据统计信息产生子句的费用估算。

　了解优化器数据处理过程的简单方法是检测SHOWPLAN命令的输出结果。如果用基于字

符的工具(例如isql)，可以通过键入SHOW SHOWPLAN ON来得到SHOWPLAN命令的输出。如果使用

图形化查询，比如SQL Enterprise Manager中的查询工具或isql/w，可以设定配置选项来提供

　SQL Server的优化通过3个阶段完成：查询分析、索引选择、合并选择。

　1.查询分析 　在查询分析阶段，SQL Server优化器查看每一个由正规查询树代表的子句，并判断它是否能被优化。SQL Server一般会尽量优化那些限制扫描的子句。例如，搜索和/或合并子句。但是不是所有合法的SQL语法都可以分成可优化的子句，如含有SQL不等关系 符<>的子句。因为<>是1个排斥性的操作符，而不是1个包括性的操作符，所在扫描整个表之前无法确定子句的选择范围会有多大。 当1个关系型查询中含有不可优化的子句时，执行计划用表扫描来访问查询的这个部分，对于查询树中可优化的SQL Server子句，则由优化器执行索引选择。

　2.索引选择

　对于每个可优化的子句，优化器都查看数据库系统表，以确定是否有相关的索引能用

于访问数据。只有当索引中的列的1个前缀与查询子句中的列完全匹配时，这个索引才被认为

是有用的。因为索引是根据列的顺序构造的，所以要求匹配是精确的匹配。对于分簇索引，原

来的数据也是根据索引列顺序排序的。想用索引的次要列访问数据，就像想在电话本中查找所

有姓为某个姓氏的条目一样，排序基本上没有什么用，因为你还是得查看每一行以确定它是否

符合条件。如果1个子句有可用的索引，那么优化器就会为它确定选择性。

　所以在设计过程中，要根据查询设计准则仔细检查所有的查询，以查询的优化特点为

　(1)比较窄的索引具有比较高的效率。对于比较窄的索引来说，每页上能存放较多的

索引行，而且索引的级别也较少。所以，缓存中能放置更多的索引页，这样也减少了I/O操作

　(2)SQL Server优化器能分析大量的索引和合并可能性。所以与较少的宽索引相比，

较多的窄索引能向优化器提供更多的选择。但是不要保留不必要的索引，因为它们将增加存储

和维护的开支。对于复合索引、组合索引或多列索引，SQL Server优化器只保留最重要的列的

　(3)表上的索引过多会影响UPDATE、INSERT和DELETE的性能，因为所有的索引都必须

做相应的调整。另外，所有的分页操作都被记录在日志中，这也会增加I/O操作。　　

　　

　(4)对1个经常被更新的列建立索引，会严重影响性能。

　(5)由于存储开支和I/O操作方面的原因，较小的自组索引比较大的索引性能更好一些

　(6)尽量分析出每一个重要查询的使用频度，这样可以找出使用最多的索引，然后可

　(7)查询中的WHERE子句中的任何列都很可能是个索引列，因为优化器重点处理这个子

　(8)对小于1个范围的小型表进行索引是不划算的，因为对于小表来说表扫描往往更快

　(9)与ORDER BY或GROUP BY一起使用的列一般适于做分族索引。如果

ORDER BY命令中用到的列上有分簇索引，那么就不会再生成1个工作表了，因为行已经排序

　(10)分簇索引不应该构造在经常变化的列上，因为这会引起整行的移动。在实现大型

交易处理系统时，尤其要注意这一点，因为这些系统中数据往往是频繁变化的。

　3.合并选择

　当索引选择结束，并且所有的子句都有了一个基于它们的访问计划的处理费用时，优

化器开始执行合并选择。合并选择被用来找出一个用于合并子句访问计划的有效顺序。为了做

到这一点，优化器比较子句的不同排序，然后选出从物理磁盘I/O的角度看处理费用最低的合

并计划。因为子句组合的数量会随着查询的复杂度极快地增长，SQL Server查询优化器使用树

剪枝技术来尽量减少这些比较所带来的开支。当这个合并选择阶段结束时，SQL Server查询优

化器已经生成了1个基于费用的查询执行计划，这个计划充分利用了可用的索引，并以最小的

　　3.2　高效的查询选择

　从以上查询优化的3个阶段不难看出，设计出物理I/O和逻辑I/O最少的方案并掌握好

处理器时间和I/O时间的平衡，是高效查询设计的主要目标。也就是说，希望设计出这样的查

询：充分利用索引、磁盘读写最少、最高效地利用了内存和CPU资源。

　以下建议是从SQL Server优化器的优化策略中总结出来的，对于设计高效的查询是很

　1.如果有独特的索引，那么带有＝操作符的WHERE子句性能最好，其次是封闭的

　2.从数据库访问的角度看，含有不连续连接词(OR和IN)的WHERE子句一般来说性能不

会太好。所以，优化器可能会采用R策略，这种策略会生成1个工作表，其中含有每个可能匹

配的执行的标识符，优化器把这些行标志符(页号和行号)看做是指向1个表中匹配的行的动

态索引。优化器只需扫描工作表，取出每一个行标志符，再从数据表中取得相应的行，所以

R策略的代价是生成工作表。

　3.包含NOT、<>、或!　=的WHERE子句对于优化器的索引选择来说没有什么用处。因为这样的子句是排斥性的，而不是包括性的，所以在扫描整个原来数据表之前无法确定子句的选择性。

　4.限制数据转换和串操作，优化器一般不会根据WHERE子句中的表达式和数据转换式

生成索引选择。例如：

　paycheck * 12>36000 or substring(lastname,1,1)=L

　如果该表建立了针对paycheck和lastname的索引，就不能利用索引进行优化，可以改

写上面的条件表达式为：

　paycheck<36000/12 or lastname like L％

　5.WHERE子句中的本地变量被认为是不被优化器知道和考虑的，例外的情况是定义为

　6.如果没有包含合并子句的索引，那么优化器构造1个工作表以存放合并中最小的表

中的行。然后再在这个表上构造1个分簇索引以完成一个高效的合并。这种作法的代价是工作

表的生成和随后的分族索引的生成，这个过程叫REFORMATTING。　　所以应该注意RAM中或磁

盘上的数据库tempdb的大小(除了SELECT INTO语句)。另外，如果这些类型的操作是很常见的

　　4　性能优化的其他考虑

　上面列出了影响SQL Server的一些主要因素，实际上远不止这些。操作系统的影响也

很大，在Windows NT下，文件系统的选择、网络协议、开启的服务、SQL Server的优先级等选

项也不同程度上影响了SQL Server的性能。

　影响性能的因素是如此的多，而应用又各不相同，找出1个通用的优化方案是不现实

的，在系统开发和维护的过程中必须针对运行的情况，不断加以调整。事实上，绝大部分的优

化和调整工作是在与客户端独立的服务器上进行的，因此也是现实可行的。