mysql updated 优化更新的方式

在工作中遇见个问题,navicat premiun for mysql 要把两张表A,B关联更新根据id字段更新pass字段,每张表的数据都是2000w+,用update
A as a,B as b SET a.pass=b.pass WHERE a.id=b.id;的方式进行更新,从昨天下午15开始,今天早上
发现电脑在晚上凌晨就宕机了,有什么办法可以避免这种情况,耗时稍微长一些没有关系(mysql版本为5.1)
听说update可以分批次更新,具体要怎么做,麻烦详细些,我刚刚使用mysql,对于语法什么的都不了解,谢谢大家!

这是基于spring的代码,基于其他照葫芦画瓢就行了,我电脑是4核,开了5个线程(并发5个线程),线程数=cpu核心数+1,按这个规则来


        package com.example.demo;

        import com.sun.jmx.remote.internal.ArrayQueue;
        import org.junit.Before;
        import org.junit.Test;
        import org.junit.runner.RunWith;
        import org.springframework.beans.factory.annotation.Autowired;
        import org.springframework.boot.test.context.SpringBootTest;
        import org.springframework.dao.DataAccessException;
        import org.springframework.jdbc.core.JdbcTemplate;
        import org.springframework.test.context.junit4.SpringRunner;

        import java.util.List;
        import java.util.Map;
        import java.util.Queue;
        import java.util.concurrent.Semaphore;
        import java.util.concurrent.atomic.AtomicLong;

        @RunWith(SpringRunner.class)
        @SpringBootTest
        public class DemoApplicationTests {

                private Long total;
                // 声明volatile关键字为了保证可见性,多线程2大特性,一:原子性,二:可见性
                private volatile Semaphore semaphore;
                private volatile AtomicLong aleary;// 已经取了多少条
                private volatile AtomicLong real;// 真实已执行了多少条
                private volatile AtomicLong threadCount;// 执行第几次线程

                @Autowired
                private JdbcTemplate jdbcTemplate;

                @Before
                private void init(){
                        // 初始化总数,因为我看你的事a表根据b表更新
                        total = jdbcTemplate.queryForObject("SELECT COUNT(id) FROM B",Long.class);
                        // 初始化信号量(并发锁数量)
                        semaphore = new Semaphore(5);
                        // 初始化已经取了0条
                        aleary = new AtomicLong(0);
                        real = new AtomicLong(0);
                        threadCount = new AtomicLong(0);
                }

                @Test
                public void contextLoads() {
                        // 开始多线程执行
                        for(int i=0;i<total/1000+1;i++){// 除以1000是因为1个线程取1000,一共需要多少个线程,其实可以优化,让线程可回收,这样可以减小内存消耗,不过,你的应该是只执行一次,以后不用了,所以就没有必要了
                                new Thread(new Runnable() {
                                        @Override
                                        public void run() {
                                                // 获取一把锁
                                                try {
                                                        semaphore.acquire();
                                                        System.out.println("第"+threadCount.addAndGet(1)+"个线程进来了...");
                                                        List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT id,pass FROM B LIMIT " + aleary.longValue() + "," + 1000);// 取一千条数据,最后一次可能取不到1000条数据不过无所谓,
                                                        aleary.addAndGet(1000);// 增加1000 ,最后已处理数量会比总数多,无所谓,不影响,其就是为了标记,对了,把mysql的max_allowed_package设大一点,要不取1000条数据可能不让取
                                                        list.forEach((l)->{// jdk8 新特性,lamb表达式,jdk版本低的话改为普通for循环
                                                                try {
                                                                        // 忽略错误插入
                                                                        jdbcTemplate.update("UPDATE IGNORE A SET pass=? WHERE id=?",new Object[]{l.get("pass"),l.get("id")});
                                                                } catch (DataAccessException e) {// try catch 忽略异常
                                                                        e.printStackTrace();
                                                                }finally {
                                                                        System.out.println(real.addAndGet(1));
                                                                }
                                                        });
                                                } catch (InterruptedException e) {
                                                        e.printStackTrace();
                                                }finally {
                                                        semaphore.release();// 释放锁
                                                }
                                        }
                                });
                        }
                }

        }

update A as a set a.pass=(select pass from B as b where a.id=b.id ) where a.id between 0 and 10000;
……

这样分批次更新呢

要是分批更新有没有说,让mysql每次自动更新多少条的,不用每次更新都去操作

