sql查询耗时过长问题

问题:UAT及生产环境一个列表查询耗时大概70+s,测试环境由于数据量不够没有产生该问题,查看日志发现sql在分页count总数时耗时过长

分析:

1.索引缺失。

使用explain优化索引后,查询耗时为40s左右。没有彻底解决问题。

2.riskStateCause字段查询为from前子查询,逻辑过于复杂,列表查询在分页count总条数的时候每条数据都要执行该子查询,导致耗时过长。

将riskStateCause字段的查询从主体sql分离,在主sql得出查询结果后进行for循环查询riskStateCause。查询耗时为5s左右,缺点是无法根据riskStateCause字段进行排序了。

总结:

from语句之前的子查询尽量避免或以单表查询为主,否则随着数据量的增加会极大降低查询效率

参考链接:MySQL Explain详解

修改前sql如下:

SELECT
	count( 0 ) 
FROM
	(
	SELECT
		lr.BUSI_ORDER_ID,
		tm.MCOM_ID mcomId,
		tm.MCOM_FNAME mcomFname,
		ts.SCOM_ID scomId,
		ts.SCOM_FNAME scomFname,
		tb.REG_ID brokerId,
		tb.BROKER_CODE brokerCode,
		tb.`NAME` brokerName,
		tc.`NAME` holderName,
		lr.tenant_id tenantId,
		tr.SUPPLIER_ID supplierId,
		supplyier.SUPPLIER_NAME supplierName,
		tl.POLICY_CODE policyCode,
		pay.POLICY_YEAR AS policyYear,
		tp.PROD_CODE prodCode,
		tp.PROD_NAME prodName,
		lr.COVERAGE_PERIOD AS coveragePeriod,
		lr.COVERAGE_PERIOD_VALUE coveragePeriodValue,
		lr.CHARGE_INTERVAL AS chargeInterval,
		lr.CHARGE_PERIOD AS chargePeriod,
		lr.CHARGE_PERIOD_VALUE chargePeriodValue,
		pay.PAY_NUM payNum,
		lr.RISK_STATE AS riskState,
		lr.RISK_STATE AS riskStateTitle,
		tor.COMM_AMT commAmt,
		tor.COMM_RATE commRate,
	CASE
			
			WHEN pay.DUE_TIME IS NOT NULL 
			OR tor.INSERT_TIME IS NOT NULL THEN
				(
				SELECT
				CASE
						
					WHEN
						TO_DAYS( t2.DUE_TIME ) = TO_DAYS( pay.DUE_TIME ) THEN
							em.EDOR_TYPE 
						END 
						FROM
							t_edor_main em
							INNER JOIN t_order_lrisk_log t1 ON em.CHANGE_ID = t1.CHANGE_ID
							INNER JOIN t_order_risk_pay_due_date t2 ON t1.RISK_ID = t2.RISK_ID 
							AND em.TENANT_ID = t1.TENANT_ID 
							AND t1.CHANGED_INDI = 'Y' 
						WHERE
							em.BUSI_ORDER_ID = lr.BUSI_ORDER_ID 
							AND lr.RISK_ID = t1.RISK_ID 
							AND em.STATUS = '1003' 
							AND em.INSERT_TIME < DATE_ADD(
							CASE
									
									WHEN t2.DUE_TIME IS NULL THEN
									tor.INSERT_TIME 
									WHEN tor.INSERT_TIME IS NULL THEN
									t2.DUE_TIME ELSE
								IF
									(
										DATE_FORMAT( t2.DUE_TIME, '%Y-%m-%d' ) > DATE_FORMAT( tor.INSERT_TIME, '%Y-%m-%d' ),
										DATE_FORMAT( t2.DUE_TIME, '%Y-%m-%d' ),
										DATE_FORMAT( tor.INSERT_TIME, '%Y-%m-%d' ) 
									) 
								END,
								INTERVAL 1 DAY 
							) 
						ORDER BY
							em.INSERT_TIME DESC,
							t2.DUE_TIME ASC 
							LIMIT 1 
						) 
					END AS riskStateCause,
					lr.CHARGE_STATE AS chargeState,
					lr.END_DATE endDate,
					lr.VALIDATE_DATE validateDate,
					pay.DUE_TIME payDueDate,
					lr.NEXT_PREM payDuePrem,
					IFNULL( tor.PREM, lr.NEXT_PREM ) duePrem,
				CASE
						tor.RENEWAL_STATUS 
						WHEN 2 THEN
						tor.PREM 
						WHEN 3 THEN
						tor.PREM 
					END paidPrem,
	tor.PAID_DATE paidDate,
	tor.PAY_MODE payMode,
	fun_getTenantDictValue ( 100013, 'CD105', tor.PAY_MODE ) payModeTitle,
	IFNULL( tor.RENEWAL_STATUS, '1' ) renewalStatus,
	tor.RENEWAL_STATUS renewalStatusTitle 
FROM
	t_order_lrisk lr
	INNER JOIN t_order_risk_pay_due_date pay ON lr.RISK_ID = pay.RISK_ID 
	AND lr.BUSI_ORDER_ID = pay.BUSI_ORDER_ID
	INNER JOIN t_order tr ON tr.BUSI_ORDER_ID = lr.BUSI_ORDER_ID 
	AND tr.MCOM_ID IN ( 100000, 100330, 100331, 2018112312575720686 ) 
	AND tr.MCOM_ID IN ( 100000, 100330, 100331, 2018112312575720686 )
	INNER JOIN t_order_life tl ON tl.BUSI_ORDER_ID = lr.BUSI_ORDER_ID
	INNER JOIN tb_manage_com tm ON tr.MCOM_ID = tm.MCOM_ID
	INNER JOIN t_sale_com ts ON tr.SCOM_ID = ts.SCOM_ID
	INNER JOIN t_broker tb ON tb.REG_ID = tr.SERVICE_BROKER_ID
	INNER JOIN t_tenant_product tp ON tp.PROD_ID = lr.PDT_ID
	INNER JOIN T_TENANT_SUPPLIER supplyier ON tr.SUPPLIER_ID = supplyier.SUPPLIER_ID
	INNER JOIN T_ORDER_HOLDER th ON th.BUSI_ORDER_ID = lr.BUSI_ORDER_ID
	INNER JOIN T_CUSTOMER tc ON tc.CUSTOMER_ID = th.CUSTOMER_ID
	LEFT JOIN t_order_renewal tor ON tor.BUSI_ORDER_ID = lr.BUSI_ORDER_ID 
	AND tor.DUE_TIME = pay.DUE_TIME 
	AND tor.PAY_NUM = pay.PAY_NUM 
	AND tor.RISK_ID = lr.RISK_ID 
	AND tor.RENEWAL_STATUS <> '4' 
WHERE
	( lr.RISK_STATE IN ( '1', '4' ) OR ( lr.RISK_STATE = '2' AND lr.RISK_STATE_CAUSE <> '03' ) ) 
	AND ( lr.CHARGE_PERIOD_VALUE > 1 OR ( lr.CHARGE_PERIOD_VALUE = 1 AND lr.COVERAGE_PERIOD_VALUE = 1 AND lr.AUTO_RENEW = '2' ) ) 
	AND lr.TENANT_ID = 100013 
ORDER BY
	mcomId 
	) tmp_count

 

