sql优化：使用 exists 优化 in () 或 = ()

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
)

优化说明：

索引优化（关键步骤）：
- 主表索引：为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，大幅提升子查询效率。
重写EXISTS为IN：
- 原EXISTS子查询逐行检查，效率较低。改为IN子查询后：
  - 子查询先执行，结果（部门ID列表）被缓存。
  - 主表通过apply_dept IN (...) + 复合索引快速定位记录。
- 实际测试中，多数数据库（如MySQL）对IN的优化优于EXISTS，尤其当子查询结果集较小时。
执行计划提示（可选）：
- /*+ 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去重后连接，避免重复扫描。