mysql - 通过删除用户定义的函数来提高性能

Question

我编写了一个查询来使用用户定义的函数计算余额金额。但需要 58 秒才能产生结果。如果我删除该函数，它会在 2 秒内运行。下面的函数可以重写吗？

select ldt.ldt_line_item_id as id,
fn_get_int_bal(ldt.ldt_line_item_id,'LINEITEM')  as Balance, 
from  ldt_tran_loan_det ldt 
join lmt_mst_loan on ldt.ldt_loan_ref_id=lmt.lmt_loan_id
join uft_map_user_clnt uft on uft.uft_clnt_id=ldt.ldt_clnt_id 
where uft.uft_clnt_id  in ('87')
and  uft.uft_user_id  in (10009)
and  date_format(ldt.ldt_entry_date,'%Y/%m/%d')  between '2009/01/01' and '2009/12/31'

功能:

CREATE  FUNCTION fn_get_int_bal(p_line_item_id INT) RETURNS decimal(20,2)
BEGIN
  DECLARE v_int_cumm   DECIMAL(20, 2) DEFAULT 0;
  DECLARE v_int_bal    DECIMAL(20, 2) DEFAULT 0;
  DECLARE v_return     DECIMAL(20, 2) DEFAULT 0;
  THEN
      SET v_int_bal =
            (SELECT ifnull(lia.lia_int_amount,0)
            FROM lia_loan_int_accrual lia
            WHERE lia.lia_line_item_id=p_line_item_id);

     SET v_int_cumm= (SELECT ifnull(sum(t.glt_txn_amt_dr) - sum(t.glt_txn_amt_cr),
                           0)
               FROM glt_tran_ledg_post t
              WHERE     glt_line_item_id = p_line_item_id
                    AND glt_acnt_code = 1001); 

     SET v_return=ifnull(v_int_bal,0)+ifnull(v_int_cumm,0);
 RETURN v_return ;
 END

表定义:

CREATE TABLE ldt_tran_loan_det (ldt_line_item_id BIGINT(20),ldt_loan_ref_id INT(11),ldt_clnt_id INT(11),ldt_entry_date DATETIME ,ldt_user_id INT(11),
PRIMARY KEY (ldt_line_item_id),INDEX ldt_loan_ref_id (ldt_loan_ref_id),INDEX ldt_clnt_id (ldt_clnt_id), INDEX ldt_line_item_id (ldt_line_item_id));

CREATE TABLE lmt_mst_loan (lmt_loan_id INT(11),lmt_clnt_id INT(11),lmt_entry_date DATETIME,lmt_guar_id  INT(11),lmt_mps_sch_id INT(11),
PRIMARY KEY (lmt_loan_id),INDEX idx_lmt_guar_id (lmt_loan_id, lmt_guar_id))

CREATE TABLE uft_map_user_clnt (id INT(11) ,uft_user_id INT(11) ,uft_clnt_id INT(11) ,PRIMARY KEY (id),INDEX uft_clnt_id1 (uft_clnt_id, uft_user_id));

CREATE TABLE glt_tran_ledg_post (glt_uniq_txn_id BIGINT(20) ,glt_loan_ref_id INT(11),glt_line_item_id BIGINT(20) ,glt_txn_amt_dr DECIMAL(20,2),
glt_txn_amt_cr DECIMAL(20,2),PRIMARY KEY (glt_uniq_txn_id),INDEX glt_guar_id (glt_guar_id),INDEX glt_line_item_id (glt_line_item_id),INDEX glt_loan_ref_id (glt_loan_ref_id));

CREATE TABLE lia_loan_int_accrual (lia_loan_ref_id INT(11),lia_line_item_id BIGINT(20),lia_int_amount DECIMAL(20,2),INDEX lia_loan_ref_id (lia_loan_ref_id),INDEX lia_line_item_id (lia_line_item_id));

执行计划

Answer 1

可能会切换到代码中的联接。

类似这样的东西(未测试):-

SELECT ldt.ldt_line_item_id as id,
        IFNULL(lia.lia_int_amount, 0) + IFNULL(v_int_cumm, 0) as Balance, 
FROM  ldt_tran_loan_det ldt 
JOIN lmt_mst_loan on ldt.ldt_loan_ref_id=lmt.lmt_loan_id
JOIN uft_map_user_clnt uft on uft.uft_clnt_id=ldt.ldt_clnt_id 
LEFT OUTER JOIN lia_loan_int_accrual lia
ON ldt.ldt_line_item_id = lia.p_line_item_id
LEFT OUTER JOIN
(
    SELECT p_line_item_id, IFNULL(SUM(t.glt_txn_amt_dr) - SUM(t.glt_txn_amt_cr),0) AS v_int_cumm
    FROM glt_tran_ledg_post t
    WHERE  glt_acnt_code = 1001
    GROUP BY p_line_item_id
) Sub2
ON ldt.ldt_line_item_id = Sub2.p_line_item_id
WHERE uft.uft_clnt_id  in ('87')
AND  uft.uft_user_id  in (10009)
AND  date_format(ldt.ldt_entry_date,'%Y/%m/%d')  BETWEEN '2009/01/01' AND '2009/12/31'

稍加尝试，也许也可以消除子查询