问题现象

• 问题号：UCMFG-293684

• 核心4分库5

SELECT DISTINCT T0.note AS `note`, T0.pk_org AS `pk_org`, T0.inspectDate AS `inspectDate`, NULL AS `currentAuditor`, NULL AS `pk_materialsku`
              , NULL AS `pk_materialsku_code`, NULL AS `pk_materialsku_name`, NULL AS `dr`, T0.check_type AS `billtype`, T0.trantype AS `trantype`
              , T0.code AS `code`, T0.verifystate AS `verifystate`, T0.freect_h AS `freect_h`, 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.inspectnum AS `inspectnum`, T0.pk_material AS `pk_material`, T0.pk_batchcode AS `pk_batchcode`
              , T0.validityDate AS `validityDate`, T0.initinspectorder_id AS `initinspectorder_id`, T0.vchangerate AS `vchangerate`, T0.inspectionplanSingle AS `inspectionplanSingle`, T0.spotchekastnum AS `spotchekastnum`
              , T0.spotcheknum AS `spotcheknum`, T0.inspectRule AS `inspectRule`, T0.inspectastnum AS `inspectastnum`, T0.castunitid AS `castunitid`, T0.applycheckbillType AS `applycheckbillType`
              , T0.vapplycheckcode AS `vapplycheckcode`, T0.pk_sample AS `pk_sample`, T0.applycheckDate AS `applycheckDate`, T0.combinecheck AS `combinecheck`, T0.pk_test AS `pk_test`
              , T0.sourcebilltype AS `sourcebilltype`, T0.vsourcecode AS `vsourcecode`, T0.sourcerowno AS `sourcerowno`, T0.orderbilltype AS `orderbilltype`, T0.samplemethod_detail_id AS `sampleMethodDetailId`
              , T0.vordercode AS `vordercode`, T0.vorderrowno AS `vorderrowno`, T0.pk_busiperiod AS `pk_busiperiod`, T0.busistartDate AS `busistartDate`, T0.pk_inspecter AS `pk_inspecter`
              , T0.busiendDate AS `busiendDate`, T0.attachmentId AS `attachmentId`, T0.source_order_code AS `sourceOrderCode`, T0.pk_inspectdept AS `pk_inspectdept`, T0.source_order_line_no AS `sourceOrderLineNo`
              , T0.source_order_type AS `sourceOrderType`, T0.inspectionplanVersion AS `inspectionplanVersion`, T0.recheck AS `recheck`, T0.inspectResult AS `inspectResult`, T0.pk_inspectionplan AS `pk_inspectionplan`
              , T0.qastnum AS `qastnum`, T0.qnum AS `qnum`, T0.nqastnum AS `nqastnum`, T0.nqnum AS `nqnum`, T0.qrate AS `qrate`
              , T0.pk_prod_dept AS `pk_prod_dept`, T0.samnum AS `samnum`, T0.unsamnum AS `unsamnum`, T0.chastnum AS `chastnum`, T0.chnum AS `chnum`
              , T0.chrate AS `chrate`, T0.creator AS `creator`, T0.create_time AS `createTime`, T0.modifier AS `modifier`, T0.cunitid AS `cunitid`
              , T0.modify_time AS `modifyTime`, T0.auditor AS `auditor`, T0.audit_time AS `auditTime`, T0.pk_applycheckorg AS `pk_applycheckorg`, T0.pk_applycheckdept AS `pk_applycheckdept`
              , T0.terminate AS `terminate`, T0.version AS `version`, T0.project_id AS `projectId`, T0.wbs AS `wbs`, T0.activity AS `activity`
              , 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.currentStage AS `currentStage`
              , 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.vcol1 AS `definect_h__SXDDH`
              , T8.lcol1 AS `definect_h__KHTT`, T8.vcol2 AS `definect_h__YWY`, T8.id AS `definect_h__id`, T20.vbnote AS `vbnote`, T20.inspectResult AS `resultListInspectResult`
              , T20.freect_b AS `freect_b`, T20.pk_qualify_grade AS `pk_qualify_grade`, T20.nnum AS `nnum`, T20.nastnum AS `nastnum`, T20.is_storage AS `is_storage`
              , T20.baditemFlag AS `baditemFlag`, T20.quality_grade AS `qualityGrade`, 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 = 'jgvq733m'
                       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 = 'jgvq733m'
                       AND T26.dr = 0
WHERE T0.source_order_code LIKE '%000799%' ESCAPE '\\'
    AND (T0.check_type IN ('002', '011')
        AND T0.inspect_result_source = 0
        AND T0.dr = 0)
    AND T0.pk_org IN ('2007173999919890438', '2007172548215701508')
    AND T0.ytenant_id = 'jgvq733m'
    AND T0.dr = 0
ORDER BY T0.code DESC
LIMIT 0, 20;

问题原因

• 执行计划

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	T0	NULL	index	idx_checktype_dr,i_pk_org,i_irs_yten_dr	y_idx_code_tenantid_dr	352	NULL	80	0.27	Using where; Backward index scan; Using temporary
1	SIMPLE	T20	NULL	ref	IX_pk_inspectorder_h,idx_ytenant_id,idx_pk_inspectorder_ytenant	IX_pk_inspectorder_h	146	qms_qit.T0.id	1	100.0	Using where
1	SIMPLE	T8	NULL	eq_ref	PRIMARY	PRIMARY	146	qms_qit.T0.definect_h	1	100.0	NULL
1	SIMPLE	T26	NULL	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  -- 593069
WHERE T0.source_order_code LIKE '%000799%' ESCAPE '\\' -- 26
AND T0.check_type IN ('002', '011') -- 312736
AND T0.inspect_result_source = 0 -- 593051
AND T0.dr = 0 -- 568933
AND T0.pk_org IN ('2007173999919890438', '2007172548215701508') -- 66040
AND T0.ytenant_id = 'jgvq733m' -- 66040
AND T0.dr = 0 -- 568933

解决方案

方案一

• 如果limit n很小并且有order by，会优先选择 code 字段的索引，按下面修改索引：

ALTER TABLE qms_qit_incominspectorder_h drop index y_idx_code_tenantid_dr;

ALTER TABLE qms_qit_incominspectorder_h ADD INDEX i_code_yten_scode_org_type (
code, -- order by code
ytenant_id, -- 必选，等值，高选择性
source_order_code,  pk_org, check_type);

• 如果limit n很大（比如1000），该索引还是会性能很差

方案二

• 将7个检验单列表order by code 改成 order by id，补充下面索引

ALTER TABLE qms_qit_incominspectorder_h ADD INDEX i_yten_scode_org_type (
ytenant_id, -- 必选，等值，高选择性
source_order_code,  pk_org, check_type);

• 但是该sql过滤效果最好的是source_order_code

方案三

• 将7个检验单列表改成source_order_code LIKE 改为source_order_code =

ALTER TABLE qms_qit_incominspectorder_h ADD INDEX i_scode_yten_org_type (source_order_code, ytenant_id, pk_org, check_type);

贡献者: 靳琛

上一页20251105-慢SQL

下一页20251217-检验单索引优化