1、使用?exists 優化 in ()

優化前：

select
id, order_no, apply_time, apply_dept, apply_operator, purpose, stage, remark
from BranchWarehouseApplyMaster
where stage = 0 and warehouse_id = 1
and apply_dept in (
select emp_DeptID
from Employee
where emp_ID = 'manager'
)

優化后：

select
id, order_no, apply_time, apply_dept, apply_operator, purpose, stage, remark
from BranchWarehouseApplyMaster
where stage = 0 and warehouse_id = 1
and exists (
select 1
from Employee
where emp_ID = 'manager'
and emp_DeptID = apply_dept
)

2、使用?exists 優化 in ()

優化前：

select
id, order_no, apply_time, apply_dept, apply_operator, purpose, stage, remark
from BranchWarehouseApplyMaster
where stage = 0 and warehouse_id = 1
and apply_dept = (
select top 1 emp_DeptID
from Employee
where emp_ID = 'manager'
)

?優化后：

select
id, order_no, apply_time, apply_dept, apply_operator, purpose, stage, remark
from BranchWarehouseApplyMaster
where stage = 0 and warehouse_id = 1
and exists (
select 1
from Employee
where emp_ID = 'manager'
and emp_DeptID = apply_dept
)

3、最終優化建議

優化前：

select
id, order_no, apply_time, apply_dept, apply_operator, purpose, stage, remark
from BranchWarehouseApplyMaster
where stage = 0 and warehouse_id = 1
and exists (
select 1
from Employee
where emp_ID = 'manager'
and emp_DeptID = apply_dept
)

優化說明：

1. 索引優化（關鍵步驟）：

   • 主表索引：為BranchWarehouseApplyMaster創建復合索引：

     sql

     復制

     下載

     CREATE INDEX master_idx ON BranchWarehouseApplyMaster(stage, warehouse_id, apply_dept);

     該索引直接覆蓋WHERE條件（stage和warehouse_id作為最左前綴）和連接字段apply_dept，避免全表掃描。

   • 子查詢表索引：為Employee創建覆蓋索引：

     sql

     復制

     下載

     CREATE INDEX emp_idx ON Employee(emp_ID, emp_DeptID);

     該索引直接覆蓋子查詢條件（emp_ID = 'manager'）和返回字段emp_DeptID，大幅提升子查詢效率。

2. 重寫EXISTS為IN：

   • 原EXISTS子查詢逐行檢查，效率較低。改為IN子查詢后：

     • 子查詢先執行，結果（部門ID列表）被緩存。

     • 主表通過apply_dept IN (...) + 復合索引快速定位記錄。

   • 實際測試中，多數數據庫（如MySQL）對IN的優化優于EXISTS，尤其當子查詢結果集較小時。

3. 執行計劃提示（可選）：

   • /*+ USE_INDEX(master_idx) */?強制使用創建的復合索引，避免優化器誤選低效索引。

備選方案（JOIN寫法）：

sql

復制

下載

SELECT m.id, m.order_no, m.apply_time, m.apply_dept, m.apply_operator, m.purpose, m.stage, m.remark
FROM BranchWarehouseApplyMaster m
JOIN (SELECT DISTINCT emp_DeptID FROM Employee WHERE emp_ID = 'manager'
) e ON m.apply_dept = e.emp_DeptID  -- 預過濾部門列表
WHERE m.stage = 0 AND m.warehouse_id = 1;

優點：子查詢僅執行一次，通過DISTINCT去重后連接，避免重復掃描。

優化效果：

• 索引生效：主表通過復合索引快速過濾stage=0 + warehouse_id=1的記錄，再通過apply_dept匹配部門列表。

• 子查詢優化：Employee表通過索引直接定位manager的部門，無需全表掃描。

• 數據流減少：IN或JOIN寫法將逐行校驗改為集合匹配，減少數據庫內部循環操作。

注意：實際執行前需在測試環境驗證執行計劃，確保索引被正確使用。如果manager對應的部門極少，IN/JOIN方案更優；如果部門較多，可考慮恢復EXISTS但確保索引有效。

?