AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.321.sql

-- 1.0.321.sql  S2 排程性能 L1：齐套检查/BOM 展开热点路径补齐二级索引。
--   背景：EXPLAIN ANALYZE 实测 ic_bom_child 子件查询扫 59,799+22,108 行、服务端 103ms；
--         InvMaster 库存查询扫 18,368 行、17.9ms。二者均为全表扫描（无可用索引）。
--   本脚本只建【非唯一】二级索引 + 一个主键，不改字段类型/可空性，不增删改任何业务数据，
--   不删除任何现有索引。因此不存在数据冲突导致失败的可能，无需前置去重。
--   索引列依据见 doc/plan/S2排程性能-L1索引体系执行任务书.md §2.4（逐条对应实际查询条件）。
-- ============================================================================

-- ========== 〇、通用幂等宏说明 ==========
-- 每个索引统一走：查 information_schema.STATISTICS 判是否已存在 -> 不存在才 ADD。
-- 重复执行本脚本不会报错，也不会重复创建。

-- ========== 一、ic_bom_child（59,799 行，当前零索引含主键） ==========
-- 1.1 主键。已实证 Id 全部唯一且非空（59799/59799，null=0）；
--     且全仓库无任何应用侧 INSERT 写该表（仅 MaterialRequirementCalculator 的 SELECT），
--     不会因新主键产生重复键失败。
SET @has_pk := (SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ic_bom_child' AND INDEX_NAME = 'PRIMARY');
SET @sql := IF(@has_pk = 0,
    'ALTER TABLE ic_bom_child ADD PRIMARY KEY (Id)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 1.2 主查询路径：WHERE bom_id=? AND tenant_id=? AND IsDeleted=0
SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ic_bom_child'
      AND INDEX_NAME = 'idx_ic_bom_child_tenant_bom');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE ic_bom_child ADD INDEX idx_ic_bom_child_tenant_bom (tenant_id, bom_id, IsDeleted)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 1.3 反查父阶 / 按物料查用量
SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ic_bom_child'
      AND INDEX_NAME = 'idx_ic_bom_child_tenant_item');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE ic_bom_child ADD INDEX idx_ic_bom_child_tenant_item (tenant_id, item_number)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== 二、ItemMaster（22,108 行） ==========
-- 现有 uk_ItemMaster_factory_item (tenant_id, factory_ref_id, ItemNum) 因中间缺 factory_ref_id
-- 无法服务 BOM 子件关联，补 (tenant_id, ItemNum)。不动现有唯一索引。
SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ItemMaster'
      AND INDEX_NAME = 'idx_ItemMaster_tenant_item');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE ItemMaster ADD INDEX idx_ItemMaster_tenant_item (tenant_id, ItemNum)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== 三、ic_bom（5,431 行） ==========
-- 3.1 ResolveBomIdByItemAsync：WHERE tenant_id=? AND IsDeleted=0 AND item_number=?
SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ic_bom'
      AND INDEX_NAME = 'idx_ic_bom_tenant_item');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE ic_bom ADD INDEX idx_ic_bom_tenant_item (tenant_id, item_number, IsDeleted)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- 3.2 ResolveBomIdAsync 的 bom_number 分支
SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ic_bom'
      AND INDEX_NAME = 'idx_ic_bom_tenant_bomnum');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE ic_bom ADD INDEX idx_ic_bom_tenant_bomnum (tenant_id, bom_number)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== 四、InvMaster（18,368 行） ==========
SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'InvMaster'
      AND INDEX_NAME = 'idx_InvMaster_tenant_item');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE InvMaster ADD INDEX idx_InvMaster_tenant_item (tenant_id, ItemNum)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== 五、PurOrdDetail（95 行，为将来增长预留） ==========
SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'PurOrdDetail'
      AND INDEX_NAME = 'idx_PurOrdDetail_item_ord');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE PurOrdDetail ADD INDEX idx_PurOrdDetail_item_ord (ItemNum, PurOrdRecID)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== 六、ic_item_stockoccupy（497 行；排程起始 DELETE + 占用汇总） ==========
SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'ic_item_stockoccupy'
      AND INDEX_NAME = 'idx_ic_item_stockoccupy_tenant_bang');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE ic_item_stockoccupy ADD INDEX idx_ic_item_stockoccupy_tenant_bang (tenant_id, bang_id, IsDeleted)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== 七、b_bom_child_examine（8,679 行，齐套结果明细） ==========
-- examine_id 必须最左：ResourceCheckResultWriter 的 UPDATE 只带 examine_id、不带 tenant_id。
SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'b_bom_child_examine'
      AND INDEX_NAME = 'idx_b_bom_child_examine_examine');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE b_bom_child_examine ADD INDEX idx_b_bom_child_examine_examine (examine_id, tenant_id, is_use)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== 八、b_examine_result（279 行，齐套结果头） ==========
SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'b_examine_result'
      AND INDEX_NAME = 'idx_b_examine_result_tenant_entry');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE b_examine_result ADD INDEX idx_b_examine_result_tenant_entry (tenant_id, sentry_id, IsDeleted)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @has_ix := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'b_examine_result'
      AND INDEX_NAME = 'idx_b_examine_result_tenant_morder');
SET @sql := IF(@has_ix = 0,
    'ALTER TABLE b_examine_result ADD INDEX idx_b_examine_result_tenant_morder (tenant_id, morder_no)', 'DO 0');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ========== 九、刷新统计信息，让优化器立刻用上新索引 ==========
ANALYZE TABLE ic_bom_child;
ANALYZE TABLE ItemMaster;
ANALYZE TABLE ic_bom;
ANALYZE TABLE InvMaster;
ANALYZE TABLE PurOrdDetail;
ANALYZE TABLE ic_item_stockoccupy;
ANALYZE TABLE b_bom_child_examine;
ANALYZE TABLE b_examine_result;

-- ============================================================================
-- 回滚 SQL（本脚本只加不减，回滚即逐条 DROP，不会丢任何数据）：
--   ALTER TABLE ic_bom_child        DROP INDEX idx_ic_bom_child_tenant_bom;
--   ALTER TABLE ic_bom_child        DROP INDEX idx_ic_bom_child_tenant_item;
--   ALTER TABLE ic_bom_child        DROP PRIMARY KEY;
--   ALTER TABLE ItemMaster          DROP INDEX idx_ItemMaster_tenant_item;
--   ALTER TABLE ic_bom              DROP INDEX idx_ic_bom_tenant_item;
--   ALTER TABLE ic_bom              DROP INDEX idx_ic_bom_tenant_bomnum;
--   ALTER TABLE InvMaster           DROP INDEX idx_InvMaster_tenant_item;
--   ALTER TABLE PurOrdDetail        DROP INDEX idx_PurOrdDetail_item_ord;
--   ALTER TABLE ic_item_stockoccupy DROP INDEX idx_ic_item_stockoccupy_tenant_bang;
--   ALTER TABLE b_bom_child_examine DROP INDEX idx_b_bom_child_examine_examine;
--   ALTER TABLE b_examine_result    DROP INDEX idx_b_examine_result_tenant_entry;
--   ALTER TABLE b_examine_result    DROP INDEX idx_b_examine_result_tenant_morder;
-- 回滚后还需把 sys_db_migration_log 中 version='1.0.321' 的行删除，脚本才会重跑。
-- ============================================================================