SQL语句优化技术分析

抄自: http://www.phpchina.com/1628/viewspace_9576.html
操作符优化

IN 操作符

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。

但是用IN的SQL性能总是比较低的：
    数据库试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

    推荐方案：在业务密集的SQL当中尽量不采用IN操作符。

NOT IN操作符

    此操作是强列推荐不使用的，因为它不能应用表的索引。

    推荐方案：用NOT EXISTS 或（外连接+判断为空）方案代替

<> 操作符（不等于）

    不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。

推荐方案：用其它相同功能的操作运算代替，如

    a<>0 改为 a>0 or a<0

    a<>’’ 改为 a>’’

IS NULL 或IS NOT NULL操作（判断字段是否为空）

    判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。

    推荐方案：

用其它相同功能的操作运算代替，如

    a is not null 改为 a>0 或a>’’等。

    不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。

    建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）



> 及 < 操作符（大于或小于操作符）

    大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A， 30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为 A>2时数据库会先找出为2的记录索引再进行比较，而A>=3时数据库则直接找到=3的记录索引。



LIKE操作符

LIKE 操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。



UNION操作符

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys

union

select * from ls_jg_dfys

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

select * from gc_dfys

union all

select * from ls_jg_dfys



SQL书写的影响

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

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

    Select * from zl_yhjbqk

B程序员写的为

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

C程序员写的为

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

D程序员写的为

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




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占用率明显比第一条低。



SQL语句索引的利用

对操作符的优化（见上节）

对条件字段的一些优化

采用函数处理的字段不能利用索引，如：

substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’

trunc(sk_rq)=trunc(sysdate)， 优化处理：

sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)

进行了显式或隐式的运算的字段不能进行索引，如：

ss_df+20>50，优化处理：ss_df>30

‘X’||hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’

sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5

hbs_bh=5401002554，优化处理：hbs_bh=’ 5401002554’，注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。

条件内包括了多个本表的字段运算时不能进行索引，如：

ys_df>cx_df，无法进行优化

qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’ and kh_bh=’250000’

用   WHERE   和   HAVING   筛选行  
  SELECT   语句中的   WHERE   和   HAVING   子句控制用源表中的那些行来构造结果集。WHERE   和   HAVING   是筛选。这两个子句指定指定一系列搜索条件，只有那些满足搜索条件的行才用来构造结果集。我们称满足搜索条件的行符合参与行集的限定条件。例如，下列   SELECT   语句中的   WHERE   子句将限定只选择地区为华盛顿州   (WA)   的行：  
   
  SELECT   CustomerID,   CompanyName  
  FROM   Northwind.dbo.Customers  
  WHERE   Region   =   'WA'  
   
  HAVING   子句通常与   GROUP   BY   子句结合使用，尽管指定该子句时也可以不带   GROUP   BY。HAVING   子句指定在应用   WHERE   子句的筛选后要进一步应用的筛选。例如，下列   WHERE   子句仅限定以高于   $100   的单价销售产品的订单，而   HAVING   子句进一步将结果限制为只包括   100   件以上的订单：  
   
  SELECT   OrdD1.OrderID   AS   OrderID,  
                SUM(OrdD1.Quantity)   AS   "Units   Sold",  
                SUM(OrdD1.UnitPrice   *   OrdD1.Quantity)   AS   Revenue  
  FROM   [Order   Details]   AS   OrdD1  
  WHERE   OrdD1.OrderID   in   (SELECT   DISTINCT   OrdD2.OrderID  
                                                  FROM   [Order   Details]   AS   OrdD2  
                                                  WHERE   OrdD2.UnitPrice   >   $100)  
  GROUP   BY   OrdD1.OrderID  
  HAVING   SUM(OrdD1.Quantity)   >   100  
   
  WHERE   和   HAVING   子句中的搜索条件或限定条件可包括：    
   
  比较运算符（如   =、<   >、<   和   >）。例如，下列查询从   Products   表中检索产品分类为   2   的行：    
  SELECT   ProductID,   ProductName  
  FROM   Northwind.dbo.Products  
  WHERE   CategoryID   =   2  
  ORDER   BY   ProductID  
   
  范围（BETWEEN   和   NOT   BETWEEN）。例如，下列查询从   Products   表中检索产品分类从   2   到   4   的行：    
  SELECT   CategoryID,   ProductID,   ProductName  
  FROM   Northwind.dbo.Products  
  WHERE   CategoryID   BETWEEN   2   and   4  
  ORDER   BY   CategoryID,   ProductID  
   
  列表（IN、NOT   IN）。例如，下列查询从   Products   表中检索产品分类   ID   与列表中某一   ID   匹配的行：    
  SELECT   CategoryID,   ProductID,   ProductName  
  FROM   Northwind.dbo.Products  
  WHERE   CategoryID   IN   (1,4,5,7)  
  ORDER   BY   CategoryID,   ProductID  
   
  模式匹配（LIKE   和   NOT   LIKE）。例如，下列查询从   Products   表中检索产品名称以   Ch   开头的行：    
  SELECT   CategoryID,   ProductID,   ProductName  
  FROM   Northwind.dbo.Products  
  WHERE   ProductName   LIKE   'Ch%'  
  ORDER   BY   CategoryID,   ProductID  
   
   
   
  说明     可用于   text   列的   WHERE   条件只有返回其它数据类型的函数（如   PATINDEX()）或运算符（如   IS   NULL、IS   NOT   NULL、LIKE   和   NOT   LIKE）。  
   
  空值（IS   NULL   和   IS   NOT   NULL）。例如，下列查询从   Customers   表中检索客户地区不为   NULL   的行：    
  SELECT   CompanyName,   City,   Region,   Country  
  FROM   Northwind.dbo.Customers  
  WHERE   Region   IS   NOT   NULL  
  ORDER   BY   CompanyName  
   
   
   
  说明     比较空值时请谨慎从事。例如，指定   =   NULL   与指定   IS   NULL   是不同的。有关更多信息，请参见空值。  
   
  所有记录（=ALL、>ALL、<=   ALL、ANY）。例如，下列查询从   Order   Details   表中检索装运产品数量大于分类   1   中任意产品的装运数量的订单和产品   ID：    
  USE   Northwind  
  GO  
  SELECT   OrdD1.OrderID,   OrdD1.ProductID  
  FROM   "Order   Details"   OrdD1  
  WHERE   OrdD1.Quantity   >   ALL  
              (SELECT   OrdD2.Quantity  
                FROM   "Order   Details"   OrdD2   JOIN   Products   Prd  
                            ON   OrdD2.ProductID   =   Prd.ProductID  
                WHERE   Prd.CategoryID   =   1)  
  GO  
   
  上述条件的组合（AND、OR、NOT）。例如，下列查询检索库存水平低于再订购点，或来自供应商   15   并属于分类   4   的所有产品：    
  SELECT   ProductID,   ProductName  
  FROM   Northwind.dbo.Products  
  WHERE   UnitsInStock   <   ReorderLevel  
        OR   (SupplierID   =   15   AND   CategoryID   =   4)  
   
   
   
  说明     当在   WHERE   子句中搜索   Unicode   字符串时，请在搜索字符串之前加字符   N，例如：  
   
  SELECT   CompanyName,   ContactName,   Phone,   Fax  
  FROM   Northwind.dbo.Customers  
  WHERE   CompanyName   =   N'Berglunds   snabbk&ouml;p'