update A as a,B as b SET a.pass=b.pass WHERE a.id=b.id and a.id in (select a.id from A as a,B as b where a.pass!=b.pass limit 10000)
自动更新的话可以设置一个定时任务,参考https://blog.csdn.net/zhenzhendeblog/article/details/50599994

主要问题是这个语句会导致表联接查询,数据量大,联接肯定不行,除非你将数据一点点迁移到临时表再做联接更新,不知道你的id有没有什么规则?
可以搞个存储过程通过mysql进行分页查询,每次查询1000条记录的id然后依次执行,update
A as a,B as b SET a.pass=b.pass WHERE a.id=b.id and a.id=n,有索引语句执行很快

mysql 几百万的数据量就会影响性能,几个百万级的表关联查询性能差表现的尤为明显,你这两个2000W加的表关联起来更扩张,建议分区。

1.创建与原始表一样结构的新表,新分区。
2.将原始表中数据复制到新表。
3.删除原始表。
4.将新表名称改为原始表名称
两张表都要这样做。

用 ID 做RANGE分区:
PARTITION BY RANGE (id)
(PARTITION d10w VALUES LESS THAN (100000) ENGINE = InnoDB,
PARTITION d20w VALUES LESS THAN (200000) ENGINE = InnoDB,
PARTITION d50w VALUES LESS THAN (500000) ENGINE = InnoDB,
PARTITION d100w VALUES LESS THAN (1000000) ENGINE = InnoDB

你是用 a.id=b.id去关联更新的,两张表你的id都按一样的规则去分区。
之后再写一个存储过程去,用游标一个分区一个分区的去更新。
如果你想 不做任何改变,就通过优化一个sql去改变,那是不可能。。。mysql没办法实现你的需求。
就单写一个sql来说,你这个sql没什么可优化的,你的 id已经是主键了。
照我我说的 去普及一下相关资料,动起来。
望采纳啊啊啊啊啊 老铁

@Service("transservice")
public class transService {
@Autowired
JdbcTemplate jdbcTemplatesql1;

@Autowired
JdbcTemplate jdbcTemplatesql2;
    //从原表中查询数据
    String asql = "SELECT id,pass FROM A";
    List<Map<String, Object>> AList = jdbcTemplatesql1.queryForList(asql);
    for (Map<String, Object> Amap : AList) {

                Integer Aid = Amap.get("id") == null ? "" : (Integer) Amap.get("id");
                String passA = Amap.get("pass") == null ? "" : ((String) Amap.get("pass")).trim();

                 String bsql = "UPDATE B set "
                                + "pass = '" + passA
                                + "' WHERE id = '"
                                + Aid + "' ;";

                jdbcTemplatesql2.update(bsql);

                }

}

转储数据的时候看好表结构是否一致,字段限制是否一致,,,如你所说不在意速度快慢的话,,,,上述方法已经测试有效..
配置好数据库的链接,,,随意写一个main方法执行 transService方法 即可
希望能够对你有所帮助

1.关联字段建立索引
2.创建一张临时表,先将两表关联记录转存临时表
3.分批次更新

你照着步骤操作,复制到你的navicat执行,注意<改一下> 的注释,有些地方需要改一下。操作完后耐心等待,看看会不会弘机。
假如能跑完,别忘了采纳,我还需要 10+8个C就行了
-- 1.创建存储过程
create procedure update_pro()
begin -- 开始存储过程
DECLARE val_id INT(10);
DECLARE val_pass varchar(32); -- <改一下> 这里把32改成你要更新字段的长度
DECLARE done INT DEFAULT FALSE; -- 控制游标循环变量,默认false
DECLARE My_Cursor CURSOR FOR ( SELECT id, pass FROM test_b ); -- <改一下> 这个sql改成你的表和字段
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 绑定控制变量到游标,游标循环结束自动转true
START TRANSACTION;-- 打开事务
OPEN My_Cursor; -- 打开游标
myLoop: LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到
FETCH My_Cursor into val_id, val_pass; -- 将游标当前读取行的数据顺序赋予变量val_id,val_pass
IF done THEN -- 判断是否继续循环
LEAVE myLoop; -- 结束循环
END IF;
-- 更新数据
UPDATE test_a SET pass = val_pass WHERE id = val_id ; -- <改一下> 这个sql改成你的表和字段 ,val_pass和val_id是上面声明的变量,不用改
END LOOP myLoop; -- 结束自定义循环体
CLOSE My_Cursor; -- 关闭游标
COMMIT; -- 提交事务
END; -- 结束存储过程

-- 2.调用你刚刚创建的存储过程
CALL update_pro();