SQL优化

sql优化

1.将in改为exists

通常在in和exists中通常情况下使用EXISTS，因为in不走索引

select * from A
where id in(select id from B)

上面的sql相当于下面的过程

List resultSet=[];
Array A=(select * from A);
Array B=(select id from B);

for(int i=0;i<A.length;i++) {
   for(int j=0;j<B.length;j++) {
      if(A[i].id==B[j].id) {
         resultSet.add(A[i]);
         break;
      }
   }
}
return resultSet;

而将上面的sql改为如下sql，则不会进行全部数据的遍历

select a.* from A a
where exists(select 1 from B b where a.id=b.id)

这个sql就相当于下面这个C

List resultSet=[];
Array A=(select * from A)

for(int i=0;i<A.length;i++) {
   if(exists(A[i].id) {    //执行select 1 from B b where b.id=a.id是否有记录返回
       resultSet.add(A[i]);
   }
}
return resultSet;

从上面的语句中我们能看出来，如果主查询的数据集大，则使用in;如果子查询的数据集大，则使用exists;

2.使用sql预编译

String sql = "select * from xxx where rank like concat('%',?,'%')";

类似这种

3.避免在索引上使用计算

 select * from user where salary*22>11000
 #(salary是索引列),修改为以下
 select * from user where salary>11000/22(salary是索引列)

4.调整Where字句中的连接顺序

DBMS一般采用自下而上的顺序解析where字句，将可以过滤掉最大数量记录的筛选条件放到最下面。

5.使用表的别名

当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

6.用union all替换union

union在进行表求并集后会去掉重复的元素，所以会对所产生的结果集进行排序运算，删除重复的记录再返回结果。union all则只是简单地将两个结果集合并后就返回结果。因此，如果返回的两个结果集中有重复的数据，那么返回的结果就会包含重复的数据。从上面的对比可以看出，在执行查询操作时，union all要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据，那么最好使用union all。

7.查询select语句优化

1.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段

2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描.

8.考虑使用“临时表”暂存中间结果

简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。但是也得避免频繁创建和删除临时表，以减少系统表资源的消耗。

9.创建索引

1.要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

2.(1)在经常需要进行检索的字段上创建索引，比如要按照表字段username进行检索，那么就应该在姓名字段上创建索引，如果经常要按照员工部门和员工岗位级别进行检索，那么就应该在员工部门和员工岗位级别这两个字段上创建索引。

(2)创建索引给检索带来的性能提升往往是巨大的，因此在发现检索速度过慢的时候应该首先想到的就是创建索引。

(3)一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。

10.更新Update语句优化

如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志

11.删除Delete语句优化语句

分批操作数据

delete product where id<1000
delete product where id>=1000 and id<2000
delete product where id>=2000 and id<3000
.....

from:https://blog.csdn.net/u010520146/article/details/81161762