80万条数据和80个表要更新如何优化

hanbingzhi 2012-01-11 09:13:13
mysql的数据库, 需要更新以下80个表的uid这个字段,用了一个十分笨的方法来进行存储过程更新,发现太慢了
原来把旧表倒过来的时候id没有同步过来,现在想把id和旧库统一一下,一开始用程序循环更新发现1秒才能更新2-3个id,80w的数据量要更新好几天,改为存储过程以后发现也没有快多少,求高手来个方法或者思路,如何提高效率
因为这个库已经使用了,不要说从新导入这样的方法 谢谢~~~~
DELIMITER $$;

DROP PROCEDURE IF EXISTS `ultrax`.`test`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(
mysqluid char(8),--更改前的uid
sqlid char(8)--更改后的uid
)
BEGIN
update ucenter.uc_members set uid=sqlid where uid=mysqluid; /*将用户表先更新如果有id被占用将会报错停止更新*/
update ucenter.uc_admins set uid=sqlid where uid=mysqluid;
update ucenter.uc_friends set uid=sqlid where uid=mysqluid;
update ucenter.uc_memberfields set uid=sqlid where uid=mysqluid;
update ucenter.uc_newpm set uid=sqlid where uid=mysqluid;
update ucenter.uc_pm_members set uid=sqlid where uid=mysqluid;
update ucenter.uc_protectedmembers set uid=sqlid where uid=mysqluid;
update pre_common_admincp_cmenu set uid=sqlid where uid=mysqluid;
update pre_common_admincp_session set uid=sqlid where uid=mysqluid;
update pre_common_block set uid=sqlid where uid=mysqluid;
update pre_common_block_item_data set uid=sqlid where uid=mysqluid;
update pre_common_credit_log set uid=sqlid where uid=mysqluid;
update pre_common_credit_rule_log set uid=sqlid where uid=mysqluid;
update pre_common_credit_rule_log_field set uid=sqlid where uid=mysqluid;
update pre_common_diy_data set uid=sqlid where uid=mysqluid;
update pre_common_invite set uid=sqlid where uid=mysqluid;
update pre_common_magiclog set uid=sqlid where uid=mysqluid;
update pre_common_member set uid=sqlid where uid=mysqluid;
update pre_common_member_action_log set uid=sqlid where uid=mysqluid;
update pre_common_member_count set uid=sqlid where uid=mysqluid;
update pre_common_member_field_forum set uid=sqlid where uid=mysqluid;
update pre_common_member_field_home set uid=sqlid where uid=mysqluid;
update pre_common_member_magic set uid=sqlid where uid=mysqluid;
update pre_common_member_profile set uid=sqlid where uid=mysqluid;
update pre_common_member_status set uid=sqlid where uid=mysqluid;
update pre_common_onlinetime set uid=sqlid where uid=mysqluid;
update pre_common_statuser set uid=sqlid where uid=mysqluid;
update pre_forum_attachment set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_1 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_2 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_3 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_4 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_5 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_6 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_7 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_8 set uid=sqlid where uid=mysqluid;
update pre_forum_attachment_9 set uid=sqlid where uid=mysqluid;
update pre_forum_debate set uid=sqlid where uid=mysqluid;
update pre_forum_groupcreditslog set uid=sqlid where uid=mysqluid;
update pre_forum_groupuser set uid=sqlid where uid=mysqluid;
update pre_forum_modwork set uid=sqlid where uid=mysqluid;
update pre_forum_pollvoter set uid=sqlid where uid=mysqluid;
update pre_forum_spacecache set uid=sqlid where uid=mysqluid;
update pre_forum_threadmod set uid=sqlid where uid=mysqluid;
update pre_forum_threadpartake set uid=sqlid where uid=mysqluid;
update pre_home_album set uid=sqlid where uid=mysqluid;
update pre_home_blog set uid=sqlid where uid=mysqluid;
update pre_home_blogfield set uid=sqlid where uid=mysqluid;
update pre_home_class set uid=sqlid where uid=mysqluid;
update pre_home_clickuser set uid=sqlid where uid=mysqluid;
update pre_home_comment set uid=sqlid where uid=mysqluid;
update pre_home_docomment set uid=sqlid where uid=mysqluid;
update pre_home_doing set uid=sqlid where uid=mysqluid;
update pre_home_favorite set uid=sqlid where uid=mysqluid;
update pre_home_feed set uid=sqlid where uid=mysqluid;
update pre_home_friend set uid=sqlid where uid=mysqluid;
update pre_home_friendlog set uid=sqlid where uid=mysqluid;
update pre_home_friend_request set uid=sqlid where uid=mysqluid;
update pre_home_notification set uid=sqlid where uid=mysqluid;
update pre_home_pic set uid=sqlid where uid=mysqluid;
update pre_home_poke set uid=sqlid where uid=mysqluid;
update pre_home_share set uid=sqlid where uid=mysqluid;
update pre_home_show set uid=sqlid where uid=mysqluid;
update pre_home_visitor set uid=sqlid where uid=mysqluid;
update pre_common_magiclog set targetuid =sqlid where targetuid =mysqluid;
update pre_common_invite set fuid =sqlid where fuid =mysqluid;
update pre_forum_debate set affirmvoterids =sqlid where affirmvoterids =mysqluid;
update pre_forum_debate set negavoterids =sqlid where negavoterids =mysqluid;
update pre_forum_forumfield set founderuid =sqlid where founderuid =mysqluid;
update pre_forum_forumrecommend set authorid =sqlid where authorid =mysqluid;
update pre_common_plugin set adminid =sqlid where adminid =mysqluid;
update pre_forum_polloption set voterids =sqlid where voterids =mysqluid;
update pre_forum_post set authorid =sqlid where authorid =mysqluid;
update pre_forum_thread set authorid =sqlid where authorid =mysqluid;
update pre_home_album set target_ids=sqlid where target_ids=mysqluid;
update pre_home_blogfield set target_ids=sqlid where target_ids=mysqluid;
update pre_home_comment set authorid=sqlid where authorid=mysqluid;
update pre_home_comment set id=sqlid where id=mysqluid;
update pre_home_favorite set spaceuid=sqlid where spaceuid=mysqluid;
update pre_home_feed set target_ids=sqlid where target_ids=mysqluid;
update pre_home_friend set fuid =sqlid where fuid =mysqluid;
update pre_home_friend_request set fuid=sqlid where fuid=mysqluid;
update pre_home_friendlog set fuid =sqlid where fuid =mysqluid;
update pre_home_notification set authorid =sqlid where authorid =mysqluid;
update pre_home_poke set fromuid =sqlid where fromuid =mysqluid;
update pre_home_share set fromuid=sqlid where fromuid=mysqluid;
update pre_home_visitor set vuid =sqlid where vuid =mysqluid;
update ucenter.uc_friends set FRIENDID =sqlid where FRIENDID =mysqluid;
END$$

