20260202-追溯效率优化

约 325 字大约 1 分钟

2026-2-2

现象

追溯查询超时

问题原因

索引优化

之前库存追溯池查询条件source_bill_row_id调整为了order_child_row_id，索引需要同步调整

SELECT
    T0.id AS `id`,
    T0.bill_row_id AS `billRowID`,
    T0.source_bill_id AS `sourceBillID`,
    T0.source_bill_row_id AS `sourceBillRowID`,
    T0.order_id AS `orderId`,
    T0.order_row_id AS `orderRowId`,
    T0.order_child_row_id AS `orderChildRowID`,
    T0.product_id AS `productID`,
    T0.batch_number AS `batchNumber`,
    T0.bill_type AS `billType`,
    T0.amount AS `amount`,
    T0.unit_id AS `unitID`
FROM
    `qms_dfm`.qms_qts_w_tracepool T0
WHERE
    T0.order_child_row_id IN(2361253311054086150, 2361253311054086153, 2361253311054086156, 2361253311054086159, 2361253311054086162, 2361253311054086165, 2361253311054086168, 2361253311054086171, 2361253311054086174, 2361253311054086177, 2361253311054086180)
  AND T0.bill_type = 'st_materialout'
  AND T0.source_bill_type IN('productionorder.po_production_order', 'productionorder.po_production_order_ustock')
  AND T0.dr = 0
  AND T0.ytenant_id = 'fd3krut9';

TABLE_NAME	INDEX_NAME	INDEX_TYPE	FIRST_COLUMN	ALL_COLUMNS	COLUMN_COUNT	UNIQUENESS	IS_PRIMARY
qms_qts_w_tracepool	idx_ytenant_id	BTREE	ytenant_id	ytenant_id	1	NON-UNIQUE	NO
qms_qts_w_tracepool	i_supplier	BTREE	supplier_id	supplier_id	1	NON-UNIQUE	NO
qms_qts_w_tracepool	i_source_row_id_bill	BTREE	source_bill_row_id	"source_bill_row_id, bill_type, dr, ytenant_id"	4	NON-UNIQUE	NO
qms_qts_w_tracepool	i_product	BTREE	product_id	product_id	1	NON-UNIQUE	NO
qms_qts_w_tracepool	i_product_bill	BTREE	product_id	"product_id, bill_type, dr, ytenant_id"	4	NON-UNIQUE	NO
qms_qts_w_tracepool	i_order_row_id_bill	BTREE	order_row_id	"order_row_id, bill_type, dr, ytenant_id"	4	NON-UNIQUE	NO
qms_qts_w_tracepool	i_org	BTREE	main_org_id	main_org_id	1	NON-UNIQUE	NO
qms_qts_w_tracepool	PRIMARY	BTREE	id	id	1	UNIQUE	YES
qms_qts_w_tracepool	i_customer	BTREE	customer_id	customer_id	1	NON-UNIQUE	NO
qms_qts_w_tracepool	i_bill_row_id_bill	BTREE	bill_row_id	"bill_row_id, bill_type, dr, ytenant_id"	4	NON-UNIQUE	NO

索引调整

call p_aa_updateindex('qms_qts_w_tracepool', 'i_order_child_row_bill', 'alter table qms_qts_w_tracepool add index i_order_child_row_bill(order_child_row_id, bill_type);', 'alter');

call p_aa_updateindex('qms_qts_w_tracepool', 'i_supplier', 'alter table qms_qts_w_tracepool drop index i_supplier;', 'alter');
call p_aa_updateindex('qms_qts_w_tracepool', 'i_product', 'alter table qms_qts_w_tracepool drop index i_product;', 'alter');
call p_aa_updateindex('qms_qts_w_tracepool', 'i_customer', 'alter table qms_qts_w_tracepool drop index i_customer;', 'alter');
call p_aa_updateindex('qms_qts_w_tracepool', 'i_source_row_id_bill', 'alter table qms_qts_w_tracepool drop index i_source_row_id_bill;', 'alter');

2、重复查询优化