sql查询耗时过长问题

最新推荐文章于 2025-05-29 11:19:15 发布

原创最新推荐文章于 2025-05-29 11:19:15 发布 · 2.7k 阅读

2 ·

CC 4.0 BY-SA版权

本文针对UAT及生产环境中一个列表查询耗时过长的问题进行了深入分析。通过优化索引和重构SQL逻辑，尤其是解决了复杂的子查询导致的效率低下，将查询时间从70秒缩短到5秒，提供了一种有效的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题：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  ;