问题现象

• 问题号：UCMFG-284226

• 核心3分库5，SQL执行耗时3秒多

SELECT count(1) AS totalCount
FROM (
	SELECT DISTINCT T0.qastnum AS `qastnum`, T0.inspectDate AS `inspectDate`, T0.pk_org AS `pk_org`, NULL AS `currentAuditor`, NULL AS `pk_materialsku`
		, NULL AS `pk_materialsku_code`, NULL AS `pk_materialsku_name`, NULL AS `dr`, T0.pk_batchcode AS `pk_batchcode`, T0.inspectnum AS `inspectnum`
		, T0.trantype AS `trantype`, T0.qnum AS `qnum`, T0.verifystate AS `verifystate`, T0.code AS `code`, T0.check_type AS `billtype`
		, T0.pk_material AS `pk_material`, T0.inspectastnum AS `inspectastnum`, T0.freect_h AS `freect_h`, T0.initinspectorder_id AS `initinspectorder_id`, T0.manufacture_date AS `manufacture_date`
		, T0.initinspectorder_code AS `initinspectorder_code`, T0.warranty_date AS `warranty_date`, T0.warranty_unit AS `warranty_unit`, T0.validityDate AS `validityDate`, T0.vchangerate AS `vchangerate`
		, T0.inspectionplanSingle AS `inspectionplanSingle`, T0.spotchekastnum AS `spotchekastnum`, T0.castunitid AS `castunitid`, T0.spotcheknum AS `spotcheknum`, T0.inspectRule AS `inspectRule`
		, T0.pk_sample AS `pk_sample`, T0.pk_test AS `pk_test`, T0.applycheckbillType AS `applycheckbillType`, T0.samplemethod_detail_id AS `sampleMethodDetailId`, T0.vapplycheckcode AS `vapplycheckcode`
		, T0.pk_inspecter AS `pk_inspecter`, T0.pk_inspectdept AS `pk_inspectdept`, T0.applycheckDate AS `applycheckDate`, T0.pk_inspectionplan AS `pk_inspectionplan`, T0.combinecheck AS `combinecheck`
		, T0.sourcebilltype AS `sourcebilltype`, T0.vsourcecode AS `vsourcecode`, T0.sourcerowno AS `sourcerowno`, T0.pk_prod_dept AS `pk_prod_dept`, T0.orderbilltype AS `orderbilltype`
		, T0.vordercode AS `vordercode`, T0.vorderrowno AS `vorderrowno`, T0.pk_busiperiod AS `pk_busiperiod`, T0.busistartDate AS `busistartDate`, T0.busiendDate AS `busiendDate`
		, T0.attachmentId AS `attachmentId`, T0.cunitid AS `cunitid`, T0.source_order_code AS `sourceOrderCode`, T0.source_order_line_no AS `sourceOrderLineNo`, T0.source_order_type AS `sourceOrderType`
		, T0.inspectionplanVersion AS `inspectionplanVersion`, T0.note AS `note`, T0.pk_applycheckorg AS `pk_applycheckorg`, T0.recheck AS `recheck`, T0.inspectResult AS `inspectResult`
		, T0.nqastnum AS `nqastnum`, T0.nqnum AS `nqnum`, T0.pk_applycheckdept AS `pk_applycheckdept`, T0.qrate AS `qrate`, T0.samnum AS `samnum`
		, T0.unsamnum AS `unsamnum`, T0.chastnum AS `chastnum`, T0.version AS `version`, T0.chnum AS `chnum`, T0.chrate AS `chrate`
		, T0.creator AS `creator`, T0.create_time AS `createTime`, T0.project_id AS `projectId`, T0.wbs AS `wbs`, T0.modifier AS `modifier`
		, T0.activity AS `activity`, T0.modify_time AS `modifyTime`, T0.auditor AS `auditor`, T0.audit_time AS `auditTime`, T0.terminate AS `terminate`
		, T0.currentStage AS `currentStage`, T0.source_order_id AS `sourceOrderId`, T0.pk_applycheck AS `pk_applycheck`, T0.pk_orderbill AS `pk_orderbill`, T0.pk_sourcebill AS `pk_sourcebill`
		, T0.pk_sourcebill_b AS `pk_sourcebill_b`, T0.inspectionplanMethod AS `inspectionplanMethod`, T0.unitExchangeType AS `unitExchangeType`, T0.define1 AS `define1`, T0.define2 AS `define2`
		, T0.define3 AS `define3`, T0.define4 AS `define4`, T0.define5 AS `define5`, T0.define6 AS `define6`, T0.define7 AS `define7`
		, T0.define8 AS `define8`, T0.define9 AS `define9`, T0.define10 AS `define10`, T0.define11 AS `define11`, T0.define12 AS `define12`
		, T0.define13 AS `define13`, T0.define14 AS `define14`, T0.define15 AS `define15`, T0.define16 AS `define16`, T0.define17 AS `define17`
		, T0.define18 AS `define18`, T0.define19 AS `define19`, T0.define20 AS `define20`, T0.define21 AS `define21`, T0.define22 AS `define22`
		, T0.define23 AS `define23`, T0.define24 AS `define24`, T0.define25 AS `define25`, T0.define26 AS `define26`, T0.define27 AS `define27`
		, T0.define28 AS `define28`, T0.define29 AS `define29`, T0.define30 AS `define30`, T0.producedate AS `producedate`, T0.invaliddate AS `invaliddate`
		, T0.firstStoredDate AS `firstStoredDate`, T0.id AS `id`, T0.pubts AS `pubts`, T0.tenant_id AS `tenant`, T0.isWfControlled AS `isWfControlled`
		, T0.returncount AS `returncount`, T0.terminal_person AS `terminalPerson`, T0.terminal_time AS `terminalTime`, T0.definect_h AS `definect_h`, T8.vcol2 AS `definect_h__YBLH`
		, T8.ncol2 AS `definect_h__WGZL`, T8.vcol16 AS `definect_h__BZ`, T8.ncol1 AS `definect_h__WGJJ`, T8.vcol1 AS `definect_h__JTQK`, T8.vcol5 AS `definect_h__chengpinhoudu`
		, T8.vcol14 AS `definect_h__fk`, T8.vcol10 AS `definect_h__hanshui`, T8.vcol7 AS `definect_h__hxqd`, T8.vcol6 AS `definect_h__hxysl`, T8.vcol13 AS `definect_h__jpjkz`
		, T8.vcol4 AS `definect_h__qfl`, T8.vcol8 AS `definect_h__qfysjf`, T8.vcol3 AS `definect_h__zqysjf`, T8.vcol11 AS `definect_h__zsysjf`, T8.vcol9 AS `definect_h__zxqd`
		, T8.vcol12 AS `definect_h__zxysl`, T8.vcol15 AS `definect_h__YB011`, T8.id AS `definect_h__id`, T20.inspectResult AS `resultListInspectResult`, T20.nnum AS `nnum`
		, T20.freect_b AS `freect_b`, T20.pk_qualify_grade AS `pk_qualify_grade`, T20.nastnum AS `nastnum`, T20.is_storage AS `is_storage`, T20.baditemFlag AS `baditemFlag`
		, T20.quality_grade AS `qualityGrade`, T20.vbnote AS `vbnote`, T20.handleType AS `handleType`, T20.id AS `result_id`, T20.pk_material_h AS `pk_material_h`
		, T20.push_rejectbill_num AS `pushRejectbillNum`, T20.pk_stockstatus AS `pk_stockstatus`, T20.cbunitid AS `cbunitid`, T20.cbastunitid AS `cbastunitid`, T20.define1 AS `define1_r`
		, T20.define2 AS `define2_r`, T20.define3 AS `define3_r`, T20.define4 AS `define4_r`, T20.define5 AS `define5_r`, T20.define6 AS `define6_r`
		, T20.define7 AS `define7_r`, T20.define8 AS `define8_r`, T20.define9 AS `define9_r`, T20.define10 AS `define10_r`, T20.define11 AS `define11_r`
		, T20.define12 AS `define12_r`, T20.define13 AS `define13_r`, T20.define14 AS `define14_r`, T20.define15 AS `define15_r`, T20.define16 AS `define16_r`
		, T20.define17 AS `define17_r`, T20.define18 AS `define18_r`, T20.define19 AS `define19_r`, T20.define20 AS `define20_r`, T20.define21 AS `define21_r`
		, T20.define22 AS `define22_r`, T20.define23 AS `define23_r`, T20.define24 AS `define24_r`, T20.define25 AS `define25_r`, T20.define26 AS `define26_r`
		, T20.define27 AS `define27_r`, T20.define28 AS `define28_r`, T20.define29 AS `define29_r`, T20.define30 AS `define30_r`, T20.definect_r AS `definect_r`
		, T26.is_generate_barcode AS `qms_qit_incominspectorder_h_extList!isGenerateBarcode`, T26.id AS `qms_qit_incominspectorder_h_extList!id`, T26.baditemFlag AS `qms_qit_incominspectorder_h_extList!baditemFlag`, T26.first_check AS `qms_qit_incominspectorder_h_extList!firstCheck`, T26.inspectconbatch_id AS `qms_qit_incominspectorder_h_extList!inspectConBatchId`
		, T0.checkPlanDetailId AS `checkPlanDetailId`
	FROM qms_qit.qms_qit_incominspectorder_h T0
		LEFT JOIN qms_qit.qms_qit_incom_result T20
		ON T20.pk_inspectorder_h = T0.id
			AND T20.ytenant_id = 'p3d4zaqu'
			AND T20.dr = 0
		LEFT JOIN qms_qit.qms_qit_incominspectorder_h_define_1 T8 ON T8.id = T0.definect_h
		LEFT JOIN qms_qit.qms_qit_incominspectorder_h_ext T26
		ON T26.id = T0.id
			AND T26.ytenant_id = 'p3d4zaqu'
			AND T26.dr = 0
	WHERE T0.pk_prod_dept IN ('2152784325897093129')
		AND (T0.check_type IN ('002', '011')
			AND T0.inspect_result_source = 0
			AND T0.dr = 0)
		AND T0.pk_org IN ('2152063020481118244')
		AND T0.ytenant_id = 'p3d4zaqu'
		AND T0.dr = 0
) t;

