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、重复查询优化