修改后代码:

SELECT
	count( 0 ) 
FROM
	(
	SELECT
		lr.BUSI_ORDER_ID,
		tm.MCOM_ID mcomId,
		tm.MCOM_FNAME mcomFname,
		ts.SCOM_ID scomId,
		ts.SCOM_FNAME scomFname,
		tb.REG_ID brokerId,
		tb.BROKER_CODE brokerCode,
		tb.`NAME` brokerName,
		tc.`NAME` holderName,
		lr.tenant_id tenantId,
		tr.SUPPLIER_ID supplierId,
		supplyier.SUPPLIER_NAME supplierName,
		tl.POLICY_CODE policyCode,
		pay.POLICY_YEAR AS policyYear,
		tp.PROD_CODE prodCode,
		tp.PROD_NAME prodName,
		lr.COVERAGE_PERIOD AS coveragePeriod,
		lr.COVERAGE_PERIOD_VALUE coveragePeriodValue,
		lr.CHARGE_INTERVAL AS chargeInterval,
		lr.CHARGE_PERIOD AS chargePeriod,
		lr.CHARGE_PERIOD_VALUE chargePeriodValue,
		pay.PAY_NUM payNum,
		lr.RISK_STATE AS riskState,
		lr.RISK_STATE AS riskStateTitle,
		tor.COMM_AMT commAmt,
		tor.COMM_RATE commRate,
		lr.CHARGE_STATE AS chargeState,
		lr.END_DATE endDate,
		lr.VALIDATE_DATE validateDate,
		pay.DUE_TIME payDueDate,
		lr.NEXT_PREM payDuePrem,
		IFNULL( tor.PREM, lr.NEXT_PREM ) duePrem,
				CASE
						tor.RENEWAL_STATUS 
						WHEN 2 THEN
						tor.PREM 
						WHEN 3 THEN
						tor.PREM 
					END paidPrem,
	tor.PAID_DATE paidDate,
	tor.PAY_MODE payMode,
	fun_getTenantDictValue ( 100013, 'CD105', tor.PAY_MODE ) payModeTitle,
	IFNULL( tor.RENEWAL_STATUS, '1' ) renewalStatus,
	tor.RENEWAL_STATUS renewalStatusTitle 
FROM
	t_order_lrisk lr
	INNER JOIN t_order_risk_pay_due_date pay ON lr.RISK_ID = pay.RISK_ID 
	AND lr.BUSI_ORDER_ID = pay.BUSI_ORDER_ID
	INNER JOIN t_order tr ON tr.BUSI_ORDER_ID = lr.BUSI_ORDER_ID 
	AND tr.MCOM_ID IN ( 100000, 100330, 100331, 2018112312575720686 ) 
	AND tr.MCOM_ID IN ( 100000, 100330, 100331, 2018112312575720686 )
	INNER JOIN t_order_life tl ON tl.BUSI_ORDER_ID = lr.BUSI_ORDER_ID
	INNER JOIN tb_manage_com tm ON tr.MCOM_ID = tm.MCOM_ID
	INNER JOIN t_sale_com ts ON tr.SCOM_ID = ts.SCOM_ID
	INNER JOIN t_broker tb ON tb.REG_ID = tr.SERVICE_BROKER_ID
	INNER JOIN t_tenant_product tp ON tp.PROD_ID = lr.PDT_ID
	INNER JOIN T_TENANT_SUPPLIER supplyier ON tr.SUPPLIER_ID = supplyier.SUPPLIER_ID
	INNER JOIN T_ORDER_HOLDER th ON th.BUSI_ORDER_ID = lr.BUSI_ORDER_ID
	INNER JOIN T_CUSTOMER tc ON tc.CUSTOMER_ID = th.CUSTOMER_ID
	LEFT JOIN t_order_renewal tor ON tor.BUSI_ORDER_ID = lr.BUSI_ORDER_ID 
	AND tor.DUE_TIME = pay.DUE_TIME 
	AND tor.PAY_NUM = pay.PAY_NUM 
	AND tor.RISK_ID = lr.RISK_ID 
	AND tor.RENEWAL_STATUS <> '4' 
WHERE
	( lr.RISK_STATE IN ( '1', '4' ) OR ( lr.RISK_STATE = '2' AND lr.RISK_STATE_CAUSE <> '03' ) ) 
	AND ( lr.CHARGE_PERIOD_VALUE > 1 OR ( lr.CHARGE_PERIOD_VALUE = 1 AND lr.COVERAGE_PERIOD_VALUE = 1 AND lr.AUTO_RENEW = '2' ) ) 
	AND lr.TENANT_ID = 100013 
ORDER BY
	mcomId 
	) tmp_count
