最近操作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数据库)
分享到:
相关推荐
1 使用子查询 查询时尽量将查询条件放到里层的查询以尽早过滤数据,即子查询,在select 后使用select。 2 使用Union all 合并集合时如果尽可能用union all 3 使用临时表 如果一个表的数据量很大,但实际用时只用一小...
第2章 风驰电掣——有效缩短SQL优化过程 24 2.1 SQL调优时间都去哪儿了 25 2.1.1 不善于批处理频频忙交互 25 2.1.2 无法抓住主要矛盾瞎折腾 25 2.1.3 未能明确需求目标白费劲 26 2.1.4 没有分析操作难度乱调优...
SQL优化经验总结34条
SQL优化总结,很实用。学习SQL应知道的动态SQL语句基本语法
数据库面试题索引sql优化.pdf+数据库SQL优化总结之百万级数据库优化.pdf 附赠Oracle高性能sql优化
SQL优化.doc SQL优化.doc SQL优化.doc
sql优化经验总结,还不错
sql优化规则详解(一些小总结) 从sql语句的写法来描述如何提高效率
sql优化经验总结打包发送 sql优化经验总结打包发送 sql优化经验总结打包发送 sql优化经验总结打包发送 sql优化经验总结打包发送 sql优化经验总结打包发送
SQL语句优化总结,很实用,讲解详细,适用于新手,用于平时工作中
sql语句优化PPT,总结基本常见的,自己总结的
2023最新mysql的sql语句优化方法技巧面试题总结.docx2023最新mysql的sql语句优化方法技巧面试题总结.docx2023最新mysql的sql语句优化方法技巧面试题总结.docx2023最新mysql的sql语句优化方法技巧面试题总结.docx2023...
个人总结sql优化的一些方式。。 oracle sqlserver等数据库优化方案。
sql查询优化总结
sql优化总结,对sql 优化在实战中对于sql优化的浅显理解,大神勿喷。
34条SQL语句的优化方案,让你轻松写出性能高效的SQL语句。
SQL 优化
sql server 优化总结,希望对大家有帮助
sql优化的各种方法,sql优化的各种方法,sql优化的各种方法,sql优化的各种方法,sql优化的各种方法,sql优化的各种方法
数据库SQL优化大总结之 百万级数据库优化方案