如何在MySQL（Laravel）中优化存储过程

我想优化用mysql联合编写的存储过程。计算将需要40分钟以上。但是excel需要5分钟来计算其记录。

BEGIN
DeclARE done INT DEFAULT FALSE;
DeclARE panCrd VARCHAR(255);
DeclARE cur1 CURSOR FOR
SELECT DISTINCT(bank_balance_card.pan)
FROM bank_balance_card
INNER JOIN bank_balance ON bank_balance.id = bank_balance_card.bank_balance_id
WHERE bank_balance.bankbal_date = reportDate
UNION
SELECT DISTINCT(daily_balance_shift.pan)
FROM daily_balance_shift
WHERE daily_balance_shift.repot_date = previousDate
UNION
SELECT DISTINCT(cardauthorisation.Card_PAN)
FROM cardauthorisation
WHERE cardauthorisation.SettlementDate = reportDate
UNION
SELECT DISTINCT(cardbaladjust.Card_PAN)
FROM cardbaladjust
WHERE cardbaladjust.SettlementDate = reportDate
UNION
SELECT DISTINCT(cardchrgbackrepres.Card_PAN)
FROM cardchrgbackrepres
WHERE cardchrgbackrepres.SettlementDate = reportDate
UNION
SELECT DISTINCT(cardevent.Card_PAN)
FROM cardevent
WHERE cardevent.Event_Date LIKE concat(reportDate,"%")
UNION
SELECT DISTINCT(cardfee.Card_PAN)
FROM cardfee
WHERE cardfee.SettlementDate = reportDate
UNION
SELECT DISTINCT(cardfinancial.Card_PAN)
FROM cardfinancial
WHERE cardfinancial.SettlementDate = reportDate
UNION
SELECT DISTINCT(cardloadunload.Card_PAN)
FROM cardloadunload
WHERE cardloadunload.SettlementDate = reportDate
UNION
SELECT DISTINCT(agencybankingfee.Card_PAN)
FROM agencybankingfee
WHERE agencybankingfee.SettlementDate = reportDate
UNION
SELECT DISTINCT(agencybanking.Card_PAN)
FROM agencybanking
WHERE agencybanking.SettlementDate = reportDate;
DeclARE CONTINUE HANDLER FOR NOT FOUND SET dOne= TRUE;
OPEN cur1;
read_loop: LOOP
set dOne= false;
FETCH cur1 INTO panCrd;
IF done THEN
LEAVE read_loop;
END IF;
CALL openingAcBal(previousDate,panCrd,@opening_ac_bal1);
CALL prevtrans_settled(previousDate,@prevTrans_settle);
IF (@prevTrans_settle IS NULL) THEN
SET @prevTrans_settle = 0;
END IF;
IF (@opening_ac_bal1 IS NULL) THEN
SET @opening_ac_bal1 = 0;
END IF;
CALL ATMSettled(startDate,endDate,@ATM_Settled);
CALL POSSettled(startDate,@POS_Settled);
CALL ATMFEE(startDate,@ATM_FEE);
CALL FPIN(startDate,@FPIN);
CALL BacsIN(startDate,@BacsIN);
IF (@BacsIN IS NULL) THEN
SET @BacsIN = 0;
END IF;
CALL FPout(startDate,@FP_out);
CALL ABDD(startDate,@AB_DD);
IF (@AB_DD IS NULL) THEN
SET @AB_DD = 0;
END IF;
CALL FPoutfee(startDate,@FP_out_fee);
CALL chargeBacks(startDate,@charge_backs);
CALL RepresentmentsDs(startDate,@representments);
CALL Otherfees(startDate,@Other_fees);
CALL LoadUnload(startDate,@Load_Unload);
CALL BalanceAdj(startDate,@Balance_Adj);
CALL closingacbalgps(@clsbal,reportDate,panCrd);
IF (@clsbal IS NULL) THEN
SET @clsbal = 0;
END IF;
SET @clacbalcal = @opening_ac_bal1 - @ATM_Settled - @POS_Settled - @ATM_FEE + @FPIN + @BacsIN - @FP_out - @AB_DD - @FP_out_fee - @Other_fees + @Load_Unload + @Balance_Adj + @charge_backs - @representments;
SET @diffCheck = @clsbal - @clacbalcal;
SET @trans_settled_not_adj_gps = 0;
SET @trans_settled_not_adj_gps_2 = NULL;
SET @trans_adj_gps1 = 0;
IF (@prevTrans_settle > 0) THEN
SET @trans_settled_not_adj_gps = (-1 * @prevTrans_settle);
END IF;
IF (@prevTrans_settle <0) THEN
SET @trans_settled_not_adj_gps = (-1 * @prevTrans_settle);
END IF;
SET @diffamt = 0;
SET @diffamt = @clacbalcal - @clsbal + @trans_settled_not_adj_gps;
SET @insertedId = NULL;
SELECT daily_balance_shift.id INTO @insertedId FROM daily_balance_shift WHERE daily_balance_shift.repot_date = reportDate AND daily_balance_shift.pan = panCrd;
IF (@insertedId IS NULL) THEN
SET @uniQueId = uuid();
INSERT INTO daily_balance_shift (id,repot_date,pan,opening_ac_bal,ATM_Settled,POS_Settled,ATM_FEE,FPIN,Bacs_IN,FP_out,AB_DD,FP_out_fee,charge_backs,representments,Other_fees,Load_Unload,Balance_Adj,closing_ac_bal_calc,closing_ac_bal_gps,trans_settled_not_adj_gps,trans_settled_not_adj_gps_2,diff) VALUES (@uniQueId,@opening_ac_bal1,@ATM_Settled,@POS_Settled,@ATM_FEE,@FPIN,@BacsIN,@FP_out,@AB_DD,@FP_out_fee,@charge_backs,@representments,@Other_fees,@Load_Unload,@Balance_Adj,@clacbalcal,@clsbal,@trans_settled_not_adj_gps,@trans_settled_not_adj_gps_2,@diffamt);
ELSE
UPDATE daily_balance_shift SET daily_balance_shift.opening_ac_bal = @opening_ac_bal1,daily_balance_shift.ATM_Settled = @ATM_Settled,daily_balance_shift.POS_Settled = @POS_Settled,daily_balance_shift.ATM_FEE = @ATM_FEE,daily_balance_shift.FPIN = @FPIN,daily_balance_shift.Bacs_IN = @BacsIN,daily_balance_shift.FP_out = @FP_out,daily_balance_shift.AB_DD = @AB_DD,daily_balance_shift.FP_out_fee = @FP_out_fee,daily_balance_shift.charge_backs = @charge_backs,daily_balance_shift.representments = @representments,daily_balance_shift.Other_fees = @Other_fees,daily_balance_shift.Load_Unload = @Load_Unload,daily_balance_shift.Balance_Adj = @Balance_Adj,daily_balance_shift.closing_ac_bal_calc = @clacbalcal,daily_balance_shift.closing_ac_bal_gps = @clsbal,daily_balance_shift.trans_settled_not_adj_gps = @trans_settled_not_adj_gps,daily_balance_shift.trans_settled_not_adj_gps_2 = @trans_settled_not_adj_gps_2,daily_balance_shift.diff = @diffamt WHERE daily_balance_shift.id = @insertedId;
END IF;
END LOOP;
CLOSE cur1;
END


最后的SELECT .. IF .. INSERT .. ELSE UPDATE闻起来很像单个查询INSERT ... ON DUPLICATE KEY ... UPDATE。尝试更改为此。

每个PROCEDUREs是否都可以获取值的表并一次对所有值进行操作，而不使用光标 ？ （光标很慢。）

然后将大cursor .. UNION更改为

CREATE TEMPORARY TABLE t (...)
SELECT .. UNION .. SELECT ....


然后将t赋予过程而不是单个@变量。

哦，闻起来像您的程序可能是函数？他们做了一些事情，并将@变量设置为OUT参数吗？

请向我们展示几个过程，以便我们对它们的工作有所了解。