DELIMITER ;$$
...全文
102 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
hanbingzhi 2012-01-13
  • 打赏
  • 举报
回复
多谢各位的解答了 没有特别有效的办法也没辙,慢慢忍受吧 也就2天就倒完了
iihero_ 2012-01-12
  • 打赏
  • 举报
回复
如果觉得慢,重建一个备用库,再导入一遍,很快就会完成。你的程序最后只需要改一下库名就完了。谈不上什么浪费时间。

楼主所说的逐个ID更新,除了建索引以外,也没什么好的优化的手段了。就如同不段的通过查字典去改个别的错别字,再快也快不到哪儿去
hanbingzhi 2012-01-12
  • 打赏
  • 举报
回复
id是自增的 不过前50w的数据有的是倒错的有删除等不规范的操作 所以很多都是不连续的
比如我的库里面50w以前基本连续,id为50w-60w的数据是空的,id为70-80是不连续的,id为80-100w是空的 id为100w以后是连续的

新旧id没有任何关系 完全是2个库的id 现在是要2个库的id统一 以后还需要有后期的开发需要2边的id相同才能互相调用 所以才要进行这恶心的操作
flykobesummer 2012-01-12
  • 打赏
  • 举报
回复
哦…… 刚才看错了……

请问楼主,id是自增id吗?新旧id间会不会有什么关系?比如相差多少?
flykobesummer 2012-01-12
  • 打赏
  • 举报
回复
update ucenter.uc_members set uid=sqlid where uid=mysqluid; 这种句子,加索引没用吧?都得一行一行的检索
hanbingzhi 2012-01-12
  • 打赏
  • 举报
回复
噢噢 我看了下 数据量大的表都有uid这个索引 ,没有索引的表有的只有几条或者几十条数据,个别的在几百个
我都加上了 但是提升很有限
ACMAIN_CHM 2012-01-12
  • 打赏
  • 举报
回复
没有索引,则会导致全表扫描。 也就是说,如果表中有100,000条记录,则每次你执行 update table1 set id=123 where id=456 都会把这 100,000记录都查询检查一遍。
hanbingzhi 2012-01-12
  • 打赏
  • 举报
回复
晕 可能不是所有的表都有索引,这也影响速度吗
hanbingzhi 2012-01-12
  • 打赏
  • 举报
回复
索引在数据库建立的时候就已经有了,还有其他的办法没 比如拼一个sql语句来执行更新批量的表
jiahehao 2012-01-12
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 rucypli 的回复:]
把旧表换个名字放到新库里面 然后和每个表进行一次联合更新
[/Quote]

从楼主的实例来看,旧表已存在于目前的数据库中。

似乎只有这楼主列的一条条更新的办法,还能有什么新招?

坐等狼头兄出现。
ACMAIN_CHM 2012-01-12
  • 打赏
  • 举报
回复
在你这80多表上创建基于与mysqluid相关联字段的索引。
rucypli 2012-01-11
  • 打赏
  • 举报
回复
把旧表换个名字放到新库里面 然后和每个表进行一次联合更新

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