需求:将旧数据导入新系统,已知旧数据为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
这样 进行连表操作是会极大减少运行速度,我们来看一下执行计划
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
添加主键索引:
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() #执行存储过程
结束,谢谢观看