Question

mysql存储过程数据插入更新慢

同步一张张的数据进库，，但是发现效率非常底，大概在每秒10条左右，，不知道哪儿的问题！插入表的索引已经去掉了，求帮助！
BEGIN
DECLARE count INT DEFAULT 0;
DECLARE orderSn VARCHAR(30);
DECLARE transSn VARCHAR(30);
DECLARE changeSn VARCHAR(30);
DECLARE orderId VARCHAR(30);
DECLARE orderSeq int(4);
DECLARE sn int(8) DEFAULT 10000;
DECLARE inCustId VARCHAR(30);
DECLARE inAccountId VARCHAR(30);
DECLARE outCustId VARCHAR(30);
DECLARE outAccountId VARCHAR(30);
DECLARE myamount DECIMAL(15,4);
DECLARE mynote CHAR(100);
DECLARE mystate VARCHAR(1);
DECLARE atime DATETIME DEFAULT SYSDATE();
DECLARE cursor_trans_in CURSOR FOR
SELECT in_cust_id,in_account_id,out_cust_id,out_account_id,amount,note,state,ctime from tem_transferzundai t;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET count = 1;
OPEN cursor_trans_in;
REPEAT
FETCH cursor_trans_in INTO inCustId,inAccountId,outCustId,outAccountId,myamount,mynote,mystate,atime;
if not count then
BEGIN
if (mystate='0') then set mystate='2';
end if;
SET orderSn=CONCAT(getSnId('AO'),sn);
INSERT INTO account_order(ORDER_SN,ORDER_ID,ORDER_SEQ,CUST_ID,IN_ACCOUNT_ID,OUT_ACCOUNT_ID,amount,ORDER_TYPE,ORDER_NOTE,STATE,CREATE_TIME,APP_ID)
VALUES(orderSn,orderSn,1,outCustId,inAccountId,outAccountId,myamount,'3',mynote,mystate,atime,'ZUNDAI');
SET transSn=CONCAT(getSnId('WB'),sn);
INSERT INTO trans_bill(TRANS_SN,ORDER_SN,OUT_CUST_ID,OUT_ACCOUNT_ID,IN_ACCOUNT_ID,IN_CUST_ID,TRAN_TYPE,AMOUNT,CREATE_TIME,STATE,FEE)
VALUES(transSn,orderSn,outCustId,outAccountId,inAccountId,inCustId,'0',myamount,atime,mystate,0);
SET changeSn=CONCAT(getSnId('AC'),sn);
INSERT INTO account_change(CHANGE_SN,ACCOUNT_ID,ORDER_SN,BILL_TYPE,BILL_SN,CUST_ID,SEQ_FLAG,IN_AMOUNT,OUT_AMOUNT,REMARK,STATE,CREATE_TIME)
VALUES(changeSn,inAccountId,orderSn,'3',transSn,inCustId,'1',myamount,0,mynote,mystate,atime);
set sn=sn+1;
SET changeSn=CONCAT(getSnId('AC'),sn);
INSERT INTO account_change(CHANGE_SN,ACCOUNT_ID,ORDER_SN,BILL_TYPE,BILL_SN,CUST_ID,SEQ_FLAG,IN_AMOUNT,OUT_AMOUNT,REMARK,STATE,CREATE_TIME)
VALUES(changeSn,outAccountId,orderSn,'3',transSn,outCustId,'0',myamount,0,mynote,mystate,atime);
if(mystate='1') then
update cust_account t1 SET t1.CASH_AMOUNT=t1.CASH_AMOUNT+myamount,t1.AMOUNT=t1.AMOUNT+myamount where t1.ACCOUNT_ID=inAccountId;
-- 更新现金账户表金额 real_account;
UPDATE cust_account t2 set t2.AMOUNT=t2.AMOUNT-myamount,t2.CASH_AMOUNT=t2.CASH_AMOUNT-myamount where t2.ACCOUNT_ID=outAccountId;
end if;
set sn=sn+1;
set count=0;
END;
end IF;
UNTIL count
END REPEAT;
CLOSE cursor_trans_in;
END

Answer 1

关闭自动commit、一次性提交100条的批量提交