在工作中遇见个问题，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();