[总结]sql优化

最近操作mysql(InnoDB),需要从大数据量(1000W+)条数据中,挑选出某一字段在一个集合内的数据,然后删除,写出的sql语句为:

 

delete from xxx where xx in (select xxx from ......);

 

删除速度极慢..无法忍受的地步..可能是in语句造成全表扫描了..

网上查了下,发现 一般来说 insert>update>delete

因为delete 写 rollback segement(回滚段信息) 比较多..造成速度慢..

最优化处理方式

1、把需要保留的数据根据条件导出到临时表

2、TRUNCATE

3、重建表，从导入临时表数据

 

-----------------------------------------------------------

以下为转帖

-----------------------------------------------------------

1.多where，少having 
where用来过滤行，having用来过滤组 

2.多union all，少union 
union删除了重复的行，因此花费了一些时间 

3.多Exists，少in 
Exists只检查存在性，性能比in强很多，有些朋友不会用Exists，就举个例子 
例，想要得到有电话号码的人的基本信息，table2有冗余信息 
select * from table1;--(id,name,age) 
select * from table2;--(id,phone) 
in： 
select * from table1 t1 where t1.id in (select t2.id from table2 t2 where t1.id=t2.id); 
Exists： 
select * from table1 t1 where Exists (select 1 from table2 t2 where t1.id=t2.id); 

4.使用绑定变量 
Oracle数据库软件会缓存已经执行的sql语句，复用该语句可以减少执行时间。 
复用是有条件的，sql语句必须相同 
问：怎样算不同？ 
答：随便什么不同都算不同，不管什么空格啊，大小写什么的，都是不同的 
想要复用语句，建议使用PreparedStatement 
将语句写成如下形式： 
insert into XXX(pk_id,column1) values(?,?); 
update XXX set column1=? where pk_id=?; 
delete from XXX where pk_id=?; 
select pk_id,column1 from XXX where pk_id=?; 

5.少用* 
很多朋友很喜欢用*，比如：select * from XXX; 
一般来说，并不需要所有的数据，只需要一些，有的仅仅需要1个2个， 
拿5W的数据量，10个属性来测试: 
(这里的时间指的是PL/SQL Developer显示所有数据的时间) 
使用select * from XXX;平均需要20秒， 
使用select column1,column2 from XXX;平均需要12秒 
(我的机子不是很好。。。) 
对于开发来说，这一条是个灾难，知道是一回事，做就是另一回事了 

6.分页sql 
一般的分页sql如下所示： 
sql1:select * from (select t.*,rownum rn from XXX t)where rn>0 and rn <10; 
sql2:select * from (select t.*,rownum rn from XXX t where rownum <10)where rn>0; 
乍看一下没什么区别，实际上区别很大...125万条数据测试， 
sql1平均需要1.25秒(咋这么准呢？ ) 
sql2平均需要... 0.07秒 
原因在于，子查询中，sql2排除了10以外的所有数据 
当然了，如果查询最后10条，那效率是一样的 

7.能用一句sql，千万别用2句sql 
不解释 
（补充内容） 
第6条指的是是oracle数据库。 
第7条本来不准备解释的，也有这么多质疑的，说明追寻真理的朋友还是相当多的。 
我认为数据库当掉确实很有可能，我没想到是我的问题。 

以上各条只针对sql如何优化，至于开发效率低，可读性等等，并不在考虑范围内 

（补充内容2）第6条，如果有分页需要排序，必须再包一层，结果为 
select * from (select t.*, rownum rn from (select * from XXX order by value desc) t where rownum <= 10 ) where rn > 0; 

（补充内容3）第3条，用in的朋友注意了，当参数超过1000个，数据库就挂了。（oracle 10g数据库）