拆表用的MySQL存储过程

MySQL存储过程用途很广泛,下面就为您介绍拆表用的MySQL存储过程,希望对您学习MySQL存储过程方面能够有所帮助。

成都创新互联长期为1000多家客户提供的网站建设服务,团队从业经验10年,关注不同地域、不同群体,并针对不同对象提供差异化的产品和服务;打造开放共赢平台,与合作伙伴共同营造健康的互联网生态环境。为秦皇岛企业提供专业的网站设计制作、成都网站设计秦皇岛网站改版等技术服务。拥有十载丰富建站经验和众多成功案例,为您定制开发。

mysql表或分表的数据达到一定量(也许是800w或者1000w..)这个时候非常需要再分表,简单的办法是直接写
--假设根据user_id分表,分成64张

 
 
 
  1. insert into table_new_0000 select * from table_old where mod(user_id,64)=0;  
  2. insert into table_new_0001 select * from table_old where mod(user_id,64)=1;  
  3. ...  

一共64条sql,OK 搞定。  但是这个一张表被全表扫描了64次,做的无用功比较多,而且导致停机时间比较长。

虽然MySQL存储过程不是很熟,稍稍学习了下写了两个脚本,一个全量+一个增量脚本完成表的拆分。
线上库也实践了下,8个分表,每个分表1000W记录拆分到64个分表。
全量 时间 150分钟,全量的时候几个分表可以一起跑,我是同时跑3个分表
增量 时间 每个分表4分钟 4个一起跑,一共是 8分钟搞定。 这样停机时间加上应用的发布一共只需要20分钟就可以搞定了。

附脚本:

###################
delimeter //

-----------
--- 全量脚本:

 
 
 
  1. CREATE PROCEDURE  sp_xf_move_item()  
  2. begin  
  3. declare v_exit int default 0;  
  4. declare v_spid bigint;  
  5. declare v_id bigint;  
  6. declare i int default 0;  
  7. declare c_table int;  

--定义游标(要分拆的表,定义一个数量的截止时间)

 
 
 
  1. declare c_ids cursor for select id,user_id from item_records_0000 where gmt_modified < '2010-8-25 00:00:00';  
  2. declare  continue handler for not found set v_exit=1;  
  3. open c_ids;  
  4. repeat  

--将需要的值装入变量

 
 
 
  1. fetch c_ids into v_id,v_spid;  
  2. if v_exit = 0 then  
  3. set @vv_id = v_id;  

--根据取模字段获取数据存在的表

 
 
 
  1. select mod(v_spid,64) into c_table;  

--组装动态sql

 
 
 
  1. SET @SQL_CONTEXT =  
  2. CONCAT('insert into item_record_',  
  3. LPAD(c_table, 4, 0),  
  4. ' select * from item_records_0000 where id = ?');  
  5.  
  6. PREPARE STMT FROM @SQL_CONTEXT;  
  7. --执行sql  
  8. EXECUTE STMT using @vv_id;  
  9. DEALLOCATE PREPARE STMT;  
  10. end if;  
  11. set ii=i+1;  
  12.  

--100条提交一次,以提高效率,记得执行存储过程前设置auto_commit

 
 
 
  1. if mod(i,100)=0 then commit;  
  2. end if;  
  3. until v_exit=1 
  4. end repeat;  
  5. close c_ids;  
  6. commit;  
  7. end;  
  8. //  
  9.  
  10. -----------  
  11. set auto_commit=0;  
  12. call  sp_xf_move_item();  
  13.  
  14. #### 增量脚本 ######  
  15.  
  16. CREATE PROCEDURE sp_xf_add_item()  
  17. begin  
  18. declare v_exit int default 0;  
  19. declare v_spid bigint;  
  20. declare v_id bigint;  
  21. declare i int default 0;  
  22. declare c_table int;  
  23. declare c_ids cursor for select id,supplier_id from item_records_0000 where gmt_modified >= '2010-8-25 00:00:00';  
  24. declare  continue handler for not found set v_exit=1;  
  25. open c_ids;  
  26. repeat  
  27.  
  28. fetch c_ids into v_id,v_spid;  
  29. if v_exit = 0 then  
  30. set @vv_id = v_id;  
  31. set @v_row=0;  
  32. select mod(v_spid,64) into c_table;  
  33.  

--判断数据是否已经存在

 
 
 
  1. SET @SQL_C =  
  2. CONCAT('select count(*) into @v_row from item_record_',  
  3. LPAD(c_table, 4, 0),  
  4. ' where id = ?');  
  5.  
  6. PREPARE STMT_C FROM @SQL_C;  
  7. EXECUTE STMT_C using @vv_id;  
  8. DEALLOCATE PREPARE STMT_C;                         
  9.  
  10. SET @SQL_INSERT =  
  11. CONCAT('insert into bbc_item_record_',  
  12. LPAD(c_table, 4, 0),  
  13. ' select * from item_records_0000 where id = ?');  
  14.  
  15. PREPARE STMT_I FROM @SQL_INSERT;           
  16.  
  17. SET @SQL_DELETE =  
  18. CONCAT('DELETE FROM bbc_item_record_',  
  19. LPAD(c_table, 4, 0),  
  20. ' where id = ?');  
  21. PREPARE STMT_D FROM @SQL_DELETE;       

--如果数据已经存在,则先delete在insert          

 
 
 
  1. if @v_row>0 then   
  2.  
  3. EXECUTE STMT_D using @vv_id;  
  4. DEALLOCATE PREPARE STMT_D;  
  5.  
  6. end if;  
  7. EXECUTE STMT_I using @vv_id;  
  8. DEALLOCATE PREPARE STMT_I;         
  9.  
  10. end if;  
  11. set ii=i+1;  
  12. if mod(i,100)=0 then commit;  
  13. end if;  
  14. until v_exit=1 
  15. end repeat;  
  16. close c_ids;  
  17. commit;  
  18. end;  
  19. //  
  20.  
  21. -------  
  22.  

如果全量和增量之间的时间拖的比较长,那么可以设置时间,多做几次增量已缩短最后的停机时间,你懂的。。。
call sp_xf_add_item()//

【编辑推荐】

深入探讨MySQL锁机制

MySQL字段中的集合

MySQL字段类型简介

Mysql外键用法分析

详解MySQL数据表类型

  

本文名称:拆表用的MySQL存储过程
当前链接:http://www.36103.cn/qtweb/news2/25252.html

网站建设、网络推广公司-创新互联,是专注品牌与效果的网站制作,网络营销seo公司;服务项目有等

广告

声明:本网站发布的内容(图片、视频和文字)以用户投稿、用户转载内容为主,如果涉及侵权请尽快告知,我们将会在第一时间删除。文章观点不代表本网站立场,如需处理请联系客服。电话:028-86922220;邮箱:631063699@qq.com。内容未经允许不得转载,或转载时需注明来源: 创新互联