sql语句优化

操作符优化

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’

Java代码

1.   
2.   
3. 用   WHERE   和   HAVING   筛选行     
4.   SELECT   语句中的   WHERE   和   HAVING   子句控制用源表中的那些行来构造结果集。WHERE   和   HAVING   是筛选。这两个子句指定指定一系列搜索条件，只有那些满足搜索条件的行才用来构造结果集。我们称满足搜索条件的行符合参与行集的限定条件。例如，下列   SELECT   语句中的   WHERE   子句将限定只选择地区为华盛顿州   (WA)   的行：     
5.       
6.   SELECT   CustomerID,   CompanyName     
7.   FROM   Northwind.dbo.Customers     
8.   WHERE   Region   =   'WA'     
9.       
10.   HAVING   子句通常与   GROUP   BY   子句结合使用，尽管指定该子句时也可以不带   GROUP   BY。HAVING   子句指定在应用   WHERE   子句的筛选后要进一步应用的筛选。例如，下列   WHERE   子句仅限定以高于   $100   的单价销售产品的订单，而   HAVING   子句进一步将结果限制为只包括   100   件以上的订单：     
11.       
12.   SELECT   OrdD1.OrderID   AS   OrderID,     
13.                 SUM(OrdD1.Quantity)   AS   "Units   Sold",     
14.                 SUM(OrdD1.UnitPrice   *   OrdD1.Quantity)   AS   Revenue     
15.   FROM   [Order   Details]   AS   OrdD1     
16.   WHERE   OrdD1.OrderID   in   (SELECT   DISTINCT   OrdD2.OrderID     
17.                                                   FROM   [Order   Details]   AS   OrdD2     
18.                                                   WHERE   OrdD2.UnitPrice   >   $100)     
19.   GROUP   BY   OrdD1.OrderID     
20.   HAVING   SUM(OrdD1.Quantity)   >   100     
21.       
22.   WHERE   和   HAVING   子句中的搜索条件或限定条件可包括：       
23.       
24.   比较运算符（如   =、<   >、<   和   >）。例如，下列查询从   Products   表中检索产品分类为   2   的行：       
25.   SELECT   ProductID,   ProductName     
26.   FROM   Northwind.dbo.Products     
27.   WHERE   CategoryID   =   2     
28.   ORDER   BY   ProductID     
29.       
30.   范围（BETWEEN   和   NOT   BETWEEN）。例如，下列查询从   Products   表中检索产品分类从   2   到   4   的行：       
31.   SELECT   CategoryID,   ProductID,   ProductName     
32.   FROM   Northwind.dbo.Products     
33.   WHERE   CategoryID   BETWEEN   2   and   4     
34.   ORDER   BY   CategoryID,   ProductID     
35.       
36.   列表（IN、NOT   IN）。例如，下列查询从   Products   表中检索产品分类   ID   与列表中某一   ID   匹配的行：       
37.   SELECT   CategoryID,   ProductID,   ProductName     
38.   FROM   Northwind.dbo.Products     
39.   WHERE   CategoryID   IN   (1,4,5,7)     
40.   ORDER   BY   CategoryID,   ProductID     
41.       
42.   模式匹配（LIKE   和   NOT   LIKE）。例如，下列查询从   Products   表中检索产品名称以   Ch   开头的行：       
43.   SELECT   CategoryID,   ProductID,   ProductName     
44.   FROM   Northwind.dbo.Products     
45.   WHERE   ProductName   LIKE   'Ch%'     
46.   ORDER   BY   CategoryID,   ProductID     
47.       
48.       
49.       
50.   说明     可用于   text   列的   WHERE   条件只有返回其它数据类型的函数（如   PATINDEX()）或运算符（如   IS   NULL、IS   NOT   NULL、LIKE   和   NOT   LIKE）。     
51.       
52.   空值（IS   NULL   和   IS   NOT   NULL）。例如，下列查询从   Customers   表中检索客户地区不为   NULL   的行：       
53.   SELECT   CompanyName,   City,   Region,   Country     
54.   FROM   Northwind.dbo.Customers     
55.   WHERE   Region   IS   NOT   NULL     
56.   ORDER   BY   CompanyName     
57.       
58.       
59.       
60.   说明     比较空值时请谨慎从事。例如，指定   =   NULL   与指定   IS   NULL   是不同的。有关更多信息，请参见空值。     
61.       
62.   所有记录（=ALL、>ALL、<=   ALL、ANY）。例如，下列查询从   Order   Details   表中检索装运产品数量大于分类   1   中任意产品的装运数量的订单和产品   ID：       
63.   USE   Northwind     
64.   GO     
65.   SELECT   OrdD1.OrderID,   OrdD1.ProductID     
66.   FROM   "Order   Details"   OrdD1     
67.   WHERE   OrdD1.Quantity   >   ALL     
68.               (SELECT   OrdD2.Quantity     
69.                 FROM   "Order   Details"   OrdD2   JOIN   Products   Prd     
70.                             ON   OrdD2.ProductID   =   Prd.ProductID     
71.                 WHERE   Prd.CategoryID   =   1)     
72.   GO     
73.       
74.   上述条件的组合（AND、OR、NOT）。例如，下列查询检索库存水平低于再订购点，或来自供应商   15   并属于分类   4   的所有产品：       
75.   SELECT   ProductID,   ProductName     
76.   FROM   Northwind.dbo.Products     
77.   WHERE   UnitsInStock   <   ReorderLevel     
78.         OR   (SupplierID   =   15   AND   CategoryID   =   4)     
79.       
80.       
81.       
82.   说明     当在   WHERE   子句中搜索   Unicode   字符串时，请在搜索字符串之前加字符   N，例如：     
83.       
84.   SELECT   CompanyName,   ContactName,   Phone,   Fax     
85.   FROM   Northwind.dbo.Customers     
86.   WHERE   CompanyName   =   N'Berglunds   snabbk&ouml;p'