mysql百万级以上的数据迁移及优化

最新推荐文章于 2025-06-09 21:45:51 发布

原创最新推荐文章于 2025-06-09 21:45:51 发布 · 1.9k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据 #优化 #迁移 #sql

mysql数据迁移专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一次涉及580万条记录的数据迁移项目经验。通过优化SQL查询、创建临时表和存储过程等手段，显著提高了数据迁移效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求：将旧数据导入新系统，已知旧数据为580万。

由于要做数据迁移工作，已知一个旧表数据为580万条，要导入新库，并且根据新的业务逻辑插入到各个分表，

结果就是要将这580万数据分别插入到近100张表中

在此也作为我自己的一个工作的整理，下面是我整理的整个思路及各个sql语句，包括优化前后的执行速度对比。

一、由于关键字段不在旧表的主表中，所以需要连表查询得到关键的连表字段的数据bankid

（1）最简单的连表操作

select a.`name`,a.caseid,a.phone,a.infor,a.callDate,b.bankid from t_execute as a

LEFT JOINt_case_notrepeat_changetype as b on a.caseid=b.id

由于t_exexute表中有580万条数据，而连表的t_case_notrepeat_changetype 表中也有20万条数据。

当没有创建主键索引时，此条语句的执行速度非常慢。

（2）优化连表操作

给连表的关键字段添加主键索引：

ALTER TABLE t_case_notrepeat_changetypeADD PRIMARY KEY ( `id` )

这样进行连表操作是会极大减少运行速度，我们来看一下执行计划

a表查询按照全表传，b表查询按照索引。这样是使用了索引进行了查询，提高了查询效率。

（3）由于观察数据，发现caseid中根据业务包含了bankid，就做一张临时表，只存主表 t_execute的id和截取出来的bankid

查看规律，发现逻辑是取caseid：ABCNS120305A0001这个字符串的前2位和第四第五位，也就是ABNS

截取的sql语句：selectCONCAT(SUBSTR(a.caseid,1,2) ,SUBSTR(a.caseid, 4,2)) bankid, a.id from t_execute a

运行结果：可以获得截取后的值和id一一对应。

（4）创建临时表只有id和截取之后的bankid。并添加主键，由此临时表去做主表t_execute的连接表使用bankid

创建临时表 temp_id:

create table temp_idas select CONCAT(SUBSTR(a.caseid,1,2) ,SUBSTR(a.caseid, 4,2)) bankid, a.id from t_execute a ;

添加主键索引：

ALTER TABLE temp_id ADD PRIMARY KEY ( `id` )

查看连表执行计划：已经按照主键索引去执行了。再运行查询语句，580万数据与临时表查询速度只用了57s

二、执行插入语句，测试单表插入

（1）按照没有优化过的连表查询语句去执行插入方法。执行速度是我用了3个小时，只插入了50万数据。

（2）用上述方法建立索引和临时表，执行速度得到了极大的提升，测试插入20万数据只用了8s

三、实现把数据按条件分插入不同表的存储过程，带有循环操作的

DROP PROCEDURE
IF EXISTS selectdata     #如果此存储过程存在就删除

CREATE PROCEDURE selectdata()    #创建一个存储过程
BEGIN                           
	DECLARE Done INT DEFAULT 0;    #声明一个表示游标异常的参数
  DECLARE bankcode VARCHAR(128); #声明一个存放bankid的参数
  DECLARE sqlStr VARCHAR(2000);  #声明一个存放可执行语句的一个参数
  
	DECLARE rs CURSOR FOR select bankid from temp_id GROUP BY bankid; #声明一个游标 ，存放要循环的值，查出几条这个游标就会让语句执行几次
	
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;  #声明当游标到了数据库表格最后一行的时候，设置done=1
  
	
	OPEN rs;    #开启游标
	FETCH NEXT FROM rs INTO bankcode; #将游标中的bankid赋值给bankcode中
		REPEAT    #循环语句的起始标签
			IF NOT Done THEN   #如果没有异常那么执行下面
          #sql里面写上要执行的插入语句，将参数加在语句中间，用引号和都好区分连接
          set sqlStr = CONCAT('INSERT INTO t_cuiji_',bankcode,' (caseid,NAME,phone,infor,callDate) SELECT a.caseid,a. NAME,a.phone,a.Content,a.serdate FROM

 t_execute a LEFT JOIN temp_id b ON a.id = b.id WHERE b.bankid = "',bankcode,'"');   
					 
             set @sqlStr:=sqlStr;     
             prepare stmt1 from @sqlStr;
             execute stmt1;             #执行stmt1.也就是执行上面的sqlstr语句
             deallocate prepare stmt1;  #删除游标 
             
			END IF ;  #if语句结束
		FETCH NEXT FROM rs INTO bankcode; #进入下一个循环，把下一个bankid赋给bankcode
		UNTIL Done END REPEAT;
	CLOSE rs;     #关闭游标
END

CALL selectdata() #执行存储过程

结束，谢谢观看