求指导:一条SQL语句的调优问题

Question

本帖最后由 thomasks 于 2014-02-08 02:58:55 编辑

Answer 1

创建索引并将SQL98行date_format换成 AND cash.DUE_POST_DATE >=  '2014-01-01'
AND cash.DUE_POST_DATE < '2014-02-01'
执行计划变为(t_bcp_cash_fee_detail数据量不同,差一个数量级):PRIMARY <derived2> ALL 760 Using temporary; Using filesort
PRIMARY tpg ref I_POLICY_GENERAL__POLICY_NO,I_POLICY_GENERAL__PRODUCT_ID I_POLICY_GENERAL__POLICY_NO 87 pay.POLICY_NO 1 Using where
PRIMARY lm index PRIMARY,FK_CIRC_LEVEL1_MAP__LEVEL1_CODE FK_CIRC_LEVEL1_MAP__LEVEL1_CODE 8 8 Using index; Using join buffer
PRIMARY ca eq_ref PRIMARY PRIMARY 4 za_prod_tst.tpg.POLICY_SOURCE 1 Using where
PRIMARY ct1 eq_ref PRIMARY PRIMARY 8 za_prod_tst.lm.level1_code 1
PRIMARY pd eq_ref PRIMARY PRIMARY 8 za_prod_tst.tpg.PRODUCT_ID 1 Using where
DERIVED <derived3> ALL 757
DERIVED tcsi ref IDX_CLM_SETTLE_ITEM__SETTLE_ID,FK_CLM_SI__PAYMENT_STATUS,FK_CLM_SETTLE_ITEM__RESV_TYPE FK_CLM_SETTLE_ITEM__RESV_TYPE 182 4708 Using where
DERIVED tcs eq_ref PRIMARY,IDX_CLM_SETTLE__OBJECT_ID PRIMARY 8 za_prod_tst.tcsi.SETTLE_ID 1 Using where
DERIVED tco eq_ref PRIMARY,IDX_CLM_OBJECT__SEQ_NO,IDX_CLM_OBJECT__CASE_ID PRIMARY 8 za_prod_tst.tcs.OBJECT_ID 1
DERIVED tcc eq_ref PRIMARY PRIMARY 8 za_prod_tst.tco.CASE_ID 1
UNION cash ALL idx_bcp_cash_fee_detail_due_post_date 10842 Using where
UNION tbr eq_ref PRIMARY,IDX_BCP_RELATION__DEBIT_ID PRIMARY 8 za_prod_tst.cash.RELATION_ID 1
UNION fd eq_ref PRIMARY,IDX_BCP_FEE_DETAIL__TRANS_ID PRIMARY 8 za_prod_tst.tbr.DEBIT_ID 1
UNION fee eq_ref PRIMARY,IDX_BCP_FEE__SETTLEMENT_ID PRIMARY 8 za_prod_tst.fd.TRANS_ID 1
UNION tcsi ref IDX_CLM_SETTLE_ITEM__SETTLE_ID,FK_CLM_SI__PAYMENT_STATUS,FK_CLM_SETTLE_ITEM__RESV_TYPE IDX_CLM_SETTLE_ITEM__SETTLE_ID 8 za_prod_tst.fee.SETTLEMENT_ID 1 Using where
UNION tcs eq_ref PRIMARY,IDX_CLM_SETTLE__OBJECT_ID PRIMARY 8 za_prod_tst.tcsi.SETTLE_ID 1 Using where
UNION tco eq_ref PRIMARY,IDX_CLM_OBJECT__SEQ_NO,IDX_CLM_OBJECT__CASE_ID PRIMARY 8 za_prod_tst.tcs.OBJECT_ID 1
UNION tcc eq_ref PRIMARY PRIMARY 8 za_prod_tst.tco.CASE_ID 1
UNION RESULT <union2,4> ALL