20251206-慢SQL
约 1042 字大约 3 分钟
2025-12-06
问题现象
问题号: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);