sql优化

1. 杜绝SELECT * 

表连接时，绝不允许写select * ，否则按照编码错误Bug处理.
    单表查询，一般情况下可以使用select *,但以下几种情况禁用：
    a、表中包含lob字段（BLOB、CLOB、LONG、LONG RAW等）。
    b、表中包含长度较大的字段，如varchar2(1000)以上的字段，
         但该SQL实际上并不需要取出该字段的值。
    c、字段数量很多，但实际要用的字段很少，比如表有50个字段，
       而你实际只用5个，并且该SQL目前没有被重用。
    d、DBA要求优化调整的。

2.减少访问数据库的次数 
    比如JAVA中用

    for(int id : ids) {

        roleDao.deleteRoleById(id);

   }

然后发多条delete

解决办法：
   用<isNotEmpty prepend="and" property="tradeNos">
<iterate  property="tradeNos" open="t.trade_no in (" close=")" conjunction=",">
      #tradeNos[]#
</iterate>
</isNotEmpty>
3.杜绝隐式类型转换 
    1、DAO传入的类型不一致。
    2、sqlmap的变量没有指定类型。
    3、时间类型的没有加to_date函数。

4.绑定变量和替代变量 

   为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享.因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径.
    替代变量是无法重复使用共享池中已经解析的语句和执行路径，每个不同变量的sql都需要重新解析，浪费很多oracle资源。 绑定变量用 #变量名# 表示   替代变量用 $变量名$ 表示

在order by子句中，通常使用替代变量而不是绑定变量

 

5.SQL中如何处理％和_ 

  原因： Oracle中%和_是两个特殊的字符,不做特殊处理，无法查询记录中含有％和_的结果。
解决方法：
   1、DAO传入的查询参数需要解析，如：
      if(recordName != null) {
            if(recordName.indexOf("%") >= 0) {
                recordName = recordName.replaceAll("%", "\\\\%");   
            }else if (recordName.indexOf("_") >= 0) {
                recordName = recordName.replaceAll("_", "\\\\_");
            }
      }
   2、在sqlmap需要加上escape关键字，如：
    RECORDNAME LIKE '%'||#recordName:VARCHAR#||'%’ESCAPE '\'

 

6.避免在索引列上使用计算和函数

   如果索引列是函数的一部分．优化器将不使用索引而使用全表扫描．

Sql代码 

 

1. 错误的写法（a.id是number类型，而b.operator_number是char类型）：   
2.     select count(*) from adm_user a, adm_action_log b where       a.id =b.operator_number and a.username = '小钗';  
3. 正确的写法：  
4.     select count(*) from adm_user a, adm_action_log b where a.id = to_number(b.operator_number) and a.username = '小钗';   

 7.分页的使用

 

Java代码 

 

1. 1、分页通常是先执行COUNT语句然后执行分页语句，当COUNT返回值为0的时候，应当避免执行后面的分页语句。  
2.     Long count = roleDao.queryCountForRole(params);  
3.     if(count > 0){  
4.         List<Role> list = roleDao.queryRoleForList(params);  
5.     }  
6. 2、有时，只须执行分页语句而无须执行COUNT语句，就不要执行COUNT语句，例如，用户下载excel格式的账户明细。  
7. 3、有时，在分页前除了要统计COUNT还需要统计SUM，这些WHERE子句一致的统计应该在一条SQL中查出，而不是分多次统计。  
8. 4、包含排序逻辑的分页查询写法，必须是三层select嵌套。  

 8.其他

    不允许出现where 1 = 1 这样没必须的条件。

    先精确查找再模糊查找。

    用UNION-ALL 替换UNION ( 如果有可能的话)。
不要使用count(1)代替count（*）。
count(column_name)计算该列不为NULL的记录条数。
count(distinct column_name)计算该列不为NULL的不重复值数量。
count()函数不会返回NULL，但sum()函数可能返回NULL，可以使用nvl(sum(qty),0)来避免返回NULL 。
理解NULL的含义，是“不确定”，而不是“空“。
查询时，使用is null或者is not null。
更新时，使用等于号，如：update tablename set column_name = null。
不使用ANSI连接，如inner join、left join、right join、full outer join，而使用(+)来表示外连接。
使用表的别名(Alias)可以减少解析的时间并减少由Column歧义引起的语法错误。
使用exists、not exists 代替in和not in(包含select子句）用UNION-ALL 替换UNION ( 如果有可能的话)。
不要使用count(1)代替count（*）。
count(column_name)计算该列不为NULL的记录条数。
count(distinct column_name)计算该列不为NULL的不重复值数量。
count()函数不会返回NULL，但sum()函数可能返回NULL，可以使用nvl(sum(qty),0)来避免返回NULL 。
理解NULL的含义，是“不确定”，而不是“空“。
查询时，使用is null或者is not null。
更新时，使用等于号，如：update tablename set column_name = null。
不使用ANSI连接，如inner join、left join、right join、full outer join，而使用(+)来表示外连接。
使用表的别名(Alias)可以减少解析的时间并减少由Column歧义引起的语法错误。
使用exists、not exists 代替in和not in(包含select子句）