mysql数据库存储过程数据迁移案例与比较

cursor 与 insert ...select 对比：　　

　　cursor：安全，不会造成死锁，可以在服务运行阶段跑,比较稳定。

　　insert...select :速度快，但是可能造成死锁，相比cursor能够成倍提升，在服务停止的情况下迁移,速度快

数据迁移案例：

　　首先数据的迁移绝对不是一朝一夕能够快速迁移完成的，如果可以很快完成的 dump便可以搞定，没必要大费周折了。

　　既然不是一朝一夕能完成的，那么有关键的日志记录表能够良好的反应数据迁移的过程

　　迁移日志表脚本：

DROP TABLE IF EXISTS `cx_delete_log`;

CREATE TABLE `cx_delete_log` (

  `id` int(30) NOT NULL,

  `table_name` varchar(30) DEFAULT NULL,

  `start_tm` datetime DEFAULT NULL,

  `end_tm` datetime DEFAULT NULL,

  `status` int(10) DEFAULT NULL,

  `pro_create_time` datetime DEFAULT NULL,

  `pro_end_time` datetime DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------

-- Records of cx_delete_log

-- ----------------------------

INSERT INTO `cx_delete_log` VALUES ('', 'cx_waybill', '2017-01-01 00:00:00', '2017-01-01 00:10:00', '', '2017-03-21 14:06:21', null);

ALTER TABLE `cx_delete_log`

ADD INDEX `index_end_date` (`end_tm`) USING BTREE ;

　　首先迁移的增量字段确定。这里存在一个最优解，即你每分钟的数据量，与你mysql的性能，我们可以在此之间寻找到一个平衡点（cursor与insert...select并不相同），设置每次迁移多次时间段的数据，这样能，最大效率的利用我们数据库的性能。

　　我们插入了一条日志数据，有这个来控制每次数据迁移的间隔时间段，与开始时间

如：2017-01-01 00:00:00 start_tm数据迁移增量字段的开始时间，2017-01-01 00:10:00 end_tm数据增量字段迁移的结束时间，时间间隔10分钟。

状态为1代表成功，2017-03-21 14:06:21 代表执行这段时间数据迁移的执行时间，pro_end_time代表执行这段时间数据迁移的执行完毕时间时间。

存储过程：

　　存储过程分为两个部分，1.日志的写入2.迁移操作的进行

　　日志写入：

   delimiter |

drop procedure if exists batch_move_cx_waybill_partition_by_minute;

create procedure batch_move_cx_waybill_partition_by_minute()

begin

        DECLARE proNo INTEGER;

        DECLARE startDate TIMESTAMP;

        DECLARE endDate TIMESTAMP;

        DECLARE nextDate TIMESTAMP;

        DECLARE pro_endDate TIMESTAMP;

        SET pro_endDate='2017-03-20 00:00:00';

    REPEAT

            SELECT  ID, end_tm,DATE_ADD(end_tm,INTERVAL '00:10:00' hour_second) INTO proNo,startDate ,endDate from  cx_delete_log  ORDER BY end_tm desc limit 1;

        IF  curtime()>'06:00:00' THEN

            SET  startDate =pro_endDate;

        END IF;

        IF startDate <pro_endDate THEN

            INSERT into cx_delete_log(id,table_name,start_tm,end_tm,status,pro_create_time)VALUES(proNo+1,'cx_waybill',startDate,endDate,'',SYSDATE());

            CALL    cursor_move_cx_waybill_partition(startDate);

            update cx_delete_log  set pro_end_time =SYSDATE() , `status`=1 where  id=proNo+1;

            COMMIT;

        END IF;

            UNTIL startDate >=pro_endDate

    END REPEAT;

end  |

delimiter ;

迁移操作的进行：

　　实例为cursor，insert..select较为简单，直接带入就行，就不做示例了

   delimiter ||

drop procedure if exists cursor_move_cx_waybill_partition;

create procedure cursor_move_cx_waybill_partition(IN startDate_tmp TIMESTAMP)

begin

        DECLARE isEXist INTEGER;

        DECLARE WAYBILLNO_TMP VARCHAR(32);

        DECLARE RESOURCECODE_TMP VARCHAR(10);

        DECLARE APPOINTMENTDDELIVERYTIME_TMP TIMESTAMP;

        DECLARE APPOINTMENTDDELIVERYENDTIME_TMP TIMESTAMP;

        DECLARE EXPECTEDDELIVERYTIME_TMP TIMESTAMP;

        DECLARE ORIGINATEID_TMP VARCHAR(32);

        DECLARE DESTINATIONID_TMP VARCHAR(32);

        DECLARE WAYBILLSTATUS_TMP VARCHAR(4);

        DECLARE VERSIONNO_TMP INTEGER;

        DECLARE STATUS_TMP CHAR(1);

        DECLARE FAILSTARTUS_TMP VARCHAR(10);

        DECLARE FAILCAUSEDESC_TMP VARCHAR(500);

        DECLARE MEMBERID_TMP VARCHAR(32);

        DECLARE HIDEFLAG_TMP VARCHAR(4);

        DECLARE CREATEUSERID_TMP VARCHAR(32);

        DECLARE CREATETIME_TMP TIMESTAMP;

        DECLARE UPDATEUSERID_TMP VARCHAR(32);

        DECLARE UPDATETIME_TMP TIMESTAMP;

        DECLARE APPOINTMENTNO_TMP VARCHAR(32);

        DECLARE SOURCECITY_TMP VARCHAR(32);

        DECLARE DESTCITY_TMP VARCHAR(32);

        DECLARE SOURCECITYCODE_TMP VARCHAR(20);

        DECLARE DESTCITYCODE_TMP VARCHAR(20);

        DECLARE EMPCODE_TMP VARCHAR(10);

        DECLARE RECEMPCODE_TMP VARCHAR(10);

        DECLARE RECMEMBERID_TMP VARCHAR(32);

        DECLARE WAYBILLFEE_TMP DOUBLE(10,0);

        DECLARE MOBILE_TMP VARCHAR(20);

        DECLARE RECMOBILE_TMP VARCHAR(20);

        DECLARE RECTIME_TMP TIMESTAMP;

        DECLARE ENDTIME_TMP TIMESTAMP;

        DECLARE EXPECTMEMBERID_TMP VARCHAR(32);

        DECLARE ORIGINATE_TMP VARCHAR(500);

        DECLARE DESTINATION_TMP VARCHAR(500);

        DECLARE PAYTYPE_TMP VARCHAR(4);

        DECLARE SECRECYTYPE_TMP VARCHAR(32);

        DECLARE SEND_EVALUATE_TMP VARCHAR(10);

        DECLARE PRODUCT_TYPE_TMP VARCHAR(32);

        DECLARE SERVICES_PROD_CODE_TMP VARCHAR(200);

        DECLARE CHANGERECORD_TMP VARCHAR(32);

        DECLARE IS_ISSUED_PREFRENCE_TMP VARCHAR(10);

        DECLARE REC_EVALUATE_TMP VARCHAR(10);

        DECLARE done INTEGER;

        DECLARE bill_cursor CURSOR for SELECT  WAYBILLNO, RESOURCECODE , APPOINTMENTDDELIVERYTIME , APPOINTMENTDDELIVERYENDTIME , EXPECTEDDELIVERYTIME ,ORIGINATEID , DESTINATIONID , WAYBILLSTATUS , VERSIONNO , STATUS, FAILSTARTUS, FAILCAUSEDESC, MEMBERID, HIDEFLAG, CREATEUSERID, CREATETIME, UPDATEUSERID, UPDATETIME, APPOINTMENTNO, SOURCECITY, DESTCITY, SOURCECITYCODE, DESTCITYCODE, EMPCODE, RECEMPCODE, RECMEMBERID,WAYBILLFEE, MOBILE, RECMOBILE, RECTIME, ENDTIME, EXPECTMEMBERID, ORIGINATE, DESTINATION, PAYTYPE, SECRECYTYPE, SEND_EVALUATE, PRODUCT_TYPE, SERVICES_PROD_CODE, CHANGERECORD, IS_ISSUED_PREFRENCE, REC_EVALUATE FROM cx_waybill WHERE CREATETIME > startDate_tmp AND CREATETIME <= DATE_ADD(startDate_tmp,INTERVAL '00:10:00' hour_second);

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        open bill_cursor;

        loop_bill:LOOP

        IF done = 1 THEN

            LEAVE loop_bill;

        END IF;

        FETCH bill_cursor INTO WAYBILLNO_TMP, RESOURCECODE_TMP, APPOINTMENTDDELIVERYTIME_TMP, APPOINTMENTDDELIVERYENDTIME_TMP,EXPECTEDDELIVERYTIME_TMP,ORIGINATEID_TMP, DESTINATIONID_TMP, WAYBILLSTATUS_TMP, VERSIONNO_TMP, STATUS_TMP, FAILSTARTUS_TMP, FAILCAUSEDESC_TMP, MEMBERID_TMP, HIDEFLAG_TMP, CREATEUSERID_TMP, CREATETIME_TMP, UPDATEUSERID_TMP, UPDATETIME_TMP, APPOINTMENTNO_TMP, SOURCECITY_TMP, DESTCITY_TMP, SOURCECITYCODE_TMP, DESTCITYCODE_TMP, EMPCODE_TMP, RECEMPCODE_TMP, RECMEMBERID_TMP,WAYBILLFEE_TMP, MOBILE_TMP, RECMOBILE_TMP, RECTIME_TMP, ENDTIME_TMP, EXPECTMEMBERID_TMP, ORIGINATE_TMP, DESTINATION_TMP, PAYTYPE_TMP, SECRECYTYPE_TMP, SEND_EVALUATE_TMP, PRODUCT_TYPE_TMP, SERVICES_PROD_CODE_TMP, CHANGERECORD_TMP, IS_ISSUED_PREFRENCE_TMP, REC_EVALUATE_TMP;

        IF WAYBILLNO_TMP is NULL THEN

            LEAVE loop_bill;

        END IF;

        SELECT count(1) INTO isEXist from cx_waybill_partition w where w.WAYBILLNO=WAYBILLNO_TMP;

        IF isEXist >0 THEN

        LEAVE loop_bill;

        ELSE

        INSERT  INTO cx_waybill_PARTITION (WAYBILLNO, RESOURCECODE, APPOINTMENTDDELIVERYTIME, APPOINTMENTDDELIVERYENDTIME,EXPECTEDDELIVERYTIME,ORIGINATEID, DESTINATIONID, WAYBILLSTATUS, VERSIONNO, STATUS, FAILSTARTUS, FAILCAUSEDESC, MEMBERID, HIDEFLAG, CREATEUSERID, CREATETIME, UPDATEUSERID, UPDATETIME, APPOINTMENTNO, SOURCECITY, DESTCITY, SOURCECITYCODE, DESTCITYCODE, EMPCODE, RECEMPCODE, RECMEMBERID,WAYBILLFEE, MOBILE, RECMOBILE, RECTIME, ENDTIME, EXPECTMEMBERID, ORIGINATE, DESTINATION, PAYTYPE, SECRECYTYPE, SEND_EVALUATE, PRODUCT_TYPE, SERVICES_PROD_CODE, CHANGERECORD, IS_ISSUED_PREFRENCE, REC_EVALUATE)VALUES(WAYBILLNO_TMP, RESOURCECODE_TMP, APPOINTMENTDDELIVERYTIME_TMP, APPOINTMENTDDELIVERYENDTIME_TMP,EXPECTEDDELIVERYTIME_TMP,ORIGINATEID_TMP, DESTINATIONID_TMP, WAYBILLSTATUS_TMP, VERSIONNO_TMP, STATUS_TMP, FAILSTARTUS_TMP, FAILCAUSEDESC_TMP, MEMBERID_TMP, HIDEFLAG_TMP, CREATEUSERID_TMP, CREATETIME_TMP, UPDATEUSERID_TMP, UPDATETIME_TMP, APPOINTMENTNO_TMP, SOURCECITY_TMP, DESTCITY_TMP, SOURCECITYCODE_TMP, DESTCITYCODE_TMP, EMPCODE_TMP, RECEMPCODE_TMP, RECMEMBERID_TMP,WAYBILLFEE_TMP, MOBILE_TMP, RECMOBILE_TMP, RECTIME_TMP, ENDTIME_TMP, EXPECTMEMBERID_TMP, ORIGINATE_TMP, DESTINATION_TMP, PAYTYPE_TMP, SECRECYTYPE_TMP, SEND_EVALUATE_TMP, PRODUCT_TYPE_TMP, SERVICES_PROD_CODE_TMP, CHANGERECORD_TMP, IS_ISSUED_PREFRENCE_TMP, REC_EVALUATE_TMP);

        END IF;

        end Loop;

close bill_cursor;

COMMIT;

end  ||

delimiter ;

定时器的创建：

　　每天凌晨一点执行：

   ---轮循删除定时器

   drop event if exists waybill_move_event;

create event waybill_move_event

on schedule every 1 DAY STARTS '2017-01-17 01:00:00'

on completion preserve ENABLE

do call batch_move_cx_waybill_partition_by_minute();

alter event waybill_move_event on completion preserve enable;

其实，在中间踩过一些坑，但是很多都是百度都能解决的。事实证明，这样的数据迁移，其速度并不慢，而且性能相对稳定，在后续的观察中，并未对数据库造成太多的压力，可看作种蚂蚁搬家。

巴特西

mysql数据库存储过程数据迁移案例与比较

最新文章

热门文章