从两个相关表中大容量删除性能

Question

我有两个表，结构如下：(系统中的每个对象类型都有多个ai_$object表)

       ai_$object         ,       ai_ext_ids
--------------------        -------------------------- 
ext_id | hash                    id | ext_id | object_name | sfID

通过批量删除，我得到了sf_ids，并且需要从这两个表中删除记录，最愚蠢的删除方法如下：

在伪代码中-->

$idToBeDeleted = SELECT ext_id FROM ai_tr_tbl_extids WHERE objectName='$object' AND sfID ='$s'" ;

DELETE FROM ai_tr_tbl_extids WHERE objectName='$object' AND sfID ='$s';

DELETE FROM ai_$object WHERE ext_id='$idToBeDeleted';

我的问题是如何提高这部分的性能？这段代码使用in循环运行，我是否应该使用数组进行批量删除: SELECT * FROM table WHERE ID IN (1,2,3,4)

Answer 1

为了获得更好的性能，应该对objectName列进行索引。并尝试以下查询。

$idToBeDeleted = SELECT ext_id FROM ai_tr_tbl_extids WHERE objectName='$object' AND sfID ='$s'" ;

DELETE FROM ai_tr_tbl_extids WHERE ext_id='$idToBeDeleted';

DELETE FROM ai_$object WHERE ext_id='$idToBeDeleted';

但是，如果您能够获得要删除的ext_id的列表，则可以删除循环，只使用IN删除。

DELETE FROM ai_tr_tbl_extids WHERE ext_id IN ($idToBeDeletedCommaSeparatedValue');

DELETE FROM ai_$object WHERE ext_id IN ($idToBeDeletedCommaSeparatedValue');