List<ExtTOrderLriskDetailRepVO> list = extTOrderRenewalMapper.orderList(reqVO);
		// 查询原因
for (ExtTOrderLriskDetailRepVO extTOrderLriskDetailRepVO : list) {
	if (extTOrderLriskDetailRepVO.getPayDueDate() != null || extTOrderLriskDetailRepVO.gettOrderRenewalInsertTime() != null) {
	    String riskStateCause = extTOrderRenewalMapper.selectRiskStateCauseBy(extTOrderLriskDetailRepVO.getRiskId(), extTOrderLriskDetailRepVO.getBusiOrderId(),
						extTOrderLriskDetailRepVO.getPayDueDate(),
						extTOrderLriskDetailRepVO.gettOrderRenewalInsertTime());
		extTOrderLriskDetailRepVO.setRiskStateCause(riskStateCause);
	}
}
SELECT
				CASE
					WHEN
						TO_DAYS( t2.DUE_TIME ) = TO_DAYS( #{payDueDate} )
					THEN
						em.EDOR_TYPE 
				END riskStateCause
			FROM
				t_edor_main em
				INNER JOIN t_order_lrisk_log t1 ON em.CHANGE_ID = t1.CHANGE_ID
				INNER JOIN t_order_risk_pay_due_date t2 ON t1.RISK_ID = t2.RISK_ID
				AND t1.BUSI_ORDER_ID = t2.BUSI_ORDER_ID
				AND em.TENANT_ID = t1.TENANT_ID
				AND t1.CHANGED_INDI = 'Y'
			WHERE
				em.BUSI_ORDER_ID = #{busiOrderId}    
				AND  t1.RISK_ID = #{riskId}
				AND em.STATUS = '1003'
				AND em.INSERT_TIME <![CDATA[<]]>  DATE_ADD(
				CASE
					WHEN
						t2.DUE_TIME IS NULL
					THEN
						#{tOrderRenewalInsertTime}
					WHEN
						#{tOrderRenewalInsertTime} IS NULL
					THEN
						t2.DUE_TIME ELSE
					IF(
							DATE_FORMAT( t2.DUE_TIME, '%Y-%m-%d' ) <![CDATA[>]]> DATE_FORMAT(#{tOrderRenewalInsertTime}, '%Y-%m-%d' ),
							DATE_FORMAT( t2.DUE_TIME, '%Y-%m-%d' ),
							DATE_FORMAT( #{tOrderRenewalInsertTime}, '%Y-%m-%d' )
					)
				END,
				INTERVAL 1 DAY )
			ORDER BY
				em.INSERT_TIME DESC ,t2.DUE_TIME asc
			LIMIT 1  ;

 

发布了11 篇原创文章 · 获赞 1 · 访问量 1万+
展开阅读全文

SQL SERVER 上的一个简单查询,执行时间过长

09-25

最近在生产环境中发现一条查询脚本耗时很长,排查了一天没发现问题在哪,请大神帮帮忙! . **表结构说明:** * SPYCDMX.MXBH、SPYCD.DJBH、YSCYD.DJBH、CANGKU.CKDM 是主键; * SPYCD.YS、SPYCD.YSRQ、CANGKU.XZDM、SPYCDMX.DJBH、SPYCDMX.dc_sync_flag、SPYCDMX.dc_sync_guid 是非聚集索引; * SPYCDMX.dc_sync_guid 字段默认值是 NULL; * SPYCDMX 表有400多万行数据; . > **异常查询** ``` SELECT m.DJBH, mx.MXBH, mx.dc_sync_flag, mx.dc_sync_guid, mx.dc_sync_time FROM SPYCDMX mx INNER JOIN SPYCD m ON m.DJBH = mx.DJBH LEFT JOIN CANGKU ck ON ck.CKDM = m.DM1 LEFT JOIN YSCYD cy ON cy.DJBH = m.YDJH WHERE 1 = 1 AND m.YS = '1' AND m.YSRQ >= '2019-08-16' AND ck.XZDM = '1' AND cy.DJBH IS NOT NULL AND mx.dc_sync_flag = 'N' AND mx.dc_sync_guid IS NULL ``` 这段代码的执行时间长达3~5分钟! . > **尝试1** ``` SELECT m.DJBH, mx.MXBH, mx.dc_sync_flag, mx.dc_sync_guid FROM SPYCDMX mx INNER JOIN SPYCD m ON m.DJBH = mx.DJBH LEFT JOIN CANGKU ck ON ck.CKDM = m.DM1 LEFT JOIN YSCYD cy ON cy.DJBH = m.YDJH WHERE 1 = 1 AND m.YS = '1' AND m.YSRQ >= '2019-08-16' AND ck.XZDM = '1' AND cy.DJBH IS NOT NULL AND mx.dc_sync_flag = 'N' AND mx.dc_sync_guid IS NULL ``` 在 SELECT 字段中剔除 mx.dc_sync_time 列,执行时间缩减到十几秒! . > **尝试2** ``` SELECT m.DJBH, mx.MXBH, mx.dc_sync_flag, mx.dc_sync_guid FROM SPYCDMX mx INNER JOIN SPYCD m ON m.DJBH = mx.DJBH LEFT JOIN CANGKU ck ON ck.CKDM = m.DM1 LEFT JOIN YSCYD cy ON cy.DJBH = m.YDJH WHERE 1 = 1 AND m.YS = '1' AND m.YSRQ >= '2019-08-16' AND ck.XZDM = '1' AND cy.DJBH IS NOT NULL AND mx.dc_sync_flag = 'N' AND ISNULL(mx.dc_sync_guid, '') = '' ``` 把 WHERE 条件中的 AND mx.dc_sync_guid IS NULL 改为 AND ISNULL(mx.dc_sync_guid, '') = '',执行时间缩减到1~2秒! . 这是什么原因? 问答

sql执行时间过长,求帮忙看看,能不能优化

06-09

( SELECT b.itemcode, b.directionidx, c.printname, a.closedate, a.serialno, a.itemtype, a.branchcode, a.centercode, a.yearmonth, a.accbooktype, a.accbookcode, a.closedate, a.accountcode, a.checktype, a.checkno, a.currency, a.itemcode, a.directionidx, b.remark, b.debitsource, b.creditsource, b.debitdest, b.creditdest, a.balancesource, a.balancedest, a.operatecode, a.operatebranch, a.operatedate, a.approvecode, a.approvebranch, a.approvedate, a.checkflag, a.checkyearmonth, a.checknum, a.dailytype, a.voucherno, a.receflag, a.detailidx, a.flag FROM AccDailyRec AS a, accsubvoucherhis AS b, ( ( SELECT printname, itemcode, directionidx, centercode FROM accitemvalidate ) UNION ALL ( SELECT itemname AS printname, itemcode, '00' AS directionidx, centercode FROM accitemdefine ) ) c WHERE a.centercode = b.centercode AND a.yearmonth = b.yearmonth AND a.voucherno = b.voucherno AND c.itemcode = b.itemcode AND c.directionidx = b.directionidx AND c.centercode = b.centercode AND b.itemcode <> '1001' AND EXISTS ( SELECT 1 FROM accmainvoucherhis er WHERE er.centercode = a.centercode AND er.yearmonth = a.yearmonth AND er.voucherno = a.voucherno AND er.voucherflag = '4' ) AND a.AccBookType = '02' AND a.CenterCode = '1000000000' AND a.AccBookCode = '11' AND a.BranchCode = '1000000000' AND a.ItemType = '0' AND a.AccountCode = 'ZZZ' AND 1 = 1 AND a.CloseDate >= '2017-03-01' AND a.CloseDate <= '2017-03-31' ORDER BY voucherdate, voucherno ) UNION ALL ( SELECT b.itemcode, b.directionidx, c.printname, a.closedate, a.serialno, a.itemtype, a.branchcode, a.centercode, a.yearmonth, a.accbooktype, a.accbookcode, a.closedate, a.accountcode, a.checktype, a.checkno, a.currency, a.itemcode, a.directionidx, b.remark, b.debitsource, b.creditsource, b.debitdest, b.creditdest, a.balancesource, a.balancedest, a.operatecode, a.operatebranch, a.operatedate, a.approvecode, a.approvebranch, a.approvedate, a.checkflag, a.checkyearmonth, a.checknum, a.dailytype, a.voucherno, a.receflag, a.detailidx, a.flag FROM AccDailyRec AS a, accsubvoucher AS b, ( ( SELECT printname, itemcode, directionidx, centercode FROM accitemvalidate ) UNION ALL ( SELECT itemname AS printname, itemcode, '00' AS directionidx, centercode FROM accitemdefine ) ) c WHERE a.centercode = b.centercode AND a.yearmonth = b.yearmonth AND a.voucherno = b.voucherno AND c.itemcode = b.itemcode AND c.directionidx = b.directionidx AND c.centercode = b.centercode AND b.itemcode <> '1001' AND EXISTS ( SELECT 1 FROM accmainvoucher er WHERE er.centercode = a.centercode AND er.yearmonth = a.yearmonth AND er.voucherno = a.voucherno AND er.voucherflag <> '4' ) AND a.AccBookType = '02' AND a.CenterCode = '1000000000' AND a.AccBookCode = '11' AND a.BranchCode = '1000000000' AND a.ItemType = '0' AND a.AccountCode = 'ZZZ' AND 1 = 1 AND a.CloseDate >= '2017-03-01' AND a.CloseDate <= '2017-03-31' GROUP BY b.voucherno, b.itemcode ORDER BY voucherdate, voucherno ) 问答

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览