问题原因

• 执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	derived2		ALL					2241	100.0
2	DERIVED	T0		index_merge	idx_checktype_dr,i_pk_org,i_irs_yten_dr	i_irs_yten_dr,i_pk_org	152,147		44495	5.04	Using intersect(i_irs_yten_dr,i_pk_org); Using where; Using temporary
2	DERIVED	T20		ref	IX_pk_inspectorder_h,idx_ytenant_id,idx_pk_inspectorder_tenant,idx_pk_inspectorder_ytenant	idx_pk_inspectorder_tenant	146	qms_qit.T0.id	1	100.0	Using where
2	DERIVED	T8		eq_ref	PRIMARY	PRIMARY	146	qms_qit.T0.definect_h	1	100.0
2	DERIVED	T26		eq_ref	PRIMARY,i_ytenant_id	PRIMARY	146	qms_qit.T0.id	1	100.0	Using where

• 过滤效果

select count(0) as num
FROM qms_qit.qms_qit_incominspectorder_h T0 -- 242024
	WHERE  1=1 
	and T0.pk_prod_dept IN ('2152784325897093129') -- 24788
	AND T0.check_type IN ('002', '011') -- 157733
	AND T0.inspect_result_source = 0			-- 240761
	AND T0.pk_org IN ('2152063020481118244') -- 143652
	AND T0.ytenant_id = 'p3d4zaqu' -- 143652
	AND T0.dr = 0 -- 220464

解决方案

• 由于 DISTINCT 操作，MySQL 需要创建临时表来处理去重，但是该SQL平台生成，领域无法干预，去除DISTINCT会降低到1s多

• 添加索引

ALTER TABLE qms_qit_incominspectorder_h ADD INDEX idx_prod_dept (pk_prod_dept, ytenant_id, dr);