预查询ID与子查询删除的性能对比及选型建议

前提回顾 #

表结构为 order 一对多关联 order_entries，order_entries 再分别一对多关联 consignments 和 discounts；需频繁通过 order_id 删除关联子表数据，无触发器和级联删除，每次涉及的 order_entries ID数量为1-8个。

两种方式的细节对比 #

方式一：预选ID后删除 #

-- 1. 一次性获取目标ID
SELECT id FROM order_entries WHERE order_id = ?
-- 2. 使用获取到的ID执行两次删除
DELETE from consignments WHERE order_entry_id IN (?)
DELETE from discounts WHERE order_entry_id IN (?)

• 核心优势：仅对 order_entries 执行一次查询，避免重复扫描该表。由于ID数量极少（1-8个），数据库处理 IN(...) 时会直接转为多个等值判断的OR组合，匹配效率极高。
• 额外成本：仅需在应用层暂存几个数字ID，网络往返开销可忽略；同时能保证两次删除操作使用完全相同的ID集合，避免两次子查询期间数据变更导致的一致性问题。

方式二：子查询直接删除 #

DELETE from consignments WHERE order_entry_id IN (SELECT id FROM order_entries WHERE order_id = ?)
DELETE from discounts WHERE order_entry_id IN (SELECT id FROM order_entries WHERE order_id = ?)

• 数据库优化情况：现代数据库（如MySQL、PostgreSQL）会对这类简单子查询做优化，比如转为JOIN或缓存子查询结果，但两次删除会分别执行一次子查询——即使结果完全相同，部分数据库也不会自动复用之前的查询结果，相当于多扫描了一次 order_entries。
• 潜在风险：如果两次删除之间，order_entries 表对应的数据发生变更（比如被其他操作修改/删除），两次子查询的结果可能不一致，导致删除的数据不匹配。

建议结论 #

因为每次涉及的ID数量仅1-8个，优先选择方式一：

• 性能上，少一次 order_entries 查询，整体开销更低；
• 数据一致性更可靠，两次删除用的是同一批ID；
• 应用层的额外工作可忽略，几乎无维护成本。

如果不想在应用层暂存ID，也可以用数据库的CTE（公共表表达式）在数据库内部缓存ID，兼顾两种方式的优点：

WITH selected_ids AS (SELECT id FROM order_entries WHERE order_id = ?)
DELETE FROM consignments WHERE order_entry_id IN (SELECT id FROM selected_ids);
DELETE FROM discounts WHERE order_entry_id IN (SELECT id FROM selected_ids);

内容的提问来源于stack exchange，提问作者Dmitry