MySQL存储过程函数优化步骤
1.存储过程/函数内部的SQL语句优化 例如：INSERT INTO tt_express_invoice (
EXPRESS_INVOICE_CODE,
............
(SELECT DISTINCT
e.express_invoice_code,
e.bill_code,
e.cust_code,
e.cust_dept,
e.vip_code,
e.vip_dept,
e.start_dt,
e.end_dt,
e.gl_dt,
e.bill_start_dt,
e.bill_end_dt,
e.BILLING_VERSION_NO,
e.bill_period,
e.bill_type,
e.BILL_JOB_BATCH_NO,
e.bill_batch_no
FROM
tt_express_invoice e
WHERE e.`invoice_batch_no` = v_invoice_batch_no
AND NOT EXISTS
(SELECT
1
FROM
tt_express_invoice t
WHERE t.`cust_code` = e.`cust_code`
AND t.`cust_dept` = e.`cust_dept`
AND t.`bill_start_dt` = e.`bill_start_dt`
AND t.`bill_end_dt` = e.`bill_end_dt`
AND t.`invoice_batch_no` = v_invoice_batch_no
AND t.`FEE_TYPE_CODE` = V_EXPRESS_SPECIAL_ZK)) e,
tt_special_rebate s
WHERE e.`cust_code` = s.`customer_code`
AND e.`cust_dept` = s.`customer_dept`
AND e.`bill_start_dt` = s.`start_dt`
AND e.`bill_end_dt` = s.`end_dt`
AND s.`billing_flag` = 1
AND s.`rebate_type` = 1
GROUP BY e.express_invoice_code,
e.bill_code,
e.cust_code,
e.cust_dept,
e.vip_code,
e.vip_dept,
e.start_dt,
e.end_dt,
e.bill_start_dt,
e.bill_end_dt,
e.billing_version_no,
e.bill_period,
e.bill_type,
e.gl_dt,
bill_job_batch_no ;
需要优化GROUP BY分组条件，去掉不需要的分组条件。将
group by子句 优化为
GROUP BY e.express_invoice_code ;
页:
[1]