mysql&使用临时表+存储过程删除大表中的大量数据
当一个表mysql的表成长到1000w以上，然后需要更加某些条件去删除其中的几百万数据的时候，如果直接delete将是一件极其痛苦的事情。
时间长是必然的，而且堵塞了正常应用的更新等操作，很有可能的导致数据库hang住和应用的崩溃
--常用的方法，如果表是1000w，符合删除条件的数据是400w的话，那时间一般会超过半个小时乃至更长
delete from xf_user_info where status=2 and date(gmt_create)
----------使用类似于oracle常用的方式来完成，如果有更好的方法，不吝赐教&
--写个临时表和存储过程，来完成
--创建临时表
create table tmp_xf_id_del& as
select id from xf_user_info where status=2 and
gmt_create& '';
//&--使用//带代替;，这个符号可以自己定义
set autocommit
=0;& --设置为非自动提交，脚本中定义为100条提交一次
------华丽的分割线---------
--创建存储过程
CREATE PROCEDURE sp_xf_del_test()
declare v_exit int default 0;
declare v_
declare i int default 0;
declare c_ids cursor for select id from tmp_xf_id_
declare& continue handler for not found set
fetch c_ids into v_
delete from xf_user_info where id& =
set i=i+1;
if mod(i,100)=0
until v_exit=1
-------------
--调用存储过程
sp_xf_del_test()//
--我的估算：
用100条COMMIT一次的方式比单条提交的方式效率高40%
已投稿到：
以上网友发言只代表其个人观点，不代表新浪网的观点或立场。当工作在很大的表上时，您可能偶尔需要运行很多查询获得一个大量数据的小的子集，不是对整个表运行这些查询，而是让MySQL每次找出所需的少数记录，将记录选择到一个临时表可能更快些，然后多这些表运行查询。
　　创建临时表很容易，给正常的CREATE TABLE语句加上TEMPORARY关键字:
　　CREATE TEMPORARY TABLE tmp_table (
　　name VARCHAR(10) NOT NULL,
　　value INTEGER NOT NULL
　　临时表将在您连接MySQL期间存在。当您断开时，MySQL将自动删除表并释放所用的空间。当然您能够在仍然连接的时候删除表并释放空间。
　　DROP TABLE tmp_table
　　假如在您创建名为tmp_table临时表时名为tmp_table的表在数据库中已存在，临时表将有必要屏蔽(隐藏)非临时表 tmp_table。
　　假如您声明临时表是个HEAP表，MySQL也允许您指定在内存中创建他:
　　CREATE TEMPORARY TABLE tmp_table (
　　name VARCHAR(10) NOT NULL,
　　value INTEGER NOT NULL
　　) TYPE = HEAP
　　因为HEAP表存储在内存中，您对他运行的查询可能比磁盘上的临时表快些。然而，HEAP表和一般的表有些不同，且有自身的限制。详见 MySQL参考手册。
　　正如前面的建议，您应该测试临时表看看他们是否真的比对大量数据库运行查询快。假如数据很好地索引，临时表可能一点不快。
delimiter ||create procedure sp_test1(IN pageno int, IN pagesize int,OUT pagecount int)BEGIN de de decla drop table if exists tmp_table21;CREATE TEMPORARY TABLE tmp_table21(&&& rowid bigint auto_increment primary key, userid bigint);insert into tmp_table21 (userid) select ID set idlower=(pageno-1)*pagesize+1; set idupper=pageno* select * from tmp_table21; /*计算总页数*/ select count(*) from tmp_table21 set pagecount= end||注意:
引言：某客户新上线一个项目，利用存储过程处理用户登录相关事务。在存储过程中，需要对用户数据进行处理，于是他们采用临时表(temporary table)来做这个动作，先创建一个临时表，然后插入数据，处理；由于是采用连接池方式，担心临时表被复用，于是在最后删除该临时表。该客户采用16G 的2950机器做mysql db server，利用loadrunner进行模拟登录测试，发现并发量达到2,30万之后，就再也上不去了，而且峰值不是很稳定的处于30多万的级别上。一开始以为是机器性能达到了极限，经过询问各种状况后，认为应该还可以得到改进和优化。经过现场分析后，发现在测试达到峰值时，会有大量的 "waiting for table"，以及大量的 create temporary table 和 drop table 的线程在等待。很明显，瓶颈在于频繁的创建和删除临时表，mysql需要频繁的处理打开和关闭表描述符，才会导致了上面的问题。还好他们采用了连接池，否则情况将会更糟糕。建议他们把最后的 drop table 改成 truncate table，把临时表清空了，也就不会担心下一次调用时临时表不为空了，省去了频繁的处理表文件描述符，并发用户数也稳定的保持在了40多万。
阅读(...) 评论()本帖子已过去太久远了，不再提供回复功能。2099人阅读
mysql（49）
& 今天遇到一个很棘手的问题，想临时存起来一部分数据，然后再读取。我记得学数据库理论课老师说可以创建临时表，不知道mysql有没有这样的功能呢？临时表在内存之中，读取速度应该比视图快一些。然后还需要将查询的结果存储到临时表中。下面是创建临时表以及插入数据的例子，以供大家参考。
A、临时表再断开于mysql的连接后系统会自动删除临时表中的数据，但是这只限于用下面语句建立的表：
1)定义字段
& CREATE TEMPORARY TABLE tmp_table (
&&&&& name VARCHAR(10) NOT NULL,&
&&&&&&time date &NOT NULL
更高级点就是：
create temporary& TABLE `temtable` (
& `jws` varchar(100) character set utf8 collate utf8_bin NOT NULL,
& `tzlb` varchar(100) character set utf8 collate utf8_bin NOT NULL,
& `uptime` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1″
连编码方式都规定了。。呵呵，以防乱码啊。
2)直接将查询结果导入临时表
& CREATE TEMPORARY TABLE tmp_table SELECT * FROM table_name
B、另外mysql也允许你在内存中直接创建临时表，因为是在内存中所有速度会很快，语法如下：
& CREATE TEMPORARY TABLE tmp_table (
&&&& name VARCHAR(10) NOT NULL,
&&&& value INTEGER NOT NULL
& ) TYPE = HEAP
那如何将查询的结果存入已有的表呢？
1、可以使用A中第二个方法
2、使用insert into temtable (select a,b,c,d from tablea)”;
/archives/152.html
&&相关文章推荐
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：190888次
积分：4003
积分：4003
排名：第8158名
原创：141篇
转载：114篇
评论：23条
(1)(1)(1)(2)(1)(10)(11)(2)(1)(3)(1)(4)(2)(2)(1)(3)(5)(2)(2)(3)(5)(14)(16)(54)(13)(63)(32)
(window.slotbydup = window.slotbydup || []).push({
id: '4740881',
container: s,
size: '200,200',
display: 'inlay-fix'2015年6月 其他数据库开发大版内专家分月排行榜第二2015年5月 其他数据库开发大版内专家分月排行榜第二2013年6月 其他数据库开发大版内专家分月排行榜第二2013年3月 其他数据库开发大版内专家分月排行榜第二2012年12月 其他数据库开发大版内专家分月排行榜第二2012年10月 其他数据库开发大版内专家分月排行榜第二2012年9月 其他数据库开发大版内专家分月排行榜第二2012年7月 其他数据库开发大版内专家分月排行榜第二2012年6月 其他数据库开发大版内专家分月排行榜第二2011年11月 其他数据库开发大版内专家分月排行榜第二2011年10月 其他数据库开发大版内专家分月排行榜第二2011年9月 其他数据库开发大版内专家分月排行榜第二2011年6月 其他数据库开发大版内专家分月排行榜第二2011年5月 其他数据库开发大版内专家分月排行榜第二2011年4月 其他数据库开发大版内专家分月排行榜第二2011年3月 其他数据库开发大版内专家分月排行榜第二2011年2月 其他数据库开发大版内专家分月排行榜第二2010年12月 其他数据库开发大版内专家分月排行榜第二2010年11月 其他数据库开发大版内专家分月排行榜第二2010年9月 其他数据库开发大版内专家分月排行榜第二2010年7月 其他数据库开发大版内专家分月排行榜第二2010年4月 其他数据库开发大版内专家分月排行榜第二2010年3月 其他数据库开发大版内专家分月排行榜第二2010年1月 其他数据库开发大版内专家分月排行榜第二2009年12月 其他数据库开发大版内专家分月排行榜第二2008年6月 其他数据库开发大版内专家分月排行榜第二2008年5月 其他数据库开发大版内专家分月排行榜第二2008年4月 其他数据库开发大版内专家分月排行榜第二2007年5月 其他数据库开发大版内专家分月排行榜第二2007年1月 其他数据库开发大版内专家分月排行榜第二2006年10月 其他数据库开发大版内专家分月排行榜第二2006年8月 其他数据库开发大版内专家分月排行榜第二2006年6月 其他数据库开发大版内专家分月排行榜第二2006年4月 其他数据库开发大版内专家分月排行榜第二2005年9月 其他数据库开发大版内专家分月排行榜第二
2013年12月 其他数据库开发大版内专家分月排行榜第三2013年5月 其他数据库开发大版内专家分月排行榜第三2013年4月 其他数据库开发大版内专家分月排行榜第三2013年2月 其他数据库开发大版内专家分月排行榜第三2013年1月 其他数据库开发大版内专家分月排行榜第三2012年5月 其他数据库开发大版内专家分月排行榜第三2012年4月 其他数据库开发大版内专家分月排行榜第三2012年3月 其他数据库开发大版内专家分月排行榜第三2012年2月 其他数据库开发大版内专家分月排行榜第三2011年8月 其他数据库开发大版内专家分月排行榜第三2011年7月 其他数据库开发大版内专家分月排行榜第三2010年10月 其他数据库开发大版内专家分月排行榜第三2010年5月 其他数据库开发大版内专家分月排行榜第三2010年2月 其他数据库开发大版内专家分月排行榜第三2009年11月 其他数据库开发大版内专家分月排行榜第三2009年10月 其他数据库开发大版内专家分月排行榜第三2009年5月 其他数据库开发大版内专家分月排行榜第三2009年4月 其他数据库开发大版内专家分月排行榜第三2008年12月 其他数据库开发大版内专家分月排行榜第三2008年11月 其他数据库开发大版内专家分月排行榜第三2008年10月 其他数据库开发大版内专家分月排行榜第三2008年9月 其他数据库开发大版内专家分月排行榜第三2007年9月 其他数据库开发大版内专家分月排行榜第三2007年8月 其他数据库开发大版内专家分月排行榜第三2007年4月 其他数据库开发大版内专家分月排行榜第三2007年3月 其他数据库开发大版内专家分月排行榜第三2007年2月 其他数据库开发大版内专家分月排行榜第三2006年7月 其他数据库开发大版内专家分月排行榜第三2006年2月 其他数据库开发大版内专家分月排行榜第三2006年1月 其他数据库开发大版内专家分月排行榜第三2005年11月 其他数据库开发大版内专家分月排行榜第三2005年10月 其他数据库开发大版内专家分月排行榜第三
2010年 总版技术专家分年内排行榜第二
2009年 总版技术专家分年内排行榜第三
2017年1月 其他数据库开发大版内专家分月排行榜第二2014年8月 其他数据库开发大版内专家分月排行榜第二2014年2月 其他数据库开发大版内专家分月排行榜第二2014年1月 其他数据库开发大版内专家分月排行榜第二2013年12月 其他数据库开发大版内专家分月排行榜第二2013年10月 其他数据库开发大版内专家分月排行榜第二2013年8月 其他数据库开发大版内专家分月排行榜第二2013年5月 其他数据库开发大版内专家分月排行榜第二2013年1月 其他数据库开发大版内专家分月排行榜第二2012年8月 其他数据库开发大版内专家分月排行榜第二2012年5月 其他数据库开发大版内专家分月排行榜第二2012年4月 其他数据库开发大版内专家分月排行榜第二2012年1月 其他数据库开发大版内专家分月排行榜第二
2017年9月 其他数据库开发大版内专家分月排行榜第三2017年7月 其他数据库开发大版内专家分月排行榜第三2017年5月 其他数据库开发大版内专家分月排行榜第三2017年3月 其他数据库开发大版内专家分月排行榜第三2016年12月 其他数据库开发大版内专家分月排行榜第三2014年11月 其他数据库开发大版内专家分月排行榜第三2014年7月 其他数据库开发大版内专家分月排行榜第三2014年6月 其他数据库开发大版内专家分月排行榜第三2014年5月 其他数据库开发大版内专家分月排行榜第三2013年7月 其他数据库开发大版内专家分月排行榜第三2013年3月 其他数据库开发大版内专家分月排行榜第三2012年7月 其他数据库开发大版内专家分月排行榜第三2012年6月 其他数据库开发大版内专家分月排行榜第三2011年12月 其他数据库开发大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。