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

以你之姓@ 2022-06-12 03:15 679阅读 0赞

# 需求：将旧数据导入新系统，已知旧数据为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\` )

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

![Center][]

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一一对应。

![Center 1][]

（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

![Center 2][]

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

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

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

![Center 3][]

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

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() #执行存储过程

结束，谢谢观看

[Center]: /images/20220612/f2843f029a5744f686fd759ba2a79a30.png
[Center 1]: /images/20220612/c0f5e8ec985347119920fe6806ef96a5.png
[Center 2]: /images/20220612/75f16df362894b079c93d9003c996818.png
[Center 3]: /images/20220612/49e4ead18630438487187030dc96782d.png